performance

How to Tune Indexes for a Stored Procedure

How to Tune Indexes for a Stored Procedure

You’ve got an important stored procedure that you think needs index help– but it runs in environment with lots of other queries. How do you focus in and discover exactly what indexes need tuning for that procedure?

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?

The #1 Thing to Never Do to Fix a Performance Problem

The #1 Thing to Never Do to Fix a Performance Problem

Never, ever, disable backups to fix a performance problem.

If you’re not having a performance crisis, you might look at that sentence and say, “Wow, that’s super dumb! Who would ever do that?”

Well, a lot of people, actually. It’s pretty common.