I just started using a new DMV (one that’s actually an ‘F’ not a ‘V’, as in Function) that gives us more info about distribution statistics. It returns info about the last statistics update date (which is also available with a function STATS_DATE()). It also provides the number of rows sampled when the statistics were last updated. This is ...

Distribution statistics are one of the most important sources of information that the Query Optimizer uses to determine a good query plan. In this post, I’m not going to tell you everything about distribution statistics. I’m just going to show you a few tricks for getting access to the statistics. If you want a deeper understanding of what the ...

Data distribution statistics is one of the foundations of the cost-based query optimizer in all modern database engines including SQL Server. From SQL Server 2005 on, most of the information displayed by DBCC SHOW_STATISTICS is kept in a binary field accessible with the STATS_STREAM clause. Back in SQL Server 2000, it was possible to modify ...

Where is SYSDATE()? I've talked about this one before, but due to a recent conversation about building a datetime value from separate date/time columns or variables, it may be worth bringing up again. When SQL Server 2008 introduced several new date/time types, they also added new built-in functions, like SYSDATETIME() and SYSDATETIMEOFFSET(). ...

In this post, I show you how to determine exactly which statistics objects were used by the query optimizer to produce an execution plan. Trace Flags We will need three undocumented trace flags.  The first one (3604) is well-known – it redirects trace output to the client so it appears in the SSMS messages tab. The second trace flag is ...

Last week, Ola published a very useful update to his Backup, Integrity Check and Index Optimization scripts: the solution now supports updating statistics.  There are several options, such as only updating when the data has been modified and using the RESAMPLE and NORECOMPUTE options.  An example call: EXEC ...

Did you ever wanted to know which execution plans the Query Optimizer would generate for your queries should your tables have millions of records? You can actually generate those plans by using the undocumented ROWCOUNT and PAGECOUNT options of the UPDATE STATISTICS statement. These options can be used on small or empty tables and can be ...

In previous posts (http://sqlblog.com/blogs/elisabeth_redei/archive/2009/08/10/lies-damned-lies-and-statistics-part-i.aspx  and http://sqlblog.com/blogs/elisabeth_redei/archive/2009/03/01/lies-damned-lies-and-statistics-part-ii.aspx)I have talked about the performance problems that may surface because of low statistics sample rate on large ...

For want of a nail the shoe was lost. For want of a shoe the horse was lost. For want of a horse the rider was lost. For want of a rider the battle was lost. For want of a battle the kingdom was lost. And all for the want of a horseshoe nail.   I'm now in the middle of severity A case that is best depicted by this ...

One of the questions I was asked recently while speaking at user groups, was regarding the order that jobs like rebuilding indexes or updating statistics should be performed as part of the database maintenance activities. Then I started writing this post about this topic on the weekend but was interrupted several times, including one of them to ...