Warning: Constant WP_TEMP_DIR already defined in /var/www/html/blogs/joe/wp-config.php on line 93
Warning: Cannot modify header information - headers already sent by (output started at /var/www/html/blogs/joe/wp-config.php:93) in /var/www/html/blogs/joe/wp-includes/rest-api/class-wp-rest-server.php on line 1902
Warning: Cannot modify header information - headers already sent by (output started at /var/www/html/blogs/joe/wp-config.php:93) in /var/www/html/blogs/joe/wp-includes/rest-api/class-wp-rest-server.php on line 1902
Warning: Cannot modify header information - headers already sent by (output started at /var/www/html/blogs/joe/wp-config.php:93) in /var/www/html/blogs/joe/wp-includes/rest-api/class-wp-rest-server.php on line 1902
Warning: Cannot modify header information - headers already sent by (output started at /var/www/html/blogs/joe/wp-config.php:93) in /var/www/html/blogs/joe/wp-includes/rest-api/class-wp-rest-server.php on line 1902
Warning: Cannot modify header information - headers already sent by (output started at /var/www/html/blogs/joe/wp-config.php:93) in /var/www/html/blogs/joe/wp-includes/rest-api/class-wp-rest-server.php on line 1902
Warning: Cannot modify header information - headers already sent by (output started at /var/www/html/blogs/joe/wp-config.php:93) in /var/www/html/blogs/joe/wp-includes/rest-api/class-wp-rest-server.php on line 1902
Warning: Cannot modify header information - headers already sent by (output started at /var/www/html/blogs/joe/wp-config.php:93) in /var/www/html/blogs/joe/wp-includes/rest-api/class-wp-rest-server.php on line 1902
Warning: Cannot modify header information - headers already sent by (output started at /var/www/html/blogs/joe/wp-config.php:93) in /var/www/html/blogs/joe/wp-includes/rest-api/class-wp-rest-server.php on line 1902
{"id":490,"date":"2012-06-29T00:37:02","date_gmt":"2012-06-29T00:37:02","guid":{"rendered":"\/blogs\/joe\/post\/MSQL_DQ-and-PREEMPTIVE_COM_QUERYINTERFACEe28093Two-Sides-of-the-Same-Coin.aspx"},"modified":"2013-01-02T20:31:54","modified_gmt":"2013-01-03T04:31:54","slug":"msql_dq-and-preemptive_com_queryinterface-two-sides-of-the-same-coin","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/joe\/msql_dq-and-preemptive_com_queryinterface-two-sides-of-the-same-coin\/","title":{"rendered":"MSQL_DQ and PREEMPTIVE_COM_QUERYINTERFACE\u2013Two Sides of the Same Coin"},"content":{"rendered":"The MSQL_DQ wait type accumulates while waiting for a distributed query to complete and it is not necessarily indicative of an issue.<\/p>\n
If MSQL_DQ is one of your top wait types accumulated on your SQL Server instance, you may also see an associated PREEMPTIVE_COM_QUERYINTERFACE wait type. And actually, you may see that the accumulated values are identical between these two wait types. PREEMPTIVE_COM_QUERYINTERFACE basically represents accumulated wait time for linked server queries and remote operations as well.<\/p>\n
To illustrate, I\u2019ll execute the following query that captures total wait stats before and after and returns the delta (I could use XEvents to do this too \u2013 but this is an isolated system and I only have one distributed query being executed at a time). <\/p>\n
I\u2019ll execute a distributed query 50 times and we\u2019ll see what accumulates:<\/p>\n
-- \u201cBefore\u201d state of waits<\/span>\r\nSELECT<\/span> wait_type, waiting_tasks_count\r\nINTO<\/span> #before_waits\r\nFROM<\/span> sys.dm_os_wait_stats\r\nWHERE<\/span> wait_type IN<\/span> ('PREEMPTIVE_COM_QUERYINTERFACE'<\/span>,\r\n 'MSQL_DQ'<\/span>);\r\nGO<\/span>\r\n\r\n-- Distributed query executed 50 times<\/span>\r\nSELECT<\/span> 'x'<\/span>\r\nFROM<\/span> OPENQUERY<\/span> ( [JOSEPHSACK-PC\\AUGUSTUS] ,'SELECT TOP 1 o1.name\r\n FROM master.dbo.sysobjects AS o1'<\/span> );\r\nGO<\/span> 50\r\n\r\n\r\n-- \u201cAfter\u201d state of waits<\/span>\r\nSELECT<\/span> wait_type, waiting_tasks_count\r\nINTO<\/span> #after_waits\r\nFROM<\/span> sys.dm_os_wait_stats\r\nWHERE<\/span> wait_type IN<\/span> ('PREEMPTIVE_COM_QUERYINTERFACE'<\/span>,\r\n 'MSQL_DQ'<\/span>);\r\nGO<\/span>\r\n\r\nSELECT<\/span> a.wait_type, \r\n (a.waiting_tasks_count - b.waiting_tasks_count) AS<\/span> accumulated_waiting_tasks_count\r\nFROM<\/span> #before_waits AS<\/span> b\r\nINNER<\/span> JOIN<\/span> #after_waits AS<\/span> a ON<\/span>\r\n b.wait_type = a.wait_type;\r\nGO<\/span>\r\n\r\nDROP<\/span> TABLE<\/span> #before_waits;\r\nDROP<\/span> TABLE<\/span> #after_waits<\/pre>\n