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
| 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.
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.
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
| 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
