Strange Performance Issue

Question

Strange Performance Issue

Viewing 3 posts - 16 through 17 (of 17 total)

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

Super Cat SSCertifiable Points: 7332 More actions · Answer 1

Sounds like it may have cached a bad execution plan.

Jeff Moden SSC Guru Points: 1003872 More actions · Answer 2

ChrisM@Work (6/14/2016)
davidlester.home (6/14/2016)
I did eventually get this to work, though it makes little sense. It turns out that dropping all the indexes on the table was the answer. With indexes the query timed out at 12 hours, without indexes, the query completed in 50 seconds.
The estimated execution plans were not helping in this one, as it showed it was using the indexes and joins optimally.
It's disappointing that you didn't see fit to posting up execution plans (actual or estimated), a choice which defeated opportunities for some very clever folks watching this thread to make a real difference. There's a great deal more to execution plan analysis than meets the eye. Your solution is not only drastic, it almost certainly masks the real issue which may have presented an otherwise interesting challenge - and which remains unsolved.

I absolutely agree. It is possible that someone sees an INDEX SEEK and thinks that's optimal without looking to see if the SEEK was executed many thousands of times or not, which can be devastatingly slow. They also don't realize that dropping all indexes just killed performance for other queries and dropping a clustered index just caused an explosion on their log file. While the problem was urgent, some very well meaning folks just move too bloody fast and eventually find out what their knee tastes like. At the very least, a real "large table" learning opportunity was missed.

--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)

curious_sqldba SSC-Dedicated Points: 36494 More actions · Answer 3

davidlester.home (4/25/2016)
So I have an unusual performance tuning issue. We have a table, which has 185 million rows in it. There is a query we have that is grouping by 3 columns and summing on one. For 6 months this code has been working fine and takes a couple of minutes to run.
In the last week it suddenly jumped to 11 hours for this single simple query.
So I went through the whole exercise of performance tuning and such, and could not get it to improve. This is where it became strange, in an attempt to see if it was simply the amount of data that was the problem, I copied the table the query runs off to a new table in a new database, to run some tests. However, the newly copied table, with no indexes when the query was run against it completed in under a minute.
So same query, same data, just copied into a new table, and the query performs great. While any thing I try on the existing table to improve performance fails to improve the execution time.
Anyone have any suggestions as to what might be going on here?

Obviously there is lots of good recommendations by others . Have you looked at the stats? Did you try updating the stats with good sample amount or may be full scan and then ran the query?