define Hierarchy: defrag

Showing posts with label defrag. Show all posts

Sunday, February 19, 2012

How To Defrag SQL Server 2000

Hi everyone, I am fairly new with SQL Server and need a little bit of help in regards to boosting my SQL servers performance. I have been advised that defraging SQL Server will definitely help solve my issue however I have never performed this function before. Can someone please help guide me through the necessary steps to execute this task. Thanks in advance.I have been advised that defraging SQL Server will definitely help solve my issue however I have never performed this function before.
Only one setting cannot solve your problem, you have to consider lots of things like server memory settings, disk space, indexes, query optimizing etc.

You can use database maintenance wizard from Enterprise Manager-> Tools-> Database maintenance planner.

You can fragment your indexes for better performance, syntax is given below.

DBCC INDEXDEFRAG
( { database_name | database_id | 0 }
, { table_name | table_id | 'view_name' | view_id }
, { index_name | index_id }
) [ WITH NO_INFOMSGS ]|||Only one setting cannot solve your problem, you have to consider lots of things like server memory settings, disk space, indexes, query optimizing etc.

You can use database maintenance wizard from Enterprise Manager-> Tools-> Database maintenance planner.

You can fragment your indexes for better performance, syntax is given below.

DBCC INDEXDEFRAG
( { database_name | database_id | 0 }
, { table_name | table_id | 'view_name' | view_id }
, { index_name | index_id }
) [ WITH NO_INFOMSGS ]
I am curious; is there any value in doing a backup/restore?

I have a daily scheduled run of Executive Software's Diskkeeper on all the servers. That keeps the files defragged on the file-system level, but of course doesn't reorder anything within the database.

As I understand it, the concept of Defragmenetation offers an optimization of physical aspects of the disk drive (rotations, head movements) and the software activities of piecing together the fragments. It follows that having all the bits of an index in order would have a similar effect (as you described above).

I guess in a database there's also a matter of eliminating all the holes left by prior deletes and of spreading indexes out more intelligently.

So then; I'm displaying a complete ignorance of "database layer fragmentation". Am I missing a lot of fundamentals in my thinking?

Question: Would it be a benifit to backup-then-restore a database?|||Backing up and restoring a database has no effect on fragmentation. Database fragmentation that is, the DBA's nerves will become highly fragmented if this sort of thing is implemented. In Oracle, you can export and import tables to remove fragmentation, which may be what you are thinking of. In SQL Server, a backup collects all pages that have data on them, and stashes them away. On a restore, the data pages are simply rewritten in place. without any moving of data around the pages.

Database fragmentation happens mainly with deletes, sometimes with updates, and somewhat less frequently with inserts (depending on your indexes).

Suppose you have a data page that originally has 20 entries (rows) in it. When you read in that page, you get 20 rows in memory. Suppose further that 19 of these rows are deleted. Now when you read in the same 8KB page, you only get 1 row of data. The space taken up by the rows that were there is not reclaimed automatically, and depending on insert/update activity and clustered index layout may not ever be reclaimed unless you rebuild the indexes. Rebuilding the indexes has the effect of re-arranging, or regenerating the entries packed closer together making read operations more efficient.

Here is a link to a decent paper about it. Note, users tend to not notice the difference, until tables get above 10,000 pages or so.
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx|||I am curious; is there any value in doing a backup/restore?

yes, there is some value|||Check this, a single web page consist of various subjects for SQL Server Maintenance.

http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/default.mspx

How to defrag an SQL 7 AND 2000 server?

Can someone tell me how to defrag. an MSSQL 7 AND MSSQL 2000 server. We
have both ver. 7 and 2000 (running on two different systems). I am not a
DBA but I really need to get this done tonight (2/24/2006) while we take our
systems down. I will also want to defrag the Windows 2000 and Windows 2003
NTSF file system volumes. Thanks for any and all input.
I will need the commands and where to run the commands (i.e. command prompt,
inside Enterprise Manager or inside Query Analyzer). I don't know anything
about SQL so go easy on me.
ClaytonHere is a quick but effective way to reindex all the tables in a specific db
which will defrag the tables and indexes. If you want to defrag the OS
files you would simply use one of the tools designed for that such as
DiskKeeper, Norton etc. You should make sure you have a good backup before
you start and turn off sql server before you do the OS defrag for best
results.
--
Andrew J. Kelly SQL MVP
"Clayton Sutton" <none@.none.com> wrote in message
news:OAnWhaVOGHA.3576@.TK2MSFTNGP15.phx.gbl...
> Can someone tell me how to defrag. an MSSQL 7 AND MSSQL 2000 server. We
> have both ver. 7 and 2000 (running on two different systems). I am not a
> DBA but I really need to get this done tonight (2/24/2006) while we take
> our systems down. I will also want to defrag the Windows 2000 and Windows
> 2003 NTSF file system volumes. Thanks for any and all input.
> I will need the commands and where to run the commands (i.e. command
> prompt, inside Enterprise Manager or inside Query Analyzer). I don't know
> anything about SQL so go easy on me.
>
> Clayton
>|||Hey Andrew,
Thanks for the reply. See inline comments.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uAxOe2WOGHA.1312@.TK2MSFTNGP09.phx.gbl...
> Here is a quick but effective way to reindex all the tables in a specific
> db which will defrag the tables and indexes.
What? How? Did you forget to give me the info?
> If you want to defrag the OS files you would simply use one of the tools
> designed for that such as DiskKeeper, Norton etc.
I will just be using Windows built-in defrag utility
> You should make sure you have a good backup before you start and turn off
> sql server before you do the OS defrag for best results.
I will stop all SQL services first.
But what are the commands to defrag ALL of the DBs on the server? Are you
saying that if a defrag the NTFS volume that that will defrag ALL the DBs
too? Are there not SQL commands that I need to run to defrag ALL of the
DBs?
Clayton
> "Clayton Sutton" <none@.none.com> wrote in message
> news:OAnWhaVOGHA.3576@.TK2MSFTNGP15.phx.gbl...
>> Can someone tell me how to defrag. an MSSQL 7 AND MSSQL 2000 server. We
>> have both ver. 7 and 2000 (running on two different systems). I am not a
>> DBA but I really need to get this done tonight (2/24/2006) while we take
>> our systems down. I will also want to defrag the Windows 2000 and
>> Windows 2003 NTSF file system volumes. Thanks for any and all input.
>> I will need the commands and where to run the commands (i.e. command
>> prompt, inside Enterprise Manager or inside Query Analyzer). I don't
>> know anything about SQL so go easy on me.
>>
>> Clayton
>|||Sorry I forgot to do the paste, see below for the code:
> But what are the commands to defrag ALL of the DBs on the server? Are you
> saying that if a defrag the NTFS volume that that will defrag ALL the DBs
> too? Are there not SQL commands that I need to run to defrag ALL of the
> DBs?
Defragging at the OS level does nothing towards defragging the tables and
indexes. You would have to run the above script on each database.
--
SET NOCOUNT ON
DECLARE @.TableName VARCHAR(100)
DECLARE curTables CURSOR STATIC LOCAL
FOR
SELECT Table_Name
FROM Information_Schema.Tables
WHERE Table_Type = 'BASE TABLE'
OPEN curTables
FETCH NEXT FROM curTables INTO @.TableName
SET @.TableName = RTRIM(@.TableName)
WHILE @.@.FETCH_STATUS = 0
BEGIN
SELECT 'Reindexing ' + @.TableName
DBCC DBREINDEX (@.TableName)
FETCH NEXT FROM curTables INTO @.TableName
END
CLOSE curTables
DEALLOCATE curTables
Andrew J. Kelly SQL MVP
"Clayton Sutton" <none@.none.com> wrote in message
news:u9FgfOYOGHA.1032@.TK2MSFTNGP11.phx.gbl...
> Hey Andrew,
> Thanks for the reply. See inline comments.
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uAxOe2WOGHA.1312@.TK2MSFTNGP09.phx.gbl...
>> Here is a quick but effective way to reindex all the tables in a specific
>> db which will defrag the tables and indexes.
> What? How? Did you forget to give me the info?
>> If you want to defrag the OS files you would simply use one of the tools
>> designed for that such as DiskKeeper, Norton etc.
> I will just be using Windows built-in defrag utility
>> You should make sure you have a good backup before you start and turn off
>> sql server before you do the OS defrag for best results.
> I will stop all SQL services first.
> But what are the commands to defrag ALL of the DBs on the server? Are you
> saying that if a defrag the NTFS volume that that will defrag ALL the DBs
> too? Are there not SQL commands that I need to run to defrag ALL of the
> DBs?
>
> Clayton
>
>> "Clayton Sutton" <none@.none.com> wrote in message
>> news:OAnWhaVOGHA.3576@.TK2MSFTNGP15.phx.gbl...
>> Can someone tell me how to defrag. an MSSQL 7 AND MSSQL 2000 server. We
>> have both ver. 7 and 2000 (running on two different systems). I am not
>> a DBA but I really need to get this done tonight (2/24/2006) while we
>> take our systems down. I will also want to defrag the Windows 2000 and
>> Windows 2003 NTSF file system volumes. Thanks for any and all input.
>> I will need the commands and where to run the commands (i.e. command
>> prompt, inside Enterprise Manager or inside Query Analyzer). I don't
>> know anything about SQL so go easy on me.
>>
>> Clayton
>>
>|||Hey Andrew,
Getting ready to go into work to get started (9:30pm CST). Just one more
question. How do I run the script? In a DOS Command Prompt? In a "Text"
file and call it "SomeFile.vbs"? Do I copy ad paste it into an MSSQL GUI
interface somewhere? Thanks for your help.
Clayton
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OBxAvZZOGHA.1288@.TK2MSFTNGP09.phx.gbl...
> Sorry I forgot to do the paste, see below for the code:
>> But what are the commands to defrag ALL of the DBs on the server? Are
>> you saying that if a defrag the NTFS volume that that will defrag ALL the
>> DBs too? Are there not SQL commands that I need to run to defrag ALL of
>> the DBs?
> Defragging at the OS level does nothing towards defragging the tables and
> indexes. You would have to run the above script on each database.
> --
> SET NOCOUNT ON
> DECLARE @.TableName VARCHAR(100)
>
> DECLARE curTables CURSOR STATIC LOCAL
> FOR
> SELECT Table_Name
> FROM Information_Schema.Tables
> WHERE Table_Type = 'BASE TABLE'
> OPEN curTables
> FETCH NEXT FROM curTables INTO @.TableName
> SET @.TableName = RTRIM(@.TableName)
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> SELECT 'Reindexing ' + @.TableName
> DBCC DBREINDEX (@.TableName)
> FETCH NEXT FROM curTables INTO @.TableName
> END
> CLOSE curTables
> DEALLOCATE curTables
>
> --
> Andrew J. Kelly SQL MVP
>
> "Clayton Sutton" <none@.none.com> wrote in message
> news:u9FgfOYOGHA.1032@.TK2MSFTNGP11.phx.gbl...
>> Hey Andrew,
>> Thanks for the reply. See inline comments.
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:uAxOe2WOGHA.1312@.TK2MSFTNGP09.phx.gbl...
>> Here is a quick but effective way to reindex all the tables in a
>> specific db which will defrag the tables and indexes.
>> What? How? Did you forget to give me the info?
>> If you want to defrag the OS files you would simply use one of the tools
>> designed for that such as DiskKeeper, Norton etc.
>> I will just be using Windows built-in defrag utility
>> You should make sure you have a good backup before you start and turn
>> off sql server before you do the OS defrag for best results.
>> I will stop all SQL services first.
>> But what are the commands to defrag ALL of the DBs on the server? Are
>> you saying that if a defrag the NTFS volume that that will defrag ALL the
>> DBs too? Are there not SQL commands that I need to run to defrag ALL of
>> the DBs?
>>
>> Clayton
>>
>> "Clayton Sutton" <none@.none.com> wrote in message
>> news:OAnWhaVOGHA.3576@.TK2MSFTNGP15.phx.gbl...
>> Can someone tell me how to defrag. an MSSQL 7 AND MSSQL 2000 server.
>> We have both ver. 7 and 2000 (running on two different systems). I am
>> not a DBA but I really need to get this done tonight (2/24/2006) while
>> we take our systems down. I will also want to defrag the Windows 2000
>> and Windows 2003 NTSF file system volumes. Thanks for any and all
>> input.
>> I will need the commands and where to run the commands (i.e. command
>> prompt, inside Enterprise Manager or inside Query Analyzer). I don't
>> know anything about SQL so go easy on me.
>>
>> Clayton
>>
>>
>|||The easiest way is to use Query Analyzer and paste the code into the window
and hit the F5 key. Query Analyzer should be found under SQL Servers folder
of the programs menu.
Andrew J. Kelly SQL MVP
"Clayton Sutton" <none@.none.com> wrote in message
news:evR7iwbOGHA.2888@.tk2msftngp13.phx.gbl...
> Hey Andrew,
> Getting ready to go into work to get started (9:30pm CST). Just one more
> question. How do I run the script? In a DOS Command Prompt? In a "Text"
> file and call it "SomeFile.vbs"? Do I copy ad paste it into an MSSQL GUI
> interface somewhere? Thanks for your help.
>
> Clayton
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OBxAvZZOGHA.1288@.TK2MSFTNGP09.phx.gbl...
>> Sorry I forgot to do the paste, see below for the code:
>> But what are the commands to defrag ALL of the DBs on the server? Are
>> you saying that if a defrag the NTFS volume that that will defrag ALL
>> the DBs too? Are there not SQL commands that I need to run to defrag
>> ALL of the DBs?
>> Defragging at the OS level does nothing towards defragging the tables and
>> indexes. You would have to run the above script on each database.
>> --
>> SET NOCOUNT ON
>> DECLARE @.TableName VARCHAR(100)
>>
>> DECLARE curTables CURSOR STATIC LOCAL
>> FOR
>> SELECT Table_Name
>> FROM Information_Schema.Tables
>> WHERE Table_Type = 'BASE TABLE'
>> OPEN curTables
>> FETCH NEXT FROM curTables INTO @.TableName
>> SET @.TableName = RTRIM(@.TableName)
>> WHILE @.@.FETCH_STATUS = 0
>> BEGIN
>> SELECT 'Reindexing ' + @.TableName
>> DBCC DBREINDEX (@.TableName)
>> FETCH NEXT FROM curTables INTO @.TableName
>> END
>> CLOSE curTables
>> DEALLOCATE curTables
>>
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Clayton Sutton" <none@.none.com> wrote in message
>> news:u9FgfOYOGHA.1032@.TK2MSFTNGP11.phx.gbl...
>> Hey Andrew,
>> Thanks for the reply. See inline comments.
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:uAxOe2WOGHA.1312@.TK2MSFTNGP09.phx.gbl...
>> Here is a quick but effective way to reindex all the tables in a
>> specific db which will defrag the tables and indexes.
>> What? How? Did you forget to give me the info?
>> If you want to defrag the OS files you would simply use one of the
>> tools designed for that such as DiskKeeper, Norton etc.
>> I will just be using Windows built-in defrag utility
>> You should make sure you have a good backup before you start and turn
>> off sql server before you do the OS defrag for best results.
>> I will stop all SQL services first.
>> But what are the commands to defrag ALL of the DBs on the server? Are
>> you saying that if a defrag the NTFS volume that that will defrag ALL
>> the DBs too? Are there not SQL commands that I need to run to defrag
>> ALL of the DBs?
>>
>> Clayton
>>
>> "Clayton Sutton" <none@.none.com> wrote in message
>> news:OAnWhaVOGHA.3576@.TK2MSFTNGP15.phx.gbl...
>> Can someone tell me how to defrag. an MSSQL 7 AND MSSQL 2000 server.
>> We have both ver. 7 and 2000 (running on two different systems). I am
>> not a DBA but I really need to get this done tonight (2/24/2006) while
>> we take our systems down. I will also want to defrag the Windows 2000
>> and Windows 2003 NTSF file system volumes. Thanks for any and all
>> input.
>> I will need the commands and where to run the commands (i.e. command
>> prompt, inside Enterprise Manager or inside Query Analyzer). I don't
>> know anything about SQL so go easy on me.
>>
>> Clayton
>>
>>
>>
>|||Cool, thank you VERY much Andrew!
Clayton
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uGXTyDhOGHA.3284@.TK2MSFTNGP14.phx.gbl...
> The easiest way is to use Query Analyzer and paste the code into the
> window and hit the F5 key. Query Analyzer should be found under SQL
> Servers folder of the programs menu.
>
> --
> Andrew J. Kelly SQL MVP
>
> "Clayton Sutton" <none@.none.com> wrote in message
> news:evR7iwbOGHA.2888@.tk2msftngp13.phx.gbl...
>> Hey Andrew,
>> Getting ready to go into work to get started (9:30pm CST). Just one more
>> question. How do I run the script? In a DOS Command Prompt? In a
>> "Text" file and call it "SomeFile.vbs"? Do I copy ad paste it into an
>> MSSQL GUI interface somewhere? Thanks for your help.
>>
>> Clayton
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:OBxAvZZOGHA.1288@.TK2MSFTNGP09.phx.gbl...
>> Sorry I forgot to do the paste, see below for the code:
>> But what are the commands to defrag ALL of the DBs on the server? Are
>> you saying that if a defrag the NTFS volume that that will defrag ALL
>> the DBs too? Are there not SQL commands that I need to run to defrag
>> ALL of the DBs?
>> Defragging at the OS level does nothing towards defragging the tables
>> and indexes. You would have to run the above script on each database.
>> --
>> SET NOCOUNT ON
>> DECLARE @.TableName VARCHAR(100)
>>
>> DECLARE curTables CURSOR STATIC LOCAL
>> FOR
>> SELECT Table_Name
>> FROM Information_Schema.Tables
>> WHERE Table_Type = 'BASE TABLE'
>> OPEN curTables
>> FETCH NEXT FROM curTables INTO @.TableName
>> SET @.TableName = RTRIM(@.TableName)
>> WHILE @.@.FETCH_STATUS = 0
>> BEGIN
>> SELECT 'Reindexing ' + @.TableName
>> DBCC DBREINDEX (@.TableName)
>> FETCH NEXT FROM curTables INTO @.TableName
>> END
>> CLOSE curTables
>> DEALLOCATE curTables
>>
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Clayton Sutton" <none@.none.com> wrote in message
>> news:u9FgfOYOGHA.1032@.TK2MSFTNGP11.phx.gbl...
>> Hey Andrew,
>> Thanks for the reply. See inline comments.
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:uAxOe2WOGHA.1312@.TK2MSFTNGP09.phx.gbl...
>> Here is a quick but effective way to reindex all the tables in a
>> specific db which will defrag the tables and indexes.
>> What? How? Did you forget to give me the info?
>> If you want to defrag the OS files you would simply use one of the
>> tools designed for that such as DiskKeeper, Norton etc.
>> I will just be using Windows built-in defrag utility
>> You should make sure you have a good backup before you start and turn
>> off sql server before you do the OS defrag for best results.
>> I will stop all SQL services first.
>> But what are the commands to defrag ALL of the DBs on the server? Are
>> you saying that if a defrag the NTFS volume that that will defrag ALL
>> the DBs too? Are there not SQL commands that I need to run to defrag
>> ALL of the DBs?
>>
>> Clayton
>>
>> "Clayton Sutton" <none@.none.com> wrote in message
>> news:OAnWhaVOGHA.3576@.TK2MSFTNGP15.phx.gbl...
>> Can someone tell me how to defrag. an MSSQL 7 AND MSSQL 2000 server.
>> We have both ver. 7 and 2000 (running on two different systems). I
>> am not a DBA but I really need to get this done tonight (2/24/2006)
>> while we take our systems down. I will also want to defrag the
>> Windows 2000 and Windows 2003 NTSF file system volumes. Thanks for
>> any and all input.
>> I will need the commands and where to run the commands (i.e. command
>> prompt, inside Enterprise Manager or inside Query Analyzer). I don't
>> know anything about SQL so go easy on me.
>>
>> Clayton
>>
>>
>>
>>
>

How to defrag an SQL 7 AND 2000 server?

How to defrag a table?

To all gurus,
Thanx for the info concerning the dbcc statements. The only remaining
question is there a command or code that is known to be used to defrag a
table. It would be very good if this would be usable on the fly (ad-hoc)
Thanx
BillO
*** Sent via Developersdex http://www.examnotes.net ***"Bill Orova" <nospam@.devdex.com> wrote in message
news:e3W#oPuZFHA.3876@.TK2MSFTNGP12.phx.gbl...
> To all gurus,
> Thanx for the info concerning the dbcc statements. The only remaining
> question is there a command or code that is known to be used to defrag a
> table. It would be very good if this would be usable on the fly (ad-hoc)
> Thanx
> BillO
>
> *** Sent via Developersdex http://www.examnotes.net ***
What do you mean by defrag a table?
A heap (table without a clustered index) stores data in no particular order.
As rows are deleted and then inserted SQL Server places those rows where
they fit.
If you have a clustered index on a table and the clustered index is
fragmented, then you could rebuild the clustered index and essentially
defrag your table.
Rick Sawtell
MCT, MCSD, MCDBA|||Hi,
See DBCC INDEXDEFRAG and DBCC DBREINDEX commands in sql server books online.
You could also see the command DBCC SHOWCONTIG in books online which details
the fragmentation for table.
Thanks
Hari
SQL Server MVP
"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:uDT6fUuZFHA.3876@.TK2MSFTNGP12.phx.gbl...
> "Bill Orova" <nospam@.devdex.com> wrote in message
> news:e3W#oPuZFHA.3876@.TK2MSFTNGP12.phx.gbl...
> What do you mean by defrag a table?
> A heap (table without a clustered index) stores data in no particular
> order.
> As rows are deleted and then inserted SQL Server places those rows where
> they fit.
> If you have a clustered index on a table and the clustered index is
> fragmented, then you could rebuild the clustered index and essentially
> defrag your table.
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>
>|||
Rick,
So essentially if there is no clustered index then it would not be
possible to defrag a table(heap) or if possible it would have no
benevolent consequences in term of hard disk space?
BillO
*** Sent via Developersdex http://www.examnotes.net ***|||It depends on what you mean by fragmentation. For a heap, the is no order. B
ut as pages and extents
are allocated and rows and pages are deallocated, you can leave "holes" in y
our pages as well as
extents. I.e., pages not fully utilized and extents where not all 8 pages ar
e used. If you consider
this a type of fragmentation, then there is no way to defrag except re-loadi
ng the table or possibly
(shudder) shrinking the file/database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Bill Orova" <nospam@.devdex.com> wrote in message news:O0EQbd6ZFHA.1088@.TK2MSFTNGP14.phx.gb
l...
>
> Rick,
> So essentially if there is no clustered index then it would not be
> possible to defrag a table(heap) or if possible it would have no
> benevolent consequences in term of hard disk space?
>
> BillO
> *** Sent via Developersdex http://www.examnotes.net ***

Sunday, February 19, 2012

How To Defrag SQL Server 2000

How to defrag an SQL 7 AND 2000 server?

How to defrag an SQL 7 AND 2000 server?

How to defrag an SQL 7 AND 2000 server?

How to defrag an SQL 7 AND 2000 server?

How to defrag a table?

define Hierarchy

Blog Archive

About Me