Search for	Example	Results
A single word	`cat`	Topics that contain the word "cat". You will also find its grammatical variations, such as "cats".
A phrase. You can specify that the search results contain a specific phrase.	`"cat food"` (quotation marks)	Topics that contain the literal phrase "cat food" and all its grammatical variations. Without the quotation marks, the query is equivalent to specifying an OR operator, which finds topics with one of the individual words instead of the phrase.

Search for	Operator	Example
Two or more words in the same topic	`AND` `and` `+` (plus symbol) `&` (ampersand)	`cat AND dog` `"cat food"+milk` `"cat food"&"dog food"`
Either word in a topic	`OR` `or` `\|` (pipe)	`cat OR dog` `cat \| dog`
Topics that do not contain a specific word or phrase	`NOT` `not` `!` (exclamation point)	`NOT cat` `! dog`
Topics that contain one string and do not contain another	`^` (caret)	`cat ^ mouse`
A combination of search types	`( )` parentheses	`cat + (dog \| mouse)` `cat \| dog + (! mouse)`

Configuration Management System (CMS)2018.05

Use > Database > Deploying the Microsoft SQL Server Database > Creating and Configuring the Microsoft SQL Server Database > Monitoring the Fragmentation Indexes

Monitoring the Fragmentation Indexes

Due to the fact that UCMDB is an OLTP application and performs many insert, update, and delete operations each day, its indexes might become fragmented. The index fragmentation could be even higher if discovery jobs that run also modify the data.

To determine the indexes' fragmentation, run the following script every day. This script could be scheduled as part of the SQL job. The result of the script contains all the indexes having average fragmentation larger than 15 percent. When you have data from several days, you should compare the results and determine if the fragmentation index continue to increase day by day.

If the indexes continue to become more fragmented, you should rebuild these indexes each day. You must rebuild or reorganize the indexes when no discovery, enrichment, or other jobs that modify the data are running.

To determinate the fragmentation indexes

You must run this script in the context of the UCMDB database. In this example, replace CMDB_DATABASE with the actual name of the client's UCMDB database.

USE CMDB_DATABASE
select 
       OBJECT_NAME(b.object_id) as TblName
       , CAST(a.object_id as varchar) as object_id
       , CAST(a.index_id as varchar) as index_id
       , Coalesce(b.name,'') as IndexName
       , CAST(Coalesce(a.avg_fragmentation_in_percent,'') as varchar) as PercentFragment
       , CAST(CAST(Coalesce(a.fragment_count,'') as bigint) as varchar) as TotalFrags
       , CAST(Coalesce(a.avg_fragment_size_in_pages,'') as varchar) as PagesPerFrag
       , CAST(CAST(Coalesce(a.page_count,'') as bigint) as varchar) as NumPages
       , Coalesce(a.index_type_desc,'') as IndexType
       , CAST(Coalesce(a.index_depth,'') as varchar) as index_depth
       , CAST(Coalesce(a.index_level,'') as varchar) as index_level
       , CAST(Coalesce(a.avg_page_space_used_in_percent,'') as varchar) as avg_page_space_used_in_percent
       , CAST(CAST(Coalesce(a.record_count,'') as bigint) as varchar) as record_count
FROM sys.dm_db_index_physical_stats(DB_ID('CMDB_DATABASE'),NULL, NULL, NULL , 'SAMPLED') AS a
JOIN sys.indexes AS b
   ON a.object_id = b.object_id AND a.index_id = b.index_id
WHERE a.avg_fragmentation_in_percent > 15 and record_count > 100000
ORDER BY OBJECT_NAME(b.object_id),a.index_id;

Send Help Center feedback