Disk Fragmentation && SQL

Apr 14, 2004

One of my production servers has been determined to be 92% fragmented.

What's the proper procedure for defraging a database server?

I couldn't find anything very helpful in BOL, nor Knowledge Base.

Sidney Ives
Database Administrator
Sentara Healthcare

View 3 Replies


ADVERTISEMENT

Disk Fragmentation

Aug 9, 2004

What's the best way to find out if disk fragmentation on Windows 2000 Server is affecting SQL Server performance?

If disk fragmentation is shown to be a cause of performance problems, what are the recommendations for a disk fragmentation strategy? eg. use the win 2000 built in disk defrag utility or buy a 3rd party product like DiskKeeper? How much of an overhead is a product like DiskKeeper that defrags in the background?

Clive

View 1 Replies View Related

MDF Disk Fragmentation - Backup And Restore

Jul 23, 2005

If databases on a physical drive [G:] are fragmented, and the drive isextended by adding more hard drives to the array, does it make sense tobackup and restore the fragmented databases?The Windows Server should be able to find contiguous space for eachdatabase, since it shows 75% free space on the SQL Data drive withoutany file fragments on it.Or will it restore to the original location, in which case does it makesense to delete the databases and restore them from the backups?Thank you very much!

View 1 Replies View Related

File Fragmentation And Disk Read Queue

May 16, 2008

I have a poorly performing SQL box.

I have run perfmon and the avg read queue length is pretty much permanently maxed out at 100%.

I have run a database index defrag.

On further inspection the file system is highly fragmented. There is a file fragmentation of 98% with the mdf file fragmented in 25,000 pieces. Running a standard windows defrag does not resolve this.

Two questions?

1- Is heavy file fragmentation of the MDF file a likely cause of the read queue length bottlneck?

2 - Why is the MDF file not defragmenting? Does the SQL server have to be taken offline? Is it possible to defrag a MDF file?

View 2 Replies View Related

Is It Possible To Move My Sql 2000 Database (in C Disk) To Another Disk (Disk) ?

Dec 28, 2006

hello,all
          I am new to Sql 2000,I installed sql 2000 database in C disk,but Now I found my C disk space is smaller than before,So I want to move my databse(include data and structure)   from C Disk to D Disk(its space is very large) .
         is it possible to do it ? 
         if its can be done ,do I need to change my asp.net program source code (exp: chaneg my crystal  report connectstring ) ?
        thanks in advanced!
 
 
 
      

View 1 Replies View Related

Calculating COUNTER Physical Disk: AVG. DISK QUEUE LENGTH

Sep 10, 2007

If I return the Average, Minimum, and Maximum values for the counter Physical Disk: Avg. Disk Queue Length, and those values are 10, 0, 87 respectively, which value do I use to compute the Avg. Disk Queue Length for a 4 disk array(RAID 10): Average, Minimum, or Maximum? The disk(lun) is on a SAN.

View 1 Replies View Related

Get Total Disk Size And Free Disk Space

Nov 13, 2007

-- Initialize Control Mechanism
DECLARE@Drive TINYINT,
@SQL VARCHAR(100)

SET@Drive = 97

-- Setup Staging Area
DECLARE@Drives TABLE
(
Drive CHAR(1),
Info VARCHAR(80)
)

WHILE @Drive <= 122
BEGIN
SET@SQL = 'EXEC XP_CMDSHELL ''fsutil volume diskfree ' + CHAR(@Drive) + ':'''

INSERT@Drives
(
Info
)
EXEC(@SQL)

UPDATE@Drives
SETDrive = CHAR(@Drive)
WHEREDrive IS NULL

SET@Drive = @Drive + 1
END

-- Show the expected output
SELECTDrive,
SUM(CASE WHEN Info LIKE 'Total # of bytes : %' THEN CAST(REPLACE(SUBSTRING(Info, 32, 48), CHAR(13), '') AS BIGINT) ELSE CAST(0 AS BIGINT) END) AS TotalBytes,
SUM(CASE WHEN Info LIKE 'Total # of free bytes : %' THEN CAST(REPLACE(SUBSTRING(Info, 32, 48), CHAR(13), '') AS BIGINT) ELSE CAST(0 AS BIGINT) END) AS FreeBytes,
SUM(CASE WHEN Info LIKE 'Total # of avail free bytes : %' THEN CAST(REPLACE(SUBSTRING(Info, 32, 48), CHAR(13), '') AS BIGINT) ELSE CAST(0 AS BIGINT) END) AS AvailFreeBytes
FROM(
SELECTDrive,
Info
FROM@Drives
WHEREInfo LIKE 'Total # of %'
) AS d
GROUP BYDrive
ORDER BYDrive

E 12°55'05.25"
N 56°04'39.16"

View 16 Replies View Related

Should The Quorum Disk Be A Physical Disk Or Majority Node Set?

Nov 15, 2006

Hello,

I am trying to setup a test cluster and am having an issue. When I try to create the resource of a physical disk it takes both the drive e: and drive q: and doesn't seperate them into two physical disks as resources. This means when I try to associate the quorum disk it links the to physcial disk resource of drive e and q. Then when I try to install SQL2k5 I get the warning about installing SQL on the quorum disk. Am I missing something? Is there a way to seperate e and q onto two physical disk resources so I can specifically associate the quorum to q and the sql to e or should I be setting the quorum disk to a majority node set? Thanks in advance.

John

View 4 Replies View Related

Disk Crash Of Disk That Contains The Paging File.

Feb 20, 2001

Hello,

this is my configuration :

1) 3 disks in RAID5 that hold the SQL data
2) 1 disk in RAID0 that holds the only paging file.

What will happen to the SQL data (DB) when the disk that holds the paging file crashes?

Kindest regards,
Luc.

View 1 Replies View Related

SQL Server, Disk Arrays And Disk IO

May 7, 2004

Hi all,

Ok here goes,

I have a three tier system using SQL server 2000, we are currently experiencing IO bottle necks on our SCSI Raid 10 array, which holds the Data and the logs in separate partitions.

So my options as I understand it are:

Get Enterprise edition

or

Get another physical raid 10 array and separate the logs and data i.e. data on one array and logs on the other array.


I would like to try the latter but I am totally unsure how much difference this will make or whether it will make any difference at all.

Does anyone know how much performance increase I will get from using two arrays as opposed to one?


Any other advice on this scenario would be greatly appreciated.


Thanks

View 4 Replies View Related

Fragmentation

May 13, 2008

 i want to do fragmentation using SQL 2005 Express. Can their be any helping material for making fragmentation.  Any Example

View 2 Replies View Related

Fragmentation

Mar 29, 2001

What DBCC or otherwise one would be able to find table fragmentation in SQL 7 and how to take some proactive actions ?

Thanks very much for your time.


-Tim

View 2 Replies View Related

Fragmentation

Jun 1, 2004

Would someone please let me know which is the better way to reduce fragmentation on Windows:NT drive?
dbcc indexdefrag
or
dbcc reindex
or
dropping and recreating indexes through SQL?

Thanks
Vinnie

View 5 Replies View Related

Fragmentation?

Jul 27, 2007

Sql Server 2000.

For simplicity, I am avoiding minor details.

Here is a scenario.

Create Table T (T_ID int PK, €¦..) and this table is referenced by 20 other places on T_ID.

And these 20 tables have T_ID as PK or part of PK.

We have large volume of deletes in T and in the other 20 tables based on T_ID.

Once the deletes are done, will dropping and re-creating the FK€™s that reference the T_ID
help reduce the fragmentation caused by the deletes?

View 4 Replies View Related

Table Fragmentation

May 8, 2001

hi all,

i have inherited a database running on sql7 sp2. a lot of the tables do not have a clustered index. some of these tables are highly fragmented. below is a sample of the dbcc showcontig output

TABLE level scan performed.
- Pages Scanned................................: 104934
- Extents Scanned..............................: 13124
- Extent Switches..............................: 13123
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 99.95% [13117:13124]
- Extent Scan Fragmentation ...................: 5.84%
- Avg. Bytes Free per Page.....................: 7757.4
- Avg. Page Density (full).....................: 4.16%

this table has 5 non-null char columns whose total length is 43 chars and the table occupies about 900 mb for only about 670,000 rows!
select on the table sucks!

since there is no clustered index i have to rebuild the table to remove the fragmentation. is there a way to reorganize the table without recreating it?

thanx

:-)

hemant

View 1 Replies View Related

Fragmentation -Urgent

Oct 13, 2000

Hi!

I have a problem with defragmenting the tables. When i ran dbcc showcontig for the tables, it shows 94%, 50%, 33% fragmentations . I have rebuild indexes on all those tables and ran dbcc showcontig again but still it shows the same % of fragmentations. Is there any way to remove fragmentation on all the tables completely. Any help would be appreciated.

Thanks,
Saran

View 2 Replies View Related

Database Fragmentation

Oct 1, 2004

Hello,

How can I mesure the database fragmentation ? Cause DBCC SHOWCONTIG shows obects fragmentation only. I would like to see the whole database fragmentation.

Thanks for help

View 12 Replies View Related

Index Fragmentation

Jun 22, 2008

What functions of tools do you use for managing index fragmentation?

DBCC?

I am working through MS Press SQL 2005 book and it mentions the
sys.dm_db_index_physical_stats function? It then give an example of code which is very involved.

Does anybody use this function?

Thanks

View 10 Replies View Related

Index Fragmentation

May 10, 2007

before rebuild index
---------------------
tablenameIndexname
avg_fragmentation_in_percent
Payoff_QuotePK_Payoff_QuoteDetail
83.3333333333333
ALTER INDEX ALL ON Payoff_Quote REBUILD
after issue the above statement result remain same.

View 1 Replies View Related

Index Fragmentation

Jun 24, 2007

Hi
When I run sys.dm_db_index_physical_stats on AdventureWorks.Production.Product, I get an average fragmentaion of 46%, 0%, 25% & 50% on the Indexes.

I run ALTER INDEX ALL ON AdventureWorks.Production.Product
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,
STATISTICS_NORECOMPUTE = ON)

and the index fragmentation does not change...

Why is that?? I have tried refreshing the table and the statistics but why does it look like rebuilding the indexes has done nothing?

View 1 Replies View Related

Index Fragmentation In The DMV

Sep 25, 2007

I have a nice script that will look at the index fragmentation by using the DMV (sys.dm_db_index_physical_stats) and if its above a specificed threshhold, it will rebuild or reorg the index.

What I have noticed is that even after reorging and/or rebuilding the index, the fragmentation percent in the DMV does not change for some indexes. Other indexes are updated just fine.

Is this a result of updating statistics? Why would the fragmentation change for some indexes and not others? Why does it seem no matter how much rebuilding or reorging is done, the fragmentation percent for some indexes does not change?


- Eric

View 6 Replies View Related

Heavy Fragmentation

May 8, 2007

I'm attempting to debug some query timeouts on a production server.

Several tables involved in this query have logical scan fragmentation levels around 10%, and extent scan fragmentation levels 90%+.

Running INDEXDEFRAG or DBREINDEX reduces the logical scan fragmentation down to almost 0%, but the extent scan fragmentation stays upwards of 90%.

Why? What can I do to get this fragmentation down? Is it because this particular index is on a GUID?

View 7 Replies View Related

SQL Backup (bak) Fragmentation

Dec 13, 2006

We backup a lot of SQL databases. The db admin uses SQL to dump the databases to a *.bak file on a network share, then we pick them up to tape.

The problem is that for some reason, the backup files are MASSIVELY fragmented, which kills our backup speed to tape (via Netbackup Enterprise). If I defragment the drive (via the built-in tool on Win2K3 or PerfectDisk 8) our speed to tape more than doubles (can go from 16MB/sec to 36MB/sec). However, after the next SQL backup, the drive is completley fragmented once again (around 70%).

Is there any way to improve how these files are written to disk (to keep them relatively in one piece)? For some reason it appears this is a bigger issue with the SQL backup files than any other file type. Thanks.



Jim B

View 3 Replies View Related

Fragmentation Does Not Change

Aug 8, 2007

I am running this script to see the fragmentation of indexes.



SELECT a.database_id

,a.object_id

,a.index_id

,b.name

,a.avg_fragmentation_in_percent

FROM sys.dm_db_index_physical_stats (7,NULL, NULL, NULL, NULL) AS a

JOIN sys.indexes AS b

ON a.object_id = b.object_id

AND a.index_id = b.index_id

where a.database_id = db_id()

order by a.avg_fragmentation_in_percent desc



When I rebuild an index the fragmentation does not change 10% or lower (default index fill factor is 0, which I think is 90% fill)

SQL Server 2005 EE SP 2

Thanks,
~Joseph~

View 1 Replies View Related

Fragmentation Of SQL Data Files

Mar 30, 2004

Hi,

How do you defragement SQL database data files? Running the DBCC DBREINDEX , DBCC INDEXDEFRAG, does that minimize fragmentations of the tables and the indexes files or is it just the index files?
Thanks in advance

a

View 6 Replies View Related

Index Showing Fragmentation

May 11, 2015

We have nightly job running to reorg all in one of our prod database. But the index on one of the table fragmenting quickly by the morning showing 90% fragmentation.

View 9 Replies View Related

SQL Server Memory Fragmentation

Dec 7, 2006

Abe writes "i am using SQL server 2000, when i tried to back up the database in SQL server, the backup began failing coz SQL Server could no longer allocate 964kb of contiguous blocks. (the min needed is 1MB)

What causes the memory or blockes to be fragmented?

How do you solve this issue other than rebooting the server?

How do you prevent this from happening or monitor this issue?

Regards,
Abe"

View 1 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

Catch-22 With Fragmentation And Shrinking

Sep 10, 2007

I've inherited a SQL 2005 Server with a major problem. It's been badly admin'd for ages.

It's got a 250gb disk with 3% space free. The disk is 93% fragmented. There is a sql data file of 231gb on that volume that i'm trying to shrink as it has 92gb of free space on it apparently. It won't shrink because the fragmentation is too bad - it just hangs for 2 days then times out. I've tried shrinking the files and database to no avail. I can't defrag as it just says it's finished after 2 minutes and doesn't do anything.

Any suggestions or am I screwed?

View 5 Replies View Related

System Tables Fragmentation

Jul 20, 2005

can you defrag system tables. They appear in my dbcc showcontigreport. Some are worse than other but if it where a user table Iwould defrag it. However the reindex commands doesnt work on systemtables.... Any ideas.Thanks Matt.

View 1 Replies View Related

Index Fragmentation Issue

Feb 9, 2008



Hi,
It is natural that index gets fragment overtime. But I would like to know how you do the reindexing or defragment when your database table is big and you cannot afford the time to rebuild them. Thanks.

View 6 Replies View Related

Check Fragmentation Without DBCC

Nov 21, 2007

Hi All,
I want to check the fragmentation for a table. But I do not have permissions to run DBCC commands on that server. Do we have any other method to identify the health of the table?

Thanks a lot.

Mannu.

View 4 Replies View Related

Index Fragmentation On SQL 2000

Nov 8, 2007

I have several databases in which our indexes have not been rebuilt/reorganized. I have worked primarily on SQL 2005 but with 2000 I am not familiar with Logical vs Extent fragmentation. And on the 2000 server there is high Logical Fragmentation (1000+%). With me rebuilding the indexes on the 2000 server databases, will this reduce performance or halt functionality of the servers while this process is running???

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved