Database Tuning
This section describes how to tune the Ivanti Service Manager database for optimal performance. If the system responds slowly while running queries, loading dashboards, or performing other actions, implement the recommendations described here. Information includes:
•Performance Enhancement Recommendations
Performance Enhancement Recommendations
The following sections describe recommendations and caveats to keep in mind when you tune the Ivanti Service Manager database.
•About Tuning the Indexes on a Regular Basis
•Deleting Inactive Tables and Records
•Checking for Under-Utilized Indexes
•Turning on JSON Compression in Microsoft Internet Information Services (IIS)
•Checking Database Latency and Moving Indexes to a Separate Disk
•About Creating Indexes Using the Configuration Console, Not Third-Party Tools
•Checking for Duplicate Indexes
•Not Using Include Fields in Indexes
•Ignoring “Create Statistics” Index Results
•Setting the Value for the Database Auto-Growth Parameter
•Monitoring Database Growth and Resetting the Auto-Growth Parameter as Necessary
About Tuning the Indexes on a Regular Basis
Evaluate on an on-going basis the need to perform the re-indexing procedure described in Tuning Indexes. If necessary, you can perform the re-indexing procedure as part of normal maintenance. It does not require hardware or other infrastructure changes.
About Deleting Inactive Tables on a Regular Basis
Whenever possible, delete inactive tables in the Ivanti Service Manager database to keep the database size and complexity to a minimum. See Deleting Inactive Tables and Records for details about deleting.
Checking for Under-Utilized Indexes
Ivanti Service Manager uses indexes to quickly access data. This is defined as reading from the index. Every time that you add a new record, you must also add the new record information to all of the indexes that it applies to. This is defined as writing to an index. While using an index saves time, adding information (writing) to the index takes time.
We recommend that you check your indexes to ensure that they are used frequently. If an index is not used frequently, the performance impact of writing to it initially might not justify the benefits that it provides. One way to check for under-utilization is to determine the ratio of an index of reads to writes. If you have a table that you are writing records to frequently, but you are not reading using this index very often, it takes more effort to add the new record to the index than it is probably worth.
Calculate the read:write ratio for an index to determine if it is under utilized. If an index has a read:write ratio of 10:1 or less (that is, if total reads to the index does not equal at least 10 times the number of total writes to the index), you should consider deleting the index.
Run the following Microsoft SQL script on the Ivanti Service Manager database to check how often data in an index is read and how often it is written.
-- This script checks for under-utilized indexes.
-- Indexes in which the number of "writes" outnumbers
-- the number of "reads" by far, e.g., 90%, are
-- considered possibly under-utilized indexes.
SELECT OBJECT_NAME(s.object_id)AS 'Table Name',
i.name AS 'Index Name',
i.index_id,
user_updates AS 'Total Writes',
user_seeks + user_scans + user_lookups AS 'Total Reads',
user_updates - (user_seeks + user_scans + user_lookups)
AS 'Difference'
FROM sys.dm_db_index_usage_stats AS s WITH (NOLOCK)
INNER JOIN sys.indexes AS i WITH (NOLOCK)ON s.object_id
= i.object_id
AND i.index_id = s.index_id
WHERE OBJECTPROPERTY(s.object_id,'IsUserTable')= 1
AND s.database_id =DB_ID()
AND user_updates > (user_seeks + user_scans + user_lookups)
AND i.index_id > 1
ORDER BY 'Difference' DESC,
'Total Writes' DESC,
'Total Reads' ASC;
Turning on JSON Compression in Microsoft Internet Information Services (IIS)
Follow these steps to improve performance by implementing JSON compression:
1.Open the Computer Management panel by going to Start > Control Panel > Administrative Tools > Computer Management.
2.In the Computer Management panel, double-click Services and Applications and double-click Internet Information Services (IIS).
3.On the page for the server (not the site) click Configuration Editor.
4.Expand system.webServer and click httpCompression.
5.Click dynamicTypes to open it.
6.Click mimeType to open the Properties dialog box.
7.For the mimeType entry, enter application/json, and for the enabled entry, enter True.
8.Click Apply.
9.Restart your Microsoft IIS server to load the new module.
Checking Database Latency and Moving Indexes to a Separate Disk
When data and indexes are stored on the same physical disk, resource contention might occur when the system looks up an index and retrieves the corresponding data. Measure the database latency to determine whether resource contention is affecting performance.
Run the following script on the Ivanti Service Manager database to check database latency. If database latency significantly exceeds 110 ms, move indexes to a physical disk that is separate from the disk that stores the Ivanti Service Manager database.
-- Description: This script checks the latency on the
-- different Ivanti databases. Ideally the latency figure
-- should be a single digit value.
SELECT
--virtual file latency
[ReadLatency] =
CASE WHEN [num_of_reads] = 0
THEN 0 ELSE ([io_stall_read_ms] / [num_of_reads]) END,
[WriteLatency] =
CASE WHEN [num_of_writes] = 0
THEN 0 ELSE ([io_stall_write_ms] / [num_of_writes]) END,
[Latency] =
CASE WHEN ([num_of_reads] = 0 AND [num_of_writes] = 0)
THEN 0 ELSE ([io_stall] / ([num_of_reads] + [num_of_writes])) END,
--avg bytes per IOP
[AvgBPerRead] =
CASE WHEN [num_of_reads] = 0
THEN 0 ELSE ([num_of_bytes_read] / [num_of_reads]) END,
[AvgBPerWrite] =
CASE WHEN [io_stall_write_ms] = 0
THEN 0 ELSE ([num_of_bytes_written] / [num_of_writes]) END,
[AvgBPerTransfer] =
CASE WHEN ([num_of_reads] = 0 AND [num_of_writes] = 0)
THEN 0 ELSE
(([num_of_bytes_read] + [num_of_bytes_written])/
([num_of_reads] + [num_of_writes])) END,
LEFT ([mf].[physical_name], 2) AS [Drive],
DB_NAME ([vfs].[database_id]) AS [DB],
-- [vfs].*,
[mf].[physical_name]
FROM
sys.dm_io_virtual_file_stats (NULL,NULL) AS [vfs]
JOIN sys.master_files AS [mf]
ON [vfs].[database_id] = [mf].[database_id]
AND [vfs].[file_id] = [mf].[file_id]
WHERE DB_NAME ([vfs].[database_id]) like '%HEAT%'
-- and [vfs].[file_id] = 2 -- log files
-- ORDER BY [Latency] DESC
-- ORDER BY [ReadLatency] DESC
ORDER BY [WriteLatency] DESC;
GO
About Creating Indexes Using the Configuration Console, Not Third-Party Tools
Do not use third-party tools (such as Microsoft SQL Server Management Studio) to update indexes in the Ivanti Service Manager database. Instead, use the Configuration Console to create new indexes. We recommend this because the Configuration Console creates and maintains index information as Ivanti Service Manager metadata. Ivanti Service Manager uses this metadata whenever tables or indexes in the Ivanti Service Manager database need to be recreated.
Checking for Duplicate Indexes
Check for duplicate indexes in these situations:
•Before creating indexes in the Ivanti Service Manager database, make sure that the table for which you are creating an index does not already have an index containing the same fields.
•If you suspect that duplicate indexes already exist and are impacting performance, find and delete them.
The following sections describe how to check a specific table to determine whether it is indexed, and how to query the Ivanti Service Manager database to identify all duplicate indexes.
•Checking a Specific Table for Indexes
•Checking for Duplicate Indexes Using a Microsoft SQL Script
Checking a Specific Table for Indexes
Follow these steps:
1.Open Microsoft SQL Server Management Studio.
2.In the Object Explorer panel, navigate to the Ivanti Service Manager database.
3.Navigate to Tables > System Tables > table_name > Indexes.
4.Double-click an index to open it in the Index Properties window. Indexed fields are listed in the Index key columns area.
Checking for Fields that are Already Indexed
Checking for Duplicate Indexes Using a Microsoft SQL Script
Run the following Microsoft SQL script on the Ivanti Service Manager database to identify duplicate indexes:
-- This script checks for duplicate indexes.
with indexcols AS
(
SELECT object_id AS id,
index_id AS indid, name,
(SELECT case keyno WHEN 0 THEN NULL ELSE colid END AS [data()]
FROM sys.sysindexkeys AS k
where k.id = i.object_id
and k.indid = i.index_id
ORDER BY keyno, colid
for xml path('')) AS cols,
(SELECT case keyno when 0 then colid else NULL end AS [data()]
from sys.sysindexkeys AS k
where k.id = i.object_id
and k.indid = i.index_id
ORDER BY colid
for xml path ('')) AS inc
FROM sys.indexes AS i
)
SELECT object_schema_name(c1.id) + '.' + object_name(c1.id) AS 'table',
c1.name as 'index',
c2.name as 'exactduplicate'
FROM indexcols as c1
JOIN indexcols as c2
ON c1.id = c2.id
AND c1.indid < c2.indid
AND c1.cols = c2.cols
AND c1.inc = c2.inc;
System Tables to Not Index
We recommend that you do not index tables that begin with FRS_. These are system tables containing default indexes that are required for Ivanti Service Manager to function properly.
Not Using Include Fields in Indexes
See Best Practices for Creating New Indexes for more information.
Ignoring “Create Statistics” Index Results
When creating indexes, ignore the “Create Statistics” results returned by performance tuning tools. See Best Practices for Creating New Indexes for more information.
Setting the Value for the Database Auto-Growth Parameter
By default, the auto-growth size of a new database is 1 MB. This is too small for the Ivanti Service Manager database, so you must increase it.
To estimate and configure the appropriate auto-growth size for your Ivanti Service Manager database, do the following:
1.Estimate the total size of your initial Ivanti Service Manager database based on expected transactions for incident, service request, and other primary business objects.
2.Open Microsoft SQL Server Management Studio.
3.In the Object Explorer panel, navigate to the Ivanti Service Manager database.
4.Right click the Ivanti Service Manager database and select Properties.
5.In the Select a page panel, click Files.
6.In the Initial Size (MB) column of the Ivanti Service Manager database line item, set the initial database size based on your estimate from step 1.
Specifying the Initial Size of the Ivanti Service Manager Database
7.Click the details icon in the Ivanti Service Manager database line item to open the Change Autogrowth for Ivanti Service Manager dialog box.
8.Check Enable Autogrowth, select In Megabytes, and enter a number that is 5 to 10 percent of the initial database size. We recommend that you set the auto-growth parameter using a static value instead of a percentage.
Setting the Auto-Growth Parameter
9.Click OK.
Monitoring Database Growth and Resetting the Auto-Growth Parameter as Necessary
We recommend that you periodically evaluate whether the value for the Enable Autogrowth parameter is appropriate and reset it as necessary.
After you create the initial Ivanti Service Manager database, monitor its size every month for the first few months to estimate its growth rate. If necessary, reset the value for the Enable Autogrowth parameter based on the observed growth rate. After you determine the long-term growth pattern, you can usually wait six months or longer before checking and adjusting the value of the Enable Autogrowth parameter.