Slow to generate Execution plan

Question

Slow to generate Execution plan

simon.cater

SSC Rookie

Points: 38
More actions
May 13, 2008 at 4:08 am

#123901

Please Help,
I have a view that when run for the first time takes about 25 seconds to run. After this it returns data in less than a second.
All of this time is take by generation of the execution plan. Once in the cache it runs great.
However, it does not stay cached for very long, could be 30 minutes. When it is dropped from the cache it takes another 25 seconds to run.
Is there a way of keeping the plan cached, or improving the time it takes to create it?
thank you for your help
Simon

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

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

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

Are you sure it's the plan generation that's taking the time? If the data returned by the view is not in the data cache, it has to be retrieved from disk and that can be slow.

To test (on a dev/test server)

Run the view a couple times until it's fast.

Run: DBCC FreeProcCache

Run the view. Fast or slow?

Run the view a couple times until it's fast.

Run DBCC DROPCleanBuffers

Run the view. Fast or slow?

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

simon.cater SSC Rookie Points: 38 More actions · Answer 2

Hi Gail,

It is slow after DBCC FreeProcCache

but is still fast after DBCC DROPCleanBuffers

cheers

Simon

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 3

Very odd. The optimiser has a limit on how much time it's allowed to spend considering a plan. It shouldn't be that long.

What do you see in sysprocesses for that process during that 25 sec?

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

simon.cater SSC Rookie Points: 38 More actions · Answer 4

Hi Gail,

What exactly am i looking for in the process table?

cheers

Simon

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 5

I'm most interested in the wait type (it's hex), the last wait type and wait resource (if any)

If the CPU is increasing or static, if the reads are increasing or static.

Also, what does SELECT @@version return?

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

simon.cater SSC Rookie Points: 38 More actions · Answer 6

Hi Gail,

Version is:

However, i cannot clearly see which process has run the query.

It is called via a webservice call.

Is there a way to find the desired process?

cheers

Simon

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 7

Try running the query from a querying tool (query analyser). It's easier to see the SPID and removes any possibility of the slow down coming from somewhere other than SQL.

If you don't know the exact query, use profiler to log it.

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