[dba-SQLServer] Performance of a SP

Dear Group,

Last week the company that supplies the SQL Server-based security
access system upgraded its software.  I have an ASP.NET application
that queries that SQL Server to retrieve stats for yet another ASP.NET
application, an employee In/Out Board.  So there are three apps either
feeding the SQL Server with new data (the security access system) or
querying it (the In/Out Board app and my monitoring app).

Our net admin is an uber geek of the first water and he decided to
make the SQL Server into a virtual machine on some big honking piece
of hardware he recently installed.  (Gawd!  He was messing with 130
one Terabyte drives a couple of weeks ago while putting three new
servers together.)

Anyway, the ASP.NET monitoring app is a kind of heads-up display of:

1) A CSS-based bar graph showing the number of people that have come
in or gone out of the building over the past three hours.  Each pair
of bars represents a 15 minute block of time.
2) A list of the users currently running the In/Out Board application.
3) A list of the special security access entries/exits by people like
cleaning staff, HVAC repairmen, etc., in the past 24 hours.
4) A list showing he names of employees that have come in and out in
the past 15 minutes.
5) A list of the errors logged by the In/Out Board app in the past 24 hours.

My problem is with the 6th feature:

6) A list that pops up showing the names of the people summarized in
one of the bars of the bar graph.  I click on one of the bars in my
3-hour bar graph to see who went IN or OUT during the selected
15-minute period.

Up until the upgrade/virtualization the list would pop up
quickly...within a second.  The list only stays on-screen until I
click another bar or until the Monitor app refreshes the stats every
60 seconds.

But now every time I click a bar, I wait about 30 seconds seeing
nothing happening and then a dialog box appears with the following
message:

"Timeout expired. The timeout period elapsed prior to completion of
the operation or the server is not responding."

The message displays in a try-catch block in the VB 2005 web service
code that retrieves the data from the SQL Server.

I've gone into SSMS and executed the stored procedure, supplied the
three parameters, and watched the query take 40 seconds to return the
correct result.  The stored procdedure contains an IF test and it
branches to one of two SELECT queries.  If I copy and paste one of the
SELECT queries from the IF test and paste it into a new query, DECLARE
and SET the three parameters, the query still runs for about 40
seconds.

Here's one of the two queries:

DECLARE
	@TimeSlotStart int,
	@TimeSlotEnd int,
	@CurrDate datetime;

-- The @TimeSlot variables indicate the window of time to examine for employees
-- coming into or going out ofthe building; in this case from 120 minutes ago to
-- 135 minutes ago.
SET @TimeSlotStart = 120;
SET @TimeSlotEnd = 135;
SET @CurrDate = CONVERT(datetime, '12/10/2008 9:07 AM');

SELECT
	p.FirstName + ' ' + p.LastName AS Name,
	CONVERT(char, e.[TimeStamp], 8) AS [Time],
	'In' AS IO,
	d.Name AS Door,
	CONVERT(char, DATEADD(n, -(@TimeSlotEnd), @CurrDate), 8) AS [Start]

-- AccessEvent has about 1.5 million records in it.  It's the complete
history of
-- every In/Out event at any door in the building in the past 6 years or so.
FROM AccessEvent AS e
INNER JOIN Personnel AS p
	ON e.PersonId = p.PersonId
INNER JOIN Door AS d
	ON e.DoorId = d.DoorId
WHERE
(
	-- Here's where we define the range of minutes to query between
	-- the TimeSlotStart and TimeSlotEnd.
	(e.[TimeStamp] > DATEADD(n, -(@TimeSlotEnd), @CurrDate)) AND
	(e.[TimeStamp] <= DATEADD(n, -(@TimeSlotStart), @CurrDate)) AND

	-- This is a list of the three "areas" that an employee can enter
	-- the building.
	(e.AreaId IN (1111497563, 1111497564, 1111497571)) AND

	-- And, finally, a series of conditions that narrow down the list of
	-- possible employees based on flags set in the Personnel table.
	(e.CardNumber > 0) AND
	(p.State > 0) AND
	(
		LTRIM(RTRIM(p.Info1)) IS NULL OR
		UPPER(LTRIM(RTRIM(p.Info1))) <> 'NOSHOW'
	) AND
	(p.TimeEntered > CONVERT(datetime, '1/1/1989'))
)
ORDER BY e.[TimeStamp];

The Personnel table only has about 340 rows.  The Doors table, 11
rows.  The result set for this query has never been more than 24 rows;
that is, 24 people coming or going within a 15 minute window of time.

Again, this is one of a pair of SELECT queries that an IF test in the
sproc branches to.  The other one is identical except that it shows
the employees that have gone OUT of the building in the last 15
minutes.

The performance of this sproc was never an issue before the upgrade,
and the sproc hasn't changed.  I ran an Estimated Execution Plan which
showed that the Clustered Index Scan on the AccessEvent primary key
takes up the bulk of the time.

I DID notice that, as part of the upgrade, the AccessEvent table now
has 7 non-clustered indexes in addition to the clustered PK index.
The indexes include one for [TimeStamp], another for DoorID, another
for AreadID, one for CardNumber, and one for PersonID...so all the
pertinent fields that are referenced in the big AccessEvent table are
indexed fields.

Any ideas what might be causing such a slowdown?  I'm sorry for the
long question, but I wanted to supply complete info.

Regards,

Steve Erbach
Neenah, WI

P.S., I DID just notice that there are TWO non-clustered indexes on
the same field: [TimeStamp].  I wonder if that has anything to do with
it?