Tuesday 20 October 2009

Demystified : How SQL Server uses threads

Data files & threads - Clarification (From Steve Jones' blog).
"The bottom line is that SQL Server uses a thread for each unique disk drive, NOT files. In SQL 2000, you can fool the system if the disk drives are volumes on the same physical disk. However in SQL 2005 the system checks to see if these are the same physical disk.

There are some caveats, but if you hear this rumor, dispel it. Let people know that multiple file groups (or files) only help if you have different physical I/O paths and drives."
http://www.sqlservercentral.com/blogs/steve_jones/archive/2009/10/13/sql-server-legend-data-files-and-threads.aspx

Therefore performance is only a reason for partitioning when separate disks are involved.
Obviously, partitioning is still worth the effort if you wish to reduce backup sizes & times by implementing a strategy that includes it.

Also, if data is partitioned and then your boss stumps up for another drive, you're ready to move some partitions!

No comments: