Is there any method to find out the size of an image datatype currently in the database. For example I have 2 tables that contain the same image, but the archive table is not displaying the same as the main. I am thinking the image file was corrupted somehow but don't know how to check this out.
Any ideas?DataLength() (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_da-db_4ep4.asp)
-PatP
Showing posts with label size. Show all posts
Showing posts with label size. Show all posts
Thursday, March 29, 2012
Determine the database objects' sizes?
I have a database whose size is over 50GB. What is the easy way to determine
the size of each database objects (tables)? I want to find out the ones that
take most of the space. Thanks a lot,
FLThis uses an undocumented and unsupported system procedure.
EXEC sp_MSForEachTable 'EXEC sp_spaceused ''?'''
Another method would be to do this:
SELECT 'EXEC sp_spaceused '+TABLE_NAME+' FROM INFORMATION_SCHEMA.TABLES
Run that in Query Analyzer, using Results to TEXT, copy the output to the
top pane and run that...
http://www.aspfaq.com/
(Reverse address to reply.)
"FLX" <nospam@.hotmail.com> wrote in message
news:OicCviu1EHA.1192@.tk2msftngp13.phx.gbl...
> I have a database whose size is over 50GB. What is the easy way to
determine
> the size of each database objects (tables)? I want to find out the ones
that
> take most of the space. Thanks a lot,
> FL
>
>|||FLX
1)sp_spaceused in the BOL
--
2) Vays has written a great SP to show big tables
CREATE PROC sp_show_huge_tables
(
@.top int = NULL,
@.include_system_tables bit = 0
)
AS
/*
To see the top three biggest user or system tables in your database:
EXEC sp_show_huge_tables 3, 1
****************************************
************************************
*********************/
BEGIN
IF @.top > 0
SET ROWCOUNT @.top
SELECT [Table Name], (SELECT rows FROM sysindexes s WHERE s.indid < 2 AN
D
s.id = OBJECT_ID(a.[Table Name])) AS [Row count], [Total space u
sed (MB)]
FROM
(
SELECT QUOTENAME(USER_NAME(o.uid)) + '.' + QUOTENAME(OBJECT_NAME(i.id)) AS
[Table Name],
CONVERT(numeric(15,2),(((CONVERT(numeric
(15,2),SUM(i.reserved)) * (SELECT
low FROM master.dbo.spt_values (NOLOCK) WHERE number = 1 AND type = 'E')) /
1024.)/1024.)) AS [Total space used (MB)]
FROM sysindexes i (NOLOCK)
INNER JOIN
sysobjects o (NOLOCK)
ON
i.id = o.id AND
((@.include_system_tables = 1 AND o.type IN ('U', 'S')) OR o.type = 'U')
AND
((@.include_system_tables = 1)OR (OBJECTPROPERTY(i.id, 'IsMSShipped') = 0))
WHERE indid IN (0, 1, 255)
GROUP BY QUOTENAME(USER_NAME(o.uid)) + '.' + QUOTENAME(OBJECT_NAME(i.id))
) as a
ORDER BY [Total space used (MB)] DESC
SET ROWCOUNT 0
END
GO
--
3)SELECT TOP 5
CAST(sysobjects.[name] AS VARCHAR) AS [TABLE_NAME],
sysindexes.[rows] AS [NO_OF_ROWS],
sysindexes.reserved AS [RESERVED_SPACE],
sysindexes.used AS [USED_SPACE]
FROM sysobjects
INNER JOIN sysindexes
ON sysobjects.[id] = sysindexes.[id]
WHERE sysindexes.indid < 2
AND sysobjects.type = 'U'
ORDER BY used DESC
"FLX" <nospam@.hotmail.com> wrote in message
news:OicCviu1EHA.1192@.tk2msftngp13.phx.gbl...
> I have a database whose size is over 50GB. What is the easy way to
determine
> the size of each database objects (tables)? I want to find out the ones
that
> take most of the space. Thanks a lot,
> FL
>
>|||And for both sets of responses, remember to update statistics first
(sysindexes won't necessarily be up to date).
http://www.aspfaq.com/
(Reverse address to reply.)
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:#MXp$pu1EHA.1564@.TK2MSFTNGP09.phx.gbl...
> FLX
> 1)sp_spaceused in the BOL
> --
> 2) Vays has written a great SP to show big tables
> CREATE PROC sp_show_huge_tables
> (
> @.top int = NULL,
> @.include_system_tables bit = 0
> )
> AS
> /*
> To see the top three biggest user or system tables in your database:
> EXEC sp_show_huge_tables 3, 1
>
****************************************
************************************[vbc
ol=seagreen]
> *********************/
> BEGIN
> IF @.top > 0
> SET ROWCOUNT @.top
> SELECT [Table Name], (SELECT rows FROM sysindexes s WHERE s.indid < 2
AND
> s.id = OBJECT_ID(a.[Table Name])) AS [Row count], [Total space
used (MB)]
> FROM
> (
> SELECT QUOTENAME(USER_NAME(o.uid)) + '.' + QUOTENAME(OBJECT_NAME(i.id))[/vbcol]
AS
> [Table Name],
> CONVERT(numeric(15,2),(((CONVERT(numeric
(15,2),SUM(i.reserved)) *
(SELECT
> low FROM master.dbo.spt_values (NOLOCK) WHERE number = 1 AND type = 'E'))
/
> 1024.)/1024.)) AS [Total space used (MB)]
> FROM sysindexes i (NOLOCK)
> INNER JOIN
> sysobjects o (NOLOCK)
> ON
> i.id = o.id AND
> ((@.include_system_tables = 1 AND o.type IN ('U', 'S')) OR o.type = 'U')
> AND
> ((@.include_system_tables = 1)OR (OBJECTPROPERTY(i.id, 'IsMSShipped') =
0))
> WHERE indid IN (0, 1, 255)
> GROUP BY QUOTENAME(USER_NAME(o.uid)) + '.' + QUOTENAME(OBJECT_NAME(i.id))
> ) as a
> ORDER BY [Total space used (MB)] DESC
>
> SET ROWCOUNT 0
> END
>
> GO
> --
> 3)SELECT TOP 5
> CAST(sysobjects.[name] AS VARCHAR) AS [TABLE_NAME],
> sysindexes.[rows] AS [NO_OF_ROWS],
> sysindexes.reserved AS [RESERVED_SPACE],
> sysindexes.used AS [USED_SPACE]
> FROM sysobjects
> INNER JOIN sysindexes
> ON sysobjects.[id] = sysindexes.[id]
> WHERE sysindexes.indid < 2
> AND sysobjects.type = 'U'
> ORDER BY used DESC
> "FLX" <nospam@.hotmail.com> wrote in message
> news:OicCviu1EHA.1192@.tk2msftngp13.phx.gbl...
> determine
> that
>|||"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uwZI6nu1EHA.3908@.TK2MSFTNGP12.phx.gbl...
> This uses an undocumented and unsupported system procedure.
> EXEC sp_MSForEachTable 'EXEC sp_spaceused ''?'''
> Another method would be to do this:
> SELECT 'EXEC sp_spaceused '+TABLE_NAME+' FROM INFORMATION_SCHEMA.TABLES
> Run that in Query Analyzer, using Results to TEXT, copy the output to the
> top pane and run that...
>
Cool.
FYI, I found there is a typo (extra single quote) in the query. It should
be:
SELECT 'EXEC sp_spaceused '+ TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
FL
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "FLX" <nospam@.hotmail.com> wrote in message
> news:OicCviu1EHA.1192@.tk2msftngp13.phx.gbl...
> determine
> that
>
the size of each database objects (tables)? I want to find out the ones that
take most of the space. Thanks a lot,
FLThis uses an undocumented and unsupported system procedure.
EXEC sp_MSForEachTable 'EXEC sp_spaceused ''?'''
Another method would be to do this:
SELECT 'EXEC sp_spaceused '+TABLE_NAME+' FROM INFORMATION_SCHEMA.TABLES
Run that in Query Analyzer, using Results to TEXT, copy the output to the
top pane and run that...
http://www.aspfaq.com/
(Reverse address to reply.)
"FLX" <nospam@.hotmail.com> wrote in message
news:OicCviu1EHA.1192@.tk2msftngp13.phx.gbl...
> I have a database whose size is over 50GB. What is the easy way to
determine
> the size of each database objects (tables)? I want to find out the ones
that
> take most of the space. Thanks a lot,
> FL
>
>|||FLX
1)sp_spaceused in the BOL
--
2) Vays has written a great SP to show big tables
CREATE PROC sp_show_huge_tables
(
@.top int = NULL,
@.include_system_tables bit = 0
)
AS
/*
To see the top three biggest user or system tables in your database:
EXEC sp_show_huge_tables 3, 1
****************************************
************************************
*********************/
BEGIN
IF @.top > 0
SET ROWCOUNT @.top
SELECT [Table Name], (SELECT rows FROM sysindexes s WHERE s.indid < 2 AN
D
s.id = OBJECT_ID(a.[Table Name])) AS [Row count], [Total space u
sed (MB)]
FROM
(
SELECT QUOTENAME(USER_NAME(o.uid)) + '.' + QUOTENAME(OBJECT_NAME(i.id)) AS
[Table Name],
CONVERT(numeric(15,2),(((CONVERT(numeric
(15,2),SUM(i.reserved)) * (SELECT
low FROM master.dbo.spt_values (NOLOCK) WHERE number = 1 AND type = 'E')) /
1024.)/1024.)) AS [Total space used (MB)]
FROM sysindexes i (NOLOCK)
INNER JOIN
sysobjects o (NOLOCK)
ON
i.id = o.id AND
((@.include_system_tables = 1 AND o.type IN ('U', 'S')) OR o.type = 'U')
AND
((@.include_system_tables = 1)OR (OBJECTPROPERTY(i.id, 'IsMSShipped') = 0))
WHERE indid IN (0, 1, 255)
GROUP BY QUOTENAME(USER_NAME(o.uid)) + '.' + QUOTENAME(OBJECT_NAME(i.id))
) as a
ORDER BY [Total space used (MB)] DESC
SET ROWCOUNT 0
END
GO
--
3)SELECT TOP 5
CAST(sysobjects.[name] AS VARCHAR) AS [TABLE_NAME],
sysindexes.[rows] AS [NO_OF_ROWS],
sysindexes.reserved AS [RESERVED_SPACE],
sysindexes.used AS [USED_SPACE]
FROM sysobjects
INNER JOIN sysindexes
ON sysobjects.[id] = sysindexes.[id]
WHERE sysindexes.indid < 2
AND sysobjects.type = 'U'
ORDER BY used DESC
"FLX" <nospam@.hotmail.com> wrote in message
news:OicCviu1EHA.1192@.tk2msftngp13.phx.gbl...
> I have a database whose size is over 50GB. What is the easy way to
determine
> the size of each database objects (tables)? I want to find out the ones
that
> take most of the space. Thanks a lot,
> FL
>
>|||And for both sets of responses, remember to update statistics first
(sysindexes won't necessarily be up to date).
http://www.aspfaq.com/
(Reverse address to reply.)
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:#MXp$pu1EHA.1564@.TK2MSFTNGP09.phx.gbl...
> FLX
> 1)sp_spaceused in the BOL
> --
> 2) Vays has written a great SP to show big tables
> CREATE PROC sp_show_huge_tables
> (
> @.top int = NULL,
> @.include_system_tables bit = 0
> )
> AS
> /*
> To see the top three biggest user or system tables in your database:
> EXEC sp_show_huge_tables 3, 1
>
****************************************
************************************[vbc
ol=seagreen]
> *********************/
> BEGIN
> IF @.top > 0
> SET ROWCOUNT @.top
> SELECT [Table Name], (SELECT rows FROM sysindexes s WHERE s.indid < 2
AND
> s.id = OBJECT_ID(a.[Table Name])) AS [Row count], [Total space
used (MB)]
> FROM
> (
> SELECT QUOTENAME(USER_NAME(o.uid)) + '.' + QUOTENAME(OBJECT_NAME(i.id))[/vbcol]
AS
> [Table Name],
> CONVERT(numeric(15,2),(((CONVERT(numeric
(15,2),SUM(i.reserved)) *
(SELECT
> low FROM master.dbo.spt_values (NOLOCK) WHERE number = 1 AND type = 'E'))
/
> 1024.)/1024.)) AS [Total space used (MB)]
> FROM sysindexes i (NOLOCK)
> INNER JOIN
> sysobjects o (NOLOCK)
> ON
> i.id = o.id AND
> ((@.include_system_tables = 1 AND o.type IN ('U', 'S')) OR o.type = 'U')
> AND
> ((@.include_system_tables = 1)OR (OBJECTPROPERTY(i.id, 'IsMSShipped') =
0))
> WHERE indid IN (0, 1, 255)
> GROUP BY QUOTENAME(USER_NAME(o.uid)) + '.' + QUOTENAME(OBJECT_NAME(i.id))
> ) as a
> ORDER BY [Total space used (MB)] DESC
>
> SET ROWCOUNT 0
> END
>
> GO
> --
> 3)SELECT TOP 5
> CAST(sysobjects.[name] AS VARCHAR) AS [TABLE_NAME],
> sysindexes.[rows] AS [NO_OF_ROWS],
> sysindexes.reserved AS [RESERVED_SPACE],
> sysindexes.used AS [USED_SPACE]
> FROM sysobjects
> INNER JOIN sysindexes
> ON sysobjects.[id] = sysindexes.[id]
> WHERE sysindexes.indid < 2
> AND sysobjects.type = 'U'
> ORDER BY used DESC
> "FLX" <nospam@.hotmail.com> wrote in message
> news:OicCviu1EHA.1192@.tk2msftngp13.phx.gbl...
> determine
> that
>|||"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uwZI6nu1EHA.3908@.TK2MSFTNGP12.phx.gbl...
> This uses an undocumented and unsupported system procedure.
> EXEC sp_MSForEachTable 'EXEC sp_spaceused ''?'''
> Another method would be to do this:
> SELECT 'EXEC sp_spaceused '+TABLE_NAME+' FROM INFORMATION_SCHEMA.TABLES
> Run that in Query Analyzer, using Results to TEXT, copy the output to the
> top pane and run that...
>
Cool.
FYI, I found there is a typo (extra single quote) in the query. It should
be:
SELECT 'EXEC sp_spaceused '+ TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
FL
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "FLX" <nospam@.hotmail.com> wrote in message
> news:OicCviu1EHA.1192@.tk2msftngp13.phx.gbl...
> determine
> that
>
Determine the database objects' sizes?
I have a database whose size is over 50GB. What is the easy way to determine
the size of each database objects (tables)? I want to find out the ones that
take most of the space. Thanks a lot,
FLThis uses an undocumented and unsupported system procedure.
EXEC sp_MSForEachTable 'EXEC sp_spaceused ''?'''
Another method would be to do this:
SELECT 'EXEC sp_spaceused '+TABLE_NAME+' FROM INFORMATION_SCHEMA.TABLES
Run that in Query Analyzer, using Results to TEXT, copy the output to the
top pane and run that...
--
http://www.aspfaq.com/
(Reverse address to reply.)
"FLX" <nospam@.hotmail.com> wrote in message
news:OicCviu1EHA.1192@.tk2msftngp13.phx.gbl...
> I have a database whose size is over 50GB. What is the easy way to
determine
> the size of each database objects (tables)? I want to find out the ones
that
> take most of the space. Thanks a lot,
> FL
>
>|||FLX
1)sp_spaceused in the BOL
--
2) Vays has written a great SP to show big tables
CREATE PROC sp_show_huge_tables
(
@.top int = NULL,
@.include_system_tables bit = 0
)
AS
/*
To see the top three biggest user or system tables in your database:
EXEC sp_show_huge_tables 3, 1
****************************************************************************
*********************/
BEGIN
IF @.top > 0
SET ROWCOUNT @.top
SELECT [Table Name], (SELECT rows FROM sysindexes s WHERE s.indid < 2 AND
s.id = OBJECT_ID(a.[Table Name])) AS [Row count], [Total space used (MB)]
FROM
(
SELECT QUOTENAME(USER_NAME(o.uid)) + '.' + QUOTENAME(OBJECT_NAME(i.id)) AS
[Table Name],
CONVERT(numeric(15,2),(((CONVERT(numeric(15,2),SUM(i.reserved)) * (SELECT
low FROM master.dbo.spt_values (NOLOCK) WHERE number = 1 AND type = 'E')) /
1024.)/1024.)) AS [Total space used (MB)]
FROM sysindexes i (NOLOCK)
INNER JOIN
sysobjects o (NOLOCK)
ON
i.id = o.id AND
((@.include_system_tables = 1 AND o.type IN ('U', 'S')) OR o.type = 'U')
AND
((@.include_system_tables = 1)OR (OBJECTPROPERTY(i.id, 'IsMSShipped') = 0))
WHERE indid IN (0, 1, 255)
GROUP BY QUOTENAME(USER_NAME(o.uid)) + '.' + QUOTENAME(OBJECT_NAME(i.id))
) as a
ORDER BY [Total space used (MB)] DESC
SET ROWCOUNT 0
END
GO
--
3)SELECT TOP 5
CAST(sysobjects.[name] AS VARCHAR) AS [TABLE_NAME],
sysindexes.[rows] AS [NO_OF_ROWS],
sysindexes.reserved AS [RESERVED_SPACE],
sysindexes.used AS [USED_SPACE]
FROM sysobjects
INNER JOIN sysindexes
ON sysobjects.[id] = sysindexes.[id]
WHERE sysindexes.indid < 2
AND sysobjects.type = 'U'
ORDER BY used DESC
"FLX" <nospam@.hotmail.com> wrote in message
news:OicCviu1EHA.1192@.tk2msftngp13.phx.gbl...
> I have a database whose size is over 50GB. What is the easy way to
determine
> the size of each database objects (tables)? I want to find out the ones
that
> take most of the space. Thanks a lot,
> FL
>
>|||And for both sets of responses, remember to update statistics first
(sysindexes won't necessarily be up to date).
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:#MXp$pu1EHA.1564@.TK2MSFTNGP09.phx.gbl...
> FLX
> 1)sp_spaceused in the BOL
> --
> 2) Vays has written a great SP to show big tables
> CREATE PROC sp_show_huge_tables
> (
> @.top int = NULL,
> @.include_system_tables bit = 0
> )
> AS
> /*
> To see the top three biggest user or system tables in your database:
> EXEC sp_show_huge_tables 3, 1
>
****************************************************************************
> *********************/
> BEGIN
> IF @.top > 0
> SET ROWCOUNT @.top
> SELECT [Table Name], (SELECT rows FROM sysindexes s WHERE s.indid < 2 AND
> s.id = OBJECT_ID(a.[Table Name])) AS [Row count], [Total space used (MB)]
> FROM
> (
> SELECT QUOTENAME(USER_NAME(o.uid)) + '.' + QUOTENAME(OBJECT_NAME(i.id))
AS
> [Table Name],
> CONVERT(numeric(15,2),(((CONVERT(numeric(15,2),SUM(i.reserved)) *
(SELECT
> low FROM master.dbo.spt_values (NOLOCK) WHERE number = 1 AND type = 'E'))
/
> 1024.)/1024.)) AS [Total space used (MB)]
> FROM sysindexes i (NOLOCK)
> INNER JOIN
> sysobjects o (NOLOCK)
> ON
> i.id = o.id AND
> ((@.include_system_tables = 1 AND o.type IN ('U', 'S')) OR o.type = 'U')
> AND
> ((@.include_system_tables = 1)OR (OBJECTPROPERTY(i.id, 'IsMSShipped') =0))
> WHERE indid IN (0, 1, 255)
> GROUP BY QUOTENAME(USER_NAME(o.uid)) + '.' + QUOTENAME(OBJECT_NAME(i.id))
> ) as a
> ORDER BY [Total space used (MB)] DESC
>
> SET ROWCOUNT 0
> END
>
> GO
> --
> 3)SELECT TOP 5
> CAST(sysobjects.[name] AS VARCHAR) AS [TABLE_NAME],
> sysindexes.[rows] AS [NO_OF_ROWS],
> sysindexes.reserved AS [RESERVED_SPACE],
> sysindexes.used AS [USED_SPACE]
> FROM sysobjects
> INNER JOIN sysindexes
> ON sysobjects.[id] = sysindexes.[id]
> WHERE sysindexes.indid < 2
> AND sysobjects.type = 'U'
> ORDER BY used DESC
> "FLX" <nospam@.hotmail.com> wrote in message
> news:OicCviu1EHA.1192@.tk2msftngp13.phx.gbl...
> > I have a database whose size is over 50GB. What is the easy way to
> determine
> > the size of each database objects (tables)? I want to find out the ones
> that
> > take most of the space. Thanks a lot,
> >
> > FL
> >
> >
> >
>|||"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uwZI6nu1EHA.3908@.TK2MSFTNGP12.phx.gbl...
> This uses an undocumented and unsupported system procedure.
> EXEC sp_MSForEachTable 'EXEC sp_spaceused ''?'''
> Another method would be to do this:
> SELECT 'EXEC sp_spaceused '+TABLE_NAME+' FROM INFORMATION_SCHEMA.TABLES
> Run that in Query Analyzer, using Results to TEXT, copy the output to the
> top pane and run that...
>
Cool.
FYI, I found there is a typo (extra single quote) in the query. It should
be:
SELECT 'EXEC sp_spaceused '+ TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
FL
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "FLX" <nospam@.hotmail.com> wrote in message
> news:OicCviu1EHA.1192@.tk2msftngp13.phx.gbl...
> > I have a database whose size is over 50GB. What is the easy way to
> determine
> > the size of each database objects (tables)? I want to find out the ones
> that
> > take most of the space. Thanks a lot,
> >
> > FL
> >
> >
> >
>
the size of each database objects (tables)? I want to find out the ones that
take most of the space. Thanks a lot,
FLThis uses an undocumented and unsupported system procedure.
EXEC sp_MSForEachTable 'EXEC sp_spaceused ''?'''
Another method would be to do this:
SELECT 'EXEC sp_spaceused '+TABLE_NAME+' FROM INFORMATION_SCHEMA.TABLES
Run that in Query Analyzer, using Results to TEXT, copy the output to the
top pane and run that...
--
http://www.aspfaq.com/
(Reverse address to reply.)
"FLX" <nospam@.hotmail.com> wrote in message
news:OicCviu1EHA.1192@.tk2msftngp13.phx.gbl...
> I have a database whose size is over 50GB. What is the easy way to
determine
> the size of each database objects (tables)? I want to find out the ones
that
> take most of the space. Thanks a lot,
> FL
>
>|||FLX
1)sp_spaceused in the BOL
--
2) Vays has written a great SP to show big tables
CREATE PROC sp_show_huge_tables
(
@.top int = NULL,
@.include_system_tables bit = 0
)
AS
/*
To see the top three biggest user or system tables in your database:
EXEC sp_show_huge_tables 3, 1
****************************************************************************
*********************/
BEGIN
IF @.top > 0
SET ROWCOUNT @.top
SELECT [Table Name], (SELECT rows FROM sysindexes s WHERE s.indid < 2 AND
s.id = OBJECT_ID(a.[Table Name])) AS [Row count], [Total space used (MB)]
FROM
(
SELECT QUOTENAME(USER_NAME(o.uid)) + '.' + QUOTENAME(OBJECT_NAME(i.id)) AS
[Table Name],
CONVERT(numeric(15,2),(((CONVERT(numeric(15,2),SUM(i.reserved)) * (SELECT
low FROM master.dbo.spt_values (NOLOCK) WHERE number = 1 AND type = 'E')) /
1024.)/1024.)) AS [Total space used (MB)]
FROM sysindexes i (NOLOCK)
INNER JOIN
sysobjects o (NOLOCK)
ON
i.id = o.id AND
((@.include_system_tables = 1 AND o.type IN ('U', 'S')) OR o.type = 'U')
AND
((@.include_system_tables = 1)OR (OBJECTPROPERTY(i.id, 'IsMSShipped') = 0))
WHERE indid IN (0, 1, 255)
GROUP BY QUOTENAME(USER_NAME(o.uid)) + '.' + QUOTENAME(OBJECT_NAME(i.id))
) as a
ORDER BY [Total space used (MB)] DESC
SET ROWCOUNT 0
END
GO
--
3)SELECT TOP 5
CAST(sysobjects.[name] AS VARCHAR) AS [TABLE_NAME],
sysindexes.[rows] AS [NO_OF_ROWS],
sysindexes.reserved AS [RESERVED_SPACE],
sysindexes.used AS [USED_SPACE]
FROM sysobjects
INNER JOIN sysindexes
ON sysobjects.[id] = sysindexes.[id]
WHERE sysindexes.indid < 2
AND sysobjects.type = 'U'
ORDER BY used DESC
"FLX" <nospam@.hotmail.com> wrote in message
news:OicCviu1EHA.1192@.tk2msftngp13.phx.gbl...
> I have a database whose size is over 50GB. What is the easy way to
determine
> the size of each database objects (tables)? I want to find out the ones
that
> take most of the space. Thanks a lot,
> FL
>
>|||And for both sets of responses, remember to update statistics first
(sysindexes won't necessarily be up to date).
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:#MXp$pu1EHA.1564@.TK2MSFTNGP09.phx.gbl...
> FLX
> 1)sp_spaceused in the BOL
> --
> 2) Vays has written a great SP to show big tables
> CREATE PROC sp_show_huge_tables
> (
> @.top int = NULL,
> @.include_system_tables bit = 0
> )
> AS
> /*
> To see the top three biggest user or system tables in your database:
> EXEC sp_show_huge_tables 3, 1
>
****************************************************************************
> *********************/
> BEGIN
> IF @.top > 0
> SET ROWCOUNT @.top
> SELECT [Table Name], (SELECT rows FROM sysindexes s WHERE s.indid < 2 AND
> s.id = OBJECT_ID(a.[Table Name])) AS [Row count], [Total space used (MB)]
> FROM
> (
> SELECT QUOTENAME(USER_NAME(o.uid)) + '.' + QUOTENAME(OBJECT_NAME(i.id))
AS
> [Table Name],
> CONVERT(numeric(15,2),(((CONVERT(numeric(15,2),SUM(i.reserved)) *
(SELECT
> low FROM master.dbo.spt_values (NOLOCK) WHERE number = 1 AND type = 'E'))
/
> 1024.)/1024.)) AS [Total space used (MB)]
> FROM sysindexes i (NOLOCK)
> INNER JOIN
> sysobjects o (NOLOCK)
> ON
> i.id = o.id AND
> ((@.include_system_tables = 1 AND o.type IN ('U', 'S')) OR o.type = 'U')
> AND
> ((@.include_system_tables = 1)OR (OBJECTPROPERTY(i.id, 'IsMSShipped') =0))
> WHERE indid IN (0, 1, 255)
> GROUP BY QUOTENAME(USER_NAME(o.uid)) + '.' + QUOTENAME(OBJECT_NAME(i.id))
> ) as a
> ORDER BY [Total space used (MB)] DESC
>
> SET ROWCOUNT 0
> END
>
> GO
> --
> 3)SELECT TOP 5
> CAST(sysobjects.[name] AS VARCHAR) AS [TABLE_NAME],
> sysindexes.[rows] AS [NO_OF_ROWS],
> sysindexes.reserved AS [RESERVED_SPACE],
> sysindexes.used AS [USED_SPACE]
> FROM sysobjects
> INNER JOIN sysindexes
> ON sysobjects.[id] = sysindexes.[id]
> WHERE sysindexes.indid < 2
> AND sysobjects.type = 'U'
> ORDER BY used DESC
> "FLX" <nospam@.hotmail.com> wrote in message
> news:OicCviu1EHA.1192@.tk2msftngp13.phx.gbl...
> > I have a database whose size is over 50GB. What is the easy way to
> determine
> > the size of each database objects (tables)? I want to find out the ones
> that
> > take most of the space. Thanks a lot,
> >
> > FL
> >
> >
> >
>|||"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uwZI6nu1EHA.3908@.TK2MSFTNGP12.phx.gbl...
> This uses an undocumented and unsupported system procedure.
> EXEC sp_MSForEachTable 'EXEC sp_spaceused ''?'''
> Another method would be to do this:
> SELECT 'EXEC sp_spaceused '+TABLE_NAME+' FROM INFORMATION_SCHEMA.TABLES
> Run that in Query Analyzer, using Results to TEXT, copy the output to the
> top pane and run that...
>
Cool.
FYI, I found there is a typo (extra single quote) in the query. It should
be:
SELECT 'EXEC sp_spaceused '+ TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
FL
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "FLX" <nospam@.hotmail.com> wrote in message
> news:OicCviu1EHA.1192@.tk2msftngp13.phx.gbl...
> > I have a database whose size is over 50GB. What is the easy way to
> determine
> > the size of each database objects (tables)? I want to find out the ones
> that
> > take most of the space. Thanks a lot,
> >
> > FL
> >
> >
> >
>
Determine the database objects' sizes?
I have a database whose size is over 50GB. What is the easy way to determine
the size of each database objects (tables)? I want to find out the ones that
take most of the space. Thanks a lot,
FL
This uses an undocumented and unsupported system procedure.
EXEC sp_MSForEachTable 'EXEC sp_spaceused ''?'''
Another method would be to do this:
SELECT 'EXEC sp_spaceused '+TABLE_NAME+' FROM INFORMATION_SCHEMA.TABLES
Run that in Query Analyzer, using Results to TEXT, copy the output to the
top pane and run that...
http://www.aspfaq.com/
(Reverse address to reply.)
"FLX" <nospam@.hotmail.com> wrote in message
news:OicCviu1EHA.1192@.tk2msftngp13.phx.gbl...
> I have a database whose size is over 50GB. What is the easy way to
determine
> the size of each database objects (tables)? I want to find out the ones
that
> take most of the space. Thanks a lot,
> FL
>
>
|||FLX
1)sp_spaceused in the BOL
2) Vays has written a great SP to show big tables
CREATE PROC sp_show_huge_tables
(
@.top int = NULL,
@.include_system_tables bit = 0
)
AS
/*
To see the top three biggest user or system tables in your database:
EXEC sp_show_huge_tables 3, 1
************************************************** **************************
*********************/
BEGIN
IF @.top > 0
SET ROWCOUNT @.top
SELECT [Table Name], (SELECT rows FROM sysindexes s WHERE s.indid < 2 AND
s.id = OBJECT_ID(a.[Table Name])) AS [Row count], [Total space used (MB)]
FROM
(
SELECT QUOTENAME(USER_NAME(o.uid)) + '.' + QUOTENAME(OBJECT_NAME(i.id)) AS
[Table Name],
CONVERT(numeric(15,2),(((CONVERT(numeric(15,2),SUM (i.reserved)) * (SELECT
low FROM master.dbo.spt_values (NOLOCK) WHERE number = 1 AND type = 'E')) /
1024.)/1024.)) AS [Total space used (MB)]
FROM sysindexes i (NOLOCK)
INNER JOIN
sysobjects o (NOLOCK)
ON
i.id = o.id AND
((@.include_system_tables = 1 AND o.type IN ('U', 'S')) OR o.type = 'U')
AND
((@.include_system_tables = 1)OR (OBJECTPROPERTY(i.id, 'IsMSShipped') = 0))
WHERE indid IN (0, 1, 255)
GROUP BY QUOTENAME(USER_NAME(o.uid)) + '.' + QUOTENAME(OBJECT_NAME(i.id))
) as a
ORDER BY [Total space used (MB)] DESC
SET ROWCOUNT 0
END
GO
3)SELECT TOP 5
CAST(sysobjects.[name] AS VARCHAR) AS [TABLE_NAME],
sysindexes.[rows] AS [NO_OF_ROWS],
sysindexes.reserved AS [RESERVED_SPACE],
sysindexes.used AS [USED_SPACE]
FROM sysobjects
INNER JOIN sysindexes
ON sysobjects.[id] = sysindexes.[id]
WHERE sysindexes.indid < 2
AND sysobjects.type = 'U'
ORDER BY used DESC
"FLX" <nospam@.hotmail.com> wrote in message
news:OicCviu1EHA.1192@.tk2msftngp13.phx.gbl...
> I have a database whose size is over 50GB. What is the easy way to
determine
> the size of each database objects (tables)? I want to find out the ones
that
> take most of the space. Thanks a lot,
> FL
>
>
|||And for both sets of responses, remember to update statistics first
(sysindexes won't necessarily be up to date).
http://www.aspfaq.com/
(Reverse address to reply.)
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:#MXp$pu1EHA.1564@.TK2MSFTNGP09.phx.gbl...
> FLX
> 1)sp_spaceused in the BOL
> --
> 2) Vays has written a great SP to show big tables
> CREATE PROC sp_show_huge_tables
> (
> @.top int = NULL,
> @.include_system_tables bit = 0
> )
> AS
> /*
> To see the top three biggest user or system tables in your database:
> EXEC sp_show_huge_tables 3, 1
>
************************************************** **************************
> *********************/
> BEGIN
> IF @.top > 0
> SET ROWCOUNT @.top
> SELECT [Table Name], (SELECT rows FROM sysindexes s WHERE s.indid < 2 AND
> s.id = OBJECT_ID(a.[Table Name])) AS [Row count], [Total space used (MB)]
> FROM
> (
> SELECT QUOTENAME(USER_NAME(o.uid)) + '.' + QUOTENAME(OBJECT_NAME(i.id))
AS
> [Table Name],
> CONVERT(numeric(15,2),(((CONVERT(numeric(15,2),SUM (i.reserved)) *
(SELECT
> low FROM master.dbo.spt_values (NOLOCK) WHERE number = 1 AND type = 'E'))
/
> 1024.)/1024.)) AS [Total space used (MB)]
> FROM sysindexes i (NOLOCK)
> INNER JOIN
> sysobjects o (NOLOCK)
> ON
> i.id = o.id AND
> ((@.include_system_tables = 1 AND o.type IN ('U', 'S')) OR o.type = 'U')
> AND
> ((@.include_system_tables = 1)OR (OBJECTPROPERTY(i.id, 'IsMSShipped') =
0))
> WHERE indid IN (0, 1, 255)
> GROUP BY QUOTENAME(USER_NAME(o.uid)) + '.' + QUOTENAME(OBJECT_NAME(i.id))
> ) as a
> ORDER BY [Total space used (MB)] DESC
>
> SET ROWCOUNT 0
> END
>
> GO
> --
> 3)SELECT TOP 5
> CAST(sysobjects.[name] AS VARCHAR) AS [TABLE_NAME],
> sysindexes.[rows] AS [NO_OF_ROWS],
> sysindexes.reserved AS [RESERVED_SPACE],
> sysindexes.used AS [USED_SPACE]
> FROM sysobjects
> INNER JOIN sysindexes
> ON sysobjects.[id] = sysindexes.[id]
> WHERE sysindexes.indid < 2
> AND sysobjects.type = 'U'
> ORDER BY used DESC
> "FLX" <nospam@.hotmail.com> wrote in message
> news:OicCviu1EHA.1192@.tk2msftngp13.phx.gbl...
> determine
> that
>
|||"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uwZI6nu1EHA.3908@.TK2MSFTNGP12.phx.gbl...
> This uses an undocumented and unsupported system procedure.
> EXEC sp_MSForEachTable 'EXEC sp_spaceused ''?'''
> Another method would be to do this:
> SELECT 'EXEC sp_spaceused '+TABLE_NAME+' FROM INFORMATION_SCHEMA.TABLES
> Run that in Query Analyzer, using Results to TEXT, copy the output to the
> top pane and run that...
>
Cool.
FYI, I found there is a typo (extra single quote) in the query. It should
be:
SELECT 'EXEC sp_spaceused '+ TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
FL
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "FLX" <nospam@.hotmail.com> wrote in message
> news:OicCviu1EHA.1192@.tk2msftngp13.phx.gbl...
> determine
> that
>
the size of each database objects (tables)? I want to find out the ones that
take most of the space. Thanks a lot,
FL
This uses an undocumented and unsupported system procedure.
EXEC sp_MSForEachTable 'EXEC sp_spaceused ''?'''
Another method would be to do this:
SELECT 'EXEC sp_spaceused '+TABLE_NAME+' FROM INFORMATION_SCHEMA.TABLES
Run that in Query Analyzer, using Results to TEXT, copy the output to the
top pane and run that...
http://www.aspfaq.com/
(Reverse address to reply.)
"FLX" <nospam@.hotmail.com> wrote in message
news:OicCviu1EHA.1192@.tk2msftngp13.phx.gbl...
> I have a database whose size is over 50GB. What is the easy way to
determine
> the size of each database objects (tables)? I want to find out the ones
that
> take most of the space. Thanks a lot,
> FL
>
>
|||FLX
1)sp_spaceused in the BOL
2) Vays has written a great SP to show big tables
CREATE PROC sp_show_huge_tables
(
@.top int = NULL,
@.include_system_tables bit = 0
)
AS
/*
To see the top three biggest user or system tables in your database:
EXEC sp_show_huge_tables 3, 1
************************************************** **************************
*********************/
BEGIN
IF @.top > 0
SET ROWCOUNT @.top
SELECT [Table Name], (SELECT rows FROM sysindexes s WHERE s.indid < 2 AND
s.id = OBJECT_ID(a.[Table Name])) AS [Row count], [Total space used (MB)]
FROM
(
SELECT QUOTENAME(USER_NAME(o.uid)) + '.' + QUOTENAME(OBJECT_NAME(i.id)) AS
[Table Name],
CONVERT(numeric(15,2),(((CONVERT(numeric(15,2),SUM (i.reserved)) * (SELECT
low FROM master.dbo.spt_values (NOLOCK) WHERE number = 1 AND type = 'E')) /
1024.)/1024.)) AS [Total space used (MB)]
FROM sysindexes i (NOLOCK)
INNER JOIN
sysobjects o (NOLOCK)
ON
i.id = o.id AND
((@.include_system_tables = 1 AND o.type IN ('U', 'S')) OR o.type = 'U')
AND
((@.include_system_tables = 1)OR (OBJECTPROPERTY(i.id, 'IsMSShipped') = 0))
WHERE indid IN (0, 1, 255)
GROUP BY QUOTENAME(USER_NAME(o.uid)) + '.' + QUOTENAME(OBJECT_NAME(i.id))
) as a
ORDER BY [Total space used (MB)] DESC
SET ROWCOUNT 0
END
GO
3)SELECT TOP 5
CAST(sysobjects.[name] AS VARCHAR) AS [TABLE_NAME],
sysindexes.[rows] AS [NO_OF_ROWS],
sysindexes.reserved AS [RESERVED_SPACE],
sysindexes.used AS [USED_SPACE]
FROM sysobjects
INNER JOIN sysindexes
ON sysobjects.[id] = sysindexes.[id]
WHERE sysindexes.indid < 2
AND sysobjects.type = 'U'
ORDER BY used DESC
"FLX" <nospam@.hotmail.com> wrote in message
news:OicCviu1EHA.1192@.tk2msftngp13.phx.gbl...
> I have a database whose size is over 50GB. What is the easy way to
determine
> the size of each database objects (tables)? I want to find out the ones
that
> take most of the space. Thanks a lot,
> FL
>
>
|||And for both sets of responses, remember to update statistics first
(sysindexes won't necessarily be up to date).
http://www.aspfaq.com/
(Reverse address to reply.)
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:#MXp$pu1EHA.1564@.TK2MSFTNGP09.phx.gbl...
> FLX
> 1)sp_spaceused in the BOL
> --
> 2) Vays has written a great SP to show big tables
> CREATE PROC sp_show_huge_tables
> (
> @.top int = NULL,
> @.include_system_tables bit = 0
> )
> AS
> /*
> To see the top three biggest user or system tables in your database:
> EXEC sp_show_huge_tables 3, 1
>
************************************************** **************************
> *********************/
> BEGIN
> IF @.top > 0
> SET ROWCOUNT @.top
> SELECT [Table Name], (SELECT rows FROM sysindexes s WHERE s.indid < 2 AND
> s.id = OBJECT_ID(a.[Table Name])) AS [Row count], [Total space used (MB)]
> FROM
> (
> SELECT QUOTENAME(USER_NAME(o.uid)) + '.' + QUOTENAME(OBJECT_NAME(i.id))
AS
> [Table Name],
> CONVERT(numeric(15,2),(((CONVERT(numeric(15,2),SUM (i.reserved)) *
(SELECT
> low FROM master.dbo.spt_values (NOLOCK) WHERE number = 1 AND type = 'E'))
/
> 1024.)/1024.)) AS [Total space used (MB)]
> FROM sysindexes i (NOLOCK)
> INNER JOIN
> sysobjects o (NOLOCK)
> ON
> i.id = o.id AND
> ((@.include_system_tables = 1 AND o.type IN ('U', 'S')) OR o.type = 'U')
> AND
> ((@.include_system_tables = 1)OR (OBJECTPROPERTY(i.id, 'IsMSShipped') =
0))
> WHERE indid IN (0, 1, 255)
> GROUP BY QUOTENAME(USER_NAME(o.uid)) + '.' + QUOTENAME(OBJECT_NAME(i.id))
> ) as a
> ORDER BY [Total space used (MB)] DESC
>
> SET ROWCOUNT 0
> END
>
> GO
> --
> 3)SELECT TOP 5
> CAST(sysobjects.[name] AS VARCHAR) AS [TABLE_NAME],
> sysindexes.[rows] AS [NO_OF_ROWS],
> sysindexes.reserved AS [RESERVED_SPACE],
> sysindexes.used AS [USED_SPACE]
> FROM sysobjects
> INNER JOIN sysindexes
> ON sysobjects.[id] = sysindexes.[id]
> WHERE sysindexes.indid < 2
> AND sysobjects.type = 'U'
> ORDER BY used DESC
> "FLX" <nospam@.hotmail.com> wrote in message
> news:OicCviu1EHA.1192@.tk2msftngp13.phx.gbl...
> determine
> that
>
|||"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uwZI6nu1EHA.3908@.TK2MSFTNGP12.phx.gbl...
> This uses an undocumented and unsupported system procedure.
> EXEC sp_MSForEachTable 'EXEC sp_spaceused ''?'''
> Another method would be to do this:
> SELECT 'EXEC sp_spaceused '+TABLE_NAME+' FROM INFORMATION_SCHEMA.TABLES
> Run that in Query Analyzer, using Results to TEXT, copy the output to the
> top pane and run that...
>
Cool.
FYI, I found there is a typo (extra single quote) in the query. It should
be:
SELECT 'EXEC sp_spaceused '+ TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
FL
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "FLX" <nospam@.hotmail.com> wrote in message
> news:OicCviu1EHA.1192@.tk2msftngp13.phx.gbl...
> determine
> that
>
Determine table size
Hi,
I have a client with a database that is running over 1G in size. Looking at
the tables, I can't find any that would seem to be larger than a 1-2 meg
with total size should be around 30meg. Is there any way to find out table
sizes?
Thanks,
Mica
Hi Mica Cooper,
To Check table sizes, Select the db name in the enterprise under Databases,
In the Main Menu Click View and select Taskpad, you will get 3 tabs :
General, Table Info and Wizards, Select Table Info and you'll see all the
tables listed with rows count and size
Mario Aoun
"Mica Cooper" wrote:
> Hi,
> I have a client with a database that is running over 1G in size. Looking at
> the tables, I can't find any that would seem to be larger than a 1-2 meg
> with total size should be around 30meg. Is there any way to find out table
> sizes?
> Thanks,
> Mica
>
>
|||Use sp_spaceused or see: http://vyaskn.tripod.com/sp_show_biggest_tables.htm
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Mica Cooper" <Mica.Cooper@.removethis.aisus.com> wrote in message
news:eMQy0MrkFHA.1444@.TK2MSFTNGP10.phx.gbl...
Hi,
I have a client with a database that is running over 1G in size. Looking at
the tables, I can't find any that would seem to be larger than a 1-2 meg
with total size should be around 30meg. Is there any way to find out table
sizes?
Thanks,
Mica
|||Database size is a fuzzy thing. I don't know your level of SQL experience,
so forgive me if I start off with some basics.
What do you mean, when you say "over 1GB in size"? Right click on the
database in SQL Server Enterprise Manager (I'm assuming SQL 2000), and go to
properties. On the first tab ("General"), there's a "Size", and then a
"Space Available". Is this the 1GB you're talking about? What does the
"Space Available" have beside it?
If that is the size that you're talking about, that includes the log file as
well as the actual data file. Click on the next tab "Data Files", and see
how much space is allocated. Then go to the "Transaction Log" tab, and see
the same thing there. If the transaction log is huge, it may be as simple
as either adjusting your backup procedures, or changing the "Recovery Model"
on the "Options" tab.
The other thing that can happen is that SQL database grow automatically, but
the default settings do not let the databases shrink automatically. To
shrink a database, right click on the database, and go to "All Tasks", then
"Shrink Database". I seem to have the best luck with shrinking by clicking
on the "Files" button on the first form, then manually setting the desired
database file sizes by selecting first the data (.mdf) file, then the log
(.ldf) file. You likely have to either do a complete backup or change the
"Recovery Model" before you'll get a lot of space out of the log file.
To find out the actual size of a particular table, you can execute the
"sp_spaceused <tablename>" function in Query Analyzer. It will give you the
number of rows, reserved size, data size, index size, and unused size.
I hope this helped! I'm not a SQL expert, and I don't play one on TV. But
post back with your results and any new questions, if you need more
guidance.
Clint
"Mica Cooper" <Mica.Cooper@.removethis.aisus.com> wrote in message
news:eMQy0MrkFHA.1444@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have a client with a database that is running over 1G in size. Looking
> at the tables, I can't find any that would seem to be larger than a 1-2
> meg with total size should be around 30meg. Is there any way to find out
> table sizes?
> Thanks,
> Mica
>
|||Mario,
That is COOL! In all the years I've used SQL Server I have never seen that.
Its exactly what I was looking for.
Mica
"Mario Aoun" <MarioAoun@.discussions.microsoft.com> wrote in message
news:8AD1DCE1-0E27-463B-BE9E-59D00D5137C2@.microsoft.com...[vbcol=seagreen]
> Hi Mica Cooper,
> To Check table sizes, Select the db name in the enterprise under
> Databases,
> In the Main Menu Click View and select Taskpad, you will get 3 tabs :
> General, Table Info and Wizards, Select Table Info and you'll see all the
> tables listed with rows count and size
> Mario Aoun
> "Mica Cooper" wrote:
|||Clint,
I have already shrank the db and it didn't change the size. I am looking for
a problem becuase I know the db shouldn't be anywhere near that large and
this info should help. Mario's post was great. I have NEVER seen that before
and it told the story pretty quick.
So guess what? Look at the view told me what a dunce I am. The problem is
the log file is autmatically growing...1.3G. I should have seen that by
looking at the .LDF. Gotta go fix that now.
Thanks Guys,
Mica
"Clint" <nobody@.nowhere.non> wrote in message
news:O6TXngrkFHA.1044@.tk2msftngp13.phx.gbl...
> Database size is a fuzzy thing. I don't know your level of SQL
> experience, so forgive me if I start off with some basics.
> What do you mean, when you say "over 1GB in size"? Right click on the
> database in SQL Server Enterprise Manager (I'm assuming SQL 2000), and go
> to properties. On the first tab ("General"), there's a "Size", and then a
> "Space Available". Is this the 1GB you're talking about? What does the
> "Space Available" have beside it?
> If that is the size that you're talking about, that includes the log file
> as well as the actual data file. Click on the next tab "Data Files", and
> see how much space is allocated. Then go to the "Transaction Log" tab,
> and see the same thing there. If the transaction log is huge, it may be
> as simple as either adjusting your backup procedures, or changing the
> "Recovery Model" on the "Options" tab.
> The other thing that can happen is that SQL database grow automatically,
> but the default settings do not let the databases shrink automatically.
> To shrink a database, right click on the database, and go to "All Tasks",
> then "Shrink Database". I seem to have the best luck with shrinking by
> clicking on the "Files" button on the first form, then manually setting
> the desired database file sizes by selecting first the data (.mdf) file,
> then the log (.ldf) file. You likely have to either do a complete backup
> or change the "Recovery Model" before you'll get a lot of space out of the
> log file.
> To find out the actual size of a particular table, you can execute the
> "sp_spaceused <tablename>" function in Query Analyzer. It will give you
> the number of rows, reserved size, data size, index size, and unused size.
> I hope this helped! I'm not a SQL expert, and I don't play one on TV.
> But post back with your results and any new questions, if you need more
> guidance.
> Clint
> "Mica Cooper" <Mica.Cooper@.removethis.aisus.com> wrote in message
> news:eMQy0MrkFHA.1444@.TK2MSFTNGP10.phx.gbl...
>
I have a client with a database that is running over 1G in size. Looking at
the tables, I can't find any that would seem to be larger than a 1-2 meg
with total size should be around 30meg. Is there any way to find out table
sizes?
Thanks,
Mica
Hi Mica Cooper,
To Check table sizes, Select the db name in the enterprise under Databases,
In the Main Menu Click View and select Taskpad, you will get 3 tabs :
General, Table Info and Wizards, Select Table Info and you'll see all the
tables listed with rows count and size
Mario Aoun
"Mica Cooper" wrote:
> Hi,
> I have a client with a database that is running over 1G in size. Looking at
> the tables, I can't find any that would seem to be larger than a 1-2 meg
> with total size should be around 30meg. Is there any way to find out table
> sizes?
> Thanks,
> Mica
>
>
|||Use sp_spaceused or see: http://vyaskn.tripod.com/sp_show_biggest_tables.htm
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Mica Cooper" <Mica.Cooper@.removethis.aisus.com> wrote in message
news:eMQy0MrkFHA.1444@.TK2MSFTNGP10.phx.gbl...
Hi,
I have a client with a database that is running over 1G in size. Looking at
the tables, I can't find any that would seem to be larger than a 1-2 meg
with total size should be around 30meg. Is there any way to find out table
sizes?
Thanks,
Mica
|||Database size is a fuzzy thing. I don't know your level of SQL experience,
so forgive me if I start off with some basics.
What do you mean, when you say "over 1GB in size"? Right click on the
database in SQL Server Enterprise Manager (I'm assuming SQL 2000), and go to
properties. On the first tab ("General"), there's a "Size", and then a
"Space Available". Is this the 1GB you're talking about? What does the
"Space Available" have beside it?
If that is the size that you're talking about, that includes the log file as
well as the actual data file. Click on the next tab "Data Files", and see
how much space is allocated. Then go to the "Transaction Log" tab, and see
the same thing there. If the transaction log is huge, it may be as simple
as either adjusting your backup procedures, or changing the "Recovery Model"
on the "Options" tab.
The other thing that can happen is that SQL database grow automatically, but
the default settings do not let the databases shrink automatically. To
shrink a database, right click on the database, and go to "All Tasks", then
"Shrink Database". I seem to have the best luck with shrinking by clicking
on the "Files" button on the first form, then manually setting the desired
database file sizes by selecting first the data (.mdf) file, then the log
(.ldf) file. You likely have to either do a complete backup or change the
"Recovery Model" before you'll get a lot of space out of the log file.
To find out the actual size of a particular table, you can execute the
"sp_spaceused <tablename>" function in Query Analyzer. It will give you the
number of rows, reserved size, data size, index size, and unused size.
I hope this helped! I'm not a SQL expert, and I don't play one on TV. But
post back with your results and any new questions, if you need more
guidance.
Clint
"Mica Cooper" <Mica.Cooper@.removethis.aisus.com> wrote in message
news:eMQy0MrkFHA.1444@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have a client with a database that is running over 1G in size. Looking
> at the tables, I can't find any that would seem to be larger than a 1-2
> meg with total size should be around 30meg. Is there any way to find out
> table sizes?
> Thanks,
> Mica
>
|||Mario,
That is COOL! In all the years I've used SQL Server I have never seen that.
Its exactly what I was looking for.
Mica
"Mario Aoun" <MarioAoun@.discussions.microsoft.com> wrote in message
news:8AD1DCE1-0E27-463B-BE9E-59D00D5137C2@.microsoft.com...[vbcol=seagreen]
> Hi Mica Cooper,
> To Check table sizes, Select the db name in the enterprise under
> Databases,
> In the Main Menu Click View and select Taskpad, you will get 3 tabs :
> General, Table Info and Wizards, Select Table Info and you'll see all the
> tables listed with rows count and size
> Mario Aoun
> "Mica Cooper" wrote:
|||Clint,
I have already shrank the db and it didn't change the size. I am looking for
a problem becuase I know the db shouldn't be anywhere near that large and
this info should help. Mario's post was great. I have NEVER seen that before
and it told the story pretty quick.
So guess what? Look at the view told me what a dunce I am. The problem is
the log file is autmatically growing...1.3G. I should have seen that by
looking at the .LDF. Gotta go fix that now.
Thanks Guys,
Mica
"Clint" <nobody@.nowhere.non> wrote in message
news:O6TXngrkFHA.1044@.tk2msftngp13.phx.gbl...
> Database size is a fuzzy thing. I don't know your level of SQL
> experience, so forgive me if I start off with some basics.
> What do you mean, when you say "over 1GB in size"? Right click on the
> database in SQL Server Enterprise Manager (I'm assuming SQL 2000), and go
> to properties. On the first tab ("General"), there's a "Size", and then a
> "Space Available". Is this the 1GB you're talking about? What does the
> "Space Available" have beside it?
> If that is the size that you're talking about, that includes the log file
> as well as the actual data file. Click on the next tab "Data Files", and
> see how much space is allocated. Then go to the "Transaction Log" tab,
> and see the same thing there. If the transaction log is huge, it may be
> as simple as either adjusting your backup procedures, or changing the
> "Recovery Model" on the "Options" tab.
> The other thing that can happen is that SQL database grow automatically,
> but the default settings do not let the databases shrink automatically.
> To shrink a database, right click on the database, and go to "All Tasks",
> then "Shrink Database". I seem to have the best luck with shrinking by
> clicking on the "Files" button on the first form, then manually setting
> the desired database file sizes by selecting first the data (.mdf) file,
> then the log (.ldf) file. You likely have to either do a complete backup
> or change the "Recovery Model" before you'll get a lot of space out of the
> log file.
> To find out the actual size of a particular table, you can execute the
> "sp_spaceused <tablename>" function in Query Analyzer. It will give you
> the number of rows, reserved size, data size, index size, and unused size.
> I hope this helped! I'm not a SQL expert, and I don't play one on TV.
> But post back with your results and any new questions, if you need more
> guidance.
> Clint
> "Mica Cooper" <Mica.Cooper@.removethis.aisus.com> wrote in message
> news:eMQy0MrkFHA.1444@.TK2MSFTNGP10.phx.gbl...
>
Determine table size
Hi,
I have a client with a database that is running over 1G in size. Looking at
the tables, I can't find any that would seem to be larger than a 1-2 meg
with total size should be around 30meg. Is there any way to find out table
sizes?
Thanks,
MicaHi Mica Cooper,
To Check table sizes, Select the db name in the enterprise under Databases,
In the Main Menu Click View and select Taskpad, you will get 3 tabs :
General, Table Info and Wizards, Select Table Info and you'll see all the
tables listed with rows count and size
Mario Aoun
"Mica Cooper" wrote:
> Hi,
> I have a client with a database that is running over 1G in size. Looking at
> the tables, I can't find any that would seem to be larger than a 1-2 meg
> with total size should be around 30meg. Is there any way to find out table
> sizes?
> Thanks,
> Mica
>
>|||Use sp_spaceused or see: http://vyaskn.tripod.com/sp_show_biggest_tables.htm
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Mica Cooper" <Mica.Cooper@.removethis.aisus.com> wrote in message
news:eMQy0MrkFHA.1444@.TK2MSFTNGP10.phx.gbl...
Hi,
I have a client with a database that is running over 1G in size. Looking at
the tables, I can't find any that would seem to be larger than a 1-2 meg
with total size should be around 30meg. Is there any way to find out table
sizes?
Thanks,
Mica|||Database size is a fuzzy thing. I don't know your level of SQL experience,
so forgive me if I start off with some basics.
What do you mean, when you say "over 1GB in size"? Right click on the
database in SQL Server Enterprise Manager (I'm assuming SQL 2000), and go to
properties. On the first tab ("General"), there's a "Size", and then a
"Space Available". Is this the 1GB you're talking about? What does the
"Space Available" have beside it?
If that is the size that you're talking about, that includes the log file as
well as the actual data file. Click on the next tab "Data Files", and see
how much space is allocated. Then go to the "Transaction Log" tab, and see
the same thing there. If the transaction log is huge, it may be as simple
as either adjusting your backup procedures, or changing the "Recovery Model"
on the "Options" tab.
The other thing that can happen is that SQL database grow automatically, but
the default settings do not let the databases shrink automatically. To
shrink a database, right click on the database, and go to "All Tasks", then
"Shrink Database". I seem to have the best luck with shrinking by clicking
on the "Files" button on the first form, then manually setting the desired
database file sizes by selecting first the data (.mdf) file, then the log
(.ldf) file. You likely have to either do a complete backup or change the
"Recovery Model" before you'll get a lot of space out of the log file.
To find out the actual size of a particular table, you can execute the
"sp_spaceused <tablename>" function in Query Analyzer. It will give you the
number of rows, reserved size, data size, index size, and unused size.
I hope this helped! I'm not a SQL expert, and I don't play one on TV. But
post back with your results and any new questions, if you need more
guidance.
Clint
"Mica Cooper" <Mica.Cooper@.removethis.aisus.com> wrote in message
news:eMQy0MrkFHA.1444@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have a client with a database that is running over 1G in size. Looking
> at the tables, I can't find any that would seem to be larger than a 1-2
> meg with total size should be around 30meg. Is there any way to find out
> table sizes?
> Thanks,
> Mica
>|||Mario,
That is COOL! In all the years I've used SQL Server I have never seen that.
Its exactly what I was looking for.
Mica
"Mario Aoun" <MarioAoun@.discussions.microsoft.com> wrote in message
news:8AD1DCE1-0E27-463B-BE9E-59D00D5137C2@.microsoft.com...
> Hi Mica Cooper,
> To Check table sizes, Select the db name in the enterprise under
> Databases,
> In the Main Menu Click View and select Taskpad, you will get 3 tabs :
> General, Table Info and Wizards, Select Table Info and you'll see all the
> tables listed with rows count and size
> Mario Aoun
> "Mica Cooper" wrote:
>> Hi,
>> I have a client with a database that is running over 1G in size. Looking
>> at
>> the tables, I can't find any that would seem to be larger than a 1-2 meg
>> with total size should be around 30meg. Is there any way to find out
>> table
>> sizes?
>> Thanks,
>> Mica
>>|||Clint,
I have already shrank the db and it didn't change the size. I am looking for
a problem becuase I know the db shouldn't be anywhere near that large and
this info should help. Mario's post was great. I have NEVER seen that before
and it told the story pretty quick.
So guess what? Look at the view told me what a dunce I am. The problem is
the log file is autmatically growing...1.3G. I should have seen that by
looking at the .LDF. Gotta go fix that now.
Thanks Guys,
Mica
"Clint" <nobody@.nowhere.non> wrote in message
news:O6TXngrkFHA.1044@.tk2msftngp13.phx.gbl...
> Database size is a fuzzy thing. I don't know your level of SQL
> experience, so forgive me if I start off with some basics.
> What do you mean, when you say "over 1GB in size"? Right click on the
> database in SQL Server Enterprise Manager (I'm assuming SQL 2000), and go
> to properties. On the first tab ("General"), there's a "Size", and then a
> "Space Available". Is this the 1GB you're talking about? What does the
> "Space Available" have beside it?
> If that is the size that you're talking about, that includes the log file
> as well as the actual data file. Click on the next tab "Data Files", and
> see how much space is allocated. Then go to the "Transaction Log" tab,
> and see the same thing there. If the transaction log is huge, it may be
> as simple as either adjusting your backup procedures, or changing the
> "Recovery Model" on the "Options" tab.
> The other thing that can happen is that SQL database grow automatically,
> but the default settings do not let the databases shrink automatically.
> To shrink a database, right click on the database, and go to "All Tasks",
> then "Shrink Database". I seem to have the best luck with shrinking by
> clicking on the "Files" button on the first form, then manually setting
> the desired database file sizes by selecting first the data (.mdf) file,
> then the log (.ldf) file. You likely have to either do a complete backup
> or change the "Recovery Model" before you'll get a lot of space out of the
> log file.
> To find out the actual size of a particular table, you can execute the
> "sp_spaceused <tablename>" function in Query Analyzer. It will give you
> the number of rows, reserved size, data size, index size, and unused size.
> I hope this helped! I'm not a SQL expert, and I don't play one on TV.
> But post back with your results and any new questions, if you need more
> guidance.
> Clint
> "Mica Cooper" <Mica.Cooper@.removethis.aisus.com> wrote in message
> news:eMQy0MrkFHA.1444@.TK2MSFTNGP10.phx.gbl...
>> Hi,
>> I have a client with a database that is running over 1G in size. Looking
>> at the tables, I can't find any that would seem to be larger than a 1-2
>> meg with total size should be around 30meg. Is there any way to find out
>> table sizes?
>> Thanks,
>> Mica
>sql
I have a client with a database that is running over 1G in size. Looking at
the tables, I can't find any that would seem to be larger than a 1-2 meg
with total size should be around 30meg. Is there any way to find out table
sizes?
Thanks,
MicaHi Mica Cooper,
To Check table sizes, Select the db name in the enterprise under Databases,
In the Main Menu Click View and select Taskpad, you will get 3 tabs :
General, Table Info and Wizards, Select Table Info and you'll see all the
tables listed with rows count and size
Mario Aoun
"Mica Cooper" wrote:
> Hi,
> I have a client with a database that is running over 1G in size. Looking at
> the tables, I can't find any that would seem to be larger than a 1-2 meg
> with total size should be around 30meg. Is there any way to find out table
> sizes?
> Thanks,
> Mica
>
>|||Use sp_spaceused or see: http://vyaskn.tripod.com/sp_show_biggest_tables.htm
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Mica Cooper" <Mica.Cooper@.removethis.aisus.com> wrote in message
news:eMQy0MrkFHA.1444@.TK2MSFTNGP10.phx.gbl...
Hi,
I have a client with a database that is running over 1G in size. Looking at
the tables, I can't find any that would seem to be larger than a 1-2 meg
with total size should be around 30meg. Is there any way to find out table
sizes?
Thanks,
Mica|||Database size is a fuzzy thing. I don't know your level of SQL experience,
so forgive me if I start off with some basics.
What do you mean, when you say "over 1GB in size"? Right click on the
database in SQL Server Enterprise Manager (I'm assuming SQL 2000), and go to
properties. On the first tab ("General"), there's a "Size", and then a
"Space Available". Is this the 1GB you're talking about? What does the
"Space Available" have beside it?
If that is the size that you're talking about, that includes the log file as
well as the actual data file. Click on the next tab "Data Files", and see
how much space is allocated. Then go to the "Transaction Log" tab, and see
the same thing there. If the transaction log is huge, it may be as simple
as either adjusting your backup procedures, or changing the "Recovery Model"
on the "Options" tab.
The other thing that can happen is that SQL database grow automatically, but
the default settings do not let the databases shrink automatically. To
shrink a database, right click on the database, and go to "All Tasks", then
"Shrink Database". I seem to have the best luck with shrinking by clicking
on the "Files" button on the first form, then manually setting the desired
database file sizes by selecting first the data (.mdf) file, then the log
(.ldf) file. You likely have to either do a complete backup or change the
"Recovery Model" before you'll get a lot of space out of the log file.
To find out the actual size of a particular table, you can execute the
"sp_spaceused <tablename>" function in Query Analyzer. It will give you the
number of rows, reserved size, data size, index size, and unused size.
I hope this helped! I'm not a SQL expert, and I don't play one on TV. But
post back with your results and any new questions, if you need more
guidance.
Clint
"Mica Cooper" <Mica.Cooper@.removethis.aisus.com> wrote in message
news:eMQy0MrkFHA.1444@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have a client with a database that is running over 1G in size. Looking
> at the tables, I can't find any that would seem to be larger than a 1-2
> meg with total size should be around 30meg. Is there any way to find out
> table sizes?
> Thanks,
> Mica
>|||Mario,
That is COOL! In all the years I've used SQL Server I have never seen that.
Its exactly what I was looking for.
Mica
"Mario Aoun" <MarioAoun@.discussions.microsoft.com> wrote in message
news:8AD1DCE1-0E27-463B-BE9E-59D00D5137C2@.microsoft.com...
> Hi Mica Cooper,
> To Check table sizes, Select the db name in the enterprise under
> Databases,
> In the Main Menu Click View and select Taskpad, you will get 3 tabs :
> General, Table Info and Wizards, Select Table Info and you'll see all the
> tables listed with rows count and size
> Mario Aoun
> "Mica Cooper" wrote:
>> Hi,
>> I have a client with a database that is running over 1G in size. Looking
>> at
>> the tables, I can't find any that would seem to be larger than a 1-2 meg
>> with total size should be around 30meg. Is there any way to find out
>> table
>> sizes?
>> Thanks,
>> Mica
>>|||Clint,
I have already shrank the db and it didn't change the size. I am looking for
a problem becuase I know the db shouldn't be anywhere near that large and
this info should help. Mario's post was great. I have NEVER seen that before
and it told the story pretty quick.
So guess what? Look at the view told me what a dunce I am. The problem is
the log file is autmatically growing...1.3G. I should have seen that by
looking at the .LDF. Gotta go fix that now.
Thanks Guys,
Mica
"Clint" <nobody@.nowhere.non> wrote in message
news:O6TXngrkFHA.1044@.tk2msftngp13.phx.gbl...
> Database size is a fuzzy thing. I don't know your level of SQL
> experience, so forgive me if I start off with some basics.
> What do you mean, when you say "over 1GB in size"? Right click on the
> database in SQL Server Enterprise Manager (I'm assuming SQL 2000), and go
> to properties. On the first tab ("General"), there's a "Size", and then a
> "Space Available". Is this the 1GB you're talking about? What does the
> "Space Available" have beside it?
> If that is the size that you're talking about, that includes the log file
> as well as the actual data file. Click on the next tab "Data Files", and
> see how much space is allocated. Then go to the "Transaction Log" tab,
> and see the same thing there. If the transaction log is huge, it may be
> as simple as either adjusting your backup procedures, or changing the
> "Recovery Model" on the "Options" tab.
> The other thing that can happen is that SQL database grow automatically,
> but the default settings do not let the databases shrink automatically.
> To shrink a database, right click on the database, and go to "All Tasks",
> then "Shrink Database". I seem to have the best luck with shrinking by
> clicking on the "Files" button on the first form, then manually setting
> the desired database file sizes by selecting first the data (.mdf) file,
> then the log (.ldf) file. You likely have to either do a complete backup
> or change the "Recovery Model" before you'll get a lot of space out of the
> log file.
> To find out the actual size of a particular table, you can execute the
> "sp_spaceused <tablename>" function in Query Analyzer. It will give you
> the number of rows, reserved size, data size, index size, and unused size.
> I hope this helped! I'm not a SQL expert, and I don't play one on TV.
> But post back with your results and any new questions, if you need more
> guidance.
> Clint
> "Mica Cooper" <Mica.Cooper@.removethis.aisus.com> wrote in message
> news:eMQy0MrkFHA.1444@.TK2MSFTNGP10.phx.gbl...
>> Hi,
>> I have a client with a database that is running over 1G in size. Looking
>> at the tables, I can't find any that would seem to be larger than a 1-2
>> meg with total size should be around 30meg. Is there any way to find out
>> table sizes?
>> Thanks,
>> Mica
>sql
Determine table size
Hi,
I have a client with a database that is running over 1G in size. Looking at
the tables, I can't find any that would seem to be larger than a 1-2 meg
with total size should be around 30meg. Is there any way to find out table
sizes?
Thanks,
MicaHi Mica Cooper,
To Check table sizes, Select the db name in the enterprise under Databases,
In the Main Menu Click View and select Taskpad, you will get 3 tabs :
General, Table Info and Wizards, Select Table Info and you'll see all the
tables listed with rows count and size
Mario Aoun
"Mica Cooper" wrote:
> Hi,
> I have a client with a database that is running over 1G in size. Looking a
t
> the tables, I can't find any that would seem to be larger than a 1-2 meg
> with total size should be around 30meg. Is there any way to find out table
> sizes?
> Thanks,
> Mica
>
>|||Use sp_spaceused or see: http://vyaskn.tripod.com/sp_show_biggest_tables.htm
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Mica Cooper" <Mica.Cooper@.removethis.aisus.com> wrote in message
news:eMQy0MrkFHA.1444@.TK2MSFTNGP10.phx.gbl...
Hi,
I have a client with a database that is running over 1G in size. Looking at
the tables, I can't find any that would seem to be larger than a 1-2 meg
with total size should be around 30meg. Is there any way to find out table
sizes?
Thanks,
Mica|||Database size is a fuzzy thing. I don't know your level of SQL experience,
so forgive me if I start off with some basics.
What do you mean, when you say "over 1GB in size"? Right click on the
database in SQL Server Enterprise Manager (I'm assuming SQL 2000), and go to
properties. On the first tab ("General"), there's a "Size", and then a
"Space Available". Is this the 1GB you're talking about? What does the
"Space Available" have beside it?
If that is the size that you're talking about, that includes the log file as
well as the actual data file. Click on the next tab "Data Files", and see
how much space is allocated. Then go to the "Transaction Log" tab, and see
the same thing there. If the transaction log is huge, it may be as simple
as either adjusting your backup procedures, or changing the "Recovery Model"
on the "Options" tab.
The other thing that can happen is that SQL database grow automatically, but
the default settings do not let the databases shrink automatically. To
shrink a database, right click on the database, and go to "All Tasks", then
"Shrink Database". I seem to have the best luck with shrinking by clicking
on the "Files" button on the first form, then manually setting the desired
database file sizes by selecting first the data (.mdf) file, then the log
(.ldf) file. You likely have to either do a complete backup or change the
"Recovery Model" before you'll get a lot of space out of the log file.
To find out the actual size of a particular table, you can execute the
"sp_spaceused <tablename>" function in Query Analyzer. It will give you the
number of rows, reserved size, data size, index size, and unused size.
I hope this helped! I'm not a SQL expert, and I don't play one on TV. But
post back with your results and any new questions, if you need more
guidance.
Clint
"Mica Cooper" <Mica.Cooper@.removethis.aisus.com> wrote in message
news:eMQy0MrkFHA.1444@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have a client with a database that is running over 1G in size. Looking
> at the tables, I can't find any that would seem to be larger than a 1-2
> meg with total size should be around 30meg. Is there any way to find out
> table sizes?
> Thanks,
> Mica
>|||Mario,
That is COOL! In all the years I've used SQL Server I have never seen that.
Its exactly what I was looking for.
Mica
"Mario Aoun" <MarioAoun@.discussions.microsoft.com> wrote in message
news:8AD1DCE1-0E27-463B-BE9E-59D00D5137C2@.microsoft.com...[vbcol=seagreen]
> Hi Mica Cooper,
> To Check table sizes, Select the db name in the enterprise under
> Databases,
> In the Main Menu Click View and select Taskpad, you will get 3 tabs :
> General, Table Info and Wizards, Select Table Info and you'll see all the
> tables listed with rows count and size
> Mario Aoun
> "Mica Cooper" wrote:
>|||Clint,
I have already shrank the db and it didn't change the size. I am looking for
a problem becuase I know the db shouldn't be anywhere near that large and
this info should help. Mario's post was great. I have NEVER seen that before
and it told the story pretty quick.
So guess what? Look at the view told me what a dunce I am. The problem is
the log file is autmatically growing...1.3G. I should have seen that by
looking at the .LDF. Gotta go fix that now.
Thanks Guys,
Mica
"Clint" <nobody@.nowhere.non> wrote in message
news:O6TXngrkFHA.1044@.tk2msftngp13.phx.gbl...
> Database size is a fuzzy thing. I don't know your level of SQL
> experience, so forgive me if I start off with some basics.
> What do you mean, when you say "over 1GB in size"? Right click on the
> database in SQL Server Enterprise Manager (I'm assuming SQL 2000), and go
> to properties. On the first tab ("General"), there's a "Size", and then a
> "Space Available". Is this the 1GB you're talking about? What does the
> "Space Available" have beside it?
> If that is the size that you're talking about, that includes the log file
> as well as the actual data file. Click on the next tab "Data Files", and
> see how much space is allocated. Then go to the "Transaction Log" tab,
> and see the same thing there. If the transaction log is huge, it may be
> as simple as either adjusting your backup procedures, or changing the
> "Recovery Model" on the "Options" tab.
> The other thing that can happen is that SQL database grow automatically,
> but the default settings do not let the databases shrink automatically.
> To shrink a database, right click on the database, and go to "All Tasks",
> then "Shrink Database". I seem to have the best luck with shrinking by
> clicking on the "Files" button on the first form, then manually setting
> the desired database file sizes by selecting first the data (.mdf) file,
> then the log (.ldf) file. You likely have to either do a complete backup
> or change the "Recovery Model" before you'll get a lot of space out of the
> log file.
> To find out the actual size of a particular table, you can execute the
> "sp_spaceused <tablename>" function in Query Analyzer. It will give you
> the number of rows, reserved size, data size, index size, and unused size.
> I hope this helped! I'm not a SQL expert, and I don't play one on TV.
> But post back with your results and any new questions, if you need more
> guidance.
> Clint
> "Mica Cooper" <Mica.Cooper@.removethis.aisus.com> wrote in message
> news:eMQy0MrkFHA.1444@.TK2MSFTNGP10.phx.gbl...
>
I have a client with a database that is running over 1G in size. Looking at
the tables, I can't find any that would seem to be larger than a 1-2 meg
with total size should be around 30meg. Is there any way to find out table
sizes?
Thanks,
MicaHi Mica Cooper,
To Check table sizes, Select the db name in the enterprise under Databases,
In the Main Menu Click View and select Taskpad, you will get 3 tabs :
General, Table Info and Wizards, Select Table Info and you'll see all the
tables listed with rows count and size
Mario Aoun
"Mica Cooper" wrote:
> Hi,
> I have a client with a database that is running over 1G in size. Looking a
t
> the tables, I can't find any that would seem to be larger than a 1-2 meg
> with total size should be around 30meg. Is there any way to find out table
> sizes?
> Thanks,
> Mica
>
>|||Use sp_spaceused or see: http://vyaskn.tripod.com/sp_show_biggest_tables.htm
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Mica Cooper" <Mica.Cooper@.removethis.aisus.com> wrote in message
news:eMQy0MrkFHA.1444@.TK2MSFTNGP10.phx.gbl...
Hi,
I have a client with a database that is running over 1G in size. Looking at
the tables, I can't find any that would seem to be larger than a 1-2 meg
with total size should be around 30meg. Is there any way to find out table
sizes?
Thanks,
Mica|||Database size is a fuzzy thing. I don't know your level of SQL experience,
so forgive me if I start off with some basics.
What do you mean, when you say "over 1GB in size"? Right click on the
database in SQL Server Enterprise Manager (I'm assuming SQL 2000), and go to
properties. On the first tab ("General"), there's a "Size", and then a
"Space Available". Is this the 1GB you're talking about? What does the
"Space Available" have beside it?
If that is the size that you're talking about, that includes the log file as
well as the actual data file. Click on the next tab "Data Files", and see
how much space is allocated. Then go to the "Transaction Log" tab, and see
the same thing there. If the transaction log is huge, it may be as simple
as either adjusting your backup procedures, or changing the "Recovery Model"
on the "Options" tab.
The other thing that can happen is that SQL database grow automatically, but
the default settings do not let the databases shrink automatically. To
shrink a database, right click on the database, and go to "All Tasks", then
"Shrink Database". I seem to have the best luck with shrinking by clicking
on the "Files" button on the first form, then manually setting the desired
database file sizes by selecting first the data (.mdf) file, then the log
(.ldf) file. You likely have to either do a complete backup or change the
"Recovery Model" before you'll get a lot of space out of the log file.
To find out the actual size of a particular table, you can execute the
"sp_spaceused <tablename>" function in Query Analyzer. It will give you the
number of rows, reserved size, data size, index size, and unused size.
I hope this helped! I'm not a SQL expert, and I don't play one on TV. But
post back with your results and any new questions, if you need more
guidance.
Clint
"Mica Cooper" <Mica.Cooper@.removethis.aisus.com> wrote in message
news:eMQy0MrkFHA.1444@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have a client with a database that is running over 1G in size. Looking
> at the tables, I can't find any that would seem to be larger than a 1-2
> meg with total size should be around 30meg. Is there any way to find out
> table sizes?
> Thanks,
> Mica
>|||Mario,
That is COOL! In all the years I've used SQL Server I have never seen that.
Its exactly what I was looking for.
Mica
"Mario Aoun" <MarioAoun@.discussions.microsoft.com> wrote in message
news:8AD1DCE1-0E27-463B-BE9E-59D00D5137C2@.microsoft.com...[vbcol=seagreen]
> Hi Mica Cooper,
> To Check table sizes, Select the db name in the enterprise under
> Databases,
> In the Main Menu Click View and select Taskpad, you will get 3 tabs :
> General, Table Info and Wizards, Select Table Info and you'll see all the
> tables listed with rows count and size
> Mario Aoun
> "Mica Cooper" wrote:
>|||Clint,
I have already shrank the db and it didn't change the size. I am looking for
a problem becuase I know the db shouldn't be anywhere near that large and
this info should help. Mario's post was great. I have NEVER seen that before
and it told the story pretty quick.
So guess what? Look at the view told me what a dunce I am. The problem is
the log file is autmatically growing...1.3G. I should have seen that by
looking at the .LDF. Gotta go fix that now.
Thanks Guys,
Mica
"Clint" <nobody@.nowhere.non> wrote in message
news:O6TXngrkFHA.1044@.tk2msftngp13.phx.gbl...
> Database size is a fuzzy thing. I don't know your level of SQL
> experience, so forgive me if I start off with some basics.
> What do you mean, when you say "over 1GB in size"? Right click on the
> database in SQL Server Enterprise Manager (I'm assuming SQL 2000), and go
> to properties. On the first tab ("General"), there's a "Size", and then a
> "Space Available". Is this the 1GB you're talking about? What does the
> "Space Available" have beside it?
> If that is the size that you're talking about, that includes the log file
> as well as the actual data file. Click on the next tab "Data Files", and
> see how much space is allocated. Then go to the "Transaction Log" tab,
> and see the same thing there. If the transaction log is huge, it may be
> as simple as either adjusting your backup procedures, or changing the
> "Recovery Model" on the "Options" tab.
> The other thing that can happen is that SQL database grow automatically,
> but the default settings do not let the databases shrink automatically.
> To shrink a database, right click on the database, and go to "All Tasks",
> then "Shrink Database". I seem to have the best luck with shrinking by
> clicking on the "Files" button on the first form, then manually setting
> the desired database file sizes by selecting first the data (.mdf) file,
> then the log (.ldf) file. You likely have to either do a complete backup
> or change the "Recovery Model" before you'll get a lot of space out of the
> log file.
> To find out the actual size of a particular table, you can execute the
> "sp_spaceused <tablename>" function in Query Analyzer. It will give you
> the number of rows, reserved size, data size, index size, and unused size.
> I hope this helped! I'm not a SQL expert, and I don't play one on TV.
> But post back with your results and any new questions, if you need more
> guidance.
> Clint
> "Mica Cooper" <Mica.Cooper@.removethis.aisus.com> wrote in message
> news:eMQy0MrkFHA.1444@.TK2MSFTNGP10.phx.gbl...
>
Tuesday, March 27, 2012
Determine running size of memtoleave area
Hi group,
it there an easy way to determine the size of the MemToLeave area on a
running instance?
I know this is caculated at startup as
[max worker threads] * 0.5MB + [-g Memory]
but we want to verify that the instance is using our -g setting.
TIA,
We use VMSTAT.EXE to monitor the total amount free and xp_memory_size (which
comes with SQL Litespeed) to monitor the max contiguous region. I can't
remember where we got vmstat from but I think it was probably from PSS.
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Gurba" <gurbao@.hotmail.com> wrote in message
news:Xns969E84434E3CDgurbaohotmailcom@.129.250.171. 68...
> Hi group,
> it there an easy way to determine the size of the MemToLeave area on a
> running instance?
> I know this is caculated at startup as
> [max worker threads] * 0.5MB + [-g Memory]
> but we want to verify that the instance is using our -g setting.
> TIA,
|||Will finding max contig help me?
We are experiencing this message from time to time:
2005-07-26 16:49:41.52 spid63 WARNING: Failed to reserve contiguous
memory of Size= 131072.
2005-07-26 16:49:41.63 spid63 Buffer Distribution: Stolen=17899
Free=5 Procedures=161541
Inram=0 Dirty=17995 Kept=0
I/O=0, Latched=1980, Other=138372
2005-07-26 16:49:41.63 spid63 Buffer Counts: Commited=337792 Target=
337792 Hashed=158347
InternalReservation=2266 ExternalReservation=3009 Min
Free=1024
2005-07-26 16:49:41.63 spid63 Procedure Cache: TotalProcs=35140
TotalPages=161541 InUsePages=108631
2005-07-26 16:49:41.63 spid63 Dynamic Memory Manager: Stolen=176976
OS Reserved=10416
OS Committed=9749
OS In Use=8822
Query Plan=171722 Optimizer=0
General=12434
Utilities=1151 Connection=263
2005-07-26 16:49:41.63 spid63 Global Memory Objects: Resource=7963
Locks=105
SQLCache=3311 Replication=4
LockBytes=2 ServerGlobal=56
Xact=93
2005-07-26 16:49:41.63 spid63 Query Memory Manager: Grants=3
Waiting=0 Maximum=163539 Available=158066
For all I know, I may find the size of the memtoleave area in these
numbers?
Regards,
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in
news:eY2UaZVkFHA.1048@.tk2msftngp13.phx.gbl:
> We use VMSTAT.EXE to monitor the total amount free and xp_memory_size
> (which comes with SQL Litespeed) to monitor the max contiguous region.
> I can't remember where we got vmstat from but I think it was probably
> from PSS.
>
|||Indeed you can. Under dynamic memory manager you will find,
OS Reserved - Reserved for >64KB requests (This is memtoleave)
OS Committed - Memory that has been committed from memtoleave
OS In Use - Portion of OS Committed backing outstanding memory allocations
Check http://support.microsoft.com/?id=271624 for full reference.
it there an easy way to determine the size of the MemToLeave area on a
running instance?
I know this is caculated at startup as
[max worker threads] * 0.5MB + [-g Memory]
but we want to verify that the instance is using our -g setting.
TIA,
We use VMSTAT.EXE to monitor the total amount free and xp_memory_size (which
comes with SQL Litespeed) to monitor the max contiguous region. I can't
remember where we got vmstat from but I think it was probably from PSS.
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Gurba" <gurbao@.hotmail.com> wrote in message
news:Xns969E84434E3CDgurbaohotmailcom@.129.250.171. 68...
> Hi group,
> it there an easy way to determine the size of the MemToLeave area on a
> running instance?
> I know this is caculated at startup as
> [max worker threads] * 0.5MB + [-g Memory]
> but we want to verify that the instance is using our -g setting.
> TIA,
|||Will finding max contig help me?
We are experiencing this message from time to time:
2005-07-26 16:49:41.52 spid63 WARNING: Failed to reserve contiguous
memory of Size= 131072.
2005-07-26 16:49:41.63 spid63 Buffer Distribution: Stolen=17899
Free=5 Procedures=161541
Inram=0 Dirty=17995 Kept=0
I/O=0, Latched=1980, Other=138372
2005-07-26 16:49:41.63 spid63 Buffer Counts: Commited=337792 Target=
337792 Hashed=158347
InternalReservation=2266 ExternalReservation=3009 Min
Free=1024
2005-07-26 16:49:41.63 spid63 Procedure Cache: TotalProcs=35140
TotalPages=161541 InUsePages=108631
2005-07-26 16:49:41.63 spid63 Dynamic Memory Manager: Stolen=176976
OS Reserved=10416
OS Committed=9749
OS In Use=8822
Query Plan=171722 Optimizer=0
General=12434
Utilities=1151 Connection=263
2005-07-26 16:49:41.63 spid63 Global Memory Objects: Resource=7963
Locks=105
SQLCache=3311 Replication=4
LockBytes=2 ServerGlobal=56
Xact=93
2005-07-26 16:49:41.63 spid63 Query Memory Manager: Grants=3
Waiting=0 Maximum=163539 Available=158066
For all I know, I may find the size of the memtoleave area in these
numbers?
Regards,
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in
news:eY2UaZVkFHA.1048@.tk2msftngp13.phx.gbl:
> We use VMSTAT.EXE to monitor the total amount free and xp_memory_size
> (which comes with SQL Litespeed) to monitor the max contiguous region.
> I can't remember where we got vmstat from but I think it was probably
> from PSS.
>
|||Indeed you can. Under dynamic memory manager you will find,
OS Reserved - Reserved for >64KB requests (This is memtoleave)
OS Committed - Memory that has been committed from memtoleave
OS In Use - Portion of OS Committed backing outstanding memory allocations
Check http://support.microsoft.com/?id=271624 for full reference.
Determine running size of memtoleave area
Hi group,
it there an easy way to determine the size of the MemToLeave area on a
running instance?
I know this is caculated at startup as
[max worker threads] * 0.5MB + [-g Memory]
but we want to verify that the instance is using our -g setting.
TIA,We use VMSTAT.EXE to monitor the total amount free and xp_memory_size (which
comes with SQL Litespeed) to monitor the max contiguous region. I can't
remember where we got vmstat from but I think it was probably from PSS.
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Gurba" <gurbao@.hotmail.com> wrote in message
news:Xns969E84434E3CDgurbaohotmailcom@.12
9.250.171.68...
> Hi group,
> it there an easy way to determine the size of the MemToLeave area on a
> running instance?
> I know this is caculated at startup as
> [max worker threads] * 0.5MB + [-g Memory]
> but we want to verify that the instance is using our -g setting.
> TIA,|||Will finding max contig help me?
We are experiencing this message from time to time:
2005-07-26 16:49:41.52 spid63 WARNING: Failed to reserve contiguous
memory of Size= 131072.
2005-07-26 16:49:41.63 spid63 Buffer Distribution: Stolen=17899
Free=5 Procedures=161541
Inram=0 Dirty=17995 Kept=0
I/O=0, Latched=1980, Other=138372
2005-07-26 16:49:41.63 spid63 Buffer Counts: Commited=337792 Target=
337792 Hashed=158347
InternalReservation=2266 ExternalReservation=3009 Min
Free=1024
2005-07-26 16:49:41.63 spid63 Procedure Cache: TotalProcs=35140
TotalPages=161541 InUsePages=108631
2005-07-26 16:49:41.63 spid63 Dynamic Memory Manager: Stolen=176976
OS Reserved=10416
OS Committed=9749
OS In Use=8822
Query Plan=171722 Optimizer=0
General=12434
Utilities=1151 Connection=263
2005-07-26 16:49:41.63 spid63 Global Memory Objects: Resource=7963
Locks=105
SQLCache=3311 Replication=4
LockBytes=2 ServerGlobal=56
Xact=93
2005-07-26 16:49:41.63 spid63 Query Memory Manager: Grants=3
Waiting=0 Maximum=163539 Available=158066
For all I know, I may find the size of the memtoleave area in these
numbers?
Regards,
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in
news:eY2UaZVkFHA.1048@.tk2msftngp13.phx.gbl:
> We use VMSTAT.EXE to monitor the total amount free and xp_memory_size
> (which comes with SQL Litespeed) to monitor the max contiguous region.
> I can't remember where we got vmstat from but I think it was probably
> from PSS.
>|||Indeed you can. Under dynamic memory manager you will find,
OS Reserved - Reserved for >64KB requests (This is memtoleave)
OS Committed - Memory that has been committed from memtoleave
OS In Use - Portion of OS Committed backing outstanding memory allocations
Check http://support.microsoft.com/?id=271624 for full reference.sql
it there an easy way to determine the size of the MemToLeave area on a
running instance?
I know this is caculated at startup as
[max worker threads] * 0.5MB + [-g Memory]
but we want to verify that the instance is using our -g setting.
TIA,We use VMSTAT.EXE to monitor the total amount free and xp_memory_size (which
comes with SQL Litespeed) to monitor the max contiguous region. I can't
remember where we got vmstat from but I think it was probably from PSS.
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Gurba" <gurbao@.hotmail.com> wrote in message
news:Xns969E84434E3CDgurbaohotmailcom@.12
9.250.171.68...
> Hi group,
> it there an easy way to determine the size of the MemToLeave area on a
> running instance?
> I know this is caculated at startup as
> [max worker threads] * 0.5MB + [-g Memory]
> but we want to verify that the instance is using our -g setting.
> TIA,|||Will finding max contig help me?
We are experiencing this message from time to time:
2005-07-26 16:49:41.52 spid63 WARNING: Failed to reserve contiguous
memory of Size= 131072.
2005-07-26 16:49:41.63 spid63 Buffer Distribution: Stolen=17899
Free=5 Procedures=161541
Inram=0 Dirty=17995 Kept=0
I/O=0, Latched=1980, Other=138372
2005-07-26 16:49:41.63 spid63 Buffer Counts: Commited=337792 Target=
337792 Hashed=158347
InternalReservation=2266 ExternalReservation=3009 Min
Free=1024
2005-07-26 16:49:41.63 spid63 Procedure Cache: TotalProcs=35140
TotalPages=161541 InUsePages=108631
2005-07-26 16:49:41.63 spid63 Dynamic Memory Manager: Stolen=176976
OS Reserved=10416
OS Committed=9749
OS In Use=8822
Query Plan=171722 Optimizer=0
General=12434
Utilities=1151 Connection=263
2005-07-26 16:49:41.63 spid63 Global Memory Objects: Resource=7963
Locks=105
SQLCache=3311 Replication=4
LockBytes=2 ServerGlobal=56
Xact=93
2005-07-26 16:49:41.63 spid63 Query Memory Manager: Grants=3
Waiting=0 Maximum=163539 Available=158066
For all I know, I may find the size of the memtoleave area in these
numbers?
Regards,
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in
news:eY2UaZVkFHA.1048@.tk2msftngp13.phx.gbl:
> We use VMSTAT.EXE to monitor the total amount free and xp_memory_size
> (which comes with SQL Litespeed) to monitor the max contiguous region.
> I can't remember where we got vmstat from but I think it was probably
> from PSS.
>|||Indeed you can. Under dynamic memory manager you will find,
OS Reserved - Reserved for >64KB requests (This is memtoleave)
OS Committed - Memory that has been committed from memtoleave
OS In Use - Portion of OS Committed backing outstanding memory allocations
Check http://support.microsoft.com/?id=271624 for full reference.sql
Determine running size of memtoleave area
Hi group,
it there an easy way to determine the size of the MemToLeave area on a
running instance?
I know this is caculated at startup as
[max worker threads] * 0.5MB + [-g Memory]
but we want to verify that the instance is using our -g setting.
TIA,We use VMSTAT.EXE to monitor the total amount free and xp_memory_size (which
comes with SQL Litespeed) to monitor the max contiguous region. I can't
remember where we got vmstat from but I think it was probably from PSS.
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Gurba" <gurbao@.hotmail.com> wrote in message
news:Xns969E84434E3CDgurbaohotmailcom@.129.250.171.68...
> Hi group,
> it there an easy way to determine the size of the MemToLeave area on a
> running instance?
> I know this is caculated at startup as
> [max worker threads] * 0.5MB + [-g Memory]
> but we want to verify that the instance is using our -g setting.
> TIA,|||Will finding max contig help me?
We are experiencing this message from time to time:
2005-07-26 16:49:41.52 spid63 WARNING: Failed to reserve contiguous
memory of Size= 131072.
2005-07-26 16:49:41.63 spid63 Buffer Distribution: Stolen=17899
Free=5 Procedures=161541
Inram=0 Dirty=17995 Kept=0
I/O=0, Latched=1980, Other=138372
2005-07-26 16:49:41.63 spid63 Buffer Counts: Commited=337792 Target=337792 Hashed=158347
InternalReservation=2266 ExternalReservation=3009 Min
Free=1024
2005-07-26 16:49:41.63 spid63 Procedure Cache: TotalProcs=35140
TotalPages=161541 InUsePages=108631
2005-07-26 16:49:41.63 spid63 Dynamic Memory Manager: Stolen=176976
OS Reserved=10416
OS Committed=9749
OS In Use=8822
Query Plan=171722 Optimizer=0
General=12434
Utilities=1151 Connection=263
2005-07-26 16:49:41.63 spid63 Global Memory Objects: Resource=7963
Locks=105
SQLCache=3311 Replication=4
LockBytes=2 ServerGlobal=56
Xact=93
2005-07-26 16:49:41.63 spid63 Query Memory Manager: Grants=3
Waiting=0 Maximum=163539 Available=158066
For all I know, I may find the size of the memtoleave area in these
numbers?
Regards,
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in
news:eY2UaZVkFHA.1048@.tk2msftngp13.phx.gbl:
> We use VMSTAT.EXE to monitor the total amount free and xp_memory_size
> (which comes with SQL Litespeed) to monitor the max contiguous region.
> I can't remember where we got vmstat from but I think it was probably
> from PSS.
>
it there an easy way to determine the size of the MemToLeave area on a
running instance?
I know this is caculated at startup as
[max worker threads] * 0.5MB + [-g Memory]
but we want to verify that the instance is using our -g setting.
TIA,We use VMSTAT.EXE to monitor the total amount free and xp_memory_size (which
comes with SQL Litespeed) to monitor the max contiguous region. I can't
remember where we got vmstat from but I think it was probably from PSS.
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Gurba" <gurbao@.hotmail.com> wrote in message
news:Xns969E84434E3CDgurbaohotmailcom@.129.250.171.68...
> Hi group,
> it there an easy way to determine the size of the MemToLeave area on a
> running instance?
> I know this is caculated at startup as
> [max worker threads] * 0.5MB + [-g Memory]
> but we want to verify that the instance is using our -g setting.
> TIA,|||Will finding max contig help me?
We are experiencing this message from time to time:
2005-07-26 16:49:41.52 spid63 WARNING: Failed to reserve contiguous
memory of Size= 131072.
2005-07-26 16:49:41.63 spid63 Buffer Distribution: Stolen=17899
Free=5 Procedures=161541
Inram=0 Dirty=17995 Kept=0
I/O=0, Latched=1980, Other=138372
2005-07-26 16:49:41.63 spid63 Buffer Counts: Commited=337792 Target=337792 Hashed=158347
InternalReservation=2266 ExternalReservation=3009 Min
Free=1024
2005-07-26 16:49:41.63 spid63 Procedure Cache: TotalProcs=35140
TotalPages=161541 InUsePages=108631
2005-07-26 16:49:41.63 spid63 Dynamic Memory Manager: Stolen=176976
OS Reserved=10416
OS Committed=9749
OS In Use=8822
Query Plan=171722 Optimizer=0
General=12434
Utilities=1151 Connection=263
2005-07-26 16:49:41.63 spid63 Global Memory Objects: Resource=7963
Locks=105
SQLCache=3311 Replication=4
LockBytes=2 ServerGlobal=56
Xact=93
2005-07-26 16:49:41.63 spid63 Query Memory Manager: Grants=3
Waiting=0 Maximum=163539 Available=158066
For all I know, I may find the size of the memtoleave area in these
numbers?
Regards,
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in
news:eY2UaZVkFHA.1048@.tk2msftngp13.phx.gbl:
> We use VMSTAT.EXE to monitor the total amount free and xp_memory_size
> (which comes with SQL Litespeed) to monitor the max contiguous region.
> I can't remember where we got vmstat from but I think it was probably
> from PSS.
>
Determine Index Size
Can anyone help me figure out the size used by my indexes?
TIA
RobEXEC sp_spaceused
optional:
EXEC sp_spaceused '<table name>'
--
Jacco Schalkwijk
SQL Server MVP
"Rob Diamant" <zzjunk.robd@.usi.com> wrote in message
news:eBBqoa0uDHA.540@.tk2msftngp13.phx.gbl...
> Can anyone help me figure out the size used by my indexes?
> TIA
> Rob
>|||Use can query the "used" column in sysindexes table for Indid = 1 , indid
between 2 and 249
select object_name(id) as Table_name,sum(used) from sysindexes
where indid between 1 and 249
group by object_name(id)
Thanks
Hari
MCDBA
"Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
news:#b4gKg0uDHA.1756@.TK2MSFTNGP09.phx.gbl...
> EXEC sp_spaceused
> optional:
> EXEC sp_spaceused '<table name>'
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Rob Diamant" <zzjunk.robd@.usi.com> wrote in message
> news:eBBqoa0uDHA.540@.tk2msftngp13.phx.gbl...
> > Can anyone help me figure out the size used by my indexes?
> >
> > TIA
> >
> > Rob
> >
> >
>sql
TIA
RobEXEC sp_spaceused
optional:
EXEC sp_spaceused '<table name>'
--
Jacco Schalkwijk
SQL Server MVP
"Rob Diamant" <zzjunk.robd@.usi.com> wrote in message
news:eBBqoa0uDHA.540@.tk2msftngp13.phx.gbl...
> Can anyone help me figure out the size used by my indexes?
> TIA
> Rob
>|||Use can query the "used" column in sysindexes table for Indid = 1 , indid
between 2 and 249
select object_name(id) as Table_name,sum(used) from sysindexes
where indid between 1 and 249
group by object_name(id)
Thanks
Hari
MCDBA
"Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
news:#b4gKg0uDHA.1756@.TK2MSFTNGP09.phx.gbl...
> EXEC sp_spaceused
> optional:
> EXEC sp_spaceused '<table name>'
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Rob Diamant" <zzjunk.robd@.usi.com> wrote in message
> news:eBBqoa0uDHA.540@.tk2msftngp13.phx.gbl...
> > Can anyone help me figure out the size used by my indexes?
> >
> > TIA
> >
> > Rob
> >
> >
>sql
Sunday, March 25, 2012
Determine Disk Block Size ?
I have a SQL Server 2000 Enterprise database that processes 300
transactions/sec, approximately 300 users, and 250 GB in size. How can I
determine the the appropriate disk block size that should be on my system.
Thanks,
Unless you have an overriding reason, go with the hardware manufacturer's
recommendations. These are typically the default settings. Changing the
NTFS block size usually has no measurable effect on system performance.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:B77A9710-86AA-4319-B539-6F65D91E5ECA@.microsoft.com...
> I have a SQL Server 2000 Enterprise database that processes 300
> transactions/sec, approximately 300 users, and 250 GB in size. How can I
> determine the the appropriate disk block size that should be on my system.
> Thanks,
>
|||Hi
Rather make sure that your drives are configured correctly.
Make sure your logs on RAID-10, and preferably your data on a separate
RAID-10 volume. RAID-5 hurts more than it helps.
As Geoff said, block size is not important, but getting maximum IO
throughput is.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:uhfJBgXBFHA.2180@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> Unless you have an overriding reason, go with the hardware manufacturer's
> recommendations. These are typically the default settings. Changing the
> NTFS block size usually has no measurable effect on system performance.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
> news:B77A9710-86AA-4319-B539-6F65D91E5ECA@.microsoft.com...
I[vbcol=seagreen]
system.
>
|||Hi
Rather make sure that your drives are configured correctly.
Make sure your logs on RAID-10, and preferably your data on a separate
RAID-10 volume. RAID-5 hurts more than it helps.
As Geoff said, block size is not important, but getting maximum IO
throughput is.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:uhfJBgXBFHA.2180@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> Unless you have an overriding reason, go with the hardware manufacturer's
> recommendations. These are typically the default settings. Changing the
> NTFS block size usually has no measurable effect on system performance.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
> news:B77A9710-86AA-4319-B539-6F65D91E5ECA@.microsoft.com...
I[vbcol=seagreen]
system.
>
|||Not that I disagree with the RAID-10 argument, I don't; however, I do
disagree with your comments about NTFS Cluster Sizes (a.k.a., block size).
The MS SQL Server Operations Manual makes a recommendation of using 64-KB
cluster sizes. We have done so with a dramatic total disk I/O throughput.
It makes sense. SQL Server reads and writes data in 8KB data pages, but
issues Scatter-Gather I/O requests in 4 or more Extents (8 x 8KB pages)
simultaneously. If your block sizes are on these 8KB boundaries, SQL Server
Disk I/O requests can be more efficient.
Your biggest disk throughput bottleneck is total number of I/O Operations a
single spindle can handle. You're throughput will be based on type of
access, sequential versus random, and the size of each request. Optimizing
the request sizes coupled with a fixed maximum number of I/O Ops, should
make your system as efficient as possible.
Nevertheless, and along the same lines as the RAID arguments already
presented, this is also suggested from the Operations Manual: more smaller
disks are more efficient than fewer larger disks. This would come into play
in the 0 part of the RAID-10 argument. Striping allows more physical
spindles to fulfill requests, each one capable of servicing a theoretical
maximum number of I/O Ops. Furthermore, more files per Filegroup will spawn
additional disk threads per request. This coupled with number of disks can
greatly improve throughput.
Sincerely,
Anthony Thomas
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:uY2FhGsBFHA.3840@.tk2msftngp13.phx.gbl...
Hi
Rather make sure that your drives are configured correctly.
Make sure your logs on RAID-10, and preferably your data on a separate
RAID-10 volume. RAID-5 hurts more than it helps.
As Geoff said, block size is not important, but getting maximum IO
throughput is.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:uhfJBgXBFHA.2180@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> Unless you have an overriding reason, go with the hardware manufacturer's
> recommendations. These are typically the default settings. Changing the
> NTFS block size usually has no measurable effect on system performance.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
> news:B77A9710-86AA-4319-B539-6F65D91E5ECA@.microsoft.com...
I[vbcol=seagreen]
system.
>
|||An I agree with your comments except for the last one:[vbcol=seagreen]
Furthermore, more files per Filegroup will spawn
additional disk threads per request.
<<
That was true with 7.0 but is not necessarily true with SQL2000. In 2000
Sql Server can spawn multiple threads to read a single file as well. So
just having multiple files does not mean it will be more effecient or spawn
more threads than a single file.
Andrew J. Kelly SQL MVP
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:ep16rw1BFHA.3592@.TK2MSFTNGP09.phx.gbl...
> Not that I disagree with the RAID-10 argument, I don't; however, I do
> disagree with your comments about NTFS Cluster Sizes (a.k.a., block size).
> The MS SQL Server Operations Manual makes a recommendation of using 64-KB
> cluster sizes. We have done so with a dramatic total disk I/O throughput.
> It makes sense. SQL Server reads and writes data in 8KB data pages, but
> issues Scatter-Gather I/O requests in 4 or more Extents (8 x 8KB pages)
> simultaneously. If your block sizes are on these 8KB boundaries, SQL
> Server
> Disk I/O requests can be more efficient.
> Your biggest disk throughput bottleneck is total number of I/O Operations
> a
> single spindle can handle. You're throughput will be based on type of
> access, sequential versus random, and the size of each request.
> Optimizing
> the request sizes coupled with a fixed maximum number of I/O Ops, should
> make your system as efficient as possible.
> Nevertheless, and along the same lines as the RAID arguments already
> presented, this is also suggested from the Operations Manual: more smaller
> disks are more efficient than fewer larger disks. This would come into
> play
> in the 0 part of the RAID-10 argument. Striping allows more physical
> spindles to fulfill requests, each one capable of servicing a theoretical
> maximum number of I/O Ops. Furthermore, more files per Filegroup will
> spawn
> additional disk threads per request. This coupled with number of disks
> can
> greatly improve throughput.
> Sincerely,
>
> Anthony Thomas
>
> --
> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
> news:uY2FhGsBFHA.3840@.tk2msftngp13.phx.gbl...
> Hi
> Rather make sure that your drives are configured correctly.
> Make sure your logs on RAID-10, and preferably your data on a separate
> RAID-10 volume. RAID-5 hurts more than it helps.
> As Geoff said, block size is not important, but getting maximum IO
> throughput is.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
> news:uhfJBgXBFHA.2180@.TK2MSFTNGP12.phx.gbl...
> I
> system.
>
|||I just read that SS2K SE spawns Read Ahead requests in 4xExtents, 128 KB
chunks, and spawns a thread per data file in each Filegroup. It also
mentioned that EE can exceed the number of concurrent extents per request
and managed the thread spawning more efficiently. But, damn if I can't
remember where I came across the information.
Anthony Thomas
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uNjLEV5BFHA.2180@.TK2MSFTNGP10.phx.gbl...
An I agree with your comments except for the last one:[vbcol=seagreen]
Furthermore, more files per Filegroup will spawn
additional disk threads per request.
<<
That was true with 7.0 but is not necessarily true with SQL2000. In 2000
Sql Server can spawn multiple threads to read a single file as well. So
just having multiple files does not mean it will be more effecient or spawn
more threads than a single file.
Andrew J. Kelly SQL MVP
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:ep16rw1BFHA.3592@.TK2MSFTNGP09.phx.gbl...
> Not that I disagree with the RAID-10 argument, I don't; however, I do
> disagree with your comments about NTFS Cluster Sizes (a.k.a., block size).
> The MS SQL Server Operations Manual makes a recommendation of using 64-KB
> cluster sizes. We have done so with a dramatic total disk I/O throughput.
> It makes sense. SQL Server reads and writes data in 8KB data pages, but
> issues Scatter-Gather I/O requests in 4 or more Extents (8 x 8KB pages)
> simultaneously. If your block sizes are on these 8KB boundaries, SQL
> Server
> Disk I/O requests can be more efficient.
> Your biggest disk throughput bottleneck is total number of I/O Operations
> a
> single spindle can handle. You're throughput will be based on type of
> access, sequential versus random, and the size of each request.
> Optimizing
> the request sizes coupled with a fixed maximum number of I/O Ops, should
> make your system as efficient as possible.
> Nevertheless, and along the same lines as the RAID arguments already
> presented, this is also suggested from the Operations Manual: more smaller
> disks are more efficient than fewer larger disks. This would come into
> play
> in the 0 part of the RAID-10 argument. Striping allows more physical
> spindles to fulfill requests, each one capable of servicing a theoretical
> maximum number of I/O Ops. Furthermore, more files per Filegroup will
> spawn
> additional disk threads per request. This coupled with number of disks
> can
> greatly improve throughput.
> Sincerely,
>
> Anthony Thomas
>
> --
> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
> news:uY2FhGsBFHA.3840@.tk2msftngp13.phx.gbl...
> Hi
> Rather make sure that your drives are configured correctly.
> Make sure your logs on RAID-10, and preferably your data on a separate
> RAID-10 volume. RAID-5 hurts more than it helps.
> As Geoff said, block size is not important, but getting maximum IO
> throughput is.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
> news:uhfJBgXBFHA.2180@.TK2MSFTNGP12.phx.gbl...
> I
> system.
>
|||Not sure which document that came from either. The read ahead is actually
dynamic in the size of each read ahead request based somewhat on how
fragmented the data is. It can be 8K or 128K, it just depends on if it is
possible to do so or not. Most of the documentation for 2000 is wrong in
that it may state you need multiple files to spawn multiple threads. That
is definitely not the case and this is supposed to be fixed in the 2005
docs. Yes it can spawn a thread of each file but it is not limited to a
single thread per file. Even with a single file it is possible to spawn
multiple threads to read the same file. Here is what looks like a very
interesting article on IO in 2000. I haven't had a chance to read it yet
(hope to in the next few days) but it looks very promising.
http://www.microsoft.com/technet/pro...lIObasics.mspx
Andrew J. Kelly SQL MVP
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:u8xc256BFHA.1564@.TK2MSFTNGP09.phx.gbl...
>I just read that SS2K SE spawns Read Ahead requests in 4xExtents, 128 KB
> chunks, and spawns a thread per data file in each Filegroup. It also
> mentioned that EE can exceed the number of concurrent extents per request
> and managed the thread spawning more efficiently. But, damn if I can't
> remember where I came across the information.
> Anthony Thomas
>
> --
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uNjLEV5BFHA.2180@.TK2MSFTNGP10.phx.gbl...
> An I agree with your comments except for the last one:
> Furthermore, more files per Filegroup will spawn
> additional disk threads per request.
> <<
> That was true with 7.0 but is not necessarily true with SQL2000. In 2000
> Sql Server can spawn multiple threads to read a single file as well. So
> just having multiple files does not mean it will be more effecient or
> spawn
> more threads than a single file.
>
> --
> Andrew J. Kelly SQL MVP
>
> "Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
> news:ep16rw1BFHA.3592@.TK2MSFTNGP09.phx.gbl...
>
|||As always, thanks Andrew. Yes, I am well aware of having to "filter" read
information and make judgement calls on the sources reliability. The
article I read seemed respectable, though. If I come across it, I'll post a
"What's up" response.
Sincerely,
Anthony Thomas
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:ewn3Iz7BFHA.2260@.TK2MSFTNGP14.phx.gbl...
Not sure which document that came from either. The read ahead is actually
dynamic in the size of each read ahead request based somewhat on how
fragmented the data is. It can be 8K or 128K, it just depends on if it is
possible to do so or not. Most of the documentation for 2000 is wrong in
that it may state you need multiple files to spawn multiple threads. That
is definitely not the case and this is supposed to be fixed in the 2005
docs. Yes it can spawn a thread of each file but it is not limited to a
single thread per file. Even with a single file it is possible to spawn
multiple threads to read the same file. Here is what looks like a very
interesting article on IO in 2000. I haven't had a chance to read it yet
(hope to in the next few days) but it looks very promising.
http://www.microsoft.com/technet/pro...lIObasics.mspx
Andrew J. Kelly SQL MVP
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:u8xc256BFHA.1564@.TK2MSFTNGP09.phx.gbl...
>I just read that SS2K SE spawns Read Ahead requests in 4xExtents, 128 KB
> chunks, and spawns a thread per data file in each Filegroup. It also
> mentioned that EE can exceed the number of concurrent extents per request
> and managed the thread spawning more efficiently. But, damn if I can't
> remember where I came across the information.
> Anthony Thomas
>
> --
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uNjLEV5BFHA.2180@.TK2MSFTNGP10.phx.gbl...
> An I agree with your comments except for the last one:
> Furthermore, more files per Filegroup will spawn
> additional disk threads per request.
> <<
> That was true with 7.0 but is not necessarily true with SQL2000. In 2000
> Sql Server can spawn multiple threads to read a single file as well. So
> just having multiple files does not mean it will be more effecient or
> spawn
> more threads than a single file.
>
> --
> Andrew J. Kelly SQL MVP
>
> "Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
> news:ep16rw1BFHA.3592@.TK2MSFTNGP09.phx.gbl...
>
|||Well, that doc confirmed at least part of what I read previously:
"SQL Server limits a single read-ahead request depth to 128 pages on most
editions. However, Microsoft SQL Server Enterprise Edition raises the limit
to 1,024 pages."
Sincerely,
Anthony Thomas
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:OIoyclBCFHA.3588@.TK2MSFTNGP11.phx.gbl...
As always, thanks Andrew. Yes, I am well aware of having to "filter" read
information and make judgement calls on the sources reliability. The
article I read seemed respectable, though. If I come across it, I'll post a
"What's up" response.
Sincerely,
Anthony Thomas
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:ewn3Iz7BFHA.2260@.TK2MSFTNGP14.phx.gbl...
Not sure which document that came from either. The read ahead is actually
dynamic in the size of each read ahead request based somewhat on how
fragmented the data is. It can be 8K or 128K, it just depends on if it is
possible to do so or not. Most of the documentation for 2000 is wrong in
that it may state you need multiple files to spawn multiple threads. That
is definitely not the case and this is supposed to be fixed in the 2005
docs. Yes it can spawn a thread of each file but it is not limited to a
single thread per file. Even with a single file it is possible to spawn
multiple threads to read the same file. Here is what looks like a very
interesting article on IO in 2000. I haven't had a chance to read it yet
(hope to in the next few days) but it looks very promising.
http://www.microsoft.com/technet/pro...lIObasics.mspx
Andrew J. Kelly SQL MVP
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:u8xc256BFHA.1564@.TK2MSFTNGP09.phx.gbl...
>I just read that SS2K SE spawns Read Ahead requests in 4xExtents, 128 KB
> chunks, and spawns a thread per data file in each Filegroup. It also
> mentioned that EE can exceed the number of concurrent extents per request
> and managed the thread spawning more efficiently. But, damn if I can't
> remember where I came across the information.
> Anthony Thomas
>
> --
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uNjLEV5BFHA.2180@.TK2MSFTNGP10.phx.gbl...
> An I agree with your comments except for the last one:
> Furthermore, more files per Filegroup will spawn
> additional disk threads per request.
> <<
> That was true with 7.0 but is not necessarily true with SQL2000. In 2000
> Sql Server can spawn multiple threads to read a single file as well. So
> just having multiple files does not mean it will be more effecient or
> spawn
> more threads than a single file.
>
> --
> Andrew J. Kelly SQL MVP
>
> "Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
> news:ep16rw1BFHA.3592@.TK2MSFTNGP09.phx.gbl...
>
transactions/sec, approximately 300 users, and 250 GB in size. How can I
determine the the appropriate disk block size that should be on my system.
Thanks,
Unless you have an overriding reason, go with the hardware manufacturer's
recommendations. These are typically the default settings. Changing the
NTFS block size usually has no measurable effect on system performance.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:B77A9710-86AA-4319-B539-6F65D91E5ECA@.microsoft.com...
> I have a SQL Server 2000 Enterprise database that processes 300
> transactions/sec, approximately 300 users, and 250 GB in size. How can I
> determine the the appropriate disk block size that should be on my system.
> Thanks,
>
|||Hi
Rather make sure that your drives are configured correctly.
Make sure your logs on RAID-10, and preferably your data on a separate
RAID-10 volume. RAID-5 hurts more than it helps.
As Geoff said, block size is not important, but getting maximum IO
throughput is.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:uhfJBgXBFHA.2180@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> Unless you have an overriding reason, go with the hardware manufacturer's
> recommendations. These are typically the default settings. Changing the
> NTFS block size usually has no measurable effect on system performance.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
> news:B77A9710-86AA-4319-B539-6F65D91E5ECA@.microsoft.com...
I[vbcol=seagreen]
system.
>
|||Hi
Rather make sure that your drives are configured correctly.
Make sure your logs on RAID-10, and preferably your data on a separate
RAID-10 volume. RAID-5 hurts more than it helps.
As Geoff said, block size is not important, but getting maximum IO
throughput is.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:uhfJBgXBFHA.2180@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> Unless you have an overriding reason, go with the hardware manufacturer's
> recommendations. These are typically the default settings. Changing the
> NTFS block size usually has no measurable effect on system performance.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
> news:B77A9710-86AA-4319-B539-6F65D91E5ECA@.microsoft.com...
I[vbcol=seagreen]
system.
>
|||Not that I disagree with the RAID-10 argument, I don't; however, I do
disagree with your comments about NTFS Cluster Sizes (a.k.a., block size).
The MS SQL Server Operations Manual makes a recommendation of using 64-KB
cluster sizes. We have done so with a dramatic total disk I/O throughput.
It makes sense. SQL Server reads and writes data in 8KB data pages, but
issues Scatter-Gather I/O requests in 4 or more Extents (8 x 8KB pages)
simultaneously. If your block sizes are on these 8KB boundaries, SQL Server
Disk I/O requests can be more efficient.
Your biggest disk throughput bottleneck is total number of I/O Operations a
single spindle can handle. You're throughput will be based on type of
access, sequential versus random, and the size of each request. Optimizing
the request sizes coupled with a fixed maximum number of I/O Ops, should
make your system as efficient as possible.
Nevertheless, and along the same lines as the RAID arguments already
presented, this is also suggested from the Operations Manual: more smaller
disks are more efficient than fewer larger disks. This would come into play
in the 0 part of the RAID-10 argument. Striping allows more physical
spindles to fulfill requests, each one capable of servicing a theoretical
maximum number of I/O Ops. Furthermore, more files per Filegroup will spawn
additional disk threads per request. This coupled with number of disks can
greatly improve throughput.
Sincerely,
Anthony Thomas
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:uY2FhGsBFHA.3840@.tk2msftngp13.phx.gbl...
Hi
Rather make sure that your drives are configured correctly.
Make sure your logs on RAID-10, and preferably your data on a separate
RAID-10 volume. RAID-5 hurts more than it helps.
As Geoff said, block size is not important, but getting maximum IO
throughput is.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:uhfJBgXBFHA.2180@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> Unless you have an overriding reason, go with the hardware manufacturer's
> recommendations. These are typically the default settings. Changing the
> NTFS block size usually has no measurable effect on system performance.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
> news:B77A9710-86AA-4319-B539-6F65D91E5ECA@.microsoft.com...
I[vbcol=seagreen]
system.
>
|||An I agree with your comments except for the last one:[vbcol=seagreen]
Furthermore, more files per Filegroup will spawn
additional disk threads per request.
<<
That was true with 7.0 but is not necessarily true with SQL2000. In 2000
Sql Server can spawn multiple threads to read a single file as well. So
just having multiple files does not mean it will be more effecient or spawn
more threads than a single file.
Andrew J. Kelly SQL MVP
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:ep16rw1BFHA.3592@.TK2MSFTNGP09.phx.gbl...
> Not that I disagree with the RAID-10 argument, I don't; however, I do
> disagree with your comments about NTFS Cluster Sizes (a.k.a., block size).
> The MS SQL Server Operations Manual makes a recommendation of using 64-KB
> cluster sizes. We have done so with a dramatic total disk I/O throughput.
> It makes sense. SQL Server reads and writes data in 8KB data pages, but
> issues Scatter-Gather I/O requests in 4 or more Extents (8 x 8KB pages)
> simultaneously. If your block sizes are on these 8KB boundaries, SQL
> Server
> Disk I/O requests can be more efficient.
> Your biggest disk throughput bottleneck is total number of I/O Operations
> a
> single spindle can handle. You're throughput will be based on type of
> access, sequential versus random, and the size of each request.
> Optimizing
> the request sizes coupled with a fixed maximum number of I/O Ops, should
> make your system as efficient as possible.
> Nevertheless, and along the same lines as the RAID arguments already
> presented, this is also suggested from the Operations Manual: more smaller
> disks are more efficient than fewer larger disks. This would come into
> play
> in the 0 part of the RAID-10 argument. Striping allows more physical
> spindles to fulfill requests, each one capable of servicing a theoretical
> maximum number of I/O Ops. Furthermore, more files per Filegroup will
> spawn
> additional disk threads per request. This coupled with number of disks
> can
> greatly improve throughput.
> Sincerely,
>
> Anthony Thomas
>
> --
> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
> news:uY2FhGsBFHA.3840@.tk2msftngp13.phx.gbl...
> Hi
> Rather make sure that your drives are configured correctly.
> Make sure your logs on RAID-10, and preferably your data on a separate
> RAID-10 volume. RAID-5 hurts more than it helps.
> As Geoff said, block size is not important, but getting maximum IO
> throughput is.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
> news:uhfJBgXBFHA.2180@.TK2MSFTNGP12.phx.gbl...
> I
> system.
>
|||I just read that SS2K SE spawns Read Ahead requests in 4xExtents, 128 KB
chunks, and spawns a thread per data file in each Filegroup. It also
mentioned that EE can exceed the number of concurrent extents per request
and managed the thread spawning more efficiently. But, damn if I can't
remember where I came across the information.
Anthony Thomas
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uNjLEV5BFHA.2180@.TK2MSFTNGP10.phx.gbl...
An I agree with your comments except for the last one:[vbcol=seagreen]
Furthermore, more files per Filegroup will spawn
additional disk threads per request.
<<
That was true with 7.0 but is not necessarily true with SQL2000. In 2000
Sql Server can spawn multiple threads to read a single file as well. So
just having multiple files does not mean it will be more effecient or spawn
more threads than a single file.
Andrew J. Kelly SQL MVP
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:ep16rw1BFHA.3592@.TK2MSFTNGP09.phx.gbl...
> Not that I disagree with the RAID-10 argument, I don't; however, I do
> disagree with your comments about NTFS Cluster Sizes (a.k.a., block size).
> The MS SQL Server Operations Manual makes a recommendation of using 64-KB
> cluster sizes. We have done so with a dramatic total disk I/O throughput.
> It makes sense. SQL Server reads and writes data in 8KB data pages, but
> issues Scatter-Gather I/O requests in 4 or more Extents (8 x 8KB pages)
> simultaneously. If your block sizes are on these 8KB boundaries, SQL
> Server
> Disk I/O requests can be more efficient.
> Your biggest disk throughput bottleneck is total number of I/O Operations
> a
> single spindle can handle. You're throughput will be based on type of
> access, sequential versus random, and the size of each request.
> Optimizing
> the request sizes coupled with a fixed maximum number of I/O Ops, should
> make your system as efficient as possible.
> Nevertheless, and along the same lines as the RAID arguments already
> presented, this is also suggested from the Operations Manual: more smaller
> disks are more efficient than fewer larger disks. This would come into
> play
> in the 0 part of the RAID-10 argument. Striping allows more physical
> spindles to fulfill requests, each one capable of servicing a theoretical
> maximum number of I/O Ops. Furthermore, more files per Filegroup will
> spawn
> additional disk threads per request. This coupled with number of disks
> can
> greatly improve throughput.
> Sincerely,
>
> Anthony Thomas
>
> --
> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
> news:uY2FhGsBFHA.3840@.tk2msftngp13.phx.gbl...
> Hi
> Rather make sure that your drives are configured correctly.
> Make sure your logs on RAID-10, and preferably your data on a separate
> RAID-10 volume. RAID-5 hurts more than it helps.
> As Geoff said, block size is not important, but getting maximum IO
> throughput is.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
> news:uhfJBgXBFHA.2180@.TK2MSFTNGP12.phx.gbl...
> I
> system.
>
|||Not sure which document that came from either. The read ahead is actually
dynamic in the size of each read ahead request based somewhat on how
fragmented the data is. It can be 8K or 128K, it just depends on if it is
possible to do so or not. Most of the documentation for 2000 is wrong in
that it may state you need multiple files to spawn multiple threads. That
is definitely not the case and this is supposed to be fixed in the 2005
docs. Yes it can spawn a thread of each file but it is not limited to a
single thread per file. Even with a single file it is possible to spawn
multiple threads to read the same file. Here is what looks like a very
interesting article on IO in 2000. I haven't had a chance to read it yet
(hope to in the next few days) but it looks very promising.
http://www.microsoft.com/technet/pro...lIObasics.mspx
Andrew J. Kelly SQL MVP
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:u8xc256BFHA.1564@.TK2MSFTNGP09.phx.gbl...
>I just read that SS2K SE spawns Read Ahead requests in 4xExtents, 128 KB
> chunks, and spawns a thread per data file in each Filegroup. It also
> mentioned that EE can exceed the number of concurrent extents per request
> and managed the thread spawning more efficiently. But, damn if I can't
> remember where I came across the information.
> Anthony Thomas
>
> --
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uNjLEV5BFHA.2180@.TK2MSFTNGP10.phx.gbl...
> An I agree with your comments except for the last one:
> Furthermore, more files per Filegroup will spawn
> additional disk threads per request.
> <<
> That was true with 7.0 but is not necessarily true with SQL2000. In 2000
> Sql Server can spawn multiple threads to read a single file as well. So
> just having multiple files does not mean it will be more effecient or
> spawn
> more threads than a single file.
>
> --
> Andrew J. Kelly SQL MVP
>
> "Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
> news:ep16rw1BFHA.3592@.TK2MSFTNGP09.phx.gbl...
>
|||As always, thanks Andrew. Yes, I am well aware of having to "filter" read
information and make judgement calls on the sources reliability. The
article I read seemed respectable, though. If I come across it, I'll post a
"What's up" response.
Sincerely,
Anthony Thomas
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:ewn3Iz7BFHA.2260@.TK2MSFTNGP14.phx.gbl...
Not sure which document that came from either. The read ahead is actually
dynamic in the size of each read ahead request based somewhat on how
fragmented the data is. It can be 8K or 128K, it just depends on if it is
possible to do so or not. Most of the documentation for 2000 is wrong in
that it may state you need multiple files to spawn multiple threads. That
is definitely not the case and this is supposed to be fixed in the 2005
docs. Yes it can spawn a thread of each file but it is not limited to a
single thread per file. Even with a single file it is possible to spawn
multiple threads to read the same file. Here is what looks like a very
interesting article on IO in 2000. I haven't had a chance to read it yet
(hope to in the next few days) but it looks very promising.
http://www.microsoft.com/technet/pro...lIObasics.mspx
Andrew J. Kelly SQL MVP
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:u8xc256BFHA.1564@.TK2MSFTNGP09.phx.gbl...
>I just read that SS2K SE spawns Read Ahead requests in 4xExtents, 128 KB
> chunks, and spawns a thread per data file in each Filegroup. It also
> mentioned that EE can exceed the number of concurrent extents per request
> and managed the thread spawning more efficiently. But, damn if I can't
> remember where I came across the information.
> Anthony Thomas
>
> --
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uNjLEV5BFHA.2180@.TK2MSFTNGP10.phx.gbl...
> An I agree with your comments except for the last one:
> Furthermore, more files per Filegroup will spawn
> additional disk threads per request.
> <<
> That was true with 7.0 but is not necessarily true with SQL2000. In 2000
> Sql Server can spawn multiple threads to read a single file as well. So
> just having multiple files does not mean it will be more effecient or
> spawn
> more threads than a single file.
>
> --
> Andrew J. Kelly SQL MVP
>
> "Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
> news:ep16rw1BFHA.3592@.TK2MSFTNGP09.phx.gbl...
>
|||Well, that doc confirmed at least part of what I read previously:
"SQL Server limits a single read-ahead request depth to 128 pages on most
editions. However, Microsoft SQL Server Enterprise Edition raises the limit
to 1,024 pages."
Sincerely,
Anthony Thomas
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:OIoyclBCFHA.3588@.TK2MSFTNGP11.phx.gbl...
As always, thanks Andrew. Yes, I am well aware of having to "filter" read
information and make judgement calls on the sources reliability. The
article I read seemed respectable, though. If I come across it, I'll post a
"What's up" response.
Sincerely,
Anthony Thomas
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:ewn3Iz7BFHA.2260@.TK2MSFTNGP14.phx.gbl...
Not sure which document that came from either. The read ahead is actually
dynamic in the size of each read ahead request based somewhat on how
fragmented the data is. It can be 8K or 128K, it just depends on if it is
possible to do so or not. Most of the documentation for 2000 is wrong in
that it may state you need multiple files to spawn multiple threads. That
is definitely not the case and this is supposed to be fixed in the 2005
docs. Yes it can spawn a thread of each file but it is not limited to a
single thread per file. Even with a single file it is possible to spawn
multiple threads to read the same file. Here is what looks like a very
interesting article on IO in 2000. I haven't had a chance to read it yet
(hope to in the next few days) but it looks very promising.
http://www.microsoft.com/technet/pro...lIObasics.mspx
Andrew J. Kelly SQL MVP
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:u8xc256BFHA.1564@.TK2MSFTNGP09.phx.gbl...
>I just read that SS2K SE spawns Read Ahead requests in 4xExtents, 128 KB
> chunks, and spawns a thread per data file in each Filegroup. It also
> mentioned that EE can exceed the number of concurrent extents per request
> and managed the thread spawning more efficiently. But, damn if I can't
> remember where I came across the information.
> Anthony Thomas
>
> --
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uNjLEV5BFHA.2180@.TK2MSFTNGP10.phx.gbl...
> An I agree with your comments except for the last one:
> Furthermore, more files per Filegroup will spawn
> additional disk threads per request.
> <<
> That was true with 7.0 but is not necessarily true with SQL2000. In 2000
> Sql Server can spawn multiple threads to read a single file as well. So
> just having multiple files does not mean it will be more effecient or
> spawn
> more threads than a single file.
>
> --
> Andrew J. Kelly SQL MVP
>
> "Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
> news:ep16rw1BFHA.3592@.TK2MSFTNGP09.phx.gbl...
>
Determine Disk Block Size ?
I have a SQL Server 2000 Enterprise database that processes 300
transactions/sec, approximately 300 users, and 250 GB in size. How can I
determine the the appropriate disk block size that should be on my system.
Thanks,Unless you have an overriding reason, go with the hardware manufacturer's
recommendations. These are typically the default settings. Changing the
NTFS block size usually has no measurable effect on system performance.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:B77A9710-86AA-4319-B539-6F65D91E5ECA@.microsoft.com...
> I have a SQL Server 2000 Enterprise database that processes 300
> transactions/sec, approximately 300 users, and 250 GB in size. How can I
> determine the the appropriate disk block size that should be on my system.
> Thanks,
>|||Hi
Rather make sure that your drives are configured correctly.
Make sure your logs on RAID-10, and preferably your data on a separate
RAID-10 volume. RAID-5 hurts more than it helps.
As Geoff said, block size is not important, but getting maximum IO
throughput is.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:uhfJBgXBFHA.2180@.TK2MSFTNGP12.phx.gbl...
> Unless you have an overriding reason, go with the hardware manufacturer's
> recommendations. These are typically the default settings. Changing the
> NTFS block size usually has no measurable effect on system performance.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
> news:B77A9710-86AA-4319-B539-6F65D91E5ECA@.microsoft.com...
I[vbcol=seagreen]
system.[vbcol=seagreen]
>|||Hi
Rather make sure that your drives are configured correctly.
Make sure your logs on RAID-10, and preferably your data on a separate
RAID-10 volume. RAID-5 hurts more than it helps.
As Geoff said, block size is not important, but getting maximum IO
throughput is.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:uhfJBgXBFHA.2180@.TK2MSFTNGP12.phx.gbl...
> Unless you have an overriding reason, go with the hardware manufacturer's
> recommendations. These are typically the default settings. Changing the
> NTFS block size usually has no measurable effect on system performance.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
> news:B77A9710-86AA-4319-B539-6F65D91E5ECA@.microsoft.com...
I[vbcol=seagreen]
system.[vbcol=seagreen]
>|||Not that I disagree with the RAID-10 argument, I don't; however, I do
disagree with your comments about NTFS Cluster Sizes (a.k.a., block size).
The MS SQL Server Operations Manual makes a recommendation of using 64-KB
cluster sizes. We have done so with a dramatic total disk I/O throughput.
It makes sense. SQL Server reads and writes data in 8KB data pages, but
issues Scatter-Gather I/O requests in 4 or more Extents (8 x 8KB pages)
simultaneously. If your block sizes are on these 8KB boundaries, SQL Server
Disk I/O requests can be more efficient.
Your biggest disk throughput bottleneck is total number of I/O Operations a
single spindle can handle. You're throughput will be based on type of
access, sequential versus random, and the size of each request. Optimizing
the request sizes coupled with a fixed maximum number of I/O Ops, should
make your system as efficient as possible.
Nevertheless, and along the same lines as the RAID arguments already
presented, this is also suggested from the Operations Manual: more smaller
disks are more efficient than fewer larger disks. This would come into play
in the 0 part of the RAID-10 argument. Striping allows more physical
spindles to fulfill requests, each one capable of servicing a theoretical
maximum number of I/O Ops. Furthermore, more files per Filegroup will spawn
additional disk threads per request. This coupled with number of disks can
greatly improve throughput.
Sincerely,
Anthony Thomas
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:uY2FhGsBFHA.3840@.tk2msftngp13.phx.gbl...
Hi
Rather make sure that your drives are configured correctly.
Make sure your logs on RAID-10, and preferably your data on a separate
RAID-10 volume. RAID-5 hurts more than it helps.
As Geoff said, block size is not important, but getting maximum IO
throughput is.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:uhfJBgXBFHA.2180@.TK2MSFTNGP12.phx.gbl...
> Unless you have an overriding reason, go with the hardware manufacturer's
> recommendations. These are typically the default settings. Changing the
> NTFS block size usually has no measurable effect on system performance.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
> news:B77A9710-86AA-4319-B539-6F65D91E5ECA@.microsoft.com...
I[vbcol=seagreen]
system.[vbcol=seagreen]
>|||An I agree with your comments except for the last one:
Furthermore, more files per Filegroup will spawn
additional disk threads per request.
<<
That was true with 7.0 but is not necessarily true with SQL2000. In 2000
Sql Server can spawn multiple threads to read a single file as well. So
just having multiple files does not mean it will be more effecient or spawn
more threads than a single file.
Andrew J. Kelly SQL MVP
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:ep16rw1BFHA.3592@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Not that I disagree with the RAID-10 argument, I don't; however, I do
> disagree with your comments about NTFS Cluster Sizes (a.k.a., block size).
> The MS SQL Server Operations Manual makes a recommendation of using 64-KB
> cluster sizes. We have done so with a dramatic total disk I/O throughput.
> It makes sense. SQL Server reads and writes data in 8KB data pages, but
> issues Scatter-Gather I/O requests in 4 or more Extents (8 x 8KB pages)
> simultaneously. If your block sizes are on these 8KB boundaries, SQL
> Server
> Disk I/O requests can be more efficient.
> Your biggest disk throughput bottleneck is total number of I/O Operations
> a
> single spindle can handle. You're throughput will be based on type of
> access, sequential versus random, and the size of each request.
> Optimizing
> the request sizes coupled with a fixed maximum number of I/O Ops, should
> make your system as efficient as possible.
> Nevertheless, and along the same lines as the RAID arguments already
> presented, this is also suggested from the Operations Manual: more smaller
> disks are more efficient than fewer larger disks. This would come into
> play
> in the 0 part of the RAID-10 argument. Striping allows more physical
> spindles to fulfill requests, each one capable of servicing a theoretical
> maximum number of I/O Ops. Furthermore, more files per Filegroup will
> spawn
> additional disk threads per request. This coupled with number of disks
> can
> greatly improve throughput.
> Sincerely,
>
> Anthony Thomas
>
> --
> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
> news:uY2FhGsBFHA.3840@.tk2msftngp13.phx.gbl...
> Hi
> Rather make sure that your drives are configured correctly.
> Make sure your logs on RAID-10, and preferably your data on a separate
> RAID-10 volume. RAID-5 hurts more than it helps.
> As Geoff said, block size is not important, but getting maximum IO
> throughput is.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
> news:uhfJBgXBFHA.2180@.TK2MSFTNGP12.phx.gbl...
> I
> system.
>|||I just read that SS2K SE spawns Read Ahead requests in 4xExtents, 128 KB
chunks, and spawns a thread per data file in each Filegroup. It also
mentioned that EE can exceed the number of concurrent extents per request
and managed the thread spawning more efficiently. But, damn if I can't
remember where I came across the information.
Anthony Thomas
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uNjLEV5BFHA.2180@.TK2MSFTNGP10.phx.gbl...
An I agree with your comments except for the last one:
Furthermore, more files per Filegroup will spawn
additional disk threads per request.
<<
That was true with 7.0 but is not necessarily true with SQL2000. In 2000
Sql Server can spawn multiple threads to read a single file as well. So
just having multiple files does not mean it will be more effecient or spawn
more threads than a single file.
Andrew J. Kelly SQL MVP
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:ep16rw1BFHA.3592@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Not that I disagree with the RAID-10 argument, I don't; however, I do
> disagree with your comments about NTFS Cluster Sizes (a.k.a., block size).
> The MS SQL Server Operations Manual makes a recommendation of using 64-KB
> cluster sizes. We have done so with a dramatic total disk I/O throughput.
> It makes sense. SQL Server reads and writes data in 8KB data pages, but
> issues Scatter-Gather I/O requests in 4 or more Extents (8 x 8KB pages)
> simultaneously. If your block sizes are on these 8KB boundaries, SQL
> Server
> Disk I/O requests can be more efficient.
> Your biggest disk throughput bottleneck is total number of I/O Operations
> a
> single spindle can handle. You're throughput will be based on type of
> access, sequential versus random, and the size of each request.
> Optimizing
> the request sizes coupled with a fixed maximum number of I/O Ops, should
> make your system as efficient as possible.
> Nevertheless, and along the same lines as the RAID arguments already
> presented, this is also suggested from the Operations Manual: more smaller
> disks are more efficient than fewer larger disks. This would come into
> play
> in the 0 part of the RAID-10 argument. Striping allows more physical
> spindles to fulfill requests, each one capable of servicing a theoretical
> maximum number of I/O Ops. Furthermore, more files per Filegroup will
> spawn
> additional disk threads per request. This coupled with number of disks
> can
> greatly improve throughput.
> Sincerely,
>
> Anthony Thomas
>
> --
> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
> news:uY2FhGsBFHA.3840@.tk2msftngp13.phx.gbl...
> Hi
> Rather make sure that your drives are configured correctly.
> Make sure your logs on RAID-10, and preferably your data on a separate
> RAID-10 volume. RAID-5 hurts more than it helps.
> As Geoff said, block size is not important, but getting maximum IO
> throughput is.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
> news:uhfJBgXBFHA.2180@.TK2MSFTNGP12.phx.gbl...
> I
> system.
>|||Not sure which document that came from either. The read ahead is actually
dynamic in the size of each read ahead request based somewhat on how
fragmented the data is. It can be 8K or 128K, it just depends on if it is
possible to do so or not. Most of the documentation for 2000 is wrong in
that it may state you need multiple files to spawn multiple threads. That
is definitely not the case and this is supposed to be fixed in the 2005
docs. Yes it can spawn a thread of each file but it is not limited to a
single thread per file. Even with a single file it is possible to spawn
multiple threads to read the same file. Here is what looks like a very
interesting article on IO in 2000. I haven't had a chance to read it yet
(hope to in the next few days) but it looks very promising.
[url]http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx[/u
rl]
Andrew J. Kelly SQL MVP
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:u8xc256BFHA.1564@.TK2MSFTNGP09.phx.gbl...
>I just read that SS2K SE spawns Read Ahead requests in 4xExtents, 128 KB
> chunks, and spawns a thread per data file in each Filegroup. It also
> mentioned that EE can exceed the number of concurrent extents per request
> and managed the thread spawning more efficiently. But, damn if I can't
> remember where I came across the information.
> Anthony Thomas
>
> --
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uNjLEV5BFHA.2180@.TK2MSFTNGP10.phx.gbl...
> An I agree with your comments except for the last one:
> Furthermore, more files per Filegroup will spawn
> additional disk threads per request.
> <<
> That was true with 7.0 but is not necessarily true with SQL2000. In 2000
> Sql Server can spawn multiple threads to read a single file as well. So
> just having multiple files does not mean it will be more effecient or
> spawn
> more threads than a single file.
>
> --
> Andrew J. Kelly SQL MVP
>
> "Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
> news:ep16rw1BFHA.3592@.TK2MSFTNGP09.phx.gbl...
>|||As always, thanks Andrew. Yes, I am well aware of having to "filter" read
information and make judgement calls on the sources reliability. The
article I read seemed respectable, though. If I come across it, I'll post a
"What's up" response.
Sincerely,
Anthony Thomas
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:ewn3Iz7BFHA.2260@.TK2MSFTNGP14.phx.gbl...
Not sure which document that came from either. The read ahead is actually
dynamic in the size of each read ahead request based somewhat on how
fragmented the data is. It can be 8K or 128K, it just depends on if it is
possible to do so or not. Most of the documentation for 2000 is wrong in
that it may state you need multiple files to spawn multiple threads. That
is definitely not the case and this is supposed to be fixed in the 2005
docs. Yes it can spawn a thread of each file but it is not limited to a
single thread per file. Even with a single file it is possible to spawn
multiple threads to read the same file. Here is what looks like a very
interesting article on IO in 2000. I haven't had a chance to read it yet
(hope to in the next few days) but it looks very promising.
[url]http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx[/u
rl]
Andrew J. Kelly SQL MVP
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:u8xc256BFHA.1564@.TK2MSFTNGP09.phx.gbl...
>I just read that SS2K SE spawns Read Ahead requests in 4xExtents, 128 KB
> chunks, and spawns a thread per data file in each Filegroup. It also
> mentioned that EE can exceed the number of concurrent extents per request
> and managed the thread spawning more efficiently. But, damn if I can't
> remember where I came across the information.
> Anthony Thomas
>
> --
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uNjLEV5BFHA.2180@.TK2MSFTNGP10.phx.gbl...
> An I agree with your comments except for the last one:
> Furthermore, more files per Filegroup will spawn
> additional disk threads per request.
> <<
> That was true with 7.0 but is not necessarily true with SQL2000. In 2000
> Sql Server can spawn multiple threads to read a single file as well. So
> just having multiple files does not mean it will be more effecient or
> spawn
> more threads than a single file.
>
> --
> Andrew J. Kelly SQL MVP
>
> "Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
> news:ep16rw1BFHA.3592@.TK2MSFTNGP09.phx.gbl...
>|||Well, that doc confirmed at least part of what I read previously:
"SQL Server limits a single read-ahead request depth to 128 pages on most
editions. However, Microsoft SQL Server Enterprise Edition raises the limit
to 1,024 pages."
Sincerely,
Anthony Thomas
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:OIoyclBCFHA.3588@.TK2MSFTNGP11.phx.gbl...
As always, thanks Andrew. Yes, I am well aware of having to "filter" read
information and make judgement calls on the sources reliability. The
article I read seemed respectable, though. If I come across it, I'll post a
"What's up" response.
Sincerely,
Anthony Thomas
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:ewn3Iz7BFHA.2260@.TK2MSFTNGP14.phx.gbl...
Not sure which document that came from either. The read ahead is actually
dynamic in the size of each read ahead request based somewhat on how
fragmented the data is. It can be 8K or 128K, it just depends on if it is
possible to do so or not. Most of the documentation for 2000 is wrong in
that it may state you need multiple files to spawn multiple threads. That
is definitely not the case and this is supposed to be fixed in the 2005
docs. Yes it can spawn a thread of each file but it is not limited to a
single thread per file. Even with a single file it is possible to spawn
multiple threads to read the same file. Here is what looks like a very
interesting article on IO in 2000. I haven't had a chance to read it yet
(hope to in the next few days) but it looks very promising.
[url]http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx[/u
rl]
Andrew J. Kelly SQL MVP
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:u8xc256BFHA.1564@.TK2MSFTNGP09.phx.gbl...
>I just read that SS2K SE spawns Read Ahead requests in 4xExtents, 128 KB
> chunks, and spawns a thread per data file in each Filegroup. It also
> mentioned that EE can exceed the number of concurrent extents per request
> and managed the thread spawning more efficiently. But, damn if I can't
> remember where I came across the information.
> Anthony Thomas
>
> --
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uNjLEV5BFHA.2180@.TK2MSFTNGP10.phx.gbl...
> An I agree with your comments except for the last one:
> Furthermore, more files per Filegroup will spawn
> additional disk threads per request.
> <<
> That was true with 7.0 but is not necessarily true with SQL2000. In 2000
> Sql Server can spawn multiple threads to read a single file as well. So
> just having multiple files does not mean it will be more effecient or
> spawn
> more threads than a single file.
>
> --
> Andrew J. Kelly SQL MVP
>
> "Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
> news:ep16rw1BFHA.3592@.TK2MSFTNGP09.phx.gbl...
>
transactions/sec, approximately 300 users, and 250 GB in size. How can I
determine the the appropriate disk block size that should be on my system.
Thanks,Unless you have an overriding reason, go with the hardware manufacturer's
recommendations. These are typically the default settings. Changing the
NTFS block size usually has no measurable effect on system performance.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:B77A9710-86AA-4319-B539-6F65D91E5ECA@.microsoft.com...
> I have a SQL Server 2000 Enterprise database that processes 300
> transactions/sec, approximately 300 users, and 250 GB in size. How can I
> determine the the appropriate disk block size that should be on my system.
> Thanks,
>|||Hi
Rather make sure that your drives are configured correctly.
Make sure your logs on RAID-10, and preferably your data on a separate
RAID-10 volume. RAID-5 hurts more than it helps.
As Geoff said, block size is not important, but getting maximum IO
throughput is.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:uhfJBgXBFHA.2180@.TK2MSFTNGP12.phx.gbl...
> Unless you have an overriding reason, go with the hardware manufacturer's
> recommendations. These are typically the default settings. Changing the
> NTFS block size usually has no measurable effect on system performance.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
> news:B77A9710-86AA-4319-B539-6F65D91E5ECA@.microsoft.com...
I[vbcol=seagreen]
system.[vbcol=seagreen]
>|||Hi
Rather make sure that your drives are configured correctly.
Make sure your logs on RAID-10, and preferably your data on a separate
RAID-10 volume. RAID-5 hurts more than it helps.
As Geoff said, block size is not important, but getting maximum IO
throughput is.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:uhfJBgXBFHA.2180@.TK2MSFTNGP12.phx.gbl...
> Unless you have an overriding reason, go with the hardware manufacturer's
> recommendations. These are typically the default settings. Changing the
> NTFS block size usually has no measurable effect on system performance.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
> news:B77A9710-86AA-4319-B539-6F65D91E5ECA@.microsoft.com...
I[vbcol=seagreen]
system.[vbcol=seagreen]
>|||Not that I disagree with the RAID-10 argument, I don't; however, I do
disagree with your comments about NTFS Cluster Sizes (a.k.a., block size).
The MS SQL Server Operations Manual makes a recommendation of using 64-KB
cluster sizes. We have done so with a dramatic total disk I/O throughput.
It makes sense. SQL Server reads and writes data in 8KB data pages, but
issues Scatter-Gather I/O requests in 4 or more Extents (8 x 8KB pages)
simultaneously. If your block sizes are on these 8KB boundaries, SQL Server
Disk I/O requests can be more efficient.
Your biggest disk throughput bottleneck is total number of I/O Operations a
single spindle can handle. You're throughput will be based on type of
access, sequential versus random, and the size of each request. Optimizing
the request sizes coupled with a fixed maximum number of I/O Ops, should
make your system as efficient as possible.
Nevertheless, and along the same lines as the RAID arguments already
presented, this is also suggested from the Operations Manual: more smaller
disks are more efficient than fewer larger disks. This would come into play
in the 0 part of the RAID-10 argument. Striping allows more physical
spindles to fulfill requests, each one capable of servicing a theoretical
maximum number of I/O Ops. Furthermore, more files per Filegroup will spawn
additional disk threads per request. This coupled with number of disks can
greatly improve throughput.
Sincerely,
Anthony Thomas
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:uY2FhGsBFHA.3840@.tk2msftngp13.phx.gbl...
Hi
Rather make sure that your drives are configured correctly.
Make sure your logs on RAID-10, and preferably your data on a separate
RAID-10 volume. RAID-5 hurts more than it helps.
As Geoff said, block size is not important, but getting maximum IO
throughput is.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:uhfJBgXBFHA.2180@.TK2MSFTNGP12.phx.gbl...
> Unless you have an overriding reason, go with the hardware manufacturer's
> recommendations. These are typically the default settings. Changing the
> NTFS block size usually has no measurable effect on system performance.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
> news:B77A9710-86AA-4319-B539-6F65D91E5ECA@.microsoft.com...
I[vbcol=seagreen]
system.[vbcol=seagreen]
>|||An I agree with your comments except for the last one:
Furthermore, more files per Filegroup will spawn
additional disk threads per request.
<<
That was true with 7.0 but is not necessarily true with SQL2000. In 2000
Sql Server can spawn multiple threads to read a single file as well. So
just having multiple files does not mean it will be more effecient or spawn
more threads than a single file.
Andrew J. Kelly SQL MVP
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:ep16rw1BFHA.3592@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Not that I disagree with the RAID-10 argument, I don't; however, I do
> disagree with your comments about NTFS Cluster Sizes (a.k.a., block size).
> The MS SQL Server Operations Manual makes a recommendation of using 64-KB
> cluster sizes. We have done so with a dramatic total disk I/O throughput.
> It makes sense. SQL Server reads and writes data in 8KB data pages, but
> issues Scatter-Gather I/O requests in 4 or more Extents (8 x 8KB pages)
> simultaneously. If your block sizes are on these 8KB boundaries, SQL
> Server
> Disk I/O requests can be more efficient.
> Your biggest disk throughput bottleneck is total number of I/O Operations
> a
> single spindle can handle. You're throughput will be based on type of
> access, sequential versus random, and the size of each request.
> Optimizing
> the request sizes coupled with a fixed maximum number of I/O Ops, should
> make your system as efficient as possible.
> Nevertheless, and along the same lines as the RAID arguments already
> presented, this is also suggested from the Operations Manual: more smaller
> disks are more efficient than fewer larger disks. This would come into
> play
> in the 0 part of the RAID-10 argument. Striping allows more physical
> spindles to fulfill requests, each one capable of servicing a theoretical
> maximum number of I/O Ops. Furthermore, more files per Filegroup will
> spawn
> additional disk threads per request. This coupled with number of disks
> can
> greatly improve throughput.
> Sincerely,
>
> Anthony Thomas
>
> --
> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
> news:uY2FhGsBFHA.3840@.tk2msftngp13.phx.gbl...
> Hi
> Rather make sure that your drives are configured correctly.
> Make sure your logs on RAID-10, and preferably your data on a separate
> RAID-10 volume. RAID-5 hurts more than it helps.
> As Geoff said, block size is not important, but getting maximum IO
> throughput is.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
> news:uhfJBgXBFHA.2180@.TK2MSFTNGP12.phx.gbl...
> I
> system.
>|||I just read that SS2K SE spawns Read Ahead requests in 4xExtents, 128 KB
chunks, and spawns a thread per data file in each Filegroup. It also
mentioned that EE can exceed the number of concurrent extents per request
and managed the thread spawning more efficiently. But, damn if I can't
remember where I came across the information.
Anthony Thomas
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uNjLEV5BFHA.2180@.TK2MSFTNGP10.phx.gbl...
An I agree with your comments except for the last one:
Furthermore, more files per Filegroup will spawn
additional disk threads per request.
<<
That was true with 7.0 but is not necessarily true with SQL2000. In 2000
Sql Server can spawn multiple threads to read a single file as well. So
just having multiple files does not mean it will be more effecient or spawn
more threads than a single file.
Andrew J. Kelly SQL MVP
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:ep16rw1BFHA.3592@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Not that I disagree with the RAID-10 argument, I don't; however, I do
> disagree with your comments about NTFS Cluster Sizes (a.k.a., block size).
> The MS SQL Server Operations Manual makes a recommendation of using 64-KB
> cluster sizes. We have done so with a dramatic total disk I/O throughput.
> It makes sense. SQL Server reads and writes data in 8KB data pages, but
> issues Scatter-Gather I/O requests in 4 or more Extents (8 x 8KB pages)
> simultaneously. If your block sizes are on these 8KB boundaries, SQL
> Server
> Disk I/O requests can be more efficient.
> Your biggest disk throughput bottleneck is total number of I/O Operations
> a
> single spindle can handle. You're throughput will be based on type of
> access, sequential versus random, and the size of each request.
> Optimizing
> the request sizes coupled with a fixed maximum number of I/O Ops, should
> make your system as efficient as possible.
> Nevertheless, and along the same lines as the RAID arguments already
> presented, this is also suggested from the Operations Manual: more smaller
> disks are more efficient than fewer larger disks. This would come into
> play
> in the 0 part of the RAID-10 argument. Striping allows more physical
> spindles to fulfill requests, each one capable of servicing a theoretical
> maximum number of I/O Ops. Furthermore, more files per Filegroup will
> spawn
> additional disk threads per request. This coupled with number of disks
> can
> greatly improve throughput.
> Sincerely,
>
> Anthony Thomas
>
> --
> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
> news:uY2FhGsBFHA.3840@.tk2msftngp13.phx.gbl...
> Hi
> Rather make sure that your drives are configured correctly.
> Make sure your logs on RAID-10, and preferably your data on a separate
> RAID-10 volume. RAID-5 hurts more than it helps.
> As Geoff said, block size is not important, but getting maximum IO
> throughput is.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
> news:uhfJBgXBFHA.2180@.TK2MSFTNGP12.phx.gbl...
> I
> system.
>|||Not sure which document that came from either. The read ahead is actually
dynamic in the size of each read ahead request based somewhat on how
fragmented the data is. It can be 8K or 128K, it just depends on if it is
possible to do so or not. Most of the documentation for 2000 is wrong in
that it may state you need multiple files to spawn multiple threads. That
is definitely not the case and this is supposed to be fixed in the 2005
docs. Yes it can spawn a thread of each file but it is not limited to a
single thread per file. Even with a single file it is possible to spawn
multiple threads to read the same file. Here is what looks like a very
interesting article on IO in 2000. I haven't had a chance to read it yet
(hope to in the next few days) but it looks very promising.
[url]http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx[/u
rl]
Andrew J. Kelly SQL MVP
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:u8xc256BFHA.1564@.TK2MSFTNGP09.phx.gbl...
>I just read that SS2K SE spawns Read Ahead requests in 4xExtents, 128 KB
> chunks, and spawns a thread per data file in each Filegroup. It also
> mentioned that EE can exceed the number of concurrent extents per request
> and managed the thread spawning more efficiently. But, damn if I can't
> remember where I came across the information.
> Anthony Thomas
>
> --
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uNjLEV5BFHA.2180@.TK2MSFTNGP10.phx.gbl...
> An I agree with your comments except for the last one:
> Furthermore, more files per Filegroup will spawn
> additional disk threads per request.
> <<
> That was true with 7.0 but is not necessarily true with SQL2000. In 2000
> Sql Server can spawn multiple threads to read a single file as well. So
> just having multiple files does not mean it will be more effecient or
> spawn
> more threads than a single file.
>
> --
> Andrew J. Kelly SQL MVP
>
> "Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
> news:ep16rw1BFHA.3592@.TK2MSFTNGP09.phx.gbl...
>|||As always, thanks Andrew. Yes, I am well aware of having to "filter" read
information and make judgement calls on the sources reliability. The
article I read seemed respectable, though. If I come across it, I'll post a
"What's up" response.
Sincerely,
Anthony Thomas
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:ewn3Iz7BFHA.2260@.TK2MSFTNGP14.phx.gbl...
Not sure which document that came from either. The read ahead is actually
dynamic in the size of each read ahead request based somewhat on how
fragmented the data is. It can be 8K or 128K, it just depends on if it is
possible to do so or not. Most of the documentation for 2000 is wrong in
that it may state you need multiple files to spawn multiple threads. That
is definitely not the case and this is supposed to be fixed in the 2005
docs. Yes it can spawn a thread of each file but it is not limited to a
single thread per file. Even with a single file it is possible to spawn
multiple threads to read the same file. Here is what looks like a very
interesting article on IO in 2000. I haven't had a chance to read it yet
(hope to in the next few days) but it looks very promising.
[url]http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx[/u
rl]
Andrew J. Kelly SQL MVP
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:u8xc256BFHA.1564@.TK2MSFTNGP09.phx.gbl...
>I just read that SS2K SE spawns Read Ahead requests in 4xExtents, 128 KB
> chunks, and spawns a thread per data file in each Filegroup. It also
> mentioned that EE can exceed the number of concurrent extents per request
> and managed the thread spawning more efficiently. But, damn if I can't
> remember where I came across the information.
> Anthony Thomas
>
> --
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uNjLEV5BFHA.2180@.TK2MSFTNGP10.phx.gbl...
> An I agree with your comments except for the last one:
> Furthermore, more files per Filegroup will spawn
> additional disk threads per request.
> <<
> That was true with 7.0 but is not necessarily true with SQL2000. In 2000
> Sql Server can spawn multiple threads to read a single file as well. So
> just having multiple files does not mean it will be more effecient or
> spawn
> more threads than a single file.
>
> --
> Andrew J. Kelly SQL MVP
>
> "Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
> news:ep16rw1BFHA.3592@.TK2MSFTNGP09.phx.gbl...
>|||Well, that doc confirmed at least part of what I read previously:
"SQL Server limits a single read-ahead request depth to 128 pages on most
editions. However, Microsoft SQL Server Enterprise Edition raises the limit
to 1,024 pages."
Sincerely,
Anthony Thomas
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:OIoyclBCFHA.3588@.TK2MSFTNGP11.phx.gbl...
As always, thanks Andrew. Yes, I am well aware of having to "filter" read
information and make judgement calls on the sources reliability. The
article I read seemed respectable, though. If I come across it, I'll post a
"What's up" response.
Sincerely,
Anthony Thomas
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:ewn3Iz7BFHA.2260@.TK2MSFTNGP14.phx.gbl...
Not sure which document that came from either. The read ahead is actually
dynamic in the size of each read ahead request based somewhat on how
fragmented the data is. It can be 8K or 128K, it just depends on if it is
possible to do so or not. Most of the documentation for 2000 is wrong in
that it may state you need multiple files to spawn multiple threads. That
is definitely not the case and this is supposed to be fixed in the 2005
docs. Yes it can spawn a thread of each file but it is not limited to a
single thread per file. Even with a single file it is possible to spawn
multiple threads to read the same file. Here is what looks like a very
interesting article on IO in 2000. I haven't had a chance to read it yet
(hope to in the next few days) but it looks very promising.
[url]http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx[/u
rl]
Andrew J. Kelly SQL MVP
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:u8xc256BFHA.1564@.TK2MSFTNGP09.phx.gbl...
>I just read that SS2K SE spawns Read Ahead requests in 4xExtents, 128 KB
> chunks, and spawns a thread per data file in each Filegroup. It also
> mentioned that EE can exceed the number of concurrent extents per request
> and managed the thread spawning more efficiently. But, damn if I can't
> remember where I came across the information.
> Anthony Thomas
>
> --
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uNjLEV5BFHA.2180@.TK2MSFTNGP10.phx.gbl...
> An I agree with your comments except for the last one:
> Furthermore, more files per Filegroup will spawn
> additional disk threads per request.
> <<
> That was true with 7.0 but is not necessarily true with SQL2000. In 2000
> Sql Server can spawn multiple threads to read a single file as well. So
> just having multiple files does not mean it will be more effecient or
> spawn
> more threads than a single file.
>
> --
> Andrew J. Kelly SQL MVP
>
> "Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
> news:ep16rw1BFHA.3592@.TK2MSFTNGP09.phx.gbl...
>
Determine Disk Block Size ?
I have a SQL Server 2000 Enterprise database that processes 300
transactions/sec, approximately 300 users, and 250 GB in size. How can I
determine the the appropriate disk block size that should be on my system.
Thanks,Unless you have an overriding reason, go with the hardware manufacturer's
recommendations. These are typically the default settings. Changing the
NTFS block size usually has no measurable effect on system performance.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:B77A9710-86AA-4319-B539-6F65D91E5ECA@.microsoft.com...
> I have a SQL Server 2000 Enterprise database that processes 300
> transactions/sec, approximately 300 users, and 250 GB in size. How can I
> determine the the appropriate disk block size that should be on my system.
> Thanks,
>|||Hi
Rather make sure that your drives are configured correctly.
Make sure your logs on RAID-10, and preferably your data on a separate
RAID-10 volume. RAID-5 hurts more than it helps.
As Geoff said, block size is not important, but getting maximum IO
throughput is.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:uhfJBgXBFHA.2180@.TK2MSFTNGP12.phx.gbl...
> Unless you have an overriding reason, go with the hardware manufacturer's
> recommendations. These are typically the default settings. Changing the
> NTFS block size usually has no measurable effect on system performance.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
> news:B77A9710-86AA-4319-B539-6F65D91E5ECA@.microsoft.com...
> >
> > I have a SQL Server 2000 Enterprise database that processes 300
> > transactions/sec, approximately 300 users, and 250 GB in size. How can
I
> > determine the the appropriate disk block size that should be on my
system.
> >
> > Thanks,
> >
> >
>|||Hi
Rather make sure that your drives are configured correctly.
Make sure your logs on RAID-10, and preferably your data on a separate
RAID-10 volume. RAID-5 hurts more than it helps.
As Geoff said, block size is not important, but getting maximum IO
throughput is.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:uhfJBgXBFHA.2180@.TK2MSFTNGP12.phx.gbl...
> Unless you have an overriding reason, go with the hardware manufacturer's
> recommendations. These are typically the default settings. Changing the
> NTFS block size usually has no measurable effect on system performance.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
> news:B77A9710-86AA-4319-B539-6F65D91E5ECA@.microsoft.com...
> >
> > I have a SQL Server 2000 Enterprise database that processes 300
> > transactions/sec, approximately 300 users, and 250 GB in size. How can
I
> > determine the the appropriate disk block size that should be on my
system.
> >
> > Thanks,
> >
> >
>|||Not that I disagree with the RAID-10 argument, I don't; however, I do
disagree with your comments about NTFS Cluster Sizes (a.k.a., block size).
The MS SQL Server Operations Manual makes a recommendation of using 64-KB
cluster sizes. We have done so with a dramatic total disk I/O throughput.
It makes sense. SQL Server reads and writes data in 8KB data pages, but
issues Scatter-Gather I/O requests in 4 or more Extents (8 x 8KB pages)
simultaneously. If your block sizes are on these 8KB boundaries, SQL Server
Disk I/O requests can be more efficient.
Your biggest disk throughput bottleneck is total number of I/O Operations a
single spindle can handle. You're throughput will be based on type of
access, sequential versus random, and the size of each request. Optimizing
the request sizes coupled with a fixed maximum number of I/O Ops, should
make your system as efficient as possible.
Nevertheless, and along the same lines as the RAID arguments already
presented, this is also suggested from the Operations Manual: more smaller
disks are more efficient than fewer larger disks. This would come into play
in the 0 part of the RAID-10 argument. Striping allows more physical
spindles to fulfill requests, each one capable of servicing a theoretical
maximum number of I/O Ops. Furthermore, more files per Filegroup will spawn
additional disk threads per request. This coupled with number of disks can
greatly improve throughput.
Sincerely,
Anthony Thomas
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:uY2FhGsBFHA.3840@.tk2msftngp13.phx.gbl...
Hi
Rather make sure that your drives are configured correctly.
Make sure your logs on RAID-10, and preferably your data on a separate
RAID-10 volume. RAID-5 hurts more than it helps.
As Geoff said, block size is not important, but getting maximum IO
throughput is.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:uhfJBgXBFHA.2180@.TK2MSFTNGP12.phx.gbl...
> Unless you have an overriding reason, go with the hardware manufacturer's
> recommendations. These are typically the default settings. Changing the
> NTFS block size usually has no measurable effect on system performance.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
> news:B77A9710-86AA-4319-B539-6F65D91E5ECA@.microsoft.com...
> >
> > I have a SQL Server 2000 Enterprise database that processes 300
> > transactions/sec, approximately 300 users, and 250 GB in size. How can
I
> > determine the the appropriate disk block size that should be on my
system.
> >
> > Thanks,
> >
> >
>|||An I agree with your comments except for the last one:
Furthermore, more files per Filegroup will spawn
additional disk threads per request.
<<
That was true with 7.0 but is not necessarily true with SQL2000. In 2000
Sql Server can spawn multiple threads to read a single file as well. So
just having multiple files does not mean it will be more effecient or spawn
more threads than a single file.
Andrew J. Kelly SQL MVP
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:ep16rw1BFHA.3592@.TK2MSFTNGP09.phx.gbl...
> Not that I disagree with the RAID-10 argument, I don't; however, I do
> disagree with your comments about NTFS Cluster Sizes (a.k.a., block size).
> The MS SQL Server Operations Manual makes a recommendation of using 64-KB
> cluster sizes. We have done so with a dramatic total disk I/O throughput.
> It makes sense. SQL Server reads and writes data in 8KB data pages, but
> issues Scatter-Gather I/O requests in 4 or more Extents (8 x 8KB pages)
> simultaneously. If your block sizes are on these 8KB boundaries, SQL
> Server
> Disk I/O requests can be more efficient.
> Your biggest disk throughput bottleneck is total number of I/O Operations
> a
> single spindle can handle. You're throughput will be based on type of
> access, sequential versus random, and the size of each request.
> Optimizing
> the request sizes coupled with a fixed maximum number of I/O Ops, should
> make your system as efficient as possible.
> Nevertheless, and along the same lines as the RAID arguments already
> presented, this is also suggested from the Operations Manual: more smaller
> disks are more efficient than fewer larger disks. This would come into
> play
> in the 0 part of the RAID-10 argument. Striping allows more physical
> spindles to fulfill requests, each one capable of servicing a theoretical
> maximum number of I/O Ops. Furthermore, more files per Filegroup will
> spawn
> additional disk threads per request. This coupled with number of disks
> can
> greatly improve throughput.
> Sincerely,
>
> Anthony Thomas
>
> --
> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
> news:uY2FhGsBFHA.3840@.tk2msftngp13.phx.gbl...
> Hi
> Rather make sure that your drives are configured correctly.
> Make sure your logs on RAID-10, and preferably your data on a separate
> RAID-10 volume. RAID-5 hurts more than it helps.
> As Geoff said, block size is not important, but getting maximum IO
> throughput is.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
> news:uhfJBgXBFHA.2180@.TK2MSFTNGP12.phx.gbl...
>> Unless you have an overriding reason, go with the hardware manufacturer's
>> recommendations. These are typically the default settings. Changing the
>> NTFS block size usually has no measurable effect on system performance.
>> --
>> Geoff N. Hiten
>> Microsoft SQL Server MVP
>> Senior Database Administrator
>> Careerbuilder.com
>> I support the Professional Association for SQL Server
>> www.sqlpass.org
>> "Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
>> news:B77A9710-86AA-4319-B539-6F65D91E5ECA@.microsoft.com...
>> >
>> > I have a SQL Server 2000 Enterprise database that processes 300
>> > transactions/sec, approximately 300 users, and 250 GB in size. How can
> I
>> > determine the the appropriate disk block size that should be on my
> system.
>> >
>> > Thanks,
>> >
>> >
>>
>|||I just read that SS2K SE spawns Read Ahead requests in 4xExtents, 128 KB
chunks, and spawns a thread per data file in each Filegroup. It also
mentioned that EE can exceed the number of concurrent extents per request
and managed the thread spawning more efficiently. But, damn if I can't
remember where I came across the information.
Anthony Thomas
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uNjLEV5BFHA.2180@.TK2MSFTNGP10.phx.gbl...
An I agree with your comments except for the last one:
Furthermore, more files per Filegroup will spawn
additional disk threads per request.
<<
That was true with 7.0 but is not necessarily true with SQL2000. In 2000
Sql Server can spawn multiple threads to read a single file as well. So
just having multiple files does not mean it will be more effecient or spawn
more threads than a single file.
Andrew J. Kelly SQL MVP
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:ep16rw1BFHA.3592@.TK2MSFTNGP09.phx.gbl...
> Not that I disagree with the RAID-10 argument, I don't; however, I do
> disagree with your comments about NTFS Cluster Sizes (a.k.a., block size).
> The MS SQL Server Operations Manual makes a recommendation of using 64-KB
> cluster sizes. We have done so with a dramatic total disk I/O throughput.
> It makes sense. SQL Server reads and writes data in 8KB data pages, but
> issues Scatter-Gather I/O requests in 4 or more Extents (8 x 8KB pages)
> simultaneously. If your block sizes are on these 8KB boundaries, SQL
> Server
> Disk I/O requests can be more efficient.
> Your biggest disk throughput bottleneck is total number of I/O Operations
> a
> single spindle can handle. You're throughput will be based on type of
> access, sequential versus random, and the size of each request.
> Optimizing
> the request sizes coupled with a fixed maximum number of I/O Ops, should
> make your system as efficient as possible.
> Nevertheless, and along the same lines as the RAID arguments already
> presented, this is also suggested from the Operations Manual: more smaller
> disks are more efficient than fewer larger disks. This would come into
> play
> in the 0 part of the RAID-10 argument. Striping allows more physical
> spindles to fulfill requests, each one capable of servicing a theoretical
> maximum number of I/O Ops. Furthermore, more files per Filegroup will
> spawn
> additional disk threads per request. This coupled with number of disks
> can
> greatly improve throughput.
> Sincerely,
>
> Anthony Thomas
>
> --
> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
> news:uY2FhGsBFHA.3840@.tk2msftngp13.phx.gbl...
> Hi
> Rather make sure that your drives are configured correctly.
> Make sure your logs on RAID-10, and preferably your data on a separate
> RAID-10 volume. RAID-5 hurts more than it helps.
> As Geoff said, block size is not important, but getting maximum IO
> throughput is.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
> news:uhfJBgXBFHA.2180@.TK2MSFTNGP12.phx.gbl...
>> Unless you have an overriding reason, go with the hardware manufacturer's
>> recommendations. These are typically the default settings. Changing the
>> NTFS block size usually has no measurable effect on system performance.
>> --
>> Geoff N. Hiten
>> Microsoft SQL Server MVP
>> Senior Database Administrator
>> Careerbuilder.com
>> I support the Professional Association for SQL Server
>> www.sqlpass.org
>> "Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
>> news:B77A9710-86AA-4319-B539-6F65D91E5ECA@.microsoft.com...
>> >
>> > I have a SQL Server 2000 Enterprise database that processes 300
>> > transactions/sec, approximately 300 users, and 250 GB in size. How can
> I
>> > determine the the appropriate disk block size that should be on my
> system.
>> >
>> > Thanks,
>> >
>> >
>>
>|||Not sure which document that came from either. The read ahead is actually
dynamic in the size of each read ahead request based somewhat on how
fragmented the data is. It can be 8K or 128K, it just depends on if it is
possible to do so or not. Most of the documentation for 2000 is wrong in
that it may state you need multiple files to spawn multiple threads. That
is definitely not the case and this is supposed to be fixed in the 2005
docs. Yes it can spawn a thread of each file but it is not limited to a
single thread per file. Even with a single file it is possible to spawn
multiple threads to read the same file. Here is what looks like a very
interesting article on IO in 2000. I haven't had a chance to read it yet
(hope to in the next few days) but it looks very promising.
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx
--
Andrew J. Kelly SQL MVP
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:u8xc256BFHA.1564@.TK2MSFTNGP09.phx.gbl...
>I just read that SS2K SE spawns Read Ahead requests in 4xExtents, 128 KB
> chunks, and spawns a thread per data file in each Filegroup. It also
> mentioned that EE can exceed the number of concurrent extents per request
> and managed the thread spawning more efficiently. But, damn if I can't
> remember where I came across the information.
> Anthony Thomas
>
> --
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uNjLEV5BFHA.2180@.TK2MSFTNGP10.phx.gbl...
> An I agree with your comments except for the last one:
> Furthermore, more files per Filegroup will spawn
> additional disk threads per request.
> <<
> That was true with 7.0 but is not necessarily true with SQL2000. In 2000
> Sql Server can spawn multiple threads to read a single file as well. So
> just having multiple files does not mean it will be more effecient or
> spawn
> more threads than a single file.
>
> --
> Andrew J. Kelly SQL MVP
>
> "Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
> news:ep16rw1BFHA.3592@.TK2MSFTNGP09.phx.gbl...
>> Not that I disagree with the RAID-10 argument, I don't; however, I do
>> disagree with your comments about NTFS Cluster Sizes (a.k.a., block
>> size).
>> The MS SQL Server Operations Manual makes a recommendation of using 64-KB
>> cluster sizes. We have done so with a dramatic total disk I/O
>> throughput.
>> It makes sense. SQL Server reads and writes data in 8KB data pages, but
>> issues Scatter-Gather I/O requests in 4 or more Extents (8 x 8KB pages)
>> simultaneously. If your block sizes are on these 8KB boundaries, SQL
>> Server
>> Disk I/O requests can be more efficient.
>> Your biggest disk throughput bottleneck is total number of I/O Operations
>> a
>> single spindle can handle. You're throughput will be based on type of
>> access, sequential versus random, and the size of each request.
>> Optimizing
>> the request sizes coupled with a fixed maximum number of I/O Ops, should
>> make your system as efficient as possible.
>> Nevertheless, and along the same lines as the RAID arguments already
>> presented, this is also suggested from the Operations Manual: more
>> smaller
>> disks are more efficient than fewer larger disks. This would come into
>> play
>> in the 0 part of the RAID-10 argument. Striping allows more physical
>> spindles to fulfill requests, each one capable of servicing a theoretical
>> maximum number of I/O Ops. Furthermore, more files per Filegroup will
>> spawn
>> additional disk threads per request. This coupled with number of disks
>> can
>> greatly improve throughput.
>> Sincerely,
>>
>> Anthony Thomas
>>
>> --
>> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
>> news:uY2FhGsBFHA.3840@.tk2msftngp13.phx.gbl...
>> Hi
>> Rather make sure that your drives are configured correctly.
>> Make sure your logs on RAID-10, and preferably your data on a separate
>> RAID-10 volume. RAID-5 hurts more than it helps.
>> As Geoff said, block size is not important, but getting maximum IO
>> throughput is.
>> Regards
>> --
>> Mike Epprecht, Microsoft SQL Server MVP
>> Zurich, Switzerland
>> IM: mike@.epprecht.net
>> MVP Program: http://www.microsoft.com/mvp
>> Blog: http://www.msmvps.com/epprecht/
>> "Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
>> news:uhfJBgXBFHA.2180@.TK2MSFTNGP12.phx.gbl...
>> Unless you have an overriding reason, go with the hardware
>> manufacturer's
>> recommendations. These are typically the default settings. Changing
>> the
>> NTFS block size usually has no measurable effect on system performance.
>> --
>> Geoff N. Hiten
>> Microsoft SQL Server MVP
>> Senior Database Administrator
>> Careerbuilder.com
>> I support the Professional Association for SQL Server
>> www.sqlpass.org
>> "Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
>> news:B77A9710-86AA-4319-B539-6F65D91E5ECA@.microsoft.com...
>> >
>> > I have a SQL Server 2000 Enterprise database that processes 300
>> > transactions/sec, approximately 300 users, and 250 GB in size. How
>> > can
>> I
>> > determine the the appropriate disk block size that should be on my
>> system.
>> >
>> > Thanks,
>> >
>> >
>>
>>
>|||As always, thanks Andrew. Yes, I am well aware of having to "filter" read
information and make judgement calls on the sources reliability. The
article I read seemed respectable, though. If I come across it, I'll post a
"What's up" response.
Sincerely,
Anthony Thomas
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:ewn3Iz7BFHA.2260@.TK2MSFTNGP14.phx.gbl...
Not sure which document that came from either. The read ahead is actually
dynamic in the size of each read ahead request based somewhat on how
fragmented the data is. It can be 8K or 128K, it just depends on if it is
possible to do so or not. Most of the documentation for 2000 is wrong in
that it may state you need multiple files to spawn multiple threads. That
is definitely not the case and this is supposed to be fixed in the 2005
docs. Yes it can spawn a thread of each file but it is not limited to a
single thread per file. Even with a single file it is possible to spawn
multiple threads to read the same file. Here is what looks like a very
interesting article on IO in 2000. I haven't had a chance to read it yet
(hope to in the next few days) but it looks very promising.
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx
--
Andrew J. Kelly SQL MVP
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:u8xc256BFHA.1564@.TK2MSFTNGP09.phx.gbl...
>I just read that SS2K SE spawns Read Ahead requests in 4xExtents, 128 KB
> chunks, and spawns a thread per data file in each Filegroup. It also
> mentioned that EE can exceed the number of concurrent extents per request
> and managed the thread spawning more efficiently. But, damn if I can't
> remember where I came across the information.
> Anthony Thomas
>
> --
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uNjLEV5BFHA.2180@.TK2MSFTNGP10.phx.gbl...
> An I agree with your comments except for the last one:
> Furthermore, more files per Filegroup will spawn
> additional disk threads per request.
> <<
> That was true with 7.0 but is not necessarily true with SQL2000. In 2000
> Sql Server can spawn multiple threads to read a single file as well. So
> just having multiple files does not mean it will be more effecient or
> spawn
> more threads than a single file.
>
> --
> Andrew J. Kelly SQL MVP
>
> "Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
> news:ep16rw1BFHA.3592@.TK2MSFTNGP09.phx.gbl...
>> Not that I disagree with the RAID-10 argument, I don't; however, I do
>> disagree with your comments about NTFS Cluster Sizes (a.k.a., block
>> size).
>> The MS SQL Server Operations Manual makes a recommendation of using 64-KB
>> cluster sizes. We have done so with a dramatic total disk I/O
>> throughput.
>> It makes sense. SQL Server reads and writes data in 8KB data pages, but
>> issues Scatter-Gather I/O requests in 4 or more Extents (8 x 8KB pages)
>> simultaneously. If your block sizes are on these 8KB boundaries, SQL
>> Server
>> Disk I/O requests can be more efficient.
>> Your biggest disk throughput bottleneck is total number of I/O Operations
>> a
>> single spindle can handle. You're throughput will be based on type of
>> access, sequential versus random, and the size of each request.
>> Optimizing
>> the request sizes coupled with a fixed maximum number of I/O Ops, should
>> make your system as efficient as possible.
>> Nevertheless, and along the same lines as the RAID arguments already
>> presented, this is also suggested from the Operations Manual: more
>> smaller
>> disks are more efficient than fewer larger disks. This would come into
>> play
>> in the 0 part of the RAID-10 argument. Striping allows more physical
>> spindles to fulfill requests, each one capable of servicing a theoretical
>> maximum number of I/O Ops. Furthermore, more files per Filegroup will
>> spawn
>> additional disk threads per request. This coupled with number of disks
>> can
>> greatly improve throughput.
>> Sincerely,
>>
>> Anthony Thomas
>>
>> --
>> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
>> news:uY2FhGsBFHA.3840@.tk2msftngp13.phx.gbl...
>> Hi
>> Rather make sure that your drives are configured correctly.
>> Make sure your logs on RAID-10, and preferably your data on a separate
>> RAID-10 volume. RAID-5 hurts more than it helps.
>> As Geoff said, block size is not important, but getting maximum IO
>> throughput is.
>> Regards
>> --
>> Mike Epprecht, Microsoft SQL Server MVP
>> Zurich, Switzerland
>> IM: mike@.epprecht.net
>> MVP Program: http://www.microsoft.com/mvp
>> Blog: http://www.msmvps.com/epprecht/
>> "Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
>> news:uhfJBgXBFHA.2180@.TK2MSFTNGP12.phx.gbl...
>> Unless you have an overriding reason, go with the hardware
>> manufacturer's
>> recommendations. These are typically the default settings. Changing
>> the
>> NTFS block size usually has no measurable effect on system performance.
>> --
>> Geoff N. Hiten
>> Microsoft SQL Server MVP
>> Senior Database Administrator
>> Careerbuilder.com
>> I support the Professional Association for SQL Server
>> www.sqlpass.org
>> "Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
>> news:B77A9710-86AA-4319-B539-6F65D91E5ECA@.microsoft.com...
>> >
>> > I have a SQL Server 2000 Enterprise database that processes 300
>> > transactions/sec, approximately 300 users, and 250 GB in size. How
>> > can
>> I
>> > determine the the appropriate disk block size that should be on my
>> system.
>> >
>> > Thanks,
>> >
>> >
>>
>>
>|||Well, that doc confirmed at least part of what I read previously:
"SQL Server limits a single read-ahead request depth to 128 pages on most
editions. However, Microsoft SQL Server Enterprise Edition raises the limit
to 1,024 pages."
Sincerely,
Anthony Thomas
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:OIoyclBCFHA.3588@.TK2MSFTNGP11.phx.gbl...
As always, thanks Andrew. Yes, I am well aware of having to "filter" read
information and make judgement calls on the sources reliability. The
article I read seemed respectable, though. If I come across it, I'll post a
"What's up" response.
Sincerely,
Anthony Thomas
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:ewn3Iz7BFHA.2260@.TK2MSFTNGP14.phx.gbl...
Not sure which document that came from either. The read ahead is actually
dynamic in the size of each read ahead request based somewhat on how
fragmented the data is. It can be 8K or 128K, it just depends on if it is
possible to do so or not. Most of the documentation for 2000 is wrong in
that it may state you need multiple files to spawn multiple threads. That
is definitely not the case and this is supposed to be fixed in the 2005
docs. Yes it can spawn a thread of each file but it is not limited to a
single thread per file. Even with a single file it is possible to spawn
multiple threads to read the same file. Here is what looks like a very
interesting article on IO in 2000. I haven't had a chance to read it yet
(hope to in the next few days) but it looks very promising.
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx
--
Andrew J. Kelly SQL MVP
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:u8xc256BFHA.1564@.TK2MSFTNGP09.phx.gbl...
>I just read that SS2K SE spawns Read Ahead requests in 4xExtents, 128 KB
> chunks, and spawns a thread per data file in each Filegroup. It also
> mentioned that EE can exceed the number of concurrent extents per request
> and managed the thread spawning more efficiently. But, damn if I can't
> remember where I came across the information.
> Anthony Thomas
>
> --
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uNjLEV5BFHA.2180@.TK2MSFTNGP10.phx.gbl...
> An I agree with your comments except for the last one:
> Furthermore, more files per Filegroup will spawn
> additional disk threads per request.
> <<
> That was true with 7.0 but is not necessarily true with SQL2000. In 2000
> Sql Server can spawn multiple threads to read a single file as well. So
> just having multiple files does not mean it will be more effecient or
> spawn
> more threads than a single file.
>
> --
> Andrew J. Kelly SQL MVP
>
> "Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
> news:ep16rw1BFHA.3592@.TK2MSFTNGP09.phx.gbl...
>> Not that I disagree with the RAID-10 argument, I don't; however, I do
>> disagree with your comments about NTFS Cluster Sizes (a.k.a., block
>> size).
>> The MS SQL Server Operations Manual makes a recommendation of using 64-KB
>> cluster sizes. We have done so with a dramatic total disk I/O
>> throughput.
>> It makes sense. SQL Server reads and writes data in 8KB data pages, but
>> issues Scatter-Gather I/O requests in 4 or more Extents (8 x 8KB pages)
>> simultaneously. If your block sizes are on these 8KB boundaries, SQL
>> Server
>> Disk I/O requests can be more efficient.
>> Your biggest disk throughput bottleneck is total number of I/O Operations
>> a
>> single spindle can handle. You're throughput will be based on type of
>> access, sequential versus random, and the size of each request.
>> Optimizing
>> the request sizes coupled with a fixed maximum number of I/O Ops, should
>> make your system as efficient as possible.
>> Nevertheless, and along the same lines as the RAID arguments already
>> presented, this is also suggested from the Operations Manual: more
>> smaller
>> disks are more efficient than fewer larger disks. This would come into
>> play
>> in the 0 part of the RAID-10 argument. Striping allows more physical
>> spindles to fulfill requests, each one capable of servicing a theoretical
>> maximum number of I/O Ops. Furthermore, more files per Filegroup will
>> spawn
>> additional disk threads per request. This coupled with number of disks
>> can
>> greatly improve throughput.
>> Sincerely,
>>
>> Anthony Thomas
>>
>> --
>> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
>> news:uY2FhGsBFHA.3840@.tk2msftngp13.phx.gbl...
>> Hi
>> Rather make sure that your drives are configured correctly.
>> Make sure your logs on RAID-10, and preferably your data on a separate
>> RAID-10 volume. RAID-5 hurts more than it helps.
>> As Geoff said, block size is not important, but getting maximum IO
>> throughput is.
>> Regards
>> --
>> Mike Epprecht, Microsoft SQL Server MVP
>> Zurich, Switzerland
>> IM: mike@.epprecht.net
>> MVP Program: http://www.microsoft.com/mvp
>> Blog: http://www.msmvps.com/epprecht/
>> "Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
>> news:uhfJBgXBFHA.2180@.TK2MSFTNGP12.phx.gbl...
>> Unless you have an overriding reason, go with the hardware
>> manufacturer's
>> recommendations. These are typically the default settings. Changing
>> the
>> NTFS block size usually has no measurable effect on system performance.
>> --
>> Geoff N. Hiten
>> Microsoft SQL Server MVP
>> Senior Database Administrator
>> Careerbuilder.com
>> I support the Professional Association for SQL Server
>> www.sqlpass.org
>> "Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
>> news:B77A9710-86AA-4319-B539-6F65D91E5ECA@.microsoft.com...
>> >
>> > I have a SQL Server 2000 Enterprise database that processes 300
>> > transactions/sec, approximately 300 users, and 250 GB in size. How
>> > can
>> I
>> > determine the the appropriate disk block size that should be on my
>> system.
>> >
>> > Thanks,
>> >
>> >
>>
>>
>|||Yes but my point was don't take that as a fixed size. It limits the max to
that value but the read ahead can be 8K as well if the conditions do not
allow for all 128K or higher.
--
Andrew J. Kelly SQL MVP
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:uJBYOuBCFHA.2568@.TK2MSFTNGP10.phx.gbl...
> Well, that doc confirmed at least part of what I read previously:
> "SQL Server limits a single read-ahead request depth to 128 pages on most
> editions. However, Microsoft SQL Server Enterprise Edition raises the
> limit
> to 1,024 pages."
> Sincerely,
>
> Anthony Thomas
>
> --
> "Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
> news:OIoyclBCFHA.3588@.TK2MSFTNGP11.phx.gbl...
> As always, thanks Andrew. Yes, I am well aware of having to "filter" read
> information and make judgement calls on the sources reliability. The
> article I read seemed respectable, though. If I come across it, I'll post
> a
> "What's up" response.
> Sincerely,
>
> Anthony Thomas
>
> --
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:ewn3Iz7BFHA.2260@.TK2MSFTNGP14.phx.gbl...
> Not sure which document that came from either. The read ahead is actually
> dynamic in the size of each read ahead request based somewhat on how
> fragmented the data is. It can be 8K or 128K, it just depends on if it is
> possible to do so or not. Most of the documentation for 2000 is wrong in
> that it may state you need multiple files to spawn multiple threads. That
> is definitely not the case and this is supposed to be fixed in the 2005
> docs. Yes it can spawn a thread of each file but it is not limited to a
> single thread per file. Even with a single file it is possible to spawn
> multiple threads to read the same file. Here is what looks like a very
> interesting article on IO in 2000. I haven't had a chance to read it yet
> (hope to in the next few days) but it looks very promising.
> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx
> --
> Andrew J. Kelly SQL MVP
>
> "Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
> news:u8xc256BFHA.1564@.TK2MSFTNGP09.phx.gbl...
>>I just read that SS2K SE spawns Read Ahead requests in 4xExtents, 128 KB
>> chunks, and spawns a thread per data file in each Filegroup. It also
>> mentioned that EE can exceed the number of concurrent extents per request
>> and managed the thread spawning more efficiently. But, damn if I can't
>> remember where I came across the information.
>> Anthony Thomas
>>
>> --
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:uNjLEV5BFHA.2180@.TK2MSFTNGP10.phx.gbl...
>> An I agree with your comments except for the last one:
>> Furthermore, more files per Filegroup will spawn
>> additional disk threads per request.
>> <<
>> That was true with 7.0 but is not necessarily true with SQL2000. In 2000
>> Sql Server can spawn multiple threads to read a single file as well. So
>> just having multiple files does not mean it will be more effecient or
>> spawn
>> more threads than a single file.
>>
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
>> news:ep16rw1BFHA.3592@.TK2MSFTNGP09.phx.gbl...
>> Not that I disagree with the RAID-10 argument, I don't; however, I do
>> disagree with your comments about NTFS Cluster Sizes (a.k.a., block
>> size).
>> The MS SQL Server Operations Manual makes a recommendation of using
>> 64-KB
>> cluster sizes. We have done so with a dramatic total disk I/O
>> throughput.
>> It makes sense. SQL Server reads and writes data in 8KB data pages, but
>> issues Scatter-Gather I/O requests in 4 or more Extents (8 x 8KB pages)
>> simultaneously. If your block sizes are on these 8KB boundaries, SQL
>> Server
>> Disk I/O requests can be more efficient.
>> Your biggest disk throughput bottleneck is total number of I/O
>> Operations
>> a
>> single spindle can handle. You're throughput will be based on type of
>> access, sequential versus random, and the size of each request.
>> Optimizing
>> the request sizes coupled with a fixed maximum number of I/O Ops, should
>> make your system as efficient as possible.
>> Nevertheless, and along the same lines as the RAID arguments already
>> presented, this is also suggested from the Operations Manual: more
>> smaller
>> disks are more efficient than fewer larger disks. This would come into
>> play
>> in the 0 part of the RAID-10 argument. Striping allows more physical
>> spindles to fulfill requests, each one capable of servicing a
>> theoretical
>> maximum number of I/O Ops. Furthermore, more files per Filegroup will
>> spawn
>> additional disk threads per request. This coupled with number of disks
>> can
>> greatly improve throughput.
>> Sincerely,
>>
>> Anthony Thomas
>>
>> --
>> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
>> news:uY2FhGsBFHA.3840@.tk2msftngp13.phx.gbl...
>> Hi
>> Rather make sure that your drives are configured correctly.
>> Make sure your logs on RAID-10, and preferably your data on a separate
>> RAID-10 volume. RAID-5 hurts more than it helps.
>> As Geoff said, block size is not important, but getting maximum IO
>> throughput is.
>> Regards
>> --
>> Mike Epprecht, Microsoft SQL Server MVP
>> Zurich, Switzerland
>> IM: mike@.epprecht.net
>> MVP Program: http://www.microsoft.com/mvp
>> Blog: http://www.msmvps.com/epprecht/
>> "Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
>> news:uhfJBgXBFHA.2180@.TK2MSFTNGP12.phx.gbl...
>> Unless you have an overriding reason, go with the hardware
>> manufacturer's
>> recommendations. These are typically the default settings. Changing
>> the
>> NTFS block size usually has no measurable effect on system performance.
>> --
>> Geoff N. Hiten
>> Microsoft SQL Server MVP
>> Senior Database Administrator
>> Careerbuilder.com
>> I support the Professional Association for SQL Server
>> www.sqlpass.org
>> "Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
>> news:B77A9710-86AA-4319-B539-6F65D91E5ECA@.microsoft.com...
>> >
>> > I have a SQL Server 2000 Enterprise database that processes 300
>> > transactions/sec, approximately 300 users, and 250 GB in size. How
>> > can
>> I
>> > determine the the appropriate disk block size that should be on my
>> system.
>> >
>> > Thanks,
>> >
>> >
>>
>>
>>
>
transactions/sec, approximately 300 users, and 250 GB in size. How can I
determine the the appropriate disk block size that should be on my system.
Thanks,Unless you have an overriding reason, go with the hardware manufacturer's
recommendations. These are typically the default settings. Changing the
NTFS block size usually has no measurable effect on system performance.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:B77A9710-86AA-4319-B539-6F65D91E5ECA@.microsoft.com...
> I have a SQL Server 2000 Enterprise database that processes 300
> transactions/sec, approximately 300 users, and 250 GB in size. How can I
> determine the the appropriate disk block size that should be on my system.
> Thanks,
>|||Hi
Rather make sure that your drives are configured correctly.
Make sure your logs on RAID-10, and preferably your data on a separate
RAID-10 volume. RAID-5 hurts more than it helps.
As Geoff said, block size is not important, but getting maximum IO
throughput is.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:uhfJBgXBFHA.2180@.TK2MSFTNGP12.phx.gbl...
> Unless you have an overriding reason, go with the hardware manufacturer's
> recommendations. These are typically the default settings. Changing the
> NTFS block size usually has no measurable effect on system performance.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
> news:B77A9710-86AA-4319-B539-6F65D91E5ECA@.microsoft.com...
> >
> > I have a SQL Server 2000 Enterprise database that processes 300
> > transactions/sec, approximately 300 users, and 250 GB in size. How can
I
> > determine the the appropriate disk block size that should be on my
system.
> >
> > Thanks,
> >
> >
>|||Hi
Rather make sure that your drives are configured correctly.
Make sure your logs on RAID-10, and preferably your data on a separate
RAID-10 volume. RAID-5 hurts more than it helps.
As Geoff said, block size is not important, but getting maximum IO
throughput is.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:uhfJBgXBFHA.2180@.TK2MSFTNGP12.phx.gbl...
> Unless you have an overriding reason, go with the hardware manufacturer's
> recommendations. These are typically the default settings. Changing the
> NTFS block size usually has no measurable effect on system performance.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
> news:B77A9710-86AA-4319-B539-6F65D91E5ECA@.microsoft.com...
> >
> > I have a SQL Server 2000 Enterprise database that processes 300
> > transactions/sec, approximately 300 users, and 250 GB in size. How can
I
> > determine the the appropriate disk block size that should be on my
system.
> >
> > Thanks,
> >
> >
>|||Not that I disagree with the RAID-10 argument, I don't; however, I do
disagree with your comments about NTFS Cluster Sizes (a.k.a., block size).
The MS SQL Server Operations Manual makes a recommendation of using 64-KB
cluster sizes. We have done so with a dramatic total disk I/O throughput.
It makes sense. SQL Server reads and writes data in 8KB data pages, but
issues Scatter-Gather I/O requests in 4 or more Extents (8 x 8KB pages)
simultaneously. If your block sizes are on these 8KB boundaries, SQL Server
Disk I/O requests can be more efficient.
Your biggest disk throughput bottleneck is total number of I/O Operations a
single spindle can handle. You're throughput will be based on type of
access, sequential versus random, and the size of each request. Optimizing
the request sizes coupled with a fixed maximum number of I/O Ops, should
make your system as efficient as possible.
Nevertheless, and along the same lines as the RAID arguments already
presented, this is also suggested from the Operations Manual: more smaller
disks are more efficient than fewer larger disks. This would come into play
in the 0 part of the RAID-10 argument. Striping allows more physical
spindles to fulfill requests, each one capable of servicing a theoretical
maximum number of I/O Ops. Furthermore, more files per Filegroup will spawn
additional disk threads per request. This coupled with number of disks can
greatly improve throughput.
Sincerely,
Anthony Thomas
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:uY2FhGsBFHA.3840@.tk2msftngp13.phx.gbl...
Hi
Rather make sure that your drives are configured correctly.
Make sure your logs on RAID-10, and preferably your data on a separate
RAID-10 volume. RAID-5 hurts more than it helps.
As Geoff said, block size is not important, but getting maximum IO
throughput is.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:uhfJBgXBFHA.2180@.TK2MSFTNGP12.phx.gbl...
> Unless you have an overriding reason, go with the hardware manufacturer's
> recommendations. These are typically the default settings. Changing the
> NTFS block size usually has no measurable effect on system performance.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
> news:B77A9710-86AA-4319-B539-6F65D91E5ECA@.microsoft.com...
> >
> > I have a SQL Server 2000 Enterprise database that processes 300
> > transactions/sec, approximately 300 users, and 250 GB in size. How can
I
> > determine the the appropriate disk block size that should be on my
system.
> >
> > Thanks,
> >
> >
>|||An I agree with your comments except for the last one:
Furthermore, more files per Filegroup will spawn
additional disk threads per request.
<<
That was true with 7.0 but is not necessarily true with SQL2000. In 2000
Sql Server can spawn multiple threads to read a single file as well. So
just having multiple files does not mean it will be more effecient or spawn
more threads than a single file.
Andrew J. Kelly SQL MVP
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:ep16rw1BFHA.3592@.TK2MSFTNGP09.phx.gbl...
> Not that I disagree with the RAID-10 argument, I don't; however, I do
> disagree with your comments about NTFS Cluster Sizes (a.k.a., block size).
> The MS SQL Server Operations Manual makes a recommendation of using 64-KB
> cluster sizes. We have done so with a dramatic total disk I/O throughput.
> It makes sense. SQL Server reads and writes data in 8KB data pages, but
> issues Scatter-Gather I/O requests in 4 or more Extents (8 x 8KB pages)
> simultaneously. If your block sizes are on these 8KB boundaries, SQL
> Server
> Disk I/O requests can be more efficient.
> Your biggest disk throughput bottleneck is total number of I/O Operations
> a
> single spindle can handle. You're throughput will be based on type of
> access, sequential versus random, and the size of each request.
> Optimizing
> the request sizes coupled with a fixed maximum number of I/O Ops, should
> make your system as efficient as possible.
> Nevertheless, and along the same lines as the RAID arguments already
> presented, this is also suggested from the Operations Manual: more smaller
> disks are more efficient than fewer larger disks. This would come into
> play
> in the 0 part of the RAID-10 argument. Striping allows more physical
> spindles to fulfill requests, each one capable of servicing a theoretical
> maximum number of I/O Ops. Furthermore, more files per Filegroup will
> spawn
> additional disk threads per request. This coupled with number of disks
> can
> greatly improve throughput.
> Sincerely,
>
> Anthony Thomas
>
> --
> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
> news:uY2FhGsBFHA.3840@.tk2msftngp13.phx.gbl...
> Hi
> Rather make sure that your drives are configured correctly.
> Make sure your logs on RAID-10, and preferably your data on a separate
> RAID-10 volume. RAID-5 hurts more than it helps.
> As Geoff said, block size is not important, but getting maximum IO
> throughput is.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
> news:uhfJBgXBFHA.2180@.TK2MSFTNGP12.phx.gbl...
>> Unless you have an overriding reason, go with the hardware manufacturer's
>> recommendations. These are typically the default settings. Changing the
>> NTFS block size usually has no measurable effect on system performance.
>> --
>> Geoff N. Hiten
>> Microsoft SQL Server MVP
>> Senior Database Administrator
>> Careerbuilder.com
>> I support the Professional Association for SQL Server
>> www.sqlpass.org
>> "Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
>> news:B77A9710-86AA-4319-B539-6F65D91E5ECA@.microsoft.com...
>> >
>> > I have a SQL Server 2000 Enterprise database that processes 300
>> > transactions/sec, approximately 300 users, and 250 GB in size. How can
> I
>> > determine the the appropriate disk block size that should be on my
> system.
>> >
>> > Thanks,
>> >
>> >
>>
>|||I just read that SS2K SE spawns Read Ahead requests in 4xExtents, 128 KB
chunks, and spawns a thread per data file in each Filegroup. It also
mentioned that EE can exceed the number of concurrent extents per request
and managed the thread spawning more efficiently. But, damn if I can't
remember where I came across the information.
Anthony Thomas
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uNjLEV5BFHA.2180@.TK2MSFTNGP10.phx.gbl...
An I agree with your comments except for the last one:
Furthermore, more files per Filegroup will spawn
additional disk threads per request.
<<
That was true with 7.0 but is not necessarily true with SQL2000. In 2000
Sql Server can spawn multiple threads to read a single file as well. So
just having multiple files does not mean it will be more effecient or spawn
more threads than a single file.
Andrew J. Kelly SQL MVP
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:ep16rw1BFHA.3592@.TK2MSFTNGP09.phx.gbl...
> Not that I disagree with the RAID-10 argument, I don't; however, I do
> disagree with your comments about NTFS Cluster Sizes (a.k.a., block size).
> The MS SQL Server Operations Manual makes a recommendation of using 64-KB
> cluster sizes. We have done so with a dramatic total disk I/O throughput.
> It makes sense. SQL Server reads and writes data in 8KB data pages, but
> issues Scatter-Gather I/O requests in 4 or more Extents (8 x 8KB pages)
> simultaneously. If your block sizes are on these 8KB boundaries, SQL
> Server
> Disk I/O requests can be more efficient.
> Your biggest disk throughput bottleneck is total number of I/O Operations
> a
> single spindle can handle. You're throughput will be based on type of
> access, sequential versus random, and the size of each request.
> Optimizing
> the request sizes coupled with a fixed maximum number of I/O Ops, should
> make your system as efficient as possible.
> Nevertheless, and along the same lines as the RAID arguments already
> presented, this is also suggested from the Operations Manual: more smaller
> disks are more efficient than fewer larger disks. This would come into
> play
> in the 0 part of the RAID-10 argument. Striping allows more physical
> spindles to fulfill requests, each one capable of servicing a theoretical
> maximum number of I/O Ops. Furthermore, more files per Filegroup will
> spawn
> additional disk threads per request. This coupled with number of disks
> can
> greatly improve throughput.
> Sincerely,
>
> Anthony Thomas
>
> --
> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
> news:uY2FhGsBFHA.3840@.tk2msftngp13.phx.gbl...
> Hi
> Rather make sure that your drives are configured correctly.
> Make sure your logs on RAID-10, and preferably your data on a separate
> RAID-10 volume. RAID-5 hurts more than it helps.
> As Geoff said, block size is not important, but getting maximum IO
> throughput is.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
> news:uhfJBgXBFHA.2180@.TK2MSFTNGP12.phx.gbl...
>> Unless you have an overriding reason, go with the hardware manufacturer's
>> recommendations. These are typically the default settings. Changing the
>> NTFS block size usually has no measurable effect on system performance.
>> --
>> Geoff N. Hiten
>> Microsoft SQL Server MVP
>> Senior Database Administrator
>> Careerbuilder.com
>> I support the Professional Association for SQL Server
>> www.sqlpass.org
>> "Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
>> news:B77A9710-86AA-4319-B539-6F65D91E5ECA@.microsoft.com...
>> >
>> > I have a SQL Server 2000 Enterprise database that processes 300
>> > transactions/sec, approximately 300 users, and 250 GB in size. How can
> I
>> > determine the the appropriate disk block size that should be on my
> system.
>> >
>> > Thanks,
>> >
>> >
>>
>|||Not sure which document that came from either. The read ahead is actually
dynamic in the size of each read ahead request based somewhat on how
fragmented the data is. It can be 8K or 128K, it just depends on if it is
possible to do so or not. Most of the documentation for 2000 is wrong in
that it may state you need multiple files to spawn multiple threads. That
is definitely not the case and this is supposed to be fixed in the 2005
docs. Yes it can spawn a thread of each file but it is not limited to a
single thread per file. Even with a single file it is possible to spawn
multiple threads to read the same file. Here is what looks like a very
interesting article on IO in 2000. I haven't had a chance to read it yet
(hope to in the next few days) but it looks very promising.
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx
--
Andrew J. Kelly SQL MVP
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:u8xc256BFHA.1564@.TK2MSFTNGP09.phx.gbl...
>I just read that SS2K SE spawns Read Ahead requests in 4xExtents, 128 KB
> chunks, and spawns a thread per data file in each Filegroup. It also
> mentioned that EE can exceed the number of concurrent extents per request
> and managed the thread spawning more efficiently. But, damn if I can't
> remember where I came across the information.
> Anthony Thomas
>
> --
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uNjLEV5BFHA.2180@.TK2MSFTNGP10.phx.gbl...
> An I agree with your comments except for the last one:
> Furthermore, more files per Filegroup will spawn
> additional disk threads per request.
> <<
> That was true with 7.0 but is not necessarily true with SQL2000. In 2000
> Sql Server can spawn multiple threads to read a single file as well. So
> just having multiple files does not mean it will be more effecient or
> spawn
> more threads than a single file.
>
> --
> Andrew J. Kelly SQL MVP
>
> "Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
> news:ep16rw1BFHA.3592@.TK2MSFTNGP09.phx.gbl...
>> Not that I disagree with the RAID-10 argument, I don't; however, I do
>> disagree with your comments about NTFS Cluster Sizes (a.k.a., block
>> size).
>> The MS SQL Server Operations Manual makes a recommendation of using 64-KB
>> cluster sizes. We have done so with a dramatic total disk I/O
>> throughput.
>> It makes sense. SQL Server reads and writes data in 8KB data pages, but
>> issues Scatter-Gather I/O requests in 4 or more Extents (8 x 8KB pages)
>> simultaneously. If your block sizes are on these 8KB boundaries, SQL
>> Server
>> Disk I/O requests can be more efficient.
>> Your biggest disk throughput bottleneck is total number of I/O Operations
>> a
>> single spindle can handle. You're throughput will be based on type of
>> access, sequential versus random, and the size of each request.
>> Optimizing
>> the request sizes coupled with a fixed maximum number of I/O Ops, should
>> make your system as efficient as possible.
>> Nevertheless, and along the same lines as the RAID arguments already
>> presented, this is also suggested from the Operations Manual: more
>> smaller
>> disks are more efficient than fewer larger disks. This would come into
>> play
>> in the 0 part of the RAID-10 argument. Striping allows more physical
>> spindles to fulfill requests, each one capable of servicing a theoretical
>> maximum number of I/O Ops. Furthermore, more files per Filegroup will
>> spawn
>> additional disk threads per request. This coupled with number of disks
>> can
>> greatly improve throughput.
>> Sincerely,
>>
>> Anthony Thomas
>>
>> --
>> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
>> news:uY2FhGsBFHA.3840@.tk2msftngp13.phx.gbl...
>> Hi
>> Rather make sure that your drives are configured correctly.
>> Make sure your logs on RAID-10, and preferably your data on a separate
>> RAID-10 volume. RAID-5 hurts more than it helps.
>> As Geoff said, block size is not important, but getting maximum IO
>> throughput is.
>> Regards
>> --
>> Mike Epprecht, Microsoft SQL Server MVP
>> Zurich, Switzerland
>> IM: mike@.epprecht.net
>> MVP Program: http://www.microsoft.com/mvp
>> Blog: http://www.msmvps.com/epprecht/
>> "Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
>> news:uhfJBgXBFHA.2180@.TK2MSFTNGP12.phx.gbl...
>> Unless you have an overriding reason, go with the hardware
>> manufacturer's
>> recommendations. These are typically the default settings. Changing
>> the
>> NTFS block size usually has no measurable effect on system performance.
>> --
>> Geoff N. Hiten
>> Microsoft SQL Server MVP
>> Senior Database Administrator
>> Careerbuilder.com
>> I support the Professional Association for SQL Server
>> www.sqlpass.org
>> "Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
>> news:B77A9710-86AA-4319-B539-6F65D91E5ECA@.microsoft.com...
>> >
>> > I have a SQL Server 2000 Enterprise database that processes 300
>> > transactions/sec, approximately 300 users, and 250 GB in size. How
>> > can
>> I
>> > determine the the appropriate disk block size that should be on my
>> system.
>> >
>> > Thanks,
>> >
>> >
>>
>>
>|||As always, thanks Andrew. Yes, I am well aware of having to "filter" read
information and make judgement calls on the sources reliability. The
article I read seemed respectable, though. If I come across it, I'll post a
"What's up" response.
Sincerely,
Anthony Thomas
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:ewn3Iz7BFHA.2260@.TK2MSFTNGP14.phx.gbl...
Not sure which document that came from either. The read ahead is actually
dynamic in the size of each read ahead request based somewhat on how
fragmented the data is. It can be 8K or 128K, it just depends on if it is
possible to do so or not. Most of the documentation for 2000 is wrong in
that it may state you need multiple files to spawn multiple threads. That
is definitely not the case and this is supposed to be fixed in the 2005
docs. Yes it can spawn a thread of each file but it is not limited to a
single thread per file. Even with a single file it is possible to spawn
multiple threads to read the same file. Here is what looks like a very
interesting article on IO in 2000. I haven't had a chance to read it yet
(hope to in the next few days) but it looks very promising.
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx
--
Andrew J. Kelly SQL MVP
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:u8xc256BFHA.1564@.TK2MSFTNGP09.phx.gbl...
>I just read that SS2K SE spawns Read Ahead requests in 4xExtents, 128 KB
> chunks, and spawns a thread per data file in each Filegroup. It also
> mentioned that EE can exceed the number of concurrent extents per request
> and managed the thread spawning more efficiently. But, damn if I can't
> remember where I came across the information.
> Anthony Thomas
>
> --
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uNjLEV5BFHA.2180@.TK2MSFTNGP10.phx.gbl...
> An I agree with your comments except for the last one:
> Furthermore, more files per Filegroup will spawn
> additional disk threads per request.
> <<
> That was true with 7.0 but is not necessarily true with SQL2000. In 2000
> Sql Server can spawn multiple threads to read a single file as well. So
> just having multiple files does not mean it will be more effecient or
> spawn
> more threads than a single file.
>
> --
> Andrew J. Kelly SQL MVP
>
> "Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
> news:ep16rw1BFHA.3592@.TK2MSFTNGP09.phx.gbl...
>> Not that I disagree with the RAID-10 argument, I don't; however, I do
>> disagree with your comments about NTFS Cluster Sizes (a.k.a., block
>> size).
>> The MS SQL Server Operations Manual makes a recommendation of using 64-KB
>> cluster sizes. We have done so with a dramatic total disk I/O
>> throughput.
>> It makes sense. SQL Server reads and writes data in 8KB data pages, but
>> issues Scatter-Gather I/O requests in 4 or more Extents (8 x 8KB pages)
>> simultaneously. If your block sizes are on these 8KB boundaries, SQL
>> Server
>> Disk I/O requests can be more efficient.
>> Your biggest disk throughput bottleneck is total number of I/O Operations
>> a
>> single spindle can handle. You're throughput will be based on type of
>> access, sequential versus random, and the size of each request.
>> Optimizing
>> the request sizes coupled with a fixed maximum number of I/O Ops, should
>> make your system as efficient as possible.
>> Nevertheless, and along the same lines as the RAID arguments already
>> presented, this is also suggested from the Operations Manual: more
>> smaller
>> disks are more efficient than fewer larger disks. This would come into
>> play
>> in the 0 part of the RAID-10 argument. Striping allows more physical
>> spindles to fulfill requests, each one capable of servicing a theoretical
>> maximum number of I/O Ops. Furthermore, more files per Filegroup will
>> spawn
>> additional disk threads per request. This coupled with number of disks
>> can
>> greatly improve throughput.
>> Sincerely,
>>
>> Anthony Thomas
>>
>> --
>> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
>> news:uY2FhGsBFHA.3840@.tk2msftngp13.phx.gbl...
>> Hi
>> Rather make sure that your drives are configured correctly.
>> Make sure your logs on RAID-10, and preferably your data on a separate
>> RAID-10 volume. RAID-5 hurts more than it helps.
>> As Geoff said, block size is not important, but getting maximum IO
>> throughput is.
>> Regards
>> --
>> Mike Epprecht, Microsoft SQL Server MVP
>> Zurich, Switzerland
>> IM: mike@.epprecht.net
>> MVP Program: http://www.microsoft.com/mvp
>> Blog: http://www.msmvps.com/epprecht/
>> "Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
>> news:uhfJBgXBFHA.2180@.TK2MSFTNGP12.phx.gbl...
>> Unless you have an overriding reason, go with the hardware
>> manufacturer's
>> recommendations. These are typically the default settings. Changing
>> the
>> NTFS block size usually has no measurable effect on system performance.
>> --
>> Geoff N. Hiten
>> Microsoft SQL Server MVP
>> Senior Database Administrator
>> Careerbuilder.com
>> I support the Professional Association for SQL Server
>> www.sqlpass.org
>> "Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
>> news:B77A9710-86AA-4319-B539-6F65D91E5ECA@.microsoft.com...
>> >
>> > I have a SQL Server 2000 Enterprise database that processes 300
>> > transactions/sec, approximately 300 users, and 250 GB in size. How
>> > can
>> I
>> > determine the the appropriate disk block size that should be on my
>> system.
>> >
>> > Thanks,
>> >
>> >
>>
>>
>|||Well, that doc confirmed at least part of what I read previously:
"SQL Server limits a single read-ahead request depth to 128 pages on most
editions. However, Microsoft SQL Server Enterprise Edition raises the limit
to 1,024 pages."
Sincerely,
Anthony Thomas
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:OIoyclBCFHA.3588@.TK2MSFTNGP11.phx.gbl...
As always, thanks Andrew. Yes, I am well aware of having to "filter" read
information and make judgement calls on the sources reliability. The
article I read seemed respectable, though. If I come across it, I'll post a
"What's up" response.
Sincerely,
Anthony Thomas
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:ewn3Iz7BFHA.2260@.TK2MSFTNGP14.phx.gbl...
Not sure which document that came from either. The read ahead is actually
dynamic in the size of each read ahead request based somewhat on how
fragmented the data is. It can be 8K or 128K, it just depends on if it is
possible to do so or not. Most of the documentation for 2000 is wrong in
that it may state you need multiple files to spawn multiple threads. That
is definitely not the case and this is supposed to be fixed in the 2005
docs. Yes it can spawn a thread of each file but it is not limited to a
single thread per file. Even with a single file it is possible to spawn
multiple threads to read the same file. Here is what looks like a very
interesting article on IO in 2000. I haven't had a chance to read it yet
(hope to in the next few days) but it looks very promising.
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx
--
Andrew J. Kelly SQL MVP
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:u8xc256BFHA.1564@.TK2MSFTNGP09.phx.gbl...
>I just read that SS2K SE spawns Read Ahead requests in 4xExtents, 128 KB
> chunks, and spawns a thread per data file in each Filegroup. It also
> mentioned that EE can exceed the number of concurrent extents per request
> and managed the thread spawning more efficiently. But, damn if I can't
> remember where I came across the information.
> Anthony Thomas
>
> --
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uNjLEV5BFHA.2180@.TK2MSFTNGP10.phx.gbl...
> An I agree with your comments except for the last one:
> Furthermore, more files per Filegroup will spawn
> additional disk threads per request.
> <<
> That was true with 7.0 but is not necessarily true with SQL2000. In 2000
> Sql Server can spawn multiple threads to read a single file as well. So
> just having multiple files does not mean it will be more effecient or
> spawn
> more threads than a single file.
>
> --
> Andrew J. Kelly SQL MVP
>
> "Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
> news:ep16rw1BFHA.3592@.TK2MSFTNGP09.phx.gbl...
>> Not that I disagree with the RAID-10 argument, I don't; however, I do
>> disagree with your comments about NTFS Cluster Sizes (a.k.a., block
>> size).
>> The MS SQL Server Operations Manual makes a recommendation of using 64-KB
>> cluster sizes. We have done so with a dramatic total disk I/O
>> throughput.
>> It makes sense. SQL Server reads and writes data in 8KB data pages, but
>> issues Scatter-Gather I/O requests in 4 or more Extents (8 x 8KB pages)
>> simultaneously. If your block sizes are on these 8KB boundaries, SQL
>> Server
>> Disk I/O requests can be more efficient.
>> Your biggest disk throughput bottleneck is total number of I/O Operations
>> a
>> single spindle can handle. You're throughput will be based on type of
>> access, sequential versus random, and the size of each request.
>> Optimizing
>> the request sizes coupled with a fixed maximum number of I/O Ops, should
>> make your system as efficient as possible.
>> Nevertheless, and along the same lines as the RAID arguments already
>> presented, this is also suggested from the Operations Manual: more
>> smaller
>> disks are more efficient than fewer larger disks. This would come into
>> play
>> in the 0 part of the RAID-10 argument. Striping allows more physical
>> spindles to fulfill requests, each one capable of servicing a theoretical
>> maximum number of I/O Ops. Furthermore, more files per Filegroup will
>> spawn
>> additional disk threads per request. This coupled with number of disks
>> can
>> greatly improve throughput.
>> Sincerely,
>>
>> Anthony Thomas
>>
>> --
>> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
>> news:uY2FhGsBFHA.3840@.tk2msftngp13.phx.gbl...
>> Hi
>> Rather make sure that your drives are configured correctly.
>> Make sure your logs on RAID-10, and preferably your data on a separate
>> RAID-10 volume. RAID-5 hurts more than it helps.
>> As Geoff said, block size is not important, but getting maximum IO
>> throughput is.
>> Regards
>> --
>> Mike Epprecht, Microsoft SQL Server MVP
>> Zurich, Switzerland
>> IM: mike@.epprecht.net
>> MVP Program: http://www.microsoft.com/mvp
>> Blog: http://www.msmvps.com/epprecht/
>> "Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
>> news:uhfJBgXBFHA.2180@.TK2MSFTNGP12.phx.gbl...
>> Unless you have an overriding reason, go with the hardware
>> manufacturer's
>> recommendations. These are typically the default settings. Changing
>> the
>> NTFS block size usually has no measurable effect on system performance.
>> --
>> Geoff N. Hiten
>> Microsoft SQL Server MVP
>> Senior Database Administrator
>> Careerbuilder.com
>> I support the Professional Association for SQL Server
>> www.sqlpass.org
>> "Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
>> news:B77A9710-86AA-4319-B539-6F65D91E5ECA@.microsoft.com...
>> >
>> > I have a SQL Server 2000 Enterprise database that processes 300
>> > transactions/sec, approximately 300 users, and 250 GB in size. How
>> > can
>> I
>> > determine the the appropriate disk block size that should be on my
>> system.
>> >
>> > Thanks,
>> >
>> >
>>
>>
>|||Yes but my point was don't take that as a fixed size. It limits the max to
that value but the read ahead can be 8K as well if the conditions do not
allow for all 128K or higher.
--
Andrew J. Kelly SQL MVP
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:uJBYOuBCFHA.2568@.TK2MSFTNGP10.phx.gbl...
> Well, that doc confirmed at least part of what I read previously:
> "SQL Server limits a single read-ahead request depth to 128 pages on most
> editions. However, Microsoft SQL Server Enterprise Edition raises the
> limit
> to 1,024 pages."
> Sincerely,
>
> Anthony Thomas
>
> --
> "Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
> news:OIoyclBCFHA.3588@.TK2MSFTNGP11.phx.gbl...
> As always, thanks Andrew. Yes, I am well aware of having to "filter" read
> information and make judgement calls on the sources reliability. The
> article I read seemed respectable, though. If I come across it, I'll post
> a
> "What's up" response.
> Sincerely,
>
> Anthony Thomas
>
> --
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:ewn3Iz7BFHA.2260@.TK2MSFTNGP14.phx.gbl...
> Not sure which document that came from either. The read ahead is actually
> dynamic in the size of each read ahead request based somewhat on how
> fragmented the data is. It can be 8K or 128K, it just depends on if it is
> possible to do so or not. Most of the documentation for 2000 is wrong in
> that it may state you need multiple files to spawn multiple threads. That
> is definitely not the case and this is supposed to be fixed in the 2005
> docs. Yes it can spawn a thread of each file but it is not limited to a
> single thread per file. Even with a single file it is possible to spawn
> multiple threads to read the same file. Here is what looks like a very
> interesting article on IO in 2000. I haven't had a chance to read it yet
> (hope to in the next few days) but it looks very promising.
> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx
> --
> Andrew J. Kelly SQL MVP
>
> "Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
> news:u8xc256BFHA.1564@.TK2MSFTNGP09.phx.gbl...
>>I just read that SS2K SE spawns Read Ahead requests in 4xExtents, 128 KB
>> chunks, and spawns a thread per data file in each Filegroup. It also
>> mentioned that EE can exceed the number of concurrent extents per request
>> and managed the thread spawning more efficiently. But, damn if I can't
>> remember where I came across the information.
>> Anthony Thomas
>>
>> --
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:uNjLEV5BFHA.2180@.TK2MSFTNGP10.phx.gbl...
>> An I agree with your comments except for the last one:
>> Furthermore, more files per Filegroup will spawn
>> additional disk threads per request.
>> <<
>> That was true with 7.0 but is not necessarily true with SQL2000. In 2000
>> Sql Server can spawn multiple threads to read a single file as well. So
>> just having multiple files does not mean it will be more effecient or
>> spawn
>> more threads than a single file.
>>
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
>> news:ep16rw1BFHA.3592@.TK2MSFTNGP09.phx.gbl...
>> Not that I disagree with the RAID-10 argument, I don't; however, I do
>> disagree with your comments about NTFS Cluster Sizes (a.k.a., block
>> size).
>> The MS SQL Server Operations Manual makes a recommendation of using
>> 64-KB
>> cluster sizes. We have done so with a dramatic total disk I/O
>> throughput.
>> It makes sense. SQL Server reads and writes data in 8KB data pages, but
>> issues Scatter-Gather I/O requests in 4 or more Extents (8 x 8KB pages)
>> simultaneously. If your block sizes are on these 8KB boundaries, SQL
>> Server
>> Disk I/O requests can be more efficient.
>> Your biggest disk throughput bottleneck is total number of I/O
>> Operations
>> a
>> single spindle can handle. You're throughput will be based on type of
>> access, sequential versus random, and the size of each request.
>> Optimizing
>> the request sizes coupled with a fixed maximum number of I/O Ops, should
>> make your system as efficient as possible.
>> Nevertheless, and along the same lines as the RAID arguments already
>> presented, this is also suggested from the Operations Manual: more
>> smaller
>> disks are more efficient than fewer larger disks. This would come into
>> play
>> in the 0 part of the RAID-10 argument. Striping allows more physical
>> spindles to fulfill requests, each one capable of servicing a
>> theoretical
>> maximum number of I/O Ops. Furthermore, more files per Filegroup will
>> spawn
>> additional disk threads per request. This coupled with number of disks
>> can
>> greatly improve throughput.
>> Sincerely,
>>
>> Anthony Thomas
>>
>> --
>> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
>> news:uY2FhGsBFHA.3840@.tk2msftngp13.phx.gbl...
>> Hi
>> Rather make sure that your drives are configured correctly.
>> Make sure your logs on RAID-10, and preferably your data on a separate
>> RAID-10 volume. RAID-5 hurts more than it helps.
>> As Geoff said, block size is not important, but getting maximum IO
>> throughput is.
>> Regards
>> --
>> Mike Epprecht, Microsoft SQL Server MVP
>> Zurich, Switzerland
>> IM: mike@.epprecht.net
>> MVP Program: http://www.microsoft.com/mvp
>> Blog: http://www.msmvps.com/epprecht/
>> "Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
>> news:uhfJBgXBFHA.2180@.TK2MSFTNGP12.phx.gbl...
>> Unless you have an overriding reason, go with the hardware
>> manufacturer's
>> recommendations. These are typically the default settings. Changing
>> the
>> NTFS block size usually has no measurable effect on system performance.
>> --
>> Geoff N. Hiten
>> Microsoft SQL Server MVP
>> Senior Database Administrator
>> Careerbuilder.com
>> I support the Professional Association for SQL Server
>> www.sqlpass.org
>> "Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
>> news:B77A9710-86AA-4319-B539-6F65D91E5ECA@.microsoft.com...
>> >
>> > I have a SQL Server 2000 Enterprise database that processes 300
>> > transactions/sec, approximately 300 users, and 250 GB in size. How
>> > can
>> I
>> > determine the the appropriate disk block size that should be on my
>> system.
>> >
>> > Thanks,
>> >
>> >
>>
>>
>>
>
Subscribe to:
Posts (Atom)