DBCC FREEPROCCACHE for Execution Plans??

Question

DBCC FREEPROCCACHE for Execution Plans??

SkyBox

SSCarpal Tunnel

Points: 4288
More actions
June 10, 2011 at 12:53 pm

#239045

Beginning to use 2008 Exec. Plans for query tuning, but I'm running into some snags.
For example, I run the exec plan against a query, add an index, exec FREEPROCCACHE - but I still get the same results in the new execution plan.
Do I need to update statistics on that table in order to update the results in the exec plan?

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

No. Are you sure the index is useful?

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SkyBox SSCarpal Tunnel Points: 4288 More actions · Answer 2

Well, the exec plan recommended the index. The columns recommended are the exact fields referenced in the "where" criteria of the poorly performing query.

After re-running the plan, it's recommending the exact same index? Suppose I could order them up differently.....smallest to largest.

Suggestions? Thanks

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Don't reorder the columns in the key, it changes the index's usefulness.

Need to see query, index & execution plan to say more (and I assume since you say the exec plan recommended the index you are not using SQL 2000 despite posting in the SQL 2000 forums?)

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SkyBox SSCarpal Tunnel Points: 4288 More actions · Answer 4

I was considering reordering the columns in the index - didn't realize that I was in the SQL 2000 area - did a 'find' on 'perf tuning' within the page.

This query is from an ERP app - just picked up the SQL through a trace. Below is the query and the index. Is the XML for the exec plan okay? Thanks

Query:

SELECT SUM(A.AMOUNTMST)

FROM CUSTTRANSOPEN A, CUSTTRANS B ,JSCUSTCREDITTABLE C

WHERE

(A.DATAAREAID='zz')

AND (B.DATAAREAID='zz')

AND ((C.DATAAREAID='zz') AND (((((B.ACCOUNTNUM='10200244')

AND (B.JSISCUSTCREDIT=0)) AND (A.REFRECID=B.RECID)) AND (C.VOUCHER=B.VOUCHER)) AND (C.CUSTCREDITSTATUS=0)))

INDEX:

CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]

ON [dbo].[CUSTTRANS] ([ACCOUNTNUM],[DATAAREAID],[JSISCUSTCREDIT])

exec plan XML:

<?xml version="1.0" encoding="utf-16"?>

<Batch>

</ColumnGroup>

</MissingIndex>

</MissingIndexGroup>

</MissingIndexes>

</OutputList>

<IF>

</Identifier>

</ScalarOperator>

</ScalarOperator>

</Compare>

</ScalarOperator>

</Condition>

<Then>

</ScalarOperator>

</Then>

<Else>

</Identifier>

</ScalarOperator>

</Else>

</IF>

</ScalarOperator>

</DefinedValue>

</DefinedValues>

</OutputList>

</ScalarOperator>

</DefinedValue>

</Identifier>

</ScalarOperator>

</Aggregate>

</ScalarOperator>

</DefinedValue>

</DefinedValues>

</OutputList>

<Hash>

</HashKeysBuild>

</HashKeysProbe>

</Identifier>

</ScalarOperator>

</Identifier>

</ScalarOperator>

</Compare>

</ScalarOperator>

</ProbeResidual>

</OutputList>

<Hash>

</HashKeysBuild>

</HashKeysProbe>

</Identifier>

</ScalarOperator>

</Identifier>

</ScalarOperator>

</Compare>

</ScalarOperator>

</ProbeResidual>

</OutputList>

</DefinedValue>

</DefinedValues>

</RangeColumns>

</ScalarOperator>

</RangeExpressions>

</Prefix>

</SeekKeys>

</SeekPredicateNew>

</SeekPredicates>

</Identifier>

</ScalarOperator>

</ScalarOperator>

</Compare>

</ScalarOperator>

</Predicate>

</IndexScan>

</RelOp>

</OutputList>

</DefinedValue>

</DefinedValue>

</DefinedValues>

</RangeColumns>

</ScalarOperator>

</ScalarOperator>

</RangeExpressions>

</Prefix>

</SeekKeys>

</SeekPredicateNew>

</SeekPredicates>

</Identifier>

</ScalarOperator>

</ScalarOperator>

</Compare>

</ScalarOperator>

</Predicate>

</IndexScan>

</RelOp>

</Hash>

</RelOp>

</OutputList>

</DefinedValue>

</DefinedValue>

</DefinedValues>

</RangeColumns>

</ScalarOperator>

</RangeExpressions>

</Prefix>

</SeekKeys>

</SeekPredicateNew>

</SeekPredicates>

</IndexScan>

</RelOp>

</Hash>

</RelOp>

</StreamAggregate>

</RelOp>

</ComputeScalar>

</RelOp>

</QueryPlan>

</StmtSimple>

</Statements>

</Batch>

</BatchSequence>

</ShowPlanXML>

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Save the file as a .sqlplan and attach 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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 6

One piece of advice. You're writing your queries using the ANSI 89 standard on joins. Don't do that. As soon as you get to outer joins you're going to have an issue because that method is not supported in 2008. Instead of putting listing the tables and then putting the join criteria in the WHERE clause, use this method:

SELECT...

FROM TableA as a

JOIN TableB as b

ON a.ID = b.ID

JOIN TableC as c

ON b.whatever = c.whatever

WHERE...

Then you put the filtering criteria in the WHERE clause.

"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

SkyBox SSCarpal Tunnel Points: 4288 More actions · Answer 7

GilaMonster (6/10/2011)
Save the file as a .sqlplan and attach it.

Sorry, didn't see the attachment option initially. The plan is attached.

SkyBox SSCarpal Tunnel Points: 4288 More actions · Answer 8

Grant Fritchey (6/11/2011)
One piece of advice. You're writing your queries using the ANSI 89 standard on joins. Don't do that. As soon as you get to outer joins you're going to have an issue because that method is not supported in 2008. Instead of putting listing the tables and then putting the join criteria in the WHERE clause, use this method:
SELECT...
FROM TableA as a
JOIN TableB as b
ON a.ID = b.ID
JOIN TableC as c
ON b.whatever = c.whatever
WHERE...
Then you put the filtering criteria in the WHERE clause.

This SQL is written into my MS DAX ERP system for a customer select. I am looking to speed it up with an index.

I knew the query was using older syntax for joining, but did not know the term for it. Thanks for the clarification.

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 9

Since that's an estimated plan not an actual plan it's hard to know for sure, but it looks like the table has a cardinality estimate of about 1 million rows and you're returning 50k rows from it. That's probably not quite selective enough for an index to really help. That's why the plan didn't change.

With all the data coming back ordered out of those seeks & scans I'm surprised it didn't use merge joins considering the data involved. I'm generally anti-hint, but you might try using a merge join hint, just out of sheer curiousity.

Other than that, it looks like your data isn't as selective as it needs to be at .5% of the data in the table. At least that's the indication. Of those three columns, which is the most selective, has the most unique values? If it's not the first one, then as you suggested, you might try rearranging them to see what you get then.

"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

SkyBox SSCarpal Tunnel Points: 4288 More actions · Answer 10

Grant Fritchey (6/13/2011)
Since that's an estimated plan not an actual plan it's hard to know for sure, but it looks like the table has a cardinality estimate of about 1 million rows and you're returning 50k rows from it. That's probably not quite selective enough for an index to really help. That's why the plan didn't change.
With all the data coming back ordered out of those seeks & scans I'm surprised it didn't use merge joins considering the data involved. I'm generally anti-hint, but you might try using a merge join hint, just out of sheer curiousity.
Other than that, it looks like your data isn't as selective as it needs to be at .5% of the data in the table. At least that's the indication. Of those three columns, which is the most selective, has the most unique values? If it's not the first one, then as you suggested, you might try rearranging them to see what you get then.

I found a code profiler (trace) within the app and was able to catch sql statements that weren't showing up in the sql trace. With this tool I found the statement that needed the index. After adding the appropriate index, the query was much faster...

Ant-Green SSC Guru Points: 113507 More actions · Answer 11

You will find that most of Dynamic's is written in cursors, we have the exact same issue here. Was talking about this yesterday on a different thread.

You need to do all Dynamics "profiler" via the Dynamics app as all you will usually see is FETCH API_CURSOR000000000000000123 etc not the actual T-SQL within the cursor.

I had a powerpoint which went into some detail on perf monitoring Dynamics, once I find it again I will upload it.

EDIT, found the powerpoint

SkyBox SSCarpal Tunnel Points: 4288 More actions · Answer 12

anthony.green (6/24/2011)
You will find that most of Dynamic's is written in cursors, we have the exact same issue here. Was talking about this yesterday on a different thread.
You need to do all Dynamics "profiler" via the Dynamics app as all you will usually see is FETCH API_CURSOR000000000000000123 etc not the actual T-SQL within the cursor.
I had a powerpoint which went into some detail on perf monitoring Dynamics, once I find it again I will upload it.
EDIT, found the powerpoint

WOW! Thanks

How long have you been working with Dynamics and what verison(s)?

Ant-Green SSC Guru Points: 113507 More actions · Answer 13

Just Dynamics 2009 but been working with it now for over a year, only recently got more and more into the tuning as it has only just taken off throughout the business in the last 8 weeks.