Mixing
Performance differences between RID Lookup vs Key Lookup?
Clash Royale CLAN TAG#URR8PPP
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty margin-bottom:0;
up vote
2
down vote
favorite
Are there any performance differences between when a non-clustered index uses the clustered index's key to locate the row vs when that table doesn't have a clustered index and the non-clustered index locates the row via the RID?
Does different levels of fragmentation impact this performance comparison as well? (E.g. in both scenarios the tables are 0% fragmented, vs 50%, vs 100%.)
sql-server performance-tuning clustered-index nonclustered-index bookmark-lookup
asked 2 days ago
J.D.
39029
add a comment |Â
up vote
2
down vote
favorite
Are there any performance differences between when a non-clustered index uses the clustered index's key to locate the row vs when that table doesn't have a clustered index and the non-clustered index locates the row via the RID?
Does different levels of fragmentation impact this performance comparison as well? (E.g. in both scenarios the tables are 0% fragmented, vs 50%, vs 100%.)
sql-server performance-tuning clustered-index nonclustered-index bookmark-lookup
asked 2 days ago
J.D.
39029
1
Basically, you don't want a heap in almost all scenarios where the table is not being used as some intermediate staging table.
– Aaron Bertrand♦
2 days ago
1
I wouldn't think so either Aaron. Unfortunately I'm battling a vendor who's application we use that has a database with 0 clustered indexes and frequently transacted (non-staging) tables, some with millions of records. I'm not crazy for being concerned that this design is one of the main reasons for poor performance from the application, am I?
– J.D.
2 days ago
add a comment |Â
up vote
2
down vote
favorite
up vote
2
down vote
favorite
Are there any performance differences between when a non-clustered index uses the clustered index's key to locate the row vs when that table doesn't have a clustered index and the non-clustered index locates the row via the RID?
Does different levels of fragmentation impact this performance comparison as well? (E.g. in both scenarios the tables are 0% fragmented, vs 50%, vs 100%.)
sql-server performance-tuning clustered-index nonclustered-index bookmark-lookup
asked 2 days ago
J.D.
39029
Are there any performance differences between when a non-clustered index uses the clustered index's key to locate the row vs when that table doesn't have a clustered index and the non-clustered index locates the row via the RID?
Does different levels of fragmentation impact this performance comparison as well? (E.g. in both scenarios the tables are 0% fragmented, vs 50%, vs 100%.)
sql-server performance-tuning clustered-index nonclustered-index bookmark-lookup
sql-server performance-tuning clustered-index nonclustered-index bookmark-lookup
asked 2 days ago
J.D.
39029
asked 2 days ago
J.D.
39029
asked 2 days ago
J.D.
39029
asked 2 days ago
J.D.
39029
asked 2 days ago
J.D.
39029
39029
1
Basically, you don't want a heap in almost all scenarios where the table is not being used as some intermediate staging table.
– Aaron Bertrand♦
2 days ago
1
I wouldn't think so either Aaron. Unfortunately I'm battling a vendor who's application we use that has a database with 0 clustered indexes and frequently transacted (non-staging) tables, some with millions of records. I'm not crazy for being concerned that this design is one of the main reasons for poor performance from the application, am I?
– J.D.
2 days ago
add a comment |Â
1
Basically, you don't want a heap in almost all scenarios where the table is not being used as some intermediate staging table.
– Aaron Bertrand♦
2 days ago
1
I wouldn't think so either Aaron. Unfortunately I'm battling a vendor who's application we use that has a database with 0 clustered indexes and frequently transacted (non-staging) tables, some with millions of records. I'm not crazy for being concerned that this design is one of the main reasons for poor performance from the application, am I?
– J.D.
2 days ago
1
1
Basically, you don't want a heap in almost all scenarios where the table is not being used as some intermediate staging table.
– Aaron Bertrand♦
2 days ago
Basically, you don't want a heap in almost all scenarios where the table is not being used as some intermediate staging table.
– Aaron Bertrand♦
2 days ago
1
1
I wouldn't think so either Aaron. Unfortunately I'm battling a vendor who's application we use that has a database with 0 clustered indexes and frequently transacted (non-staging) tables, some with millions of records. I'm not crazy for being concerned that this design is one of the main reasons for poor performance from the application, am I?
– J.D.
2 days ago
I wouldn't think so either Aaron. Unfortunately I'm battling a vendor who's application we use that has a database with 0 clustered indexes and frequently transacted (non-staging) tables, some with millions of records. I'm not crazy for being concerned that this design is one of the main reasons for poor performance from the application, am I?
– J.D.
2 days ago
add a comment |Â
2 Answers
2
active
oldest
votes
up vote
7
down vote
Leaving aside the fragmentation bogeyeman (it doesn't really matter when doing singleton lookups), the main difference is that an RID specifies the exact page a row is on, while with a Key Lookup you traverse the non-leaf levels of the clustered index to find the target page. Aaron Bertrand did some tests on this in Is a RID Lookup faster than a Key Lookup?
However, Heaps can have forwarded fetches (or records) in them, in which case multiple logical IOs are required to find the target row.
I blogged about this recently, and I'm reproducing the content here to avoid a comment answer.
CREATE TABLE el_heapo
(
id INT IDENTITY,
date_fudge DATE,
stuffing VARCHAR(3000)
);
INSERT dbo.el_heapo WITH (TABLOCKX)
( date_fudge, stuffing )
SELECT DATEADD(HOUR, x.n, GETDATE()), REPLICATE('a', 1000)
FROM (
SELECT TOP (1000 * 1000)
ROW_NUMBER() OVER (ORDER BY @@SPID)
FROM sys.messages AS m
CROSS JOIN sys.messages AS m2
) AS x (n)
CREATE NONCLUSTERED INDEX ix_heapo ON dbo.el_heapo (date_fudge);
We can look at the table with sp_BlitzIndex
EXEC master.dbo.sp_BlitzIndex @DatabaseName = N'Crap',
@SchemaName = 'dbo',
@TableName = 'el_heapo';
This query will produce bookmark lookups.
SELECT *
FROM dbo.el_heapo AS eh
WHERE eh.date_fudge BETWEEN '2018-09-01' AND '2019-09-01'
AND 1 = (SELECT 1)
OPTION(MAXDOP 1);
Now we can cause some forwarded records to occur:
UPDATE eh
SET eh.stuffing = REPLICATE('z', 3000)
FROM dbo.el_heapo AS eh
WHERE eh.date_fudge BETWEEN '2018-09-01' AND '2019-09-01'
OPTION(MAXDOP 1)
BlitzIndex will show them to us:
And if we re-run the lookup query:
Profiler will show a difference as well:
edited yesterday
Paul White♦
46.5k14251398
answered 2 days ago
sp_BlitzErik
19.5k1161101
add a comment |Â
up vote
1
down vote
Remember that the traversing of the non-leaf levels of the clustered index (which is basically what differs a Key to a rid lookup) will almost all be in memory. SQL Server reads through the root, next level, etc again, again, again and again etc. In other words, they will be super-hot.
Compare that to forwarded records in a heap. You think you found one row, but dar, it is somewhere else. You, so to speak, jump all over the place. So one should be careful to just look at "reads" here. Logical vs physical reads becomes a factor.
And, of course, a very important aspect is if you have heap forwarded records in the first place! Sys.dm_db_index_physical_stats with DETAILED option will tell you.
answered yesterday
Tibor Karaszi
8615
add a comment |Â
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
7
down vote
Leaving aside the fragmentation bogeyeman (it doesn't really matter when doing singleton lookups), the main difference is that an RID specifies the exact page a row is on, while with a Key Lookup you traverse the non-leaf levels of the clustered index to find the target page. Aaron Bertrand did some tests on this in Is a RID Lookup faster than a Key Lookup?
However, Heaps can have forwarded fetches (or records) in them, in which case multiple logical IOs are required to find the target row.
I blogged about this recently, and I'm reproducing the content here to avoid a comment answer.
CREATE TABLE el_heapo
(
id INT IDENTITY,
date_fudge DATE,
stuffing VARCHAR(3000)
);
INSERT dbo.el_heapo WITH (TABLOCKX)
( date_fudge, stuffing )
SELECT DATEADD(HOUR, x.n, GETDATE()), REPLICATE('a', 1000)
FROM (
SELECT TOP (1000 * 1000)
ROW_NUMBER() OVER (ORDER BY @@SPID)
FROM sys.messages AS m
CROSS JOIN sys.messages AS m2
) AS x (n)
CREATE NONCLUSTERED INDEX ix_heapo ON dbo.el_heapo (date_fudge);
We can look at the table with sp_BlitzIndex
EXEC master.dbo.sp_BlitzIndex @DatabaseName = N'Crap',
@SchemaName = 'dbo',
@TableName = 'el_heapo';
This query will produce bookmark lookups.
SELECT *
FROM dbo.el_heapo AS eh
WHERE eh.date_fudge BETWEEN '2018-09-01' AND '2019-09-01'
AND 1 = (SELECT 1)
OPTION(MAXDOP 1);
Now we can cause some forwarded records to occur:
UPDATE eh
SET eh.stuffing = REPLICATE('z', 3000)
FROM dbo.el_heapo AS eh
WHERE eh.date_fudge BETWEEN '2018-09-01' AND '2019-09-01'
OPTION(MAXDOP 1)
BlitzIndex will show them to us:
And if we re-run the lookup query:
Profiler will show a difference as well:
edited yesterday
Paul White♦
46.5k14251398
answered 2 days ago
sp_BlitzErik
19.5k1161101
add a comment |Â
up vote
7
down vote
Leaving aside the fragmentation bogeyeman (it doesn't really matter when doing singleton lookups), the main difference is that an RID specifies the exact page a row is on, while with a Key Lookup you traverse the non-leaf levels of the clustered index to find the target page. Aaron Bertrand did some tests on this in Is a RID Lookup faster than a Key Lookup?
However, Heaps can have forwarded fetches (or records) in them, in which case multiple logical IOs are required to find the target row.
I blogged about this recently, and I'm reproducing the content here to avoid a comment answer.
CREATE TABLE el_heapo
(
id INT IDENTITY,
date_fudge DATE,
stuffing VARCHAR(3000)
);
INSERT dbo.el_heapo WITH (TABLOCKX)
( date_fudge, stuffing )
SELECT DATEADD(HOUR, x.n, GETDATE()), REPLICATE('a', 1000)
FROM (
SELECT TOP (1000 * 1000)
ROW_NUMBER() OVER (ORDER BY @@SPID)
FROM sys.messages AS m
CROSS JOIN sys.messages AS m2
) AS x (n)
CREATE NONCLUSTERED INDEX ix_heapo ON dbo.el_heapo (date_fudge);
We can look at the table with sp_BlitzIndex
EXEC master.dbo.sp_BlitzIndex @DatabaseName = N'Crap',
@SchemaName = 'dbo',
@TableName = 'el_heapo';
This query will produce bookmark lookups.
SELECT *
FROM dbo.el_heapo AS eh
WHERE eh.date_fudge BETWEEN '2018-09-01' AND '2019-09-01'
AND 1 = (SELECT 1)
OPTION(MAXDOP 1);
Now we can cause some forwarded records to occur:
UPDATE eh
SET eh.stuffing = REPLICATE('z', 3000)
FROM dbo.el_heapo AS eh
WHERE eh.date_fudge BETWEEN '2018-09-01' AND '2019-09-01'
OPTION(MAXDOP 1)
BlitzIndex will show them to us:
And if we re-run the lookup query:
Profiler will show a difference as well:
edited yesterday
Paul White♦
46.5k14251398
answered 2 days ago
sp_BlitzErik
19.5k1161101
add a comment |Â
up vote
7
down vote
up vote
7
down vote
Leaving aside the fragmentation bogeyeman (it doesn't really matter when doing singleton lookups), the main difference is that an RID specifies the exact page a row is on, while with a Key Lookup you traverse the non-leaf levels of the clustered index to find the target page. Aaron Bertrand did some tests on this in Is a RID Lookup faster than a Key Lookup?
However, Heaps can have forwarded fetches (or records) in them, in which case multiple logical IOs are required to find the target row.
I blogged about this recently, and I'm reproducing the content here to avoid a comment answer.
CREATE TABLE el_heapo
(
id INT IDENTITY,
date_fudge DATE,
stuffing VARCHAR(3000)
);
INSERT dbo.el_heapo WITH (TABLOCKX)
( date_fudge, stuffing )
SELECT DATEADD(HOUR, x.n, GETDATE()), REPLICATE('a', 1000)
FROM (
SELECT TOP (1000 * 1000)
ROW_NUMBER() OVER (ORDER BY @@SPID)
FROM sys.messages AS m
CROSS JOIN sys.messages AS m2
) AS x (n)
CREATE NONCLUSTERED INDEX ix_heapo ON dbo.el_heapo (date_fudge);
We can look at the table with sp_BlitzIndex
EXEC master.dbo.sp_BlitzIndex @DatabaseName = N'Crap',
@SchemaName = 'dbo',
@TableName = 'el_heapo';
This query will produce bookmark lookups.
SELECT *
FROM dbo.el_heapo AS eh
WHERE eh.date_fudge BETWEEN '2018-09-01' AND '2019-09-01'
AND 1 = (SELECT 1)
OPTION(MAXDOP 1);
Now we can cause some forwarded records to occur:
UPDATE eh
SET eh.stuffing = REPLICATE('z', 3000)
FROM dbo.el_heapo AS eh
WHERE eh.date_fudge BETWEEN '2018-09-01' AND '2019-09-01'
OPTION(MAXDOP 1)
BlitzIndex will show them to us:
And if we re-run the lookup query:
Profiler will show a difference as well:
edited yesterday
Paul White♦
46.5k14251398
answered 2 days ago
sp_BlitzErik
19.5k1161101
Leaving aside the fragmentation bogeyeman (it doesn't really matter when doing singleton lookups), the main difference is that an RID specifies the exact page a row is on, while with a Key Lookup you traverse the non-leaf levels of the clustered index to find the target page. Aaron Bertrand did some tests on this in Is a RID Lookup faster than a Key Lookup?
However, Heaps can have forwarded fetches (or records) in them, in which case multiple logical IOs are required to find the target row.
I blogged about this recently, and I'm reproducing the content here to avoid a comment answer.
CREATE TABLE el_heapo
(
id INT IDENTITY,
date_fudge DATE,
stuffing VARCHAR(3000)
);
INSERT dbo.el_heapo WITH (TABLOCKX)
( date_fudge, stuffing )
SELECT DATEADD(HOUR, x.n, GETDATE()), REPLICATE('a', 1000)
FROM (
SELECT TOP (1000 * 1000)
ROW_NUMBER() OVER (ORDER BY @@SPID)
FROM sys.messages AS m
CROSS JOIN sys.messages AS m2
) AS x (n)
CREATE NONCLUSTERED INDEX ix_heapo ON dbo.el_heapo (date_fudge);
We can look at the table with sp_BlitzIndex
EXEC master.dbo.sp_BlitzIndex @DatabaseName = N'Crap',
@SchemaName = 'dbo',
@TableName = 'el_heapo';
This query will produce bookmark lookups.
SELECT *
FROM dbo.el_heapo AS eh
WHERE eh.date_fudge BETWEEN '2018-09-01' AND '2019-09-01'
AND 1 = (SELECT 1)
OPTION(MAXDOP 1);
Now we can cause some forwarded records to occur:
UPDATE eh
SET eh.stuffing = REPLICATE('z', 3000)
FROM dbo.el_heapo AS eh
WHERE eh.date_fudge BETWEEN '2018-09-01' AND '2019-09-01'
OPTION(MAXDOP 1)
BlitzIndex will show them to us:
And if we re-run the lookup query:
Profiler will show a difference as well:
edited yesterday
Paul White♦
46.5k14251398
answered 2 days ago
sp_BlitzErik
19.5k1161101
edited yesterday
Paul White♦
46.5k14251398
edited yesterday
Paul White♦
46.5k14251398
edited yesterday
Paul White♦
46.5k14251398
46.5k14251398
answered 2 days ago
sp_BlitzErik
19.5k1161101
answered 2 days ago
sp_BlitzErik
19.5k1161101
answered 2 days ago
sp_BlitzErik
19.5k1161101
19.5k1161101
add a comment |Â
add a comment |Â
up vote
1
down vote
Remember that the traversing of the non-leaf levels of the clustered index (which is basically what differs a Key to a rid lookup) will almost all be in memory. SQL Server reads through the root, next level, etc again, again, again and again etc. In other words, they will be super-hot.
Compare that to forwarded records in a heap. You think you found one row, but dar, it is somewhere else. You, so to speak, jump all over the place. So one should be careful to just look at "reads" here. Logical vs physical reads becomes a factor.
And, of course, a very important aspect is if you have heap forwarded records in the first place! Sys.dm_db_index_physical_stats with DETAILED option will tell you.
answered yesterday
Tibor Karaszi
8615
add a comment |Â
up vote
1
down vote
Remember that the traversing of the non-leaf levels of the clustered index (which is basically what differs a Key to a rid lookup) will almost all be in memory. SQL Server reads through the root, next level, etc again, again, again and again etc. In other words, they will be super-hot.
Compare that to forwarded records in a heap. You think you found one row, but dar, it is somewhere else. You, so to speak, jump all over the place. So one should be careful to just look at "reads" here. Logical vs physical reads becomes a factor.
And, of course, a very important aspect is if you have heap forwarded records in the first place! Sys.dm_db_index_physical_stats with DETAILED option will tell you.
answered yesterday
Tibor Karaszi
8615
add a comment |Â
up vote
1
down vote
up vote
1
down vote
Remember that the traversing of the non-leaf levels of the clustered index (which is basically what differs a Key to a rid lookup) will almost all be in memory. SQL Server reads through the root, next level, etc again, again, again and again etc. In other words, they will be super-hot.
Compare that to forwarded records in a heap. You think you found one row, but dar, it is somewhere else. You, so to speak, jump all over the place. So one should be careful to just look at "reads" here. Logical vs physical reads becomes a factor.
And, of course, a very important aspect is if you have heap forwarded records in the first place! Sys.dm_db_index_physical_stats with DETAILED option will tell you.
answered yesterday
Tibor Karaszi
8615
Remember that the traversing of the non-leaf levels of the clustered index (which is basically what differs a Key to a rid lookup) will almost all be in memory. SQL Server reads through the root, next level, etc again, again, again and again etc. In other words, they will be super-hot.
Compare that to forwarded records in a heap. You think you found one row, but dar, it is somewhere else. You, so to speak, jump all over the place. So one should be careful to just look at "reads" here. Logical vs physical reads becomes a factor.
And, of course, a very important aspect is if you have heap forwarded records in the first place! Sys.dm_db_index_physical_stats with DETAILED option will tell you.
answered yesterday
Tibor Karaszi
8615
answered yesterday
Tibor Karaszi
8615
answered yesterday
Tibor Karaszi
8615
answered yesterday
Tibor Karaszi
8615
8615
add a comment |Â
add a comment |Â
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f217199%2fperformance-differences-between-rid-lookup-vs-key-lookup%23new-answer', 'question_page');
);
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
1
Basically, you don't want a heap in almost all scenarios where the table is not being used as some intermediate staging table.
– Aaron Bertrand♦
2 days ago
1
I wouldn't think so either Aaron. Unfortunately I'm battling a vendor who's application we use that has a database with 0 clustered indexes and frequently transacted (non-staging) tables, some with millions of records. I'm not crazy for being concerned that this design is one of the main reasons for poor performance from the application, am I?
– J.D.
2 days ago