Referential Integrity Performance

How does Sql Server do referential integrity checks? My reason for asking
this is a conceptual theory that a referential integrity check against a
narrow row would be faster than on a wider row because there would be more
rows on a page, hence any mapping structures that SQL Server would use
internally to check RI would also be much smaller reducing seek and traverse
time?
--
Timothy A. Vanover MCSD, MCDBA, MCAD, MCSD for .Net=?Utf-8?B?VFZhbm92ZXI=?= <TVanover@.discussions.microsoft.com> wrote in
news:C8207684-981B-48D4-922B-E5A79B8DFF60@.microsoft.com:
> How does Sql Server do referential integrity checks? My reason for
> asking this is a conceptual theory that a referential integrity check
> against a narrow row would be faster than on a wider row because there
> would be more rows on a page, hence any mapping structures that SQL
> Server would use internally to check RI would also be much smaller
> reducing seek and traverse time?
I'm not sure, but I feel pretty comfortable that this is done the same way
as a query, that is checking all the columns at the same time. Reson for
this is mainly performance, since you can do with one scan of the data
instead of several, which of course could hurt performance even more.
Another reason for my thought is the fact that indexes can be used to
improve the performance of the integrity checks.
--
Ole Kristian Bangås
MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging, MCTS, MCITP|||The RI check would be against the key itself, which is supported by a unique
index. Key lookups are generally quick. It doesn't really matter about the
width of the table's rows. The key's width, coupled with disk I/O speed,
amount of memory and CPU speed are the overall determining factor.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"TVanover" <TVanover@.discussions.microsoft.com> wrote in message
news:C8207684-981B-48D4-922B-E5A79B8DFF60@.microsoft.com...
How does Sql Server do referential integrity checks? My reason for asking
this is a conceptual theory that a referential integrity check against a
narrow row would be faster than on a wider row because there would be more
rows on a page, hence any mapping structures that SQL Server would use
internally to check RI would also be much smaller reducing seek and traverse
time?
--
Timothy A. Vanover MCSD, MCDBA, MCAD, MCSD for .Net|||Well if the RI is going against a clustered index then it would- could matter
as this is associated with a full row of data at the leaf level. Remember
that rows are stored on pages and pages are stored on extents. How many times
does a narrow row have to be traversed vs a wide row given that I can only
get 8060 bytes of row data per page?
Secondly if there is a black box structure that holds a mapping of the key
location then there will ceratinly be fewer leaf levels in that structure as
there are more rows per page on a narrow row?
--
Timothy A. Vanover MCSD, MCDBA, MCAD, MCSD for .Net
"Tom Moreau" wrote:
> The RI check would be against the key itself, which is supported by a unique
> index. Key lookups are generally quick. It doesn't really matter about the
> width of the table's rows. The key's width, coupled with disk I/O speed,
> amount of memory and CPU speed are the overall determining factor.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> ..
> "TVanover" <TVanover@.discussions.microsoft.com> wrote in message
> news:C8207684-981B-48D4-922B-E5A79B8DFF60@.microsoft.com...
> How does Sql Server do referential integrity checks? My reason for asking
> this is a conceptual theory that a referential integrity check against a
> narrow row would be faster than on a wider row because there would be more
> rows on a page, hence any mapping structures that SQL Server would use
> internally to check RI would also be much smaller reducing seek and traverse
> time?
> --
> Timothy A. Vanover MCSD, MCDBA, MCAD, MCSD for .Net
>|||But you are not traversing all of the leaf node 'data' pages, you are only
traversing the upper intermediate (or 'index') pages to get to the
datapages. And yes, a wide data row will result in fewer rows of data on a
page and consequently more intermediate pages -that will be compensated for
in the index.
From BOL on 'Clustered Indexes'...
"For a clustered index, sysindexes.root points to the top of the clustered
index. SQL Server navigates down the index to find the row corresponding to
a clustered index key. "
--
Arnie Rowland
"To be successful, your heart must accompany your knowledge."
"TVanover" <TVanover@.discussions.microsoft.com> wrote in message
news:A768C36E-7AFA-4F47-92E6-A453BB4DC1FA@.microsoft.com...
> Well if the RI is going against a clustered index then it would- could
> matter
> as this is associated with a full row of data at the leaf level. Remember
> that rows are stored on pages and pages are stored on extents. How many
> times
> does a narrow row have to be traversed vs a wide row given that I can only
> get 8060 bytes of row data per page?
> Secondly if there is a black box structure that holds a mapping of the key
> location then there will ceratinly be fewer leaf levels in that structure
> as
> there are more rows per page on a narrow row?
> --
> Timothy A. Vanover MCSD, MCDBA, MCAD, MCSD for .Net
>
> "Tom Moreau" wrote:
>> The RI check would be against the key itself, which is supported by a
>> unique
>> index. Key lookups are generally quick. It doesn't really matter about
>> the
>> width of the table's rows. The key's width, coupled with disk I/O speed,
>> amount of memory and CPU speed are the overall determining factor.
>> --
>> Tom
>> ----
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> SQL Server MVP
>> Toronto, ON Canada
>> ..
>> "TVanover" <TVanover@.discussions.microsoft.com> wrote in message
>> news:C8207684-981B-48D4-922B-E5A79B8DFF60@.microsoft.com...
>> How does Sql Server do referential integrity checks? My reason for asking
>> this is a conceptual theory that a referential integrity check against a
>> narrow row would be faster than on a wider row because there would be
>> more
>> rows on a page, hence any mapping structures that SQL Server would use
>> internally to check RI would also be much smaller reducing seek and
>> traverse
>> time?
>> --
>> Timothy A. Vanover MCSD, MCDBA, MCAD, MCSD for .Net
>>