Update statistics on a large table

Question

Post reply

Update statistics on a large table

kladibeeto

SSCommitted

Points: 1582
More actions
May 25, 2009 at 2:35 am

#200794

Hi all.
Can anybody explain me how SQL server decide how much rows will be taken for sample when we run update statistics statemant?
I know for hints like full scan or percantage but I dont know what is he doing when auto update statistics is executed.
I ask this because I am little confused with this situation...
I have table which have 125 milions of rows and when I look dbcc show_statistics I see that he take only 400 000 rows for sample.
I am using sql server 2000.
Thanks in advance.

Viewing 11 posts - 1 through 10 (of 10 total)

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

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 1

As far as I know Microsoft didn't document the way that the server decides on the sampling rate.

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Paul White SSC Guru Points: 150467 More actions · Answer 2

As far as I know, the precise details of the default sampling are undocumented, save that tables with fewer than 1,024 pages are always fully scanned. Some additional information exists which might be of interest however:

For LOB statistics, see http://blogs.msdn.com/psssql/archive/2009/01/22/how-it-works-statistics-sampling-for-blob-data.aspx

For general statistics-related information, a key resource is http://technet.microsoft.com/en-us/library/cc966419.aspx

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

kladibeeto (5/25/2009)
I am using sql server 2000.

Please post SQL 2000-related questions in the SQL 2000 forums in the future. If you post in the 2005 forums, you're very likely to get 2005-specific solutions.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

kladibeeto SSCommitted Points: 1582 More actions · Answer 4

This applies to both SQL 2000 and SQL 2005 I test it.

Paul White SSC Guru Points: 150467 More actions · Answer 5

kladibeeto (6/6/2009)
This applies to both SQL 2000 and SQL 2005 I test it.

Yes but the links I posted are 2005-specific. That's why it pays to post in the correct place as Gail said.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

kladibeeto SSCommitted Points: 1582 More actions · Answer 6

kladibeeto

SSCommitted

Points: 1582

June 8, 2009 at 5:22 am

#1006003

Ok, I will be more carefull next time.

Sorry 🙂

neilan Mr or Mrs. 500 Points: 578 More actions · Answer 7

I'm pretty sure you can set the sample rate, I'll try and dig the syntax out

neilan Mr or Mrs. 500 Points: 578 More actions · Answer 8

Try

UPDATE STATISTICS (table_or_indexed_view_name) WITH FULLSCAN

and then check the stats on that table/index to see a full scan wasw done

or us the SAMPLE syntax (below) to set the sample rate.

Kind regards

Jamie

http://msdn.microsoft.com/en-us/library/ms187348.aspx

UPDATE STATISTICS table_or_indexed_view_name

[

{

{ index_or_statistics__name }

| ( { index_or_statistics_name } [ ,...n ] )

}

]

[ WITH

[

[ FULLSCAN ]

| SAMPLE number { PERCENT | ROWS } ]

| RESAMPLE

| [ ,...n ]

]

[ [ , ] [ ALL | COLUMNS | INDEX ]

[ [ , ] NORECOMPUTE ]

] ;

Paul White SSC Guru Points: 150467 More actions · Answer 9

Jamie,

Good post.

However, the first post did say that 'kladibeeto' knew about full scan and percentages - the question was specifically about the default sampling rate...

I say 'good post' because you addressed something which might be useful for other people reading this thread. 🙂

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

neilan Mr or Mrs. 500 Points: 578 More actions · Answer 10

neilan

Mr or Mrs. 500

Points: 578

June 19, 2009 at 9:17 am

#1012496

So he does!

My apologies