Thursday, 19 April 2012

When we use SP_UPDATESTATS

 

We have common doubts when to update statistics, before index or after index. To resolve this query we must understand, what is the statistics is? Why we need the statistics? If we can solve this problem we must solve our previous query. In this article I am trying to solve this problem.

The query optimizer uses the statistics to create the query plan to improve the query performance. The query optimizer automatically generates the necessary statistics to build high quality query plan to improve the performance of the query.

In fewer cases we need to create statistics for better result.

The statistics of the query optimization are objects that contain statistical information about the distribution of values in one or more columns of a table or indexed views.

The query optimizer use this statistics to estimates the cardinality, or number of rows, in the query result. These cardinality estimates enable the query optimizer to create a high-quality query plan.  The query optimizer could use cardinality estimates to choose the index seek operator instead index scan operator, and in doing so improve query performance.

We can use the following query to determine that the query optimizer has created statistics for a query predicate column. It queries the catalog views sys.stats and sys.stats_columns to return the database object name, the column name, and the statistics name for all of the columns that have single-column statistics.

When the query optimizer creates statistics on single columns as a result of using the AUTO_CREATE_STATISTICS option, the statistics name starts with _WA.

SELECT OBJECT_NAME(s.object_id) AS object_name,

       COL_NAME(sc.object_id, sc.column_id) AS column_name,

       s.name AS statistics_name

FROM   sys.stats s

       INNER JOIN sys.stats_columns sc ON s.stats_id = sc.stats_id

                                          AND s.object_id = sc.object_id

WHERE s.name like '_WA%'

ORDER BY s.name;
 

GO

Now we are going to solve this query that I mentioned in the top of my article.

"When we are going to update the Statistics by sp_updatestats, after or before rebuilding the query"

 

To solve this query we must take some example of t-sql statements.

 

Step-1 [ Create Table with primary key ]

CREATE TABLE tbl_stat

(

  ID             INT,

  SNAME          CHAR(2000)

  CONSTRAINT PK_tbl_stat PRIMARY KEY CLUSTERED (ID)

)

 

Step-2 [ Now Insert Some records in the Table Objects ]

BEGIN

      DECLARE @RowID DECIMAL=0

      WHILE @RowID <> 15000

        BEGIN

             SET @RowID = @RowID + 1

             INSERT INTO tbl_stat(ID, SNAME)

             SELECT @RowID, 'Student-' + CONVERT(VARCHAR(5), @RowID)

        END

END

 

GO

 

SELECT * FROM tbl_stat

 

Step-3 [ Now see the statistics look like for primary key ]

DBCC SHOW_STATISTICS ('tbl_stat', PK_tbl_stat) WITH STAT_HEADER

Name

PK_tbl_stat

Updated

NULL

Rows

NULL

Rows Sampled

NULL

Steps

NULL

Density

NULL

Average key length

NULL

String Index

NULL

Filter Expression

NULL

Unfiltered Rows

NULL

 

Step-4 [Let's rebuild the index to start with ]

ALTER INDEX PK_tbl_stat ON tbl_stat REBUILD

Step-5 [ Now See the Statistics Again ]

DBCC SHOW_STATISTICS ('tbl_stat', PK_tbl_stat) WITH STAT_HEADER

Name

PK_tbl_stat

Updated

Apr 19 2012 12:09AM

Rows

15000

Rows Sampled

15000

Steps

3

Density

1

Average key length

4

String Index

NO

Filter Expression

NULL

Unfiltered Rows

15000

 

 

Step-6 [ Conclusion ]

Here in this example we find that the rebuilding the index automatically update the statistics.

But what about the statistics that are not the part of the Index.

 

Step-7 [ Create Non-Index Statistics ]

CREATE STATISTICS stats_NIndxData ON tbl_stat(SNAME)

Step-8 [ Observation ]

DBCC SHOW_STATISTICS ('tbl_stat', PK_tbl_stat) WITH STAT_HEADER

n  No Changes Found

 

 

Rebuild the Index Again

 

ALTER INDEX PK_tbl_stat ON tbl_stat REBUILD

 

Now check

DBCC SHOW_STATISTICS ('tbl_stat', PK_tbl_stat) WITH STAT_HEADER

n  No Changes Found

 

Step-9 [ Now run the sp_updatestats ]

EXEC sp_updatestats

DBCC SHOW_STATISTICS ('tbl_stat', stats_NIndxData) WITH STAT_HEADER

 

Name

PK_tbl_stat

Updated

Apr 19 2012 12:18AM

Rows

15000

Rows Sampled

7068

Steps

15

Density

1

Average key length

2000

String Index

YES

Filter Expression

NULL

Unfiltered Rows

15000

 

Step-8 [ Remarks ]

So the answer of the query is "You must run the sp_updatestats after rebuilding the index.

 

Hope you like it.

 

Posted by: MR. JOYDEEP DAS

  

 

 

 

 

10 comments:

  1. Wonderful bloggers like yourself who would positively reply encouraged me to be more open and engaging in commenting.So know it's helpful.

    ccna training in chennai Adyar

    ReplyDelete
  2. Very nice post here thanks for it .I always like and such a super contents of these post.Excellent and very cool idea and great content of different kinds of the valuable information's.

    PPC Services in Chennai

    ReplyDelete
  3. i like that kind of information,not only i like that post all peoples like that post,because of all given information was very excellent.


    SEO Company in Chennai
    SEO Services in Chennai

    ReplyDelete



  4. What an awesome post, I just read it from start to end. Learned something new after a long time.


    SAP FICO Training in Chennai

    SAP SD training in Chennai

    ReplyDelete
  5. I cant wait to check out some of these blogs! I’ve really wanted to start learning more about cars and auto repairs lately and I think this will help a lot. I think it can save my family some money if we knew how to do some repairs at home.! Thanks again for all the options.

    home spa services in mumbai

    ReplyDelete
  6. I just see the post i am so happy to the communication science post of information's.So I have really enjoyed and reading your blogs for these posts.Any way I’ll be replay for your great thinks and I hope you post again soon.

    digital marketing company in india

    ReplyDelete
  7. Superb i really enjoyed very much with this article here. Really its a amazing article i had ever read. I hope it will help a lot for all. Thank you so much for this amazing posts and please keep update like this excellent article.

    Digital Marketing Company in Chennai

    ReplyDelete
  8. Great Article… I love to read your articles because your writing style is too good, its is very very helpful for all of us and I never get bored while reading your article because, they are becomes a more and more interesting from the starting lines until the end.

    Digital Marketing Training in Chennai

    ReplyDelete
  9. Great post! I am actually getting ready to across this information, It's very helpful for this blog.Also great with all of the valuable information you have Keep up the good work you are doing well.
    House Cleaning Service in Chennai

    ReplyDelete


  10. تتعدد الشركات التي تقدم خدمات ىالتنظيف لاكن لا يمكن ان تكون كلها في نفس مستوي الجوده فان كنت من الباحثين عن جودة الشركه قبل اي شئ اخر فانصحة بزيارة احدي تلك الصفحات
    شركة تنظيف بالخرج
    والتي تقدم افضل خدمات التنظيف بالمنزل باعلي مستوي من الكفائه

    ReplyDelete