February 2, 2013 at 2:29 pm
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,
February 2, 2013 at 3:43 pm
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.
February 3, 2013 at 1:19 am
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 4, 2013 at 12:08 am
Every Night, statistics are updated and indexes are rebuilt. All queries take for long durations. It is not specific to one query.
February 4, 2013 at 12:21 am
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.
February 4, 2013 at 12:35 am
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.
February 4, 2013 at 12:57 am
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
February 4, 2013 at 1:01 am
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.
February 4, 2013 at 1:05 am
You should be able to do an estimated execution plan rather than an actual execution plan.
February 4, 2013 at 1:47 am
Estimated Execution Plan is in below attachments
February 4, 2013 at 1:48 am
Exec plan without forceplan please, need to see what SQL's doing wrong to help.
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 4, 2013 at 2:01 am
I prepared Table and index definitions on DDLs.sql file. And reorganized query in one database as OnError.sql
You see all in the attachments:
February 4, 2013 at 2:36 am
Can you also script statistics with histogram?
February 4, 2013 at 3:48 am
There's some very dodgy joins in there. When you join on a substring or function, it's going to slow everything down. Can you modify the joins to SAP_MARA? Or break the query down into more manageable pieces? The NOT IN statement and RIGHT function are causing grief too.
Try this, doubt it will make a difference, but might help the optimiser to evaluate the join conditions more evenly.
SELECT SAP_MAKT.MATNR,
SAP_MAKT.MAKTX,
DIMS.dim_productmaincategdesc,
DIMS.dim_productcategdesc,
DIMS.dim_productbranddesc,
BUTCE.YIL,
BUTCE.AY,
Sum(BUTCE.TUTAR),
Sum(BUTCE."MIKTAR(KG)"),
BUTCE.SIRKET,
SAP_T880.NAME1
FROM SAP_MAKT
LEFT OUTER JOIN BUTCE ON (SAP_MAKT.MATNR=BUTCE.URUN AND SAP_MAKT.SPRAS = 'T')
LEFT OUTER JOIN SAP_TVK5T ON (SAP_TVK5T.SPRAS = 'T' AND SAP_TVK5T.KATR5=BUTCE.KANAL)
LEFT OUTER JOIN SAP_T880 ON (RIGHT(SAP_T880.RCOMP,4)=BUTCE.SIRKET)
-- LEFT OUTER JOIN SAP_MARA ON (BUTCE.URUN=SAP_MARA.MATNR)
LEFT OUTER JOIN (
SELECT SAP_MARA.MATNR, DIM_PRODUCTMAINCATEG.dim_productmaincategdesc, DIM_PRODUCTCATEG.dim_productcategdesc, DIM_PRODUCTBRAND.dim_productbranddesc
FROM SAP_MARA
INNER JOIN DIM_PRODUCTBRAND ON (substring(SAP_MARA.PRDHA,8,2)=DIM_PRODUCTBRAND.dim_orighierarchycode)
INNER JOIN DIM_PRODUCTCATEG ON (substring(SAP_MARA.PRDHA,2,2)=DIM_PRODUCTCATEG.dim_orighierarchycode)
INNER JOIN DIM_PRODUCTMAINCATEG ON (substring (SAP_MARA.PRDHA,1,1)=DIM_PRODUCTMAINCATEG.dim_orighierarchycode)
WHERE DIM_PRODUCTMAINCATEG.sur_productmaincateg_id in (17,24,28,35)
AND 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)
) DIMS ON (BUTCE.URUN=DIMS.MATNR)
WHERE
(
SAP_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_TVK5T.KATR5 NOT IN ( '10' )
OR
SAP_TVK5T.KATR5 Is Null
)
)
GROUP BY
SAP_MAKT.MATNR,
SAP_MAKT.MAKTX,
DIMS.dim_productmaincategdesc,
DIMS.dim_productcategdesc,
DIMS.dim_productbranddesc,
BUTCE.YIL,
BUTCE.AY,
BUTCE.SIRKET,
SAP_T880.NAME1
Otherwise, you can split the query into a temp table then join to that. Post the execution plan for
SELECT SAP_MARA.MATNR, DIM_PRODUCTMAINCATEG.dim_productmaincategdesc, DIM_PRODUCTCATEG.dim_productcategdesc, DIM_PRODUCTBRAND.dim_productbranddesc
FROM SAP_MARA
INNER JOIN DIM_PRODUCTBRAND ON (substring(SAP_MARA.PRDHA,8,2)=DIM_PRODUCTBRAND.dim_orighierarchycode)
INNER JOIN DIM_PRODUCTCATEG ON (substring(SAP_MARA.PRDHA,2,2)=DIM_PRODUCTCATEG.dim_orighierarchycode)
INNER JOIN DIM_PRODUCTMAINCATEG ON (substring (SAP_MARA.PRDHA,1,1)=DIM_PRODUCTMAINCATEG.dim_orighierarchycode)
WHERE DIM_PRODUCTMAINCATEG.sur_productmaincateg_id in (17,24,28,35)
AND 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)
February 4, 2013 at 4:21 am
Hi foxxo,
Here is the splitted Execution Plan:
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply