monitoring sql execution perfomance of an application that makes use of SP_prepare

Question

we have an application that makes heavy use of sp_prepare and sp_execute and we would like to start monitoring the execution plans of the sql. but this is made difficult by the use of sp_prepare

much of the reading i have done suggests that sp_prepare is inefficient, is this correct? also how would i monitor such database activity?

we are looking to monitor

• potential missing indexes
• Longest Running Queries

if you need any more information please let me know.

Answer 1

Both missing indexes and long-running queries are available in DMVs, with the caveat that DMVs can get refreshed on some events, such as service restart, certain sp_configure changes, etc.

Here's a missing index query from Bart Duncan's blog post, Are you using SQL's Missing Index DMVs?:

SELECT
  migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) 
    * (migs.user_seeks + migs.user_scans) AS improvement_measure,
  'CREATE INDEX [missing_index_' 
    + CONVERT (varchar, mig.index_group_handle) + '_' 
    + CONVERT (varchar, mid.index_handle)
  + '_' + LEFT (PARSENAME(mid.statement, 1), 32) + ']'
  + ' ON ' + mid.statement
  + ' (' + ISNULL (mid.equality_columns,'')
    + CASE WHEN mid.equality_columns IS NOT NULL 
    AND mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END
    + ISNULL (mid.inequality_columns, '')
  + ')'
  + ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement,
  migs.*, mid.database_id, mid.[object_id]
FROM sys.dm_db_missing_index_groups mig
INNER JOIN sys.dm_db_missing_index_group_stats migs 
  ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid 
  ON mig.index_handle = mid.index_handle
WHERE migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) 
  * (migs.user_seeks + migs.user_scans) > 10
ORDER BY migs.avg_total_user_cost * migs.avg_user_impact 
  * (migs.user_seeks + migs.user_scans) DESC

I would change about 20 things in that query, but it's a start. This will show you the indexes you should investigate creating - but please don't just create all of the indexes suggested. I've blogged about why you don't want to do that.

And here is a query from the sys.dm_exec_query_stats documentation:

SELECT TOP 5 query_stats.query_hash AS "Query Hash", 
  SUM(query_stats.total_worker_time) / SUM(query_stats.execution_count) AS "Avg CPU Time",
  MIN(query_stats.statement_text) AS "Statement Text"
FROM 
    (SELECT QS.*, 
    SUBSTRING(ST.text, (QS.statement_start_offset/2) + 1,
    ((CASE statement_end_offset 
        WHEN -1 THEN DATALENGTH(ST.text)
        ELSE QS.statement_end_offset END 
            - QS.statement_start_offset)/2) + 1) AS statement_text
     FROM sys.dm_exec_query_stats AS QS
     CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) as ST) as query_stats
GROUP BY query_stats.query_hash
ORDER BY 2 DESC;

Also some bad habits here, but shrug. This will collect the information about your longest-running queries in aggregate. If you want to know about individual instances of long-running queries, you'll have to capture those yourself using a server-side trace with a duration filter, or extended events, or a 3rd party monitoring product (I'm biased because I worked there for nearly a decade).