Slowly running a query that always runs fast

Question

I have two tables that 99% of operation on it is simple select. Rarely we have insert/delete and updates.

There is a inner join on these tables and execution plan of the query is index seek. 99% of times the query run very fast (about 1 second). But there is some times that query longs 30 seconds to run.

There isn't any pattern on when this was happened. One time this happened on '1 dec 3:17:20 am'. One time on '8 dec 10:13:43 pm'. One time happened on '11 dec 16:50:43 pm'.

How can I find clues for where this problem comes from?

Does SQL Server have a tool for logging all events?

My query just has simple where clause: where id = @id.

Answer 1

SQL Server 2014 doesn't give you a lot of options for investigating poor performance of a query that ran in the past. You could try checking the error log to see if any catastrophic events occurred near your slow query executions. For example, perhaps there was a long I/O that took more than 15 seconds. You could also check the system health extended event. For example, perhaps there was a long lock wait (>30 seconds) that occurred near your slow query execution.

It sounds like what you really need is to increase application logging (you already know the precise second when the error occurred, so consider logging performance information when the query runs longer than expected), purchasing a SQL Server monitoring tool such as SentryOne, or upgrading to SQL Server 2017 and looking at query store wait stats. You should know that investigating a performance issue that occurred a year ago will be difficult even with many of those tools.

Answer 2

If it is really just a simple SELECT ... FROM tbl where tbl.id = @id (without joins etc.) I'd assume, that this is a blocking problem. Often this is the result of a poorly written application:

• user tries to save e.g. an order
• app launches a begin transaction
• app inserts the row into the order table
• app finds something that has to be confirmed by the user (e.g. unplausible delivery date)
• app opens a dialog to ask the user, if this is really correct
• user confirms and the app executes a COMMIT or he declines and the app does a ROLLBACK

Usually the user clicks fast onto the button and everything is fine. But sometimes he goes to lunch or gets a call or whatever and sometimes even the app frontend / browser crashes, so you now have a long lock on this row. When the user himself or another guy now wants to select the the same ID, he has to wait...

Answer 3

Erik Darling’s above answer is likely what you’re looking for. So I won’t go into too much detail here.

Do you know that the query is using the same execution plan each time? I’ve seen numerous times where an application uses dynamic SQL, polluting the plan cache. 95% of the time the parameter used returns a handful of rows. The typical plan for that is usually an index seek. Then the other 5% of the time, the parameter matches many more rows and SQL does an index scan.