Query Suddenly Performing Badly

Question

Query Suddenly Performing Badly

lmarkum

SSCertifiable

Points: 7989
More actions
August 5, 2016 at 7:50 am

#400528

We have spotted a query that has suddenly gone rogue. Over the last 5 days it went from running in under 2 seconds to taking over 12 seconds. I can see from SolarWinds Database Performance Analyzer that that this started suddenly on 8/1. The number of rows processed daily jumped from around 75,000 to now over 125,000 yesterday. Number of executions has stayed steady at around 4600 per day. Physical reads have gradually plummeted from around 500,000 per day to just under 65,000 yesterday while logical reads has skyrocketed from just under 500,000 to 1.5 million yesterday.
There were no index changes on 7/31 or early on 8/1. The procedure itself was not changed. I did think about whether there were simply suddenly more rows to process so I looked at the tables involved. I am not daily tracking table growth so I had to compare row counts from about two months ago when I restored a backup of prod to Dev. There are some tables that have grown by a few million rows. I looked at stats on those tables. Some stats have not updated in 2-3 months. I have trace flag 2371 enabled to address the issue of auto update stats not updating soon enough for large tables. I manually updated the stats for a particular one that was old but it made no different in the query plan and the estimated and actual rows information did not change.
I am not particularly adept at Profiler or XEvents, but I used the Query Wait Statistic template to make an XEvents session to filter on one of the sessions running the rogue query.
Any ideas would be great.

Viewing 15 posts - 1 through 15 (of 31 total)

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

Eirikur Eiriksson SSC Guru Points: 182916 More actions · Answer 1

Quick question, is it the same execution plan or is there a new one? DPA should have this information if you run analysis on the procedure.

😎

lmarkum SSCertifiable Points: 7989 More actions · Answer 2

There are a number of different query plans beginning on 7/1. Viewing in SQL Sentry Plan Explorer I can see where the increase in rows is coming from fairly easily, so that is good. I have looked at one new plan so far versus the previous, most often used plan. The only difference is that the new plan replaces a Hash Match Right Outer Join with a Nested Loops operator. I'm assuming that happened because the row counts between the input tables are equal now. I will continue to look at the different plans in place. I did some index changes last night and eliminated some key lookups so that now the data is being gathered in an Index Seek that was already occurring. However, that has not helped the query.

When I look at Activity monitor I noticed that a session running the rogue proc intermittently had a wait type of EXECUTION_PIPE_EVENT_INTERNAL. When I use a query from Paul Randall to do a diff on wait stats in 30 second intervals that is currently the most consistent wait type. Because I was not tracking this query before I don't know if that was the case prior to 8/1. I also can find almost no helpful information on that wait type to even know what it means. The BOL definition does not really help me.

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 3

Can you post up the two plans you spoke of for us to review please?

The switch to nested loop is likely the cause of the vastly increased logical IOs. Spinning those for large numbers of rows can be nasty.

Is there any variety in the input parameters for the sproc?

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

lmarkum SSCertifiable Points: 7989 More actions · Answer 4

I have attached the most often used plan prior to 8/1. There are three new plans that show up on 8/1. I attached the one I previously spoke of. I can look at the others and attach them as well. In the case of this plan there are only 111 rows from each input going to the Nested Loop. The change involves a temp table called #Authorized Users.

lmarkum SSCertifiable Points: 7989 More actions · Answer 5

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.

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 6

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.

The Missing Indexes Subsystem (and Database Tuning Advisor) doesn't care what you currently have. It will overlap and in other ways hose up your indexing UNBELIEVABLY BADLY!!! :w00t:

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 7

1) You are at the mercy of how many rows are in the temp table referenced

2) CONVERT and CONVERT_IMPLICITs potentially affecting plan choice. Mismatched ad hocs, variables, column data types anywhere should be resolved.

3) Got ACTUAL execution plans by chance? A very key factor here is the difference between estimated and actual rows.

4) At least one parallel plan generation blocker.

5) This should result in a scalar and at first blush it seems like it would be easy to treat it as such and simplify the query:

CROSS JOIN (SELECT ContactRecordId FROM ContactRecords WHERE ContactRecordId = @AntiSniffContactRecordID) as C

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 8

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.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

drew.allen SSC Guru Points: 76996 More actions · Answer 9

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.

Does that existing index include the INCLUDE column(s) from the missing index? That could be why it's suggesting that index even though a similar one already exists.

I also noticed that you have the following:

MAX(CASE WHEN au.ApproveNewsletters = 1 THEN 1 WHEN au.ApproveNewsletters = 0 THEN 0 END)

Assuming that ApproveNewsletters is a pseudo-Boolean and is thus limited to 0 or 1, why don't you just use MAX(au.ApproveNewsletters)? Those tables are probably small enough that it won't have a profound impact on the performance, but there might be a small gain by doing this.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

lmarkum SSCertifiable Points: 7989 More actions · Answer 10

Thanks for the feedback. Here is an actual plan. One thing I also see in the proc is filtering in a WHERE clause based on strings instead of looking for the corresponding integer. There are a couple of places where it looks for character strings that don't match the word 'Manual'.

lmarkum SSCertifiable Points: 7989 More actions · Answer 11

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.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 12

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.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 13

Also, you ARE rebuilding stats on the indexes for this table on a regular basis, correct?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

lmarkum SSCertifiable Points: 7989 More actions · Answer 14

I am using Ola Hallengren to handle the index and stats maintenance.