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 docid\ slsbup2zewgpf4oubdnsutuning indexes docid\ qu 0rrvgthcdvwom4msmfabout using full text search docid\ yerizihjrtjrbu4tgjrtv 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 docid\ slsbup2zewgpf4oubdnsudeleting inactive tables and records docid\ re1ppmikg jkpplm58rp4checking for under utilized indexes docid\ slsbup2zewgpf4oubdnsuturning on json compression in microsoft internet information services (iis) docid\ slsbup2zewgpf4oubdnsuchecking database latency and moving indexes to a separate disk docid\ slsbup2zewgpf4oubdnsuabout creating indexes using the configuration console, not third party tools docid\ slsbup2zewgpf4oubdnsuchecking for duplicate indexes docid\ slsbup2zewgpf4oubdnsusystem tables to not index docid\ slsbup2zewgpf4oubdnsunot using include fields in indexes docid\ slsbup2zewgpf4oubdnsuignoring “create statistics” index results docid\ slsbup2zewgpf4oubdnsusetting the value for the database auto growth parameter docid\ slsbup2zewgpf4oubdnsumonitoring database growth and resetting the auto growth parameter as necessary docid\ slsbup2zewgpf4oubdnsu 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 docid\ qu 0rrvgthcdvwom4msmf 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 docid\ re1ppmikg jkpplm58rp4 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 open the computer management panel by going to start > control panel > administrative tools > computer management in the computer management panel, double click services and applications and double click internet information services (iis) on the page for the server (not the site) click configuration editor expand system webserver and click httpcompression click dynamictypes to open it click mimetype to open the properties dialog box for the mimetype entry, enter application/json, and for the enabled entry, enter true click apply 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 docid\ slsbup2zewgpf4oubdnsuchecking for duplicate indexes using a microsoft sql script docid\ slsbup2zewgpf4oubdnsu checking a specific table for indexes follow these steps open microsoft sql server management studio in the object explorer panel, navigate to the ivanti service manager database navigate to tables > system tables > table name > indexes 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 docid\ qu 0rrvgthcdvwom4msmf 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 docid\ qu 0rrvgthcdvwom4msmf 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 estimate the total size of your initial ivanti service manager database based on expected transactions for incident, service request, and other primary business objects open microsoft sql server management studio in the object explorer panel, navigate to the ivanti service manager database right click the ivanti service manager database and select properties in the select a page panel, click files 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 click the details icon in the ivanti service manager database line item to open the change autogrowth for ivanti service manager dialog box 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 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