query tuning general question
-
Hi All,
This is a very general question on query performance. Need some explanation on how can we make other teams understand that more work takes more time and more CPU.
There is a query which takes like 16 secs and it run multiple times in our environment.
The expectation from the app team is the query is should finish within 2 secs and they are setting query timeout of 2 secs in app.
My point is that even a valid or fair statement ? if a particular query has to do this much of work and it takes this much of time then how can they set 2 secs timeout for that query in the application.
What we can try helping them to get the sql stmt and check the actual execution plan and show where it is taking time.(high cost operators), missing index recommendations parameter sniffing ..etc
My point is that, how can a DBA can make other teams understand that query reading more data takes more time and a query reading less data will take less time to execute. How to put some points across with facts strategically? any analogies used for layman explanation would help in these cases? Am I missing anything?
Please help.
Thanks,
Sam
- vsamantha35 wrote:
how can a DBA can make other teams understand that query reading more data takes more time and a query reading less data will take less time to execute.
You are going to have to work with the developers to work out what the problem is and how to improve it so you will need to take the time to understand the basics of the framework they are using. (If you are lucky it will be Dapper, if you are unluckly it will be EF.) If it turns out to be purely a query tuning problem your best bet is to get them to call a SP which you can then tune. If the query is reading large transaction tables etc there may be a way of maintaining summary/balance tables etc. I would start by contacting the lead developer to discuss the situation before it becomes too political. I would also keep the people you report to in the loop so they are aware of the situation.
vsamantha35 wrote:There is a query which takes like 16 secs and it run multiple times in our environment.
This sounds as though there may be scope for the developers to use some sort of caching solution, like Redis, to take the load off the db.
-
Education.
Get extended events out and set up data collection. Show them how long queries take. Show them how blocking works, and how sometimes, you simply can't guarantee 2 seconds of performance. Show them execution plans that include scans on giant indexes and late filtering so that millions of rows of data are being accessed in order to filter down to 10. Educate them. It's the only way forward.
And a two second timeout is crazy. Remember, it's not just two seconds of processing time on the server. It's two seconds from connection to the return of the data and completion of the operation. That generally involves more than just the time within SQL Server gathering the data, however efficiently.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
Thank you Ken for the wise advise. As of now, it got escalated.
- Grant Fritchey wrote:
Education.
Get extended events out and set up data collection. Show them how long queries take. Show them how blocking works, and how sometimes, you simply can't guarantee 2 seconds of performance. Show them execution plans that include scans on giant indexes and late filtering so that millions of rows of data are being accessed in order to filter down to 10. Educate them. It's the only way forward.
And a two second timeout is crazy. Remember, it's not just two seconds of processing time on the server. It's two seconds from connection to the return of the data and completion of the operation. That generally involves more than just the time within SQL Server gathering the data, however efficiently.
Sir, since the query SLA is 2 seconds for few queries, capturing blocked process report with a blocked threshold of 2 secs , wouldn't be too much of trace/XE data. Very are see heavy blocking in our environment and it is generating a lot of trace data even if we ran for 4 hours. it is causing slowness and we have to stop the XE.
The other option, what we tried is capturing locking info using DMV queries and tried to schedule the code in a SQL Agent Job. But the problem when we try to schedule it, the minimum frequency we can set to run every 10 secs. It's not allowing to run every 1 sec or 2 secs.
We have enabled RCSI as well as Snapshot level ON at database level to see if it is helping in blocking. However, we still see blocking which is for writers which is understandable. (UPD's and INS's). Observing the txns are kept opened for long duration. The blocker spids are having status=sleeping and AWAITING COMMAND and open_tran > 0.
-
Is the blocked process report running as part of query collection? It shouldn't separate those out. One as one session. One as another. And, yeah, capturing query metrics, especially at the statement level is expensive. I would suggest only capturing batches and procedures until you know which ones are causing problems. Then, only capture statements for those, and only for short periods of time (30 minutes or less). Otherwise, you're just dealing with too much data.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning - Grant Fritchey wrote:
Is the blocked process report running as part of query collection? It shouldn't separate those out. One as one session. One as another. And, yeah, capturing query metrics, especially at the statement level is expensive. I would suggest only capturing batches and procedures until you know which ones are causing problems. Then, only capture statements for those, and only for short periods of time (30 minutes or less). Otherwise, you're just dealing with too much data.
Currently, we are 2 separate traces setup. Will below would work fine for both if it has to be one single trace? if one is fine, then will generate this code and convert into a XE using jonathan kehayias stored proc. will put column filter on database and duration > 1000 ms(i.e 1 sec). Will this okay?
-
I would be inclined to install and run these scripts first before getting involved with XEs:
https://www.brentozar.com/first-aid/
-
Oof. Trace. Not extended events. These are even more resource intensive than extended events. I'd turn them ALL off and go with XE, especially because your system is already under stress. Trace is just going to make it worse.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
I will be scripting it out as server side trace . Make changes (filename, maxfilesize,rollover etc) and then convert into an Extended Event using Jonathan's script
https://www.sqlskills.com/blogs/jonathan/converting-sql-trace-to-extended-events-in-sql-server-2012/
Sir, one thing I want to ask. What should be good size for @maxfilesize so that it should be copied over to a sub-prod environment for analysis.
-
Sir, is that a good thing to have 2 separate XE's , one for collect sql stmts and another one for collecting blocked processes on two different drives or one trace is sufficient? which is better?
-
You don't want any one session to do too much. It makes it harder to distinguish the information, search for what you want, etc.. Plus, you put more of a load on the system. You want to be cautious about that. Run anything that is load intensive (query capture, especially statement level capture, counts) in short bursts, not 24/7. Stuff like the blocking report should be pretty benign (on most systems, testing will always be your friend). To get started with blocked processes, read this blog post from Erin Stellato. In terms of consuming Extended Events data, I've got a ton of posts at ScaryDBA.com, but here's one, and a video that should help.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning - Grant Fritchey wrote:
You don't want any one session to do too much. It makes it harder to distinguish the information, search for what you want, etc.. Plus, you put more of a load on the system. You want to be cautious about that. Run anything that is load intensive (query capture, especially statement level capture, counts) in short bursts, not 24/7. Stuff like the blocking report should be pretty benign (on most systems, testing will always be your friend). To get started with blocked processes, read this blog post from Erin Stellato. In terms of consuming Extended Events data, I've got a ton of posts at ScaryDBA.com, but here's one, and a video that should help.
Thank you Sir. Will take a look at the resources and test it one by one.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply