x

what's the meaning of resource_description in DMVs sys.dm_tran_locks

in the DMVs sys.dm_tran_locks have columns like reresource_subtype, resource_description , and the values 'INDEXSTATS' ,'object_id = 1019254786, index_id or stats_id = 33'
1) Does it have an especialy scene??
2) what's time would be apply a SCH_S lock??
3) exec SP_REFRESHVIEW will apply SCH-M lock and sql like ' [AdHoc]: EXEC %%Object(MultiName = @name).LockMatchID(ID = @objid, Exclusive = 1, BindInternal = 0) ' how to reduce the block ??

more ▼

asked Nov 19, 2013 at 04:29 AM in Default

avatar image

T.Encore
0 1 1 1

The site works on votes. Please indicate any helpful answers by clicking on the the thumbs up symbol next to them. If any one answer lead to a solution, please indicate that by clicking on the check mark next to that answer.

Nov 22, 2013 at 02:46 PM Grant Fritchey ♦♦
(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

I guess I was confused by the wording of the title and the additional points. It appeared at first you were asking about

what's the meaning of resource_description in DMVs sys.dm_tran_locks

Reading now, it appears you are trying to reduce the amount of locking (SCH-M) when executing the sp_refreshview. And you want to run it every 10 seconds because of replication.

I would ask why this needs to be run every 10 seconds. I can't see using it like that in production. Maybe I am wrong or still don't understand your question.

To reduce the amount of locking you will need to look at how the code is executed and in what order. Depending on what you can/can't change your options may be limited.

more ▼

answered Nov 21, 2013 at 02:47 AM

avatar image

sqlaj 1
5.5k 4 6

(comments are locked)
10|1200 characters needed characters left

Have you seen this resource yet? This TechNet page is a great place to start providing information on each column returned.

sys.dm_tran_locks http://technet.microsoft.com/en-us/library/ms190345.aspx

more ▼

answered Nov 19, 2013 at 12:21 PM

avatar image

sqlaj 1
5.5k 4 6

(comments are locked)
10|1200 characters needed characters left

hi AJ Mendo

The Point is that,the column value is not supported. my main question is execute the sp sp_refreshview will be blocking or blocked the other sql ,who wanna apply the SCH_S lock .but in our environment the sp need be scheduled with every 10 seconds ,becuz the replication. so is there any way to solve it?

more ▼

answered Nov 20, 2013 at 01:51 AM

avatar image

T.Encore
0 1 1 1

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1149
x50

asked: Nov 19, 2013 at 04:29 AM

Seen: 1768 times

Last Updated: Nov 22, 2013 at 02:46 PM

Copyright 2017 Redgate Software. Privacy Policy