Ignite 8.2 introduced a new process for collecting plans from instances of SQL Server 2005 and later. This process has been tested at many customer sites, and at a few of them we noticed a large growth in plan tables in the Ignite repository database. These tables are named CONSPT_XX, CONSPA_XX and CONSPH_XX (where XX is the ID for the instance in the COND table). This article explains the causes and remedies for large repository plan tables.
To detect if your Ignite Repository has large plan tables, you can log in to the repository as the repository user and run the statements below:
SQL Server repository
IF OBJECT_ID('tempdb..#t', 'U') IS NOT NULL DROP TABLE #t CREATE TABLE #t ([name] NVARCHAR(128),[rows] CHAR(11),reserved VARCHAR(18),data VARCHAR(18),index_size VARCHAR(18),unused VARCHAR(18)) -- get the space used for each table in this database INSERT #t EXEC sp_msForEachTable 'EXEC sp_spaceused ''?''' SELECT * FROM ( SELECT [name] as "Table", CONVERT(int, [rows]) as "Rows", CONVERT(int, LEFT([reserved],LEN([reserved])-3)) / 1024 as "Reserved MB", CONVERT(int, LEFT([data],LEN([data])-3)) / 1024 as "Data MB", CONVERT(int, LEFT([index_size],LEN([index_size])-3)) / 1024 as "Index MB", CONVERT(int, LEFT([unused],LEN([unused])-3)) / 1024 as "Unused MB" FROM #t WHERE [name] like 'CONSPT%') t ORDER BY [Reserved MB] DESC DROP TABLE #t
select segment_name, sum(bytes)/1024/1024 mb from user_segments where segment_name like 'CONSPT%' group by segment_name order by 2 desc
Some SQL Server instances experience a significant amount of plan churn, which in turn causes Ignite to collect the plan info for many unique plans. This issue seems to be caused by SQL Server and the number of plans being generated for some queries.
The outcomes of generating a large number of plans are slowed response time (high Memory/CPU wait time) as well as higher churn on SQL Server memory, which in turn can lead to decreased cache hit ratio.
There are several articles that discuss the issue:
Some SQL Server plans (actually, the XML format that the plan is defined in) can be quite large, as big as 13 MB. If many large plans are collected by Ignite (possibly by cause #1), this can contribute significantly to plan table size.
Safeguards were added to Ignite that help to prevent excessively large plan tables. If you observe large plan tables: