Sunday, February 10, 2013

MSSQL - Drive Letters vs Mounted Volumes?

Ok, time to gather info for updating best practices.

I had previously assumed that MSSQL behavior with LUNs provisioned as Windows drive letters and LUNs provisioned as mounted volumes under a drive letter was the same.

I'd heard of clustering challenges if the mounted volume was not within the directory tree of a drive letter.  And I know it can present some challenges for monitoring IO utilization and performance.  But its such a widespread practice and general performance testing has not yielded any specific differences.

However, dbcc checkdb was not included in our performance testing.  And, this kb article points out a potential difference in dbcc checkdb behavior with drive letters vs mounted partitions.

A single database with a single filegroup, and 8 database files with each file on its own drive letter.  By default, dbcc checkdb will build 8 lists of page reads.

Same situation with the 8 database files each on their own mounted volume, all within the directory tree of a single drive letter.  By default dbcc checkdb will build only one list for page reads, because there is only 1 drive letter.

Enter trace flag 2549.  With this trace flag, dbcc checkdb will assume each database file is on its own LUN, and build 8 page lists even though there's only one drive letter.

Be careful - if you've got several database files for a database on a single LUN (I consider that contrary to best practices in the first place), trace flag 2549 could lead to overtaxing IO resources.  But if you adhere to a one database file per one LUN model, trace flag 2549 can make dbcc checkdb behavior the same for drive letters or mounted volumes.

I wonder if there's anything else out there that mounted volume setups need to be aware of to have the same behavior as a similar system with drive letters?

No comments:

Post a Comment