Author: Michael Gonzalez
Frequently Answered Questions
Miscellaneous

File Text Search & Replace Utility
HTML Text Extraction using innerText
E-mail (CDONTS.NewMail) Sample Code
SQL Server 7.0/2000

Incorporating ASP and SQL Server
100's of T-SQL Scripts
Don't Use @@ERROR with UPDATE Statements
Exporting Tables to Text Files
Creating SQL Server Databases
ASP (SQL) Query Analyzer
Increasing SQL Server Performance with Indexes
Distributed SQL Server Transactions & Queries
COM/COM+ Development
What is COM?
Isn't ActiveX and COM the same?
How can Components benefit my ASPs?
Am I using COM Components now?
How do I use COM Components in my ASPs?
Creating your First COM Component
Creating a COM Component that uses ASP Intrinsic Objects
Creating a COM Component to access an MS-Access Database
MTS Component Template
MSMQ Component Template / Example

Improving SQL Server 7/2000 Performance with Indexes

Indexes can sure speed up the performance of your SQL Server. However, using the wrong types of indexes, fill factors and/or using too many can actually hurt your performance goals.

I will be teaching you all about SQL Server storage and indexes in a brief, but effective, tutorial.

Understanding SQL Server Storage:
  • Databases are stored on physical files with default extensions of MDF. For example, the master database is stored on master.mdf.
  • Extents are where data is stored and they contain up to 8 pages:
    • Shared Extents: Up to seven, 8K, pages are stored in these types of extents.
    • Uniform Extents: Once a Shared extent contains 8 pages, it is upgraded to this type of extent.
  • Pages are stored in extents are there can be up to 8 of them in any given (one) extent. The pages can store a maximum of 8KB of data. This also means that an extent can store up to 64KB of data (8KB[page] * 8[pages]).
  • Rows can store up to 8KB of data.
Two methods of Data Access in SQL Server:
  • Table scan: every physical row is scaned one-by-one until the data sought is found.
  • Indexes: Microsoft uses the B-Tree (Balanced Tree) method for using and creating indexes. Much better than a table scan - and faster!
Indexes are Built on Two Types of Tables:
  • Heaps: tables that DO NOT contain clustered indexes make use of a RID (Row ID) globally unique identifier (GUID) to uniquely identity rows.
  • Clustered Tables: any table that contains a clustered index is called a clustered table.
Three Types of Indexes:
  • Clustered Indexes: a column, or columns, that contains a clustered index organizes data in a sort order (e.g., 1, 2, 3, 4, ...). And, ONLY one can exist per table. The data in tables that contain clustered indexes is actually organized in the sort order of the index itself. Also, a clustered index contains the data in the table.
  • Non-Clustered Indexes:
    • Non-Clustered Index on a Heap: Using this type of index makes use of a RID (Row ID) column on the table to get to the data. Non-clustered indexes DO NOT contain the actual table data but rather a pointer to it. The data in the table is NOT stored in the same order as the index.
    • Non-Clustered Index on a Clustered Table: Using this type of index makes use of the Clustered Index on the table to get to the data. Non-clustered indexes DO NOT contain the actual table data but rather a pointer to it. The data in the table is NOT stored in the same order as the index.
Non-Clustered indexes make use of either a Clustered Index or a RID (Row ID) to get to data - NOT so with a Clustered Index. With a Clustered Index, access to data is fast and direct. The downside to using Clustered Indexes is that when inserts are made to the table, the entire index/data has to be rearranged in order to preserve the sort criteria inherent in the Clustered Index.

A table can have many indexes but doing so really places additional overhead rather than increasing the performance of the server. You can explicitly create Non-Clustered Index on a Heap or Non-Clustered Indexes on a Clustered Table. You can ONLY create either non-clustered or clustered indexes.
Which Indexes to Choose?
  • Non-Clustered Indexes: only on tables/columns where many modifications are made (such as on OLTP [Online Transaction Processing] systems) and where the values are close to unique.
  • Clustered Indexes: only on tables/columns where little or no modifications are made (such as on OLAP [Online Analytical Processing] systems) and where the values are not unique.
Determining what Indexes Currently Exist:
  1. Using the SQL Server Query Analyzer, type the following command EXEC sp_help table_name (Or, you can use the sp_helpindex sproc). Replace table_name with the proper table name.
  2. Look for the column titled index_name and index_description.
Creating Indexes using CREATE INDEX:
  • Creating a Clustered Index: USE Database_Name CREATE CLUSTERED INDEX index_name ON Table_Name(Column_Name1, Column_Name2) WITH FILLFACTOR = 95 NOTE: The FILLFACTOR arguement tells SQL Server what percentage of the index should be filled with data; the remainder is left empty for additional data to be inserted. Use a HIGH FILLFACTOR IF NOT very many modifications are to be made to the table.
  • Creating a Non-Clustered Index: USE Database_Name CREATE NONCLUSTERED INDEX index_name ON Table_Name(Column_Name1, Column_Name2) WITH FILLFACTOR = 50 NOTE: The FILLFACTOR arguement tells SQL Server what percentage of the index should be filled with data; the remainder is left empty for additional data to be inserted. Use a LOW FILLFACTOR IF many modifications are to be made to the table.
Defragmenting Indexes:

As SQL Server makes modifications to an Index, it becomes fragmented, much like that of a hard disk. This can seriously reduce performance! Use the following command to rebuild your indexes: DBCC DBREINDEX ("Table_Name","Index_Name",50) NOTE: 50 is the FILLFACTOR percentage and tells SQL Server how much of the index to fill with data (as a percentage). Use a HIGH percentage for CLUSTERED indexes and a LOW percentage for NONCLUSTERED indexes.

When you rebuild an index on a table, all indexes on that table are also rebuilt. Rebuilding indexes defragments the index and provides for better performance!
Using My sp_Fragmentation Stored Procedure to Check for Fragmentation:
  1. Startup the SQL Server Query Analyzer and type in the following code: USE Database_Name GO CREATE PROC sp_Fragmentation @TableName NVARCHAR(255), @IndexName NVARCHAR(255) AS IF @TableName = "" BEGIN RAISERROR("You did not provide a value for @TableName",1,1) END IF @IndexName = "" BEGIN RAISERROR("You did not provide a value for @IndexName",1,1) END DECLARE @TableID INT, @IndexID INT SET @TableID = OBJECT_ID(@TableName) SELECT @IndexID = IndID FROM sysindexes WHERE id = @TableID AND name = @IndexName IF @IndexID > 0 AND @TableID > 0 DBCC SHOWCONTIG (@TableID, @IndexID) -- The arguements MUST be in parenthesis ELSE RAISERROR("DBCC SHOWCONTIG did NOT return ID values for either @TableID or @IndexID! CAN NOT PROCEED!",1,1) NOTE: Replace Database_Name with the name of the database you want this stored procedure to run on (most likely that which you will be using this sproc to check indexes on).
  2. Press the F5 to execute the command.
  3. Now you can use it to check for fragmentation of indexes:
    1. Clear the query/code window and type the following code: USE Database_Name EXEC sp_Fragmentation "Table_Name","Index_Name"
    2. Press the F5 key to execute the stored procedure.
    3. Look for an entry titled Scan Density[Best Count:Actual Count]. If there is no fragmentation then the Best Count value and the Actual Count value will be the same, and, the Scan Density value will be 100%.
Renaming Indexes:
  1. Using the SQL Server Query Analyzer type in the following code: USE Database_Name EXEC sp_rename @objname = "IndexName", @newname = "NewName"
  2. Press the F5 key to execute the Stored Procedure.
Deleting Indexes:
  1. Using the SQL Server Query Analyzer type in the following code: DROP INDEX IndexName
  2. Press the F5 to execute the command.
Defragmenting ALL of the Indexes in a Database - my Custom Stored Procedure:

Performing a rebuild/defragmentation on multiple indexes can be time consuming. With a stored procedure I have created, shown below, you can rebuild all of the indexes in all of the tables in a given database.
  1. Using the SQL Server Query Analyzer type in the following code: CREATE PROC sp_RebuildAllIndexes AS BEGIN TRAN DECLARE TableCursor CURSOR FOR SELECT TABLE_CATALOG + ".." + TABLE_NAME AS T FROM INFORMATION_SCHEMA.TABLES OPEN TableCursor DECLARE @Table VARCHAR(255) WHILE @@FETCH_STATUS = 0 BEGIN FETCH NEXT FROM TableCursor INTO @Table DBCC DBREINDEX (@Table) END CLOSE TableCursor DEALLOCATE TableCursor IF @@ERROR > 0 BEGIN PRINT "Error(s) occured, discarding any changes made..." ROLLBACK TRAN END ELSE COMMIT TRAN
  2. Press the F5 to execute the command.
  3. Now it's ready: all you have to do to rebuild/defragment all of the indexes in a given database is type in the following code in the SQL Server Query Analyzer window: USE DatabaseName EXEC sp_RebuildAllIndexes
  4. Press the F5 to execute the command.
Comments & Questions Form

Send It!