Database maintenance - mdb database

[ipsku01]

Hello out there,

what do you or your customers do for mdb database maintenance. I'm especially interested in maintenance of Indexes.

How do they maintain indexes, how often, with or without shutting down SDM etc.

 

The reason I'm asking is that I have customer where we experience database locks every now and then caused by Index maintenance. It happens every 4 - 8 weeks where a query operation from SDM locks with the ALTER INDEX operation from the maintenance job. It is now about to reach escalation point - so any insight from you guys out there would be very appreciated.

 

Thanks.

 

/Kurt

[Derek (Chun) Li]

Hi Kurt,

Generally, SDM should be shut down during database maintenance such as patching.

 

Regards,

Derek

[ipsku01]

Hi Derek,

generally I agree, but this is a 24/7 Global shop of some size too:

2,500 analysts
55,000 employees

53,000 Incidents/Problems/Requests per month

13,000 Change Orders per month

So that's why I'm interested in what other similar installations do.

/Kurt

[Derek (Chun) Li]

If it is 24/7 non-stop, the DB should be in High availability setting. The DB operation cause locks should inform the situation to the DBA. Is the DBA aware of it?

[ipsku01]

Good point you are bringing up there. SQL Server isn't in HA mode - it is a single server with the database in Full Recovery mode though. The DBA is also working on this from the SQL side, but has not been able to bring anything useful to the table yet.

[Derek (Chun) Li]

In SDM have clear the lock from Admin tab >System >Current Locks when there is locks.

As it is server operation causing the lock, SDM as client in the standpoint can't do too much until the server side taking care of it.

May someone have other suggestions.

[Jon_Israel]

Hi Kurt,

Unfortunately unless you had some type of SQL Cluster going and could do maintenance on one and not the other, it would require SDM to be down while doing that maintenance.  SDM doesnt use "sessions" to communicate with SQL such as odbc, but rather it uses a "constant concurrent connection" which requires SQL to be available 100% in order for SDM to work properly.  Unfortunately there is no way around that at this time from our side.  Not sure if HA mode would help as usually its the SQL application that is failing over, but the DB is stored in the same space between the cluster nodes, so it may still cause issues if that shared space is being worked on.

Anyone else out there willing to share their ideas or methods of doing this?

Jon I.

[Grant Bruneau]

Our environment is configured in a sql server always on environment. I can confirm that running a rebuild index command online will still cause delays until it’s been rebuilt. 

 

In my research I came across the following post that describes how to setup an automated reindex  process that gets triggered when an index is fragmented at a specific percentage.  I plan to review this with my dba at some point but haven’t found time. If implemented properly it may help your situation. It would need to be scheduled to run at time when it would be least impactful to users.

 

Index Defrag Script, v4.1 SQL Fool 

[ipsku01]

Hi Grant,

 

thanks for your input. My customer does not have Always On, so that could be something to pursue.

 

How often are you running index optimization?

and what is the approximate size of the system in terms of new tickets per month?

 

Thanks.

 

/Kurt

[yona1987]

Hello,

 

If you are looking for best practices to maintenace database the first step is to configure some cluster, personally if you are working with SQL, you could try AlwaysOn its really good, some customer have it since 2016 and everithing are working very well, this customer has 250.000 Incident/Problem/Request per month 25.000 Changes per month, after you configure some cluster, your DBA should configure some process to reindex, to update statistics, among others. We have some daily and weekly process to do that. You also can try to increase your DB agent from USD, create some DB agent for the biggest table. There are improvements that you can do from the service desk side

 

How i said the principal improvement is configure a cluster

 

Regards,

[Louis van Amelsfort]

Good Afternoon Kurt.
 
While researching for you on this, I came across the following document:
'MDB Proper Care and Feeding of your SQL MDB.pdf'
 
To be accessed via the below link:
https://support.ca.com/phpdocs/0/common/impcd/r11/MDBMain/Doc/MDB03_Proper%20Care%20and%20Feeding%20of%20your%20SQL%20MDB.pdf

 

Not sure though whether this answers your questions. However, it is a useful document as is.

 

Please check on this and let us know your findings?
 
Thanks and kind regards, Louis.

[ipsku01]

Hi Louis,

I'm familiar with the document you refer to. It is indeed a useful document, unfortunately we are in this case a bit beyond it.

 

/Kurt

[Lumir_Botur]

Hi ipsku01

 

Here is another TEC about MS SQL maintainance. I hope it helps TEC580653

 

Bye

Lumir

[ritri01]

Hi Kurt, just to throw this out there, whenever I hear issues with "locks" please make sure the srel_blocks_timeout option is installed, check the nx.env for the variable.  Info on how to install here;

CA SDM Performance Problems - Quick Checklist - CA Service Management - 14.1 - CA Technologies Documentation 

 

Regards,

Richard

[Jon_Israel]

Thanks for sharing ritri01!   In this case the locks are not coming from the application, but are happening on tables when SQL Server maintenance is performed.    But its always a good idea to put that option in place anyway if you ever experience locks from the application that are not released.

Jon

[JEROME MAYER]

A small add at this seems to be some confusion here.

Either if a Always on setup will be recommend based on your volumes, a MSSQL cluster will not prevent a lock when the index is been rebuild. This will only help to speed up the rebuild itself making a lock more unlikely.

 

The best way to handle this is to  not wait that your index been too much fragmented before reindexing. You dba must monitor the grow of the defragmentation and identify the pattern.

Based on that pattern identified, schedule a periodic reindexing for those. Then the operation will take less time to complete

 

This is also important to split your jobs for reindexing across the time meaning make sure that you don't rebuild all your index on all you table at same time but identify the one consuming and scheduled those separately

 

Make sure you DBA is using online index rebuild vs.offline. (assuming you have an SQL enterprise)

Online will only lock twice during a online reindexing of a table at start and the end of the index and this is really short.

Therefor you may want also to strongly look at the overall performance of you DB server and do the necessary action to optimize.

A bottleneck for indexing been mostly I/O performance (make sure your infra can support I/O at top speed, more specially if you run your SQL server on VM where latency will be higher)

 

Note also that an important contributor of fragmentation is free space in your database.

First the auto growth of your DB must never use % in your setting but directly MB.

Depending of your growth make sure that put enough there. at the price of HDD today don't hesitate to put 100 mb or more there for both the mdb and mdb (look at the actual size of you mdb and mdb_log and ensure that amount there correspond at less to 10% of the size)

If you own MSSQL 2014 or higher you can also take advantage of the Managed Lock Priority that was introduced there.

You will then be able to define a low priority for the ALTER INDEX.

There is a nice article there on it:

https://www.sqlshack.com/control-online-index-rebuild-locking-using-sql-server-2014-managed-lock-priority/

 

DBA staff and responsibility but hope this help your conversation with him.

 

My 2cents

/J