September 19, 2013 at 5:52 am
I'm playing with a stored proc that takes 2 days to run. I am pretty certain the issue was the lack of indexes on the Staging table so I created a clustered index and a few nonclustered indexes. Now I'm running the proc with Display Actual Execution Plan.
The proc just started and is still running. But as I'm looking at the initial EP results, the first query (which is only 7% of the batch cost) has a Key Lookup of 94% on my new clustered index.
I know Index Seeks are best, Index Scans are okay, and Table Scans (when they can be avoided) are not the best. But I am not sure where Key Lookup falls amongst those. Is it a good thing? Is it a bad thing?
Before I created the clustered index, I did nonclustered indexes only and ran an estimated EP. The estimate had 0 cost relative to the rest of the batch and an Index Seek of 9% in the place where the Key Lookup used to be. The majority of the cost of this query was in a Table Update (73%), but in the Key Lookup plan, there's a Clustered Index Update of 6%...
I think this is a good change (better than it used to be), but I'm not 100%, so I'm throwing this up here. Does anyone have a definitive "it depends" answer? @=)
To be clear, I set Statistics on the actual execution and received the following information:
Scan count 1, logical reads 22236, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
September 19, 2013 at 11:35 am
In my experience, key/bookmark lookups are not usually a good thing. Basically this says that the index being used to filter the data cannot return all the columns needed to satisfy the query, so for each row returned by the index seek/scan the engine has to then use the clustered index key (in this case) or the rid (in the case of a heap) to get the rest of the columns needed to fulfill the query.
When you have a small result set it isn't that bad but as the result set grows it becomes a problem. This is a classic use-case for creating an index with included columns, which will eliminate the need for the lookup. All the usual caveats apply when adding/changing an index (make sure there isn't a better index to change, make sure the change doesn't harm other things, etc...)
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 19, 2013 at 12:05 pm
Thanks, Jack.
Huh. Apparently the Key Lookup shook itself out of the execution plan a bit later on. Now I've got Clustered Index Scans.
Of course, I've been playing with these table indexes so much that maybe dropping and recreating them finally got the stats reset...
September 19, 2013 at 12:16 pm
Brandie Tarvin (9/19/2013)
Thanks, Jack.Huh. Apparently the Key Lookup shook itself out of the execution plan a bit later on. Now I've got Clustered Index Scans.
Of course, I've been playing with these table indexes so much that maybe dropping and recreating them finally got the stats reset...
I'm not surprised to see it change to a clustered index scan. I haven't done any testing on it, but I'd bet the tipping point for going from index seek & key lookup to a CI scan isn't that many rows. When you think about doing multiple reads of a table and then having to JOIN the results, a single scan would be faster and more consistent pretty quickly. I'd still try to see what happens if you create a covering index using included columns.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 19, 2013 at 12:58 pm
Okay, this is interesting. When I run the first two queries in conjuntion with everything else, the first execution plan changes back to a Key Lookup.
When I run them by themselves, I get Clustered Index Scans.
I wonder what the heck is going on to make SQL change this.
September 19, 2013 at 12:59 pm
Jack Corbett (9/19/2013)
Brandie Tarvin (9/19/2013)
Thanks, Jack.Huh. Apparently the Key Lookup shook itself out of the execution plan a bit later on. Now I've got Clustered Index Scans.
Of course, I've been playing with these table indexes so much that maybe dropping and recreating them finally got the stats reset...
I'm not surprised to see it change to a clustered index scan. I haven't done any testing on it, but I'd bet the tipping point for going from index seek & key lookup to a CI scan isn't that many rows. When you think about doing multiple reads of a table and then having to JOIN the results, a single scan would be faster and more consistent pretty quickly. I'd still try to see what happens if you create a covering index using included columns.
I have a covering index using include columns. It's a filtered covering index actually.
September 19, 2013 at 1:05 pm
BTW, it's not a JOIN. It's an UPDATE on a single table that splits one column into many.
Basically it's:
UPDATE ri
SET col1 = Substring(ri.x), col2 = Substring(ri.x)....col7 = Substring(ri.x)
FROM MyTable ri
WHERE RecordID = 2
UPDATE ri
SET col8 = Substring(ri.x), col9 = Substring(ri.x)....
FROM MyTable ri
WHERE RecordID = 3
So nothing major. And the filtered indexes are filtered on RecordID.
September 19, 2013 at 2:40 pm
Brandie Tarvin (9/19/2013)
Okay, this is interesting. When I run the first two queries in conjuntion with everything else, the first execution plan changes back to a Key Lookup.When I run them by themselves, I get Clustered Index Scans.
I wonder what the heck is going on to make SQL change this.
Parameter sniffing of some form.
Execution plans please. Guessing otherwise.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 19, 2013 at 2:41 pm
Brandie Tarvin (9/19/2013)
Jack Corbett (9/19/2013)
Brandie Tarvin (9/19/2013)
Thanks, Jack.Huh. Apparently the Key Lookup shook itself out of the execution plan a bit later on. Now I've got Clustered Index Scans.
Of course, I've been playing with these table indexes so much that maybe dropping and recreating them finally got the stats reset...
I'm not surprised to see it change to a clustered index scan. I haven't done any testing on it, but I'd bet the tipping point for going from index seek & key lookup to a CI scan isn't that many rows. When you think about doing multiple reads of a table and then having to JOIN the results, a single scan would be faster and more consistent pretty quickly. I'd still try to see what happens if you create a covering index using included columns.
I have a covering index using include columns. It's a filtered covering index actually.
If you have key lookups, it's not covering (or it's yet another bug around filtered indexes)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 20, 2013 at 4:44 am
GilaMonster (9/19/2013)
Execution plans please. Guessing otherwise.
I have to scrub the names first. I'll post it in a bit.
September 20, 2013 at 5:14 am
Here we go. Tables & Indexes in the below code box. Execution Plan attached.
USE [MyDB]
GO
/****** Object: Table [dbo].[tblTransactionDetail] Script Date: 09/18/2013 07:54:20 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tblTransactionDetail]')
AND type in (N'U'))
DROP TABLE [dbo].[tblTransactionDetail]
GO
/****** Object: Table [dbo].[tblTransactions] Script Date: 09/18/2013 07:54:20 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tblTransactions]')
AND type in (N'U'))
DROP TABLE [dbo].[tblTransactions]
GO
/****** Object: Table [dbo].[Staging] Script Date: 09/18/2013 07:54:20 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Staging]')
AND type in (N'U'))
DROP TABLE [dbo].[Staging]
GO
/****** Object: Table [dbo].[Staging] Script Date: 09/18/2013 07:54:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Staging](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Extract] [varchar](2000) NULL,
[UNID] [int] NULL,
[DateField] [date] NULL,
[RecordID] [tinyint] NOT NULL,
[CoCd] [varchar](4) NULL,
[ANum] [varchar](10) NULL,
[AOrg] [varchar](3) NULL,
[TNum] [varchar](20) NULL,
[INum] [varchar](8) NULL,
[BPartner] [varchar](10) NULL,
[CBPNum] [varchar](10) NULL,
[DNum] [varchar](30) NULL,
[PDate] [varchar](8) NULL,
[PNum] [varchar](4) NULL,
[ECD] [varchar](4) NULL,
[PCNum] [varchar](20) NULL,
[EDate] [varchar](8) NULL,
[CNum] [varchar](24) NULL,
[ADate] [varchar](8) NULL,
[NDDate] [varchar](8) NULL,
[UA] [varchar](24) NULL,
[FProd] [varchar](10) NULL,
[FC] [varchar](4) NULL,
[PC] [varchar](10) NULL,
[SP] [varchar](4) NULL,
[COB] [varchar](24) NULL,
[Cat] [varchar](3) NULL,
[Amt] [varchar](17) NULL,
[Cur] [varchar](5) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: Index [IDX_Staging_ID] Script Date: 09/18/2013 09:15:11 ******/
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[Staging]')
AND name = N'IDX_Staging_ID')
DROP INDEX [IDX_Staging_ID] ON [dbo].[Staging] WITH ( ONLINE = OFF )
GO
/****** Object: Index [IDX_Staging_RecordID] Script Date: 09/18/2013 09:15:11 ******/
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[Staging]')
AND name = N'IDX_Staging_RecordID')
DROP INDEX [IDX_Staging_RecordID] ON [dbo].[Staging] WITH ( ONLINE = OFF )
GO
/****** Object: Index [FIDX_StagingRecID2] Script Date: 09/19/2013 07:17:34 ******/
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[Staging]')
AND name = N'FIDX_StagingRecID2')
DROP INDEX [FIDX_StagingRecID2] ON [dbo].[Staging] WITH ( ONLINE = OFF )
GO
/****** Object: Index [FIDX_StagingRecID3] Script Date: 09/19/2013 07:17:34 ******/
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[Staging]')
AND name = N'FIDX_StagingRecID3')
DROP INDEX [FIDX_StagingRecID3] ON [dbo].[Staging] WITH ( ONLINE = OFF )
GO
/****** Object: Index [CIDX_Staging_IDRecID] Script Date: 09/19/2013 07:19:35 ******/
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[Staging]')
AND name = N'CIDX_Staging_IDRecID')
DROP INDEX [CIDX_Staging_IDRecID] ON [dbo].[Staging] WITH ( ONLINE = OFF )
GO
SET ANSI_PADDING ON
GO
CREATE CLUSTERED INDEX CIDX_Staging_IDRecID ON Staging (ID, RecordID) ON [PRIMARY];
CREATE NONCLUSTERED INDEX IDX_Staging_UNID ON Staging (UNID)
INCLUDE (ID, RecordID, Extract)
WITH (PAD_INDEX = OFF) ON [PRIMARY];
CREATE NONCLUSTERED INDEX IDX_Staging_ID ON Staging (ID)
INCLUDE (RecordID, UNID)
WITH (PAD_INDEX = OFF) ON [PRIMARY];
CREATE NONCLUSTERED INDEX FIDX_StagingRecID3 ON Staging (RecordID, ID)
INCLUDE (PNum, ECD, PCNum, EDate, CNum, ADate, NDDate, UA,
FProd, FC, PC, SP, COB, Cat, Amt,
Cur)
WHERE RecordID = 3
WITH (PAD_INDEX = OFF) ON [PRIMARY];
CREATE NONCLUSTERED INDEX FIDX_StagingRecID2 ON Staging (RecordID, ID)
INCLUDE (CoCd, ANum, AOrg, TNum, INum, BPartner, CBPNum,
DNum, PDate)
WHERE RecordID = 2
WITH (PAD_INDEX = OFF) ON [PRIMARY];
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[tblTransactionDetail] Script Date: 09/18/2013 07:54:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tblTransactionDetail](
[TransactionDetailID] [int] IDENTITY(1,1) NOT NULL,
[TransactionID] [int] NULL,
[PNum] [varchar](4) NOT NULL,
[ECD] [varchar](4) NOT NULL,
[PCNum] [varchar](20) NULL,
[EDate] [varchar](8) NULL,
[CNum] [varchar](24) NULL,
[ADate] [varchar](8) NULL,
[NDDate] [varchar](8) NULL,
[UA] [varchar](24) NOT NULL,
[FProd] [varchar](10) NOT NULL,
[FC] [varchar](4) NOT NULL,
[PC] [varchar](10) NOT NULL,
[SP] [varchar](4) NOT NULL,
[COB] [varchar](24) NOT NULL,
[Cat] [varchar](3) NOT NULL,
[Amt] [varchar](17) NOT NULL,
[Cur] [varchar](5) NOT NULL,
CONSTRAINT [PK_tblTransactionDetail_TransactionDetailID] PRIMARY KEY NONCLUSTERED
(
[TransactionDetailID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[tblTransactionDetail]')
AND name = N'CIDX_tblTransDetail_TransDetIDPNum')
DROP INDEX [CIDX_tblTransDetail_TransDetIDPNum] ON [dbo].[tblTransactions] WITH ( ONLINE = OFF )
GO
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[tblTransactionDetail]')
AND name = N'IDX_tblTransDetail_PNum')
DROP INDEX [IDX_tblTransDetail_PNum] ON [dbo].[tblTransactionDetail] WITH ( ONLINE = OFF )
GO
CREATE CLUSTERED INDEX CIDX_tblTransDetail_TransDetIDPNum ON [dbo].[tblTransactionDetail]
(TransactionDetailID, PNum)
WITH (PAD_INDEX = ON, FILLFACTOR = 80, STATISTICS_NORECOMPUTE = OFF) ON [PRIMARY];
CREATE NONCLUSTERED INDEX IDX_tblTransDetail_PNum ON [dbo].[tblTransactionDetail] (PNum)
INCLUDE (ECD, PCNum, EDate, CNum, ADate, NDDate, UA, FProd, FC, PC, SP, COB, Cat, Amt, Cur)
WITH (PAD_INDEX = ON, FILLFACTOR = 80, STATISTICS_NORECOMPUTE = OFF) ON [PRIMARY];
/****** Object: Table [dbo].[tblTransactions] Script Date: 09/18/2013 07:54:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tblTransactions](
[TransactionID] [int] IDENTITY(1,1) NOT NULL,
[CoCd] [varchar](4) NOT NULL,
[ANum] [varchar](10) NOT NULL,
[AOrg] [varchar](3) NOT NULL,
[TNum] [varchar](20) NOT NULL,
[INum] [varchar](8) NOT NULL,
[BPartner] [varchar](10) NOT NULL,
[CBPNum] [varchar](10) NOT NULL,
[DNum] [varchar](30) NULL,
[PDate] [varchar](8) NULL,
CONSTRAINT [PK_tblTransactions_TransactionID] PRIMARY KEY NONCLUSTERED
(
[TransactionID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[tblTransactions]')
AND name = N'CIDX_tblTransactions_TransIDPDate')
DROP INDEX [CIDX_tblTransactions_TransIDPDate] ON [dbo].[tblTransactions] WITH ( ONLINE = OFF )
GO
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[tblTransactions]')
AND name = N'IDX_tblTransactions_PDateCoCd')
DROP INDEX [IDX_tblTransactions_PDateCoCd] ON [dbo].[tblTransactions] WITH ( ONLINE = OFF )
GO
CREATE CLUSTERED INDEX CIDX_tblTransactions_TransIDPDate ON [dbo].[tblTransactions]
(TransactionID, PDate)
WITH (PAD_INDEX = ON, FILLFACTOR = 80, STATISTICS_NORECOMPUTE = OFF) ON [PRIMARY];
CREATE NONCLUSTERED INDEX IDX_tblTransactions_PDateCoCd ON [dbo].[tblTransactions]
(TransactionID)
INCLUDE (ANum, AOrg, TNum, INum, BPartner, CBPNum, DNum)
WITH (PAD_INDEX = ON, FILLFACTOR = 80, STATISTICS_NORECOMPUTE = OFF) ON [PRIMARY];
September 20, 2013 at 5:45 am
Is that plan with them being run together with other stuff, or them running separately?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 20, 2013 at 6:06 am
GilaMonster (9/20/2013)
Is that plan with them being run together with other stuff, or them running separately?
Running together with GO between parts of the statement. I'm about to run the two UPDATE statements together but by themselves to check something else. I'll grab another sqlplan for that.
September 20, 2013 at 6:17 am
Here's the UPDATE statement with the Key Lookup. To be honest, it isn't taking very long to run. My biggest issue is with another part of the query. Still, I'd like to fix this before it does become a problem.
EDIT: Here's the UPDATE code. And if you want more detail on what's going on with this, here's a link to the original post that started this all.
UPDATE ri
SET CoCd = LTRIM(RTRIM(Substring(Extract_RI, 2,4))),
ANum = LTRIM(RTRIM(Substring(Extract_RI, 6,10))),
AOrg = LTRIM(RTRIM(Substring(Extract_RI, 16,3))),
TNum = LTRIM(RTRIM(Substring(Extract_RI, 19,20))),
INum = LTRIM(RTRIM(Substring(Extract_RI, 39,8))),
BPartner = LTRIM(RTRIM(Substring(Extract_RI, 47,10))),
CBPNum = LTRIM(RTRIM(Substring(Extract_RI, 57,10))),
DNum = LTRIM(RTRIM(Substring(Extract_RI, 67,30))),
PDate = LTRIM(RTRIM(Substring(Extract_RI, 97,8)))
FROM dbo.Staging ri
WHERE RecordID = 2;
September 20, 2013 at 1:39 pm
Brandie Tarvin (9/20/2013)
Here's the UPDATE statement with the Key Lookup. To be honest, it isn't taking very long to run. My biggest issue is with another part of the query. Still, I'd like to fix this before it does become a problem.EDIT: Here's the UPDATE code. And if you want more detail on what's going on with this, here's a link to the original post that started this all.
UPDATE ri
SET CoCd = LTRIM(RTRIM(Substring(Extract_RI, 2,4))),
ANum = LTRIM(RTRIM(Substring(Extract_RI, 6,10))),
AOrg = LTRIM(RTRIM(Substring(Extract_RI, 16,3))),
TNum = LTRIM(RTRIM(Substring(Extract_RI, 19,20))),
INum = LTRIM(RTRIM(Substring(Extract_RI, 39,8))),
BPartner = LTRIM(RTRIM(Substring(Extract_RI, 47,10))),
CBPNum = LTRIM(RTRIM(Substring(Extract_RI, 57,10))),
DNum = LTRIM(RTRIM(Substring(Extract_RI, 67,30))),
PDate = LTRIM(RTRIM(Substring(Extract_RI, 97,8)))
FROM dbo.Staging ri
WHERE RecordID = 2;
In just a quick review of your DDL, I didn't see an index that would cover this query. FIDX_StagingRecID2 comes close but it doesn't include the Extract_RI column. Since SQL Server can't perform both steps necessary to prepare for the UPDATE TABLE operation (identify the rows to be updated and compute the values for other columns using the value of Extract_RI) by reference to this index only, I suspect it is variously deciding to either scan the clustered index or use a nonclustered index to identify the rows to be updated and a key lookup to get the Extract_RI value.
Jason Wolfkill
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply