KB2458438 - FIX: SQL Server 2008 R2 Analysis Services introduces new trace events to track resource usage and locks by using SQL Server Profiler

Service pack information for SQL Server 2008 R2

To resolve this problem, obtain the latest service pack for SQL Server 2008 R2. For more information, click the following article number to view the article in the Microsoft Knowledge Base:

2527041
How to obtain the latest service pack for SQL Server 2008 R2
Note This fix for issue was first released in SQL Server 2008 R2 Service Pack 1.

The new Execute MDX Script Command event

The current SQL Server 2008 R2 Analysis Services supports the following events for the Execute MDX script operation:

• Execute MDX Script Begin
  

• Execute MDX Script Current
  

• Execute MDX Script End
  

After you install this update, a new sub event MDX Script Command is available to track the Execute MDXScript operation. Below are the combinations of event and sub event:

• Execute MDX Script Begin/MDX Script

• Execute MDX Script Begin/MDX Script Command

• Execute MDX Script End/MDX Script

• Execute MDX Script End /MDX Script Command

These events indicate which part of the whole script is related to every sub command of the MDX script. The TextData column of the Execute MDX Script Begin event contains the whole MDX script. The TextData column of the Execute MDX Script Command Begin event contains only the MDX script of the current command.

New trace events to track resource usage

Current trace information does not indicate which resources are consumed by a particular command. After you install this update, the new Resource Usage event class is introduced to include information about CPU usage and I/O operations. This information is logged in a separate event or in additional columns on the Command End event or on the Query End event. The following information can be included in the TextData column of the event:

• READS, <Value>

• READ_KB, <Value>

• WRITES, <Value>

• WRITE_KB, <Value>

• CPU_TIME_MS, <Value>

• ROWS_SCANNED, <Value>

• ROWS_RETURNED, <Value>

Trace events that track any locking activities

SQL Server 2008 R2 Analysis Services supports the following two events that track any locking activities:

• Deadlock

• LockTimeOut

After you install this update, the following new trace events are available to track any locking activities:

• Locks Acquired
  

• Locks Released
  

• Lock Waiting

The Locks Acquired event indicates when the transaction has obtained a batch of locks for the processing of the transaction. The Locks Released event indicates when the transaction has released a batch of locks that the transaction requested. This event also indicates the duration that the locks are held. The Lock Waiting event indicates when a transaction tries and waits in a queue to obtain a lock in a batch. This information is in the TextData column of those events. This information includes the following additional related data:

• The transaction ID

• The LockList XML node

• The WaitList XML node

• The HoldList XML node

The Lock Acquired event and the Lock Released event contain the LockList information. The Lock Waiting event contains the LockList, WaitList, and HoldList information.

<LockList>
                <Lock>
                                <Type>Read</Type>
                                <LockStatus>Acquired</LockStatus>
                                <Object><DatabaseID>AdventureWorks</DatabaseID></Object>
                                <ObjectID>asadfb-vfbvadr-ft3323-54235</ObjectID>
                </Lock>
                <Lock>
                                <Type>Read</Type>
                                <LockStatus>Waiting</LockStatus>
                                <Object><DatabaseID>FoodMart</DatabaseID><Object>
                                <ObjectID>asadfb-vfbvadr-ft3323-54235</ObjectID>
                </Lock>
                <Lock>
                                <Type>Read</Type>
                                <LockStatus>Requested</LockStatus>
                                <Object><DatabaseID>FoodMart</DatabaseID><Object>
                                <ObjectID>asadfb-vfbvadr-ft3323-54235</ObjectID>
                </Lock>
</LockList>

<WaitList>
     <Object><DatabaseID>FoodMart</DatabaseID><Object>
     <ObjectID>asadfb-vfbvadr-ft3323-54235</ObjectID>
     <Type>Read</Type>
     <Transaction>  
  <TransactionID>2342-3we-dsdf-sdf<TransactionID>
  <SPID>234</SPID>
  <Type>Write</Type>
     </Transaction>  
     <Transaction>  
  <TransactionID>2ger342-3rtee-dsdf-sdf<TransactionID>
  <SPID>222</SPID>
  <Type>Read</Type>
     </Transaction>  
</WaitList>

<HoldList>
     <Object><DatabaseID>FoodMart</DatabaseID><Object>
     <ObjectID>asadfb-vfbvadr-ft3323-54235</ObjectID>
     <Type>Read</Type>
     <Transaction>  
  <TransactionID>2342-3we-dsdf-sdf<TransactionID>
  <SPID>234</SPID>
  <Type>Write</Type>
     </Transaction>  
     <Transaction>  
  <TransactionID>2ger342-3rtee-dsdf-sdf<TransactionID>
  <SPID>222</SPID>
  <Type>Read</Type>
     </Transaction>  
</HoldList>