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?
Best Answer
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.