DBCC SHOWCONTIG

Show contig on it's own will show results for all tables in the database, this could take some time. I don't recommend doing so in the production environment, since DBCC SHOWCONTIG has to scan a table/index to determine the fragmentation level and is therefore a time-consuming operation.

You can also export the results to a database table for further script parsing

DBCC SHOWCONTIG (tabletoshowcontig) WITH TABLERESULTS

Note: Not shure what it names the database, will have to test

To show contig on one table use DBCC SHOWCONTIG ('table')

DBCC SHOWCONTIG scanning 'authors' table...
Table: 'authors' (1977058079); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned................................: 1
- Extents Scanned..............................: 1
- Extent Switches..............................: 0
- Avg. Pages per Extent........................: 1.0
- Scan Density [Best Count:Actual Count].......: 100.00% [1:1]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 0.00%
- Avg. Bytes Free per Page.....................: 6009.0
- Avg. Page Density (full).....................: 25.76%
DBCC execution completed. If DBCC printed error messages, contact your system...

The most important values are logical fragmentation and scan density. The scan density value shows the ratio of best count of pages (the ideal scenario) over the actual count of pages in all extents (an extent is a group of eight data or index pages). Therefore, if the scan density returns 100%, there is no fragmentation. The logical fragmentation value, on the other hand, returns the percentage of pages that are out of order in the leaf level of the index. I won't to go into details of what this can mean, but the short version is that if logical fragmentation is other than zero or scan density is less than 100%, your index has some level of fragmentation and needs help.

As data is added, modified, or removed from the table, indexes get fragmented. As the level of index fragmentation grows, the effectiveness of the index decreases proportionately. To keep your data-retrieval operations as efficient as possible, it's crucial to keep index fragmentation to a minimum. There are a couple of ways of removing fragmentation

1. Drop and re-create an index. If multiple indexes are affected on the same table, you can drop and re-create the clustered index, which will cause all non-clustered indexes to be rebuilt as well. You can use DBCC DBREINDEX with a table name to rebuild one or multiple indexes on the specified table. Alternatively, you can use CREATE INDEX ... WITH DROP EXISTING syntax to rebuild an index. All three of these methods provide comparable performance.
2. Run the DBCC INDEXDEFRAG statement against the affected index. This statement reorders the leaf-level pages of the index to remove fragmentation. DBCC INDEXDEFRAG is an online operation-in other words, it doesn't prevent users from reading and writing to a table. On the other hand, dropping and re-creating an index locks the table and prevents anyone else from using it. The drawback of DBCC INDEXDEFRAG is that it doesn't do as good a job as the complete rebuild of an index.

As I mentioned earlier, there are few ways to get rid of fragmentation. Some environments allow the luxury of a maintenance window in which you can drop and re-create indexes on a regular basis. In a 24/7 shop you try to avoid such activities, since dropping and re-creating indexes takes a long time and uses much transaction log space.

Instead it is recommended to re-creating only those indexes that have a high level of fragmentation (over 40%), defragmenting the ones that are moderately fragmented (15-40%) and not touching those that are less than 15% fragmented.

To automate the process of index maintenance, write a procedure that does the following:

1. Populate a temporary table with all user table names.
2. Loop over the temp table with a cursor to get a table name.
3. Get the names and keys of each index on this table.
4. Run DBCC SHOWCONTIG ... WITH TABLERESULTS on each index and store the results of this statement in a second temporary table.
5. Examine the values of scan density and logical fragmentation in the second temporary table.
6. Apply the logic in the paragraph above to rebuild or defragment each index.

This procedure will save tons of transaction log space and much maintenance time versus rebuilding all indexes from scratch.

Minimizing index fragmentation will help you to maintain a high level of performance. However, such time-consuming tasks should only be performed during the off-peak hours. In addition, if you're trying to provide a high level of uptime, you probably can't afford to run such jobs more than once a week. Fortunately, you can perform one activity on a regular basis to help your indexes: updating the statistics.

The index statistics inform SQL Server about the distribution of key values in the index. If statistics are outdated, SQL Server might not be able to evaluate index effectiveness appropriately, and might choose to scan a table instead of scanning the index. Up-to-date statistics guarantee effective use of indexes. SQL Server can update statistics automatically, but doing so slows down performance a bit. Alternatively, you can schedule a job that executes the UPDATE STATISTICS statement or executes the system procedure sp_updatestats. The difference between the two is that UPDATE STATISTICS requires the table (view) or index name and performs an update on the specified object. The sp_updatestats procedure simply runs through a cursor containing all user tables and runs UPDATE STATISTICS on each. Updating statistics on large tables with multiple indexes might take a long time, so generally it's better to run UPDATE STATISTICS on such tables separately from the rest of the tables. For instance, run UPDATE STATISTICS on small tables on Monday, Wednesday, and Friday and do the same on large tables on Tuesday, Thursday, Saturday, and Sunday.

You might think that you should keep the AUTOSHRINK option turned on. Please don't try this in a production environment! The AUTOSHRINK option slows down database performance significantly, since SQL Server has to continuously monitor the disk space usage and free up the space every time data is deleted. You're much better off turning off this option and shrinking database files manually as needed.

You can view the current settings through the database properties in SQL Enterprise Manager (SEM) or you can run this Transact-SQL command:

sp_helpdb [ [ @dbname= ] 'name' ] 

If you combine the autogrow and autoshrink options, you might create unnecessary overhead. Make sure that the thresholds that trigger the growth and shrink operations will not cause frequent up and down size changes. For example, you may run a transaction that causes the transaction log to grow by 100 MB by the time it commits. Some time after that the autoshrink starts and shrinks the transaction log by 100 MB. Then, you run the same transaction and it causes the transaction log to grow by 100 MB again. In that example, you are creating unnecessary overhead and potentially creating fragmentation of the log file, either of which can negatively affect performance.

Physical fragmentation from changing the size of the data or log files can have a severe affect on your performance. This is true whether you use the automatic settings or whether you manually grow and shrink the files frequently.

If you grow your database by small increments, or if you grow it and then shrink it, you can end up with disk fragmentation. Disk fragmentation can cause performance issues in some circumstances. A scenario of small growth increments can also reduce the performance on your system.

For a managed production system, you must consider autogrow to be merely a contingency for unexpected growth. Do not manage your data and log growth on a day-to-day basis with autogrow.

You can use alerts or monitoring programs to monitor file sizes and grow files proactively. This helps you avoid fragmentation and permits you to shift these maintenance activities to non-peak hours.

AutoShrink and autogrow must be carefully evaluated by a trained Database Administrator (DBA); they must not be left unmanaged.

Your autogrow increment must be large enough to avoid the performance penalties listed in the previous section. The exact value to use in your configuration setting and the choice between a percentage growth and a specific MB size growth depends on many factors in your environment. A general rule of thumb to you can use for testing is to set your autogrow setting to about one-eight the size of
the file.

Turn on the <MAXSIZE> setting for each file to prevent any one file from growing to a point where it uses up all available disk space.

Keep the size of your transactions as small as possible to prevent unplanned file growth.

http://msdn.microsoft.com/en-us/library/ms190488.aspx

This will perform a NOTRUNCATE then a TRUNCATEONLY. This is what you want to move around free space and shrink the physical database size

DBCC SHRINKDATABASE('Ebis_Prod', 10)

Or do them individually with

DBCC SHRINKDATABASE('Ebis_Prod', 10, NOTRUNCATE)
DBCC SHRINKDATABASE('Ebis_Prod', 10, TRUNCATEONLY)

Note the 10 represents 10%. So shrink the database, leaving 10% of the total database size free. So if your database is 100gb, the final size will be 110gb with 100g of actual data, and 10g free space (space allocated in the physical file, but no data occupied in the database)

This can be done with the database online, and will not lock the database.
Is not as effective as droping and re-building the index, infact I did this on a table whos scan density was 96.55% and logical scan was 0.45% and it changed nothing (DBREINDEX worked, it set density to 100% and logical to 0%)
http://msdn.microsoft.com/en-us/library/aa258286(SQL.80).aspx

DBCC INDEXDEFRAG ('ebis_prod', 'tblApplications')
or
DBCC INDEXDEFRAG ('ebis_prod', 'tblApplications', 'PK_tblApplications')

This will lock the table, so do it off hours
This is more effective that INDEXDEFRAG. BUT Microsoft is going to discontinue this feature, so use ALTER INDEX instead.

DBCC DBREINDEX ('tblApplications')
or
DBCC DBREINDEX ('tblApplications', 'PK_tblApplications')

The Prefered method, This is what I use
http://msdn.microsoft.com/en-us/library/ms188388.aspx

ALTER INDEX ALL ON Ebis_Prod.dbo.tblUsers REBUILD

There are tons of options for this, see the msdn, but I just rebuild ALL index on a table.

Used to shrink a log file

DBCC SHRINKFILE(Ebis_Prod_log)