February 8, 2016 at 3:35 pm
When you get the chance, here's some great material for you to review:
http://www.sommarskog.se/query-plan-mysteries.html
http://www.sommarskog.se/dynamic_sql.html
http://www.sommarskog.se/dyn-search.html
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 9, 2016 at 12:31 pm
TheSQLGuru (2/8/2016)
When you get the chance, here's some great material for you to review:http://www.sommarskog.se/query-plan-mysteries.html
Come on brother!. At least now I am expecting a better answer after posting so many details. If you point me to the blogs or other things then it won't help me much. I've been through them, and have come over here for help. In case if I was an expert in writing codes or reading an execution plan I would not have come here for help. Please understand I am not being rude, but being a bit more practical.
Thanks
February 9, 2016 at 12:50 pm
ffarouqi (2/9/2016)
TheSQLGuru (2/8/2016)
When you get the chance, here's some great material for you to review:http://www.sommarskog.se/query-plan-mysteries.html
Come on brother!. At least now I am expecting a better answer after posting so many details. If you point me to the blogs or other things then it won't help me much. I've been through them, and have come over here for help. In case if I was an expert in writing codes or reading an execution plan I would not have come here for help. Please understand I am not being rude, but being a bit more practical.
Thanks
I already told you that I will not help you on your MANY problems with the code you have posted, and I am honestly extremely doubtful anyone else will step up to assist either. Here is the relevant part of my prior post as to why I won't help:
...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.
I will point out that there are several very talented consultants here on SSC.com that could be able to assist you with these issues as a paid engagement.
I do wish you the best in resolving your issues, or being able to get someone to resolve them for you.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 9, 2016 at 1:07 pm
ffarouqi (2/9/2016)
I have attached the actual execution plan for the queries that I already posted a couple of weeks back.At least now I am expecting a better answer after posting so many details.
How long did it take to run these queries from which you have posted execution plans? And how much faster you want them to run?
February 9, 2016 at 1:14 pm
Alexander Suprun (2/9/2016)
ffarouqi (2/9/2016)
I have attached the actual execution plan for the queries that I already posted a couple of weeks back.At least now I am expecting a better answer after posting so many details.
How long did it take to run these queries from which you have posted execution plans? And how much faster you want them to run?
Thanks! Alex. It is already executing quickly within no time when I execute the same code from ssms, but when this same query is run from the application side it sometimes take about 30s, and sometimes more than that, but again this does not happen frequently. However, I am really not a 100% sure if this is anything to do with the no. of executions from the application that hit this database, and starts building pressure on memory/cpu. I also see these queries being the top consumer as far as waits are concerned. I mean this is a beefy server with 512 GB RAM, and 64 core processor, so we should ideally not see this happening, but I am confused as to what I should do with these queries, and the management here keeps looking at SolarWinds, and when there is a red exclamation for these queries they send it across to me to investigate. I am not sure what suggestion I should provide at this point. I know I am not paying anyone but I am in need of some help/guidelines to communicate to management out here.
February 9, 2016 at 1:47 pm
Work through the method described here: https://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/
Yes, it's out of date, no I haven't got around to updating it with Extended Events. It's still valid, you can use Extended Events in much the same way. Identify what the worst aggregate CPU consumers are, not individual. Once you've identified the worst 3-5 queries, capture the plans from the application using Extended Events, not from SSMS (they can be different) and tune those.
Try out the index suggestions in the plans. They're suggestions and hence need testing, but they can be a useful place to start.
I'd link the video on my overall tuning methodology, but I think I know what kind of reply that'll get.
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
February 9, 2016 at 3:37 pm
ffarouqi (2/9/2016)
Thanks! Alex. It is already executing quickly within no time when I execute the same code from ssms, but when this same query is run from the application side it sometimes take about 30s, and sometimes more than that, but again this does not happen frequently.
So if it runs fast with the provided plans, then what do you want us to optimize?
There are multiple reasons why it sometimes can run slow, for example, because of the blocking issues, and you won't be able to see it in the plan, you need monitoring solutions.
ffarouqi (2/9/2016)
and the management here keeps looking at SolarWinds, and when there is a red exclamation for these queries they send it across to me to investigate.
Why management is looking at SolarWinds, not the DBAs? Also do you understand that we don't see your SolarWinds red exclamations and have no idea what exactly does it say the problem is?
ffarouqi (2/9/2016)
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.
What are these execution numbers? What is the time period? 5 minutes? A day? A month? Why do you think it causes any issues on the server itself?
What exactly do you call a "memory/cpu pressure" or "the top wait" ?
ffarouqi (2/9/2016)
I mean this is a beefy server with 512 GB RAM, and 64 core processor
Have anyone configured MAXDOP properly?
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply