Interesting problem with table/filesystem growth

Any body have any ideas on this one?
Have an Sqlserver 2000 instance with SP3a on it and had a
problem where we had multiple data files within the
primary filegroup. An end user was adding rows into the
table and received an out of space error on the data
component. Checking the properties on the files the auto
extend option had been turned off , which was fine in our
environment, however, checking the space used in each of
these files showed that there was plenty of space
available for use in all. (No, it wasnt the trans log that
gave grief), I allowed the autoextend on each file and got
over the problem for the table in the short term , (and
saw one of the files extend.)
This lead me to do some thinking about the way Sqlserver
handles the growth of tables on multiple files. The good
book says that Sqlserver will allocate in a round robin
fashion the data pages to a table, however this doesnt
seem to be the case. Also, How does the table what file it
is on. I found this in the sysindexes table and decoding
the first value (Contains fileid and page id and row
offset).
Thats fine, however the major problem is, if Sqlserver
doesnt do the round robin allocation of datapages like it
should, then are all your free space calcs on the file
allocation valid?
Any thoughts' Any stored procedures about to handle this'
cheers
MikeWe've had an experience in the past, where the disks seemed to get overused
and reported an error similar to (unable to allocate space). Unfortunately,
I cannot
remember the specifics. We've had a problem where the auto-extend conflicts
with
an insert.
With regard to the round robin filling, sql server will fill the files using
a proporitional
algorithm. Therefore if you fill one file then add another, the 2nd file
will get filled. If
you create 2 files at the same time, then you will observe that each file is
filled with the
same amount of data each time. In short a 20GB insert will put 10GB in each
file.
HTH
"Mike" <anonymous@.discussions.microsoft.com> wrote in message
news:1a9101c3e08e$548d4c70$a101280a@.phx.gbl...
> Any body have any ideas on this one?
> Have an Sqlserver 2000 instance with SP3a on it and had a
> problem where we had multiple data files within the
> primary filegroup. An end user was adding rows into the
> table and received an out of space error on the data
> component. Checking the properties on the files the auto
> extend option had been turned off , which was fine in our
> environment, however, checking the space used in each of
> these files showed that there was plenty of space
> available for use in all. (No, it wasnt the trans log that
> gave grief), I allowed the autoextend on each file and got
> over the problem for the table in the short term , (and
> saw one of the files extend.)
> This lead me to do some thinking about the way Sqlserver
> handles the growth of tables on multiple files. The good
> book says that Sqlserver will allocate in a round robin
> fashion the data pages to a table, however this doesnt
> seem to be the case. Also, How does the table what file it
> is on. I found this in the sysindexes table and decoding
> the first value (Contains fileid and page id and row
> offset).
> Thats fine, however the major problem is, if Sqlserver
> doesnt do the round robin allocation of datapages like it
> should, then are all your free space calcs on the file
> allocation valid?
> Any thoughts' Any stored procedures about to handle this'
> cheers
> Mike|||Interesting,
still leads to the problem where you think you should have
space available because you tally the total of all file
systems and unfortunately one is full!!! Kind of makes one
stop and think about what level should your space
statistcs be collected at and how you manage them!
>--Original Message--
>We've had an experience in the past, where the disks
seemed to get overused
>and reported an error similar to (unable to allocate
space). Unfortunately,
>I cannot
>remember the specifics. We've had a problem where the
auto-extend conflicts
>with
>an insert.
>
>With regard to the round robin filling, sql server will
fill the files using
>a proporitional
>algorithm. Therefore if you fill one file then add
another, the 2nd file
>will get filled. If
>you create 2 files at the same time, then you will
observe that each file is
>filled with the
>same amount of data each time. In short a 20GB insert
will put 10GB in each
>file.
>HTH
>"Mike" <anonymous@.discussions.microsoft.com> wrote in
message
>news:1a9101c3e08e$548d4c70$a101280a@.phx.gbl...
>> Any body have any ideas on this one?
>> Have an Sqlserver 2000 instance with SP3a on it and had
a
>> problem where we had multiple data files within the
>> primary filegroup. An end user was adding rows into the
>> table and received an out of space error on the data
>> component. Checking the properties on the files the auto
>> extend option had been turned off , which was fine in
our
>> environment, however, checking the space used in each of
>> these files showed that there was plenty of space
>> available for use in all. (No, it wasnt the trans log
that
>> gave grief), I allowed the autoextend on each file and
got
>> over the problem for the table in the short term , (and
>> saw one of the files extend.)
>> This lead me to do some thinking about the way Sqlserver
>> handles the growth of tables on multiple files. The good
>> book says that Sqlserver will allocate in a round robin
>> fashion the data pages to a table, however this doesnt
>> seem to be the case. Also, How does the table what file
it
>> is on. I found this in the sysindexes table and decoding
>> the first value (Contains fileid and page id and row
>> offset).
>> Thats fine, however the major problem is, if Sqlserver
>> doesnt do the round robin allocation of datapages like
it
>> should, then are all your free space calcs on the file
>> allocation valid?
>> Any thoughts' Any stored procedures about to handle
this'
>> cheers
>> Mike
>
>.
>

Interesting problem with table/filesystem growth

Any body have any ideas on this one?
Have an Sqlserver 2000 instance with SP3a on it and had a
problem where we had multiple data files within the
primary filegroup. An end user was adding rows into the
table and received an out of space error on the data
component. Checking the properties on the files the auto
extend option had been turned off , which was fine in our
environment, however, checking the space used in each of
these files showed that there was plenty of space
available for use in all. (No, it wasnt the trans log that
gave grief), I allowed the autoextend on each file and got
over the problem for the table in the short term , (and
saw one of the files extend.)
This lead me to do some thinking about the way Sqlserver
handles the growth of tables on multiple files. The good
book says that Sqlserver will allocate in a round robin
fashion the data pages to a table, however this doesnt
seem to be the case. Also, How does the table what file it
is on. I found this in the sysindexes table and decoding
the first value (Contains fileid and page id and row
offset).
Thats fine, however the major problem is, if Sqlserver
doesnt do the round robin allocation of datapages like it
should, then are all your free space calcs on the file
allocation valid?
Any thoughts' Any stored procedures about to handle this'
cheers
MikeWe've had an experience in the past, where the disks seemed to get overused
and reported an error similar to (unable to allocate space). Unfortunately,
I cannot
remember the specifics. We've had a problem where the auto-extend conflicts
with
an insert.
With regard to the round robin filling, sql server will fill the files using
a proporitional
algorithm. Therefore if you fill one file then add another, the 2nd file
will get filled. If
you create 2 files at the same time, then you will observe that each file is
filled with the
same amount of data each time. In short a 20GB insert will put 10GB in each
file.
HTH
"Mike" <anonymous@.discussions.microsoft.com> wrote in message
news:1a9101c3e08e$548d4c70$a101280a@.phx.gbl...

quote:

---

> Any body have any ideas on this one?
> Have an Sqlserver 2000 instance with SP3a on it and had a
> problem where we had multiple data files within the
> primary filegroup. An end user was adding rows into the
> table and received an out of space error on the data
> component. Checking the properties on the files the auto
> extend option had been turned off , which was fine in our
> environment, however, checking the space used in each of
> these files showed that there was plenty of space
> available for use in all. (No, it wasnt the trans log that
> gave grief), I allowed the autoextend on each file and got
> over the problem for the table in the short term , (and
> saw one of the files extend.)
> This lead me to do some thinking about the way Sqlserver
> handles the growth of tables on multiple files. The good
> book says that Sqlserver will allocate in a round robin
> fashion the data pages to a table, however this doesnt
> seem to be the case. Also, How does the table what file it
> is on. I found this in the sysindexes table and decoding
> the first value (Contains fileid and page id and row
> offset).
> Thats fine, however the major problem is, if Sqlserver
> doesnt do the round robin allocation of datapages like it
> should, then are all your free space calcs on the file
> allocation valid?
> Any thoughts' Any stored procedures about to handle this'
> cheers
> Mike

---

|||Interesting,
still leads to the problem where you think you should have
space available because you tally the total of all file
systems and unfortunately one is full!!! Kind of makes one
stop and think about what level should your space
statistcs be collected at and how you manage them!

quote:

---

>--Original Message--
>We've had an experience in the past, where the disks

---

seemed to get overused

quote:

---

>and reported an error similar to (unable to allocate

---

space). Unfortunately,

quote:

---

>I cannot
>remember the specifics. We've had a problem where the

---

auto-extend conflicts

quote:

---

>with
>an insert.
>
>With regard to the round robin filling, sql server will

---

fill the files using

quote:

---

>a proporitional
>algorithm. Therefore if you fill one file then add

---

another, the 2nd file

quote:

---

>will get filled. If
>you create 2 files at the same time, then you will

---

observe that each file is

quote:

---

>filled with the
>same amount of data each time. In short a 20GB insert

---

will put 10GB in each

quote:

---

>file.
>HTH
>"Mike" <anonymous@.discussions.microsoft.com> wrote in

---

message

quote:

---

>news:1a9101c3e08e$548d4c70$a101280a@.phx.gbl...
a[QUOTE]
our[QUOTE]
that[QUOTE]
got[QUOTE]
it[QUOTE]
it[QUOTE]
this'[QUOTE]
>
>.
>

---

sql

Interesting CASE behavior -- bug?

(SQL Server 2000, SP3a)
Hello all!
I had a situation where I was getting some extraneous spaces in some string
building, and
it took me a while to figure it out. I think I can appreciate *why* it's do
ing this, but
I wish it weren't.
Consider the following:
declare @.CrLf nchar(2) select @.CrLf = nchar(13) + nchar(10)
declare @.String nvarchar(255) select @.String = N''
select @.String = @.String + case when (len(@.String) != 0) then @.CrLf + @.CrLf
else N'' end +
N'Test'
print N'"' + @.String + N'"'
print [master].[dbo].& #91;fn_varbintohexstr](convert(varbinary
(16),
@.String))
The output is:
" Test"
0x20002000200020005400650073007400
It feels like, for some reason, the CASE is evaluating both "sides" of the r
esultant
expression, and determining that it can be coerced to a NCHAR(2). However,
somewhere
along the lines, it gets confused and is introducing 2 bytes for every NCHAR
-- sort of
like accidentally making it a CHAR(4) that then gets promoted to a NCHAR(4)
(as per the
NULL bytes in every other character).
If I change the definition of @.CrLf to be a NVARCHAR(2), then everything wor
ks as
expected.
It's very odd, and certainly smells like a bug to me.
Thoughts?Hi John,
This behaviour isn't a bug. CASE is an expression that returns one datatype
only, and the datatype it returns is determined by looking at the datatypes
of all the possible results of the expression, and then choosing the
appropriate datatype according to the datatype precedence. So what happens
is that your CASE can either return a zero length NCHAR literal (N''), or a
NCHAR(4) (@.CrLf + @.CrLf) so the return type of your CASE expression will be
NCHAR(4), as this has a higher precedence.
--
Jacco Schalkwijk
SQL Server MVP
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:OM4e%23b6$DHA.3048@.tk2msftngp13.phx.gbl...
> (SQL Server 2000, SP3a)
> Hello all!
> I had a situation where I was getting some extraneous spaces in some
string building, and
> it took me a while to figure it out. I think I can appreciate *why* it's
doing this, but
> I wish it weren't.
> Consider the following:
>
> declare @.CrLf nchar(2) select @.CrLf = nchar(13) + nchar(10)
> declare @.String nvarchar(255) select @.String = N''
> select @.String = @.String + case when (len(@.String) != 0) then @.CrLf +
@.CrLf else N'' end +
> N'Test'
> print N'"' + @.String + N'"'
> print [master].[dbo].& #91;fn_varbintohexstr](convert(varbinary
(16)
, @.String))
>
> The output is:
>
> " Test"
> 0x20002000200020005400650073007400
>
> It feels like, for some reason, the CASE is evaluating both "sides" of the
resultant
> expression, and determining that it can be coerced to a NCHAR(2).
However, somewhere
> along the lines, it gets confused and is introducing 2 bytes for every
NCHAR -- sort of
> like accidentally making it a CHAR(4) that then gets promoted to a
NCHAR(4) (as per the
> NULL bytes in every other character).
> If I change the definition of @.CrLf to be a NVARCHAR(2), then everything
works as
> expected.
> It's very odd, and certainly smells like a bug to me.
> Thoughts?
>|||Ah, yes...I think I grok that -- I think that I forgot that I had *two* @.CrL
fs in my first
expression (hence the doubling).
Thanks for your help!
John Peterson
"Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
news:uWhzb36$DHA.3352@.TK2MSFTNGP09.phx.gbl...
> Hi John,
> This behaviour isn't a bug. CASE is an expression that returns one datatyp
e
> only, and the datatype it returns is determined by looking at the datatype
s
> of all the possible results of the expression, and then choosing the
> appropriate datatype according to the datatype precedence. So what happens
> is that your CASE can either return a zero length NCHAR literal (N''), or
a
> NCHAR(4) (@.CrLf + @.CrLf) so the return type of your CASE expression will b
e
> NCHAR(4), as this has a higher precedence.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:OM4e%23b6$DHA.3048@.tk2msftngp13.phx.gbl...
> string building, and
> doing this, but
> @.CrLf else N'' end +
> resultant
> However, somewhere
> NCHAR -- sort of
> NCHAR(4) (as per the
> works as
>

Interesting CASE behavior -- bug?

(SQL Server 2000, SP3a)
Hello all!
I had a situation where I was getting some extraneous spaces in some string building, and
it took me a while to figure it out. I think I can appreciate *why* it's doing this, but
I wish it weren't.
Consider the following:
declare @.CrLf nchar(2) select @.CrLf = nchar(13) + nchar(10)
declare @.String nvarchar(255) select @.String = N''
select @.String = @.String + case when (len(@.String) != 0) then @.CrLf + @.CrLf else N'' end +
N'Test'
print N'"' + @.String + N'"'
print [master].[dbo].[fn_varbintohexstr](convert(varbinary(16), @.String))
The output is:
" Test"
0x20002000200020005400650073007400
It feels like, for some reason, the CASE is evaluating both "sides" of the resultant
expression, and determining that it can be coerced to a NCHAR(2). However, somewhere
along the lines, it gets confused and is introducing 2 bytes for every NCHAR -- sort of
like accidentally making it a CHAR(4) that then gets promoted to a NCHAR(4) (as per the
NULL bytes in every other character).
If I change the definition of @.CrLf to be a NVARCHAR(2), then everything works as
expected.
It's very odd, and certainly smells like a bug to me.
Thoughts?Hi John,
This behaviour isn't a bug. CASE is an expression that returns one datatype
only, and the datatype it returns is determined by looking at the datatypes
of all the possible results of the expression, and then choosing the
appropriate datatype according to the datatype precedence. So what happens
is that your CASE can either return a zero length NCHAR literal (N''), or a
NCHAR(4) (@.CrLf + @.CrLf) so the return type of your CASE expression will be
NCHAR(4), as this has a higher precedence.
--
Jacco Schalkwijk
SQL Server MVP
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:OM4e%23b6$DHA.3048@.tk2msftngp13.phx.gbl...
> (SQL Server 2000, SP3a)
> Hello all!
> I had a situation where I was getting some extraneous spaces in some
string building, and
> it took me a while to figure it out. I think I can appreciate *why* it's
doing this, but
> I wish it weren't.
> Consider the following:
>
> declare @.CrLf nchar(2) select @.CrLf = nchar(13) + nchar(10)
> declare @.String nvarchar(255) select @.String = N''
> select @.String = @.String + case when (len(@.String) != 0) then @.CrLf +
@.CrLf else N'' end +
> N'Test'
> print N'"' + @.String + N'"'
> print [master].[dbo].[fn_varbintohexstr](convert(varbinary(16), @.String))
>
> The output is:
>
> " Test"
> 0x20002000200020005400650073007400
>
> It feels like, for some reason, the CASE is evaluating both "sides" of the
resultant
> expression, and determining that it can be coerced to a NCHAR(2).
However, somewhere
> along the lines, it gets confused and is introducing 2 bytes for every
NCHAR -- sort of
> like accidentally making it a CHAR(4) that then gets promoted to a
NCHAR(4) (as per the
> NULL bytes in every other character).
> If I change the definition of @.CrLf to be a NVARCHAR(2), then everything
works as
> expected.
> It's very odd, and certainly smells like a bug to me.
> Thoughts?
>|||Ah, yes...I think I grok that -- I think that I forgot that I had *two* @.CrLfs in my first
expression (hence the doubling).
Thanks for your help!
John Peterson
"Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
news:uWhzb36$DHA.3352@.TK2MSFTNGP09.phx.gbl...
> Hi John,
> This behaviour isn't a bug. CASE is an expression that returns one datatype
> only, and the datatype it returns is determined by looking at the datatypes
> of all the possible results of the expression, and then choosing the
> appropriate datatype according to the datatype precedence. So what happens
> is that your CASE can either return a zero length NCHAR literal (N''), or a
> NCHAR(4) (@.CrLf + @.CrLf) so the return type of your CASE expression will be
> NCHAR(4), as this has a higher precedence.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:OM4e%23b6$DHA.3048@.tk2msftngp13.phx.gbl...
> > (SQL Server 2000, SP3a)
> >
> > Hello all!
> >
> > I had a situation where I was getting some extraneous spaces in some
> string building, and
> > it took me a while to figure it out. I think I can appreciate *why* it's
> doing this, but
> > I wish it weren't.
> >
> > Consider the following:
> >
> >
> > declare @.CrLf nchar(2) select @.CrLf = nchar(13) + nchar(10)
> > declare @.String nvarchar(255) select @.String = N''
> >
> > select @.String = @.String + case when (len(@.String) != 0) then @.CrLf +
> @.CrLf else N'' end +
> > N'Test'
> > print N'"' + @.String + N'"'
> > print [master].[dbo].[fn_varbintohexstr](convert(varbinary(16), @.String))
> >
> >
> > The output is:
> >
> >
> > " Test"
> > 0x20002000200020005400650073007400
> >
> >
> > It feels like, for some reason, the CASE is evaluating both "sides" of the
> resultant
> > expression, and determining that it can be coerced to a NCHAR(2).
> However, somewhere
> > along the lines, it gets confused and is introducing 2 bytes for every
> NCHAR -- sort of
> > like accidentally making it a CHAR(4) that then gets promoted to a
> NCHAR(4) (as per the
> > NULL bytes in every other character).
> >
> > If I change the definition of @.CrLf to be a NVARCHAR(2), then everything
> works as
> > expected.
> >
> > It's very odd, and certainly smells like a bug to me.
> >
> > Thoughts?
> >
> >
>