BISQL # 62 – What are Statistics ? SQL Server–SSMS Statistics Information and Usage ,Reasons !
Hi friends ,
While measuring performance and query optimization in SQL server one must understand Statistics available in SQL Server
In this post we are going to basic of Statistics and how we are going to achieve in SQL Server
Before digging into this post I have to answer Why I should use Statistics ?
Reason 1 : What is the best option to keep up to the mark with using Statistics option and using same in production.
Reason 2 :Why anyone has change this Statistics option and what was motto/aim behind that
Reason 3 :How my Database will behave if I have changed any of the following option.
Reason 4 :Is there any other way that I don’t know to improve or measure performance
Perform following steps for Statistics check :
Right Click and Click on Properties
Click on option tab and check various following properties there
We are focusing on following potions
- Auto Create Statistics
- Auto Update Statistics
- Auto Update Statistics Asynchronously
- Now lets dig into what is Statistics ?
Statistics is very very (10 times ) important aspect of SQL Server.Without the help of Statistics, the SQL Server Engine cannot decide the most optimal execution plan for the query.
Statistics are used for every option in SQL Server such as
- SELECT
- INSERT
- UPDATE
- DELETE How should I Check or enable Statistics option
- With help of following query we can check this option
USE AdventureWorks GO sp_helpstats 'HumanResources.Department'; GO
- Which shows output as
But there is other round way to get this option also by firing this script :
USE AdventureWorks GO SELECT DISTINCT OBJECT_NAME(s.[object_id]) AS TableName, c.name AS ColumnName, s.name AS StatName, s.auto_created, s.user_created, s.no_recompute, s.[object_id], s.stats_id, sc.stats_column_id, sc.column_id, STATS_DATE(s.[object_id], s.stats_id) AS LastUpdated FROM sys.stats s JOIN sys.stats_columns sc ON sc.[object_id] = s.[object_id] AND sc.stats_id = s.stats_id JOIN sys.columns c ON c.[object_id] = sc.[object_id] AND c.column_id = sc.column_id JOIN sys.partitions par ON par.[object_id] = s.[object_id] JOIN sys.objects obj ON par.[object_id] = obj.[object_id] WHERE OBJECTPROPERTY(s.OBJECT_ID,'IsUserTable')= 1 AND (s.auto_created = 1 OR s.user_created = 1);
Above query also gives elaborative result with some more details
Output :
But we will not stop at this details only !!
With help of BCC SHOW_STATISTICS we can see more information as shown in scrip
USE AdventureWorks GO DBCC SHOW_STATISTICS ('HumanResources.Department',_WA_Sys_00000003_300424B4 ); GO
Output
Now before ending with conclusion just want to give answer of following question
Do we need keep Auto Create Statistics and Auto Update Statistics settings true/on ?
My answer is Surely Yes Because its really helpful for any execution plan.
But still if your are interested why its should be true/on wait for my next post on same !! 
Hope this three query is useful for you !!
Thanks for visiting my blog !!
If you really like reading my blog and understood at lest few thing then please don’t forget to subscribe my blog .
If you wan daily link and analysis or interesting link go to following website which will give @ your inbox please subscribe our following link resource blog
Where todays links are
SQL Server Mentalist 