Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
SQLCode
Posting Yak Master
143 Posts |
 Posted - 2004-03-18 : 10:39:32
|
| Hi all,First of all, let me take a momemt to appreciate all the great help that is provided here in this forum. It is just too good forum. I am totally hooked on to it now.Thanks.Problem:I have to optimize an existing database which is a huge DB, built from a datawarehouse. It is mostly used for reporting.Until now the performance was not an issue as there were paper based, static reports.Since things have changed now and I am doing the optimization, my first take is to check indexes.Here's what I found in one of the tables.TableA 2884289 rowsTableA ( FK (dDate - DKey) [DateECKey] [int] NOT NULL ,FK (dC) [CKey] [int] NOT NULL ,FK (dT) [TKey] [int] NOT NULL ,FK (dDate - DKey) [DatePCKey] [int] NOT NULL ,FK (dDate - DKey) [DateFCKey] [int] NOT NULL ,FK (dCA) [CAKey] [int] NOT NULL , [MPCount] [smallint] NOT NULL , [RFCCount] [smallint] NOT NULL , [PCCount] [smallint] NOT NULL , [FCCount] [smallint] NOT NULL , [PScore] [smallint] NULL , [Fcore] [smallint] NULL , [CID] [int] NOT NULL , [TID] [int] NOT NULL , [RSource] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL Index ColumnsPK- Tkey, CKey,DateECKey,CAKey,DatePCKey,DateFCKeyCEF0 CKeyidx_nc_CE_TID_CID TID, CIDIX_CoEFDate DateECKeyIX_CEFTKey TKeyTo my understanding, these are all redundundant indexes. some of the columns are indexed more than once.The first index PK is probably because there are joins on those columns.What should be the right approach. Please help.TIA |
|
|
samsekar
Constraint Violating Yak Guru
437 Posts |
Posted - 2004-03-18 : 10:57:38
|
| Nothing wrong in having same columns indexed more than once. The indexes are created based on how you are quering the columns. For more information check BOL on 'Desinging an Index'- Sekar |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-03-18 : 12:25:38
|
| You can drop IX_DEFTKey. It is considered a duplicate to the primary key since TKey is the first column in both. The optimizer can use the PK index instead. The others are not duplicates.Tara |
|
|
|
SQLCode
Posting Yak Master
143 Posts |
Posted - 2004-03-18 : 12:41:36
|
| Thanks Tara, for your input. What about IX_CoEFDate DateECKey, it is not a duplicate becuase it is not the first?Also, since I have a mostly read-only DB and space may not be an issue, can I have many indexes as per my queries? Of course depending on how well they fit on tables.I have to speed it up in secs as opposed to current queries that run for several minutes.TIA |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-03-18 : 12:44:40
|
| IX_CoEFDate is not a duplicate because no other index has DateECKey as the first column in the index.Since your database is read-only, you could benefit from a highly indexed system. But there would really not be much benefit from duplicate indexes. SQL Server can use the other index already. Now, if you had more composite indexes where the first column was the same but the second and the rest were different, that would be different. You would benefit from keeping those in your system if you had queries that could use these indexes. So remove any single column indexes that are not the primary key or a unique constraint that are considered duplicates. It is a duplicate if another index has that single column as the first column in the index.Tara |
|
|
|
SQLCode
Posting Yak Master
143 Posts |
Posted - 2004-03-18 : 12:58:45
|
| Thanks Tara, for that useful help. I tried looking at http://www.sql-server-performance.com/ a few times with the covering indexes etc., but still could not figure out if the same columns could be indexed in different ways. It just didn't dawn on me. duhh!Your explanation did the trick. Thanks a bunch! for taking the time to answer. |
|
|
|
|
|
|
|
|
Optimization -- Duplicate indexes?