December 6, 2016 at 11:48 am
I have statement below which does a merge join but does a clustered index scan with over 2 million rows I am trying to get rid of. This comes from a 3rd party app. There is a clustered index on TherapyAdmin_ID, VisitObservation_ID on the adminobservation which does the scan
exec sp_executesql N'select TherapyAdmin_ID, VisitObservation_ID, 1 as RowVersionNumber from hcs.AdminObservation
where TherapyAdmin_ID in (select TherapyAdmin_ID from hcs.PatientOrder,hcs.TherapyAdmin where PatientOrder.Order_ID=TherapyAdmin.Order_ID
and PatientOrder.PatientVisit_ID=@Parameter1) order by VisitObservation_ID',N'@Parameter1 bigint',@Parameter1=264377212
December 6, 2016 at 12:13 pm
If you don't have an index on hcs.AdminObservation(TherapyAdmin_ID) the only option is to use the clustered index.
The following index might help your query, but will also create overhead on writes. You need to evaluate what's the best indexing option for your system.
CREATE INDEX IX_GiveAnAppropriateName ON hcs.AdminObservation( TherapyAdmin_ID);
December 6, 2016 at 12:25 pm
tried that still loves the clustered index scan
December 6, 2016 at 12:50 pm
Follow the indications on this article for further advice.
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
December 6, 2016 at 1:00 pm
It's extremely difficult to troubleshoot performance issues without an execution plan (preferably actual). Can you attach the plan as a .sqlplan file?
In the meantime, you might want to try using an EXISTS instead of IN.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
December 6, 2016 at 1:27 pm
How many rows of data are you expecting to be returned with query posted?
SQL Server may be doing and clustered index scan (also known as a table scan) because it believes this to be the easiest way to get the data needed for this query.
December 6, 2016 at 1:32 pm
It returns 71 (reads 2.8 million in scan). Tried using non clustered on all fields in table in different variations, best I could get was a non clustered index scan on same fields with less reads but same data
December 6, 2016 at 1:34 pm
As was asked earlier, can you post the execution plan as a .sqlplan file.
My only other question is why the dynamic SQL for a query that doesn't need it?
December 6, 2016 at 1:54 pm
3rd party app no choice can't change code can change indexes
December 6, 2016 at 1:56 pm
Lynn Pettis (12/6/2016)
My only other question is why the dynamic SQL for a query that doesn't need it?
That's probably generated by an ORM.
December 6, 2016 at 1:58 pm
Post DDL for tables and indexes involved. Also post the actual execution plan. In a previous post, I shared an article on how to get all that.
December 7, 2016 at 12:33 pm
As already said, the DDL and plan is really needed to give the best answer.
However, the index suggested by Luis is guaranteed to require a clustered index lookup to satisfy your query, which could easily cause the optimiser to ignore it due to the cost of the lookup.
A covering index has a better chance of being used:
CREATE INDEX IX_GiveAnAppropriateName ON hcs.AdminObservation( TherapyAdmin_ID) INCLUDE(VisitObservation_ID);
However, even with what seems to be the best index possible, if the optimiser thinks that just about every extent for the table will be accessed by your query then a clustered index scan may still be the best performing access path.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
December 7, 2016 at 12:36 pm
What type of access are you getting for PatientOrder and TherapyAdmin? This can affect how the optimiser might access AdminObservation. Please post the plan.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
December 7, 2016 at 12:37 pm
Duh missed the obvious the first table only has 2 columns which are part of the clustered index time for a captain obvious
December 7, 2016 at 12:52 pm
EdVassie (12/7/2016)
As already said, the DDL and plan is really needed to give the best answer.However, the index suggested by Luis is guaranteed to require a clustered index lookup to satisfy your query, which could easily cause the optimiser to ignore it due to the cost of the lookup.
A covering index has a better chance of being used:
CREATE INDEX IX_GiveAnAppropriateName ON hcs.AdminObservation( TherapyAdmin_ID) INCLUDE(VisitObservation_ID);
However, even with what seems to be the best index possible, if the optimiser thinks that just about every extent for the table will be accessed by your query then a clustered index scan may still be the best performing access path.
Actually, you don't need to make it a covering index. The nonclustered indexes include the clustered index key on them. Here's a demonstration that there's no index lookup on AdminObservation with the index I proposed.
CREATE TABLE AdminObservation(
VisitObservation_ID int,
TherapyAdmin_ID int,
DateColumn datetime
);
CREATE CLUSTERED INDEX CI_AdminObservation ON AdminObservation( VisitObservation_ID);
INSERT INTO AdminObservation
SELECT TOP(2000000)
ISNULL(ABS(CHECKSUM(NEWID())) % 100000, 0),
ISNULL(ABS(CHECKSUM(NEWID())) % 100000, 0),
CAST( '2010' AS datetime) + RAND(CHECKSUM(NEWID()))*10000
FROM sys.all_columns, sys.all_columns b;
CREATE TABLE TherapyAdmin(
TherapyAdmin_ID int
);
INSERT INTO TherapyAdmin
SELECT TOP(100) TherapyAdmin_ID
FROM AdminObservation
ORDER BY NEWID();
CREATE CLUSTERED INDEX CI_TherapyAdmin ON TherapyAdmin( TherapyAdmin_ID);
SET STATISTICS XML ON;
select TherapyAdmin_ID, VisitObservation_ID, 1 as RowVersionNumber from AdminObservation
where TherapyAdmin_ID in (select TherapyAdmin_ID from TherapyAdmin )
order by VisitObservation_ID;
SET STATISTICS XML OFF;
CREATE INDEX IX_GiveAnAppropriateName ON AdminObservation( TherapyAdmin_ID);
SET STATISTICS XML ON;
select TherapyAdmin_ID, VisitObservation_ID, 1 as RowVersionNumber from AdminObservation
where TherapyAdmin_ID in (select TherapyAdmin_ID from TherapyAdmin )
order by VisitObservation_ID;
SET STATISTICS XML OFF;
DROP TABLE TherapyAdmin, AdminObservation;
Viewing 15 posts - 1 through 15 (of 30 total)
You must be logged in to reply to this topic. Login to reply