The tag has no wiki summary.

learn more… | top users | synonyms

7
votes
2answers
2k views

Defragmentation - Rebuilding Indexes SQL Server 2005

I'm researching defragmenting databases and it seems the following SQL statement is what I'm looking for: ALTER INDEX ALL ON mytablename REBUILD WITH(ONLINE = ON) When I pull the info from ...
6
votes
4answers
2k views

How does SQL Server reduce index fragmentation?

If a user never runs REBUILD or REORGANIZE on their database, does SQL Server still somehow defragment the indexes? MSDN suggests that if an index is over 30% fragmented, it is recommended to run ...
3
votes
1answer
758 views

Index fragmentation increase as more row being deleted

I have a table that contains more than 18 million records. I have a process that clears data out of that table everyday. Index fragmentation was low. This table has high transaction throughput. ...
12
votes
4answers
8k views

What's better for large changes to a table: DELETE and INSERT every time or UPDATE existing?

I am making a project where I need to change around 36K records in one table daily. I'm wondering what will perform better: delete rows and insert new ones, or update already existing rows For me ...
2
votes
2answers
1k views

How can an identity primary key index become fragmented?

From what I understand about index fragmentation, this should not be possible. The cases I have found in my databases are non-clustered. Example: ALTER TABLE [dbo].[ClaimLineInstitutional] ADD ...
1
vote
1answer
734 views

How to Prevent an Ever-Growing index file (MyISAM engine) From Causing Disk Fragmentation in Windows (NTFS partition)?

We have a keep-growing table which using MYISAM storage engine. Almost every half year, this table will crash, and need to be repaired. I checked the defragment result reported by windows ...
25
votes
3answers
18k views

Why is my database still fragmented after I rebuilt and reindexed everything?

I have a database which I tried to defragment all the tables at once by running this T-SQL: SELECT 'ALTER INDEX all ON ' + name + ' REORGANIZE;' + CHAR(10) + 'ALTER INDEX all ON ' + ...
2
votes
1answer
798 views

Optimising a Database Shrink (in terms of speed and reducing fragmentation)

We have a large database that has had a lot of older data stripped out, leaving primary datafile (all objects are currently in the primary filegroup) of about 80Gb containing a little less than 20Gb ...
5
votes
3answers
911 views

SQL Azure Fragmentation / Database Size

I have a database running on SQL Azure that is currently 280mb. It is a test database for a system that we're going to be putting into production soon so the data frequently gets deleted in batches ...
7
votes
1answer
678 views

B-tree node split strategy in SQL Server for monotonically increasing value

Consider a B-tree index on a value that will always increase monotonically, e.g. a column of type IDENTITY. With a conventional B-tree implementation, whenever a node is full, it will be split 50%/50% ...
2
votes
3answers
2k views

SQL Server Index Fragmentation

When should you rebuild an index? vs reorganize? What index fragmentation value is considered healthy?