To fix blocking & deadlocks, you have 3 tools:
- Have enough indexes to make your queries fast, but not so many that they slow down delete/update/insert operations. (I cover that in the Mastering Index Tuning class.)
- Use the right isolation level for your app’s needs. (I cover that in the Mastering Server Tuning class.)
- Tuning your T-SQL to work through tables in a consistent order and touch them as few times as possible.
I’ll cover #3 in a one-hour webcast with Quest Software on Thursday at noon Eastern. Register here.
Fearest deadlocks I’ve seen are with multi-database applications — the ones that access data/call sps on linked servers.
The deadlock graphs are unlike anything in the textbooks and more like Kraken. . .
BWAAAA-HAAAA! You forgot the most important aspect… write good, nasty fast, low resource usage code. 😀 Indexes won’t be used unless you know how to write code that will use them and trying to write code “in the same order” won’t necessarily help when high input rates occur.
Heh… all hail the Kraken! 😀
CREATE TYPE [dbo].[my_bigint] FROM [bigint] NOT NULL
DECLARE @a TABLE (a my_bigint);
–exec sp_executesql N’DECLARE @a TABLE (a my_bigint);’
–exec (‘DECLARE @a TABLE (a my_bigint);’)