Skip to main content
Kofax

SQL Server Instance and Database Settings For Kofax TotalAgility

3020059

Question / Problem: 

How should I collect information about SQL settings relevant to KTA databases?

Answer / Solution: 

To gather most of the SQL Server settings relevant to KTA, have a DBA run the script below against each SQL instance that is hosting KTA databases.  If all KTA databases are in one instance, then this only needs to be run once.

How to run this script

Because this script returns many different result sets, it is substantially more convenient to run the scripts this way:

  • To avoid truncated results from certain queries: Query > Query Options... > Results > Text, change "Maximum number of characters displayed in each column" to 8000
  • After opening a script in SQL Management Studio, Click the “Query” menu, then under “Results To…”, choose “Results to File”. 
    • Alternatively, use the shortcut: Ctrl + Shift + F.
  • Now when you execute the script it will prompt to save the entire set of results as a file. 
  • It is important to save the results using a descriptive filename.

SQL Script

SQLInstanceSettings.sql

-- If needed, use the below select to troubleshoot permissions of current user running this script
-- select * from fn_my_permissions(null, 'SERVER') UNION select * from fn_my_permissions(null, 'DATABASE') order by entity_name, subentity_name, permission_name

--To see what settings are recommended, refer to the documentation for your version of KTA:
--https://knowledge.kofax.com/Smart_Process_Applications/General_Documentation/Organization_of_KTA_Documentation

-- Basic info
SELECT @@VERSION AS Version, ServerProperty('ServerName') AS ServerName, ServerProperty('IsClustered') AS IsClustered, ServerProperty('IsHadrEnabled') AS AlwaysOnAGEnabled


-- Enable advanced options to see additional settings from sp_configure
EXEC sp_configure 'Show Advanced Options', 1;  --Requires RECONFIGURE to take effect
RECONFIGURE; 
-- Requires ALTER SETTINGS permission and is needed for 'Show Advanced Options' to take effect if not already enabled

-- Check  'Cost Threshold for Parallelism' and 'max degree of parallelism' (shown if 'Show Advanced Options' is enabled)
RAISERROR(' *** sp_configure settings *** ', 0, 1) WITH NOWAIT
exec sp_configure 


-- Check compatibility_level, is_read_committed_snapshot_on, recovery_model_desc, is_auto_update_stats_async_on, encryption_state
RAISERROR(' *** database properties *** ', 0, 1) WITH NOWAIT
SELECT d.*, dm.encryption_state
FROM sys.databases AS d  
LEFT JOIN sys.dm_database_encryption_keys dm ON d.database_id = dm.database_id
-- Requires ALTER ANY DATABASE or the VIEW ANY DATABASE server-level permission


-- Check if any trace flags are enabled (notably 9481 enables LCE prior to new option in SQL Server 2016) 
RAISERROR(' *** DBCC TRACESTATUS *** ', 0, 1) WITH NOWAIT
DBCC TRACESTATUS(-1)
-- Requires membership in the public role


-- Database file sizes and locations 
RAISERROR(' *** Database file sizes and locations *** ', 0, 1) WITH NOWAIT
SELECT DB_NAME(database_id) AS DatabaseName,
Name AS Logical_Name,
Physical_Name, (CAST(size AS BIGINT)*8)/1024 SizeMB
FROM sys.master_files
ORDER BY size DESC
-- Requires permissions CREATE DATABASE, ALTER ANY DATABASE, or VIEW ANY DEFINITION


--Who is connected, from where, using which authentication 
RAISERROR(' *** Connections and connecting systems *** ', 0, 1) WITH NOWAIT
SELECT  S.login_name, C.auth_scheme, s.host_name, COUNT(*) AS Count
FROM sys.dm_exec_connections AS C
JOIN sys.dm_exec_sessions AS S ON C.session_id = S.session_id
GROUP BY S.login_name, C.auth_scheme, s.host_name
ORDER BY COUNT(*) DESC
-- requires permission VIEW SERVER STATE

-- To avoid truncated results: Query > Query Options... > Results > Text,
--  change "Maximum number of characters displayed in each column" to 8000
RAISERROR(' *** Most blocked queries *** ', 0, 1) WITH NOWAIT
SELECT TOP 10 
[Average Time Blocked] = (total_elapsed_time - total_worker_time) / qs.execution_count ,
[Total Time Blocked] = total_elapsed_time - total_worker_time ,
[Execution count] = qs.execution_count ,
[Individual Query] = CHAR(13)+CHAR(10) + '--start individual query ' + CHAR(13)+CHAR(10) + SUBSTRING (qt.text,qs.statement_start_offset/2+1, (
    CASE WHEN qs.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2 
    ELSE qs.statement_end_offset 
    END - qs.statement_start_offset)/2+1) + CHAR(13)+CHAR(10) + '-- end individual query ' + CHAR(13)+CHAR(10),
[Parent Query] = CHAR(13)+CHAR(10) + '--start parent query' + CHAR(13)+CHAR(10) + qt.text + CHAR(13)+CHAR(10) + '--end parent query' + CHAR(13)+CHAR(10),DatabaseName = DB_NAME(p.dbid)
FROM sys.dm_exec_query_stats qs 
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt 
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS p
WHERE qs.execution_count>1
ORDER BY [Average Time Blocked] DESC


declare @SqlServerEngineEdition smallint 
declare @SqlServerProductMajorVersion smallint 
SELECT @SqlServerEngineEdition = CONVERT(sysname, SERVERPROPERTY ('EngineEdition'));
SELECT @SqlServerProductMajorVersion = CONVERT(sysname, SERVERPROPERTY ('ProductMajorVersion'));
declare @SqlServerProductMinorVersion smallint 
SELECT @SqlServerProductMinorVersion=CONVERT(sysname, SERVERPROPERTY ('ProductMinorVersion'));

--Please refer the following article for possible values for the EngineEdition: https://msdn.microsoft.com/en-us/library/ms174396.aspx
--3 = Enterprise (This is returned for Evaluation, Developer, and both Enterprise editions.)
--5 = SQL Azure
-- For SQL2016 the ProductMajorVersion is 13.

--If SQL 2016
IF @SqlServerProductMajorVersion > 12 
BEGIN
    -- For SQL 2016+
    -- Check MAXDOP, LEGACY_CARDINALITY_ESTIMATION
    RAISERROR(' *** Database scoped configurations *** ', 0, 1) WITH NOWAIT
    EXEC sys.sp_MSforeachdb N'SELECT N''?'' AS DatabaseName, * FROM ?.sys.database_scoped_configurations;'; 
END

Applies to:  

Product Version
KTA ALL

 

 

  • Was this article helpful?