In the previous articles, we have seen basic about SQL Statistics and Automated configuration of Statistics.
In this post, I am going share script to create user defined Statistics in SQL Server.
The query optimizer already generates the necessary statistics for a high-quality query plan and in a few cases, you need to create additional statistics for better performance.
You can also use the Database Engine Tuning Advisor which suggests you to create additional statistics or not.
For the Database performance tuning and optimization, User defined Database Statistics are playing one of the important Role.
For example, we can create Filtered Statistics on a column like Filtered Index.
We can also create user defined normal Statistics but Filtered Statistics is a one kind of special tips for performance optimization.
Now take one example that, Table has 1000000 records and Statistics of this table is also updated and Query Optimizer will choose best suitable execution plane base on 1000000 records.
But query contains additional WHERE condition to filter out a data and after applying this filter, there are only 1000 desired records.
So here, Execution plan should be generated for 1000 desired records instead of 1000000 records.
Syntax to Create Filtered Statistics:
CREATE STATISTICS My_Filterd_Statistics
ON TableName (Col1,Col2,Col3)
Basically, in the Filtered Statistics, you can add WHERE condition for desired statistics only. This is one the advanced technique of performance tuning.
Please share your ideas and opinions about this topic with me, your contribution will add true value to this topic.
If anyone has doubts on this topic then please do let me know by leaving comments or send me an email.
If you like this post, then please share it with others.
Please follow dbrnd.com, I will share my experience towards the success of Database Research and Development Activity.
I put up a post every day, please keep reading and learning.
Discover Yourself, Happy Blogging !
Anvesh M. Patel.