Databases Reference
In-Depth Information
The main difference between DMVs and Perfmon counters is that the Perfmon counter is
a single numeric value reporting the server resource utilization, whereas a DMV reports
more detailed information. For example, the
MSAS 2008:Connection\Current connec-
tions
counter reports the total number of currently opened connections to Analysis
Services. While querying
Discover_Connections
, the DMV returns a list of all connections
and information about each one of the connections. DMVs, Perfmon counters, and traces
are all complementary mechanisms that enable you to access information about the
current Analysis Services state.
The performance-monitoring infrastructure enables you to see counter values in real time
using the Perfmon application, and enables you to record Perfmon counter values in the
form of a log. After you have collected your Perfmon log, you can use the often-over-
looked SQL Server Profiler functionality that enables you to correlate a Perfmon log with a
previously recorded trace.
NOTE
For Analysis Services 2005 to record a Perfmon counter log, you need to grant access
to the server bin folder
(%program files%\Microsoft SQL Server\MSSQL.2\OLAP\bin
)
for the
LocalMachine\Performance Log Users
security group. In Analysis Services
2008, this is no longer required.
To make it easier to access Analysis Services Perfmon counters, we decided to expose the
Analysis Services Perfmon counters through DMVs. You can also write your own applica-
tion to retrieve Perfmon counters using the Perfmon API.
Retrieving Perfmon counters via DMVs is a little tricky because of one of the limitations of
the SQL language implementation by Analysis Services. Because multiple Perfmon coun-
ters could be available on a single computer, Analysis Services does not allow you to access
values of all of them in a single request. You must explicitly specify which counter you
need to see. To pass a name of the Perfmon counter to the DMV, use the
systemrestrictschema
function provided by Analysis Services. This function takes its para-
meter and uses it as a restriction to the underlying internal
SchemaRowset
request. (For
more information about the DMV internal architecture, refer back to the material earlier
in this chapter.) For example, to query for the value of the
MSAS 2008:Connection\Current
connections
counter, you need to send the following DMV query:
select * from systemrestrictschema ($system.discover_performance_counters,
PERF_COUNTER_NAME = '\MSAS 2008:Connection\Current connections')
You can also query for multiple Perfmon counters at the same time by specifying multiple
restrictions in the
systemrestrictschema
function. For example:
select *
from systemrestrictschema ($system.discover_performance_counters,
PERF_COUNTER_NAME = '\MSAS 2008:Connection\Current connections',
PERF_COUNTER_NAME = '\MSAS 2008:MDX\Total Cells Calculated')
