<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2008
»
SQL Server 2008 - General
»
my queries are too slow when SET FORCEPLAN...
18 posts, Page 1 of 2
1
2
»»
my queries are too slow when SET FORCEPLAN OFF
Rate Topic
Display Mode
Topic Options
Author
Message
Thinky Night
Thinky Night
Posted Saturday, February 02, 2013 2:29 PM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Thursday, June 06, 2013 7:57 AM
Points: 123,
Visits: 221
Hi All,
I am using an reporting tool for my dwh system.
My tool are generating queries and those queries are too slow.
When i tested those in new query window, queires does not return any rows for 4-5 hours, But, when i enable FORCEPLAN ON, those queries took 2-3 seconds.
So, I understand this; my tool are generating queries while accepting optimiser is in SET FORCEPLAN ON Mode. But When query arrive to SQL Server optimiser, it generates a different execution plan and could not generate correct execution plan.
So, I want to help about default FORCEPLAN ON for all sessions. Like Database trigger?
How do i achive this situation, to do my all sessions in the mode SET FORCEPLAN ON?
Regards,
Post #1414964
e4d4
e4d4
Posted Saturday, February 02, 2013 3:43 PM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Today @ 12:33 AM
Points: 160,
Visits: 1,147
This is a one query or many?
Please post a query execution plan, there must be something that send a query optimizer to choose a wrong execution plan, this could be a very outdated statistics, a heap table, or maybe someting else.
Post #1414968
GilaMonster
GilaMonster
Posted Sunday, February 03, 2013 1:19 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 12:52 AM
Points: 38,100,
Visits: 30,393
Please post table definitions, index definitions and execution plan, as per
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
When FORCEPLAN is set to ON, the SQL Server query optimizer processes a join in the same order as the tables appear in the FROM clause of a query. In addition, setting FORCEPLAN to ON forces the use of a nested loop join unless other types of joins are required to construct a plan for the query, or they are requested with join hints or query hints.
As a complete guess, I'd say the stats are badly enough out of date (or just wrong) so that the optimiser is generating a bad plan. The solution isn't forceplan as a default, it's fixing the cause of the problem.
Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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
Post #1414989
Thinky Night
Thinky Night
Posted Monday, February 04, 2013 12:08 AM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Thursday, June 06, 2013 7:57 AM
Points: 123,
Visits: 221
Every Night, statistics are updated and indexes are rebuilt. All queries take for long durations. It is not specific to one query.
Post #1415129
foxxo
foxxo
Posted Monday, February 04, 2013 12:21 AM
Right there with Babe
Group: General Forum Members
Last Login: Today @ 12:29 AM
Points: 754,
Visits: 1,101
Post the query and execution plan as Gail requested.
You don't want to use the FORCEPLAN ON option as you're addressing a symptom and not the cause. It's only going to cause more issues later.
Post #1415133
Thinky Night
Thinky Night
Posted Monday, February 04, 2013 12:35 AM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Thursday, June 06, 2013 7:57 AM
Points: 123,
Visits: 221
Query:
SELECT
SAP_DB.URP.MAKT.MATNR,
SAP_DB.URP.MAKT.MAKTX,
PLY_V.dbo.DIM_PRODUCTMAINCATEG.dim_productmaincategdesc,
PLY_V.dbo.DIM_PRODUCTCATEG.dim_productcategdesc,
PLY_V.dbo.DIM_PRODUCTBRAND.dim_productbranddesc,
SAP_DB.dbo.BUTCE.YIL,
SAP_DB.dbo.BUTCE.AY,
Sum(SAP_DB.dbo.BUTCE.TUTAR),
Sum(SAP_DB.dbo.BUTCE."MIKTAR(KG)"),
SAP_DB.dbo.BUTCE.SIRKET,
SAP_DB.URP.T880.NAME1
FROM
SAP_DB.URP.MAKT RIGHT OUTER JOIN SAP_DB.dbo.BUTCE ON (SAP_DB.dbo.BUTCE.URUN=SAP_DB.URP.MAKT.MATNR AND SAP_DB.URP.MAKT.SPRAS = 'T')
LEFT OUTER JOIN STG_V.DBO.SAP_MARA ON (SAP_DB.dbo.BUTCE.URUN=STG_V.DBO.SAP_MARA.MATNR)
INNER JOIN PLY_V.dbo.DIM_PRODUCTBRAND ON (substring(STG_V.DBO.SAP_MARA.PRDHA,8,2)=PLY_V.dbo.DIM_PRODUCTBRAND.dim_orighierarchycode)
INNER JOIN PLY_V.dbo.DIM_PRODUCTCATEG ON (substring(STG_V.DBO.SAP_MARA.PRDHA,2,2)=PLY_V.dbo.DIM_PRODUCTCATEG.dim_orighierarchycode)
INNER JOIN PLY_V.dbo.DIM_PRODUCTMAINCATEG ON (substring (STG_V.DBO.SAP_MARA.PRDHA,1,1)=PLY_V.dbo.DIM_PRODUCTMAINCATEG.dim_orighierarchycode)
LEFT OUTER JOIN SAP_DB.URP.TVK5T ON (SAP_DB.URP.TVK5T.SPRAS = 'T' AND SAP_DB.URP.TVK5T.KATR5=SAP_DB.dbo.BUTCE.KANAL)
LEFT OUTER JOIN SAP_DB.URP.T880 ON (RIGHT(SAP_DB.URP.T880.RCOMP,4)=SAP_DB.dbo.BUTCE.SIRKET)
WHERE
( PLY_V.dbo.DIM_PRODUCTMAINCATEG.sur_productmaincateg_id in (17,24,28,35) ) AND ( PLY_V.dbo.DIM_PRODUCTCATEG.sur_productcateg_id in (44,45,46,73,74,80,81,82,109,114,186,187,188,218,258,260,293,294,295,334) )
AND
(
SAP_DB.URP.T880.NAME1 NOT IN ( 'Atlas Gida Paz.San.Tic. A.S.','Atlantik Gida Paz.Tic. A.S.','Merkez Gida Paz.San.Tic. A.S.' )
AND
(
SAP_DB.URP.TVK5T.KATR5 NOT IN ( '10' )
OR
SAP_DB.URP.TVK5T.KATR5 Is Null
)
)
GROUP BY
SAP_DB.URP.MAKT.MATNR,
SAP_DB.URP.MAKT.MAKTX,
PLY_V.dbo.DIM_PRODUCTMAINCATEG.dim_productmaincategdesc,
PLY_V.dbo.DIM_PRODUCTCATEG.dim_productcategdesc,
PLY_V.dbo.DIM_PRODUCTBRAND.dim_productbranddesc,
SAP_DB.dbo.BUTCE.YIL,
SAP_DB.dbo.BUTCE.AY,
SAP_DB.dbo.BUTCE.SIRKET,
SAP_DB.URP.T880.NAME1
Query Statistics when SET FORCEPLAN ON:
Table 'SAP_MAKT'. Scan count 17, logical reads 2887, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'BUTCE'. Scan count 17, logical reads 8549, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 64, logical reads 2369998, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SAP_T880'. Scan count 16, logical reads 96, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SAP_TVK5T'. Scan count 16, logical reads 32, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'DIM_PRODUCTMAINCATEG'. Scan count 64, logical reads 128, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'DIM_PRODUCTCATEG'. Scan count 320, logical reads 640, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'DIM_PRODUCTBRAND'. Scan count 666787, logical reads 1333574, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SAP_MARA'. Scan count 666787, logical reads 2352459, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
I added Execution Plan below.
Post Attachments
xxQuery.sqlplan
(
15 views,
187.53 KB
)
Post #1415135
foxxo
foxxo
Posted Monday, February 04, 2013 12:57 AM
Right there with Babe
Group: General Forum Members
Last Login: Today @ 12:29 AM
Points: 754,
Visits: 1,101
Was that execution plan with FORCEPLAN ON? If so, can you post the other plan and also script the table definition and any index definitions
Post #1415143
Thinky Night
Thinky Night
Posted Monday, February 04, 2013 1:01 AM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Thursday, June 06, 2013 7:57 AM
Points: 123,
Visits: 221
Yes, the execution plan is for FORCEPLAN ON. I can not take execution plan for FORCEPLAN OFF.
Now, i am preparing table and index scripts.
Post #1415145
foxxo
foxxo
Posted Monday, February 04, 2013 1:05 AM
Right there with Babe
Group: General Forum Members
Last Login: Today @ 12:29 AM
Points: 754,
Visits: 1,101
You should be able to do an estimated execution plan rather than an actual execution plan.
Post #1415147
Thinky Night
Thinky Night
Posted Monday, February 04, 2013 1:47 AM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Thursday, June 06, 2013 7:57 AM
Points: 123,
Visits: 221
Estimated Execution Plan is in below attachments
Post Attachments
EstimatedExecPlan.sqlplan
(
13 views,
150.76 KB
)
Post #1415167
« Prev Topic
|
Next Topic »
18 posts, Page 1 of 2
1
2
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
