Top – Down:
% Disk Time
Page faults \ sec
Buffer cache hit ratio
Full scans \ sec
SQL complications \ sec
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.
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.
In the next version of SQL Server the profiler will be based on this tool.