Maintaining your larger databases better Part 3 – Recommended SQL Server DMV’s

Reading Time: 5 minutes

In this post I cover personally recommended SQL Server DMV’s, part 3 of a series about maintaining your larger databases better if you use SQL Server.

I’ve done this post about personally recommended DMV’s because they can help you maintain your larger databases better.

If you’ve yet to read the other parts of this series, then you can follow the links below:

• Maintaining your larger databases better Part 1
• Part 2 – Indexes
• Part 4 – Recommended FREE tools for the job

To clarify, my recommended SQL Server DMV’s below are ones that I have found the most useful over the years. In reality, there are lots of others in SQL Server that may prove useful for you.

However, if you are going to do anything complex with these DMV’s, I do recommend you test it in a non-production environment first. Now that has being said, lets dive into them.

sys.dm_db_index_physical_stats

If you’ve just inherited a large database with lots of big tables then the sys.dm_db_index_physical_stats DMV can be very useful.

In addition, if you have a set of tables that are large compared to the others then it’s worth querying this DMV against one of the smaller tables first. Instead of using it against the largest table straight away. Afterwards, you can then work your way up to the larger tables.

Why would you do this? Because, as I mentioned in a previous post here most reindexing solutions only do a limited scan of your indexes for performance reasons.

In fact, you can see this for yourself. You can check what commands are running on your SQL Server instance whilst your index maintenance is starting if you have a reasonably large database.

When you look at the results you will see that the index maintenance job uses this DMV. In addition, if you look at the end of the command you will see it uses the ‘Limited’ option as well.

Now, using the ‘Limited’ option to discover the fragmentation levels can have some interesting consequences for your indexes.

Due to the fact that once it has grown to a certain size the fragmentation percentage can stay low. So, unless the reindexing solution has been customized to cater for this, the larger indexes can end up being reorganized every time.

You can verify this by selecting one of the larger tables and running the below SQL. Of course, replace the TABLENAME value with the name of the table:

SELECT * FROM SYS.DM_DB_INDEX_PHYSICAL_STATS(DB_ID(),OBJECT_ID('TABLENAME'),NULL,NULL,'DETAILED');

It’s important to realize that the index_level column of the returned results shows the various levels of the index. Another key point is that the row that states index_level 0 is the leaf level of the index.

Fragmentation

Now, a common thing to happen over time is that the percentage of fragmentation within the leaf level of an index becomes low. Mostly due to the fact that the index has got bigger. However, the higher levels of the index can be shown as more fragmented.

If the avg_fragmentation_in_percentage number is high for some of these levels, and they are not the highest index levels shown, then you probably should rebuild the indexes concerned.

Page compression

In addition, another thing you might want to test is rebuilding the larger indexes using page compression. Which can reduce the size of them even further.

In fact, some recommend this as good practice for databases in the cloud as it can improve IOPS.

You can use the sp_estimate_data_compression_savings stored procedure to find out how much space you will potentially save by using compression. In addition, if you have SQL Server 2019 you can use this to estimate how much space you will save if you convert the indexes to Columnstore indexes.

sys.partitions

In fact, there is also a quick way to check if page compression is used. You can query the sys.partitions DMV and join the sys.indexes DMV to it using the index_id column.

sys.dm_db_column_store_row_group_physical_stats

As you can probably guess, the sys.dm_db_column_store_row_group_physical_stats DMV is an alternative version of the above DMV. It is specifically for Columnstore indexes. Microsoft have their own guidelines for dealing with Columnstore fragmentation, which you can view in detail here.

sys.dm_db_index_usage_stats

On the topic of Columnstore indexes, the sys.dm_db_index_usage_stats DMV can be useful for identifying rowstore indexes that can be converted to Columnstore indexes. Generally speaking, tables that are used for Datawarehouse loads are good candidates for this. However, sometimes you want some certainty.

For instance, you can run the below SQL after the instance has been running for a reasonable amount of time (say a few weeks, including over a month end).

SELECT * FROM SYS.DM_DB_INDEX_USAGE_STATS WHERE DATABASE_ID=DB_ID() AND OBJECT_ID = OBJECT_ID('TABLENAME');

If you find all the indexes for the table are being constantly scanned, then you than can conclude the table itself might be a good candidate to convert to a clustered Columnstore index.

As I said in a previous post, you can monitor the system_scans value and see if they only increase after maintenance tasks. If so, it means they are updating due to statistics updates and the index can be removed if nothing else is being done on the index.

sys.dm_db_stats_properties

Like a lot of other DBA’s, I once wrote customized scripts for updating statistics in SQL Server 2000. In the past, I used the rowmodctr value in the sysindexes table to help decide if statistics required updating.

However, as SQL versions changed, we were warned not to use this column in newer versions of SQL.

Now, you can use the above DMV instead to decide which statistics should be updated if writing your own customized scripts. For example, there are some good samples on how you can find the details about statistics here.

sys.foreign_keys

If you have foreign keys within your large database then you can use sys.foreign_keys to check them.

For instance, you can use this DMV to check the consistency of your data you got to make sure that the foreign keys are trustworthy.

Because you can also miss out on potential performance gains against queries on larger database tables when your foreign keys are not trustworthy. Due to the fact that SQL is able to avoid having to use indexes in certain situations.

You can check if foreign keys are trustworthy by checking that the is_not_trusted column is set to 0.

If the value is set to 1 it is untrusted. You can first try to resolve this by running the below syntax. Just replace TABLENAME with the correct table name and the FK_Name with the name of the foreign key:

ALTER TABLE TABLENAME WITH CHECK CHECK CONSTRAINT FK_NAME

Now, if that does not work you will have to check if the data in the tables is consistent. If not, you will have to resolve and then try the above again afterwards.

All the DMV’s relating to missing indexes

Last but by no means least in this post about recommended SQL Server DMV’s, are the DMV’s relating to missing indexes.

So far, I have listed the DMV’s above individually. However, in this section I have decided to mention all the DMV’s relating to missing indexes as a group.

Because when use these DMV’s together they can be very powerful in helping to identify missing indexes.

In reality, there are multiple versions of scripts online you can use to see which indexes are potentially missing. My favourite scripts have helped me make dramatic improvements on multiple systems over the years.

Glenn Berry has an excellent example of how to use these DMV’s in his diagnostic queries scripts which are available online. You can find out more about them in detail here.

Final word

I hope you enjoyed part 3 in this series which covers recommended SQL Server DMV’s.

Of course, there are a whole load of other useful DMV’s you can use with SQL Server.

Of course, you are more than welcome to comment on this post below.