Maximize SQL Server Performance with SQL Tuning by ddn50364

VIEWS: 91 PAGES: 47

									Maximize SQL Server
Performance with SQL
Tuning



Claudia Fernandez
SQL Tuning Products, Product Manager
Quest Software, Inc.
Agenda
 Understanding Microsoft tuning
 techniques
 – SET STATISTICS IO
 – SET STATISTICS TIME
 – SET SHOW PLAN
 Tips and tricks toward making
 queries perform better
 Using tools to tune SQL
SET STATISTICS IO
 Enabled before a query is run
 Can be enabled as a query parameter in SQL
 Query Analyzer
 The important info appears after the result set of
 the query is returned:
 –   How many scans were performed
 –   How many logical reads (reads in cache) were performed
 –   How many physical reads (reads on disk) were performed
 –   How many pages were placed in the cache in anticipation
     of future reads (read-ahead reads)
 Good queries usually have higher logical reads and
 few, if any physical reads and scans
EXAMPLE
Query:
USE northwind
GO
SET STATISTICS IO ON
GO
SELECT COUNT(*) FROM employees
GO
SET STATISTICS IO OFF
GO

Results:
-----------
2977

Table 'Employees'. Scan count 1, logical reads
  53, physical reads 0, read-ahead reads 0.
Analyzing Results
Results:
-----------
2977

Table 'Employees'. Scan count 1, logical reads 53, physical
   reads 0, read-ahead reads 0.


  Obtain table size statistics
  sp_spaceused employees

Results:
name       rows reserved data     index_size unused
---------- ---- --------- ------- ----------- -------
Employees 2977 2008 KB    1504 KB 448 KB      56 KB
SET STATISTICS TIME
 Enabled before a query is run
 Can be enabled as a query parameter in SQL
 Query Analyzer
 Returns the elapsed time of each query with
 the query result set
 Depends on the total activity of the server
 Gives you a more accurate metric for the
 user experience
 Helps you measure software performance in
 terms of real performance
EXAMPLE #1
Query:
SET STATISTICS TIME ON
GO
SELECT COUNT(*) FROM titleauthors
GO
SET STATISTICS TIME OFF
GO

Results:
SQL Server Execution Times:
   cpu time = 0 ms. elapsed time = 8672 ms.
SQL Server Parse and Compile Time:
   cpu time = 10 ms.

-----------
25

 SQL Server Execution Times:
   cpu time = 0 ms. elapsed time = 10 ms.
SQL Server Parse and Compile Time:
   cpu time = 0 ms.
EXAMPLE #2
  Use this script to capture time before and after a single
  command that does not contain multiple GO
  statements, reporting a total elapsed time in seconds
  for the statement

Query:

DECLARE @start_time DATETIME
SELECT @start_time = GETDATE()
< any query or a script that you want to
   time, without a GO >
SELECT ‘Elapsed sec’ = DATEDIFF(second,
   @start_time, GETDATE() )
GO
EXAMPLE #3
  Use this script to capture time before and after multiple
  commands that contain multiple GO statements,
  reporting a total elapsed time in seconds for the
  statement

Query:

CREATE TABLE #save_time ( start_time DATETIME NOT NULL )
INSERT #save_time VALUES ( GETDATE() )
GO

< any script that you want to time (may include GO) >
GO

SELECT ‘Elapsed sec’ = DATEDIFF(second, start_time,
   GETDATE() )
FROM   #save_time
DROP TABLE #save_time
GO
Retrieving Query Plans
 Retrieve query plan output via:
 –   SET SHOWPLAN_TEXT ON
 –   SET SHOWPLAN_ALL ON
 –   SET STATISTICS PROFILE ON
 –   Graphic Explain Plan
 We will focus on SHOWPLAN_TEXT
Other Useful SET Commands
SET NOEXEC {ON | OFF}: checks T-SQL
syntax. Includes compile step but does
not execute code. Useful for checking
deferred-name resolution.
SET FMTONLY {ON | OFF}: returns only
metadata of query result set to client,
usually column headers.
SET PARSEONLY {ON | OFF}: checks T-
SQL code for syntax but does not compile
or execute steps.
Invoking SHOWPLAN
Invoke SHOWPLAN via:
 SET SHOWPLAN_TEXT ON
 GO

 …<SQL and T-SQL code>…
 GO

 SET SHOWPLAN_TEXT OFF
 GO


Query Analyzer
 – Enable this setting under Query   Current
   Connection Properties.
Why not SHOWPLAN_ALL?
 SHOWPLAN_TEXT retrieves a tight,
 cohesive result set
 – It is easy to read and, once trained, easy to
   understand
 SHOWPLAN_ALL retrieves a much
 larger result set geared towards a user
 interface
 – It is NOT easy to read
Comparing SHOWPLAN_TEXT…
SELECT * FROM authors

SHOWPLAN_TEXT StmtText
-------------------------------
  |--Clustered Index Scan
     (OBJECT:([pubs].[dbo].[authors].[UPKCL_auidind]))
…To SHOWPLAN_ALL, pg 1
SHOWPLAN_ALL StmtText
-----------------------------
  |--Clustered Index Scan
    (OBJECT:([pubs].[dbo].
    [authors].[UPKCL_auidind]))

StmtID NodeID Parent
------ ------ ------
  2     1     0

PhysicalOp            LogicalOp
---------------------- ----------------------
  NULL                NULL
  Clustered Index Scan Clustered Index Scan

Argument
-------------------------------------------------
  1
  OBJECT:([pubs].[dbo].[authors].[UPKCL_auidind])
…To SHOWPLAN_ALL, pg 2
DefinedValues
--------------------------------------------------
  23
  …<all columns in table>…

EstimatedRows EstimatedIO EstimatedCPU
------------- ------------ --------------
  23          NULL         NULL
  23          0.01878925 5.1899999E-5

AvgRowSize TotalSubtreeCost
---------- -----------------
  NULL       3.7682299E-2
  111       3.7682299E-2
…To SHOWPLAN_ALL, pg 3
OutputList
--------------------------------------------------
  NULL
  …<all columns in table>…

Warnings Type         Parallel EstimateExecutions
-------- --------- -------- ------------------
  NULL SELECT          0       NULL
  NULL PLAN_ROW 0                 1.0
Query Plan Operations Overview
 Falls into two general categories:
  – Physical Operators: describes the physical
    algorithm used to process the query
  (ex. Performing an index seek)
  – Logical Operators: describes the relational
    algebra algorithm used by the statement
  (ex. An aggregation)
 Represents the steps taken by the
 query engine to process the query
Getting Started
 SQL Server will attempt to satisfy any given query as
 efficiently as it can
 Will use the best SEEK operation available, based on
 indexes, and if none are available, then use a SCAN
 operation
  SELECT ord_date
  FROM big_sales
  --------------------------------------------------------------------
    |--Clustered Index Scan(OBJECT:
     ([pubs].[dbo].[big_sales].[UPKCL_big_sales]))

 Depending on the kind of query, an alternative index
 might be available:
  SELECT title_id
  FROM big_sales
  ---------------------------------------------------------------
    |--Index Scan(OBJECT: ([pubs].[dbo].[big_sales].[ndx_sales_ttlID]))
SEEK versus SCAN
 The WHERE clause can make the single largest difference
   in a queries performance:

    SELECT *
    FROM big_sales
    WHERE stor_id = '6380'
    ----------------------------------------------------------
      |--Clustered Index Seek(OBJECT:
         ([pubs].[dbo].[big_sales].[UPKCL_big_sales]),
         SEEK:([big_sales].[stor_id]=[@1]) ORDERED FORWARD)
Branching Steps in the Plan
In queries with branching steps, the indented lines show
   you the order of occurrence from bottom to top

   SELECT au_fname, au_lname
   FROM   authors
   WHERE au_id IN
      (SELECT au_id FROM titleauthor)
   ----------------------------------------------------------
     |--Nested Loops(Inner Join, OUTER REFERENCES:
      ([titleauthor].[au_id]))
          |--Stream Aggregate(GROUP BY:([titleauthor].[au_id]))
          |    |--Index Scan(OBJECT:([pubs].[dbo].[titleauthor].
                  [auidind]), ORDERED FORWARD)
          |--Clustered Index Seek(OBJECT: ([pubs].[dbo].[authors].
             [UPKCL_auidind]),
             SEEK:([authors].[au_id]=[titleauthor].[au_id])
             ORDERED FORWARD)
Comparing Query Plans
This is the most important and common
usage for reviewing query plans – to
figure out which approach to a query
works best
The SET SHOWPLAN_TEXT is most useful
for quick query plan comparison
Graphic Explain Plan or SET STATISTICS
PROFILE are better for true tuning and
comparison because they show the cost
of each step in the query
Join Tip #1
There are three major join methods:
 – Nested Loop: Best strategy for joining small
   tables. It cycles through the outer (hopefully,
   smaller table) then searches for matches in the
   inner table.
 – Merge: Best strategy for large, similarly sized
   tables that are sorted. It takes a row from each
   table and directly compares them.
 – Hash: Best for large, dissimilarly sized tables or
   those without a useful index and other special
   operations. It builds a hashing table to sort
   through the results.
Join Tip #2
Hash joins are computationally expensive
Merge joins are too, but less so
Get a clue and tune your production
queries that consistently use HASH join
strategies
Subquery Usage
 Investigate replacing subqueries with
 Joins
 – The optimizer invisibly flattens subqueries
   and replaces them with joins
 – Explicit joins give the optimizer more
   options to choose the order of tables and
   find the best possible plan
Subquery Examples
 SELECT st.stor_name AS 'Store',
   ISNULL((SELECT SUM(bs.qty) FROM big_sales AS bs
           WHERE bs.stor_id = st.stor_id), 0) AS ‘Books Sold'
 FROM stores AS st
 WHERE st.stor_id IN (SELECT DISTINCT stor_id
    FROM big_sales)
 ~~~~~
 SELECT st.stor_name AS 'Store', SUM(bs.qty) AS 'Books Sold'
 FROM stores AS st
 JOIN big_sales AS bs ON bs.stor_id = st.stor_id
 WHERE st.stor_id IN (SELECT DISTINCT stor_id
   FROM big_sales)
 GROUP BY st.stor_name
Subquery Statistics Time
Subquery:                  Join:
 SQL Server parse           SQL Server parse
 and compile time:          and compile time:
   CPU time = 28 ms,          CPU time = 50 ms,
   elapsed time = 28 ms.      elapsed time = 54 ms.

 SQL Server                 SQL Server
 Execution Times:           Execution Times:
   CPU time = 145 ms,         CPU time = 109 ms,
   elapsed time = 145
                              elapsed time = 109
   ms.
                              ms.
Subquery Statistics I/O
 Subquery:
  – Table 'big_sales'. Scan count 14, logical reads 1884,
    physical reads 0, read-ahead reads 0.
  – Table 'stores'. Scan count 12, logical reads 24,
    physical reads 0, read-ahead reads 0.
~~~~~
 Join:
  – Table 'big_sales'. Scan count 14, logical reads 966,
    physical reads 0, read-ahead reads 0.
  – Table 'stores'. Scan count 12, logical reads 24,
    physical reads 0, read-ahead reads 0.
Subquery Query Plans
Subquery:                         Join:
|--Compute Scalar                 |--Stream Aggregate
     |--Nested Loops                  |--Sort(ORDER
                                    BY:([st].[stor_name] ASC))
         |--Nested Loops
                                          |--Nested Loops
        | |--Stream                           |--Nested Loops
   Aggregate
                                              | |--Stream
         | | |--Clustered           Aggregate
   Index Scan                                 | | |--
         | |--Clustered Index       Clustered Index Scan
   Seek                                       | |--Clustered
        |--Stream Aggregate         Index Seek
             |--Clustered Index               |--Clustered Index
                                    Seek
UNION vs. UNION ALL
  Whenever possible use UNION ALL instead
  of UNION
  UNION has the “side effect” of eliminating
  all duplicate rows and sorting results,
  which UNION ALL does not
  – Selecting a distinct result requires building a
    temporary worktable, storing all rows in it, and
    sorting before producing the output
  – Displaying the SHOWPLAN on a SELECT DISTINCT
    query will reveal a stream aggregation is taking
    place, consuming as much as 30% of the resources
    used to process the query
  – Large worktables could flood tempdb
UNION Examples
SELECT stor_id FROM big_sales
UNION
SELECT stor_id FROM sales

~~~~~

SELECT stor_id FROM big_sales
UNION ALL
SELECT stor_id FROM sales
UNION Query Plans
UNION

|--Merge Join(Union)
    |--Stream Aggregate(GROUP BY:([big_sales].[stor_id]))
    | |--Clustered Index Scan [big_sales.UPKCL_big_sales]
    |--Stream Aggregate(GROUP BY:([sales].[stor_id]))
        |--Clustered Index Scan [sales.UPKCL_sales]
~~~~~

UNION ALL

|--Concatenation
     |--Index Scan [big_sales.ndx_sales_ttlID]
     |--Index Scan [sales.titleidind]
UNION Statistics I/O
UNION

  Table 'sales'. Scan count 1, logical reads 2, physical
  reads 0, read-ahead reads 0
  Table 'big_sales'. Scan count 1, logical reads 463,
  physical reads 0, read-ahead reads 0

~~~~~

UNION ALL

  Table 'sales'. Scan count 1, logical reads 1, physical
  reads 0, read-ahead reads 0
  Table 'big_sales'. Scan count 1, logical reads 224,
  physical reads 0, read-ahead reads 0
SET NOCOUNT ON
 Command SET NOCOUNT ON allows you to
 suppress the message for all subsequent
 transactions in your session, until you issue
 the SET NOCOUNT OFF command
 Suppresses all DONE_IN_PROC messages
 from the server
 Can add up to very big savings when included
 in any procedural code such as procedures,
 functions (UDFs), triggers, and batches
 Example in the white paper inserts 9999 rows
 into the sales table:
  – Finished in 5176 milliseconds without SET NOCOUNT ON.
  – Finished in 1620 milliseconds with SET NOCOUNT ON!
TOP n and SET ROWCOUNT
 SELECT TOP n [PERCENT] allows you to
 limit a query to a specific number of
 records
 – Best method for SELECT statements
 – Only available in SQL 2000
 SET ROWCOUNT n allows you to limit all
 subsequent transactions, including
 INSERT, UPDATE, and DELETE statements,
 to a specific number of records
 – Must be manually disabled
 – Should be used with caution
 – Available for SQL Server 6, 6.5, and 2000
 TOP n Example
ANSI SQL:
   SELECT title, ytd_sales
   FROM titles a
   WHERE ( SELECT COUNT(*) FROM titles b
            WHERE b.ytd_sales > a.ytd_sales ) < 5
   ORDER BY ytd_sales DESC


~~~~~

TOP n Extension:
   SELECT TOP 5 title, ytd_sales
   FROM    titles
   ORDER BY ytd_sales DESC
Using Tools to Tune SQL
 Query Analyzer
Query Analyzer
Query menu
 – Enable SET STATISTICS IO and SET STATISTICS TIME
Tuning SQL with the Query Analyzer
   Step 1: Review the execution plan
Tuning SQL with the Query Analyzer
  Step 2. If execution plan uses
  inefficient operations, SQL needs
  tuning
Tuning SQL with the Query Analyzer
 Step 3. Tune SQL
  – Manually reformulate the SQL code
  – Review the execution plan of the alternative SQL
     • Compare original and alternative query plans
         – Are they different?
  – Execute alternative SQL (enable SET STATISTICS IO
    and SET STATISTICS TIME)
  – Is alternative SQL’s run time stats better than original
    SQL?
  – Repeat the process until satisfactory results are
    obtained
Tuning SQL with the Query Analyzer
  Reformulate SQL, time it, compare results and
  repeat until satisfactory results are achieved
Quest Central® for Databases -
SQL Server
 Powerful database tools that extend and
 enhance the abilities of the enterprise SQL
 Server DBA
 –   database administration
 –   performance diagnostics
 –   load testing
 –   database analysis
 –   space management
 –   database comparison
 –   SQL tuning
Quest Central® for SQL Server–SQL Tuning
  Analyzes execution plans to identify potential
  SQL performance problems
Quest Central® for SQL Server–SQL Tuning
  Automatically generates all SQL alternatives
Quest Central® for SQL Server–SQL Tuning
  Tests and compares SQL alternatives and
  original SQL to identify the most efficient one
  Thank you.
Additional Resources:

  More information about Quest Central:
  http://www.quest.com/quest_central/

  Register for an online demo of Quest Central for SQL
  Server:
  http://www.quest.com/landing/qc_demos.asp

  Download a trial of Quest Central for SQL Server:
  http://www.quest.com/quest_central/download.asp

  Questions? info@quest.com

								
To top