Dynamic SQL - long execution time - first time only

Question

I have stored procedure which is building dynamic SQL statement depending on its input parameters and then executed it.

One of the queries is causing time outs, so I have decided to check it. The first time (and only the first time) the issue statement is executed it is slow (30 secs - 45 secs) and every next execute takes 1-2 seconds.

In order to reproduce the issue, I am using

DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS

I am really confused where the problem is, because ordinary if SQL statement is slow, it is always slow. Now, it has long execution time only the first time.

Is is possible, the itself to be slow and needs optimization or the problem can be caused by something else?

The execution plan is below, but for me there is nothing strange with it:

Answer 1

From your reply to my comment, it would appear that the first time this query runs it is performing a lot of physical reads or read-ahead reads, meaning that a lot of IO is required to get the right pages into the buffer pool to satisfy this query.

Once pages are read into the buffer pool (memory) they generally stay there so that physical IO is not required to read them again (you can see this is happening as you indicated that the physical reads are converted to logical reads the second time the query is run). Memory is orders of magnitude faster than disk IO, hence the difference in speed for this query.

Looking at the plan, I can just about see that every read operation is being done against the clustered index of the table. As the clustered index contains every column for the row it is potentially fetching more data per row than is actually required for the query.

Unless you are selecting every column from every table, I would suggest that creating non-clustered covering indexes that satisfy this query (that are as narrow as possible), this will reduce the IO requirement for the query and make it less expensive the first time round.

Of course this may not be possible/viable for you to do, in which case you should either just take the hit on the first run and not empty the caches, or rewrite the query itself to be more efficient and perform less reads.

Answer 2

Its very simple the reason 1st and very 1st time it takes longer and then all later executions are done fairly quickly. the reason behind this mystery is "CACHED EXECUTION PLANS".

While working with Stored Procedures, Sql server takes the following steps.

1) Parse Syntax of command.
2) Translate to Query Tree.
3) Develop Execution Plan.
4) Execute.

The 1st two steps only take place when you create a Stored Procedure.

3rd step only takes place on very 1st Execution or if the CACHED PLAN has been flushed from the CACHE MEMORY.

Fourth Step takes place on every execution, and this is the only step that takes place after the very 1st execution if the Plan is still in cache memory.

In your case its quite understandable that very 1st execution took long and then later it gets executed fairly quickly.

To reproduce the "issue" you executed DBCC FREEPROCCACHE AND DBCC DROPCLEANBUFFERS commanda which basically Flushes the BUFFER CACHE MEMORY and causes your stored procedure to create a new Execution plan on it next execution. Hope this will clear the fog a little bit :)