Avatar of enrique_aeo
enrique_aeo
 asked on

10774 - bad performance (index scan)

Hi experts

i have this query

select soh.AccountNumber, sod.LineTotal, sod.OrderQty, sod.UnitPrice,p.Name
from Sales.SalesOrderHeader soh
            Inner join Sales.SalesOrderDetail sod on soh.SalesOrderID = sod.SalesOrderID
            inner join Production.Product p
on sod.ProductID = p.ProductID
where sod.LineTotal > 1000;

That should create indexes, thanks

view execution plan attached.

sp_helpindex 'Sales.SalesOrderHeader'
index_name      index_description      index_keys
AK_SalesOrderHeader_rowguid      nonclustered, unique located on PRIMARY      rowguid
AK_SalesOrderHeader_SalesOrderNumber      nonclustered, unique located on PRIMARY      SalesOrderNumber
IX_SalesOrderHeader_CustomerID      nonclustered located on PRIMARY      CustomerID
IX_SalesOrderHeader_SalesPersonID      nonclustered located on PRIMARY      SalesPersonID
PK_SalesOrderHeader_SalesOrderID      clustered, unique, primary key located on PRIMARY      SalesOrderID

sp_helpindex 'Sales.SalesOrderDetail'
index_name      index_description      index_keys
AK_SalesOrderDetail_rowguid      nonclustered, unique located on PRIMARY      rowguid
IX_SalesOrderDetail_ProductID      nonclustered located on PRIMARY      ProductID
PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID      clustered, unique, primary key located on PRIMARY      SalesOrderID, SalesOrderDetailID

sp_helpindex 'Production.Product'
index_name      index_description      index_keys
AK_Product_Name      nonclustered, unique located on PRIMARY      Name
AK_Product_ProductNumber      nonclustered, unique located on PRIMARY      ProductNumber
AK_Product_rowguid      nonclustered, unique located on PRIMARY      rowguid
PK_Product_ProductID      clustered, unique, primary key located on PRIMARY      ProductID
execution-plan.png
exution-plan.csv
Microsoft SQL Server 2008Microsoft SQL Server 2005Microsoft SQL Server

Avatar of undefined
Last Comment
ste5an

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
ste5an

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
enrique_aeo

ASKER
SSMS suggest
USE [AdventureWorks2012]
GO
CREATE NONCLUSTERED INDEX IDX_SOD
ON [Sales].[SalesOrderDetail] ([LineTotal])
INCLUDE ([SalesOrderID],[OrderQty],[ProductID],[UnitPrice])
GO

seen in plan execution using the new index.
You can not do anything else? to improve scan
execution-plan-better.png
SOLUTION
ste5an

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23