Query Performance - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Query Performance

Author

Topic

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2010-02-17 : 18:18:53

I have the following query that works however sometimes it takes upto 40 seconds to complete when run in SQL SERVER Management studio 2005. This is mainly the first time run and after that it normally takes a lot less time to show the results in the grid. So.. I was wondering if the query was written poorly causing performance issues? I just run the sql with some parameters that return 1489 rows and it took; test1 = 18 sec, test2 = 12 sec, test3 = 12 sec. We are using SQL Server 2005 Express running on a standard PC, nothing special. I do have concerns our indexing is done poorly.

USE [VC]
GO
/****** Object: StoredProcedure [dbo].[GetAckedEventsEx] Script Date: 02/18/2010 09:33:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Steve Harlington
-- Create date: <25 August 2009,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[GetAckedEventsEx]

@SiteID int,
@Top int,
@StartDateRange DateTime,
@EndDateRange DateTime

As

SELECT
E.EventID,
[CameraNumber],
[CameraName],
[EventType],
[Description],
[Priority],
[State],
[Cause],
[SourceID],
[SourceType],
[ACKFlag],
[FullyACKed],
[EventStartTime],
[LocalStartTime],
[EventEndTime],
[LocalEndTime],
[SiteID],
[StateType],
[StateData],
[SessionID],
[Indicator],
[ZoneNumber],
[EventCode],
[AlarmProfile],
[ZoneText],
[DetectorText],
[RID],
[MetaDataType],
[PictureID],
[CameraMask],
[RawData],
[EventExtraDataID],
EventExtraData.EventID As EventExtraDataEventID,
[Type],
[Format],
[FileName],
[StartTime],
[EndTime]
FROM (SELECT Top (@Top) *
FROM EventDetails
WHERE SiteID = @SiteID AND
EventDetails.LocalStartTime BETWEEN @StartDateRange AND @EndDateRange AND
EventDetails.FullyAcked = 'True'
ORDER BY EventDetails.LocalStartTime DESC, EventDetails.EventID DESC
) As E
LEFT JOIN
EventExtraData ON E.EventID = EventExtraData.EventID

Return

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-02-17 : 18:39:00

What indexes do you have on the tables involved in the query? Are your statistics up to date? How about fragmentation?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2010-02-17 : 19:55:06

I have just updated all statistics, rebuilt indexes and the query is still very variable sometimes returning instantly and other times taking upto 15 seconds (depending on the input parameters)?

These are the current indexes on the two tables in question.

EventDetails

IX_EventDetails nonclustered, unique located on PRIMARY EventID
IX_EventDetails_1 nonclustered located on PRIMARY EventStartTime(-)
IX_EventDetails_2 nonclustered located on PRIMARY SiteID, FullyACKed, EventStartTime(-), EventID(-)
IX_EventDetails_3 nonclustered located on PRIMARY SiteID, FullyACKed
IX_EventDetails_4 nonclustered located on PRIMARY SiteID, FullyACKed, CameraNumber
IX_EventDetails_5 nonclustered located on PRIMARY SiteID, FullyACKed, CameraNumber, EventStartTime(-), EventID(-)
IX_EventDetails_6 nonclustered located on PRIMARY SiteID, FullyACKed, Priority(-), EventStartTime
PK_EventDetails_1 clustered, unique, primary key located on PRIMARY EventID

EventExtraData

IX_EventExtraData nonclustered located on PRIMARY EventID(-)
IX_EventExtraData_1 nonclustered located on PRIMARY EventExtraDataID
PK_EventData clustered, unique, primary key located on PRIMARY EventExtraDataID

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2010-02-17 : 23:02:34

First of all You have many similar indexes which are not required.You
don't have index on LocalStartTime. I would make Composite Index on
(SITEID,LOCALSTARTTIME,FULLYACKED) If this query runs frequently.
I would also EVENTID clustered index in EVENTEXTRA Tables coz leaf level will have all data pages.
Also Check the index usage statistics and remove unnecessary indexes as it is confusing SQL Server.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-02-17 : 23:49:04

Remove the following duplicate indexes:
IX_EventDetails_3 nonclustered located on PRIMARY SiteID, FullyACKed
IX_EventDetails_4 nonclustered located on PRIMARY SiteID, FullyACKed, CameraNumber
IX_EventDetails nonclustered, unique located on PRIMARY EventID

IX_EventExtraData_1 nonclustered located on PRIMARY EventExtraDataID

Add the index that sodeep mentioned.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources