Archive for the ‘Database’ Category

Chaching with Sql Server 2005 Query Notifications

February 24, 2010 Leave a comment
Here is the POC on implementing caching in ASP .net, with SQL Server 2005 Query Notifications.
Download the source code here
Challenges in troubleshooting:
Additional references
Categories: Database

Deadlock on Index

November 2, 2009 Leave a comment
In this entry I would like to share about the “Debugging Deadlock”. Below is the short notes on business requirement.
My database has a User’s table called “USER_DETAIL”. I am updating this table using a Stored procedure. This can be called form multiple application instances.

Table definition & Fill with Data:


Here is short descrition about the columns…
user_detail_id: Primary key, Identity Column in this table.
network_id: Say, it is like user name.
tl_id (Team lean Id): Self referance for User_detail_id. Like a Manager/Lead for a User. And also that, Manager/Lead is also a user in application.

I am skipping remaining columns from this section, as we can understand from its name.

This table 3 non-cluster indexes. Table definition, Insert statements are documented here.

Inserted some dummy data using a WHILE loop


— Fill Data
declare @index int
@index = 1


@index <=1000




INSERT INTO TempDemos.dbo.user_detail


VALUES (‘network_id_’ + CONVERT(varchar(10), @index)

, ‘first_name_’ + CONVERT(varchar(10), @index)

, ‘last_name_’ + CONVERT(varchar(10), @index)

, ’email_’ + CONVERT(varchar(10), @index) + ‘’


set @index=@index+1



Now, lets work for generating deadlock. Execute the same code from Instance of SSMS. For the demo purpose I am taking all input values into a #temp table.
And below are steps in the stored procedure.
1. Update User
2. Update TeamLead (If exists Update, else Insert)
3. Update User.TeamLead

Note: The order of execution may not be correct. But here I wanted to have deadlock. So I am with this order of steps.

Now take Process 1, Process 2 into 2 separate SSMS instances. And execute as shown below.
 11-2-2009 11-39-27 PM
Execute each section one by one. If you look at lock information immediately after executing section-6, you will have it as below…
11-3-2009 12-09-28 AM


  1. One process is searching the row using Heap search, another one is using Non-Clustered.
  2. Each one is holding one resource and waiting for each other.
  3. Causing a deadlock

Cause for Deadlock:


This table doesn’t have a cluster index. Change the Non-cluster ix_user_detail(on network_id column) to cluster index.


🙂 Basically I am not expert in DB development. I just tried to explore it. Found the solution by Trail&Error method.
I welcome, if any one interested in describe it technically. Let’s fill the ???<GAP>???

Donwload other presentation and sample code from here…


Bind multiple connections to share the same locking space. Execute the stored procedure sp_bindsession to keep more than one session in the same.


Pradeep Y.

Categories: Database

Find Tables With Primary Key Constraint

October 27, 2009 Leave a comment

    , COL_NAME(ic.OBJECT_ID,ic.column_id) AS ColumnName
FROM sys.indexes AS i
    INNER JOIN sys.index_columns AS ic ON i.OBJECT_ID = ic.OBJECT_ID AND i.index_id = ic.index_id
WHERE i.is_primary_key = 1

Categories: Database

Find Tables With Foreign Key Constraint

October 27, 2009 Leave a comment

.name AS ForeignKey
    , OBJECT_NAME(f.parent_object_id) AS TableName
    , COL_NAME(fc.parent_object_id, fc.parent_column_id) AS ColumnName
    , OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName
    , COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS ReferenceColumnName
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id
where OBJECT_NAME (f.referenced_object_id) =‘table_name’

Categories: Database