Database Reference
In-Depth Information
WHERE COMMAND_ELAPSED_TIME_MS > 0
ORDER BY COMMAND_CPU_TIME_MS DESC
The
Active Sessions
page checks the
$system.DISCOVER_SESSIONS
DMV with
the session status set to
1
using the following query:
SELECT SESSION_SPID,SESSION_USER_NAME,
SESSION_START_TIME,
SESSION_ELAPSED_TIME_MS,SESSION_CPU_TIME_MS,
SESSION_ID
FROM $SYSTEM.DISCOVER_SESSIONS
WHERE SESSION_STATUS = 1
ORDER BY SESSION_USER_NAME DESC
The
Dormant sessions
page runs a very similar query to that of the
Active Ses-
sions
page, except it checks for sessions with
SESSION_STATUS=0
—sessions that
are currently not running any queries. The result set is also limited to the top 10 ses-
sions, based on idle time measured in milliseconds.
The
Locks
page examines all the columns of the
$system.DISCOVER_LOCKS
DMV to find all requested locks as well as lock creation time, lock type, and lock
status. As you have already learned, the lock status of
0
indicates that the request
is blocked, whereas the lock status of
1
means that the request has been granted.
Analysis Services blocking can be caused by conflicting operations that attempt to
query and modify objects. For example, a long running query can block a process-
ing or synchronization job from completion because processing will change the data
values. Similarly, a command altering the database structure will block queries. The
database administrator or instance administrator can explicitly issue the
LOCK
XMLA
command as well as the
BEGIN TRANSACTION
command. Other operations request
locks implicitly.
The following table documents the most frequently encountered Analysis Services
lock types:
Search WWH ::
Custom Search