Maintain Zylinc databases

Zylinc uses relational databases. Relational databases store data in tables. A table can contain one or more indexes. The purpose of indexes is to optimize query times; just like an index in a book makes it easier to find particular content in the book.

However, database indexes can get fragmented, so that they don't work as efficiently as before. The result is that database query times go up. Fortunately, you can view metadata about the indexes, and that can tell you when it's time to defragment your indexes.

A high fragmentation percentage for an index can severely affect query times, especially if the index is big and uses many database pages to store its data.

On the other hand, a small index can, because of the way SQL Server is designed, show a high fragmentation percentage without affecting query times. This is because up to eight different indexes may share the pages that SQL Server stores small indexes on. When you defragment small indexes that have low page count values, the fragmentation percentage may not be reduced.

You can typically ignore high fragmentation percentage values, if page count is a relatively small number, for example less than 1000.

If database indexes in your ZyDB or ZyStatDB, with a high number of pages, becomes more than 30% fragmented, many queries will begin to take noticeably longer. If you reach 80% fragmentation, you can no longer expect the databases to work on a busy day.

According to Microsoft recommendations, you need to reorganize indexes (ALTER INDEX REORGANIZE) when an index reaches a fragmentation level between 5% and 30%.

If fragmentation is above 30%, you need to rebuild indexes (ALTER INDEX REBUILD).

Therefore, defragment your Zylinc-related databases as soon as an index with a high number in page count becomes more than 10% fragmented.

When you update the databases, or delete large amounts of data from a database, they'll often get fragmented, so always check their fragmentation levels after updates or deletions.

If you run the following script, it'll display a list of indexes with fragmentation greater than 30% and page count greater than 1000 pages. You can change the values if want.

If you want to view all indexes, regardless of fragmentation percentage and page count, delete the last two lines from the script.

1. Start SQL Server Management Studio, and connect to the SQL Server.

2. Click New Query.

3. Copy/paste this SQL code into the query window, and replace the second word ZyDB with the name of the database that you want to view index fragmentation percentage and page count for.

   Copy

   use ZyDB
   SELECT db_name(db_id()) as database_name, tables.name as table_name,indexes.name as index_name, stats.avg_fragmentation_in_percent ,stats.page_count
   FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS stats
   INNER JOIN sys.tables  AS tables  ON tables.object_id  = stats.object_id
   INNER JOIN sys.schemas AS schemas ON tables.schema_id  = schemas.schema_id
   INNER JOIN sys.indexes AS indexes ON indexes.object_id = stats.object_id AND stats.index_id = indexes.index_id
   WHERE stats.database_id = DB_ID()
   AND indexes.name IS NOT NULL
   AND avg_fragmentation_in_percent>30
   AND page_count >1000

4. Click Execute to run the script.

5. Remember to repeat the procedure for both your ZyDB and ZyStatDB databases.

As opposed to ZyDB, the statistics database ZyStatDB is likely to contain more and more historical data over time. That's why it can be a good idea to use SQL Server Management Studio to delete any unwanted old data in ZyStatDB, so it doesn't grow unnecessarily large.

Always create a backup of the database before you delete old data, because there's no way to restore the old data once you've deleted it. It's also a good idea to create a backup before you execute any SQL statements.

The actual name of ZyStatDB is defined during the installation of each Zylinc solution, so bear in mind that it can be different from installation to installation.

You can delete rows older than a certain date from the channel table. All related records, like channel_events, queue_channels, etc., will be deleted due to the foreign key constraints.

So, if you want to delete all calls prior to 1st of January 2017, run a command like this:

DELETE FROM channel WHERE StartTime < '2017-01-01'

You'll then still have old data stored in the user state tables:

• user_connected

• user_state

• user_workstate

• user_chatstate

• user_mailstate

• user_aggrstate

You can remove that old data too, if you run a command like this:

DECLARE @oldDate DATETIME = '2017-01-01';

DELETE FROM user_connected WHERE TimeStart < @oldDate;

DELETE FROM user_state WHERE Time < @oldDate;

DELETE FROM user_workstate WHERE Time < @oldDate;

DELETE FROM user_chatstate WHERE Time < @oldDate;

DELETE FROM user_mailstate WHERE Time < @oldDate;

DELETE FROM user_aggrstate WHERE Time < @oldDate;

When you delete large amounts of data from a database, the database can easily become fragmented, so it's a good idea to subsequently rebuild all indexes for all the tables in the database (see the previous).