October 22, 2008 at 10:56 am
Hi,
Over the last 2-3 weeks i have been battling with performance on one of my servers. I looked at a number of things and it all came to zero. Attached is a counter log that i generated for the machine. This machine has sql2005 on it. Users complain that machines hang when they use the call centre application.
Cpu activity is always high resulting in a low page life expectancy. I transferred databases to another server(with more processors) thinking that would solve the problem but it did not. It seems that one of the databases is giving a problem as there is no other application (of note) running on the server. No jobs are running when they are supposed not to.
Could the statistics be out thats causing certain queries to take long to run? Are recompilations happening at a high rate than normal? Do i have to update the statistics on each table manually even the Auto Update Statistics option on all databases are set to on? Please help to solve this problem.
October 22, 2008 at 12:37 pm
Have you run profiler during a period when it is appearing slow? This will show you the queries performing badly and may show the indicate the cause of the problem.
Out of date statistics can cause these problems, have you tried updating the statistics? What's on disk E:?
October 22, 2008 at 12:49 pm
It could be statistics, it could be poor indexes, it could be poorly written queries, it could be inadequate hardware. At the moment it looks like high CPU usage. Run profiler for a while (an hour or so) during the server's busy time, and see if you can identify the top 5 or so queries in terms of CPU used.
Take them into management studio and see if you can fix them to use less CPU.
If you need help with that, post the queries and their execution plans here and we can offer some advice.
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
October 28, 2008 at 6:27 am
Hi,
I think i found what the problem is.There was a table with not all the necessary indexes on it. As a result, the execution plans of the procs that use this table were shoing table scans on that table. I rectified it and seems things are fine. Don't understand though how the indexes were dropped. How do i set up auditing in sql to monitor who does what in the systsm?
Thanks
Tendayi
October 28, 2008 at 6:48 am
In SQL 2005 you can set up DDL triggers to log schema changes.
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
October 29, 2008 at 4:16 pm
You can view schema changes by using the reports built into SSMS.
Right click on the database, Reports, Standard Reports, Schema Changes History
Might require SP2, can't remember off the top of my head.
October 30, 2008 at 1:27 am
That queries the default trace. It's 5 files of 20 MB and when the 5'th fills up the 1st is deleted. The trace will only have data for a certain amount of activity on the server, it might be hours, days or weeks.
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply