Hello everybody:
How can I do to calculate the record size in bytes, and the table size in
bytes? Does exist any stored procedure to do that '
Thanks
Richard_SQLTable size:
EXEC sp_spaceused 'table_name'
For row size, you can average by the above result / SELECT COUNT(*) FROM
table_name
For individual rows, this gets a little trickier because there is overhead
for certain datatypes, and whether the data is nullable and/or is null.
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Richard_SQL" <Richard_SQL@.discussions.microsoft.com> wrote in message
news:ED295DB2-396D-4E5D-B42B-CAF858473F04@.microsoft.com...
> Hello everybody:
> How can I do to calculate the record size in bytes, and the table size in
> bytes? Does exist any stored procedure to do that '
> Thanks
> Richard_SQL|||Richard,
Be sure to use @.updateusage = 'TRUE' when invoking sp_spaceused.
Also, for more exact calculations concerning table and row size, see
"Estimating the Size of a Table" in SQL Server 2000 Books Online. There are
related topics for calculating table size for a heap table and a clustered
table.
Ron
--
Ron Talmage
SQL Server MVP
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OSjvpDB9EHA.4004@.tk2msftngp13.phx.gbl...
> Table size:
> EXEC sp_spaceused 'table_name'
> For row size, you can average by the above result / SELECT COUNT(*) FROM
> table_name
> For individual rows, this gets a little trickier because there is overhead
> for certain datatypes, and whether the data is nullable and/or is null.
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Richard_SQL" <Richard_SQL@.discussions.microsoft.com> wrote in message
> news:ED295DB2-396D-4E5D-B42B-CAF858473F04@.microsoft.com...
> > Hello everybody:
> >
> > How can I do to calculate the record size in bytes, and the table size
in
> > bytes? Does exist any stored procedure to do that '
> >
> > Thanks
> >
> > Richard_SQL
>|||Thanks a lot for your help.
Richard
"Ron Talmage" wrote:
> Richard,
> Be sure to use @.updateusage = 'TRUE' when invoking sp_spaceused.
> Also, for more exact calculations concerning table and row size, see
> "Estimating the Size of a Table" in SQL Server 2000 Books Online. There are
> related topics for calculating table size for a heap table and a clustered
> table.
> Ron
> --
> Ron Talmage
> SQL Server MVP
>
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:OSjvpDB9EHA.4004@.tk2msftngp13.phx.gbl...
> > Table size:
> >
> > EXEC sp_spaceused 'table_name'
> >
> > For row size, you can average by the above result / SELECT COUNT(*) FROM
> > table_name
> >
> > For individual rows, this gets a little trickier because there is overhead
> > for certain datatypes, and whether the data is nullable and/or is null.
> >
> > --
> > http://www.aspfaq.com/
> > (Reverse address to reply.)
> >
> >
> >
> >
> > "Richard_SQL" <Richard_SQL@.discussions.microsoft.com> wrote in message
> > news:ED295DB2-396D-4E5D-B42B-CAF858473F04@.microsoft.com...
> > > Hello everybody:
> > >
> > > How can I do to calculate the record size in bytes, and the table size
> in
> > > bytes? Does exist any stored procedure to do that '
> > >
> > > Thanks
> > >
> > > Richard_SQL
> >
> >
>
>
Showing posts with label bytes. Show all posts
Showing posts with label bytes. Show all posts
Tuesday, March 20, 2012
Calculate row size
Hello everybody:
How can I do to calculate the record size in bytes, and the table size in
bytes? Does exist any stored procedure to do that '
Thanks
Richard_SQLTable size:
EXEC sp_spaceused 'table_name'
For row size, you can average by the above result / SELECT COUNT(*) FROM
table_name
For individual rows, this gets a little trickier because there is overhead
for certain datatypes, and whether the data is nullable and/or is null.
http://www.aspfaq.com/
(Reverse address to reply.)
"Richard_SQL" <Richard_SQL@.discussions.microsoft.com> wrote in message
news:ED295DB2-396D-4E5D-B42B-CAF858473F04@.microsoft.com...
> Hello everybody:
> How can I do to calculate the record size in bytes, and the table size in
> bytes? Does exist any stored procedure to do that '
> Thanks
> Richard_SQL|||Richard,
Be sure to use @.updateusage = 'TRUE' when invoking sp_spaceused.
Also, for more exact calculations concerning table and row size, see
"Estimating the Size of a Table" in SQL Server 2000 Books Online. There are
related topics for calculating table size for a heap table and a clustered
table.
Ron
--
Ron Talmage
SQL Server MVP
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OSjvpDB9EHA.4004@.tk2msftngp13.phx.gbl...
> Table size:
> EXEC sp_spaceused 'table_name'
> For row size, you can average by the above result / SELECT COUNT(*) FROM
> table_name
> For individual rows, this gets a little trickier because there is overhead
> for certain datatypes, and whether the data is nullable and/or is null.
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Richard_SQL" <Richard_SQL@.discussions.microsoft.com> wrote in message
> news:ED295DB2-396D-4E5D-B42B-CAF858473F04@.microsoft.com...
in[vbcol=seagreen]
>|||Thanks a lot for your help.
Richard
"Ron Talmage" wrote:
> Richard,
> Be sure to use @.updateusage = 'TRUE' when invoking sp_spaceused.
> Also, for more exact calculations concerning table and row size, see
> "Estimating the Size of a Table" in SQL Server 2000 Books Online. There ar
e
> related topics for calculating table size for a heap table and a clustered
> table.
> Ron
> --
> Ron Talmage
> SQL Server MVP
>
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:OSjvpDB9EHA.4004@.tk2msftngp13.phx.gbl...
> in
>
>
How can I do to calculate the record size in bytes, and the table size in
bytes? Does exist any stored procedure to do that '
Thanks
Richard_SQLTable size:
EXEC sp_spaceused 'table_name'
For row size, you can average by the above result / SELECT COUNT(*) FROM
table_name
For individual rows, this gets a little trickier because there is overhead
for certain datatypes, and whether the data is nullable and/or is null.
http://www.aspfaq.com/
(Reverse address to reply.)
"Richard_SQL" <Richard_SQL@.discussions.microsoft.com> wrote in message
news:ED295DB2-396D-4E5D-B42B-CAF858473F04@.microsoft.com...
> Hello everybody:
> How can I do to calculate the record size in bytes, and the table size in
> bytes? Does exist any stored procedure to do that '
> Thanks
> Richard_SQL|||Richard,
Be sure to use @.updateusage = 'TRUE' when invoking sp_spaceused.
Also, for more exact calculations concerning table and row size, see
"Estimating the Size of a Table" in SQL Server 2000 Books Online. There are
related topics for calculating table size for a heap table and a clustered
table.
Ron
--
Ron Talmage
SQL Server MVP
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OSjvpDB9EHA.4004@.tk2msftngp13.phx.gbl...
> Table size:
> EXEC sp_spaceused 'table_name'
> For row size, you can average by the above result / SELECT COUNT(*) FROM
> table_name
> For individual rows, this gets a little trickier because there is overhead
> for certain datatypes, and whether the data is nullable and/or is null.
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Richard_SQL" <Richard_SQL@.discussions.microsoft.com> wrote in message
> news:ED295DB2-396D-4E5D-B42B-CAF858473F04@.microsoft.com...
in[vbcol=seagreen]
>|||Thanks a lot for your help.
Richard
"Ron Talmage" wrote:
> Richard,
> Be sure to use @.updateusage = 'TRUE' when invoking sp_spaceused.
> Also, for more exact calculations concerning table and row size, see
> "Estimating the Size of a Table" in SQL Server 2000 Books Online. There ar
e
> related topics for calculating table size for a heap table and a clustered
> table.
> Ron
> --
> Ron Talmage
> SQL Server MVP
>
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:OSjvpDB9EHA.4004@.tk2msftngp13.phx.gbl...
> in
>
>
Calculate row size
Hello everybody:
How can I do to calculate the record size in bytes, and the table size in
bytes? Does exist any stored procedure to do that ?
Thanks
Richard_SQL
Table size:
EXEC sp_spaceused 'table_name'
For row size, you can average by the above result / SELECT COUNT(*) FROM
table_name
For individual rows, this gets a little trickier because there is overhead
for certain datatypes, and whether the data is nullable and/or is null.
http://www.aspfaq.com/
(Reverse address to reply.)
"Richard_SQL" <Richard_SQL@.discussions.microsoft.com> wrote in message
news:ED295DB2-396D-4E5D-B42B-CAF858473F04@.microsoft.com...
> Hello everybody:
> How can I do to calculate the record size in bytes, and the table size in
> bytes? Does exist any stored procedure to do that ?
> Thanks
> Richard_SQL
|||Richard,
Be sure to use @.updateusage = 'TRUE' when invoking sp_spaceused.
Also, for more exact calculations concerning table and row size, see
"Estimating the Size of a Table" in SQL Server 2000 Books Online. There are
related topics for calculating table size for a heap table and a clustered
table.
Ron
Ron Talmage
SQL Server MVP
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OSjvpDB9EHA.4004@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> Table size:
> EXEC sp_spaceused 'table_name'
> For row size, you can average by the above result / SELECT COUNT(*) FROM
> table_name
> For individual rows, this gets a little trickier because there is overhead
> for certain datatypes, and whether the data is nullable and/or is null.
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Richard_SQL" <Richard_SQL@.discussions.microsoft.com> wrote in message
> news:ED295DB2-396D-4E5D-B42B-CAF858473F04@.microsoft.com...
in
>
|||Thanks a lot for your help.
Richard
"Ron Talmage" wrote:
> Richard,
> Be sure to use @.updateusage = 'TRUE' when invoking sp_spaceused.
> Also, for more exact calculations concerning table and row size, see
> "Estimating the Size of a Table" in SQL Server 2000 Books Online. There are
> related topics for calculating table size for a heap table and a clustered
> table.
> Ron
> --
> Ron Talmage
> SQL Server MVP
>
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:OSjvpDB9EHA.4004@.tk2msftngp13.phx.gbl...
> in
>
>
How can I do to calculate the record size in bytes, and the table size in
bytes? Does exist any stored procedure to do that ?
Thanks
Richard_SQL
Table size:
EXEC sp_spaceused 'table_name'
For row size, you can average by the above result / SELECT COUNT(*) FROM
table_name
For individual rows, this gets a little trickier because there is overhead
for certain datatypes, and whether the data is nullable and/or is null.
http://www.aspfaq.com/
(Reverse address to reply.)
"Richard_SQL" <Richard_SQL@.discussions.microsoft.com> wrote in message
news:ED295DB2-396D-4E5D-B42B-CAF858473F04@.microsoft.com...
> Hello everybody:
> How can I do to calculate the record size in bytes, and the table size in
> bytes? Does exist any stored procedure to do that ?
> Thanks
> Richard_SQL
|||Richard,
Be sure to use @.updateusage = 'TRUE' when invoking sp_spaceused.
Also, for more exact calculations concerning table and row size, see
"Estimating the Size of a Table" in SQL Server 2000 Books Online. There are
related topics for calculating table size for a heap table and a clustered
table.
Ron
Ron Talmage
SQL Server MVP
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OSjvpDB9EHA.4004@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> Table size:
> EXEC sp_spaceused 'table_name'
> For row size, you can average by the above result / SELECT COUNT(*) FROM
> table_name
> For individual rows, this gets a little trickier because there is overhead
> for certain datatypes, and whether the data is nullable and/or is null.
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Richard_SQL" <Richard_SQL@.discussions.microsoft.com> wrote in message
> news:ED295DB2-396D-4E5D-B42B-CAF858473F04@.microsoft.com...
in
>
|||Thanks a lot for your help.
Richard
"Ron Talmage" wrote:
> Richard,
> Be sure to use @.updateusage = 'TRUE' when invoking sp_spaceused.
> Also, for more exact calculations concerning table and row size, see
> "Estimating the Size of a Table" in SQL Server 2000 Books Online. There are
> related topics for calculating table size for a heap table and a clustered
> table.
> Ron
> --
> Ron Talmage
> SQL Server MVP
>
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:OSjvpDB9EHA.4004@.tk2msftngp13.phx.gbl...
> in
>
>
Sunday, February 19, 2012
bytes used per datatype
Trying to calculate the row size and wanted to know if theres a bettter way
to do it than just finding how much each datatype sizes and add accordingly.
I have an int, datetime,Smalldatetime,money,bit ,smallint. Can you tell me
the size of these datatypes
Thanks
Look here:
http://groups.google.de/groups?q=row...oft.com&rnum=3
select sum(CHARACTER_MAXIMUM_LENGTH)
from information_schema.columns
where TABLE_SCHEMA = 'dbo'
and TABLE_NAME = 'authors'
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"Hassan" <fatima_ja@.hotmail.com> schrieb im Newsbeitrag
news:O0J4XGLRFHA.3544@.TK2MSFTNGP12.phx.gbl...
> Trying to calculate the row size and wanted to know if theres a bettter
> way
> to do it than just finding how much each datatype sizes and add
> accordingly.
> I have an int, datetime,Smalldatetime,money,bit ,smallint. Can you tell me
> the size of these datatypes
> Thanks
>
to do it than just finding how much each datatype sizes and add accordingly.
I have an int, datetime,Smalldatetime,money,bit ,smallint. Can you tell me
the size of these datatypes
Thanks
Look here:
http://groups.google.de/groups?q=row...oft.com&rnum=3
select sum(CHARACTER_MAXIMUM_LENGTH)
from information_schema.columns
where TABLE_SCHEMA = 'dbo'
and TABLE_NAME = 'authors'
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"Hassan" <fatima_ja@.hotmail.com> schrieb im Newsbeitrag
news:O0J4XGLRFHA.3544@.TK2MSFTNGP12.phx.gbl...
> Trying to calculate the row size and wanted to know if theres a bettter
> way
> to do it than just finding how much each datatype sizes and add
> accordingly.
> I have an int, datetime,Smalldatetime,money,bit ,smallint. Can you tell me
> the size of these datatypes
> Thanks
>
bytes used per datatype
Trying to calculate the row size and wanted to know if theres a bettter way
to do it than just finding how much each datatype sizes and add accordingly.
I have an int, datetime,Smalldatetime,money,bit ,smallint. Can you tell me
the size of these datatypes
ThanksLook here:
http://groups.google.de/groups?q=rowsize+sql+server&hl=de&lr=&selm=udj18LFHAHA.259%40cppssbbsa02.microsoft.com&rnum=3
select sum(CHARACTER_MAXIMUM_LENGTH)
from information_schema.columns
where TABLE_SCHEMA = 'dbo'
and TABLE_NAME = 'authors'
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Hassan" <fatima_ja@.hotmail.com> schrieb im Newsbeitrag
news:O0J4XGLRFHA.3544@.TK2MSFTNGP12.phx.gbl...
> Trying to calculate the row size and wanted to know if theres a bettter
> way
> to do it than just finding how much each datatype sizes and add
> accordingly.
> I have an int, datetime,Smalldatetime,money,bit ,smallint. Can you tell me
> the size of these datatypes
> Thanks
>
to do it than just finding how much each datatype sizes and add accordingly.
I have an int, datetime,Smalldatetime,money,bit ,smallint. Can you tell me
the size of these datatypes
ThanksLook here:
http://groups.google.de/groups?q=rowsize+sql+server&hl=de&lr=&selm=udj18LFHAHA.259%40cppssbbsa02.microsoft.com&rnum=3
select sum(CHARACTER_MAXIMUM_LENGTH)
from information_schema.columns
where TABLE_SCHEMA = 'dbo'
and TABLE_NAME = 'authors'
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Hassan" <fatima_ja@.hotmail.com> schrieb im Newsbeitrag
news:O0J4XGLRFHA.3544@.TK2MSFTNGP12.phx.gbl...
> Trying to calculate the row size and wanted to know if theres a bettter
> way
> to do it than just finding how much each datatype sizes and add
> accordingly.
> I have an int, datetime,Smalldatetime,money,bit ,smallint. Can you tell me
> the size of these datatypes
> Thanks
>
bytes used per datatype
Trying to calculate the row size and wanted to know if theres a bettter way
to do it than just finding how much each datatype sizes and add accordingly.
I have an int, datetime,Smalldatetime,money,bit ,smallint. Can you tell me
the size of these datatypes
ThanksLook here:
%40cppssbbsa02.microsoft.com&rnum=3" target="_blank">http://groups.google.de/groups?q=ro...soft.com&rnum=3
select sum(CHARACTER_MAXIMUM_LENGTH)
from information_schema.columns
where TABLE_SCHEMA = 'dbo'
and TABLE_NAME = 'authors'
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Hassan" <fatima_ja@.hotmail.com> schrieb im Newsbeitrag
news:O0J4XGLRFHA.3544@.TK2MSFTNGP12.phx.gbl...
> Trying to calculate the row size and wanted to know if theres a bettter
> way
> to do it than just finding how much each datatype sizes and add
> accordingly.
> I have an int, datetime,Smalldatetime,money,bit ,smallint. Can you tell me
> the size of these datatypes
> Thanks
>
to do it than just finding how much each datatype sizes and add accordingly.
I have an int, datetime,Smalldatetime,money,bit ,smallint. Can you tell me
the size of these datatypes
ThanksLook here:
%40cppssbbsa02.microsoft.com&rnum=3" target="_blank">http://groups.google.de/groups?q=ro...soft.com&rnum=3
select sum(CHARACTER_MAXIMUM_LENGTH)
from information_schema.columns
where TABLE_SCHEMA = 'dbo'
and TABLE_NAME = 'authors'
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Hassan" <fatima_ja@.hotmail.com> schrieb im Newsbeitrag
news:O0J4XGLRFHA.3544@.TK2MSFTNGP12.phx.gbl...
> Trying to calculate the row size and wanted to know if theres a bettter
> way
> to do it than just finding how much each datatype sizes and add
> accordingly.
> I have an int, datetime,Smalldatetime,money,bit ,smallint. Can you tell me
> the size of these datatypes
> Thanks
>
bytes used by datatypes
Where can i find how much bytes datatypes such as
int,datetime,money,bit,varchar consume ?
Thanks
Check BOL
Books Online...
Thanks,
Sree
[Please specify the version of Sql Server as we can save one thread and time
asking back if its 2000 or 2005]
"Hassan" wrote:
> Where can i find how much bytes datatypes such as
> int,datetime,money,bit,varchar consume ?
> Thanks
>
>
|||Check SQL Server Books Online
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
"Hassan" <Hassan@.hotmail.com> wrote in message
news:u9T5xLLQGHA.3192@.TK2MSFTNGP09.phx.gbl...
> Where can i find how much bytes datatypes such as
> int,datetime,money,bit,varchar consume ?
> Thanks
>
|||Look at Data Types in books online
Regards
Amish shah
Jack Vamvas wrote:
[vbcol=seagreen]
> Check SQL Server Books Online
> --
> Jack Vamvas
> ___________________________________
> Receive free SQL tips - www.ciquery.com/sqlserver.htm
> "Hassan" <Hassan@.hotmail.com> wrote in message
> news:u9T5xLLQGHA.3192@.TK2MSFTNGP09.phx.gbl...
int,datetime,money,bit,varchar consume ?
Thanks
Check BOL
Books Online...
Thanks,
Sree
[Please specify the version of Sql Server as we can save one thread and time
asking back if its 2000 or 2005]
"Hassan" wrote:
> Where can i find how much bytes datatypes such as
> int,datetime,money,bit,varchar consume ?
> Thanks
>
>
|||Check SQL Server Books Online
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
"Hassan" <Hassan@.hotmail.com> wrote in message
news:u9T5xLLQGHA.3192@.TK2MSFTNGP09.phx.gbl...
> Where can i find how much bytes datatypes such as
> int,datetime,money,bit,varchar consume ?
> Thanks
>
|||Look at Data Types in books online
Regards
Amish shah
Jack Vamvas wrote:
[vbcol=seagreen]
> Check SQL Server Books Online
> --
> Jack Vamvas
> ___________________________________
> Receive free SQL tips - www.ciquery.com/sqlserver.htm
> "Hassan" <Hassan@.hotmail.com> wrote in message
> news:u9T5xLLQGHA.3192@.TK2MSFTNGP09.phx.gbl...
bytes used by datatypes
Where can i find how much bytes datatypes such as
int,datetime,money,bit,varchar consume ?
ThanksCheck BOL
Books Online...
--
Thanks,
Sree
[Please specify the version of Sql Server as we can save one thread and
time
asking back if its 2000 or 2005]
"Hassan" wrote:
> Where can i find how much bytes datatypes such as
> int,datetime,money,bit,varchar consume ?
> Thanks
>
>|||Check SQL Server Books Online
--
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
"Hassan" <Hassan@.hotmail.com> wrote in message
news:u9T5xLLQGHA.3192@.TK2MSFTNGP09.phx.gbl...
> Where can i find how much bytes datatypes such as
> int,datetime,money,bit,varchar consume ?
> Thanks
>|||Look at Data Types in books online
Regards
Amish shah
Jack Vamvas wrote:
[vbcol=seagreen]
> Check SQL Server Books Online
> --
> Jack Vamvas
> ___________________________________
> Receive free SQL tips - www.ciquery.com/sqlserver.htm
> "Hassan" <Hassan@.hotmail.com> wrote in message
> news:u9T5xLLQGHA.3192@.TK2MSFTNGP09.phx.gbl...
int,datetime,money,bit,varchar consume ?
ThanksCheck BOL
Books Online...
--
Thanks,
Sree
[Please specify the version of Sql Server as we can save one thread and
time
asking back if its 2000 or 2005]
"Hassan" wrote:
> Where can i find how much bytes datatypes such as
> int,datetime,money,bit,varchar consume ?
> Thanks
>
>|||Check SQL Server Books Online
--
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
"Hassan" <Hassan@.hotmail.com> wrote in message
news:u9T5xLLQGHA.3192@.TK2MSFTNGP09.phx.gbl...
> Where can i find how much bytes datatypes such as
> int,datetime,money,bit,varchar consume ?
> Thanks
>|||Look at Data Types in books online
Regards
Amish shah
Jack Vamvas wrote:
[vbcol=seagreen]
> Check SQL Server Books Online
> --
> Jack Vamvas
> ___________________________________
> Receive free SQL tips - www.ciquery.com/sqlserver.htm
> "Hassan" <Hassan@.hotmail.com> wrote in message
> news:u9T5xLLQGHA.3192@.TK2MSFTNGP09.phx.gbl...
bytes used by datatypes
Where can i find how much bytes datatypes such as
int,datetime,money,bit,varchar consume ?
ThanksCheck BOL
Books Online...
--
Thanks,
Sree
[Please specify the version of Sql Server as we can save one thread and time
asking back if its 2000 or 2005]
"Hassan" wrote:
> Where can i find how much bytes datatypes such as
> int,datetime,money,bit,varchar consume ?
> Thanks
>
>|||Check SQL Server Books Online
--
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
"Hassan" <Hassan@.hotmail.com> wrote in message
news:u9T5xLLQGHA.3192@.TK2MSFTNGP09.phx.gbl...
> Where can i find how much bytes datatypes such as
> int,datetime,money,bit,varchar consume ?
> Thanks
>|||Look at Data Types in books online
Regards
Amish shah
Jack Vamvas wrote:
> Check SQL Server Books Online
> --
> Jack Vamvas
> ___________________________________
> Receive free SQL tips - www.ciquery.com/sqlserver.htm
> "Hassan" <Hassan@.hotmail.com> wrote in message
> news:u9T5xLLQGHA.3192@.TK2MSFTNGP09.phx.gbl...
> > Where can i find how much bytes datatypes such as
> > int,datetime,money,bit,varchar consume ?
> >
> > Thanks
> >
> >
int,datetime,money,bit,varchar consume ?
ThanksCheck BOL
Books Online...
--
Thanks,
Sree
[Please specify the version of Sql Server as we can save one thread and time
asking back if its 2000 or 2005]
"Hassan" wrote:
> Where can i find how much bytes datatypes such as
> int,datetime,money,bit,varchar consume ?
> Thanks
>
>|||Check SQL Server Books Online
--
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
"Hassan" <Hassan@.hotmail.com> wrote in message
news:u9T5xLLQGHA.3192@.TK2MSFTNGP09.phx.gbl...
> Where can i find how much bytes datatypes such as
> int,datetime,money,bit,varchar consume ?
> Thanks
>|||Look at Data Types in books online
Regards
Amish shah
Jack Vamvas wrote:
> Check SQL Server Books Online
> --
> Jack Vamvas
> ___________________________________
> Receive free SQL tips - www.ciquery.com/sqlserver.htm
> "Hassan" <Hassan@.hotmail.com> wrote in message
> news:u9T5xLLQGHA.3192@.TK2MSFTNGP09.phx.gbl...
> > Where can i find how much bytes datatypes such as
> > int,datetime,money,bit,varchar consume ?
> >
> > Thanks
> >
> >
bytes per i/o
In SQL server 2000 it recommends configuring 64 kb stripes when setting
up raid arrays. So technically each I/O is 64 kb. But when I am
monitoring the I/O writes and I/O write bytes the numbers don't add up
(meaning if I multiply the I/O's times the 64 KB it doesn't equal the
I/O write bytes) Is this most likely because each write doesn't fill up
the 64 kb stripe? If so why not? Probably something to do with data
pages being 8kb each and only filling up each page half way or
something?
I'm not sure what recommendation you've been reading (I normally leave
the striping configuration up to the RAID controller; the RAID vendor
usually has the best idea about best practices for their RAID sets).
However, when SQL Server performs a single read or a single write it is
always 1 single, whole, 8K page that is read or written. Even if only 1
byte on the page has changed, SQL Server has to write the entire 8K page
to disk (and also create one or more log records for the modification).
Similarly, when you read a single integer value, for example, from 1 row
in a table, even though that may only account for 1, 2, 4 or 8 bytes,
depending on the datatype, the entire page is read from disk and loaded
into memory (if it's not already cached). So each I/O ought to
represent 8K.
*mike hodgson*
http://sqlnerd.blogspot.com
tbone wrote:
>In SQL server 2000 it recommends configuring 64 kb stripes when setting
>up raid arrays. So technically each I/O is 64 kb. But when I am
>monitoring the I/O writes and I/O write bytes the numbers don't add up
>(meaning if I multiply the I/O's times the 64 KB it doesn't equal the
>I/O write bytes) Is this most likely because each write doesn't fill up
>the 64 kb stripe? If so why not? Probably something to do with data
>pages being 8kb each and only filling up each page half way or
>something?
>
>
|||SQL Server does more than just 8K I/Os. For transaction log writes, a write
could be as small as a sector (i.e. 256 bytes) or sa large as 64K. For
checkpoints, a write may be much larger than 8K (from 8K to 64K). For log
backups, you may yet see a different I/O size. For bulk inserts, a write can
be up to 128K. In addition, DBCC DBREINDEX and restore are often not 8K
writes.
On most systems, because of a multiplicity of activities, you can hardly
expect to see a single I/O size in terms of write bytes/sec, and the counter
values taken at the drive can fluctuate wildly. If you want to observe the
block sizes of SQL Server I/Os, you would need to carefully control the
environment and try to make sure only one type of SQL Server I/Os is taking
place, ideally on an isolated drive.
Linchi
"Mike Hodgson" wrote:
> I'm not sure what recommendation you've been reading (I normally leave
> the striping configuration up to the RAID controller; the RAID vendor
> usually has the best idea about best practices for their RAID sets).
> However, when SQL Server performs a single read or a single write it is
> always 1 single, whole, 8K page that is read or written. Even if only 1
> byte on the page has changed, SQL Server has to write the entire 8K page
> to disk (and also create one or more log records for the modification).
> Similarly, when you read a single integer value, for example, from 1 row
> in a table, even though that may only account for 1, 2, 4 or 8 bytes,
> depending on the datatype, the entire page is read from disk and loaded
> into memory (if it's not already cached). So each I/O ought to
> represent 8K.
> --
> *mike hodgson*
> http://sqlnerd.blogspot.com
>
> tbone wrote:
>
up raid arrays. So technically each I/O is 64 kb. But when I am
monitoring the I/O writes and I/O write bytes the numbers don't add up
(meaning if I multiply the I/O's times the 64 KB it doesn't equal the
I/O write bytes) Is this most likely because each write doesn't fill up
the 64 kb stripe? If so why not? Probably something to do with data
pages being 8kb each and only filling up each page half way or
something?
I'm not sure what recommendation you've been reading (I normally leave
the striping configuration up to the RAID controller; the RAID vendor
usually has the best idea about best practices for their RAID sets).
However, when SQL Server performs a single read or a single write it is
always 1 single, whole, 8K page that is read or written. Even if only 1
byte on the page has changed, SQL Server has to write the entire 8K page
to disk (and also create one or more log records for the modification).
Similarly, when you read a single integer value, for example, from 1 row
in a table, even though that may only account for 1, 2, 4 or 8 bytes,
depending on the datatype, the entire page is read from disk and loaded
into memory (if it's not already cached). So each I/O ought to
represent 8K.
*mike hodgson*
http://sqlnerd.blogspot.com
tbone wrote:
>In SQL server 2000 it recommends configuring 64 kb stripes when setting
>up raid arrays. So technically each I/O is 64 kb. But when I am
>monitoring the I/O writes and I/O write bytes the numbers don't add up
>(meaning if I multiply the I/O's times the 64 KB it doesn't equal the
>I/O write bytes) Is this most likely because each write doesn't fill up
>the 64 kb stripe? If so why not? Probably something to do with data
>pages being 8kb each and only filling up each page half way or
>something?
>
>
|||SQL Server does more than just 8K I/Os. For transaction log writes, a write
could be as small as a sector (i.e. 256 bytes) or sa large as 64K. For
checkpoints, a write may be much larger than 8K (from 8K to 64K). For log
backups, you may yet see a different I/O size. For bulk inserts, a write can
be up to 128K. In addition, DBCC DBREINDEX and restore are often not 8K
writes.
On most systems, because of a multiplicity of activities, you can hardly
expect to see a single I/O size in terms of write bytes/sec, and the counter
values taken at the drive can fluctuate wildly. If you want to observe the
block sizes of SQL Server I/Os, you would need to carefully control the
environment and try to make sure only one type of SQL Server I/Os is taking
place, ideally on an isolated drive.
Linchi
"Mike Hodgson" wrote:
> I'm not sure what recommendation you've been reading (I normally leave
> the striping configuration up to the RAID controller; the RAID vendor
> usually has the best idea about best practices for their RAID sets).
> However, when SQL Server performs a single read or a single write it is
> always 1 single, whole, 8K page that is read or written. Even if only 1
> byte on the page has changed, SQL Server has to write the entire 8K page
> to disk (and also create one or more log records for the modification).
> Similarly, when you read a single integer value, for example, from 1 row
> in a table, even though that may only account for 1, 2, 4 or 8 bytes,
> depending on the datatype, the entire page is read from disk and loaded
> into memory (if it's not already cached). So each I/O ought to
> represent 8K.
> --
> *mike hodgson*
> http://sqlnerd.blogspot.com
>
> tbone wrote:
>
bytes per i/o
In SQL server 2000 it recommends configuring 64 kb stripes when setting
up raid arrays. So technically each I/O is 64 kb. But when I am
monitoring the I/O writes and I/O write bytes the numbers don't add up
(meaning if I multiply the I/O's times the 64 KB it doesn't equal the
I/O write bytes) Is this most likely because each write doesn't fill up
the 64 kb stripe? If so why not? Probably something to do with data
pages being 8kb each and only filling up each page half way or
something?This is a multi-part message in MIME format.
--020609000407000503080500
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
I'm not sure what recommendation you've been reading (I normally leave
the striping configuration up to the RAID controller; the RAID vendor
usually has the best idea about best practices for their RAID sets).
However, when SQL Server performs a single read or a single write it is
always 1 single, whole, 8K page that is read or written. Even if only 1
byte on the page has changed, SQL Server has to write the entire 8K page
to disk (and also create one or more log records for the modification).
Similarly, when you read a single integer value, for example, from 1 row
in a table, even though that may only account for 1, 2, 4 or 8 bytes,
depending on the datatype, the entire page is read from disk and loaded
into memory (if it's not already cached). So each I/O ought to
represent 8K.
--
*mike hodgson*
http://sqlnerd.blogspot.com
tbone wrote:
>In SQL server 2000 it recommends configuring 64 kb stripes when setting
>up raid arrays. So technically each I/O is 64 kb. But when I am
>monitoring the I/O writes and I/O write bytes the numbers don't add up
>(meaning if I multiply the I/O's times the 64 KB it doesn't equal the
>I/O write bytes) Is this most likely because each write doesn't fill up
>the 64 kb stripe? If so why not? Probably something to do with data
>pages being 8kb each and only filling up each page half way or
>something?
>
>
--020609000407000503080500
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>I'm not sure what recommendation you've been reading (I normally
leave the striping configuration up to the RAID controller; the RAID
vendor usually has the best idea about best practices for their RAID
sets).<br>
<br>
However, when SQL Server performs a single read or a single write it is
always 1 single, whole, 8K page that is read or written. Even if only
1 byte on the page has changed, SQL Server has to write the entire 8K
page to disk (and also create one or more log records for the
modification). Similarly, when you read a single integer value, for
example, from 1 row in a table, even though that may only account for
1, 2, 4 or 8 bytes, depending on the datatype, the entire page is read
from disk and loaded into memory (if it's not already cached). So each
I/O ought to represent 8K.</tt><br>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font></span> <b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"><br>
<font face="Tahoma" size="2"><a href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a></font></span>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a></font></span>
</p>
</div>
<br>
<br>
tbone wrote:
<blockquote
cite="mid1141683797.703495.277790@.i39g2000cwa.googlegroups.com"
type="cite">
<pre wrap="">In SQL server 2000 it recommends configuring 64 kb stripes when setting
up raid arrays. So technically each I/O is 64 kb. But when I am
monitoring the I/O writes and I/O write bytes the numbers don't add up
(meaning if I multiply the I/O's times the 64 KB it doesn't equal the
I/O write bytes) Is this most likely because each write doesn't fill up
the 64 kb stripe? If so why not? Probably something to do with data
pages being 8kb each and only filling up each page half way or
something?
</pre>
</blockquote>
</body>
</html>
--020609000407000503080500--|||SQL Server does more than just 8K I/Os. For transaction log writes, a write
could be as small as a sector (i.e. 256 bytes) or sa large as 64K. For
checkpoints, a write may be much larger than 8K (from 8K to 64K). For log
backups, you may yet see a different I/O size. For bulk inserts, a write can
be up to 128K. In addition, DBCC DBREINDEX and restore are often not 8K
writes.
On most systems, because of a multiplicity of activities, you can hardly
expect to see a single I/O size in terms of write bytes/sec, and the counter
values taken at the drive can fluctuate wildly. If you want to observe the
block sizes of SQL Server I/Os, you would need to carefully control the
environment and try to make sure only one type of SQL Server I/Os is taking
place, ideally on an isolated drive.
Linchi
"Mike Hodgson" wrote:
> I'm not sure what recommendation you've been reading (I normally leave
> the striping configuration up to the RAID controller; the RAID vendor
> usually has the best idea about best practices for their RAID sets).
> However, when SQL Server performs a single read or a single write it is
> always 1 single, whole, 8K page that is read or written. Even if only 1
> byte on the page has changed, SQL Server has to write the entire 8K page
> to disk (and also create one or more log records for the modification).
> Similarly, when you read a single integer value, for example, from 1 row
> in a table, even though that may only account for 1, 2, 4 or 8 bytes,
> depending on the datatype, the entire page is read from disk and loaded
> into memory (if it's not already cached). So each I/O ought to
> represent 8K.
> --
> *mike hodgson*
> http://sqlnerd.blogspot.com
>
> tbone wrote:
> >In SQL server 2000 it recommends configuring 64 kb stripes when setting
> >up raid arrays. So technically each I/O is 64 kb. But when I am
> >monitoring the I/O writes and I/O write bytes the numbers don't add up
> >(meaning if I multiply the I/O's times the 64 KB it doesn't equal the
> >I/O write bytes) Is this most likely because each write doesn't fill up
> >the 64 kb stripe? If so why not? Probably something to do with data
> >pages being 8kb each and only filling up each page half way or
> >something?
> >
> >
> >
>
up raid arrays. So technically each I/O is 64 kb. But when I am
monitoring the I/O writes and I/O write bytes the numbers don't add up
(meaning if I multiply the I/O's times the 64 KB it doesn't equal the
I/O write bytes) Is this most likely because each write doesn't fill up
the 64 kb stripe? If so why not? Probably something to do with data
pages being 8kb each and only filling up each page half way or
something?This is a multi-part message in MIME format.
--020609000407000503080500
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
I'm not sure what recommendation you've been reading (I normally leave
the striping configuration up to the RAID controller; the RAID vendor
usually has the best idea about best practices for their RAID sets).
However, when SQL Server performs a single read or a single write it is
always 1 single, whole, 8K page that is read or written. Even if only 1
byte on the page has changed, SQL Server has to write the entire 8K page
to disk (and also create one or more log records for the modification).
Similarly, when you read a single integer value, for example, from 1 row
in a table, even though that may only account for 1, 2, 4 or 8 bytes,
depending on the datatype, the entire page is read from disk and loaded
into memory (if it's not already cached). So each I/O ought to
represent 8K.
--
*mike hodgson*
http://sqlnerd.blogspot.com
tbone wrote:
>In SQL server 2000 it recommends configuring 64 kb stripes when setting
>up raid arrays. So technically each I/O is 64 kb. But when I am
>monitoring the I/O writes and I/O write bytes the numbers don't add up
>(meaning if I multiply the I/O's times the 64 KB it doesn't equal the
>I/O write bytes) Is this most likely because each write doesn't fill up
>the 64 kb stripe? If so why not? Probably something to do with data
>pages being 8kb each and only filling up each page half way or
>something?
>
>
--020609000407000503080500
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>I'm not sure what recommendation you've been reading (I normally
leave the striping configuration up to the RAID controller; the RAID
vendor usually has the best idea about best practices for their RAID
sets).<br>
<br>
However, when SQL Server performs a single read or a single write it is
always 1 single, whole, 8K page that is read or written. Even if only
1 byte on the page has changed, SQL Server has to write the entire 8K
page to disk (and also create one or more log records for the
modification). Similarly, when you read a single integer value, for
example, from 1 row in a table, even though that may only account for
1, 2, 4 or 8 bytes, depending on the datatype, the entire page is read
from disk and loaded into memory (if it's not already cached). So each
I/O ought to represent 8K.</tt><br>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font></span> <b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"><br>
<font face="Tahoma" size="2"><a href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a></font></span>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a></font></span>
</p>
</div>
<br>
<br>
tbone wrote:
<blockquote
cite="mid1141683797.703495.277790@.i39g2000cwa.googlegroups.com"
type="cite">
<pre wrap="">In SQL server 2000 it recommends configuring 64 kb stripes when setting
up raid arrays. So technically each I/O is 64 kb. But when I am
monitoring the I/O writes and I/O write bytes the numbers don't add up
(meaning if I multiply the I/O's times the 64 KB it doesn't equal the
I/O write bytes) Is this most likely because each write doesn't fill up
the 64 kb stripe? If so why not? Probably something to do with data
pages being 8kb each and only filling up each page half way or
something?
</pre>
</blockquote>
</body>
</html>
--020609000407000503080500--|||SQL Server does more than just 8K I/Os. For transaction log writes, a write
could be as small as a sector (i.e. 256 bytes) or sa large as 64K. For
checkpoints, a write may be much larger than 8K (from 8K to 64K). For log
backups, you may yet see a different I/O size. For bulk inserts, a write can
be up to 128K. In addition, DBCC DBREINDEX and restore are often not 8K
writes.
On most systems, because of a multiplicity of activities, you can hardly
expect to see a single I/O size in terms of write bytes/sec, and the counter
values taken at the drive can fluctuate wildly. If you want to observe the
block sizes of SQL Server I/Os, you would need to carefully control the
environment and try to make sure only one type of SQL Server I/Os is taking
place, ideally on an isolated drive.
Linchi
"Mike Hodgson" wrote:
> I'm not sure what recommendation you've been reading (I normally leave
> the striping configuration up to the RAID controller; the RAID vendor
> usually has the best idea about best practices for their RAID sets).
> However, when SQL Server performs a single read or a single write it is
> always 1 single, whole, 8K page that is read or written. Even if only 1
> byte on the page has changed, SQL Server has to write the entire 8K page
> to disk (and also create one or more log records for the modification).
> Similarly, when you read a single integer value, for example, from 1 row
> in a table, even though that may only account for 1, 2, 4 or 8 bytes,
> depending on the datatype, the entire page is read from disk and loaded
> into memory (if it's not already cached). So each I/O ought to
> represent 8K.
> --
> *mike hodgson*
> http://sqlnerd.blogspot.com
>
> tbone wrote:
> >In SQL server 2000 it recommends configuring 64 kb stripes when setting
> >up raid arrays. So technically each I/O is 64 kb. But when I am
> >monitoring the I/O writes and I/O write bytes the numbers don't add up
> >(meaning if I multiply the I/O's times the 64 KB it doesn't equal the
> >I/O write bytes) Is this most likely because each write doesn't fill up
> >the 64 kb stripe? If so why not? Probably something to do with data
> >pages being 8kb each and only filling up each page half way or
> >something?
> >
> >
> >
>
bytes per i/o
In SQL server 2000 it recommends configuring 64 kb stripes when setting
up raid arrays. So technically each I/O is 64 kb. But when I am
monitoring the I/O writes and I/O write bytes the numbers don't add up
(meaning if I multiply the I/O's times the 64 KB it doesn't equal the
I/O write bytes) Is this most likely because each write doesn't fill up
the 64 kb stripe? If so why not? Probably something to do with data
pages being 8kb each and only filling up each page half way or
something?I'm not sure what recommendation you've been reading (I normally leave
the striping configuration up to the RAID controller; the RAID vendor
usually has the best idea about best practices for their RAID sets).
However, when SQL Server performs a single read or a single write it is
always 1 single, whole, 8K page that is read or written. Even if only 1
byte on the page has changed, SQL Server has to write the entire 8K page
to disk (and also create one or more log records for the modification).
Similarly, when you read a single integer value, for example, from 1 row
in a table, even though that may only account for 1, 2, 4 or 8 bytes,
depending on the datatype, the entire page is read from disk and loaded
into memory (if it's not already cached). So each I/O ought to
represent 8K.
*mike hodgson*
http://sqlnerd.blogspot.com
tbone wrote:
>In SQL server 2000 it recommends configuring 64 kb stripes when setting
>up raid arrays. So technically each I/O is 64 kb. But when I am
>monitoring the I/O writes and I/O write bytes the numbers don't add up
>(meaning if I multiply the I/O's times the 64 KB it doesn't equal the
>I/O write bytes) Is this most likely because each write doesn't fill up
>the 64 kb stripe? If so why not? Probably something to do with data
>pages being 8kb each and only filling up each page half way or
>something?
>
>|||SQL Server does more than just 8K I/Os. For transaction log writes, a write
could be as small as a sector (i.e. 256 bytes) or sa large as 64K. For
checkpoints, a write may be much larger than 8K (from 8K to 64K). For log
backups, you may yet see a different I/O size. For bulk inserts, a write can
be up to 128K. In addition, DBCC DBREINDEX and restore are often not 8K
writes.
On most systems, because of a multiplicity of activities, you can hardly
expect to see a single I/O size in terms of write bytes/sec, and the counter
values taken at the drive can fluctuate wildly. If you want to observe the
block sizes of SQL Server I/Os, you would need to carefully control the
environment and try to make sure only one type of SQL Server I/Os is taking
place, ideally on an isolated drive.
Linchi
"Mike Hodgson" wrote:
> I'm not sure what recommendation you've been reading (I normally leave
> the striping configuration up to the RAID controller; the RAID vendor
> usually has the best idea about best practices for their RAID sets).
> However, when SQL Server performs a single read or a single write it is
> always 1 single, whole, 8K page that is read or written. Even if only 1
> byte on the page has changed, SQL Server has to write the entire 8K page
> to disk (and also create one or more log records for the modification).
> Similarly, when you read a single integer value, for example, from 1 row
> in a table, even though that may only account for 1, 2, 4 or 8 bytes,
> depending on the datatype, the entire page is read from disk and loaded
> into memory (if it's not already cached). So each I/O ought to
> represent 8K.
> --
> *mike hodgson*
> http://sqlnerd.blogspot.com
>
> tbone wrote:
>
>
up raid arrays. So technically each I/O is 64 kb. But when I am
monitoring the I/O writes and I/O write bytes the numbers don't add up
(meaning if I multiply the I/O's times the 64 KB it doesn't equal the
I/O write bytes) Is this most likely because each write doesn't fill up
the 64 kb stripe? If so why not? Probably something to do with data
pages being 8kb each and only filling up each page half way or
something?I'm not sure what recommendation you've been reading (I normally leave
the striping configuration up to the RAID controller; the RAID vendor
usually has the best idea about best practices for their RAID sets).
However, when SQL Server performs a single read or a single write it is
always 1 single, whole, 8K page that is read or written. Even if only 1
byte on the page has changed, SQL Server has to write the entire 8K page
to disk (and also create one or more log records for the modification).
Similarly, when you read a single integer value, for example, from 1 row
in a table, even though that may only account for 1, 2, 4 or 8 bytes,
depending on the datatype, the entire page is read from disk and loaded
into memory (if it's not already cached). So each I/O ought to
represent 8K.
*mike hodgson*
http://sqlnerd.blogspot.com
tbone wrote:
>In SQL server 2000 it recommends configuring 64 kb stripes when setting
>up raid arrays. So technically each I/O is 64 kb. But when I am
>monitoring the I/O writes and I/O write bytes the numbers don't add up
>(meaning if I multiply the I/O's times the 64 KB it doesn't equal the
>I/O write bytes) Is this most likely because each write doesn't fill up
>the 64 kb stripe? If so why not? Probably something to do with data
>pages being 8kb each and only filling up each page half way or
>something?
>
>|||SQL Server does more than just 8K I/Os. For transaction log writes, a write
could be as small as a sector (i.e. 256 bytes) or sa large as 64K. For
checkpoints, a write may be much larger than 8K (from 8K to 64K). For log
backups, you may yet see a different I/O size. For bulk inserts, a write can
be up to 128K. In addition, DBCC DBREINDEX and restore are often not 8K
writes.
On most systems, because of a multiplicity of activities, you can hardly
expect to see a single I/O size in terms of write bytes/sec, and the counter
values taken at the drive can fluctuate wildly. If you want to observe the
block sizes of SQL Server I/Os, you would need to carefully control the
environment and try to make sure only one type of SQL Server I/Os is taking
place, ideally on an isolated drive.
Linchi
"Mike Hodgson" wrote:
> I'm not sure what recommendation you've been reading (I normally leave
> the striping configuration up to the RAID controller; the RAID vendor
> usually has the best idea about best practices for their RAID sets).
> However, when SQL Server performs a single read or a single write it is
> always 1 single, whole, 8K page that is read or written. Even if only 1
> byte on the page has changed, SQL Server has to write the entire 8K page
> to disk (and also create one or more log records for the modification).
> Similarly, when you read a single integer value, for example, from 1 row
> in a table, even though that may only account for 1, 2, 4 or 8 bytes,
> depending on the datatype, the entire page is read from disk and loaded
> into memory (if it's not already cached). So each I/O ought to
> represent 8K.
> --
> *mike hodgson*
> http://sqlnerd.blogspot.com
>
> tbone wrote:
>
>
Subscribe to:
Posts (Atom)