Slow query performance (bad query plan). Can I delete query plan ?

(0) Share

Report

Posted on by Ireneusz Smaczny

1,535

In Ax2012 R3 CU9 (with SQL 2014) I have select with 6 tables. But this simple query takes about 70 seconds:

while
Select ValidTimeState(validFrom, validTo) PPLPayrollCalculatePreparedSet //1-10 rows
where PPLPayrollCalculatePreparedSet.RandomNumber == MyNumber
Join PPLEmplBase where ... //6000 rows
Join PPLCalendarPeriodForEmpl where ... //8000 rows
Join PPLCalendar where ... //1 mln rows
Join PPLCalendarDayTypeTable where ... //20 rows
Join PPLContract where ... //6000 rows

Query plan is wrong becouse joins PPLContract (6000 rows) x PPLCalendar(1 mln rows) x …

After changing query:
where PPLPayrollCalculatePreparedSet.RandomNumber == MyNumber
to
where PPLPayrollCalculatePreparedSet.RandomNumber >= MyNumber
query takes only 0.02 sec.

New query plan is quite good becouse starting from PPLPayrollCalculatePreparedSet (10 rows) x PPLContract (6000 rows) x …

I can send via email this slow and fast execution plan.

What can I do to force create new query plan (or delete this slow query plan) ? I tryed:
DBCC FREEPROCCACHE;
GO
DBCC FREESYSTEMCACHE ('SQL Plans');
GO
DECLARE @intDBID INT;
SET @intDBID = (SELECT [dbid]
FROM master.dbo.sysdatabases
WHERE name = N'DynamicsAX_TEST');
DBCC FLUSHPROCINDB (@intDBID);
GO

but always select with == MyNumber is using slow query plan but select with >= MyNumber is using fast query plan. Can I delete this slow query plan?

*This post is locked for comments

I have the same question (0)

All responses (5)

Answers (0)

Suggested answer

Vilmos Kintera 46,149 on at

Like (0)

Report
The above SQL statement is a horrible idea since it drops all query plans in cache, which is almost an equivalent of restarting the SQL instance. It has to rebuild each and every database call's query fresh.

You could just target the specific query plans to drop them instead, here are the scripts I use:

-- FIND QUERY PLAN BELONGING TO A TABLE OR STATEMENT select plan_handle, creation_time, last_execution_time, execution_count, qt.text, qp.query_plan FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text (qs.[sql_handle]) AS qt CROSS APPLY sys.dm_Exec_query_plan(qs.plan_handle) as qp where qt.text like '%logisticspostaladdress%' and qt.text like '%purchtable%' -- FREE UP QUERY PLAN BY HANDLE DBCC FREEPROCCACHE (0x06000500D6A9C4292077152D5E00000001000000000000000000000000000000000000000000000000000000) -- RECOMPILE QUERY PLANS FOR A TABLE !!! CAUSES LOCKS !!! sp_recompile @objname='MyTable'

Also your original plan seems to be badly designed, consider replacing inner joins with exists joins if you do not use the data from those tables. Also use a field list for the specific fields if you are actually using them. It will improve execution time considerably.

Was this reply helpful? Yes No
Ireneusz Smaczny 1,535 on at

Like (0)

Report

In my query I have 6 tables. Do I have to make something like this?

sp_recompile @objname='MyTable1'

sp_recompile @objname='MyTable2'

...

sp_recompile @objname='MyTable6'

Was this reply helpful? Yes No
Suggested answer

Vilmos Kintera 46,149 on at

Like (0)

Report

Yes, but you need to separate them by a semicolon at the end, and put GO after each instruction to execute it one by one. The recompile is also a bad idea, since you are not targeting specific plans again like you did before, but you throw out all plans for the tables, which is bad practice again. Use the first T-SQL I gave to identify your specific ones, and free proccache for the specific handles only!

But again, I think your query is badly designed, fix that first. Changing the query itself will generate new plans anyway.

Was this reply helpful? Yes No
Suggested answer

MahmoudHakim 110 on at

Like (0)

Report

you can create index and use views.

Was this reply helpful? Yes No
Suggested answer

Brandon Wiese 17,788 on at

Like (0)

Report

First, a bad query plan is usually the result of either a poor or missing index, or out of date statistics.

The easiest thing is to update the statistics on all tables involved (I prefer WITH FULLSCAN when troubleshooting, just to be certain), and do a DBCC FREEPROCCACHE, and try again.

If that does not work, start with the actual query plan in SQL Server Management Studio and figure out if another index would help you. Or, you could specify an index (hint) from X++ to tune your query.

Finally, you could probably try using literals instead of placeholders for the query, and SQL Server might select a more optimal query plan based on actual query values instead of statistically likely ones.

Was this reply helpful? Yes No