April 25, 2016 at 7:49 am
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?
April 25, 2016 at 7:57 am
.. 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
April 25, 2016 at 7:57 am
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.
April 25, 2016 at 8:05 am
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.
April 25, 2016 at 8:08 am
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.
April 25, 2016 at 9:17 am
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
April 25, 2016 at 9:26 am
Could it be index fragmentation? Or could it be some other process(es) using the table at the same time your query is running?
April 25, 2016 at 9:29 am
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.
April 25, 2016 at 9:36 am
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!
April 25, 2016 at 5:40 pm
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
April 26, 2016 at 3:15 am
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?
June 14, 2016 at 12:32 am
sorry not able to get answers...
plz.. ]] mantion whats is ans. and what is qus. guys
June 14, 2016 at 6:18 am
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
Change is inevitable... Change for the better is not.
June 14, 2016 at 6:24 am
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.
June 14, 2016 at 6:53 am
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.
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
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply