[dba-SQLServer] Performance of a SP

What about the memory he has set up for the virtual SQL Server? Nancy Lytle N_Lytle at terpalum.umd.edu



 EMAILING FOR THE GREATER GOODJoin me> Date: Wed, 10 Dec 2008 10:25:43 -0600> From: erbachs at gmail.com> To: dba-sqlserver at databaseadvisors.com> Subject: [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?> _______________________________________________> dba-SQLServer mailing list> dba-SQLServer at databaseadvisors.com> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver> http://www.databaseadvisors.com>