Mrdenny 
46795 pts. | Jul 1 2008 11:00PM GMT
Check out my SQL Server blog “SQL Server with Mr Denny” for more SQL Server information.
Q:
Slowdown of the SAN due to overusage of temporary tables
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:
Jul 1 2008 8:23 PM GMT
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
A:
RATE THIS ANSWER
0
Click to Vote:
0
Click to Vote:
- 0
0
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 here.
----------
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.
