Sample Header Ad - 728x90

Database Administrators

Q&A for database professionals who wish to improve their database skills

Latest Questions

5 votes
2 answers
30607 views
Find out when your index was last rebuilt / reorganized
I've recently come across a database, which has very high fragmentation on large tables. Page count > 1'000'000 with fragmentation 99,... I would like to know how long this table has gone without a reorganize / rebuild. That way I can review changes made on that date, to try and find a cause. (Also...
I've recently come across a database, which has very high fragmentation on large tables. Page count > 1'000'000 with fragmentation 99,... I would like to know how long this table has gone without a reorganize / rebuild. That way I can review changes made on that date, to try and find a cause. (Also it'd look nice on my report "this database has not been properly maintained since...) As I'm in the process of setting up a job to quickly reorganize the affected tables (this night probably), any solution that could offer me a history of index operations would be much appreciated. If not, simply knowing that I can check this data in the future would be very helpful.
Reaces (2681 rep)
Nov 18, 2014, 02:39 PM • Last activity: Aug 22, 2024, 09:28 AM
0 votes
1 answers
94 views
mysql reuse of deleted rows
Using MySql and innodb with `innodb_file_per_table` flag ON. - We have a table with a size of ~100GB. We delete the data and insert fresh data (approx same size) every day (the delete is in chunks by a key, when this key task is fetch fresh data). - We see that there is no reuse, the table size is i...
Using MySql and innodb with innodb_file_per_table flag ON. - We have a table with a size of ~100GB. We delete the data and insert fresh data (approx same size) every day (the delete is in chunks by a key, when this key task is fetch fresh data). - We see that there is no reuse, the table size is increasing with ~100GB every day. - As far as we know mysql should reuse deleted space (same table). - We do not want to reclaim the space, just to have the reuse on the same table working... What could cause this behavior? We cannot optimize the table every single day. (similar to https://dba.stackexchange.com/questions/162632/does-mysql-use-deleted-rows-space-in-a-table)
Amichay Miara (1 rep)
Jul 11, 2024, 01:03 PM • Last activity: Jul 18, 2024, 01:16 AM
0 votes
1 answers
106 views
Fragmentation when creating non-clustered index
When I was dealing with fragmentation, I had a question about a non-clustered index. In non-clustered index actual table data (typically stored in a clustered index, otherwise a heap) is stored separately from the non-clustered index. How does this impact table and index fragmentation when I create...
When I was dealing with fragmentation, I had a question about a non-clustered index. In non-clustered index actual table data (typically stored in a clustered index, otherwise a heap) is stored separately from the non-clustered index. How does this impact table and index fragmentation when I create non-clustered index? I appreciate a detailed response.
volodya_neftyannik (35 rep)
Jan 20, 2024, 12:45 PM • Last activity: Jan 20, 2024, 02:34 PM
0 votes
1 answers
1102 views
Index Maintenance - Reorganize After BIG SQL Server Shrink
I'm planning to execute the below index maintenance created by [Ola Hallengren][1] in a 1TB Database. EXECUTE [dbo].[IndexOptimize] @Databases = 'USER_DATABASES', @FragmentationLow = NULL, @FragmentationMedium = 'INDEX_REORGANIZE', @FragmentationHigh = 'INDEX_REORGANIZE', @FragmentationLevel1 = 50,...
I'm planning to execute the below index maintenance created by Ola Hallengren in a 1TB Database. EXECUTE [dbo].[IndexOptimize] @Databases = 'USER_DATABASES', @FragmentationLow = NULL, @FragmentationMedium = 'INDEX_REORGANIZE', @FragmentationHigh = 'INDEX_REORGANIZE', @FragmentationLevel1 = 50, @FragmentationLevel2 = 80, @UpdateStatistics = 'ALL' ,@FillFactor = 100 So basically I know that shrinking is a very bad idea and not a common practice, I'm Shrinking because is really needed and it has been working good. So due to the fragmentation that the shrinking is causing, I'm planning to use the mentioned stored procedure to **Reorganize** all the affected indexes and avoid getting the freed space back with a **Rebuild** So the specific question would be, does this look like a good approach or have you faced something similar and used another approach for it ?, or if you have suggestions are gonna be highly appreciated.
user141153
Aug 22, 2018, 02:35 AM • Last activity: Dec 7, 2023, 05:08 PM
0 votes
1 answers
2916 views
Reclaiming Disk Space for MariaDB Table
I'm facing a disk space issue with a MariaDB database and would appreciate your insights on resolving it. Here's the scenario: - MariaDB version: 10.3 - OS: Ubuntu 20.04 - Engine: Innodb - Table name: 'foo' - Data_length: 26GB - Index_length: 16GB - Data_free: 7GB - Fragmentation ~ 17% - innodb_file...
I'm facing a disk space issue with a MariaDB database and would appreciate your insights on resolving it. Here's the scenario: - MariaDB version: 10.3 - OS: Ubuntu 20.04 - Engine: Innodb - Table name: 'foo' - Data_length: 26GB - Index_length: 16GB - Data_free: 7GB - Fragmentation ~ 17% - innodb_file_per_table=ON The problem is that the main disk, where the database data is stored, has only 8GB of free space left. We recently encountered a bulk delete operation on the 'foo' table, which has resulted in fragmentation. I want to reclaim space back to OS. Considering the limited free space, running the OPTIMIZE TABLE command directly on the 'foo' table is not feasible. The operation would require additional disk space to create a defragmented copy of the table. I have an extra disk with more than enough space, which could be utilized to address the issue. The downtime can be up to 6 hours long at night time. Here's the approach I'm considering, and I'd appreciate your feedback on its viability: 1. Stop MariaDB service 2. Change datadir location in mysqld.cnf to point to extra disk 3. Start MariDB service 4. OPTIMIZE table using this query - ALTER TABLE foo ENGINE innodb; 5. Repeat 1-3 to go back to using main disk. Are there any alternative methods you suggest to handle this situation effectively?
Tamer Mukayev (5 rep)
Jul 10, 2023, 06:26 AM • Last activity: Jul 10, 2023, 03:59 PM
0 votes
1 answers
261 views
How can I monitor and address fragmentation on RDS for Oracle database?
I'm planning to migrate my database from on-premises to AWS RDS for Oracle. However, I'm not sure if Amazon RDS supports tools such as Automatic Fragment Advisor or the Automatic Segment Space Management feature of Oracle to control fragmentation issues. I have tried searching on AWS documentation a...
I'm planning to migrate my database from on-premises to AWS RDS for Oracle. However, I'm not sure if Amazon RDS supports tools such as Automatic Fragment Advisor or the Automatic Segment Space Management feature of Oracle to control fragmentation issues. I have tried searching on AWS documentation and Google but I haven't found any official information confirming this.
Phú Trịnh (3 rep)
Apr 27, 2023, 07:21 AM • Last activity: Apr 27, 2023, 07:32 AM
1 votes
2 answers
97 views
Does clustered index fragmentation happen in relational DBMS?
Is it possible that after a certain pattern of random inserts and deletes **leaf data nodes** become fragmented with **clustered** index? I.e, that the physical order does not reflect the logical order (by say, INT primary key) imposed by the clustered index? This way, range queries would require ra...
Is it possible that after a certain pattern of random inserts and deletes **leaf data nodes** become fragmented with **clustered** index? I.e, that the physical order does not reflect the logical order (by say, INT primary key) imposed by the clustered index? This way, range queries would require random I/O even after finding the beginning of the interval. Most university courses (ex. CMU Introduction to Database Systems by Andy Pavlo) say that data is physically ordered according to the key. While definitely approximate to the reality, that looks as unrealistic to me counting the cost of frequent file defragmentation that would be required.
Borisav Živanović (135 rep)
Apr 12, 2023, 07:32 PM • Last activity: Apr 14, 2023, 02:30 PM
7 votes
2 answers
11228 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 Scanned..............................: 127400 - Extent Switches..............................: 127399...
DBCC SHOWCONTIG scanning 'MyTable' table... Table: 'MyTable' (2048062382); index ID: 1, database ID: 28 TABLE level scan performed. - Pages Scanned................................: 1019182 - Extents Scanned..............................: 127400 - Extent Switches..............................: 127399 - Avg. Pages per Extent........................: 8.0 - Scan Density [Best Count:Actual Count].......: 100.00% [127398:127400] - Logical Scan Fragmentation ..................: 0.01% - Extent Scan Fragmentation ...................: 77.25% - Avg. Bytes Free per Page.....................: 135.7 - Avg. Page Density (full).....................: 98.32% I have read that Scan Density = 100% is very good, and Logical Scan Fragementation <1% is also great. 77% Extent Scan Fragmentation troubles me, but the internet says to ignore it. I'm analyzing a single-table slow performing query. It runs ~30 seconds on first execution, then 200 ms on second and subsequent executions. I can reset this behavior with DBCC DROPCLEANBUFFERS. Is the high Extent Scan Fragmentation an important clue? (If not, I'll likely add another question about my single-table query).
Amy B (241 rep)
Apr 16, 2013, 04:24 PM • Last activity: Apr 6, 2023, 12:38 AM
35 votes
3 answers
43619 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 seems that this happens especially with small indices.
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 seems that this happens especially with small indices.
jrara (5393 rep)
May 24, 2012, 06:03 PM • Last activity: Apr 5, 2023, 06:43 PM
2 votes
2 answers
3125 views
How defragment compressed partitioned table in oracle?
I have partitioned table by day with compressed query high option for partitions in Oracle 11.2 database. Delete by key and direct insert was used to populate a data for the table. So the table is fragmented currently. As I know from Oracle documentation the shrink space can not be applied to compre...
I have partitioned table by day with compressed query high option for partitions in Oracle 11.2 database. Delete by key and direct insert was used to populate a data for the table. So the table is fragmented currently. As I know from Oracle documentation the shrink space can not be applied to compressed tables. I performed next actions
alter table order nocompress;
alter table order enable row movement;
alter table order MODIFY PARTITION SYS_P21674395 nocompress;
....
Also checked the autoextensible for table space
select tablespace_name, file_name, autoextensible from dba_data_files
where tablespace_name='DEV_ODS_DATA'
DEV_ODS_DATA datafile/dev_ods_data.878.1079624941 YES
select * from dba_tab_partitions where table_name='order';
all partitions compression is disabled also
select iot_type,compression from user_tables where table_name='order';
iot_type=NULL; compression=NULL When I trying to perform
ALTER TABLE order SHRINK SPACE compact;
or
ALTER TABLE order MODIFY PARTITION SYS_P21674395 SHRINK SPACE;
I get the next error
ALTER TABLE order MODIFY PARTITION SYS_P21674395 SHRINK SPACE
Error report -
ORA-10635: Invalid segment or tablespace type
10635. 00000 -  "Invalid segment or tablespace type"
*Cause:    Cannot shrink the segment because it is not in auto segment space
           managed tablespace or it is not a data, index or lob segment.
*Action:   Check the tablespace and segment type and reissue the statement
The table does not have FUNCTION based indexes Do the exists approach to shrink space for the table without table recreating?
Banan904 (33 rep)
Feb 13, 2023, 10:03 AM • Last activity: Mar 9, 2023, 12:42 PM
1 votes
1 answers
421 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. The index has had low fragmentation for the last 6 months and it hasn't needed reorganization. The way it's being used hasn't changed either. For the last three weeks every time it runs it reports that the cl...
I am using Ola Hallengren's solution for optimizing indexes. I run it on Sunday every week. The index has had low fragmentation for the last 6 months and it hasn't needed reorganization. The way it's being used hasn't changed either. For the last three weeks every time it runs it reports that the clustered index on the database's biggest table is getting between 5-12% fragmented. Strangely enough this is not occurring during weekdays, so it must be there sometime before the maintenance job runs. My table has a timestamp on every row so I know that the traffic has stayed on the same level for months. Usually it's under 1% fragmentation per month. I have two questions: 1. What else could be fragmenting my index? 2. Is there an automated solution I can set up to track changes? Ideally something not disruptive since this is a production box. The table has 134 columns contains 14gb worth of data, and the primary key is not an identity (*sigh*) The index in question looks like this: * Average row size: 1413 * Depth: 4 * Leaf-level rows: 9884500 * Maximum row size: 2303 * Minimum row size: 746 * Pages: 1904907 * Partition ID: 1 CREATE UNIQUE CLUSTERED INDEX [FOOINDEX] ON [dbo].[FOOTABLE] ( [FOONO] ASC, [ID] ASC ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
Nick Patsaris (113 rep)
Oct 25, 2014, 01:46 PM • Last activity: Jan 24, 2023, 12:08 PM
1 votes
3 answers
598 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 to fix it seems to cause the same problem....self-blocking (I know it's not a big deal on SQL Server 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 to fix it seems to cause the same problem....self-blocking (I know it's not a big deal on SQL Server 2000 SP4 but then a blocking spid of -2). Even something like SELECT count(1) FROM causes this spid(-2) to appear. I went into the table properties and there's about 1.5 million rows which isn't much. I have no problems running the same count statement on much larger sized tables. I did notice the fragmentation is quite high on this table (around 45%) but any attempt to either rebuild or reorganize wreaks havoc on the system. I tried running the reorganize during the weekend for like 8 hours and the job was still running and people were complaining of time-out issues. Then the other day, I tried using the import/export wizard to copy the table to my local instance and it ran fine for the first 250K rows and I was also monitoring sysprocesses and saw the spid for the ETL process. It was fine for those first 250K rows...then the wizard row count wouldn't increment anymore. Then I check the spid on 2K box and it was blocked by spid(-2) again. What the hell is wrong with the table? Is there an integrity issue? The only way to run SELECT statements against it without this happening is to use NOLOCK.
user27810 (61 rep)
Sep 26, 2013, 03:55 PM • Last activity: Dec 15, 2022, 11:03 AM
0 votes
1 answers
6921 views
When to rebuild and when to reorganize indexes
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 t.NAME 'Table name', i.NAME 'Index name', ips.index_type_desc, ips.alloc_unit_type_desc, ips.index_depth,...
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 t.NAME 'Table name', i.NAME 'Index name', ips.index_type_desc, ips.alloc_unit_type_desc, ips.index_depth, ips.index_level, ips.avg_fragmentation_in_percent, ips.fragment_count, ips.avg_fragment_size_in_pages, ips.page_count, ips.avg_page_space_used_in_percent, ips.record_count, ips.ghost_record_count, ips.Version_ghost_record_count, ips.min_record_size_in_bytes, ips.max_record_size_in_bytes, ips.avg_record_size_in_bytes, ips.forwarded_record_count FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') ips INNER JOIN sys.tables t ON ips.OBJECT_ID = t.Object_ID INNER JOIN sys.indexes i ON ips.index_id = i.index_id AND ips.OBJECT_ID = i.object_id WHERE AVG_FRAGMENTATION_IN_PERCENT > 0.0 ORDER BY AVG_FRAGMENTATION_IN_PERCENT, fragment_count The problem is that when i rebuilt indexes the avg_fragmentation_in_percent increased instead of decreasing. Any pointers?? If this is the normal behavior then what am i missing here?? Previously the avg_fragmentation_in_percent was 30 and after rebuild it has increased to 66. enter image description here enter image description here enter image description here enter image description here
user2438237 (141 rep)
Jul 13, 2014, 06:03 AM • Last activity: Dec 9, 2022, 10:03 AM
0 votes
2 answers
487 views
Should I defragment InnoDB table with fixed-length fields?
I am running MariaDB 10.2.13 on Linux (Debian). I am setting up an InnoDB table that will record a lot of data but I will keep only the last one-hour rows. Thus, the number of rows will remain constant. I should expect on this table: - many INSERTs - many UPDATEs - some DELETE (rows > 1 hour) from t...
I am running MariaDB 10.2.13 on Linux (Debian). I am setting up an InnoDB table that will record a lot of data but I will keep only the last one-hour rows. Thus, the number of rows will remain constant. I should expect on this table: - many INSERTs - many UPDATEs - some DELETE (rows > 1 hour) from time to time #Example: Table is defined with **fixed-length fields only** and some indexes. CREATE TABLE tbl_log ( ip int(4) unsigned NOT NULL, date datetime NOT NULL, external_id smallint(6) unsigned NOT NULL, counter smallint(6) unsigned NOT NULL DEFAULT 0, PRIMARY KEY (ip,external_id), KEY external_id (external_id), KEY counter (counter), KEY date_idx (date) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; Inserts (and updates, through on duplicate key) may look like this (*ip* and *external_id* will vary): INSERT INTO tbl_log SET ip = INET_ATON('192.168.1.1'), date = now(), external_id = 123, counter = 0 ON DUPLICATE KEY UPDATE counter=counter+1; Finally, deleting old rows will be done with a query: DELETE FROM tbl_log WHERE date < DATE_SUB(NOW(), INTERVAL 1 HOUR); #Question: **Will such a table fragment over time?** If so, I think I should defragment it. If necessary, I planned to run OPTIMIZE TABLE tbl_log; (with option innodb-defragment=1) right after delete...
Nicolas Payart (2508 rep)
Mar 20, 2018, 02:35 PM • Last activity: Mar 14, 2022, 01:01 PM
2 votes
1 answers
478 views
Does a fragmented database cause the transaction log to grow faster?
Hoping to get some clarity here as I've reached the limits of my SQL knowledge. The company I work for sells products storing data in Microsoft SQL Server databases, but our focus isn't really on the storage and management of this data; we normally leave that for on-site IT to manage. In this case t...
Hoping to get some clarity here as I've reached the limits of my SQL knowledge. The company I work for sells products storing data in Microsoft SQL Server databases, but our focus isn't really on the storage and management of this data; we normally leave that for on-site IT to manage. In this case though, IT don't really do anything and now I have to look at it when I'm more of an application specialist. Our customer ran out of space on the disk they use to store their transaction log. This was resolved by giving more disk space to the drive; however, they're convinced that "the log used to be way smaller and now it's growing much faster than it used to". I understand that the speed at which the transaction log grows is determined the by number of transactions carried out, if any index rebuilds/reorganisations happen etc., but as I mentioned, this customer does **nothing** to manage their database, so I would expect this speed to just reflect how much data they insert but not much else. index size & fragmentation To get to the point, after checking the indexes, I noticed that nearly all of them are over 90% fragmented. I was wondering if this smack to performance could create more logging than usual — or would it just affect the speed at which any queries/transactions execute? I tried viewing the SQL Server logs, but I just run out of memory each time become of the insane amount of errors saying: >The transaction log for database "Name of Database" is full due to "LOG_BACKUP" This stops me from seeing the transaction leading up to this error. **Note** - I can only shrink the database by 9MB (database is around 580GB) - Recovery Model = Full From my own research, I think what I'm seeing is quite normal and the DB just needs better maintenance in general; however, in my industry (industrial manufacturing), databases are seen as a mystical entity no one wants to touch, so I'm trying to find an explanation for what they believe (if any). We have a service and application that adds data to the database over time (collecting data etc.). The server admin doesn't take any backups, reorganize or rebuild any indexes or even check what disk space is free for ever-growing databases. Now a disk was filled and the service was blocked they want to know "why did logs grow so rapidly". I'm realistically just sanity checking to see if the reason due to their lack of maintenance as it seems like a likely cause for increased amount of logging but I don't have the competences to confirm that. Thanks all for any help/guidance and please excuse any inconsistent jargon/noobish points (still self-learning).
Antony Pain (21 rep)
Aug 10, 2021, 10:25 AM • Last activity: Mar 10, 2022, 03:24 PM
3 votes
1 answers
197 views
Is concept of fill factor useful only when index is rebuilt?
Fill factor is concept that works only when creating a new index or when the index is rebuilt. Subsequently there will be fragmentation due to inserts/updates which will create new pages (page split) without the fill factor. Is it correct to say that the concept of fill factor is useful only when th...
Fill factor is concept that works only when creating a new index or when the index is rebuilt. Subsequently there will be fragmentation due to inserts/updates which will create new pages (page split) without the fill factor. Is it correct to say that the concept of fill factor is useful only when the index rebuilt?
variable (3590 rep)
Feb 10, 2022, 08:53 AM • Last activity: Feb 11, 2022, 01:55 PM
0 votes
3 answers
991 views
Will I need to fix fragmentation after shrinking a database full of only HEAPs?
I have a 1 TB database that contains hundreds of quarterly archive tables and I am looking to drop any table older than 5 financial quarters, which will free up ~600gb of space. To provide an example of what the table architecture looks like: myTable myTable_Q4_21 myTable_Q3_21 myTable2_Q4_21 myTabl...
I have a 1 TB database that contains hundreds of quarterly archive tables and I am looking to drop any table older than 5 financial quarters, which will free up ~600gb of space. To provide an example of what the table architecture looks like: myTable myTable_Q4_21 myTable_Q3_21 myTable2_Q4_21 myTable2_Q3_21 I am wondering if after dropping these tables and shrinking the database to reclaim the newly freed up space if I will need to either ALTER..REBUILD each remaining HEAP table or put a CX on each table and remove them afterwards.
Data Dill (255 rep)
Jan 19, 2022, 01:18 PM • Last activity: Jan 20, 2022, 11:33 AM
1 votes
1 answers
1866 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: 1. Normally defrag the partition while the service is still running until the only fragmented files...
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: 1. Normally defrag the partition while the service is still running until the only fragmented files are the database files. 2. Enter single user mode. 3. Make complete backup to a different partition. 4. Delete the database with fragmented MDF file. 5. Create/restore new database from the recently created backup. Will dropping database and then restore from backup remove the file fragmentation? Or should I just turn off the service and run defrag tool until it finishes?
Endy Tjahjono (381 rep)
Dec 13, 2012, 03:42 AM • Last activity: Jan 14, 2022, 09:00 AM
44 votes
4 answers
57341 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 ' + name + ' REBUILD;' FROM sys.tables And then copying and pasting the output to a new query window and running that. I g...
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 ' + name + ' REBUILD;' FROM sys.tables And then copying and pasting the output to a new query window and running that. I got no errors, but I still have fragmentation. I tried running both commands separately too and still have fragmentation. **Note:** I have been made aware that REORGANIZE is unnecessary by Aaron, and I'm aware I could use dynamic sql to automate this. I ran this to determine I still have fragmentation: SELECT * FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, NULL) WHERE avg_fragmentation_in_percent > 0 And I got: | database_id | object_id | index_id | partition_number | index_type_desc | alloc_unit_type_desc | index_depth | index_level | avg_fragmentation_in_percent | fragment_count | avg_fragment_size_in_pages | page_count | avg_page_space_used_in_percent | record_count | ghost_record_count | version_ghost_record_count | min_record_size_in_bytes | max_record_size_in_bytes | avg_record_size_in_bytes | forwarded_record_count | compressed_page_count | ----------- | --------- | -------- | ---------------- | --------------- | -------------------- | ----------- | ----------- | ---------------------------- | -------------- | -------------------------- | ---------- | ------------------------------ | ------------ | ------------------ | -------------------------- | ------------------------ | ------------------------ | ------------------------ | ---------------------- | --------------------- | 85 | 171147655 | 1 | 1 | CLUSTERED INDEX | IN_ROW_DATA | 2 | 0 | 36.3636363636364 | 5 | 2.2 | 11 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 85 | 421576540 | 1 | 1 | CLUSTERED INDEX | IN_ROW_DATA | 2 | 0 | 75 | 7 | 1.14285714285714 | 8 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 85 | 965578478 | 1 | 1 | CLUSTERED INDEX | IN_ROW_DATA | 2 | 0 | 14.7058823529412 | 6 | 5.66666666666667 | 34 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 85 | 1061578820 | 1 | 1 | CLUSTERED INDEX | IN_ROW_DATA | 2 | 0 | 40 | 4 | 1.25 | 5 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 85 | 1109578991 | 1 | 1 | CLUSTERED INDEX | IN_ROW_DATA | 2 | 0 | 30.7692307692308 | 5 | 2.6 | 13 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 85 | 1205579333 | 2 | 1 | NONCLUSTERED INDEX | IN_ROW_DATA | 2 | 0 | 50 | 5 | 1.6 | 8 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 85 | 1493580359 | 1 | 1 | CLUSTERED INDEX | IN_ROW_DATA | 2 | 0 | 50 | 6 | 1.66666666666667 | 10 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | I know I am missing something real basic, but I don't know what.
Justin Dearing (2727 rep)
Sep 2, 2011, 08:57 PM • Last activity: Dec 23, 2021, 01:22 AM
0 votes
2 answers
220 views
I think SQL Docs are wrong for sys.dm_db_index_physical_stats
Go to this documentation page: [https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-index-physical-stats-transact-sql?view=sql-server-ver15][1] Then go to the index_level column explanation: > The nonleaf levels of indexes are only processed when mode...
Go to this documentation page: https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-index-physical-stats-transact-sql?view=sql-server-ver15 Then go to the index_level column explanation: > The nonleaf levels of indexes are only processed when mode = DETAILED. Now go to the Scanning Modes section: > The LIMITED mode is the fastest mode and scans the smallest number of pages. For an index, only the parent-level pages of the B-tree (that is, the pages above the leaf level) are scanned. For a heap, the associated PFS and IAM pages are examined and the data pages of a heap are scanned in LIMITED mode. > > With LIMITED mode, compressed_page_count is NULL because the Database > Engine only scans non-leaf pages of the B-tree and the IAM and PFS > pages of the heap. The scanning modes section is wrong, right? Limited mode only scans leaf pages. Let me know your thoughts. I wanted to check with the community before posting it to the proper channels.
Victor Barajas (363 rep)
Nov 9, 2021, 01:05 AM • Last activity: Nov 10, 2021, 08:10 AM
Showing page 1 of 20 total questions