July 30, 2015 at 11:56 pm
Hi Expertz,
We are facing some weird issues in our 2012 servers. We have sp running in the database which is very slow initially after first execution it will speed up. For initial time of execution it take 35 seconds and after that it will run in 22 seconds
Scenario -1 - Production
SP executes every time in 22 seconds
Scenario -2- Pre production ( Same exact copy of production)
SP executes initially after recompilation in 12 seconds after that it will executes in 3 seconds ( I was able to recreate this event by using DBCC FREEPROCACHE, every time I use free cache it will take 12 seconds)
So the question is why there is two different timing for the same exact copy of databases in different server's ? and what are the options that I have to check to see whats causing the issue
How can I reduce the compilation time ?
In production it is taking 22 seconds every time, how can I improve it and which are the areas I have to check to improve the same.
Kindly help..
Thanks & regards
Milesh
July 31, 2015 at 5:01 am
The differences between the servers could be the amount of memory on the servers, the number and speed of the CPU, or just the load because resource contention on any of these will cause problems.
The only way I know to improve compile time on procedures is to reduce the complexity of the procedure. If you have a 20 second compile time, you probably have a fairly complex procedure. Break it down into smaller chunks, maybe make it a wrapper procedure with calls to secondary procedures. You have to reduce the complexity.
"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
August 1, 2015 at 7:55 pm
@Grant Fritchey
Thanks Grant and hope you are doing well, we had chats several times some time back.
I have one query if we want to force the sql server to use the same plan will it give some performance ? what will be options in front of me. The procedure runs little faster after intial compilation , how can we avoid recompilation on this procedure or any method we can achieve the same.
Anyway we are trying to break down the proc, but we need an immediate solution to satisfy some non technical management people over here, so atleast a temporary solution will suffice for now will get some time window to break down the proc and investigate on the same.
Thanks
MJ
August 2, 2015 at 5:02 am
MJ-1115929 (8/1/2015)
@Grant FritcheyThanks Grant and hope you are doing well, we had chats several times some time back.
I have one query if we want to force the sql server to use the same plan will it give some performance ?
You can force a plan, but it won't reduce the compile time. In fact, it will add to it, so you're better off not using that approach especially in this situation.
what will be options in front of me. The procedure runs little faster after intial compilation , how can we avoid recompilation on this procedure or any method we can achieve the same.
You can add a query hint KEEPFIXEDPLAN. That will reduce the amount of recompiles you get, but, that can introduce other issues, possibly. If your statistics are changing much, you may need to generate different plans over time.
Anyway we are trying to break down the proc, but we need an immediate solution to satisfy some non technical management people over here, so atleast a temporary solution will suffice for now will get some time window to break down the proc and investigate on the same.
Thanks
MJ
Unfortunately, there's likely not an immediate solution or instant fix. Using the KEEPFIXEDPLAN hint will help reduce the number of recompiles, but it won't many recompiles that still occur any faster. Compile time goes up with complexity of the query and the amount of work that the optimizer has to do. The simpler the query, the faster the compile time. The more the query is tuned and has good indexes and statistics and isn't using nested views or nested user-defined functions, the faster the compile time.
"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
August 2, 2015 at 6:21 am
Have you checked the compile time using SHOW STATISTICS TIME ON?
Its very rare for the complile time to be affected by the amount of RAM since SQL internally reallocates space between clerks when needed.
Here is what you can do to test , set query hint RECOMPILE within the procedure and see if it always takes the same amount of time, if not your probably looking at the difference between a warm cache and cold cache of the data in the buffer pool.
Also have you looked at Minimum memory per query server setting?
https://msdn.microsoft.com/en-us/library/ms191303.aspx
Do you know you can do recompiles at the statement level?
Keep fixed plan is a good option however it will prevent recompliles if there are any changes to the underlying schema at a later point and this can harm performance later.
August 2, 2015 at 8:33 am
Quick question, can you post the specs and info (CPUs, Total Server Memory, Allocated Memory, Page Live Expectancy, Cache Hit Ratio etc.), got a hunch that some vital information are missing. Note that 12 sec. compile time is to say the least, not normal.
😎
August 3, 2015 at 4:11 am
How can you be certain that the extra time taken for the first run of this stored procedure is the time taken to compile and not the time taken to load data to a cold cache? The latter seems more likely.
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply