Francisco Tapia
fhtapia at gmail.com
Thu Dec 11 08:08:54 CST 2008
Steve, I am a big fan of VM's but even I will admit that there are limitations. SQL requires fast I/o access. So even though the host is a honking machine, how much CPU/ memory is alocated to you SQL vm? Are there any other vm's on the same disk with your VM? Because of the way VM's work, what VM technology did he implement? By that I ask who is the host? Is it esx or is it windows. Sent from my mobile On Dec 10, 2008, at 8:25 AM, "Steve Erbach" <erbachs at gmail.com> wrote: > 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 >