Performance Tips - SQL

We had an application which was not performing fast enough for our satisfaction. The application updates various tables with millions of records. We tweaked few things in our SQL and suddenly the speed of application improved to our target level. All the changes we made were very simple and well known, but as it happens often enough that we overlook simple things, so here are the changes we made:

 

 

Where clause:

 

We had the business rule which makes us write the query like this

 

 SELECT RecipientName FROM recipients

                   WHERE email = @email or EmployeeSSN = @EmployeeSSN

 

The query plan showed us that the above 'where' clause is executed by SQL in parallel for email and EmployeeSSN, SQL tries to help us as much as it can. However, we realized we search 99.9% times only on @email and @EmployeeSSN will be null for most of the times, so there is no need to check EmployeeSSN every time. We changed the code as follows:

 

IF @Email is null

          SELECT RecipientName FROM recipients

                   WHERE EmployeeSSN = @EmployeeSSN

ELSE

          SELECT RecipientName FROM recipients

                   WHERE Email = @Email

 

 

Data Type conversion

 

Data type for one of the tables column(emailAddress) was 'varchar', but data type of a variable in stored procedure was defined as 'nvarchar', This variable is used in the where clause, as follows:

declare

@email nvarchar (100)
select * from recipient where emailAddress = @email

The above statement made SQL do the data conversion for 'where' clause. This conversion was a drag on the SP execution speed. This again we come to know when we looked at the Query plan, otherwise SQL was keeping quiet about this extra work. I wish SQL should start giving us warning for this kind of over sights.

 

Fully Qualified Names

 

In our stored procedure we made all the names of object fully qualified. Complete name of an object consists of four identifiers: server, database, owner, and object name. An object name that specifies all four parts is known as a fully qualified name. Using fully qualified names eliminates any confusion about which stored procedure you want to run and can boost performance because SQL Server has a better chance to reuse the stored procedures execution plans if they were executed using fully qualified names.

SELECT Alias FROM Recipient

Changed to

SELECT Alias FROM DBName.dbo.Recipient

 

Prefer sp_executesql stored procedure instead of the EXECUTE statement.

When you use the sp_executesql stored procedure to executes a Transact-SQL statements that will be reused many times, the SQL Server query optimizer will reuse the execution plan, hence boosting the performance.

 

Keep transactions as short as possible.

Some time we had 'begin transaction' much before we actually needed, and 'commit transaction' much latter then actually we needed. We moved begin and commit to make the transactions as short as possible, this not only helped the overall speed but helped in preventing the deadlocks too. Classical mistake we seen again and again:

 

Begin Tran

Check some condition

Delete

Updates

Selects

          End Tran

 

Most of the time ‘End Tran’ can safely move above select, and if you are returning huge data rows back to middle tier then this will surely improve your application performance.

 

In our case we changed the SP as follows:

Check some condition

Begin Tran

Delete

Updates

End Tran

Selects

           

  

Changed temp table to temp variables

We changed temp tables to temp variables.

Using temporary tables inside stored procedure reduces the chance to reuse the execution plan.

select top 1000 from LimitedProgramTBNEventID

          into #Events from LimitedProgramTBNEvent (NOLOCK)

                             where eventStatus = 7

we changed it as follows:

DECLARE @Events table

(

    LimitedProgramTBNEventID int NOT NULL

)

insert into @Events select top 1000 LimitedProgramTBNEventID

                             from LimitedProgramTBNEvent (NOLOCK)

                                      where eventStatus = 7

 

GetDate ()

In couple of stored procedures, in couple of places we had GetDate() funtion, we replaced this function with a date variable and initialized this variable only once, so we don’t need to call getdate again and again.

NOLOCK

Added NOLOCK in many select statements. 'No Lock' not only ignore Exclusive locks on rows, but it does not issue a Shared Lock on the records it reads. Therefore, it will not delay or block a transaction trying to write. There are caveats to this approach, please read on:

https://www.databasejournal.com/features/mssql/article.php/3553281

https://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_con_7a_1hf7.asp

Indexes

SQL Server 2005 has introduced DMV (Dynamic management views), Dynamic management views and functions return server state information that can be used to monitor the health of a server instance, diagnose problems, and tune performance. Please read about them in books on-line. It tells you lots of good thing about your system, Most utilixed Index, Cost of index benefit, hot spot index contentions etc. Everyone knows how important indexing is, and we over do it some time. Using the following query, you can find out which indexes are used, and how much work sql does to maintain each indexes

 

 

 --- sys.dm_db_index_usage_stats

declare @dbid int

select @dbid = db_id()

 

select objectname=object_name(s.object_id), indexname=i.name, i.index_id

,reads=user_seeks + user_scans + user_lookups

,writes = user_updates

from sys.dm_db_index_usage_stats s,

sys.indexes i

where objectproperty(s.object_id,'IsUserTable') = 1

and s.object_id = i.object_id

and i.index_id = s.index_id

and s.database_id = @dbid

order by reads desc

go

 

Table Name	Index Name	Index_id	Reads	Writes
Recipient	IX_Recipient	4	0	3048893
Process	IX_Process	22	0	11709

The above Query tells us we are doing too much work maintaining these two indexes and system never utilities them. Helps us getting rid of lot of redundant work for SQL.

If the user_lookup and user_seek is too high on a clustered index and user_seek high on other non-clustered index then you will be better of turning the non-clustered index to clustered.

declare @dbid int

select @dbid = db_id()

select objectname=object_name(s.object_id), s.object_id, indexname=i.name, i.index_id

      , user_seeks, user_scans, user_lookups, user_updates

from sys.dm_db_index_usage_stats s,

      sys.indexes i

where database_id = @dbid and objectproperty(s.object_id,'IsUserTable') = 1

and i.object_id = s.object_id

and i.index_id = s.index_id

order by user_updates desc, user_seeks asc

Table Name	Index Name	User_seek	User_lookup	User_update
LimitedProgramRecipient	LimitedProgramRecipient_PK	164654	3851134	3850446
LimitedProgramRecipient	LimitedProgramRecipient_UC1	3851582	0	3850446

In the above example LimitedProgramRecipient_UC1 columns, should be included in the clustered Index.

Here are some more links for DMV.

Database Engine Tuning Advisor

Of course this going without saying that you should always run the Database Engine Tuning Advisor on your database. It does tells us about some of the missing statistics.

 

I am sure this is not an exhaustive list for performance increase tips, Please feel free to add any other performance tips …

Comments

• Anonymous
  December 18, 2006
  Hi Rafat!   great stuff; it would be really cool if you could explain how and how much your performance improved by implementing these improvements, i.e. "duration/CPU/reads, on average, dropped from x seconds to y seconds by changing the WHERE clause" keep it coming! --oj.