How can i reduce time to generate execution plan and store into cache when Stored procedure execute first time ?

Question

How can i reduce time to generate execution plan and store into cache when Stored procedure execute first time ?

patelchandresh330 80387

Mr or Mrs. 500

Points: 512
More actions
September 23, 2014 at 2:41 am

#293521

How can i reduce time to generate execution plan and store into cache when Stored procedure execute first time ?
I am using
SET TRANSACTION ISOLATION LEVEL SNAPSHOT,
Views in join
in Stored procedure.
Please guide me.
Thanks in Advance....

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

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

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

The delay on first execution is unlikely to be the execution plan. Far more likely to be loading the data into cache. Plans just don't usually take that much time to generate.

You can test it out, on a dev server, using DropCleanBuffers or FreeProcCache to do one of the two, then see where the slow down is actually coming from.

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

patelchandresh330 80387 Mr or Mrs. 500 Points: 512 More actions · Answer 2

GilaMonster,

yes you are right..

it takes time to cache more than 1.25 GB data approx.. huge amount of data page scanning and logical reads happen by this procedure..

i really appreciate ur answare..

Thanks for your valuable answer..

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

patelchandresh330 80387 (9/25/2014)
huge amount of data page scanning and logical reads happen by this procedure..

Then you probably want to focus on tuning the procedure so that it doesn't need to read so much. The less it needs to read, the faster the load into cache will be and the less impact the procedure will have on whatever else is running

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

patelchandresh330 80387 Mr or Mrs. 500 Points: 512 More actions · Answer 4

Hi,

how can we know the "Compile" and "data cache" time of particular store procedure when it execute first time.

can you please guide me??

Thank You in Advance...

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

Can you post the actual execution plan for your stored procedure?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

patelchandresh330 80387 Mr or Mrs. 500 Points: 512 More actions · Answer 6

Hi,

it happen in in most of all stored procedure. it takes more time when execute first time and then from next time it will execute in 0 seconds.

i checked logical read/write, physical read/write. it is not big figure now.

is there any way to know following time of particular stored procedure:

1. Compile time (in milliseconds)

2. stored procedure plan generation and plan cache time (in milliseconds)

3. data/index page cache time (in milliseconds)

is any DMV query or demo script available or any other way?

can you please guide me..

Thanks in advance...

Grant Fritchey SSC Guru Points: 398904 More actions · Answer 7

Compile time for the plan is stored with the plan itself. If you look at the graphical operator it can be seen in the first operation of the plan (usually SELECT/INSERT/UPDATE/DELETE). You can also see this value in the XML.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning