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

image

Click on option tab and check various following properties there

image

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

  • 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

image

 

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 :

image 

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

image

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

Advertisements
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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

%d bloggers like this: