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://darkslategrey-bat-805937.hostingersite.com)
