Is there anyway to view historical performance of SQL 2008 R2 database? 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 &amp; 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.

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&amp;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.