Solved

Bad SQL server 2005 performance

Posted on 2011-02-22
15
562 Views
Last Modified: 2012-06-27
I have had some serious SQL server performance problems in the last few weeks. Microsoft RMS is working very slow at times and the problem is at the sql server. I ran the wait stats code from this page. I first cleared the stats and then checked in 10 min. The numbers in the following picture look very high to me. This is after having reset the counters for about 10 minutes. Is this normal? What should I look at doing to bring these numbers down?
 Wait-stats
After about another 10 minutes the wait stats were like the following.
wait-stats2
I've noticed the file system is fragmented pretty bad. I'll try to defragment the file system once I can shut down the sql server because the defragmenter cannot defragment the database files while the sql server is running. But I don't know if fragmentation can reduce the performance so much..??
0
Comment
Question by:timbraun
  • 6
  • 5
  • 4
15 Comments
 
LVL 25

Expert Comment

by:slam69
ID: 34952139
yup fragmentantion of the physical disk can cause susch problems especially if the mdf file has been grown and shrunk lots of times, look to sort thsi fragmentnation as a priority. take a look at Diskeeper, its a tool that automatically defragments your drives during periods of low activity and it can work with MDF files which are in use so no need for downtime.

Additional things though to check---

Are your SQl table indexes regularly updated, if you need to know how to check thi let me know.

if the sql indexes are up to speed then how about teh statistics ( both vital to good read performance0

Also take a look at teh underlying config of the server _

what drive config do you have raid etc and how are the files located within teh physical structure


let me know on the rest and we can take it form there but these are good starting points
0
 
LVL 76

Expert Comment

by:arnold
ID: 34952294
Can you provide specs for hardware, CPU/Memory size of DB and amount of space available.
Check taskmgr performance commit charge)

If system has RAID, it might be degraded.
http://technet.microsoft.com/en-us/library/ms179984.aspx
0
 
LVL 3

Author Comment

by:timbraun
ID: 34952592
slam69, thanks for your recommendation of Diskeeper. That is a program we need.

I don't know how to check if the SQL table indexes are updated regularly. Microsoft RMS has an option to reindex tables and I've done it a few times but not very often. Let me know if you need more info and how to get it. (I've written lots of sql queries and programmed in VB.NET but overall I'm a beginner in sql)

Our disk system is raid 5, 4 80gb drives, with 2 partitions. A 20gb system partition with the OS, MS server 2003 on it. Another 200gb partition that we use for file sharing. The db is on the system partition.

arnold,
Processor, 3.2G, 2M, Xeon Irwindale, 800, R0
Memory, 2gb 400mhz
DB Size, 1800mb
Free space, 4gb on system partition, 16gb used.
taskmgr commit charge is 35%


Also FYI, this server has been working very well almost till now. It has slowly degraded performance over the last month. As I felt already and after slam69's post I strongly feel this is a fragmentation issue. I'll defragment next night and let you know how it goes tomorrow. Let me know if you have any other ideas. Thanks!
0
 
LVL 25

Expert Comment

by:slam69
ID: 34952676
system disk striped with the db on same patition? hmm thats not great? where are the log files on same parttion?

striping data (raid5) is great for writing to disk but not great from a read point of view especially if teh data is fragmented.

get the physical fragmentation resolved first, im in teh office right now but when i get a spare 5 ill give you a coupel of scripts, first one willlet you see the most fragmented indexes, second one you can schedule to run in maintenance window, checks for top 10 (can be adjusted) fragmented indexes and rebuilds them, after its been running a few nights depending on volume of indexes it shoudl have touched each one and then go into a rolling program of keeping them all in check 10 per day.

Just seen that you file share from that machine too, is that all from teh same raid 5 array? if so even fixing your fragmentation might not help, check using perfmon your average diskqueue length and then may need to look at setting an sql trace up to see what sort of read write performance yoru gettign from teh db perspective
0
 
LVL 76

Expert Comment

by:arnold
ID: 34952723
You have a total of 2GB memory with your DB at ~2Gb.
Check the configuration of the server to make sure it is configured as an Application server.
Advanced settings, performance settings\advanced
memory and processor should be prioritized for programs.

Are you storing the DB on the same drives as the os?
0
 
LVL 3

Author Comment

by:timbraun
ID: 34954312
Yes the data and log files are on the system partition. I guess this is very bad practice. The whole server is on RAID5 including the file sharing. Do you mean we should not have an OS running on Raid, on a different raid config or is that part fine?
0
 
LVL 25

Expert Comment

by:slam69
ID: 34954591
ok think if its ok to go right back on this please bear with me if im teaching you to suck eggs and if anyone posts in between.

within any system one of the drivers for speed of the system aside from ram etc is how fast the data for the system can be read and written as this is the purpose of a database, speed of reads and writes is governed by how much i/o your set up can handle. All the i/o for any machine where writes to the disk need to happen have to go through the disk head or heads.

in your setup you have 3 diskheads which are being used for the raid (one is redundant as hot swap in raid 5) those three disk heads of sharing the load for reading the information from the database master file, writing to the database master file, writing the sql transaction log, running the os and operating the page file ALONG with all the other things you have going on. All these bits of information are stored across different parts of teh drives so your disk heads are havin the to whack their knackers off running round teh drive to get all the info you need, obviously thsi takes time for each transaction and activity so you start to get queuing of requests at the disk head and this is when you start to see performance degradation.

the OS will prioritise the various functions and depending on the config you have some activities will keep queuing as more priority actions are passed through and this keeps going and building and slowing as things build up hence degradation gets worse over time. A reboot would resolve for a short while clearing teh cache etc and releasing locked memory but it will just ramp up again.

really need to look at getting teh config right or your just going to keep having this happen if the load on the system stays the same.

happy to help with this however might be needed over mail as it could get quite long winded

apologies if i have missed anything or not quite worded properl its been a long day.....
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 25

Expert Comment

by:slam69
ID: 34954636
I Should add that whilst this will definately be having an impact on your performance and is likely to be a contributing factor to your situation, it may not be the only issue however this would be my starting point and first point of resolution

Additionally I have simplified things quite a bit in my above description to illustrate this example and the applicable elements
0
 
LVL 3

Author Comment

by:timbraun
ID: 34955896
Avg disk read queue length is .74
Avg disk write queue length is between 6 and 16 during peak periods.

What should those numbers be?
0
 
LVL 76

Assisted Solution

by:arnold
arnold earned 100 total points
ID: 34956445
http://msdn.microsoft.com/en-us/library/ms175903.aspx

In a three disk setup, the value should not exceed 6.

The OS should often be running on a RAID 1 (mirror) such that a failure of one of the disks will have no impact on the performance. the Data disk where the SQL datafiles are stored should be raid 5 or raid 10 if you can afford the cost of the disk as a RAID 10 provides a higher fault tolerance.
I.e. raid 10 is a stripe of two RAID 1' This means that you will not see an impact on performance event if each RAID 1 pair loses a drive.
RAID 5 will see a decline in performance when one drive is lost.
The rebuild process in a raid 5 takes longer than a RAID 1/RAID10. The length of time  the process takes leaves one open to a second drive failure which could lead to a total data loss.
0
 
LVL 76

Expert Comment

by:arnold
ID: 34956449
Oh sorry, the queue write value should not exceed 6.
0
 
LVL 25

Accepted Solution

by:
slam69 earned 400 total points
ID: 34958778
Arnold answered your diosk queue question whilst i was sleeping lol yup that is way too long.

Arnold has also pointed his raid configuration, obviously you would have to spend quite a bit to get into his scenario as you would need minimum 5 disk array for the os raid 1 + raid 5 data partition, additionally there is no mention of what to do with the log files. Also whilst raid 10 is a DBA's dream as it offers great redundancy in terms of minimising data loss but it doesnt suffer from too much degradation in performance however this is at a massive cost in terms of actual disk capacity seen against what you have to plug in (you get 25% of the total capacity of disk space you plug in)

for me the best mid price solution would be an 8 drive hardware chassis,2 disk mirror(raid 1) the system disk for redundancy, 4 drive raid 5 array just for the data and then another 2 disk mirror (raid 1) for the log files. this separates out the key functionalities and gives them all their own disk head for operation.

there are other config ideas to consider such as where to put the temp db, page file, speed of disk and size of disk etc however the above config would massively increase your performance, total outlay for a new box, something like a dell R710 with licensing would be around the £7k mark if in UK
0
 
LVL 3

Author Closing Comment

by:timbraun
ID: 34960311
Thanks a lot! This will give me plenty ideas to chew on!
0
 
LVL 76

Expert Comment

by:arnold
ID: 34961053
Just to correct Slam69's last comment, the drive capacity will be 50% of cummulative hard drive capacity and not 25%.

The separation I have is the minimal.  If one has the resources then one can further offload/distribute the disk I/O.


0
 
LVL 25

Expert Comment

by:slam69
ID: 34964013
apologies yes arnold 50% is correct its been a long week, if you know anything about sharepoint take a look at my open question youll see why :))
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now