MS SQL Server :: Extent Scan Fragmentation Increases After DBReindex

High Extent Scan Fragmentation

Oct 25, 2002

Can anyone let me now why the extent scan fragmentation is very high.
I do have a clustered index on this table .
The fill factor is 0 and this table has high inserts as it is used to maintain history.
Rebuilding the Indexes did not help.

DBCC SHOWCONTIG scanning 'ACCOUNTS' table...
Table: 'ACCOUNTS'(1061578820); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned................................: 728157
- Extents Scanned..............................: 91759
- Extent Switches..............................: 93305
- Avg. Pages per Extent........................: 7.9
- Scan Density .......: 97.55% [91020:93306]
- Logical Scan Fragmentation ..................: 0.33%
- Extent Scan Fragmentation ...................: 99.99%
- Avg. Bytes Free per Page.....................: 76.6
- Avg. Page Density (full).....................: 99.05%
DBCC execution completed. If DBCC printed error messages, contact your system administrator

Thanks in advance,
Shades[B]Extent Scan Fragmentation

View 2 Replies View Related

Extent Fragmentation High Just After Reindexing

Jul 18, 2007

Hello. When reviewing the DBCC SHOWCONTIG immediately after reindexing all indexes on a database, I see the ExtentFragmentation has values like 50 to 70%... These are SQL 2005 tables with clustered PK's, no large varchars/blobs, and at least 100 pages in the index... The numbers related to PAGE fragmentation are ok after reindexing, but not the EXTENT fragmentation numbers.

I noticed the drive is in need of being defragged at the disk level. Is that a reason why reindexing doesn't fix the Extent frag numbers?? ANy other ideas on this? I can try defragging the DISK over the weekend, bringing the database offline then, but any other thougths on why the Extents show these high %'s? Is there any command to reset them and maybe that isn't happening? Like must I do update usage to get valid Extent frag #'s??

If there were MANY autogrows on the files, is that a different level of fragmentation? and how could all those small pieces of files be pulled back together? Thanks, Bruce

View 7 Replies View Related

Extent Fragmentation Still High After Index Drop/create

Jul 20, 2005

hello everyone,we dropped the clustered & nonclustered indeces on a table, thenrebuilt them. logical fragmentation is near zero, but extentfragmentation is about 40%. how can this be if the indeces are brandnew?

View 2 Replies View Related

Logical Scan Fragmentation

Aug 16, 2007

I have a table with 50% Logical Scan Fragmentation. [ according to Dbcc Showcontig (myTable) ]
Why after running DBCC INDEXDEFRAG (myDB,myTable) does it still sit at 50%.
Why isn't it lower?

View 7 Replies View Related

What Is The Difference Between Clustered Index Scan And Table Scan

Jan 29, 2006

How can I improve performance of my search if I am looking in a table with more than ten million rows with a "like query"?

Does putting an index mean only telling the computer to start in a particular order?

if I index all the coloums does my search get faster ?

how can I decide on an indexing strategy?

View 7 Replies View Related

Extent Allocation/Free Space - SQL2000??

May 6, 2004

I've faced with this problem in my DB:

There are some tables that suffer inserts and deletes daily. These tables have 3 nonclustered indexes including the pk.

I perceived that the space used for these tables are growing day after day even ocurring daily deletes.

These inserts and deletes follow the keys of the pk in ascent order.

I ran DBCC SHOWCONTIG on the tables and got results like this:

- Avg. Bytes Free per Page.....................: 7996.3

When I transform the pk to clustered this problem doesn't happen.

You realized the consequence of this: the users complains because the DB is without space, but it's not true!

Anybody could help me to understand why the extents are not being deallocated?

Thank's for help!

View 8 Replies View Related

To What Extent Can We Believe The Pattens Discovered By Mining Models?

Jul 16, 2007

Hi, all,

Again I am confused about the extent of being convinced by the mining models. We can validate the models with accuracy chart, but then to what extent can we trust that? (you never get 100% correct miming models) If we dont trust the results of the models, then the patterns the models discover are meaningless.

Just need some advices from you experts here to help me convince people on what I got from my mining models.

I am looking forward to hearing from you shortly and thank you very much.

With best regards,

Yours sincerely,

View 3 Replies View Related

Index Seek, Index Scan, Table Scan

Oct 4, 2007

Hey,

what is the difference between Table Scan und Index Scan?

I find no difitions in the internet

Finchen

View 5 Replies View Related

What Is Table Scan, Index Scan And Index Seek??

Sep 21, 2007

Hi,
I want to know wht is a

TABLE SCAN
INDEX SCAN
INDEX SEEKand When they are used, Wht is the difference between all these.????

View 5 Replies View Related

Log File Size Increases Over And Over

Oct 4, 2004

Hi everybody,

I have a database in production server with 3,5 GB of data file size and 10 GB log file size. This is very strange isn't it?
The features of this database are:

SQL Server 2000
Recovery Model = Full
Auto Update Statistics = Yes
Torn page detection = Yes
Auto create statistics = Yes
Full database backup taken once daily.
No log backup is taken.

So, I would like to apply some statregy to avoid the log file increase out of control. Can you give me your suggestions???
My free disk space is very low.

Thank you all,

View 5 Replies View Related

IF NOT EXISTS (... - EXISTS TABLE : Nested Iteration. Table Scan.Forward Scan.

Sep 20, 2006

Hi,

This is on Sybase but I'm guessing that the same situation would happen on SQL Server. (Please confirm if you know).

I'm looking at these new databases and I'm seeing code similar to this all over the place:

if not exists (select 1 from dbo.t1 where f1 = @p1)
begin
select @errno = @errno | 1
end

There's a unique clustered in dex on t1.f1.

The execution plan shows this for this statement:

FROM TABLE
dbo.t1
EXISTS TABLE : nested iteration.
Table Scan.
Forward scan.
Positioning at start of table.

It's not using my index!!!!!

It seems to be the case with EXISTS statements. Can anybody confirm?

I also hinted to use the index but it still didn't use it.

If the existence check really doesn't use the index, what's a good code alternative to this check?

I did this and it's working great but I wonder if there's a better alternative. I don't really like doing the SET ROWCOUNT 1 and then SET ROWCOUNT 0 thing. SELECT TOP 1 won't work on Sybase, :-(.

SET ROWCOUNT 1
SELECT @cnt = (SELECT 1 FROM dbo.t1 (index ix01)
WHERE f1 = @p1
)
SET ROWCOUNT 0

Appreciate your help.

View 3 Replies View Related

Transaction Log Size Increases Enormously(log Shipping)

Feb 4, 2008

Hi,

Im in the process of setting up logshipping on sqlserver 2005 enterprise edition.
My scenario is like this:
My Avg size of my tlog is 500MB and im planning to set the log shipping at 30mins interval(ie backup job schedule,Copy,restore job schedule).But at some part of the day the Tlog suddenly increases up to 1.5GB - 2 GB .So i wanted to know, wht if that 1.5GB-2GB tlog file is unable to get backed up,copy and restore at 30mins interval?.How to deal with this kind of issues where the size of tlogs are increased suddenly.I cannot do it at15mins interval due to some network restrictions at my office.

i have One more doubt about the setting on Logshipping screen:
Now let us suppose my settting on log shipping screen 'Alert if no restore occurs within' is set to '180mins', then does this setting mean that the restore job will keep on looking for the copied file in the folder on secondary for next 90mins and if its not able to find any, it will generate an alert after 90mins ??? or it will generate an error if its nt able to find any copied file after the first restore job execution.???
in the same way,

Thnx in advance for any help.

Regards
Arvind L

View 1 Replies View Related

DB Engine :: Simple Recovery Mode - Log Size Increases

Jun 2, 2015

Is this Possible, If database is in Simple recovery Mode and the ldf size gets increased?? .

mdf size : 159 GB (171,383,717,888 bytes)
ldf size : 6.46 GB (6,945,505,280 bytes).

My question is if the recovery model is in Simple Mode then why the log gets generated high.

dbcc sqlperf(logspace) --output
DATABASE Logsize(MB) Log space used(%) status
mam 6623.742
0.4305579
0

Is there any issue or it is Normal.

View 9 Replies View Related

DB Engine :: Page Life Expectancy Increases Dramatically From A Minute To Another

May 22, 2015

I measure PLE on my server and insert them every minute into a table. Now, when I look into the table I just dont know how to interpret the following data. I dont understand how is that possible. It's an sql server bug? or? How to interpret that data?

View 10 Replies View Related

Creating Index Time Increases Poroportional To The Amount Of Rows???

Apr 28, 2008

Hello!

I have a problem. I want to know if the time which is needed for creating an index increases proportional to the amount of rows. example: if creating an index on a table which 10.000 rows takes 15 seconds. does creating an index on a table with 20.000 rows take 30 seconds , 40.000 rows 60 seconds and so on...
or does it take longer like 10.000 rows 15 second, 20.000 rows 40 seconds, 40.000 rows 80 seconds.

thx for your help!!

Filipe

View 4 Replies View Related

SQL Server Admin 2014 :: Why Number Of Reads Increases During Insert Test

Jun 2, 2014

I am writing a performance baseline test.

The first test writes 5000000 rows in one table. I realise this is not representative OLTP behaviour, but it worked me to start interpreting performance counters and to test several setups to be discussed with our server, storage and network administrators. This way we have been able to compare the results of different hard disks, Lun vs vmdk, 1GB vs 10GB network, AMD vs Intel, etc. This way I can also compare several SQL setups (recovery model, max memory config, ...)

The screenshot shows the results of 2 runs on the same server : Win2012R2, SQL2014, 16GB RAM.

In test 1 min/max server memory was set to 9215MB/10751MB
In test 2 min/max server memory was set to 13311MB/14847MB

The script assures the number of bytes inserted in the nvarchar columns is always the same.

This explains why the number of pages and the number of MB in the table are the same at the end of the 2 tests (column 5 and 6)

Since ca 13GB has to be written, the results of test 1 show the lead time is increasing once more than 10GB has been inserted (column 8 and 9) In addition you can see at that moment

- buffer cache hit ratio is decreasing
- page life expectance becomes "terrible"
- free list stall/sec increases
- lazy writes/sec increases
- readlatency increases (write latency does not)

In test 2 (id 3 in column 1 in the screenshot) those counters are not really influenced (since the 5000000 rows can all be stored in memory).

Now what I do not understand is :

Why the number of pages read (instance level) as well as the number of bytes read and the number of reads (databaselevel) is increasing extremely during run 1.

I expected to see serious impact on write behavior, since SQL server is forced to start flushing dirty pages once memory is filled. Well actually you can see here the number of writes (not the the number of bytes written) starts to increase faster in test 1 after 4000000 rows, but there's no real impact on write latency.

Finally I want to notice

- I'm the only user on this machine
- the table has a clustered index on a identity column
- there are no foreign key constraints
- inserts are executed using a loop, not one big transaction
- to monitor progress and behaviour/impact, each 10.000 loops the counters are stored using dmv queries

So I wonder why SQL Server starts to execute so many reads in test 1.

View 4 Replies View Related

Pivot That Increases Columns Dynamically With Column Header As Boundary Date For Each Week

Dec 5, 2007

Hi All,

The current/ Base table would be like below,

Products

level

Date

N1

b

11/5/2007

N2

p

11/6/2007

N3

p

11/7/2007

N4

p

11/14/2007

N5

b

11/15/2007

N6

p

11/23/2007

Expected Result.

<=11/7/2007

<= 11/14/2007

<=11/21/2007

b

1

1

2

p

2

3

4

Total

3

4

6

As you can see, the above table has cumulative data.
1. It calculates the number of Products submitted till a particular date- weekly
2. The date columns should increase dynamically(if the dates in base table increases) each time the query is executed
For ex: the next date would be 11/28/2007
I tried something like, it gives me count of ��b�� level and ��p�� level products by week
declare @date1 as datetime
select @date1 = '6/30/2007'
while (@date1 != (select max(SDate) from dbo.TrendTable))
begin
set @date1 = @date1 + 7
select Level, count(Products)
from
dbo.TrendTable
where SDate < @date1
group by Level
end
what I think is required is a pivot that dynamically adds the columns for increase in date range.
/Pls suggest if any other way of achieving it.
Pls help!!!

Thanks & Regards

View 3 Replies View Related

DBREINDEX - More

Jan 8, 2002

I ran the dbreindex using query analyzer (on the tables mentioned in my previous posting - 1/7/02) and still the scan density remains at 50%.
I do not understand why the dbreindex is not reorging these tables to a
higher scan density.
I used a fill factor of 90%.

View 5 Replies View Related

DBREINDEX

Dec 17, 2001

HI,

I have a database at 30GB, with 'FULL' recovery model. The transaction log backup after a DBREINDEX job on this database is creating a very huge backup file. Any Idea how can I avoid such a huge backup file.

I have tried to change the recovery model to 'Bulk_logged' before DBREINDEX and changed it back to 'FULL'. That sure helped me to avoid the growth of log file. But, that did not help me to reduce the size of the transactional log backup file.

Regards
Chakri

View 1 Replies View Related

Qustion On Dbreindex.

Dec 21, 2001

Is that OK to run dbreindex every 24 hours on heavily modified/inserted tables?Does that harm anyway?Gurus advice needed!
thanks.

View 1 Replies View Related

Dbcc Dbreindex

Jan 7, 2002

We are new to SQL Server and we are currently setting up a job that
does a dbreindex for tables that need it. I got the scripts for this
from swynk. 50 of the tables that needed a dbreindex,
(under 90% scan density) were not impacted by running the dbreindex.
I.e., I ran the dbreindex and it left it at 50% scan density.
The fill factor for these tables is set to 100%.
I tried update stats, updateusage...nothing changes....
I am sure there must be a logical reason as to why the dbreindex
had no impact on these tables. Does any one have any info on this?
Thanks in advance!

View 4 Replies View Related

DBCC DBREINDEX

Mar 21, 2001

I am executing a DBCC DBREINDEX for a table with about
4 million rown in SQL6.5. I has run for about
15 hrs now and is till running.
The index is the PK for the table and it is clustred index.
I ran it on a non clustered index on the same table and it ran for a couple of hrs.
Does any one have any idea how much time it might take for the clustered index?
I heard that this process might take more time and for one of my friends it ran for more than 40 hours.
I don;t have so much if time as th DB has to go for production to night and if not it might effect our businees.
I cancelled DBCC DBREINDEX before by killing the process and the master.DAT file got corrupted.I had to restore everytihng. Does anyone know any work around?
Help appreciated.

Thanks.

View 4 Replies View Related

DBCC DBREINDEX

Jun 11, 2001

Could users use database while running DBCC DBREINDEX for all tables in database?

View 1 Replies View Related

DBCC DBREINDEX ?

Aug 14, 2001

Hi,
I wanted to know is DBCC DBREINDEX is a logged operation?
The reason why I am asking this is, Whenever we create an index or drop an existing it is a logged operation. Is this the same when we do DBCC DBREINDEX? Is it drops existing indexes and creates new indexes. My concern here is about transaction log. If I run a DBCC DBREINDEX on a huge table with lot of indexes, Is it going to fill transaction log?

Any help is appreciated!

Thank you,
Ravi.

View 1 Replies View Related

Dbcc Dbreindex

Nov 7, 2000

About how long should I expect this to run? The Database is 200MB. The table I doing the reindex on has about 800 rows. Is an hour outrageous.. ?

View 1 Replies View Related

Dbcc Dbreindex

Aug 4, 2000

Hello!

SQL Server 7
After database clean up (removing old data)I run dbcc dbreindex.
Is it true I don't need to run UPDATE STATISTICS after DBCC DBREINDEX as since SQL Server 7 runs UPDATE STATISTICS automatically (after dbcc dbreindex).
Thank you.
Anny

View 2 Replies View Related

DBCC DBREINDEX

Mar 21, 2001

I am executing a DBCC DBREINDEX for a table with about
4 million rown in SQL6.5. I has run for about
15 hrs now and is till running.
The index is the PK for the table and it is clustred index.
I ran it on a non clustered index on the same table and it ran for a couple of hrs.
Does any one have any idea how much time it might take for the clustered index?
I heard that this process might take more time and for one of my friends it ran for more than 40 hours.
I don;t have so much if time as th DB has to go for production to night and if not it might effect our businees.
I cancelled DBCC DBREINDEX before by killing the process and the master.DAT file got corrupted.I had to restore everytihng. Does anyone know any work around?
Help appreciated.

Thanks.

View 1 Replies View Related

DBREINDEX:How Does It Work?

Jun 10, 2005

Can anyone shed some light as to how SQL Server performs DBCC DBREINDEX?
Does it use PRIMARY data file space and how much extra data space/log space do we need to have to be able to run this command?

View 2 Replies View Related

Dbcc Dbreindex

Jan 10, 2006

Hi All,

I have a following script:

DECLARE @TableName sysname
DECLARE cur_reindex CURSOR FOR
SELECT table_name
FROM information_schema.tables
WHERE table_type = 'base table'
OPEN cur_reindex
FETCH NEXT FROM cur_reindex INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Rebuilding indexes ' + @TableName + ' table'
DBCC DBREINDEX (@TableName, ' ', 10)
FETCH NEXT FROM cur_reindex INTO @TableName
END
CLOSE cur_reindex
DEALLOCATE cur_reindex
GO

I added as a step in the job. However, when I run it I get an error message:

Msg 2501, Sev 16: Could not find a table or object named 'table_1'. Check sysobjects. [SQLSTATE 42S02]

When I run
SELECT table_name
FROM information_schema.tables
WHERE table_type = 'base table'

table_1 is the first on list. Any idea why I am getting this error message?

View 5 Replies View Related

Please Help!...Hanging DBREINDEX Job

Feb 24, 2007

I have a scheduled job on my SQL Server 2000 that runs a DBREINDEX job on all the tables in my database. For some reason the system just hangs for hours now when it reaches one particular table. Can somebody please help me to correct the problem. Thank you all in advance.

View 2 Replies View Related

DBCC DBREINDEX Does Nothing

May 17, 2004

Has anyone had the problem with DBCC DBREINDEX on a table/index not doing anything.
You just get the usual "DBCC execution completed. If DBCC printed error messages, contact your system administrator." message.
I have tried:
DBCC DBREINDEX ('<table_name>')
DBCC DBREINDEX (<table_name>)
DBCC DBREINDEX ('<table_name>', '')
DBCC DBREINDEX ('<table_name>', '<index_name>')
DBCC DBREINDEX ('<table_name>', '<index_name>', 80)

Maybe its "optimising" and not thinking anything needs updating.

Should I look for an alternative way of rebuilding an index like dropping and recreating?

The beauty with DBCC DBREINDEX ('<table_name>') would have been that I was going to get the list of user tables in the database and call it for each table to simply rebuild all indexes.

View 5 Replies View Related

DB Maintenance -dbreindex

Apr 19, 2007

We are running a maintenance plan that rebuilds the indexes for all the databases. I have ran showcontig after the maintenance plan and am still seeing fragmentation. Here is one of the master tables that is included in on the maintenance job.
sysobjects table:
pages scanned - 5
extents scanned - 3
extents switches - 3
avg. pages per extent - 1.7
scan density - 25%
logical scan fragmentation - 40%
extent fragmentation - 66.67%
avg.bytes free per page - 1360.4
avg. page density 83.19%
According to what I have read this still seems to be fragmented. What can be done to improve this?

View 11 Replies View Related

Extent Scan Fragmentation Increases After DBReindex - Why?