January 29, 2016 at 10:07 am
Hi,
I'm actually facing an issue at this point, and there seems to be successive cpu/memory pressure due to the attached code that's running below.This code consists of a tables, joined through views, and synonyms, and I have also scripted out those details from the database, so one can actually create and test it, of course it is without data.
Kindly, let me know if someone can help me find out where the pain point lies, and how can I modify this script to improve execution performance with less load on the cpu or at least point me in the right direction.
Regards,
Fz
January 29, 2016 at 10:26 am
Can you limit the table definitions to just the tables relevant to the query please, and include the index definitions.
Problem 1:
AND (@PickListGuid is NULL or PList.PickListGuid=@PickListGuid)
AND (@ProjectGuid is NULL or P.ProjectGuid = @ProjectGuid)
AND (@JobOrderGuid is NULL or JOH.JobOrderGuid = @JobOrderGuid)
AND (@OrderGuid is NULL or O.OrderGuid=@OrderGuid)
See - https://www.simple-talk.com/sql/t-sql-programming/how-to-confuse-the-sql-server-query-optimizer/
Once you're fixed that, please post the actual execution plan (only once you've fixed that catch-all query) as a .sqlplan file
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
January 29, 2016 at 11:08 am
Hi Gail,
I am not sure what do you mean by catch-all. Could you please explain me in a bit more detail about what is wrong in that piece of code that is causing problems. I have attached only the table, and index definition along with synonym and view which is relevant to the query. In case if this can be re-written in a better way than the one they have now it would make a lot of difference, especially if we could incorporate that into a stored proc.
I shall shortly send you the query plan mostly by Monday, as my organization where I currently work has lots of stupid restrictions in terms of security that I can't do much about.
Thanks!
January 29, 2016 at 11:16 am
I guess you missed the link.
GilaMonster (1/29/2016)
See - https://www.simple-talk.com/sql/t-sql-programming/how-to-confuse-the-sql-server-query-optimizer/
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
January 29, 2016 at 11:20 am
I did not miss the link, but at this point of time. It seems to be too lengthy of an article to read, and understand that, and no doubt about that the article itself is informative, but if you could describe the issue in your words. I mean just an overview of what exactly seems to be a bottleneck would certainly help here.
Thanks!
January 29, 2016 at 11:43 am
Since I wrote that article, it's already in my words.
Skim the first section and read the second section. You can skip the rest of it for now. The second section covers what causes the problems with this query form, shows examples and gives options on fixing it
Quite frankly, I wrote the article so that I wouldn't have to write a page or two into a forum thread every time it comes up.
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
January 29, 2016 at 12:50 pm
GilaMonster (1/29/2016)
Since I wrote that article, it's already in my words.Skim the first section and read the second section. You can skip the rest of it for now. The second section covers what causes the problems with this query form, shows examples and gives options on fixing it
Quite frankly, I wrote the article so that I wouldn't have to write a page or two into a forum thread every time it comes up.
And it comes up A LOT ... and it is DEVASTATINGLY BAD.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
January 29, 2016 at 1:10 pm
ffarouqi (1/29/2016)
I did not miss the link, but at this point of time. It seems to be too lengthy of an article to read, and understand that, and no doubt about that the article itself is informative, but if you could describe the issue in your words. I mean just an overview of what exactly seems to be a bottleneck would certainly help here.
If you don't want to spend any of your valuable time in order to understand the root cause of this kind of issues then simply put OPTION(RECOMPILE) to your query.
January 29, 2016 at 1:47 pm
Come on people, forums are meant to ask questions and help each other in a good way. I know you all have vast amount of experience that does not mean everyone else posses the same. I thank Gail for pointing me in the right direction, but as the Alien headed said to use option(recompile) I still do not want to go that route, because I have attached very similar code below, it might not be the same catch-all query, and this even after using option(recompile) is causing problems, because it is not a one time run of this code from the application, but multiple executions almost all at once, and each time it has to recompile it puts pressure back to cpu. The problem also in this organization is that they think anything > 30% is a problem, and cpu is facing pressure even if there is no version limitation to go with that number. It is a beefy server with x no. of cores, and I am not sure if it is the code that is a problem or if the server setting needs to be changed.
My only question or concern was to have these in-line sql been encapsulated into a stored procedure, and I was only asking if this is a possibility, because I think Gail mentioned it in the blog to use Dynamic SQL as a better solution for this...right?
January 29, 2016 at 2:07 pm
GilaMonster (1/29/2016)
Since I wrote that article, it's already in my words.Skim the first section and read the second section. You can skip the rest of it for now. The second section covers what causes the problems with this query form, shows examples and gives options on fixing it
Quite frankly, I wrote the article so that I wouldn't have to write a page or two into a forum thread every time it comes up.
Hi Gail,
In case if you do not mind me asking is there anything what we can do for these queries. Anything that can make this better. These are all pretty much same queries with same problems multiple executions causing cpu spikes. As explained I will try and send the query plan, but would it make any difference, because the values may be different all the time. Please let me know I am only at the start of things in my career, and a little help can make me better.
Thanks
January 29, 2016 at 2:37 pm
ffarouqi (1/29/2016)
Come on people, forums are meant to ask questions and help each other in a good way. I know you all have vast amount of experience that does not mean everyone else posses the same. I thank Gail for pointing me in the right direction, but as the Alien headed said to use option(recompile) I still do not want to go that route, because I have attached very similar code below, it might not be the same catch-all query, and this even after using option(recompile) is causing problems, because it is not a one time run of this code from the application, but multiple executions almost all at once, and each time it has to recompile it puts pressure back to cpu. The problem also in this organization is that they think anything > 30% is a problem, and cpu is facing pressure even if there is no version limitation to go with that number. It is a beefy server with x no. of cores, and I am not sure if it is the code that is a problem or if the server setting needs to be changed.My only question or concern was to have these in-line sql been encapsulated into a stored procedure, and I was only asking if this is a possibility, because I think Gail mentioned it in the blog to use Dynamic SQL as a better solution for this...right?
I am going to apologize in advance for the below sounding harsh, but you really have my dander up on this one (especially this line - Come on people, forums are meant to ask questions and help each other in a good way.)
I challenge you to prove to us that your CPU burn is because of the COMPILATION of queries. I have worked on VERY busy systems with THOUSANDS of executions per second of AD HOC queries and the CPU used for those compilation and query plan operations was not at all troubling. Having said that, statement level OPTION RECOMPILE will NOT be sufficient to fix your attached queries. There are a HUGE collection of things that are just going to blow up the optimizer or execution engine or both as written (see below). Dynamic SQL won't address all the issues either, but it could certainly help a good bit if done correctly (and with precautions against SQL Injection of course).
You didn't want to be bothered to read a VERY WELL WRITTEN and EXCEPTIONALLY APPROPRIATE BLOG post to help fix your problems. Yet you give us EIGHT HUNDRED AND TWENTY FIVE LINES of EXCEPTIONALLY complex and verbose queries with: lord-knows-what views, at least 6-level-deep parentheses in WHERE clauses, table vars, IS NULL OR, LOTS of ORs in WHEREs, GUIDs, aggregates, lots of IN (SELECT ...), OR NOT EXISTS, RANGE filtering, etc.
There are a LOT of things that can be done with these queries. But they go WAY WAY beyond the unpaid assistance you can get from an online forum. It would take someone several hours just to get a handle on the data structures involved and disassemble the views into their component parts. Indexing of the tables needs to be analyzed. You can't tune just one input so multiple evaluations would need to be run. Given the complexity intermediate temp table(s) may well be necessary to optimize and normalize the runtimes. In short REALLY fixing all of your posted queries (without fixing the likely structural issues underlying the queries) could easily be a week or more of dedicated time.
Also note that given that you are at the start of your career I will be straight up with you: you have essentially no hope of REALLY fixing this stuff. You simply don't have the tools and knowledge and experience at your disposal. You could go back and forth for WEEKS on a forum (and yes, I have seen that done) and still not make any progress - or even make things worse.
I agree with your statement that there are almost certainly settings and configurations on the IO System and Server Hardware and Windows and SQL Server to be fixed as well. I have NEVER come across a client that had even half of the stuff done right from those perspectives.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
January 29, 2016 at 2:38 pm
ffarouqi (1/29/2016)
I still do not want to go that route, because I have attached very similar code below, it might not be the same catch-all query, and this even after using option(recompile) is causing problems
Have anybody told you that OPTION(RECOMPILE) is going to fix all performance issues with all your queries?
ffarouqi (1/29/2016)
My only question or concern was to have these in-line sql been encapsulated into a stored procedure, and I was only asking if this is a possibility, because I think Gail mentioned it in the blog to use Dynamic SQL as a better solution for this...right?
Dynamic SQL is not necessary better. Yes, it will reduce the number of re-compilations, but it's more complex solution, it's harder to maintain, and it's vulnerable to SQL injections if coded poorly. Plus with this approach you may still hit another problem - parameter sniffing.
There is no universal solution to every performance problem. You need to consider multiple variables when optimizing a query, but in order to do that you have to understand how SQL Server works, why it's doing this or that.
January 30, 2016 at 1:42 am
ffarouqi (1/29/2016)
In case if you do not mind me asking is there anything what we can do for these queries. Anything that can make this better.
Exactly the same problem (the catch-all form of where clause), exactly the same solution.
The 'alien headed', who's name is Alex btw, did what you asked me to do and summarised that long article into a solution for you without all of the explanations, tests, caveats, demos, etc. Option recompile. It works very well for queries of this form, and the compilation overhead is usually way lower than the CPU used by the inefficient form. Doesn't mean it's the only solution needed, but it's the first step to getting that form of query to work. Once you've added recompile, we'll need to see execution plans to help tune further.
As I explained in the article (which I guess you still haven't read), I don't like dynamic SQL any longer, unless you're still on SQL 2005. The recompile solution works much better, much easier to maintain, easier to understand.
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
January 30, 2016 at 3:56 am
Kevin apologizes for sounding harsh ...
I am going to sound much harsher, and I am not even going to apologize for it.
ffarouqi (1/29/2016)
Come on people, forums are meant to ask questions and help each other in a good way.
Exactly!
Gail did offer you the perfect help by posting a link to a very good article on the issue. You chose not to accept that help.
If you want something else to do your job for you, hire a consultant. (It's what I do as my daytime job, but I guess that after this comment you'll prefer to hire someone much nicer). If you want us to help you get better, to help you learn to fix those issues yourself, put in the work.
February 8, 2016 at 2:33 pm
GilaMonster (1/30/2016)
ffarouqi (1/29/2016)
In case if you do not mind me asking is there anything what we can do for these queries. Anything that can make this better.Exactly the same problem (the catch-all form of where clause), exactly the same solution.
The 'alien headed', who's name is Alex btw, did what you asked me to do and summarised that long article into a solution for you without all of the explanations, tests, caveats, demos, etc. Option recompile. It works very well for queries of this form, and the compilation overhead is usually way lower than the CPU used by the inefficient form. Doesn't mean it's the only solution needed, but it's the first step to getting that form of query to work. Once you've added recompile, we'll need to see execution plans to help tune further.
As I explained in the article (which I guess you still haven't read), I don't like dynamic SQL any longer, unless you're still on SQL 2005. The recompile solution works much better, much easier to maintain, easier to understand.
Thanks! Gail for being kind, and considerate. I apologize for it. I know I sounded weird, but I am trying my best to provide you with whatever information I can. I have attached the actual execution plan for the queries that I already posted a couple of weeks back. I know I said this before, but as explained earlier they are already using option(recompile), and we are still facing this issue. Basically, they have this in-line sql with all the code running from the application (they aren't using stored proc), and if I give you the details from SolarWinds the top wait that I notice shows me memory/cpu pressure, and the total execution it does is 87,317, logical writes are pretty low, but it does a lot of logical reads I have attached the screen shot as well. I have the "Optimize for AdHoc Workloads" knob turned on, but I am curious is this because of the # of executions we are seeing those memory/cpu pressure waits. I do not have any visibility whatsoever from the application side, and neither I know about the application side of things, but the same code when run inside sql server using ssms, it works like a charm.
Appreciate all your help on this.
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply