{"id":1170,"date":"2025-04-19T09:57:14","date_gmt":"2025-04-19T09:57:14","guid":{"rendered":"https:\/\/humtechno.com\/master\/?p=1170"},"modified":"2025-04-19T09:57:53","modified_gmt":"2025-04-19T09:57:53","slug":"sql-server-database-stats-gather","status":"publish","type":"post","link":"https:\/\/humtechno.com\/master\/2025\/04\/19\/sql-server-database-stats-gather\/","title":{"rendered":"SQL Server Database Stats Gather"},"content":{"rendered":"\n\n\n\n<p>HST Techno<\/p>\n\n<p>Kamran Hussain<\/p>\n\n<p>Download Complete <a href=\"https:\/\/humtechno.com\/master\/wp-content\/uploads\/2025\/04\/SQL Server Stats Gather.pdf\">Steps<\/a><\/p>\n\n<figure class=\"wp-block-embed is-type-video is-provider-youtube wp-block-embed-youtube wp-embed-aspect-16-9 wp-has-aspect-ratio\"><div class=\"wp-block-embed__wrapper\">\n<div class=\"nv-iframe-embed\"><iframe loading=\"lazy\" title=\"SQL Server Stats Gathering #sqlserver #hubsubtechno #statsgather\" width=\"1200\" height=\"675\" src=\"https:\/\/www.youtube.com\/embed\/E6IBCTP5KiY?feature=oembed\" frameborder=\"0\" allow=\"accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture; web-share\" referrerpolicy=\"strict-origin-when-cross-origin\" allowfullscreen><\/iframe><\/div>\n<\/div><\/figure>\n\n<p>Statistics gathering in SQL Server means collecting information about the data in tables\u2014like how many rows there are, what kind of values appear in each column, and how often they show up. This helps the SQL Server engine choose the most efficient way to run queries. Stats can be updated automatically when data changes a lot or manually using commands like <code>UPDATE STATISTICS<\/code> or <code>sp_updatestats<\/code>. Keeping statistics up to date ensures better performance and faster query results.<\/p>\n\n<p><strong>When does it happen?<\/strong><\/p>\n\n<ul class=\"wp-block-list\">\n<li><strong>Automatically<\/strong> when a lot of data changes<\/li>\n\n\n\n<li><strong>Manually<\/strong> by running commands<\/li>\n<\/ul>\n\n<p><strong><u>How to Check<\/u><\/strong><\/p>\n\n<p>select s.name as statsname,stats_date(o.object_id,s.stats_id) as LastUpdated,o.name as tablename,s.auto_created,s.user_created from<\/p>\n\n<p>sys.stats s join sys.objects o ON s.object_id=o.object_id where o.type=&#8217;U&#8217; Order by LastUpdated;<\/p>\n\n<p>select object_name(sp.object_id) as TableName,s.name as StatsName<\/p>\n\n<p>,sp.last_updated,sp.rows,sp.rows_sampled,sp.modification_counter<\/p>\n\n<p>from<\/p>\n\n<p>sys.stats s<\/p>\n\n<p>cross apply<\/p>\n\n<p>sys.dm_db_stats_properties(s.object_id,s.stats_id)<\/p>\n\n<p>as sp<\/p>\n\n<p>where OBJECTPROPERTY(s.object_id,&#8217;IsUserTable&#8217;)=1<\/p>\n\n<p>order by<\/p>\n\n<p>sp.modification_counter desc;<\/p>\n\n<p>SELECT<\/p>\n\n<p>&nbsp;&nbsp;&nbsp; t.name AS TableName,<\/p>\n\n<p>&nbsp;&nbsp;&nbsp; s.name AS StatsName,<\/p>\n\n<p>&nbsp;&nbsp;&nbsp; STATS_DATE(s.object_id, s.stats_id) AS LastUpdated<\/p>\n\n<p>FROM sys.stats s<\/p>\n\n<p>JOIN sys.tables t ON s.object_id = t.object_id<\/p>\n\n<p>WHERE t.is_ms_shipped = 0<\/p>\n\n<p>ORDER BY LastUpdated DESC;<\/p>\n\n<p>SELECT<\/p>\n\n<p>&nbsp;&nbsp;&nbsp; name AS DatabaseName,<\/p>\n\n<p>&nbsp;&nbsp;&nbsp; is_auto_create_stats_on,<\/p>\n\n<p>&nbsp;&nbsp;&nbsp; is_auto_update_stats_on,<\/p>\n\n<p>&nbsp;&nbsp;&nbsp; is_auto_update_stats_async_on<\/p>\n\n<p>FROM sys.databases<\/p>\n\n<p>WHERE name = &#8216;DB1&#8217;;<\/p>\n\n<p><strong><u>When the automatic create statistics option,&nbsp;<\/u><\/strong><a href=\"https:\/\/learn.microsoft.com\/en-us\/sql\/t-sql\/statements\/alter-database-transact-sql-set-options?view=sql-server-ver16#auto_create_statistics\"><strong>AUTO_CREATE_STATISTICS<\/strong><\/a><strong><u>&nbsp;is ON<\/u><\/strong><\/p>\n\n<p>SELECT OBJECT_NAME(s.object_id) AS object_name,<\/p>\n\n<p>&nbsp;&nbsp;&nbsp; COL_NAME(sc.object_id, sc.column_id) AS column_name,<\/p>\n\n<p>&nbsp;&nbsp;&nbsp; s.name AS statistics_name<\/p>\n\n<p>FROM sys.stats AS s<\/p>\n\n<p>&nbsp;&nbsp;&nbsp; INNER JOIN sys.stats_columns AS sc<\/p>\n\n<p>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ON s.stats_id = sc.stats_id<\/p>\n\n<p>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; AND s.object_id = sc.object_id<\/p>\n\n<p>WHERE s.name LIKE &#8216;_WA%&#8217;<\/p>\n\n<p>ORDER BY s.name;<\/p>\n\n<p><strong><u>Scripts for Execute on All Tables of Database<\/u><\/strong><\/p>\n\n<p>DECLARE @table NVARCHAR(255);<\/p>\n\n<p>DECLARE @sql NVARCHAR(MAX);<\/p>\n\n<p>DECLARE table_cursor CURSOR FOR<\/p>\n\n<p>SELECT QUOTENAME(SCHEMA_NAME(schema_id)) + &#8216;.&#8217; + QUOTENAME(name)<\/p>\n\n<p>FROM sys.tables<\/p>\n\n<p>WHERE is_ms_shipped = 0;<\/p>\n\n<p>OPEN table_cursor;<\/p>\n\n<p>FETCH NEXT FROM table_cursor INTO @table;<\/p>\n\n<p>WHILE @@FETCH_STATUS = 0<\/p>\n\n<p>BEGIN<\/p>\n\n<p>&nbsp;&nbsp;&nbsp; SET @sql = &#8216;UPDATE STATISTICS &#8216; + @table + &#8216; WITH FULLSCAN;&#8217;;<\/p>\n\n<p>&nbsp;&nbsp;&nbsp; PRINT @sql; &#8212; Optional: see the statements<\/p>\n\n<p>&nbsp;&nbsp;&nbsp; EXEC sp_executesql @sql;<\/p>\n\n<p>&nbsp;&nbsp;&nbsp; FETCH NEXT FROM table_cursor INTO @table;<\/p>\n\n<p>END<\/p>\n\n<p>CLOSE table_cursor;<\/p>\n\n<p>DEALLOCATE table_cursor;<\/p>\n\n<p><strong><u>Scripts for Execute on All Tables of Database<\/u><\/strong><\/p>\n\n<p>&#8212; Update all stats with FULLSCAN on a specific table<\/p>\n\n<p>DECLARE @sql NVARCHAR(MAX) = &#8221;<\/p>\n\n<p>SELECT @sql += &#8216;UPDATE STATISTICS &#8216; + QUOTENAME(OBJECT_SCHEMA_NAME(object_id)) + &#8216;.&#8217; + QUOTENAME(OBJECT_NAME(object_id)) +<\/p>\n\n<p>&nbsp;&nbsp;&nbsp; &#8216; WITH FULLSCAN;&#8217; + CHAR(13)<\/p>\n\n<p>FROM sys.stats<\/p>\n\n<p>WHERE OBJECT_NAME(object_id) LIKE &#8216;TestTable_%&#8217;<\/p>\n\n<p>EXEC sp_executesql @sql;<\/p>\n\n<p><strong><u>Enable Auto Stats on Database<\/u><\/strong><\/p>\n\n<p>ALTER DATABASE DB1 SET AUTO_CREATE_STATISTICS ON; (Create Stats for Single Column if it not Present)<\/p>\n\n<p><strong><u>Only touches <strong>modified statistics<\/strong><\/u><\/strong>.<\/p>\n\n<p>&nbsp;EXEC sp_updatestats; ((500+20% of Rows changes))<\/p>\n\n<p><strong><u>Update Stats of Specific Tables<\/u><\/strong><\/p>\n\n<p>UPDATE STATISTICS dbo.TestTable_1;<\/p>\n\n<p>UPDATE STATISTICS dbo.TestTable_1 WITH FULLSCAN;&nbsp; &#8212; uses all rows<\/p>\n\n<p>UPDATE STATISTICS dbo.TestTable_1 WITH SAMPLE 50 PERCENT; &#8212; uses 50% sample<\/p>\n\n\n","protected":false},"excerpt":{"rendered":"","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_siteseo_robots_primary_cat":"","pagelayer_contact_templates":[],"_pagelayer_content":"","neve_meta_sidebar":"","neve_meta_container":"","neve_meta_enable_content_width":"","neve_meta_content_width":0,"neve_meta_title_alignment":"","neve_meta_author_avatar":"","neve_post_elements_order":"","neve_meta_disable_header":"","neve_meta_disable_footer":"","neve_meta_disable_title":"","_themeisle_gutenberg_block_has_review":false,"footnotes":""},"categories":[17],"tags":[25],"class_list":["post-1170","post","type-post","status-publish","format-standard","hentry","category-mssql","tag-sql-server-stats-gather"],"_links":{"self":[{"href":"https:\/\/humtechno.com\/master\/wp-json\/wp\/v2\/posts\/1170","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/humtechno.com\/master\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/humtechno.com\/master\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/humtechno.com\/master\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/humtechno.com\/master\/wp-json\/wp\/v2\/comments?post=1170"}],"version-history":[{"count":1,"href":"https:\/\/humtechno.com\/master\/wp-json\/wp\/v2\/posts\/1170\/revisions"}],"predecessor-version":[{"id":1175,"href":"https:\/\/humtechno.com\/master\/wp-json\/wp\/v2\/posts\/1170\/revisions\/1175"}],"wp:attachment":[{"href":"https:\/\/humtechno.com\/master\/wp-json\/wp\/v2\/media?parent=1170"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/humtechno.com\/master\/wp-json\/wp\/v2\/categories?post=1170"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/humtechno.com\/master\/wp-json\/wp\/v2\/tags?post=1170"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}