1

I know that if we separate databases on separate drive configured of separate disk group (SAN LUN), it helps to improve disk IOPs.

Does separation of databases to different drives help to improve IO performance on the database? If not why, when as the URL says, Windows has it's own indexing, and installed application may or may not have it's own indexing. SQL server has got its own.

Then why locating database to separate drive on same storage group (SAN LUN) wouldn't help to improve performance? The OS will have separate and smaller index for each drive, which should help to improve disk IOPs.

1 Answer 1

2

For starter, let's not confuse Windows disk indexing with SQL Server's (or any database's, really) indexing.

It's true that Windows does create an index of disk contents. That feature, however, is aimed to help for searching for quite limited set of things based on common use cases. Typically indexing disk helps one to find music, pictures and documents based on either metadata (type, date, keywords) or by leveraging application specific features such as Outlook's ability to stretch indexing to mail contents. There is little if any way to configure Windows file indexing. The index helps users to find actual files they are interested in.

In contrast, an index in SQL Server (and in databases in general) have a different philisophy. An index is designed for specific use case based on what data there is and how it's being queried. For covering indexes, the answer to a query is just what was read from the index itself. Of course, sometimes the whole row is needed. There are other uses too, such as foreign keys, materialised indexes and others. This is pretty different from the Windows file indexing results that only help you to find a file of interest.

Now, why does separating database files to different LUNs in a SAN help with performance? It doesn't have anyting to do with Windows file indexing. The performance advantage comes from ability to spread out the workload to multiple devices. MPIO in a SAN makes it possible to run parallel IO by dividing it to multiple channels, and that often scales in pretty nice a way. A SAN storage device has multiple disks (SSD, HDD or both) which increase performance when the workload is spread to multiple devices. When the disk access happens in parallel, more work is done at the same time than when it's being queued in a serial process.

If the database files are located on a single physical disk, be it on SAN or local computer, there isn't as much possibilies for parallel work as when the data is spread out.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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