Internal query processor error:

On SQL Server 2008 SP1, Received a “Internal query processor error: Query processor could not produce a query plan”

Based on http://blogs.msdn.com/b/sqlserverfaq/archive/2014/07/15/troubleshooting-error-8624-severity-16-state-21-internal-query-processor-error-the-query-processor-could-not-produce-a-query-plan.aspx

First of all, please check whether the stats are updated with Full Scan as the optimizer replies on the stats for generating the best execution plan.

We can update them by using the below command for all the statistics created in the associated tables:
UPDATE STATISTICS SchemaName.TableName(StatsName) WITH FULLSCAN;

After updating the stats try to run the query. If it still gives the same error, then the cause of the above error can be:

1. The SET operators are not correctly set in the instance.
2. This query is really complex and optimizer timed out while generating the plan.
3. Maybe we didn’t write the query in optimal way.
4. Maybe we are missing some indexes or statistics from the database.

If all the SET options are set correctly, so now we know that the optimizer is not able to produce the query plan because of the complexity (join order, where clause, nested tables etc.), so we need to tell the optimizer not to use its own logic and just follow the join order which we specified in the query and produce the query plan (may be a bad plan but just produce it).

We can achieve the above by specifying a clause at the end of the query: OPTION (FORCE ORDER).Now, executed the query and it gave the result. It means that the optimizer produced the query plan (may be a bad plan, but at least it produced that).

 

Index Histograms and Density Vector

DBCC SHOW_STATISTICS will give the density vector and histogram information.

The density Vector gives an average of the index key. SQL Server might also give an average of the nonclustered key along with the the clustered index key

The histogram gives us specific information of the index based on only the first column in the key.

The “All Density” column multiplied by “Rows” column will give the “Average number of rows for a key value”, in this case, LastName.
In this example, there are 370.3 rows on average (0.0370.3 * 10,000) for any given lastname. And there is 1 row on average for each combination of LastName, Member no. (0.0001 * 10,000).

 

MERGE statement

http://www.sqlservercentral.com/articles/Stairway+Series/92557/

 

DMV List

SELECT
*
FROM sys.system_objects
WHERE name LIKE ‘dm_%’
AND type = ‘V’
ORDER BY name

 

Large Deployment Script file

If you are using osql to run sql server deployment script files, the file size should be limited to around 512 KB. The file fails to execute successfully if the file is longer in size

 

Lock escalation

http://aboutsqlserver.com/2012/01/11/locking-in-microsoft-sql-server-part-12-lock-escalation/

 

DBCC Page

http://www.mssqltips.com/sqlservertip/1578/using-dbcc-page-to-examine-sql-server-table-and-index-data/

http://blogs.msdn.com/b/sqlserverstorageengine/archive/2006/12/13/more-undocumented-fun_3a00_-dbcc-ind_2c00_-dbcc-page_2c00_-and-off_2d00_row-columns.aspx

To figure out which objects the page belongs to, look for Metadata: ObjectId in the Header section of DBCC page. Use that object id to plug into the following command to obtain the object.

SELECT OBJECT_NAME(ObjectId)

 

 

Statistics

STATS_DATE
Returns the date of the most recent update for statistics on a table or indexed view.

USE AdventureWorks2012;
GO
SELECT name AS stats_name, 
    STATS_DATE(object_id, stats_id) AS statistics_update_date
FROM sys.stats 
WHERE object_id = OBJECT_ID('Person.Address');
GO

If statistics correspond to an index, the stats_id value in the sys.stats catalog view is the same as the index_id value in the sys.indexes catalog view, and the following query returns the same results as the preceding query. If statistics do not correspond to an index, they are in the sys.stats results but not in the sys.indexes results.

USE AdventureWorks2012;

GO
SELECT name AS index_name, 
    STATS_DATE(object_id, index_id) AS statistics_update_date
FROM sys.indexes 
WHERE object_id = OBJECT_ID('Person.Address');
GO

http://technet.microsoft.com/en-us/library/ms190330.aspx

 

DROP_EXISTING Clause of CREATE index

Rebuild the Clustered Index in a Single Step

Because of the dependency of nonclustered indexes on the clustered index, rebuilding the clustered index as separate DROP INDEX AND CREATE statements causes all the nonclustered indexes to be rebuilt twice. To Avoid this, use the DROP_EXISTING clause of the CREATE INDEX statement to rebuild the clustered index in a single atomic step. Similary, you can also use the DROP_EXISTING clause with a nonclustered index.

 

Querying Plan Cache

SELECT [cp].[refcounts] ,
[cp].[usecounts] ,
[cp].[objtype] ,
[st].[dbid] ,
[st].[objectid] ,
[st].[text] ,
[qp].[query_plan],
cp.plan_handle
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp;

where the value in cp.plan_handle can be used to single out a particular plan and use that value to clear cache for just 1 proc..

example dbcc freeproccache(plan_handle)

— From Sql Server Execution Plans by Grant Fritchley, page 52