Original content by Debasish Namata
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.
The tempDB is global resource for all databases on the SQL Server. It is used by SQL Server to:
(See also: https://docs.microsoft.com/en-us/sql/relational-databases/databases/tempdb-database?view=sql-server-...)
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.
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
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.
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.
Thanks,
Manjesh
@manjesh_kumar I would think they do because of the following snippet from the 7.3p3 release notes:
"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.
>> If you are seeing unusual or unexpected tempdb usage, please work with your Database Administrator to see which queries are causing it.
Our tempdb grows pretty rapidly and the main source are following requests:
update spt_request ...
update spt_identity ...
Is there anything we can do to prevent it?