Query Suddenly Performing Badly

Question

Query Suddenly Performing Badly

Viewing 2 posts - 31 through 31 (of 31 total)

You must be logged in to reply to this topic. Login to reply

lmarkum SSCertifiable Points: 7989 More actions · Answer 1

TheSQLGuru (8/5/2016)
lmarkum (8/5/2016)
Jeff Moden (8/5/2016)
lmarkum (8/5/2016)
I forgot to mention that in these plans there will be a missing index recommendation on the NewsletterHistory. I don't understand the recommendation because the only place I can find in the plan for that table there is already an index seek on a non-clustered index. I didn't want to create what would essentially have been a duplicate index.
Look at the properties of that seek and see how many times it's executed.
247 times.
Mssing Index Recommendation -
USE [MortgageReturns4]
GO
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[NewsletterHistory] ([NewsletterID],[ContactRecordID])
INCLUDE ([DateSent])
GO
Index it is using -
CREATE INDEX [IX_NewsletterHistory_NewsletterId_CustomerId_ContactRecordID_StatusID_INCL] ON [dbo].[NewsletterHistory]
( [NewsletterID], [CustomerID], [ContactRecordID], [StatusID] )
INCLUDE ( [DateSent]) WITH (FILLFACTOR=100);
The difference here is essentially the order of where ContactRecordId appears in the index.
So you can't seek on your index for the columns the MIS recommends. CustomerID is "in the way".

Actually, Kevin, that was what I was saying at the beginning that I did not understand. There IS a seek on the existing index already. Thus, I did not understand why SQL Server wanted this new index.

I created the missing index request and performance immediately improved. I am going to find other queries using the index this query was previously using and change the order of CustomerId and ContactRecordId and see what the performance is like.

I did realize that because of the way I was using Ola's scripts there was no statistics maintenance on the indexes that were created with the NORECOMPUTE option set to ON. I have already corrected that by creating a job from Ola's scripts that just updates the stats. When I review the CommandLog table, in most cases the statistics update only took a few hundred milliseconds on the indexes, even on the large tables. A few hundred milliseconds compared to the average run time of most of our queries is nothing. Still, I know it is going to be better to update them in a job in the middle of the night than to let auto update stats do it for those cases.

lmarkum SSCertifiable Points: 7989 More actions · Answer 2

Jeff Moden (8/5/2016)
lmarkum (8/5/2016)
Jeff Moden (8/5/2016)
lmarkum (8/5/2016)
I forgot to mention that in these plans there will be a missing index recommendation on the NewsletterHistory. I don't understand the recommendation because the only place I can find in the plan for that table there is already an index seek on a non-clustered index. I didn't want to create what would essentially have been a duplicate index.
Look at the properties of that seek and see how many times it's executed.
247 times.
Mssing Index Recommendation -
USE [MortgageReturns4]
GO
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[NewsletterHistory] ([NewsletterID],[ContactRecordID])
INCLUDE ([DateSent])
GO
Index it is using -
CREATE INDEX [IX_NewsletterHistory_NewsletterId_CustomerId_ContactRecordID_StatusID_INCL] ON [dbo].[NewsletterHistory]
( [NewsletterID], [CustomerID], [ContactRecordID], [StatusID] )
INCLUDE ( [DateSent]) WITH (FILLFACTOR=100);
The difference here is essentially the order of where ContactRecordId appears in the index.
Unless it's an absolutely huge table, I'd try the recommended index just to see if it were a reasonable recommendation for the query you're having problems with. If it's not, drop it and make a better human guess.
If it does help a lot, remember it but still drop it. Then find the query(ies) that the existing index supports and check them. Then, change the order of that index and see if the queries still perform at least as well. If they do, then see if the query you're talking about also benefitted. If it does, you're done. If it doesn't, then you may actually need the extra index.

So, I have searched the cache several times this morning for queries using the previous index with CustomerId listed after NewsletterId. Nothing has turned up. When I look at index information with sp_BlitzIndex there have been no new Reads on that previous index in hours. However, the new index with NewsletterId, ContactRecordId has had a lot of activity. I am starting to wonder if all activity from the previous index has moved to this new one. There is now a new index recommendation to have CustomerId in an index by itself. Benefit value is only 35,000 and Esitmated Impact column shows only one use, so I can't see any point to adding that index.

Usage Stats

Reads: 4,514 (4,514 seek) Writes:1

Op Stats:

0 singleton lookups; 1,111,410 scans/seeks; 0 deletes; 0 updates;