Primary Keys

We check the existence of indexes & primary keys (PK) on all tables in the schema, as one step within our health check.

Without a PK, the integrity of the data remains suspect – duplicates can & will exist in the table. Also, no Foreign Keys (FK) can exist without the necessary PK. Without this, orphan data can appear. These are fundamental data quality reasons for implementing them.

However, performance is also a huge reason to implement them.

I still find developers creating tables with no indexes. ‘They are too small’ to need indexes. Yet, it is the performance that is the first issue that we come across.

I found one table where the developer would SELECT @ID = MAX(id) FROM table and then insert a new row as @ID + 1. Without PKs or indexes, the SELECT was scanning through 250,000 records all the way to the last record (with the highest value) before inserting the record. This was being called every 10 seconds and took about the same time to complete. The server was straining under the load.

When the PK was added to this ID field, the query fell to milliseconds in duration and the server went from permanently busy down to always idle.

Presenting to the SQL Server Ireland User Group

Last Tuesday, for the second time, I presented to the SQL Server Ireland User Group. This time the topic was on Indexing in SQL Server 2000 & 2005.

It is always a challenge to present to one’s peers because there is always the risk that they might identify a glaring gap in one’s logic.

Having discussed the members’ survey, I am comfortable that we have presentations suited to the group’s interest for the rest of the year. This is a huge morale boost and takes a lot of pressure off the committee.

Microsoft continue to generously provide facilities, goodies and refreshments. This is appreciated by all.

Parameter Sniffing

My weekend was ruined with this one. Well, that is not fully true. We are still looking at other underlying performance reasons for a system slowdown. But this slowdown manifested itself in a single stored procedure called many hundreds of times by each user.

Consider a number of cases below. Case A is a simple select statement. Case B is the same code wrapped in a stored procedure

Case A
Declare @x int
Set @x = 1
Select * from something where id = @x

Case B
Create Procedure usp_Niall
@x int
As
Select * from something where id = @x
GO
Exec usp_Niall 1

So why does Case B perform 500 times slower than Case A – on 3 different servers (Production, Reporting & Backup servers)?

After some research, we discovered parameter sniffing does not always work. This is where SQL Server should use the parameter to deduct the optimal path.

When we then modified the stored procedure to be

Case C
Create Procedure usp_Niall2
@x int
As
Declare @y int
Set @y = @x
Select * from something where id = @y
GO
Exec usp_Niall2 1

This performed exactly the same as the Case A above – on all 3 servers.

You have been warned.

If you want to know more, then visit the following sites, where the issue is more fully discussed.
http://groups.google.com/group/microsoft.public.sqlserver.programming/msg/c992528dc5c6d8e4?hl=en&lr=&ie=UTF-8&oe=UTF-8
http://www.thescripts.com/forum/thread81556.html

%d bloggers like this: