November 1, 2005 at 6:11 am
I am using sql server 2000. I ran an update stats command with 100% percent to increase the rows examined for my dimension tables.
DECLARE @@name [nvarchar](64), @@stmt [nvarchar](256)
DECLARE c CURSOR FOR
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND TABLE_NAME LIKE 'D%'
OPEN c
SET @@stmt = N'--'
FETCH FROM c INTO @@name
WHILE (@@FETCH_STATUS = 0)
BEGIN
-- statement to update table's statistics
SET @@stmt = N'UPDATE STATISTICS ' + @@name + ' with resample 100 percent'
EXEC sp_executesql @@stmt
FETCH NEXT FROM c INTO @@name
END
CLOSE c
DEALLOCATE c
However, when I then ran an sp_updatestats, it went back to the default sample size of about 5% of rows.
Can anyone tell me how I can increase this so it always samples 100% of my dimension tables when creating statistics.
PS. The code above was obtained from a web site, but I cannot remember where I got it from.
November 1, 2005 at 7:36 am
Maybe I am missing something but why don't you just use the maintance plan wizard for the stats? I have used it for four years on all of our databases and it works just fine.
November 1, 2005 at 7:58 am
The trouble I have is that I want to sample dimension tables at 100%, but fact tables at a lot lower level.
If I ran the entire database at 100%, it would take hours or even days. I know from experience, gaving tried it on a test box.
November 1, 2005 at 8:03 am
OK, I understand why you do it that way then...
I assume you have the auto stats turned off on the database since it is so large then ?
I have seen some info on gathering stats at 25% vs. 100% as there is no performance gain. I know in Oracle land they say that on large objects 5% sample is good enough.
November 1, 2005 at 8:13 am
We actually have the auto stats truned on. It's just that the sample is too low. I presume this happened when the database was first set up.
Let me give you an example.
I ran a normal sp_updatestats command-
DBCC SHOW_STATISTICS (table,index) resulted in:
sample = 17517, rows=375946 density=6.75%
I then run command -
UPDATE STATISTICS table with sample 100 percent
DBCC SHOW_STATISTICS (table,index) resulted in:
sample = 375946, rows=375946 density=2.60%
I understand that if density reaches 10%, it will never use an index, often even at a lower level. I am close to it here. The problem I have is that we use a lot of month/year fields from 1989 to 2010. If it samples 15,000 rows and only finds dates 199301,199302,199303 - it will think that in index is not required.
November 1, 2005 at 8:17 am
Am I missing something here...
If you have auto stats turned on then really you do NOT need to run a manual process to update the stats..... that is what the AUTO Stats feature is for.
November 1, 2005 at 9:20 am
My assumption was that the auto stats merely updates the existing stats based on the sample it has already examined. Hence, becasue the sample was low, updating stats does not build them efficiently.
If this is not the case, I would love to know.
It's just that the example I gace in my previous post proves a more realistic density is achieved through a larger sample.
November 2, 2005 at 9:45 am
I have decided to create a job that:
Samples 100% for my dimension tables, all starting with a 'd'.
Samples 10% for my fact tables starting with 'f'.
Using the statment earlier in the post. Works well for me and gives more realistic density levels. Seems to me that sql server only samples about 2.5% with the resample command for large tables over 100k rows. Not enough as far as I am concerned.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply