Where can I retrieve historical database performance report?

  • Hi, I would want to know, how can I get a report of the DB performance for a particular time frame?

    A few of my users reported some slowness for a certain jobs accessing a table during a specific timeframe. I was hoping to pull a report out to see if there's any abnormal high load during that time.

  • Unless you already have a monitoring tool in place then there is nothing you can do to report past events

    If you do have a tool then use it.

    If not then consider that you may need to implement one for future needs. Here is a list of some https://www.pcwdld.com/best-sql-server-performance-monitoring-tools

  • You can run perfmon and get it to log chosen stats to a flat file or even a SQL Server database. But unless you have something in place and running, like perfmon, to monitor and store the server performance statistics you won't be able to get that data after the event.

     

  • 3rd party monitoring tool is the best way to go. sys.dm_os_ring_buffers DMV provides very limited history for four hours and you can find scripts to parse the xml and get the result you need. But this is unlikely to give very much details to pinpoint issues, good for trend analysis if you don't have any 3rd party tool and want to do it yourself. I collect those every four hours via agent job. It will be phased out after four hours.

  • SQL Server doesn't track much by default. There is data in the DMVs, but all monitoring tools do is read this, aggregate, store, and manage data retention. If you don't have this set up, you can't go back.

    You can set this up, but you're writing software, which is a time sink and can be hard. Often purchasing a third party tool, like SQL Monitor, is a better use of time and resources. Download some trials of different products and give them a try. You can quick demo SQL Monitor at Monitor.red-gate.com

    Disclosure: I work for Redgate Software, maker of SQL Monitor.

  • Check BrentOzar (First Responder Kit) and Adam Mechanic (SpWho2Active) tools, you can run those and save the result to tables that will give you some idea

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply