Dynamic SQL - Long Run Time - First Time Only

Question

I have a stored procedure that builds a dynamic SQL statement based on its input parameters, and then executes it.

One of the requests causes timeouts, so I decided to check it out. The first time (and only the first time), the task operator runs slowly (30 seconds - 45 seconds), and each subsequent run takes 1-2 seconds.

To reproduce the problem, I use

DBCC FREEPROCCACHE DBCC DROPCLEANBUFFERS 

I really got confused where the problem is, because normal, if the SQL query is slow, it is always slow. Now it has a long run time only for the first time.

Is it possible that it should be slow and in need of optimization, or can the problem be caused by something else?

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

Answer 1

From your answer to my comment, it seems that the first time you run this request, a lot of physical reads or reads are performed, which means that it takes a lot of IO pool to enter the correct pages into the buffer to satisfy this request.

As soon as the pages are read into the buffer pool (memory), they usually remain there, so the physical IO is not required to read them again (you can see that this happens because you indicated that the physical reads are converted to logical reads the second runtime request). The memory is an order of magnitude higher than that of the IO drive, therefore, the speed difference for this request.

Looking at the plan, I can simply see that each read operation is performed against the clustered index of the table. Because the clustered index contains each column for a row, it potentially retrieves more data per row than is actually required by the query.

If you do not select each column from each table, I would suggest creating non-clustered coverage indexes that satisfy this query (which will be as narrow as possible), this will reduce the I / O requirement for the query and make it less expensive for the first time.

Of course, this may not be possible / viable for you, and in this case you should either just take a hit at the first start, or not empty the caches, or rewrite the request itself in order to be more efficient and perform less reads.

Answer 2

Its very simple reason for the first and very first time takes longer, and then all subsequent executions are carried out quite quickly. The reason for this secret is "EXECUTIVE USE PLANS."

When working with stored procedures, the Sql server takes the following steps.

1) The syntax of the command syntax.
2) Go to the Query Tree.
3) Develop an implementation plan.
4) Run.

The first two steps are performed only when the stored procedure is created.

The 3rd step is performed only at the first execution or if the CACHED PLAN has been reset from CACHE MEMORY.

The fourth step is performed at each run, and this is the only step that is performed after the very first run if the plan is still in the cache.

In your case, it is quite understandable that the very first execution took a lot of time, and then it runs pretty quickly.

To reproduce the “problem”, you DBCC FREEPROCCACHE AND DBCC DROPCLEANBUFFERS commanda DBCC DROPCLEANBUFFERS , which basically clears the BUFFER CACHE MEMORY and forces your stored procedure to create a new execution plan the next time it BUFFER CACHE MEMORY . Hope this clears the fog a bit :)

Answer 3

Usually, when a stored procedure or its statistics is first created, etc. reset, it takes the first value passed to the stored procedure as the default value for the stored procedure. Then he will try to optimize himself based on this.

There are several things you can do to stop this.

You might use the query hint feature to mark certain variables as Unknown. So, as an example, at the end of a stored procedure, you can put something line by line:

 select * from foo where foo.bar = @myParam option (optimize for @myParam unknown) 

As another approach, you can force the SQL plan to be recompiled every time - which might be a good idea if your stored procedure is heavily modified in the type of SQL that it generates. How do you do this:

 select * from foo where foo.bar = @myParam option (optimize recompile) 

Hope this helps.