February 12, 2014 at 4:45 am
Hi,
As below query order by - sorting cost is 78%, pls. suggestion me how to reduce the cost? attached exec.plan here.
select * from BC_ArchiveJobs where JobStatus=0 and [State]=0 and Appl_Entity like 'OLTASK%' order by priority desc , jobdate asc
thanks
ananda
February 12, 2014 at 5:07 am
The cost figure is a relative number.
If you want your sorting to be faster - create an index.
February 12, 2014 at 5:36 am
I'm going to ask the same question I ask every time you post a question like this...
Is that query a problem? Is that operator the problem?
Costs are relative, costs MUST add to 100% within a plan. Hence, just noting that a cost is 96% says nothing as to whether or not that operator or even that query is a problem.
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
February 12, 2014 at 6:14 am
I am checking with profiler, this paricualr Query tooks duration 30 sec for completion at every execution, and CPU is 0 seconds..
February 12, 2014 at 6:21 am
can you post the ACTUAL execution plan? there's gotta be a difference between estimated and actual to explain such a huge difference.
the estimated plan thinks it will return one row, and an estimated subplan cost of a measly 0.014. that's a trivial cost.
if you can post the actual, we might see enough details to help.
Lowell
February 12, 2014 at 6:26 am
ananda.murugesan (2/12/2014)
I am checking with profiler, this paricualr Query tooks duration 30 sec for completion at every execution, and CPU is 0 seconds..
How many records your query returns? It may take no CPU time to execute, but a while to get data over to you...
February 12, 2014 at 7:28 am
ananda.murugesan (2/12/2014)
I am checking with profiler, this paricualr Query tooks duration 30 sec for completion at every execution, and CPU is 0 seconds..
That sounds more like a blocking problem, or that the query was waiting for some resource. If CPU is 0 seconds, then it spent almost no time executing and trying to tune the execution of the query may well be a waste
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
February 12, 2014 at 9:13 am
Assuming there's more to this query than in the plan you posted, to get improved sort performance, the best mechanism is eliminating the sort entirely from the plan. This is done by having the index that retrieves the data in the same order as the sort so that it can retrieve the data in sort order.
But, you're pulling six columns and filtering on two columns other than the columns in the ORDER BY statement. So, it's not likely you'll be able to make that work. Instead you'd need to add a second index for the sort columns, but then you'd need to do a lookup to get the other data out of the database which could end up making the performance worse.
But, I'm with the other posters. The evident rows returned by this query based on the cardinality estimates in the plan is extremely low. The actual rows returned is ZERO. Why tune this query?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 13, 2014 at 3:36 am
Due to blcoking another statement, so that query was taking too much duration. maunally executed in SSMS also took 30 min during the blocking happening. attached Actual exe.plan for blocking query. Pls. suggestion me what could be issues? for aviod the blocking will asking dev.team to assign NOLOCK hint in blocking statment.
February 13, 2014 at 3:43 am
ananda.murugesan (2/13/2014)
for aviod the blocking will asking dev.team to assign NOLOCK hint in blocking statment.
It'll avoid blocking. It'll also potentially return incorrect data. Do your users like getting reports that are intermittently incorrect? Most don't.
The query is operating on a couple thousand rows, it has a CPU time of 0 (from your earlier post), the sorts are 1% costs, from that it does not look like a performance problem itself, it looks more like it was forced to wait for resources (locks, latches, memory, etc) and was slow as a result of that. Is that the case?
The only thing in that query that looks like it could perhaps need changing is this: where upper(ss.SS_ID) = '75E1F2FC-0227-4337-9668-EF346B3D8B6D'
Unless this is a case-sensitive database, that UPPER is a waste of processing time.
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
February 13, 2014 at 3:53 am
The fact that you have four clustered index scans and a heap scan should be a bigger concern than sorting cost. And, what the heck, that's a completely different execution plan from the first one.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 13, 2014 at 3:59 am
Grant Fritchey (2/13/2014)
The fact that you have four clustered index scans and a heap scan should be a bigger concern than sorting cost. And, what the heck, that's a completely different execution plan from the first one.
They're tables joined with no filters (no where clause), could be that the clustered index scans are the most efficient (alternative would be one scan, one seek and a nested loop join instead of the merge, not sure that would be better)
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
February 13, 2014 at 4:36 am
Thanks Gila, as you suggested no need to assign the NOLOCK hint at statement level, it cause of incorrect data will be display in reporting.
Yes. Second one is another blocking query of actual exec.plan due to this blocking statement pervious query got too much duration.
Blocking happened during generate lots of shift reports at end of the shifts, once completed the reports then all transaction will become a normal state
During blocked transaction and another transaction Query have been waited as below resource
wait_type: LCK_M_S
Lock_Request: S (shared locks)
I noted, at every reporting SPs, Developers mentioned READ UNCOMITTED ISOLATION LEVEL
READ UNCOMMITTED level do not issue shared locks to prevent other transactions from modifying data read by the current transaction.
Can I suggest to developer to change the SNAPSHOT ISOLATION LEVEL in all SPs and transactions reading data do not block other transactions from writing data.
Thanks
ananda
February 13, 2014 at 5:20 am
ananda.murugesan (2/13/2014)
Yes. Second one is another blocking query of actual exec.plan due to this blocking statement pervious query got too much duration.
Make sure you're tuning the query that is causing the blocking, not one that is being blocked.
I noted, at every reporting SPs, Developers mentioned READ UNCOMITTED ISOLATION LEVEL
READ UNCOMMITTED level do not issue shared locks to prevent other transactions from modifying data read by the current transaction.
And result in intermittently incorrect data.
Can I suggest to developer to change the SNAPSHOT ISOLATION LEVEL in all SPs and transactions reading data do not block other transactions from writing data.
Maybe. Can your TempDB handle the additional load?
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
February 13, 2014 at 5:27 am
GilaMonster (2/13/2014)
Grant Fritchey (2/13/2014)
The fact that you have four clustered index scans and a heap scan should be a bigger concern than sorting cost. And, what the heck, that's a completely different execution plan from the first one.They're tables joined with no filters (no where clause), could be that the clustered index scans are the most efficient (alternative would be one scan, one seek and a nested loop join instead of the merge, not sure that would be better)
No question that given the query as structured the plan is the more efficient one. But it's the "as structured" that worries me. Surely we can get a WHERE clause in there (and yes, I know, the likely answer is, "But the business wants ALL THE DATAS", but that's where the soft-side of DBA work comes into play).
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply