How can we help?

My Cases

Defragment an ESS Database on Microsoft SQL Server
Last Updated: Dec 15, 2017 04:57PM EST

This article applies to:  Echo360 Admins

Summary

Over time, the ESS database can become fragmented, leading to longer queries and slower performance. This document explains how to defragment an ESS database running on Microsoft SQL Server.

Relevance

With the addition of the media consolidation background job and the script to purge media orphans, any customer using Microsoft SQL Server for their ESS database in EchoSystem 5.3 or greater would likely benefit from this article.

Process

The EchoSystem documentation has information on the database consolidation and restructuring which was introduced in EchoSystem 5.3, and contains this link:

https://www.simple-talk.com/sql/database-administration/defragmenting-indexes-in-sql-server-2005-and-2008/

This document gives a general overview on defragging a Microsoft SQL Server database. However, in order to actually do this, it is important to identify all the ESS database indexes that need to be defragmented. The following script will identify which indexes need to be defragmented:

********************************************************************************
SELECT
   OBJECT_NAME (ips.[object_id]) AS 'Object Name',
   si.name AS 'Index Name',
   ROUND (ips.avg_fragmentation_in_percent, 2) AS 'Fragmentation',
   ips.page_count AS 'Pages',
   ROUND (ips.avg_page_space_used_in_percent, 2) AS 'Page Density'
FROM sys.dm_db_index_physical_stats (DB_ID ('DB_NAME'), NULL, NULL, NULL, 'DETAILED') ips
CROSS APPLY sys.indexes si
WHERE
   si.object_id = ips.object_id
   AND si.index_id = ips.index_id
   AND ips.index_level = 0;
GO

********************************************************************************


  1. We may ignore a fragmented index if the page count is less than 1000.
  2. If fragmentation (avg_fragmentation_in_percent) is greater then 40%, we need to check page density (avg_page_space_used_in_percent).
  3. If page density is lower than 60, then an Apache Lucene index rebuild is necessary per this article's solution.
c9f5f1d87ac29bd0c146e9565da3c739@echo360.desk-mail.com
https://cdn.desk.com/
false
desk
Loading
seconds ago
a minute ago
minutes ago
an hour ago
hours ago
a day ago
days ago
about
false
Invalid characters found
/customer/en/portal/articles/autocomplete