The independence holiday is here. I have half day off today so l just want to spend some time and write a short post on optimizing queries. Have you asked yourself this question, How do I optimize queries? Well, I think I have asked myself this question almost on daily basis because am obsessed with fast and optimized performing queries. Here are some suggestions on how l usually go about finding ways to optimize my queries.
Identify the queries that have the longest duration: Using SQL Profiler you can determine where they are spending time and whether they can be improved. SQL Profiler is part of the tools provided SSMS. It can be found under the tools menu.
Steps involved in overall optimization process:
Using SET Statements like
SET SHOWPLAN_ALL (returns detailed information about how the statements are executed and provides estimates of the resource requirements for the statements)
SET STATISTICS IO (to display information regarding the amount of disk activity generated by Transact-SQL statements)
SET STATISTICS TIME (Displays the number of milliseconds required to parse, compile, and execute each statement.)
SET STATISTICS PROFILE (Displays the profile information for a statement. STATISTICS PROFILE works for ad hoc queries, views, triggers, and stored procedures.)
To know the elapsed time and execution time for each query and understand where does it take time, use the SQL Server Query Execution Plan. When it comes time to analyze the performance of a specific query, one of the best methods is to view the query execution plan. A query execution plan outlines how the SQL Server query optimizer actually ran (or will run) a specific query. This information is very valuable when it comes time to find out why a specific query is running slow, why a given query is performing thousands of scans, putting your I/O through the roof, you can use the execution plan to identify the exact piece of SQL code that is causing the problem. For example, it may be scanning an entire table-worth of data when, with the proper index, it could simply backpack out only the rows you need. All this and more is displayed in the execution plan.
SSMS displays query execution plans in text mode or graphical mode. This is how to see the execution plan for the query;
- Start SSMS, connect to the server, and select the database that you are working on.
- If you are using SQL Profiler to trace queries, the script can be copied from the trace window and used within SSMS.
- On the Query menu, click Display Estimated Execution Plan. The estimated execution plan for the query is displayed. If the query window contains multiple queries, an execution plan is displayed for each query.
- On the Query menu, click Show Execution Plan, and then run the query in the query window. Execution plans and query results now appear in separate panes of the window so you can view them together.
Snapshot below shows an example of an execution plan along with the related query information.
- Place the mouse pointer over any icon displayed in the query execution plan. Details of the query step are displayed, including information about the execution and cost of the step
Analyzing the Results
The icons in the query window graphically represent each step in the execution plan. To read an execution plan, read from right to left and from bottom to top. To fully understand an execution plan, you need to familiarize yourself with the various icons that can be displayed. Certain events and actions can cause a plan to be recompiled. It is important to remember this because recompiling execution plans can be a very expensive operation.
The following actions can lead to recompilation of an execution plan:
Changing the structure or schema of a table referenced by the query
- Changing an index used by the query
- Dropping an index used by the query
- Updating the statistics used by the query
- Calling the function, sp_recompile
- Subjecting the keys in tables referenced by the query to a large number of inserts or deletes
- For tables with triggers, significant growth of the inserted or deleted tables
- Mixing DDL and DML within a single query, often called a deferred compile
- Changing the SET options within the execution of the query
- Changing the structure or schema of temporary tables used by the query
- Changes to dynamic views used by the query
- Changes to cursor options within the query
- Changes to a remote rowset, like in a distributed partitioned view
- When using client side cursors, if the FOR BROWSE options are changed
Since the cache plays such an important role in how execution plans operate, you need a few tools for querying and working with the plan cache . First off, while testing, you may want to see how long a plan takes to compile, or to investigate how minor adjustments might create slightly different plans. To completely clear the cache, run this:
DBCC FREEPROCCACHE.
Last but the least option l want to touch is the database tuning advisor which is also a tool that comes with SSMS. This tool will monitor the query and recommend actions you can take to optimize it. For example, it can advice you to create index on a table referenced in the query. To use the Database Engine tuning advisor, with your query opened in SSMS, click on the query menu and select the option to analyze using the advisor as show below
The advisor runs and analyze the query and based on its decision making engine, recommends things to do to make the query optimize. For this simple query, there is no recommended actions but below is the screen shot of how the advisor screen looks after performing analysis of the query.
These are just some ways to get idea about the performance of the queries.
Hope that helps and Enjoy the July 4th holiday !!!
