This is really just a stub post, and should force me to come back and add more detail to the topic.

In a nutshell, be careful if you have parallelism turned ON for your multi-core SQL server as SQL’s internal worker threads can seemingly jam themselves into a deadlock scenario when executing a parallelised (sp?) query.

to disable parallelism, use the OPTION (MAXDOP 1) query hint.

SELECT col1, col2, col3
FROM table1 t1 INNER JOIN table 2 t2 on t1.id = t2.id
WHERE SomeCondition = SomeValue
OPTION (MAXDOP 1)

This will force SQL Server to run the query with a max parallelism of 1 (ie: single thread). Good-bye locks!

Some references:
Query Hint (T-SQL)
Appropriate Uses of Parallelism in SQL Server
Detecting and Ending Deadlocks
Deadlock Troubleshooting (Part 1)

Wow lots has happened in the last 4 hours. Where do i start?

  • In order to diagnose a locking/blocking issue on SQL server, start with an sp_who2 to give you the list of active PIDS, and the PIDS blocking requests. Then execute DBCC INPUTBUFFER(<BLOCKING PID>) to find the offending SQL
  • In order to free space on a critical HDD, you can move the data over to a new location on the same machine and create a junction (a symoblic link for the *nix folk). New feature of NTFS 3.0 and IMO not used anywhere near as heavily as it should. Most underrated feature in Windows.
  • It’s hard to come across a good music compilation, but by God someone has managed to do it