SIMS Server SQL causing 100%

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:
Reboots,
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
64BIT OS

Do you have the graphs on the front page?

Quote:

---

Originally Posted by glennda

Do you have the graphs on the front page?

---

The homepage widgets were set by the school to show quite a bit of information. We removed the setting to push homepage widgets to staff to see if this would be reduce the load and saw no change in performance.

This thread covers a lot of possibilities.
http://www.edugeek.net/forums/mis-sy...pus-100-a.html

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

Quote:

---

Originally Posted by matt40k

What's your Average Disc queue length

---

How would I check the 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!

Re-disc queue:

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
Click Add..
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.