August 5, 2016 at 7:50 am
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.
August 5, 2016 at 8:01 am
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.
😎
August 5, 2016 at 8:37 am
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.
August 5, 2016 at 8:49 am
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
August 5, 2016 at 9:10 am
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.
August 5, 2016 at 9:36 am
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.
August 5, 2016 at 10:33 am
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
August 5, 2016 at 10:45 am
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
August 5, 2016 at 11:18 am
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
August 5, 2016 at 11:20 am
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
August 5, 2016 at 11:44 am
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'.
August 5, 2016 at 12:03 pm
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.
August 5, 2016 at 12:18 pm
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
August 5, 2016 at 12:19 pm
Also, you ARE rebuilding stats on the indexes for this table on a regular basis, correct?
--Jeff Moden
August 5, 2016 at 12:22 pm
I am using Ola Hallengren to handle the index and stats maintenance.
Viewing 15 posts - 1 through 15 (of 31 total)
You must be logged in to reply to this topic. Login to reply