DBCC SHOWCONTIG WITH TABLERESULTS will show a ROW column for an index's row count. However, on an important table with quite a few indexes that I am using as a baseline, ROW is NULL for all indexes. Why? BOL definines ROWS as:-
"Number of data or index records at that level of the index. For a heap, this is the number of data records in the entire heap."
By the way, if I select from sysindexes for a one of the above indexes, there is a correct rowcount for the index.
Any ideas?
CliveI found it. I was using the 'WITH FAST' parameter. Without FAST, it returns the row count.
Clive
Showing posts with label showcontig. Show all posts
Showing posts with label showcontig. Show all posts
Sunday, March 25, 2012
DBCC ShowContig With TableResults
I've looked through BOL, and tried all the samples I could find, but I can't seem to get it to work. I need either the results from ShowContig or more specifically the Scan Density number. I need this in a table or a recordset so I can get to it from an .exe. So far I haven't been able to get this to work.
This needs to work with both SQL 7.0 & 2000. Some of the options aren't avaible under 7.0, and the parameters differ slightly.
My code runs it, but doesn't put the results into the temp table.
Does anyone know the way to simply get the Scan Density value without using ShowContig?
Here's the code I've got so far:
-- SQL
CREATE TABLE #fraglist (
ObjectName CHAR (255),
ObjectId INT,
IndexName CHAR (255),
IndexId INT,
Lvl INT,
CountPages INT,
CountRows INT,
MinRecSize INT,
MaxRecSize INT,
AvgRecSize INT,
ForRecCount INT,
Extents INT,
ExtentSwitches INT,
AvgFreeBytes INT,
AvgPageDensity INT,
ScanDensity DECIMAL,
BestCount INT,
ActualCount INT,
LogicalFrag DECIMAL,
ExtentFrag DECIMAL)
INSERT INTO #fraglist
(ObjectName,
ObjectId,
IndexName,
IndexId,
Lvl,
CountPages,
CountRows,
MinRecSize,
MaxRecSize,
AvgRecSize,
ForRecCount,
Extents,
ExtentSwitches,
AvgFreeBytes,
AvgPageDensity,
ScanDensity,
BestCount,
ActualCount,
LogicalFrag,
ExtentFrag)
EXEC ('DBCC SHOWCONTIG (270624007, 2)
WITH TABLERESULTS, NO_INFOMSGS')
Select * from #fraglist
Drop table #fraglist
-- Output
DBCC SHOWCONTIG scanning 'tbInventory' table...
Table: 'tbInventory' (270624007); index ID: 2, database ID: 5
LEAF level scan performed.
- Pages Scanned........................: 65
- Extents Scanned.......................: 9
- Extent Switches.......................: 8
- Avg. Pages per Extent..................: 7.2
- Scan Density [Best Count:Actual Count]......: 100.00% [9:9]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 11.11%
- Avg. Bytes Free per Page................: 112.8
- Avg. Page Density (full)................: 98.61%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
(0 row(s) affected)
ObjectName ObjectId IndexName IndexId Lvl CountPages CountRows MinRecSize MaxRecSize AvgRecSize ForRecCount Extents ExtentSwitches AvgFreeBytes AvgPageDensity ScanDensity BestCount ActualCount LogicalFrag ExtentFrag
---------------------------------------------------------------------------- ---- ---------------------------------------------------------------------------- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----- ---- ----- ------- ---- ---- ------- -------
(0 row(s) affected)
Thanks!Hi
I ran the script that you posted on SQL Server 2000, however I changed the DBCC command to :
EXEC ('DBCC SHOWCONTIG (''authors'') WITH TABLERESULTS, NO_INFOMSGS')
Since I don't have your table, I used authors in Pubs. This worked fine:
ObjectName ObjectId IndexName
----- ---- ------
authors 1977058079 UPKCL_auidind
I didn't include the whole output, it just wraps around.
For SQL Server 7.0 you'll need to do some coding:
set nocount on
create table #Density(line varchar(80))
create table #contig (tb varchar(40), value float, description varchar(30))
declare @.cmd varchar(250)
declare @.tb sysname
declare @.id int
declare tb_cur cursor
for
select table_name,
object_id(table_name)
from Information_Schema.tables
where table_type = 'base table'
open tb_cur
fetch next from tb_cur into @.tb, @.id
while @.@.fetch_status = 0 begin
select @.cmd = 'use ' + db_name()
+ ' insert #Density EXECUTE master..xp_cmdshell ''isql -E -Snike -dSDMT -Q"dbcc showcontig (' + convert(varchar(100),@.id) + ')"'''
exec (@.cmd)
insert #contig
SELECT @.tb,
CONVERT(FLOAT,REPLACE(SUBSTRING(line, PATINDEX('%: %', line) + 2, 6),'%','')),
CONVERT(VARCHAR(30),
CASE
WHEN line LIKE '%Pages Scanned%' THEN 'Pages Scanned'
WHEN line LIKE '%Extents Scanned%' THEN 'Extents Scanned'
WHEN line LIKE '%Extent Switches%' THEN 'Extent Switches'
WHEN line LIKE '%Avg. Pages per %' THEN 'Avg. Pages per Extent'
WHEN line LIKE '%Scan Density%Best%' THEN 'Scan Density'
WHEN line LIKE '%Logical Scan%' THEN 'Logical Scan Fragmentation'
WHEN line LIKE '%Extent Scan%' THEN 'Extent Scan Fragmentation'
WHEN line LIKE '%Avg. Bytes Free%' THEN 'Avg. Bytes Free per Page'
WHEN line LIKE '%Avg. Page Density%' THEN 'Avg. Page Density (full)'
ELSE Line
END)
FROM #Density
WHERE line LIKE '%- %'
truncate table #Density
fetch next from tb_cur into @.tb, @.id
end
close tb_cur
deallocate tb_cur
select *
from #contig
drop table #Density
drop table #contig
go
It's not the fastest code but it works.|||I've been working with your code (thank you!) and also doing some other testing. This is interesting...
-- SQL2000
EXEC ('DBCC SHOWCONTIG (''authors'') WITH TABLERESULTS, NO_INFOMSGS')
Produces 1 row of data, looks like you did a select top 1 * from a table.
-- SQL7
EXEC ('DBCC SHOWCONTIG (117575457) WITH TABLERESULTS, NO_INFOMSGS')
or
dbcc showcontig (117575457, 1) with TableResults
Produces:
DBCC SHOWCONTIG scanning 'authors' table...
Table: 'authors' (117575457); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 1
- Extents Scanned.......................: 1
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 6008.0
- Avg. Page Density (full)................: 25.77%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
It doesn't look like SQL7 is honoring the TableResults parm.
Unfortunately the example you provided (which does work) runs extremely slow on our server. Not sure exactly why, none of it looks that complicated. I'm guessing it's because we are shelling out to isql??|||Is there any way to use OpenRowSet with this DBCC Command?
This needs to work with both SQL 7.0 & 2000. Some of the options aren't avaible under 7.0, and the parameters differ slightly.
My code runs it, but doesn't put the results into the temp table.
Does anyone know the way to simply get the Scan Density value without using ShowContig?
Here's the code I've got so far:
-- SQL
CREATE TABLE #fraglist (
ObjectName CHAR (255),
ObjectId INT,
IndexName CHAR (255),
IndexId INT,
Lvl INT,
CountPages INT,
CountRows INT,
MinRecSize INT,
MaxRecSize INT,
AvgRecSize INT,
ForRecCount INT,
Extents INT,
ExtentSwitches INT,
AvgFreeBytes INT,
AvgPageDensity INT,
ScanDensity DECIMAL,
BestCount INT,
ActualCount INT,
LogicalFrag DECIMAL,
ExtentFrag DECIMAL)
INSERT INTO #fraglist
(ObjectName,
ObjectId,
IndexName,
IndexId,
Lvl,
CountPages,
CountRows,
MinRecSize,
MaxRecSize,
AvgRecSize,
ForRecCount,
Extents,
ExtentSwitches,
AvgFreeBytes,
AvgPageDensity,
ScanDensity,
BestCount,
ActualCount,
LogicalFrag,
ExtentFrag)
EXEC ('DBCC SHOWCONTIG (270624007, 2)
WITH TABLERESULTS, NO_INFOMSGS')
Select * from #fraglist
Drop table #fraglist
-- Output
DBCC SHOWCONTIG scanning 'tbInventory' table...
Table: 'tbInventory' (270624007); index ID: 2, database ID: 5
LEAF level scan performed.
- Pages Scanned........................: 65
- Extents Scanned.......................: 9
- Extent Switches.......................: 8
- Avg. Pages per Extent..................: 7.2
- Scan Density [Best Count:Actual Count]......: 100.00% [9:9]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 11.11%
- Avg. Bytes Free per Page................: 112.8
- Avg. Page Density (full)................: 98.61%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
(0 row(s) affected)
ObjectName ObjectId IndexName IndexId Lvl CountPages CountRows MinRecSize MaxRecSize AvgRecSize ForRecCount Extents ExtentSwitches AvgFreeBytes AvgPageDensity ScanDensity BestCount ActualCount LogicalFrag ExtentFrag
---------------------------------------------------------------------------- ---- ---------------------------------------------------------------------------- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----- ---- ----- ------- ---- ---- ------- -------
(0 row(s) affected)
Thanks!Hi
I ran the script that you posted on SQL Server 2000, however I changed the DBCC command to :
EXEC ('DBCC SHOWCONTIG (''authors'') WITH TABLERESULTS, NO_INFOMSGS')
Since I don't have your table, I used authors in Pubs. This worked fine:
ObjectName ObjectId IndexName
----- ---- ------
authors 1977058079 UPKCL_auidind
I didn't include the whole output, it just wraps around.
For SQL Server 7.0 you'll need to do some coding:
set nocount on
create table #Density(line varchar(80))
create table #contig (tb varchar(40), value float, description varchar(30))
declare @.cmd varchar(250)
declare @.tb sysname
declare @.id int
declare tb_cur cursor
for
select table_name,
object_id(table_name)
from Information_Schema.tables
where table_type = 'base table'
open tb_cur
fetch next from tb_cur into @.tb, @.id
while @.@.fetch_status = 0 begin
select @.cmd = 'use ' + db_name()
+ ' insert #Density EXECUTE master..xp_cmdshell ''isql -E -Snike -dSDMT -Q"dbcc showcontig (' + convert(varchar(100),@.id) + ')"'''
exec (@.cmd)
insert #contig
SELECT @.tb,
CONVERT(FLOAT,REPLACE(SUBSTRING(line, PATINDEX('%: %', line) + 2, 6),'%','')),
CONVERT(VARCHAR(30),
CASE
WHEN line LIKE '%Pages Scanned%' THEN 'Pages Scanned'
WHEN line LIKE '%Extents Scanned%' THEN 'Extents Scanned'
WHEN line LIKE '%Extent Switches%' THEN 'Extent Switches'
WHEN line LIKE '%Avg. Pages per %' THEN 'Avg. Pages per Extent'
WHEN line LIKE '%Scan Density%Best%' THEN 'Scan Density'
WHEN line LIKE '%Logical Scan%' THEN 'Logical Scan Fragmentation'
WHEN line LIKE '%Extent Scan%' THEN 'Extent Scan Fragmentation'
WHEN line LIKE '%Avg. Bytes Free%' THEN 'Avg. Bytes Free per Page'
WHEN line LIKE '%Avg. Page Density%' THEN 'Avg. Page Density (full)'
ELSE Line
END)
FROM #Density
WHERE line LIKE '%- %'
truncate table #Density
fetch next from tb_cur into @.tb, @.id
end
close tb_cur
deallocate tb_cur
select *
from #contig
drop table #Density
drop table #contig
go
It's not the fastest code but it works.|||I've been working with your code (thank you!) and also doing some other testing. This is interesting...
-- SQL2000
EXEC ('DBCC SHOWCONTIG (''authors'') WITH TABLERESULTS, NO_INFOMSGS')
Produces 1 row of data, looks like you did a select top 1 * from a table.
-- SQL7
EXEC ('DBCC SHOWCONTIG (117575457) WITH TABLERESULTS, NO_INFOMSGS')
or
dbcc showcontig (117575457, 1) with TableResults
Produces:
DBCC SHOWCONTIG scanning 'authors' table...
Table: 'authors' (117575457); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 1
- Extents Scanned.......................: 1
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 6008.0
- Avg. Page Density (full)................: 25.77%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
It doesn't look like SQL7 is honoring the TableResults parm.
Unfortunately the example you provided (which does work) runs extremely slow on our server. Not sure exactly why, none of it looks that complicated. I'm guessing it's because we are shelling out to isql??|||Is there any way to use OpenRowSet with this DBCC Command?
DBCC SHOWCONTIG shows no result
Hello
Trying to get some fragmentation info back on SQL2000 tables but no results
are returned. Is this a bug?
DBCC SHOWCONTIG ('dbo.[MsgAudit_CBOT]') WITH FAST, TABLERESULTS,
ALL_INDEXES, NO_INFOMSGS
DBCC SHOWCONTIG ('MsgAudit_CBOT')
DBCC SHOWCONTIG ('dbo.MsgAudit_CBOT')
-- all above tables exist and have clustered index with millions of rows.
tks
all queries produce no result:
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
--
-- cranfield, DBAAre you certain you really execute the queries? Press the "execute" button? Also, can you try using
OSQL.EXE? That DBCC should give you an error message if the table doesn't exist.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Cranfield" <alan_cranfield@.msn.co.za> wrote in message
news:A7E3358D-4814-49C6-9E48-451F6CB20EA1@.microsoft.com...
> Hello
> Trying to get some fragmentation info back on SQL2000 tables but no results
> are returned. Is this a bug?
> DBCC SHOWCONTIG ('dbo.[MsgAudit_CBOT]') WITH FAST, TABLERESULTS,
> ALL_INDEXES, NO_INFOMSGS
> DBCC SHOWCONTIG ('MsgAudit_CBOT')
> DBCC SHOWCONTIG ('dbo.MsgAudit_CBOT')
> -- all above tables exist and have clustered index with millions of rows.
> tks
>
> all queries produce no result:
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
>
> --
> -- cranfield, DBA|||I am certain query is executed. Have also tried osql - still no results, just
as below. My SQL2005 table returns results but NOT SQL2000
1> use eqmclog
2> go
1> dbcc showcontig('providersourceinfo')
2> go
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
1>
very strange this...
--
-- cranfield, DBA
"Tibor Karaszi" wrote:
> Are you certain you really execute the queries? Press the "execute" button? Also, can you try using
> OSQL.EXE? That DBCC should give you an error message if the table doesn't exist.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Cranfield" <alan_cranfield@.msn.co.za> wrote in message
> news:A7E3358D-4814-49C6-9E48-451F6CB20EA1@.microsoft.com...
> > Hello
> >
> > Trying to get some fragmentation info back on SQL2000 tables but no results
> > are returned. Is this a bug?
> >
> > DBCC SHOWCONTIG ('dbo.[MsgAudit_CBOT]') WITH FAST, TABLERESULTS,
> > ALL_INDEXES, NO_INFOMSGS
> >
> > DBCC SHOWCONTIG ('MsgAudit_CBOT')
> >
> > DBCC SHOWCONTIG ('dbo.MsgAudit_CBOT')
> >
> > -- all above tables exist and have clustered index with millions of rows.
> >
> > tks
> >
> >
> > all queries produce no result:
> >
> > DBCC execution completed. If DBCC printed error messages, contact your
> > system administrator.
> >
> >
> >
> > --
> > -- cranfield, DBA
>|||What happens if you omit the single quotes? As in DBCC SHOWCONTIG
(MsgAudit_CBOT)? Looking at the syntax for DBCC statements in SQL Server
2000 BOL, it does not indicate the use of single quotes around the object
name, but does in the SQL Server 2005 topics.
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://technet.microsoft.com/en-us/sqlserver/bb428874.aspx
"Cranfield" <alan_cranfield@.msn.co.za> wrote in message
news:2FAEA2D1-46ED-4248-BB68-8C0B62B90E52@.microsoft.com...
>I am certain query is executed. Have also tried osql - still no results,
>just
> as below. My SQL2005 table returns results but NOT SQL2000
> 1> use eqmclog
> 2> go
> 1> dbcc showcontig('providersourceinfo')
> 2> go
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> 1>
> very strange this...
> --
> -- cranfield, DBA
>
> "Tibor Karaszi" wrote:
>> Are you certain you really execute the queries? Press the "execute"
>> button? Also, can you try using
>> OSQL.EXE? That DBCC should give you an error message if the table doesn't
>> exist.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Cranfield" <alan_cranfield@.msn.co.za> wrote in message
>> news:A7E3358D-4814-49C6-9E48-451F6CB20EA1@.microsoft.com...
>> > Hello
>> >
>> > Trying to get some fragmentation info back on SQL2000 tables but no
>> > results
>> > are returned. Is this a bug?
>> >
>> > DBCC SHOWCONTIG ('dbo.[MsgAudit_CBOT]') WITH FAST, TABLERESULTS,
>> > ALL_INDEXES, NO_INFOMSGS
>> >
>> > DBCC SHOWCONTIG ('MsgAudit_CBOT')
>> >
>> > DBCC SHOWCONTIG ('dbo.MsgAudit_CBOT')
>> >
>> > -- all above tables exist and have clustered index with millions of
>> > rows.
>> >
>> > tks
>> >
>> >
>> > all queries produce no result:
>> >
>> > DBCC execution completed. If DBCC printed error messages, contact your
>> > system administrator.
>> >
>> >
>> >
>> > --
>> > -- cranfield, DBA|||no, omitting quotes makes no difference. MS uses quotes themselves in their
BOL samples.
Just to make sure I have the correct tables:
select 'DBCC SHOWCONTIG ('+name+')'
from sysobjects
where type = 'u'
DBCC SHOWCONTIG (DataRecoveryLog)
DBCC SHOWCONTIG (ProviderSourceInfo)
DBCC SHOWCONTIG (MsgAudit_CBOT)
DBCC SHOWCONTIG (MsgAudit_KCBT)
DBCC SHOWCONTIG (MsgAudit_MGEX)
DBCC SHOWCONTIG (MsgAudit_WCE)
DBCC SHOWCONTIG (MsgAudit_JADE)
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
-- cranfield, DBA
"Gail Erickson [MS]" wrote:
> What happens if you omit the single quotes? As in DBCC SHOWCONTIG
> (MsgAudit_CBOT)? Looking at the syntax for DBCC statements in SQL Server
> 2000 BOL, it does not indicate the use of single quotes around the object
> name, but does in the SQL Server 2005 topics.
> --
> Gail Erickson [MS]
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
> Download the latest version of Books Online from
> http://technet.microsoft.com/en-us/sqlserver/bb428874.aspx
> "Cranfield" <alan_cranfield@.msn.co.za> wrote in message
> news:2FAEA2D1-46ED-4248-BB68-8C0B62B90E52@.microsoft.com...
> >I am certain query is executed. Have also tried osql - still no results,
> >just
> > as below. My SQL2005 table returns results but NOT SQL2000
> >
> > 1> use eqmclog
> > 2> go
> > 1> dbcc showcontig('providersourceinfo')
> > 2> go
> > DBCC execution completed. If DBCC printed error messages, contact your
> > system administrator.
> > 1>
> >
> > very strange this...
> > --
> > -- cranfield, DBA
> >
> >
> > "Tibor Karaszi" wrote:
> >
> >> Are you certain you really execute the queries? Press the "execute"
> >> button? Also, can you try using
> >> OSQL.EXE? That DBCC should give you an error message if the table doesn't
> >> exist.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://sqlblog.com/blogs/tibor_karaszi
> >>
> >>
> >> "Cranfield" <alan_cranfield@.msn.co.za> wrote in message
> >> news:A7E3358D-4814-49C6-9E48-451F6CB20EA1@.microsoft.com...
> >> > Hello
> >> >
> >> > Trying to get some fragmentation info back on SQL2000 tables but no
> >> > results
> >> > are returned. Is this a bug?
> >> >
> >> > DBCC SHOWCONTIG ('dbo.[MsgAudit_CBOT]') WITH FAST, TABLERESULTS,
> >> > ALL_INDEXES, NO_INFOMSGS
> >> >
> >> > DBCC SHOWCONTIG ('MsgAudit_CBOT')
> >> >
> >> > DBCC SHOWCONTIG ('dbo.MsgAudit_CBOT')
> >> >
> >> > -- all above tables exist and have clustered index with millions of
> >> > rows.
> >> >
> >> > tks
> >> >
> >> >
> >> > all queries produce no result:
> >> >
> >> > DBCC execution completed. If DBCC printed error messages, contact your
> >> > system administrator.
> >> >
> >> >
> >> >
> >> > --
> >> > -- cranfield, DBA
> >>
>
>|||Can you run it in the pubs database? I ran your SELECT (which produces the DBCC commands) in pubs
against a SQL2K (8.00.194), and it produced results. Also, be careful to note that without
TABLERESULTS, you get text back (use the correct tab in QA results).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Cranfield" <alan_cranfield@.msn.co.za> wrote in message
news:97BDC55E-5F6E-4025-B576-0441777497DB@.microsoft.com...
> no, omitting quotes makes no difference. MS uses quotes themselves in their
> BOL samples.
> Just to make sure I have the correct tables:
> select 'DBCC SHOWCONTIG ('+name+')'
> from sysobjects
> where type = 'u'
> DBCC SHOWCONTIG (DataRecoveryLog)
> DBCC SHOWCONTIG (ProviderSourceInfo)
> DBCC SHOWCONTIG (MsgAudit_CBOT)
> DBCC SHOWCONTIG (MsgAudit_KCBT)
> DBCC SHOWCONTIG (MsgAudit_MGEX)
> DBCC SHOWCONTIG (MsgAudit_WCE)
> DBCC SHOWCONTIG (MsgAudit_JADE)
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
>
> --
> -- cranfield, DBA
>
> "Gail Erickson [MS]" wrote:
>> What happens if you omit the single quotes? As in DBCC SHOWCONTIG
>> (MsgAudit_CBOT)? Looking at the syntax for DBCC statements in SQL Server
>> 2000 BOL, it does not indicate the use of single quotes around the object
>> name, but does in the SQL Server 2005 topics.
>> --
>> Gail Erickson [MS]
>> SQL Server Documentation Team
>> This posting is provided "AS IS" with no warranties, and confers no rights
>> Download the latest version of Books Online from
>> http://technet.microsoft.com/en-us/sqlserver/bb428874.aspx
>> "Cranfield" <alan_cranfield@.msn.co.za> wrote in message
>> news:2FAEA2D1-46ED-4248-BB68-8C0B62B90E52@.microsoft.com...
>> >I am certain query is executed. Have also tried osql - still no results,
>> >just
>> > as below. My SQL2005 table returns results but NOT SQL2000
>> >
>> > 1> use eqmclog
>> > 2> go
>> > 1> dbcc showcontig('providersourceinfo')
>> > 2> go
>> > DBCC execution completed. If DBCC printed error messages, contact your
>> > system administrator.
>> > 1>
>> >
>> > very strange this...
>> > --
>> > -- cranfield, DBA
>> >
>> >
>> > "Tibor Karaszi" wrote:
>> >
>> >> Are you certain you really execute the queries? Press the "execute"
>> >> button? Also, can you try using
>> >> OSQL.EXE? That DBCC should give you an error message if the table doesn't
>> >> exist.
>> >>
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://sqlblog.com/blogs/tibor_karaszi
>> >>
>> >>
>> >> "Cranfield" <alan_cranfield@.msn.co.za> wrote in message
>> >> news:A7E3358D-4814-49C6-9E48-451F6CB20EA1@.microsoft.com...
>> >> > Hello
>> >> >
>> >> > Trying to get some fragmentation info back on SQL2000 tables but no
>> >> > results
>> >> > are returned. Is this a bug?
>> >> >
>> >> > DBCC SHOWCONTIG ('dbo.[MsgAudit_CBOT]') WITH FAST, TABLERESULTS,
>> >> > ALL_INDEXES, NO_INFOMSGS
>> >> >
>> >> > DBCC SHOWCONTIG ('MsgAudit_CBOT')
>> >> >
>> >> > DBCC SHOWCONTIG ('dbo.MsgAudit_CBOT')
>> >> >
>> >> > -- all above tables exist and have clustered index with millions of
>> >> > rows.
>> >> >
>> >> > tks
>> >> >
>> >> >
>> >> > all queries produce no result:
>> >> >
>> >> > DBCC execution completed. If DBCC printed error messages, contact your
>> >> > system administrator.
>> >> >
>> >> >
>> >> >
>> >> > --
>> >> > -- cranfield, DBA
>> >>
>>|||Hi Tibor
I have found the problem! These tables have 0 rows. DBCC SHOWCONTIG does
not return results for empty tables.
doh!
--
-- cranfield, DBA
"Tibor Karaszi" wrote:
> Can you run it in the pubs database? I ran your SELECT (which produces the DBCC commands) in pubs
> against a SQL2K (8.00.194), and it produced results. Also, be careful to note that without
> TABLERESULTS, you get text back (use the correct tab in QA results).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Cranfield" <alan_cranfield@.msn.co.za> wrote in message
> news:97BDC55E-5F6E-4025-B576-0441777497DB@.microsoft.com...
> > no, omitting quotes makes no difference. MS uses quotes themselves in their
> > BOL samples.
> >
> > Just to make sure I have the correct tables:
> >
> > select 'DBCC SHOWCONTIG ('+name+')'
> > from sysobjects
> > where type = 'u'
> >
> > DBCC SHOWCONTIG (DataRecoveryLog)
> > DBCC SHOWCONTIG (ProviderSourceInfo)
> > DBCC SHOWCONTIG (MsgAudit_CBOT)
> > DBCC SHOWCONTIG (MsgAudit_KCBT)
> > DBCC SHOWCONTIG (MsgAudit_MGEX)
> > DBCC SHOWCONTIG (MsgAudit_WCE)
> > DBCC SHOWCONTIG (MsgAudit_JADE)
> >
> > DBCC execution completed. If DBCC printed error messages, contact your
> > system administrator.
> > DBCC execution completed. If DBCC printed error messages, contact your
> > system administrator.
> > DBCC execution completed. If DBCC printed error messages, contact your
> > system administrator.
> > DBCC execution completed. If DBCC printed error messages, contact your
> > system administrator.
> > DBCC execution completed. If DBCC printed error messages, contact your
> > system administrator.
> > DBCC execution completed. If DBCC printed error messages, contact your
> > system administrator.
> > DBCC execution completed. If DBCC printed error messages, contact your
> > system administrator.
> >
> >
> >
> > --
> > -- cranfield, DBA
> >
> >
> > "Gail Erickson [MS]" wrote:
> >
> >> What happens if you omit the single quotes? As in DBCC SHOWCONTIG
> >> (MsgAudit_CBOT)? Looking at the syntax for DBCC statements in SQL Server
> >> 2000 BOL, it does not indicate the use of single quotes around the object
> >> name, but does in the SQL Server 2005 topics.
> >>
> >> --
> >> Gail Erickson [MS]
> >> SQL Server Documentation Team
> >> This posting is provided "AS IS" with no warranties, and confers no rights
> >> Download the latest version of Books Online from
> >> http://technet.microsoft.com/en-us/sqlserver/bb428874.aspx
> >>
> >> "Cranfield" <alan_cranfield@.msn.co.za> wrote in message
> >> news:2FAEA2D1-46ED-4248-BB68-8C0B62B90E52@.microsoft.com...
> >> >I am certain query is executed. Have also tried osql - still no results,
> >> >just
> >> > as below. My SQL2005 table returns results but NOT SQL2000
> >> >
> >> > 1> use eqmclog
> >> > 2> go
> >> > 1> dbcc showcontig('providersourceinfo')
> >> > 2> go
> >> > DBCC execution completed. If DBCC printed error messages, contact your
> >> > system administrator.
> >> > 1>
> >> >
> >> > very strange this...
> >> > --
> >> > -- cranfield, DBA
> >> >
> >> >
> >> > "Tibor Karaszi" wrote:
> >> >
> >> >> Are you certain you really execute the queries? Press the "execute"
> >> >> button? Also, can you try using
> >> >> OSQL.EXE? That DBCC should give you an error message if the table doesn't
> >> >> exist.
> >> >>
> >> >> --
> >> >> Tibor Karaszi, SQL Server MVP
> >> >> http://www.karaszi.com/sqlserver/default.asp
> >> >> http://sqlblog.com/blogs/tibor_karaszi
> >> >>
> >> >>
> >> >> "Cranfield" <alan_cranfield@.msn.co.za> wrote in message
> >> >> news:A7E3358D-4814-49C6-9E48-451F6CB20EA1@.microsoft.com...
> >> >> > Hello
> >> >> >
> >> >> > Trying to get some fragmentation info back on SQL2000 tables but no
> >> >> > results
> >> >> > are returned. Is this a bug?
> >> >> >
> >> >> > DBCC SHOWCONTIG ('dbo.[MsgAudit_CBOT]') WITH FAST, TABLERESULTS,
> >> >> > ALL_INDEXES, NO_INFOMSGS
> >> >> >
> >> >> > DBCC SHOWCONTIG ('MsgAudit_CBOT')
> >> >> >
> >> >> > DBCC SHOWCONTIG ('dbo.MsgAudit_CBOT')
> >> >> >
> >> >> > -- all above tables exist and have clustered index with millions of
> >> >> > rows.
> >> >> >
> >> >> > tks
> >> >> >
> >> >> >
> >> >> > all queries produce no result:
> >> >> >
> >> >> > DBCC execution completed. If DBCC printed error messages, contact your
> >> >> > system administrator.
> >> >> >
> >> >> >
> >> >> >
> >> >> > --
> >> >> > -- cranfield, DBA
> >> >>
> >>
> >>
> >>
>|||Ouch... That was not obvious! Thanks for posting back. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Cranfield" <alan_cranfield@.msn.co.za> wrote in message
news:FF387413-A310-4994-8A65-B118987CBF42@.microsoft.com...
> Hi Tibor
> I have found the problem! These tables have 0 rows. DBCC SHOWCONTIG does
> not return results for empty tables.
> doh!
> --
> -- cranfield, DBA
>
> "Tibor Karaszi" wrote:
>> Can you run it in the pubs database? I ran your SELECT (which produces the DBCC commands) in pubs
>> against a SQL2K (8.00.194), and it produced results. Also, be careful to note that without
>> TABLERESULTS, you get text back (use the correct tab in QA results).
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Cranfield" <alan_cranfield@.msn.co.za> wrote in message
>> news:97BDC55E-5F6E-4025-B576-0441777497DB@.microsoft.com...
>> > no, omitting quotes makes no difference. MS uses quotes themselves in their
>> > BOL samples.
>> >
>> > Just to make sure I have the correct tables:
>> >
>> > select 'DBCC SHOWCONTIG ('+name+')'
>> > from sysobjects
>> > where type = 'u'
>> >
>> > DBCC SHOWCONTIG (DataRecoveryLog)
>> > DBCC SHOWCONTIG (ProviderSourceInfo)
>> > DBCC SHOWCONTIG (MsgAudit_CBOT)
>> > DBCC SHOWCONTIG (MsgAudit_KCBT)
>> > DBCC SHOWCONTIG (MsgAudit_MGEX)
>> > DBCC SHOWCONTIG (MsgAudit_WCE)
>> > DBCC SHOWCONTIG (MsgAudit_JADE)
>> >
>> > DBCC execution completed. If DBCC printed error messages, contact your
>> > system administrator.
>> > DBCC execution completed. If DBCC printed error messages, contact your
>> > system administrator.
>> > DBCC execution completed. If DBCC printed error messages, contact your
>> > system administrator.
>> > DBCC execution completed. If DBCC printed error messages, contact your
>> > system administrator.
>> > DBCC execution completed. If DBCC printed error messages, contact your
>> > system administrator.
>> > DBCC execution completed. If DBCC printed error messages, contact your
>> > system administrator.
>> > DBCC execution completed. If DBCC printed error messages, contact your
>> > system administrator.
>> >
>> >
>> >
>> > --
>> > -- cranfield, DBA
>> >
>> >
>> > "Gail Erickson [MS]" wrote:
>> >
>> >> What happens if you omit the single quotes? As in DBCC SHOWCONTIG
>> >> (MsgAudit_CBOT)? Looking at the syntax for DBCC statements in SQL Server
>> >> 2000 BOL, it does not indicate the use of single quotes around the object
>> >> name, but does in the SQL Server 2005 topics.
>> >>
>> >> --
>> >> Gail Erickson [MS]
>> >> SQL Server Documentation Team
>> >> This posting is provided "AS IS" with no warranties, and confers no rights
>> >> Download the latest version of Books Online from
>> >> http://technet.microsoft.com/en-us/sqlserver/bb428874.aspx
>> >>
>> >> "Cranfield" <alan_cranfield@.msn.co.za> wrote in message
>> >> news:2FAEA2D1-46ED-4248-BB68-8C0B62B90E52@.microsoft.com...
>> >> >I am certain query is executed. Have also tried osql - still no results,
>> >> >just
>> >> > as below. My SQL2005 table returns results but NOT SQL2000
>> >> >
>> >> > 1> use eqmclog
>> >> > 2> go
>> >> > 1> dbcc showcontig('providersourceinfo')
>> >> > 2> go
>> >> > DBCC execution completed. If DBCC printed error messages, contact your
>> >> > system administrator.
>> >> > 1>
>> >> >
>> >> > very strange this...
>> >> > --
>> >> > -- cranfield, DBA
>> >> >
>> >> >
>> >> > "Tibor Karaszi" wrote:
>> >> >
>> >> >> Are you certain you really execute the queries? Press the "execute"
>> >> >> button? Also, can you try using
>> >> >> OSQL.EXE? That DBCC should give you an error message if the table doesn't
>> >> >> exist.
>> >> >>
>> >> >> --
>> >> >> Tibor Karaszi, SQL Server MVP
>> >> >> http://www.karaszi.com/sqlserver/default.asp
>> >> >> http://sqlblog.com/blogs/tibor_karaszi
>> >> >>
>> >> >>
>> >> >> "Cranfield" <alan_cranfield@.msn.co.za> wrote in message
>> >> >> news:A7E3358D-4814-49C6-9E48-451F6CB20EA1@.microsoft.com...
>> >> >> > Hello
>> >> >> >
>> >> >> > Trying to get some fragmentation info back on SQL2000 tables but no
>> >> >> > results
>> >> >> > are returned. Is this a bug?
>> >> >> >
>> >> >> > DBCC SHOWCONTIG ('dbo.[MsgAudit_CBOT]') WITH FAST, TABLERESULTS,
>> >> >> > ALL_INDEXES, NO_INFOMSGS
>> >> >> >
>> >> >> > DBCC SHOWCONTIG ('MsgAudit_CBOT')
>> >> >> >
>> >> >> > DBCC SHOWCONTIG ('dbo.MsgAudit_CBOT')
>> >> >> >
>> >> >> > -- all above tables exist and have clustered index with millions of
>> >> >> > rows.
>> >> >> >
>> >> >> > tks
>> >> >> >
>> >> >> >
>> >> >> > all queries produce no result:
>> >> >> >
>> >> >> > DBCC execution completed. If DBCC printed error messages, contact your
>> >> >> > system administrator.
>> >> >> >
>> >> >> >
>> >> >> >
>> >> >> > --
>> >> >> > -- cranfield, DBA
>> >> >>
>> >>
>> >>
>> >>
Trying to get some fragmentation info back on SQL2000 tables but no results
are returned. Is this a bug?
DBCC SHOWCONTIG ('dbo.[MsgAudit_CBOT]') WITH FAST, TABLERESULTS,
ALL_INDEXES, NO_INFOMSGS
DBCC SHOWCONTIG ('MsgAudit_CBOT')
DBCC SHOWCONTIG ('dbo.MsgAudit_CBOT')
-- all above tables exist and have clustered index with millions of rows.
tks
all queries produce no result:
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
--
-- cranfield, DBAAre you certain you really execute the queries? Press the "execute" button? Also, can you try using
OSQL.EXE? That DBCC should give you an error message if the table doesn't exist.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Cranfield" <alan_cranfield@.msn.co.za> wrote in message
news:A7E3358D-4814-49C6-9E48-451F6CB20EA1@.microsoft.com...
> Hello
> Trying to get some fragmentation info back on SQL2000 tables but no results
> are returned. Is this a bug?
> DBCC SHOWCONTIG ('dbo.[MsgAudit_CBOT]') WITH FAST, TABLERESULTS,
> ALL_INDEXES, NO_INFOMSGS
> DBCC SHOWCONTIG ('MsgAudit_CBOT')
> DBCC SHOWCONTIG ('dbo.MsgAudit_CBOT')
> -- all above tables exist and have clustered index with millions of rows.
> tks
>
> all queries produce no result:
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
>
> --
> -- cranfield, DBA|||I am certain query is executed. Have also tried osql - still no results, just
as below. My SQL2005 table returns results but NOT SQL2000
1> use eqmclog
2> go
1> dbcc showcontig('providersourceinfo')
2> go
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
1>
very strange this...
--
-- cranfield, DBA
"Tibor Karaszi" wrote:
> Are you certain you really execute the queries? Press the "execute" button? Also, can you try using
> OSQL.EXE? That DBCC should give you an error message if the table doesn't exist.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Cranfield" <alan_cranfield@.msn.co.za> wrote in message
> news:A7E3358D-4814-49C6-9E48-451F6CB20EA1@.microsoft.com...
> > Hello
> >
> > Trying to get some fragmentation info back on SQL2000 tables but no results
> > are returned. Is this a bug?
> >
> > DBCC SHOWCONTIG ('dbo.[MsgAudit_CBOT]') WITH FAST, TABLERESULTS,
> > ALL_INDEXES, NO_INFOMSGS
> >
> > DBCC SHOWCONTIG ('MsgAudit_CBOT')
> >
> > DBCC SHOWCONTIG ('dbo.MsgAudit_CBOT')
> >
> > -- all above tables exist and have clustered index with millions of rows.
> >
> > tks
> >
> >
> > all queries produce no result:
> >
> > DBCC execution completed. If DBCC printed error messages, contact your
> > system administrator.
> >
> >
> >
> > --
> > -- cranfield, DBA
>|||What happens if you omit the single quotes? As in DBCC SHOWCONTIG
(MsgAudit_CBOT)? Looking at the syntax for DBCC statements in SQL Server
2000 BOL, it does not indicate the use of single quotes around the object
name, but does in the SQL Server 2005 topics.
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://technet.microsoft.com/en-us/sqlserver/bb428874.aspx
"Cranfield" <alan_cranfield@.msn.co.za> wrote in message
news:2FAEA2D1-46ED-4248-BB68-8C0B62B90E52@.microsoft.com...
>I am certain query is executed. Have also tried osql - still no results,
>just
> as below. My SQL2005 table returns results but NOT SQL2000
> 1> use eqmclog
> 2> go
> 1> dbcc showcontig('providersourceinfo')
> 2> go
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> 1>
> very strange this...
> --
> -- cranfield, DBA
>
> "Tibor Karaszi" wrote:
>> Are you certain you really execute the queries? Press the "execute"
>> button? Also, can you try using
>> OSQL.EXE? That DBCC should give you an error message if the table doesn't
>> exist.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Cranfield" <alan_cranfield@.msn.co.za> wrote in message
>> news:A7E3358D-4814-49C6-9E48-451F6CB20EA1@.microsoft.com...
>> > Hello
>> >
>> > Trying to get some fragmentation info back on SQL2000 tables but no
>> > results
>> > are returned. Is this a bug?
>> >
>> > DBCC SHOWCONTIG ('dbo.[MsgAudit_CBOT]') WITH FAST, TABLERESULTS,
>> > ALL_INDEXES, NO_INFOMSGS
>> >
>> > DBCC SHOWCONTIG ('MsgAudit_CBOT')
>> >
>> > DBCC SHOWCONTIG ('dbo.MsgAudit_CBOT')
>> >
>> > -- all above tables exist and have clustered index with millions of
>> > rows.
>> >
>> > tks
>> >
>> >
>> > all queries produce no result:
>> >
>> > DBCC execution completed. If DBCC printed error messages, contact your
>> > system administrator.
>> >
>> >
>> >
>> > --
>> > -- cranfield, DBA|||no, omitting quotes makes no difference. MS uses quotes themselves in their
BOL samples.
Just to make sure I have the correct tables:
select 'DBCC SHOWCONTIG ('+name+')'
from sysobjects
where type = 'u'
DBCC SHOWCONTIG (DataRecoveryLog)
DBCC SHOWCONTIG (ProviderSourceInfo)
DBCC SHOWCONTIG (MsgAudit_CBOT)
DBCC SHOWCONTIG (MsgAudit_KCBT)
DBCC SHOWCONTIG (MsgAudit_MGEX)
DBCC SHOWCONTIG (MsgAudit_WCE)
DBCC SHOWCONTIG (MsgAudit_JADE)
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
-- cranfield, DBA
"Gail Erickson [MS]" wrote:
> What happens if you omit the single quotes? As in DBCC SHOWCONTIG
> (MsgAudit_CBOT)? Looking at the syntax for DBCC statements in SQL Server
> 2000 BOL, it does not indicate the use of single quotes around the object
> name, but does in the SQL Server 2005 topics.
> --
> Gail Erickson [MS]
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
> Download the latest version of Books Online from
> http://technet.microsoft.com/en-us/sqlserver/bb428874.aspx
> "Cranfield" <alan_cranfield@.msn.co.za> wrote in message
> news:2FAEA2D1-46ED-4248-BB68-8C0B62B90E52@.microsoft.com...
> >I am certain query is executed. Have also tried osql - still no results,
> >just
> > as below. My SQL2005 table returns results but NOT SQL2000
> >
> > 1> use eqmclog
> > 2> go
> > 1> dbcc showcontig('providersourceinfo')
> > 2> go
> > DBCC execution completed. If DBCC printed error messages, contact your
> > system administrator.
> > 1>
> >
> > very strange this...
> > --
> > -- cranfield, DBA
> >
> >
> > "Tibor Karaszi" wrote:
> >
> >> Are you certain you really execute the queries? Press the "execute"
> >> button? Also, can you try using
> >> OSQL.EXE? That DBCC should give you an error message if the table doesn't
> >> exist.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://sqlblog.com/blogs/tibor_karaszi
> >>
> >>
> >> "Cranfield" <alan_cranfield@.msn.co.za> wrote in message
> >> news:A7E3358D-4814-49C6-9E48-451F6CB20EA1@.microsoft.com...
> >> > Hello
> >> >
> >> > Trying to get some fragmentation info back on SQL2000 tables but no
> >> > results
> >> > are returned. Is this a bug?
> >> >
> >> > DBCC SHOWCONTIG ('dbo.[MsgAudit_CBOT]') WITH FAST, TABLERESULTS,
> >> > ALL_INDEXES, NO_INFOMSGS
> >> >
> >> > DBCC SHOWCONTIG ('MsgAudit_CBOT')
> >> >
> >> > DBCC SHOWCONTIG ('dbo.MsgAudit_CBOT')
> >> >
> >> > -- all above tables exist and have clustered index with millions of
> >> > rows.
> >> >
> >> > tks
> >> >
> >> >
> >> > all queries produce no result:
> >> >
> >> > DBCC execution completed. If DBCC printed error messages, contact your
> >> > system administrator.
> >> >
> >> >
> >> >
> >> > --
> >> > -- cranfield, DBA
> >>
>
>|||Can you run it in the pubs database? I ran your SELECT (which produces the DBCC commands) in pubs
against a SQL2K (8.00.194), and it produced results. Also, be careful to note that without
TABLERESULTS, you get text back (use the correct tab in QA results).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Cranfield" <alan_cranfield@.msn.co.za> wrote in message
news:97BDC55E-5F6E-4025-B576-0441777497DB@.microsoft.com...
> no, omitting quotes makes no difference. MS uses quotes themselves in their
> BOL samples.
> Just to make sure I have the correct tables:
> select 'DBCC SHOWCONTIG ('+name+')'
> from sysobjects
> where type = 'u'
> DBCC SHOWCONTIG (DataRecoveryLog)
> DBCC SHOWCONTIG (ProviderSourceInfo)
> DBCC SHOWCONTIG (MsgAudit_CBOT)
> DBCC SHOWCONTIG (MsgAudit_KCBT)
> DBCC SHOWCONTIG (MsgAudit_MGEX)
> DBCC SHOWCONTIG (MsgAudit_WCE)
> DBCC SHOWCONTIG (MsgAudit_JADE)
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
>
> --
> -- cranfield, DBA
>
> "Gail Erickson [MS]" wrote:
>> What happens if you omit the single quotes? As in DBCC SHOWCONTIG
>> (MsgAudit_CBOT)? Looking at the syntax for DBCC statements in SQL Server
>> 2000 BOL, it does not indicate the use of single quotes around the object
>> name, but does in the SQL Server 2005 topics.
>> --
>> Gail Erickson [MS]
>> SQL Server Documentation Team
>> This posting is provided "AS IS" with no warranties, and confers no rights
>> Download the latest version of Books Online from
>> http://technet.microsoft.com/en-us/sqlserver/bb428874.aspx
>> "Cranfield" <alan_cranfield@.msn.co.za> wrote in message
>> news:2FAEA2D1-46ED-4248-BB68-8C0B62B90E52@.microsoft.com...
>> >I am certain query is executed. Have also tried osql - still no results,
>> >just
>> > as below. My SQL2005 table returns results but NOT SQL2000
>> >
>> > 1> use eqmclog
>> > 2> go
>> > 1> dbcc showcontig('providersourceinfo')
>> > 2> go
>> > DBCC execution completed. If DBCC printed error messages, contact your
>> > system administrator.
>> > 1>
>> >
>> > very strange this...
>> > --
>> > -- cranfield, DBA
>> >
>> >
>> > "Tibor Karaszi" wrote:
>> >
>> >> Are you certain you really execute the queries? Press the "execute"
>> >> button? Also, can you try using
>> >> OSQL.EXE? That DBCC should give you an error message if the table doesn't
>> >> exist.
>> >>
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://sqlblog.com/blogs/tibor_karaszi
>> >>
>> >>
>> >> "Cranfield" <alan_cranfield@.msn.co.za> wrote in message
>> >> news:A7E3358D-4814-49C6-9E48-451F6CB20EA1@.microsoft.com...
>> >> > Hello
>> >> >
>> >> > Trying to get some fragmentation info back on SQL2000 tables but no
>> >> > results
>> >> > are returned. Is this a bug?
>> >> >
>> >> > DBCC SHOWCONTIG ('dbo.[MsgAudit_CBOT]') WITH FAST, TABLERESULTS,
>> >> > ALL_INDEXES, NO_INFOMSGS
>> >> >
>> >> > DBCC SHOWCONTIG ('MsgAudit_CBOT')
>> >> >
>> >> > DBCC SHOWCONTIG ('dbo.MsgAudit_CBOT')
>> >> >
>> >> > -- all above tables exist and have clustered index with millions of
>> >> > rows.
>> >> >
>> >> > tks
>> >> >
>> >> >
>> >> > all queries produce no result:
>> >> >
>> >> > DBCC execution completed. If DBCC printed error messages, contact your
>> >> > system administrator.
>> >> >
>> >> >
>> >> >
>> >> > --
>> >> > -- cranfield, DBA
>> >>
>>|||Hi Tibor
I have found the problem! These tables have 0 rows. DBCC SHOWCONTIG does
not return results for empty tables.
doh!
--
-- cranfield, DBA
"Tibor Karaszi" wrote:
> Can you run it in the pubs database? I ran your SELECT (which produces the DBCC commands) in pubs
> against a SQL2K (8.00.194), and it produced results. Also, be careful to note that without
> TABLERESULTS, you get text back (use the correct tab in QA results).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Cranfield" <alan_cranfield@.msn.co.za> wrote in message
> news:97BDC55E-5F6E-4025-B576-0441777497DB@.microsoft.com...
> > no, omitting quotes makes no difference. MS uses quotes themselves in their
> > BOL samples.
> >
> > Just to make sure I have the correct tables:
> >
> > select 'DBCC SHOWCONTIG ('+name+')'
> > from sysobjects
> > where type = 'u'
> >
> > DBCC SHOWCONTIG (DataRecoveryLog)
> > DBCC SHOWCONTIG (ProviderSourceInfo)
> > DBCC SHOWCONTIG (MsgAudit_CBOT)
> > DBCC SHOWCONTIG (MsgAudit_KCBT)
> > DBCC SHOWCONTIG (MsgAudit_MGEX)
> > DBCC SHOWCONTIG (MsgAudit_WCE)
> > DBCC SHOWCONTIG (MsgAudit_JADE)
> >
> > DBCC execution completed. If DBCC printed error messages, contact your
> > system administrator.
> > DBCC execution completed. If DBCC printed error messages, contact your
> > system administrator.
> > DBCC execution completed. If DBCC printed error messages, contact your
> > system administrator.
> > DBCC execution completed. If DBCC printed error messages, contact your
> > system administrator.
> > DBCC execution completed. If DBCC printed error messages, contact your
> > system administrator.
> > DBCC execution completed. If DBCC printed error messages, contact your
> > system administrator.
> > DBCC execution completed. If DBCC printed error messages, contact your
> > system administrator.
> >
> >
> >
> > --
> > -- cranfield, DBA
> >
> >
> > "Gail Erickson [MS]" wrote:
> >
> >> What happens if you omit the single quotes? As in DBCC SHOWCONTIG
> >> (MsgAudit_CBOT)? Looking at the syntax for DBCC statements in SQL Server
> >> 2000 BOL, it does not indicate the use of single quotes around the object
> >> name, but does in the SQL Server 2005 topics.
> >>
> >> --
> >> Gail Erickson [MS]
> >> SQL Server Documentation Team
> >> This posting is provided "AS IS" with no warranties, and confers no rights
> >> Download the latest version of Books Online from
> >> http://technet.microsoft.com/en-us/sqlserver/bb428874.aspx
> >>
> >> "Cranfield" <alan_cranfield@.msn.co.za> wrote in message
> >> news:2FAEA2D1-46ED-4248-BB68-8C0B62B90E52@.microsoft.com...
> >> >I am certain query is executed. Have also tried osql - still no results,
> >> >just
> >> > as below. My SQL2005 table returns results but NOT SQL2000
> >> >
> >> > 1> use eqmclog
> >> > 2> go
> >> > 1> dbcc showcontig('providersourceinfo')
> >> > 2> go
> >> > DBCC execution completed. If DBCC printed error messages, contact your
> >> > system administrator.
> >> > 1>
> >> >
> >> > very strange this...
> >> > --
> >> > -- cranfield, DBA
> >> >
> >> >
> >> > "Tibor Karaszi" wrote:
> >> >
> >> >> Are you certain you really execute the queries? Press the "execute"
> >> >> button? Also, can you try using
> >> >> OSQL.EXE? That DBCC should give you an error message if the table doesn't
> >> >> exist.
> >> >>
> >> >> --
> >> >> Tibor Karaszi, SQL Server MVP
> >> >> http://www.karaszi.com/sqlserver/default.asp
> >> >> http://sqlblog.com/blogs/tibor_karaszi
> >> >>
> >> >>
> >> >> "Cranfield" <alan_cranfield@.msn.co.za> wrote in message
> >> >> news:A7E3358D-4814-49C6-9E48-451F6CB20EA1@.microsoft.com...
> >> >> > Hello
> >> >> >
> >> >> > Trying to get some fragmentation info back on SQL2000 tables but no
> >> >> > results
> >> >> > are returned. Is this a bug?
> >> >> >
> >> >> > DBCC SHOWCONTIG ('dbo.[MsgAudit_CBOT]') WITH FAST, TABLERESULTS,
> >> >> > ALL_INDEXES, NO_INFOMSGS
> >> >> >
> >> >> > DBCC SHOWCONTIG ('MsgAudit_CBOT')
> >> >> >
> >> >> > DBCC SHOWCONTIG ('dbo.MsgAudit_CBOT')
> >> >> >
> >> >> > -- all above tables exist and have clustered index with millions of
> >> >> > rows.
> >> >> >
> >> >> > tks
> >> >> >
> >> >> >
> >> >> > all queries produce no result:
> >> >> >
> >> >> > DBCC execution completed. If DBCC printed error messages, contact your
> >> >> > system administrator.
> >> >> >
> >> >> >
> >> >> >
> >> >> > --
> >> >> > -- cranfield, DBA
> >> >>
> >>
> >>
> >>
>|||Ouch... That was not obvious! Thanks for posting back. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Cranfield" <alan_cranfield@.msn.co.za> wrote in message
news:FF387413-A310-4994-8A65-B118987CBF42@.microsoft.com...
> Hi Tibor
> I have found the problem! These tables have 0 rows. DBCC SHOWCONTIG does
> not return results for empty tables.
> doh!
> --
> -- cranfield, DBA
>
> "Tibor Karaszi" wrote:
>> Can you run it in the pubs database? I ran your SELECT (which produces the DBCC commands) in pubs
>> against a SQL2K (8.00.194), and it produced results. Also, be careful to note that without
>> TABLERESULTS, you get text back (use the correct tab in QA results).
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Cranfield" <alan_cranfield@.msn.co.za> wrote in message
>> news:97BDC55E-5F6E-4025-B576-0441777497DB@.microsoft.com...
>> > no, omitting quotes makes no difference. MS uses quotes themselves in their
>> > BOL samples.
>> >
>> > Just to make sure I have the correct tables:
>> >
>> > select 'DBCC SHOWCONTIG ('+name+')'
>> > from sysobjects
>> > where type = 'u'
>> >
>> > DBCC SHOWCONTIG (DataRecoveryLog)
>> > DBCC SHOWCONTIG (ProviderSourceInfo)
>> > DBCC SHOWCONTIG (MsgAudit_CBOT)
>> > DBCC SHOWCONTIG (MsgAudit_KCBT)
>> > DBCC SHOWCONTIG (MsgAudit_MGEX)
>> > DBCC SHOWCONTIG (MsgAudit_WCE)
>> > DBCC SHOWCONTIG (MsgAudit_JADE)
>> >
>> > DBCC execution completed. If DBCC printed error messages, contact your
>> > system administrator.
>> > DBCC execution completed. If DBCC printed error messages, contact your
>> > system administrator.
>> > DBCC execution completed. If DBCC printed error messages, contact your
>> > system administrator.
>> > DBCC execution completed. If DBCC printed error messages, contact your
>> > system administrator.
>> > DBCC execution completed. If DBCC printed error messages, contact your
>> > system administrator.
>> > DBCC execution completed. If DBCC printed error messages, contact your
>> > system administrator.
>> > DBCC execution completed. If DBCC printed error messages, contact your
>> > system administrator.
>> >
>> >
>> >
>> > --
>> > -- cranfield, DBA
>> >
>> >
>> > "Gail Erickson [MS]" wrote:
>> >
>> >> What happens if you omit the single quotes? As in DBCC SHOWCONTIG
>> >> (MsgAudit_CBOT)? Looking at the syntax for DBCC statements in SQL Server
>> >> 2000 BOL, it does not indicate the use of single quotes around the object
>> >> name, but does in the SQL Server 2005 topics.
>> >>
>> >> --
>> >> Gail Erickson [MS]
>> >> SQL Server Documentation Team
>> >> This posting is provided "AS IS" with no warranties, and confers no rights
>> >> Download the latest version of Books Online from
>> >> http://technet.microsoft.com/en-us/sqlserver/bb428874.aspx
>> >>
>> >> "Cranfield" <alan_cranfield@.msn.co.za> wrote in message
>> >> news:2FAEA2D1-46ED-4248-BB68-8C0B62B90E52@.microsoft.com...
>> >> >I am certain query is executed. Have also tried osql - still no results,
>> >> >just
>> >> > as below. My SQL2005 table returns results but NOT SQL2000
>> >> >
>> >> > 1> use eqmclog
>> >> > 2> go
>> >> > 1> dbcc showcontig('providersourceinfo')
>> >> > 2> go
>> >> > DBCC execution completed. If DBCC printed error messages, contact your
>> >> > system administrator.
>> >> > 1>
>> >> >
>> >> > very strange this...
>> >> > --
>> >> > -- cranfield, DBA
>> >> >
>> >> >
>> >> > "Tibor Karaszi" wrote:
>> >> >
>> >> >> Are you certain you really execute the queries? Press the "execute"
>> >> >> button? Also, can you try using
>> >> >> OSQL.EXE? That DBCC should give you an error message if the table doesn't
>> >> >> exist.
>> >> >>
>> >> >> --
>> >> >> Tibor Karaszi, SQL Server MVP
>> >> >> http://www.karaszi.com/sqlserver/default.asp
>> >> >> http://sqlblog.com/blogs/tibor_karaszi
>> >> >>
>> >> >>
>> >> >> "Cranfield" <alan_cranfield@.msn.co.za> wrote in message
>> >> >> news:A7E3358D-4814-49C6-9E48-451F6CB20EA1@.microsoft.com...
>> >> >> > Hello
>> >> >> >
>> >> >> > Trying to get some fragmentation info back on SQL2000 tables but no
>> >> >> > results
>> >> >> > are returned. Is this a bug?
>> >> >> >
>> >> >> > DBCC SHOWCONTIG ('dbo.[MsgAudit_CBOT]') WITH FAST, TABLERESULTS,
>> >> >> > ALL_INDEXES, NO_INFOMSGS
>> >> >> >
>> >> >> > DBCC SHOWCONTIG ('MsgAudit_CBOT')
>> >> >> >
>> >> >> > DBCC SHOWCONTIG ('dbo.MsgAudit_CBOT')
>> >> >> >
>> >> >> > -- all above tables exist and have clustered index with millions of
>> >> >> > rows.
>> >> >> >
>> >> >> > tks
>> >> >> >
>> >> >> >
>> >> >> > all queries produce no result:
>> >> >> >
>> >> >> > DBCC execution completed. If DBCC printed error messages, contact your
>> >> >> > system administrator.
>> >> >> >
>> >> >> >
>> >> >> >
>> >> >> > --
>> >> >> > -- cranfield, DBA
>> >> >>
>> >>
>> >>
>> >>
DBCC SHOWCONTIG results: Good/bad?
My company is running Microsoft Navision Axapta as our ERP solution.
We've had some performance problems (high disk loads). After reading
"Microsoft SQL Server 2000 Index Defragmentation Best Practices" at
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/maintain/optimize/ss2kidbp.asp
...I decided to run DBCC SHOWCONTIG on the Axapta database.
The resulting report: http://home.c2i.net/hmhaga/sql/showcontig.htm
(500 kb text)
I'm new to administering SQL servers with large datbases and heavy
load. I need som expert opinions! Is this database heavily fragmented
or not? Should I schedule a daily index defragmentation job?
Any feedback appreciated!
H.M.Haga
'98 Subaru Impreza GT
'91 Suzuki Bandit 400
http://www.imprezadriver.com/phpBB2
http://home.c2i.net/hmhaga"Hans-Martin Haga" <hmhaga@.c2i.net> wrote in message
news:8orppvodf2rqgod40vmtgqbmc4leclcs94@.4ax.com...
> My company is running Microsoft Navision Axapta as our ERP solution.
> We've had some performance problems (high disk loads). After reading
> "Microsoft SQL Server 2000 Index Defragmentation Best Practices" at
>
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/maintain/optimize/ss2kidbp.asp
> ...I decided to run DBCC SHOWCONTIG on the Axapta database.
> The resulting report: http://home.c2i.net/hmhaga/sql/showcontig.htm
> (500 kb text)
> I'm new to administering SQL servers with large datbases and heavy
> load. I need som expert opinions! Is this database heavily fragmented
> or not? Should I schedule a daily index defragmentation job?
> Any feedback appreciated!
>
Look to the best:actual counts. If there was no fragmentation they would be
close to the same.
However, fragmentation is not necessarily a bad thing. In an OLTP database
(loads of updates, few reports) it is good. In an OLAP database (few
updates, bags of reports) it is bad.
You really need to know why your database is running slow by analysing
output from Profiler and System Manager. There's roughly a gazillion things
that can affect performance, fragmentation being only one of them.
Does it affect just SELECT statements? INSERT/DELETE/UPDATE statements?
Both?
If your Indexes are fragmented, try rebuilding the indexes with a lower
FILL_FACTOR setting. That will cause fewer page splits, though SELECT
statements may theoretically increase as SQL will have to read more pages
per index search.
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.530 / Virus Database: 325 - Release Date: 22/10/2003|||You really don't expect us to go through all that, do you? ;-)
I suggest you run SHOWCONTIG this way:
DBCC SHOWCONTIG WITH TABLERESULTS
That gives you a resultset. I even use below method (from VB code):
INSERT INTO #tbl (...)
DBCC SHOWCONTIG WITH TABLERESULTS
You have to create the temp tables first, with proper columns, but that gives you the ability to do
SELECT with WHERE, ORDER BY etc. Easy to do an ORDER BY LogicalFragmentation DESC, for example.
General tips:
If you don't have > 500 to 1000 pages, don't worry about fragmentation.
If you have > 1 database files, use Logial Fragmentation, scan density will not report correct.
Seems you have some tables without clustered index... Any particular reason?
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Hans-Martin Haga" <hmhaga@.c2i.net> wrote in message
news:8orppvodf2rqgod40vmtgqbmc4leclcs94@.4ax.com...
> My company is running Microsoft Navision Axapta as our ERP solution.
> We've had some performance problems (high disk loads). After reading
> "Microsoft SQL Server 2000 Index Defragmentation Best Practices" at
>
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/maintain/optimize/ss2kidbp.asp
> ...I decided to run DBCC SHOWCONTIG on the Axapta database.
> The resulting report: http://home.c2i.net/hmhaga/sql/showcontig.htm
> (500 kb text)
> I'm new to administering SQL servers with large datbases and heavy
> load. I need som expert opinions! Is this database heavily fragmented
> or not? Should I schedule a daily index defragmentation job?
> Any feedback appreciated!
>
> H.M.Haga
> '98 Subaru Impreza GT
> '91 Suzuki Bandit 400
> http://www.imprezadriver.com/phpBB2
> http://home.c2i.net/hmhaga|||> Look to the best:actual counts. If there was no fragmentation they would be
> close to the same.
Just watch out if > 1 database file for the filegroup. Jumps between files will count as
fragmentation.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver|||You should read the whitepaper at
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/maintain/optimize/ss2kidbp.asp
which explains everything you need to know about managing fragmentation.
--
Paul Randal
DBCC Technical Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:#0LxzdInDHA.2416@.TK2MSFTNGP10.phx.gbl...
> You really don't expect us to go through all that, do you? ;-)
> I suggest you run SHOWCONTIG this way:
> DBCC SHOWCONTIG WITH TABLERESULTS
> That gives you a resultset. I even use below method (from VB code):
> INSERT INTO #tbl (...)
> DBCC SHOWCONTIG WITH TABLERESULTS
> You have to create the temp tables first, with proper columns, but that
gives you the ability to do
> SELECT with WHERE, ORDER BY etc. Easy to do an ORDER BY
LogicalFragmentation DESC, for example.
> General tips:
> If you don't have > 500 to 1000 pages, don't worry about fragmentation.
> If you have > 1 database files, use Logial Fragmentation, scan density
will not report correct.
> Seems you have some tables without clustered index... Any particular
reason?
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Hans-Martin Haga" <hmhaga@.c2i.net> wrote in message
> news:8orppvodf2rqgod40vmtgqbmc4leclcs94@.4ax.com...
> > My company is running Microsoft Navision Axapta as our ERP solution.
> > We've had some performance problems (high disk loads). After reading
> > "Microsoft SQL Server 2000 Index Defragmentation Best Practices" at
> >
>
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/maintain/optimize/ss2kidbp.asp
> >
> > ...I decided to run DBCC SHOWCONTIG on the Axapta database.
> > The resulting report: http://home.c2i.net/hmhaga/sql/showcontig.htm
> > (500 kb text)
> >
> > I'm new to administering SQL servers with large datbases and heavy
> > load. I need som expert opinions! Is this database heavily fragmented
> > or not? Should I schedule a daily index defragmentation job?
> >
> > Any feedback appreciated!
> >
> >
> > H.M.Haga
> > '98 Subaru Impreza GT
> > '91 Suzuki Bandit 400
> > http://www.imprezadriver.com/phpBB2
> > http://home.c2i.net/hmhaga
>|||On Mon, 27 Oct 2003 13:39:52 +0100, "Tibor Karaszi"
<tibor.please_reply_to_public_forum.karaszi@.cornerstone.se> wrote:
>You really don't expect us to go through all that, do you? ;-)
No... ;-)
After I read your answers, I signed up for "2072 Administering a MS
SQL Server 2000 Database" :-)
Our server has been running for years AS IS, but after we decided to
put our ERP solution on it - it suddenly requires a lot more attention
- and knowledge!
I've just started to analyze the database, currently it is in the same
state as it was when our vendor installed the solution (Axapta).
H.M.Haga
'98 Subaru Impreza GT
'91 Suzuki Bandit 400
http://www.imprezadriver.com/phpBB2
http://home.c2i.net/hmhaga|||> After I read your answers, I signed up for "2072 Administering a MS
> SQL Server 2000 Database" :-)
Teaching those courses, I just want to say that the Admin course do not deal with indexes,
fragmentation and such. The programming course does (2073). Just a heads up. :-)
Again, check out the tables with some 500 pages or more, and concentrate on those. Also, consider
why some tables doesn't have clustered indexes. That would be a good start. And read the paper that
Paul referred to. My tips...
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Hans-Martin Haga" <hmhaga@.c2i.net> wrote in message
news:dlfspv8vi4esvgf2cv2ktgv3ot1ejp9pht@.4ax.com...
> On Mon, 27 Oct 2003 13:39:52 +0100, "Tibor Karaszi"
> <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se> wrote:
> >You really don't expect us to go through all that, do you? ;-)
> No... ;-)
> After I read your answers, I signed up for "2072 Administering a MS
> SQL Server 2000 Database" :-)
> Our server has been running for years AS IS, but after we decided to
> put our ERP solution on it - it suddenly requires a lot more attention
> - and knowledge!
> I've just started to analyze the database, currently it is in the same
> state as it was when our vendor installed the solution (Axapta).
>
> H.M.Haga
> '98 Subaru Impreza GT
> '91 Suzuki Bandit 400
> http://www.imprezadriver.com/phpBB2
> http://home.c2i.net/hmhaga|||On Tue, 28 Oct 2003 12:08:46 +0100, "Tibor Karaszi"
<tibor.please_reply_to_public_forum.karaszi@.cornerstone.se> wrote:
>Teaching those courses, I just want to say that the Admin course do not deal with indexes,
>fragmentation and such. The programming course does (2073). Just a heads up. :-)
Thanks! I've just signed 2073 as well, 2 weeks of SQL courses then :)
As for clustered index, neither the database nor the application has
been tuned or optimized in any way after the implementation - so I
guess that is up to me. Our dealer has very limited knowledge of SQL
(it's like a black box), they've put a layer of Axapta application
logic between themself and SQL! ;-)
H.M.Haga
'98 Subaru Impreza GT
'91 Suzuki Bandit 400
http://www.imprezadriver.com/phpBB2
http://home.c2i.net/hmhaga
We've had some performance problems (high disk loads). After reading
"Microsoft SQL Server 2000 Index Defragmentation Best Practices" at
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/maintain/optimize/ss2kidbp.asp
...I decided to run DBCC SHOWCONTIG on the Axapta database.
The resulting report: http://home.c2i.net/hmhaga/sql/showcontig.htm
(500 kb text)
I'm new to administering SQL servers with large datbases and heavy
load. I need som expert opinions! Is this database heavily fragmented
or not? Should I schedule a daily index defragmentation job?
Any feedback appreciated!
H.M.Haga
'98 Subaru Impreza GT
'91 Suzuki Bandit 400
http://www.imprezadriver.com/phpBB2
http://home.c2i.net/hmhaga"Hans-Martin Haga" <hmhaga@.c2i.net> wrote in message
news:8orppvodf2rqgod40vmtgqbmc4leclcs94@.4ax.com...
> My company is running Microsoft Navision Axapta as our ERP solution.
> We've had some performance problems (high disk loads). After reading
> "Microsoft SQL Server 2000 Index Defragmentation Best Practices" at
>
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/maintain/optimize/ss2kidbp.asp
> ...I decided to run DBCC SHOWCONTIG on the Axapta database.
> The resulting report: http://home.c2i.net/hmhaga/sql/showcontig.htm
> (500 kb text)
> I'm new to administering SQL servers with large datbases and heavy
> load. I need som expert opinions! Is this database heavily fragmented
> or not? Should I schedule a daily index defragmentation job?
> Any feedback appreciated!
>
Look to the best:actual counts. If there was no fragmentation they would be
close to the same.
However, fragmentation is not necessarily a bad thing. In an OLTP database
(loads of updates, few reports) it is good. In an OLAP database (few
updates, bags of reports) it is bad.
You really need to know why your database is running slow by analysing
output from Profiler and System Manager. There's roughly a gazillion things
that can affect performance, fragmentation being only one of them.
Does it affect just SELECT statements? INSERT/DELETE/UPDATE statements?
Both?
If your Indexes are fragmented, try rebuilding the indexes with a lower
FILL_FACTOR setting. That will cause fewer page splits, though SELECT
statements may theoretically increase as SQL will have to read more pages
per index search.
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.530 / Virus Database: 325 - Release Date: 22/10/2003|||You really don't expect us to go through all that, do you? ;-)
I suggest you run SHOWCONTIG this way:
DBCC SHOWCONTIG WITH TABLERESULTS
That gives you a resultset. I even use below method (from VB code):
INSERT INTO #tbl (...)
DBCC SHOWCONTIG WITH TABLERESULTS
You have to create the temp tables first, with proper columns, but that gives you the ability to do
SELECT with WHERE, ORDER BY etc. Easy to do an ORDER BY LogicalFragmentation DESC, for example.
General tips:
If you don't have > 500 to 1000 pages, don't worry about fragmentation.
If you have > 1 database files, use Logial Fragmentation, scan density will not report correct.
Seems you have some tables without clustered index... Any particular reason?
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Hans-Martin Haga" <hmhaga@.c2i.net> wrote in message
news:8orppvodf2rqgod40vmtgqbmc4leclcs94@.4ax.com...
> My company is running Microsoft Navision Axapta as our ERP solution.
> We've had some performance problems (high disk loads). After reading
> "Microsoft SQL Server 2000 Index Defragmentation Best Practices" at
>
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/maintain/optimize/ss2kidbp.asp
> ...I decided to run DBCC SHOWCONTIG on the Axapta database.
> The resulting report: http://home.c2i.net/hmhaga/sql/showcontig.htm
> (500 kb text)
> I'm new to administering SQL servers with large datbases and heavy
> load. I need som expert opinions! Is this database heavily fragmented
> or not? Should I schedule a daily index defragmentation job?
> Any feedback appreciated!
>
> H.M.Haga
> '98 Subaru Impreza GT
> '91 Suzuki Bandit 400
> http://www.imprezadriver.com/phpBB2
> http://home.c2i.net/hmhaga|||> Look to the best:actual counts. If there was no fragmentation they would be
> close to the same.
Just watch out if > 1 database file for the filegroup. Jumps between files will count as
fragmentation.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver|||You should read the whitepaper at
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/maintain/optimize/ss2kidbp.asp
which explains everything you need to know about managing fragmentation.
--
Paul Randal
DBCC Technical Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:#0LxzdInDHA.2416@.TK2MSFTNGP10.phx.gbl...
> You really don't expect us to go through all that, do you? ;-)
> I suggest you run SHOWCONTIG this way:
> DBCC SHOWCONTIG WITH TABLERESULTS
> That gives you a resultset. I even use below method (from VB code):
> INSERT INTO #tbl (...)
> DBCC SHOWCONTIG WITH TABLERESULTS
> You have to create the temp tables first, with proper columns, but that
gives you the ability to do
> SELECT with WHERE, ORDER BY etc. Easy to do an ORDER BY
LogicalFragmentation DESC, for example.
> General tips:
> If you don't have > 500 to 1000 pages, don't worry about fragmentation.
> If you have > 1 database files, use Logial Fragmentation, scan density
will not report correct.
> Seems you have some tables without clustered index... Any particular
reason?
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Hans-Martin Haga" <hmhaga@.c2i.net> wrote in message
> news:8orppvodf2rqgod40vmtgqbmc4leclcs94@.4ax.com...
> > My company is running Microsoft Navision Axapta as our ERP solution.
> > We've had some performance problems (high disk loads). After reading
> > "Microsoft SQL Server 2000 Index Defragmentation Best Practices" at
> >
>
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/maintain/optimize/ss2kidbp.asp
> >
> > ...I decided to run DBCC SHOWCONTIG on the Axapta database.
> > The resulting report: http://home.c2i.net/hmhaga/sql/showcontig.htm
> > (500 kb text)
> >
> > I'm new to administering SQL servers with large datbases and heavy
> > load. I need som expert opinions! Is this database heavily fragmented
> > or not? Should I schedule a daily index defragmentation job?
> >
> > Any feedback appreciated!
> >
> >
> > H.M.Haga
> > '98 Subaru Impreza GT
> > '91 Suzuki Bandit 400
> > http://www.imprezadriver.com/phpBB2
> > http://home.c2i.net/hmhaga
>|||On Mon, 27 Oct 2003 13:39:52 +0100, "Tibor Karaszi"
<tibor.please_reply_to_public_forum.karaszi@.cornerstone.se> wrote:
>You really don't expect us to go through all that, do you? ;-)
No... ;-)
After I read your answers, I signed up for "2072 Administering a MS
SQL Server 2000 Database" :-)
Our server has been running for years AS IS, but after we decided to
put our ERP solution on it - it suddenly requires a lot more attention
- and knowledge!
I've just started to analyze the database, currently it is in the same
state as it was when our vendor installed the solution (Axapta).
H.M.Haga
'98 Subaru Impreza GT
'91 Suzuki Bandit 400
http://www.imprezadriver.com/phpBB2
http://home.c2i.net/hmhaga|||> After I read your answers, I signed up for "2072 Administering a MS
> SQL Server 2000 Database" :-)
Teaching those courses, I just want to say that the Admin course do not deal with indexes,
fragmentation and such. The programming course does (2073). Just a heads up. :-)
Again, check out the tables with some 500 pages or more, and concentrate on those. Also, consider
why some tables doesn't have clustered indexes. That would be a good start. And read the paper that
Paul referred to. My tips...
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Hans-Martin Haga" <hmhaga@.c2i.net> wrote in message
news:dlfspv8vi4esvgf2cv2ktgv3ot1ejp9pht@.4ax.com...
> On Mon, 27 Oct 2003 13:39:52 +0100, "Tibor Karaszi"
> <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se> wrote:
> >You really don't expect us to go through all that, do you? ;-)
> No... ;-)
> After I read your answers, I signed up for "2072 Administering a MS
> SQL Server 2000 Database" :-)
> Our server has been running for years AS IS, but after we decided to
> put our ERP solution on it - it suddenly requires a lot more attention
> - and knowledge!
> I've just started to analyze the database, currently it is in the same
> state as it was when our vendor installed the solution (Axapta).
>
> H.M.Haga
> '98 Subaru Impreza GT
> '91 Suzuki Bandit 400
> http://www.imprezadriver.com/phpBB2
> http://home.c2i.net/hmhaga|||On Tue, 28 Oct 2003 12:08:46 +0100, "Tibor Karaszi"
<tibor.please_reply_to_public_forum.karaszi@.cornerstone.se> wrote:
>Teaching those courses, I just want to say that the Admin course do not deal with indexes,
>fragmentation and such. The programming course does (2073). Just a heads up. :-)
Thanks! I've just signed 2073 as well, 2 weeks of SQL courses then :)
As for clustered index, neither the database nor the application has
been tuned or optimized in any way after the implementation - so I
guess that is up to me. Our dealer has very limited knowledge of SQL
(it's like a black box), they've put a layer of Axapta application
logic between themself and SQL! ;-)
H.M.Haga
'98 Subaru Impreza GT
'91 Suzuki Bandit 400
http://www.imprezadriver.com/phpBB2
http://home.c2i.net/hmhaga
DBCC SHOWCONTIG Questions
On most of the results from a series of DBCC SHOWCONTIG
commands, we see an entry for "Logical Scan
Fragmentation", but on others we don't see that entry.
From comparing the tables I can see that a table with only
a clustered index or table with some indexes but none of
which are specified as primary key or clustered are the
ones without the logical scan frag information. Can
someone explain to me why tables with these characterists
don't also generate logical scan fragmentation
information? BOL did not help me figure that one out.
Thanks for any info.
that value really only makes sense for tables with a clustered index.
Tables without a clustered index are known as "Heaps". For Heaps, I focus on
the "Scan Density" Value and make sure that it stays above 80%
Cheers,
Greg Jackson
PDX, Oregon
sql
commands, we see an entry for "Logical Scan
Fragmentation", but on others we don't see that entry.
From comparing the tables I can see that a table with only
a clustered index or table with some indexes but none of
which are specified as primary key or clustered are the
ones without the logical scan frag information. Can
someone explain to me why tables with these characterists
don't also generate logical scan fragmentation
information? BOL did not help me figure that one out.
Thanks for any info.
that value really only makes sense for tables with a clustered index.
Tables without a clustered index are known as "Heaps". For Heaps, I focus on
the "Scan Density" Value and make sure that it stays above 80%
Cheers,
Greg Jackson
PDX, Oregon
sql
DBCC SHOWCONTIG Questions
On most of the results from a series of DBCC SHOWCONTIG
commands, we see an entry for "Logical Scan
Fragmentation", but on others we don't see that entry.
From comparing the tables I can see that a table with only
a clustered index or table with some indexes but none of
which are specified as primary key or clustered are the
ones without the logical scan frag information. Can
someone explain to me why tables with these characterists
don't also generate logical scan fragmentation
information? BOL did not help me figure that one out.
Thanks for any info.that value really only makes sense for tables with a clustered index.
Tables without a clustered index are known as "Heaps". For Heaps, I focus on
the "Scan Density" Value and make sure that it stays above 80%
Cheers,
Greg Jackson
PDX, Oregon
commands, we see an entry for "Logical Scan
Fragmentation", but on others we don't see that entry.
From comparing the tables I can see that a table with only
a clustered index or table with some indexes but none of
which are specified as primary key or clustered are the
ones without the logical scan frag information. Can
someone explain to me why tables with these characterists
don't also generate logical scan fragmentation
information? BOL did not help me figure that one out.
Thanks for any info.that value really only makes sense for tables with a clustered index.
Tables without a clustered index are known as "Heaps". For Heaps, I focus on
the "Scan Density" Value and make sure that it stays above 80%
Cheers,
Greg Jackson
PDX, Oregon
DBCC SHOWCONTIG Questions
On most of the results from a series of DBCC SHOWCONTIG
commands, we see an entry for "Logical Scan
Fragmentation", but on others we don't see that entry.
From comparing the tables I can see that a table with only
a clustered index or table with some indexes but none of
which are specified as primary key or clustered are the
ones without the logical scan frag information. Can
someone explain to me why tables with these characterists
don't also generate logical scan fragmentation
information? BOL did not help me figure that one out.
Thanks for any info.that value really only makes sense for tables with a clustered index.
Tables without a clustered index are known as "Heaps". For Heaps, I focus on
the "Scan Density" Value and make sure that it stays above 80%
Cheers,
Greg Jackson
PDX, Oregon
commands, we see an entry for "Logical Scan
Fragmentation", but on others we don't see that entry.
From comparing the tables I can see that a table with only
a clustered index or table with some indexes but none of
which are specified as primary key or clustered are the
ones without the logical scan frag information. Can
someone explain to me why tables with these characterists
don't also generate logical scan fragmentation
information? BOL did not help me figure that one out.
Thanks for any info.that value really only makes sense for tables with a clustered index.
Tables without a clustered index are known as "Heaps". For Heaps, I focus on
the "Scan Density" Value and make sure that it stays above 80%
Cheers,
Greg Jackson
PDX, Oregon
DBCC SHOWCONTIG question
Hi Freinds,
SQL 2000
I have performance issues on my database and got to this point that DBCC
SHOWCONTIG result for my tables are:
DBCC SHOWCONTIG scanning 'dup_source_title' table...
Table: 'dup_source_title' (779149821); index ID: 1, database ID: 7
TABLE level scan performed.
- Pages Scanned........................: 19296
- Extents Scanned.......................: 2429
- Extent Switches.......................: 2428
- Avg. Pages per Extent..................: 7.9
- Scan Density [Best Count:Actual Count]......: 99.30% [2412:2429]
- Logical Scan Fragmentation ..............: 0.06%
- Extent Scan Fragmentation ...............: 1.03%
- Avg. Bytes Free per Page................: 7265.6
- Avg. Page Density (full)................: 10.24%
DBCC SHOWCONTIG scanning 'jm_wo_transaction' table...
Table: 'jm_wo_transaction' (667305587); index ID: 1, database ID: 7
TABLE level scan performed.
- Pages Scanned........................: 180459
- Extents Scanned.......................: 22605
- Extent Switches.......................: 23105
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 97.63% [22558:23106]
- Logical Scan Fragmentation ..............: 3.78%
- Extent Scan Fragmentation ...............: 12.67%
- Avg. Bytes Free per Page................: 656.4
- Avg. Page Density (full)................: 91.89%
is this good result? bad result? which statistic is not good and need
tuning? and how , what should I do to tune it?
Thanks in advance,
PatThey are both fine in regards to fragmentation but the first one is only 10%
full where as the second is 90% full. There is no right or wrong number for
fullness since it depends a lot on how the index is used. But 10% is
probably way too low of a fill factor. Have a look at these:
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
Index Defrag Best Practices 2000
http://www.sql-server-performance.c..._showcontig.asp
Understanding DBCC SHOWCONTIG
http://www.sqlservercentral.com/col...
illfactors.asp
Fill Factors
http://www.sql-server-performance.c...red_indexes.asp
Clustered Indexes
Andrew J. Kelly SQL MVP
"Patrick" <patriarck@.gmail.com> wrote in message
news:OroJcabLGHA.1028@.TK2MSFTNGP11.phx.gbl...
> Hi Freinds,
> SQL 2000
> I have performance issues on my database and got to this point that DBCC
> SHOWCONTIG result for my tables are:
> DBCC SHOWCONTIG scanning 'dup_source_title' table...
> Table: 'dup_source_title' (779149821); index ID: 1, database ID: 7
> TABLE level scan performed.
> - Pages Scanned........................: 19296
> - Extents Scanned.......................: 2429
> - Extent Switches.......................: 2428
> - Avg. Pages per Extent..................: 7.9
> - Scan Density [Best Count:Actual Count]......: 99.30% [2412:2429]
> - Logical Scan Fragmentation ..............: 0.06%
> - Extent Scan Fragmentation ...............: 1.03%
> - Avg. Bytes Free per Page................: 7265.6
> - Avg. Page Density (full)................: 10.24%
> DBCC SHOWCONTIG scanning 'jm_wo_transaction' table...
> Table: 'jm_wo_transaction' (667305587); index ID: 1, database ID: 7
> TABLE level scan performed.
> - Pages Scanned........................: 180459
> - Extents Scanned.......................: 22605
> - Extent Switches.......................: 23105
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 97.63% [22558:23106]
> - Logical Scan Fragmentation ..............: 3.78%
> - Extent Scan Fragmentation ...............: 12.67%
> - Avg. Bytes Free per Page................: 656.4
> - Avg. Page Density (full)................: 91.89%
> is this good result? bad result? which statistic is not good and need
> tuning? and how , what should I do to tune it?
> Thanks in advance,
> Pat
>|||Thank you very much for the answer,
I am struggling with the performance now and need to do smt to get the db
running.
What does Scan Density [Best Count:Actual Count] show? Should it be low ?
High?
Where are the main places that Ihave to look to tune the database?
Thanks again ina advance ,
Pat
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OGkHq8bLGHA.344@.TK2MSFTNGP11.phx.gbl...
> They are both fine in regards to fragmentation but the first one is only
> 10% full where as the second is 90% full. There is no right or wrong
> number for fullness since it depends a lot on how the index is used. But
> 10% is probably way too low of a fill factor. Have a look at these:
>
> http://www.microsoft.com/technet/pr..._showcontig.asp Understanding
> DBCC SHOWCONTIG
> http://www.sqlservercentral.com/col...fillfactors.asp
> Fill Factors
> http://www.sql-server-performance.c...red_indexes.asp Clustered
> Indexes
> --
> Andrew J. Kelly SQL MVP
>
> "Patrick" <patriarck@.gmail.com> wrote in message
> news:OroJcabLGHA.1028@.TK2MSFTNGP11.phx.gbl...
>|||The 2nd link I gave you is all about DBCC SHOWCONTIG and should answer most
if not all of your questions on that topic. The other links are extremely
beneficial as well. But fragmentation is probably not the first place you
should look if you are having performance issues. Have a look at these to
help you narrow down the culprits:
http://www.sql-server-performance.c...nce_audit10.asp
Performance Audit
http://www.microsoft.com/technet/pr...perfmonitor.asp Perfmon counters
http://www.sql-server-performance.c...mance_audit.asp
Hardware Performance Checklist
http://www.sql-server-performance.c...rmance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/d.../>
on_24u1.asp
Disk Monitoring
http://sqldev.net/misc/WaitTypes.htm Wait Types
Andrew J. Kelly SQL MVP
"Patrick" <patriarck@.gmail.com> wrote in message
news:ej0DZfcLGHA.3936@.TK2MSFTNGP10.phx.gbl...
> Thank you very much for the answer,
> I am struggling with the performance now and need to do smt to get the db
> running.
> What does Scan Density [Best Count:Actual Count] show? Should it be low ?
> High?
> Where are the main places that Ihave to look to tune the database?
> Thanks again ina advance ,
> Pat
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OGkHq8bLGHA.344@.TK2MSFTNGP11.phx.gbl...
>
SQL 2000
I have performance issues on my database and got to this point that DBCC
SHOWCONTIG result for my tables are:
DBCC SHOWCONTIG scanning 'dup_source_title' table...
Table: 'dup_source_title' (779149821); index ID: 1, database ID: 7
TABLE level scan performed.
- Pages Scanned........................: 19296
- Extents Scanned.......................: 2429
- Extent Switches.......................: 2428
- Avg. Pages per Extent..................: 7.9
- Scan Density [Best Count:Actual Count]......: 99.30% [2412:2429]
- Logical Scan Fragmentation ..............: 0.06%
- Extent Scan Fragmentation ...............: 1.03%
- Avg. Bytes Free per Page................: 7265.6
- Avg. Page Density (full)................: 10.24%
DBCC SHOWCONTIG scanning 'jm_wo_transaction' table...
Table: 'jm_wo_transaction' (667305587); index ID: 1, database ID: 7
TABLE level scan performed.
- Pages Scanned........................: 180459
- Extents Scanned.......................: 22605
- Extent Switches.......................: 23105
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 97.63% [22558:23106]
- Logical Scan Fragmentation ..............: 3.78%
- Extent Scan Fragmentation ...............: 12.67%
- Avg. Bytes Free per Page................: 656.4
- Avg. Page Density (full)................: 91.89%
is this good result? bad result? which statistic is not good and need
tuning? and how , what should I do to tune it?
Thanks in advance,
PatThey are both fine in regards to fragmentation but the first one is only 10%
full where as the second is 90% full. There is no right or wrong number for
fullness since it depends a lot on how the index is used. But 10% is
probably way too low of a fill factor. Have a look at these:
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
Index Defrag Best Practices 2000
http://www.sql-server-performance.c..._showcontig.asp
Understanding DBCC SHOWCONTIG
http://www.sqlservercentral.com/col...
illfactors.asp
Fill Factors
http://www.sql-server-performance.c...red_indexes.asp
Clustered Indexes
Andrew J. Kelly SQL MVP
"Patrick" <patriarck@.gmail.com> wrote in message
news:OroJcabLGHA.1028@.TK2MSFTNGP11.phx.gbl...
> Hi Freinds,
> SQL 2000
> I have performance issues on my database and got to this point that DBCC
> SHOWCONTIG result for my tables are:
> DBCC SHOWCONTIG scanning 'dup_source_title' table...
> Table: 'dup_source_title' (779149821); index ID: 1, database ID: 7
> TABLE level scan performed.
> - Pages Scanned........................: 19296
> - Extents Scanned.......................: 2429
> - Extent Switches.......................: 2428
> - Avg. Pages per Extent..................: 7.9
> - Scan Density [Best Count:Actual Count]......: 99.30% [2412:2429]
> - Logical Scan Fragmentation ..............: 0.06%
> - Extent Scan Fragmentation ...............: 1.03%
> - Avg. Bytes Free per Page................: 7265.6
> - Avg. Page Density (full)................: 10.24%
> DBCC SHOWCONTIG scanning 'jm_wo_transaction' table...
> Table: 'jm_wo_transaction' (667305587); index ID: 1, database ID: 7
> TABLE level scan performed.
> - Pages Scanned........................: 180459
> - Extents Scanned.......................: 22605
> - Extent Switches.......................: 23105
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 97.63% [22558:23106]
> - Logical Scan Fragmentation ..............: 3.78%
> - Extent Scan Fragmentation ...............: 12.67%
> - Avg. Bytes Free per Page................: 656.4
> - Avg. Page Density (full)................: 91.89%
> is this good result? bad result? which statistic is not good and need
> tuning? and how , what should I do to tune it?
> Thanks in advance,
> Pat
>|||Thank you very much for the answer,
I am struggling with the performance now and need to do smt to get the db
running.
What does Scan Density [Best Count:Actual Count] show? Should it be low ?
High?
Where are the main places that Ihave to look to tune the database?
Thanks again ina advance ,
Pat
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OGkHq8bLGHA.344@.TK2MSFTNGP11.phx.gbl...
> They are both fine in regards to fragmentation but the first one is only
> 10% full where as the second is 90% full. There is no right or wrong
> number for fullness since it depends a lot on how the index is used. But
> 10% is probably way too low of a fill factor. Have a look at these:
>
> http://www.microsoft.com/technet/pr..._showcontig.asp Understanding
> DBCC SHOWCONTIG
> http://www.sqlservercentral.com/col...fillfactors.asp
> Fill Factors
> http://www.sql-server-performance.c...red_indexes.asp Clustered
> Indexes
> --
> Andrew J. Kelly SQL MVP
>
> "Patrick" <patriarck@.gmail.com> wrote in message
> news:OroJcabLGHA.1028@.TK2MSFTNGP11.phx.gbl...
>|||The 2nd link I gave you is all about DBCC SHOWCONTIG and should answer most
if not all of your questions on that topic. The other links are extremely
beneficial as well. But fragmentation is probably not the first place you
should look if you are having performance issues. Have a look at these to
help you narrow down the culprits:
http://www.sql-server-performance.c...nce_audit10.asp
Performance Audit
http://www.microsoft.com/technet/pr...perfmonitor.asp Perfmon counters
http://www.sql-server-performance.c...mance_audit.asp
Hardware Performance Checklist
http://www.sql-server-performance.c...rmance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/d.../>
on_24u1.asp
Disk Monitoring
http://sqldev.net/misc/WaitTypes.htm Wait Types
Andrew J. Kelly SQL MVP
"Patrick" <patriarck@.gmail.com> wrote in message
news:ej0DZfcLGHA.3936@.TK2MSFTNGP10.phx.gbl...
> Thank you very much for the answer,
> I am struggling with the performance now and need to do smt to get the db
> running.
> What does Scan Density [Best Count:Actual Count] show? Should it be low ?
> High?
> Where are the main places that Ihave to look to tune the database?
> Thanks again ina advance ,
> Pat
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OGkHq8bLGHA.344@.TK2MSFTNGP11.phx.gbl...
>
DBCC SHOWCONTIG Performances
I have decided to automate the Index Defragmentation process on all tha
tables of my database
with a store proc.
As a starting point, I used an example from SQL Server 2000 Help (Transact
SQL Reference -
DBCC ShowContig topic : Example E : Use DBCC SHOWCONTIG and DBCC INDEXDEFRAG
to defragment
indexes in a database).
I want to only consider indexes with a fragmentation over 20%.
My problem is :
When I run DBCC SHOWCONTIG on a large table (43,000,000 rows, 12 indexes),
it takes an average
of 65 minutes before I get a result.
Code : DBCC SHOWCONTIG (TFt01_Vente_hebdo)
WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS
I must consider over 60 tables.
My SQL Server runs on a Windows 2000 Server (SP4), with a dual 2.70 Ghz
Intel Xeon Mp CPU and
7680 MB of RAM.
Would you have any advise helping me increasing thoses performances ?
The perf of SHOWCONTIG (which I wrote) is proportional to the IO
capabilities of your system, and in FAST mode, is also proportional to the
fanout of your table. In FAST mode, it reads all the b-tree pages in the
parent level (one level above the leaf level of the index) - so the larger
the fanout, the smaller the number of pages it has to read.
For the example table you give, you need to do the following for each index:
1) work out index row size, R
2) divide 8000 / R = LF (leaf fanout)
3) divide 43000000 / LF = LN (number of pages required at leaf level)
4) work out index key size, K (this can be different from the row size,
depending on SQL 2005 features used and whether the index is unique or not -
see 2005 BOL for full details)
5) divide 8000 / K = TF (tree fanout)
6) divide LN / TF = P (number of pages SHOWCONTIG has to read for this
index)
Does the table have a clustered index? If not, SHOWCONTIG will have to read
all (i.e. 8000 / data row size) pages.
What's the max IO throughput of your IO subsystem? Are you running
SHOWCONTIG while there's heavy load?
Why do you need to do this for all tables and indexes? Are you able to
correlate increasing logical scan fragmentation with decreasing query
performance, for all these tables and indexes?
Have a read of this whitepaper for more info too - let me know if you have
any further questions.
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
Regards
Paul Randal
Lead Program Manager, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Marc G." <Marc G.@.discussions.microsoft.com> wrote in message
news:5F40205F-2835-4AA1-9B9E-D43D32E6CABE@.microsoft.com...
>I have decided to automate the Index Defragmentation process on all tha
> tables of my database
> with a store proc.
> As a starting point, I used an example from SQL Server 2000 Help (Transact
> SQL Reference -
> DBCC ShowContig topic : Example E : Use DBCC SHOWCONTIG and DBCC
> INDEXDEFRAG
> to defragment
> indexes in a database).
> I want to only consider indexes with a fragmentation over 20%.
> My problem is :
> When I run DBCC SHOWCONTIG on a large table (43,000,000 rows, 12 indexes),
> it takes an average
> of 65 minutes before I get a result.
> Code : DBCC SHOWCONTIG (TFt01_Vente_hebdo)
> WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS
> I must consider over 60 tables.
> My SQL Server runs on a Windows 2000 Server (SP4), with a dual 2.70 Ghz
> Intel Xeon Mp CPU and
> 7680 MB of RAM.
> Would you have any advise helping me increasing thoses performances ?
tables of my database
with a store proc.
As a starting point, I used an example from SQL Server 2000 Help (Transact
SQL Reference -
DBCC ShowContig topic : Example E : Use DBCC SHOWCONTIG and DBCC INDEXDEFRAG
to defragment
indexes in a database).
I want to only consider indexes with a fragmentation over 20%.
My problem is :
When I run DBCC SHOWCONTIG on a large table (43,000,000 rows, 12 indexes),
it takes an average
of 65 minutes before I get a result.
Code : DBCC SHOWCONTIG (TFt01_Vente_hebdo)
WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS
I must consider over 60 tables.
My SQL Server runs on a Windows 2000 Server (SP4), with a dual 2.70 Ghz
Intel Xeon Mp CPU and
7680 MB of RAM.
Would you have any advise helping me increasing thoses performances ?
The perf of SHOWCONTIG (which I wrote) is proportional to the IO
capabilities of your system, and in FAST mode, is also proportional to the
fanout of your table. In FAST mode, it reads all the b-tree pages in the
parent level (one level above the leaf level of the index) - so the larger
the fanout, the smaller the number of pages it has to read.
For the example table you give, you need to do the following for each index:
1) work out index row size, R
2) divide 8000 / R = LF (leaf fanout)
3) divide 43000000 / LF = LN (number of pages required at leaf level)
4) work out index key size, K (this can be different from the row size,
depending on SQL 2005 features used and whether the index is unique or not -
see 2005 BOL for full details)
5) divide 8000 / K = TF (tree fanout)
6) divide LN / TF = P (number of pages SHOWCONTIG has to read for this
index)
Does the table have a clustered index? If not, SHOWCONTIG will have to read
all (i.e. 8000 / data row size) pages.
What's the max IO throughput of your IO subsystem? Are you running
SHOWCONTIG while there's heavy load?
Why do you need to do this for all tables and indexes? Are you able to
correlate increasing logical scan fragmentation with decreasing query
performance, for all these tables and indexes?
Have a read of this whitepaper for more info too - let me know if you have
any further questions.
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
Regards
Paul Randal
Lead Program Manager, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Marc G." <Marc G.@.discussions.microsoft.com> wrote in message
news:5F40205F-2835-4AA1-9B9E-D43D32E6CABE@.microsoft.com...
>I have decided to automate the Index Defragmentation process on all tha
> tables of my database
> with a store proc.
> As a starting point, I used an example from SQL Server 2000 Help (Transact
> SQL Reference -
> DBCC ShowContig topic : Example E : Use DBCC SHOWCONTIG and DBCC
> INDEXDEFRAG
> to defragment
> indexes in a database).
> I want to only consider indexes with a fragmentation over 20%.
> My problem is :
> When I run DBCC SHOWCONTIG on a large table (43,000,000 rows, 12 indexes),
> it takes an average
> of 65 minutes before I get a result.
> Code : DBCC SHOWCONTIG (TFt01_Vente_hebdo)
> WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS
> I must consider over 60 tables.
> My SQL Server runs on a Windows 2000 Server (SP4), with a dual 2.70 Ghz
> Intel Xeon Mp CPU and
> 7680 MB of RAM.
> Would you have any advise helping me increasing thoses performances ?
DBCC SHOWCONTIG IndexID (0,255)
What do the output from DBCC SHOWCONTIG, IndexID values of
0 and 255 correspond to?
Thanks
Mike
Hi ,
0 = Data pages
1 = Clustered index
>1 and <=249 = Nonclustered
255 = Entry for tables that have text or image data
Thanks
Hari
MCDBA
"Mike" <anonymous@.discussions.microsoft.com> wrote in message
news:2947101c465c4$ea118a70$a601280a@.phx.gbl...
> What do the output from DBCC SHOWCONTIG, IndexID values of
> 0 and 255 correspond to?
> Thanks
> Mike
|||Indid = 0 means a heap. indid = 1 is a clustered index. nonclustered index
will have indid between 2 and 250. 251-254 is reserved. and 255 is for text
and image only.
richard
"Mike" <anonymous@.discussions.microsoft.com> wrote in message
news:2947101c465c4$ea118a70$a601280a@.phx.gbl...
> What do the output from DBCC SHOWCONTIG, IndexID values of
> 0 and 255 correspond to?
> Thanks
> Mike
|||Richard's explanation below is the correct one of the two posted.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Richard Ding" <rding@.acadian-asset.com> wrote in message
news:##LAbjdZEHA.384@.TK2MSFTNGP10.phx.gbl...
> Indid = 0 means a heap. indid = 1 is a clustered index. nonclustered index
> will have indid between 2 and 250. 251-254 is reserved. and 255 is for
text
> and image only.
>
> richard
> "Mike" <anonymous@.discussions.microsoft.com> wrote in message
> news:2947101c465c4$ea118a70$a601280a@.phx.gbl...
>
sql
0 and 255 correspond to?
Thanks
Mike
Hi ,
0 = Data pages
1 = Clustered index
>1 and <=249 = Nonclustered
255 = Entry for tables that have text or image data
Thanks
Hari
MCDBA
"Mike" <anonymous@.discussions.microsoft.com> wrote in message
news:2947101c465c4$ea118a70$a601280a@.phx.gbl...
> What do the output from DBCC SHOWCONTIG, IndexID values of
> 0 and 255 correspond to?
> Thanks
> Mike
|||Indid = 0 means a heap. indid = 1 is a clustered index. nonclustered index
will have indid between 2 and 250. 251-254 is reserved. and 255 is for text
and image only.
richard
"Mike" <anonymous@.discussions.microsoft.com> wrote in message
news:2947101c465c4$ea118a70$a601280a@.phx.gbl...
> What do the output from DBCC SHOWCONTIG, IndexID values of
> 0 and 255 correspond to?
> Thanks
> Mike
|||Richard's explanation below is the correct one of the two posted.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Richard Ding" <rding@.acadian-asset.com> wrote in message
news:##LAbjdZEHA.384@.TK2MSFTNGP10.phx.gbl...
> Indid = 0 means a heap. indid = 1 is a clustered index. nonclustered index
> will have indid between 2 and 250. 251-254 is reserved. and 255 is for
text
> and image only.
>
> richard
> "Mike" <anonymous@.discussions.microsoft.com> wrote in message
> news:2947101c465c4$ea118a70$a601280a@.phx.gbl...
>
sql
DBCC SHOWCONTIG IndexID (0,255)
What do the output from DBCC SHOWCONTIG, IndexID values of
0 and 255 correspond to?
Thanks
MikeHi ,
0 = Data pages
1 = Clustered index
>1 and <=249 = Nonclustered
255 = Entry for tables that have text or image data
Thanks
Hari
MCDBA
"Mike" <anonymous@.discussions.microsoft.com> wrote in message
news:2947101c465c4$ea118a70$a601280a@.phx.gbl...
> What do the output from DBCC SHOWCONTIG, IndexID values of
> 0 and 255 correspond to?
> Thanks
> Mike|||Indid = 0 means a heap. indid = 1 is a clustered index. nonclustered index
will have indid between 2 and 250. 251-254 is reserved. and 255 is for text
and image only.
richard
"Mike" <anonymous@.discussions.microsoft.com> wrote in message
news:2947101c465c4$ea118a70$a601280a@.phx.gbl...
> What do the output from DBCC SHOWCONTIG, IndexID values of
> 0 and 255 correspond to?
> Thanks
> Mike|||Richard's explanation below is the correct one of the two posted.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Richard Ding" <rding@.acadian-asset.com> wrote in message
news:##LAbjdZEHA.384@.TK2MSFTNGP10.phx.gbl...
> Indid = 0 means a heap. indid = 1 is a clustered index. nonclustered index
> will have indid between 2 and 250. 251-254 is reserved. and 255 is for
text
> and image only.
>
> richard
> "Mike" <anonymous@.discussions.microsoft.com> wrote in message
> news:2947101c465c4$ea118a70$a601280a@.phx.gbl...
> >
> > What do the output from DBCC SHOWCONTIG, IndexID values of
> > 0 and 255 correspond to?
> >
> > Thanks
> >
> > Mike
>
0 and 255 correspond to?
Thanks
MikeHi ,
0 = Data pages
1 = Clustered index
>1 and <=249 = Nonclustered
255 = Entry for tables that have text or image data
Thanks
Hari
MCDBA
"Mike" <anonymous@.discussions.microsoft.com> wrote in message
news:2947101c465c4$ea118a70$a601280a@.phx.gbl...
> What do the output from DBCC SHOWCONTIG, IndexID values of
> 0 and 255 correspond to?
> Thanks
> Mike|||Indid = 0 means a heap. indid = 1 is a clustered index. nonclustered index
will have indid between 2 and 250. 251-254 is reserved. and 255 is for text
and image only.
richard
"Mike" <anonymous@.discussions.microsoft.com> wrote in message
news:2947101c465c4$ea118a70$a601280a@.phx.gbl...
> What do the output from DBCC SHOWCONTIG, IndexID values of
> 0 and 255 correspond to?
> Thanks
> Mike|||Richard's explanation below is the correct one of the two posted.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Richard Ding" <rding@.acadian-asset.com> wrote in message
news:##LAbjdZEHA.384@.TK2MSFTNGP10.phx.gbl...
> Indid = 0 means a heap. indid = 1 is a clustered index. nonclustered index
> will have indid between 2 and 250. 251-254 is reserved. and 255 is for
text
> and image only.
>
> richard
> "Mike" <anonymous@.discussions.microsoft.com> wrote in message
> news:2947101c465c4$ea118a70$a601280a@.phx.gbl...
> >
> > What do the output from DBCC SHOWCONTIG, IndexID values of
> > 0 and 255 correspond to?
> >
> > Thanks
> >
> > Mike
>
DBCC SHOWCONTIG incorrect?
Hi. I have a table that has 58 million rows and is about 24 GB. I ran a DBCC
DBREINDEX which appeared to run fine - new fillfactor showing etc. However,
showcontig still shows the indexes as being badly fragmented. I've run an
update stats with fullscan and update usage which has corrected incorrect
space stats but that's all. I run this process every month and normally
everything works as expected. Any ideas why this time it didn't please?Perhaps DBCC UPDATEUSAGE will fix the incorrect stats.
Keith Kratochvil
"Shirley" <Shirley@.discussions.microsoft.com> wrote in message
news:B05EB837-51D3-40E1-B517-E758862A412A@.microsoft.com...
> Hi. I have a table that has 58 million rows and is about 24 GB. I ran a
> DBCC
> DBREINDEX which appeared to run fine - new fillfactor showing etc.
> However,
> showcontig still shows the indexes as being badly fragmented. I've run an
> update stats with fullscan and update usage which has corrected incorrect
> space stats but that's all. I run this process every month and normally
> everything works as expected. Any ideas why this time it didn't please?|||Thanks. I've already done that and it didn't.
"Keith Kratochvil" wrote:
> Perhaps DBCC UPDATEUSAGE will fix the incorrect stats.
> --
> Keith Kratochvil
>
> "Shirley" <Shirley@.discussions.microsoft.com> wrote in message
> news:B05EB837-51D3-40E1-B517-E758862A412A@.microsoft.com...
>
>|||Hi Shirley
Can you show us the details from DBCC SHOWCONTIG?
Also, how many indexes are there? Are they all fragmented, clustered and
nonclustered?
How much free space is in the database?
HTH
Kalen Delaney, SQL Server MVP
"Shirley" <Shirley@.discussions.microsoft.com> wrote in message
news:B05EB837-51D3-40E1-B517-E758862A412A@.microsoft.com...
> Hi. I have a table that has 58 million rows and is about 24 GB. I ran a
> DBCC
> DBREINDEX which appeared to run fine - new fillfactor showing etc.
> However,
> showcontig still shows the indexes as being badly fragmented. I've run an
> update stats with fullscan and update usage which has corrected incorrect
> space stats but that's all. I run this process every month and normally
> everything works as expected. Any ideas why this time it didn't please?|||Hi Kalen, thanks for replying.
There is one clustered IX and 3 NC IX. These are the results
- Scan Density [Best Count:Actual Count]......: 98.18% [292775:2981
97] CL
- Scan Density [Best Count:Actual Count]......: 54.10% [22661:41886
] NC
- Scan Density [Best Count:Actual Count]......: 52.88% [30754:58162
] NC
- Scan Density [Best Count:Actual Count]......: 54.74% [27210:49709
] NC
There is about 30 GB free space in the DB and it can autogrow.
"Kalen Delaney" wrote:
> Hi Shirley
> Can you show us the details from DBCC SHOWCONTIG?
> Also, how many indexes are there? Are they all fragmented, clustered and
> nonclustered?
> How much free space is in the database?
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> "Shirley" <Shirley@.discussions.microsoft.com> wrote in message
> news:B05EB837-51D3-40E1-B517-E758862A412A@.microsoft.com...
>
>|||Problem fixed. I ran an UPDATE STATISTICS again but with the FULLSCAN option
,
then re-ran the reorg and all looks fine now. Don't know why this just
started to happen but will do the updatestats before the reorg every time no
w.
"Shirley" wrote:
[vbcol=seagreen]
> Hi Kalen, thanks for replying.
> There is one clustered IX and 3 NC IX. These are the results
> - Scan Density [Best Count:Actual Count]......: 98.18% [292775:29
8197] CL
> - Scan Density [Best Count:Actual Count]......: 54.10% [22661:418
86] NC
> - Scan Density [Best Count:Actual Count]......: 52.88% [30754:581
62] NC
> - Scan Density [Best Count:Actual Count]......: 54.74% [27210:497
09] NC
> There is about 30 GB free space in the DB and it can autogrow.
>
> "Kalen Delaney" wrote:
>
DBREINDEX which appeared to run fine - new fillfactor showing etc. However,
showcontig still shows the indexes as being badly fragmented. I've run an
update stats with fullscan and update usage which has corrected incorrect
space stats but that's all. I run this process every month and normally
everything works as expected. Any ideas why this time it didn't please?Perhaps DBCC UPDATEUSAGE will fix the incorrect stats.
Keith Kratochvil
"Shirley" <Shirley@.discussions.microsoft.com> wrote in message
news:B05EB837-51D3-40E1-B517-E758862A412A@.microsoft.com...
> Hi. I have a table that has 58 million rows and is about 24 GB. I ran a
> DBCC
> DBREINDEX which appeared to run fine - new fillfactor showing etc.
> However,
> showcontig still shows the indexes as being badly fragmented. I've run an
> update stats with fullscan and update usage which has corrected incorrect
> space stats but that's all. I run this process every month and normally
> everything works as expected. Any ideas why this time it didn't please?|||Thanks. I've already done that and it didn't.
"Keith Kratochvil" wrote:
> Perhaps DBCC UPDATEUSAGE will fix the incorrect stats.
> --
> Keith Kratochvil
>
> "Shirley" <Shirley@.discussions.microsoft.com> wrote in message
> news:B05EB837-51D3-40E1-B517-E758862A412A@.microsoft.com...
>
>|||Hi Shirley
Can you show us the details from DBCC SHOWCONTIG?
Also, how many indexes are there? Are they all fragmented, clustered and
nonclustered?
How much free space is in the database?
HTH
Kalen Delaney, SQL Server MVP
"Shirley" <Shirley@.discussions.microsoft.com> wrote in message
news:B05EB837-51D3-40E1-B517-E758862A412A@.microsoft.com...
> Hi. I have a table that has 58 million rows and is about 24 GB. I ran a
> DBCC
> DBREINDEX which appeared to run fine - new fillfactor showing etc.
> However,
> showcontig still shows the indexes as being badly fragmented. I've run an
> update stats with fullscan and update usage which has corrected incorrect
> space stats but that's all. I run this process every month and normally
> everything works as expected. Any ideas why this time it didn't please?|||Hi Kalen, thanks for replying.
There is one clustered IX and 3 NC IX. These are the results
- Scan Density [Best Count:Actual Count]......: 98.18% [292775:2981
97] CL
- Scan Density [Best Count:Actual Count]......: 54.10% [22661:41886
] NC
- Scan Density [Best Count:Actual Count]......: 52.88% [30754:58162
] NC
- Scan Density [Best Count:Actual Count]......: 54.74% [27210:49709
] NC
There is about 30 GB free space in the DB and it can autogrow.
"Kalen Delaney" wrote:
> Hi Shirley
> Can you show us the details from DBCC SHOWCONTIG?
> Also, how many indexes are there? Are they all fragmented, clustered and
> nonclustered?
> How much free space is in the database?
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> "Shirley" <Shirley@.discussions.microsoft.com> wrote in message
> news:B05EB837-51D3-40E1-B517-E758862A412A@.microsoft.com...
>
>|||Problem fixed. I ran an UPDATE STATISTICS again but with the FULLSCAN option
,
then re-ran the reorg and all looks fine now. Don't know why this just
started to happen but will do the updatestats before the reorg every time no
w.
"Shirley" wrote:
[vbcol=seagreen]
> Hi Kalen, thanks for replying.
> There is one clustered IX and 3 NC IX. These are the results
> - Scan Density [Best Count:Actual Count]......: 98.18% [292775:29
8197] CL
> - Scan Density [Best Count:Actual Count]......: 54.10% [22661:418
86] NC
> - Scan Density [Best Count:Actual Count]......: 52.88% [30754:581
62] NC
> - Scan Density [Best Count:Actual Count]......: 54.74% [27210:497
09] NC
> There is about 30 GB free space in the DB and it can autogrow.
>
> "Kalen Delaney" wrote:
>
DBCC SHOWCONTIG incorrect?
Hi. I have a table that has 58 million rows and is about 24 GB. I ran a DBCC
DBREINDEX which appeared to run fine - new fillfactor showing etc. However,
showcontig still shows the indexes as being badly fragmented. I've run an
update stats with fullscan and update usage which has corrected incorrect
space stats but that's all. I run this process every month and normally
everything works as expected. Any ideas why this time it didn't please?Perhaps DBCC UPDATEUSAGE will fix the incorrect stats.
--
Keith Kratochvil
"Shirley" <Shirley@.discussions.microsoft.com> wrote in message
news:B05EB837-51D3-40E1-B517-E758862A412A@.microsoft.com...
> Hi. I have a table that has 58 million rows and is about 24 GB. I ran a
> DBCC
> DBREINDEX which appeared to run fine - new fillfactor showing etc.
> However,
> showcontig still shows the indexes as being badly fragmented. I've run an
> update stats with fullscan and update usage which has corrected incorrect
> space stats but that's all. I run this process every month and normally
> everything works as expected. Any ideas why this time it didn't please?|||Thanks. I've already done that and it didn't.
"Keith Kratochvil" wrote:
> Perhaps DBCC UPDATEUSAGE will fix the incorrect stats.
> --
> Keith Kratochvil
>
> "Shirley" <Shirley@.discussions.microsoft.com> wrote in message
> news:B05EB837-51D3-40E1-B517-E758862A412A@.microsoft.com...
> > Hi. I have a table that has 58 million rows and is about 24 GB. I ran a
> > DBCC
> > DBREINDEX which appeared to run fine - new fillfactor showing etc.
> > However,
> > showcontig still shows the indexes as being badly fragmented. I've run an
> > update stats with fullscan and update usage which has corrected incorrect
> > space stats but that's all. I run this process every month and normally
> > everything works as expected. Any ideas why this time it didn't please?
>
>|||Hi Shirley
Can you show us the details from DBCC SHOWCONTIG?
Also, how many indexes are there? Are they all fragmented, clustered and
nonclustered?
How much free space is in the database?
--
HTH
Kalen Delaney, SQL Server MVP
"Shirley" <Shirley@.discussions.microsoft.com> wrote in message
news:B05EB837-51D3-40E1-B517-E758862A412A@.microsoft.com...
> Hi. I have a table that has 58 million rows and is about 24 GB. I ran a
> DBCC
> DBREINDEX which appeared to run fine - new fillfactor showing etc.
> However,
> showcontig still shows the indexes as being badly fragmented. I've run an
> update stats with fullscan and update usage which has corrected incorrect
> space stats but that's all. I run this process every month and normally
> everything works as expected. Any ideas why this time it didn't please?|||Hi Kalen, thanks for replying.
There is one clustered IX and 3 NC IX. These are the results
- Scan Density [Best Count:Actual Count]......: 98.18% [292775:298197] CL
- Scan Density [Best Count:Actual Count]......: 54.10% [22661:41886] NC
- Scan Density [Best Count:Actual Count]......: 52.88% [30754:58162] NC
- Scan Density [Best Count:Actual Count]......: 54.74% [27210:49709] NC
There is about 30 GB free space in the DB and it can autogrow.
"Kalen Delaney" wrote:
> Hi Shirley
> Can you show us the details from DBCC SHOWCONTIG?
> Also, how many indexes are there? Are they all fragmented, clustered and
> nonclustered?
> How much free space is in the database?
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> "Shirley" <Shirley@.discussions.microsoft.com> wrote in message
> news:B05EB837-51D3-40E1-B517-E758862A412A@.microsoft.com...
> > Hi. I have a table that has 58 million rows and is about 24 GB. I ran a
> > DBCC
> > DBREINDEX which appeared to run fine - new fillfactor showing etc.
> > However,
> > showcontig still shows the indexes as being badly fragmented. I've run an
> > update stats with fullscan and update usage which has corrected incorrect
> > space stats but that's all. I run this process every month and normally
> > everything works as expected. Any ideas why this time it didn't please?
>
>|||Problem fixed. I ran an UPDATE STATISTICS again but with the FULLSCAN option,
then re-ran the reorg and all looks fine now. Don't know why this just
started to happen but will do the updatestats before the reorg every time now.
"Shirley" wrote:
> Hi Kalen, thanks for replying.
> There is one clustered IX and 3 NC IX. These are the results
> - Scan Density [Best Count:Actual Count]......: 98.18% [292775:298197] CL
> - Scan Density [Best Count:Actual Count]......: 54.10% [22661:41886] NC
> - Scan Density [Best Count:Actual Count]......: 52.88% [30754:58162] NC
> - Scan Density [Best Count:Actual Count]......: 54.74% [27210:49709] NC
> There is about 30 GB free space in the DB and it can autogrow.
>
> "Kalen Delaney" wrote:
> > Hi Shirley
> >
> > Can you show us the details from DBCC SHOWCONTIG?
> > Also, how many indexes are there? Are they all fragmented, clustered and
> > nonclustered?
> > How much free space is in the database?
> >
> > --
> > HTH
> > Kalen Delaney, SQL Server MVP
> >
> >
> > "Shirley" <Shirley@.discussions.microsoft.com> wrote in message
> > news:B05EB837-51D3-40E1-B517-E758862A412A@.microsoft.com...
> > > Hi. I have a table that has 58 million rows and is about 24 GB. I ran a
> > > DBCC
> > > DBREINDEX which appeared to run fine - new fillfactor showing etc.
> > > However,
> > > showcontig still shows the indexes as being badly fragmented. I've run an
> > > update stats with fullscan and update usage which has corrected incorrect
> > > space stats but that's all. I run this process every month and normally
> > > everything works as expected. Any ideas why this time it didn't please?
> >
> >
> >
DBREINDEX which appeared to run fine - new fillfactor showing etc. However,
showcontig still shows the indexes as being badly fragmented. I've run an
update stats with fullscan and update usage which has corrected incorrect
space stats but that's all. I run this process every month and normally
everything works as expected. Any ideas why this time it didn't please?Perhaps DBCC UPDATEUSAGE will fix the incorrect stats.
--
Keith Kratochvil
"Shirley" <Shirley@.discussions.microsoft.com> wrote in message
news:B05EB837-51D3-40E1-B517-E758862A412A@.microsoft.com...
> Hi. I have a table that has 58 million rows and is about 24 GB. I ran a
> DBCC
> DBREINDEX which appeared to run fine - new fillfactor showing etc.
> However,
> showcontig still shows the indexes as being badly fragmented. I've run an
> update stats with fullscan and update usage which has corrected incorrect
> space stats but that's all. I run this process every month and normally
> everything works as expected. Any ideas why this time it didn't please?|||Thanks. I've already done that and it didn't.
"Keith Kratochvil" wrote:
> Perhaps DBCC UPDATEUSAGE will fix the incorrect stats.
> --
> Keith Kratochvil
>
> "Shirley" <Shirley@.discussions.microsoft.com> wrote in message
> news:B05EB837-51D3-40E1-B517-E758862A412A@.microsoft.com...
> > Hi. I have a table that has 58 million rows and is about 24 GB. I ran a
> > DBCC
> > DBREINDEX which appeared to run fine - new fillfactor showing etc.
> > However,
> > showcontig still shows the indexes as being badly fragmented. I've run an
> > update stats with fullscan and update usage which has corrected incorrect
> > space stats but that's all. I run this process every month and normally
> > everything works as expected. Any ideas why this time it didn't please?
>
>|||Hi Shirley
Can you show us the details from DBCC SHOWCONTIG?
Also, how many indexes are there? Are they all fragmented, clustered and
nonclustered?
How much free space is in the database?
--
HTH
Kalen Delaney, SQL Server MVP
"Shirley" <Shirley@.discussions.microsoft.com> wrote in message
news:B05EB837-51D3-40E1-B517-E758862A412A@.microsoft.com...
> Hi. I have a table that has 58 million rows and is about 24 GB. I ran a
> DBCC
> DBREINDEX which appeared to run fine - new fillfactor showing etc.
> However,
> showcontig still shows the indexes as being badly fragmented. I've run an
> update stats with fullscan and update usage which has corrected incorrect
> space stats but that's all. I run this process every month and normally
> everything works as expected. Any ideas why this time it didn't please?|||Hi Kalen, thanks for replying.
There is one clustered IX and 3 NC IX. These are the results
- Scan Density [Best Count:Actual Count]......: 98.18% [292775:298197] CL
- Scan Density [Best Count:Actual Count]......: 54.10% [22661:41886] NC
- Scan Density [Best Count:Actual Count]......: 52.88% [30754:58162] NC
- Scan Density [Best Count:Actual Count]......: 54.74% [27210:49709] NC
There is about 30 GB free space in the DB and it can autogrow.
"Kalen Delaney" wrote:
> Hi Shirley
> Can you show us the details from DBCC SHOWCONTIG?
> Also, how many indexes are there? Are they all fragmented, clustered and
> nonclustered?
> How much free space is in the database?
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> "Shirley" <Shirley@.discussions.microsoft.com> wrote in message
> news:B05EB837-51D3-40E1-B517-E758862A412A@.microsoft.com...
> > Hi. I have a table that has 58 million rows and is about 24 GB. I ran a
> > DBCC
> > DBREINDEX which appeared to run fine - new fillfactor showing etc.
> > However,
> > showcontig still shows the indexes as being badly fragmented. I've run an
> > update stats with fullscan and update usage which has corrected incorrect
> > space stats but that's all. I run this process every month and normally
> > everything works as expected. Any ideas why this time it didn't please?
>
>|||Problem fixed. I ran an UPDATE STATISTICS again but with the FULLSCAN option,
then re-ran the reorg and all looks fine now. Don't know why this just
started to happen but will do the updatestats before the reorg every time now.
"Shirley" wrote:
> Hi Kalen, thanks for replying.
> There is one clustered IX and 3 NC IX. These are the results
> - Scan Density [Best Count:Actual Count]......: 98.18% [292775:298197] CL
> - Scan Density [Best Count:Actual Count]......: 54.10% [22661:41886] NC
> - Scan Density [Best Count:Actual Count]......: 52.88% [30754:58162] NC
> - Scan Density [Best Count:Actual Count]......: 54.74% [27210:49709] NC
> There is about 30 GB free space in the DB and it can autogrow.
>
> "Kalen Delaney" wrote:
> > Hi Shirley
> >
> > Can you show us the details from DBCC SHOWCONTIG?
> > Also, how many indexes are there? Are they all fragmented, clustered and
> > nonclustered?
> > How much free space is in the database?
> >
> > --
> > HTH
> > Kalen Delaney, SQL Server MVP
> >
> >
> > "Shirley" <Shirley@.discussions.microsoft.com> wrote in message
> > news:B05EB837-51D3-40E1-B517-E758862A412A@.microsoft.com...
> > > Hi. I have a table that has 58 million rows and is about 24 GB. I ran a
> > > DBCC
> > > DBREINDEX which appeared to run fine - new fillfactor showing etc.
> > > However,
> > > showcontig still shows the indexes as being badly fragmented. I've run an
> > > update stats with fullscan and update usage which has corrected incorrect
> > > space stats but that's all. I run this process every month and normally
> > > everything works as expected. Any ideas why this time it didn't please?
> >
> >
> >
DBCC SHOWCONTIG inconsistency??
I have a table where I store around 1 million rows for 7 days.
I run a DBCC SHOWCONTIG every week and I noticed that the number of
Rows and the Average Free Bytes are stable but the number of Extents
and the Average Record Size keeps increasing.
Any idea how to explain this incrase? and how to stop it? Will DBCC
DBREINDEX will help?
I run a DBCC SHOWCONTIG every week and I noticed that the number of
Rows and the Average Free Bytes are stable but the number of Extents
and the Average Record Size keeps increasing.
Any idea how to explain this incrase? and how to stop it? Will DBCC
DBREINDEX will help?
Here is my data:
Rows: 1166273 (same as last week)
Extents: 147099 (+10% since last week)
Max RecordSize: 7050
Average Record Size: 7688 (+10% since last week)
AverageFreeBytes: 372
Average Page Density: 95
Scan density: 30Guillaume (loizeau@.hotmail.com) writes:
> I have a table where I store around 1 million rows for 7 days.
> I run a DBCC SHOWCONTIG every week and I noticed that the number of
> Rows and the Average Free Bytes are stable but the number of Extents
> and the Average Record Size keeps increasing.
> Any idea how to explain this incrase? and how to stop it? Will DBCC
> DBREINDEX will help?
> Here is my data:
> Rows: 1166273 (same as last week)
> Extents: 147099 (+10% since last week)
> Max RecordSize: 7050
> Average Record Size: 7688 (+10% since last week)
> AverageFreeBytes: 372
> Average Page Density: 95
> Scan density: 30
If he row size is growing that indicates that the newer data
has higher row size. But it cannot increase with 10% for long,
since 8060 bytes is the max row size. (Unless that row size includes
text/image data.)
> Around 150,000 new rows are inserted every day and 150,000 rows (older
> than 7 days) are deleted every day.
That can certainly do a fragmentation, and you should run DBCC DBREINDEX
at least once a week.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland,
The Average Record Size has reached 7984 bytes and it's still
increasing.
The first time I had the problem my filegroup was full and I didn't have
time to investigate the issue so I just truncated the table. Now, I have
plenty of space in the filegroup and I'd like to understand the problem
before running any DBCC DBREINDEX.
Could the statistics for the table be wrong? or not updated?
*** Sent via Developersdex http://www.developersdex.com ***|||Guillaume (anonymous@.devdex.com) writes:
> The Average Record Size has reached 7984 bytes and it's still
> increasing.
> The first time I had the problem my filegroup was full and I didn't have
> time to investigate the issue so I just truncated the table. Now, I have
> plenty of space in the filegroup and I'd like to understand the problem
> before running any DBCC DBREINDEX.
> Could the statistics for the table be wrong? or not updated?
If the average record size keeps increaseing, it's probably because of
your data. If that is abnormal, you will need to look at the data you
load. Or do you have any reason to assume that the number is incorrect?
What is your maximum record size?
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland
The table contains only "events" monitoring my system and the data I
store in that table should not grow over time. I have several systems
running in parallel and this is the only DB where I have the problem.
Here is what I get from the SHOWCONTIG on the suspicious DB:
Minimum Record Size: 116
Maximum record Size: 7050
Average Record Size: 7984
Do you know why the Average Record Size is bigger then the Max Record
Size? it doesn't make sense.
Here is another SHOWCONTIG result from a different system.
Minimum Record Size: 111
Maximum record Size: 7231
Average Record Size: 420
*** Sent via Developersdex http://www.developersdex.com ***|||Guillaume (anonymous@.devdex.com) writes:
> The table contains only "events" monitoring my system and the data I
> store in that table should not grow over time. I have several systems
> running in parallel and this is the only DB where I have the problem.
> Here is what I get from the SHOWCONTIG on the suspicious DB:
> Minimum Record Size: 116
> Maximum record Size: 7050
> Average Record Size: 7984
> Do you know why the Average Record Size is bigger then the Max Record
> Size? it doesn't make sense.
That's undoubtedly a little funny. Maybe Paul Randal knows about some
reason how this can arise. (And if Paul does not know, no one else is
likely to know.)
Then again, while the number is funny, it's not likely to be a major
problem in itself, or do you have other problems with the table? A DBCC
CHECKDB could reveal some corruption.
I would try a DBCC DBREINDEX and see what happens.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||>Then again, while the number is funny, it's not likely to be >a major
problem in itself, or do you have other problems with >the table?
No I don't. The only problem I have is the size of this single table
growing every day.
>A DBCC CHECKDB could reveal some corruption.
Yes I will try to run it.
>I would try a DBCC DBREINDEX and see what happens.
I haven't done it yet because I was trying to figure out why the table
size was growing steadily. If there is nothing I can do right now, I'll
try the DBCC DBREINDEX
Thanks for your help
*** Sent via Developersdex http://www.developersdex.com ***|||The only think I can think of is a bug in showcontig where we're dividing
the cumulative row size count by the wrong number of pages, but I find it
hard to believe that such a bug has existed unnoticed for 6 years (since I
rewrote most of showcontig in Spring '99).
Can you post the full showcontig output for the two tables (one on the
suspicious system and the comparable table on another system)?
Thanks
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns96477EB94FE49Yazorman@.127.0.0.1...
> Guillaume (anonymous@.devdex.com) writes:
> > The table contains only "events" monitoring my system and the data I
> > store in that table should not grow over time. I have several systems
> > running in parallel and this is the only DB where I have the problem.
> > Here is what I get from the SHOWCONTIG on the suspicious DB:
> > Minimum Record Size: 116
> > Maximum record Size: 7050
> > Average Record Size: 7984
> > Do you know why the Average Record Size is bigger then the Max Record
> > Size? it doesn't make sense.
> That's undoubtedly a little funny. Maybe Paul Randal knows about some
> reason how this can arise. (And if Paul does not know, no one else is
> likely to know.)
> Then again, while the number is funny, it's not likely to be a major
> problem in itself, or do you have other problems with the table? A DBCC
> CHECKDB could reveal some corruption.
> I would try a DBCC DBREINDEX and see what happens.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||I looked through the code and found the problem.
In SQL Server 2000, showcontig uses the size information from all the rows, but only counts non-ghost rows in the rowcount. For an index with a bunch of ghost rows, the avg rowsize will be higher than the real value, as the total rowsize is being divided by the wrong (smaller) number of rows.
In SQL Server 2005 this problem has been fixed. I checked back through the internal bug database and this issue was found internally in 2002 and the decision was made not to fix it in SQL Server 2000. Shows how good my memory is.
If you heavily rely on this value I recommend rebuilding the index as we won't provide a fix for it at this stage of SQL Server 2000's lifecycle.
My apologies for the time you've spent trying to figure this out but I'm glad we finally did. Let me know if you have an further questions.
Thanks and regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Guillaume" <anonymous@.devdex.com> wrote in message news:Nvzce.442$zX2.13250@.news.uswest.net...
Here is the SHOWCONTIG for the same table on 3 Databases. I have NOT
done a DBCC DBREINDEX on any of those tables.
Suspicious table 1.3 million rows
ObjectName Events
ObjectId 1221579390
IndexName IX_Time
IndexId 1
Level 0
Pages 1372708
Rows 1323293
Min RecordSize 116
Max RecordSize 7050
Average RecordSize 7984.698
Extents 173093
ExtentSwitches 640050
AverageFreeBytes 370.9679871
Average Page Density 95.41675568
Scan Density 26.80864494
Best Count 171589
Actual Count 640051
Logical Fragmentation 12.87914085
ExtentFragmentation 6.509795189
Normal table 1.1 million rows
ObjectName Events
ObjectId 1221579390
IndexName IX_Time
IndexId 1
Level 0
Pages 63520
Rows 1170832
Min RecordSize 111
Max RecordSize 7231
Average RecordSize 420.889
Extents 7981
ExtentSwitches 8036
AverageFreeBytes 301.098999
Average Page Density 96.27997589
Scan Density 98.793082
Best Count 7940
Actual Count 8037
Logical Fragmentation 0.127518892
ExtentFragmentation 33.07855988
Normal table 9 million rows
ObjectName Events
ObjectId 1221579390
IndexName IX_Time
IndexId 1
Level 0
Pages 467440
Rows 9048200
Min RecordSize 130
Max RecordSize 7059
Average RecordSize 375.19
Extents 61,950
ExtentSwitches 115,936
AverageFreeBytes 794.7390137
Average Page Density 90.18113708
Scan Density 50.39806102
Best Count 58430
Actual Count 115937
Logical Fragmentation 11.53752327
ExtentFragmentation 12.42292213
*** Sent via Developersdex http://www.developersdex.com ***|||BTW - forgot to say - the reason you're seeing the avg row size increase is that (ironically) you're deleting more records and so the number of ghost records is growing, further distorting the calculation.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message news:4272e24a$1@.news.microsoft.com...
I looked through the code and found the problem.
In SQL Server 2000, showcontig uses the size information from all the rows, but only counts non-ghost rows in the rowcount. For an index with a bunch of ghost rows, the avg rowsize will be higher than the real value, as the total rowsize is being divided by the wrong (smaller) number of rows.
In SQL Server 2005 this problem has been fixed. I checked back through the internal bug database and this issue was found internally in 2002 and the decision was made not to fix it in SQL Server 2000. Shows how good my memory is.
If you heavily rely on this value I recommend rebuilding the index as we won't provide a fix for it at this stage of SQL Server 2000's lifecycle.
My apologies for the time you've spent trying to figure this out but I'm glad we finally did. Let me know if you have an further questions.
Thanks and regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Guillaume" <anonymous@.devdex.com> wrote in message news:Nvzce.442$zX2.13250@.news.uswest.net...
Here is the SHOWCONTIG for the same table on 3 Databases. I have NOT
done a DBCC DBREINDEX on any of those tables.
Suspicious table 1.3 million rows
ObjectName Events
ObjectId 1221579390
IndexName IX_Time
IndexId 1
Level 0
Pages 1372708
Rows 1323293
Min RecordSize 116
Max RecordSize 7050
Average RecordSize 7984.698
Extents 173093
ExtentSwitches 640050
AverageFreeBytes 370.9679871
Average Page Density 95.41675568
Scan Density 26.80864494
Best Count 171589
Actual Count 640051
Logical Fragmentation 12.87914085
ExtentFragmentation 6.509795189
Normal table 1.1 million rows
ObjectName Events
ObjectId 1221579390
IndexName IX_Time
IndexId 1
Level 0
Pages 63520
Rows 1170832
Min RecordSize 111
Max RecordSize 7231
Average RecordSize 420.889
Extents 7981
ExtentSwitches 8036
AverageFreeBytes 301.098999
Average Page Density 96.27997589
Scan Density 98.793082
Best Count 7940
Actual Count 8037
Logical Fragmentation 0.127518892
ExtentFragmentation 33.07855988
Normal table 9 million rows
ObjectName Events
ObjectId 1221579390
IndexName IX_Time
IndexId 1
Level 0
Pages 467440
Rows 9048200
Min RecordSize 130
Max RecordSize 7059
Average RecordSize 375.19
Extents 61,950
ExtentSwitches 115,936
AverageFreeBytes 794.7390137
Average Page Density 90.18113708
Scan Density 50.39806102
Best Count 58430
Actual Count 115937
Logical Fragmentation 11.53752327
ExtentFragmentation 12.42292213
*** Sent via Developersdex http://www.developersdex.com ***|||Thanks a lot for your answer,
Based on your description, it turns out that my problem is related to
ghost rows.
Do you have any idea why I have such a large number of ghost rows on one
single table from only one database? I assume the ghost rows are
periodically deleted from the database. If I'm right then something
might be going wrong during the physical delete of the rows.
I ran a TRUNCATE TABLE once on my table and it fixed my issue for a
period of time but now the problem is back so do you think it might make
a difference if I deleted and then recreated the table or should I just
run DBREINDEX from time to time?
Guillaume
*** Sent via Developersdex http://www.developersdex.com ***|||Do you have any trace flags turned on? I'm wondering if someone's caused
ghost cleanup to be turned off.
Also, do you have any (very) long running transactions in that database?
That would also prevent any ghost rows need by potential rollback of these
transactions from being deleted.
Truncate will remove them as it deletes all rows from the table.
Yes, a reindex will have the same effect but shouldn't be needed because of
the automatic ghost cleanup process.
This seems to be the root of the problem - the number of ghost rows climbing
steadily.
Thanks
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Guillaume" <anonymous@.devdex.com> wrote in message
news:Zalde.50$wu5.2310@.news.uswest.net...
> Thanks a lot for your answer,
> Based on your description, it turns out that my problem is related to
> ghost rows.
> Do you have any idea why I have such a large number of ghost rows on one
> single table from only one database? I assume the ghost rows are
> periodically deleted from the database. If I'm right then something
> might be going wrong during the physical delete of the rows.
> I ran a TRUNCATE TABLE once on my table and it fixed my issue for a
> period of time but now the problem is back so do you think it might make
> a difference if I deleted and then recreated the table or should I just
> run DBREINDEX from time to time?
> Guillaume
> *** Sent via Developersdex http://www.developersdex.com ***|||Paul,
I don't have any specific Trace flags on. And I don't have any long
running transaction. Do you know if there is a way to check why the
ghost cleanup doesn't run on a single table on my machine?
I recently ran the following command:
DBCC TRACEON (2514)
GO
DBCC CHECKTABLE (my event table)
GO
The result was:
Ghost Record count = 52 millions
How can delete those ghost records from my table?
Can I force the ghost cleanup service to run?
Thanks a lot.
Guillaume
*** Sent via Developersdex http://www.developersdex.com ***|||Guillaume,
Normally there is a background task that wakes up to clean some ghosted
records every 5 seconds or so. To force the ghost cleanup activities, you
can:
First, make sure traceflag 661 is turned off in the server by
dbcc traceoff(661, -1)
dbcc tracestatus(661, -1)
go
Then, issue a scan on the table/index that contains the 52 millions of
ghosted record, something like:
select * from [my event table] with (index=the index id of the index that
contains ghosted record)
go
Upon seeing a ghosted record, the scan will enqueue a request to clean it up
aggressively.
--
Gang He
Software Design Engineer
Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Guillaume" <anonymous@.devdex.com> wrote in message
news:Vzage.300$693.7473@.news.uswest.net...
> Paul,
> I don't have any specific Trace flags on. And I don't have any long
> running transaction. Do you know if there is a way to check why the
> ghost cleanup doesn't run on a single table on my machine?
> I recently ran the following command:
> DBCC TRACEON (2514)
> GO
> DBCC CHECKTABLE (my event table)
> GO
> The result was:
> Ghost Record count = 52 millions
> How can delete those ghost records from my table?
> Can I force the ghost cleanup service to run?
> Thanks a lot.
> Guillaume
> *** Sent via Developersdex http://www.developersdex.com ***
DBCC SHOWCONTIG and SQL2k Maintenance Plan Question
When performing the DBCC SHOWCONTIG will this perform table locks? If
I use the WITH FAST option will this allow data to be written to the file?
Also when using the maintenance plan wizard to perform maintenance, will
tables be locked when the indexes are rebuilt and data is reorganized?
Thanks,
-Ben(I'm assuming you're using SQL Server 2000 as you mention the WITH FAST
option I added in that release)
DBCC SHOWCONTIG ... WITH FAST takes a table IS lock - thus allowing updates.
Without WITH FAST is takes a table S lock. Look in BOL for more details
here.
Rebuilding an index requires a table S lock if you're rebuilding a
non-clustered index, and a table X lock for rebuilding a clustered index.
Running DBCC INDEXDEFRAG only requires a table IX lock - so allows updates.
You should read the whitepaper below for more details:
http://www.microsoft.com/technet/tr...ze/ss2kidbp.asp
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Ben" <ben@.brainspout.com> wrote in message
news:uoGzfuO5DHA.1368@.TK2MSFTNGP10.phx.gbl...
I use the WITH FAST option will this allow data to be written to the file?
Also when using the maintenance plan wizard to perform maintenance, will
tables be locked when the indexes are rebuilt and data is reorganized?
Thanks,
-Ben(I'm assuming you're using SQL Server 2000 as you mention the WITH FAST
option I added in that release)
DBCC SHOWCONTIG ... WITH FAST takes a table IS lock - thus allowing updates.
Without WITH FAST is takes a table S lock. Look in BOL for more details
here.
Rebuilding an index requires a table S lock if you're rebuilding a
non-clustered index, and a table X lock for rebuilding a clustered index.
Running DBCC INDEXDEFRAG only requires a table IX lock - so allows updates.
You should read the whitepaper below for more details:
http://www.microsoft.com/technet/tr...ze/ss2kidbp.asp
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Ben" <ben@.brainspout.com> wrote in message
news:uoGzfuO5DHA.1368@.TK2MSFTNGP10.phx.gbl...
quote:
> When performing the DBCC SHOWCONTIG will this perform table locks? If
> I use the WITH FAST option will this allow data to be written to the file?
> Also when using the maintenance plan wizard to perform maintenance, will
> tables be locked when the indexes are rebuilt and data is reorganized?
> Thanks,
> -Ben
>
DBCC SHOWCONTIG and SQL2k Maintenance Plan Question
When performing the DBCC SHOWCONTIG will this perform table locks? If
I use the WITH FAST option will this allow data to be written to the file?
Also when using the maintenance plan wizard to perform maintenance, will
tables be locked when the indexes are rebuilt and data is reorganized?
Thanks,
-Ben(I'm assuming you're using SQL Server 2000 as you mention the WITH FAST
option I added in that release)
DBCC SHOWCONTIG ... WITH FAST takes a table IS lock - thus allowing updates.
Without WITH FAST is takes a table S lock. Look in BOL for more details
here.
Rebuilding an index requires a table S lock if you're rebuilding a
non-clustered index, and a table X lock for rebuilding a clustered index.
Running DBCC INDEXDEFRAG only requires a table IX lock - so allows updates.
You should read the whitepaper below for more details:
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/maintain/optimize/ss2kidbp.asp
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Ben" <ben@.brainspout.com> wrote in message
news:uoGzfuO5DHA.1368@.TK2MSFTNGP10.phx.gbl...
> When performing the DBCC SHOWCONTIG will this perform table locks? If
> I use the WITH FAST option will this allow data to be written to the file?
> Also when using the maintenance plan wizard to perform maintenance, will
> tables be locked when the indexes are rebuilt and data is reorganized?
> Thanks,
> -Ben
>
I use the WITH FAST option will this allow data to be written to the file?
Also when using the maintenance plan wizard to perform maintenance, will
tables be locked when the indexes are rebuilt and data is reorganized?
Thanks,
-Ben(I'm assuming you're using SQL Server 2000 as you mention the WITH FAST
option I added in that release)
DBCC SHOWCONTIG ... WITH FAST takes a table IS lock - thus allowing updates.
Without WITH FAST is takes a table S lock. Look in BOL for more details
here.
Rebuilding an index requires a table S lock if you're rebuilding a
non-clustered index, and a table X lock for rebuilding a clustered index.
Running DBCC INDEXDEFRAG only requires a table IX lock - so allows updates.
You should read the whitepaper below for more details:
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/maintain/optimize/ss2kidbp.asp
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Ben" <ben@.brainspout.com> wrote in message
news:uoGzfuO5DHA.1368@.TK2MSFTNGP10.phx.gbl...
> When performing the DBCC SHOWCONTIG will this perform table locks? If
> I use the WITH FAST option will this allow data to be written to the file?
> Also when using the maintenance plan wizard to perform maintenance, will
> tables be locked when the indexes are rebuilt and data is reorganized?
> Thanks,
> -Ben
>
Subscribe to:
Posts (Atom)