Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Query Plans in SQL Server 2000 vs SQL Server 2005


By:   |   Last Updated: 2006-09-21   |   Comments (2)   |   Related Tips: 1 | 2 | 3 | 4 | 5 | 6 | More > Query Plans

Problem
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?

Solution

SQL Server 2000 - Query Plan Options

There are three primary interfaces in SQL Server 2000 to access the query plan.  First is via the SHOWPLAN_TEXT  command, this command offers a textual view of the SQL Server optimizer's query plan.  A more advanced command is the SHOWPLAN_ALL command.  This command provides the estimated IO, CPU, row size, parallelism, etc. A final option is use the graphical query plans from Query Analyzer which can be activated by CTRL + K.  Each component of the query is broken down and the read, writes, etc. statistics are available for each.

SQL Server 2000 - Query Plan Commands

ID Command Example
1 SHOWPLAN_TEXT - Simple view of the optimizer results SET SHOWPLAN_TEXT ON
GO
SELECT TOP 1 a.au_lname AS 'AuthorLastName', a.au_fname AS 'AuthorFirstName',
t.title AS 'Title', t.pubdate AS 'PublicationDate'
FROM dbo.Authors a
INNER JOIN dbo.TitleAuthor ta
ON a.au_id = ta.au_id
INNER JOIN dbo.Titles t
ON ta.title_id = t.title_id
WHERE a.state = 'CA'
 
    Corresponding Output
 
2 SHOWPLAN_ALL - Detailed view of the optimizer results SET SHOWPLAN_ALL ON
GO
SELECT TOP 1 a.au_lname AS 'AuthorLastName', a.au_fname AS 'AuthorFirstName',
t.title AS 'Title', t.pubdate AS 'PublicationDate'
FROM dbo.Authors a
INNER JOIN dbo.TitleAuthor ta
ON a.au_id = ta.au_id
INNER JOIN dbo.Titles t
ON ta.title_id = t.title_id
WHERE a.state = 'CA'
 
    Corresponding Output
 
3 Graphical Query Plan - Graphical view of the query plan data from the SQL Server optimizer

This feature is activated by CTRL + K in Query Analyzer

SELECT TOP 1 a.au_lname AS 'AuthorLastName', a.au_fname AS 'AuthorFirstName',
t.title AS 'Title', t.pubdate AS 'PublicationDate'
FROM dbo.Authors a
INNER JOIN dbo.TitleAuthor ta
ON a.au_id = ta.au_id
INNER JOIN dbo.Titles t
ON ta.title_id = t.title_id
WHERE a.state = 'CA'
 
   
 

SQL Server 2005 - Query Plan Related Dynamic Management Views

SQL Server 2005 continues to support the SQL Server 2000 options listed above in addition to the introduction of the dynamic management views below.  These new dynamic management views support reviewing the current query plan and the associated performance metrics: 

The query below provides a fairly complete view of the query plan metrics (IO, CPU, memory, etc.) as well as the query plan in XML format based on the cached query plans in your environment.  Try this query out on your SQL Server 2005 server:

USE master;
GO
SELECT * FROM sys.dm_exec_query_stats
qs
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle
);
GO

-- Reference - SQL Server 2005 Books Online

Next Steps

  • Once you have pinpointed the queries in your environment which have plagued your performance, begin to review the query plans and begin to tune these individual queries.
  • As a best practice have 2 separate Management Studio sessions to compare the results of the original query and the query as it is being tuned.
  • Stay tuned for additional tips from MSSQLTips.com on techniques to improve query performance.
  • Check out MSSQLTips.com for a complete list of performance tuning tips such as SQL Server 2000 Query Analyzer Short Cuts.


Last Updated: 2006-09-21


get scripts

next tip button



About the author




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Thursday, July 11, 2013 - 9:37:05 PM - Jeremy Kadlec Back To Top

RedDevil,

I would check out the SELECT tutorial to start to learn about how to access the data - http://www.mssqltips.com/sqlservertutorial/10/select-command-for-sql-server/.

Then I would start to see how to Join tables - http://www.mssqltips.com/sqlservertip/1667/sql-server-join-example/.

Once you have these concepts mastered, we can work on your next steps.

Happy learning.

Thank you,
Jeremy Kadlec
Community Co-Leader


Thursday, July 11, 2013 - 5:48:33 PM - reddevil Back To Top

como puedo ver los datos guardados en mi base de datos la verdad soy nuevo en esto y no se como ver los datos guardados y en que tablas estan 


Learn more about SQL Server tools