Indexes and Fragmentation in SQL Server 2000 Part 2

Most DBAs are constantly looking for ways to tune their servers to run better. Joe Doherty brings us the second part of his series on indexes with an in depth look at how the indexes are stored in your database.

Remove index fragmentation

This script will drop and recreated all indexes in a database within a transaction. The script will also add standard nameing conventions to the index names. Any duplicated indexes are removed.

Update statistics for all tables in any DB

Most of the time even after executing dbcc dbreindex script on database you won't see much improvement in application/DB performance.  Thats because dbreindex creates statistics for all tables but it executes sp_updatestats which is like sample statistics. To get the maximum performance we had to execute Update statistics with fullscan on table. This simple will […]

Updating all the indexes/statistics of a database

The below command will rebuild all indexes on a database.We need to run the below command in the context of each database that is of interest.

Finding missing indexes in sql server 2005

the following scripts helps in finding top 5 missing indexes using the new feature of DMV available in SQl Server 2005

Quick Fix secondary Indexes ( SQL 2000 )

In real world production tuning sometimes a "quick fix" is required. This script will generate index create scripts for any column ending ID which doesn't have an index. It's dirty and its quick but you might just be amazed by the results when run against your database!

Get All Foreign Key names and Tables referenced

This script lists all the foreign keys ,the referenced and the referencing tables , along with the column information.

List all indexes with keys, description and size

This script combines functionality of sp_helpindex and sp_spaceused to list all tables with individual indexes, their keys and description  (sp_spaceused cannot do that) and size in MB per individual index (sp_helpindex cannot do that either). Feel free to use and modify as you see fit. Hope it can help.

Script all indexes as CREATE INDEX statements

this script will generate CREATE index statements for all indexes in the database;there are already scripts out there that script constraints for primary keys and unique constraints, which automatically create an index behind the scenes. but what about the other indexes?uncomment two statements in order to filter out primary keys and Unique indexes so you […]

Simple script to ReIndex complete database