I manage a database server that supports a C#/VB.NET application and we've had a lot of CPU usage problems. I am using the following dmv query from Glenn Berry at SQLSkills:
SELECT TOP(50) DB_NAME(t.[dbid]) AS [Database Name], REPLACE(REPLACE(LEFT(t.[text], 255), CHAR(10),''), CHAR(13),'') AS [Short Query Text], qs.total_worker_time AS [Total Worker Time], qs.min_worker_time AS [Min Worker Time], qs.total_worker_time/qs.execution_count AS [Avg Worker Time], qs.max_worker_time AS [Max Worker Time], qs.min_elapsed_time AS [Min Elapsed Time], qs.total_elapsed_time/qs.execution_count AS [Avg Elapsed Time], qs.max_elapsed_time AS [Max Elapsed Time], qs.min_logical_reads AS [Min Logical Reads], qs.total_logical_reads/qs.execution_count AS [Avg Logical Reads], qs.max_logical_reads AS [Max Logical Reads], qs.execution_count AS [Execution Count], qs.creation_time AS [Creation Time] --,t.[text] AS [Query Text], qp.query_plan AS [Query Plan] -- uncomment out these columns if not copying results to Excel FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK) CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS t CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp ORDER BY qs.total_worker_time DESC OPTION (RECOMPILE);
and by a wide margin, the top 5 queries are all this sys.sp_primary_key_rowset procedure. It looks like it's not documented in SQL Server's documentation, but some research tells me that it might have something to do with ADO and the application needing to find out more info about the table before running a query? I can't seem to find any good info on this proc.
What causes this, how can I determine what queries are causing it, and most importantly, can we make it run less often? It doesn't look like it's that expensive when it runs, only 100ms on average, but it's running 120+ times a minute.