SQL Server-Performance Tunning Tips


TABLE OF CONTENTS

1.  Introduction..

2.  Query tuning..

3.  Procedure Tuning..

4.  Cursor Tuning..

5. Application Level Tuning..

6. References.

1.  Introduction

This document list is the ways of improving the performance of TSQL.It contains a few techniques and tips used in our project.

2.  Query tuning

  1. 1.    If your SQL Server database is not configured to be case sensitive, you don’t need to use LOWER or UPPER to force the case of text to be equal for a comparison to be performed:

Example:

SELECT column_name FROM table_name
WHERE LOWER (column_name) = ‘name’

(Not preferred)

 

SELECT column_name FROM table_name
WHERE column_name = ‘name’

            (Preferred)

 

Both the queries will fetch same result unless SQL Server database is not configured to be case sensitive.

To check whether the sql server is configured for case senstivity or not, use:

SELECT DATABASEPROPERTYEX (”, ‘Collation’)

Result: SQL_Latin1_General_CP1_CI_AS (means Case Insensitive)

Result: SQL_Latin1_General_CP1_CS_AS (means Case Sensitive)

  1. Use ‘= ‘ as much as possible, and  ‘<>’  as least as possible:

Operators at the top will produce results faster than those listed at the bottom.

  • =
  • >, >=, <, <=
  • LIKE
  • <>
  1. 3.    Put ‘nolock’ on tables while selecting data from them

Use of nolock on tables, in select queries, hints sql compiler not to put lock on the tables.

Example:

Select <column_names> from <table_name>

(Not preferred)

Select <column_names> from <table_name> with (nolock)

(Preferred)

  1. 4.    Stop Misspelling T-SQL Words (very useful)
    1. a.    AUTO_SHRINK

Set the AUTO_SHRINK database option to OFF. Turning it, ON may fragment your indexes.

If you know that the space that you are reclaiming will not be needed in the future, you can reclaim the space by manually shrinking the database.

  1. SELECT 

It is advisable to use column names rather than ‘*’ in select query.This will improve the performance.

  1. c.    IN

In some circumstances, you would be better to spell IN as EXISTS (especially when preceded by NOT.) The problem is that IN and EXISTS handle NULL values differently.

When dealing with null values ‘NOT IN’ won’t give same result as ‘NOT EXISTS’.

For example, refer http://bit.ly/520pQM.

  1. Do not use optimizer hints in your queries.
    SQL Server query optimizer is very clever.It is very unlikely that one can optimize the query by using optimizer hints.More often this hurts performance.
  2. 6.    Avoid use of SELECT COUNT(*)

There is an alternare way to get the count of rows in a particular table. This will improve the peformace in case the table contains huge number of rows.

Select count (*) from <table_name>

(Not preferred)

SELECT rows FROM sysindexes WHERE id = OBJECT_ID (‘<table_name>’) AND indid < 2

(Preferred)

  1. Try to avoid the HAVING clause
    The HAVING clause is used to restrict the result set returned by the GROUP BY clause. When you use GROUP BY with the HAVING clause, the GROUP BY clause divides the rows into sets of grouped rows and aggregates their values, and then the HAVING clause eliminates undesired aggregated groups. In many cases, you can write your select statement so, that it will contain only WHERE and GROUP BY clauses without HAVING clause. This can improve the performance of your query.
  2. Try to avoid using the DISTINCT clause
    The use of DISTINCT clause sorts the result, which reults into performance degradation. You should use this clause only when it is necessary.
  3. Try to use UNION ALL statement instead of UNION
    The UNION ALL statement is much faster than UNION, because UNION ALL statement does not look for duplicate rows, and UNION statement does look for duplicate rows, whether or not they exist.

10. Try to avoid use of NOT IN Clause

If you currently have a query that uses NOT IN, which offers poor performance then try out following options for better performance:

  • Use EXISTS or NOT EXISTS
  • Use IN
  • Perform a LEFT OUTER JOIN and check for a NULL condition

11. Try to replcace IN with BETWEEN

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.

Example:

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

(Not preferred)

WHERE customer_number BETWEEN 1000 and 1004

(Preferred)

12. Use ‘is null or‘ over ‘isnull ()’.

Operators are normally faster than functions.In this case use of ‘is null or‘ will fetch the results faster than ‘isnull ()’ function.

Example:

Let @in_name be the input variable.

SELECT column_names FROM table_name
WHERE isnull (@in_name, column_name) = column_name)

(Not preferred)

SELECT column_names FROM table_name
WHERE (@in_name is null or column_name=@in_name)

(Preferred)

13. Incase of insufficient indexes,use UNION ALL clause rather than OR clause

A query with one or more OR clauses can sometimes be rewritten as a series of queries that are combined with a UNION ALL statement, in order to boost the performance of the query.
Example:

WHERE dept = ‘retx’ or city = ‘karnal’ or division = ‘adidas’

(Not Preferred because this query has three separate conditions in the WHERE clause. In order for this query to use an index, then there must be an index on all three columns found in the WHERE clause)

SELECT employeeID, firstname, lastname FROM names WHERE dept = ‘retx’
UNION ALL
SELECT employeeID, firstname, lastname FROM names WHERE city = ‘karnal’
UNION ALL
SELECT employeeID, firstname, lastname FROM names WHERE division = ‘adidas’

(Preferred, because if there is only an index on dept, but not the other columns in the WHERE clause, then the first version will not use any index and a table scan must be performed. However, in the second version of the query will use the index for part of the query, but not for the entire query)

14. Rearrange where clause conditions

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. Because of this, 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.

15. Avoid sorting of the data

Whenever SQL Server has to perform a sorting operation, additional resources have to be used to perform this task. Sorting often occurs when any of the following Transact-SQL statements are executed:

  • ORDER BY
  • GROUP BY
  • SELECT DISTINCT
  • UNION
  • CREATE INDEX (generally not as critical as happens much less often)

In many cases, these commands cannot be avoided. On the other hand, there are few ways that sorting overhead can be reduced. These include:

  • Keep the number of rows to be sorted to a minimum. Do this by only returning those rows that absolutely need to be sorted.
  • Keep the number of columns to be sorted to the minimum. In other words, do not sort more columns that required.
  • Keep the width (physical size) of the columns to be sorted to a minimum.
  • Sort column with number datatypes instead of character datatypes

16. Try to avoid WHERE clauses that are non-sargable.

The term “sargable” (which is in effect a made-up word) comes from the pseudo-acronym “SARG”, which stands for “Search ARGument,” which refers to a WHERE clause that compares a column to a constant value. If a WHERE clause is sargable, this means that it can take advantage of an index (assuming one is available).

Example:

WHERE SUBSTRING (login, 1, 1) = ‘infosys’

(Non-sargable so not preferred)

WHERE login like ‘infosys%’

(Sargable so preferred)
WHERE DATEDIFF (yy, datofbirth, GETDATE ()) > 21
(Non-sargable so not preferred)
WHERE dateofbirth < DATEADD(yy,-21,GETDATE())

(Sargable because the fucntion is not applied directly on column so preferred)

WHERE NOT column_name > 5

(Non-sargable so not preferred)

WHERE column_name <= 5

(Sargable so preferred)

WHERE column_name LIKE ‘%m’

(Non-sargable so not preferred)

WHERE column_name LIKE ‘m%’
(Sargable so preferred) If you use a leading character in your LIKE clause, then the Query Optimizer has the ability to potentially use an index to perform the query, speeding performance and reducing the load on SQL Server.

17. In a WHERE clause, the various operands used directly affect how fast a query is run. The operand at the top will produce results faster than those listed at the bottom

  • A single literal used by itself on one side of an operator
  • A single column name used by itself on one side of an operator
  • a single parameter used by itself on one side of an operator
  • A multi-operand expression on one side of an operator
  • A single exact number on one side of an operator
  • Other numeric number (other than exact), date, and time
  • Character data, NULLs

3.  Procedure Tuning

  1. 1.       Include SET NOCOUNT ON statement into your stored procedures to stop the message indicating the number of rows affected by a T-SQL statement.
    This can reduce network traffic, because your client will not receive the message indicating the number of rows affected by a T-SQL statement.Use NOCOUNT at the beginning of all your Stored Procedures, Triggers and Functions.
  2. 2.       Avoid using SQL Server cursors:

Drawbacks:

  • They generally use many SQL Server resources and reduce the performance.
  • Using cursors can reduce concurrency and lead to unnecessary locking and blocking

Alternatives:

  • Use Temp Tables
  • Temp Variables
  1. 3.    Try using ‘temp variable’ or ‘temp tables’ to aviod the main table reference more than once

If a particular set of data is fetched from joining more than one tables and this set of data is needed in more than one query than use temp tables or temp variables instead of refering main tables repeatedly.

Temporary tables or varibles keeps the data into a separate area reducing main table scan and avoiding locks.

4.  Cursor Tuning

  1. 1.       Try to use a FORWARD-ONLY or FAST-FORWARD, READ-ONLY cursor:
  • Generally, a FORWARD-ONLY cursor is the most performant, followed by DYNAMIC, KEYSET, and finally STATIC, which is generally the least performant.
  • When working with unidirectional, read-only data, use the FAST_FORWARD option instead of the FORWARD_ONLY option, as it has some internal performance optimizations to speed performance.
                   Example:   DECLARE cursor_name CURSOR FAST_FORWARD

FOR select_statement

  1. 2.    When you are done using a cursor, don’t just CLOSE it, you must also DEALLOCATE it :

Deallocation is required to free up the SQL Server resources used by the cursor

                    Example:  Close cursor_name
                                    Deallocate cursor_name
3.    If you need to perform a JOIN as part of your cursor, keyset and static cursors are generally faster than dynamic cursors.
4.    Avoid using static/insensitive and keyset cursors,Unless you have no other choice:
They cause a temporary table to be created in TEMPDB, which increases overhead.
5.    Try to reduce the number of records to process.

5. Application Level Tuning

1.    If your application needs to insert a large binary value into an image data column, perform this task using a stored procedure, not using an INSERT statement embedded in your application. 
The reason for this is that application must first convert the binary value into a character string (which doubles its size, thus increasing network traffic and taking more time) before it can be sent to the server. Moreover, when the server receives the character string, it then has to convert it back to the binary format (taking even more time). 
Using a stored procedure avoids all this because all the activity occurs on the SQL Server, and little data is transmitted over the network. 
2.    Use OPTION(FAST <number_of_rows>) where perception is more important than actual performance

If you run into situations where perception is more important than raw performance, consider using the FAST query hint.

Example:

Select from usermgtusers OPTION (FAST 5)

This will fetch five numbers of rows as fast as possible while the server is still processing the query.

  1. Use views and stored procedures instead of heavy-duty queries.
    This can reduce network traffic, because your client will send only stored procedure or view name (perhaps with some parameters) to the server, instead of large heavy-duty queries text. This can be used to facilitate permission management also, because you can restrict user access to table columns they should not see.

SQL Server -Window XP useful commands for DBA


run>msinfo32

It gets the system information like OS,version name,components and many more.

run>sqlservermanager11.msc

Opens the SQL server configuration manager if the latest SQL server installed on the machine is SQL 2012

run>sqlservermanager10.msc

open the SQL server configuration manager if the latest SQL server installed on the machine is SQL 2008

run>compmgmt.msc

It opens the computer management window which in turn used for user management,event viewer,Disk management etc.

run>inetmgr

It opens the IIS window which int-run used for managing FTP,Web sites and SMTP

run>cmd>osql -L

It fetches the list of all the SQL server installed on the current network.

run>cmd>netstat -na|find "1433"

Finds the status of the 1433 port

run>c:\windows\system32\drivers\etc\hosts

The host file path.In case client SSMS is not able to connect to the SQL server using the server name but can connect using the IP address then try making an entry(associate IP address with the Server name) into this file.

run>secpol.msc

open the local security setting window which is used to set security options,IP security,Password policy etc.

SQL Server -Find currently Running jobs


If you want to find the currently ruining jobs then following queries will help you.

List All currently running SQL agent jobs:

exec msdb..sp_help_job @execution_status=1

List extra information about you job:

exec msdb..sp_help_job @Job_name=<yourjobname>

possible values of @execution_status

Value Description
0 Returns only those jobs that are not idle or suspended.
1 Executing.
2 Waiting for thread.
3 Between retires.
4 Idle.
5 Suspended.
7 Performing completion actions.

Links:

http://msdn.microsoft.com/en-us/library/ms186722(SQL.105).aspx

SQL Server-Types of Replication


Replication Terminology:

Article:An article can be an entire table, select rows from a table, specified columns from a table or a stored procedure. An article is what is to be replicated.

Publication:Publication is a group of articles.It simplifies the configuration and management.Also assures the consistency of data.

Publisher:A publisher is a server that makes data available for other servers, i.e. it is the server that makes data available for replication.

Subscriber:A subscriber is a server that receives updates to the data. Each subscriber is associated with a publisher.

Distributor:The server that contains the distribution database.

Distribution Agent:The distribution agent is a process that moves transactions and snapshots held in the distribution database to subscribers.

Pull Subscription:With pull subscription the subscriber asks the publisher for periodic updates. With pull subscription each subscriber can specify the best time for the update. Pull subscription is generally considered the better form of replication when a large number of subscribers are involved, or where the load on the LAN needs to be distributed.

Push Subscription:With push subscription the publisher pushes any changes made to the data out to the subscribers. Typically push subscription is used where changes need to be replicated to the subscribers shortly after the change occurs, i.e. where the the replication of changes is required in near real time.

PUSH PULL
Advantages *Less administrator overhead.*Easier troubleshooting. *No permanent connection required.* Reduces Distributor Overhead.
Disadvantages *Stable and permanent    Connection required.

Replication is used for copying and distributing data and Database objects.Also Keeps them synchronized.It also increases the availability of data.SSRM and RMO(Replication Management Object) creates ,manage and monitor replication.Three types of Replication are:

Snapshot Transactional Merge
Similar to full backup and Restore. The replication agent monitors the publisher for changes to the database and transmits those changes to the subscribers. Support simultaneous data modifications in Publisher and the subscriber.
Copies an entire set of data(Publisher) at scheduled time. Uses the Transactional log. Copies the complete data from publisher to subscriber.
Uses when Database is small and in case of static data. Uses when data changes on periodic basis. Both entities can work independently.

Replication Agent: A group of programs that executes replication process.Implemented using SQL agent jobs.

Snapshot(Snapshot.exe) Log Reader(logread.exe) Distribution(distrib.exe) Merge(replmerge.exe) Queue Reader agent
Prepares schema and initial copy of data files. Monitors the database transaction log and copies the transaction that affects the publication to the distribution. Delivers the initial snapshot to the subscriber. Delivers the initial snapshot from distribution to the subscriber and vice-versa The agent reads message stored in queue.
All replication uses it as an entry point Multiple Publication of the same database uses the same log reader agent. Applies the transactions stored in distributed to subscriber. Reconcile conflicts based on rules defined during the configuration. Applies transaction to the publisher.
Used in transaction replication at publisher server. Used in snapshot & transaction replication. Used in snapshot and transaction when configured for queued updating subscription.
Runs in distributor server. Runs on distribution on push. Runs on distribution on push.
Runs on subscription on pull. Runs on subscription on pull.

Replication Maintenance Jobs:

Agent History Clean Up: Distribution Removes replication agent history from the distribution database.
Distribution Clean Up: Distribution Removes replicated transactions from the distribution database.
Expired Subscription Clean Up Detects and removes expired subscriptions from publication databases.
Reinitialize Subscriptions Having Data Validation Failures Detects all subscriptions that have data validation failures and mark them  for reinitialization.T
Replication Agents Check Up Detects replication agents that are not actively logging history. It writes to the Microsoft Windows event log if a job step fails.
Replication monitoring refresher for distribution Refreshes cached queries used by replication Monitor.