Do you have the graphs on the front page?
One of our Servers is performing really poorly, the CPU Utilisation shows SQL running at 100% from around 9am on a morning until 3-4pm on an afternoon. (When the teachers are logged in and using SIMS)
CAPITA are really scratching their heads to try and resolve this one, so I wondered if anyone out there had any thoughts.
The server follows a pattern on our performance graphs showing the CPU spike daily to 100% and stay that way until mid afternoon, clearly showing this is a load issue somewhere.
Any help is really appreciated.
We have tried:
Index Patch (Multiple Times)
Detaching and Re-Attaching Databases
Increasing RAM from 6GB to 8GB as requested by CAPITA
Tech Spec of Server
E6250 @ 2.4GHz Quad Core VM
8GB of RAM
Windows 2008 SP2
Do you have the graphs on the front page?
This thread covers a lot of possibilities.
SQL Server CPUs at 100%
I'd open SQL Activity Monitor and look the the 'expensive queries' list when you get these problems.
If the cause remains unexplained, data or log file 'auto grow' operations can sometimes cause unexplained performance drops.
What's your Average Disc queue length
There is an extensive query running that CAPITA have noted but said it was ok, even though it is running at around 3millions operations a second!!!
This has been queried multiple times but I have been told by CAPITA Engineers and a CAPITA Dev that this is not the cause of the high CPU.
Disk queue length is you can view in Admin Tools - performance.
What is the size of the database?
Have you tried uninstalling and re-installing SQL then re-attaching the database?
Are you running the lastest Service Pack and patches for SQL?
As the others have said check the disk queue length for the database drive.
We had a similar issue and it was caused by the OS, database & logfiles all being on the same disk spindles. In their infinite wisdom a predecessor (or LEA) put the database on the D: drive, which was just a partition on the RAID5 containing the OS. The disk queue length was huge when checked. I rebuilt it last summer on our new virtual server platform with the database on the SAN now and working very well. We tried the SIMS diagnostics on the old server and it would take 30-40 minutes, now it takes less than 2.
Something else we did with FMS rather than SIMS. Detattach and reattach the database was the advice from Capita and it worked! We also found the rebuild indexes patch helped a little. However these were only a sticking plaster on the real problem of the server being badly set up in the first place.
As an aside I am planning on getting a t-shirt made with something like "it wasn't my fault, I wasn't here when that was bought." written on it as I seem to be saying it lot at the moment. The only problem is that we are changing so much that soon it will all be my fault!
Last edited by sjatkn; 16th July 2012 at 01:18 PM.
Open Performance Monitor from Administrative Tools
Data Collector Sets > User Defined
R-click in the right hand window panel > New > Data Collector Set
Name: IO Performance - Select Create manually (Advanced) - Next
Create data logs > Performance counter
Select counters from computer: <local computer>
PhysicalDisk - Select each individual drive that SIMS uses - ie S: - Select Current Disk Queue Length, Avg. Disc sec/Read, Avg. Disc sec/Write - click OK
Sample interval: 1 - Units: Minutes
Root directory: %systemdrive%\PerfLogs\Admin\IO Performance
Save and close - Finish
R-click on the IO Performance > Properties - Stop Condition - Overall duration (tick), 1 - Units: Weeks - click OK
Start the IO Performance - single click on it, then click on the play button
Once it's finished - Open Performance Monitor from Administrative Tools
Reports > User Defined > IO Performance > Double-click the report
Ideally Current Disc Queue Length will be less than 3 and ideally the Avg. Disc sec/Read and Avg. Disc sec/Write will be 10 ms or less however up to 20 ms would be acceptable. These should be only used a target as they will be almost impossible to achieve 100% of the time in a real-world environment.
For more information - visit: SQL Server Best Practices Article
Ideally you want proper monitoring, ie the dedicated monitoring server that just polls the server, however this will do in most cases.
There are currently 1 users browsing this thread. (0 members and 1 guests)