用Fiddler 和 SQL Server Profiler 測試資料庫瓶頸

某國外論壇的文章，先收藏以後看有空再考慮翻譯

話說如果熟悉這2個工具直接看沒啥問題吧~~~

How to Use Fiddler and SQL Server Profiler to Look for Database Performance Bottlenecks
Posted on 2/4/2009 12:47:35 PM in #.NET

Here I want to describe the steps you can use to search for database performance bottlenecks before you deploy your web application to production server.

1. Modify Fiddler Rules

We want to add to Fiddler the option that would allow us to execute a sequence of requests and then tell it to repeat the same sequence N number of times.

First of all if you don’t have Fiddler yet, install the latest version from http://www.fiddlertool.com/Fiddler2/version.asp then start it and go to Rules - Customize Rules…

Scroll to the point where it says static function OnExecAction(sParams: String[]){ and add there the following code right after switch (sAction){

// Custom code starts here
case "ptest":
  if (sParams.Length<3) {
    FiddlerObject.alert(
       "USE AS: ptest URL_STARTS_WITH NUMBER_REPETITIONS for example ptest loc:8080 10");
    return;
  }
  var server = sParams[1];
  var repetitions = parseInt(sParams[2]);

  var oSessions = FiddlerObject.UI.GetAllSessions()
  var num = 0;
  for (var r = 0; r < repetitions; r++){
      for (var x = 0; x < oSessions.length; x++){
     
        if(oSessions[x].url.ToLower().StartsWith(server)){
            var ct = oSessions[x].oResponse["Content-Type"];
            if(ct == "text/html; charset=utf-8" ||
               ct == "text/xml; charset=utf-8" ||
               ct == "text/css; charset=utf-8"){
              FiddlerObject.utilIssueRequest(
                 oSessions[x].oRequest.headers.ToString(true, true) +
                 System.Text.Encoding.UTF8.GetString(oSessions[x].requestBodyBytes)
              );
              num++;
            }
        }
      }
  }
  FiddlerObject.StatusText="ptest was completed #executed requests: "+num
 
  break;
// Custom code ends here

2. Prepare SQL Server Profiler Trace

Now start the Sql Server Profiler (Start – All Programs – Microsoft SQL Server 2008 or 2005 – Performance Tools – Sql Server Profiler). Then choose File – New Trace… Click on the Event Selection tab and unselect all the events except RPC:Completed and SQL:BatchCompleted then select the following columns TextData, CPU, Duration, ClientProcessID, SPID, DatabaseName, ObjectName and unselect the others. Of course you can also look at other parameters I just show you here the basic once. You may also want to define column filter if you want to avoid traces from calls from other clients. For example I have a specific database that only I use for testing so I define that to be a filter for DatabaseName LIKE 'MyName%', you can also use the ClientProcessID column.

3. Perform a sequence of sample user actions

Open a web browser that is detectable by Fiddler. In the Fiddler window choose Tools – Clear WinINET Cache and Tools – Clear WinINET Cookies. Then press Ctrl – X to remove the existing sessions in Fiddler. Now use your browser to perform the sequence of test actions. If your site is located at localhost here is a tip how to make it visible with Fiddler http://www.coderjournal.com/2008/03/localhost-http-debugging-with-fiddler/

4. Run ptest command to simulate multiple site visitors.

Now click on the command line text field of Fiddler. You can find it at the bottom of the left side – a black line. Type ptest MYSITE 50 where MYSITE will be your web site (for example ptest loc:8080 50) and you will execute the sequence of test steps 50 times

5. Stop and save SQL Trace.

Wait for the executions to complete and then go back to SQL Server Profiler and choose File – Stop Trace. Now save it by File – Save As… - Trace Table. I have a test database where I store all the tables for future reference. Give the table a meaningful name, for example TestFeb042009_01

6. Query the test table to find of your bottlenecks.

Now you can execute the following database queries against the database where you stored your test table. The first one shows what queries use most CPU:

SELECT
    CAST(TextData as VARCHAR(8000)),
    DatabaseName,
    SUM(CPU) AgrCpu,
    COUNT(*) TimesCalled
FROM TestFeb042009_01
WHERE CPU IS NOT NULL
GROUP BY CAST(TextData as VARCHAR(8000)), DatabaseName
ORDER BY AgrCpu DESC

The second shows what queries take more time to execute:

SELECT
    CAST(TextData as VARCHAR(8000)),
    DatabaseName,
    SUM(Duration) AgrDuration,
    COUNT(*) TimesCalled
FROM TestFeb042009_01
WHERE Duration IS NOT NULL
GROUP BY CAST(TextData as VARCHAR(8000)), DatabaseName
ORDER BY AgrDuration DESC

You may notice that I use the sum of CPUs or duration and not the average value. This is because my concern is with general performance footprint of a particular query and a specific query performance. If you have a query that takes 100 ms but runs only ones during the normal workflow in your site it is not as bad as a query that takes 50 ms but runs 100 times.