SQL Server performance tips
A colleague of mine has been looking at SQL Server performance, and come up with a great set of tips (mostly gleaned from this website):
Does your SQL statement have a WHERE clause?
I know this sounds obvious, but don't retrieve more data than you need. However, less obvious is that even if your SELECT statement retrieves the same quantity of data without a WHERE clause, it may run faster with one.
Is SELECT DISTINCT being used properly?
Again, pretty obvious, but using SELECT DISTINCT where no duplicate records are being returned is an unnecessary performance hit. If you are getting duplicate records, first double check your table joins as this is often the cause and only use the DISTINCT clause if you really need it.
Are you using UNION instead of UNION ALL?
A UNION statement effectively does a SELECT DISTINCT on the results set. If you know that all the records returned are unique from your union, use UNION ALL instead, it is much quicker.
Are your stored procedures prefixed with 'sp_'?
Any stored procedures prefixed with 'sp_' are first searched for in the Master database rather than the one it is created in. This will cause a delay in the stored procedure being executed.
Are all stored procedures referred to as dbo.sprocname?
When calling a stored procedure you should include the owner name in the call, i.e. use EXEC dbo.spMyStoredProc instead of EXEC spMyStoredProc.
Prefixing the stored procedure with the owner when executing it will stop SQL Server from placing a COMPILE lock on the procedure while it determines if all objects referenced in the code have the same owners as the objects in the current cached procedure plan.
Are you using temporary tables when you don't need to?
Although there is sometimes a benefit of using temporary tables, generally they are best eliminated from your stored procedure. Don't assume that retrieving data multiple times is always less efficient than getting the data once and storing it in temporary table as often it isn't. Consider using a sub-query or derived table instead of a temporary table (see examples below). If you are using a temporary table in lots of JOINS in you stored procedure and it contains loads of data, it might be beneficial to add an index to your temporary table as this may also improve performance.
An example of a derived table instead of a temporary table
Are you using Cursors when you don't need to?
Cursors of any kind slow down SQL Server's performance. While in some cases they are unavoidable, often there are ways to remove them from your code.
Consider using any of these options instead of using a cursor as they are all faster:
If you are use SQL transactions, try to keep them as short as possible. This will help db performance by reducing the number of locks. Remove anything that doesn't specifically need to be within the transaction like setting variables, select statements etc.
Is SET NO COUNT ON being used?
By default, every time a stored procedure is executed, a message is sent from the server to the client indicating the number of rows that were affected by the stored procedure. You can reduce network traffic between the server and the client if you don't need this feature by adding SET NO COUNT ON at the beginning of your stored procedure.
Are you using IN or NOT IN when you should be using EXISTS or NOT EXISTS?
If you are using IN or NOT IN in a WHERE clause that contains a sub-query you should re-write it to use either EXISTS, NOT EXISTS or perform a LEFT OUTER JOIN. This is because particularly the NOT IN statement offers really poor performance. The example below probably better explains what I mean:
e.g. This SQL statement:
Could be re-written like this:
Do you have a function that acts directly on a column used in a WHERE clause?
If you apply a function to a column used in the WHERE clause of your SQL statement, it is unlikely that the SQL statement will be able to make use of any indexes applied to that column.
e.g.
Could be re-written:
Use the BETWEEN clause as it is much more efficient
e.g. This SQL statement:
Should be re-written like this:
Are you doing excessive string concatenation in your stored procedure?
Where possible, avoid doing loads of string concatenation as it is not a fast process in SQL Server.
Have you checked the order of WHERE clauses when using AND?
If you have a WHERE clause that includes expressions connected by two or more AND operators, SQL Server will evaluate them from left to right in the order they are written (assuming that no parenthesis have been used to change the order of execution). You may want to consider one of the following when using AND:
Often you don't have much of a choice of which operator you use in your SQL statement. However, sometimes there is an alternative way to re-write your SQL statement to use a more efficient operator. Below is a list of operators in their order of performance (with the most efficient first).
Does your SQL statement have a WHERE clause?
I know this sounds obvious, but don't retrieve more data than you need. However, less obvious is that even if your SELECT statement retrieves the same quantity of data without a WHERE clause, it may run faster with one.
Is SELECT DISTINCT being used properly?
Again, pretty obvious, but using SELECT DISTINCT where no duplicate records are being returned is an unnecessary performance hit. If you are getting duplicate records, first double check your table joins as this is often the cause and only use the DISTINCT clause if you really need it.
Are you using UNION instead of UNION ALL?
A UNION statement effectively does a SELECT DISTINCT on the results set. If you know that all the records returned are unique from your union, use UNION ALL instead, it is much quicker.
Are your stored procedures prefixed with 'sp_'?
Any stored procedures prefixed with 'sp_' are first searched for in the Master database rather than the one it is created in. This will cause a delay in the stored procedure being executed.
Are all stored procedures referred to as dbo.sprocname?
When calling a stored procedure you should include the owner name in the call, i.e. use EXEC dbo.spMyStoredProc instead of EXEC spMyStoredProc.
Prefixing the stored procedure with the owner when executing it will stop SQL Server from placing a COMPILE lock on the procedure while it determines if all objects referenced in the code have the same owners as the objects in the current cached procedure plan.
Are you using temporary tables when you don't need to?
Although there is sometimes a benefit of using temporary tables, generally they are best eliminated from your stored procedure. Don't assume that retrieving data multiple times is always less efficient than getting the data once and storing it in temporary table as often it isn't. Consider using a sub-query or derived table instead of a temporary table (see examples below). If you are using a temporary table in lots of JOINS in you stored procedure and it contains loads of data, it might be beneficial to add an index to your temporary table as this may also improve performance.
An example of a derived table instead of a temporary table
SELECT COLUMN1, COLUMN2, COUNTOFCOL3
FROM A_TABLE A
INNER JOIN (SELECT COUNT(COLUMN3) AS COUNTOFCOL3, COLUMN2
FROM B_TABLE B
INNER JOIN C_TABLE C ON B.ID = C.ID) ON A.ID = B.ID
Are you using Cursors when you don't need to?
Cursors of any kind slow down SQL Server's performance. While in some cases they are unavoidable, often there are ways to remove them from your code.
Consider using any of these options instead of using a cursor as they are all faster:
- Derived tables
- Sub-queries
- CASE statements
- Multiple queries
- Temporary tables
If you are use SQL transactions, try to keep them as short as possible. This will help db performance by reducing the number of locks. Remove anything that doesn't specifically need to be within the transaction like setting variables, select statements etc.
Is SET NO COUNT ON being used?
By default, every time a stored procedure is executed, a message is sent from the server to the client indicating the number of rows that were affected by the stored procedure. You can reduce network traffic between the server and the client if you don't need this feature by adding SET NO COUNT ON at the beginning of your stored procedure.
Are you using IN or NOT IN when you should be using EXISTS or NOT EXISTS?
If you are using IN or NOT IN in a WHERE clause that contains a sub-query you should re-write it to use either EXISTS, NOT EXISTS or perform a LEFT OUTER JOIN. This is because particularly the NOT IN statement offers really poor performance. The example below probably better explains what I mean:
e.g. This SQL statement:
SELECT A_TABLE.COLUMN1
FROM A_TABLE
WHERE A_TABLE.COLUMN2 NOT IN (SELECT A_TABLE2.COLUMN2
FROM A_TABLE2)
Could be re-written like this:
SELECT A_TABLE.COLUMN1
FROM A_TABLE
WHERE NOT EXISTS (SELECT A_TABLE2.COLUMN2
FROM A_TABLE2
WHERE A_TABLE.COLUMN2 = A_TABLE2.COLUMN2)
Do you have a function that acts directly on a column used in a WHERE clause?
If you apply a function to a column used in the WHERE clause of your SQL statement, it is unlikely that the SQL statement will be able to make use of any indexes applied to that column.
e.g.
SELECT A_TABLE.LASTNAME
FROM A_TABLE
WHERE SUBSTRING (FIRSTNAME,1,1) = 'm'
Could be re-written:
SELECT A_TABLE.LASTNAMEWhere you have a choice of using the IN or BETWEEN clauses
FROM A_TABLE
WHERE FIRSTNAME LIKE = 'm%'
Use the BETWEEN clause as it is much more efficient
e.g. This SQL statement:
SELECT A_TABLE.NAME
FROM A_TABLE
WHERE A_TABLE.NUMBER IN (100, 101, 102, 103)
Should be re-written like this:
SELECT A_TABLE.NAME
FROM A_TABLE
WHERE A_TABLE.NUMBER BETWEEN 100 AND 103
Are you doing excessive string concatenation in your stored procedure?
Where possible, avoid doing loads of string concatenation as it is not a fast process in SQL Server.
Have you checked the order of WHERE clauses when using AND?
If you have a WHERE clause that includes expressions connected by two or more AND operators, SQL Server will evaluate them from left to right in the order they are written (assuming that no parenthesis have been used to change the order of execution). You may want to consider one of the following when using AND:
- Locate the least likely true AND expression first. This way, if the AND expression is false, the clause will end immediately, saving time.
- If both parts of an AND expression are equally likely being false, put the least complex AND expression first. This way, if it is false, less work will have to be done to evaluate the expression.
Often you don't have much of a choice of which operator you use in your SQL statement. However, sometimes there is an alternative way to re-write your SQL statement to use a more efficient operator. Below is a list of operators in their order of performance (with the most efficient first).
- =
- >, >=, <, <=
- LIKE
- <>
40 Comments:
Nice! *bookmarked* :-)
Glad you liked it - I knew someone would find it useful.
Very good article!
Regarding the difference between IN and EXISTS you can check out one of my blog posts: SQL: IN and EXISTS
Thanks Bjorn, interesting post.
This is a really nice list. Concise and clearly written. I'll be referring to it regularly! Thanks!
Glad you like it Steve.
I'd be inclined to add some points about making sure that searched columns are indexed, and ensure that the query plans shown in query analyser do not contain thick lines. If they do you can talk about forcing join types or introducing temp tables (or in-place sub queries) to improve performance.
I didn't know about the thick lines - I'll check that out. Thanks!
Dont forget to include Sub selects. They are performance killers.
I'm not convinced about any impact of the order of where clauses. I'm sure the optimisor decides the best table access path - joins - (and thus which indexes and where clause filters may be processed at the same time) regardless of the where clause order. I've tested query plans after changing the order and it doesn't seem to impact it....
Hi can you take a look at this and let me know if there is a reason why im getting duplicate records;
SELECT
tEmployee.EmpCode,
tPerson.FirstName,
tPerson.LastName,
tPersonSensitive.BirthDate,
PerCon.Email as EmailAddress,
tEmployee.HireDate as DateHired,
vdsEmpWorkStatus.StatusEmpCode as Status,
tPersonSecondEd.Location as SecEdLocation,
tPersonSecondEd.Institute as SecEdSchool,
tPersonSecondEd.SEdLevID as SecEdLevel,
tPersonSecondEd.Completed as SecEdCompleted,
tEmployee.ActualLeaveDate as RetireDate,
tPerson.SK_MobComment as MobilityPref,
tMobilityText.Code as Mobility,
tSK_EmpSuccesData.EarliestMoveDate,
tSK_EmpSuccesData.InTop250,
tSK_EmpSuccesData.LatestMoveDate,
tSK_EmpSuccesData.RatingDate as PotentialRatingDate,
vRetentionRisk.Code as RiskOfLoss,
PerCon.WorkPhone,
tEmpIndivPref.CareerPreference as CareerAspirations,
vEmpSlotPosSuperAll.Title as HRISTitle,
POrgUnitBasic.UnitCode_2 as BusinessUnit,
vEmpSlotPosSuperAll.SupervisorEmpCode as MgrEmpCode,
vEmpSlotPosSuperAll.SupervisorFullName as MgrFullName,
vEmpSlotPosSuperAll.SupervisorTitle as MgrTitle,
POrgUnitBasic.UnitCode_3 as Division,
vPosition.SK_Region,
vPosition.SK_District,
vPosition.SK_EnglishTitle,
vPosition.SK_Department as Department,
vPosition.Description as PositionDesc,
tEmployee.ETWeb9_EmpID as EmpID,
vEmpSlotPosSuperAll.MiddleName as MiddleInitial,
vPosition.SK_Location as CityLocation,
vEmpSlotPosSuperAll.DateAppointed,
tSK_EmpSuccesData.RatingOfPotentialID as Potential,
tSK_EmpCurManagLev.CurrMgmtlevelID as CurrMgmtLevel,
tSK_EmpSuccesData.HiPotential as EmpHiPotential
FROM
tEmployee INNER JOIN tPerson
ON tEmployee.PID = tPerson.PID INNER JOIN
tPersonSensitive
ON tEmployee.PID = tPersonSensitive.PID INNER JOIN
tPersonContact PerCon
ON tEmployee.PID = PerCon.PID INNER JOIN
vdsEmpWorkStatus
ON tEmployee.PID = vdsEmpWorkStatus.PID INNER JOIN
tPersonSecondEd
ON tEmployee.PID = tPersonSecondEd.PID INNER JOIN
vEmpSlotPosSuperAll
ON tEmployee.PID = vEmpSlotPosSuperAll.PID INNER JOIN
tSK_EmpCurManagLev
ON tEmployee.PID = tSK_EmpCurManagLev.PID INNER JOIN
tSK_EmpSuccesData
ON tEmployee.PID = tSK_EmpSuccesData.PID INNER JOIN
tEmpIndivPref
ON tEmployee.PID = tEmpIndivPref.PID INNER JOIN
vdsSlotEmpPosition
ON tEmployee.PID = vdsSlotEmpPosition.PID LEFT JOIN
vdsPosOrgUnitBasic POrgUnitBasic
ON vdsSlotEmpPosition.PosID = POrgUnitBasic.PosID LEFT JOIN
vPosition
ON vdsSlotEmpPosition.Title = vPosition.Title LEFT JOIN
tMobilityText
ON tPerson.SK_MobileID = tMobilityText.MobileID LEFT JOIN
vRetentionRisk
ON vRetentionRisk.RetRiskID = tSK_EmpSuccesData.RiskOfLossID
WHERE
(tEmployee.EmpCode LIKE 'QB%')
Hi Aniel,
I can't really tell without looking at the data, but I'm guessing that one of the tables has two entries for a foreign key, such as an employee having more than one entry in (for example) tPersonSecondEd.
Hi Simon,
Ok well im working with data that may be duplicated in my database, but if the client database doesnt then im assuming they wont see duplicate records. The problem is that i get almost 500,000 records returned even though there is only 40,000 in my DB. I think my query is duplicating the record for every join in using, any clues as to why this would be?
Well, the first step would be to remove the duplicates from your database. Then try using a cut down version of your query, maybe just one join. If that works ok, then add another join, and keep going until you find the problem. It may be the left joins, but I'm not that good at spotting things from "just looking" - I need to have a proper rummage around with the data and try out some things.
Thanks for the feedback, im applying one join at a time and seem to be working so far, 4 tables to go!! Its returning the right amount each time, computers u gotta love em ;)
Hi Simon,
Excellent Article.... Keep it up. Could you please tell me why is it not advisable to use a function in a WHERE clause? Also could you provide me with link(s) that explains well the Sql Server Stored Proc execution plan as well as the steps Sql server 2000 engine performs to creates, caches and reuses an execution plan (a detailed explanation).
Thanks: Subhadip
Hi,
Nice article, please keep updating if you can.
I had one query, if you can help - If a Table has an index on a field which would be better/optimizer:
1. Using 2 statements
- Select * from Table where indexed_field = A
- Select * from Table where indexed_field = A
OR
2. Select * from Table where indexed field = A or indexed_field = B
(Assuming about 500,000 records in table with varied indexed_field values).
Thank you!
In 1. above second statement should be = B
Subhadip, the reason that functions in a WHERE clause may not do well is if an index is applied to the column being used in the function. The function may not take the index into account.
As for the links to the execution plan details, I'm sorry, but I don't have any.
Anonymous, it depends on what you're intending to do with the results, and whether you're going to use the data together. If As and Bs populate different datasets then separating them may be better, otherwise keeping them in the same statement will probably be more convenient for processing.
Nice. Antother tip: Using UNION instead of OR in WHERE-clause can also be positive for the performance.
Wonderful Article... Thanks a lot
Wonderful Article... Thanks a lot
I suppose the Query will use the index even if we use functions in the WHERE clause. The catch here is ... that while building INDEX you will have to build it using the function.
Hence in the example below:
SELECT A_TABLE.LASTNAME
FROM A_TABLE
WHERE SUBSTRING (FIRSTNAME,1,1) = 'm'
we could build an INDEX on firstname with SUBSTRING (FIRSTNAME,1,1) function and rest assured it will be used in the above query.
I chanced upon this funda a few years back when I was working on ORACLE8i. I'am sure SQL SERVER also does the same.
Simon your expert comments solicited....
For the second last optimization given (Have you checked the order of WHERE clauses when using AND?), we can extend the rule to WHERE and OR clauses; the cluase most likely to be true should be placed first so that other OR clauses do not need to be evalutated.
Hi Simon,
how to write a query to give an addition of a decimal column of successive rows. E.g.
1
4
8
10
15
Output should
1
5
12
18
25
Hi Simon,
Write a query to delete rows from table A not present in table B based on relationship between then the two tables on Column C.
DELETE FROM [A]
WHERE NOT EXISTS (SELECT * FROM [B] WHERE [B].C=[A].C)
>> Hi Simon,
>> Write a query to delete rows from table A not present in table B based on relationship between then the two tables on Column C.
Just an Idea
Suppose you have table [A], Do
--Suppose you have Table A where you want to perform your desired operation at column "Counter"
--Skip follow line if you have Identity Column
ALTER TABLE [A] ADD ID INT IDENTITY(1,1)
DECLARE @CCounter INT
DECLARE @LCounter INT
SET @LCounter=0
DECLARE @NREC INT
DECLARE @CREC INT
SELECT @NREC=COUNT(*) FROM [A]
SET @CREC=1
WHILE @CREC<=@NREC
BEGIN
SELECT @CCounter = ISNULL(Counter,0) FROM [A] --ISNULL Incase if our value is NULL
IF @CREC>1
SELECT @CCounter+ @LCounter
ELSE
SELECT @CCounter
SET @LCounter = @CCounter
SET @CREC=@CREC+1
END
ALTER TABLE [A] DROP COLUMN ID
Hope this help.
>> Hi Simon,
>>
>> how to write a query to give an addition of a decimal column of successive rows. E.g.
>> 1
>>
>> 4
>>
>> 8
>>
>> 10
>>
>> 15
>>
>> Output should
>>
>> 1
>>
>> 5
>>
>> 12
>>
>> 18
>>
>> 25
opps
change
SELECT @CCounter = ISNULL(Counter,0) FROM [A] --ISNULL Incase if our value is NULL
to
SELECT @CCounter = ISNULL(Counter,0) FROM [A] WHERE ID=@CREC
Really good article !! It solved my performance issue with UNION.
This post has been removed by the author.
Good article.
I've created a factsheet for SQL server developers that fits on one A4 (printed on both sides). You can laminate it and keep it on your desk.
http://www.dotnet4all.com/snippets/2008/04/factsheet-for-sql-server-developers.html
Hey Xander,
That's a brilliant idea, and very nicely executed. I love the colour coding for the data type sizes - inspired. Thanks for linking.
Simon
this is really helpful to me.
thank you,
C# SQL Server yazılım geliştirme günlüğü
Ahmet Kaymaz
I've found this to be a useful list of tips, but I'm not sure about the one entitled 'Have you checked the order of WHERE clauses when using AND?'. As far as I can see, SQL Server usually manages to figure out the best order, regardless of the order in which the WHERE clause is written.
E.g.
SELECT * FROM TABLE_A
WHERE ValidFromDate > '1900-01-01'
AND ID = 1
Running a query like this one doesn't seem to cause SQL Server to look for all the rows where ValidFromDate > '1900-01-01' (lots) and then find the one where ID = 1 (just one). As far as I can see from SQL Profiler, it just finds the one where ID = 1. (In this case ID is the primary key.)
David.
Hi Simon,
This is a great article for performance.
Can you explain the performance monitoring through DMVs or if you have any good link for detail explanation.
Thanks,
Ashutosh Jha
Many Thanks man! Good AWSOME tips! Some obvious BUT some unthinkable :)
nice article..keep posting...
thanks
I've always found IN and EXISTS, as well as NOT IN and NOT EXISTS to have the exact same execution plan. The point you may have been referencing is that NOT IN and NOT EXISTS have very poor performance and can most times be written as an IN or EXISTS with a subquery using EXCEPT (MINUS in standard SQL).
Post a Comment