There are countless sources for information on SQL Server available today. There are books, webcasts, conferences, and blogs. With all of these sources, it is easy to forget that there are white papers available from Microsoft. It is important that we make an effort to read the white papers that are available.
The trouble is, there are so many white papers available from Microsoft and other sources, we need to know which are most important. Should those from the latest release of SQL Server trump the older white papers? If a white paper hasn’t been updated, is it still valuable?
As I’ve thought about this, there are a few Microsoft white papers that are available that are more important than others. They should be read by every DBA, and anyone that has a hand in managing or administering a SQL Server environment. These are:
Troubleshooting Performance Problems in SQL Server 2008 (also available for SQL Server 2005) – This whitepaper is where I first turn people when they want to start learning about how to fix their performance issues with SQL Server. It hasn’t been updated for SQL Server 2012, but the concepts remain the same, and the approaches recommended are just as valid.
SQL Server 2005 Performance Tuning using the Waits and Queues – Similar to the first item, here’s another white paper in desperate need of an update but with information just as useful today as when it was first written. When you look at how many DBAs performance tune their environments today, the roots of those methodologies are found in this white paper. In fact, many third party monitoring tools are built around this white paper.
SQL Server 2012 Security Best Practices – Security is never one of the most exciting topics, but this white paper walks through some basics, and helps dispel some myths. It’s one of those papers that can easily provide a quick reference for security questions. It has an easily digestible length, and can be used to send to managers and peers when security questions arise.
SQL Server 2012 Upgrade Technical Reference Guide Whitepaper - Contrary to the other white papers in this list, this document is fairly large, coming in at 454 pages. While lengthy, this document should be kept on hand and read through prior to any SQL Server 2012 upgrade. In fact, when people suggest doing “a quick weekend upgrade”, send this over to them beforehand to verify that they’ve considered everything in the document already. Not to stop or slow down the upgrade, but you need to be sure that upgrades don’t lead to disasters, because at the end of the day DBAs will be responsible for those results.
The white papers listed above are just a few of the ones available from Microsoft. There are countless others, and even more from other vendors. What white papers do you recommend to others on SQL Server?
We are down to the last few days before first SQL Saturday in Fargo. It’s been some work pulling it all together, but with the help of Ted Krueger (Blog | @Onpnt), Sarah Strate (Blog | @DanceM0m), and Jes Borland (Blog | @grrl_geek), we are almost ready for the weekend. The question I have for you is… are you registered? Of course, not everyone can make it, and many of you aren’t close enough to join us. For you folks, I ask, have you let your peers in Fargo, Moorhead, or West Fargo know about this event?
As part of the schedule of sessions, I’ll be presenting my session from last year’s PASS Summit:
Discovering the Plan Cache
Execution plans are stored after execution in the plan cache. This metadata about how queries are executed can provide insight into how your SQL Server environment is functioning. By using XQuery to browse and search the plan cache you can find potential performance issues and opportunities to tune your queries. This information can be used to help reduce issues related to parallelism, shift queries from using scans to using seek operations, or discover exactly which queries are using what indexes. All of this and more is readily available through the plan cache. In this session we will explore the plan cache and start you on the road to discovery.
If you are in the area and looking to enhance your career and expand your knowledge on SQL Server, you should take the day and join us to learn about SQL Server. The event is free and you can get registered here.
In last week’s post, I talked about the steps required to backup a Azure SQL Database. That post leaves us asking what’s the use of having a backup of a database, if you aren’t going to restore it somewhere. And more importantly, if you can’t restore a database, or don’t know how, then the backup has no value. Thus, restoring is the other half of the coin that is as critical as the first half.
In today’s post, I’m going to take the backup that was previously created and restore it to a local SQL Server instance. Personally, I first needed to do this when I wanted to do some development work on one of my Azure SQL Databases while I was offline. I could have used SQL Server Data Tools but wanted to have a fully functional database that I could redeploy to Azure when I reconnected. Hence the local restore.
The Keys to Restoring
The first step to restoring a backup of an Azure SQL Database to a local server is getting access to the backup of the database. This might seem to be an obvious step, but it lends itself to the question of how a backup created within the Azure will be made available to a local instance. There are a number ways to accomplish this need. As I mentioned in the previous post, one of the options is to download the file from the Windows Azure portal. There is another way that removes the need to perform the download manually.
Instead, the download of the bacpac backup file can be performed as a part of the restore. To accomplish the restore in this manner, the Access Keys for the storage account are required. The Access Keys are accessible from the Storage Account list by clicking the Manage Access Keys button. The button can be found in the bottom menu bar, indicated by an orange arrow in Figure 1.
Figure 1. Storage Account List
The Manage Access Keys window provides the current access keys for each storage account, shown in Figure 2. The access keys allow secured connections to storage accounts; which can be used by SQL Server Management Studio to retrieve bacpac files. There are two access keys for each storage account. Either key will work for the restore, select one of the keys and copy the value.
Figure 2. Manage Access Keys Screen
Executing the Restore
With the keys in hand, the next step in restoring a Azure SQL Database locally is to right-click on the Database container in SQL Server Management Studio. From this context menu, shown in Figure 3, select the option to Import Data-tier Application. Contrary to backups from on-premise SQL Server instances, the backup format for Azure SQL Databases is a data-tier format, or bacpac. This format contains all of the schema and data for a database.
Figure 3. Right-click Menu for Databases in SQL Server Management Studio
After selecting Import Data-tier Application button, the Import Data-tier Application wizard initiates. The first screen in the wizard is the obligatory Introduction screen, shown in Figure 4. This screen provides a description of the restore, or import process. From the perspective of restoring a Azure SQL Database, those two terms are synonymous. The biggest, and only, decision on the screen is whether the Introduction screen should show up on any future restores.
The next screen in the wizard is Import Settings. Since this restore is assuming that the bacpac file has not been downloaded, select the Connect button, located in the middle-right of the screen. The Connect to Windows Azure Storage window then appears, shown in figure 5. Type in the name of the Storage Account and paste in the Access Key, retrieved previously. Either the primary or secondary key will suffice.
Figure 5. Connect to Windows Azure Storage window
After the connection is secured, the drop-down box for the available container will populate. Select the container and then the file from the list provided. If done correctly, the backup file will be listed. Lastly, double-check the location for the temporary file. A temporary file is created for the backup while it is being restored. For all intents, this is the same as a manual download of the bacpac file. The temporary file location will need to be large enough to store the backup file. When finished, select the Next button.
The next wizard screen is the Database Settings screen. There are few options for the database. The first is the database name; which will default to the name of the bacpac file. The next two are the names of the data and transaction log files for the database. By default there will only be the two files. If more files, or filegroups, are desired, or needed, the database will need to be updated after the restore. Click next to advance to the next screen.
Over the last two screens in the Import Data-tier Application wizard, there were few options to set for the restore operation but they are all that is needed. The next screen summarizes those options for review, shown in Figure 8. If the import settings are correct, click on Finish to start the restore operation.
At the conclusion of the wizard, the database is restored. As simple as this post makes the restore appear is the actuality in the difficulty in the restore process. The only thing I’ve found lacking in the restore is the option to script out the process to allow some automation of the restore, but maybe there are PowerShell commands that could do that. As you look forward to backups and restores between on-premise and local databases, be sure to understand that while terminology may change, the world of database management is not getting that much more difficult. In fact, its getting just a little bit easier.
For those that follow me on twitter (@StrateSQL), you’ll know that throughout the day I tweet out some links of things I find interesting. These tweets include “Reading” in the message and are items I’ve read over the past few days, usually after hours when sharing would be less than useful, and spaced out to avoid spamming. The content of the links usually pertain to SQL Server, technology, and career topics; which I think others would find useful.
Most Popular Link
The most popular item tweeted out last week had nothing to do with SQL Server, but it is seriously cool. I would really love to get my hands on one of the cameras for a night at SQL Karaoke… the video might actually be steady then.
“This New Camera Stabilizer Could Change Cinematography Forever” bit.ly/Z6rQYl <– I want one of these cameras.
Of course, no week would be complete without a few off-topic links. These have nothing to do with technology or your career, but they sure are entertaining and interesting (and a couple are pretty gross).
Got something you think I should read and share, leave a comment below. Also, if you want to see all of the links that were tweeted out last week? Then follow the links on my tumblr blog or subscribe to it’s RSS feed.
Welcome to this Friday’s reblog summary post. The aim of these posts is to bring some old posts that newer readers may not have seen back to the forefront. As with many technology blogs, just because a post is old, doesn’t mean it doesn’t still have value.
Some posts from years gone by that you may have missed are:
Do you have something from years gone by that was posted during this week? If so, leave a comment below and we’ll give some new life to good knowledge.
EDIT: Oops, apparently I forgot to remove a number of items not worth while from the original post.
When I first started working with Azure SQL Databases, I wasn’t sure what it would take to backup a database. In fact, I wasn’t even sure if this concept was appropriate when it came to databases hosted in “the cloud”. What I found was that the concept of backing up databases in the Azure cloud does still exist, though the terminology is slightly than we are used to with on-premise SQL Server instances.
As evidence that things are different when it comes to backup Azure SQL Databases, the BACKUP DATABASE command is not available. If you try to run a backup statement, an error stating that “Statement ‘BACKUP DATABASE’ is not supported in this version of SQL Server” is returned. Without this command, then how do you create a backup of your database?
If we consider the basics of what a backup of a database is, all we are looking for is a copy of the data and schema that matches what is currently in the database. There are a few ways to accomplish this. For this post, I am going to walk-through the steps required to backup a database to blog storage within the Azure environment. From a high level, the steps are:
Create a storage account
Create a container
Export the database
It may seem like this is too easy of a process to backing up a database, but the reality is that it is this easy. Just being a different platform, doesn’t mean it has to be challenging.
Create a Storage Account
We’ll start this process by assuming that no backups of the Azure SQL Database have ever been done. Based on that, the first step is to create a place to store the database. Begin by logging into the Widows Azure Portal, and browsing to the Storage folder. The screen will look similar to the one included in Figure 1. If you don’t have any storage accounts created, the list will be empty, as in the figure. To create a new storage account, select New, as indicated with the orange arrow.
Figure 1. Empty Storage Account List
When it comes to creating the storage account, there are not that many options that are required for completion, but these options are fairly important. The first choice, shown in Figure 2, is deciding on the name for the storage account. The only requirement on this name is that the name is in all lowercase letters. Use what makes sense. The next two are pretty important though. First, decide which location/affinity group to have the storage located in. This is important because you’l likely want to keep this in the data center that the database is located in to reduce the costs incurred in moving around the data. Remember with Windows Azure, and other cloud solutions, you pay for what you use; which includes all of your network traffic. The last choice is the subscription under which the storage account will be created. Be sure to select the correct subscription to make certain that the correct account is charged for the backups.
With the details in place, select the Create Storage Account button. This will start the process for creating the storage account; which can take a few minutes. While the storage account is being created, the status for the account will indicate that it is being created. Once finished, the storage account will be listed under storage accounts with the status of Online, as shown in Figure 3.
Figure 3. Storage Account List with New Storage Account
Create a Container
Once the storage account is created, the next object needed is a container. The container creates a location that the database can be backed up to and the pathing required to access the data stored. To create the container, start by browsing to the newly created storage account and selecting the Containers listing, shown in Figure 4. From this screen, select the Add Container button, highlighted by the orange arrow in the figure.
Figure 4. Empty Container List Under Storage Account
The wizard for creating a container under the storage account is relatively simple. The wizard requires two choices to be made to create the container, shown in Figure 5. The first choice involves choosing a new for the container, again with lower-case letters. The second choice is to select the access granted for the container. Since the purpose of the container is to store a backup of the database, you should select Private access. There aren’t many cases where a backup of your databases should be accessible via anonymous access.
Figure 5. Create Container Wizard
Once the container wizard is completed, the container will become available. Once the container is created, it will be listed under the Containers list for the Storage Account, shown in Figure 6. With the container name, the URL for accessing the container is also provided on this screen.
Figure 6. Container List Under Storage Account with New Container
Export the Database
With a storage account and container created, there is now a place to backup the Azure SQL Database. In Azure terms, backing up the database is basically copying the database from one place to another to make a point-in-time copy of the data. The reality, though, that’s all backing up a database is and with an Azure SQL Database, creating a backup is done by exporting the database. The Export button is located at the bottom of the Database Dashboard, indicated by the orange arrow in Figure 7.
Figure 7. Database Dashboard
Once the export is started, the Export Database Settings screen will appear, showing in Figure 8. As with the other screens, there are a few options that need to be selected. The first is the name of the bacpac file; which is the format for the exporting from Azure SQL Database and is similar to DACPACs with the addition of data. Next select the subscription that ties to the Storage Account and Container where the backup will be stored. Once that is completed, enter an account and password with access to the database and start the export.
Figure 8. Export Database Settings Screen
When the export of the database is completed, it will appear in the container that was created in a previous step. As shown in Figure 9, the backup information includes the name, size and URL for accessing the backup. At this point, the backup is created and ready for what ever you need it for. If you want, you can even download it directly from the Azure portal, as shown by the arrow in Figure 9.
Figure 9. Empty Storage Account List
Summary
In this post, I covered the steps needed to create a backup, or export, of an Azure SQL Database. There are other ways available to accomplish this task, but this is the one that I turn to most often with the work that I am required to complete. With more complex Azure architectures, this may not be the ideal backup process, since this export focused on a single database. But for starting out with Azure SQL Databases and needing to accomplish that first and easy backup, this is the way to go.
For those with experience with backups in the cloud, are there options that I haven’t considered and should? Any gotchas that you’ve run into while working on backups?
When I first started working with Azure SQL Databases, I wasn’t sure what it would take to backup a database. In fact, I wasn’t even sure if this concept was appropriate when it came to databases hosted in “the cloud”. What I found was that the concept of backing up databases in the Azure cloud does still exist, though the terminology is slightly than we are used to with on-premise SQL Server instances.
As evidence that things are different when it comes to backup Azure SQL Databases, the BACKUP DATABASE command is not available. If you try to run a backup statement, an error stating that “Statement ‘BACKUP DATABASE’ is not supported in this version of SQL Server” is returned. Without this command, then how do you create a backup of your database?
If we consider the basics of what a backup of a database is, all we are looking for is a copy of the data and schema that matches what is currently in the database. There are a few ways to accomplish this. For this post, I am going to walk-through the steps required to backup a database to blog storage within the Azure environment. From a high level, the steps are:
Create a storage account
Create a container
Export the database
It may seem like this is too easy of a process to backing up a database, but the reality is that it is this easy. Just being a different platform, doesn’t mean it has to be challenging.
Create a Storage Account
We’ll start this process by assuming that no backups of the Azure SQL Database have ever been done. Based on that, the first step is to create a place to store the database. Begin by logging into the Widows Azure Portal, and browsing to the Storage folder. The screen will look similar to the one included in Figure 1. If you don’t have any storage accounts created, the list will be empty, as in the figure. To create a new storage account, select New, as indicated with the orange arrow.
Figure 1. Empty Storage Account List
When it comes to creating the storage account, there are not that many options that are required for completion, but these options are fairly important. The first choice, shown in Figure 2, is deciding on the name for the storage account. The only requirement on this name is that the name is in all lowercase letters. Use what makes sense. The next two are pretty important though. First, decide which location/affinity group to have the storage located in. This is important because you’l likely want to keep this in the data center that the database is located in to reduce the costs incurred in moving around the data. Remember with Windows Azure, and other cloud solutions, you pay for what you use; which includes all of your network traffic. The last choice is the subscription under which the storage account will be created. Be sure to select the correct subscription to make certain that the correct account is charged for the backups.
With the details in place, select the Create Storage Account button. This will start the process for creating the storage account; which can take a few minutes. While the storage account is being created, the status for the account will indicate that it is being created. Once finished, the storage account will be listed under storage accounts with the status of Online, as shown in Figure 3.
Figure 3. Storage Account List with New Storage Account
Create a Container
Once the storage account is created, the next object needed is a container. The container creates a location that the database can be backed up to and the pathing required to access the data stored. To create the container, start by browsing to the newly created storage account and selecting the Containers listing, shown in Figure 4. From this screen, select the Add Container button, highlighted by the orange arrow in the figure.
Figure 4. Empty Container List Under Storage Account
The wizard for creating a container under the storage account is relatively simple. The wizard requires two choices to be made to create the container, shown in Figure 5. The first choice involves choosing a new for the container, again with lower-case letters. The second choice is to select the access granted for the container. Since the purpose of the container is to store a backup of the database, you should select Private access. There aren’t many cases where a backup of your databases should be accessible via anonymous access.
Figure 5. Create Container Wizard
Once the container wizard is completed, the container will become available. Once the container is created, it will be listed under the Containers list for the Storage Account, shown in Figure 6. With the container name, the URL for accessing the container is also provided on this screen.
Figure 6. Container List Under Storage Account with New Container
Export the Database
With a storage account and container created, there is now a place to backup the Azure SQL Database. In Azure terms, backing up the database is basically copying the database from one place to another to make a point-in-time copy of the data. The reality, though, that’s all backing up a database is and with an Azure SQL Database, creating a backup is done by exporting the database. The Export button is located at the bottom of the Database Dashboard, indicated by the orange arrow in Figure 7.
Figure 7. Database Dashboard
Once the export is started, the Export Database Settings screen will appear, showing in Figure 8. As with the other screens, there are a few options that need to be selected. The first is the name of the bacpac file; which is the format for the exporting from Azure SQL Database and is similar to DACPACs with the addition of data. Next select the subscription that ties to the Storage Account and Container where the backup will be stored. Once that is completed, enter an account and password with access to the database and start the export.
Figure 8. Export Database Settings Screen
When the export of the database is completed, it will appear in the container that was created in a previous step. As shown in Figure 9, the backup information includes the name, size and URL for accessing the backup. At this point, the backup is created and ready for what ever you need it for. If you want, you can even download it directly from the Azure portal, as shown by the arrow in Figure 9.
Figure 9. Empty Storage Account List
Summary
In this post, I covered the steps needed to create a backup, or export, of an Azure SQL Database. There are other ways available to accomplish this task, but this is the one that I turn to most often with the work that I am required to complete. With more complex Azure architectures, this may not be the ideal backup process, since this export focused on a single database. But for starting out with Azure SQL Databases and needing to accomplish that first and easy backup, this is the way to go.
For those with experience with backups in the cloud, are there options that I haven’t considered and should? Any gotchas that you’ve run into while working on backups?
*UPDATE
Earlier this month, Grant Fritchey (Blog | @GFritchey) released a video of backing up an Azure SQL Database.
Each release of SQL Server is chalk full of new features and an assorted amount of bells and whistles. Some of these, like Availability Groups and ColumnStore, are great additions and their introduction includes much fanfare. Among all of those new features, is the occasional little bell which offers us a chance to change how we investigate SQL Server. In this case, the bell of the day is the new dynamic management function (DMF) named sys.dm_db_database_page_allocations.
Basics of sys.dm_db_database_page_allocations
The new DMF provides replacement functionality for DBCC IND. Based on the parameters provided, it will return a list of all pages that are associated with one or more tables and indexes. The available parameters for sys.dm_db_database_page_allocations are:
@DatabaseId: Database from which to return the page listing for tables and indexes. The parameter is required and accepts the use of the DB_ID() function.
@TableId: Object_id for the table from which to return the page listing. The parameter is required and accepts the use of the OBJECT_ID() function. NULL can also be used to return all tables.
@IndexId: Index_id from the table which the page list is from. The parameter is required and accepts the use of NULL to return information for all indexes.
@PartionId: ID of the partition which the page list is returning. The parameter is required and accepts the use of NULL to return information for all indexes.
@Mode: Defines the mode for returning data, the options are DETAILED or LIMITED. With LIMITED, the information is limited to page metadata, such as page allocation and relationships information. Under the DETAILED mode, additional information is provided, such as page type and inter-page relationship chains.
Uses for sys.dm_db_database_page_allocations
There are number of ways that sys.dm_db_database_page_allocations can be utilized. For starters, as a replacement for DBCC IND, the DMF can be used in all cases where the DBCC command would be used. The main advantage of using the DMF is the ability to easily join the page information to other DMVs and DMFs on the database. When using DBCC IND, the data would first need to be placed into a table to interact with other data.
If you’re not familiar with DBCC IND, there are plenty of good uses for that command and this DMF. Some of these include the following:
Investigating the structure of indexes: Useful for showing how an index is built to help form an understanding how SQL Server processes data. If a table or index is partitioned, this DMF can help validate that there are in-fact multiple B-tree structures for the index.
Researching page corruption: When you have a corrupt page, looking at the preceding and following pages can help to identify the data on the corrupt page.
Investigating space utilization: When a table has been identified as taking a large amount of space, use this DMF to determine what types of pages are being utilized and offer an opportunity to see the effects of storage choices.
Replicating sysindexes page values: The compatibility view sysindexes contains the columns root, first, and first_iam_pages which are not a part of sys.indexes. This DMF allows the ability to recreate those values.
What other uses for DBCC IND do you have? Is there anything you can accomplish with DBCC IND that isn’t possible with sys.dm_db_database_page_allocations? Or visa versa? Share them in the comments section, and I’ll update this list.
sys.dm_db_database_page_allocations COMPARISON
There are many similarities and differences between using the new DMF and using DBCC IND. For starters, the columns between the two overlap in a number of places (though the names of columns do differ), as shown in Table 1. As the table shows, there is 100% coverage in sys.dm_db_database_page_allocations for the columns returned by DBCC IND.
Table 1. Mapping Overlapping Columns
DMF Column
DBCC Column
Description
object_id
ObjectID
Object ID for the table or view
index_id
IndexID
ID for the index
partition_id
PartitionNumber
Partition number for the index
rowset_id
PartitionID
Partition ID for the index
allocation_unit_type_desc
iam_chain_type
Description of the allocation unit
allocated_page_iam_file_id
IAMFID
File ID for the index allocation map page associated to the page
allocated_page_iam_page_id
IAMPID
Page ID for the index allocation map page associated to the page
allocated_page_file_id
PageFID
File ID of the allocated page
allocated_page_page_id
PagePID
Page ID for the allocated page
page_type
PageType
Page type ID for the allocated page
page_level
IndexLevel
Level of the page in B-Tree index
next_page_file_id
NextPageFID
File ID for the next page
next_page_page_id
NextPagePID
Page ID for the next page
previous_page_file_id
PrevPageFID
File ID for the previous page
previous_page_page_id
PrevPagePID
Page ID for the previous page
Comparative queries between sys.dm_db_database_page_allocations and DBCC IND can be created using the mapping table. The only other differences are the values returned as NULL from sys.dm_db_database_page_allocations instead of 0 when there is no referencing page. Also, with the DMF, to return the same information as DBCC IND, only allocated pages are included. For the DMF, to return similar data, the DETAILED mode is required. Queries for these two statements are provided in Listing 1.
--Listing 1. Queries for sys.dm_db_database_page_allocations and DBCC IND
SELECT
allocated_page_file_id AS PageFID
,allocated_page_page_id AS PagePID
,allocated_page_iam_file_id AS IAMFID
,allocated_page_iam_page_id AS IAMPID
,object_id AS ObjectID
,index_id AS IndexID
,partition_id AS PartitionNumber
,rowset_id AS PartitionID
,allocation_unit_type_desc AS iam_chain_type
,page_type AS PageType
,page_level AS IndexLevel
,next_page_file_id AS NextPageFID
,next_page_page_id AS NextPagePID
,previous_page_file_id AS PrevPageFID
,previous_page_page_id AS PrevPagePID
FROM sys.dm_db_database_page_allocations(DB_ID(), OBJECT_ID('Sales.SalesOrderHeader'), 1, NULL, 'DETAILED')
WHERE is_allocated = 1;
GO
DBCC IND (0,'Sales.SalesOrderHeader',1)
Besides the columns that match, there are a number of additional columns in the DMF. These columns, defined in Table 2, provide metadata on pages and information on the extents they tie into. There is one set of columns that I haven’t figured out yet these are data_clone_id, clone_state, and clone_state_desc.
Table 2. New sys.dm_db_database_page_allocations Columns
DMF Column
Description
database_id
ID of the database
allocation_unit_id
ID of the allocation unit
allocation_unit_type
Type of allocation unit
data_clone_id
Unknown
clone_state
Unknown
clone_state_desc
Unknown
extent_file_id
File ID of the extent
extent_page_id
Page ID for the extent
is_allocated
Indicates whether a page is allocated
is_iam_page
Indicates whether a page is the index allocation page
is_mixed_page_allocation
Indicates whether a page is allocated
page_free_space_percent
Percentage of space free on the page
page_type_desc
Description of the page type
is_page_compressed
Indicates if the page is compressed
has_ghost_records
Indicates if the page has ghost records
Beyond the additional columns, there are a few other differences between the DBCC command the the DMF. The main one is that with the DMF, you have the ability to join the results from the output directly into other DMVs and DMFs, without the need for intermediate tables. Also, with the is_allocated flag, results can be included for those pages that have been assigned (via uniform extents) but haven’t been allocated with data. Lastly, through the additional metadata, you can easily determine which pages in an index are compressed versus non-compressed.
Conclusion
The addition of sys.dm_db_database_page_allocations to SQL Server is a welcomed new feature of SQL Server 2012. While it is still considered an undocumented feature, the same as DBCC IND, it has the potential to become much more useful that DBCC IND ever was. How do you plan to use the DMF in your environments?
For those that follow me on twitter (@StrateSQL), you’ll know that throughout the day I tweet out some links of things I find interesting. These tweets include “Reading” in the message and are items I’ve read over the past few days, usually after hours when sharing would be less than useful, and spaced out to avoid spamming. The content of the links usually pertain to SQL Server, technology, and career topics; which I think others would find useful.
Most Popular Link
The most popular item tweeted out last week had nothing to do with SQL Server, but it is an odd one worth noting…
Japanese Vodka Made from Fermented Giant Hornets | Oddity Central|Collecting Oddities bit.ly/YgTV3s <– crazy
Of course, no week would be complete without a few off-topic links. These have nothing to do with technology or your career, but they sure are entertaining and interesting (and a couple are pretty gross).
Got something you think I should read and share, leave a comment below. Also, if you want to see all of the links that were tweeted out last week? Then follow the links on my tumblr blog or subscribe to it’s RSS feed.
After lunch, I led the session “What Are You Waiting For?” at SQL Saturday Chicago #211. It was another well-received session and the attendees definitely learned a lot about the value in wait stat analysis. The only downside, was my snap decision to add an extended event demo to show the connection between looking at wait stats and connecting to the information that they can use to immediately start to mitigate the performance issues that are showing up. It did seem, though, that with the change that the light bulb went on for a few people and there were some converts in the crowd.
If you have questions on this session, please leave a comment below. For the presentation materials, you can download them (What Are You Waiting For (SQLSat211)) or if you just want to flip through the slides, you can do that below.
There are countless sources for information on SQL Server available today. There are books, webcasts, conferences, and blogs. With all of these sources, it is easy to forget that there are white papers available from Microsoft. It is important that we make an effort to read the white papers that are available.
