PASS Data Community Summit logo

PASS Data Community Summit 2023

Get all the latest announcements direct to your inbox

Looking for outdated statistics

Outdated statistics are a major cause of bad query plans in SQL Server’s.

Even when we have ‘Auto Update Statistics’ on, this doesn’t solve the problem, because the formula for the number of modifications that triggers the ‘Auto Update Statistics’ is 20% + 500. If your table has 3 million rows you will need to wait until 1.8 million + 500 modifications.

Configuring trace flag 2371, created in service pack 1 of SQL Server 2008 R2 helps a lot, so the formula became dynamic. When we achieve 1 million rows, for example, the formula will be 10% + 500 and so on.

But this doesn’t solve the problem, between zero and 100 thousands modifications our server can face a lot of wrong query plans, so we still need to check the statistics and manually update them.

In SQL Server 2012 the DMF sys.dm_db_stats_properties was created. The best about this function is that it allows to us not only retrieve the last update date for each stats, but also allow us to retrieve the number of modifications already happened since the last update, so we can identify if the statistics is updated or is needing to be manually updated.

Here is a query to achieve this :

This is one more excellent query to become a function in our DBA toolset, below you can see how the function will be:

After creating this function we can check the statistics for a table with a simple query:

In the result we can find how many modifications already happened since last update, identifying if the statistics needs to be manually updated