Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
rubs_65
Posting Yak Master
144 Posts |
 Posted - 2005-08-17 : 11:56:08
|
| Hi,We have an update statistics job that is taking very long time for a particular table. Table have 80 columns (int,numeric,nvarchar) and average row length is about 600 bytes and number of pages used by this table is about 1007493 and number of rows in table is about 18M.Following is the time taken for different sampling ratios:30--58 min5 - 3min10 - 28 min20 - 45 min 100 - 95 minWe have about 14 spindles for the hard disks and we can see from perfmon that avg. disk read queue length is between 45-90 continuously during operation and avg cpu used(4 cpu’s) is 30% and avg disk read bytes per sec is about 90MB.We used dbcc dbreindex to remove any fragmentation and still almost the same timings. This is the only job currently running on the system.Why it is taking 58 min for 30 percent sampling ratio when reading 90MB/s and total table is only about 8G in size?What do SQL Server do when running update statistics—what is the logic used?Thanks--Harvinder |
|
|
rubs_65
Posting Yak Master
144 Posts |
Posted - 2005-08-17 : 12:16:51
|
| We got it. This table have about 47 system generated statistics _WA_* and SQL Server was doing fullscan of table irrespective of sampling ratio 47 times for each statistics so once I removed all the _WA_* stats from the table it is now only taking 1 min for 30 percent sampling ratio for the table. |
 |
|
|
|
|
|
update stats taking long time