Query Plans Tips

One of the primary things that any DBA or developer will do when tuning their SQL statements is to look at the query plan or execution plan. This can be done in both a graphical tree format as well as a text based format. For most people this information is confusing at best and does not explicitly tell you what the issue is and how to fix the problem. For this reason, people have probably become more use to using the graphical plan output versus the text based output. So how can you get useful query plan information in a graphical format when running a trace?

I have been using Graphical Query Plans for some time, but I know that Text Plans are also available. I have looked at them a few times, but I don't really understand how to relate what I know about the Graphical Plans to the Text Plan format.

I am familiar with the basic database maintenance tasks on Microsoft SQL Server, but now I received a new assignment. I have to optimize a few queries and I know that I have to check the query plans. Can you please explain which type of query plan I should use? What are the differences between text based and graphical query plans? Check out this tip for all of the details.

In the previous tip in this series, I gave you an initial look into how to launch and read a graphical query execution plan. We're going to dive deeper into the different opportunities for information sources within the graphical execution plan itself. Yes, you read that right. There are other sources of information in the graphical execution plan that are not readily apparent at first blush. This tip will focus on Tooltips and the next on the Properties window.

My company uses a vendor purchased application to process orders. Lately, one of the vendor application queries has been running very slowly and is starting to cause problems. My testing has determined that the SQL statement can benefit from a query hint. Is there a way I can apply this query hint even though I do not have access to the vendor application code?

One nice feature of SQL Server is being able to display graphical query plans. The problem with graphical query plans though is that they are sometimes extremely large and often difficult to maneuver through the plan to identity where the issues are within your statement or statements. Within SQL Server Management Studio you have the ability to zoom in and zoom as well as the ability to find certain parts of the query plan by hovering over a reduced version of the query plan, but often you are constrained by the size of the results pane to view the plan and it is still quite difficult to find the information you are looking for. Are there any other ways of making navigation of a query plan easier?

In some shops developers are quite inconsistent in how they write their T-SQL. Formatting styles differ, from whitespace and indenting, to upper- or lower-case keywords, to the use of schema and properly cased object and column names. When dealing with ad hoc SQL, since entries in the plan cache are case- and whitespace-sensitive, these inconsistencies can be quite problematic, leading to inefficient use of the plan cache (sometimes referred to as "plan cache bloat"). Check out this tip to minimize the SQL Server plan cache bloat.

With system performance on the mind of most business and technical teams (management, DBA, developer, network admin), determining the issues is the first major challenge. So once you have determined which queries are causing issues, now comes the time to determine how to improve the performance of the query while reducing the reads and writes. In SQL Server 2000 the primary interface was Query Analyzer's graphical interface where it was necessary to hover over the object to determine the statistics. Does SQL Server 2005 have a more elegant approach reviewing the query plans?

You get a call from tech support that users are complaining about a report being particularly slow. Tech support says they think the problem occurs when retrieving data from the database. After using SQL Profiler to find out what is happening, you find out that the stored procedure that pulls the data for the report is having very high reads and it is taking several seconds to complete. However, when you execute this procedure on the same database to analyze the query plan and start solving the problem, you can’t reproduce the bad performance the users are complaining about, even when you use the same parameters. How can you reproduce the problem to get the same results as the users?

First, you click the 'New Query' button on the top left of SQL Server Management Studio to have a new query window to execute a query. Second, you click the 'Execute' icon on the toolbar or press the F5 key to process a query. Third, SQL Server does some processing behind the scenes. Fourth, results are returned. These are the four steps to retrieving information from Microsoft SQL Server. Three of which are pretty straightforward and happen in the light of day. You see the commands, parameters, object names, keywords and the like being laid down on the display in front of you when entering T-SQL commands. You are the one clicking the Execute or F5 key. You see the results as they are returned. Is there not a way to see how SQL Ser

In some shops developers are quite inconsistent in how they write their T-SQL. Formatting styles differ, from whitespace and indenting, to upper- or lower-case keywords, to the use of schema and properly cased object and column names. When dealing with ad hoc SQL, since entries in the plan cache are case- and whitespace-sensitive, these inconsistencies can be quite problematic, leading to inefficient use of the plan cache (sometimes referred to as "plan cache bloat"). Check out this tip to minimize the SQL Server plan cache bloat.

I have been using Graphical Query Plans for some time, but I know that Text Plans are also available. I have looked at them a few times, but I don't really understand how to relate what I know about the Graphical Plans to the Text Plan format.

In the previous tip in this series, I gave you an initial look into how to launch and read a graphical query execution plan. We're going to dive deeper into the different opportunities for information sources within the graphical execution plan itself. Yes, you read that right. There are other sources of information in the graphical execution plan that are not readily apparent at first blush. This tip will focus on Tooltips and the next on the Properties window.

My company uses a vendor purchased application to process orders. Lately, one of the vendor application queries has been running very slowly and is starting to cause problems. My testing has determined that the SQL statement can benefit from a query hint. Is there a way I can apply this query hint even though I do not have access to the vendor application code?

First, you click the 'New Query' button on the top left of SQL Server Management Studio to have a new query window to execute a query. Second, you click the 'Execute' icon on the toolbar or press the F5 key to process a query. Third, SQL Server does some processing behind the scenes. Fourth, results are returned. These are the four steps to retrieving information from Microsoft SQL Server. Three of which are pretty straightforward and happen in the light of day. You see the commands, parameters, object names, keywords and the like being laid down on the display in front of you when entering T-SQL commands. You are the one clicking the Execute or F5 key. You see the results as they are returned. Is there not a way to see how SQL Ser

I am familiar with the basic database maintenance tasks on Microsoft SQL Server, but now I received a new assignment. I have to optimize a few queries and I know that I have to check the query plans. Can you please explain which type of query plan I should use? What are the differences between text based and graphical query plans? Check out this tip for all of the details.

You get a call from tech support that users are complaining about a report being particularly slow. Tech support says they think the problem occurs when retrieving data from the database. After using SQL Profiler to find out what is happening, you find out that the stored procedure that pulls the data for the report is having very high reads and it is taking several seconds to complete. However, when you execute this procedure on the same database to analyze the query plan and start solving the problem, you can’t reproduce the bad performance the users are complaining about, even when you use the same parameters. How can you reproduce the problem to get the same results as the users?

With system performance on the mind of most business and technical teams (management, DBA, developer, network admin), determining the issues is the first major challenge. So once you have determined which queries are causing issues, now comes the time to determine how to improve the performance of the query while reducing the reads and writes. In SQL Server 2000 the primary interface was Query Analyzer's graphical interface where it was necessary to hover over the object to determine the statistics. Does SQL Server 2005 have a more elegant approach reviewing the query plans?

One of the primary things that any DBA or developer will do when tuning their SQL statements is to look at the query plan or execution plan. This can be done in both a graphical tree format as well as a text based format. For most people this information is confusing at best and does not explicitly tell you what the issue is and how to fix the problem. For this reason, people have probably become more use to using the graphical plan output versus the text based output. So how can you get useful query plan information in a graphical format when running a trace?

One nice feature of SQL Server is being able to display graphical query plans. The problem with graphical query plans though is that they are sometimes extremely large and often difficult to maneuver through the plan to identity where the issues are within your statement or statements. Within SQL Server Management Studio you have the ability to zoom in and zoom as well as the ability to find certain parts of the query plan by hovering over a reduced version of the query plan, but often you are constrained by the size of the results pane to view the plan and it is still quite difficult to find the information you are looking for. Are there any other ways of making navigation of a query plan easier?

In some shops developers are quite inconsistent in how they write their T-SQL. Formatting styles differ, from whitespace and indenting, to upper- or lower-case keywords, to the use of schema and properly cased object and column names. When dealing with ad hoc SQL, since entries in the plan cache are case- and whitespace-sensitive, these inconsistencies can be quite problematic, leading to inefficient use of the plan cache (sometimes referred to as "plan cache bloat"). Check out this tip to minimize the SQL Server plan cache bloat.

I have been using Graphical Query Plans for some time, but I know that Text Plans are also available. I have looked at them a few times, but I don't really understand how to relate what I know about the Graphical Plans to the Text Plan format.

I am familiar with the basic database maintenance tasks on Microsoft SQL Server, but now I received a new assignment. I have to optimize a few queries and I know that I have to check the query plans. Can you please explain which type of query plan I should use? What are the differences between text based and graphical query plans? Check out this tip for all of the details.

In the previous tip in this series, I gave you an initial look into how to launch and read a graphical query execution plan. We're going to dive deeper into the different opportunities for information sources within the graphical execution plan itself. Yes, you read that right. There are other sources of information in the graphical execution plan that are not readily apparent at first blush. This tip will focus on Tooltips and the next on the Properties window.

First, you click the 'New Query' button on the top left of SQL Server Management Studio to have a new query window to execute a query. Second, you click the 'Execute' icon on the toolbar or press the F5 key to process a query. Third, SQL Server does some processing behind the scenes. Fourth, results are returned. These are the four steps to retrieving information from Microsoft SQL Server. Three of which are pretty straightforward and happen in the light of day. You see the commands, parameters, object names, keywords and the like being laid down on the display in front of you when entering T-SQL commands. You are the one clicking the Execute or F5 key. You see the results as they are returned. Is there not a way to see how SQL Ser

My company uses a vendor purchased application to process orders. Lately, one of the vendor application queries has been running very slowly and is starting to cause problems. My testing has determined that the SQL statement can benefit from a query hint. Is there a way I can apply this query hint even though I do not have access to the vendor application code?

One nice feature of SQL Server is being able to display graphical query plans. The problem with graphical query plans though is that they are sometimes extremely large and often difficult to maneuver through the plan to identity where the issues are within your statement or statements. Within SQL Server Management Studio you have the ability to zoom in and zoom as well as the ability to find certain parts of the query plan by hovering over a reduced version of the query plan, but often you are constrained by the size of the results pane to view the plan and it is still quite difficult to find the information you are looking for. Are there any other ways of making navigation of a query plan easier?

You get a call from tech support that users are complaining about a report being particularly slow. Tech support says they think the problem occurs when retrieving data from the database. After using SQL Profiler to find out what is happening, you find out that the stored procedure that pulls the data for the report is having very high reads and it is taking several seconds to complete. However, when you execute this procedure on the same database to analyze the query plan and start solving the problem, you can’t reproduce the bad performance the users are complaining about, even when you use the same parameters. How can you reproduce the problem to get the same results as the users?

One of the primary things that any DBA or developer will do when tuning their SQL statements is to look at the query plan or execution plan. This can be done in both a graphical tree format as well as a text based format. For most people this information is confusing at best and does not explicitly tell you what the issue is and how to fix the problem. For this reason, people have probably become more use to using the graphical plan output versus the text based output. So how can you get useful query plan information in a graphical format when running a trace?

With system performance on the mind of most business and technical teams (management, DBA, developer, network admin), determining the issues is the first major challenge. So once you have determined which queries are causing issues, now comes the time to determine how to improve the performance of the query while reducing the reads and writes. In SQL Server 2000 the primary interface was Query Analyzer's graphical interface where it was necessary to hover over the object to determine the statistics. Does SQL Server 2005 have a more elegant approach reviewing the query plans?