I am using SQL Server 2008 SP2 and I'm facing an issue with a particular query.
It is taking 4 sec to 4 min in execution. Please suggest the possibilities.
There is no scope of query optimization. Indexes are also proper.
SELECT ALL X.*
,sd1.string_value "sd1_string_value"
,sd2.string_value "sd2_string_value"
,sd3.string_value "sd3_string_value"
,sd4.string_value "sd4_string_value"
,sd5.string_value "sd5_string_value"
,sd6.string_value "sd6_string_value"
,sd7.string_value "sd7_string_value"
,wi.r_object_id "wi_r_object_id"
,wi.r_act_def_id "wi_r_act_def_id"
,wi.r_workflow_id "wi_r_workflow_id"
FROM (
SELECT *
FROM (
SELECT ALL q.content_type q_content_type
,q.item_type q_item_type
,q.dependency_type q_dependency_type
,q.router_id q_router_id
,q.event q_event
,q.item_name q_item_name
,q.task_subject q_task_subject
,q.task_name q_task_name
,q.NAME q_name
,q.sent_by q_sent_by
,q.r_object_id q_r_object_id
,q.priority q_priority
,q.item_id q_item_id
,q.date_sent q_date_sent
,q.task_state q_task_state
,q.due_date q_due_date
,q.task_number q_task_number
,q.source_docbase q_source_docbase
,q.source_event q_source_event
,wf.process_id wf_process_id
,wf.r_object_id wf_r_object_id
,CAST(ROW_NUMBER() OVER (
ORDER BY q.date_sent DESC
,q.r_object_id ASC
) AS INT) AS dm_rnum
FROM dm_workflow_sp wf
,dmi_queue_item_sp q
WHERE (
((q.router_id = wf.r_object_id))
AND (
q.NAME IN (
N'Hemavathy Aneesh. Nair'
,N'al_nko1000abs_group'
,N'al_nmp1000abs_group'
,N'al_nmu1000abs_group'
,N'al_nub1000abs_group'
,N'al_nue1000abs_group'
,N'edms_user'
)
AND (wf.process_id = N'4b027d9a800077e8')
AND (
(q.delete_flag = 0)
AND q.dependency_type IN (
N'0'
,N'1'
,N'2'
,N'3'
,N'4'
,N'5'
,N'6'
)
)
)
)
) dm_inline_view_1
WHERE dm_rnum >= 1
AND dm_rnum <= 101
) X
LEFT JOIN (
SELECT ALL dmc_wfsd_element_string.workflow_id
,dmc_wfsd_element_string.object_name
,dmc_wfsd_element_string.r_object_id
,dmc_wfsd_element_string.process_id
,dmc_wfsd_element_string.string_value
FROM dmc_wfsd_element_string_sp dmc_wfsd_element_string
WHERE ((dmc_wfsd_element_string.object_name = N'dm_taskName'))
AND (
dmc_wfsd_element_string.i_has_folder = 1
AND dmc_wfsd_element_string.i_is_deleted = 0
)
) sd1 ON (sd1.workflow_id = X.q_router_id)
LEFT JOIN (
SELECT ALL dmc_wfsd_element_string.workflow_id
,dmc_wfsd_element_string.object_name
,dmc_wfsd_element_string.r_object_id
,dmc_wfsd_element_string.process_id
,dmc_wfsd_element_string.string_value
FROM dmc_wfsd_element_string_sp dmc_wfsd_element_string
WHERE ((dmc_wfsd_element_string.object_name = N'circel'))
AND (
dmc_wfsd_element_string.i_has_folder = 1
AND dmc_wfsd_element_string.i_is_deleted = 0
)
) sd2 ON (sd2.workflow_id = X.q_router_id)
LEFT JOIN (
SELECT ALL dmc_wfsd_element_string.workflow_id
,dmc_wfsd_element_string.object_name
,dmc_wfsd_element_string.r_object_id
,dmc_wfsd_element_string.process_id
,dmc_wfsd_element_string.string_value
FROM dmc_wfsd_element_string_sp dmc_wfsd_element_string
WHERE ((dmc_wfsd_element_string.object_name = N'VendorCode'))
AND (
dmc_wfsd_element_string.i_has_folder = 1
AND dmc_wfsd_element_string.i_is_deleted = 0
)
) sd3 ON (sd3.workflow_id = X.q_router_id)
LEFT JOIN (
SELECT ALL dmc_wfsd_element_string.workflow_id
,dmc_wfsd_element_string.object_name
,dmc_wfsd_element_string.r_object_id
,dmc_wfsd_element_string.process_id
,dmc_wfsd_element_string.string_value
FROM dmc_wfsd_element_string_sp dmc_wfsd_element_string
WHERE ((dmc_wfsd_element_string.object_name = N'VendorName'))
AND (
dmc_wfsd_element_string.i_has_folder = 1
AND dmc_wfsd_element_string.i_is_deleted = 0
)
) sd4 ON (sd4.workflow_id = X.q_router_id)
LEFT JOIN (
SELECT ALL dmc_wfsd_element_string.workflow_id
,dmc_wfsd_element_string.object_name
,dmc_wfsd_element_string.r_object_id
,dmc_wfsd_element_string.process_id
,dmc_wfsd_element_string.string_value
FROM dmc_wfsd_element_string_sp dmc_wfsd_element_string
WHERE ((dmc_wfsd_element_string.object_name = N'InvoiceNumber'))
AND (
dmc_wfsd_element_string.i_has_folder = 1
AND dmc_wfsd_element_string.i_is_deleted = 0
)
) sd5 ON (sd5.workflow_id = X.q_router_id)
LEFT JOIN (
SELECT ALL dmc_wfsd_element_string.workflow_id
,dmc_wfsd_element_string.object_name
,dmc_wfsd_element_string.r_object_id
,dmc_wfsd_element_string.process_id
,dmc_wfsd_element_string.string_value
FROM dmc_wfsd_element_string_sp dmc_wfsd_element_string
WHERE ((dmc_wfsd_element_string.object_name = N'InvoiceAmountCommaFormat'))
AND (
dmc_wfsd_element_string.i_has_folder = 1
AND dmc_wfsd_element_string.i_is_deleted = 0
)
) sd6 ON (sd6.workflow_id = X.q_router_id)
LEFT JOIN (
SELECT ALL dmc_wfsd_element_string.workflow_id
,dmc_wfsd_element_string.object_name
,dmc_wfsd_element_string.r_object_id
,dmc_wfsd_element_string.process_id
,dmc_wfsd_element_string.string_value
FROM dmc_wfsd_element_string_sp dmc_wfsd_element_string
WHERE ((dmc_wfsd_element_string.object_name = N'Parking_Doc_Id'))
AND (
dmc_wfsd_element_string.i_has_folder = 1
AND dmc_wfsd_element_string.i_is_deleted = 0
)
) sd7 ON (sd7.workflow_id = X.q_router_id)
LEFT JOIN dmi_workitem_sp wi ON (wi.r_queue_item_id = X.q_r_object_id)
ORDER BY X.q_date_sent DESC
,X.q_r_object_id ASC
Best Answer
The query taking a lot longer to run some times could be due to I/O or memory issues. If it is table (or index) scanning over a large amount of data and that data is sometimes in memory and sometimes has been pushed out by other data, then that could explain the speed difference of seconds to minutes: when the data is in RAM those scans will be CPU bound, when it is not it'll be IO bound. If that is the case than throwing more memory at the database server might well help, though removing the need to scan large amounts of index/heap by query/index optimisation will help more (especially as the data grows over time).
Another possibility is that high activity on the server at times could also explain the difference, either due to physical resource contention (CPU or IO) or locking issues (another long running query holding locks on data this query needs).