SQL Query Optimization and Performance Guide



Contents

Windows Settings    3

Storage on 2 Physical disks – standard solution    7

Storage on 3 Physical disks – extended solution    7

Storage on 4 physical disks – optimal solution    7

Storage on external hard disk or flash memory    9

Database Auto growth    9

Index Management    9

Stored procedures    12

Cursors    12

Query optimization    13

Scheduled Maintenance Plan    13

Check disk usage by top tables    14

Windows Settings

  • Adjust performance for background services
  • Configure size of windows paging file(virtual memory) to be twice the size of physical memory
  • Turn off system protection feature for all disks except for C
  • Disable unneeded sql services
  • Configure weekly defragmentation schedule for all disks

Storage on 2 Physical disks – standard solution

  • Store log file on C
  • Store data file on the second disk

Storage on 3 Physical disks – extended solution

  • Store data file on second disk
  • Store log file on third disk
  • Store Windows Paging File(Virtual Memory) on C

Storage on 4 physical disks – optimal solution

  • Store windows paging file on C
  • Store primary data file on second disk
  • Store log file on third disk
  • Create Secondary data file on fourth disk to store indexes

    When creating indexes, select the secondary file group

Storage on external hard disk or flash memory

  • Not allowed
  • This will disable all optimizations performed by SQL Server
  • This will limit read/write speed to 25Mbits/sec for external hard disk and slower for flash memory

Database Auto growth

  • Do not set to grow in %. Use static value instead(100MB is good option)

Index Management

  • Create non-clustered index only for tables that have rate of SELECT much larger than rate of INSERT and UPDATE
  • Do not create indexes for all table columns
  • For indexes created on base tables(for example Person, Admission, AdmissionRequestDetail), set the index fill factor to 70 instead of 80(default option). This will enhance performance for INSERT.
  • Use the following procedure to analyze index fragmentation. You should “reorganize” indexes when the External Fragmentation value for the index is between 10-15 and the Internal Fragmentation value is between 60-75. Otherwise, you should rebuild indexes.

    ALTER
    procedure
    [dbo].[CheckIndexFragmentation]
    as

    SELECT
    object_name(dt.object_id)
    Tablename,si.name

    IndexName,dt.avg_fragmentation_in_percent
    AS

    ExternalFragmentation,dt.avg_page_space_used_in_percent
    AS

    InternalFragmentation

    FROM

    (


    SELECT
    object_id,index_id,avg_fragmentation_in_percent,avg_page_space_used_in_percent


    FROM
    sys.dm_db_index_physical_stats
    (db_id(‘Clis3’),null,null,null,‘DETAILED’

    )

    WHERE
    index_id
    <> 0)
    AS
    dt
    INNER
    JOIN
    sys.indexes
    si
    ON
    si.object_id=dt.object_id

    AND
    si.index_id=dt.index_id AND dt.avg_fragmentation_in_percent>10

    AND
    dt.avg_page_space_used_in_percent<75 ORDER
    BY avg_fragmentation_in_percent

    DESC

    GO

  • Create non-clustered indexes only for columns that appear in:
    • Where clause
    • Order By clause
    • Join clause
    • Distinct
    • All foreign keys
  • Avoid indexing small tables
  • Create indexed views for columns used by Linq query in the where clause, if the index is not created on the table but do not create the index on both.
  • Do not use Year(), Month() ,Day() functions in the where clause on a column even if it has an index. Using these functions and other similar functions will disable the index and will do a full table scan. So change the query to make use of the index. Example:

    select
    *
    from
    Person
    where
    YEAR(BirthDate)=1986

    select
    *
    from
    Person
    where
    BirthDate
    >=
    ‘1986-01-01’
    and
    BirthDate
    <
    ‘1987-01-01’

  • Use the index usage report to check if there are unused indexes on the table in order to delete them. If the number of seeks is 0. The index can be deleted.

Stored procedures

  • Do not name the procedure sp_something. This will cause a delay when the procedure executes.
  • Use Set Nocount On at the top of the procedure to avoid additional round trip to the server
  • Try to avoid using exec of dynamic sql statements

Cursors

  • Do not use a cursor to iterate over a set of records. Creating and using a cursor is very expensive and resource consuming. Instead, use a while loop with defined upper bound. This option is not available for SQL 2000. Example:

        declare
    @count
    int

        select
    @count=COUNT(*)
    from
    SubDepartment

        declare
    @i
    int=1

        declare
    @temp
    table(id
    int,rownumber
    int)

        insert
    into
    @temp
    select
    ID,ROW_NUMBER()
    over(order
    by
    [order])
    from SubDepartment


        while (@i<=@count)

            begin

                update
    SubDepartment
    set
    [Order]=@i
    where
    ID=
    (select
    ID
    from
    @temp
    where
    rownumber=@i)

                set
    @i=@i+1

            end

Query optimization

  • Do not use OR in the where clause. Instead, use UNION ALL to implement OR functionality but only if there is an index on the column used in OR operator. If there is no index, this optimization will slow down performance by doing multiple table scans. If there is an index this approach will speed up the query. Example:

    select
    *
    from
    Person
    where
    person.Sex=0 or person.Sex=1

    select
    *
    from
    person
    where
    person.Sex=0 union
    all

    select
    *
    from
    person
    where
    Person.sex=1

  • Avoid using inline queries, use joins instead
  • Avoid using inline function in the query. Instead, create a precomputed column with the function formula to be stored for selection in the query. May not be applicable for all cases.
  • When writing queries containing NOT IN, the query will have poor performance as the optimizer need to use nested table scan to perform this activity. This can be avoided by using EXISTS or NOT EXISTS
  • When you have the choice to use IN or BETWEEN clause in the query, always use BETWEEN. This will speed up the query
  • When having multiple columns in where clause separated by AND operator, make sure to order the columns from least likely true to most likely true if the expected result is most likely true. Otherwise, order the columns from most likely false to least likely false. In other words, the condition that filters the least number of records must appear first in the where clause.
  • For queries that require immediate feedback to the user, use FAST n option to immediately return first n records while the query continues to fetch the remaining records. This option can be used in procedures and views but it does not apply for linq queries because .ToList() ignores this option. Example:

    SELECT
    *
    FROM
    person
    WHERE
    firstname
    like
    ‘ali’
    OPTION(FAST 100)


Scheduled Maintenance Plan

  • Use daily maintenance plan to perform daily cleanup, update statistics, check integrity, rebuild indexes, organize indexes, shrink database files and perform a full backup.
  • Do not include system databases in the plan. Select the option “User databases only”
  • Make sure SQL Server Agent startup option is set to Automatic
  • Schedule the plan to run daily at night. 1am is a good option.
  • The below plan is standard and can be used for most cases.

Check disk usage by top tables

  • Monitor disk usage by most used tables. These will be the target for potential indexes.
  • Use this approach to check if the application is performing repeated unnecessary reads

One Response to SQL Query Optimization and Performance Guide

  1. Hasan Jaffal says:

    Nice post

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: