January 21, 2008 at 10:50 am
We have a SQL Server 2005 server that hosts two "small" applications. I saw small because that is what the vendor told us.
Anyway, the server was initially configured by the company that supports this particular application (part of the contract.) I ordered them a spiffy server with SQL Server 2005. Should be "PLENTY" according to them.
Any how, I started to get some calls regarding slowness, from the application connecting to the server/database to run reports. I started to dig a bit and low and behold, I am getting killed on physical disk performance. I mean terrible.
Digging further, I see that what they did was setup one RAID array, with two partitions on it; one for the OS and one for SQL data. I remember asking them specifically if we should stick more drives in there to split out the database and log files. I was reassured that this was such a small database app, it was not necessary. Never again will I listen like that.
So, as it stands right now, I have two disks, one array, with all the database and t-log files on it. As you can guess, read and write performance is terrible, which explains clearly why users are experiencing slowness and the client piece "freezing".
So here is the dilema.
I stuck two more drives into the server (only had space for two more drives unfortunately). 300gb, 15K SAS drives in a Dell 2950.
So what do I do?
I mean, I should move something over to this new array correct? Should it be t-logs or database files?
I could move some, but even then, something will still be left on the other array, which holds the OS files, master, msdb databases.
I should get a little bit better performance boost, but not sure what to expect even if I move some files over to the new array.
Anyone have suggestions?
BTW, one other thing that I noticed is that this particular application likes to grow the tempdb to 17.1gb. It steadily grew to that size, but has remained the same for awhile now. Something I wanted to throw in there as well.
Appreciate the help and suggestions.
Cheers,
Jason
EDIT: Was going to follow this URL on how to do the moves:
January 21, 2008 at 11:14 am
Hi Jason,
Based on the info you have I'd suggest to make a RAID 1 of the two new drives and put all the transaction logs including tempdb's log there.
However, more details would be helpful:
- is the application write intensive? or does the performance hit only show when reports are run?
- do you have re-indexing/database optimization jobs scheduled and running.
- is there memory pressure/extensive pagefile usage on the server? With pagefile on the same array this might be an issue.
- etc.
January 21, 2008 at 11:16 am
move the transaction logs to the new array.
when was last time all indexes were rebuilt? rebuilding indexes might solve all of your problems right there. was is Cache Hit Ratio and Page Life Expectancy? (under the sql server buffer manager in perfmon)
what is physical disk queue length at peak operating hours?
January 21, 2008 at 11:24 am
bbychkov (1/21/2008)
Hi Jason,Based on the info you have I'd suggest to make a RAID 1 of the two new drives and put all the transaction logs including tempdb's log there.
However, more details would be helpful:
- is the application write intensive? or does the performance hit only show when reports are run?
- do you have re-indexing/database optimization jobs scheduled and running.
- is there memory pressure/extensive pagefile usage on the server? With pagefile on the same array this might be an issue.
- etc.
Appreciate the help. I will answer as best as I can.
I did built a RAID 1 for the new drives.
Since I am getting my feet wet with SQL right now, I am still trying to gather as much information as I can.
If I had to guess, I would see that the performance hit is when reports are run. I ran perfmon and saw the % Disk Read just spike to 100%. It stayed there for a good 5 minutes. I am guessing that is when a report was run.
At this time, do not have a re-indexing/database optimization job scheduled or running. I need to set that up. Any tips here is great. 🙂
For the memory, the server itself has 4GB of memory. The PF usage is pretty constant at right around 2.17GB.
Granted, this is on a Windows Server 2003 Server. No PAE switches have been modified.
Appreciate the help.
January 21, 2008 at 11:26 am
russell (1/21/2008)
move the transaction logs to the new array.when was last time all indexes were rebuilt? rebuilding indexes might solve all of your problems right there. was is Cache Hit Ratio and Page Life Expectancy? (under the sql server buffer manager in perfmon)
what is physical disk queue length at peak operating hours?
Doing some perfmon right now:
Cache hit Ratio: 99.838 right now.
Page Life Expectancy: 87, but on a scale of 0.1000000
Thanks.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply