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

April 9, 2007

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.

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.

25

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

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

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

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 Begin” event), we see only the 25 – ExecuteSQL EventSubclasses (in addition to the blank EventSubclasses) appear.

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.

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

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.

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