I've run pt-query-digest and noticed many outliers with very high query times. Many of these queries do not perform slowly when I run them myself, and I'm having trouble figuring out the root cause of these sporadic slow queries. Is there another tool I might use to shed more light on this situation? What might cause the same query to only be slow some of the time?
Add a comment
|
3 Answers
Queries can become slow in such cases:
- server is shorted out with disk i/o
- server is shorted out with memory and swapped
- server have too small
key_buffer_sizeand buffer should be refreshed prior to perform the query - server have too small
(join|sort)_buffer_sizeand on-disk temporary tables are created
Any of the mentioned above can cause sporadical slowdown. List is not exhaustive, sure.
In addition to @kondybas You can also have slow or different query plans. A query executed as a procedure, as a query and from an application can all have different query plans. Also, depening on the queries you can have parameter sniffing issues too. Use sp_blitzcache and have a look and see if any of your queries are there and what the actual execution plans are compared to when you run the queries. If they are different, and the saved plan is worse, then you have one of the above problems.
And also you have to do the "explain" command for your queries. If that query doesn't use any indices then that also increase the execution time.
Compare your local dataset size and the actual prod data size. and optimize your query.
