34,590
社区成员
发帖
与我相关
我的任务
分享
DBCC TRACEON (3604);
DBCC RULEOFF('JNtoSM', 'JNtoHS');
DBCC RULEON('JNtoSM', 'JNtoHS');
-- Route messages to the client
DBCC TRACEON (3604);
-- Ensure the four core implementation rules are available
DBCC RULEON ('GetToScan');
DBCC RULEON ('JNtoNL');
DBCC RULEON ('SelectToFilter');
DBCC RULEON ('GbAggToStrm')
-- Required by SQL Server 2005 only
DBCC RULEON ('ReduceGbAgg');
-- Alternative join implementations
DBCC RULEOFF ('JNtoHS');
DBCC RULEOFF ('JNtoSM');
-- Index-related transformations
DBCC RULEOFF ('GetIdxToRng');
DBCC RULEOFF ('GetToIdxScan');
DBCC RULEOFF ('SelIdxToRng');
DBCC RULEOFF ('SelToIdxStrategy');
DBCC RULEOFF ('SELonJN');
DBCC RULEOFF ('JNtoIdxLookup');
DBCC RULEOFF ('AppIdxToApp');
DBCC RULEOFF ('SelResToFilter');
DBCC RULEOFF ('WCJNonSELtoIdxLookup');
-- Exploration rules
DBCC RULEOFF ('GbAggToHS')
DBCC RULEOFF ('JoinCommute');
DBCC RULEOFF ('GbAggBeforeJoin');
DBCC RULEOFF ('GenLGAgg');
DBCC RULEOFF ('BuildSpool');
DBCC RULEOFF ('ImplRestrRemap');
DBCC RULEOFF ('EnforceSort');
DBCC RULEOFF ('NormalizeGbAgg');
SELECT P.ProductNumber,
P.ProductID,
total_qty = SUM(I.Quantity)
FROM Production.Product P
JOIN Production.ProductInventory I
ON I.ProductID = P.ProductID
WHERE P.ProductNumber LIKE N'T%'
GROUP BY
P.ProductID,
P.ProductNumber
OPTION (RECOMPILE);
.Net SqlClient Data Provider: Msg 8624, Level 16, State 1, Line 1
Internal Query Processor Error: The query processor could not produce a query plan.
For more information, contact Customer Support Services.
-- Clear the snapshot
TRUNCATE TABLE #Snapshot;
-- Save a snapshot of the DMV
INSERT #Snapshot
(
name,
promise_total,
promised,
built_substitute,
succeeded
)
SELECT name,
promise_total,
promised,
built_substitute,
succeeded
FROM sys.dm_exec_query_transformation_stats
OPTION (KEEPFIXED PLAN);
-- Query under test
-- Must use OPTION (RECOMPILE)
SELECT P.ProductNumber,
P.ProductID,
total_qty = SUM(I.Quantity)
FROM Production.Product P
JOIN Production.ProductInventory I
ON I.ProductID = P.ProductID
WHERE P.ProductNumber LIKE N'T%'
GROUP BY
P.ProductID,
P.ProductNumber
OPTION (RECOMPILE);
GO
-- Results
SELECT QTS.name,
promise = QTS.promised - S.promised,
promise_value_avg =
CASE
WHEN QTS.promised = S.promised
THEN 0
ELSE
(QTS.promise_total - S.promise_total) /
(QTS.promised - S.promised)
END,
built = QTS.built_substitute - S.built_substitute,
success = QTS.succeeded - S.succeeded
FROM #Snapshot S
JOIN sys.dm_exec_query_transformation_stats QTS
ON QTS.name = S.name
WHERE QTS.succeeded != S.succeeded
OPTION (KEEPFIXED PLAN);
DBCC RULEON ('BuildSpool');
DBCC RULEON ('JNtoHS');
DBCC RULEON ('JNtoSM');
DBCC RULEON ('GetIdxToRng');
DBCC RULEON ('GetToIdxScan');
DBCC RULEON ('SelIdxToRng');
DBCC RULEON ('SelToIdxStrategy');
DBCC RULEON ('SELonJN');
DBCC RULEON ('JNtoIdxLookup');
DBCC RULEON ('AppIdxToApp');
DBCC RULEON ('SelResToFilter');
DBCC RULEON ('WCJNonSELtoIdxLookup');
DBCC RULEON ('GbAggToHS')
DBCC RULEON ('JoinCommute');
DBCC RULEON ('GbAggBeforeJoin');
DBCC RULEON ('GenLGAgg');
DBCC RULEON ('BuildSpool');
DBCC RULEON ('ImplRestrRemap');
DBCC RULEON ('EnforceSort');
DBCC RULEON ('NormalizeGbAgg');
DBCC RULEON ('ReduceGbAgg');