<\/p>\n

   8:<\/span> ON<\/span> o.[object_id] = i.[object_id]<\/pre>\n
<\/p>\n
   9:<\/span> INNER<\/span> JOIN<\/span> sys.stats AS<\/span> s WITH<\/span> (NOLOCK)<\/pre>\n
<\/p>\n
  10:<\/span> ON<\/span> i.[object_id] = s.[object_id] <\/pre>\n
<\/p>\n
  11:<\/span> AND<\/span> i.index_id = s.stats_id<\/pre>\n
<\/p>\n
  12:<\/span> INNER<\/span> JOIN<\/span> sys.dm_db_partition_stats AS<\/span> st WITH<\/span> (NOLOCK)<\/pre>\n
<\/p>\n
  13:<\/span> ON<\/span> o.[object_id] = st.[object_id]<\/pre>\n
<\/p>\n
  14:<\/span> AND<\/span> i.[index_id] = st.[index_id]<\/pre>\n
<\/p>\n
  15:<\/span> WHERE<\/span> o.[type] IN<\/span> ('U'<\/span>, 'V'<\/span>)<\/pre>\n
<\/p>\n
  16:<\/span> AND<\/span> st.row_count > 0<\/pre>\n
<\/p>\n
  17:<\/span> ORDER<\/span> BY<\/span> STATS_DATE(i.[object_id], i.index_id) DESC<\/span> OPTION<\/span> (RECOMPILE);  <\/pre>\n
<\/p>\n
  18:<\/span>  <\/pre>\n
<\/p>\n
  19:<\/span> -- Helps discover possible problems with out-of-date statistics<\/span><\/pre>\n
<\/p>\n
  20:<\/span> -- Also gives you an idea which indexes are<\/span> the most active<\/pre>\n
<\/div>\n<\/div>\n
Figure 2: Query #60 Statistics Update<\/strong><\/p>\n
This query shows you a number of relevant properties about the index-associated statistics in your current database, ordered by the last time that statistics were updated. This can help you determine whether you might have a problem with out of date statistics. My general guidance about statistics is that you should use the default database properties of auto create and auto update for statistics, plus I think you should also use the auto update statistics asynchronously database property, along with global trace flag 2371<\/a>. In some situations, you may also want\/need to do additional statistics maintenance on highly volatile tables with SQL Server Agent jobs.<\/p>\n","protected":false},"excerpt":{"rendered":"
For Day 26 of this series, we start out with Query #59, which is Table Properties. This query retrieves information from the sys.tables object catalog view, and the sys.partitions object catalog view about various table properties in the current database. Query #59 is shown in Figure 1. 1: — Get some key table properties (Query […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[273],"tags":[272],"class_list":["post-1054","post","type-post","status-publish","format-standard","hentry","category-dmv-diagnostic-queries-detailed","tag-dmv-diagnostic-queries-detailed"],"yoast_head":"\n