2

I have a little problem with a two-node mirror and customer requirements:

There is a maintenance window every night from 01:30 to 03:00, within which I have to run rebuild/reorg and checkdb (physical_only). If Murphy happens both jobs run on the same index/table and SQL Server creates a (mini-)dump. Due to the dump, the principal doesn't respond within the set timeout period and the mirror roles change, leading to app problems.

We just have that 90 minute window during the night and customer/PFE wants us to run both jobs in this window. DBCC runs for ~80min and rebuild/reorg for ~30min.

Any suggestions to get around those Murphy-moments or to avoid that dump?

  • SQL Server 2012 SP3 CU7 used by an older Axapta Installation DB-Size
  • 900GB-1TB (data files on SSDs)

Index Maintenance with Ola Hallengren:

EXECUTE dbo.IndexOptimize @Databases = 'PROD',
@FragmentationLow = NULL,
@FragmentationMedium = NULL,
@FragmentationHigh = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 50,
@FragmentationLevel2 = 80,
@SortInTempdb = 'Y',
@MaxDOP = 0,
@LogToTable = 'Y',
@TimeLimit = 3600

DBCC CHECKDB also from Ola Hallengren with Physical_only option.

I don't know if there is a "real" need to run those rebuild/reorgs every night but the customer told us to do so (also suggested by MSFT PFE).

We're in communication with the customer about expanding the maintenance window, but I want to know if there are other suggestions/tips.

The actual DBCC CHECKDB error message is:

DBCC CHECKDB (XXXX) WITH all_errormsgs, no_infomsgs, physical_only executed by Username found 2 errors and repaired 0 errors.
Table error: Object ID 111391516, index ID 2, partition ID 720 57595795734528, alloc unit ID 72057595826864128 (type In-row data), page (7:14130686).
Test (IS_OFF (BUF_IOERR, p BUF->bstat)) failed.
Values are 133129 and -4

0
2

Defragmenting indexes when those indexes are stored on Flash/SSD is close to pointless. I'd recommend performing the reindex far less frequently. See Paul's answer here regarding page density and fill factor for mitigating the effects of index fragmentation.

Updating statistics, on the other hand, will provide important clues to the query optimizer. If auto-update-stats is disabled for the given database, ensure you are updating statistics via a nightly job.

Ensure you run DBCC CHECKDB as often as possible to ensure corruption does not negatively affect your RPO. You may consider offloading DBCC CHECKDB to a non-production instance. This consists of automated backup-and-restore to the non-production instance, then automatically running DBCC CHECKDB on that non-production instance. You'll probably need to license the machine where you run DBCC CHECKDB since it would be dealing with production data, however I'd check with your Microsoft rep to be certain.

If you're seeing SQL Server creating memory dumps, that indicates a problem with SQL Server that should be brought to the attention of Microsoft Tech Support - they may either have a hotfix available to resolve the issue, or they may offer some further advice about how to resolve that issue. The problem may indeed be a bug that needs to be fixed.

1
  • Or just defrag the tables and show them it made no difference to performance. – Hannah Vernon Aug 11 '17 at 16:34
0

Community wiki answer:

If SQL Server is crashing, that is a defect and you need to open a support incident with Microsoft so that they can fix it - or if you're lucky, they already have a fix for it.

With Axapta, you may be able to disable or drop numerous indexes. The product comes with a number of indexes, but if certain components of the application are not being used, some indexes may not be used. Fewer indexes = faster reindexing.

Another option is to add logic to the job so that it waits until the other one is finished before it starts.

Or, simply skip the index maintenance and just update statistics. Checkdb is far more important.

Reindexing on flash storage can be rather pointless from a technical perspective, but it removes a barrier when people who don't understand it are complaining about a performance issue. Developers and level 1 tech support people, for example, often want to blame fragmented indexes for performance issues, and if you can show them that your indexes aren't fragmented, you can quickly move the troubleshooting process forward. Otherwise, you have to educate them why those 10,000 blog posts about fragmented indexes causing performance issues are not applicable.

CheckDB normally runs on a database snapshot copy of the database that is transactionally consistent. This means your two workloads (checkdb and rebuilds) shouldn't affect each other, except to compete for physical resources.

If CheckDB is returning an error, that's a problem. Generally, if CheckDB hits really bad errors a memory dump will occur.

Your error is an IO error (BUF_IOERROR == true) so I would check your disk subsystem and not look at the maintenance. If your disk subsystem is going to throw IO errors when it gets hit hard - that's a pretty big problem that needs to be fixed or you're going to run into this without doing checkdb/index maintenance.

0

I want to come back to you with customers decision... and whole "solution" for this.

Customer made improvements to his own "ETL"-jobs so that they were able to extend their maintenance window... now we're running both jobs in one (second step). First (more important) step is DBCC CHECKDB and second is the index maintenance.

Complete runtime is between 1:50 and 1:57 which fits totally in the maintenance window.

Thanks for your support!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.