Slowdown of the SAN due to overusage of temporary tables

Tags:
iSCSI
RAID 10
SAN
SQL stored procedures
Stored Procedures
TempDBs
We make use of temporary tables when we develop stored procedures, but they also become very slow when there is a heavy workload. We have an ISCSI SAN running RAID 10, and our database, logs and tempdb (15 GB) are on the SAN. We think the SAN is overworked; the SQL Server shows no CPU. Should we move the tempdb, and perhaps the logs, off the SAN?
ASKED: July 1, 2008  8:23 PM
UPDATED: July 30, 2008  6:42 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.

No, you should not move the tempdb and logs off of the SAN. What you need to do is look at the SAN and see what sort of IO your disk is getting. How is disks are in the RAID 10 array? You may need to add some disks. What other things are sharing the disks that this LUN is on?

You can also try lowering the amount of cache the SAN uses for read cache which will then increase your write cache. Your write cache may be getting full, which should be the only time that your writes should ever actually be going to the disk.

You might want to check out a session I gave over the weekend about SAN configuration. You can find the Power Point <a href=”http://itknowledgeexchange.techtarget.com/sql-server/socal-code-camp-slide-decks-and-sample-code/”>here</a>.

———-
Do you have specific evidence that your SAN is the cause of your problems? While the storage subsystem is often a contributor to database performance problems, you’d really want to get hard data before doing anything. Also, performance problem or bottleneck can mean a lot of different things. Are you suffering from contention? fragmentation? IO saturation? insufficient throughput? Hotspotting? Perfmon is the best place to start followed closely by waitstats. There aren’t many hardware related perf issues that can’t be identified using the two.

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following