Indexing Problem

• vaibhav.tiwari

  SSCommitted

  Points: 1946

  More actions

  July 24, 2010 at 12:38 am

  #223679

  Hello Everyone,

  I have one Stored Procedure like below

  I am having two 18% Index Scan and sort 63%

  I have to optimize it. at least i want to remove index scan.

  What indexing should i choose

  File is attached for execution plan and SP code is below -

  DECLARE @varStTime DATETIME

  DECLARE @varEnTime DATETIME

  SET @varStTime = CONVERT( VARCHAR(10),@INStartDate, 110) + ' 00:00:00AM'

  SET @varEnTime = CONVERT( VARCHAR(10),@INEndDate, 110) + ' 11:59:59PM'

  SELECTTemplateId, MemberId, TemplateName, TemplateDescription, OSType, ServerDeployID, ServerTill_Time,

  replace(replace(replace(replace(replace(replace(replace(ServerWeekDays, '1', 'Sun'), '2', 'Mon'), '3', 'Tue'), '4', 'Wed'), '5', 'Thu'), '6', 'Fri'), '7', 'Sat') AS ServerWeekDays,

  ServerSch_Time, RestartServer, ModifiedOn,

  CASE UserType WHEN 'NOC' THEN UserType ELSE ModifiedBy END ModifiedBy, UserType,

  CASE [Action] WHEN 'I' THEN 1 WHEN 'U' THEN 2 WHEN 'D' THEN 3 END [Action], ModifiedOn DCDTime

  FROM

  (

  SELECTTemplateId, MemberId, TemplateName, TemplateDescription, OSType, ServerDeployID, ServerTill_Time,

  ServerWeekDays, ServerSch_Time, RestartServer, ModifiedOn, ModifiedBy, UserType, [Action], ModifiedOn DCDTime

  FROMpth_ServerTemplate WITH(NOLOCK)

  UNION

  SELECTTemplateId, MemberId, TemplateName, TemplateDescription, OSType, ServerDeployID, ServerTill_Time,

  ServerWeekDays, ServerSch_Time, RestartServer, ModifiedOn, ModifiedBy, UserType, [Action], DCDTime

  FROMpth_ServerTemplate_Audit WITH(NOLOCK)

  ) A

  WHEREMemberID = @INMemberID

  AND ( Templateid= @INTemplateId OR @INTemplateId IS NULL )

  AND DCDTime BETWEEN @varStTime AND @varEnTime

  Please assist me.

  Vaibhav K Tiwari
  To walk fast walk alone
  To walk far walk together

• Gail Shaw

  SSC Guru

  Points: 1004494

  More actions

  July 24, 2010 at 1:21 am

  #1197837

  Please post table definitions, index definitions and execution plan, as per http://qa.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

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass