Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Apr 9, 2007

Administration and Optimization: SQL Server Profiler for Analysis Services Queries - Page 4

By William Pearson

Add a Filter to the Trace

Filters can be useful for several purposes, chief among which is the minimization of overhead incurred as a part of tracing in general. We are instituting a filter here simply for convenience, but filters can be useful anywhere we need to limit the events that our traces collect.

1.  Click the Properties button within the trace viewer toolbar, as shown in Illustration 13.


Illustration 13: Select the Pause Selected Trace Button

2.  Click the Events Selection tab on the Trace Properties dialog, which appears next.

As we may have noticed in our initial visit to this tab, we can select events and event columns individually by placing a check in the appropriate checkbox in the Event rows, or in the specific intersects of the events with the various column types, within the matrix, to achieve just the filter criteria we need. Moreover, we can filter the individual columns based upon conditions that we can impose, as we shall see in the next step.

3.  Click the Column Filters button in the bottom right corner of the Events Selection tab, as depicted in Illustration 14.


Illustration 14: Click the Column Filters Button ...

4.  On the Edit Filter dialog that appears next, select EventSubclass.

5.  Expand Equals in the context sensitive selection list that appears to the right of be EventSubclass selection.

6.  Type the following integer into the input space that opens (containing the cursor) underneath Equals:

25

The Edit Filter dialog appears, with our input, as shown in Illustration 15.


Illustration 15: Our Filter Input ...

Here we are simply applying shorthand for 25 – ExecuteSQL (only integers are accepted), one of the many EventSubclasses that are captured.

7.  Click OK.

We are returned to the Events Selection tab of the Trace viewer, once again.

8.  Click Run, once again, to restart the trace.

9.  Re-process the cube, following the same steps as before.

Examine Processing and Query Events in the SQL Server Profiler Trace

We are returned to the Trace viewer, where, beginning with the events generated by our second cube processing cycle (and a “Progress Report Beginevent), we see only the 25 – ExecuteSQL EventSubclasses (in addition to the blank EventSubclasses) appear.

10.  Click the first EventSubclass Column containing “25 – ExecuteSQL” in the respective row, as depicted in Illustration 16.


Illustration 16: Select the First Instance of 25 – ExecuteSQL

The following SQL query appears in the lower half of the trace viewer:

SELECT [FactSalesSummary].[SalesOrderNumber] AS [FactSalesSummarySalesOrderNumber0_0],[FactSalesSummary].[OrderDateKey] AS 
 [FactSalesSummaryOrderDateKey0_1],[FactSalesSummary].[ShipDateKey] AS [FactSalesSummaryShipDateKey0_2],[FactSalesSummary].[DueDateKey] AS 
 [FactSalesSummaryDueDateKey0_3],[FactSalesSummary].[ProductKey] AS [FactSalesSummaryProductKey0_4],[FactSalesSummary].[PromotionKey] AS 
 [FactSalesSummaryPromotionKey0_5],[FactSalesSummary].[SalesTerritoryKey] AS 
 [FactSalesSummarySalesTerritoryKey0_6],[FactSalesSummary].[SalesChannel] AS [FactSalesSummarySalesChannel0_7],[FactSalesSummary].[CurrencyKey] AS 
 [FactSalesSummaryCurrencyKey0_8]
  FROM 
   (
  
SELECT     ProductKey, OrderDateKey, DueDateKey, ShipDateKey, ResellerKey, NULL AS CustomerKey, EmployeeKey, PromotionKey, CurrencyKey, 
                      SalesTerritoryKey, SalesOrderNumber, SalesOrderLineNumber, RevisionNumber, OrderQuantity, UnitPrice, ExtendedAmount, UnitPriceDiscountPct, 
                      DiscountAmount, ProductStandardCost, TotalProductCost, SalesAmount, TaxAmt, Freight, CarrierTrackingNumber, CustomerPONumber, 
                      'Reseller' AS SalesChannel, CONVERT(CHAR(10), SalesOrderNumber) + 'Line ' + CONVERT(CHAR(4), SalesOrderLineNumber) AS SalesOrderDesc
FROM         FactResellerSales
WHERE       OrderDateKey >= '915' AND OrderDateKey <= '1280'
UNION
SELECT     ProductKey, OrderDateKey, DueDateKey, ShipDateKey, NULL AS ResellerKey, CustomerKey, NULL AS EmployeeKey, PromotionKey, CurrencyKey, 
                      SalesTerritoryKey, SalesOrderNumber, SalesOrderLineNumber, RevisionNumber, OrderQuantity, UnitPrice, ExtendedAmount, UnitPriceDiscountPct, 
                      DiscountAmount, ProductStandardCost, TotalProductCost, SalesAmount, TaxAmt, Freight, CarrierTrackingNumber, CustomerPONumber, 
                      'Internet' AS SalesChannel, CONVERT(CHAR(10), SalesOrderNumber) + 'Line ' + CONVERT(CHAR(4), SalesOrderLineNumber) AS SalesOrderDesc
FROM         FactInternetSales
WHERE       OrderDateKey >= '915' AND OrderDateKey <= '1280'
   )
   AS [FactSalesSummary]
  ORDER BY [FactSalesSummary].[SalesOrderNumber]
  ASC

We can therefore see the syntax of each individual SQL query that is executed as part of our cube build. This is helpful in the isolation of suboptimal queries and other diagnostics and performance tuning exercises. Now let's take a look at something conceptually similar, the examination of an MDX query, but this time based upon query processing versus our cube build. To do so, we will first remove the 25 – ExecuteSQL filter we installed in the last few steps.

11.  Pause the trace, as before.

12.  Click the Properties button within the trace viewer toolbar, once again.

13.  Click the Events Selection tab on the Trace Properties dialog, as we did before.

14.  Click the Column Filters button in the bottom right corner of the Events Selection tab, again as we did earlier

15.  Select EventSubclass on the Edit Filter dialog that appears next, once again.

16.  Expand Equals in the context sensitive selection list, as required.

17.  Remove the integer “25,” which we placed within the Equals input box before (using the Backspace key works fine).

18.  Click OK.

We are returned to the Events Selection tab of the Trace viewer, once again.

19.  Click Run, on the Events Selection tab of the Trace viewer, to which we are returned once again, to accept our filter modification and to restart the trace.

We arrive again at the Trace viewer.

20.  Shift back to SQL Server Management Studio.

21.  Close the Process Progress viewer, from the last cube processing cycle, as appropriate.

22.  Click the New Query button, just above the Registered Servers and Object Explorer panes, within the upper left hand corner of the Management Studio, as shown in Illustration 17.


Illustration 17: Click the Start Selected Trace Button

23.  Type (or cut and paste) the following MDX query into the Query pane of the newly opened tab:

-- ANSYS059  MDX Query Profiling Example
SELECT
   {[MEASURES].[Internet Order Quantity]} ON AXIS(0),
   NON EMPTY {[Customer].[Customer Geography].[Full Name].MEMBERS}
      ON AXIS (1)
FROM
 
   [Adventure Works]

The Query pane appears, with our input, as depicted in Illustration 18.


Illustration 18: The Query Pane with Our Input



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM


















Thanks for your registration, follow us on our social networks to keep up-to-date