SQLTeam.com | Weblogs | Forums

Finding highest IO queries in a specific time period?


#1

I use many queries to find problem queries. But they all present data since SQL Server was last restarted. Not being able to find worst queries yesterday or in a specific time interval when problems were happening seems like a massive oversight by Microsoft. Perhaps I'm missing something. Is there a way to do this?


#2

No, not unless you captured the query stats earlier yourself.

The latest version of SQL Server 2016 has a query store that will keep old plans, even across SQL Server restarts.


#3

I presume that storing all such stats on an ongoing logging-basis ... until ... when?? ... would be a massive undertaking just on the offchance that you might need to check yesterday's figures between 11:00 and 11:15.

You can run SQl Profiler, continuously, to log that data if you think you might need it.

We have all our SProcs log their Start / End times, and we use that (in real time) to alert that one/many/all SProcs are running slower than their norm (and we do use Recompile automatically as a Bandaid in some circumstances)

But we had to build that, and we have to purge the stale logging data, its just our solution to how we perceive we want to handle that situation (which is probably not quite the same as yours).. Our logging data takes up far more space in the DB than the client's own OLTP does !!