Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

SQL Server - How to tune insert/update/delete on very active Tables

Posted on 2013-11-22
6
Medium Priority
?
434 Views
Last Modified: 2014-02-06
Hi All,
I have some really active tables mainly with insert/update running against them.
There are lots of waits and as a result I often come across Deadlock. Once application hit deadlocks it often crashes some processes and I have to manually run those transactions. It's a pain.

How do I tune insert/update/delete so that these waits are gone for good.

Thanks
0
Comment
Question by:crazywolf2010
6 Comments
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 39668605
so are your deadlocks in general caused by the applications waiting for resources across tables or within single tables?

userA    update TableA    wants to update tableB

userb    updated tableB  wants to update tableA

or

userA   update tableA row1   wants to update another tableA row2

userb   updated tableA row2  is still doing processing...


or is it normally more complex with with more than 2 users/connections involved?

in general
you may be able to change the clustering/partitioning  indexes to reduce contention if its page locks which are creating the problems...

you maybe able to influence the program design so that (in general) Table A is always updated before TableB

you may find that adding a logical deleted status column , instead of actually doing a DELETE (ie a delete becomes an UPDATE) will assist as DELETES are (usually) much more expensive than updates... (and run the physical delete in a batch period...)


the other standard TUNING mechanic apply ... ensure that the SQL actually needs to be performed... ensure that code delay between sql statements is minimised...
look to tune the actual statements usually involved...

e.g..

        update   row1

        select something
        insert something

         update row 2


ccould that be changed
   to
select something

update row1
 insert something
update row2

so that the lock for row1 is minimised....
0
 

Author Comment

by:crazywolf2010
ID: 39668636
Hi,
I think I forgot to add my attachment report for Tables. Could you have a look and comment how do I optimise these tables?
ActiveSQL.txt
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 39668703
its not clear what your report is actually showing...

the only comment i can make is that you appear to have 2 updates consecutively for the same table (and same basic row id...)

UPDATE NmsBroadLogRcp

could the updates  be combined into one statement and the actual
updated columns be specified by case statements in the set conditions...
*ie  move the selectivity from the where clause into case statement on the set condition

that could reduce the workload... (alternatively it could increase it if the set of rows affected significantly increases...)
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
LVL 8

Expert Comment

by:gpizzuto
ID: 39668824
You can queue the requests by creating a table in wich your requests are saved and then, execute each command one at a time...
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 39669060
How many indexes you have on that table. Keep in mind that every time you insert/update/delete all the indexes have to be updated which leads to a lot of extra work. I would recommend you to limit the indexes at a strict minimum.

Another thing is if you are running selects against that table you should use (nolock) hint:

select * from yourtable t (nolock)

or in inner join:

select
      *
from
      yourtable t (nolock)
      inner join anothertable a (nolock)
            on a.fk_col=t.pk_col
0
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 2000 total points
ID: 39669283
By far the single best tuning is to get the correct clustered index; do that FIRST.

Then you can work on/worry about other things.

Based on the extremely limited number of queries posted so far, I'd suggest verifying that:
NmsBroadLogRcp is clustered on iBroadLogId


Btw, I'd avoid using any logical delete flags, as they usually destroy SELECT performance and aren't necessary for large DELETEs anyway, since SQL itself automatically defers those physical DELETEs until later.
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Suggested Courses

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question