From GUIDs to ints

Say you have a complex database which, due to the usual reasons, was
designed with most PKs as GUIDs. Knowing better now (and replication
in SQL2K no longer requires GUIDs), you rework the database and use
identity ints as PKs (OK, still not my personal favorite technique,
but undeniably common out here in the world).
I'm guessing this kind of GUID-to-int redesign has happened a lot out
there over the past year or two. Any good war stories? Did it turn
out to be a good thing?
Thanks.
J.
jxstern,
Sounds like an ambitious, but worthwhile endeavour for future projects.
If your existing application is working and performing well, and you
think it will scale, I would leave alone. However if it is causing some
performance issues (confirmed by testing), I would go ahead and convert
to int.
The motto of "If it ain't broke, don't fix it." has served me well over
the years.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
jxstern wrote:
> Say you have a complex database which, due to the usual reasons, was
> designed with most PKs as GUIDs. Knowing better now (and replication
> in SQL2K no longer requires GUIDs), you rework the database and use
> identity ints as PKs (OK, still not my personal favorite technique,
> but undeniably common out here in the world).
> I'm guessing this kind of GUID-to-int redesign has happened a lot out
> there over the past year or two. Any good war stories? Did it turn
> out to be a good thing?
> Thanks.
> J.
>
|||On Fri, 05 Aug 2005 09:21:21 +0100, Mark Allison
<mark@.no.tinned.meat.mvps.org> wrote:
>Sounds like an ambitious, but worthwhile endeavour for future projects.
>If your existing application is working and performing well, and you
>think it will scale, I would leave alone. However if it is causing some
>performance issues (confirmed by testing), I would go ahead and convert
>to int.
>The motto of "If it ain't broke, don't fix it." has served me well over
>the years.
It's a fine motto I fully endorse, but in this case the system is
broken, confirmed by testing, profiling, perfmon, yada ya.
In fact, I find it very interesting and somewhat hard to explain, much
less believe, just how incredibly broken it is. That's a different
rant, but as a tease, initial estimates are that simply doing the
conversion will improve performance by 100x, yes, that's 100 times,
not just 100%. For a little database on a big server that might not
matter, but this is at least a medium database and even on a big
server it won't have nearly the required capacity until and unless we
spike the GUIDs.
Josh
|||On Fri, 05 Aug 2005 13:34:32 -0700, jxstern <jxstern@.nowhere.xyz>
wrote:
> initial estimates are that simply doing the
>conversion will improve performance by 100x, yes, that's 100 times,
>not just 100%.
Hmm, put a hold on that. Anecdotal reports are mixed, and a search of
archived discussions on the topic - that's mixed, too.
My assumption going in was that the conversion should result in a
modest 2x improvement, maybe a bit more. Including all FK's there's a
pretty significant effect on total database size, ok that's hard to
quantify exactly, too, but if it's even 10% that would ... well, maybe
that would just correspond to a linear improvement in performance of
10% also, now that I think about it. Or even ln(10%).
I even see cases where people are converting databases *from* ints
*to* GUIDs, and not just because of replication.
Bottom line, on further study, I'm more confused than before.
Tie in the debate about using surrogate keys in the first place, add
in a discussion of what happens to natural keys, or even
normalization, when you use surrogates, and hey, somebody ought to
write a book, you really can't enumerate the arguments, much less
decide between them, in anything shorter.
Fun and games,
J.
|||100X improvement would be totally unreasonable just attributed from changing
a GUID to an INT. Personally I hate GUID's myself and would always welcome
the chance to use a INT key over a GUID where possible. But performance is
most likely suffering from two aspects associated with the guids themselves.
One is the amount of page splitting that can occur on poorly designed
schemas especially if the clustered index is on the GUID. The second is the
fact the GUID's do take up more room. While disk space is cheap these days
it is the memory space I am talking about. Since a guid is 4 times large
than an int you can get a significant larger amount of rows onto a page with
ints than guids. That means better use of memory for data and index pages.
This is compounded if you have lots of page splits associated with the
guids. There are other aspects along those lines but those are the biggest I
see. The other thing that is most likely hurting you is not necessarily
attributed to the guid over the int but the guid's do tend to make it worse.
That is poor plan reuse. If you are not using stored procedures or at lease
queries that can be parameterized properly you will not get plan reuse. I
had a system once that used guids as PK's with clustered indexes on them.
They then did all adhoc queries. I made two simple changes. One was to
change the Ci to a better column that was not guid. The other was to change
a single adhoc call to a sp. I immediately received a 54,863% gain in
performance when pumping calls intothe box. We went from 8 procs at over
60% utilization down to .2% for much more calls.
Andrew J. Kelly SQL MVP
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:45n9f11h4vm4mqhfgsti98p91ble64b4jc@.4ax.com...
> On Fri, 05 Aug 2005 13:34:32 -0700, jxstern <jxstern@.nowhere.xyz>
> wrote:
> Hmm, put a hold on that. Anecdotal reports are mixed, and a search of
> archived discussions on the topic - that's mixed, too.
> My assumption going in was that the conversion should result in a
> modest 2x improvement, maybe a bit more. Including all FK's there's a
> pretty significant effect on total database size, ok that's hard to
> quantify exactly, too, but if it's even 10% that would ... well, maybe
> that would just correspond to a linear improvement in performance of
> 10% also, now that I think about it. Or even ln(10%).
> I even see cases where people are converting databases *from* ints
> *to* GUIDs, and not just because of replication.
> Bottom line, on further study, I'm more confused than before.
> Tie in the debate about using surrogate keys in the first place, add
> in a discussion of what happens to natural keys, or even
> normalization, when you use surrogates, and hey, somebody ought to
> write a book, you really can't enumerate the arguments, much less
> decide between them, in anything shorter.
> Fun and games,
> J.
>
|||Just to add that you can reduce page split by guid by using sequential guid.
Gert has made one available for sql2k.
http://sqldev.net/xp/xpguid.htm
-oj
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OY3m7nqmFHA.3960@.TK2MSFTNGP12.phx.gbl...
> 100X improvement would be totally unreasonable just attributed from
> changing a GUID to an INT. Personally I hate GUID's myself and would
> always welcome the chance to use a INT key over a GUID where possible. But
> performance is most likely suffering from two aspects associated with the
> guids themselves. One is the amount of page splitting that can occur on
> poorly designed schemas especially if the clustered index is on the GUID.
> The second is the fact the GUID's do take up more room. While disk space
> is cheap these days it is the memory space I am talking about. Since a
> guid is 4 times large than an int you can get a significant larger amount
> of rows onto a page with ints than guids. That means better use of memory
> for data and index pages. This is compounded if you have lots of page
> splits associated with the guids. There are other aspects along those
> lines but those are the biggest I see. The other thing that is most
> likely hurting you is not necessarily attributed to the guid over the int
> but the guid's do tend to make it worse. That is poor plan reuse. If you
> are not using stored procedures or at lease queries that can be
> parameterized properly you will not get plan reuse. I had a system once
> that used guids as PK's with clustered indexes on them. They then did all
> adhoc queries. I made two simple changes. One was to change the Ci to a
> better column that was not guid. The other was to change a single adhoc
> call to a sp. I immediately received a 54,863% gain in performance when
> pumping calls intothe box. We went from 8 procs at over 60% utilization
> down to .2% for much more calls.
> --
> Andrew J. Kelly SQL MVP
>
> "JXStern" <JXSternChangeX2R@.gte.net> wrote in message
> news:45n9f11h4vm4mqhfgsti98p91ble64b4jc@.4ax.com...
>

From GUIDs to ints

Say you have a complex database which, due to the usual reasons, was
designed with most PKs as GUIDs. Knowing better now (and replication
in SQL2K no longer requires GUIDs), you rework the database and use
identity ints as PKs (OK, still not my personal favorite technique,
but undeniably common out here in the world).
I'm guessing this kind of GUID-to-int redesign has happened a lot out
there over the past year or two. Any good war stories? Did it turn
out to be a good thing?
Thanks.
J.jxstern,
Sounds like an ambitious, but worthwhile endeavour for future projects.
If your existing application is working and performing well, and you
think it will scale, I would leave alone. However if it is causing some
performance issues (confirmed by testing), I would go ahead and convert
to int.
The motto of "If it ain't broke, don't fix it." has served me well over
the years.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
jxstern wrote:
> Say you have a complex database which, due to the usual reasons, was
> designed with most PKs as GUIDs. Knowing better now (and replication
> in SQL2K no longer requires GUIDs), you rework the database and use
> identity ints as PKs (OK, still not my personal favorite technique,
> but undeniably common out here in the world).
> I'm guessing this kind of GUID-to-int redesign has happened a lot out
> there over the past year or two. Any good war stories? Did it turn
> out to be a good thing?
> Thanks.
> J.
>|||On Fri, 05 Aug 2005 09:21:21 +0100, Mark Allison
<mark@.no.tinned.meat.mvps.org> wrote:
>Sounds like an ambitious, but worthwhile endeavour for future projects.
>If your existing application is working and performing well, and you
>think it will scale, I would leave alone. However if it is causing some
>performance issues (confirmed by testing), I would go ahead and convert
>to int.
>The motto of "If it ain't broke, don't fix it." has served me well over
>the years.
It's a fine motto I fully endorse, but in this case the system is
broken, confirmed by testing, profiling, perfmon, yada ya.
In fact, I find it very interesting and somewhat hard to explain, much
less believe, just how incredibly broken it is. That's a different
rant, but as a tease, initial estimates are that simply doing the
conversion will improve performance by 100x, yes, that's 100 times,
not just 100%. For a little database on a big server that might not
matter, but this is at least a medium database and even on a big
server it won't have nearly the required capacity until and unless we
spike the GUIDs.
Josh|||On Fri, 05 Aug 2005 13:34:32 -0700, jxstern <jxstern@.nowhere.xyz>
wrote:
> initial estimates are that simply doing the
>conversion will improve performance by 100x, yes, that's 100 times,
>not just 100%.
Hmm, put a hold on that. Anecdotal reports are mixed, and a search of
archived discussions on the topic - that's mixed, too.
My assumption going in was that the conversion should result in a
modest 2x improvement, maybe a bit more. Including all FK's there's a
pretty significant effect on total database size, ok that's hard to
quantify exactly, too, but if it's even 10% that would ... well, maybe
that would just correspond to a linear improvement in performance of
10% also, now that I think about it. Or even ln(10%).
I even see cases where people are converting databases *from* ints
*to* GUIDs, and not just because of replication.
Bottom line, on further study, I'm more confused than before.
Tie in the debate about using surrogate keys in the first place, add
in a discussion of what happens to natural keys, or even
normalization, when you use surrogates, and hey, somebody ought to
write a book, you really can't enumerate the arguments, much less
decide between them, in anything shorter.
Fun and games,
J.|||100X improvement would be totally unreasonable just attributed from changing
a GUID to an INT. Personally I hate GUID's myself and would always welcome
the chance to use a INT key over a GUID where possible. But performance is
most likely suffering from two aspects associated with the guids themselves.
One is the amount of page splitting that can occur on poorly designed
schemas especially if the clustered index is on the GUID. The second is the
fact the GUID's do take up more room. While disk space is cheap these days
it is the memory space I am talking about. Since a guid is 4 times large
than an int you can get a significant larger amount of rows onto a page with
ints than guids. That means better use of memory for data and index pages.
This is compounded if you have lots of page splits associated with the
guids. There are other aspects along those lines but those are the biggest I
see. The other thing that is most likely hurting you is not necessarily
attributed to the guid over the int but the guid's do tend to make it worse.
That is poor plan reuse. If you are not using stored procedures or at lease
queries that can be parameterized properly you will not get plan reuse. I
had a system once that used guids as PK's with clustered indexes on them.
They then did all adhoc queries. I made two simple changes. One was to
change the Ci to a better column that was not guid. The other was to change
a single adhoc call to a sp. I immediately received a 54,863% gain in
performance when pumping calls intothe box. We went from 8 procs at over
60% utilization down to .2% for much more calls.
--
Andrew J. Kelly SQL MVP
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:45n9f11h4vm4mqhfgsti98p91ble64b4jc@.4ax.com...
> On Fri, 05 Aug 2005 13:34:32 -0700, jxstern <jxstern@.nowhere.xyz>
> wrote:
>> initial estimates are that simply doing the
>>conversion will improve performance by 100x, yes, that's 100 times,
>>not just 100%.
> Hmm, put a hold on that. Anecdotal reports are mixed, and a search of
> archived discussions on the topic - that's mixed, too.
> My assumption going in was that the conversion should result in a
> modest 2x improvement, maybe a bit more. Including all FK's there's a
> pretty significant effect on total database size, ok that's hard to
> quantify exactly, too, but if it's even 10% that would ... well, maybe
> that would just correspond to a linear improvement in performance of
> 10% also, now that I think about it. Or even ln(10%).
> I even see cases where people are converting databases *from* ints
> *to* GUIDs, and not just because of replication.
> Bottom line, on further study, I'm more confused than before.
> Tie in the debate about using surrogate keys in the first place, add
> in a discussion of what happens to natural keys, or even
> normalization, when you use surrogates, and hey, somebody ought to
> write a book, you really can't enumerate the arguments, much less
> decide between them, in anything shorter.
> Fun and games,
> J.
>|||Just to add that you can reduce page split by guid by using sequential guid.
Gert has made one available for sql2k.
http://sqldev.net/xp/xpguid.htm
--
-oj
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OY3m7nqmFHA.3960@.TK2MSFTNGP12.phx.gbl...
> 100X improvement would be totally unreasonable just attributed from
> changing a GUID to an INT. Personally I hate GUID's myself and would
> always welcome the chance to use a INT key over a GUID where possible. But
> performance is most likely suffering from two aspects associated with the
> guids themselves. One is the amount of page splitting that can occur on
> poorly designed schemas especially if the clustered index is on the GUID.
> The second is the fact the GUID's do take up more room. While disk space
> is cheap these days it is the memory space I am talking about. Since a
> guid is 4 times large than an int you can get a significant larger amount
> of rows onto a page with ints than guids. That means better use of memory
> for data and index pages. This is compounded if you have lots of page
> splits associated with the guids. There are other aspects along those
> lines but those are the biggest I see. The other thing that is most
> likely hurting you is not necessarily attributed to the guid over the int
> but the guid's do tend to make it worse. That is poor plan reuse. If you
> are not using stored procedures or at lease queries that can be
> parameterized properly you will not get plan reuse. I had a system once
> that used guids as PK's with clustered indexes on them. They then did all
> adhoc queries. I made two simple changes. One was to change the Ci to a
> better column that was not guid. The other was to change a single adhoc
> call to a sp. I immediately received a 54,863% gain in performance when
> pumping calls intothe box. We went from 8 procs at over 60% utilization
> down to .2% for much more calls.
> --
> Andrew J. Kelly SQL MVP
>
> "JXStern" <JXSternChangeX2R@.gte.net> wrote in message
> news:45n9f11h4vm4mqhfgsti98p91ble64b4jc@.4ax.com...
>> On Fri, 05 Aug 2005 13:34:32 -0700, jxstern <jxstern@.nowhere.xyz>
>> wrote:
>> initial estimates are that simply doing the
>>conversion will improve performance by 100x, yes, that's 100 times,
>>not just 100%.
>> Hmm, put a hold on that. Anecdotal reports are mixed, and a search of
>> archived discussions on the topic - that's mixed, too.
>> My assumption going in was that the conversion should result in a
>> modest 2x improvement, maybe a bit more. Including all FK's there's a
>> pretty significant effect on total database size, ok that's hard to
>> quantify exactly, too, but if it's even 10% that would ... well, maybe
>> that would just correspond to a linear improvement in performance of
>> 10% also, now that I think about it. Or even ln(10%).
>> I even see cases where people are converting databases *from* ints
>> *to* GUIDs, and not just because of replication.
>> Bottom line, on further study, I'm more confused than before.
>> Tie in the debate about using surrogate keys in the first place, add
>> in a discussion of what happens to natural keys, or even
>> normalization, when you use surrogates, and hey, somebody ought to
>> write a book, you really can't enumerate the arguments, much less
>> decide between them, in anything shorter.
>> Fun and games,
>> J.
>>
>

From GUIDs to ints

Say you have a complex database which, due to the usual reasons, was
designed with most PKs as GUIDs. Knowing better now (and replication
in SQL2K no longer requires GUIDs), you rework the database and use
identity ints as PKs (OK, still not my personal favorite technique,
but undeniably common out here in the world).
I'm guessing this kind of GUID-to-int redesign has happened a lot out
there over the past year or two. Any good war stories? Did it turn
out to be a good thing?
Thanks.
J.jxstern,
Sounds like an ambitious, but worthwhile endeavour for future projects.
If your existing application is working and performing well, and you
think it will scale, I would leave alone. However if it is causing some
performance issues (confirmed by testing), I would go ahead and convert
to int.
The motto of "If it ain't broke, don't fix it." has served me well over
the years.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
jxstern wrote:
> Say you have a complex database which, due to the usual reasons, was
> designed with most PKs as GUIDs. Knowing better now (and replication
> in SQL2K no longer requires GUIDs), you rework the database and use
> identity ints as PKs (OK, still not my personal favorite technique,
> but undeniably common out here in the world).
> I'm guessing this kind of GUID-to-int redesign has happened a lot out
> there over the past year or two. Any good war stories? Did it turn
> out to be a good thing?
> Thanks.
> J.
>|||On Fri, 05 Aug 2005 09:21:21 +0100, Mark Allison
<mark@.no.tinned.meat.mvps.org> wrote:
>Sounds like an ambitious, but worthwhile endeavour for future projects.
>If your existing application is working and performing well, and you
>think it will scale, I would leave alone. However if it is causing some
>performance issues (confirmed by testing), I would go ahead and convert
>to int.
>The motto of "If it ain't broke, don't fix it." has served me well over
>the years.
It's a fine motto I fully endorse, but in this case the system is
broken, confirmed by testing, profiling, perfmon, yada ya.
In fact, I find it very interesting and somewhat hard to explain, much
less believe, just how incredibly broken it is. That's a different
rant, but as a tease, initial estimates are that simply doing the
conversion will improve performance by 100x, yes, that's 100 times,
not just 100%. For a little database on a big server that might not
matter, but this is at least a medium database and even on a big
server it won't have nearly the required capacity until and unless we
spike the GUIDs.
Josh|||On Fri, 05 Aug 2005 13:34:32 -0700, jxstern <jxstern@.nowhere.xyz>
wrote:
> initial estimates are that simply doing the
>conversion will improve performance by 100x, yes, that's 100 times,
>not just 100%.
Hmm, put a hold on that. Anecdotal reports are mixed, and a search of
archived discussions on the topic - that's mixed, too.
My assumption going in was that the conversion should result in a
modest 2x improvement, maybe a bit more. Including all FK's there's a
pretty significant effect on total database size, ok that's hard to
quantify exactly, too, but if it's even 10% that would ... well, maybe
that would just correspond to a linear improvement in performance of
10% also, now that I think about it. Or even ln(10%).
I even see cases where people are converting databases *from* ints
*to* GUIDs, and not just because of replication.
Bottom line, on further study, I'm more confused than before.
Tie in the debate about using surrogate keys in the first place, add
in a discussion of what happens to natural keys, or even
normalization, when you use surrogates, and hey, somebody ought to
write a book, you really can't enumerate the arguments, much less
decide between them, in anything shorter.
Fun and games,
J.|||100X improvement would be totally unreasonable just attributed from changing
a GUID to an INT. Personally I hate GUID's myself and would always welcome
the chance to use a INT key over a GUID where possible. But performance is
most likely suffering from two aspects associated with the guids themselves.
One is the amount of page splitting that can occur on poorly designed
schemas especially if the clustered index is on the GUID. The second is the
fact the GUID's do take up more room. While disk space is cheap these days
it is the memory space I am talking about. Since a guid is 4 times large
than an int you can get a significant larger amount of rows onto a page with
ints than guids. That means better use of memory for data and index pages.
This is compounded if you have lots of page splits associated with the
guids. There are other aspects along those lines but those are the biggest I
see. The other thing that is most likely hurting you is not necessarily
attributed to the guid over the int but the guid's do tend to make it worse.
That is poor plan reuse. If you are not using stored procedures or at lease
queries that can be parameterized properly you will not get plan reuse. I
had a system once that used guids as PK's with clustered indexes on them.
They then did all adhoc queries. I made two simple changes. One was to
change the Ci to a better column that was not guid. The other was to change
a single adhoc call to a sp. I immediately received a 54,863% gain in
performance when pumping calls intothe box. We went from 8 procs at over
60% utilization down to .2% for much more calls.
Andrew J. Kelly SQL MVP
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:45n9f11h4vm4mqhfgsti98p91ble64b4jc@.
4ax.com...
> On Fri, 05 Aug 2005 13:34:32 -0700, jxstern <jxstern@.nowhere.xyz>
> wrote:
> Hmm, put a hold on that. Anecdotal reports are mixed, and a search of
> archived discussions on the topic - that's mixed, too.
> My assumption going in was that the conversion should result in a
> modest 2x improvement, maybe a bit more. Including all FK's there's a
> pretty significant effect on total database size, ok that's hard to
> quantify exactly, too, but if it's even 10% that would ... well, maybe
> that would just correspond to a linear improvement in performance of
> 10% also, now that I think about it. Or even ln(10%).
> I even see cases where people are converting databases *from* ints
> *to* GUIDs, and not just because of replication.
> Bottom line, on further study, I'm more confused than before.
> Tie in the debate about using surrogate keys in the first place, add
> in a discussion of what happens to natural keys, or even
> normalization, when you use surrogates, and hey, somebody ought to
> write a book, you really can't enumerate the arguments, much less
> decide between them, in anything shorter.
> Fun and games,
> J.
>|||Just to add that you can reduce page split by guid by using sequential guid.
Gert has made one available for sql2k.
http://sqldev.net/xp/xpguid.htm
-oj
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OY3m7nqmFHA.3960@.TK2MSFTNGP12.phx.gbl...
> 100X improvement would be totally unreasonable just attributed from
> changing a GUID to an INT. Personally I hate GUID's myself and would
> always welcome the chance to use a INT key over a GUID where possible. But
> performance is most likely suffering from two aspects associated with the
> guids themselves. One is the amount of page splitting that can occur on
> poorly designed schemas especially if the clustered index is on the GUID.
> The second is the fact the GUID's do take up more room. While disk space
> is cheap these days it is the memory space I am talking about. Since a
> guid is 4 times large than an int you can get a significant larger amount
> of rows onto a page with ints than guids. That means better use of memory
> for data and index pages. This is compounded if you have lots of page
> splits associated with the guids. There are other aspects along those
> lines but those are the biggest I see. The other thing that is most
> likely hurting you is not necessarily attributed to the guid over the int
> but the guid's do tend to make it worse. That is poor plan reuse. If you
> are not using stored procedures or at lease queries that can be
> parameterized properly you will not get plan reuse. I had a system once
> that used guids as PK's with clustered indexes on them. They then did all
> adhoc queries. I made two simple changes. One was to change the Ci to a
> better column that was not guid. The other was to change a single adhoc
> call to a sp. I immediately received a 54,863% gain in performance when
> pumping calls intothe box. We went from 8 procs at over 60% utilization
> down to .2% for much more calls.
> --
> Andrew J. Kelly SQL MVP
>
> "JXStern" <JXSternChangeX2R@.gte.net> wrote in message
> news:45n9f11h4vm4mqhfgsti98p91ble64b4jc@.
4ax.com...
>

Fragmentation on sql server

SQL2K Ent SP3a Win 2k Adv
We've SAN disks showing high levels of fragmentation and have been
looking into defragmenting this disks with a 3rd party tool, as Win2K
won't defrag anything with block sizes > 4k.
Reading:
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/maintain/optimize/ss2kidbp.asp
states;
"On large-scale environments that benefit from more intelligent disk
subsystems, such as SAN (storage area networks)
environments, correcting disk fragmentation is not necessary."
Can anybody explain (or indeed refer me to some information on this), for my
benefit and others, why this is the case? Even with a
large cache, to my mind, fragmentation must have some performance hit.
All ideas welcome.That is because the disk management tools on NT or on any
SAN client are designed for direct physical access (i.e.
they work on local drives), and currently they treat SAN
drives also as local drives. But they really don't know
about the actualy disk layout on SAN, even today's SAN
tools cannot give you much information. it is all
guesswork by SAN admins.
This is tauted as one of the advantages of the SAN that
physical disk management is transparent to the OS and you
can change your disks layouts without affecting OSes. but
because of this you can't use existing tools to monitor
and manage SAN drives from the client tools. SAN is a new
technogogy and it has yet to develop such tools to work on
SAN. I don't know of any SAN vendor who supplies any good
tool for disk monitoring and management. First or second
generation of such tools will not run on NT but you have
to use terminal client interface for it. And may be well
after a while in future they will come up with a nice gui
and accurate tool that can run on NT.
>--Original Message--
>SQL2K Ent SP3a Win 2k Adv
>We've SAN disks showing high levels of fragmentation and
have been
>looking into defragmenting this disks with a 3rd party
tool, as Win2K
>won't defrag anything with block sizes > 4k.
>Reading:
>http://www.microsoft.com/technet/treeview/default.asp?
url=/technet/prodtechnol/sql/maintain/optimize/ss2kidbp.asp
>states;
>"On large-scale environments that benefit from more
intelligent disk
>subsystems, such as SAN (storage area networks)
>environments, correcting disk fragmentation is not
necessary."
>Can anybody explain (or indeed refer me to some
information on this), for my
>benefit and others, why this is the case? Even with a
>large cache, to my mind, fragmentation must have some
performance hit.
>All ideas welcome.
>
>.
>