April 28, 2011 at 3:27 am
Hi,
I am running the below query uding execution plan, but found the bookmarks. Trying to remove those bookmarks, but not able to do so. Checked the indexes by running index tuning wizard and it has not given any new indexes to be added or modified.
SELECT
E.StartTimestamp
FROM
test E
WHERE
E.Number_FK = @Number_PK AND
E.RMType_FK = @RMType_PK AND
E.CEN = @CEN_PK AND
E.StartTimestamp BETWEEN @StatisticsStartTime AND @StatisticsEndTime AND
ISNULL(@FloorNbr, -1) = CASE WHEN @FloorByFloor = 1 THEN E.Floor ELSE -1 END AND
( -- Temporary Fault / KNXM: 17923, 17924 and KNX3 : 1543
E.MessageType_FK IN (17923, 17924, 1543)
OR
-- low level system failures
E.MessageType_FK IN (17921, 17922) -- rif, analyzer
OR
( -- Elevator Failure or Escalator Failure > a call-out NOT sent to Konect / KNXM: 16644, 16646 and KNX3 : 513
E.MessageType_FK IN (16644, 16646, 513) AND
E.Event_PK IN (SELECT Event_fK
FROM Test2
WHERE FilteringStatus_FK IN (2, 4, 5, 6))
)
)
ORDER BY
E.StartTimestamp
Please help on how to remove the bookmarks created
Regards,
Naveen
April 28, 2011 at 4:19 am
Please post execution plans, query, table definition, index definitions. See http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 28, 2011 at 4:55 am
Please find the trace of the query which i posted
SET STATISTICS PROFILE ON SQL:StmtCompleted0000
SELECT E.StartTimestamp FROM eventrelevant_v E WHERE --e.Irrelevant <> '1' and E.EquipmentNumber_FK = 1 AND E.RemoteMonitoringType_FK = 'knxm' AND E.CEN = 1 AND E.StartTimestamp BETWEEN getdate() AND getdate() AND ISNULL(null, -1) SQL:StmtCompleted150420
SET STATISTICS PROFILE OFF SQL:StmtCompleted0000
April 28, 2011 at 4:59 am
index definitin:
I_CalculatedEndTimestamp_Eventnonclustered located on PRIMARYCalculatedEndTimestamp
I1_Eventnonclustered located on PRIMARYRMType_FK, MessageType_FK, StartTimestamp
I2_Eventnonclustered located on PRIMARYStartTimestamp(-), Number_FK, MessageType_FK
PK_Eventclustered, unique, primary key located on PRIMARYEvent_PK
U1_Eventnonclustered, unique located on PRIMARYNumber_FK, StartTimestamp, CEN, RMType_FK, MessageType_FK, Floor
table definition:
Event_PKbigintno8 no(n/a)(n/a)NULL
Number_FKnumericno918 0 no(n/a)(n/a)NULL
StartTimestampdatetimeno8 no(n/a)(n/a)NULL
RMType_FKvarcharno10 nononoSQL_Latin1_General_CP1_CI_AS
AnalyzerType_FKvarcharno20 yesnonoSQL_Latin1_General_CP1_CI_AS
Class_FKvarcharno20 yesnonoSQL_Latin1_General_CP1_CI_AS
MessageType_FKintno410 0 no(n/a)(n/a)NULL
CENintno410 0 yes(n/a)(n/a)NULL
Floorsmallintno25 0 yes(n/a)(n/a)NULL
EndTimestampdatetimeno8 yes(n/a)(n/a)NULL
FailureClassDelaysmallintno25 0 yes(n/a)(n/a)NULL
MovingStatussmallintno25 0 yes(n/a)(n/a)NULL
OperationModesmallintno25 0 yes(n/a)(n/a)NULL
DoorZonesmallintno25 0 yes(n/a)(n/a)NULL
DoorStatussmallintno25 0 yes(n/a)(n/a)NULL
DriveStatussmallintno25 0 yes(n/a)(n/a)NULL
FaultClasscharno1 yesnoyesSQL_Latin1_General_CP1_CI_AS
SourceUpperbitno1 yes(n/a)(n/a)NULL
SourceLowerbitno1 yes(n/a)(n/a)NULL
SourceLeftbitno1 yes(n/a)(n/a)NULL
SourceRightbitno1 yes(n/a)(n/a)NULL
FaultCurrentlyActivebitno1 yes(n/a)(n/a)NULL
FaultShutsDownbitno1 yes(n/a)(n/a)NULL
FaultNeedsManualResetbitno1 yes(n/a)(n/a)NULL
FaultIsEmergencyStopbitno1 yes(n/a)(n/a)NULL
FaultInDevice1bitno1 yes(n/a)(n/a)NULL
FaultInDevice2bitno1 yes(n/a)(n/a)NULL
FaultInDevice3bitno1 yes(n/a)(n/a)NULL
FaultInDevice4bitno1 yes(n/a)(n/a)NULL
FaultInDevice5bitno1 yes(n/a)(n/a)NULL
FaultInDevice6bitno1 yes(n/a)(n/a)NULL
FaultInDevice7bitno1 yes(n/a)(n/a)NULL
FaultInDevice8bitno1 yes(n/a)(n/a)NULL
BrakeSlidingDistanceintno410 0 yes(n/a)(n/a)NULL
StoppingDistanceDataintno410 0 yes(n/a)(n/a)NULL
Knx3ExternalInput1bitno1 yes(n/a)(n/a)NULL
Knx3ExternalInput2bitno1 yes(n/a)(n/a)NULL
Knx3SystemFailureReason_FKtinyintno13 0 yes(n/a)(n/a)NULL
ReportCallbitno1 yes(n/a)(n/a)NULL
ReportCallHandledbysmallintno25 0 yes(n/a)(n/a)NULL
CalculatedEndTimestampdatetimeno8 yes(n/a)(n/a)NULL
CreateDatedatetimeno8 no(n/a)(n/a)NULL
Creatorvarcharno10 nononoSQL_Latin1_General_CP1_CI_AS
DriveDetectedFault1intno410 0 yes(n/a)(n/a)NULL
DriveDetectedFault2intno410 0 yes(n/a)(n/a)NULL
DriveDetectedFault3intno410 0 yes(n/a)(n/a)NULL
Commentvarcharno2000 yesnonoSQL_Latin1_General_CP1_CI_AS
CarLoaddecimalno56 3 yes(n/a)(n/a)NULL
FrontEndIDvarcharno15 yesnonoSQL_Latin1_General_CP1_CI_AS
CEN_Statusintno410 0 yes(n/a)(n/a)NULL
Irrelevantcharno1 yesnoyesSQL_Latin1_General_CP1_CI_AS
April 28, 2011 at 5:17 am
Execution plan in Excel please, as shown in article.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 28, 2011 at 5:28 am
Excel is attached please lookinto it
April 28, 2011 at 6:06 am
As requested i have attached the execution plan, can you please check it and let me know what should be done so that bookmarks should be removed
Regards,
NAveen
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply