Strange Performance Issue

Question

Strange Performance Issue

davidlester.home

SSC Veteran

Points: 203
More actions
April 25, 2016 at 7:49 am

#325879

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?

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

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

Eric M Russell SSC Guru Points: 125521 More actions · Answer 1

.. So I went through the whole exercise of performance tuning and such, and could not get it to improve ..

So, have you compared the two execution plan: the plan for slow version against the original table versus the plan for the fast query against the copied table?

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

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

Very hard to give you any recommendations with out seeing the actual query and schema. First thing i would check if your stats are upto date. There could be other problems too.

davidlester.home SSC Veteran Points: 203 More actions · Answer 3

I have not been able to get an execution plan out of the query now that it is slow, 11+ hour run time is making it difficult to say the least to get an actual execution plan.

davidlester.home SSC Veteran Points: 203 More actions · Answer 4

While I can give the DDL for this, I don't think that this is a normal issue. I am good myself at performance tuning. It seems to be something unusual. I can't explain why simply making a copy of the source table with all of the data intact and pointing the query to that new table makes it work.

Ie..

Query on Table A - 11+ hours

Query on Copy of Table A - 1 minute.

Eric M Russell SSC Guru Points: 125521 More actions · Answer 5

davidlester.home (4/25/2016)
While I can give the DDL for this, I don't think that this is a normal issue. I am good myself at performance tuning. It seems to be something unusual. I can't explain why simply making a copy of the source table with all of the data intact and pointing the query to that new table makes it work.
Ie..
Query on Table A - 11+ hours
Query on Copy of Table A - 1 minute.

You can't explain, because you're not looking at the execution plans. The plan will tell you important things like: if an indexes is being used, scans versus seeks, number of rows returned from each operation, sorts, spools, the relative cost of each operation, etc. It may all boil down to outdated statistics, but without looking at the plan, in this case comparing two plans, then you have no way of knowing.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

tripleAxe SSCertifiable Points: 5706 More actions · Answer 6

Could it be index fragmentation? Or could it be some other process(es) using the table at the same time your query is running?

davidlester.home SSC Veteran Points: 203 More actions · Answer 7

I thought those things as well.

I have completely re-built the table and rebuilt the indexes, still have the performance issue.

I have also made sure that code was the only thing running on the server at the time.

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 8

Eric M Russell (4/25/2016)
davidlester.home (4/25/2016)
While I can give the DDL for this, I don't think that this is a normal issue. I am good myself at performance tuning. It seems to be something unusual. I can't explain why simply making a copy of the source table with all of the data intact and pointing the query to that new table makes it work.
Ie..
Query on Table A - 11+ hours
Query on Copy of Table A - 1 minute.
You can't explain, because you're not looking at the execution plans. The plan will tell you important things like: if an indexes is being used, scans versus seeks, number of rows returned from each operation, sorts, spools, the relative cost of each operation, etc. It may all boil down to outdated statistics, but without looking at the plan, in this case comparing two plans, then you have no way of knowing.

This.

Comparing the execution plans is the key. With the actual query and DDL of the tables/views involved, we might be able to make some educated guesses even without the query plans (still just guesses though, and might not be anything apparent from just that).

Without query plans, the query, and the data, it's all just guessing.

If you can grab the execution plans, preferably the actual execution plans, and save them as .sqlplan files and attach them to a post here, we'll be able to help out.

Cheers!

Sergiy SSC Guru Points: 110206 More actions · Answer 9

davidlester.home (4/25/2016)
I have not been able to get an execution plan out of the query now that it is slow, 11+ hour run time is making it difficult to say the least to get an actual execution plan.

Try this query:

SELECT TOP 10 total_worker_time/execution_count AS [Avg CPU Time], total_worker_time, DATEADD(s, total_worker_time/execution_count/1000, 0) AvgDuration, execution_count,

Plan_handle, query_plan , t.dbid, t.objectid, t.text

FROM sys.dm_exec_query_stats AS qs

CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle)

CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) t

WHERE t.text LIKE 'Beginning of your query %'

ORDER BY total_worker_time/execution_count DESC;

If your long running query is not yet "forgotten" by the server you should be able to get its plan.

_____________
Code for TallyGenerator

tripleAxe SSCertifiable Points: 5706 More actions · Answer 10

Is the table a heap or is there a clustered index on it? Same clustered index on your copy table? Is the copy table on the same server?

haridbakorrapati Grasshopper Points: 23 More actions · Answer 11

sorry not able to get answers...

plz.. ]] mantion whats is ans. and what is qus. guys

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

haridbakorrapati (6/14/2016)
sorry not able to get answers...
plz.. ]] mantion whats is ans. and what is qus. guys

There's not enough information to help. It could be anything and without actual execution plans, etc., it's not possible to identify the cause... only make suggestions.

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

davidlester.home SSC Veteran Points: 203 More actions · Answer 13

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.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 14

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.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden