December 15, 2006 at 1:59 pm
Ok, here is the problem.
Our Prod system is having Performance problems.
what I know:
All drives are RAID 5 (ON an EMC SAN).
We use TempDB a LOT (I know, I know...we shouldnt)
TempDB is Placed on the same logic drive as the Data
So....I recommend that we switch to RAID 1+0 (we are not a broke company)
I recommend moving TempDB to it's own logical drive RAID 0, 1 or 1+0 (Not 5)
the hardware guys say "This is not the problem"
so.....I need Statistics to PROVE that my hunch is correct.
how do I gather stats to PROVE that moving TempDB and switching to RAID 1+0 will improve perf by more than just a few % points...?
we are not seeing massive Disk Queueing at this point. So what other stats do I grab to prove my point ?
thanks
Greg J
Gregory A Jackson MBA, CSM
December 15, 2006 at 2:59 pm
How have you determined that the location of tempdb is the issue ?
Maybe it's how you're using it versus where it's physically located ? Do you have long-running SQL that builds ##emp tables ? If so, what form are they in ?
Are they:
SELECT * INTO #TempTable
FROM SomeOtherTable
Or are they:
CREATE TABLE #TempTable ( {columnlist} )
INSERT INTO #TempTable
SELECT {columnlist} FROM SomeOtherTable
Also, if you're building large #Temp tables and joining them to other data, are you indexing the #Temp tables ?
December 15, 2006 at 3:06 pm
well...I dont "Know" that the location of TempDB is the problem.
I "Suspect" that it is.
I want to be able to definitively prove if my hunch is right or wrong.
The fact that TempDB is located where the data resides is a common issue that can cause contention.
But...I dont see disk Queueing. So, If I dont see disk queueing, does that mean I'm barking up the wrong tree ?
yeah we create temp tables specifically and then Insert Into Them (the preferred way).
Yes the Temp Tables have indexes when it makes sense.
When we can, we use Table Varialbes instead. But we cant do t his in all cases....
GAJ
Gregory A Jackson MBA, CSM
December 16, 2006 at 12:01 am
What is your build number?
Take a look Concurrency enhancements for the tempdb database article applies to sp3...
http://support.microsoft.com/kb/328551
MohammedU
Microsoft SQL Server MVP
December 18, 2006 at 2:01 am
Tricky things SAN's and tempdb !! OK, you'll never get decent disk queue stats froma SAN try using disk i/o completion time, this will show if the SAN is an issue ( which in a number of scenarios I've dealt with has been the case ) I could write a small book on how a SAN can screw your database performance and how the vendors and your SAN guys will "prove" otherwise. fn_filestats is your friend for collecting information about tempdb - use that in combination to see which database on your server has the highest trans/sec, this is an indication of tempdb probs if tempdb has greater than 50% of all trans.
if your read/write ratio is greater than, say, 85/15 ( >15 writes ) raid 5 is bad news - raid 5 is ok for reads but absolute pants for writes, including on a SAN. Finally get a physical map of the drives and partiitioning for the SAN, if you don't have dedicated spindles then find out what you share with etc. etc. It's a long hard slog and I talk from experience, which is why I got SNIA certified. ( http://www.snia.org )
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
December 19, 2006 at 10:47 am
this is great info. Thanks for the reply.
GAJ
Gregory A Jackson MBA, CSM
December 21, 2006 at 3:52 pm
Sharing spindles with other apps is a huge killer on san's and is much more common on the ones that do virtualization you tend to get hot spots and you are always doing random I/O.
performance increase between raid 5 and raid 10 is simple math 4 iops for a write in raid 5 vs. 2 iops in raid 10.
Check out SQL SErver 2000 Performance Tuning Techincal Referince from ms press around page 40 it gets into some decent detail on disk and I/O with diffrent raid levels.
Also, If you move tempdb add a data file for every proccessing core you have if you are killing tempdb now it will help some with the creation and deletion of objects in temp and widen the I/O path alittle bit.
Wes
December 22, 2006 at 5:23 am
I don't know enough about hardware issues, especially not about SAN, but I know that we had precisely the same problem. Started with Raid 5 and tempdb on the same drive as data. Performance : terrible. Changing from RAID 5 to 10 brought a drastical improvement; separating TEMPDB on another drive brought less, but still considerable improvement.
I think both your suggestions are very probably to the point and although a lot depends on what types of processes run on your server, how many concurrent users, read/write ratio etc., such change should always help. BTW, our LOG file also resides on a different physical drive than the data file.
December 22, 2006 at 8:58 am
Thanks for the reply.
Our system is very write intensive.
Even our Reporting sprocs do tons of writes to tempTables before writing out final results.
we are working feverishly on removing all temp table usage, all User Defined Functions, etc.
we are making progress with improving performance on the T-SQL\app side but I still firmly believe that the smoking gun is the RAID 5 and File Placement issue.
I also found that SQL server is configured to use Named Pipes instead of TCP\IP (go figure).
Now I just have to battle the politics to get all the hardware\networking issue fixed. I'm only allowed to dabble on the app layer.
cheers,
GAJ
Gregory A Jackson MBA, CSM
December 22, 2006 at 11:08 am
been there, book, T shirt, baseball cap, etc. etc. kick raid 5 into touch
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply