Some SQL server performance tips

I have been reading SQL Server Secret Diary by Vijayan and Kirsh.

Here are a few snipets of performance wisdom from them.

It says where multiple variables are set for Microsoft SQL server the a SELECT statement is faster than several SET statements e.g
SET @employeeid = @employeeid+1, @a=2
Is faster than
SET @employeeid = @employeeid+1,
SET @a=2

Here is another tip when if you want to count the rows in a table quickly. Rather than just
select count(*) from dbo.tablename
Which would do a complete table or cluster index scan. Try this to extract the data from sysindexes
SELECT rows FROM sysindexes
WHERE Id = OBJECT_ID('dbo.tablename') AND indid < 2

To help speed up the performance of a stored procedure remember to add.
SET NOCOUNT ON
This avoids sending row count information for every statement

Advertisements

One thought on “Some SQL server performance tips

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s