Queries running fast most of the time but >100x slower other times

by ColdAir   Last Updated January 12, 2018 16:06 PM

I know this is a very generic question, but if someone can point me to what else I should investigate/check I would greatly appreciate it.

We have a view which runs around 15 seconds most of the time but during random times starts running around 45 minutes. These long running episodes last for hours and all of a sudden out of now where go back to running normal times. We using SQL SERVER 2016. We have one dedicated hard drive for data, one for log and one for backup.

I have confirmed the following during the long running episodes:

1) There are no transactions open

2) There are no other heavy queries running. Used the query below:

SELECT sqltext.TEXT,
    req.session_id,
    req.status,
    req.command,
    req.cpu_time,
    req.total_elapsed_time
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext

3) There are no backups running and anti-virus does not scan the database files

4) Execution plans between the normal and the long runs are exactly the same (at least the execution plan of the top level view. The top level view unions some child views)

I am in no way a database administrator and getting one is not one of the available options.

I am wondering else could be causing these issues and what kind of queries I can run to determine the culprit. If there is anything else you would like to know please let me know.

Thank you



Related Questions


Converting MTVF into In-Line TVF

Updated June 19, 2018 09:06 AM

Why is query choosing horrible execution plan?

Updated December 04, 2017 23:06 PM