Post reply

Capture the required stored stored procedures to execute

  • August 2, 2018 at 9:11 am

    #374115

    Dear All,

    I'm trying to create a query which will recompile stored procedures which take longto execute and here is the query I 'm using, I need to know how to capture the actual stored procedures as at the moment I can only get the database which they are coming from:

    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

    SELECT TOP 100
    qs.execution_count AS [Runs]
    , (qs.total_worker_time - qs.last_worker_time) /
    (qs.execution_count - 1) AS [Avg time]
    , qs.last_worker_time AS [Last time]
    , (qs.last_worker_time - ((qs.total_worker_time - qs.last_worker_time)
    / (qs.execution_count - 1))) AS [Time Deviation]
    , CASE WHEN qs.last_worker_time = 0
    THEN 100
    ELSE (qs.last_worker_time - ((qs.total_worker_time -
    qs.last_worker_time) / (qs.execution_count - 1))) * 100
    END
    / (((qs.total_worker_time - qs.last_worker_time)
    / (qs.execution_count - 1))) AS [% Time Deviation]
    , qs.last_logical_reads + qs.last_logical_writes
    + qs.last_physical_reads AS [Last IO]
    , ((qs.total_logical_reads + qs.total_logical_writes +
    qs.total_physical_reads) - (qs.last_logical_reads +
    qs.last_logical_writes + qs.last_physical_reads))
    / (qs.execution_count - 1) AS [Avg IO]
    , SUBSTRING (qt.text,(qs.statement_start_offset/2) + 1,
    ((CASE WHEN qs.statement_end_offset = -1
    THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
    ELSE qs.statement_end_offset
    END - qs.statement_start_offset)/2) + 1) AS [Individual Query]
    , qt.text AS [Parent Query]
    , DB_NAME(qt.dbid) AS [DatabaseName]
    INTO #SlowRunningQueries
    FROM sys.dm_exec_query_stats qs
    CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) qt
    WHERE qs.execution_count > 1
    AND qs.total_worker_time != qs.last_worker_time
    ORDER BY [% Time Deviation] DESC

    SELECT TOP 100 [Runs]
    , [Avg time]
    , [Last time]
    , [Time Deviation]
    , [% Time Deviation]
    , [Last IO]
    , [Avg IO]
    , [Last IO] - [Avg IO] AS [IO Deviation]
    , CASE WHEN [Avg IO] = 0
    THEN 0
    ELSE ([Last IO]- [Avg IO]) * 100 / [Avg IO]
    END AS [% IO Deviation]
    , [Individual Query]
    , [Parent Query]
    , [DatabaseName]
    INTO  #SlowRunningQueriesByIO
    FROM #SlowRunningQueries
    ORDER BY [% Time Deviation] DESC

    SELECT TOP 100
    [Runs]
    , [Avg time]
    , [Last time]
    , [Time Deviation]
    , [% Time Deviation]
    , [Last IO]
    , [Avg IO]
    , [IO Deviation]
    , [% IO Deviation]
    , [Impedance] = [% Time Deviation] - [% IO Deviation]
    , [Individual Query]
    , [Parent Query]
    , [DatabaseName]
    INTO #QueriesRunningSlowerThanNormal
    FROM #SlowQueriesByIO
    WHERE [% Time Deviation] - [% IO Deviation] > 20
    ORDER BY [Impedance] DESC

    SELECT DISTINCT
    ' EXEC sp_recompile' + ''+ '[' + [DatabaseName] + ']'  AS recompileRoutineSQL
    INTO #RecompileQuery
    FROM #QueriesRunningSlowerThanNormal 
    WHERE [DatabaseName] NOT IN ('master', 'msdb', 'model','tempdb', 'distribution')

    DECLARE @RecompilationSQL NVARCHAR(MAX)
    SET @RecompilationSQL = ''

    SELECT @RecompilationSQL = @RecompilationSQL
    + recompileRoutineSQL + CHAR(10)
    FROM #RecompileQuery
    WHERE recompileRoutineSQL IS NOT NULL

    DECLARE @StartOffset INT
    DECLARE @Length INT
    SET @StartOffset = 0
    SET @Length = 4000

    WHILE (@StartOffset < LEN(@RecompilationSQL))
    BEGIN
    PRINT SUBSTRING(@RecompilationSQL, @StartOffset, @Length)
    SET @StartOffset = @StartOffset + @Length
    END

    PRINT SUBSTRING(@RecompilationSQL, @StartOffset, @Length)

    EXECUTE sp_executesql @RecompilationSQL

    DROP TABLE #SlowRunningQueries
    DROP TABLE #SlowRunningQueriesByIO
    DROP TABLE #QueriesRunningSlowerThanNormal
    DROP TABLE #RecompileQuery

    Thank you in advance

  • August 2, 2018 at 9:24 am

    #2000018

    If it's just stored procedures you are trying to find performance problems with. I use this stored procedure, then execute it.
    It lists all the stored procedures with how many times they've been called, how much CPU, I/O and elapsed time. It's very useful for diagnosing performance problems.
    IF OBJECT_ID('dbo.INFOExecutionStats','P') IS NULL BEGIN
      EXEC ('CREATE PROCEDURE [dbo].[INFOExecutionStats] AS BEGIN SELECT 1 END')
    END
    GO
    -- **********************************************************************
    -- PROCEDURE INFOExecutionStats
    -- Description:
    -- Sample Call: EXEC [INFOExecutionStats] @Database='myDatabase'
    -- Author: Jonathan Roberts
    -- **********************************************************************
    ALTER PROCEDURE [dbo].[INFOExecutionStats]
    (
      @Database sysname = '%'
    )
    AS
    BEGIN

      -- Do not lock anything, and do not get held up by any locks.
      SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

      -- *************************************************************
      -- Stored procedure execution count
      -- *************************************************************
      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'
       AND DB_NAME(st.dbid) LIKE @Database
      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

      -- *************************************************************
      -- CPU Time
      -- *************************************************************
      SELECT DB_NAME(st.dbid)             DBName,
        OBJECT_SCHEMA_NAME(st.objectid,dbid)      SchemaName,
        OBJECT_NAME(st.objectid,dbid)        StoredProcedure,
        Max(cp.usecounts)            Execution_count,
        Sum(qs.total_worker_time)          total_cpu_time,
        Sum(qs.total_worker_time)/(Max(cp.usecounts) * 1.0) avg_cpu_time
      FROM sys.dm_exec_cached_plans  cp
      INNER JOIN sys.dm_exec_query_stats qs
         ON cp.plan_handle = qs.plan_handle
      CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
      WHERE DB_NAME(st.dbid) IS NOT NULL
       AND cp.objtype = 'proc'
       AND DB_NAME(st.dbid) LIKE @Database
      GROUP BY DB_NAME(st.dbid),OBJECT_SCHEMA_NAME(objectid,st.dbid),
         OBJECT_NAME(objectid,st.dbid)
      ORDER BY Sum(qs.total_worker_time) DESC

      -- *************************************************************
      -- I/O File
      -- *************************************************************
      SELECT DB_NAME(st.dbid)               DBName,
        OBJECT_SCHEMA_NAME(objectid,st.dbid)         SchemaName,
        OBJECT_NAME(objectid,st.dbid)           StoredProcedure,
        Max(cp.usecounts)               execution_count,
        Sum(qs.total_physical_reads + qs.total_logical_reads + qs.total_logical_writes) total_IO,
        Sum(qs.total_physical_reads + qs.total_logical_reads + qs.total_logical_writes) / (Max(cp.usecounts)) avg_total_IO,
        Sum(qs.total_physical_reads)           total_physical_reads,
        Sum(qs.total_physical_reads)/(Max(cp.usecounts) * 1.0)   avg_physical_read  ,
        Sum(qs.total_logical_reads)            total_logical_reads,
        Sum(qs.total_logical_reads)/(Max(cp.usecounts) * 1.0)   avg_logical_read ,
        Sum(qs.total_logical_writes)           total_logical_writes,
        Sum(qs.total_logical_writes)/(Max(cp.usecounts) * 1.0)   avg_logical_writes
      FROM sys.dm_exec_query_stats qs
      CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) st
      INNER JOIN sys.dm_exec_cached_plans cp
         ON qs.plan_handle = cp.plan_handle
      WHERE DB_NAME(st.dbid) IS NOT NULL
       AND cp.objtype = 'proc'
       AND DB_NAME(st.dbid) LIKE @Database
      GROUP BY DB_NAME(st.dbid),
         OBJECT_SCHEMA_NAME(objectid,st.dbid),
         OBJECT_NAME(objectid,st.dbid)
      ORDER BY Sum(qs.total_physical_reads + qs.total_logical_reads + qs.total_logical_writes) DESC

      -- *************************************************************
      -- Time to execute
      -- *************************************************************
      SELECT DB_NAME(st.dbid)           DBName,
        OBJECT_SCHEMA_NAME(objectid,st.dbid)     SchemaName,
        OBJECT_NAME(objectid,st.dbid)       StoredProcedure,
        Max(cp.usecounts)           execution_count,
        Sum(qs.total_elapsed_time)        total_elapsed_time,
        Sum(qs.total_elapsed_time)/Max(cp.usecounts)  avg_elapsed_time
      FROM sys.dm_exec_query_stats qs
      CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) st
      INNER JOIN sys.dm_exec_cached_plans cp
         ON qs.plan_handle = cp.plan_handle
      WHERE DB_NAME(st.dbid) IS NOT NULL
       AND cp.objtype = 'proc'
       AND DB_NAME(st.dbid) LIKE @Database
      GROUP BY DB_NAME(st.dbid),
         OBJECT_SCHEMA_NAME(objectid,st.dbid),
         OBJECT_NAME(objectid,st.dbid)
      ORDER BY Sum(qs.total_elapsed_time) DESC

    END
    GO

  • August 2, 2018 at 10:46 am

    #2000033

    Since in your first query where you're creating #SlowRunningQueries you already join to sys.dm_exec_sql_text, you can get most of the objects by adding these 2 lines to your SELECT clause:
    , OBJECT_SCHEMA_NAME(qt.objectid, qt.dbid) AS [ObjectSchema]
    , OBJECT_NAME(qt.objectid, qt.dbid) AS [ObjectName]
    An alternative, although it's a bit slower, is to use sys.dm_exec_query_plan which also has objectid, dbid in it.

  • August 3, 2018 at 6:05 am

    #2000146

    Jonathan AC Roberts - Thursday, August 2, 2018 9:24 AM

    If it's just stored procedures you are trying to find performance problems with. I use this stored procedure, then execute it.
    It lists all the stored procedures with how many times they've been called, how much CPU, I/O and elapsed time. It's very useful for diagnosing performance problems.
    IF OBJECT_ID('dbo.INFOExecutionStats','P') IS NULL BEGIN
      EXEC ('CREATE PROCEDURE [dbo].[INFOExecutionStats] AS BEGIN SELECT 1 END')
    END
    GO
    -- **********************************************************************
    -- PROCEDURE INFOExecutionStats
    -- Description:
    -- Sample Call: EXEC [INFOExecutionStats] @Database='myDatabase'
    -- Author: Jonathan Roberts
    -- **********************************************************************
    ALTER PROCEDURE [dbo].[INFOExecutionStats]
    (
      @Database sysname = '%'
    )
    AS
    BEGIN

      -- Do not lock anything, and do not get held up by any locks.
      SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

      -- *************************************************************
      -- Stored procedure execution count
      -- *************************************************************
      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'
       AND DB_NAME(st.dbid) LIKE @Database
      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

      -- *************************************************************
      -- CPU Time
      -- *************************************************************
      SELECT DB_NAME(st.dbid)             DBName,
        OBJECT_SCHEMA_NAME(st.objectid,dbid)      SchemaName,
        OBJECT_NAME(st.objectid,dbid)        StoredProcedure,
        Max(cp.usecounts)            Execution_count,
        Sum(qs.total_worker_time)          total_cpu_time,
        Sum(qs.total_worker_time)/(Max(cp.usecounts) * 1.0) avg_cpu_time
      FROM sys.dm_exec_cached_plans  cp
      INNER JOIN sys.dm_exec_query_stats qs
         ON cp.plan_handle = qs.plan_handle
      CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
      WHERE DB_NAME(st.dbid) IS NOT NULL
       AND cp.objtype = 'proc'
       AND DB_NAME(st.dbid) LIKE @Database
      GROUP BY DB_NAME(st.dbid),OBJECT_SCHEMA_NAME(objectid,st.dbid),
         OBJECT_NAME(objectid,st.dbid)
      ORDER BY Sum(qs.total_worker_time) DESC

      -- *************************************************************
      -- I/O File
      -- *************************************************************
      SELECT DB_NAME(st.dbid)               DBName,
        OBJECT_SCHEMA_NAME(objectid,st.dbid)         SchemaName,
        OBJECT_NAME(objectid,st.dbid)           StoredProcedure,
        Max(cp.usecounts)               execution_count,
        Sum(qs.total_physical_reads + qs.total_logical_reads + qs.total_logical_writes) total_IO,
        Sum(qs.total_physical_reads + qs.total_logical_reads + qs.total_logical_writes) / (Max(cp.usecounts)) avg_total_IO,
        Sum(qs.total_physical_reads)           total_physical_reads,
        Sum(qs.total_physical_reads)/(Max(cp.usecounts) * 1.0)   avg_physical_read  ,
        Sum(qs.total_logical_reads)            total_logical_reads,
        Sum(qs.total_logical_reads)/(Max(cp.usecounts) * 1.0)   avg_logical_read ,
        Sum(qs.total_logical_writes)           total_logical_writes,
        Sum(qs.total_logical_writes)/(Max(cp.usecounts) * 1.0)   avg_logical_writes
      FROM sys.dm_exec_query_stats qs
      CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) st
      INNER JOIN sys.dm_exec_cached_plans cp
         ON qs.plan_handle = cp.plan_handle
      WHERE DB_NAME(st.dbid) IS NOT NULL
       AND cp.objtype = 'proc'
       AND DB_NAME(st.dbid) LIKE @Database
      GROUP BY DB_NAME(st.dbid),
         OBJECT_SCHEMA_NAME(objectid,st.dbid),
         OBJECT_NAME(objectid,st.dbid)
      ORDER BY Sum(qs.total_physical_reads + qs.total_logical_reads + qs.total_logical_writes) DESC

      -- *************************************************************
      -- Time to execute
      -- *************************************************************
      SELECT DB_NAME(st.dbid)           DBName,
        OBJECT_SCHEMA_NAME(objectid,st.dbid)     SchemaName,
        OBJECT_NAME(objectid,st.dbid)       StoredProcedure,
        Max(cp.usecounts)           execution_count,
        Sum(qs.total_elapsed_time)        total_elapsed_time,
        Sum(qs.total_elapsed_time)/Max(cp.usecounts)  avg_elapsed_time
      FROM sys.dm_exec_query_stats qs
      CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) st
      INNER JOIN sys.dm_exec_cached_plans cp
         ON qs.plan_handle = cp.plan_handle
      WHERE DB_NAME(st.dbid) IS NOT NULL
       AND cp.objtype = 'proc'
       AND DB_NAME(st.dbid) LIKE @Database
      GROUP BY DB_NAME(st.dbid),
         OBJECT_SCHEMA_NAME(objectid,st.dbid),
         OBJECT_NAME(objectid,st.dbid)
      ORDER BY Sum(qs.total_elapsed_time) DESC

    END
    GO

    SELECT DB_NAME(st.dbid) DBName,
         OBJECT_SCHEMA_NAME(st.objectid, dbid) SchemaName,
         OBJECT_NAME(st.objectid,dbid) StoredProcedure,
         Max(cp.usecounts) Execution_count
    INTO #slowrunningqueries
    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'
       AND DB_NAME(st.dbid) NOT IN ('master', 'msdb', 'model','tempdb')
    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

    --select * from #slowrunningqueries

    SELECT DISTINCT
    'EXEC ' + quotename(DBName) + '.sys.sp_recompile ' + quotename(StoredProcedure, '''') AS recompileRoutineSQL, DBName 
    INTO #RecompileQuery
    FROM #slowrunningqueries 
    WHERE [DbName] NOT IN ('master', 'msdb', 'model','tempdb', 'distribution')

    --SELECT * FROM #RecompileQuery

    --DROP TABLE #RecompileQuery

    DECLARE @RecompilationSQL NVARCHAR(MAX)
    SET @RecompilationSQL = ''

    SELECT @RecompilationSQL = @RecompilationSQL + recompileRoutineSQL + CHAR(10)
    FROM #RecompileQuery
    WHERE recompileRoutineSQL IS NOT NULL

    DECLARE @StartOffset INT
    DECLARE @Length INT
    SET @StartOffset = 0
    SET @Length = 4000

    WHILE (@StartOffset < LEN(@RecompilationSQL))
    BEGIN
    PRINT SUBSTRING(@RecompilationSQL, @StartOffset, @Length)
    SET @StartOffset = @StartOffset + @Length
    END

    PRINT SUBSTRING(@RecompilationSQL, @StartOffset, @Length)

    (This line seems to be giving a problem but I'm not sure what is missing) EXECUTE sp_executesql @RecompilationSQL

  • August 3, 2018 at 6:27 am

    #2000148

    tt-615680 - Friday, August 3, 2018 6:05 AM

    SELECT DB_NAME(st.dbid) DBName,
         OBJECT_SCHEMA_NAME(st.objectid, dbid) SchemaName,
         OBJECT_NAME(st.objectid,dbid) StoredProcedure,
         Max(cp.usecounts) Execution_count
    INTO #slowrunningqueries
    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'
       AND DB_NAME(st.dbid) NOT IN ('master', 'msdb', 'model','tempdb')
    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

    --select * from #slowrunningqueries

    SELECT DISTINCT
    'EXEC ' + quotename(DBName) + '.sys.sp_recompile ' + quotename(StoredProcedure, '''') AS recompileRoutineSQL, DBName 
    INTO #RecompileQuery
    FROM #slowrunningqueries 
    WHERE [DbName] NOT IN ('master', 'msdb', 'model','tempdb', 'distribution')

    --SELECT * FROM #RecompileQuery

    --DROP TABLE #RecompileQuery

    DECLARE @RecompilationSQL NVARCHAR(MAX)
    SET @RecompilationSQL = ''

    SELECT @RecompilationSQL = @RecompilationSQL + recompileRoutineSQL + CHAR(10)
    FROM #RecompileQuery
    WHERE recompileRoutineSQL IS NOT NULL

    DECLARE @StartOffset INT
    DECLARE @Length INT
    SET @StartOffset = 0
    SET @Length = 4000

    WHILE (@StartOffset < LEN(@RecompilationSQL))
    BEGIN
    PRINT SUBSTRING(@RecompilationSQL, @StartOffset, @Length)
    SET @StartOffset = @StartOffset + @Length
    END

    PRINT SUBSTRING(@RecompilationSQL, @StartOffset, @Length)

    (This line seems to be giving a problem but I'm not sure what is missing) EXECUTE sp_executesql @RecompilationSQL

    Line:

    'EXEC ' + quotename(DBName) + '.sys.sp_recompile ' + quotename(StoredProcedure, '''') AS recompileRoutineSQL
    Should be:
    'EXEC sys.sp_recompile ' + '''' + quotename(DBName) + '.' + quotename(SchemaName) + '.' + quotename(StoredProcedure) + '''' AS recompileRoutineSQL

  • August 6, 2018 at 5:24 am

    #2000389

    Jonathan AC Roberts - Friday, August 3, 2018 6:27 AM

    tt-615680 - Friday, August 3, 2018 6:05 AM

    SELECT DB_NAME(st.dbid) DBName,
         OBJECT_SCHEMA_NAME(st.objectid, dbid) SchemaName,
         OBJECT_NAME(st.objectid,dbid) StoredProcedure,
         Max(cp.usecounts) Execution_count
    INTO #slowrunningqueries
    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'
       AND DB_NAME(st.dbid) NOT IN ('master', 'msdb', 'model','tempdb')
    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

    --select * from #slowrunningqueries

    SELECT DISTINCT
    'EXEC ' + quotename(DBName) + '.sys.sp_recompile ' + quotename(StoredProcedure, '''') AS recompileRoutineSQL, DBName 
    INTO #RecompileQuery
    FROM #slowrunningqueries 
    WHERE [DbName] NOT IN ('master', 'msdb', 'model','tempdb', 'distribution')

    --SELECT * FROM #RecompileQuery

    --DROP TABLE #RecompileQuery

    DECLARE @RecompilationSQL NVARCHAR(MAX)
    SET @RecompilationSQL = ''

    SELECT @RecompilationSQL = @RecompilationSQL + recompileRoutineSQL + CHAR(10)
    FROM #RecompileQuery
    WHERE recompileRoutineSQL IS NOT NULL

    DECLARE @StartOffset INT
    DECLARE @Length INT
    SET @StartOffset = 0
    SET @Length = 4000

    WHILE (@StartOffset < LEN(@RecompilationSQL))
    BEGIN
    PRINT SUBSTRING(@RecompilationSQL, @StartOffset, @Length)
    SET @StartOffset = @StartOffset + @Length
    END

    PRINT SUBSTRING(@RecompilationSQL, @StartOffset, @Length)

    (This line seems to be giving a problem but I'm not sure what is missing) EXECUTE sp_executesql @RecompilationSQL

    Line:

    'EXEC ' + quotename(DBName) + '.sys.sp_recompile ' + quotename(StoredProcedure, '''') AS recompileRoutineSQL
    Should be:
    'EXEC sys.sp_recompile ' + '''' + quotename(DBName) + '.' + quotename(SchemaName) + '.' + quotename(StoredProcedure) + '''' AS recompileRoutineSQL

    The script is as follows:

    SELECT DB_NAME(st.dbid) DBName,
         OBJECT_SCHEMA_NAME(st.objectid, dbid) SchemaName,
         OBJECT_NAME(st.objectid,dbid) StoredProcedure,
         Max(cp.usecounts) Execution_count
    INTO #slowrunningqueries
    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'
       AND DB_NAME(st.dbid) NOT IN ('master', 'msdb', 'model','tempdb')
    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

    SELECT DISTINCT
    'EXEC sys.sp_recompile ' + '''' + quotename(DBName) + '.' + quotename(SchemaName) + '.' + quotename(StoredProcedure) + '''' AS recompileRoutineSQL
    INTO #RecompileQuery
    FROM #slowrunningqueries 
    WHERE DBName NOT IN ('master','msdb', 'model','tempdb','distribution')

    DECLARE @RecompilationSQL NVARCHAR(MAX)
    SET @RecompilationSQL = ''

    SELECT @RecompilationSQL = @RecompilationSQL + recompileRoutineSQL + CHAR(10)
    FROM #RecompileQuery
    WHERE recompileRoutineSQL IS NOT NULL

    DECLARE @StartOffset INT
    DECLARE @Length INT
    SET @StartOffset = 0
    SET @Length = 4000

    WHILE (@StartOffset < LEN(@RecompilationSQL))
    BEGIN
    PRINT SUBSTRING(@RecompilationSQL, @StartOffset, @Length)
    SET @StartOffset = @StartOffset + @Length
    END

    PRINT SUBSTRING(@RecompilationSQL, @StartOffset, @Length)

    EXECUTE sp_executesql @RecompilationSQL

    But when executing the last part of the script, it throws the following error (please note that the database name and stored procedure names have been replaced for the purpose of an example)

    EXEC sys.sp_recompile '[DATABASENAME].[data].[STOREDPROCEDURENAME]'

    Msg 15165, Level 16, State 1, Procedure sp_recompile, Line 94
    Could not find object '[DATABASENAME].[dbo].[STOREDPROCEDURENAME]' or you do not have permission.

    Thank you

  • August 6, 2018 at 6:05 am

    #2000396

    Another thing you could do, provided your database isn't really busy and the stored procedures aren't massive is occasionally force a recompile of all of them by issuing a DBCC FREEPROCCACHE
    After that when a stored procedure is called it will be recompiled.

  • August 6, 2018 at 6:24 am

    #2000401

    Jonathan AC Roberts - Monday, August 6, 2018 6:05 AM

    Another thing you could do, provided your database isn't really busy and the stored procedures aren't massive is occasionally force a recompile of all of them by issuing a DBCC FREEPROCCACHE
    After that when a stored procedure is called it will be recompiled.

    Just to add to that thought...

    I can't speak for others systems but, for the systems I'm working with, DBCC FREEPROCCACHE isn't the bear that everyone seems to make it out to be and is the first thing I do if I see the system unexpectedly go off the rails for CPU or I/O.  I don't need to use it often (maybe once in two weeks or longer) and I don't have hundreds of servers to monitor but it has worked a treat when it looks like it's needed.

    If you do monitor your system(s), don't use it just because there's high CPU or I/O.  It might just be a batch job or an ad-hoc query that's running.  You have to know your system baselines and you have to know what's running.  In other words, I don't use it prophylactically.  On the other hand, I don't see why someone couldn't except for the fact that it may cover up some things that are prone to parameter sniffing that actually should be repaired..

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • August 6, 2018 at 3:00 pm

    #2000511

    I don't believe you can pass a three part object name to sp_recompile, so you need to generate something like Use [DatabaseName];Exec sys.sp_recompile '[Schema].[Object]'

    As to FREEPROCCACHE, it feels like a bit of a sledgehammer-to-crack-a-nut solution to resolving performance issues (although so does just recompiling everything that takes a "long time" too). I've certainly seen it go bad, where not only did it not fix the problem of a slow SP (updating stats did in the end) but did it simultaneously expose a number of other procedures that were highly susceptible to parameter sniffing and slowed them right down too.

    Personally I'd be inclined instead to generate a report of slow procedures and take a more manual approach to diagnosing the issue (even if I started out with a quick sp_recompile to try and fix an immediate problem). Although these days I've moved over to using Query Store to quickly rectify issues instead.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply