September 28, 2011 at 8:09 am
I'm working on and have "completed" a process to purge old data from a database. Last Friday when I left the process was taking about 20 minutes (49 tables affected). The last table which has 49 FK's defined against it that must be checked has gone from taking ~140 seconds and ~11,759,891 reads according to a trace to taking ~1750 seconds and ~40,439,946. I have checked index fragmentation (not an issue) and statistics, which are good. The query plan had not changed either, at least not that I could see.
When I first started this project this table was taking about ~1000 seconds and 161,204,100 reads, which I got down to the 140 seconds by adding recommended indexes on the referencing tables. All these indexes still exist.
Any ideas where else I can look or what I can do? There are still a few indexes that could be added, but why the major change?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 28, 2011 at 8:11 am
What waits is the query incurring?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 28, 2011 at 8:36 am
GilaMonster (9/28/2011)
What waits is the query incurring?
I should be ashamed that I posted in the 2008 forums when the query is running against 2005. My excuse is that the process is part of an SSIS 2008 package.
I thought I might get that question. I don't know. Is there a way to correlate waits to a query? I assume I'll have to run some queries against the DMV's while this specific task is running to find this out.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 28, 2011 at 8:55 am
Jack Corbett (9/28/2011)
GilaMonster (9/28/2011)
What waits is the query incurring?I should be ashamed that I posted in the 2008 forums when the query is running against 2005. My excuse is that the process is part of an SSIS 2008 package.
Doesn't make the slightest difference to the question on waits, just means we can't use extended events to answer it.
I don't know. Is there a way to correlate waits to a query? I assume I'll have to run some queries against the DMV's while this specific task is running to find this out.
sys.dm_exec_requests, sys.dm_os_waiting_tasks. You'll have to poll them.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 28, 2011 at 8:59 am
GilaMonster (9/28/2011)
sys.dm_exec_requests, sys.dm_os_waiting_tasks. You'll have to poll them.
That's what I thought. I'll do that during the current run. It could be awhile before I have any good information.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 28, 2011 at 11:29 am
Okay here are the wait stats gathered using this query running regularly during the process:
INSERT INTO #more_stats
(
session_id,
wait_duration_ms,
wait_type,
blocking_session_id,
resource_description,
program_name,
text,
dbid,
cpu_time,
memory_usage
)
SELECT
owt.session_id,
owt.wait_duration_ms,
owt.wait_type,
owt.blocking_session_id,
owt.resource_description,
es.program_name,
est.text,
est.dbid,
es.cpu_time,
es.memory_usage
FROM
sys.dm_os_waiting_tasks owt
INNER JOIN sys.dm_exec_sessions es
ON owt.session_id = es.session_id
INNER JOIN sys.dm_exec_requests er
ON es.session_id = er.session_id
OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) est
OUTER APPLY sys.dm_exec_query_plan(er.plan_handle) eqp
WHERE
es.is_user_process = 1 AND
ES.program_name = 'Program Name' ;
SELECT
SUM(MS.wait_duration_ms) AS wait_time_ms,
MS.session_id,
MS.wait_type,
MS.blocking_session_id,
MS.resource_description,
MS.program_name,
MS.text,
MS.dbid,
MS.cpu_time,
MS.memory_usage
FROM
#more_stats AS MS
GROUP By
MS.session_id,
MS.wait_type,
MS.blocking_session_id,
MS.resource_description,
MS.program_name,
MS.text,
MS.dbid,
MS.cpu_time,
MS.memory_usage
ORDER BY MS.text
Wait Types Total Wait TimeCount
PAGEIOLATCH_EX 11403 217
PAGEIOLATCH_SH 225080 2537
SLEEP_TASK 10063 1
SOS_SCHEDULER_YIELD 905 1
Grand Total 247451 2756
It looks like IO waits are the culprit. The wait times are in ms.
I'm also attaching the execution plan. I know it is suggesting an index, but this is how it was running in 1/10 of the time last Friday. I'm not averse to doing more indexing since FK's are usually a good choice for indexes anyway.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 28, 2011 at 11:42 am
Can you get me the actual plan please?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 28, 2011 at 12:13 pm
Okay, here's an actual plan where no actual work is needed because I have to run the entire process before this will run successfully.
I'll re-run the process to the point of that operator in SSIS and then run this in SSMS to get the actual plan.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 28, 2011 at 12:31 pm
Jack Corbett (9/28/2011)
I'll re-run the process to the point of that operator in SSIS and then run this in SSMS to get the actual plan.
I'll wait for that, because what I need are the row counts, and since no work was done in this one, the row counts are 0.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 28, 2011 at 12:35 pm
That's what I thought. It'll be awhile....
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 28, 2011 at 2:50 pm
Here's the actual execution plan. This is pretty much what it should look like. I am deleting from all the child tables prior to this so they should be 0 rows.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 28, 2011 at 3:18 pm
Your row estimation is off, right from the beginning.
In the index seek on the far right, estimated rows 1, actual 1000. That's having ripple effects the whole way through. I'm seeing estimated executions 1, actual executions 1000 on just about all the index seeks/table scans of the foreign keys
Try forcing a statistics update (preferably fullscan) before you start, and run the whole process (with real data) again, see if it makes any difference to plan or performance.
Also, if you know that all the child rows are gone, can you not disable all the fk constraints and re-enable them (with CHECK) afterwards? Only if there's no concurrent access that could violate them though
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 28, 2011 at 4:03 pm
I noticed the row estimations at the beginning. Stats were updated today, so I don't know why they'd be off. I'll look at doing the update before to see if it helps.
I proposed disabling and re-enabling (with check) the FK's but the boss didn't like the idea since there is the possibility of concurrent processes. That takes the delete to sub-second, because I tested it.
As I think about it, I bet there is an statistics update happening that is not a Full Scan and it might be causing the problem. This has happened after the weekend one other time.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 28, 2011 at 4:26 pm
Jack Corbett (9/28/2011)
I proposed disabling and re-enabling (with check) the FK's but the boss didn't like the idea since there is the possibility of concurrent processes. That takes the delete to sub-second, because I tested it.
What you can do is run the ALTER TABLE (to drop the constraints) and the delete within a transaction, along with the ALTER TABLE to recreate them. The ALTER TABLE will take a Sch-M lock and nothing, but nothing will be able to access the table at all until the transaction commits. If it's sub-second, then that may be acceptable to have the table unavailable for that small amount of time.
If the re-enable with check take time, then enable with nocheck within the transaction (re-enabling the constraints as untrusted) and then check it.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply