Update Statistics

Question

Update Statistics

Viewing 6 posts - 16 through 20 (of 20 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

woot - nice question

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Chad Downey SSC Enthusiast Points: 126 More actions · Answer 2

Okay, just be clear I work with SQL Server 2005 right now so I went to 2005 BOL by default. My thoughts were not meant to be shallow, but rather to point out that the SQL Server BOL for 2005 doesn't explicitly state anything about updating query optimization statistics. Instead it makes reference to indexes and non indexed fields that you can update using the update statistics command.

So it appears that BOL 2005 implies what BOL 2008 is explicitly saying in the first paragraph. Please have a look for yourself. Yes, I was confused by the question but only because I looked at BOL 2005 first. Ha! My answer now will be straight from the 2008 verbiage. Also see the remarks section:

****************** snippet from remarks section of 2005 BOL for UPDATE STATISTICS *******************************************************

The Database Engine keeps statistics about the distribution of the key values in each index and uses these statistics to determine which index or indexes to use in query processing. Users can create statistics on nonindexed columns by using the CREATE STATISTICS statement. Query optimization depends on the accuracy of the distribution steps:

* If there is significant change in the key values in the index, rerun UPDATE STATISTICS on that index.

* If lots of data in an indexed column has been added, changed, or removed (that is, if the distribution of key values has changed), or the table has been truncated by using the TRUNCATE TABLE statement and then repopulated, use UPDATE STATISTICS.

****************** end snippet from remarks section of 2005 BOL for UPDATE STATISTICS *******************************************************

References:

http://msdn.microsoft.com/en-us/library/ms187348%28v=sql.90%29.aspx - 2005 BOL

http://msdn.microsoft.com/en-us/library/ms187348%28v=SQL.100%29.aspx - 2008 BOL

Thanks,

Chad

Chad E. Downey, CDMP - Certified Data Management Professional
Consultant - SQL Server, SSIS, SSRS, SSAS

Andre Guerreiro SSCertifiable Points: 7319 More actions · Answer 3

It's really nice that they improve BOL with each new version.

The 2008 version looks way clearer IMHO.

I'm often very impressed by the MS SQL Server team.

I don't know if I can say the same about the other MS teams working on other projects though.

They only need to implement more ANSI standards. 😀

Best regards,

Andre Guerreiro Neto

Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA

TomThomson SSC Guru Points: 104773 More actions · Answer 4

Nice question. I got it wrong, so I learned something.

Tom

Chad Downey SSC Enthusiast Points: 126 More actions · Answer 5

Sorry, I should have been more specific. The index structure is not being updated, but the statistics about the index would be updated. My point really was that the two go hand in hand. If you have an index on a large table and you run update the statistics command on the index it can assist in performance. The Query Optimizer will use these statistics to develop the best query plan. At least that is how I understand it for now.

I have used the sp_updatestats against larger tables or when moving from one version of SQL Server to another on a data source. Run the following command and you will see that UPDATE STATISTICS is embedded into this stored procedure.

sp_helptext sp_updatestats

/*

line 92

select @exec_stmt_head = 'UPDATE STATISTICS ' + @schema_table_name + ' '

*/

The cool thing is that it only updates what is necessary and then gives you a bit of information about what happened.

Thanks,

Chad E. Downey, CDMP - Certified Data Management Professional
Consultant - SQL Server, SSIS, SSRS, SSAS

Ken SSCoach Points: 17191 More actions · Answer 6

Got this one because the DBA at my old job helped me with an issue with a stored procedure where it was running exponentially longer every day and the only way to speed up the proc was to issue an update statistics statement on a problem table used in the procedure. This instantly reduced the query time back down to seconds as expected.