Query sometimes causes SP:CacheRemove

Question

Query sometimes causes SP:CacheRemove

GoofyKC-268034

SSC Veteran

Points: 294
More actions
January 14, 2008 at 8:08 am

#378007

I have a query (actually supplied by third party consultants) that joins two tables. Out of the columns selected one of the columns is a sub-select that gets the max date from the same two tables being joined again. The main problem is that this query will run for 10 seconds or 4 minutes or ten minutes. When I run profiler on this sometimes it runs SP:CacheRemove. The SP:CacheRemove is what takes all of the time. The questions I have is why does this happen and if i can't directly control this happening (through commands or settings), what should I be doing as a DBA to stop the need for SP:CacheRemove (change a server setting, make physical changes to the server)?

Viewing 5 posts - 1 through 4 (of 4 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

You can't directly control it.

The cache remove occurs when SQL, for whatever reason, decides that the cached execution plan for the query its about to run is no longer optimal. It removes the old plan from the cache, generates a new plan, puts that into the cache, then runs the query.

The most common reasons for this are statistics changes on the underlying tables (showing that data distributions have changed snce the last time the plan was compiled), and temp table usage.

How much memory does your server have? How much allocated to the plan cache?

Rough query to see the latter:

SELECT (sum(pagesused)*8096)/1024/1024 AS CacheSizeInMB from master..syscacheobjects

Can you post the query you're having trouble with please?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

GoofyKC-268034 SSC Veteran Points: 294 More actions · Answer 2

Yesterday it ran in 10 seconds or less (once it ran for 23 seconds). Today it has gone to about 4:30. During all of the good and bad run times I have ran the SQL you gave me for the CacheSizein MB and it has shown between 2 MB & 15 MB. During this last run the CacheSizein MB was 5 MB, SQL Profiler showed that the SPID was doing a bunch of SP:CacheRemove commands and the transaction ran in 4:19. I tried to post the code but the posting keeps failing. If this note goes through I'll try to post a followup with the code.

GoofyKC-268034 SSC Veteran Points: 294 More actions · Answer 3

Here is the code: (Part 1 of 2)

SELECT O3.INXFLD02, O3.UNITCD, O3.WRKTYPE, O3.STATCD, O3.QUEUECD, O3.SUSPFLAG, O3.INXFLD04,

O3.INXFLD03, O20.ENDDATTIM#, O20.ENDUSERID, O3.CRDATTIM# , O3.VIFLAG

FROM vwGW03 as O3 LEFT OUTER JOIN vwGW20 as O20 ON

(O3.CRDATTIM = O20.CRDATTIM and

O3.RECORDCD = O20.RECORDCD and

O3.CRNODE = O20.CRNODE)

WHERE O3.SUSPFLAG = 'N' and

(O3.QUEUECD = 'ASPOOR' or

O3.QUEUECD = 'CLUNA' or

O3.QUEUECD = 'EVELAZ' or

O3.QUEUECD = 'GFLICK' or

O3.QUEUECD = 'JSTAUB' or

O3.QUEUECD = 'LESTRA' or

O3.QUEUECD = 'LGRIFFIS' or

O3.QUEUECD = 'NCOLLI' or

O3.QUEUECD = 'SMCCOL' or

O3.QUEUECD = 'UWWORK' or

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 4

Posting this for someone

Here is the code:

SELECT O3.INXFLD02, O3.UNITCD, O3.WRKTYPE, O3.STATCD, O3.QUEUECD, O3.SUSPFLAG, O3.INXFLD04,

O3.INXFLD03, O20.ENDDATTIM#, O20.ENDUSERID, O3.CRDATTIM# , O3.VIFLAG

FROM vwGW03 as O3 LEFT OUTER JOIN vwGW20 as O20 ON

(O3.CRDATTIM = O20.CRDATTIM and

O3.RECORDCD = O20.RECORDCD and

O3.CRNODE = O20.CRNODE)

WHERE O3.SUSPFLAG = 'N' and

(O3.QUEUECD = 'ASPOOR' or

O3.QUEUECD = 'CLUNA' or

O3.QUEUECD = 'EVELAZ' or

O3.QUEUECD = 'GFLICK' or

O3.QUEUECD = 'JSTAUB' or

O3.QUEUECD = 'LESTRA' or

O3.QUEUECD = 'LGRIFFIS' or

O3.QUEUECD = 'NCOLLI' or

O3.QUEUECD = 'SMCCOL' or

O3.QUEUECD = 'UWWORK' or

O3.QUEUECD = 'UWWORKA' or

O3.QUEUECD = 'UWWORKD' or

O3.QUEUECD = 'UWWORKE' or

O3.QUEUECD = 'UWWORKF' or

O3.QUEUECD = 'UWWORKG' or

O3.QUEUECD = 'UWWORKI' or

O3.QUEUECD = 'UWWORKJ') and

(O20.ENDDATTIM# = (SELECT MAX( I20.ENDDATTIM#)

FROM vwGW03 as I03 LEFT OUTER JOIN vwGW20 as I20 ON

(I03.CRDATTIM = I20.CRDATTIM and

I03.RECORDCD = I20.RECORDCD and

I03.CRNODE = I20.CRNODE)

WHERE I03.INXFLD02 = O3.INXFLD02

GROUP BY I03.INXFLD02))

ORDER BY O3.INXFLD02

Yesterday it ran in 10 seconds or less (once it ran for 23 seconds). Today it has gone to about 4:30. During all of the good and bad run times I have ran the SQL you gave me for the CacheSizein MB and it has shown between 2 MB & 15 MB. During this last run the CacheSizein MB was 5 MB, SQL Profiler showed that the SPID was doing a bunch of SP:CacheRemove commandss and the transaction ran in 4:19.