Figure 1: The SQL Profiler, awaiting a job
SQL Server 2000 comes with several tools that can be used to fine-tune your database. In some cases, if your database has not been optimized with indexes, the improvements can be dramatic. This article will focus on one aspect of the SQL Server Profiler -- using it to profile a typical web site session to allow the Index Tuning Wizard to make suggestions based on an actual use case scenario. This article will apply to any web site that uses SQL Server 2000 -- ASP, ColdFusion, or other.
Don't depend on the tools provided by SQL Server -- They are no substitute for knowing how data is accessed and knowing how to properly index your data. However, after you have created what you feel are optimal indexes, it can't hurt to test your application in a real world scenario to see if any further optimization is needed. For an introduction to indexes, see my article Indexing a Database Table. This article will attempt to expand your toolset to include two built-in SQL Server tools.
You can use the following small site as an example, or simply use the steps on your own site. I will set up a simple 2-page web site using the Northwind database. Because Northwind is already somewhat optimized, the first thing I'll do is set up three new tables. Run this statement in the Query Analyzer:
SELECT * INTO OrdersNew FROM Orders
SELECT * INTO OrderDetailsNew FROM [Order Details]
SELECT * INTO ProductsNew FROM Products
By running these statements you create duplicates of the Orders, Order Details, and Products tables without indexes.
We'll create a Master/Detail page set, only we'll do it manually. I want to get this preliminary stuff out of the way quickly, so I'm going to assume you know how to create pages, recordsets, and know how to use data objects in Dreamweaver. First, create two pages in an empty or test web site -- master.cfm and detail.cfm (or .asp or other). Then follow these steps:
1. Open master.cfm and add this recordset as rsMaster:
SELECT OrderID, CustomerID, OrderDate
FROM OrdersNew
2. Add a Dynamic Table object to the page. Use all the default options.
3. Add a Recordset Navigation Bar to the page
4. Select the OrderID field in design view and go to the Properties window. Click the folder icon next to the Link box. Choose the detail.cfm page
5. With the dialog box still open, click the Parameters button. Type in orderid under Name and click the lightening bolt icon to choose rsMaster.OrderID for the Value. Master page is complete.
6. For the details page, create a recordset called rsDetail with the following SQL. I'll assume you know how to add a filter of URL variable called orderid:
SELECT od.OrderID, o.OrderDate, p.ProductName, od.UnitPrice, od.Quantity, od.Discount
FROM OrdersNew o
INNER JOIN
OrderDetailsNew od
ON o.OrderID = od.OrderID
INNER JOIN ProductsNew p
ON od.ProductID = p.ProductID
WHERE od.OrderID = url.orderid
7. Insert a table with 6 rows and 2 columns. Drag each field from the Bindings panel to the second column and put a descriptive text label in column one. This is your details page.
Browse your master page and click the detail links and the paging links to make sure everything is working. With the functional web site out of the way, we'll move to the Profiler.
The Profiler has many important functions, but one of the most valuable is the ability to examine every SQL statement being executed by your database while an application is being used in the manner for which it was designed. Profiling individual statements is valuable as well, but when an application is being used in an everyday situation, the profiling becomes important to the efficiency of the application and not just to a specific query or table.
Note: The Profiler will slow your system while in use, so it's better to use during a slow period or on a test server.
To open the profiler from the Enterprise Manager, choose Tools > SQL Profiler. This opens the Profiler to the intimidating blank screen:
Figure 1: The SQL Profiler, awaiting a job
To start the job, choose File > New Trace. Choose your server in the dialog that pops up. This brings you to this screen:
Figure 2: The New Trace dialog
In the General tab, name the trace, choose the SQLProfilerStandard if it isn't already chosen, and check the Save to File checkbox. You can save this profile anywhere, such as on your desktop.
In the Events tab, select the T-SQL tree and click the Add>> button. Remove everything else:
Figure 4: Adding the Events we want to trace
We'll leave everything else set to default except for the Filters tab. In this tab, filter the DatabaseName by the name "northwind" and LoginName LIKE "northwind" (assuming your database username for the Northwind database is Northwind. Substitute yours here):
Figure 4: Add a filter for Northwind
All that is left now is to run the Profiler. Click the Run button and be ready to test your web application. What we'll do is pretend we are a typical user of this application looking at 4 orders and do the following things:
Now, stop the trace by clicking the red stop button. Your trace should look something like this:
Figure 5: The completed Trace output
The next step is to open the Index Tuning Wizard from the Enterprise Manager. Choose Tools > Wizards > Management > Index Tuning Wizard. This brings up the following:
Figure 6: The Index Tuning Wizard
Click the Next button to get you into the wizard. Click Next again after choosing your database (Northwind) and choosing the Thorough radio button. This brings you to the following, which you will set to the workload Trace file that you just created, as shown:
Figure 7: Setting the Index Tuning Wizard to your trace file
In the next screen, select ALL the tables. True, we only used three tables in this instance, but in a real-world scenario any table in your database will be a candidate for improving performance. You can also choose to target problem tables here as well, but for our purposes just choose All:
Figure 8: Choose Select All Tables to analyze every table
Click the Next button and begin the analysis. It should only take a few seconds for this really simple application, but a real-world analysis using a web application for a few minutes spanning multiple tables might take a few minutes to complete. When finished, you are presented with a recommendation:
Figure 9: Recommendation for indexes
In this particular case, the recommendations are for indexes on OrdersNew and OrderDetailsNew. Surprisingly, no recommendations are made on the ProductsNew table. In any event, a 50% improvement in performance is indicated based solely on the sample data from your sample use of the application. The closer to a real-world scenario you use, the better the recommendations will be. You can now click Next to apply the indexes to the tables. Indexes will be created automatically.
Figure 10: Apply the changes (create indexes) to your database
What if we used the existing Northwind tables that are already indexed? I wonder if the Index Tuning Wizard would be able to come up with a better index plan than what the database already has. There is one way to find out -- change your web pages to use the original Northwind tables (Products, Orders, and Order Details) and run the same steps. It's not surprising that no recommendations are made -- either the database is optimized enough with the existing indexes, or the sample scenario is too small to tell. Changing the scenario a little, I clicked through on more than 4 orders -- this time about 10 orders. Now, the Index Tuning Wizard recommends two new indexes on the Orders table -- the exact two indexes recommended with our unindexed tables and also indicates a 50% improvement in performance. It's clear that the more data the wizard has, the better it is able to determine the optimal course of action.
Optimizing a web application can be done at several levels, but the first place to start the optimization is in the database. Using some built-in SQL Server tools, you can frequently boost your performance just by testing and profiling your application in a real-world scenario and use the tools provided to make suggestions for indexes that will speed up your data access.