1. Odd Performance Issue
I have a strange problem. I have an application being developed that
queries the SQL Server database often. Sometimes, reponse time for these
queries goes to between 4 and 10 seconds. But the queries are identical,
and 90% of the time it takes 30ms. The SQL Server being used is currently
the desktop version of SQL 7 running on an NT Workstation (that's not being
used for anything else at the time) with 256MB of RAM.
I've got the Perfomance Monitor running and the Profiler tracing. I see no
lock deadlocks reported. I don't think I've seen any lock waits reported in
the performance monitor. The client is a VB application using ADO. I've
installed MDAC 2.6 on the client. The pause is in the server, it seems,
because the profiler does report the duration time as between 4000 and
10000ms. It always seems to be the same query that suddenly takes a long
time.
Here is the query. It actually references a view, I'll provide that as
well...
SELECT Unit, Status, CallID,
Online = CASE When ISNULL(ActiveUnitsView.Online, 0) > 0 THEN 'YES' ELSE
'' END,
UnitType, StatusCode, BackColor, ForeColor, Bold, Italic
FROM ActiveUnitsView
WHERE (NOT ISNULL(UnitType, ' ') = 'D')
ORDER BY Unit
Here is the ActiveUnitsView. It is a join of a number of tables:
CREATE VIEW dbo.ActiveUnitsView
AS
SELECT UnitShiftEntry.UnitKey AS Unit,
UnitsActive.UnitShiftEntryKey,
UnitsActive.LastActivity AS StatusCode,
UnitsActive.CADCallKey AS CallID,
CADCallActivityTypeRef.CADCallActivityName AS Status,
Unit.UnitType, UnitsOnlineView.Cnt AS Online,
ISNULL(Officer.OfficerRank + ' ', '') + ISNULL(Officer.LastName, '') +
ISNULL(', ' + Officer.FirstName, '') AS StaticOfficer,
CADCallActivityTypeRef.BackColor,
CADCallActivityTypeRef.ForeColor,
CADCallActivityTypeRef.Bold,
CADCallActivityTypeRef.Italic,
CADCallActivityTypeRef.MobileCode,
UnitTypeRef.MultiCall
FROM UnitsActive LEFT OUTER JOIN
UnitShiftEntry ON UnitsActive.UnitShiftEntryKey =
UnitShiftEntry.UnitShiftEntryKey LEFT OUTER JOIN
Unit ON UnitShiftEntry.UnitKey = Unit.UnitKey LEFT OUTER JOIN
CADCallActivityTypeRef ON CADCallActivityTypeRef.CADCallActivityTypeKey
= UnitsActive.LastActivity LEFT OUTER JOIN
UnitsOnlineView ON Unit.UnitKey = UnitsOnlineView.UnitKey LEFT OUTER
JOIN
Officer ON unit.StaticOfficerKey = Officer.OfficerKey LEFT OUTER JOIN
UnitTypeRef ON Unit.UnitType = UnitTypeRef.UnitType
Which references the UnitsOnlineView, which is defined as:
2. Network Suggestions???
3. Odd performance problem
4. Generate an e-mail from Pick
5. Odd performance dropoff inside a cursor
6. Help ! Tempdb !
7. Odd Performance Problem
8. InterDev and Progress
9. Odd Performance Spikes in ASE 11.9.2
10. Very odd performance problem
11. Odd view performance
12. Online 7.11 Select Performance - Odd Results
13. SQL Server Performance Problem - Good query performance, bad update performance
by