DX Unified Infrastructure Management

  • Private Community
 View Only

  • 1.  QoS Insert Queuing to a SQL Backend During Re-index Job

    Posted Jun 06, 2016 11:28 AM


    Has anyone experienced QoS queuing during re-indexing maintenance jobs? Our installation is large enough that CA recommends we don't enable "Index Maintenance" in the data engine probe. They recommend we do it directly in SQL enterprise edition. How is everyone else handling their re-indexing process?

     

    Please respond with robot count, db size, and method if you can. Thanks!

     

    We tried switching our back-end job to run weekly starting at 1am. This caused the job to take 19+ hours to run and you can see the effect it had on our QoS writing.

     

    imageFile.png



  • 2.  Re: QoS Insert Queuing to a SQL Backend During Re-index Job
    Best Answer

    Broadcom Employee
    Posted Jun 06, 2016 12:13 PM

    Make sure your index job is set to rebuild (not reorganize) the fragmented indexes, and make sure it is using the WITH ONLINE=ON option.  This should avoid locking the QoS tables while they're being re-indexed.

     

    This is ultimately benign behavior to some extent - the data_engine is smart enough to queue up the data for any locked tables and insert it later.



  • 3.  Re: QoS Insert Queuing to a SQL Backend During Re-index Job

    Posted Jun 07, 2016 09:36 AM

    Yeah. The backup in data is what's killing our users. They go to look at the their reports or dashboards and data is hours old. I'll recommend those values to our DBA though. Thank you.



  • 4.  Re: QoS Insert Queuing to a SQL Backend During Re-index Job

    Broadcom Employee
    Posted Jun 07, 2016 09:51 AM

    Is your database partitioned?

     

    If I am not mistaken, it may not be possible to rebuild partitioned indexes online in SQL 2012 and prior, while SQL 2014 offers the option to reindex partitioned tables online a partition at a time (vs. reindexing the entire table.)  Your DBA may know more about this.  If that's the case, reorganize may be a better choice than rebuild - it's always an online operation as far as I know.