Note that PostgreSQL uses the term "clustered index" to use something vaguely similar and yet very different to SQL Server. name AS index_name, i. If running into a particular performance issue, Microsoft PSS will sometimes recommend a manual full reindex. mdf file size: your disk have to have space to re-allocate a 35-gig file, in order to defragment it. Reorganize/Rebuild SQL Server database indexes using ApexSQL Defrag ApexSQL Defrag is a 3rd party tool made specifically to automatically fix index fragmentation in SQL Server databases. Reorganizing an index only deals with the fragmentation that exists, and the more fragmentation there is, the longer a reorganize will take. This point here is extremely important. The Server is running SQL Server 2017 (RTM-GDR) (KB4505224) - 14. @tblName specifies if you only want to defrag indexes for a specific table. So now, we have identified all the fragmented indexes, how to defrag them? That is our next blog 😊. Azure SQL Database is a PaaS solution for migrating your on-premises databases to the Azure cloud infrastructure. Example: Name: Indexrebuild_job; Description: RDS for SQL server agent job for index rebuild. Double Click on one of the SQL. Like SQL Server Management Studio, you run Tuning Advisor from a client computer that connects to your Amazon. If you don't want to grant so granular. ApexSQL Defrag is a powerful application used for scheduled analyzing and defragmenting of SQL Server database indexes. The disks defrag OK, and SQL is not harmed - this is the result that should be expected. Two types of fragmentation can afflict SQL Server indexes and consequently the performance of your databases. [index_id] , idx. Normal Windows defrag cannot defrag open files. It is designed specifically to overcome the compromises DBAs have to make regarding the important task of index fragmentation maintenance. OBJECT_ID) + '] REBUILD; ' END, 'GO' FROM sys. It automates the time-consuming process of finding fragmented indexes based on policies for the targeted databases. In order to begin, proceed to Manage Index. Paul pointed out a couple of other points. We would like to show you a description here but the site won’t allow us. In our previous blog post, we got to know, how to find out all fragmented indexes in a database. Create a execute sql task and schedule it through sql agent . Rob Sheldon tackles the subject of Index Defragmentation in SQL Server 2005 and 2008, using the sys. Weekly – 3% fragmented. This can be checked using:. Now we will see how to defrag indexes. Missed Connection: Employer Seeking Employee. A non-clustered index is the other main type of index used in SQL Server. There are several options that allow you to specify the level of. This is called fragmentation. The tempdb is used a lot – place it on separate drive to the Log and MDF files. database_id = 6. This common maintenance task actually causes fragmentation, which is especially terrible if you’re doing both this and defragmenting your indexes. 1. The index fragmentation is the index performance value in percentage, which can be fetched by SQL Server DMV. In the SQL Server index defrag Job Properties window, browse to the Notifications tab and check the box beside the Email to send email notification using the created Database Mail when the job failed or completed successfully to the operator, based on your requirements, then click OK to apply the change on the job, as below:. Review CREATE INDEX. Each option had its pros and cons, and you need to know which option works best for your environment. Find and remove unused indexes – everything that is unused doesn’t do anything good. DBCC SHRINKFILE does what the name implies. Viewed 439 times. I want to defrag tables in sql server, I used this code to know the fragmented indexes: sELECT dbschemas. Rob Sheldon tackles the subject of Index Defragmentation in SQL Server 2005 and 2008, using the sys. Defragmentation utilities may lock sections of the file, preventing SQL Server from completing a read or write operation. Let it autogrow with at least 200 MB at the time. 1 Answer. This REBUILD option is available in SQL Server 2008 onwards. Speaking for SQL Server, I tend to choose a index size and index fragmentation level at which point I begin performing index maintenance. Why do you think defragging is an operation eligible for minimal logging? I don. You can also change this threshold via parameters. It creates a thread that runs periodically to detect when the database has more than 25% free disk space allocated to it. I'd rebuild stats every night at least. Daily – 6% fragmented. Instead of our phone book having 1,000 pages that are 100% full, we might have 1100 pages that are only 90% full. type_desc AS index_type, ips. and a weekly rebuilding of indexes. Reorganizing an index only deals with the fragmentation that exists, and the more fragmentation there is, the longer a reorganize will take. For database optimization, we recommend using an index and statistics maintenance script. The re-inserting of the data in PK order assures that the data is well-defragmented. SQL Server Agent job alert configuration. It has been proven that Online Index Rebuild – Can Cause Increased Fragmentation when it is allowed to run with MAX DOP > 1 and ALLOW_PAGE_LOCKS = OFF directives. 16 years ago. The requirement is that we defragment indices (on SS2K tables that are log ship destination). It’s advisable to remove those often. To reorganize index SQL Server, right-click it & choose Reorganize. Indexes with small number of pages (<1000) will usually. Hi, The widely used thresholds are: Reorganize if fragmentation is between 10-30%. If you have space constraint on the Server then you may need to Shrink the db. For partitioned indexes, sys. Q1: None. It will do: Nothing if the fragmentation level is below 5%;. When creating a SQL Server index, there is an option to create the index with a certain amount of free space on each data page. He makes the point that, by analyzing indexes effectively, you can save a lot of unnecessary rebuilding of indexes. SQL Server Internal Fragmentation SQL Server Internal Fragmentation is caused by pages that have too much. Daily – 6% fragmented. Chad Boyd. Fragmented indexes will primarily slow down ‘search’ performance. July 17, 2012 at 12:16 pm. This will block all queries to that table using the index till the index defrag is finished. Index names must follow the rules of. 25. It’s like a looking at the results of a developer writing SQL after three Red Bulls – it doesn’t make any sense, and you just want to wad it up and throw it away. 2. SQL Server allows us to create only one Clustered index per each table, as the data can be sorted in the table using one order criteria. The link points to SQL Server 2014 docs, but the syntax should work on 2005 and 2008 as well. avg_page_space_used_in_percent, ips. (directly or using the alias) requires that the Java class can be reached by the database (server). You also can create a linked server to SQLAZURE and create a sql agent job. lang. I have a plan that I am not sure will work: Normally defrag the partition while the service is still running until the only fragmented files are the database files. If an index is between 10% and 30% fragmented, I will REORGANIZE the index and UPDATE the statistics. This statement defragments the leaf level of the index so that the physical order of the index pages match the left-to-right logical order of the leaf nodes. Proceed to the Steps tab: Clicking on New. 0. Indexes with small number of pages (<1000) will usually. Since we updated from SQL Server 2008 to 2019, the queries have become much slower. Automate defragmentation policies to multiple databases, tables, and indexes. NAME + '] ON [' + Object_name(ind. Odds are, you’re not actually making things better – you’re making them worse! I explain why in this video: Why Index Fragmentation Doesn't Matter in SQL Server. ApexSQL Defrag is a SQL Server index monitoring and analysis 3 rd party tool that is used to monitor and manage SQL index defragmentation operations in SQL Server. Share. I'd consider doing a LOB_COMPACTION less frequently but both options comes down to your maintenance windows duration pretty much. It has its own disk space and is arranged in a B-tree structure. 1. Contig can defragment SQL Server database and log files while the databases are online, but there is some performance cost (whether or not the. According to Microsoft guidelines, based upon index fragmentation percentage, we may choose to either REORGANIZE,. dm_db_index_physical_stats ) function accepts parameters such as the database, database table, and index for which you want to find fragmentation. index_type_desc AS IndexType, qs. SQL Server 2005 and later provides a dynamic management function (DMF) to determine index fragmentation level. The wise use of indexes is a proven method of improving database performance. . [name] as 'Table', dbindexes. "SQL Fool") has an automated index. Your could find your indexes are 95% plus fragmented, affecting query performance badly. So we defragment and build indexes twice a week. It can be turned on by using the in-app wizard in any version. The defragmentation schedule must be created manually in all versions. Identify and remove index fragmentation – this is obviously what we have been talking until now and the biggest part of the SQL index maintenance. Consequently, the fragmentation adversely affects the performance of SQL Server. If not specified, all non-system databases plus msdb and model will be defragmented. However, over time, indexes will get fragmented, which can severely impact server performance. Bad internal fragmentation (having lots of free space on the pages) means the index is bigger than it needs to be. Defragmentation in Practice. It creates a thread that runs periodically to detect when the database has more than 25% free disk space allocated to it. In this case Reorganize option is selected: Databases with no index defragmentation were an average of 5% fragmented. We need to identify all the indexes that are fragmented and defrag them to achieve better performance. Yes, rebuilding indexes will take a toll on your transaction log file. @operation: {all, index, statistics} (no default) statistics : will run only statistics update. SQL Server will by default perform a NOTRUNCATE which will move data pages from the end of the file to any free space at the beginning of the database data file. Fragmentation. If you are experiencing slow full-text query performance, you can use sys. REBUILD WITH (ONLINE=ON) some of them still remain fragmented. This index uses column-based data storage and query processing to achieve gains up to 10. From this tab, select a SQL Server instance in the server explorer on the left and choose database (s) as a target of index operation. We have several databases in which a large number of tables are created and dropped. The automation built into SQL Defrag Manager enables you to implement a "setThis is one of the optimizations SQL Server does when. Paul Randal. It can be turned on by using the in-app wizard in any version. December 8, 2009. After the index rebuilds, query was way faster when comparing to clean buffer caches. In order to solve this problem, it is necessary to start with analyzing the index and evaluating the degree of its fragmentation. It does exactly that if ON. The database will be shrunk leaving it. Or with other fill factors I tested: - Setting a Fill factor of 80 on the clustered index (Fragmentation > 98%. So, how to identify the fragmentation of all indexes in a database? Here is the query. Yup. Yes, rebuilding indexes will take a toll on your transaction log file. Pre-allocate space for tempdb (approximately 20-25% of the size of MEM’s database) Set the file growth increment to a reasonable size. If not specified, all non-system databases plus msdb and model will be defragmented. This to me would be a sign that constant rebuilds of that index actually aren't a good help. Even though it should be safe, I would not want to defrag disks in Production while SQL is running. It depends on the degree of fragmentation of Indexes. Q2, Q3: OPTIMIZE TABLE rebuilds the table by CREATEing a new table and INSERTing all the rows, then RENAMEing and DROPping. In addition, SQL Server offers several different ways to perform index defragmentation. 0. [object_id] AND [ips]. DBCC DBReIndex – 356610 ms (357 secs or just under 6 minutes to complete). go. 25. We have a database with 2 tables, one with hundreds of millions of rows (row size<1KB), another 14 million rows. An SQL index is a small and organized lookup table, allowing the SQL Server engine to quickly locate the requested rows of a database table without scanning the entire table. Create a execute sql task and schedule it through sql agent . Paul’s answer to the question was very practical and dealt with the wasted space utilization that excessive index fragmentation can cause in a database that uses a uniqueidentifier column with newid () as the primary key, and given the cost per gigabyte for SSD storage this could be quite significant. With spinning disks, this can be bad. The best recommendation I have is to create your own procedure which loops through the tables of the database and runs DBCC INDEXDEFRAG. Custom SQL Server Index Defrag and Rebuild Procedures. A new window. Defragmentation in Practice. On the right panel, right-click SQL Server Agent. Defragment indexes on your SQL SCCM database every night. Sc (Comp Sci) SQL In The Wild : Discussions on DB performance with occasional diversions into recoverability We walk in the dark places no others. index_id] ,SI. (Look in the Misconceptions blog category for the rest of the month’s posts and check out the 60-page PDF with all the myths and misconceptions blog posts collected together when you join our Insider list, plus my online Myths and. This common maintenance task actually causes fragmentation, which is especially terrible if you’re doing both this and defragmenting your indexes. Here are the changes: Added support for database and object names with special characters. That article is in-depth and difficult to summarise in this reply, but it comes down to creating an asynchronous. To rebuild or reorganize indexes manually, head over to the Fragmentation tab. An index rebuild will always build a new index, even if there’s no fragmentation. No matter how many times I run the script to defrag these records, I still have the same indexes to be defraged and most of them with the same. page_count, ips. if the index has: less than 5% logical fragmentation, don’t do anything. 1. Analysing the results of both the DBCC DBReIndex and the Alter Index Rebuild the percentage of fragmentation after the command was run is the same but there is a big difference in the amount of time to complete the operation, nearly 1/3. Less bad with modern disk subsystems (which is why we focus less on defrag nowadays). In short, defragmentation is essential for performance improvement of SQL. If an index contains less than 100 pages, I will perform no maintenance. This optimizes and speeds up file access. Indexes were 75% fragmented. 1. The results for this command are up for comparison against the Alter Index Rebuild. In order to begin, proceed to Manage Index. In this article, I will provide a mechanism for automating SQL Server index defragmentation implemented in a database used in the company I work for. As of version 2020. There are two types of fragmentation: Internal Fragmentation and External Fragmentation. avg_fragmentation_in_percent as Fragmentation, indexstats. Expand the table on which you want to reorganize an index. The procedure CommandExecute also has to be installed in order to use the index optimize. ApexSQL Defrag tool is a 3 rd party SQL index defragmentation tool that you can easily use to perform number of operations related to the SQL Server indexes. ; @dbScope specifies a database name to defrag. Internal fragmentation means there is free space inside the data pages rather than having them fully packed with row data. Contact the manufacturer of the defragmentation tool to learn how files are locked and how this could affect SQL Server concurrency. If you what you are saying is true, even. SQL Server A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions. Enable Fragmentation Manager through the right-click context menu of any instance or by opening the Indexes tab of SQL Sentry and selecting Enable Now. Having said that, fragmented data can produce unnecessary reads thereby reducing. 59. I would highly recommend using a tool specific to sql server to defragment the drive(s) on which the files sit. DBCC INDEXDEFRAG defragments the leaf level of an index so that the physical order of the pages matches the left-to-right logical order of the leaf nodes, thus improving index-scanning performance. With both SQL 2000 and SQL 2005 and later versions you can get the fragmentation information by using the DBCC SHOWCONTIG command. ; @tblName specifies if you only want to defrag indexes for a. The sys. Select Steps, and then select New to add an execution step. So let’s take one thing at a time. Index fragmentation can cause high CPU usage, which means disk usage, and therefore performance degradation. This should be part of our SQL Server Maintenance jobs. Index fragments occur when any transaction takes place in database table. Logical fragmentation happens when an index leaf page is full and space is required on it, either for an insert or to make an existing record longer (from updating a variable-length column). This white paper provides information to help you determine whether you should defragment table indexes to benefit workload performance. Over time, the indexes created for an SQL Server database can become fragmented. It gives you the flexibility to reduce the SQL fragmentation immediately or during off-peak hours, all. SQL - defragmenting an index on a compressed table. SQL Server allows the creation of multiple indexes for rapid data search and access operations in the. On versions prior to SQL Server 2005 you can defrag standard indexes using the DBCC INDEXDEFRAG command:For a linked list (a B-tree index), than can cause SQL Server to "jump back and forth" in the database file when it read lots of pages (a scan or partial scan). It’s a tool that simplifies the management of SQL Server indexes across your organization. In older versions of SQL Sentry, this feature is disabled by default. I made them up. Monthly – 17% fragmented. while exists (select top 1 Reorganize_SQL from @fragmented_indexes) begin. This may affect the concurrency of the server that's running SQL Server. The length of time the rebuild takes is related to the size of the index, not the amount of fragmentation in it. If you’re a production database administrator responsible for backups, corruption checking, and index maintenance on SQL Server, try Ola Hallengren’s free database maintenance scripts. For those unfamiliar with this object, it defragments indexes in SQL Server 2005 using the ALTER INDEX command. Note that a heap index number will be zero. This clause is equivalent to DBCC INDEXDEFRAG. There's a general consensus that you should reorganize ("defragment") your indices as soon as index fragmentation reaches more than 5 (sometimes 10%), and you should rebuild them completely when it goes beyond 30% (at least that's the numbers I've heard advocated in a lot of places). It is very common to see big increase in performance after optimizing your SQL indexes. If you have fragmentation, you need to REBUILD or REORGANIZE. Identify Skewed and Outdated Index and Column Statistics and Ensure they are Representative and Up-to-DateAs stated though you would need to shutdown SQL Server, or at least take the DB offline to do an OS Defrag. Kalman, When you use keyword ALL you do not specify particular index name instead you just specify table name. If you are seeing this behavior, then its better to use ( MAXDOP 1) ( serial index rebuild) at query level when. Here are some steps you can follow to free up some space in a hurry. Lock durations are short and will cause minimal blocking of queries. Here is a. SQL SERVER – 2005 – A Simple Way To Defragment All Indexes In A Database That Is Fragmented Above A Declared Threshold. Create/restore new database from the recently created backup. Reorganizing an index defragments the leaf level of an index by physically re-ordering pages to match the logical order. Results still shows high fragmentation, even the application is only used for 2 to 3 days after defragmentation. SQL SERVER – Sharpen Your Basic SQL Server Skills – Learn the distinctions between unique constraint and primary key constraint and the easiest way to get random rows from a table Next Post SQL SERVER – Simple Example of Cursor – Sample Cursor Part 2 This causes a lot of free space or, as you call it, fragmentation. [index_id] = [idx]. In this article. Here's one from MSDN:--This example shows a simple way to defragment all indexes in a database that is fragmented above a declared threshold. Stats will not be updated if you perform a rebuild. SQL Defrag Manager is a powerful solution that automates and optimizes your SQL Server database defragmentation. 2. Script to Manage SQL Server Rebuilds and Reorganize for Index Fragmentation. Often, this is not enough to perform index. Open the Tables folder & expand it. In that case, the Storage Engine creates a new, empty page and moves 50 % of the rows (usually, but not always) from the full page to the new page. In the Rebuild Indexes dialog box, verify that the correct index is in the Indexes to be rebuilt grid and click OK. Reorganizing the indexes: ALTER INDEX ALL ON dbo. You don't mention what tool you're using to defrag the disk, I supose it's the standard Disk Defragmenter. Like Ola's scripts, mine only defrag indexes that need it. Logical fragmentation happens when an index leaf page is full and space is required on it, either for an insert or to make an existing record longer (from updating a variable-length column). This article shows an example of how to manually perform defragmentation on an index using SQL Management Studio. Paul Randal. To test this you can comment out (or remove completely) the line second from the bottom with two minus signs (--). When auto-index defragmentation is enabled in Orion, databasemaintinence will sometimes fail to defragment highly fragmented indexes will get defragmented during the Orion database maintenance, which runs at 2:15 AM by default. We have a Non-Clustered index on the varbinary field and our index maintenance scripts perform a REORGANIZE on this every 2-3 days. Databases with no index defragmentation were an average of 5% fragmented. Check the comment header block for its documentation. Use the ALTER TABLE command to rebuild the Heap. record_count > 0 AND o. Script to Manage SQL Server Rebuilds and Reorganize for Index Fragmentation. If only table_id is specified or if index_id is 0, the data pages of the specified table are scanned. Added support for indexes with the ALLOW_PAGE_LOCKS option. 0. Artemakis Artemiou. Right-click on the index that you need to check and choose the Properties option. This schema modification lock blocks all other concurrent access to the table, but it is only held for a very short period of time while the old index is dropped and the statistics updated. Location : HKEY_LOCAL_MACHINESOFTWAREMicrosoftWindows Search. As administrators will know, identification of fragmentation and of the same are crucial elements in database . As of version 2020. You’d have to set this up on your own. Watch this video by List the Indexes. Indexes were 75% fragmented. The name of the index. Defragmentation utilities may lock sections of the file, preventing SQL Server from completing a read or write operation. Paul Randal. So if I understand, with the fragmented indexes, SQL Server must go all around the Disk to find the pages it needs. Index Rebuild vs Index Reorganize. I teach the MS 2073 class quite a bit and the course material just. Some things I've tried: Rebuilding the indexes: ALTER INDEX ALL ON dbo. Enter a Name and Description for the agent job, and then select OK. A new window. 1 Answer. These scripts are widely tested in the community and are much flexible so that you. Missed Connection: Employer Seeking Employee. Fragmentation typically happens owing to insert, update and delete transactions. Aaron Bertrand April 13th, 2009 SQL Server 3. It's not free, so if you're. This article covers one of main tasks for a proactive database administrator through monitoring different activities happening in SQL Server instances. For an example, see the SQL Server index and statistics. To check for index fragmentation using SSMS, open Object Explorer and navigate to the index you want to check. According to. For the SQL Servers we manage for our clients, we use. The fill factor determines the amount of empty space on each page in the index, to accommodate future expansion. Defaults to 480m (8h). This tip provides a fully parameterized T-SQL script which identifies only the fragmented indexes in a database or in all databases in a SQL Server instance, and either generates a report with the defragmentation commands for review and controlled execution, or directly reorganizes or rebuilds the fragmented indexes based on Microsoft's. dm_ db_ index_ physical_ stats (in 2005+). Kalman Toth, SQL Server & BI Training; SQL 2008 GRAND SLAM. x) Service Pack 2, executing DBCC CHECKDB clears the plan cache. The reason why too many page splits can decrease the performance of SQL Server is because of the large number of I/O operations. According to the index performance value, users can take the indexes in maintenance by revising the fragmentation percentage with the help of Rebuild or Reorganize operation. With ApexSQL Defrag tool the SQL index fragmentation of SQL Server instance can be easily monitored by DBA’s. There is all reason to only rebuild index that are fragmented, and a good maintenance. It appears to be a bug in 2012 R2. #1637994. To work around a very large index that won't defragment, the BEST solution is to pre-size a new database and move all your objects to that DB. Rebuild the NCIs. This job needs to be scheduled and ran on a weekly basis due to. DBREINDEX = REBUILD option "Rebuilding Indexes". Columnstore Online Index Rebuilds – Columnstore indexes need more attention to maintenance. April 29, 2010. Remarks. VLF Fragmentation inside of SQL Log files. 1. The. This means you can grant alter table on every table of interest that already exists. This article serves as a guide as to how to automate and schedule SQL Server index defragmentation, and covers three different ways of doing it. A starting point could be looking at the queries being executed on your server. It is very common to see big increase in performance after optimizing your SQL indexes. Using SQL Server Managed Lock Priority for Online Index Rebuilds. It is designed specifically to overcome the compromises DBAs have to make regarding the important task of index fragmentation maintenance. He makes the point that, by analyzing indexes effectively, you can save a lot of. It's. Here are the options that I came up with: Expand the 1st file in the PRIMARY filegroup by ~28GB (7 files x 4GB) Move the data off each of the successive files and mark them for deletion. This database has a few hundred tables and each table has a range of 1 to 15 indexes per table. Two additional methods to defragment physical database and log files are backup/restore or detach-move-attach, and both require down-time. This mechanism allows defragmenting required indexes on a regular basis since index fragmentation takes place constantly in the 24/7 system. The Ultimate Index Width Stats Reporter. Azure Automation: Automate Azure SQL Database indexes and statistics maintenance. 1 For a memory-optimized Nonclustered index, the full key is not required to perform an index seek. , Indexes: "timezone_description_pkey" PRIMARY KEY, btree. Fragmentation typically happens owing to insert, update and delete transactions. Over time these indexes become fragmented so that ordered data retrieval becomes less efficient. For maintenance purposes, you can use Azure Automation and create a recurring script that checks for fragmented indexes and optimizes it. The SYS. 1. Any full-text indexes of non-trivial size with fragmentation of at least 10% will be flagged to be re-built by our over-night maintenance. 4 Answers. 8. Note: In versions 2020. Improve this answer. 3. You have the option LOB_COMPACTION. #1637994. The DBCC SHOWCONTIG statement traverses the page chain at the leaf level of the specified index when index_id is specified. We have several databases in which a large number of tables are created and dropped. The database will be shrunk leaving it. To optimize read-only workloads on the readable secondary replicas, you may want to create indexes on the tables in the secondary databases. If. When we were listing the fragmanted indexes our scope was on the indexes that are used in expensive SQL Statements. The Ultimate Index Width Stats Reporter. But over a period of time, because of DML operations (inserts/deletes/updates), the data will get spread across the disc (Physical order of pages are NOT in sequence). Veritas now uses the heavily debated command DBCC SHOWCONTIG WITH ALL_INDEXES,or DBCC SHOWCONTIG WITH ALL_INDEXES,tableresults (Table results will give you a table format to read) to. The other thing you can do, for when you do need to defragment indexes (if for example you have deleted a large amount of data), is using SQL Server's Service Broker to set up the maintenance plan for multiple tables in a batch. We have tried this but it does not work: Amend registry value. The SAMPLED mode returns statistics based on a 1 percent sample of all the pages in the index or heap. This is because the doc set was reorganized for the SQL Server 2012 release, and not all topics/categories made it. Then, choose New, Job to create a SQL agent job. This may affect the concurrency of the server that's running SQL Server. But this takes. Heaptest default values go 50 select index_type_desc,avg_fragmentation. If it grows often, it could concievably become fragmented. There are three options to remove fragmentation in heaps: Create a clustered index and drop it once it is completed. Let it autogrow with at least 200 MB at the time. 1. To create SQL database maintenance plan, launch SQL Server Management Studio > expand the database instance > Management > right-click on the maintenance plan > New Maintenance Plan. Maintenance. Veritas now uses the heavily debated command DBCC SHOWCONTIG WITH ALL_INDEXES,or DBCC SHOWCONTIG WITH ALL_INDEXES,tableresults (Table results will give you a table format to read) to monitor the health of the table indexes when the use of the sys. In Object Explorer, Expand the database that contains the table on which you want to reorganize an index. There are several types of fragmentation that can occur. A. OBJECT_ID) AS TableName, fq.