Stimulating DEADLOCK in SQL Server

Here in this article we are trying to stimulate Deadlock situation, we are trying to create two table table1 and table2 for the same purpose

create table table1

(id int identity(1,1),

col2 int)

create table table2

(id int identity(1,1),

col2 int)

Command(s) completed successfully.

Now lets insert few records in both the table.

insert into table1

select 10 union all

select 20 union all

select 30

insert into table2

select 100 union all

select 200 union all

select 300

No w open a session by opening a new query window in SSMS, I will call it session1 and try to execute the below code, here we opened a transaction then updating table1 col2 where id =1

begin tran

       update table1

       set col2 = col2 + 10

       where id = 2

Now open another query window (second session) in SSMS and execute the below statement, here we are trying to update both the table the second table will wait for first transaction to unlock table1 for its update. And it goes on execution for infinite time.

begin tran

       update table2

       set col2 = col2 + 100

       where id = 2

       update table1

       set col2=col2 + 10

       where id = 2

commit tran

Now again come back to session1 window and execute the below code

update table2

set col2 = col2 + 100

where id = 2

now as you run this above statement on session1, SQL Server sense deadlock since the above code wait for table2 to get unlocked by session2 and same way session2 waits for session1 for table1 to unlock and DEADLOCK occurs, in this case SQL server chosen session1 as victim and abort session1 with a message

Msg 1205, Level 13, State 45, Line 7

Transaction (Process ID 55) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

And session 2 will complete automatically.

There are no nights for SQL Developers, if so please give me address  J

Post Reference: Vikram Aristocratic Elfin Share

Truncate can be Rollback in same session

When you are surrounding TRUNCATE in transaction block you can rollback Truncated table in the current session.

If you close the session where Truncate is surrounded by transaction block, you cannot rollback the table unlike DELETE.

Here we have a table with only one record

select * from temp2

id          c_address  zip

----------- ---------- -----------

1           delhi      326595

(1 row(s) affected)

Below we are opening a transaction block and truncating the table later we are doing rollback, to check the intermediate result we have put select statement in between.

Lets execute the transaction block and see the result of rollback on truncate.                                                                                                 

begin transaction   

       select * from temp2

       truncate table temp2

       select * from temp2

rollback tran

      

id          c_address  zip

----------- ---------- -----------

1           delhi      326595

(1 row(s) affected)

id          c_address  zip

----------- ---------- -----------

(0 row(s) affected)

So here we saw before truncate we had one record and after truncate we had no records.

Now the next statement which we fired is rollback, here we need to check whether truncate statement gets rollback or not.

Lets fire the select query to see what affect it made on truncate after rollback

select * from temp2

id          c_address  zip

----------- ---------- -----------

1           delhi      326595

Ah! Truncate has no affect when it is followed by rollback in transaction block like Delete Command.

Conclusion: Truncated record can be rollback in the same session.

Few minute spent with you my SQL Server, rejuvenate for hours  :)

Post Reference: Vikram Aristocratic Elfin Share

Function in where clause degrade performance

When function are used in where clause it majorly affect the performance of query if query is intended to result large number of rows.

This is because function will force to evaluate for each row of table data involved to make the comparison which will force optimizer to use Index scan or Table Scan instead of Index Seek if proper index involve.

Lets see with an example how function in where clause affect performance. For demo purpose I am making use of AdventureWorks2008R2 database table Person.Person

First I am creating an Nonclustered index on FirstName column of Person table.

create index ix_person_firstname

on Person.Person(FirstName) include(LastName,MiddleName)

Command(s) completed successfully.

Now lets fire two  query, one which is using substring function in where clause and another which is simply written but both are producing the same output. And check the relative batch cost of each query. Set the execution plan on

select FirstName,LastName,MiddleName from Person.Person

where FirstName like 'AB%'

select FirstName,LastName,MiddleName from Person.Person

where substring(FirstName,0,3) = 'AB'

 Finding from execution Plan:

1        The first query use Index Seek to find the record where as the second query with Substring function in where clause uses Index scan which affect the performance as compare to index seek. Because Function in where clause force optimizer to evaluate the value of where predicate for each record in table to bring the matching criteria of records.

2.       

           If  we see the relative batch cost of both the query, then we are finding query without function in where predicate took just 3% of total batch cost and query with function in where clause took 97% of total cost of batch.

Conclusion: Query without function in where predicate perform very well as compared to query with function in where clause. Try to avoid function in where predicate.    

If there is only one thing which makes coder always chill whatever would be the situation, probably it would be code, hmm I love :)

 

Post Reference: Vikram Aristocratic Elfin Share

Work Around for Filtered Index Parameter Sniffing Problem

Problem Statement: The problem with Filtered index is, when a local variable is passed as a parameter to the query, Query Optimizer does not select the correct Index (Filtered index) even though the WHERE  condition (the selectivity) falls in the range of Filtered index.

Again I am using AdventureWork2008R2 database for explanation. We will make use of Person.Person table.

We are interested to fire this query

select PersonType  from Person.Person where PersonType = 'SP'

Here I am creating 2 indexes on the PersonType column, one Non Custered Index and another Filtered Index

create index ix_Person_PersonType

on Person.Person(PersonType)

Command(s) completed successfully.

create index fix_Person_PersonType

on Person.Person(PersonType) where PersonType = 'SP'

Command(s) completed successfully.

Now our indexes are in place, lets fire query to see the execution plan.

We are firing the query against Person table for PersonType like ‘SP’, and expecting Optimizer to pick up newly created Filtered Index.

select PersonType  from Person.Person where PersonType = 'SP'

Thats fantastic it picked up the right index what we were expecting. Here in the above query we have explicitly passed the value in the where predicate that is PersonType = ‘SP’

Let see what Optimizer will do if we pass the value through local variable.

declare @para varchar(2) = 'SP'

select PersonType  from Person.Person where PersonType = @para

Optimizer did not use the Filtered index even though the where predicate search the same records.

The reason behind is Optimizer don’t have idea what value the local variable will hold at compile time, so it trade off Filtered Index and choose the Non clustered index.

Workaround Solution: Option(RECOMPILE) we can use the statement level recompile statement to tell the engine to recompile the plan at execution time.

declare @para varchar(2) = 'SP'

select PersonType  from Person.Person where PersonType = @para

Here in this query we can see the @para value is not available at compile time only at run time it is exposed. So the alternative solution would be recompiling the  plan at run time with @para value is available.

declare @para varchar(2) = 'SP'

select PersonType  from Person.Person  where PersonType = @para option(recompile)

So the execution pan clearly tells that when you use execution level RECOMPILE option the correct index will be picked up. Still try to avoid statement level recompilation and make use of stored cache plan. This is just an work around solution.

If your code has bliss then do you think you need to go out in search of materialized happiness?? J

Post Reference: Vikram Aristocratic Elfin Share