Help on turning query

• huishi.ca

  SSChasing Mays

  Points: 600

  More actions

  January 14, 2021 at 10:07 pm

  #3833548

  hi there:

  we are using sql2017 std version.  Query plan attached here. I found that sort operator was having the biggest operator cost. Any suggestion to tune this query?  I am starting to work on tuning.. please forgive some naïve questions I may post from time to time.

  Thanks

  Hui

  <?xml version="1.0" encoding="utf-16"?>

  <ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.3.1" Build="12.0.5223.6">

  <BatchSequence>

  <Batch>

  <Statements>

  <StmtSimple StatementText="SELECT [DocumentCategory].[CategoryID] AS [CategoryID], [DocumentCategory].[ProcessEvents] AS [ProcessEvents], [DocumentCategory].[HeaderText] AS [HeaderText], [DocumentCategory].[DocumentCategoryTypeID] AS [DocumentCategoryTypeID], [DocumentCategory].[ParentCategoryID] AS [ParentCategoryID], [DocumentCategory].[HideInCategoryTree] AS [HideInCategoryTree], [DocumentCategory].[Name] AS [Name] FROM (SELECT [DocumentCategory].[CategoryID] AS [CategoryID], [DocumentCategory].[ProcessEvents] AS [ProcessEvents], [DocumentCategory].[HeaderText] AS [HeaderText], [DocumentCategory].[DocumentCategoryTypeID] AS [DocumentCategoryTypeID], [DocumentCategory].[ParentCategoryID] AS [ParentCategoryID], [DocumentCategory].[HideInCategoryTree] AS [HideInCategoryTree], COALESCE(ut0.String, DocumentCategory.Name) AS Name FROM DocumentCategory LEFT OUTER JOIN (SELECT u0.[String], u0.[StringRef] FROM [UserStrings] u0 WHERE u0.[Culture]='en') ut0 ON 'DocumentCategory_' + [DocumentCategory].[CategoryID] + '_Name'=ut0.[StringRef] ) [DocumentCategory] ORDER BY Name" StatementId="1" StatementCompId="1" StatementType="SELECT" RetrievedFromCache="true" StatementSubTreeCost="1226.23" StatementEstRows="205834" StatementOptmLevel="FULL" QueryHash="0xC1AC9D4638533A38" QueryPlanHash="0xE18768F03861D94F" CardinalityEstimationModelVersion="120">

  <StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="false" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false" />

  <QueryPlan CachedPlanSize="72" CompileTime="3" CompileCPU="3" CompileMemory="456">

  <ThreadStat Branches="2" />

  <Warnings>

  <PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT_IMPLICIT(nvarchar(max),[Prod_SD].[dbo].[DocumentCategory].[Name],0)" />

  </Warnings>

  <MemoryGrantInfo SerialRequiredMemory="1536" SerialDesiredMemory="2074776" />

  <OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="262144" EstimatedPagesCached="131072" EstimatedAvailableDegreeOfParallelism="2" MaxCompileMemory="11220568" />

  <TraceFlags IsCompileTime="1">

  <TraceFlag Value="2861" Scope="Global" />

  </TraceFlags>

  <RelOp NodeId="0" PhysicalOp="Parallelism" LogicalOp="Gather Streams" EstimateRows="205834" EstimateIO="0" EstimateCPU="59.7792" AvgRowSize="8211" EstimatedTotalSubtreeCost="1226.23" Parallel="1" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row">

  <OutputList>

  <ColumnReference Database="[Prod_SD]" Schema="[dbo]" Table="[DocumentCategory]" Column="CategoryID" />

  <ColumnReference Database="[Prod_SD]" Schema="[dbo]" Table="[DocumentCategory]" Column="ParentCategoryID" />

  <ColumnReference Database="[Prod_SD]" Schema="[dbo]" Table="[DocumentCategory]" Column="HeaderText" />

  <ColumnReference Database="[Prod_SD]" Schema="[dbo]" Table="[DocumentCategory]" Column="HideInCategoryTree" />

  <ColumnReference Database="[Prod_SD]" Schema="[dbo]" Table="[DocumentCategory]" Column="DocumentCategoryTypeID" />

  <ColumnReference Database="[Prod_SD]" Schema="[dbo]" Table="[DocumentCategory]" Column="ProcessEvents" />

  <ColumnReference Column="Expr1005" />

  </OutputList>

  <Parallelism>

  <OrderBy>

  <OrderByColumn Ascending="1">

  <ColumnReference Column="Expr1005" />

  </OrderByColumn>

  </OrderBy>

  <RelOp NodeId="1" PhysicalOp="Sort" LogicalOp="Sort" EstimateRows="205834" EstimateIO="1158.98" EstimateCPU="1.25241" AvgRowSize="8211" EstimatedTotalSubtreeCost="1166.45" Parallel="1" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row">

  <OutputList>

  <ColumnReference Database="[Prod_SD]" Schema="[dbo]" Table="[DocumentCategory]" Column="CategoryID" />

  <ColumnReference Database="[Prod_SD]" Schema="[dbo]" Table="[DocumentCategory]" Column="ParentCategoryID" />

  <ColumnReference Database="[Prod_SD]" Schema="[dbo]" Table="[DocumentCategory]" Column="HeaderText" />

  <ColumnReference Database="[Prod_SD]" Schema="[dbo]" Table="[DocumentCategory]" Column="HideInCategoryTree" />

  <ColumnReference Database="[Prod_SD]" Schema="[dbo]" Table="[DocumentCategory]" Column="DocumentCategoryTypeID" />

  <ColumnReference Database="[Prod_SD]" Schema="[dbo]" Table="[DocumentCategory]" Column="ProcessEvents" />

  <ColumnReference Column="Expr1005" />

  </OutputList>

  <MemoryFractions Input="0.999985" Output="1" />

  <Sort Distinct="0">

  <OrderBy>

  <OrderByColumn Ascending="1">

  <ColumnReference Column="Expr1005" />

  </OrderByColumn>

  </OrderBy>

  <RelOp NodeId="2" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="205834" EstimateIO="0" EstimateCPU="0.0102917" AvgRowSize="8211" EstimatedTotalSubtreeCost="6.22054" Parallel="1" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row">

  <OutputList>

  <ColumnReference Database="[Prod_SD]" Schema="[dbo]" Table="[DocumentCategory]" Column="CategoryID" />

  <ColumnReference Database="[Prod_SD]" Schema="[dbo]" Table="[DocumentCategory]" Column="ParentCategoryID" />

  <ColumnReference Database="[Prod_SD]" Schema="[dbo]" Table="[DocumentCategory]" Column="HeaderText" />

  <ColumnReference Database="[Prod_SD]" Schema="[dbo]" Table="[DocumentCategory]" Column="HideInCategoryTree" />

  <ColumnReference Database="[Prod_SD]" Schema="[dbo]" Table="[DocumentCategory]" Column="DocumentCategoryTypeID" />

  <ColumnReference Database="[Prod_SD]" Schema="[dbo]" Table="[DocumentCategory]" Column="ProcessEvents" />

  <ColumnReference Column="Expr1005" />

  </OutputList>

  <ComputeScalar>

  <DefinedValues>

  <DefinedValue>

  <ColumnReference Column="Expr1005" />

  <ScalarOperator ScalarString="CASE WHEN [Expr1003] IS NOT NULL THEN [Expr1003] ELSE CONVERT_IMPLICIT(nvarchar(max),[Prod_SD].[dbo].[DocumentCategory].[Name],0) END">

  <IF>

  <Condition>

  <ScalarOperator>

  <Compare CompareOp="IS NOT">

  <ScalarOperator>

  <Identifier>

  <ColumnReference Column="Expr1003" />

  </Identifier>

  </ScalarOperator>

  <ScalarOperator>

  <Const ConstValue="NULL" />

  </ScalarOperator>

  </Compare>

  </ScalarOperator>

  </Condition>

  <Then>

  <ScalarOperator>

  <Identifier>

  <ColumnReference Column="Expr1003" />

  </Identifier>

  </ScalarOperator>

  </Then>

  <Else>

  <ScalarOperator>

  <Convert DataType="nvarchar(max)" Length="2147483647" Style="0" Implicit="1">

  <ScalarOperator>

  <Identifier>

  <ColumnReference Database="[Prod_SD]" Schema="[dbo]" Table="[DocumentCategory]" Column="Name" />

  </Identifier>

  </ScalarOperator>

  </Convert>

  </ScalarOperator>

  </Else>

  </IF>

  </ScalarOperator>

  </DefinedValue>

  </DefinedValues>

  <RelOp NodeId="3" PhysicalOp="Hash Match" LogicalOp="Right Outer Join" EstimateRows="205834" EstimateIO="0" EstimateCPU="0.917066" AvgRowSize="8468" EstimatedTotalSubtreeCost="6.21025" Parallel="1" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row">

  <OutputList>

  <ColumnReference Database="[Prod_SD]" Schema="[dbo]" Table="[DocumentCategory]" Column="CategoryID" />

  <ColumnReference Database="[Prod_SD]" Schema="[dbo]" Table="[DocumentCategory]" Column="ParentCategoryID" />

  <ColumnReference Database="[Prod_SD]" Schema="[dbo]" Table="[DocumentCategory]" Column="Name" />

  <ColumnReference Database="[Prod_SD]" Schema="[dbo]" Table="[DocumentCategory]" Column="HeaderText" />

  <ColumnReference Database="[Prod_SD]" Schema="[dbo]" Table="[DocumentCategory]" Column="HideInCategoryTree" />

  <ColumnReference Database="[Prod_SD]" Schema="[dbo]" Table="[DocumentCategory]" Column="DocumentCategoryTypeID" />

  <ColumnReference Database="[Prod_SD]" Schema="[dbo]" Table="[DocumentCategory]" Column="ProcessEvents" />

  <ColumnReference Column="Expr1003" />

  </OutputList>

  <MemoryFractions Input="1" Output="1.54348e-005" />

  <Hash>

  <DefinedValues />

  <HashKeysBuild>

  <ColumnReference Database="[Prod_SD]" Schema="[dbo]" Table="[UserStrings]" Alias="[u0]" Column="StringRef" />

  </HashKeysBuild>

  <HashKeysProbe>

  <ColumnReference Column="Expr1006" />

  </HashKeysProbe>

  <ProbeResidual>

  <ScalarOperator ScalarString="[Expr1006]=[Prod_SD].[dbo].[UserStrings].[StringRef] as [u0].[StringRef]">

  <Compare CompareOp="EQ">

  <ScalarOperator>

  <Identifier>

  <ColumnReference Column="Expr1006" />

  </Identifier>

  </ScalarOperator>

  <ScalarOperator>

  <Identifier>

  <ColumnReference Database="[Prod_SD]" Schema="[dbo]" Table="[UserStrings]" Alias="[u0]" Column="StringRef" />

  </Identifier>

  </ScalarOperator>

  </Compare>

  </ScalarOperator>

  </ProbeResidual>

  <RelOp NodeId="4" PhysicalOp="Parallelism" LogicalOp="Distribute Streams" EstimateRows="1" EstimateIO="0" EstimateCPU="0.0287879" AvgRowSize="4149" EstimatedTotalSubtreeCost="0.150917" Parallel="1" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row">

  <OutputList>

  <ColumnReference Database="[Prod_SD]" Schema="[dbo]" Table="[UserStrings]" Alias="[u0]" Column="StringRef" />

  <ColumnReference Column="Expr1003" />

  </OutputList>

  <Parallelism PartitioningType="Broadcast">

  <RelOp NodeId="5" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="1" EstimateIO="0" EstimateCPU="0.0004605" AvgRowSize="4156" EstimatedTotalSubtreeCost="0.119919" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row">

  <OutputList>

  <ColumnReference Database="[Prod_SD]" Schema="[dbo]" Table="[UserStrings]" Alias="[u0]" Column="StringRef" />

  <ColumnReference Column="Expr1003" />

  </OutputList>

  <ComputeScalar>

  <DefinedValues>

  <DefinedValue>

  <ColumnReference Column="Expr1003" />

  <ScalarOperator ScalarString="[Prod_SD].[dbo].[UserStrings].[String] as [u0].[String]">

  <Identifier>

  <ColumnReference Database="[Prod_SD]" Schema="[dbo]" Table="[UserStrings]" Alias="[u0]" Column="String" />

  </Identifier>

  </ScalarOperator>

  </DefinedValue>

  </DefinedValues>

  <RelOp NodeId="6" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="1" EstimateIO="0.114236" EstimateCPU="0.0052225" AvgRowSize="4156" EstimatedTotalSubtreeCost="0.119459" TableCardinality="4605" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row">

  <OutputList>

  <ColumnReference Database="[Prod_SD]" Schema="[dbo]" Table="[UserStrings]" Alias="[u0]" Column="StringRef" />

  <ColumnReference Database="[Prod_SD]" Schema="[dbo]" Table="[UserStrings]" Alias="[u0]" Column="String" />

  </OutputList>

  <IndexScan Ordered="0" ForcedIndex="0" ForceScan="0" NoExpandHint="0" Storage="RowStore">

  <DefinedValues>

  <DefinedValue>

  <ColumnReference Database="[Prod_SD]" Schema="[dbo]" Table="[UserStrings]" Alias="[u0]" Column="StringRef" />

  </DefinedValue>

  <DefinedValue>

  <ColumnReference Database="[Prod_SD]" Schema="[dbo]" Table="[UserStrings]" Alias="[u0]" Column="String" />

  </DefinedValue>

  </DefinedValues>

  <Object Database="[Prod_SD]" Schema="[dbo]" Table="[UserStrings]" Index="[PK_UserStrings]" Alias="[u0]" IndexKind="Clustered" Storage="RowStore" />

  <Predicate>

  <ScalarOperator ScalarString="[Prod_SD].[dbo].[UserStrings].[Culture] as [u0].[Culture]=N'en'">

  <Compare CompareOp="EQ">

  <ScalarOperator>

  <Identifier>

  <ColumnReference Database="[Prod_SD]" Schema="[dbo]" Table="[UserStrings]" Alias="[u0]" Column="Culture" />

  </Identifier>

  </ScalarOperator>

  <ScalarOperator>

  <Const ConstValue="N'en'" />

  </ScalarOperator>

  </Compare>

  </ScalarOperator>

  </Predicate>

  </IndexScan>

  </RelOp>

  </ComputeScalar>

  </RelOp>

  </Parallelism>

  </RelOp>

  <RelOp NodeId="10" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="205834" EstimateIO="0" EstimateCPU="0.0102917" AvgRowSize="4515" EstimatedTotalSubtreeCost="5.14226" Parallel="1" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row">

  <OutputList>

  <ColumnReference Database="[Prod_SD]" Schema="[dbo]" Table="[DocumentCategory]" Column="CategoryID" />

  <ColumnReference Database="[Prod_SD]" Schema="[dbo]" Table="[DocumentCategory]" Column="ParentCategoryID" />

  <ColumnReference Database="[Prod_SD]" Schema="[dbo]" Table="[DocumentCategory]" Column="Name" />

  <ColumnReference Database="[Prod_SD]" Schema="[dbo]" Table="[DocumentCategory]" Column="HeaderText" />

  <ColumnReference Database="[Prod_SD]" Schema="[dbo]" Table="[DocumentCategory]" Column="HideInCategoryTree" />

  <ColumnReference Database="[Prod_SD]" Schema="[dbo]" Table="[DocumentCategory]" Column="DocumentCategoryTypeID" />

  <ColumnReference Database="[Prod_SD]" Schema="[dbo]" Table="[DocumentCategory]" Column="ProcessEvents" />

  <ColumnReference Column="Expr1006" />

  </OutputList>

  <ComputeScalar>

  <DefinedValues>

  <DefinedValue>

  <ColumnReference Column="Expr1006" />

  <ScalarOperator ScalarString="N'DocumentCategory_'+[Prod_SD].[dbo].[DocumentCategory].[CategoryID]+N'_Name'">

  <Arithmetic Operation="ADD">

  <ScalarOperator>

  <Arithmetic Operation="ADD">

  <ScalarOperator>

  <Const ConstValue="N'DocumentCategory_'" />

  </ScalarOperator>

  <ScalarOperator>

  <Identifier>

  <ColumnReference Database="[Prod_SD]" Schema="[dbo]" Table="[DocumentCategory]" Column="CategoryID" />

  </Identifier>

  </ScalarOperator>

  </Arithmetic>

  </ScalarOperator>

  <ScalarOperator>

  <Const ConstValue="N'_Name'" />

  </ScalarOperator>

  </Arithmetic>

  </ScalarOperator>

  </DefinedValue>

  </DefinedValues>

  <RelOp NodeId="11" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="205834" EstimateIO="5.01868" EstimateCPU="0.113287" AvgRowSize="4442" EstimatedTotalSubtreeCost="5.13197" TableCardinality="205834" Parallel="1" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row">

  <OutputList>

  <ColumnReference Database="[Prod_SD]" Schema="[dbo]" Table="[DocumentCategory]" Column="CategoryID" />

  <ColumnReference Database="[Prod_SD]" Schema="[dbo]" Table="[DocumentCategory]" Column="ParentCategoryID" />

  <ColumnReference Database="[Prod_SD]" Schema="[dbo]" Table="[DocumentCategory]" Column="Name" />

  <ColumnReference Database="[Prod_SD]" Schema="[dbo]" Table="[DocumentCategory]" Column="HeaderText" />

  <ColumnReference Database="[Prod_SD]" Schema="[dbo]" Table="[DocumentCategory]" Column="HideInCategoryTree" />

  <ColumnReference Database="[Prod_SD]" Schema="[dbo]" Table="[DocumentCategory]" Column="DocumentCategoryTypeID" />

  <ColumnReference Database="[Prod_SD]" Schema="[dbo]" Table="[DocumentCategory]" Column="ProcessEvents" />

  </OutputList>

  <IndexScan Ordered="0" ForcedIndex="0" ForceScan="0" NoExpandHint="0" Storage="RowStore">

  <DefinedValues>

  <DefinedValue>

  <ColumnReference Database="[Prod_SD]" Schema="[dbo]" Table="[DocumentCategory]" Column="CategoryID" />

  </DefinedValue>

  <DefinedValue>

  <ColumnReference Database="[Prod_SD]" Schema="[dbo]" Table="[DocumentCategory]" Column="ParentCategoryID" />

  </DefinedValue>

  <DefinedValue>

  <ColumnReference Database="[Prod_SD]" Schema="[dbo]" Table="[DocumentCategory]" Column="Name" />

  </DefinedValue>

  <DefinedValue>

  <ColumnReference Database="[Prod_SD]" Schema="[dbo]" Table="[DocumentCategory]" Column="HeaderText" />

  </DefinedValue>

  <DefinedValue>

  <ColumnReference Database="[Prod_SD]" Schema="[dbo]" Table="[DocumentCategory]" Column="HideInCategoryTree" />

  </DefinedValue>

  <DefinedValue>

  <ColumnReference Database="[Prod_SD]" Schema="[dbo]" Table="[DocumentCategory]" Column="DocumentCategoryTypeID" />

  </DefinedValue>

  <DefinedValue>

  <ColumnReference Database="[Prod_SD]" Schema="[dbo]" Table="[DocumentCategory]" Column="ProcessEvents" />

  </DefinedValue>

  </DefinedValues>

  <Object Database="[Prod_SD]" Schema="[dbo]" Table="[DocumentCategory]" Index="[PK_Category]" IndexKind="Clustered" Storage="RowStore" />

  </IndexScan>

  </RelOp>

  </ComputeScalar>

  </RelOp>

  </Hash>

  </RelOp>

  </ComputeScalar>

  </RelOp>

  </Sort>

  </RelOp>

  </Parallelism>

  </RelOp>

  </QueryPlan>

  </StmtSimple>

  </Statements>

  </Batch>

  </BatchSequence>

  </ShowPlanXML>

  • This topic was modified 3 years, 10 months ago by  huishi.ca.
  • This topic was modified 3 years, 10 months ago by  huishi.ca.

• frederico_fonseca

  SSCoach

  Points: 16154

  More actions

  January 14, 2021 at 10:42 pm

  #3833578

  not much you can do on this except remove the sort.

  plan here https://www.brentozar.com/pastetheplan/?id=BJrAKS0RD

  SELECT [DocumentCategory].[CategoryID] AS [CategoryID]
       , [DocumentCategory].[ProcessEvents] AS [ProcessEvents]
       , [DocumentCategory].[HeaderText] AS [HeaderText]
       , [DocumentCategory].[DocumentCategoryTypeID] AS [DocumentCategoryTypeID]
       , [DocumentCategory].[ParentCategoryID] AS [ParentCategoryID]
       , [DocumentCategory].[HideInCategoryTree] AS [HideInCategoryTree]
       , [DocumentCategory].[Name] AS [Name]
  FROM (SELECT [DocumentCategory].[CategoryID] AS [CategoryID]
             , [DocumentCategory].[ProcessEvents] AS [ProcessEvents]
             , [DocumentCategory].[HeaderText] AS [HeaderText]
             , [DocumentCategory].[DocumentCategoryTypeID] AS [DocumentCategoryTypeID]
             , [DocumentCategory].[ParentCategoryID] AS [ParentCategoryID]
             , [DocumentCategory].[HideInCategoryTree] AS [HideInCategoryTree]
             , COALESCE(ut0.String , DocumentCategory.Name) AS Name
        FROM DocumentCategory
        LEFT OUTER JOIN (SELECT u0.[String] , u0.[StringRef]
                         FROM [UserStrings] u0
                         WHERE u0.[Culture]='en'
                        ) ut0
             ON 'DocumentCategory_' + [DocumentCategory].[CategoryID] + '_Name' = ut0.[StringRef] ) [DocumentCategory]
  
  ORDER BY Name
  

  • This reply was modified 3 years, 10 months ago by  frederico_fonseca.

• huishi.ca

  SSChasing Mays

  Points: 600

  More actions

  January 14, 2021 at 10:59 pm

  #3833584

  thanks. It was vendor supported application. We are not allowed to change any query though

• Mr. Brian Gale

  SSC-Insane

  Points: 24800

  More actions

  January 15, 2021 at 9:57 pm

  #3833961

  My 2 cents - if you can't change the query, you only have 2 options to improve performance:

  1 - reduce the data set (ie archive data)

  2 - indexes

  My reading of raw XML is a bit rusty, but I think you have decent indexes.  So, if you can't change the query, and the indexes are decent (getting index scans all around and no seeks), I think reducing the data set is the only other performance tip I can think of.  Well, apart from faster disk, isolated disk, and potentially upgrading to 2019.

  The above is all just my opinion on what you should do. 
  As with all advice you find on a random internet forum - you shouldn't blindly follow it.  Always test on a test server to see if there is negative side effects before making changes to live!
  I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

• ScottPletcher

  SSC Guru

  Points: 101095

  More actions

  January 16, 2021 at 6:00 am

  #3834041

  huishi.ca wrote:

  thanks. It was vendor supported application. We are not allowed to change any query though

  I'm confused then.  When you originally asked:

  Any suggestion to tune this query?

  Did you expect we could tune the query without changing it, at all?  That doesn't really make any sense.

  If you can't change the query, you can't really tune it properly.  You might be able to add or change index(es), but, then again, if you can't change the query, it's rather likely you can't add/change any indexes either.

  SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".