SQL Performance Improvement Guidelines

• As a common practice, every table should have a clustered index. Generally, but not always, the clustered index should be on a column that monotonically increases, such as an identity column or some other column where the value is unique. In many cases, the primary key is the ideal column for a clustered index.

• Do not automatically add indexes on a table because it seems like the right thing to do. Only add indexes if you know that they will be used by the queries run against the table.
• Ideally a clustered index should be based on a single column (not multiple columns) that are as narrow as possible. This not only reduces the clustered index's physical size, it also reduces the physical size of non-clustered indexes and boosts SQL Server's overall performance.
• When you create a clustered index, try to create it as a unique clustered index, not a non-unique clustered index.
• Queries that include either the DISTINCT or the GROUP BY clauses can be optimized by including appropriate indexes. Any of the following indexing strategies can be used:
• Include a covering, non-clustered index (covering the appropriate columns) of the DISTINCT or the GROUP BY clauses.
• Include a clustered index on the columns in the GROUP BY clause.
• Include a clustered index on the columns found in the SELECT clause.
• Adding appropriate indexes to queries that include DISTINCT or GROUP BY is most important for those queries that run often.
• Avoid use of cursor. It could be done using WHILE loops.
• Avoid use of COUNT in a sub-query.
  
• SET NOCOUNT ON

SET NOCOUNT ON at the beginning of each stored procedure you write. This statement should be included in every stored procedure, trigger, etc. that you write.

e.g.

USE AdventureWorks2012;

GO

SET NOCOUNT OFF;

GO

-- Display the count message.

SELECT TOP(5)LastName 

FROM Person.Person

WHERE LastName LIKE 'A%';

GO

-- SET NOCOUNT to ON to no longer display the count message.

SET NOCOUNT ON;

GO

SELECT TOP(5) LastName

FROM Person.Person

WHERE LastName LIKE 'A%';

GO

-- Reset

SET NOCOUNT to OFF

SET NOCOUNT OFF;

GO

• Between over IN

When you have a choice of using the IN or the BETWEEN clauses in your Transact-SQL, you will generally want to use the BETWEEN clause, as it is much more efficient.

e.g.

SELECT task_id, task_name

FROM tasks

WHERE task_id in (1000, 1001, 1002, 1003, 1004)

...is much less efficient than this:

SELECT task_id, task_name

FROM tasks

WHERE task_id BETWEEN 1000 and 1004

• Avoid using the SUBSTRING function

If possible, you should avoid using the SUBSTRING function and use the LIKE condition instead for better performance. Instead of doing this:

WHERE SUBSTRING(task_name,1,1) = 'b'

Try using this instead:

WHERE task_name LIKE 'b%'

• If you are creating a stored procedure to run in a database other than the Master database, don't use the prefix sp_ in its name. This special prefix is reserved for system stored procedures.Although using this prefix will not prevent a user defined stored procedure from working, what it can do is to slow down its execution ever so slightly. 

•  When joining tables, you should specify a WHERE expression.

Joins without WHERE expressions are often time-consuming to evaluate because of the multiplier effect of the Cartesian product. For example, joining two tables of 1,000 rows each without specifying a WHERE expression or an ON clause, produces a result table with one million rows.

• Creating Full Text Catalog and Full Text Search

Full Text Index helps to perform complex queries against character data.  These queries can include word or phrase searching. We can create a full-text index on a table or indexed view in a database.

FREETEXT( ) Is predicate used to search columns containing character-based data types. It will not match the exact word, but the meaning of the words in the search condition. When FREETEXT is used, the full-text query engine internally performs the following actions on the freetext_string, assigns each term a weight, and then finds the matches.

• Separates the string into individual words based on word boundaries (word-breaking).
• Generates inflectional forms of the words (stemming).
• Identifies a list of expansions or replacements for the terms based on matches in the thesaurus.

e.g.

SELECT BusinessEntityID, JobTitle

FROM HumanResources.Employee

WHERE FREETEXT(*, 'Marketing Assistant');

• You can speed up your query by rewriting it with OR condition as a UNION

e.g.

rewriting

SELECT * FROM table WHERE (Field1 = 'Value1') OR (Field2 = 'Value2') 

as

SELECT ... WHERE Field1 = 'Value1' 

UNION 

SELECT ... WHERE Field2 = 'Value2' 

• Use of NOLOCK

Use of WITH(NOLOCK) can prevent reads being deadlocked by other operations

e.g.

SELECT *

FROM sys.all_objects a WITH (NOLOCK)

CROSS JOIN sys.all_objects b WITH (NOLOCK)

About Author

Rajratna Shelhalkar works with Systems Plus and is working on Dot Net technology projects. He can be contacted at: rajratna.s@spluspl.com