CODEWITHSUNDEEP
sqlsql-serverperformance
Bob Probst
Bob Probst

Reputation: 9641

Increasing rows returned by "select top" suddently makes the query incredibly slower

A load that has been running in about 2 minutes suddenly turned into a 90 minute run before being manually cancelled.

It's a simple shadow query:

select fields
into shadow_table
from table
where date = '8/23/2011'

date has a non-clustered index on it.

If I change the query to select

Our server team shows a lot of self blocking while it runs.

Can anyone suggest possible bottlenecks to look at?

Upvotes: 2

Views: 460

Answers (4)

Derek
Derek

Reputation: 23248

Also make sure you have your fields as part of the include of your nonclustered index. If you don't, you're going to have to back to the table using an RID lookup to get that data.

create nonclustered index ix_whatever on YourTable (date) 
include (field1, field2, ...)

Upvotes: 0

Remus Rusanu
Remus Rusanu

Reputation: 294317

Follow the proven Waits and Queues methodology to identify the bottleneck.

When a request is running parallel query the proper way to analyze blockage is to dive at the subtask level and see what is blocking each of the sub tasks. One should never stop at CXPACKET as wait type, or 'self block' as an explanation.

select w.last_wait_type, 
    wt.wait_type, 
    wt.resource_description, 
    wt.blocking_session_id, 
    t.pending_io_count, 
    r.* 
from sys.dm_os_tasks t
left join sys.dm_os_waiting_tasks wt on wt.waiting_task_address = t.task_address
join sys.dm_os_workers w on t.worker_address = w.worker_address
join sys.dm_exec_requests r on t.session_id = r.session_id
where r.session_id = <queryspid>;

Upvotes: 2

dkretz
dkretz

Reputation: 37655

If it's what it seems - an archival query - on records that won't be updated while it's running, you can turn off blocking entirely. Other queries that need integrity but use your records won't be affected - they manage their own locking.

Upvotes: 0

JNK
JNK

Reputation: 65177

Out of date stats.

Self-blocking only occurs with parallelism, and super long parallel runs (compared to norms) ordinarily means out-of-date stats. It could also be a change in cardinality in the data.

Step 1 should be running an UPDATE STATISTICS WITH FULLSCAN on your source table.

Upvotes: 7

Related Questions