How to replace clustered index scan with a non-clustered index seek or clustered index seek?
Below is my create table script:-
CREATE TABLE [dbo].[PatientCharts]( [PatientChartId] [uniqueidentifier] ROWGUIDCOL NOT NULL, [FacilityId] [uniqueidentifier] NOT NULL, [VisitNumber] [varchar](200) NOT NULL, [MRNNumber] [varchar](100) NULL, [TimeIn] [time](7) NULL, [TimeOut] [time](7) NULL, [DateOfService] [date] NULL, [DateOut] [date] NULL),
I have one clustered index on PatientChartId and two non-clustered index on VisitNumber and MRNNumber. This table has millions of records.
The following query is doing a clustered index scan:-
SELECT * FROM dbo.PatientCharts INNER JOIN ( SELECT FacilityID FROM Facilities WHERE RemoteClientDB IN ( SELECT SiteID FROM RemoteClient WITH ( NOLOCK ) WHERE Code = 'IN-ESXI-EDISC14' ) ) AS Filter ON dbo.PatientCharts.FacilityId = Filter.FacilityID
This clustered index scan is taking a lot of time in production because of data volume.
The execution plan is :-
I have even tried adding a Non-clusted index on FacilityID and including PatientChartID but still the same execution plan.
I am doing DBCC FREEPROCCACHE everytime to instruct sql server to use a new plan every time.
Is there anything else which I should do to prevent clusteredindex scan ?
The clustered scan will occur since there is no index to support your query. Even if you index FacilityID and PatientChartID you are still potentially asking for sufficient amounts of data to scan due to going past the tipping point (Google Kimberly Tripp Tipping Point)
There is no easy way to say the next part, but for a system with millions of records but such a trivial query causing you a problem, you are going to have to get a lot more aware about indexing in general and how the SQL plan engine behaves. I would recommend Kalen Delany's SQL Internals and if you search on here for book recommendations, there are questions with a number of good solid recommendations.
Have you tried implementing this as a straight query with inner joins instead of using subqueries for each step?
I would be happy to take a look at the resulting execution plan if you change the query to the following form:
select * from patientschart... inner join facilities... inner join remoteclientdb.... where...
I think the optimizer will choose the correct indexes once you get rid of the subqueries. Try it and share the execution plan.
Also, on another note, do you need all fields in the resultset? You might benefit by switching to specific columns instead of * in the select list.
I hope this helps.
As Andrew mentioned, your clustered index isn't helping you or hurting you here- if you didn't have the clustered index, you'd see a table scan instead (which I assure you would be no more fun than the clustered index scan).
Assuming that this is the most important query on this table, I'd say that you should change the table design so that the clustered index is on FacilityID instead. That would be dramatically faster.
I think you should avoid doing a SELECT * and specify the coulmns which you require . Then you can plan your indexes on the execution plan you get