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 |
|
arkiboys
Master Smack Fu Yak Hacker
1433 Posts |
 Posted - 2011-11-10 : 08:23:02
|
| Schema of table:--tblMain SCRIPT CREATE TABLE [dbo].[tblMain]( [Field1] [int] NOT NULL, [Field2] [char](3) NOT NULL, [Field3] [varchar](50) NOT NULL, [Field4] [float] NOT NULL, [Field5] [varchar](50) NOT NULL, [Field6] [datetime] NOT NULL, [Field7] [int] NOT NULL, [Field8] [bit] NOT NULL, [Field9] [bit] NOT NULL, CONSTRAINT [PK_tblMain] PRIMARY KEY CLUSTERED ( [Field1] ASC, [Field2] ASC, [Field3] ASC, [Field5] ASC, [Field6] ASC, [Field7] ASC, [Field8] ASC, [Field9] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] ------------------------------------------The new table has the same number of fields including an identity column as PK. All the other fields are NOT PK.There is a unique constraint on all the fields except the PK.There is a nonclustered index on field5 and field6---------------------- Then ran the two select as follows: Note that I have to do a * as all the fields are required. 1- select * from tblMainOld where Field5 = 'suser' and Field6 = '25 sep 2011' 2- select * from tblMainNEW where Field5 = 'suser' and Field6 = '25 sep 2011' The extimated execution plan on these two queries show as follows: 1- Query cost 54% select 0% <------Paralellism 9% <------ clustered index scan 91% 2- Query cost 46% select 0% <-----Paralellism 11% <------ Index scan (Non-clustered) Questions: 1- Is this an improvement. It seems there is an 8% improvement i.e. 54%-46% 2- Why is step 2, It does not show an index seek? Thanks |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-11-10 : 08:28:30
|
| What's the definition of that nonclustered index? (the actual create index statement)?Which index is step 2 scanning?--Gail ShawSQL Server MVP |
 |
|
|
|
|
|
|
|
execution plan - index