Pinpointing Bottlenecks for Better Database Access in ASP.Net

0
959
7 min read

ASP.NET Site Performance Secrets

ASP.NET Site Performance Secrets

Simple and proven techniques to quickly speed up your ASP.NET website

  • Speed up your ASP.NET website by identifying performance bottlenecks that hold back your site’s performance and fixing them
  • Tips and tricks for writing faster code and pinpointing those areas in the code that matter most, thus saving time and energy
  • Drastically reduce page load times
  • Configure and improve compression – the single most important way to improve your site’s performance
  • Written in a simple problem-solving manner – with a practical hands-on approach and just the right amount of theory you need to make sense of it all

In this section, we’ll identify the biggest bottlenecks.

Missing indexes and expensive queries

You can greatly improve the performance of your queries by reducing the number of reads executed by those queries. The more reads you execute, the more potentially you stress the disk, CPU, and memory. Secondly, a query reading a resource normally blocks another query from updating that resource. If the updating query has to wait while holding locks itself, it may then delay a chain of other queries. Finally, unless the entire database fits in memory, each time data is read from disk, other data is evicted from memory. If that data is needed later, it then needs to be read from the disk again.


The most effective way to reduce the number of reads is to create sufficient indexes on your tables. Just as an index in a book, an SQL Server index allows a query to go straight to the table row(s) it needs, rather than having to scan the entire table. Indexes are not a cure-all though—they do incur overhead and slow down updates, so they need to be used wisely.

In this section, we’ll see:

  • How to identify missing indexes that would reduce the number of reads in the database
  • How to identify those queries that create the greatest strain, either because they are used very often, or because they are just plain expensive
  • How to identify superfluous indexes that take resources but provide little benefit

Missing indexes

SQL Server allows you to put indexes on table columns, to speed up WHERE and JOIN statements on those columns. When the query optimizer optimizes a query, it stores information about those indexes it would have liked to have used, but weren’t available. You can access this information with the Dynamic Management View (DMV) dm_db_missing_index_details (indexesqueries.sql in the code bundle):

select d.name AS DatabaseName, mid.*
from sys.dm_db_missing_index_details mid
join sys.databases d ON mid.database_id=d.database_id

The most important columns returned by this query are:

ColumnDescriptionDatabaseNameName of the database this row relates to.equality_columnsComma-separated list of columns used with the equals operator, such as:
column=valueinequality_columnsComma-separated list of columns used with a comparison operator other than the equals operator, such as:
column>valueincluded_columnsComma-separated list of columns that could profitably be included in an index.statementName of the table where the index is missing.

This information is not persistent—you will lose it after a server restart.

An alternative is to use Database Engine Tuning Advisor, which is included with SQL Server 2008 (except for the Express version). This tool analyzes a trace of database operations and identifies an optimal set of indexes that takes the requirements of all queries into account. It even gives you the SQL statements needed to create the missing indexes it identified.

The first step is to get a trace of database operations during a representative period. If your database is the busiest during business hours, then that is probably when you want to run the trace:

  1. Start SQL Profiler. Click on Start | Programs | Microsoft SQL Server 2008 | Performance Tools | SQL Server Profiler.
  2. In SQL Profiler, click on File | New Trace.
  3. Click on the Events Selection tab.
  4. You want to minimize the number of events captured to reduce the load on the server. Deselect every event, except SQL:BatchCompleted and RPC:Completed. It is those events that contain resource information for each batch, and so are used by Database Engine Tuning Advisor to analyze the workload. Make sure that the TextData column is selected for both the events.
  5. To capture events related only to your database, click on the Column Filters button. Click on DatabaseName in the left column, expand Like in the righthand pane, and enter your database name. Click on OK.

    (Move the mouse over the image to enlarge.)

  6. To further cut down the trace and only trace calls from your website, put a filter on ApplicationName, so only events where this equals “.Net SqlClient Data Provider” will be recorded.
  7. Click on the Run button to start the trace. You will see batch completions scrolling through the window. At any stage, you can click on File | Save or press Ctrl + S. to save the trace to a file.
  8. Save the template so that you don’t have to recreate it next time. Click on File | Save As | Trace Template. Fill in a descriptive name and click on OK. Next time you create a new trace by clicking on File | New Trace, you can retrieve the template from the Use the template drop-down.Sending all these events to your screen takes a lot of server resources. You probably won’t be looking at it all day anyway. The solution is to save your trace as a script and then use that to run a background trace. You’ll also be able to reuse the script later on.
  9. Click on File | Export | Script Trace Definition | For SQL Server 2005 – 2008. Save the file with a .sql extension. You can now close SQL Server Profiler, which will also stop the trace.
  10. In SQL Server Management Studio, open the .sql file you just created. Find the string InsertFileNameHere and replace it with the full path of the file where you want the log stored. Leave off the extension; the script will set it to .trc. Press Ctrl + S to save the .sql file.
  11. To start the trace, press F5 to run the .sql file. It will tell you the trace ID of this trace.
  12. To see the status of this trace and any other traces in the system, execute the following command in a query window:

    select * from ::fn_trace_getinfo(default)

    
    

    Find the row with property 5 for your trace ID. If the value column in that row is 1, your trace is running. The trace with trace ID 1 is a system trace.

  13. To stop the trace after it has captured a representative period, assuming your trace ID is two, run the following command:

    exec sp_trace_setstatus 2,0

    
    

    To restart it, run:

    exec sp_trace_setstatus 2,1

    
    
  14. To stop and close it so that you can access the trace file, run:
    exec sp_trace_setstatus 2,0
    exec sp_trace_setstatus 2,2

Now, run Database Engine Tuning Advisor:

  1. Start SQL Profiler. Click on Start | Programs | Microsoft SQL Server 2008 | Performance Tools | Database Engine Tuning Advisor.
  2. In the Workload area, select your trace file. In the Database for workload analysis drop-down, select the first database you want to be analyzed.
  3. Under Select databases and tables to tune, select the databases for which you want index recommendations.
  4. Especially with a big trace, Database Engine Tuning Advisor may take a long time to do its analysis. On the Tuning Options tab, you can tell it when to stop analyzing. This is just a limit; if it is done sooner, it will produce results as soon as it is done.
  5. To start the analysis, click on the Start Analysis button in the toolbar.

Keep in mind that Database Engine Tuning Advisor is just a computer program. Consider its recommendations, but make up your own mind. Be sure to give it a trace with a representative workload, otherwise its recommendations may make things worse rather than better. For example, if you provide a trace that was captured at night when you process few transactions but execute lots of reporting jobs, its advice is going to be skewed towards optimizing reporting, not transactions.

LEAVE A REPLY

Please enter your comment!
Please enter your name here