Page Level Locking in database indexes

Monitoring and reporting for Veeam Backup & Replication, VMware vSphere and Microsoft Hyper-V

Page Level Locking in database indexes

Veeam Logoby fmt1962 » Thu Feb 13, 2014 11:04 pm

I have recently moved my DB over to a full version of SQL server 2008 R2.

As standard practice on this DB server, all DB's are backed up and maintenance conducted for optimisation etc, this includes a periodic reindexing.

However since I moved VeeamOne database over to the SQL server, the indexing operation has failed due to 4 indexes in VeeamOne having page level locking disabled.

businessview.ObjectProperty|IDX_ObjType
businessview.ObjectProperty|IDX_ParentType
reporter.LatestObjectProperty|IDX_LatestObjectPropertyObjectType
reporter.LatestObjectProperty|IDX_LatestObjectPropertyParentType

The reindexing problem can be fixed by enabling page level locking, however I am concerned that this setting may have been deliberate, as it is very unusual to find this disabled in any database.

I can only suspect that maybe Veeam is aware of a deadlock possibility with this enabled??? I cannot think of any other good reason this would be done? Sometimes it is unintentional, however I would like to check if it is appropriate to enable page level locking so this DB can be included in my maintenance plan, or should these settings be kept disabled?

Any ideas?
Daryl
fmt1962
Influencer
 
Posts: 12
Liked: 3 times
Joined: Tue Jul 31, 2012 9:04 am
Full Name: Daryl W

Re: Page Level Locking in database indexes

Veeam Logoby Vitaliy S. » Fri Feb 14, 2014 2:09 pm

Hello Daryl,

Thanks for the feedback, I have passed this info to our dev team. As for now, you can enable page level locking manually.

Thank you!
Vitaliy S.
Veeam Software
 
Posts: 19558
Liked: 1102 times
Joined: Mon Mar 30, 2009 9:13 am
Full Name: Vitaliy Safarov

Re: Page Level Locking in database indexes

Veeam Logoby fmt1962 » Mon Feb 17, 2014 9:32 pm

Hi Vitality S.

It is obviously a deliberate setting as I have found that the setting for the "IDX_LatestObjectPropertyObjType" (partition 1) on table "LatestObjectProperty" has been reset to disabled, so the application must have reset this.

So I will re-enable all of them and have to seek further advice, as obviously now the settings are disabled. However the question is now, should I exclude these databases entirely from standard optimisation maintenance?

I have opened ticket #00518438

Regards,

Daryl.
fmt1962
Influencer
 
Posts: 12
Liked: 3 times
Joined: Tue Jul 31, 2012 9:04 am
Full Name: Daryl W

Re: Page Level Locking in database indexes

Veeam Logoby Vitaliy S. » Tue Feb 18, 2014 7:54 am

Hi Daryl,

I have just consulted with our dev team and you exclude can this table from indexing "reporter.LatestObjectProperty". This table contains the latest data on the objects which is rebuilt every collection cycle.

Thank you!
Vitaliy S.
Veeam Software
 
Posts: 19558
Liked: 1102 times
Joined: Mon Mar 30, 2009 9:13 am
Full Name: Vitaliy Safarov

Re: Page Level Locking in database indexes

Veeam Logoby mistafu » Mon Apr 25, 2016 7:24 am

Hello,

since update to V9 i've got the same problem with periodic reindexing, veeam db doesn't allow page locks.
Can i still just change allow_page_lock 0 to allow_page_lock 1 for the reindexing?

Andreas
mistafu
Novice
 
Posts: 3
Liked: 1 time
Joined: Mon Apr 25, 2016 7:17 am

Re: Page Level Locking in database indexes

Veeam Logoby mistafu » Thu Apr 28, 2016 7:24 am

I don't know how to edit my post...
it's about the VeeamONE database not the normal Veeam db.
mistafu
Novice
 
Posts: 3
Liked: 1 time
Joined: Mon Apr 25, 2016 7:17 am

Re: Page Level Locking in database indexes

Veeam Logoby Shestakov » Thu Apr 28, 2016 12:42 pm

Hello Andreas,
Yes, you can use page locking. You may also enable it only to the failing indexes if you have the similar issue as OP.
Thanks!
Shestakov
Veeam Software
 
Posts: 4861
Liked: 395 times
Joined: Wed May 21, 2014 11:03 am
Location: Saint Petersburg
Full Name: Nikita Shestakov


Return to Veeam ONE



Who is online

Users browsing this forum: No registered users and 2 guests