SQL Timeout and indices

Question

Changing and finding stuff in a database containing a few dozen tables with around half a million rows in the big ones I'm running into timeouts quite often.

Some of these timeouts I don't understand. For example I got this table:

CREATE TABLE dbo.[VPI_APO]
 (
  [Key] bigint IDENTITY(1,1) NOT NULL CONSTRAINT [PK_VPI_APO] PRIMARY KEY,
  [PZN] nvarchar(7) NOT NULL,
  [Key_INB] nvarchar(5) NOT NULL,
 ) ON [PRIMARY]
GO

ALTER TABLE dbo.[VPI_APO] ADD CONSTRAINT [IX_VPI_APOKey_INB] UNIQUE NONCLUSTERED 
 (
  [PZN],
  [Key_INB]
 ) ON [PRIMARY]
GO

I often get timeouts when I search an item in this table like this (during inserting high volumes of items):

SELECT [Key] FROM dbo.[VPI_APO] WHERE ([PZN] = @Search1) AND ([Key_INB] = @Search2)

These timeouts when searching on the unique constraints happen quite often. I expected unique constraints to have the same benefits as indices, was I mistaken? Do I need an index on these fields, too?
Or will I have to search differently to benefit of the constraint?

I'm using SQL Server 2008 R2.

Answer 1

My first thought is parameter sniffing.

If you're on SQL Server 2008, try "OPTIMIZE FOR UNKNOWN" rather than parameter masking

2nd thought is change the unique constrant to an index and INCLUDE the Key column explicitly. Internally they are the same, but as an index you have some more flexibility (eg filter, include etc)

Answer 2

A unique constraint creates an index. Based on the query and the constraint defined you should be in a covering situation, meaning the index provides everything the query needs without a need to back to the cluster or the heap to retrieve data. I suspect that your index is not sufficiently selective or that your statistics are out of date. First try updating the statistics with sp_updatestats. If that doesn't change the behavior, try using UPDATE STATISTICS VPI_APO WITH FULL SCAN. If neither of those work, you need to examine the selectivity of the index using DBCC SHOW_STATISTICS.