Sql Server - Stored Procedure Suddenly Become Slow

I have written a stored procedure that, yesterday, typically completed in under a second. Today, it takes about 18 seconds. I ran into the problem yesterday as well, and it seemed

Solution 1:

Ah, can it be the query plan sucks?

SP's get compiled / query lpan deterined on FIRST USE - depending on parameters. So, the parameters of the first call (when no lpan is present) determine the query plan. At one piont i gets dropped from cache, new plan generated.

Next time it runs slow, possibly make a call using query analyzer and get the selected plan - and check how it looks.

if it is this - put in an opton to recompile the SP on every call (with recompile).

Solution 2:

parameter sniffing google it. try this, which will "remap" the input parameters to local variables to prevent SQL Server from trying to guess the query plan based on parameters:

ALTERPROCEDURE [dbo].[spGetPOIs]
    @lat1float,
    @lon1float,
    @lat2float,
    @lon2float,
    @minLOD tinyint, 
    @maxLOD tinyint,
    @exact bit
ASBEGINDECLARE@X_lat1 float,
    @X_lon1 float,
    @X_lat2 float,
    @X_lon2 float,
    @X_minLOD tinyint, 
    @X_maxLOD tinyint,
    @X_exact bit



    -- Create the query rectangle as a polygonDECLARE@bounds geography;
    SET@bounds= dbo.fnGetRectangleGeographyFromLatLons(@X_lat1, @X_lon1, @lX_at2, @X_lon2);

    -- Perform the selection
    if (@exact=0)
    BEGINSELECT [ID], [Name], [Type], [Data], [MinLOD], [MaxLOD], [Location].[Lat] AS [Latitude], [Location].[Long] AS [Longitude], [SourceID]
        FROM [POIs]
        WHERENOT ((@X_maxLOD  [MaxLOD])) AND
            (@bounds.Filter([Location]) =1)
    ENDELSEBEGINSELECT [ID], [Name], [Type], [Data], [MinLOD], [MaxLOD], [Location].[Lat] AS [Latitude], [Location].[Long] AS [Longitude], [SourceID]
        FROM [POIs]
        WHERENOT ((@X_maxLOD  [MaxLOD])) AND
            (@bounds.STIntersects([Location]) =1)
    ENDEND

Solution 3:

I had a similar problem and it was related with indexes. Rebuilding them help the SP to run fast again.

I found the solution here

USE master;
GO

CREATE PROC DatabaseReIndex(@DatabaseVARCHAR(100)) ASBEGINDECLARE@DbIDSMALLINT=DB_ID(@Database)--Get Database ID
  IF EXISTS(SELECT*FROM tempdb.sys.objects WHERE name='Indexes') 
  BEGIN--Delete Temp Table if exists, then createDROPTABLE TempDb.dbo.Indexes
  ENDCREATETABLE TempDb.dbo.Indexes(IndexTempID INTIDENTITY(1,1),SchemaName NVARCHAR(128),TableName NVARCHAR(128),IndexName NVARCHAR(128),IndexFrag FLOAT)
EXEC ('USE '+@Database+';
INSERT INTO TempDb.dbo.Indexes(TableName,SchemaName,IndexName,IndexFrag)
SELECT OBJECT_NAME(ind.OBJECT_ID) AS TableName,sch.name,ind.name IndexName,indexstats.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats('+@DbID+', NULL, NULL, NULL, NULL) indexstats
INNER JOIN sys.indexes ind ON ind.object_id = indexstats.object_id AND ind.index_id = indexstats.index_id
INNER JOIN sys.objects obj on obj.object_id=indexstats.object_id
INNER JOIN sys.schemas as sch ON sch.schema_id = obj.schema_id
WHERE indexstats.avg_fragmentation_in_percent > 10 AND indexstats.index_type_desc<>''HEAP''
ORDER BY indexstats.avg_fragmentation_in_percent DESC')--Get index data and fragmentation, set the percentage as high or low as you needDECLARE@IndexTempIDBIGINT=0,@SchemaName NVARCHAR(128),@TableName NVARCHAR(128),@IndexName NVARCHAR(128),@IndexFragFLOATSELECT*FROM TempDb.dbo.Indexes --View your results, comment out if not needed...-- Loop through the indexes
WHILE @IndexTempIDISNOTNULLBEGINSELECT@SchemaName=SchemaName,@TableName=TableName,@IndexName=IndexName,@IndexFrag=IndexFrag FROM TempDb.dbo.Indexes WHERE IndexTempID=@IndexTempID
    IF @IndexNameISNOTNULLAND@SchemaNameISNOTNULLAND@TableNameISNOTNULLBEGIN
      IF @IndexFrag<30.BEGIN--Low fragmentation can use re-organise, set at 30 as per most articles
        PRINT 'USE '+@Database+'; ALTER INDEX '+@IndexName+ N' ON '+@SchemaName+ N'.'+@TableName+ N' REORGANIZE'EXEC('USE '+@Database+'; ALTER INDEX '+@IndexName+ N' ON '+@SchemaName+ N'.'+@TableName+ N' REORGANIZE')
      ENDELSEBEGIN--High fragmentation needs re-build
        PRINT 'USE '+@Database+'; ALTER INDEX '+@IndexName+ N' ON '+@SchemaName+ N'.'+@TableName+ N' REBUILD'EXEC('USE '+@Database+'; ALTER INDEX '+@IndexName+ N' ON '+@SchemaName+ N'.'+@TableName+ N' REBUILD')
      ENDENDSET@IndexTempID=(SELECTMIN(IndexTempID) FROM TempDb.dbo.Indexes WHERE IndexTempID>@IndexTempID)
  ENDENDDROPTABLE TempDb.dbo.Indexes

GO