Learn more about SQL Server tools

mssqltips logo
giveaway
 

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

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server Query Performance Guidelines Tutorial



By:
Overview

In my years being a DBA Iíve seen many (even made some myself) common mistakes when reviewing the SQL queries that run against the systems I maintain. With this experience Iíve found that there are a some general guidelines that should be followed when writing queries and also when designing a database schema. In this tutorial we will take a look at a few different areas where these common mistakes are made and what can be done to fix them. These areas include:

  • Query writing
  • Indexing
  • Schema design
Explanation

In each section of this tutorial we will take a look at specific examples that will illustrate things that should be avoided when it comes to performance in SQL Server. For each of these items I will provide a solution or alternative that would provide better performance. Please keep in mind that these are general guidelines and there will be exceptions to these examples but in general following these basic principles should get you off to a fast start performance wise.

The specific topics that will be covered in this tip are as follows:

  • Query writing:
    • How Join Order Can Affect the Query Plan
    • Remove Function Calls From the SELECT List
    • Avoid Using <> in WHERE Clause
    • Avoid Using Functions in WHERE Clause
    • Avoid Using Wildcard Characters to Start Search Criteria
    • Use a Derived Table in Place of IN Predicate With Aggregate Functions
  • Indexing:
    • Make Sure All JOIN Columns are Indexed
    • Use WHERE, JOIN, ORDER BY, SELECT Column Order When Creating Indexes
    • Make Sure All Tables Have a Clustered Index Defined
  • Schema design:
    • Use DELETE CASCADE Option to Handle Child Key Removal in Foreign Key Relationships
    • Denormalize For Performance

Sample Table and Data Setup

So you can work through these examples in your own environment below is a sample schema with some test data you can use to test each topic.

-- NOTE: You have to recreate these tables after each section of the tutorial

-- table creation logic
-- parent table
CREATE TABLE [dbo].[Parent](
 [ParentID] [bigint] NOT NULL,
 [IntDataColumn] [bigint] NULL,
 [VarcharDataColumn] [varchar](1000) NULL,
 [DateDataColumn] [datetime] NULL,
 CONSTRAINT [PK_Parent] PRIMARY KEY CLUSTERED 
    ([ParentID] ASC)
)
GO
-- child table
CREATE TABLE [dbo].[Child](
 [ChildID] [bigint] NOT NULL,
 [ParentID] [bigint] NULL,
 [IntDataColumn] [bigint] NULL,
 [VarcharDataColumn] [varchar](10) NULL,
 CONSTRAINT [PK_Child] PRIMARY KEY CLUSTERED 
    ([ChildID] ASC)
)
GO
-- foreign key constraint
ALTER TABLE [dbo].[Child]  WITH CHECK 
ADD CONSTRAINT [FK_Child_Parent] FOREIGN KEY([ParentID])
REFERENCES [dbo].[Parent] ([ParentID])
ON DELETE CASCADE
GO

-- child detail table
CREATE TABLE [dbo].[ChildDetail](
 [ChildDetailID] [bigint] NOT NULL,
 [ChildID] [bigint] NOT NULL,
 [ExtraDataColumn] [bigint] NULL,
 CONSTRAINT [PK_ChildDetail] PRIMARY KEY CLUSTERED 
    ([ChildDetailID],[ChildID] ASC)
)
GO
-- foreign key constraint
ALTER TABLE [dbo].[ChildDetail]  WITH CHECK 
ADD CONSTRAINT [FK_ChildDetail_Child] FOREIGN KEY([ChildID])
REFERENCES [dbo].[Child] ([ChildID])
ON DELETE CASCADE
GO

-- data load
DECLARE @val BIGINT
DECLARE @val2 BIGINT
SELECT @val=1
WHILE @val < 100000
BEGIN  
   INSERT INTO dbo.[Parent] VALUES(@val,@val,'TEST' + CAST(@val AS VARCHAR),getdate()-(@val/24.0))
   
   SELECT @val2=1
   WHILE @val2 < 20
   BEGIN  
      INSERT INTO dbo.[Child] VALUES ((@val*100000)+@val2,@val,@val,'TEST' + CAST(@val AS VARCHAR))
      INSERT INTO dbo.[ChildDetail] VALUES (1,(@val*100000)+@val2,9999)
      INSERT INTO dbo.[ChildDetail] VALUES (2,(@val*100000)+@val2,1111)
      INSERT INTO dbo.[ChildDetail] VALUES (3,(@val*100000)+@val2,3333)
      INSERT INTO dbo.[ChildDetail] VALUES (4,(@val*100000)+@val2,7777)
   SELECT @val2=@val2+1
   END
   SELECT @val=@val+1
     
END
GO 

-- small table for joins
CREATE TABLE [dbo].[Small](
 [SmallID] [bigint] NOT NULL,
 [IntDataColumn] [bigint] NULL,
 [VarcharDataColumn] [varchar](100) NULL,
 CONSTRAINT [PK_Small] PRIMARY KEY CLUSTERED 
    ([SmallID] ASC)
)
GO

-- data load
INSERT INTO dbo.[Small] VALUES(50,80,'TEST5080')
INSERT INTO dbo.[Small] VALUES(510,810,'TEST510810')
INSERT INTO dbo.[Small] VALUES(7001,9030,'TEST70019030')
INSERT INTO dbo.[Small] VALUES(12093,10093,'TEST1209310093')
INSERT INTO dbo.[Small] VALUES(48756,39843,'TEST48756,39843')
INSERT INTO dbo.[Small] VALUES(829870,57463,'TEST82987057463')
GO


-- cleanup statements
--DROP TABLE [dbo].[Small]
--DROP TABLE [dbo].[ChildDetail]
--DROP TABLE [dbo].[Child]
--DROP TABLE [dbo].[Parent]





More SQL Server Solutions











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    Notify for updates 


SQL tips:

*Enter Code refresh code     



Learn more about SQL Server tools