tuning the sort step of execution plan | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

tuning the sort step of execution plan

hi<br />i got a query that takes about 14 mins<br />here it is<br /> select BDProduct.ProductCode,BDProduct.ProductName,SALTerritory.TerritoryID<br /> ,SALTerritory.TerritoryName,SALAccount.AccountID,SALAccount.AccountName<br /> ,sum(SalesNetFact.Qty2) as Quantity<br /> ,sum(SalesNetFact.bonus) as Bonus<br /> from SalesNetFact <br /> inner join BDProduct<br /> on BDProduct.ProductID=SalesNetFact.ProductID<br /> inner join SALAccount <br /> on SALAccount.AccountID=SalesNetFact.AccountID <br /> and SALAccount.BranchID=SalesNetFact.branchid <br /> inner join SALTerritory <br /> on dbo.SALAccount.TerritoryID = dbo.SALTerritory.TerritoryID <br /> and dbo.SALAccount.BranchID = dbo.SALTerritory.BranchID <br /> group by BDProduct.ProductCode,BDProduct.ProductName<br /> ,SALTerritory.TerritoryID,SALTerritory.TerritoryName,SALAccount.AccountID<br /> ,SALAccount.AccountName<br />the SalesNetFact table has BranchID,TransactionLineID as primary key<br />the BDProduct table has ProductID as primary key<br />the SALAccount table has AccountID,BranchID as primary key<br />the SALTerritory table has TerritoryID,BranchID as primary key<br />i have no other indices in any of these tables<br />the execution plan shows that the sort step takes 96% cost,that is the most expensive step,it is done after all the joining steps and before the group by step<br />for the sort step:the estimated row count is 1552242,the arguments are<img src=’/community/emoticons/emotion-3.gif’ alt=’:O’ />RDER BY [BDProduct].[ProductCode] asc,[SALTerritory].[TerritoryID] asc,[SALTerritory].[TerritoryName] asc,[SalesNetFact].[AccountID] asc,[SALAccount].[AccountID] asc)<br />any ideas about how to improve this sort step<br />
http://www.sql-server-performance.com/query_execution_plan_analysis.asp fyi.
Ensure all the involed columns has required indexes. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
I have this issue as well but I cannot read anything in your link to give me a clue. I am doing an update and it uses the clustered index to do the lookup. The execution plan shows a sort. I have a non clustered index with all the columns in the where statement but it appears to use the clustered one which does not have any of the fields in the where statement in it (this is why it is a clustered scan I suppose). Now while writing this I realised the sort was then followed by and index update. The index it was updating was the one I created to speed up this very query (which it did not use). So I removed the unused index (non clustered) and the sort went away. Just got to get rid of the clustered table scan now
The above link gives you information about analyzing the execution plan returned by your query. Clustered indexes are not a good choice for:
* Columns that undergo frequent changes because this results in the entire row moving (because SQL Server must keep the row#%92s data values in physical order). This is an important consideration in high-volume transaction processing systems where data tends to be volatile. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
]]>