SSAS 2008: What's new in the Schema Rowsets?

I was asking myself this question recently and while I could not find anything in Books Online, it is not too hard to figure this out for ourselves. The following comparison was run between SSAS 2005 SP2 and the February CTP (CTP6) of SSAS 2008.

I really love the way that you can discover the metadata in SSAS, all I had to do is to run a DISCOVER_SCHEMA_ROWSETS XML/A command against each server and then use XMLNotepad 2007 to compare the resulting XML.

In the SQL Server 2008 you should find that features are not included in a CTP until they are pretty much complete, including documentation. So you can find documentation for all of this stuff under the "Schema Rowsets" section in Books Online (BOL) here: http://msdn2.microsoft.com/en-us/library/ms126233(SQL.100).aspx. In fact I have used a number of snippets from BOL in the rest of this post.

Changes to existing Rowsets

MDSCHEMA_MEMBERS Added SCOPE (int) restriction

from BOL:

The scope of the member. The member can be a session calculated member or global calculated member. The column returns NULL for non-calculated members.

This column can have one of the following values:

MDMEMBER_SCOPE_GLOBAL=1

MDMEMBER_SCOPE_SESSION=2

MDSCHEMA_SETS Added SET_EVALUATION_CONTEXT (int) restriction

from BOL:

The context for the set. The set can be static or dynamic.

This column can have one of the following values:

MDSET_RESOLUTION_STATIC=1

MDSET_RESOLUTION_DYNAMIC=2

MDSCHEMA_KPIS Added SCOPE (int) restriction
  (see above for a definition of SCOPE)
DISCOVER_TRACE_EVENT_CATEGORIES changed SPID restriction from string to unsignedInt
changed MemoryUsed restriction from int to long
changed BaseObjectType restriction from string to unsignedInt
DISCOVER_SESSIONS

Added SESSION_STATUS (int) restriction

from BOL:

The activity status of the session.

0 means "Idle": No current activity is ongoing.

1 means "Active": The session is executing some requested task.

2 means is "Blocked": The session is waiting for some resource to continue executing the suspended task.

 

New Rowsets for SQL 2008 (as of CTP6)

I found 4 completely new rowsets DISCOVER_COMMANDS, DISCOVER_COMMAND_OBJECTS, DISCOVER_OBJECT_ACTIVITY and DISCOVER_OBJECT_MEMORY_USAGE. I have included a brief overview and a link to the MSDN page for each of them below.

 

Name DISCOVER_COMMANDS
Restrictions SESSION_SPID (int)
Columns SESSION_SPID (int)
SESSION_COMMAND_COUNT (int)COMMAND_START_TIME (dateTime)
COMMAND_ELAPSED_TIME_MS (long)
COMMAND_CPU_TIME_MS (long)
COMMAND_READS (long)
COMMAND_READ_KB (long)
COMMAND_WRITES (long)
COMMAND_WRITE_KB (long)

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

for more details see: http://msdn2.microsoft.com/en-us/library/bb934103(SQL.100).aspx

image

 

Name DISCOVER_COMMAND_OBJECTS
Restrictions SESSION_SPID (int)
SESSION_ID (string)
OBJECT_PARENT_PATH (string)
OBJECT_ID (string)
Columns SESSION_SPID (int)
SESSION_ID (string)
SESSION_COMMAND_COUNT (int)
OBJECT_PARENT_PATH (string)
OBJECT_ID (string)
OBJECT_VERSION (int)
OBJECT_DATA_VERSION (int)
OBJECT_CPU_TIME_MS (long)
OBJECT_READS (long)
OBJECT_READ_KB (long)
OBJECT_WRITES (long)
OBJECT_WRITE_KB (long)
OBJECT_ROWS_SCANNED (long)
OBJECT_ROWS_RETURNED (long)

for more details see: http://msdn2.microsoft.com/en-us/library/bb934100(SQL.100).aspx

The first time I ran this I thought it was not working as I got the following.

image

However, in Books Online it has the following description of this rowset (italics mine)

Provides resource usage and activity information about the objects in use by the current executing commands on the server.

And sure enough when you execute a long running query and query the rowset while the other query is still running you will get results like the following.

image

So this rowset is actually reporting of in-memory structures, while commands are executing which gives

Name DISCOVER_OBJECT_ACTIVITY
Restrictions OBJECT_PARENT_PATH  (string)
OBJECT_ID (string)
Columns OBJECT_PARENT_PATH (string)
OBJECT_ID (string)
OBJECT_CPU_TIME_MS (long)
OBJECT_READS (long)
OBJECT_READ_KB (long)
OBJECT_WRITES (long)
OBJECT_WRITE_KB (long)
OBJECT_AGGREGATION_HIT (long)
OBJECT_AGGREGATION_MISS (long)
OBJECT_HIT (long)
OBJECT_MISS (long)
OBJECT_VERSION (int)
OBJECT_DATA_VERSION (int)
OBJECT_ROWS_SCANNED (long)
OBJECT_ROWS_RETURNED (long)

Provides resource usage per object since the start of the service.

for more details see: http://msdn2.microsoft.com/en-us/library/bb934094(SQL.100).aspx

   image

Name DISCOVER_OBJECT_MEMORY_USAGE
Restrictions OBJECT_PARENT_PATH (string)
OBJECT_ID (string)
Columns OBJECT_PARENT_PATH (string)
OBJECT_ID (string)
OBJECT_MEMORY_SHRINKABLE (long)
OBJECT_MEMORY_NONSHRINKABLE (long)
OBJECT_VERSION (int)
OBJECT_DATA_VERSION (int)
OBJECT_TYPE_ID (int)
OBJECT_TIME_CREATED (dateTime)

Provides information about memory resources used by objects.

for more details see: http://msdn2.microsoft.com/en-us/library/bb934098(SQL.100).aspx

image

Print | posted on Thursday, February 21, 2008 2:56 PM

Comments on this post

# re: SSAS 2008: What's new in the Schema Rowsets?

Requesting Gravatar...
Hello Darren,
pretty nice Article, its just what i was searching for... i've had the questions you are answering in your Article.
Thank you very much.

Best Regards & Greetings from Berlin
R2
Left by R2 on Nov 09, 2010 12:33 AM

# re: SSAS 2008: What's new in the Schema Rowsets?

Requesting Gravatar...
Here are this and some other articles on What's New in SSAS 2008:

http://ssas-wiki.com/w/Articles#What.27s_New_in_SSAS_2008
Left by Sam Kane on Mar 23, 2011 2:17 AM

Your comment:

 (will show your gravatar)