Frequently Queries Used by Production Support DBA to support Analysis Services

Leave a comment (0) Go to comments

$System.DISCOVER_CONNECTIONS – Provides resource usage and activity information about the currently opened connections on the server.

Usage

SELECT * FROM $System.DISCOVER_CONNECTIONS

OUTPUT

Frequently Queries Used by Production Support DBA to support Analysis Services

DISCOVER_COMMANDS - Provides resource usage and activity information about the currently executing or last executed commands in the opened connections on the server.

USAGE

SELECT * FROM $System.DISCOVER_COMMANDS 

This will return results in following column, COMMAND_CPU_TIME_MS, COMMAND_ELAPSED_TIME_MS, COMMAND_READ_KB, COMMAND_READS, COMMAND_START_TIME, COMMAND_WRITE_KB, COMMAND_WRITES, SESSION_COMMAND_COUNT, SESSION_SPID


Other Available XMLA Analysis Schema Objects, which gives us more information

  1. DISCOVER_CONNECTIONS
  2. DISCOVER_COMMANDS
  3. DISCOVER_COMMAND_OBJECTS
  4. DISCOVER_DATASOURCES Rowset
  5. DISCOVER_DB_CONNECTIONS
  6. DISCOVER_ENUMERATORS Rowset
  7. DISCOVER_JOBS
  8. DISCOVER_KEYWORDS Rowset (XMLA)
  9. DISCOVER_LITERALS Rowset
  10. DISCOVER_LOCKS
  11. DISCOVER_OBJECT_ACTIVITY
  12. DISCOVER_OBJECT_MEMORY_USAGE
  13. DISCOVER_PROPERTIES Rowset
  14. DISCOVER_SCHEMA_ROWSETS Rowset
  15. DISCOVER_SESSIONS
  16. DISCOVER_XML_METADATA Rowset

Most of the time, DBA ended running up following queries.

–Will return a detailed list of active client connections by using this command.

SELECT * FROM $System.DISCOVER_CONNECTIONS

– For each connection there is always a user session; a list of these sessions can be obtained with the following command.

SELECT * FROM $System.DISCOVER_SESSIONS

– In the resultset, for each session we can see the last command issued and the current database, the date/time when the connection started

and the date/time of the last command.

Finally, we can see a list of all currently executing commands with the following statement.

SELECT *

FROM $System.DISCOVER_COMMANDS

–Holds Analysis Services objects’ resource usage. It gives you a list of all objects and how much CPU each consumed, how much read and write

activity occurred and even whether access to it incurred an aggregation hit or miss

SELECT * FROM $system.DISCOVER_OBJECT_ACTIVITY

—-Analysis Services Activity Viewer 2008

If you are looking getting a detailed output like SP_WHO in SQL Server Database engine, you might want to develop your own solution to get more detailed and relevant information, where we will be joining multiple DataSets Click here to see more details on this.

EOF - Frequently Queries Used by Production Support DBA to support Analysis Services, SQL Server 2012 , 2008 R2 , 2008 , 2005 , 2000

Leave a Reply

Your email address will not be published. Required fields are marked *


*


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Notify me of followup comments via e-mail. You can also subscribe without commenting.