August 14, 2008 at 11:28 pm
we have a few web applications running databases on SQL Server. One of the databases with full-text catalog. The CPU load was OK but it has heavy load of CPU ( above 90% very often used by sqlservr service) since upgraded from SQL 2000 to SQL 2005 sp2 which has same CPU and more RAM. and our web server loses connection to the database when the database CPU goes high.
How can I investigate into this? as it is so constantly, I am not sure it's a good idea to investigate with profiler.
can experts here give some suggestion what I should do?
Thanks,
Sherry
August 15, 2008 at 12:13 am
Make sure that you've updated all the statistics on all the tables after the upgrade.
UPDATE STATISTICS < Table Name >
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
August 15, 2008 at 1:36 am
as a side note BOL also recommends to update usages too using
dbcc updateusage
I've already seen a similar problem on a couple of databases i upgraded recently.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 15, 2008 at 4:31 am
yes, I did run dbcc updateusage when upgrade each database.
August 15, 2008 at 4:33 am
yes, I Do schedule update statistics for all tables of each database overnight.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply