This is a follow up of my earlier blog post How to Drop All the User Created Statistics by SQL Server? – Interview Question of the Week #158. During the recent consulting engagement, Comprehensive Database Performance Health Check, I ended up a very interesting situation where I had to drop all the auto created statistics.
Brief Story
When we talk about SQL Server performance tuning, the best possible situation is when you do preventive maintenance of your system to avoid any potential performance issues. However, nearly 90% of my client only reaches out to me the reactive way or when they face performance problems with their SQL Server. However, there are few organizations do reach out to me before the problem shows up.
One of the largest fintech organizations of Europe/Asia recently launched its brand new application in the month of February. In the month of January, they did launch their system for their selected clients and also did elaborative load testing. Once they completed all of their tasks, they reached out to me to help them to check their system in terms of performance before they launch that openly to their over 60,000+ consumers.
During the Comprehensive Database Performance Health Check, I found that they have lots of statistics already created by users and the system is still not put out in public. The way the statistics work is that when SQL Server runs any query and it needs the statistics for them, it will automatically create them if the required statistics do not exist.
Now the system which I was looking at was yet not released to the end-users and had lots of statistics (way way way more than I usually find in the well run system). Upon checking with the DBA, I realized that all the statistics were created by load testing script which was auto-generated and not necessarily represents the actual business logic. Essentially, all the statistics which already exist were of no good.
Drop All Auto Created Statistics
After carefully evaluating the system, we decided that we will drop all the auto-created statistics, so when the system goes live, it can build from the scratch all the necessary statistics based on the query patterns. I have previously written a query over here which drops all the user-created statistics, we had modified it to drop all the auto-created statistics.
SELECT DISTINCT 'DROP STATISTICS ' + QUOTENAME(SCHEMA_NAME(ob.Schema_id)) + '.' + QUOTENAME(OBJECT_NAME(s.object_id)) + '.' + QUOTENAME(s.name) DropStatisticsStatement FROM sys.stats s INNER JOIN sys.Objects ob ON ob.Object_id = s.object_id WHERE SCHEMA_NAME(ob.Schema_id) <> 'sys' AND Auto_Created = 1
The above script will generate the drop script which you can run for your server and drop all the auto created statistics. Remember, if you are not sure if this step will help you or not, you should reach out to any SQL Server Performance Tuning Expert before you execute the drop statistics query.
Reference: Pinal Dave (https://blog.sqlauthority.com)
5 Comments. Leave new
–Realy good.
–Here the rest to delete the statistics
DECLARE @sql nvarchar(255)
DECLARE cur CURSOR FOR
(
SELECT DISTINCT ‘DROP STATISTICS ‘
+ QUOTENAME(SCHEMA_NAME(ob.Schema_id)) + ‘.’
+ QUOTENAME(OBJECT_NAME(s.object_id)) + ‘.’ +
QUOTENAME(s.name) DropStatisticsStatement
FROM sys.stats s
INNER JOIN sys.Objects ob ON ob.Object_id = s.object_id
WHERE SCHEMA_NAME(ob.Schema_id) ‘sys’
AND Auto_Created = 1
)
OPEN cur
FETCH NEXT FROM cur INTO @sql
WHILE @@FETCH_STATUS = 0
BEGIN
print @sql
–EXECUTE sp_executeSQL @sql; –Drop it
FETCH NEXT FROM cur INTO @sql;
END
CLOSE cur;
DEALLOCATE cur;
This will give you the script to clear the entire instance.
BEGIN
IF OBJECT_ID(‘tempdb..#tempcommands’) IS NOT NULL
DROP TABLE #tempcommands;
CREATE TABLE #tempcommands
(
xCommand varchar(500)
);
EXEC sp_MSForEachDb ‘
IF ”?” NOT IN (”master”, ”model”, ”msdb”, ”tempdb”)
IF ((SELECT DATABASEPROPERTYEX(”?”, ”Updateability”))=”read_write”)
BEGIN
INSERT INTO #tempcommands
SELECT
— so.name,
— ss.name,
”USE [?];DROP STATISTICS [”+ ssc.name+”]”+”.[”+ so.name +”]”+ ”.[”+ ss.name+ ”];”
FROM ?.sys.stats AS ss
INNER JOIN ?.sys.objects AS so
ON ss.[object_id] = so.[object_id]
INNER JOIN ?.sys.schemas AS ssc
ON so.schema_id = ssc.schema_id
WHERE ss.auto_created = 1 AND so.is_ms_shipped = 0
order by so.name,ss.name;
END’;
Select * from #tempcommands;
END;
–This will generate the queries to clear the entire instance
BEGIN
IF OBJECT_ID(‘tempdb..#tempcommands’) IS NOT NULL
DROP TABLE #tempcommands;
CREATE TABLE #tempcommands
(
xCommand varchar(500)
);
EXEC sp_MSForEachDb ‘
IF ”?” NOT IN (”master”, ”model”, ”msdb”, ”tempdb”)
IF ((SELECT DATABASEPROPERTYEX(”?”, ”Updateability”))=”read_write”)
BEGIN
INSERT INTO #tempcommands
SELECT
— so.name,
— ss.name,
”USE [?];DROP STATISTICS [”+ ssc.name+”]”+”.[”+ so.name +”]”+ ”.[”+ ss.name+ ”];”
FROM ?.sys.stats AS ss
INNER JOIN ?.sys.objects AS so
ON ss.[object_id] = so.[object_id]
INNER JOIN ?.sys.schemas AS ssc
ON so.schema_id = ssc.schema_id
WHERE ss.auto_created = 1 AND so.is_ms_shipped = 0
order by so.name,ss.name;
END’;
Select * from #tempcommands;
END;
How old stats affect for poor performance? Can I get elaborated answer or any link will be appreciated
I have a 3 TB Production database which is taking approx. 8hours maintenance(Both Index and UpdateStats together) which is causing large transaction log backup files and causing latency in Logshpping Secondary server (DR) .
Found there are 6000 Auto created Statistics in the db, around 2000 autoStats are being updated through weekend maintenance. is it okay to drop all AutoCreated Stats at once? will this make the sql server slow?