Troubleshooting with the Microsoft 2008 R2 Tools

This is a live post from the session of Guy Glantser at Tech Ed Israel 2010 .

Top - Down:

Performance Monitor:
%Processor Time
% Disk Time
Page faults \ sec
Buffer cache hit ratio
Full scans \ sec
SQL complications \ sec

Resource Governor:
Managing SQL server workload and system resource consumption
In essence, we check how much each query, application etc. require from our different resources. We can check by groups, resources and so on.
We can then decide how to limit the use of the resource.

Dynamic Management Objects:
Querying DMVs to check query statistics, usage of buffers, partitions, indexes.
All the data we get is from the last time we did a restart to the server. We need to consider whether we have enough information to have relevant statistics. At the same time, this enables us the investigation of a recent problem.
The numbers we see should be viewed in relation to your system and server. A certain number of scans can be considered high or low - considering your specific situation.

Data Collector:
New to SQL Server 2008. Including data on disk usage, query statistics and server activity. We get pre-defined reports which we can drill down on.

Extended Events:
In the next version of SQL Server the profiler will be based on this tool.