query-tuning

Should I change the 'locks' configuration in SQL Server?

Should I change the 'locks' configuration in SQL Server?

I recently got a fantastic question from a reader regarding lock usage in SQL Server:

One of my production databases has a total lock count around 25,000 (select count(*) from sys.dm_tran_locks). The configuration setting for locks is set to the default of zero. This lock count is due to multiple procedures which frequently run and use the same 2-3 tables, repeatedly taking out and releasing locks. Do I need to change the configuration for locks or look into the SP’s so they can finish more quickly, rather than creating locks?

Measuring Query Duration: SSMS vs SQL Sentry Plan Explorer

Measuring Query Duration: SSMS vs SQL Sentry Plan Explorer

Every query tuner wants to explain exactly how much faster we made a query.

But sometimes SQL Server Management Studio adds noticeable overhead to the query duration. For relatively fast queries that return more than a few rows, just the overhead of displaying the results can skew your duration metric.