Reducing SQL Server Deadlocks

Deadlocking and SQL best practices:

Deadlocking refers to the condition in which one resource is waiting on the action of a second, while that second action is waiting on the first. This is different from being blocked, or having to wait for a resource. Using the locks above, if a transaction had a shared lock, then you issued a delete on those same records held by the first lock, you would not be deadlocked. Instead, you would be blocked. When the shared lock was released, your delete statement would complete. Blocking implies some performance hit, but the transaction will complete. It simply has to wait for something else to finish first. A deadlock on the other hand, means there is no way to finish. Your transaction is stuck in a loop with some other transaction. At this point, the database system will usually pick one transaction to be killed so the other can complete.

Here are some tips on how to avoid deadlocking on your SQL Server:

  • Esure the database design is properly normalized.
  • Have the application access server objects in the same order each time.
  • During transactions, don’t allow any user input. Collect it before the transaction begins.
  • Avoid cursors.
  • Keep transactions as short as possible. One way to help accomplish this is to reduce the number of round trips between your application and SQL Server by using stored procedures or keeping transactions with a single batch. Another way of reducing the time a transaction takes to complete is to make sure you are not performing the same reads over and over again. If your application does need to read the same data more than once, cache it by storing it in a variable or an array, and then re-reading it from there, not from SQL Server.
  • Reduce lock time. Try to develop your application so that it grabs locks at the latest possible time, and then releases them at the very earliest time.
  • If appropriate, reduce lock escalation by using the ROWLOCK or PAGLOCK.
  • Consider using the NOLOCK hint to prevent locking if the data being locked is not modified often.
  • If appropriate, use as low of an isolation level as possible for the user connection running the transaction.
  • Consider using bound connections
  • Remember to SET NOCOUNT ON at the beginning of your SQL bataches, stored procedures, triggers to avoid network traffic. This will also reduct the chances of error on linked server.
  • Index should be created on highly selective columns, which are used in JOINS, WHERE and ORDER BY clause.
  • Use Column name in ORDER BY clause instead of numbers.
  • Do not use TEXT or NTEXT if possible. In SQL Server 2005 use VARCHAR(MAX) or NVARCHAR(MAX).
  • Do not use SELECT *, use proper column names to decrease network traffic and fewer locks on table.

Use SQL Server Profiler to identify the cause of a deadlock. A deadlock occurs when there is a cyclic dependency between two or more threads, or processes, for some set of resources within SQL Server. Using SQL Server Profiler, you can create a trace that records, replays, and displays deadlock events for analysis.

To trace deadlock events, add the Deadlock graph event class to a trace. This event class populates the TextData data column in the trace with XML data about the process and objects that are involved in the deadlock. SQL Server Profiler can extract the XML document to a deadlock XML (.xdl) file which you can view later in SQL Server Management Studio. You can configure SQL Server Profiler to extract Deadlock graph events to a single file that contains all Deadlock graph events, or to separate files. This extraction can be done in any of the following ways:

  • At trace configuration time, using the Events Extraction Settings tab. Note that this tab does not appear until you select the Deadlock graph event on the Events Selection tab.
  • Using the Extract SQL Server Events option on the File menu.
  • Individual events can also be extracted and saved by right-clicking a specific event and choosing Extract Event Data.

Backup/Restore Optimization Tips

Backup/Restore Optimization Tips:

  1. Try to perform backup to the local hard disk first, and copy backup file(s) to the tape later.

    When you perform backup, some SQL Server commands cannot be made, for example: during backup you cannot run ALTER DATABASE statement with either the ADD FILE or REMOVE FILE options, you cannot shrink database, you cannot run CREATE INDEX statement and so on. So, to decrease the backup operation’s time, you can perform backup to the local hard disk first, and then copy backup file(s) to the tape, because tape device usually much more slow than hard disks. The smaller backup
    operation’s time is, the less impact there will be on the server when the backup occurs.

  2. Perform backup on multiple backup devices.
    Using multiple backup devices forces SQL Server to create a separate backup thread for each backup device, so the backups will be written to all backup devices in parallel.
  3. Perform backup on a physical disk array, so the more disks in array the more quickly the backup will be made.This can improve performance because a separate thread will be created for each backup device on each disk in order to write the backup’s data in parallel.
  4. Perform backups during periods of low database access.
    Because backup is very resource effective, try to schedule it during CPU idle time and slow production periods.
  5. Use full backup to minimize the time to restore databases.
    The full backups take the longest to perform in comparison with differential and incremental backups, but are the fastest to restore.
  6. Use incremental backup to minimize the time to backup databases.
    The incremental backups take the fastest to perform in comparison with full and differential backups, but are the longest to restore.
  7. Use differential backup instead of incremental backup when the users update the same data many times.
    Because a differential backup captures only those data pages that have changed after the last database backup, you can eliminate much of the time the server spends rolling transactions forward when recovering transaction logs from the incremental backups. Using differential backup, in this case, can improve the recovery process in several times.
  8. Try to separate your database to different files and filegroups to backing up only appropriate file/filegroup.
    This can results in smaller backup operation’s time. The smaller backup operation’s time is, the less impact there will be on the server when the backup occurs.
  9. Use Windows NT Performance Monitor or Windows 2000 System Monitor to check a backup impact on the total system performance.
    You can verify the following counters: SQL Server Backup Device: Device Throughput Bytes/sec to determine the throughput of specific backup devices, rather than the entire database backup or restore operation; SQL Server Databases: Backup/Restore Throughput/sec to monitor the throughput of the entire database backup or restore operation; PhysicalDisk: % Disk Time to monitors the percentage of time that the disk is busy with read/write activity; Physical Disk Object: Avg. Disk Queue Length to determine how many system requests on average are waiting for disk access.
  10. To decrease the backup operation’s time consider backing up more often.
    The more often you will make backup, the smaller they will be, and the less impact there will be on the server when the backup occurs. So, to avoid locking users for a long time during everyday work, you can perform backup more often. Note. The more often you will make backup, the less data you will lost if the database becomes corrupt.
  11. Place a tape drive on another SCSI bus as disks or a CD-ROM drive.
    The tape drives perform better if they have a dedicated SCSI bus for each tape drive used. Using separate SCSI bus for a tape drive can results in maximum backup performance and prevents conflicts with other drive array access. Microsoft recommends using dedicated SCSI bus for the tape drives whose native transfer rate exceeds 50 percent of the SCSI bus speed.
  12. Use SQL Server 2000 snapshot backups for the very large databases.
    The SQL Server 2000 snapshot backup and restore technologies work in conjunction with third party hardware and software vendors. The main advantages of snapshot backups and restores are that they can be done in a very short time, typically measured in seconds, not hours, and reduce the backup/restore impact on the overall server performance. The snapshot backups accomplished by splitting a mirrored set of disks or creating a copy of a disk block when it is written and required the special hardware and software.

Analyze and Fix Index Fragmentation in SQL Server 2008

Analyze and Fix Index Fragmentation in SQL Server 2008

It is very common that over time SQL Server tables and indexes tend to become fragmented. The fragmentation generally happens when data within the underlying tables on which an index exists is modified. The data modification basically can be an insert, update or a delete operation. The indexes over time become ineffective because they get fragmented. In this article you will see an example of how an index gets fragmented and the steps which database administrator needs to take to fix index fragmentations.

Example to Analyze and Fix Index Fragmentation in SQL Server 2008

Follow the below mentioned steps to see how an index fragmentation occurs on a table which has indexes defined on it. And finally you will see the steps which you need to take to fix index fragmentation issues.

Create AnalyzeFragmentation Database

First let us create a new database named AnalyzeFragmentation for this example. Database can be created by executing the below mentioned TSQL Query.

Use master
IF EXISTS (SELECT name FROM sys.databases WHERE name = N'AnalyzeFragmentation')
DROP DATABASE [AnalyzeFragmentation]
CREATE DATABASE AnalyzeFragmentation

Create FindAndFixFragmentation Table in AnalyzeFragmentation Database

The next step will be to create a new table named FindAndFixFragmentation within the AnalyzeFragmentation database.

USE AnalyzeFragmentation
IF OBJECT_ID (N'dbo.FindAndFixFragmentation', N'U') IS NOT NULL
DROP TABLE dbo.FindAndFixFragmentation;

— Create FindAndFixFragmentation Table–
CREATE TABLE [dbo].[FindAndFixFragmentation]

[AddressID] [int] NOT NULL,
[AddressLine1] [nvarchar](60) NOT NULL,
[City] [nvarchar](30) NOT NULL,
[PostalCode] [nvarchar](15) NOT NULL,
[ModifiedDate] [datetime] NOT NULL,



Populate the FindAndFixFragmentation Table using the below TSQL code

The next step will be to populate the FindAndFixFragmentation table which you have created earlier by executing the below mentioned TSQL code. For this example we will be using the data which is available in Person.Address table available in AdventureWorks database.

USE AnalyzeFragmentation

— Populate FindAndFixFragmentation table with data from AdventureWorks.Person.Address —

INSERT INTO FindAndFixFragmentation
FROM AdventureWorks.Person.Address

Create a Clustered Index on FindAndFixFragmentation Table using the below TSQL code

The next step will be to create a clustered index named CL_FindAndFixFragmentation_Index on FindAndFixFragmentation table using the below mentioned TSQL code.

-- Drop the index if it is already existing--
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[FindAndFixFragmentation]') AND name = N'CL_FindAndFixFragmentation_Index')

DROP INDEX [CL_FindAndFixFragmentation_Index] ON [dbo].[FindAndFixFragmentation]
— Create Clustered Index on FindAndFixFragmentation(RowGUID) —
CREATE CLUSTERED INDEX [CL_FindAndFixFragmentation_Index] ON [dbo].[FindAndFixFragmentation]

You can see that we are creating a clustered index on FindAndFixFragmentation table with a Fill Factor 90. The fill factor option is basically provided for fine tuning index data storage and to improve performance. Whenever an index is created or it is rebuilt, the fill factor value basically determines the percentage of space on each leaf level page that needs to be filled with data. Based on the fill factor value a percentage of free space is allocated on every single page. By default the fill factor value is 0 or 100 which means there will be no free space allocated on each leaf level page. The value for fill factor is defined in percentages and this can be any value in between 1 to 100. In this example the fill factor value provide is 90 which mean on every single page there will be a 10 percentage of free space left to accommodate future growth.

Query to Find Existing Fragmentation on FindAndFixFragmentation Table

Next step will be to execute the below mentioned TSQL query to know the existing fragmentation on FindAndFixFragmentation table. The important values which need to be noted by the database administrators are AvgPageFragmentation and PageCounts. The value for AvgPageFragmentation is 0.341296928327645, which means there is a very little fragmentation existing on the table at this point of time. However the value for PageCounts is 293, which mean the data is stored in that many data pages on SQL Server. This query will be executing many a times in this article.

-- Find index fragmentation --
SI.NAME AS IndexName,
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, N'LIMITED') DPS
INNER JOIN sysindexes SI

Perform Update Operation on FindAndFixFragmentation Table

Next step will be to perform updates on FindAndFixFragmentation table by executing the below mentioned TSQL code. This query will modify all the data for RowGUID column on which we have created clustered index with fill factor as 90.

— Update all the rows within to FindAndFixFragmentation table create index fragmentation —

USE AnalyzeFragmentation
UPDATE FindAndFixFragmentation

Execute the query to find existing fragmentation on FindAndFixFragmentation table as shown in the below snippet.

Now you can see that the value for AvgPageFragmentation has changed from 0.341296928327645 to 99.0049751243781, which means index is completely fragmentation. At the same time the value for PageCounts has changed from 293 to 603, which mean more number of data pages are required to store the content. Now the question which comes to your mind is how this can be fixed.

There are two methods to fix index fragmentation issues in SQL Server 2005 and higher versions. The two methods are Reorganize or Rebuild Index. The Reorganize Index is an online operation, however Rebuild Index is not an online operation until you have specified the option ONLINE=ON while performing the Rebuild. Next step will be to perform first REORGANIZE Index option and then finally perform we will perform the REBUILD and see which options is the best.

Perform Reorgainize Index Operation on Clustered Index of FindAndFixFragmentation Table

First let us perform REORGANIZE Index operation on the clustered index, and then execute the query as shown in the snippet to find the fragmentation on FindAndFixFragmentation table.

-- Reorganize [CL_FindAndFixFragmentation_Index] index on FindAndFixFragmentation --

ALTER INDEX [CL_FindAndFixFragmentation_Index] ON FindAndFixFragmentation



Once we have performed the REORGANIZE Index operation you can see that the value for AvgPageFragmentation has changed from 99.0049751243781 to 5.70469798657718, which means index fragmentation is much better that how it was earlier. And at the same time the value for PageCounts has also come down from 603 to 298, this is considerable improvement.

Perform Rebuild Index Operation on Clustered Index of FindAndFixFragmentation Table

Now let us perform REBUILD Index operation on the clustered index, when you are using the Rebuild index operation it basically drops and recreates the index. The important thing what we need to see is does this results in reducing the index fragmentation further down from 5.70469798657718. Once you have performed the Rebuild operation execute the query as shown in the snippet to check the fragmentation on FindAndFixFragmentation table.

-- Rebuild [CL_FindAndFixFragmentation_Index] index on FindAndFixFragmentation --

ALTER INDEX [CL_FindAndFixFragmentation_Index] ON FindAndFixFragmentation

You can see that the value for AvgPageFragmentation is back to 0.341296928327645, which means the fragmentation is same as it was when we began this exercise. And at the same time the value for PageCounts is back to 293. This proves that using REBUILD Index operation is better than REORGANIZE Index operation.

Reorganize Index

Reorganize Index uses minimal system resources and it is performed online. The biggest advantage is it does not require locks for long time therefore it does not block updates or other user queries. If the index fragmentation ranges in between 5% to 30% then it is better to perform Reorganize Index.

Rebuild Index

Rebuild Index basically drops and recreates the index; this is by far the best approach. If the index fragmentation is greater than 30% then the best strategy will be to use Rebuild Index instead of Reorganize Index.


Database Administrators should always make sure that fragmentation of indexes is handled on time. If the indexes are fragmented then the query response will not only be very slow; the data storage will also require more disk space. In this article you have seen an example where the clustered index gets fragmented over time and the steps which you need to perform to resolve index fragmentation issues.

How to Cluster SQL Server 2005

How to Cluster SQL Server 2005

Believe it or not, the procedure to install a SQL Server 2005 instance onto a cluster is one of the easiest parts of getting your SQL Server 2005 cluster up and running. The SQL Server 2005 setup program is used for the install and does the hard work for you. All you have to do is make a few (but critically important) decisions, and then sit back and watch the installation take place. In fact, the setup program even goes to the trouble to verify that your nodes are all properly configured, and if not, will suggest how to fix most problems before the installation begins.

When the installation process does begin, the setup program recognizes all the nodes, and once you give it the go ahead to install on each one, it does, all automatically. SQL Server 2005 binaries are installed on the local drive of each node, and the system databases are stored on the shared array you designate.

In the next section are the step-by-steps instructions for installing a SQL Server 2005 instance in a cluster. The assumption for this example is that you will be installing this instance in a 2-node active/passive cluster. Even if you will be installing a 2-node active/active or a multi-node cluster, the steps in this section are virtually the same. The only real difference is that you will have to run SQL Server 2005 setup for every instance you want to install on the cluster, and you will have to specify a different logical drive on the shared array.

Clustering SQL Server

To begin installing your SQL Server 2005 cluster, you will need the installation CD or DVD. You can either install it directly from the media, or copy the install files from the media to the current active node of the cluster, and run the setup program from there.

To begin the installation, run Setup.exe. After an introductory screen, you will get the first install dialog box as shown in the figure below.

The Installing Prerequisites dialog box lists the prerequisites that need to be installed before installation of SQL Server 2005 can begin. The number of components may vary from the above figure, depending on what you have already installed on your nodes. What is interesting to note here is that these prerequisite components will only be installed immediately on the active node. They will be installed on the passive node later during the installation process. This is done automatically and you don’t have to worry about it.

Click Install to install these components. When completed, you will get a dialog box telling you that they were installed successfully, and then you can the click Next to proceed. On occasion, I have seen these components fail to install correctly. If this happens, you will have to troubleshoot the installation. Generally speaking, try rebooting both nodes of the cluster and try installing them again. This often fixes whatever caused the first setup try to fail.

Once the prerequisite components have been successfully installed, the SQL Server Installation Wizard launches

SQL Tuning or SQL Optimization

SQL Tuning or SQL Optimization

Sql Statements are used to retrieve data from the database. We can get same results by writing different sql queries. But use of the best query is important when performance is considered. So you need to sql query tuning based on the requirement. Here is the list of queries which we use reqularly and how these sql queries can be optimized for better performance.

SQL Tuning/SQL Optimization Techniques:

1) The sql query becomes faster if you use the actual columns names in SELECT statement instead of than ‘*’.

For Example: Write the query as

SELECT id, first_name, last_name, age, subject FROM student_details;

Instead of:

SELECT * FROM student_details;

2) HAVING clause is used to filter the rows after all the rows are selected. It is just like a filter. Do not use HAVING clause for any other purposes.
For Example: Write the query as

SELECT subject, count(subject)
FROM student_details
WHERE subject != 'Science'
AND subject != 'Maths'
GROUP BY subject;

Instead of:

SELECT subject, count(subject)
FROM student_details
GROUP BY subject
HAVING subject!= 'Vancouver' AND subject!= 'Toronto';

3) Sometimes you may have more than one subqueries in your main query. Try to minimize the number of subquery block in your query.
For Example: Write the query as

FROM employee
WHERE (salary, age ) = (SELECT MAX (salary), MAX (age)
FROM employee_details)
AND dept = 'Electronics';

Instead of:

FROM employee
WHERE salary = (SELECT MAX(salary) FROM employee_details)
AND age = (SELECT MAX(age) FROM employee_details)
AND emp_dept = 'Electronics';

4) Use operator EXISTS, IN and table joins appropriately in your query.
a) Usually IN has the slowest performance.
b) IN is efficient when most of the filter criteria is in the sub-query.
c) EXISTS is efficient when most of the filter criteria is in the main query.

For Example: Write the query as

Select * from product p
where EXISTS (select * from order_items o
where o.product_id = p.product_id)

Instead of:

Select * from product p
where product_id IN
(select product_id from order_items

5) Use EXISTS instead of DISTINCT when using joins which involves tables having one-to-many relationship.
For Example: Write the query as

SELECT d.dept_id, d.dept
FROM dept d
WHERE EXISTS ( SELECT 'X' FROM employee e WHERE e.dept = d.dept);

Instead of:

SELECT DISTINCT d.dept_id, d.dept
FROM dept d,employee e
WHERE e.dept = e.dept;

6) Try to use UNION ALL in place of UNION.
For Example: Write the query as

SELECT id, first_name
FROM student_details_class10
SELECT id, first_name
FROM sports_team;

Instead of:

SELECT id, first_name, subject
FROM student_details_class10
SELECT id, first_name
FROM sports_team;

7) Be careful while using conditions in WHERE clause.
For Example: Write the query as

SELECT id, first_name, age FROM student_details WHERE age > 10;

Instead of:

SELECT id, first_name, age FROM student_details WHERE age != 10;

Write the query as

SELECT id, first_name, age
FROM student_details
WHERE first_name LIKE 'Chan%';

Instead of:

SELECT id, first_name, age
FROM student_details
WHERE SUBSTR(first_name,1,3) = 'Cha';

Write the query as

SELECT id, first_name, age
FROM student_details
WHERE first_name LIKE NVL ( :name, '%');

Instead of:

SELECT id, first_name, age
FROM student_details
WHERE first_name = NVL ( :name, first_name);

Write the query as

SELECT product_id, product_name
FROM product
WHERE unit_price BETWEEN MAX(unit_price) and MIN(unit_price)

Instead of:

SELECT product_id, product_name
FROM product
WHERE unit_price >= MAX(unit_price)
and unit_price <= MIN(unit_price)

Write the query as

SELECT id, name, salary
FROM employee
WHERE dept = 'Electronics'
AND location = 'Bangalore';

Instead of:

SELECT id, name, salary
FROM employee
WHERE dept || location= 'ElectronicsBangalore';

Use non-column expression on one side of the query because it will be processed earlier.

Write the query as

SELECT id, name, salary
FROM employee
WHERE salary < 25000;

Instead of:

SELECT id, name, salary
FROM employee
WHERE salary + 10000 < 35000;

Write the query as

SELECT id, first_name, age
FROM student_details
WHERE age > 10;

Instead of:

SELECT id, first_name, age
FROM student_details
WHERE age NOT = 10;

8) Use DECODE to avoid the scanning of same rows or joining the same table repetitively. DECODE can also be made used in place of GROUP BY or ORDER BY clause.
For Example: Write the query as

SELECT id FROM employee
WHERE name LIKE 'Ramesh%'
and location = 'Bangalore';

Instead of:

SELECT DECODE(location,'Bangalore',id,NULL) id FROM employee
WHERE name LIKE 'Ramesh%';

9) To store large binary objects, first place them in the file system and add the file path in the database.

10) To write queries which provide efficient performance follow the general SQL standard rules.

a) Use single case for all SQL verbs
b) Begin all SQL verbs on a new line
c) Separate all words with a single space
d) Right or left aligning verbs within the initial SQL verb

SQL Subquery

SQL Subquery

Subquery or Inner query or Nested query is a query in a query. A subquery is usually added in the WHERE Clause of the sql statement. Most of the time, a subquery is used when you know how to search for a value using a SELECT statement, but do not know the exact value.

Subqueries are an alternate way of returning data from multiple tables.

Subqueries can be used with the following sql statements along with the comparision operators like =, <, >, >=, <= etc.


For Example:

1) Usually, a subquery should return only one record, but sometimes it can also return multiple records when used with operators like IN, NOT IN in the where clause. The query would be like,

SELECT first_name, last_name, subject
FROM student_details
WHERE games NOT IN (‘Cricket’, ‘Football’);

The output would be similar to:

first_name     last_name     games
————-     ————-     ———-
Shekar     Gowda     Badminton
Priya     Chandra     Chess

2) Lets consider the student_details table which we have used earlier. If you know the name of the students who are studying science subject, you can get their id’s by using this query below,

SELECT id, first_name
FROM student_details
WHERE first_name IN ('Rahul', 'Stephen');

but, if you do not know their names, then to get their id’s you need to write the query in this manner,

SELECT id, first_name
FROM student_details
WHERE first_name IN (SELECT first_name
FROM student_details
WHERE subject= 'Science');


id     first_name
--------     -------------
100     Rahul
102     Stephen

In the above sql statement, first the inner query is processed first and then the outer query is processed.

3) Subquery can be used with INSERT statement to add rows of data from one or more tables to another table. Lets try to group all the students who study Maths in a table ‘maths_group’.

INSERT INTO maths_group(id, name)
SELECT id, first_name || ' ' || last_name
FROM student_details WHERE subject= 'Maths'

4) A subquery can be used in the SELECT statement as follows. Lets use the product and order_items table defined in the sql_joins section.

select p.product_name, p.supplier_name, (select order_id from order_items where product_id = 101) as order_id from product p where p.product_id = 101

product_name     supplier_name     order_id
------------------     ------------------     ----------
Television     Onida     5103
Correlated Subquery

A query is called correlated subquery when both the inner query and the outer query are interdependent. For every row processed by the inner query, the outer query is processed as well. The inner query depends on the outer query before it can be processed.

SELECT p.product_name FROM product p
WHERE p.product_id = (SELECT o.product_id FROM order_items o
WHERE o.product_id = p.product_id);

1) You can nest as many queries you want but it is recommended not to nest more than 16 subqueries in oracle.
2) If a subquery is not dependent on the outer query it is called a non-correlated subquery.

Using Stored Procedures

Most of us, the database programmers, have used Stored Procedures. May be not all of us knows about why we use them. This article is for those who have used/never used stored procedures, and are yet to understand why everyone suggests using them in your Database.

Stored Procedures – What are they?

Stored procedure is a set of pre-defined Transact-SQL statements, used to perform a specific task. There can be multiple statements in a stored procedure, and all the multiple statements are clubbed in to one database object.

How to create a stored procedure?

Creating a stored procedure is as easy as running the “Create Procedure” statement followed by the SQL script. You can run your Create Procedure statement from the SQL Query Analyzer, or can use the New Procedure menu item in the Enterprise Manager.

The simplest skeleton of a stored procedure.

CREATE PROC procedure_name
[ { @parameter data_type }
AS sql_statement

Check the basic building blocks of a stored procedure.

A stored procedure includes:

2.      The procedure name;
3.      The parameter list
4.      And the SQL statements.

Even though there are numerous other options available while we define a stored procedure, I kept it simple, just to give you a basic idea about creating stored procedures.


Almost every database Guru that you will meet, will suggest using stored procedures. For you, it will seem as if most of them blindly believes in stored procedures. But there are reasons for this. This is what I am trying to explore in this article.

1. Performance

All the SQL statements, that you send to your database server passes through a series of actions, called execution. These are the steps that your SQL statement passes through before the data is returned to the client.

User sends request to execute the Stored Procedure. SQL Server checks for syntax errors. Identifies and checks the aliases in the FROM clause. Creates a query plan. Compiles the query and. Executes the query plan and return the requested data.

See, lots of things are happening inside that we didn’t knew about. Now, the crucial question. Does a stored procedure bypass all these?

In a way, yes. The previous versions of SQL Server stored the compiled execution plan in system tables, making them partially pre-compiled. This improved performance, because the Server did not have to compile the stored procedure each and every time it is called.

In later versions of SQL Server, there were a large number of changes in statement processing. Now, the stored procedure is stored in a procedure cache when it is called, making subsequent calls faster.

2. Security

Stored procedures provide significant benefits when it comes to security. By using a stored procedure, you can grant permissions to certain users to access data, reducing the immense coding that you need to do in your client applications. This is one of the best ways to control access to your data.

3. Modifications/Maintenance

If you use stored procedures for database access, any change in the database can be reflected on to the client application without much effort. This is because you know exactly where the data is accessed from, and you also know exactly where you need to alter. This means no scuba diving in to thousands of lines of source code to identify areas where you need to alter and no headache of re-deploying the client application.

4. Minimal processing at the client.

When creating a client/server application, normally it was the client who took care of the integrity of data that went in to the database. Managing Primary Keys, Foreign keys, cascaded deletion everything was done by the client, and the database server just had to store data given by the client.

Well friends, things have changed. Stored procedures help you write batch of SQL statements, which helps you manage the transactions, constraints etc. A little data aware code has to be written in to the client application, making it a thin-client application. These applications will be concerned more about displaying data in the way the user needs them and they know little about the database.

Take another scenario. You have a database with millions of rows and hundreds of tables. You need to do some calculations before updating each and every record. If you are fetching the complete data to the client, and is asking the client machine to process the data completely, then think about the overhead it creates. But when the client can execute a store procedure, where you have done the calculations prior to updating the records, you have a client, that doesn’t need to know about the calculations. This also reduces the amount of computing happening in the client, and the server takes care of tedious calculations.

5. Network traffic

Client applications always have to request/send data from the database server. These data are sent as packets, and travel through the network to the server.

To explain how stored procedures can help reduce network traffic, let us see another scenario, where a request for data is send from the client. The request is sent as an SQL statement, and here it is.

SELECT dbo.Tbl_Tablename.fieldID,
dbo.TBl_otherTableName on dbo.Tbl_Tablename.fieldID=dbo.TBl_otherTableName.ID
DateDiff ( wk, dbo.Tbl_Tablename.TransactionDate, getdate()) <= 1
and dbo.Tbl_Tablename.Approved = 0

518 Characters travel through the network, and when there are 20 client applications using this stored procedure 20 times a day, the number of characters passing through the network for just this request will be 2,07,200!

You see the difference now. If it was a stored procedure, lets call it SP_fetchSomething, there are only 6800 characters in the network for the request. A saving of 2,004,00!

As you have seen the five major points that I use to explain why I used a stored procedure, I hope you will also elect to intelligently use this awesome technology in your next database design.