Do I need two indexes or just one? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Do I need two indexes or just one?

Author

Topic

kfb5926
Starting Member

7 Posts

Posted - 2010-12-09 : 17:29:16

The dm_db_missing_index_group_stats DMV is suggesting I add two indexes. I'm wondering if I can kill two birds with one stone:

Index 1:
Table: RM
Equality: [CUSTNMBR]
Inequality: [RMDTYPAL]
Included columns: [DOCNUMBR], [CURTRXAM]

Index 2:
Table: RM
Equality: [CUSTNMBR], [VOIDSTTS]
Inequality: [RMDTYPAL]
Included columns: [CPRCSTNM], [DOCNUMBR], [CURTRXAM]

Would creating index 2 satisfy index 1?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-12-09 : 17:31:42

Yes, however Index2 is wider, which may not be a problem. How wide are both of these in terms of bytes?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-12-09 : 18:06:30

quote:
Originally posted by kfb5926

Would creating index 2 satisfy index 1?

Kinda, partially, not entirely.

Index 1 would have the key columns CUSTNMBR, RMDTYPAL
Index 2 would have the key columns CUSTNMBR, VOIDSTTS, RMDTYPAL

If a query filters just on CUSTNMBR and RMDTYPAL it can only partially seek on the second index. http://sqlinthewild.co.za/index.php/2009/02/06/index-columns-selectivity-and-inequality-predicates/

It may be good enough, only you can answer that by testing.

p.s. The missing index DMV is a reasonable place to start looking at indexes, not a good place to end. Test any suggestions out thoroughly.

--
Gail Shaw
SQL Server MVP

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-12-09 : 18:23:19

Agreed. I was thinking it would be in this order though:

Index 1 would have the key columns CUSTNMBR, RMDTYPAL
Index 2 would have the key columns CUSTNMBR, RMDTYPAL, VOIDSTTS

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

kfb5926
Starting Member

7 Posts

Posted - 2010-12-09 : 22:26:50

Thanks for the help!

kfb5926
Starting Member

7 Posts

Posted - 2010-12-09 : 22:35:24

quote:
Originally posted by tkizer

Yes, however Index2 is wider, which may not be a problem. How wide are both of these in terms of bytes?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

They're about 200 MBs

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-12-09 : 23:40:36

I hope you mean 200 bytes, but even then it may be too wide.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-12-10 : 02:04:52

quote:
Originally posted by tkizer

Agreed. I was thinking it would be in this order though:

Index 1 would have the key columns CUSTNMBR, RMDTYPAL
Index 2 would have the key columns CUSTNMBR, RMDTYPAL, VOIDSTTS

That's not what the missing index DMV suggests though. Since it's suggestion has 2 columns as equalities and one as inequality, the order it's suggesting for the second is CUSTNMBR, VOIDSTTS, RMDTYPAL

With CUSTNMBR, RMDTYPAL, VOIDSTTS if a query does equality predicates on CUSTNMBR and VOIDSTTS it will only be able to partially use that index.

--
Gail Shaw
SQL Server MVP

kfb5926
Starting Member

7 Posts

Posted - 2010-12-10 : 09:10:23

Sorry, I thought you meant how big the indexes themselves would be. The table, RM, has 251,000 rows.

CUSTNMBER is char(15)
VOIDSTTS is smallint
RMDTYPAL is smallint

CPRCSTNM is char(15)
DOCNUMBR is char(21)
CURTRXAM is numeric(19,5)

quote:
Originally posted by tkizer

I hope you mean 200 bytes, but even then it may be too wide.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

kfb5926
Starting Member

7 Posts

Posted - 2010-12-10 : 09:27:39

Actually, I think that 200MB is the size of the existing table, not the index.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources