The tag has no usage guidance.

learn more… | top users | synonyms

3
votes
2answers
62 views

Should I put followers_no & following_no & product_no & last_login and etc in the user table? Won't updates cause fragmentation on the table?

I have a table for users in a social networking site. I want it to be efficient from ground up. If I put mentioned fields on the table then I have to update user's table on each changes. Should I have ...
10
votes
3answers
2k views

PostgreSQL Initial Database Size

There are 2 parts to my question. Is there a way of specifying the initial size of a database in PostgreSQL? If there isn't, how do you deal with fragmentation when the database grows over time? ...
9
votes
2answers
2k views

How to prevent daily index fragmentation of 99%

I have a highscore table for 100.000 players that is being inserted into 2 times a day with one record per player. At the end of the day the index fragmentation for the indexes in that table is 99%. ...
8
votes
1answer
454 views

Defragmenting SQL Server data and log files live with MoveFile API

My employer is looking at deploying NTFS defragmentation software that uses the Windows MoveFile API to defragment open files. This would get deployed to thousands of SQL Server servers running SQL ...
3
votes
1answer
4k views

SQL Server Rebuild/Reorganize Fragmented Indexes causes CRC error or consistency errors

In our test lab I've been experimenting with different jobs to keep our critical indexes from becoming too fragmented. I'm currently using the approach described here: sys.dm_db_index_physical_stats ...
14
votes
2answers
13k views

Why index REBUILD does not reduce index fragmentatation?

I have used ALTER INDEX REBUILD to remove index fragmentation. In some cases REBUILD does not seem to remove this fragmentation. What are the reasons why REBUILD does not remove fragmentation? It ...
0
votes
1answer
588 views

Model for Distributed Database [closed]

I want to design a model for a distributed database solution. Are there appropriate tools and approaches? How to include stereotypes for (horizontal) fragments?
7
votes
2answers
5k views

REBUILD - Clustered Index, TABLE, or both?

I'm having trouble finding a definitive resource on this anywhere, so hopefully a guru can give me an answer here. I have a very large table that we had to add a column to. The clustered index is ...
10
votes
2answers
280 views

Index fragmentation while continuously processing

SQL Server 2005 I need to be able to continuously process about 350M records in a 900M record table. The query I'm using to select the records to process becomes badly fragmented as I process and I ...
1
vote
1answer
455 views

How should I distribute my tables for better performance from fragmentation point of view

I have a database on SQL Server 2008 R2 with about 20 non-indexed tables that are continuously undergoing INSERTs AND DELETEs. There are about 400,000 records @ 4KB/record being inserted or deleted ...
3
votes
2answers
3k views

How to identify fragmentation level of the table data itself not the table indexes, and then defrag

Every where I can find descriptions on how to identify the fragmentation level of the table indexes, but I cant find how to identify the same for the table data iself. Also defragmentation solution is ...
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 ...
7
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
879 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. ...
16
votes
4answers
12k 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 ...
4
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
767 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 ...
29
votes
3answers
24k 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
870 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
939 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
767 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% ...
3
votes
3answers
3k views

SQL Server Index Fragmentation

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