SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Index Selection and Overreading of Rows


Index Selection and Overreading of Rows

Author
Message
michael.cole 47030
michael.cole 47030
SSC Veteran
SSC Veteran (256 reputation)SSC Veteran (256 reputation)SSC Veteran (256 reputation)SSC Veteran (256 reputation)SSC Veteran (256 reputation)SSC Veteran (256 reputation)SSC Veteran (256 reputation)SSC Veteran (256 reputation)

Group: General Forum Members
Points: 256 Visits: 494
Firstly, this is actually being run on Azure, but I couldn't find an Azure TSQL group.

I have a rather complex query that is being run - this question concerns only a smaill part of that query, which is

FROM common.contactRoleConnection
INNER JOIN [common].contactRole AS RDO ON RDO.gidContactRoleId IN ( contactRoleConnection.gidContactRoleIdFrom, contactRoleConnection.gidContactRoleIdTo )

WHERE contactRoleConnection.gidListItemIdConnectionStatus = 'C80367B5-45A4-49D1-A027-EE0C03D929BD'
AND contactRoleConnection.chrRecordStatus = 'A'


SELECT COUNT(*)
FROM common.contactRoleConnection
returns 40292
SELECT COUNT(*)
FROM common.contactRoleConnection
WHERE gidListItemIdConnectionStatus = 'C80367B5-45A4-49D1-A027-EE0C03D929BD'
AND contactRoleConnection.chrRecordStatus = 'A';
returns 37732

There are three relevent indexes on this table - they are: -

/****** Object: Index [IX_contactRoleConnection_gidContactRoleIdFrom_gidListItemIdConnectionStatus_chrRecordStatus_gidContactRoleIdTo] Script Date: 8/02/2018 2:38:54 PM ******/
CREATE NONCLUSTERED INDEX [IX_contactRoleConnection_gidContactRoleIdFrom_gidListItemIdConnectionStatus_chrRecordStatus_gidContactRoleIdTo] ON [common].[contactRoleConnection]
(
[gidContactRoleIdFrom] ASC,
[gidListItemIdConnectionStatus] ASC,
[chrRecordStatus] ASC,
[gidContactRoleIdTo] ASC
)
INCLUDE ( [gidContactRoleConnectionId]) WITH (STATISTICS_NORECOMPUTE = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
GO



/****** Object: Index [IX_contactRoleConnection_gidContactRoleIdTo_gidListItemIdConnectionStatus_chrRecordStatus_gidContactRoleIdFrom] Script Date: 8/02/2018 2:38:14 PM ******/
CREATE NONCLUSTERED INDEX [IX_contactRoleConnection_gidContactRoleIdTo_gidListItemIdConnectionStatus_chrRecordStatus_gidContactRoleIdFrom] ON [common].[contactRoleConnection]
(
[gidContactRoleIdTo] ASC,
[gidListItemIdConnectionStatus] ASC,
[chrRecordStatus] ASC,
[gidContactRoleIdFrom] ASC
)
INCLUDE ( [gidContactRoleConnectionId]) WITH (STATISTICS_NORECOMPUTE = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
GO

/****** Object: Index [IX_contactRoleConnection_gidListItemIdConnectionStatus_chrRecordStatus] Script Date: 8/02/2018 2:58:54 PM ******/
CREATE NONCLUSTERED INDEX [IX_contactRoleConnection_gidListItemIdConnectionStatus_chrRecordStatus] ON [common].[contactRoleConnection]
(
[gidListItemIdConnectionStatus] ASC,
[chrRecordStatus] ASC
)
INCLUDE ( [gidContactRoleConnectionId],
[gidContactRoleIdFrom],
[gidContactRoleIdTo]) WITH (STATISTICS_NORECOMPUTE = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
GO



If I run the following query,
SELECT gidContactRoleIdFrom,
gidContactRoleIdTo
FROM common.contactRoleConnection
WHERE gidListItemIdConnectionStatus = 'C80367B5-45A4-49D1-A027-EE0C03D929BD'
AND contactRoleConnection.chrRecordStatus = 'A';

It will use the last index to return the records, with the stats as

(Results from SQL Sentry Plan Exporer - sorry for the image, but it seemed the best way to get all details across.)

If, however, I run the much more complex query , then most of the time, instead of using the third index, it will use the first two indexes, concatinate and then distinct sort the results and then inner join to the contactRole table. This is fine, because it is virtually instantanious, so I have no complaints about this. I say mostly, because occasionally, for no conceivable reason that I can see, it will switch to using the third index, which is probably what it should use because then it does not have to concatinate or distinct sort. The problem then is that it seems to do an enourmous amount of reads, as per

For some reason, it seems to be doing the read that it should do, over 21000 times. And I have no idea why. And this blows the query time out to about 12 minutes.
And then, again for no discernable reason, at some point in time it will switch back to using the dual index seek with concatination and distinct sort, and the query time will return to a couple of seconds. Mainly 'cos it only does that process once.
If there is any further information that I should provide, let mwe know (apart from full schemas, as it is rather complex), but at this point, I'm still not fully sure what the question should be, apart from what the hell is going on, and what should I do?
michael.cole 47030
michael.cole 47030
SSC Veteran
SSC Veteran (256 reputation)SSC Veteran (256 reputation)SSC Veteran (256 reputation)SSC Veteran (256 reputation)SSC Veteran (256 reputation)SSC Veteran (256 reputation)SSC Veteran (256 reputation)SSC Veteran (256 reputation)

Group: General Forum Members
Points: 256 Visits: 494
Actually folks, don't worry about this one. I didn't figure out why it was happenning, but I managed to get around it by splitting the complex query up into segments and using temporary tables.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search