lowlydba & lowlysre

Table of Contents

NOLOCK

Despite the implication of the name, NOLOCK doesn’t just prevent the taking of a shared lock. It ignores other exclusive locks and makes the transaction run equivalent to the READ UNCOMMITTED isolation level. This allows for the possibility of dirty reads, missing rows, duplicate rows, DML issues, and read errors that will kill the query. If you don’t care about your query succeeding and don’t care about the integrity or quality of the data you’re returning, then by all all means proceed.

Don’t trust me? Check out what these Microsoft MVPs and Reputable Stack Exchange users have to say on the topic:

How Many Indexes

For every insert, update, or delete statement there is a cost to maintain an index on a table. However, indexes are a core feature of all relational databases and just because there is a cost associated does not mean they should be avoided. Like many database design features, indexes need to be used responsibly in moderation in order to speed up a targeted query. How many indexes are appropriate depends on the workload of the system and specific configurations of each index. Usually, 5 or less indexes will not negatively impact DML performance on a table, but each scenario should be assessed independently. As with most things databse related, the ultimate answer is “it depends”.

Don’t believe me? Check out what these industry experts have to say on the topic:

Identity as Clustered Index

99% of the time, using an identity column as a clustered index in SQL Server is the right choice.

Query Performance Across Environments

See the following question on DBA.StackExchange:

Batch Processing

When making updates to VLT (very large tables), batching is always key for both performance and to not lock up the table for extended periods of time. There are also many other nuanced ways to batch, which can unlock a ton more performance potential.