The tag has no wiki summary.

learn more… | top users | synonyms

0
votes
0answers
14 views

What is fragmenting my index on a table with stable traffic?

I am using Ola Hallengren's solution for optimizing indexes. I run it on Sunday every week. For the last three weeks every time it runs it reports that the clustered index on the database's biggest ...
2
votes
3answers
50 views

Additional space required for INDEX REORGANIZE

MSDN states: ALTER INDEX REORGANIZE; however, log space is required. The database recovery mode is Simple, does that implies the log space is actually NOT required ?.
1
vote
1answer
45 views

Options for Index Defragmentations For Critical Tables

I wonder if this is a suitable solution for index defragmentations. scenario is like this; There is a table named 'Events' which is very critical and has 4 indexes which are not done maintenance ...
0
votes
0answers
34 views

Measure fragmentation of a single index?

We are measuring the impact of different defrag approaches that we do one index at a time. How do we query to see fragmentation of a single index, by index name (or table + index). This is on SQL ...
2
votes
1answer
62 views

How do databases deal with file system fragmentation?

Especially analytic databases that try to optimize for queries that scan large portions of tables instead of randomly accessing specific rows. For example Redshift has a concept of a sort key that, ...
0
votes
1answer
116 views

When to rebuild and when to reorganize indexes [duplicate]

I have been trying to gather information about rebuilding and reorganizing indexes. From stackoverflow page (How Often should the indexes be re-build in sql-server DB?) i got this query : SELECT ...
2
votes
1answer
135 views

Clustered composite key high fragmentation and fill factor

I've recently made a table to hold the language preferences of my users as follow: CREATE TABLE [dbo].[systemUserLangPreference]( [systemUserID] [int] NOT NULL, [langID] [int] NOT NULL, ...
2
votes
2answers
107 views

fragmentation for non cluster index growth very fast to 30

i have table with about 7 milion records.index fragmentation was about 90 . i try rebuild index for all index. and it works fine but after nightly job fragmentation for one of none cluster index ...
0
votes
0answers
31 views

Including INSERT, UPDATE, DELETE when performing vertical framentation

I had a task to perform vertical fragmentation that involves developing Attribute Usage Matrix (AUM) and Attribute Affinity Matrix (AAM) I do know how to construct them, but I am confused over one ...
1
vote
2answers
582 views

What is index fragmentation percent a percentage of?

So I am familiar with the concept of index fragmentation and how to fix it but I'm not really sure of one specific item: When you look at index fragmentation, it's always reported in terms of ...
1
vote
2answers
103 views

For merge replication, do I need to schedule index maintenance on all sites or just publisher?

I have a very fragmented database that uses merge replication with remote sites. Do I need to schedule index maintenance (reorganize / rebuild) for all sites? Or just on publisher copy?
1
vote
3answers
117 views

What is SQL Server 2000 spid -2?

I have a SQL Server 2000 machine that has a pretty big database on it - almost a terabyte in size. There's a particular table that has been a source of some production issues and any attempt to try ...
2
votes
1answer
265 views

InnoDB, Hash Table, Reference Table and Fragmentation

I have a big list of domains and URLs that I store in a database, there are around 150M domains and 300M URLs, I'm using InnoDB to store each in the format: CREATE TABLE IF NOT EXISTS `domains_list` ...
1
vote
2answers
454 views

Difference between partitioning and fragmentation [closed]

I'm trying to learn something about distributed databases and I stumbled upon the concepts of fragmentation and partitioning. It seems to me as if both do the same thing and produce similar results ...
6
votes
1answer
852 views

How to set SQL Server index pages per fragment?

I have SQL Server 2008, and a number of databases. I have discovered that one of my table's indexes is extremely fragmented (How I know: http://msdn.microsoft.com/en-us/library/ms189858.aspx) The ...
7
votes
0answers
51 views

Is Logical Fragmentation Meaningful in the age of huge SANs? [duplicate]

So, every time I read that a data file shrink will cause fragmentation or a developer rave on that the 95% frag he sees is the cause of all life's misery, I wonder what the problem really is. We no ...
1
vote
2answers
2k views

SQL Server clustered index high fragmentation

I've got a Table with an Integer (4bytes) as primary key. it's defined as an identity. it is also the clustered index. Inserts are working perfectly fine. After inserting 2000 rows the fragmentation ...
1
vote
1answer
154 views

Equivalent of MRG_MYISAM in databases other than MySQL? [closed]

Does anyone know if other database have something equivalent to MRG_MYISAM (aka the MERGE table type/storage engine)? I know about fragmenting, but this is not quite the same AFAIK. We're using ...
4
votes
2answers
3k views

Is high Extent Fragmentation a problem?

DBCC SHOWCONTIG scanning 'MyTable' table... Table: 'MyTable' (2048062382); index ID: 1, database ID: 28 TABLE level scan performed. - Pages Scanned................................: 1019182 - Extents ...
0
votes
1answer
194 views

What is the significance of root and page level fragmentation in a clustered b-tree index?

In investigating fragmentation on some of our large tables in a SQL Server 2005 database, I was using a query based on sys.dm_db_index_physical_stats. The query returns a row for each level in the ...
1
vote
1answer
816 views

MySQL Index Fragmentation

Is there any way to find out the fragmentation of indexes in MySQL ? If such an output can be obtained, it would be helpful to identify which all indexes need to be rebuilt. I'm using MySQL 5.1 on ...
2
votes
1answer
402 views

Disadvantages of reorganizing all tables nightly

I consider scheduling a SQL Server job that reorganizes all tables nightly. We don't have enterprise, so we cannot rebuild online (this means we need to reorganize). We also don't want to invest too ...
1
vote
1answer
456 views

Defrag or Backup+Restore

I have an MDF file that was autogrown from 1MB to 40GB in 10% increment. The file is heavily fragmented now, and I want to defrag it with minimum downtime. I have a plan that I am not sure will work: ...
4
votes
2answers
718 views

Why index REBUILD does not reduce index fragmentatation with a page count > 1000?

This is kind of a followup to the existing question on this site "Why index REBUILD does not reduce index fragmentatation?". The accepted answer to that question, and all other resources I have found ...
2
votes
2answers
455 views

Replacing composite key with surrogate

I have the following table that has a couple of million rows in it and is 99% fragmented virtually all of the time. My plan was to insert a IDENTITY field as a surrogate key to replace the current ...
2
votes
2answers
225 views

Removing physical fragmentation

If I have inherited db that has grown to 400GB 1 MB chunk at at time. Is an offline defrag the only way to remove all the physical fragmentation. Another issue I have is that the data and log files ...
5
votes
2answers
768 views

How can I save a severely fragmented database/table?

I've recently made a discovery that might explain my slow SQL run times. I found out that the default in SSMS is to allow mdf growth of 1MB at a time. When the size of one table alone is roughly 23 ...
2
votes
2answers
1k views

Oracle: deleting data from tables leads to data file fragmentation?

When randomly deleting ~5GB from a ~10GB Oracle table, I suspect that disk space is left in fragments. Is there a way to know about the disk state and any query to defragment or shrink the space?
3
votes
1answer
171 views

I have Fragmented Index on a empty table!

I ran the following query on my database to identify the fragmented indexes. However I surprised when saw the Indexes belong to some empty tables among the highly fragmented indexes! How can it be ...
3
votes
2answers
60 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 ...
9
votes
3answers
1k 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? ...
7
votes
1answer
1k 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
291 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 ...
2
votes
1answer
2k 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 ...
8
votes
2answers
6k 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
494 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?
5
votes
2answers
3k 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
231 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
228 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
2k 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 ...
6
votes
2answers
1k 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
615 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. ...
9
votes
3answers
4k 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
906 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
672 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 ...
13
votes
3answers
10k 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
678 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
812 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
528 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% ...