Community
- Command Central
- MVP Program
- Monthly Mission
- Blogs
- Groups
- Events
- Media Vault
Products
- Observability
- Network Management
- Application Management
- IT Security
- IT Service Management
- System Management
- Database Management
Content Exchange
- SolarWinds Platform
- Server & Application Monitor
- Database Performance Analyzer
- Server Configuration Monitor
- Network Performance Monitor
- Network Configuration Manager
- SQL Sentry
- Web Help Desk
Free Tools & Trials
Store

Recent Expensive Query Is Blocking Other Queries

Does anybody know what's calling the query below? I've had perfromance issues ever since upgrading. Executing sp_who2 from SQL Server Management Studio will show several commands blocked by a select query. If I run DBCC INPUTBUFFER on that SPID, that query always shows up. It also shows up in SQL Server Management Studio's Activity Monitor, under "Recent Expensive Queries".

(@serverName nvarchar(3))SELECT n.NodeID, n.IP_Address, n.Status, n.StatusLED, n.UnManaged, n.UnManageFrom, n.UnManageUntil, n.[External], n.PollInterval, n.EngineID, n.ObjectSubType, n.AgentPort, n.SNMPVersion, n.Community, n.StatCollection, n.RediscoveryInterval, n.DynamicIP, n.DNS, n.SysName, n.Caption, COALESCE(NULLIF(n.SNMPV3AuthMethod,''), 'None') AS SNMPV3AuthMethod, COALESCE(NULLIF(n.SNMPV3PrivMethod,''), 'None') AS SNMPV3PrivMethod, n.SNMPV3PrivKey, n.SNMPV3AuthKey, n.SNMPV3Username, n.SNMPV3Context, n.SNMPV3PrivKeyIsPwd, n.SNMPV3AuthKeyIsPwd, n.RWCommunity, n.EntityType, e.ServerName FROM dbo.Nodes n LEFT JOIN dbo.Engines e ON n.EngineID = e.EngineID WHERE e.ServerName=@serverName

Find more posts tagged with

recent_expensive_query_is_blocking_other_queries

Accepted answers

All comments

david.templeton

More info:

8100 nodes

3 engines

sp_who2 shows program name as .Net SqlClient Data Provider

mezdem

what version are you on? I cannot find that sql code in v 10.1.1..

maybe it is a custom report that you have created..

*Most sessions from orion will be via .Net SqlClient Data Provider

david.templeton

I'm on v10.1.1. None of the stored procedures in SQL Server contain that text, and the .Net SqlClient Data Provider made it sound like it was coming from some code.

netlogix

I formated that up a bit:
SELECT n.NodeID, n.IP_Address, n.Status, n.StatusLED, n.UnManaged, n.UnManageFrom, n.UnManageUntil,
        n.[External], n.PollInterval, n.EngineID,   n.ObjectSubType, n.AgentPort, n.SNMPVersion, n.Community,
        n.StatCollection, n.RediscoveryInterval, n.DynamicIP, n.DNS, n.SysName, n.Caption,
        COALESCE(NULLIF(n.SNMPV3AuthMethod,''), 'None') AS SNMPV3AuthMethod,
        COALESCE(NULLIF(n.SNMPV3PrivMethod,''),'None') AS SNMPV3PrivMethod, n.SNMPV3PrivKey, n.SNMPV3AuthKey,
        n.SNMPV3Username, n.SNMPV3Context, n.SNMPV3PrivKeyIsPwd, n.SNMPV3AuthKeyIsPwd, n.RWCommunity,
        n.EntityType, e.ServerName
FROM dbo.Nodes n
    LEFT JOIN dbo.Engines e ON n.EngineID = e.EngineID
WHERE e.ServerName=@serverName

To me it looks like the query for the polling engine to find what it is assigned to poll. Maybe your indexes are fragmented and you just need to re-index.

This is the script I use to re-index all databases (works with 2005 & 2008):
DECLARE @Database VARCHAR(255)
DECLARE @Table VARCHAR(255)
DECLARE @cmd NVARCHAR(500)
DECLARE @fillfactor INT

SET @fillfactor = 90

DECLARE DatabaseCursor CURSOR FOR
SELECT name FROM master.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb','distrbution')
ORDER BY 1

OPEN DatabaseCursor

FETCH NEXT FROM DatabaseCursor INTO @Database
@FETCH_STATUS = 0
BEGIN

   SET @cmd = 'DECLARE TableCursor CURSOR FOR SELECT table_catalog + ''.'' + table_schema + ''.'' + table_name as tableName
                    FROM ' + @Database + '.INFORMATION_SCHEMA.TABLES WHERE table_type = ''BASE TABLE'''

   -- create table cursor
   EXEC (@cmd)
   OPEN TableCursor
   FETCH NEXT FROM TableCursor INTO @Table
@FETCH_STATUS = 0
   BEGIN
       SET @cmd = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'
       EXEC (@cmd)
       FETCH NEXT FROM TableCursor INTO @Table
   END
   CLOSE TableCursor
   DEALLOCATE TableCursor
   FETCH NEXT FROM DatabaseCursor INTO @Database
END
CLOSE DatabaseCursor
DEALLOCATE DatabaseCursor