Home > Query, SQL > BISQL # 62 – What are Statistics ? SQL Server–SSMS Statistics Information and Usage ,Reasons !

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

  1. Auto Create Statistics
  2. Auto Update Statistics
  3. 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

  • DELETE How should I Check or enable Statistics option
    With help of following query we can check this option
    USE AdventureWorks
    sp_helpstats 'HumanResources.Department';
    Which shows output as



But there is other round way to get this option also by firing this script :

USE AdventureWorks
                OBJECT_NAME(s.[object_id]) AS TableName,
                c.name AS ColumnName,
                s.name AS StatName,
                STATS_DATE(s.[object_id], s.stats_id) AS LastUpdated
    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]
      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
DBCC SHOW_STATISTICS ('HumanResources.Department',_WA_Sys_00000003_300424B4 );



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 !! Smile

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

Link Resource Website

Categories: Query, SQL
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: