{"id":585,"date":"2021-06-15T11:30:11","date_gmt":"2021-06-15T10:30:11","guid":{"rendered":"https:\/\/sqldoubleg.live-website.com\/?p=585"},"modified":"2021-06-15T11:30:13","modified_gmt":"2021-06-15T10:30:13","slug":"how-to-update-sql-server-stats-the-right-way","status":"publish","type":"post","link":"https:\/\/www.sqldoubleg.com\/es\/2021\/06\/15\/how-to-update-sql-server-stats-the-right-way\/","title":{"rendered":"C\u00f3mo Actualizar Estad\u00edsticas en SQL Server de la Manera Correcta"},"content":{"rendered":"\n

<\/p>\n\n\n\n 

Here I am, trying to get back my habit and write about something useful I was working on the other day.<\/p>\n\n\n\n

If you ask me which SQL Server feature I love the most these days I’d say, without hesitation, that is the Query Store.<\/p>\n\n\n\n

Query Store has been around now for a number of years already, but I’m surprised that still lots of people haven’t used it to troubleshoot performance issues, to asses the general health of their systems or to capture a baseline for future reference.<\/p>\n\n\n\n

Recently I was looking at some data collected by the Query Store and I found something interesting I want to share.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Background<\/h2>\n\n\n\n

A good database maintenance is mandatory if you want to have peace of mind, backups, integrity checks, statistics update and index maintenance.<\/p>\n\n\n\n

Each of these steps will help you in a different way and should be altogether the foundation of a healthy environment for any DBA, but sometimes we might be overzealous and there may be some negative impact as side effect in our servers. <\/p>\n\n\n\n

The simple reason is just because the available resources are finite and we might we wasting them by doing something that is a bit pointless.<\/p>\n\n\n\n

Statistics<\/h2>\n\n\n\n

One if the most important (if not the most) when it comes to help SQL Server performance, it is Stats Updates<\/strong> because that is the key information the Query Optimizer will use to produce the best <\/s>good enough plans for our data layout. <\/p>\n\n\n\n

The problem comes when some of these stats are really useless, let me explain.<\/p>\n\n\n\n

When AUTO_CREATE_STATISTICS <\/a>is ON (best practice, do not turn it off), the Query Optimizer creates statistics on individual columns in the query predicate, as necessary, to improve cardinality estimates for the query plan<\/em>. <\/p>\n\n\n\n

That means that sometimes, stats might be created on columns that are not a very good fit due to the histogram nature or the way we access specific columns.<\/p>\n\n\n\n

Scenario<\/h2>\n\n\n\n

For this demo I will use a copy of the StackOverflow database I restored in my localhost.<\/p>\n\n\n\n

If we look at the stats created on the table [dbo].[Posts] we can see there is a number of them. This query will show them along with their data types, which is important.<\/p>\n\n\n\n

USE StackOverflow\n\nSELECT OBJECT_SCHEMA_NAME(c.object_id) + '.' + OBJECT_NAME(c.object_id) AS [object_name]\n\t\t, s.name\n\t\t, c.name\n\t\t, t.name\n\t\t, c.max_length\n\t\t--, *\n\tFROM sys.columns AS c\n\t\tINNER JOIN sys.types AS t\n\t\t\tON t.user_type_id = c.user_type_id\n\t\tLEFT JOIN sys.stats_columns AS sc\n\t\t\tON sc.object_id = c.object_id\n\t\t\t\tAND sc.column_id = c.column_id\n\t\tLEFT JOIN sys.stats AS s\n\t\t\tON sc.object_id = s.object_id\n\t\t\t\tAND sc.stats_id = s.stats_id\n\tWHERE c.object_id = OBJECT_ID('dbo.Posts')\n\tORDER BY c.column_id\n<\/pre><\/p>\n\n\n\n
\"\"<\/a><\/figure><\/div>\n\n\n\n
The column [Body] is defined as nvarchar(max) and currently there are no [column] statistics created on it.<\/p>\n\n\n\n
Now, let’s say someone decides to run this query:<\/p>\n\n\n\n
\nSELECT TOP(10) * \nFROM dbo.Posts\nWHERE Body LIKE '%sql server%'\n<\/pre><\/p>\n\n\n\n
If we have another look, we can see now there is a new stats on the column [Body], and by looking at the name, we can deduce it’s auto created.<\/p>\n\n\n\n
\"\"<\/a><\/figure><\/div>\n\n\n\n
As mentioned earlier, stats play a key role during the execution plan generation, and inaccurate stats lead to all sort of performance issues.<\/p>\n\n\n\n
The problem here is how stats (or the histogram in this case) is stored and the information SQL Server can extract to help to generate the most optimal plan.<\/p>\n\n\n\n
Let’s have a look at the histogram for this stats.<\/p>\n\n\n\n
\nSELECT * \nFROM sys.dm_db_stats_histogram (OBJECT_ID('dbo.Posts'),11)\n<\/pre><\/p>\n\n\n\n
\"\"<\/a><\/figure>\n\n\n\n
The values stored in the column [range_high_key] are actual values from the table’s column [Body] and since it’s great when we speak about integers, dates or short strings where we do equality, it’s not that great when we speak about others, like long strings or blobs for instance.<\/p>\n\n\n\n
The Problem<\/h2>\n\n\n\n
For now, everything described might not be such a horrible thing, it’s clear that SQL Server will not take full advantage of the stats on the column [Body] if the queries we are running use wildcards (specially leading), but why so much fuss? Well, now it’s when things start making sense (or not).<\/p>\n\n\n\n
Running stats maintenance on this kind of columns every night can become really expensive and this is what I’ve found more than once when using the Query Store to look for queries that have a high number of reads.<\/p>\n\n\n\n
\"\"<\/a><\/figure><\/div>\n\n\n\n
In the picture you can see how one single UPDATE STATS was incurring in ~60GB almost every night, which can be expensive.<\/p>\n\n\n\n
The SQLG Hack<\/h2>\n\n\n\n
Even if you have used Ola Hallengren’s solution<\/a> for a while, you might not be aware of every possible configuration because the default usually works just fine.<\/p>\n\n\n\n
But here I am to tell you a little secret, there is a parameter called @Indexes<\/strong><\/em> to control which indexes you want to include or exclude from being maintained in the stored procedure [dbo].[IndexOptimize].<\/p>\n\n\n\n
\"\"<\/a><\/figure><\/div>\n\n\n\n
But although there is no equivalent for statistics and the documentation does not state it, you can also use it to exclude statistics<\/em><\/strong>, so we can programmatically look for those stats on the columns you think it doesn’t makes sense to update stats, because of the little return comparing the cost of doing it.<\/p>\n\n\n\n
Once that is said, we can be creative and choose for instance those stats whose data types might not make be a good fit, like XML, [n]varchar(max), old style blobs (text, ntext, image), or [var]binary.<\/p>\n\n\n\n
This is how I do it.<\/p>\n\n\n\n
\nSET QUOTED_IDENTIFIER ON\nGO\nSET ANSI_NULLS ON\nGO\n--=============================================\n-- Copyright (C) 2021 Raul Gonzalez @SQLDoubleG.\n-- All rights reserved.\n--   \n-- You may alter this code for your own *non-commercial* purposes. You may\n-- republish altered code as long as you give due credit.\n--   \n-- THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF \n-- ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED \n-- TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND\/OR FITNESS FOR A\n-- PARTICULAR PURPOSE.\n--\n-- =============================================\n-- Author:\t\tRaul Gonzalez, @SQLDoubleG (RAG)\n-- Create date: 01\/06\/2021\n-- Description:\tThis script run IndexOptimize to Update Stats\nUSE master;\n\nDECLARE @indexes nvarchar(MAX);\nDECLARE @dbname sysname;\nDECLARE @sql nvarchar(MAX);\n\nDECLARE dbs CURSOR LOCAL STATIC FORWARD_ONLY READ_ONLY FOR\nSELECT name\nFROM sys.databases\nWHERE database_id &amp;gt; 4\nAND name = 'StackOverflow';\n\nDROP TABLE IF EXISTS #t;\nCREATE TABLE #t (stats_name nvarchar(500) NOT NULL);\n\nSET @sql = 'USE [?]\n\nSELECT DB_NAME() + ''.'' + QUOTENAME (OBJECT_SCHEMA_NAME (s.object_id)) + ''.'' + QUOTENAME (OBJECT_NAME (s.object_id)) + ''.'' + QUOTENAME (s.name)\n    -- , c.name\n    -- , t.name\n    -- , c.max_length\nFROM sys.stats AS s\n        INNER JOIN sys.stats_columns AS sc\n            ON sc.object_id = s.object_id\n               AND sc.stats_id = s.stats_id\n        INNER JOIN sys.columns AS c\n            ON c.object_id = s.object_id\n               AND c.column_id = sc.column_id\n        INNER JOIN sys.types AS t\n            ON t.user_type_id = c.user_type_id\n    WHERE OBJECTPROPERTY (s.object_id, ''IsMSShipped'') = 0\n        AND\n        (\n            t.name IN ( N''image'', N''text'', N''sql_variant'', N''ntext'', N''xml'', N''hierarchyid'', N''geometry'',\n                        N''geography'', N''varbinary'', N''binary''\n                    )\n            OR c.max_length = -1\n        )';\n\nOPEN dbs;\nFETCH NEXT FROM dbs INTO @dbname;\nWHILE @@FETCH_STATUS = 0 BEGIN\nSET @sql = REPLACE(@sql, '?', @dbname);\n\nINSERT INTO #t (stats_name)\nEXECUTE sys.sp_executesql @stmt = @sql;\n\nFETCH NEXT FROM dbs INTO @dbname;\nEND;\nCLOSE dbs;\nDEALLOCATE dbs;\n\nSET @indexes =\n(\n    SELECT ',-' + stats_name\n    FROM #t\n    ORDER BY stats_name\n    FOR XML PATH ('')\n);\n\nSET @indexes = N'ALL_INDEXES' + @indexes;\n\n-- SELECT @indexes\n\n-- UPDATE Stats ONLY\nEXECUTE dbo.IndexOptimize\n    @Databases = 'StackOverflow',\n    @Indexes = @indexes,\n    @FragmentationLow = NULL,\n    @FragmentationMedium = NULL,\n    @FragmentationHigh = NULL,\n    @UpdateStatistics = 'ALL',\n    @OnlyModifiedStatistics = 'N',\n    @LogToTable = 'Y',\n    @Execute = 'N';\n<\/pre><\/p>\n\n\n\n
If we execute the above and look at the output, there are a couple of interesting things. <\/p>\n\n\n\n
First, this message gives me the wrong impression that the syntax is not correct or the parameter will not work on stats (as I told you already)<\/p>\n\n\n\n
The following indexes in the @Indexes parameter do not exist: [StackOverflow][dbo].[Posts].[_WA_Sys_00000004_2B3F6F97].<\/span><\/p><\/blockquote>\n\n\n\n
\"\"<\/a><\/figure>\n\n\n\n
Second and most important is that it actually worked absolutely fine! If we scroll down to the table [dbo].[Posts], we can see the update stats commands for all stats, but not for the one on the column [Body] (called _WA_Sys_00000004_2B3F6F97<\/em>), so our intention, which is to exclude it, was achieved.<\/p>\n\n\n\n
\"\"<\/a><\/figure>\n\n\n\n
The script is ready to go through all databases with a simple change, but I let you play with it and change it if you like.<\/p>\n\n\n\n
Conclusion<\/h2>\n\n\n\n
I believe Ola’s solution is a pretty good tool and saves the bacon of lots of us which otherwise we will be re-inventing the wheel over and over.<\/p>\n\n\n\n
Finding these little gems and with a bit of scripting, it becomes even more flexible, which is even better.<\/p>\n\n\n\n
I hope you enjoyed the reading and please let me know if you found it useful in the comments.<\/p>\n\n\n\n
Cheers!<\/p>\n","protected":false},"excerpt":{"rendered":"
  Here I am, trying to get back my habit and write about something useful I was working on the other day. If you ask me which SQL Server feature I love…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4,14,19],"tags":[],"_links":{"self":[{"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/posts\/585"}],"collection":[{"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/comments?post=585"}],"version-history":[{"count":0,"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/posts\/585\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/media?parent=585"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/categories?post=585"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/tags?post=585"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}