In their excellent whitepaper "How to Diagnose and Correct Errors 17883, 17884, 17887, and 17888", Bob Dorr and Sameer Tejani put forward the following warning regarding querying about the activity of the SQL Server 2005 schedulers:
|"Warning: These dynamic management views (DMVs) are powerful but they require synchronized access to the internal data structures and lists. Frequent query activity against these DMVs can impact overall system performance and some queries can return large result sets. Access to the information is thread-safe but it is recommended that you query these system DMVs only when necessary."|
The DMV's in question are:
It is conceivable that you may take snapshots of these DMV's at a frequency of once every four seconds. Would that be too frequent? Would that lead to any ill performance effect as Bob and Sameer have warned? I had absolutely no idea what the answer might be.
So I conducted some tests to get a feel for it. The tests were done in three steps. First, I ran a series of I/O bound workloads to establish a performance baseline in terms of transactions per second at different load levels. Then, I kicked off the following script with osql.exe to snapshot the DMV's in an infinite loop once every four seconds:
set nocount on
SELECT * FROM sys.dm_os_schedulers
SELECT * FROM sys.dm_os_workers
SELECT * FROM sys.dm_os_threads
SELECT * FROM sys.dm_os_tasks
SELECT * FROM sys.dm_os_waiting_tasks
SELECT * FROM sys.dm_os_ring_buffers
waitfor delay '00:00:04'
The output of osql.exe was thrown away to a nul device.
Finally, I re-ran the exact test workloads after the test database was restored to the exact pristine state at the beginning of the first step, and while the DMV's were being repeatedly queried with the above script. Combining the results from this step with those from the first step, i.e. the baseline, gives me the following chart for a comparison:
As you can see from the chart, in these tests no terrible performance impact was found when the SQL2005 scheduler-related DMV's were queried at a 4-second interval while the I/O test workloads were run at various load levels from 5 users to 600 users.
Note that the test workloads were TPC-C like with all the five TPC-C transactions configured at the following standard mix ratio:
| New Order
| Order Status
| Stock Level
Each user was configured to wait for 50 milliseconds before submitting the next transaction.
Caution Do not try to extrapolate too much from the results. It is possible that the performance impact may be quite different with a different workload. And it is also possible that increasing the frequency of querying the DMV's may lead to a different result. These results simply give you some data points. They are not comprehensive.
Note The tests were conducted on 32-bit SQL2005 SP1 with 32-bit Windows Server 2005 SP1, running on a DL585 with four AMD Opteron processors and 16GB of physical memory, 12GBof which was given to the SQL Server instance. The test database was scaled for 1000 TPC-C warehouses and was about 150GB in size.