December 8, 2011 at 8:37 am
Hi All,
I have been trying to get more performance out of our SQL server. Here are the specs...
Proliant ML350 G5
Windows 2008 Standard
16gb of ram
Three 7200 RPM SCSI drives in a Raid5
SQL Server 2005 32 bit. It is using a
about 3.6 gbs of ram
Two Quad-Core 2.4 processors
GB Ethernet
Everything is installed to the C:\. Including the transaction log.
I've read that the Transaction log should be on a separate disk. So...I have a few questions about this.
Please give me your opinions
1. Would it have better performance putting the transaction log on a single drive or Raid1...assuming backups
were not a concern.
2. What type of performance gains could I expect to see?
3. Would this help improve performance when running large Crystal Reports or Queries?
Thanks all for your input
December 8, 2011 at 8:43 am
You shouldn't guess at this.
Where is your bottleneck? Fix that.
Where's your new bottleneck.
Fix that, rinse repeat.
How did you come up with the answer "my biggest problem is disks for the log files"?
December 8, 2011 at 8:50 am
Good points.
As for bottleneck, I don't know for sure that is the bottleneck. Months back I remember running performance
monitor and watched the counters. According to a few articles I read, there was certain counters I was suppose to
look at to see if I had a bottleneck. I don't remember what the counters were, but I remember having a read/write
problem. That's why I would like to know if moving my transaction log would yield performance gains.
December 8, 2011 at 8:54 am
That may help. But that's rather far down the list.
Counters are counters. They are only useful when compared to themselves when the system was running well.
Many (most) metric you'll find on the web are not really useful rules that you can just apply to your env.
December 8, 2011 at 9:06 am
So, in General, are you saying you should or shouldn't put the transaction log on a separate physical disk?
December 8, 2011 at 9:09 am
dale.schwabjr (12/8/2011)
So, in General, are you saying you should or shouldn't put the transaction log on a separate physical disk?
In general you should. As long as the backups are not on either of the data or log file drives.
That being said I still think you are half poking around rather than hitting the correct issue right on the head.
December 8, 2011 at 9:15 am
I agree. I am poking around. If I was to try to find performance bottlenecks, where would I start?
The main performance issue I can see is when running large reports (queries), the system locks up.
December 8, 2011 at 9:18 am
dale.schwabjr (12/8/2011)
I agree. I am poking around. If I was to try to find performance bottlenecks, where would I start?The main performance issue I can see is when running large reports (queries), the system locks up.
Then tune those queries. Post the actual execution plan if you need help with that.
December 8, 2011 at 9:50 am
Start with this. http://www.simple-talk.com/books/sql-books/troubleshooting-sql-server-a-guide-for-the-accidental-dba/
Guessing, or flailing around is going to take lots of time for little gain. It may be that the logs are a bottleneck, it may be that the queries are the problem, it may be something else, but you have to identify a problem before applying a solution.
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
December 8, 2011 at 11:11 am
Thanks all for your help thus far, troubleshooting the performance issues is one thing...and I get that.
Still have the questions regarding
1. Would it have better performance putting the transaction log on a single drive or Raid1...assuming backups
were not a concern.
2. What type of performance gains could I expect to see?
3. Would this help improve performance when running large Crystal Reports or Queries?
Any thoughts?
December 8, 2011 at 11:13 am
dale.schwabjr (12/8/2011)
Thanks all for your help thus far, troubleshooting the performance issues is one thing...and I get that.Still have the questions regarding
1. Would it have better performance putting the transaction log on a single drive or Raid1...assuming backups
were not a concern.
2. What type of performance gains could I expect to see?
3. Would this help improve performance when running large Crystal Reports or Queries?
Any thoughts?
1 - backups are never not a concern. They need to be safe, period.
2 - Some loss to some gain. Only 1 way to know => test
3 - None at all. they should NOT be using the t-logs in any way.
December 8, 2011 at 11:24 am
dale.schwabjr (12/8/2011)
1. Would it have better performance putting the transaction log on a single drive or Raid1...assuming backups were not a concern.
Maybe.
2. What type of performance gains could I expect to see?
Depends on the usage pattern of the database and whether the log was a bottleneck or not.
3. Would this help improve performance when running large Crystal Reports or Queries?
Unless those reports are doing strange things like updates or inserts, no.
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 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply