How SQL Server manages when a database is created in terms of I/O and disk usage?
One of the best features you have in the SQL Server is to create database data file (additional) on fly without having a slow performance affect on existing connections. But think about how SQL Server manages to use server threads for the data file that is used for the databases and how big disk queue length in terms of I/O bottleneck.
A simple explanation will be when the SQL processess is initiated to create a database file (.NDF or MDF/LDF) it will initiall allocates 0 bytes to the file on the operating system level, which is a golden rule of 1 thread per 1 file with relevant disk allocation structure. But behind the screen it will work out to create unique disk I/O process that we call as a thread in initializing the data file and this is where you will see the spikes in worker thread limit when monitoring with SYSMON. Say if you try to create 2 data files with your script then you will see the double of the processes time. Adding more files and properly aligning them with storage can increase the I/O performance for a variety of reasons but achieving new I/O threads per file is NOT one of them. SQL Server I/O is all about I/O response time and with variety of disk & I/O configurations as long as the response times are acceptable you can plan such changes during online hours.
With latest feature in SQL Server 2005 version this overhead reduces the impact of database creation because zero’s don’t have to be stamped in all bytes of a database file, only the log files. This reduces the gain from using multiple threads during database creation. As referred the asynchronous I/O process allowing any worker to issue an I/O requests regardless of the number and size of the database files or what scheduler is involved. Then comes to the important aspect of SQL Server usage of TEMPDB!
Tempdb is the database with the highest level of create and drop actions and under high stress the allocation pages, syscolumns and sysobjects can become bottlenecks. Operating System resources such as worker threads will also use tempdb like any other database, any worker can issue I/O to and from tempdb as needed. In this scenario the extensive usage of TEMPDB in SQL Server 2005 reduces contention with the ‘cached temp table’ feature and allocation contention skip ahead actions.
I have been referring the Technet Article on Further hardware configuration such as I/O affinity and disk queue length ....are very specific to serve resources and by default they do not care about database and data files, any task or work is initiated by the Task manager will have a queuing mechanism for issuing and completing I/O. A worker process on server determines it needs to perform an I/O and does all the basic work and it will hook the actual ReadFile or WriteFile call processes instead of issuing the I/O the worker puts the I/O request on a queue. The queue is serviced by the dedicated scheduler on the server.
When you see a spike in disk queue length as greater than 2, then it refers to the disk bottleneck on that server. More importantly you should checkover what other processes are running at the same time of this value spiked. SQL Server is designed to push disk queue lengths above 2 when it is appropriate, not all the times it refers that as a bottleneck as the underline engine async I/O to help maximize resource usage. Based on the this resource usage it will initiate an I/O request to the I/O subsystem and continue with other activity. Taking the same scenario on TEMPDB usage within SQL Server, as it does read ahead or tempdb spooling actions. As reference of disk queu length to get correct assumption of resources usage you should start looking at the Average Disk Seconds Per Transfer for the same I/O path by additional reference to SQL Server sysprocesses and waitstats for buffer based I/O wait information.