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

Article translations Article translations
Article ID: 2458438 - View products that this article applies to.
Microsoft distributes Microsoft SQL Server 2008 R2 fixes as one downloadable file. Because the fixes are cumulative, each new release contains all the hotfixes and all the security fixes that were included with the previous SQL Server 2008 R2 fix release.
Expand all | Collapse all

On This Page

SYMPTOMS

When you use Microsoft SQL Server Profiler to monitor an instance of SQL Server 2008 R2 Analysis Services, one or more of the following issues may occur.

Issue 1
When an Execute MDX Script trace event is triggered by a session other than Analysis Services, the ConnectionID and SPID attributes are incorrectly reported as 0. Therefore, the session that triggers the event is not logged.

Issue 2
The Execute MDX Script Current event that is triggered for every sub command of a script incorrectly contains the whole script in the TextData column. Therefore, the script part that is related to the event is not indicated.

Issue 3
Current trace information does not indicate which resources are consumed by a particular command or by a query.

Issue 4
In SQL Server Profiler, the Locks category that contains lock operation information logs only the following two events:
  • Deadlock
  • LockTimeOut
However, this information is insufficient when you try to troubleshoot the performance of SQL Server 2008 R2 Analysis Services.

This update introduces some new trace events to SQL Server Profiler to resolve these issues. For more information, see the "More Information" section.

RESOLUTION

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.

MORE INFORMATION

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, the following two events are available to track the Execute MDX script operation:
  • Execute MDX Script Command Begin
  • Execute MDX Script Command End
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

The LockList node contains the following information:
  • Lock type
  • Lock status
  • Object path of the object that is being requested
  • Object ID
Note The object path is reported without a namespace. The Lock Released event additionally contains the Duration property. The Duration property indicates the duration that the lock is held in milliseconds.

The following is an example of the LockList node:
<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>
In this example, the transaction requests three locks, obtains one, and waits for the second lock.

WaitList

The WaitList node lists the waiting transactions that are ahead of the current transaction. The following is an example of the WaitList node:
<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

The HoldList node lists transactions that hold a lock that the current transaction tries to obtain. The following is an example of the HoldList node:
<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>

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.

REFERENCES

For more information about the Incremental Servicing Model for SQL Server, click the following article number to view the article in the Microsoft Knowledge Base:
935897 An Incremental Servicing Model is available from the SQL Server team to deliver hotfixes for reported problems
For more information about the naming schema for SQL Server updates, click the following article number to view the article in the Microsoft Knowledge Base:
822499 New naming schema for Microsoft SQL Server software update packages
For more information about software update terminology, click the following article number to view the article in the Microsoft Knowledge Base:
824684 Description of the standard terminology that is used to describe Microsoft software updates

Properties

Article ID: 2458438 - Last Review: May 24, 2012 - Revision: 1.1
APPLIES TO
  • Microsoft SQL Server 2008 R2 Analysis Services
Keywords: 
kbfix kbqfe kbexpertiseadvanced kbsurveynew KB2458438

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com