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?
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.
- 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)
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.
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.
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.
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.
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.