The process of optimizing a stored procedure is much like the process for optimizing a query, with a few differences. If a sproc was running well, but slowed down, it will probably benefit from being recompiled. This can be accomplished in 2 ways:
- Run the system stored procedure sp_recompile against the sproc. This will cause the sproc to be recompiled the next time it is run.
- Include the WITH RECOMPILE option when executing the query
EXEC spName
WITH RECOMPILE
- Review the code to determine if it could be rewritten
- Replace cursors with temp tables or table variables
- Determine if table variables could replace temp tables or correlated sub-queries
- Determine if sub-queries could be replaced with joins, or vice versa (test to see which will run faster)
- Look for any declared variables that are never used in the code. Get rid of these to avoid grabbing unnecessary memory.
- Determine that only the needed columns and rows are being selected (try avoiding select *)
- Look at the order of the joins to determine that, where possible, the most limiting joins are executed first
Step by Step SQL Profiler to optimize Stored Procedure
To determine if you have a problem with existing stored procedures or a specific stored procedure:
1. Start Profiler
2. Start a new trace
3. Connect to your server
4. On the General Tab, give the trace a name
5. On the Events Tab remove all default events and add SP:Recompile, SP:Starting, and SP:Completed under Stored Procedure events. If you want to determine the statement that causes the recompile also add SP:StmtStarting and SP:StmtCompleted to the selection.
6. You can leave the data columns as is or change them as you see fit. You can also leave the trace without filters, but stored procedures run by replication may tend to clutter your trace. If you tracing only one stored procedure,you can filter by the stored procedure name under the Text-Like filter.
“DATABASE ENGINE TUNING ADVISOR”
(OLDER VERSION SQL 2000 :Index Tuning Wizard)
The Database Engine Tuning Advisor is a new tool in Microsoft SQL Server 2005 that enables you to tune databases for improved query processing. Database Engine Tuning Advisor examines how queries are processed in the databases you specify and then it recommends how you can improve query processing performance by modifying physical design structures such as indexes, indexed views, and partitioning. It replaces the Index Tuning Wizard from Microsoft SQL Server 2000, and offers many new features.
For example, Database Engine Tuning Advisor provides two user interfaces: a graphical user interface (GUI) and the dta command prompt utility. The GUI makes it easy to quickly view the results of tuning sessions, and the dta utility makes it easy to incorporate Database Engine Tuning Advisor functionality into scripts for automated tuning. In addition, Database Engine Tuning Advisor can take XML input, which offers more control over the tuning process.
These SQL Server Profiler event classes can be used to help you find out if SQL Server is running as efficiently as it can. They include:
- Execution Warnings: For very busy servers, it is possible that a query may have to wait a short time before there are available resources for it to run. This event class can be watched to find out if and how long queries have to wait for needed resources. This event class has two possible values. “Query Wait” is used to indicate how often a query has to wait. “Query Time-Out” is used to indicate how often a query has timed out waiting for the necessary resources. Obviously, you don’t want to see either of these events. If you do, and they occur regularly, then you need to reduce the load on your server, beef up the server hardware, rewrite the queries, select more appropriate indexes to resolve the problem, or some combination of all these actions.
- Hash Warning: This event is used to measure hash recursions or hash bails that have occurred on your server. A hash recursion (event 0) happens when the input of the query does not fit entirely into memory, forcing SQL Server to split the input into multiple parts, which are then processed individually. A hash bail (event 1) is even worse for performance. It occurs when a hashing operation reaches its maximum recursion depth, which forces the query to run under an alternative query plan, one that is much less than optimal. Think of a hash bail as a hash recursion’s worst nightmare. As you can imagine, these two events can really slow down a query. Some options to correct these problem events include: insure that the Index Statistics are up-to-date, rewrite the query, experiment with optimizer hints, or add more RAM to the server.
While the purpose of this warning is to let you know when hash operations go awry, it also serves another purpose. If you want optimum performance, you want to avoid hash operations of any type, even ones that work properly. So if you identify any hash warnings, you might want to try to find out why a hash operation is being performed in the first place, and try to fix this problem. This of course, will also fix your hash warning problem.
- Missing Column Statistics: Tells you which columns in your query are missing statistics, such as the Index Statistics used by the Query Optimizer to help it evaluate the most efficient query plan. If one or more columns of statistics are missing, the Query Optimizer may not select the best query plan, hurting performance. To help prevent this problem, consider the following: Be sure you have “auto create statistics” turned on for your database, use the CREATE STATISTICS statement to manually create the statistics on the missing columns, or use the Index Tuning Wizard or Database Engine Tuning Advisor to identify and automatically create the needed statistics.
- Missing Join Predicate: Indicates whether or not the query in question has a join predicate. If not, this can cause the Query Optimizer to produce a less than optimized query plan. To fix this, add a join predicate.
- Sort Warnings: Tells you if sort operations can be done completely in memory or if the sorting operation has to be divided into two or more steps in order to complete. Obviously, a one step operation will be much faster. Potential solutions to this problem include reducing the number of rows to be returned and sorted, reducing the number of columns being sorted, getting rid of the sort, or adding more RAM to the server.
