Taxonomy is the science of classification. It comes from the Greek taxis meaning arrangement or division and nomos meaning law. It provides a conceptual framework for discussion, analysis, or information retrieval. It arranges hierarchically, elements of a group (taxon) into subgroups (taxa) that are mutually exclusive. Each group has the same properties, behaviours, and constraints. And each increasing level has further properties, behaviours, and constraints.
In biological terms this would be the classification of species.
Chaos, complexity, curiosity and database systems. A place where research meets industry
Welcome
Passionately curious about Data, Databases and Systems Complexity. Data is ubiquitous, the database universe is dichotomous (structured and unstructured), expanding and complex. Find my Database Research at SQLToolkit.co.uk . Microsoft Data Platform MVP
"The important thing is not to stop questioning. Curiosity has its own reason for existing" Einstein
"The important thing is not to stop questioning. Curiosity has its own reason for existing" Einstein
Monday 25 January 2010
Monday 18 January 2010
Fragmentation
Fragmentation is the storing of data non-contiguously on disk.
Detecting Fragmentation
The level of fragmentation can be determined by running the Dynamic Management View (DMV): sys.dm_db_index_physical_stats.
To return information for all databases detailing all statistics for all tables and indexes within the instance of SQL Server run
SELECT * FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, NULL);
A couple of the DMV columns are defined below
avg_fragmentation_in_percent: Logical fragmentation for indexes, or extent fragmentation for heaps in the IN_ROW_DATA allocation unit.. The percent of logical fragmentation (out-of-order pages in the index). The lower this value, the better it is. If this value is higher than 10%, some corrective action should be taken.
avg_page_space_used_in_percent : Average percentage of available data storage space used in all pages. Higher the value, the better it is. A value is lower than 75%, some corrective action should be taken.
Reducing Fragmentation
After the degree of fragmentation is identified from sys.dm_db_index_physical_stats, a guideline on the course of action to be taken is listed below
Detecting Fragmentation
The level of fragmentation can be determined by running the Dynamic Management View (DMV): sys.dm_db_index_physical_stats.
To return information for all databases detailing all statistics for all tables and indexes within the instance of SQL Server run
SELECT * FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, NULL);
A couple of the DMV columns are defined below
avg_fragmentation_in_percent: Logical fragmentation for indexes, or extent fragmentation for heaps in the IN_ROW_DATA allocation unit.. The percent of logical fragmentation (out-of-order pages in the index). The lower this value, the better it is. If this value is higher than 10%, some corrective action should be taken.
avg_page_space_used_in_percent : Average percentage of available data storage space used in all pages. Higher the value, the better it is. A value is lower than 75%, some corrective action should be taken.
Reducing Fragmentation
After the degree of fragmentation is identified from sys.dm_db_index_physical_stats, a guideline on the course of action to be taken is listed below
avg fragmentation in percent | Suggested Action |
> 5% | No action required |
> 5% and < = 30% | ALTER INDEX REORGANIZE |
> 30% | ALTER INDEX REBUILD WITH (ONLINE = ON) can be executed online or offline. A replacement for DBCC DBREINDEX |
Subscribe to:
Posts (Atom)