Sql-server – seeing high execution counts for the system stored procedure sys.sp_columns_managed

sql server

We are seeing high wait times during business hours on one of our databases. Looking at SolarWinds I am finding that the system stored procedure 'sys.sp_columns_managed' is being executed more than 300,000 times per hour (according to SolarWinds). Has anyone else ever experienced this? Does anyone know what can be done to stop this? Or, am I not seeing things correctly in SolarWinds?

Best Answer

This script below shows you the execution count of the stored procedures that are currently in your plan cache.

set transaction isolation level read uncommitted

SELECT DB_NAME(st.dbid) DBName
      ,OBJECT_SCHEMA_NAME(st.objectid,dbid) SchemaName
      ,OBJECT_NAME(st.objectid,dbid) StoredProcedure
      ,max(cp.usecounts) Execution_count
 FROM sys.dm_exec_cached_plans cp
         CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
 where DB_NAME(st.dbid) is not null and cp.objtype = 'proc'
   group by cp.plan_handle, DB_NAME(st.dbid),
            OBJECT_SCHEMA_NAME(objectid,st.dbid), 
   OBJECT_NAME(objectid,st.dbid) 
 order by max(cp.usecounts) desc

That would be an starting point, if you could see the procedure there and from which database it is fired.

Then have a look who and what is using that particular database\calling that procedure.

here you can find the code of the sys.sp_columns_managed stored procedure.

Related Solutions

Sql-server – SQL Server 2012 CPU spike due to LinkedServer – how to find the offending query

To answer the question: How to find the offending query:

Since the spikes in the graph you posted last for several minutes you have plenty of time to use the following method:

Download sysinternals process explorer

start process explorer and find the SQL Server process.
right click and select properties
look at the thread tab.
Sort on the CPU column and note the thread id (TID) that is consuming the most CPU.

Use this query and lookup the query that is currently being executed by that thread:

SELECT r.session_id, st.text, qp.query_plan
FROM sys.dm_os_threads AS ot
JOIN sys.dm_os_tasks AS t
ON t.worker_address = ot.worker_address
JOIN sys.dm_exec_requests AS r
ON t.session_id = r.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(r.plan_handle) AS qp
WHERE os_thread_id = <thread id>

SQL Server – sp_GetAppLock Slow Performance and Unusual Behaviour

Try adding SET_XACT_ABORT ON to the stored procedure to ensure the transaction is rolled back following termination due to an attention request. This is a best practice for procs with explicit transactions.

A scenario that could cause the symptoms you describe is that the proc execution timed out during execution due to blocking on the table. When a client timeout occurs, the client API cancels the proc by sending an attention event to stop execution. This cancels the executing proc but does not roll back the transaction if SET_XACT_ABORT OFF. The transaction will remain open until the connection is closed and reused (in the case of a connection pool), the connection is physically closed, or an explicit rollback or commit is issued on the same connection.

This is especially nasty with persistent (non-pooled) connections that don't properly clean up after an exception because all subsequent work performed on the connection is unwittingly done under the context of the open transaction.

Best Answer

Related Solutions

Sql-server – SQL Server 2012 CPU spike due to LinkedServer – how to find the offending query

SQL Server – sp_GetAppLock Slow Performance and Unusual Behaviour

Related Question