Error 8622
Contents |
Me Contact Me !!! SQL Server FORCESEEK Hint February 22nd, 2013 Vinod Kumar Generally I am not a hangfire query processor could not produce a query plan because of the hints defined in this query big fan of using hints inside SQL Server. Having said that, submit the query without specifying any hints and without using set forceplan. have seen many customers use wrong hints and not knowing what they are used for. From SQL Server with forceseek 2008, there were several interesting new hints introduced that are lesser known to developers. One such hint is around FORCESEEK. Please use this with care and after proper
Query Processor Could Not Produce A Query Plan Because Of The Hints Defined In This Query Hash Join
testing in your environment. The FORCESEEK table hint instructs the optimizer to only use an index seek (clustered or non-clustered index) as the access path for a given table. If you also specify an index hint, it will only produce a query plan with a seek on the specified index. If an index seek plan is not found, msg 8622 or is not found using the specified index, the query compilation will fail with error 8622: Msg 8622, Level 16, State 1, Line 1 Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN. With only an index hint we can force the use of a particular index but we cannot force an index seek. If you forced a particular index, the query might still perform a scan instead of an index seek. Here is below is one such example: CREATE TABLE test (c1 INT NOT NULL PRIMARY KEY, c2 INT) GO CREATE INDEX i ON test (c2) GO SET SHOWPLAN_TEXT ON GO SELECT * FROM test WHERE c1 = 0 or c2 = 100 SELECT * FROM test WITH (INDEX(1)) WHERE c1 = 0 or c2 = 100 GO SET SHOWPLAN_TEXT OFF The output will show: StmtText ------------------------------------------------- |-Index Scan(OBJECT:([tempdb].[dbo].[test].[i]), WHERE:([tempdb].[dbo].[test].[c1]=(0) OR [tempdb].[dbo].[test].[c2]=(100))) (1 row(s) affected) StmtText ------------------------------------------------- |-Cl
★★★★★★★★★★★★★★★ Craig FreedmanApril 28, 20099 0 0 0 In this post, I want to take a look at how two seemingly unrelated features of option hash join SQL Server can interact to cause a problem. The idea for this
Query Hints
post came from a question submitted by a reader. Let's begin. Consider the following trivial schema and query: CREATE
Sql Server Index Hint
TABLE T1 (A INT, B INT)CREATE TABLE T2 (A INT, B INT) SELECT *FROM T1 INNER JOIN T2 ON T1.A = T2.AWHERE T1.B = 0OPTION (HASH JOIN) Not surprisingly, this query http://blogs.extremeexperts.com/2013/02/22/sql-server-forceseek-hint/ yields the following plan: |-Hash Match(Inner Join, HASH:([T1].[A])=([T2].[A]), RESIDUAL:([T2].[A]=[T1].[A])) |-Table Scan(OBJECT:([T1]), WHERE:([T1].[B]=(0))) |-Table Scan(OBJECT:([T2])) In fact, this query yields this plan with or without the hint. Now let's make a small modification to the WHERE clause of the query see what happens: SELECT *FROM T1 INNER JOIN T2 ON T1.A = T2.AWHERE T1.A = 0OPTION (HASH JOIN) Now this query yields https://blogs.msdn.microsoft.com/craigfr/2009/04/28/implied-predicates-and-query-hints/ the following error message: Msg 8622, Level 16, State 1, Line 1 Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN. What happened? Why does this seemingly innocuous change to the query cause it to fail? To find the answer, let's run the query without the HASH JOIN hint and look at the plan: SELECT *FROM T1 INNER JOIN T2 ON T1.A = T2.AWHERE T1.A = 0 |-Nested Loops(Inner Join) |-Table Scan(OBJECT:([T2]), WHERE:([T2].[A]=(0))) |-Table Scan(OBJECT:([T1]), WHERE:([T1].[A]=(0))) There are two things about this plan that are especially notable: First, the plan includes the predicate "T2.A = 0". Although we did not specify this predicate in the original query, SQL Server derives it from the predicates that we did specify. This derivation is a good thing. It allows SQL Server to filter out rows from the scan of T2 earlier than would otherwise be possible. Second, the original equijoin predicate "T1.A = T2.A" appears nowhere in this plan. This predicate is redundant with the original pre
Msg 8622, Level 16, State 2, Line 2 Query processor could not produce a query plan because of the hints defined in this query. Resubmit the http://www.sql-server-performance.com/2009/query-processor-could-not-produce-a-query-plan/ query without specifying any hints and without using SET FORCEPLAN. Severity level: 16. http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=190196 Description: This error message appears when you try to specify conflicting hints in a query. Consequences: The T-SQL statement can be parsed, but causes the error at runtime. Resolution: Error of the Severity level 16 are generated by the user and can be fixed by the SQL Server user. The statement query processor cannot be run this way. Consider removing the hints or changing them. Versions: All versions of SQL Server. Example(s): IF OBJECT_ID (‘dbo.t') IS NOT NULL DROP TABLE dbo.t; CREATE TABLE dbo.t ( c1 int PRIMARY KEY, s1 varchar(20) ); GO SELECT * FROM dbo.t WITH (INDEX = 0 INDEX = 1); Remarks: In the above example we try to query processor could execute a query with a conflicting specification of query hints. This raises the error. ASK A QUESTION Tweet Array Errors No comments yet... Be the first to leave a reply! Leave a Reply Click here to cancel reply. Popular Latest Tags Setting up Transactional Replication in SQL Server 2008 R2. November 30, -0001 Backing Up a SQL Server Database Directly Onto a Remote Server November 30, -0001 Recovering a SQL Server Database from Suspect Mode November 30, -0001 SQL Server T-SQL Tuning - NOT IN and NOT Exists November 30, -0001 SQL Server T-SQL Tuning - Temp Tables, Table Variables and Union November 30, -0001 SQL Server Logical Reads – What do they really tell us? October 4, 2016 Physical Join Operators in SQL Server - Hash Operator September 21, 2016 Physical Join Operators in SQL Server - Merge Operator August 25, 2016 Techniques to Monitor SQL Server memory usage August 12, 2016 Recovering a SQL Server Database from Suspect Mode August 7, 2016 Forum posts... 2008 R2 2012 Agent AlwaysOn Analysis Services Backup Clustering Data Mining deadlock Denormalization Disaster Recovery Email Entity Framework Excel Geo high availability Identity I
SQL Server experts to answer whatever question you can come up with. Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums. SQL Server Forums Profile | ActiveTopics | Members | Search | ForumFAQ Register Now and get your question answered! Username: Password: Save Password Forgot your Password? All Forums SQL Server 2008 Forums Transact-SQL (2008) Error:Query processor could not produce a query pl Reply to Topic Printer Friendly Author Topic alejo46 Posting Yak Master Colombia 157 Posts Posted-12/10/2013: 08:50:52 Good morningI need your help pls, theres a sp the used to work fine by means of a DTS bit now it yielñded an error:Msg 8622, Level 16, State 1, Line 2Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN.This is a complex SP as follow:IF EXISTS (SELECT * FROM trafico1.SYS.INDEXES WHERE NAME = 'IX_RESUMEN_ABONADO_FECHAS_TRAFICO_TMP_201312_COD_ABONADO' AND OBJECT_ID = OBJECT_ID ( N'[trafico1]..[RESUMEN_ABONADO_FECHAS_TRAFICO_TMP_201312]')) DROP INDEX IX_RESUMEN_ABONADO_FECHAS_TRAFICO_TMP_201312_COD_ABONADO ON trafico1..RESUMEN_ABONADO_FECHAS_TRAFICO_TMP_201312(1 row(s) affected)(1 row(s) affected)(1 row(s) affected)SELECT @V_NUMREG_BASE_ABONADO = LP.NUM_REG FROM LOG_PROCESOS LP , (SELECT MAX (FEC_INICIO) AS FEC_INICIO FROM LOG_PROCESOS WHERE ESTADO = '1' AND NUM_PROCESO = 5056) LPF WHERE LP.FEC_INICIO = LPF.FEC_INICIO(1 row(s) affected)SELECT @CANT_MIN_RECARGAS =CASE WHEN Evento Like 'RECARGA%' THEN ISNULL(TRRF.VALORMINIMOCANTIDAD,0) ELSE @CANT_MIN_RECARGAS END ,@MONTO_MIN_RECARGAS =CASE WHEN Evento Like 'RECARGA%' THEN ISNULL(TRRF.VALORMINIMOMONTO,0) ELSE @MONTO_MIN_RECARGAS END ,@CANT_MIN_ACTUACIONES =CASE WHEN Evento Like 'ACTUAC%' THEN ISNULL(TRRF.VALORMINIMOCANTIDAD,0) ELSE @CANT_MIN_ACTUACIONES END ,@MONTO_MIN_ACTUACIONES =CASE WHEN Evento Like 'ACTUAC%' THEN ISNULL(TRRF.VALORMINIMOMONTO,0) ELSE @MONTO_MIN_ACTUACIONES END ,@CANT_MIN_TARVAL =CASE WHEN Eve