Tuning Indexes

perform these steps to re index the ivanti service manager database capturing a database trace docid\ qu 0rrvgthcdvwom4msmfgetting index tuning recommendations docid\ qu 0rrvgthcdvwom4msmf about creating new indexes docid\ qu 0rrvgthcdvwom4msmf capturing a database trace use the microsoft sql server profiler in microsoft sql server management studio to capture a database trace you analyze the information from this trace file in getting index tuning recommendations docid\ qu 0rrvgthcdvwom4msmf open microsoft sql server management studio by selecting start > all programs > microsoft sql server 2008 > sql server management studio highlight the server that hosts the ivanti service manager database from the tools menu, select sql server profiler start a new trace if you are prompted for login credentials, specify them for ivanti service manager and click connect the system displays the trace properties window if you are not prompted for login credentials, select file > new trace in the sql server profiler window the system prompts you for login credentials specify credentials for ivanti service manager and click connect the system displays the trace properties window on the general tab, do the following a in the trace name field, enter a name for the trace b in the use the template field, select standard (default) c check save to file and designate a file location for the trace running a trace from the trace properties window click run to initiate the trace the system displays trace information while the trace runs a running trace when the trace has collected enough information to analyze as described in the next section, select pause trace or stop trace from the file menu getting index tuning recommendations use the index tuning advisor in microsoft sql server management studio to get index tuning recommendations open microsoft sql server management studio by selecting start > all programs > microsoft sql server 2008 > sql server management studio highlight the server that hosts the ivanti service manager database from the tools menu, select database engine tuning advisor start a new tuning session if you are prompted for login credentials, specify the credentials for ivanti service manager and click connect the system displays the database engine tuning advisor window containing a new open session if you are not prompted for login credentials, select file > new session in the database engine tuning advisor window the system prompts you for login credentials specify credentials for ivanti service manager and click connect the system opens a new session in the database engine tuning advisor window on the general tab, in the workload area, browse to the trace file that you created in capturing a database trace docid\ qu 0rrvgthcdvwom4msmf and click open in the select databases and tables to tune area, highlight the ivanti service manager database selecting the ivanti service manager database from the drop down menu next to the selected database in the selected tables column, choose which tables to analyze selecting tables click the tuning options tab in the physical design structures (pds) to use in database section, select indexes in the partitioning strategy to employ section, select no partitioning in the physical design structures (pds) to keep in database section, select keep all existing pds click start analysis to view progress, select the progress tab to view recommended indexing actions, click the recommendations tab and go to the index recommendations section index recommendations verify that the indexes recommended for creation do not already exist in the ivanti service manager database as described in checking for duplicate indexes docid\ slsbup2zewgpf4oubdnsu to view the tuning summary, click the reports tab you can view reports in the tuning reports drop down list beneath the tuning summary about creating new indexes after you verify that the indexes recommended for creation do not already exist in the ivanti service manager database, use the configuration console to create the indexes best practices for creating new indexes docid\ qu 0rrvgthcdvwom4msmfcreating new indexes docid\ qu 0rrvgthcdvwom4msmf best practices for creating new indexes follow these best practices when you create new indexes ivanti service manager does not create clustered indexes disregard any clustered indexes created outside of ivanti service manager ivanti service manager does not create or use include fields or included indexes you must create and maintain any included indexes that you use outside of the ivanti service manager database we recommend that you evaluate on a case by case basis the trade off between performance gains and maintenance overhead ivanti service manager maintains statistics only for indexes created in ivanti service manager disregard any statistics returned by the create statistics operations in database performance tuning tools creating new indexes perform these steps to create new indexes using the configuration console from the configuration console, click build > business objects the system displays the business objects workspace select a business object the system displays the business object page click the indexes tab the system displays the indexes workspace with a list of indexes for the business object to add a new index, click add new\ the system displays index fields associated with this business object select the field to index for this business object you can add more fields later the system displays the system properties page enter information into the fields field description index name a unique name for this index description a useful description for this index unique designates this value as unique, preventing duplicate entries in the index and its backing table you can create uniqueness by using multiple fields this option automatically creates an unique index of this field unlike the primary key field you can have multiple unique fields in the business object this field is allowed to contain null values (although the null values are not indexed) clustered checks if the business object is part of a clustered index at the database level clustered indexes can greatly increase overall speed of retrieval, but usually only where data is accessed sequentially in the same or reverse order of the clustered index, or when a range of items is selected this is the same as a clustered index function for a microsoft sql database a clustered index cannot contain included fields (it cannot be a cover index) this checkbox is disabled for index fields that have an increasing value, such as guid key fields see viewing, adding, and deleting key fields docid\ qu 0rrvgthcdvwom4msmf included fields see viewing, adding, and deleting included fields docid\ qu 0rrvgthcdvwom4msmf filter expression optional you can apply a filter to the index that does not include null values click the add new icon , then choose a field from the list to create a filter repeat as needed operation choose an operation from the drop down list for this filter notnull the filter does not index null values value enter a string value or expression for this filter click add this index the system adds the index to the business object viewing, adding, and deleting key fields the system displays the running size, in bytes, of the index for the key fields the limit is 900 bytes from the configuration console, click build > business objects the system displays the business objects workspace select a business object the system displays the business object page click the indexes tab the system displays the indexes workspace with a list of indexes for the business object click an index name to open the record to add a key field, do the following a click the add icon at the end of the row b click not set to select a field from the drop down list c click ascending to change the order you can select either ascending or descending to delete a key field, click the delete icon at the end of the row click save viewing, adding, and deleting included fields from the configuration console, click build > business objects the system displays the business objects workspace select a business object the business object page appears click the indexes tab the system displays the indexes workspace with a list of indexes for the business object click an index name to open the record to add a key field, do the following a click the add icon at the end of the row b click not set to select a field from the drop down list c click ascending to change the order you can select either ascending or descending to delete a key field, click the delete icon at the end of the row click save for additional information about adding indexes, see the ivanti service manager online help (for information about accessing the ivanti service manager documentation