Can you tell me the difference between nolock and rowlock in sql server?

Dear friends, i am working for performance tuning and our ERP software for reports taking Screen sp taking so much time to execute the statment.kindly tell me the difference the row lock and nolock hints.is this affected the performance of the coding.

update  si_supplier_balance  with (rowlock)
    set       ob_credit     = isnull(ob_credit,0)  + @cr_amount_tmp,
         cb_credit       = isnull(cb_credit,0)     + @cr_amount_tmp,
         ob_debit        = isnull(ob_debit,0)  + @dr_amount_tmp,
         cb_debit     = isnull(cb_debit,0)   + @dr_amount_tmp,
         last_modified_by = @userid_in,
         last_modified_date   = @system_date_tmp
    from    fcc_sysact_allperiod_vw FBP (nolock), 
         si_supplier_balance  BAL --(nolock) -- Porselvi.J - SIDMS412AT_000013
    where  FBP.company_code     = @company_code_tmp
    and    FBP.fin_year_code     = BAL.fin_year
    and       FBP.fin_period_code = BAL.fin_period
    and       BAL.ou_id     = @ouinstance_in
    and       BAL.fb_id     = @fbid_in  
    and       BAL.account_code    = @accountcode_in
    and       BAL.balance_currency      = @bal_currency_in
    and     BAL.currency_code      = @tran_currency_in
    and    BAL.balance_type      = @bal_type_in
    and    BAL.supplier_code   = @supp_code
    and    FBP.sequenceno      >= @post_prd_seq_tmp
and BAL.fb_id = FBP.fb_id
more ▼

asked Jan 20, 2012 at 03:41 AM in Default

cstephen gravatar image

234 24 27 29

Ha, I was just formatting the code myself @Kev.
Jan 20, 2012 at 03:48 AM Grant Fritchey ♦♦

Looking at the code, nothing jumps out as being obviously problematic, so the only way to be sure what's wrong is to look at the execution plan.

I do note that you're using ANSI 89 style JOIN where you put the JOIN syntax somewhere in the WHERE clause. I would suggest you start using ANSI 92 style with JOIN... ON... style syntax. Two reasons. First, it's much more clear. Second, for OUTER JOINs in 2008R2 and better, you can't use the ANSI 89 style at all.
Jan 20, 2012 at 03:50 AM Grant Fritchey ♦♦
So far you have asked a lot of questions on this forum and have had a large number of answers but have not marked any of them as having helped you. This wont encourage people to keep offering solutions to questions you pose. Would you be able to find some time to review the answers that you have had on other questions and mark appropriate one as helpful by clicking the tick beside them please? It isnt much to ask for all the assistance that is offered across this site.
Jan 22, 2012 at 02:05 AM Kev Riley ♦♦
(comments are locked)
10|1200 characters needed characters left

1 answer: sort oldest

NO_LOCK is suggesting that reads don't take out a shared lock on resources. This leads to bad data being returned by the query including missing rows and extra rows. ROW_LOCK is a way to force the system to use only row level locking over page, extent or table locking.

I don't recommend using hints like this for your queries. What it looks like is you're trying to outsmart the query optimizer to increase performance. Don't do that. Instead, look at the execution plan and understand why you're getting slow downs. It could be the code or it could be the structure.
more ▼

answered Jan 20, 2012 at 03:46 AM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
103k 19 21 74

Dear Grant Fritchey, si_supplier_balance tables has clustered index..IS it affecting the query performance.and shall i use nonclustered index for this table.because it is most important table in finance.
Jan 20, 2012 at 05:27 AM cstephen


Where is the clustered index? Have you displayed the execution plan? Is the index that is clustered being used?
Jan 20, 2012 at 06:13 AM Chris shaw
No way to know if an index is affecting performance negatively or positively by looking at a query. You need to look at the execution plan. In general, clustered indexes are a good thing. But you do need to make sure the clustered index is on the right column(s). But I really can't advise you one way or another based on the information we have currently.
Jan 20, 2012 at 06:27 AM Grant Fritchey ♦♦
(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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

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



Answers and Comments

SQL Server Central

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



asked: Jan 20, 2012 at 03:41 AM

Seen: 2485 times

Last Updated: Jan 20, 2012 at 03:47 AM