7/21/2015

SSAS Performance Metrics using Fiddler, SQL Server Profiler and Logging

Working in Business Intelligence, sometimes its necessary to watch the HTTP traffic on the production server.  Perhaps to take metrics.  Before and after setting changes.  Like adding Cube Warming to an SSAS Cube for example.

I like to use the Fiddler application.

So you start a new session, it captures all the network activity on the server:






Looking at the Fiddler website, it turns out the logging feature to Database, is an added feature.
 
The web sessions show all HTTP and HTTPS sessions captured by Fiddler, the messages include certain key information:
  • # – An ID# of the request generated by Fiddler for your convenience
  • Result – The Result code from the HTTP Response
  • Protocol – The Protocol (HTTP/HTTPS/FTP) used by this session
  • Host – The hostname of the server to which the request was sent
  • URL – The path and file requested from the server
  • Body – The number of bytes in the Response body
  • Caching – Values from the Response’s Expires or Cache-Control headers
  • Process – The local Windows Process from which the traffic originated
  • Content-Type – The Content-Type header from the Response
  • Custom – A text field you can set via scripting
  • Comments – A text field you can set from scripting or the session’s context menu
Has to be customized with script.  Instructions found here:

Created table in db:



USE [SSASQueryLog]
GO

DROP TABLE [dbo].[tblSessions]
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[tblSessions](
       [RowNumber] [int] IDENTITY(0,1) NOT NULL,
       [ResponseCode] [nvarchar](4000) NULL,
       [URL] [nvarchar](4000) ,
       ClientConnected [nvarchar](4000) null,
       ClientDoneRequest [nvarchar](4000) null,
       ServerConnected [nvarchar](4000) null,
       ServerGotRequest [nvarchar](4000) null,
       ServerBeginResponse [nvarchar](4000) null,
       ServerDoneResponse [nvarchar](4000) null,
       ClientBeginResponse [nvarchar](4000) null,
       ClientDoneResponse [nvarchar](4000) null
      
) ON [PRIMARY]

GO

To add code to Customize Rules:



 I added the code, and was able to save the contents of the Fiddler HTTP status updates into the database.  From there, you can write SQL code to determine time intervals between fields.
    // Log the currently selected sessions in the list to a database.
    // Note: The DB must already exist and you must have permissions to write to it.
    public static ToolsAction("Log Selected Sessions")
   
function DoLogSessions(oSessions: Fiddler.Session[]){
        if (null == oSessions || oSessions.Length < 1){
            MessageBox.Show("Please select some sessions first!");
            return;
        }
        var strMDB = "C:\\log.mdb";
        var cnn = null;
        var sdr = null;
        var cmd = null;
        try
        {
            //cnn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strMDB);
            cnn = new OleDbConnection("Provider=server;Data Source=database;Initial Catalog=SSASQueryLog;Integrated Security=SSPI;")
            cnn.Open();
            cmd = new OleDbCommand();
            cmd.Connection = cnn;

            for (var x = 0; x < oSessions.Length; x++){
                var strSQL = "INSERT into tblSessions ([ResponseCode],[URL],[ClientConnected],[ClientDoneRequest],[ServerConnected],[ServerGotRequest],[ServerBeginResponse],[ServerDoneResponse],[ClientBeginResponse],[ClientDoneResponse]) Values (" +
                   
oSessions[x].responseCode + ", '" + oSessions[x].url + "', '" + oSessions[x].Timers.ClientConnected + "', '" + oSessions[x].Timers.ClientDoneRequest + "', '" + oSessions[x].Timers.ServerConnected  + "', '" + oSessions[x].Timers.ServerGotRequest+ "', '" + oSessions[x].Timers.ServerBeginResponse + "', '" +  oSessions[x].Timers.ServerDoneResponse + "', '" +  oSessions[x].Timers.ClientBeginResponse  + "', '" +  oSessions[x].Timers.ClientDoneResponse + "')";
                cmd.CommandText = strSQL;
                cmd.ExecuteNonQuery();
            }
        }
        catch (ex){
            MessageBox.Show(ex);
        }
        finally
        {
        if (cnn != null ){
            cnn.Close();
        }
    }
        }


Don't forget to add this line to the top of the page:
import System.Data.OleDb;

And query the data through SQL:


SELECT [RowNumber]
      ,[ResponseCode]
      ,[URL]

         ,convert(datetime, [ClientConnected]) as [ClientConnected]
         ,convert(datetime, [ClientDoneRequest]) as [ClientDoneRequest]
         ,case
             when IsDate([ServerConnected]) = 0 then '1900-01-01 00:00:00'
             else convert(datetime, IsNull([ServerConnected],'01-01-1900 00:00:00'))
             end as [ServerConnected]
         ,case
             when IsDate([ServerGotRequest]) = 0 then '1900-01-01 00:00:00'
             else convert(datetime, IsNull([ServerGotRequest],'01-01-1900 00:00:00'))
             end as [ServerGotRequest]
         ,case
             when IsDate([ServerBeginResponse]) = 0 then '1900-01-01 00:00:00'
             else convert(datetime, IsNull([ServerBeginResponse],'01-01-1900 00:00:00'))
             end as [ServerBeginResponse]
         ,case
             when IsDate([ServerDoneResponse]) = 0 then '1900-01-01 00:00:00'
             else convert(datetime, IsNull([ServerDoneResponse],'01-01-1900 00:00:00'))
             end as [ServerDoneResponse]
         ,case
             when IsDate([ClientBeginResponse]) = 0 then '1900-01-01 00:00:00'
             else convert(datetime, IsNull([ClientBeginResponse],'01-01-1900 00:00:00'))
             end as [ClientBeginResponse]
         ,case
             when IsDate([ClientDoneResponse]) = 0 then '1900-01-01 00:00:00'
             else convert(datetime, IsNull([ClientDoneResponse],'01-01-1900 00:00:00'))
             end as [ClientDoneResponse]

  FROM [SSASQueryLog].[dbo].[tblSessions]

Results:



Another thing I did was to create metrics using SQL Server Profiler.  You basically start the application, then click on the website, which generates MDX queries from the PerformancePoint Dashboard, and it captures the data to a custom table.  From their, we were able to extract the MDX queries and add them to an SSIS package to run the queries after the ETL job runs and after the cube is processed.  Warming the cube is basically calling some queries to get them loaded into Cache, so when the users click on the reports, the queries are "hot".







How to implement cube warming strategy.http://www.sqlis.com/post/SSAS-Cache-Warming-Using-SSIS.aspx
And a reference, Chris Webb was kind enough to forward the SSIS project to me after I sent a DM on Twitter, thanks Chris!

There’s a tool to assist in the Cube Warming process:



Lastly, there's a feature in SQL Server Analysis server in which you can log queries to the custom table in an OLTP database.



You tell it the server, the database in the connection string, and specify the table name in another setting.  Then as the user clicks on the web page, it generates the MDX queries and saves every 5 or 10 row to the database.  

Data gets inserted into an OLTP table specified above: OLAPQueryLog:



SELECT TOP 1000
       --[MSOLAP_Database]
       --,[MSOLAP_ObjectPath]
      --[MSOLAP_User]
      [Dataset]
      ,[StartTime]
      ,[Duration]
  FROM [SSASQueryLog].[dbo].[OlapQueryLog]


You can then set Usage Aggregate Optimization in the SSAS project to tune the Cube's partitions, for faster response time.  It could take some time to generate enough MDX queries before it will allow UAO to be set.



So that's basically what I did yesterday to improve performance on the production server and supply metrics to the client, before and after, to show the increase in speed (hopefully).

No comments:

Post a Comment

Bloom Consulting Since Year 2000