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.