Skip to content

SteveStedman

Duplicate Indexes, What a Waste

Today on the tuning minute on the SQL Data Partners Podcast, we discussed duplicate indexes, which lead me to think more about and and write this post.

You know there are many different ways of doing things in SQL Server, and often times you can argue that one way or the other is better, and given the right situation anything might be a good idea. However duplicate indexes are a different story.

When I talk about duplicate indexes, what I mean is 2 or more indexes on the same table that are exactly the same columns.  Something like this:


CREATE NONCLUSTERED INDEX [IX_LastName] ON [dbo].[Customer]
(
 [Lastname] ASC
);

CREATE NONCLUSTERED INDEX [dta123123123_LastName] ON [dbo].[Customer]
(
 [Lastname] ASC
);




Two indexes on exactly the same column. There is nothing to be gained here.


Read More »Duplicate Indexes, What a Waste

The Use of WITH NOLOCK Query Hint

TWITH NOLOCKhere is a great deal of confusion about the WITH NOLOCK query hint. The following is a transcription of a conversation that I had on the SQL Data Partners Podcast #57 about the WITH NOLOCK hint, and some of the misconceptions about it.

WITH NOLOCK Hint

Carlos: So the next on is the WITH NOLOCK hint and I think this is on the list, ultimately, because there’s a lot of misinformation out there.

Steve: Absolutely, yes. The NOLOCK hint is one of my peeves on SQL Server, actually. I see it used a lot and really 99 percent of the time I see it used, people think that it’s doing something different than what it does. So, I heard the statement, “But I want to run a query in the production system but not impact or block anyone else. Shouldn’t I just use NOLOCK?” And the answer there is NO. The NOLOCK hint tells SQL Server to ignore other people’s query locks. Basically, to read dirty or uncommitted data at that point, which can lead to missing rows or phantom rows or data showing up in results. But it doesn’t do anything to stop the locking or the blocking in any way on the query that’s calling it. So it’s the equivalent of saying read uncommitted on a specific table that it’s referencing.

Read More »The Use of WITH NOLOCK Query Hint