sql delete multiple tables: indexdefrag

Showing posts with label indexdefrag. Show all posts

Monday, March 19, 2012

Defraging database / help

Hi all
I want to defrag the SQL database
yes this is code
DBCC INDEXDEFRAG (Northwind, Orders, CustomersOrders)
but this will defrag a specific Table
but i want defrag entire a specific database
how can i do it
ThanksCreate a view in your database:

create view dbo.vw_DBCC_INDEXDEFRAG as
select cmd = 'dbcc indexdefrag (' + db_name() + ', ' + o.name + ', ' + i.name + ')'
from sysindexes i
inner join sysobjects o on i.id=o.id
where objectproperty(o.id, 'IsMSShipped') = 0
and objectproperty(o.id, 'IsTable') = 1
and indid > 0 and indid < 255 and (i.status & 64)=0

Then, at command prompt type the following:

C:\>BCP <db_name>.dbo.vw_DBCC_INDEXDEFRAG out DBCC_INDEXDEFRAG.SQL -S <your_server> -T -c
C:\>OSQL -i DBCC_INDEXDEFRAG.SQL -o DBCC_INDEXDEFRAG.OUT -S <server_name> -d <your_database> -E -h-1 -n -w 256

...And you're done.

Sunday, March 11, 2012

defrag system tables indexes

does anyone know how could i defrag system tables indexes.. because dbcc
indexdefrag or rebuild can't be used, thksnikolakg wrote:
> does anyone know how could i defrag system tables indexes.. because dbcc
> indexdefrag or rebuild can't be used, thks
You shouldn't have to mess with the system table indexes. They're small
enough that defragging really isn't going to help them, and they're
static enough that they shouldn't fragment much, if at all, anyway.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||i ran dbcc showcontig and the scan density for system tables was too low
12-15%.
Î? Ï?Ï?Î®Ï?Ï?Î·Ï? "Tracy McKibben" ÎÎ³Î³Ï?Î±Ï?Îµ:
> nikolakg wrote:
> > does anyone know how could i defrag system tables indexes.. because dbcc
> > indexdefrag or rebuild can't be used, thks
> You shouldn't have to mess with the system table indexes. They're small
> enough that defragging really isn't going to help them, and they're
> static enough that they shouldn't fragment much, if at all, anyway.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||nikolakg wrote:
> i ran dbcc showcontig and the scan density for system tables was too low
> 12-15%.
>
How many pages are in the indexes you're looking at?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||OBJECT NAME INDEX NAME PAGES ROWS SCAN DENSITY
syscolumns syscolumns 244 12200 12.810
sysdepends ncsysdepends1 56 2027 13.462
syscolumns ncsyscolumns 93 12200 13.483
sysindexes sysindexes 187 1063 13.793
sysdepends sysdepends 57 2027 14.815
syscomments syscomments 926 1505 16.885
sysobjects sysobjects 27 1680 17.391
sysobjects ncsysobjects 14 1680 20.000
sysproperties sysproperties 6 65 20.000
sysobjects ncsysobjects2 5 1680 25.000
this is what i get when i run DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES
(and some more columns)
Î? Ï?Ï?Î®Ï?Ï?Î·Ï? "Tracy McKibben" ÎÎ³Î³Ï?Î±Ï?Îµ:
> nikolakg wrote:
> > i ran dbcc showcontig and the scan density for system tables was too low
> > 12-15%.
> >
> How many pages are in the indexes you're looking at?
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||nikolakg wrote:
> OBJECT NAME INDEX NAME PAGES ROWS SCAN DENSITY
> syscolumns syscolumns 244 12200 12.810
> sysdepends ncsysdepends1 56 2027 13.462
> syscolumns ncsyscolumns 93 12200 13.483
> sysindexes sysindexes 187 1063 13.793
> sysdepends sysdepends 57 2027 14.815
> syscomments syscomments 926 1505 16.885
> sysobjects sysobjects 27 1680 17.391
> sysobjects ncsysobjects 14 1680 20.000
> sysproperties sysproperties 6 65 20.000
> sysobjects ncsysobjects2 5 1680 25.000
>
> this is what i get when i run DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES
> (and some more columns)
>
Less than 1000 pages, don't worry about them.
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||thank you!
Î? Ï?Ï?Î®Ï?Ï?Î·Ï? "Tracy McKibben" ÎÎ³Î³Ï?Î±Ï?Îµ:
> nikolakg wrote:
> > OBJECT NAME INDEX NAME PAGES ROWS SCAN DENSITY
> > syscolumns syscolumns 244 12200 12.810
> > sysdepends ncsysdepends1 56 2027 13.462
> > syscolumns ncsyscolumns 93 12200 13.483
> > sysindexes sysindexes 187 1063 13.793
> > sysdepends sysdepends 57 2027 14.815
> > syscomments syscomments 926 1505 16.885
> > sysobjects sysobjects 27 1680 17.391
> > sysobjects ncsysobjects 14 1680 20.000
> > sysproperties sysproperties 6 65 20.000
> > sysobjects ncsysobjects2 5 1680 25.000
> >
> >
> > this is what i get when i run DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES
> > (and some more columns)
> >
> Less than 1000 pages, don't worry about them.
> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||We have an issue because text data from sysIndexes is sitting right at the
end of our primary datafile and this stops us truncating the end off the
file.
We have done a load of housekeeping and could reclaim a greate deal of disk
space.
We cannot do a shrink/movc data pages due to log shipping across our WAN.
DBCC extentinfo (Production, sysIndexes, -1)
Paul

Defrag Script

Hi all,
I'm writing my own defrag script. I've set it up so under certain conditions
it will run an IndexDefrag and under others a Reindex. I wanted to post the
conditions I've set and see if anyone has any feedback that can improve them.
I have two temporary tables 1 contains the tables and indexes, and one the
ShowContig information.
The IndexDefrag test is:
UPDATE #TableIndexList
SET ReqAction = 'I'
FROM #TableIndexList AS a
INNER JOIN #FragStats AS b
ON a.ObjectID = b.ObjectID
WHERE b.Pages BETWEEN 16 AND 4096
AND ((b.LogicalFragmentation BETWEEN 20 AND 40)
OR b.AveragePageDensity < 75)
The reindex criteria is:
UPDATE #TableIndexList
SET ReqAction = 'R'
FROM #TableIndexList AS a
INNER JOIN #FragStats AS b
ON a.ObjectID = b.ObjectID
WHERE (b.Pages > 16
AND (b.LogicalFragmentation > 40
OR b.AveragePageDensity > 95))
OR (b.Pages > 4096
AND (b.LogicalFragmentation > 20
OR b.AveragePageDensity < 75))
As advised any feedback would be greatly appreciated...Have you compared the before and after results. Sometimes due to the way you
have constructed your table you will find residual fragmentation after
running such scripts will be almost the same.
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"BenUK" <BenUK@.discussions.microsoft.com> wrote in message
news:AD82F542-DD91-4973-B0C1-8DAB523A7EF8@.microsoft.com...
> Hi all,
> I'm writing my own defrag script. I've set it up so under certain
> conditions
> it will run an IndexDefrag and under others a Reindex. I wanted to post
> the
> conditions I've set and see if anyone has any feedback that can improve
> them.
> I have two temporary tables 1 contains the tables and indexes, and one the
> ShowContig information.
> The IndexDefrag test is:
> UPDATE #TableIndexList
> SET ReqAction = 'I'
> FROM #TableIndexList AS a
> INNER JOIN #FragStats AS b
> ON a.ObjectID = b.ObjectID
> WHERE b.Pages BETWEEN 16 AND 4096
> AND ((b.LogicalFragmentation BETWEEN 20 AND 40)
> OR b.AveragePageDensity < 75)
> The reindex criteria is:
> UPDATE #TableIndexList
> SET ReqAction = 'R'
> FROM #TableIndexList AS a
> INNER JOIN #FragStats AS b
> ON a.ObjectID = b.ObjectID
> WHERE (b.Pages > 16
> AND (b.LogicalFragmentation > 40
> OR b.AveragePageDensity > 95))
> OR (b.Pages > 4096
> AND (b.LogicalFragmentation > 20
> OR b.AveragePageDensity < 75))
> As advised any feedback would be greatly appreciated...|||Thats interesting.. ..I'm identifying heaps for manual defrags, I'm excluding
blobs, is it a scenario where the table is busy and the IndexDefrag is
skipping the pages?
"Hilary Cotter" wrote:
> Have you compared the before and after results. Sometimes due to the way you
> have constructed your table you will find residual fragmentation after
> running such scripts will be almost the same.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "BenUK" <BenUK@.discussions.microsoft.com> wrote in message
> news:AD82F542-DD91-4973-B0C1-8DAB523A7EF8@.microsoft.com...
> > Hi all,
> >
> > I'm writing my own defrag script. I've set it up so under certain
> > conditions
> > it will run an IndexDefrag and under others a Reindex. I wanted to post
> > the
> > conditions I've set and see if anyone has any feedback that can improve
> > them.
> > I have two temporary tables 1 contains the tables and indexes, and one the
> > ShowContig information.
> >
> > The IndexDefrag test is:
> > UPDATE #TableIndexList
> > SET ReqAction = 'I'
> > FROM #TableIndexList AS a
> > INNER JOIN #FragStats AS b
> > ON a.ObjectID = b.ObjectID
> > WHERE b.Pages BETWEEN 16 AND 4096
> > AND ((b.LogicalFragmentation BETWEEN 20 AND 40)
> > OR b.AveragePageDensity < 75)
> >
> > The reindex criteria is:
> > UPDATE #TableIndexList
> > SET ReqAction = 'R'
> > FROM #TableIndexList AS a
> > INNER JOIN #FragStats AS b
> > ON a.ObjectID = b.ObjectID
> > WHERE (b.Pages > 16
> > AND (b.LogicalFragmentation > 40
> > OR b.AveragePageDensity > 95))
> > OR (b.Pages > 4096
> > AND (b.LogicalFragmentation > 20
> > OR b.AveragePageDensity < 75))
> >
> > As advised any feedback would be greatly appreciated...
>
>|||or maybe when there's not enough disk space to run DBCC Reindex on a large
table
"Hilary Cotter" wrote:
> Have you compared the before and after results. Sometimes due to the way you
> have constructed your table you will find residual fragmentation after
> running such scripts will be almost the same.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "BenUK" <BenUK@.discussions.microsoft.com> wrote in message
> news:AD82F542-DD91-4973-B0C1-8DAB523A7EF8@.microsoft.com...
> > Hi all,
> >
> > I'm writing my own defrag script. I've set it up so under certain
> > conditions
> > it will run an IndexDefrag and under others a Reindex. I wanted to post
> > the
> > conditions I've set and see if anyone has any feedback that can improve
> > them.
> > I have two temporary tables 1 contains the tables and indexes, and one the
> > ShowContig information.
> >
> > The IndexDefrag test is:
> > UPDATE #TableIndexList
> > SET ReqAction = 'I'
> > FROM #TableIndexList AS a
> > INNER JOIN #FragStats AS b
> > ON a.ObjectID = b.ObjectID
> > WHERE b.Pages BETWEEN 16 AND 4096
> > AND ((b.LogicalFragmentation BETWEEN 20 AND 40)
> > OR b.AveragePageDensity < 75)
> >
> > The reindex criteria is:
> > UPDATE #TableIndexList
> > SET ReqAction = 'R'
> > FROM #TableIndexList AS a
> > INNER JOIN #FragStats AS b
> > ON a.ObjectID = b.ObjectID
> > WHERE (b.Pages > 16
> > AND (b.LogicalFragmentation > 40
> > OR b.AveragePageDensity > 95))
> > OR (b.Pages > 4096
> > AND (b.LogicalFragmentation > 20
> > OR b.AveragePageDensity < 75))
> >
> > As advised any feedback would be greatly appreciated...
>
>

Monday, March 19, 2012

Defraging database / help

Sunday, March 11, 2012

defrag system tables indexes

Defrag Script

Defrag Script

sql delete multiple tables

Blog Archive

About Me