Skip to main content

Strategy to deign index for new database/table/s

It is recommended to take a strategic approach while you start designing index for a new database or new table/s for optimal performance and use of resources. Please consider using below strategy to design indexes for a newly created database/table/s:

PHASE 1: INITIAL DESIGN

This phase deals with designing index for a newly created database/table/s before moving it to production. Please follow below steps to design index for the database/table/s:

1. Start with each table and go one by one. Identify all queries which will run on the table and identify a best clustered index candidate for the table.

2. Select Primary Key and unique key. Do not blindly make your primary key as a clustered index. It is fine if your clustered index is not primary key.

3. Have foreign key are in place where ever needed. Foreign keys are not that bad for performance as long as you have index on this. So make sure you create non clustered index on the columns which are part of foreign key.

4. Create non clustered index for highly selective queries. Highly selective queries are the queries that will be frequently used and their performances are critical to business. Prefer to use composite (wide) index instead of having multiple smaller index but do not go for covering index.

5. STOP INDEX DESIGN AND IMPLEMENTATION AT THIS STAGE AND MOVE AHEAD WITH YOUR FINAL LOAD TEST AND/OR USER ACCEPTANCE TEST WHERE YOU CAN COLLECT TRUE WORKLOAD FROM PRODUCTION LOAD SCENERIO.

6. Gather true work load from load test and/or user acceptance test and analyze it and then design other non-clustered indexes. Follow below rules while you create such indexes:

a. Be sure that the workload you are working with truly represents the production scenario. A workload which captures partial scenario can lead us take decision which may degrade application’s performance.

b. Use available tools to analyze the workload and situation. Most common tools will be DTA (Database Tuning Advisor) and Missing Index Hint from Query Plan. However both tool has it’s limitation so do not blindly follow their recommendation.

7. MOVE THE DATABASE/TABLES TO PRODUCTION AT THIS STAGE. HOWEVER PLEASE REFER TO PHASE 2 FOR MAINTEAINCE OF INDEX FROM DESIGN PRESPECTIVE WHILE DATABASE/TABLE/S ARE IN PRODUCTION.

PHASE 2: DYNAMIC MAINTENANCE

Applications changes with time. This means database objects and server configuration changes which can make some index irrelevant or harmful for some queries or ma require new index. So once the database/tables are in production you need to keep evaluating the index design. You can execute below strategies to accomplish this task:

Proactive: Ensure that you capture good workload and execute Step 6 of PHASE 1 of this post at a realistic time interval. This time interval should be based on the database/application’s performance history and other relevant parameters. However you need to be very careful to ensure that you do not adversely affect existing system/s while working on this proactive maintenance.

Reactive: When you move a new code (change) to production, make sure that indexing is designed properly.

Note: In this post I outlined a general strategy to design and implement index for new application/database/table/s which should work for almost any situation. However good index design comes from extensive experience and deep knowledge. Also consider using other options (like indexed view, code review etc) instead of completely relying on index for achieving desired performance.

If you have any question please feel free to contact me at info@consultdba.com

Labels:

Comments

Post a Comment

Popular posts from this blog

How to kill a negative SPID (like SPID -2) in SQL Server?

Rarely this scenario will arise when most likely you see this negative SPID (most likely SPID -2) is blocking other transaction causing issues. If you try to kill it using normal KILL command, it will fail reporting below error: Msg 6101, Level 16, State 1, Line 1 Process ID <SPID Number> is not a valid process ID. Choose a number between 1 and 2048 This is because of an orphaned distributed transaction ID.  Please follow below steps to kill it: Step 1: -- Find the UOW Number select req_transactionUOW from master..syslockinfo where req_spid = <SPID Number> --  <SPID Number>  is -2 most likely. Step 2: -- Copy the UOW number from Step one KILL ‘<UOW Number>’ This will kill the negative SPID resolving the issue.  However please note following points: 1. For SPID -2, you may find multiple UOW numbers. Please start killing them one by one. Typically killing first UOW will resolve the issues. (ie. will kill all UOW and release
Post a Comment
Read more

DMV/TSQL to find out basic hardware information of the SQL Server including when SQL Server started.

Please use below code: However, please be advised that it can not tell correct information around virtualization.  For example, it will show Hypervisor even if SQL runs on a physical OS where Hyper-V is on. So use this query only when you do not have sufficient access on underlying Windows Operating system to get these information directly. -- Basic hardware information for SQL Server (sys.dm_os_sys_info) /* This query is courtesy of https://sqlserverperformance.wordpress.com/. All credits goes to original author. */ SELECT cpu_count AS [Logical CPU Count] , scheduler_count , hyperthread_ratio AS [Hyperthread Ratio] , cpu_count / hyperthread_ratio AS [Physical CPU Count] , physical_memory_kb / 1024 AS [Physical Memory (MB)] , committed_kb / 1024 AS [Committed Memory (MB)] , committed_target_kb / 1024 AS [Committed Target Memory (MB)] , max_workers_count AS [Max Workers Count] , affinity_type_desc AS [Affinity Type] , sqlserver_start_time AS [
Post a Comment
Read more

‘Trace Skipped Records’ – What is this and how to resolve it while using SQL Server Profiles?

In some very rare case, you may experience a very weired message in profiler’s output as ‘Trace Skipped Records’ while you trace something on SQL Server. Screenshot of similer situation is as below: This is not an error but it comes by design of SQL Server (I believe so). When you are using SQL profiler and return data is too big to fit in the GUI (for me, it is an enormous xml), SQL Server simply prints this message and proceed to next step. Mostlikely this is to save server’s memory and performance. Although not suggested and guranteed, you can try to run a server side trace and dump data in a file which should capture all the data. However, it is strongly not recommended to run a trace on your production server from server side. Microsoft will probally document this limitation in future. More details may be found at https://connect.microsoft.com/SQLServer/feedback/details/304225/msft-edw-profiler-displays-trace-skipped-records-for-large-530kb-batch 
Post a Comment
Read more