Manage Learn to apply best practices and optimize your operations.

Freebie: Query Analyzer's Query Plans

Learn about one hidden gem in the Query Analyzer -- graphical query plans.

You may already be working with the usual suspects for performance tuning tools, but are you using all the features available in each tool? The following is one of several must-have free tools, or go to the complete list of freebies.

  5. Query Analyzer's Query Plans  Return to Complete List

Most DBAs and developers use Query Analyzer to issue queries, but the one hidden gem is graphical query plans. To access the graphical query plan options, open Query Analyzer, navigate to the Query menu and select either show Execution Plan, Show Server Trace or Show Client Statistics. Based on the T-SQL in the query window and the graphical query plan option selected, the results will look similar to the image below.

If you hover your mouse over any of the objects, it is possible to gather additional statistics about an individual step. One wish is to be able to extract the data when hovering over an object, but hopefully that will arrive in the coming months with SQL Server 2005.

Two other options to capture query plans in text format are via the following commands:

  • SET SHOWPLAN_ALL
  • SET SHOWPLAN_TEXT
  • Click for the next featured freebie: Stored procedures and functions


    About the author: Jeremy Kadlec is the Principal Database Engineer at Edgewood Solutions, a technology services company delivering professional services and product solutions for Microsoft SQL Server. He has authored numerous articles and delivers frequent presentations at regional SQL Server Users Groups and nationally at SQL PASS. Jeremy is also the SearchSQLServer.com Performance Tuning expert. Ask him a question here.

    This was last published in July 2005

    Dig Deeper on Microsoft SQL Server Performance Monitoring and Tuning

    Start the conversation

    Send me notifications when other members comment.

    Please create a username to comment.

    -ADS BY GOOGLE

    SearchBusinessAnalytics

    SearchDataCenter

    SearchDataManagement

    SearchAWS

    SearchOracle

    SearchContentManagement

    SearchWindowsServer

    Close