Simple and proven techniques to quickly speed up your ASP.NET website
In this section, we’ll identify the biggest bottlenecks.
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:
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:
(Move the mouse over the image to enlarge.)
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.
exec sp_trace_setstatus 2,0
To restart it, run:
exec sp_trace_setstatus 2,1
exec sp_trace_setstatus 2,0 exec sp_trace_setstatus 2,2
Now, run Database Engine Tuning Advisor:
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.
I remember deciding to pursue my first IT certification, the CompTIA A+. I had signed…
Key takeaways The transformer architecture has proved to be revolutionary in outperforming the classical RNN…
Once we learn how to deploy an Ubuntu server, how to manage users, and how…
Key-takeaways: Clean code isn’t just a nice thing to have or a luxury in software projects; it's a necessity. If we…
While developing a web application, or setting dynamic pages and meta tags we need to deal with…
Software architecture is one of the most discussed topics in the software industry today, and…