Reducing SQL Server Deadlocks

Deadlocking occurs when two user processes have locks on separate objects and each process is trying to acquire a lock on the object that the other process has.

• Ensure the database design is properly normalized.
• Have the application access server objects in the same order each time.
• During transactions, don’t allow any user input. Collect it before the transaction begins.
• Avoid cursors.
• Keep transactions as short as possible. One way to help accomplish this is to reduce the number of round trips between your application and SQL Server by using stored procedures or keeping transactions with a single batch.
• Another way of reducing the time a transaction takes to complete is to make sure you are not performing the same reads over and over again. If you do need to read the same data more than once, cache it by storing it in a variable or an array, and then re-reading it from there.
• Reduce lock time. Try to develop your application so that it grabs locks at the latest possible time, and then releases them at the very earliest time.
• If appropriate, reduce lock escalation by using the ROWLOCK or PAGLOCK.
  Consider using the NOLOCK hint to prevent locking if the data being locked is not modified often.
• If appropriate, use as low of an isolation level as possible for the user connection running the transaction. Consider using bound connections

I have analyzed couple of articles mentioned below and came to know about approach.

• http://www.sql-server-performance.com/2006/deadlocks/
• http://mssqlserverindia.blogspot.in/2007/05/how-to-avoid-deadlocking-on-your-sql.html
• http://msdn.microsoft.com/en-us/library/ms191242%28SQL.105%29.aspx
• http://support.microsoft.com/kb/169960

 

SQL SERVER – 2008 – Performance Optimization – Part1

The first step towards the performance optimization is to rebuild all the indexes which are created on the database.

1. How to know which index was build, how many days before?

SELECT * FROM (
    SELECT NAME
    AS INDEX_NAME,TYPE, SYS.INDEXES.TYPE_DESC,SYS.INDEXES.OBJECT_ID,
    STATS_DATE(OBJECT_ID, INDEX_ID) AS STATSUPDATED,
    DATEDIFF(DAY, STATS_DATE(OBJECT_ID, INDEX_ID) ,GETDATE()) AS DAYSOLD
    FROM SYS.INDEXES
    WHERE OBJECT_ID IN (SELECT SYS.TABLES.OBJECT_ID FROM SYS.TABLES
    WHERE SYS.TABLES.TYPE = ‘U’)
) AS T1
WHERE DAYSOLD IS NOT NULL –AND DAYSOLD <> 0
ORDER BY DAYSOLD DESC 

2. How to rebuild all the indexes?

   EXEC sp_updatestats

Enjoy database performance!!!

  

 

SQL SERVER – 2008 – Optimize for Ad hoc Workloads – Advance Performance Optimization

What is Optimize Ad Hoc Workloads?

This option is used to improve the efficiency of the plan cache for workloads that contain many single use ad hoc batches. When this option is set to 1, the Database Engine stores a small compiled plan stub in the plan cache when a batch is compiled for the first time, instead of the full compiled plan. This helps to relieve memory pressure by not allowing the plan cache to become filled with compiled plans that are not reused. Setting the optimize for ad hoc workloads to 1 affects only new plans; plans that are already in the plan cache are unaffected.

Following is a code that shows how we can do that in SQL server 2008:

sp_configure ‘show advanced options’,1
RECONFIGURE WITH OVERRIDE
GO

sp_configure  ‘optimize for ad hoc workloads’,1
RECONFIGURE WITH OVERRIDE
GO

For more details please visit this link http://sqlserverperformance.idera.com/memory/optimize-ad-hoc-workloads-option-sql-server-2008/

Enjoy performance improvement!!!