Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Often times we all hear the same thing. The recommendations on where to put OLAP/OLTP databases, where to put the tempdb, where to put transaction logs...etc.

But say you are in an environment where the physical definition of the storage is a mystery to you. In other words, sure you can see logical drives, and you can give the system admin a call and ask for more space, but you never really know what is a different physical drive (say there's a SAN or a NAS behind the scenes). If you don't know this for fact, how can you follow best practices by putting, say, the heavily hit tempdb database on a different physical drive?

What's the best practice on this one?

share|improve this question
1  
I would recommend reading Brent Ozar's series regarding SAN storage: SAN Storage Best Practices for SQL Server. It will help you shed some light. –  Marian Aug 19 '11 at 7:24
add comment

2 Answers

up vote 3 down vote accepted

What I would do is to benchmark the performance before and after the changes. There should be a performance gain after tempdb is moved to another drive. Use DMVs like sys.dm_io_virtual_file_stats to see the read and write wait times for the DB files.

Use the perfmon physical disk counters: Avg. Disk sec/Read, Avg. Disk sec/Write, Disk Reads/sec, Disk Writes/sec. The change in these metrics will let you know if the disks are physically separate or are just the same disks which are logically separated.

Want a third party disk benchmarking tool for your disks, so that the SAN admins cannot accuse SQL server of its results? Use SQLIO. Although there is SQL in the name, this tool does not require SQL server to run and is not related to SQL server.

This is a good question, and I look forward to see the other answers.

share|improve this answer
add comment

Slightly orthogonal answer...

Try and use local disks for tempdb and leave user DBs on the SAN/NAS. This doesn't address the issues you have with the "black box" of the SAN but it does allow you to decouple tempdb from the network. YMMV with clusters.

One thing I've found too is that you have to have high volumes for it to matter too much assuming your SAN admins aren't complete muppets/cheapskates.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.