0

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.

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 happen to represent one of those: SQL Sentry. If you'd like further information, you can contact us.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.