October 22, 2025 at 12:00 am
Comments posted to this topic are about the item Why Indexes are Important Beyond Faster Execution of Queries
October 22, 2025 at 7:15 am
Thank you for the good point you make. Indeed indexes do help with isolating transactions and avoiding contention on a common resource.
I do want to note some areas where I think that things could be better explained. Right away it was stated that indexes can help. Fair enough , but how they help is an important piece of knowledge for that helps with understanding (not just that we will show they do help through empirical means). The very definition of an index was shallow. It was not stated that it is a copy/subset of the table and its own separate data structure as such ordered by a different column(s) than the base table & joined by the pk or row identifier (when the base table is heap).
Speaking of the base table, the term clustered index was thrown out as if it had already been defined prior in the article. It should've be noted the clustered index is the ordering (there is that important word again) of the base table. This implies the base table would be not in heap form.
I think answering the following would help further the information provided by the article:
How are the shared lock and update lock different to each other?
A row lock seems self explanatory. A index key lock not so, and I would explain the difference between these two.
It was mentioned index key locks work on the "corresponding" data rows. What is meant by corresponding ( the base table, clustered index, non clustered index) ?
It was mentioned indexes help reduce scope. Again it would help to give the definition of an index to see that this is done due introducing to the machine the concept of ordering, which is the heart and soul of an index.
-------
It would be good as well to make the pictures zoomable. It appears like there is good information there but was very hard to see.
One thing that is worth mentioning, though indexes do define how the pages of a non heap table are ordered, they wont necessarily prevent blocking. It depends on the query and the statistical distribution of values on a column in the where clause. Even when only page locks are applied (which I believe are the default behavior), two transactions on two rows on the same page can cause a albeit brief block. Row locks are expensive so even queries hinting this may not be a guarantee that the optimiser will follow this advise.
Another item, big elephant in the room that I thought I was going to see was that of covering indexes. This is a index that needs no data from the base table and answers the question/query all on its own. By not going to the base table the covering index is a major player in avoiding blocks on a database (at the cost of more space and longer maintenance operations, but probably well worth it).
Thank you again for your good work on this
----------------------------------------------------
October 22, 2025 at 11:11 pm
A very nice article making excellent points and with good examples.
One minor comment. Rather than a heap being defined as "A table without any indexes is a heap, meaning data is stored in no particular order, and SQL Server uses Row IDs (RIDs) to locate rows.", I think you meant to say that "A table without a clustered index is a heap..."
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply