Deadlocks in SQL Server 2008

There are few different kinds of lock available in SQL Server but the most resource consuming especially CPU power is DeadLock. It is better to know about deadlock and try to reduce as much as possible. First of all let us understand what is deadlock and how deadlock happens?
Deadlock happens when two or more processes waiting for the resource and wait for other process to finish and eventually neither of them ever does it at that time SQL Server select one process and abort other process.
Let us see one small example to create dead lock.
–create one two tables for demo and insert dummy records.
create table DL1
(
id int,
name varchar(10)
)
go
create table DL2
(
id int,
name varchar(10)
)
go
insert into DL1
select 1,‘ritesh’ union all
select 2,‘rajan’
go
insert into DL2
select 1,‘vipul’ union all
select 2,‘darshan’
go
–open other query window, will consider this window as A1
begin tran
update dl1 set name=‘rit’ where id=1
go
–open other query window, will consider this window as A2
begin tran
update dl2 set name=‘vi’ where id=2
go
–again come back to window A1
update dl2 set name=‘d’ where id=2
go
Now this statement will create lock and query got hold but it is not a dead lock so far.
–now comeback to A2 window and execute following command
update dl1 set name=‘raj’ where id=1
go
Now, this statement creates dead lock and you will be greeted with the error message something like below and your
Msg 1205, Level 13, State 45, Line 2
Transaction (Process ID 56) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
As soon as you execute COMMIT or ROLLBACK, your lock gets release. So, this is how deadlock generates so while designing the database, keep relatively normalize. Keep your transaction as short as possible and try to use TRY….CATCH block so if there is any error in your TRY block, it transfer the focuses to CATCH block and you can ROLLBACK transaction from there.
BTW, if you want to find tables which are being locked, have a look at my article here.
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com
Advertisements

Find Schema Name, row size in bytes and Total Number of rows in table for all tables in SQL Server 2008

System catalog and views provided inbuilt with SQL Server, are really wonderful, very useful and handy to get some inside information immediately. Today I want to show you use of following different system catalog and views.
SysObjects: used to show you list of all objects in database
SysColumns: used to show you list of all columns of all tables
Information_Schema.Columns:  also going to show you the list of columns for all tables, some information are easy to access other than SysColumns but note that SysColumns is powerful than this one
Sys.Partitions: Generally tables and indexes are in at least one partition in SQL Server 2008 so I use it as a handy tool to get total number of rows in each table.

Now, here I present one very small code snippet which will show you Schema Name, Table Name, Total Maximuz size of row in table and total number of rows exits in table. I have used above introduced system catalog to get these information, You may have different combination to get this kind of information.
select
      isc.TABLE_SCHEMA as SchemaName,
      so.name as TableName,
      SUM(sc.length) AS RowSizeInBytes,
      sp.rows as TotRowsInTable
from
      sysobjects so join
      syscolumns sc on so.name = OBJECT_NAME(sc.id) join
      INFORMATION_SCHEMA.COLUMNS isc on sc.name=isc.COLUMN_NAME and so.name=isc.TABLE_NAME join
      sys.partitions sp on OBJECT_NAME(sp.object_id)=so.name
where
      so.type = ‘U’
GROUP BY
      so.name, isc.TABLE_SCHEMA,sp.rows;
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com