Help on creating a Covering Index???? - 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

General SQL Server Forums

New to SQL Server Programming

Help on creating a Covering Index????

Author

Topic

phrankbooth
Posting Yak Master

162 Posts

Posted - 2007-04-02 : 13:36:37

Hi,

Trying to optimize a query but not sure what to do. I have this query on which I ran an exec plan,

SET NOCOUNT ON;
SELECT qaTestSuite.TestSuiteID, qaTestSuite.TestSuiteStart, qaTestSuite.TestInterface, qaTestSuite.TestVersion, qaTests.TestMachine, qaTestSuite.TestClientMachine, qaTests.TestLogin, qaTests.TestLabel,
qaTestSuite.TestCLPs, qaTestSuite.TestSuiteEnd, qaTests.TestID, qaTests.TestIDInternal, qaTests.TestStart,
qaTests.TestName, qaTests.TestTier, qaTests.TestNo, qaTests.TestWFBCalled, qaTests.TestWFBTime,
qaTests.TestSearches, qaTests.TestSearchesTime, qaTests.TestResult, qaTests.TestEnd, qaTestMssgs.TestMssgsID,
qaTestMssgs.TestMssgTime, qaTestMssgs.TestMssgType, qaTestMssgs.TestMessage, qaTestSuite.TestMode
FROM qaTestSuite with(NOLOCK) INNER JOIN
qaTests with(NOLOCK) ON qaTestSuite.TestSuiteID = qaTests.TestSuiteID INNER JOIN
qaTestMssgs with(NOLOCK) ON qaTests.TestID = qaTestMssgs.TestID
order by qaTestSuite.TestSuiteStart DESC

and it gives me the following results:

Use a Bookmark (RID or Clustering Key) to look up the corresponding row in the Table or Clustered Index.

Physical Op: Bookmark Lookup
Logical Op: Bookmark Lookup
Est. Row Count: 128
Est. Row Size: 4760
Est. I/O Cost: 0.368
Est. CPU Cost: 0.000141
Est. Execs: 1.0
Est. Cost: 0.368888(89%)
Est. Subtree Cost:.415

Argument:
BOOKMARK:([Bmk1004]), OBJECT:([QAMaster].[dbo].[qaTestMssgs]) WITH PREFETCH

I have no idea what to do with that. Anyone have any clues? What I found online was that I should make a Covering Index, but I didn't find
any patterns on how to do that. Any one have ideas of how to do this?

Thanks very much for your help!

--PhB

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-04-02 : 13:45:29

The bookmark lookup is required to get the data for the columns in your select list. I would not recommend putting a covering index to "fix" this as you've got too many columns to cover.

Could you show us the DDL for your tables including the DDL for your indexes? Make sure to only include DDL for those tables in your query.

Tara Kizer
http://weblogs.sqlteam.com/tarad/

phrankbooth
Posting Yak Master

162 Posts

Posted - 2007-04-02 : 14:01:23

Hey thanks for the quick reply.

Excuse my ignorance but what is DDL?

--PhB

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-04-02 : 14:13:53

It stands for data definition language. Examples are CREATE/ALTER/DROP. So we need to see your CREATE TABLE statements plus CREATE INDEX statements. Depending on how you script it, we may need to see ALTER TABLE too.

Tara Kizer
http://weblogs.sqlteam.com/tarad/

X002548
Not Just a Number

15586 Posts

Posted - 2007-04-02 : 14:15:58

Are you saying you want to return ALL of the data (including uncommitted transactions)?

For what purpose?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

phrankbooth
Posting Yak Master

162 Posts

Posted - 2007-04-02 : 14:48:37

OK here it is. I left out the (if exist commands, but I if you need those I can post them.) And yes, I need all the data, it's for running a detail report, which is running slow now. I don;t want uncommitted trans because those wouldn't count for the rpt.

Thank you.

CREATE TABLE [dbo].[qaTestMssgs] (
[TestMssgsID] [int] IDENTITY (1, 1) NOT NULL ,
[TestID] [int] NOT NULL ,
[TestMssgTime] [datetime] NULL ,
[TestMssgType] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TestMessage] [varchar] (8000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[qaTestSuite] (
[TestSuiteID] [bigint] IDENTITY (1, 1) NOT NULL ,
[TestSuiteStart] [datetime] NULL ,
[TestInterface] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TestVersion] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TestRelease] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TestClientMachine] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TestCLPs] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TestSuiteEnd] [datetime] NULL ,
[TestMode] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[qaTests] (
[TestID] [int] IDENTITY (1, 1) NOT NULL ,
[TestSuiteID] [bigint] NOT NULL ,
[TestIDInternal] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TestStart] [datetime] NULL ,
[TestName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TestTier] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TestNo] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TestLabel] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TestLogin] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TestMachine] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TestWFBTime] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TestWFBCalled] [int] NULL ,
[TestSearches] [int] NULL ,
[TestSearchesTime] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TestResult] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TestEnd] [datetime] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[qaTestMssgs] WITH NOCHECK ADD
CONSTRAINT [PK__qaTestMssgs__75A278F5] PRIMARY KEY CLUSTERED
(
[TestMssgsID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[qaTestSuite] WITH NOCHECK ADD
CONSTRAINT [PK_TestSuite] PRIMARY KEY CLUSTERED
(
[TestSuiteID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[qaTests] WITH NOCHECK ADD
CONSTRAINT [PK__Tests__72C60C4A] PRIMARY KEY CLUSTERED
(
[TestID]
) ON [PRIMARY]
GO

CREATE INDEX [IX_qaTestMssgs_TestID] ON [dbo].[qaTestMssgs]([TestID]) WITH FILLFACTOR = 70 ON [PRIMARY]
GO

CREATE INDEX [IX_qaTestSuite_TestInterface] ON [dbo].[qaTestSuite]([TestInterface]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [IX_qaTestSuite_TestMode] ON [dbo].[qaTestSuite]([TestMode] DESC ) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [IX_qaTestSuite_Start] ON [dbo].[qaTestSuite]([TestSuiteStart] DESC ) ON [PRIMARY]
GO

ALTER TABLE [dbo].[qaTests] ADD
CONSTRAINT [DF_qaTests_TestLabel] DEFAULT ('Live') FOR [TestLabel],
CONSTRAINT [DF_qaTests_TestResult] DEFAULT ('P') FOR [TestResult]
GO

CREATE INDEX [IX_qaTests_SuiteID] ON [dbo].[qaTests]([TestSuiteID]) WITH FILLFACTOR = 85 ON [PRIMARY]
GO

CREATE INDEX [IX_qaTests_IDInternal] ON [dbo].[qaTests]([TestIDInternal]) WITH FILLFACTOR = 80 ON [PRIMARY]
GO

CREATE INDEX [IX_qaTests_result] ON [dbo].[qaTests]([TestResult]) WITH FILLFACTOR = 80 ON [PRIMARY]
GO

ALTER TABLE [dbo].[qaTestMssgs] ADD
CONSTRAINT [FK_qaTestMssgs_qaTests] FOREIGN KEY
(
[TestID]
) REFERENCES [dbo].[qaTests] (
[TestID]
)
GO

ALTER TABLE [dbo].[qaTests] ADD
CONSTRAINT [FK_Tests_qaTestSuite] FOREIGN KEY
(
[TestSuiteID]
) REFERENCES [dbo].[qaTestSuite] (
[TestSuiteID]
)
GO

--PhB

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-04-02 : 15:32:18

How many rows are in each table? How many rows will be returned by your query?

Tara Kizer
http://weblogs.sqlteam.com/tarad/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-04-02 : 15:41:37

You've got the FROM clause entirely indexed as well as the ORDER BY. So you could put covering indexes over the columns in the SELECT list, however that probably will negatively impact DML (INSERT/UPDATE/DELETE) operations from other queries.

Here are the 3 possible covering indexes to add:
qaTestSuite.TestSuiteID, qaTestSuite.TestSuiteStart, qaTestSuite.TestInterface, qaTestSuite.TestVersion,
qaTestSuite.TestClientMachine, qaTestSuite.TestCLPs, qaTestSuite.TestSuiteEnd, qaTestSuite.TestMode

qaTests.TestMachine, qaTests.TestLogin, qaTests.TestLabel, qaTests.TestID, qaTests.TestIDInternal,
qaTests.TestStart, qaTests.TestName, qaTests.TestTier, qaTests.TestNo, qaTests.TestWFBCalled,
qaTests.TestWFBTime, qaTests.TestSearches, qaTests.TestSearchesTime, qaTests.TestResult, qaTests.TestEnd

qaTestMssgs.TestMssgsID, qaTestMssgs.TestMssgTime, qaTestMssgs.TestMssgType, qaTestMssgs.TestMessage

If you are only concerned about the qaTestMssgs table (since that's the only one you included information about from your execution plan), then just add the 4 columns to an index.

Tara Kizer
http://weblogs.sqlteam.com/tarad/

phrankbooth
Posting Yak Master

162 Posts

Posted - 2007-04-02 : 16:08:16

The qatestsuite table will return several tens of rows, the qatests table will return several 100's of rows and the qamessgs table will return 1000's.

Thanks for your help! But how do you implement them? Is there something special that needs to be done to set them up? I ask because they have a special name (Covering Index) and so I wonder if the syntax is different for setting them up.

--PhB

pootle_flump

1064 Posts

Posted - 2007-04-02 : 16:14:27

Nothing is different. All it means is that the query can be satisfied without retrieving data from the leaf level clustered index (or data page of the heap). There is nothing "special" about the index as such.

You *should* always consider indexes with regard to all DML (SELECT, INSERT, DELETE, UPDATE) on the tables though, not just one query.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-04-02 : 16:38:01

The syntax is the same. You just put commas in between each column.

But I wouldn't recommend adding covering indexes for this!

Tara Kizer
http://weblogs.sqlteam.com/tarad/

phrankbooth
Posting Yak Master

162 Posts

Posted - 2007-04-02 : 20:23:09

OK, so I'll just add these columns to the existing indexes I have (no dupes of course).

Thank you Almighty SQL Goddess!!

You rock...so hard! :)

--PhB

X002548
Not Just a Number

15586 Posts

Posted - 2007-04-03 : 09:55:57

quote:
Originally posted by phrankbooth

OK, so I'll just add these columns to the existing indexes I have (no dupes of course).

Thank you Almighty SQL Goddess!!

You rock...so hard! :)

--PhB

Just don't play her at pool

In any case, a report on everything..hmmmm, sounds very useful

And estimated row count of 128? I mean how long does this thing take anyway?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

phrankbooth
Posting Yak Master

162 Posts

Posted - 2007-04-04 : 11:08:48

Under 4 secs.

--PhB

pootle_flump

1064 Posts

Posted - 2007-04-04 : 11:10:15

Test your insert & update queries that hit these tables too.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources