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.
DynamicTanAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

arnoldCommented:
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.
Vitor MontalvãoMSSQL Senior EngineerCommented:
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DynamicTanAuthor Commented:
Thanks!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Windows Server 2012

From novice to tech pro — start learning today.