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

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.

| improve this answer | |
  • >>>You may consider offloading DBCC CHECKDB to a non-production instance<<< Yes of course, but NOT Physicals_only, and it's exacly what he does. Another instance is always anothe IO subsystem, and if the disk is corrupted on one server it can be fine on another – sepupic Aug 11 '17 at 14:24
  • good point. Of course, if you run DBCC CHECKDB on another non-production system, you'd run the full DBCC CHECKDB, since ostensibly you'd not be limited time-wise in the way that running PHYSICAL_ONLY would help mitigate. – Max Vernon Aug 11 '17 at 14:32
  • Or just defrag the tables and show them it made no difference to performance. – Max Vernon Aug 11 '17 at 16:34
  • 1
    I will re-think your answer and ask the right questions to the right people ... let's see what comes out ... the SQL Server or the application is controlled weekly by a SQL PFE and an Axapta PFE ... from which came the recommendation for rebuilds / reorgs. But your answers like me very much, I had expected with something. THANK YOU I will forward it on Monday and come back to you asap – Björn Peters Aug 11 '17 at 18:31
  • 1
    Today I talked to the customer and told him about your suggestions and that I would prefer the solution only to update the statistics within that maintenance window... they will discuss that with MSFT and internally. – Björn Peters Aug 16 '17 at 9:49
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!

| improve this answer | |

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.