We help IT Professionals succeed at work.

How do I check historical performance on database within the SQL?

Hi All

We have an application configured to connect to a database on the SQL Server 2012 High Availability Group.

Users are complaining that the application is running very sluggishly and I suspect this could be due to the SQL performance issue.

Is there a way to check historical performance for a particular database within the SQL Server, if we may need to add additional resources to it?

Many Thanks.
Watch Question

Distinguished Expert 2019
The only way to look at historical performance is if you have monitoring tools that keep track of that performance.
Perfmon, base line.
Zabix, Nagios, cacti, etc. that collect this information.

One thing to consider to assess in your case is:
What are the specs of the server: CPU, Memory, Hard Drives.
What is the current database size, how many SQL instances you have and the size of the databases of each instance. The number and types of drives.

If your memory is X while the combination of your SQl instances/databases is such that the sum is Y time X, your slowdown is the consequence of less memory that needed resulting in sql services/data being written out to disk (swapping) which would contribute to a slower performance.)

If you do not have a performance monitoring setup in place, you should consider setting one up.  This will also tell you times when the needs are greatest and you will have data to point to when trying to implement modify adjust to deal with that.
IT Engineer
Distinguished Expert 2017
Is there a way to check historical performance for a particular database within the SQL Server
Like Arnold said you need to have a monitoring tool like SCOM, BMC Patrol, Nimsoft, or other.

Without those tools you still can do some analysis but may not enough for what you want.
Those analysis consists in:
- Performance Monitor - Utility from MS Windows that has 2 data collectors  (System Diagnostics and Performance)configured and disabled by default. Some Systems Administrator like to enable them after installing a Windows Server so check if you're lucky and those are running so you can see the data collected for the period you want.
- Default trace - MSSQL server has a default trace that is enable by default. If no one disabled it then you can access it and check the data for the period you want. This article shows how to work with the default trace.
- DMV's - You can also use DMV's to check the most expensive queries but mind that information is only in the memory so after a SQL Server restart is gone. Here's an article with good examples on how to check that.