Evaluating Slow Running Queries

queries that take a long time to execute can manifest themselves just about anywhere in ivanti service manager dashboards, saved searches, and viewing a business object are all areas impacted by slow running queries with some familiarity with the tools available, tracking down and fixing these problems is a relatively straight forward task identifying problem queries docid\ cpvizfju3o4og psefqqimproving saved search query performance docid\ cpvizfju3o4og psefqq identifying problem queries if queries run slowly, use the microsoft sql server profiling tools to find out which queries are the problem see tuning indexes docid\ qu 0rrvgthcdvwom4msmf for more information improving saved search query performance saved searches are used in many places throughout ivanti service manager, especially in dashboards and workspaces saved search queries can be complicated because they can involve related data and typically include criteria for more than just a single field for this reason, dealing with performance problems for a saved search can be a bit more involved constructing saved searches in the most efficient way possible can significantly improve performance you can create a saved search, modify a saved search, delete a saved search, add a saved search as a favorite, and find saved searches for more information about using the saved search feature, see the “using saved search” topic in the ivanti service manager online help \<font color="#ff0000">\</font>\<font color="#000000">\</font> you can use the contain operator and like (sql format) operator in your search query, but this can result in slower query performance the position of the wild card characters in the query determines the performance and index usage hence, your search query should have the wild card characters positioned correctly to improve the query performance we recommend the following using “begin with”, “not begin with” and “not null” in saved searches by adding index to fields used in saved search see " about creating indexes using the configuration console, not third party tool " on page 25 for information on how to create indexes using the ivanti service manager database minimizing sorting and grouping in grids creating index fields for tables frequently used in saved searches to reduce database access time use the microsoft sql server profiling tools to find problem queries and to help identify indexes that can improve performance see the ivanti service manager online help for information about creating indexes about data and log files microsoft sql server databases generally contain two types of files data files, which usually use the mdf extension these files contain the database data log files, which usually use the ldf file extension these files contain log data which can be verbose as a best practice, we recommend that you use house these files on different volumes about snapshot isolation levels about snapshot isolation levels docid\ cpvizfju3o4og psefqqusing read committed snapshot isolation (rcsi) docid\ cpvizfju3o4og psefqq about snapshot isolation levels isolation levels control how two or more transactions running simultaneously are isolated from each other in terms of locking and blocking resources the isolation level determines the level of concurrency and data consistency ivanti service manager uses the following isolation levels read committed this is the default transaction isolation level in microsoft sql server this isolation level prevents "dirty reads" which occur when a transaction is allowed to read data from a row that was modified by another transaction that has not yet been committed this level acquires shared locks to prevent other transactions from modifying the data during a read operation by that transaction a shared lock can be acquired only if there is no exclusive lock (required for data modification) by other transactions; this ensures it reads only committed data read committed snapshot isolation (rcsi) an extension of read committed but with increased concurrency with this level, the system uses the last committed version before the statement starts, regardless of when the transaction starts we recommend that you use rcsi if your system meets any of the following criteria it has a high number of transactions if you encounter a high number of locks in your database your system has multiple back end servers, because there could be multiple workflow, escalation, and other tasks that are triggered after the system creates or updates a record note that using rcsi generally increases the cpu utilization due to the additional processing, but lowers the cpu utilization due to a reduction in locks and deadlocks the net result is lower cpu utilization and therefore, we recommend using rcsi using read committed snapshot isolation (rcsi) for the ivanti service manager database, ensure that the following two parameters are set to on allow snapshot isolation is read committed snapshot on to set these parameters to on, do the following open microsoft sql server management studio navigate to and highlight the ivanti service manager database to set the properties for right click and select properties on the left, select options under miscellaneous , set allow snapshot isolation to true and set is read committed snapshot on to true click ok