When IdentityIQ is installed with Microsoft SQL Server as the backing database, IdentityIQ requires ALLOW_SNAPSHOT_ISOLATION and READ_COMMITTED_SNAPSHOT to be enabled. (See Configuring IdentityIQ for use with SQL server mirroring) This is also part of the DDL scripts for SQL Server.
This configuration is also known to cause an increase in tempDB usage. This article discusses how to identify what might be causing the tempDB to grow.
What is tempDB used for?
The tempDB is global resource for all databases on the SQL Server. It is used by SQL Server to:
Hold temporary user objects that are explicitly created, such as: global or local temporary tables and indexes, temporary stored procedures, table variables, Tables returned in table-valued functions, or cursors.
Internal objects that are created by the database engine. These include:
Work tables to store intermediate results for spools, cursors, sorts, and temporary large object (LOB) storage.
Work files for hash join or hash aggregate operations.
Intermediate sort results for operations such as creating or rebuilding indexes (if SORT_IN_TEMPDB is specified), or certain GROUP BY, ORDER BY, or UNION queries.
IdentityIQ does not directly make use of the tempdb. Any operations done on it are controlled by SQL Server. But as the above usage shows, there may be queries run by IdentityIQ that can cause objects to be created in the Tempdb. The ultimate size depends on the query and the data being queried. Normally these objects are not very large.
Finding the current tempDB size
The tempDB will grow depending on need, and shrink again when the queries that needed the space are completed, but by default the data file on disk will not shrink, so just looking at the data file does not tell you how large the tempdb currently is. To find the current actual size, you can use the following query, for example:
USE tempdb
EXEC sp_spaceused
Finding out who/what is using the tempDB
To see which queries are using up tempDB space, you need to check while the space is actually being used. A query that can be used for that is:
SELECT t1.session_id, (t1.internal_objects_alloc_page_count + task_alloc) as allocated, (t1.internal_objects_dealloc_page_count + task_dealloc) as deallocated, qt.text as [Batch Text], qp.query_plan as [Execution Plan] FROM sys.dm_db_session_space_usage as t1, (select session_id, sum(internal_objects_alloc_page_count) as task_alloc, sum (internal_objects_dealloc_page_count) as task_dealloc FROM sys.dm_db_task_space_usage group by session_id) as t2, sys.dm_exec_requests t3 cross apply sys.dm_exec_sql_text (t3.sql_handle) as qt cross apply sys.dm_exec_query_plan (t3.plan_handle) as qp WHERE t1.session_id = t2.session_id AND t1.session_id >50 AND t1.database_id = DB_ID('tempdb') AND t1.session_id = t3.session_id ORDER BY allocated DESC;
If the tempDB keeps growing but you are not sure when it grows, it may be best to run this and the earlier query in a scheduled job every hour or even minute, to record the usage.
Unexpected tempDB usage
If you are seeing unusual or unexpected tempdb usage, please work with your Database Administrator to see which queries are causing it.
Do IdentityIQ versions( 7.1, 7.2 & 7.3) also require ALLOW_SNAPSHOT_ISOLATION and READ_COMMITTED_SNAPSHOT to be enabled. We see that IdentityIQ 8.0 database script has it turned ON by default but no information in lower version of IIQ. We had an issue yesterday in our environment with the tempdb size where it ran out of space causing application to not work correctly.
"As of this release, snapshot isolation in SQL Server now is enabled by default in the database upgrade scripts, which matches the database creation scripts. The settings enabled are ALLOW_SNAPSHOT_ISOLATION and READ_COMMITTED_SNAPSHOT. This facilitates locking without excessive blocking. As a result of this change, administrators can expect an increase in the utilization of the SQL Server tempdb resource."
However, in a current case we're following up w/ Sailpoint Support where our TempDB version store size grows because of hung DB sessions we've been advised to try to turn the ALLOW_SNAPSHOT_ISOLATION and READ_COMMITTED_SNAPSHOT off to see if that helps so I'd say it not 100% mandatory, only so much recommended so that Sailpoint decided to turn this on by default.
