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 |
|
rockmoose
SQL Natt Alfen
3279 Posts |
 Posted - 2005-10-12 : 06:03:11
|
Hi all,I ran UPDATE STATISTICS with SAMPLE 80%On all indexes in the DB with outdated statistics date.(100+)Now the CPU spiked to 90%-100% and stabilized at that level.The DB worked as usual, but with very high CPU usage.There are about 900+ procs.My question is:Can the update of the stats and the high cpu be related ?We ended up restarting sql, and now the CPU is back to a nice comfortable 40%  ...rockmoose |
|
|
mcrowley
Aged Yak Warrior
771 Posts |
Posted - 2005-10-12 : 09:45:53
|
| Updating statistics will probably cause a bunch of procedure recompilations, as well (but I am guessing there). See what happens when you spread out the updates over a few hours. Also, monitor SQL Statistics:Compilations/sec in perfmon, and the CPU column for the update statistics process in sp_who2. That should give clues as to where the extra CPU usage is going. |
 |
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2005-10-12 : 14:37:41
|
| While the server was at 90-100% I kept checking sp_who2, sysprocesses, and used perfmon to see where the action was at.Recompilations was something I did check, but I did not see anything special there.The batch requests/sec was at normal level (avg 80) during the spike period.The update statistics batch was well finished, but I suspect a certain relationship between the two events though, but could not establish one.At first I was thinking of running sp_recompile against all the procs, but ended up with a simple restart, since I wasn't sure of the problem.Well I'll be running indexdefrag with update statistics soon enough, we'll see what happens then.Practically no maintenance at all has been done on the db for the last few years. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-10-12 : 14:45:41
|
| The two events are definitely related. Sampling at 80% is pretty high. We typically only sample that much when there has been a drastic change in data or it is a non-production environment. We run update statistics nightly with 10-25% sampling.Tara |
|
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2005-10-12 : 17:30:55
|
| >> Sampling at 80% is pretty highI wanted to give all the statistics a thourough initial runthrough, maybe it was excessive.Future plans is to implement indexdefrag and only run update statistics on the defragged indexes.Also just run this on indexes that are high->seriously fragmented.Autostats is on.I read the ms paper on best practices, and I must say that I am a bit surprised on the small/no performance improvement this gave in many cases. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-10-12 : 17:33:33
|
quote:
I read the ms paper on best practices, and I must say that I am a bit surprised on the small/no performance improvement this gave in many cases.
I was surprised as well. My little old databases (little to MS is anything under 1TB!), according to that article just don't need the regular database maintenance routines run on them. I've seen substantial performance improvements when running these though (but not always), so I've continued to run them regardless of that article.Tara |
|
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2005-10-12 : 18:02:18
|
For large tables you reclaim space as an additional benefit.I've seen many avg size (20-100GB) databases running for years without any major maintenance and it doesn't seem much of a problem.Then again I don't know how much better thay would have worked with maintenance  My plan was to do this where I see major defragmentation of the indexes. Otherwise leave it.I believe that the system stabilizes to some degree and too much reorganization can upset it, thus degrading perf temporarily.This is just speculation of course. |
|
|
|
|
|
|
|
|
UPDATE STATISTICS - HIGH CPU ?