Is there anyway to view historical performance of SQL 2008 R2 database?

Question

So.. I was wondering, is there a way for me to generate any report for the historical performance of a MS SQL database? I'm originally an Oracle DBA, but now I'm requested to support a few SQL databases in my office as there's nobody supporting it now. Being an Oracle DBA, I know where to look if I need any reports related to DB performance e.g. AWR & Statspack but now MS SQL DB. Going around google doesn't yield any result as well unfortunately, since most requested me to turn on SQL profiler to run the trace, which I can't since it will cause even more load to the high loaded server. Is there a way to configure the DB to take snapshots every half hour and combine it into a report like how AWR / Statspack behave in Oracle?

Answer 1

System Dynamic Management Views

You might want to dive into the Execution Related Dynamic Management Views and Functions (Transact-SQL) that reside in a SQL Server instance. These views and functions do contains some information related to performance.

The suggested views and functions are a sub-set of the System Dynamic Management Views which contain various other sub-sets of views and functions, which may or may not be what you are looking for.

Further sub-sets

• Database Related Dynamic Management Views (Transact-SQL)
• Index Related Dynamic Management Views and Functions (Transact-SQL)
• I O Related Dynamic Management Views and Functions (Transact-SQL)
• Memory-Optimized Table Dynamic Management Views (Transact-SQL)
• Transaction Related Dynamic Management Views and Functions (Transact-SQL)
• ...

^{(Just to name a few)}

Note
Most of these views are recycled after an instance restart. You will have to dump the information to static tables in a (e.g.) Admin database if you want to retain historical data.

Built-In Reports

If you right-click on a database and move your mouse pointer into the reports, then you might find can occasional jewel of information:

Creating a Baseline for SQL Server

There is an extensive Q&A on DBA.SE with the title Creating SQL Server Performance Baseline Monitoring which show you how you could baseline a SQL Server instance. The article references other material.

And then you could start reading Erin Stellato's article 5 Reasons You Must Start Capturing Baseline Data for an interesting start into SQL Server "baselining".

Answering Your Question

So.. I was wondering, is there a way for me to generate any report for the historical performance of a MS SQL database?

There are indeed a couple of ways with the Management Views and Functions and Standard Reports. For a deep dive you are going to either build something yourself or purchase a third-party tool.

Answer 2

I use Brent Ozar's First Responder Kit PowerBI Dashboard. You install some procs, create a table to store the data and then schedule a job to populate the data.

You can drill down into performance of specific queries and lots of other cool stuff.

Answer 3

As for built-in features, older versions of SQL Server have the Management Data Warehouse, which will collect performance data and store it in a separate database, optionally on a separate server.

Newer versions add the Query Store which will automatically record query plans and stats, as well as query wait stats. And newer versions track wait stats at the session level.

Answer 4

Not out of the box, no. SQL Server does have a default trace, but the range of events captured is quite small https://www.databasejournal.com/features/mssql/a-few-cool-things-you-can-identify-using-the-default-trace.html and not much history is kept.

Extended Events are the server-side replacement for SQL Profiler, but there is a bit of a learning curve and you kind of already need to know what you are interested in.

A third party monitoring tool might be an option - maybe a free trial or demo version? If not, you could roll your own using PerfMon. SQL Server exposes a lot of counters which will give you stats on executions, locks, CPU, IO and so on, see https://www.sqlservercentral.com/articles/using-perfmon-and-pal-to-diagnose-sql-issues . It won't give you information about individual SQL statements though. Newer versions of SQL Server have a feature called the Query Store, which has similarities to Oracle's Active Session History.

HTH