THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Linchi Shea

Checking out SQL Server via empirical data points

Performance Impact of Querying SQL Server 2005 Scheduler-Related DMV's

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:

sys.dm_os_schedulers
sys.dm_os_workers
sys.dm_os_threads
sys.dm_os_tasks
sys.dm_os_waiting_tasks
sys.dm_os_ring_buffers

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
go
while 1=1
begin
   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'
end

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:

Transaction

Mix

 New Order    45%
 Payment    43%
 Order Status    4%
 Delivery    4%
 Stock Level    4%

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.

Published Thursday, January 18, 2007 11:03 AM by Linchi Shea
Filed under: ,

Attachment(s): SQLSchedulers.gif

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

No Comments

Leave a Comment

(required) 
(required) 
Submit

About Linchi Shea

Checking out SQL Server via empirical data points

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement