Fixing My Table Based On Dbcc Showcontig Results
Jul 20, 2005
Can someone please help me interpret this result set below and suggest
on way I can speed up my table? What changes should I make?
DBCC SHOWCONTIG scanning 'tblListing' table...
Table: 'tblListing' (1092914965); index ID: 1, database ID: 13
TABLE level scan performed.
- Pages Scanned................................: 97044
- Extents Scanned..............................: 12177
- Extent Switches..............................: 13452
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 90.17% [12131:13453]
- Logical Scan Fragmentation ..................: 0.86%
- Extent Scan Fragmentation ...................: 2.68%
- Avg. Bytes Free per Page.....................: 1415.8
- Avg. Page Density (full).....................: 82.51%
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
Thank you.
View 2 Replies
ADVERTISEMENT
Feb 19, 2008
Hi:
how could I load "DBCC showcontig (@TableName) with tableResults" to a table?
I created a table and let insert to it via above "DBCC...."
but problem is the results with "DBCC execution completed. If DBCC printed error messages, contact your system administrator.", or how could I mute this output line for each table ?
thanks
David
--=========================================
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
ObjectName ObjectId IndexName IndexId Level Pages Rows MinimumRecordSize MaximumRecordSize AverageRecordSize ForwardedRecords Extents ExtentSwitches AverageFreeBytes AveragePageDensity ScanDensity BestCount ActualCount LogicalFragmentation ExtentFragmentation
-------------------------------------------------------------------------------------------------------------------------------- ----------- -------------------------------------------------------------------------------------------------------------------------------- ----------- ----------- -------------------- -------------------- ----------------- ----------------- ---------------------- -------------------- -------------------- -------------------- ---------------------- ---------------------- ---------------------- -------------------- -------------------- ---------------------- ----------------------
sysproxylogin 37575172 clust 1 0 0 0 0 0 0 0 0 0 0 0 100 0 0 0 0
View 1 Replies
View Related
Nov 15, 2007
Dear Experts,
just now i've read a great article from sql server performance.com
and understand several key factors related to performance.
still i have one doubt that what will be the best ratio between
extents scanned and extent switches.
here i'm pasting some of my table result. please suggest me how is the performance of the table
table 1
- Pages Scanned................................: 85618
- Extents Scanned..............................: 11283
- Extent Switches..............................: 85405
- Avg. Pages per Extent........................: 7.6
- Scan Density [Best Count:Actual Count].......: 12.53% [10703:85406]
- Logical Scan Fragmentation ..................: 49.93%
- Extent Scan Fragmentation ...................: 46.31%
- Avg. Bytes Free per Page.....................: 2707.1
- Avg. Page Density (full).....................: 66.55%
table2
- Pages Scanned................................: 60648
- Extents Scanned..............................: 7650
- Extent Switches..............................: 60639
- Avg. Pages per Extent........................: 7.9
- Scan Density [Best Count:Actual Count].......: 12.50% [7581:60640]
- Logical Scan Fragmentation ..................: 50.93%
- Extent Scan Fragmentation ...................: 64.01%
- Avg. Bytes Free per Page.....................: 2672.5
- Avg. Page Density (full).....................: 66.98%
thankyou very much
Vinod
Even you learn 1%, Learn it with 100% confidence.
View 3 Replies
View Related
May 21, 2008
Dear All,
i've used the DBCC showcontig command against my table table103
but i dont know how to analyze the results of fragmentation levels. please give me some explanations or some good links.....
the results are:
DBCC SHOWCONTIG scanning 'TABLE103' table...
Table: 'TABLE103' (1899257921); index ID: 1, database ID: 10
TABLE level scan performed.
- Pages Scanned................................: 20
- Extents Scanned..............................: 13
- Extent Switches..............................: 18
- Avg. Pages per Extent........................: 1.5
- Scan Density [Best Count:Actual Count].......: 15.79% [3:19]
- Logical Scan Fragmentation ..................: 90.00%
- Extent Scan Fragmentation ...................: 92.31%
- Avg. Bytes Free per Page.....................: 3281.4
- Avg. Page Density (full).....................: 59.46%
Vinod
Even you learn 1%, Learn it with 100% confidence.
View 10 Replies
View Related
May 7, 2002
I was attempting to run the dbcc command on a couple of my databases and I get some errors on some servers but not others. Is there a reason for this?
What am I doing wrong? Is there a server setting that stops dbcc commands?
Here are the commands and errors
dbcc showcontig
Server: Msg 2583, Level 16, State 3, Line 1
An incorrect number of parameters was given to the DBCC statement.
dbcc showcontig (PS_ABS_HIST_COM_UK)
Server: Msg 2560, Level 16, State 14, Line 1
Parameter 1 is incorrect for this DBCC statement.
View 3 Replies
View Related
May 8, 2002
Hi
Here us the Output of DBCC SHOWCONTIG for one of my tables
Row Size = 82 (i.e Sum of the lengths of all the Columns)
No: of Rows = 162 (No of Rows in the table)
162 Rows can fit in two pages, Is there any way to make sure that these pages go on a same Extent so that my Scan Density will be 100.00% ( Ratio of Extent Switches to the Extent Scanned)
I tried all the Combinations of rebuilding the Clustered Indexes,BCP out the data and BCP IN, But all in vain....It does not put the pages in a same extent.
Any Help in this regard is greatly appreciated!!
DBCC SHOWCONTIG scanning 'VENU_TEMP1' table...
Table: 'VENU_TEMP1' (1681441064); index ID: 1, database ID: 18
TABLE level scan performed.
- Pages Scanned................................: 2
- Extents Scanned..............................: 2
- Extent Switches..............................: 1
- Avg. Pages per Extent........................: 1.0
- Scan Density [Best Count:Actual Count].......: 50.00% [1:2]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 50.00%
- Avg. Bytes Free per Page.....................: 58.0
- Avg. Page Density (full).....................: 99.28%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Thanks In Advance
Venu
View 2 Replies
View Related
May 11, 2001
Whenever I try to run this command against a table, I get the following error message:
Parameter 1 is incorrect for this DBCC statement.
Any ideas?
Thanks
View 1 Replies
View Related
May 17, 2001
Results are -
- Pages Scanned................................: 21767
- Extents Scanned..............................: 2738
- Extent Switches..............................: 19919
- Avg. Pages per Extent........................: 7.9
- Scan Density [Best Count:Actual Count].......: 13.66% [2721:19920]
- Logical Scan Fragmentation ..................: 91.57%
- Extent Scan Fragmentation ...................: 29.69%
- Avg. Bytes Free per Page.....................: 4409.4
- Avg. Page Density (full).....................: 45.52%
is this good/bad. How do I interpret these results ?
Could someone drop some lines.
Thanks a lot.
Rick
View 1 Replies
View Related
Jul 17, 2001
Does anyone know of a way to capture the results of DBCC ShowContig. My intentions are to capture these results, particularly the Avg. Page Density value and build my Reindexing jobs based on those values. Any ideas would be appreciated.
Thanks
Joe
View 1 Replies
View Related
Feb 18, 2001
Dear sir
I understand the term fragmentation.Will u please giude me regarding what is meant by internal fragmentation and external fragmentation their difference and given an output of DBCC SHOWCONTIG how do we determine wheather the there is interfragmentation or external fragmentation
View 1 Replies
View Related
Mar 10, 2005
Hi,
Ive just created a table, clustered index with fillfactor 70% (as it will be doing thousands of inserts and deletions daily).
After a few thousand inserts, i run dbcc showcontig on the table.
Result:
---------
Table: 'MyTable' (1650104919); index ID: 1, database ID: 10
TABLE level scan performed.
- Pages Scanned................................: 5460
- Extents Scanned..............................: 686
- Extent Switches..............................: 1885
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 36.21% [683:1886]
- Logical Scan Fragmentation ..................: 13.19%
- Extent Scan Fragmentation ...................: 0.29%
- Avg. Bytes Free per Page.....................: 2083.2
- Avg. Page Density (full).....................: 74.26%
***the Extent switches just balloons out, and scan density drops badly. is this terrible?
Also - What is the main difference between "Logical Scan Fragmentation" & "Extent Scan Fragmentation". Every time, its either one of these values is much higher than the other. What is the best practice, which of the 2 should I be trying to keep fragmentation low?
View 12 Replies
View Related
Apr 7, 1999
Can anybody tell me what the "Disconnected Overflow Pages" means in the
DBCC SHOWCONTIG output.
Thanks
Kevin Napier
View 1 Replies
View Related
Oct 17, 2004
DBCC SHOWCONTIG scanning 'MYTAB' table...
Table: 'MYTAB'(50099219); index ID: 1, database ID: 7
TABLE level scan performed.
- Pages Scanned................................: 150
- Extents Scanned..............................: 25
- Extent Switches..............................: 25
- Avg. Pages per Extent........................: 6.0
- Scan Density [Best Count:Actual Count].......: 73.08% [19:26]
- Logical Scan Fragmentation ..................: 1.33%
- Extent Scan Fragmentation ...................: 96.00%
- Avg. Bytes Free per Page.....................: 878.5
- Avg. Page Density (full).....................: 89.15%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Since Extents Scanned and Extent Switches r equal, why the density is slow, what about pages per Extent, can it be manaully increased?
Howdy
View 1 Replies
View Related
Apr 18, 2006
Hi
The following is the result of the DBCC Showcontig
DBCC SHOWCONTIG scanning 'publishers' table...
Table: 'publishers' (2057058364); 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 ..................: 100.00%
- Extent Scan Fragmentation ...................: 0.00%
- Avg. Bytes Free per Page.....................: 7699.0
- Avg. Page Density (full).....................: 4.88%
Now as a per a Doc i read the
Scan Density should be 100%
Logical Scan Fragmentation should be from 0-10%
Extent Scan Fragmentation should be 0%
Here Logical Scan Fragmentation ..................: 100.00%
So I ran the DBCC INDEXDEFRAG command
Still its showing the same results, can someone explain , should it not become 0-10%
I even tried the DBCC DBREINDEX
No Effects Please Advice.
View 2 Replies
View Related
Oct 30, 2007
I feel I have a REAL question this time
I have been trying to up my sql knowledge and have been reading the following; http://www.sql-server-performance.com/articles/dba/dt_dbcc_showcontig_p1.aspx]
Now I think I understand it fully but I have a question about this. I understand Scan density, logical and extent scan fragmentation and I also now understand page density. My question. How do you know depending on your scenario how much to leave in your pages? Would you reindex heavily read tables to be of high scan density with low logical and extent fragmentation and aim for as much page density as possible? Well what if you have a heavily read table that also has a lot of inserts on it daily. What percentage of page density would you tell dbcc reindex to leave in your pages in order to avoid page splits? Or rather, would you allow them to occur and just have dbcc reindex run often? As im migrating from sql 2000 this is the first sql 2005 function I have started to look at and I already have so many questions...
"Impossible is Nothing"
View 9 Replies
View Related
Aug 21, 2001
Hello,
I'm new to SQL Server, can somebody please give me a good explaination about whats wrong in the following table stats. I tried to see it in BOL, but I wuold appreciate if someone can suggest me the rules here, for expample what should be the ideal scan density and so on. Also let me know what one should do if there are some problems like mentioned below:
Thanks in advance.
Zubair
DBCC SHOWCONTIG scanning 'OrderDetails' table...
Table: 'OrderDetails' (2141354793); index ID: 1, database ID: 7
TABLE level scan performed.
- Pages Scanned................................: 24168
- Extents Scanned..............................: 3034
- Extent Switches..............................: 4984
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 60.60% [3021:4985]
- Logical Scan Fragmentation ..................: 65.76%
- Extent Scan Fragmentation ...................: 2.80%
- Avg. Bytes Free per Page.....................: 775.8
- Avg. Page Density (full).....................: 90.42%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
View 1 Replies
View Related
Jul 14, 2004
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?
Clive
View 1 Replies
View Related
Nov 3, 2005
hi,
I am running this :
dbcc showcontig (EMPLOYEE_EXTRACT)
i got error below:
Server: Msg 2501, Level 16, State 45, Line 1
Could not find a table or object named 'EMPLOYEE_EXTRACT'. Check sysobjects.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Then i look for that table name in sysobjects table it return me a record with that name
select [name] from sysobjects where [name] ='EMPLOYEE_EXTRACT'
what is the problem here? - Any idea would be greatly appreciated.
I run dbcc showcontig on other table , it runs fine.
Thanks
View 3 Replies
View Related
Jul 23, 2005
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 ofRows and the Average Free Bytes are stable but the number of Extentsand the Average Record Size keeps increasing.Any idea how to explain this incrase? and how to stop it? Will DBCCDBREINDEX will help?Here is my data:Rows: 1166273 (same as last week)Extents: 147099 (+10% since last week)Max RecordSize: 7050Average Record Size: 7688 (+10% since last week)AverageFreeBytes: 372Average Page Density: 95Scan density: 30
View 13 Replies
View Related
Dec 5, 2000
I ran a DBCC Showcontig (tableid) and it tell me that one of my tables is only putting 0.19% data on each page. How can I control this for more data on a page. It tells me that I have 8078.8 bytes free per page.
View 1 Replies
View Related
Apr 22, 2004
I ran an INDEXDEFRAG against the tables in my DB, & yet the results in DBCC SHOWCONTIG are the same before and after for some of the tables that it defragmented. Why?
In the DBCC SHOWCONTIG before defragging, this was a sample table:
Table1:
- Pages Scanned................................: 2279
- Extents Scanned..............................: 294
- Extent Switches..............................: 293
- Avg. Pages per Extent........................: 7.8
- Scan Density [Best Count:Actual Count].......: 96.94% [285:294]
- Extent Scan Fragmentation ...................: 4.42%
- Avg. Bytes Free per Page.....................: 358.9
- Avg. Page Density (full).....................: 95.57%
I ran the script from the BOL entry for 'DBCC SHOWCONTIG' to defragment all indexes in a database , under letter E, with @maxfrag = 5%. It returned the following results (there are 5 nonclustered indexes):
Executing DBCC INDEXDEFRAG (0, table1,
3) - fragmentation currently 13%
Pages Scanned Pages Moved Pages Removed
------------- ----------- -------------
1146 1088 0
(1 row(s) affected)
(1 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Executing DBCC INDEXDEFRAG (0, table1,
4) - fragmentation currently 13%
Pages Scanned Pages Moved Pages Removed
------------- ----------- -------------
509 509 0
(1 row(s) affected)
(1 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Executing DBCC INDEXDEFRAG (0, table1,
5) - fragmentation currently 13%
Pages Scanned Pages Moved Pages Removed
------------- ----------- -------------
509 509 0
(1 row(s) affected)
(1 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Executing DBCC INDEXDEFRAG (0, table1,
6) - fragmentation currently 13%
Pages Scanned Pages Moved Pages Removed
------------- ----------- -------------
720 685 0
Running DBCC SHOWCONTIG again, the results were exactly the same for Table1.
Thanks for any advice to interpret this information.
View 4 Replies
View Related
Jan 8, 2007
Hi friends,
please guide me as to why will we use these 2 commands.
their basic use and what information do we have to check from the results of these commands. and what changes do we have to make?
how can we improve performance by using these commands?
please also tell me if these commands are to be used in conjunction with other commands.
Regards,
Jheds
View 3 Replies
View Related
May 10, 2007
Does anyone know if there is a simple way to get the results of a DBCCINPUTBUFFER() request into a table? I have a process for monitoringactivity that will give me the results of sp_who2 into a temp table,and want to scroll through the active connections and get the inputbuffers into another table for review:Insert into #TmpWhoexec sp_who2 'active'Something like that with the dbcc command.I am using SQL 2000 SP4.Thanks,Tom
View 3 Replies
View Related
May 28, 2015
I have this table:
with actividades_secundarias as (
select a.*, r.Antigo, r.Novo, rn = row_number()
over (PARTITION BY a.nif_antigo, r.novo ORDER BY a.nif_antigo)
from ACT_SECUNDARIAS a inner join
((select
[Code] ....
I want to make a delete statement like this:
select * into #table1 from actvidades_secundarias where rn>1
Delete from act_secundarias where act_secundarias.nif_antigo = #table1.nif_antigo and act_secundarias.cod_cae = #table1.cod_cae
But it seems that I cant delete like this.
View 5 Replies
View Related
Apr 6, 2008
Using SQL Server 2005 Express:
I'd like to know how to do a SELECT Query using the following tables:
Miles Table:
Date/Car/Miles/MilesTypeID
===============
(some date)/Ford/20/1
(some date)Ford/20/2
(some date)Chevy/30/1
(some date)Toyota/50/3
(some date)Ford/30/3
Miles Type Table
MilesTypeID/MilesType
=================
1/City
2/Highway
3/Off-Road
I'd like the results to be like this:
Date/Car/City Miles/Highway Miles/Off-Road Miles
=====================================
(date)-Ford-20-0-0
(date)-Chevy-0-20-0
(date)-Ford-20-0-0
(date)-Toyota-0-0-50
(date)-Ford-0-0-30
Anyone? Thanks in advance!
View 3 Replies
View Related
Nov 30, 2007
I have a results table that was created from many different sources in SSIS. I have done calculations and created derived columns in it. I am trying to figure out if there is a way to remove duplicate rows from this table without first writing it to a temp sql table and then parsing through it to remove them.
each row has a like key in a column - I would like to remove like rows keeping specific columns in the resulting row based on the data in this key field.
Ideas?
Thanks,
Ad.
View 7 Replies
View Related
Oct 4, 2005
Hello Ryan.
I run DBCC PAGE (dbname, 1, 136, 3) with trace 3604 on:
Server: Msg 8968, Level 16, State 1, Line 1
Table error: DBCC PAGE page (1:136) (object ID 0, index ID 0) is out of the range of this database.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
View 10 Replies
View Related
May 30, 2001
One of our databases seems to be looking dodgy as some scheduled jobs are failing, but DBCC CHECKDB is no use since it has been running for over 1/2 hour without giving me any results, just the spinning globe.
How do I find out what is wrong without resorting to backups?
View 3 Replies
View Related
Jul 28, 2004
I have lost the reference but I read somewhere that when running DBCC DBREINDEX against a clustered index, all the secondary indexes on the table are automatically re-indexed as well. I did a test of this on a small table and it seemed to confirm this. However, now I've put this into practice, I am finding that it doesn't seem to work this way. I noticed that having run DBCC DBREINDEX against a table's clustered index (DBCC DBREINDEX ('tablename', 'clusteredIndexName', fill_factor)), the secondary indexes were not automatically re-indexed - as born out by the fact that they remained badly fragmented.
First of all, do the dba's who read this beleive it is correct that DBCC DBREINDEX run against a clustered index will automatically rebuild the secondary indexes too? If so, why wouldn't it work in all cases?
Clive
View 12 Replies
View Related
Aug 9, 2004
Normally, after I use DBCC DBREINDEX, I can be sure that Scan Density on a clustered or non-clustered index is very good - eg. 99% or 100%. However, I have one database where there are a number of indexes that are not showing any improvement in Scan Density after running DBCC DBREINDEX. In on case, a clustered index, I run it on two days in succession and Scan Density actually go worse! Can anyone give me a reason for this? Can anyone suggest how to fix it?
Clive
View 1 Replies
View Related
Feb 28, 2008
So, basically I'm trying to do an insert into exec(dbcc shrinkdatabase)
Code Snippet
DECLARE
@SQL VARCHAR(1024)
,@DBName VARCHAR(512)
SET @DBName= 'admin'
IF OBJECT_ID('tempdb.dbo.#ShrinkDB') IS NOT NULL
DROP TABLE #ShrinkDB
CREATE TABLE #ShrinkDB
(
DbId INT
,FileID INT
,CurrentSize BIGINT
,MinimumSize BIGINT
,UsedPages BIGINT
,EstimatedPages BIGINT
)
SET @SQL=
'
INSERT INTO #ShrinkDB
(DbId,FileId,CurrentSize,MinimumSize,Usedpages,EstimatedPages)
EXEC(''DBCC SHRINKDATABASE(' + @DBName + ')'')
'
EXEC(@SQL)
SELECT * FROM #ShrinkDB
and receive the following:
Cannot perform a shrinkdatabase operation inside a user transaction. Terminate the transaction and reissue the statement.
I've tried adding a begin tran and commit tran around it, doesn't help ...
Is there any way around this? Is there any other way to capture the output of a shrink database from a procedure perspective?
Thanks
View 4 Replies
View Related
May 23, 2008
Guys,
I need to send a group of people a list of specific processes running on the server, one of the requirements is to send them what's actually being ran on the machine. I have the information I was on the sysprocess tabke and the results of the DBCC Inputbuffer. Is there a way to link both result sets?
This is the criteria of the processes that neeed to be sent out to my users:
SELECT *
FROM master.dbo.sysprocesses p
WHERE last_batch < DATEADD(mi, -5, GETDATE())
AND dbo.fncGetNumLocks(p.spid, DB_ID('EngDataMart')) > 1
GROUP BY p.spid, p.loginame, p.hostname, dbo.fncGetNumLocks(p.spid, db_id('DBName'))
ORDER BY p.spid
Thanks,
View 1 Replies
View Related
May 26, 2015
I can find many examples of loading DBCC results into tables. They all begin with a create table statement defining the results. My question is , other than trial and error, is there a way to determine what data types will be returned. Sure you can say that first element looks like an integer, but is it really a bigint, and that text string can be varchar(max) but will char(2) work.
I'm not looking for an answer for a specific DBCC function, but rather a generic way I can determine the characteristics of any DBCC result set.
I tried
SELECT *
INTO #tmp
FROM OPENROWSET('SQLOLEDB',
'Server=ray;Trusted_Connection=Yes;Database=Ed_sandbox',
'Set FmtOnly OFF; DBCC loginfo WITH tableresults ')
but I got back
Msg 11527, Level 16, State 1, Procedure sp_describe_first_result_set, Line 1
The metadata could not be determined because statement 'DBCC loginfo WITH tableresults' does not support metadata discovery.
View 1 Replies
View Related
