I have a stored procedure that runs slowly and takes over 60 seconds sometimes, but almost all of the time it takes only one or two seconds. In both cases the same input data is used and there have been no changes to the table. The slow running is after the query has not been run for several days.
In the background 16 records are inserted every minute. The table has 5 million records. There are two indexes on the table one is clustered the other is unclustered. They are very well tuned. The record contains a two integers and 90 floats.
The stored procedure gets one record from the database which is the latest record for a particular entity. If I go to the web site after a few days the page times out. If I go to Mgmt Studio, and run the sp form there it takes a very long time but eventually comes back with the correct answer. If execute the stored procedure after that the response is a second or two using the same input data. If I change the input data the response is equally as fast. The web site also works very quickly too.
What is going on and how do I fix it?
Pops73
Try running the SP with SET STATISTICS IO ON, then look at the messages tab. It sounds like the first time you run the query, it is having to do physical reads as opposed to logical reads. The second time you run it, it is doing logical reads, which would be much, much faster.|||
Thank you for the response and it sounds reasonable. I restarted the server which I thought would clear the buffer cache and cause the problem to occur. But it didn't after the restart the timeout did not occur. Response was good. Is the buffer cache saved through a restart of a server? How can I clear the cache to easily reproduce the problem and gather the statistics?
Pops73
|||
Restarting the server does clear the the buffer cache. You can run the command DBCC FREEPROCCACHE to clear out the procedure cache, and DBCC DROPCLEANBUFFERS to flush the data cache.
http://msdn2.microsoft.com/en-US/library/ms187762.aspx
Run these commands:
DBCC FREEPROCCACHE
GO
CHECKPOINT
GO
DBCC DROPCLEANBUFFERS
|||
Thank you for your help. This still does not reproduce the problem.
Table '#7F0FE051'. Scan count 0, logical reads 21, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TempMtrScan'. Scan count 1, logical reads 8, physical reads 3, read-ahead reads 607, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
More info -- I moved the indexes. I have the data in one file group with three files and the indexes in another file group with two files. I still saw the problem.
running Sql Server 2005 sp1
I had the problem yesterday in another db instance of the same applicaiton on another machine. This table only had one record instead of 5 million. Two different queries will show the same problem
Part of the table definition
CREATE TABLE [dbo].[TempMtrScan](
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[ShovelID] [int] NOT NULL,
[DateTime] [datetime] NOT NULL,
[FHoistNDE] [float] NULL,
[FHoistDE] [float] NULL,
[FHoistIP] [float] NULL,
[FHoistFLD] [float] NULL, ............
Query 1
INSERT INTO @.TempMtrScanUnpivot ([ShovelID],[TempMtrScanName], [Temperature])
SELECT ShovelID, TempMtrScanName, Temperature
FROM
(SELECT ShovelID,[DateTime],
FHoistNDE,FHoistDE,
RHoistNDE,RHoistDE,
CrowdNDE,CrowdDE,
SwingNDE,SwingDE,
RearSwingNDE,RearSwingDE,
RPropelNDE,RPropelDE,
LPropelNDE,LPropelDE,
LFrontSwingNDE,LFrontSwingDE,
RFrontSwingNDE,RFrontSwingDE,
TransPhaseA,TransPhaseB,TransPhaseC,
DriveInletTemp1,DriveInletTemp2,DriveInletTemp3,DriveInletTemp4,
DriveInletTemp5,DriveInletTemp6,DriveInletTemp7,DriveInletTemp8,
CwdMotorTemp,CwdMotorWindABTemp,CwdMotorWindBCTemp,CwdMotorWindCATemp,
PplRhtMtrMotorTemp,PplRhtMtrMotorWindABTemp,PplRhtMtrMotorWindBCTemp,PplRhtMtrMotorWindCATemp,
SwgLFntMtrMotorTemp,SwgLFntMtrMotorWindABTemp,SwgLFntMtrMotorWindBCTemp,SwgLFntMtrMotorWindCATemp,
HstRearMtrMotorTemp,HstRearMtrMotorWindABTemp,HstRearMtrMotorWindBCTemp,HstRearMtrMotorWindCATemp,
PplLftMtrMotorTemp,PplLftMtrMotorWindABTemp,PplLftMtrMotorWindBCTemp,PplLftMtrMotorWindCATemp,
SwgRFntMtrMotorTemp,SwgRFntMtrMotorWindABTemp,SwgRFntMtrMotorWindBCTemp,SwgRFntMtrMotorWindCATemp,
HstFntMtrMotorTemp,HstFntMtrMotorWindABTemp,HstFntMtrMotorWindBCTemp,HstFntMtrMotorWindCATemp,
SwgRearMtrMotorTemp,SwgRearMtrMotorWindABTemp,SwgRearMtrMotorWindBCTemp,SwgRearMtrMotorWindCATemp,
SwgFntMtrMotorTemp, SwgFntMtrMotorWindABTemp, SwgFntMtrMotorWindBCTemp, SwgFntMtrMotorWindCATemp, ACS800Temp,
TempDiffPhsU, TempDiffPhsV, TempDiffPhsW
FROM dbo.TempMtrScan
WHERE [Datetime] =
(SELECT MAX([datetime])
FROM dbo.TempMtrScan
WHERE ShovelID = @.ShovelID)
AND ShovelID = @.ShovelID) A
UNPIVOT (
Temperature
FOR TempMtrScanName
IN ( FHoistNDE,FHoistDE,
RHoistNDE,RHoistDE,
CrowdNDE,CrowdDE,
SwingNDE,SwingDE,
RearSwingNDE,RearSwingDE,
RPropelNDE,RPropelDE,
LPropelNDE,LPropelDE,
LFrontSwingNDE,LFrontSwingDE,
RFrontSwingNDE,RFrontSwingDE,
TransPhaseA,TransPhaseB,TransPhaseC,
DriveInletTemp1,DriveInletTemp2,DriveInletTemp3,DriveInletTemp4,
DriveInletTemp5,DriveInletTemp6,DriveInletTemp7,DriveInletTemp8,
CwdMotorTemp,CwdMotorWindABTemp,CwdMotorWindBCTemp,CwdMotorWindCATemp,
PplRhtMtrMotorTemp,PplRhtMtrMotorWindABTemp,PplRhtMtrMotorWindBCTemp,PplRhtMtrMotorWindCATemp,
SwgLFntMtrMotorTemp,SwgLFntMtrMotorWindABTemp,SwgLFntMtrMotorWindBCTemp,SwgLFntMtrMotorWindCATemp,
HstRearMtrMotorTemp,HstRearMtrMotorWindABTemp,HstRearMtrMotorWindBCTemp,HstRearMtrMotorWindCATemp,
PplLftMtrMotorTemp,PplLftMtrMotorWindABTemp,PplLftMtrMotorWindBCTemp,PplLftMtrMotorWindCATemp,
SwgRFntMtrMotorTemp,SwgRFntMtrMotorWindABTemp,SwgRFntMtrMotorWindBCTemp,SwgRFntMtrMotorWindCATemp,
HstFntMtrMotorTemp,HstFntMtrMotorWindABTemp,HstFntMtrMotorWindBCTemp,HstFntMtrMotorWindCATemp,
SwgRearMtrMotorTemp,SwgRearMtrMotorWindABTemp,SwgRearMtrMotorWindBCTemp,SwgRearMtrMotorWindCATemp,
SwgFntMtrMotorTemp, SwgFntMtrMotorWindABTemp, SwgFntMtrMotorWindBCTemp, SwgFntMtrMotorWindCATemp, ACS800Temp,
TempDiffPhsU, TempDiffPhsV, TempDiffPhsW
)
) AS unpvt
Table '#7F0FE051'. Scan count 0, logical reads 21, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TempMtrScan'. Scan count 1, logical reads 8, physical reads 3, read-ahead reads 607, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Query 2
SELECT
(max(T.FHoistNDE)* 1.8 + 32) as "FHoistNDE" ,
(max(T.FHoistDE)* 1.8 + 32) as "FHoistDE" ,
(max(T.FHoistIP)* 1.8 + 32) as "FHoistIP" ,
(max(T.FHoistFLD)* 1.8 + 32) as "FHoistFLD" ,
(max(T.RHoistNDE)* 1.8 + 32) as "RHoistNDE" ,
(max(T.RHoistDE)* 1.8 + 32) as "RHoistDE" ,
(max(T.RHoistIP)* 1.8 + 32) as "RHoistIP" ,
(max(T.RHoistFLD)* 1.8 + 32) as "RHoistFLD" ,
(max(T.CrowdNDE)* 1.8 + 32) as "CrowdNDE" ,
(max(T.CrowdDE) * 1.8 + 32) as "CrowdDE" ,
(max(T.CrowdIP) * 1.8 + 32) as "CrowdIP" ,
(max(T.CrowdFLD) * 1.8 + 32) as "CrowdFLD" ,
(max(T.SwingNDE)* 1.8 + 32) as "SwingNDE" ,
(max(T.SwingDE) * 1.8 + 32) as "SwingDE" ,
(max(T.SwingIP) * 1.8 + 32) as "SwingIP" ,
(max(T.SwingFLD)* 1.8 + 32) as "SwingFLD" ,
(max(T.RearSwingNDE)* 1.8 + 32) as "RearSwingNDE" ,
(max(T.RearSwingDE) * 1.8 + 32) as "RearSwingDE" ,
(max(T.RearSwingIP) * 1.8 + 32) as "RearSwingIP" ,
(max(T.RearSwingFLD)* 1.8 + 32) as "RearSwingFLD" ,
(max(T.RPropelNDE) * 1.8 + 32) as "RPropelNDE" ,
(max(T.RPropelDE)* 1.8 + 32) as "RPropelDE" ,
(max(T.RPropelIP) * 1.8 + 32) as "RPropelIP" ,
(max(T.RPropelFLD) * 1.8 + 32) as "RPropelFLD" ,
(max(T.LPropelNDE) * 1.8 + 32) as "LPropelNDE" ,
(max(T.LPropelDE) * 1.8 + 32) as "LPropelDE" ,
(max(T.LPropelIP) * 1.8 + 32) as "LPropelIP" ,
(max(T.LPropelFLD) * 1.8 + 32) as "LPropelFLD" ,
(max(T.LFrontSwingNDE)* 1.8 + 32) as "LFrontSwingNDE" ,
(max(T.LFrontSwingDE)* 1.8 + 32) as "LFrontSwingDE" ,
(max(T.LFrontSwingIP) * 1.8 + 32) as "LFrontSwingIP" ,
(max(T.LFrontSwingFLD) * 1.8 + 32) as "LFrontSwingFLD" ,
(max(T.RFrontSwingNDE) * 1.8 + 32) as "RFrontSwingNDE" ,
(max(T.RFrontSwingDE) * 1.8 + 32) as "RFrontSwingDE" ,
(max(T.RFrontSwingIP)* 1.8 + 32) as "RFrontSwingIP" ,
(max(T.RFrontSwingFLD) * 1.8 + 32) as "RFrontSwingFLD" ,
(max(T.TransPhaseA)* 1.8 + 32) as "TransPhaseA" ,
(max(T.TransPhaseB)* 1.8 + 32) as "TransPhaseB" ,
(max(T.TransPhaseC)* 1.8 + 32) as "TransPhaseC" ,
(max(T.DriveInletTemp1)* 1.8 + 32) as "DriveInletTemp1" ,
(max(T.DriveInletTemp2)* 1.8 + 32) as "DriveInletTemp2" ,
(max(T.DriveInletTemp3)* 1.8 + 32) as "DriveInletTemp3" ,
(max(T.DriveInletTemp4)* 1.8 + 32) as "DriveInletTemp4" ,
(max(T.DriveInletTemp5)* 1.8 + 32) as "DriveInletTemp5" ,
(max(T.DriveInletTemp6)* 1.8 + 32) as "DriveInletTemp6" ,
(max(T.DriveInletTemp7)* 1.8 + 32) as "DriveInletTemp7" ,
(max(T.DriveInletTemp8)* 1.8 + 32) as "DriveInletTemp8" ,
(max(T.CwdMotorTemp)* 1.8 + 32) as "CwdMotorTemp" ,
(max(T.CwdMotorWindABTemp)* 1.8 + 32) as "CwdMotorWindABTemp" ,
(max(T.CwdMotorWindBCTemp)* 1.8 + 32) as "CwdMotorWindBCTemp" ,
(max(T.CwdMotorWindCATemp)* 1.8 + 32) as "CwdMotorWindCATemp" ,
(max(T.PplRhtMtrMotorTemp)* 1.8 + 32) as "PplRhtMtrMotorTemp" ,
(max(T.PplRhtMtrMotorWindABTemp)* 1.8 + 32) as "PplRhtMtrMotorWindABTemp" ,
(max(T.PplRhtMtrMotorWindBCTemp)* 1.8 + 32) as "PplRhtMtrMotorWindBCTemp" ,
(max(T.PplRhtMtrMotorWindCATemp)* 1.8 + 32) as "PplRhtMtrMotorWindCATemp" ,
(max(T.SwgLFntMtrMotorTemp)* 1.8 + 32) as "SwgLFntMtrMotorTemp" ,
(max(T.SwgLFntMtrMotorWindABTemp)* 1.8 + 32) as "SwgLFntMtrMotorWindABTemp" ,
(max(T.SwgLFntMtrMotorWindBCTemp)* 1.8 + 32) as "SwgLFntMtrMotorWindBCTemp" ,
(max(T.SwgLFntMtrMotorWindCATemp)* 1.8 + 32) as "SwgLFntMtrMotorWindCATemp" ,
(max(T.HstRearMtrMotorTemp)* 1.8 + 32) as "HstRearMtrMotorTemp" ,
(max(T.HstRearMtrMotorWindABTemp)* 1.8 + 32) as "HstRearMtrMotorWindABTemp" ,
(max(T.HstRearMtrMotorWindBCTemp)* 1.8 + 32) as "HstRearMtrMotorWindBCTemp" ,
(max(T.HstRearMtrMotorWindCATemp)* 1.8 + 32) as "HstRearMtrMotorWindCATemp" ,
(max(T.PplLftMtrMotorTemp)* 1.8 + 32) as "PplLftMtrMotorTemp" ,
(max(T.PplLftMtrMotorWindABTemp)* 1.8 + 32) as "PplLftMtrMotorWindABTemp" ,
(max(T.PplLftMtrMotorWindBCTemp)* 1.8 + 32) as "PplLftMtrMotorWindBCTemp" ,
(max(T.PplLftMtrMotorWindCATemp)* 1.8 + 32) as "PplLftMtrMotorWindCATemp" ,
(max(T.SwgRFntMtrMotorTemp)* 1.8 + 32) as "SwgRFntMtrMotorTemp" ,
(max(T.SwgRFntMtrMotorWindABTemp)* 1.8 + 32) as "SwgRFntMtrMotorWindABTemp" ,
(max(T.SwgRFntMtrMotorWindBCTemp)* 1.8 + 32) as "SwgRFntMtrMotorWindBCTemp" ,
(max(T.SwgRFntMtrMotorWindCATemp)* 1.8 + 32) as "SwgRFntMtrMotorWindCATemp" ,
(max(T.HstFntMtrMotorTemp)* 1.8 + 32) as "HstFntMtrMotorTemp" ,
(max(T.HstFntMtrMotorWindABTemp)* 1.8 + 32) as "HstFntMtrMotorWindABTemp" ,
(max(T.HstFntMtrMotorWindBCTemp)* 1.8 + 32) as "HstFntMtrMotorWindBCTemp" ,
(max(T.HstFntMtrMotorWindCATemp)* 1.8 + 32) as "HstFntMtrMotorWindCATemp" ,
(max(T.SwgRearMtrMotorTemp)* 1.8 + 32) as "SwgRearMtrMotorTemp" ,
(max(T.SwgRearMtrMotorWindABTemp)* 1.8 + 32) as "SwgRearMtrMotorWindABTemp" ,
(max(T.SwgRearMtrMotorWindBCTemp)* 1.8 + 32) as "SwgRearMtrMotorWindBCTemp" ,
(max(T.SwgRearMtrMotorWindCATemp)* 1.8 + 32) as "SwgRearMtrMotorWindCATemp" ,
(max(T.SwgFntMtrMotorTemp)* 1.8 + 32) as "SwgFntMtrMotorTemp" ,
(max(T.SwgFntMtrMotorWindABTemp)* 1.8 + 32) as "SwgFntMtrMotorWindABTemp" ,
(max(T.SwgFntMtrMotorWindBCTemp)* 1.8 + 32) as "SwgFntMtrMotorWindBCTemp" ,
(max(T.SwgFntMtrMotorWindCATemp)* 1.8 + 32) as "SwgFntMtrMotorWindBCTemp" ,
(max(T.ACS800Temp)* 1.8 + 32) as "ACS800Temp" ,
(max(T.TempDiffPhsU)* 1.8 + 32) as "TempDiffPhsU" ,
(max(T.TempDiffPhsV)* 1.8 + 32) as "TempDiffPhsV" ,
(max(T.TempDiffPhsW)* 1.8 + 32) as "TempDiffPhsW" ,
Max(T.DateTime) as "TemperatureTime"
FROM TempMtrScan T
JOIN Shovel S ON T.ShovelID = S.ID
WHERE (S.ID =@.ShovelID) AND T.DATETIME <= @.EndTime AND T.DATETIME >= @.StartTime
Group by ShovelID, datepart(yy,T.DateTime), datepart(dy,T.DateTime),datepart(hh,T.DateTime),datepart(mi,T.DateTime)/@.Duration
order by Max(T.DateTime) asc
(1943 row(s) affected)
Table 'Shovel'. Scan count 0, logical reads 2, physical reads 2, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TempMtrScan'. Scan count 3, logical reads 38911, physical reads 493, read-ahead reads 736, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Warning: Null value is eliminated by an aggregate or other SET operation.
