Submit a ticketCall us

Webinar: Web Help Desk for HR, Facilities and Accounting Departments
This webinar will focus on use cases for HR, Facilities and Accounting.

Having a unified ticketing and asset management system for all the departments in your company can provide end-users with a seamless experience and make things easier for your IT team. Yet, with different business tasks and objectives, many departments don’t fully understand the capabilities of Web Help Desk and how the software can be customized for effective use in their departments.
Register Now.

Home > Success Center > Database Performance Analyzer (DPA) > Large repository plan tables

Large repository plan tables

Overview

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.

Environment

  • Ignite 8.2.61 and earlier

Does my repository have large 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

 

Oracle repository

select segment_name, sum(bytes)/1024/1024 mb
 from user_segments
 where segment_name like 'CONSPT%'
 group by segment_name
 order by 2 desc

 

Cause #1 – High Plan Count

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. 

 

  • In many cases, this happens for applications that use the .NET 3.5 framework and issue SQL Server queries against variable length character fields.  Basically, SQL Server ends up generating a plan for each combination of string lengths that are queried (even if bind variables are used).  This problem is more severe in SQL Server 2005 (because a new "plan generation number" is generated each time, as opposed to SQL Server 2008 which uses a query plan hash "thumbprint" which reduces plan churn).
  • There is also a SQL Server 2008 bug that causes a new plan to be generated for every execution of a query against a temp table.

 

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:

Cause #2 – Large Plans

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.

Solution

Safeguards were added to Ignite that help to prevent excessively large plan tables. If you observe large plan tables:

  1. Make sure you are running Ignite 8.2.62 or later.
  2. If you are running a lower version than 8.2.62 and cannot upgrade yet, you can turn off plan collection for the instance(s) experiencing the issue.
    1. From the home screen, locate the database instance.
    2. Click Action > Advanced Options.
    3. Click the PLANPOLL_ENABLED option and set to False.
    4. Restart the Ignite PI Server Windows service.
Last modified
19:15, 22 Jun 2016

Tags

Classifications

Public