July 30, 2010 at 4:25 am
Hi experts,
We have a query causing locks and how can we pin point that a particular object is locked??
July 30, 2010 at 4:52 am
Queries causing locks is normal. They're supposed to take locks to ensure consistency and isolation.
To see what locks a connection has, query sys.dm_tran_locks.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 30, 2010 at 6:56 am
Thanks Gail,
How can we avoid that??
Its a procedure which takes data from a table inserts into table variable and displays that...I know its difficult to tell something from wat i wrote but from ur experience i am sure u would have come across such situation and some tip wil definitely help
July 30, 2010 at 7:08 am
Avoid what? Taking locks?
Taking locks is normal and expected and without taking locks SQL would be risking violating the requirements of isolation and consistency. Locks are NOT a problem.
Excessive blocking causing slow queries is a problem (often caused by badly written T-SQL or incorrect indexes). Deadlocks are a problem (often with the same cause). Locking is not a problem that needs to be prevented.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 1, 2010 at 10:26 pm
Thanks Gail...
The problem is that its taking more time to get data seleteced from a table..when checked found to have xclusive locks on many tables..
There is update on few rows to those tables but why an xclusive lock on table??
August 2, 2010 at 3:38 am
Most likely poor indexing, poorly written query, or you're updating a significant portion of the table.
Again, locks, even exclusive table locks aren't a problem. It's when other things need concurrent access and get blocked that there's a problem. So do you have blocking, or just a lot of locks?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 2, 2010 at 4:08 am
Thanks alot Gail..
No blocking or deadlocks just hundreds of locks
August 2, 2010 at 4:20 am
So why are you worried? They're not affecting other queries, not causing blocking, not causing deadlocks, hence not a problem. Why are you worries about something that isn't causing a problem?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 2, 2010 at 4:22 am
Ratheesh.K.Nair (8/1/2010)
The problem is that its taking more time to get data seleteced from a table..when checked found to have xclusive locks on many tables..
Because of blocking? Are you seeing that select waiting for a lock to be released?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 2, 2010 at 4:25 am
Hi Gail,
we uses a view of two select queries with no.of joins and these two quies retriving data using UNION all.
it clousing deadlock
how i optimize it....i checked indexex are currect.
how i decide it is a poor query.....
------------------this is the view
CREATE view [INMISPR01].[ANH_Cancellation_Report] as
--set dateformat 'dmy'
SELECT RecCustomerInformation.PolicyNo as [Policy No], RecCustomerInformation.CustomerName as [Policy Holder],
RecDataEntry.CCNo as [CC No.], RecDataEntry.RefundAmount, [Billed Date]=cast(RecDataEntry.BilledDt as nvarchar(12)),
[Pol Eff. Date]=cast(RecCustomerInformation.PolicyEffectiveDate as nvarchar(12)),
Premium=isnull(RecCustomerInformation.Premium,0),RecDataEntry.CancelledEmma, [Cancel Date]=
cast(RecDataEntry.cancellationDate as nvarchar(12)),RecDataEntry.EndorsementDate ,
[Refund Date]=cast(RecDataEntry.Refunddate as nvarchar(12)), [Sec Rfnd Amt]=RecDataEntry.SndRefundAmount,
[Refund Date2]=cast(RecDataEntry.SndRefundDate as nvarchar(12)), RecDataEntry.Remarks,[HSBC Date]=
cast(RecDataEntry.HSBCRecDt as nvarchar(12)), RecDataEntry.HSBCBatchNo, RecDataEntry.RefundAmount as Amount,
CAST( RecDataEntry.BilledDt AS nvarchar(12)) as [Transaction Date],CAST( ReccustomerInformation.PolicyPrintDate AS
nvarchar(12)) as [Policy Print Date], DocumentsRecDate, null as VLIDATE
FROM RecDataEntry INNER
JOIN RecCustomerInformation ON RecDataEntry.PolicyNo = RecCustomerInformation.PolicyNo
Inner join RecCallDetails on reccustomerInformation.TxnrecID=ReccallDetails.Txnrecid
where
--RecDataEntry.EndorsementDate between '10/14/2009' and '10/14/2009' and
RecDataEntry.TxnID in ( select max(TxnID)
from RecDataEntry group by PolicyNo)-- and RecDataEntry.DocumentsRecDate is not null
and reccalldetails.TxnID in ( select max(TxnID)
from reccalldetails group by PolicyNo)-- and RecDataEntry.DocumentsRecDate is not null
--Comment By Priya Arora dated 0n 14-10-09
and reccalldetails.policyno not in(select distinct policyno from reccalldetails)
--where vlidate is not null)
--order by RecDataEntry.policyno
--Comment By Priya Arora dated on 14-10-09
union all
SELECT RecCustomerInformation.PolicyNo as [Policy No], RecCustomerInformation.CustomerName as [Policy Holder],
RecDataEntry.CCNo as [CC No.], RecDataEntry.RefundAmount, [Billed Date]=cast(RecDataEntry.BilledDt as nvarchar(12)),
[Pol Eff. Date]=cast(RecCustomerInformation.PolicyEffectiveDate as nvarchar(12)),
Premium=isnull(RecCustomerInformation.Premium,0),RecDataEntry.CancelledEmma, [Cancel Date]=
cast(RecDataEntry.cancellationDate as nvarchar(12)), RecDataEntry.EndorsementDate ,
[Refund Date]=cast(RecDataEntry.Refunddate as nvarchar(12)), [Sec Rfnd Amt]=RecDataEntry.SndRefundAmount,
[Refund Date2]=cast(RecDataEntry.SndRefundDate as nvarchar(12)), RecDataEntry.Remarks,[HSBC Date]=
cast(RecDataEntry.HSBCRecDt as nvarchar(12)), RecDataEntry.HSBCBatchNo, RecDataEntry.RefundAmount as Amount,
CAST( RecDataEntry.BilledDt AS nvarchar(12)) as [Transaction Date],CAST( ReccustomerInformation.PolicyPrintDate AS
nvarchar(12)) as [Policy Print Date], DocumentsRecDate, VLIDATE
FROM RecDataEntry INNER
JOIN RecCustomerInformation ON RecDataEntry.PolicyNo = RecCustomerInformation.PolicyNo
Inner join RecCallDetails on reccustomerInformation.TxnrecID=ReccallDetails.Txnrecid
where
--RecDataEntry.EndorsementDate between '15/05/2007' and '15/05/2007' and
RecDataEntry.TxnID in ( select max(TxnID)
from RecDataEntry group by PolicyNo)-- and RecDataEntry.DocumentsRecDate is not null
and reccalldetails.TxnID in ( select min(TxnID)
from reccalldetails
--where VLIDATE is not Null
group by policyno)-- and RecDataEntry.DocumentsRecDate is not null
--order by RecDataEntry.policyno
Regards,
Shivrudra W
August 2, 2010 at 4:35 am
Shivrudra (8/2/2010)
Hi Gail,we uses a view of two select queries with no.of joins and these two quies retriving data using UNION all.
it clousing deadlock
how i optimize it....i checked indexex are currect.
how i decide it is a poor query.....
Please post new questions in a new thread. Don't hijack someone else's thread and problem.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply