October 12, 2012 at 4:47 am
So we host a vendor application that has some pretty serious performance issues. A DBA for the vendor has suggested that we run the following commands every 5 minutes with a SQL agent job. It seems pretty unusual to me;
dbcc freesystemcache('SQL Plans');
dbcc freesystemcache('TokenAndPermUserStore')
dbcc freesystemcache('Bound Trees');
Also since the vendor made some changes to one very heavily executed stored proc (2 Million executions per day) the average execution time has doubled. It was recommended that we manually recompile the sp every 5 minutes as well. IE sp_recompile. The vendor believes that a bad execution plan is being created. Again seems quite unusual.
Even given the fact that this application is poorly written do these recommendations seem reasonable?
We're running SQL 2008 SP1 (x64).
October 12, 2012 at 4:53 am
It is highly unusual.
Does the proc in question gradually decrease in performance? Does it improve when recompiled?
If it is a single procedure why not add the RECOMPILE option to the procedure?
It just doesnt make sense to me, clearing the entire buffercache on a scheduled basis for a single stored procedure is not required and doesnt stop the possibility that the procedure will pick up a bad plan on the next compilation. You have been asked to run something without correct diagnosis of the problem in my opinion.
October 12, 2012 at 6:42 am
I agree with MysteryJimbo that if the procedure has been caught doing nasty things time to time, we may add recompile option on the procedure level rather than running dbcc commands blindly.
I wish some more experts pitch in here to advise you because using recompile option will be like a band-aid to the problem and with the frequency of the executions of that procedure you mentioned at some point of time even recompiling may hurt. You may try to reach the root cause of the issue and if this is parameter sniffing using recompile at the statement level or procedure level may help. In some cases declaring a local variable and copying the passed parameter value too may give you some good results.
Thanks
Chandan
October 12, 2012 at 7:22 am
chandan_jha18 (10/12/2012)
I agree with MysteryJimbo that if the procedure has been caught doing nasty things time to time, we may add recompile option on the procedure level rather than running dbcc commands blindly.I wish some more experts pitch in here to advise you because using recompile option will be like a band-aid to the problem and with the frequency of the executions of that procedure you mentioned at some point of time even recompiling may hurt. You may try to reach the root cause of the issue and if this is parameter sniffing using recompile at the statement level or procedure level may help. In some cases declaring a local variable and copying the passed parameter value too may give you some good results.
Thanks
Chandan
The problem is the amount of information available. As its a vendor there are potential copyright issues with the DML so I havent requested it.
I would refuse to put this in place and look at alternatives. You should easily be able to prove its the execution plan by getting it out of the cache when it performing well and again when its performing poorly. Guessing doesnt work.
October 12, 2012 at 8:28 am
Something like this tormented me for a several month stretch some years ago. The vendor app had a SQL module for each DBMS (SQL Server, Oracle, etc.) and the dynamic SQL worked well with some parameters, very badly with others. Sounds like something similar is happening here. You should capture the SQL and XML plans from times when the system is performing well AND bad, and then at least you or the vendor will be able to compare the two.
October 12, 2012 at 8:49 am
Oh wow, that's horrid...
The main problem here is the 'believes'. Performance recommendations should never be made on what someone believes may be happening. They should be based on what exactly is happening, and that should come from investigations and analysis.
Depending how heavily used the server is, those additional compiles could tip it over the edge into worse performance, not help. If there are bad execution plans being cached, then the procedures need to be examined to see why they are prone to bad plans and appropriate solutions put in place (could be rewrites, could be hints, could be recompiled)
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
October 12, 2012 at 9:09 am
jackimo (10/12/2012)
So we host a vendor application that has some pretty serious performance issues. A DBA for the vendor has suggested that we run the following commands every 5 minutes with a SQL agent job. It seems pretty unusual to me;dbcc freesystemcache('SQL Plans');
dbcc freesystemcache('TokenAndPermUserStore')
dbcc freesystemcache('Bound Trees');
Also since the vendor made some changes to one very heavily executed stored proc (2 Million executions per day) the average execution time has doubled. It was recommended that we manually recompile the sp every 5 minutes as well. IE sp_recompile. The vendor believes that a bad execution plan is being created. Again seems quite unusual.
Even given the fact that this application is poorly written do these recommendations seem reasonable?
We're running SQL 2008 SP1 (x64).
Wow.:crying:
Based on my experience, the average vendor "DBA" is an idiot with very little knowledge of how to tune a production application.
I think you need to start by looking at the stored procedure to see why it is getting bad performance. Examine the query plan carefully, look for the places where it is using the most resources, and see what you can do to improve the performance: eliminate non-sargable queries, add indexes if needed, etc.
I also recommend doing a trace to look a the calls to that procedure. If the duration, cpu, reads, or writes vary a lot, look at the calls to see if particular combinations of parameters are a problems.
In other words, look for the problem, find it, and fix it.
October 12, 2012 at 9:50 am
Thanks for all the replies. I recompiled the SP with no performance improvement. The query has been very predictable since they made changes to it. It's predictable in that it's around 2X slower than it had been.
I'll continue to look at the execution plans to see if I notice anything odd but at this point I think its just poorly thought out t-sql.
Thanks!
October 12, 2012 at 9:54 am
Doesn't sound like bad plans then, or at least not the type that can be fixed with a recompile. Considered gettign a consultant in?
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
October 12, 2012 at 2:24 pm
GilaMonster (10/12/2012)
Doesn't sound like bad plans then, or at least not the type that can be fixed with a recompile. Considered gettign a consultant in?
No joy doing that with a vendor supplier, you can't make changes. You have two options here really. 1) Find a new product. 2) If you're big enough and dropping enough $$ on the product, you have leverage. Use it against the vendor. Heavily. Tell THEM to hire the consultant.
You're in a pickle, and it's one of the primary reasons we usually don't have a lot of nice things to say about vendor apps. You never remember the good ones.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
October 12, 2012 at 2:57 pm
Evil Kraig F (10/12/2012)
GilaMonster (10/12/2012)
Doesn't sound like bad plans then, or at least not the type that can be fixed with a recompile. Considered gettign a consultant in?No joy doing that with a vendor supplier, you can't make changes. You have two options here really. 1) Find a new product. 2) If you're big enough and dropping enough $$ on the product, you have leverage. Use it against the vendor. Heavily. Tell THEM to hire the consultant.
You're in a pickle, and it's one of the primary reasons we usually don't have a lot of nice things to say about vendor apps. You never remember the good ones.
There are good ones?
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
October 12, 2012 at 3:30 pm
GilaMonster (10/12/2012)
Evil Kraig F (10/12/2012)
GilaMonster (10/12/2012)
Doesn't sound like bad plans then, or at least not the type that can be fixed with a recompile. Considered gettign a consultant in?No joy doing that with a vendor supplier, you can't make changes. You have two options here really. 1) Find a new product. 2) If you're big enough and dropping enough $$ on the product, you have leverage. Use it against the vendor. Heavily. Tell THEM to hire the consultant.
You're in a pickle, and it's one of the primary reasons we usually don't have a lot of nice things to say about vendor apps. You never remember the good ones.
There are good ones?
Yep. Working with 3/5 now in the financial industry, and we're about to bring another one that looks good on board. One of the problem children is 'you get what you paid for', it wasn't meant for the scope of our data volume. Another is "you got what they sold you", which is a different issue altogether and we're swapping that one out.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply