Odd Performance Issue

Odd Performance Issue

Post by Brian Biale » Wed, 27 Jun 2001 06:59:57



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:

 
 
 

Odd Performance Issue

Post by Arvind Krishn » Fri, 29 Jun 2001 03:46:15


Brian,
Have you tried to capture the Execution Plan event in SQL Profiler and
comparing the plans for the different execution times? Also, is the
duration consistent if you run the same query from Query Analyzer?

Thanks,
Arvind Krishnan
SQL Server Support
Microsoft Corporation

 
 
 

Odd Performance Issue

Post by Brian Biale » Sat, 30 Jun 2001 05:58:00


I didn't capture the plan, but did notice that most of the time the query
took about 30ms, but then started briefly to take 5000-10000ms.  I couldn't
see what was triggering it.  I believe the read count was about 500, which
seems high, but apparently not a big deal most of the time.

We just installed a W2k Server with SQL Server 2000 on it, and moved the
database there.  We'll see if that alone solves the problem.  Did that
yesterday...

I'll let you know if it is still a problem.  I read somewhere that the
personal edition, which I was using, has some throttling code in it.  I
don't know if that is related, but it is no longer part of the equation.

I may still want to "unnormalize" the data, keeping a single table with all
the agregated info I need to see refreshed often.  Then some changes may be
a little more expensive, as they would have to alter detail tables as well
as this aggregate table, but the queries would be very efficient...


Quote:> Brian,
> Have you tried to capture the Execution Plan event in SQL Profiler and
> comparing the plans for the different execution times? Also, is the
> duration consistent if you run the same query from Query Analyzer?

> Thanks,
> Arvind Krishnan
> SQL Server Support
> Microsoft Corporation

 
 
 

1. Odd twist to recompile issue on different connections

Reading the archives, it seems that most people are having problems
with to many recompiles.  I'm having the opposite problem. Because of
a stored procedure(s) that queries some very dynamic data, changed by
updates, adds and deletes, I have started scheduling updates to
statistics.  The gain in performance is astounding, a 100X improvement
in some queries.  However, clients who execute the query via a .net
app, don't see the change.  OK, so I added a sp_recompile to the
scheduled job.  It still didn't help.  Then I ran the sp_recomile
myself from query analyzer, and the .net calls started working again.
Now I'm confused.

I have seen other things like this, for instance the same query run
from query analyzer takes seconds, but run from .net takes minutes, at
least until I run sp_recompile again.  Is the cache that contains the
execution plan associated to specific connections?  What can I do in
my scheduled jobs to get my .net based clients to run with the new
execution plan?

I't possible I've misdiagnosed the problem, but over the last few
days, the sp_recompile always takes care of it when run manually.
There are seldom locks that could be causing the problem, or they are
all shared locks and the query is read only.  Any ideas appreciated.

Windows 2000 Avanced Server (clustered active/passive setup) - Sql
Server 2000

2. GUIDS Are not handled correctly

3. OPENROWSET performance issue or SET FMTONLY OFF issue

4. DBD::Oracle::st fetchrow_array failed: ERROR no statement executing

5. Stored Procedure issuing an odd error sometimes...

6. format date results from query

7. Odd deadlock issue.

8. SQL v6.5 for workstation installation problem

9. Odd SQL 6.5 client install issue

10. having an odd permissions related issue

11. Odd view performance

12. Odd performance problem

13. Odd performance dropoff inside a cursor