I am looking into a minor performance issue, where an optimizer tool is basically saying, "hey just go ahead and include all the columns on this table in this index" which is a horrible solution in my opinion. My thought process brought me to think "why don't I go ahead and give it everything in this existing index to do its sort and TOP(N) operation and cut out this massive 100k key lookup operation, surely SQL Server can do that and change from 100k key lookups to N." That was not what I saw, what I saw was that nothing changed at all, it still did all the key lookups and sort after that. As below Very simply removing the select of the other columns not in the index changes it to not need a key lookup at all of course. I have seen many workarounds using CTE to get around this, but I am using Entity Framework for this query and simply playing around with the query isn't as easy as that. I would like the primary purpose of this question to be WHY does this happen? Seems like a trivial operation thing to do the sort and top clause prior to the key lookup loop if you are able. It not doing this seems a glaring weakness in the platform. I am asking why this behavior exists, not how can I improve this query's performance.

I have a table such as the following:

create table [Thing]
(
	[Id] int constraint [PK_Thing_Id] primary key,
	[Status] nvarchar(20),
	[Timestamp] datetime2,
	[Foo] nvarchar(100)
)

with a non-clustered, non-covering index on the Status and Timestamp fields:

create nonclustered index [IX_Status_Timestamp] on [Thing] ([Status], [Timestamp] desc)

If I query for a 'page' of these rows, using offset/fetch as follows,

select * from [Thing]
where Status = 'Pending'
order by [Timestamp] desc
offset 2000 rows
fetch next 1000 rows only

I understand that the query will need to read a total of 3000 rows to find the 1000 that I'm interested in. I would then expect it to perform key lookups for each of those 1000 rows to fetch the fields not included in the index. However, the execution plan indicates that it is doing key lookups for all 3000 rows. I don't understand why, when the only criteria (filter by [Status] and order by [Timestamp]) are both in the index. If I rephrase the query with a cte, as follows, I get more or less what I expected the first query to do:

with ids as
(
	select Id from [Thing]
	where Status = 'Pending'
	order by [Timestamp] desc
	offset 2000 rows
	fetch next 1000 rows only
)

select t.* from [Thing] t
join ids on ids.Id = t.Id
order by [Timestamp] desc

Some statistics from SSMS to compare the 2 queries: | | Original | With CTE | |---------------|----------|----------| | Logical reads | 12265 | 4140 | | Subtree cost | 9.79 | 3.33 | | Memory grant | 0 | 3584 KB | The CTE version seems 'better' at first glance, although I don't know how much weight to place on the fact that it incurs a memory grant for a worktable. (The messages from set statistics io on indicate that there were zero reads of any kind on the worktable) Am I wrong in saying that the first query should be able to isolate the relevant 1000 rows first (even though that requires reading past 2000 other rows first), and then only do key lookups on those 1000? It seems a bit odd to have to try and 'force' that behaviour with the CTE query. (As a minor second question: I'm assuming that the last part of the CTE approach needs to do its own order by on the results of the join, even though the CTE itself had an order by, as the ordering might be lost during the join. Is this correct?)

I'm trying to learn about covering indexes. In the Northwind database, I select from the table *Categories*: As you can see the table has a non-clustered index on the column *CategoryName*. This SQL query: select CategoryName from Categories where Categories.CategoryName like 'Beverages' returns an execution plan with an index seek: However, this: select CategoryName ,Description from Categories where Categories.CategoryName like 'Beverages' returns this execution plan with an index scan using the primary key index, which isn't expected: I can find the expected behaviour only when I force the query with the non-clustered index: select CategoryName ,Description from Categories with(index(CategoryName)) where Categories.CategoryName like 'Beverages' What is the problem?

I am trying to understand why using a table variable is preventing the optimizer from using an index seek and then bookmark lookup versus an index scan. Populating the table: CREATE TABLE dbo.Test ( RowKey INT NOT NULL PRIMARY KEY, SecondColumn CHAR(1) NOT NULL DEFAULT 'x', ForeignKey INT NOT NULL ) INSERT dbo.Test ( RowKey, ForeignKey ) SELECT TOP 1000000 ROW_NUMBER() OVER (ORDER BY (SELECT 0)), ABS(CHECKSUM(NEWID()) % 10) FROM sys.all_objects s1 CROSS JOIN sys.all_objects s2 CREATE INDEX ix_Test_1 ON dbo.Test (ForeignKey) Populate a table variable with a single record and attempt to lookup the primary key and the second column by searching on the foreign key column: DECLARE @Keys TABLE (RowKey INT NOT NULL) INSERT @Keys (RowKey) VALUES (10) SELECT t.RowKey, t.SecondColumn FROM dbo.Test t INNER JOIN @Keys k ON t.ForeignKey = k.RowKey Below is the execution plan: Now the same query using a temp table instead: CREATE TABLE #Keys (RowKey INT NOT NULL) INSERT #Keys (RowKey) VALUES (10) SELECT t.RowKey, t.SecondColumn FROM dbo.Test t INNER JOIN #Keys k ON t.ForeignKey = k.RowKey This query plan uses a seek and bookmark lookup: Why the optimizer is willing do the bookmark lookup with the temp table, but not the table variable? The table variable is used in this example to represent data coming through a user-defined table type in a stored procedure. I realize the index seek might not be appropriate if the foreign key value occurred hundreds of thousands of times. In that case, a scan would probably be a better choice. For the scenario I created, there was no row with a value of 10. I still think the behavior is interesting and would like to know if there is a reason for it. SQL Fiddle Adding OPTION (RECOMPILE) did not change the behavior. The UDDT has a primary key. @@VERSION is SQL Server 2008 R2 (SP2) - 10.50.4042.0 (X64) (Build 7601: Service Pack 1) (Hypervisor)

I've implemented a covering index to avoid a key lookup: CREATE INDEX IX_StatusHistory_Covering ON StatusHistory(ID) INCLUDE (Status_ID, StatusComment, StatusReason_ID, StatusReasonComment, UserEnteredStatusDateTime, ChangeDateTime, ChangedBy_UserName, IMWBWagonMass) WITH (ONLINE= ON) But the key lookup is still happening. Execution Plan Here is the query causing this: DECLARE @default_tare DECIMAL(18,2) = 19.94 SELECT TOP 100 *, [Gross (WI)] - ISNULL([Tare (WX)],@default_tare) AS [Arrived Nett (WI-WX)], [Gross (WN)] - ISNULL([Tare (WT)],@default_tare) AS [Tipped Nett (WN-WT)], [Client Weight] - ([Gross (WI)] - ISNULL([Tare (WX)],@default_tare)) AS [Arrived Variance], [Client Weight] - ([Gross (WN)] - ISNULL([Tare (WT)],@default_tare)) AS [Tipped Variance] FROM (SELECT CASE WHEN LoadedWeight > 9999 THEN LoadedWeight/1000 ELSE LoadedWeight END [Client Weight] ,CASE WHEN his.Status_Code IN ('WH','WI') THEN his.IMWBWagonMass END AS [Gross (WI)] ,CASE WHEN his.Status_Code ='WN' THEN his.IMWBWagonMass END AS [Gross (WN)] ,CASE WHEN his.Status_Code = 'WX' THEN his.[IMWBWagonMass] ELSE NULL END AS [Tare (WX)] ,CASE WHEN his.Status_Code = 'WT' THEN his.[IMWBWagonMass] ELSE NULL END AS [Tare (WT)] ,dp.Description AS Commodity ,dp.Grade ,Header_Client_Name AS Client ,Header_Destination_Name AS Destination ,Header_Origin_Name AS Origin ,Header_Product_Name AS [Commodity Name] ,Header_ProductSubCategory_Name AS [Commodity Group] ,his.StatusComment AS [Status Comment] ,LEFT(LOWER(Rcd.Header_LastUpdatedByUser_UserName),CASE WHEN Header_LastUpdatedByUser_UserName 'System' THEN CHARINDEX( '@',Rcd.Header_LastUpdatedByUser_UserName)-1 ELSE 999 END) AS [Last updated by] ,ISNULL(Header_StatusDateTime, Header_CreatedDateTime) AS [Last Updated] ,st.Name AS [Status Name] ,StatusReasonComment AS [Reason Comment] ,StatusReasonName AS [Reason Name] ,UserEnteredStatusDateTime AS [Status Actual Event Time] ,ChangeDateTime AS [Status System Change Time] ,ChangedBy_UserName AS [Status Changed by] ,st.Code AS [Status Code] ,sr.Code AS [Reason Code] ,his.ID AS [Status History ID] ,his.StatusReasonDateTime AS [Reason Time] ,his.IMWBWagonMass AS [Wagon Status History Mass] ,rcd.ID AS [Wagon Line ID] ,Wagon_Number AS [Wagon Number] ,GrossWeight AS [Gross (Most Recent)] ,TareWeight AS [Tare (Most Recent)] ,Tarps ,DelayDescription AS [Delay Description] ,rcd.StatusComment AS [Current Wagon Status Comment] ,rcd.StatusDateTime AS [Current Wagon Status Date Time] ,rcd.Status_Code AS [Current Wagon Status Code] ,rcd.Status_Name AS [Current Wagon Status] ,RailConsignment_ID ,Header_Number AS [Consignment] ,Header_DepartureFromClientDateTime AS [Departure Time] ,Header_EstimatedArrivalDateTime AS [Estimated Arrival Time] ,Header_IsInbound ,Header_PermitNumber AS [Permit Nr] ,Header_RailAccountNumber AS [Rail Account Nr] ,Header_Sender_Name AS [Sender] ,Header_SenderSidingNumber AS [Sender Siding Nr] ,Header_Status_Code AS [Train Current Status Code] ,Header_Status_Name AS [Train Current Status] ,Header_StatusDateTime AS [Train Current Status Actual Event Time] ,Header_Train AS [Train] ,Header_CreatedDateTime AS [Created On] ,rcd.dw_timestamp ,CreatedDateTime AS [Wagon Created On] ,Header_Contract_Number AS [Contract Nr] ,rcd.IMWBWagonMass AS [Wagon Mass (Most Recent)] ,Header_WeeklyRailPlan_ID AS [NWB ID] ,NettWeight AS [Nett (Most Recent)] ,CASE WHEN rcd.Status_Code = 'WA' THEN rcd.Header_StatusDateTime ELSE Header_WAChangeDateTime END AS [Wagons Uncoupled From Locomotive] ,CASE WHEN rcd.Status_Code = 'WF' THEN rcd.Header_StatusDateTime ELSE Header_WFChangeDateTime END AS [Consignment Finalised] ,CASE WHEN rcd.Status_Code = 'WG' THEN rcd.Header_StatusDateTime ELSE Header_WGChangeDateTime END AS [Consignment at the Gate] FROM RailConsignmentDetails AS rcd WITH(NOLOCK) JOIN FullStatusHistoryRailLine AS his WITH(NOLOCK) on his.ID = rcd.ID JOIN DimProduct AS dp WITH(NOLOCK) ON dp.ID = rcd.Header_Product_ID LEFT JOIN DimConsignmentStatus AS st WITH(NOLOCK)ON st.ID = his.Status_ID LEFT JOIN DimStatusReasons AS sr WITH(NOLOCK) ON st.ID = his.StatusReason_ID WHERE Header_Deleted = 0 AND rcd.Deleted = 0 AND ISNULL(Header_StatusDateTime, Header_CreatedDateTime) BETWEEN CAST(DATEADD(MONTH,-2,GETDATE()) AS DATE) AND GETDATE()+ 1) AS t The FullStatusHistoryLine view is: CREATE VIEW [dbo].[FullStatusHistoryRailLine] AS SELECT sh.PK, sh.ID, CAST(sh.Number AS VARCHAR(255)) AS Number, sh.Status_ID, sh.Status_Name, sh.StatusComment, sh.StatusReason_ID, sh.StatusReasonComment, dsr.Name AS StatusReasonName, sh.UserEnteredStatusDateTime, sh.ChangeDateTime, sh.ChangedBy_Id, sh.ChangedBy_UserName, sh.dw_timestamp, dcs.code AS Status_Code, sh.StatusReason_Code, prev.StatusReasonDateTime AS FromStatusReasonDateTime, sh.StatusReasonDateTime, sh.IMWBWagonMass FROM StatusHistory AS sh INNER JOIN DimConsignmentStatus AS dcs ON sh.Status_ID = dcs.ID LEFT JOIN DimStatusReasons AS dsr ON dsr.ID = sh.StatusReason_ID OUTER APPLY (SELECT TOP 1 StatusReasonDateTime FROM StatusHistory WHERE ID = sh.ID AND Number = sh.Number AND IsHeader = sh.IsHeader AND TransportMode_Name = sh.TransportMode_Name AND StatusReasonDateTime < sh.StatusReasonDateTime ORDER BY StatusReasonDateTime DESC) AS prev WHERE sh.TransportMode_Name = 'Rail' AND sh.IsHeader = 0 UNION ALL SELECT CAST(rcd.ID AS VARCHAR) + '_' + CAST(ISNULL(CONVERT(VARCHAR(64),rcd.StatusReasonDateTime,126),CONVERT(VARCHAR(64),rcd.LastUpdatedDateTime,126)) AS VARCHAR) + '_L' AS PK, rcd.ID, CAST(rcd.Wagon_Number AS VARCHAR(255)) AS Number, rcd.Status_ID, rcd.Status_Name, rcd.StatusComment, rcd.StatusReason_ID, '' AS StatusReasonComment, rcd.StatusReason_Name AS StatusReasonName, rcd.StatusDateTime AS UserEnteredStatusDateTime, rcd.StatusSavedDateTime AS ChangeDateTime, rcd.LastUpdatedByUser_Id AS ChangedBy_Id, rcd.LastUpdatedByUser_UserName AS ChangedBy_UserName, rcd.dw_timestamp, rcd.Status_Code, rcd.StatusReason_Code, prev.StatusReasonDateTime AS FromStatusReasonDateTime, rcd.StatusReasonDateTime, rcd.IMWBWagonMass FROM RailConsignmentDetails AS rcd OUTER APPLY (SELECT TOP 1 StatusReasonDateTime FROM StatusHistory AS sh WHERE sh.ID = rcd.ID AND Number = CAST(rcd.Wagon_Number AS VARCHAR(255)) AND IsHeader = 0 AND TransportMode_Name = 'Rail' AND StatusReasonDateTime < rcd.Header_StatusReasonDateTime ORDER BY StatusReasonDateTime DESC) AS prev And finally the DDL: CREATE TABLE [dbo].[StatusHistory]( [PK] [varchar](99) NOT NULL, [ID] [int] NOT NULL, [Number] [varchar](255) NULL, [Status_ID] [int] NULL, [Status_Name] [nvarchar](max) NOT NULL, [StatusComment] [varchar](255) NULL, [StatusReason_ID] [int] NULL, [StatusReasonComment] [varchar](255) NULL, [UserEnteredStatusDateTime] [datetime] NULL, [ChangeDateTime] [datetime] NULL, [ChangedBy_Id] [nvarchar](128) NOT NULL, [ChangedBy_UserName] [nvarchar](256) NOT NULL, [IsHeader] [bit] NOT NULL, [dw_timestamp] [datetime] NOT NULL, [EventTime] [datetime] NOT NULL, [StatusReason_Code] [nvarchar](max) NULL, [StatusReasonDateTime] [datetime] NULL, [TransportMode_Name] [varchar](5) NULL, [IMWBWagonMass] [decimal](18, 2) NULL, [RoadFirstWeight] [decimal](18, 2) NULL, [RoadSecondWeight] [decimal](18, 2) NULL, [DraftSurveyTons] [decimal](18, 2) NULL, [StatusHistory_key] [int] IDENTITY(1,1) NOT NULL, CONSTRAINT [XPK_StatusHistory] PRIMARY KEY CLUSTERED ( [StatusHistory_key] ASC )WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Don't nonclustered indexes inherently store a reference to the primary key on a table so that it can do a key lookup as needed?...if so, is it any less or more performant to specify the primary key as an included column when creating a nonclustered index? Side question, why does the nonclustered index default to storing the primary key and not the clustered index fields to do a key lookup on the table?...In the cases where the primary key is not the clustered index, isn't it slower for the key lookup to occur whereas if it stored the clustered index it could do the lookup that way?

I've noticed in my system, whenever a non-clustered index is used in a query that has to also do a key lookup to get the additional fields being selected, it's faster for me to instead do two queries. The first with the non-clustered index inserting only the key field into a temp table (so no key lookup is performed) and the second using that temp table to join back to the original table to filter it down on the key and then select the fields I need. I'm typically querying tables with hundreds of millions to tens of billions of rows when I notice this. I'm not sure if it can be related to the fact that I'm eliminating the key lookup when the table is first loaded into memory and instead I'm inserting the key into a temp table so that the subsequent field lookup query occurs between two tables already in memory? The difference in time I'll see is usually significant too, e.g. on the order of minutes.

I've been trying to get rid of expensive lookups in my query plan but cannot seem to get my head around it. I understand the idea of creating a covering index to eliminate the need for lookups but I don't know how to approach it with a complicated plan like this. Any ideas on how to approach this would really be appreciated. Query plan can be found here: https://www.brentozar.com/pastetheplan/?id=HyBbQ0eRr Thanks

How can I eliminate a Key Lookup (Clustered) operator in my execution plan? Table tblQuotes already has a clustered index (on QuoteID) and 27 nonclustered indexes, so I am trying not to create any more. I put the clustered index column QuoteID in my query, hoping it will help - but unfortunately still the same. Execution plan here . Or view it: This is what the Key Lookup operator says: Query: declare @EffDateFrom datetime ='2017-02-01', @EffDateTo datetime ='2017-08-28' SET NOCOUNT ON SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED IF OBJECT_ID('tempdb..#Data') IS NOT NULL DROP TABLE #Data CREATE TABLE #Data ( QuoteID int NOT NULL, --clustered index [EffectiveDate] [datetime] NULL, --not indexed [Submitted] [int] NULL, [Quoted] [int] NULL, [Bound] [int] NULL, [Exonerated] [int] NULL, [ProducerLocationId] [int] NULL, [ProducerName] [varchar](300) NULL, [BusinessType] [varchar](50) NULL, [DisplayStatus] [varchar](50) NULL, [Agent] [varchar] (50) NULL, [ProducerContactGuid] uniqueidentifier NULL ) INSERT INTO #Data SELECT tblQuotes.QuoteID, tblQuotes.EffectiveDate, CASE WHEN lstQuoteStatus.QuoteStatusID >= 1 THEN 1 ELSE 0 END AS Submitted, CASE WHEN lstQuoteStatus.QuoteStatusID = 2 or lstQuoteStatus.QuoteStatusID = 3 or lstQuoteStatus.QuoteStatusID = 202 THEN 1 ELSE 0 END AS Quoted, CASE WHEN lstQuoteStatus.Bound = 1 THEN 1 ELSE 0 END AS Bound, CASE WHEN lstQuoteStatus.QuoteStatusID = 3 THEN 1 ELSE 0 END AS Exonareted, tblQuotes.ProducerLocationID, P.Name + ' / '+ P.City as [ProducerName], CASE WHEN tblQuotes.PolicyTypeID = 1 THEN 'New Business' WHEN tblQuotes.PolicyTypeID = 3 THEN 'Rewrite' END AS BusinessType, tblQuotes.DisplayStatus, tblProducerContacts.FName +' '+ tblProducerContacts.LName as Agent, tblProducerContacts.ProducerContactGUID FROM tblQuotes INNER JOIN lstQuoteStatus on tblQuotes.QuoteStatusID=lstQuoteStatus.QuoteStatusID INNER JOIN tblProducerLocations P On P.ProducerLocationID=tblQuotes.ProducerLocationID INNER JOIN tblProducerContacts ON dbo.tblQuotes.ProducerContactGuid = tblProducerContacts.ProducerContactGUID WHERE DATEDIFF(D,@EffDateFrom,tblQuotes.EffectiveDate)>=0 AND DATEDIFF(D, @EffDateTo, tblQuotes.EffectiveDate) <=0 AND dbo.tblQuotes.LineGUID = '6E00868B-FFC3-4CA0-876F-CC258F1ED22D'--Surety AND tblQuotes.OriginalQuoteGUID is null select * from #Data Execution Plan:

Are there any performance differences between when a non-clustered index uses the clustered index's key to locate the row vs when that table doesn't have a clustered index and the non-clustered index locates the row via the RID? Does different levels of fragmentation impact this performance comparison as well? (E.g. in both scenarios the tables are 0% fragmented, vs 50%, vs 100%.)

I have tried to create a non-clustered index on the fields that are in the output list which are created_by and Chk1002 .I don't have a column called Chk1002 anywhere . I have read here that it has to do with the Cursor . Is there any way I could keep the Cursor and get reduce the cost of the Query plan and get rid of the Key Lookup? DECLARE @ClaimUniqueNo INT DECLARE ClaimAudit CURSOR FOR SELECT CA.Claim_Audit_ID, CA.Claim_Audit_Action, CA.Create_Date, CA.Created_By, C.Claim_Status FROM dbo.Claim_Audit_Tbl CA LEFT JOIN Claim_Tbl C ON CA.Claim_Unique_No = C.Claim_Unique_No WHERE CA.Claim_Unique_No = @ClaimUniqueNo ORDER BY Create_Date ASC

Suppose we have a dictionary that maps keys to sequences of values - for example: - potato: A, B, C - tomato: B - lettuce: C - carrot: A, C - cucumber: C I would like to perform queries such as: - query A, B, C returns potato - query C returns lettuce, cucumber (order shouldn't matter) I understand that the reverse dictionary lookup could be done with normalized relational databases (as suggested in this post). However, my intuition is that a non relational database could better fit this problem, like a graph. Additionally, I'm looking for something read-optimized. Is there a NoSQL database that fits this kind of problem? Where should I direct my search for such an implementation?

I am using SQL server, and I have been looking closely at the concept of key lookups, http://blog.sqlauthority.com/2009/10/07/sql-server-query-optimization-remove-bookmark-lookup-remove-rid-lookup-remove-key-lookup/ So if you have a key lookup you can create an index with the 'include' columns to cover the non index columns you have in the select statement. For instance, SELECT ID, FirstName FROM OneIndex WHERE City = 'Las Vegas' GO This index will include a key lookup, CREATE NONCLUSTERED INDEX [IX_OneIndex_City] ON [dbo].[OneIndex] ( [City] ASC ) ON [PRIMARY] GO But this one will remove the key lookup, CREATE NONCLUSTERED INDEX [IX_OneIndex_Include] ON [dbo].[OneIndex] ( City ) INCLUDE (FirstName,ID) ON [PRIMARY] GO I mean how much of an impact will this have on performance? The key lookup has an operator cost of 0.295969 (99%), but what does that really mean? How do you know that you need the second index there, and at what point does it become the case that you are trying to add too many indexes and it is not worth it? It seems to me that some queries can include index scans, key lookups, and still seem to perform very fast.

I have a persisted computed column on a table which is simply made up concatenated columns, e.g. CREATE TABLE dbo.T ( ID INT IDENTITY(1, 1) NOT NULL CONSTRAINT PK_T_ID PRIMARY KEY, A VARCHAR(20) NOT NULL, B VARCHAR(20) NOT NULL, C VARCHAR(20) NOT NULL, D DATE NULL, E VARCHAR(20) NULL, Comp AS A + '-' + B + '-' + C PERSISTED NOT NULL ); In this Comp is not unique, and D is the valid from date of each combination of A, B, C, therefore I use the following query to get the end date for each A, B, C (basically the next start date for the same value of Comp): SELECT t1.ID, t1.Comp, t1.D, D2 = ( SELECT TOP 1 t2.D FROM dbo.T t2 WHERE t2.Comp = t1.Comp AND t2.D > t1.D ORDER BY t2.D ) FROM dbo.T t1 WHERE t1.D IS NOT NULL -- DON'T CARE ABOUT INACTIVE RECORDS ORDER BY t1.Comp; I then added an index to the computed column to assist in this query (and also others): CREATE NONCLUSTERED INDEX IX_T_Comp_D ON dbo.T (Comp, D) WHERE D IS NOT NULL; The query plan however surprised me. I would have thought that since I have a where clause stating that D IS NOT NULL and I am sorting by Comp, and not referencing any column outside of the index that the index on the computed column could be used to scan t1 and t2, but I saw a clustered index scan. So I forced the use of this index to see if it yielded a better plan: SELECT t1.ID, t1.Comp, t1.D, D2 = ( SELECT TOP 1 t2.D FROM dbo.T t2 WHERE t2.Comp = t1.Comp AND t2.D > t1.D ORDER BY t2.D ) FROM dbo.T t1 WITH (INDEX (IX_T_Comp_D)) WHERE t1.D IS NOT NULL ORDER BY t1.Comp; Which gave this plan This shows that a Key lookup is being used, the details of which are: Now, according to the SQL-Server documentation: >You can create an index on a computed column that is defined with a deterministic, but imprecise, expression if the column is marked PERSISTED in the CREATE TABLE or ALTER TABLE statement. This means that the Database Engine stores the computed values in the table, and updates them when any other columns on which the computed column depends are updated. The Database Engine uses these persisted values when it creates an index on the column, and when the index is referenced in a query. This option enables you to create an index on a computed column when Database Engine cannot prove with accuracy whether a function that returns computed column expressions, particularly a CLR function that is created in the .NET Framework, is both deterministic and precise. So if, as the docs say *"the Database Engine stores the computed values in the table"*, and the value is also being stored in my index, why is a Key Lookup required to get A, B and C when they are not referenced in the query at all? I assume they are being used to calculate Comp, but why? Also, why can the query use the index on t2, but not on t1? *[Queries and DDL on SQL Fiddle](http://sqlfiddle.com/#!3/e47a1/2)* *N.B. I have tagged SQL Server 2008 because this is the version that my main problem is on, but I also get the same behaviour in 2012.*

I am trying to optimize following query (little bit more complex actually, but this is important part): SELECT Id, StatusDate, [...Lot Of Columns...] FROM ( ( SELECT Id, StatusDate, [...Lot Of Columns...] FROM Results_201505 WHERE A = 0, B = 1, C = 3 ) UNION ALL ( SELECT Id, StatusDate, [...Lot Of Columns...] FROM Results_201504 WHERE A = 0, B = 1, C = 3 ) UNION ALL ( SELECT Id, StatusDate, [...Lot Of Columns...] FROM Results_201503 WHERE A = 0, B = 1, C = 3 ) ) as result ORDER BY StatusDate DESC OFFSET xxx ROWS FETCH NEXT 25 ROWS ONLY On every table I've created index covering 'where' part: CREATE NONCLUSTERED INDEX ix_asdf ON Results_asdf (StatusDate DESC) INCLUDE (A, B, C) I can't use index A, B, C, StatusDate, because the WHERE conditions are changing depending on filters that are user selected. It can be WHERE A=, B=, C=, but also WHERE B=, D= or WHERE C= or without WHERE at all). My idea is that SQL Server will perform fast index scan with my index, and delay key lookup until last possible moment (fetching top 25 rows) - this seems to me like most reasonable thing to do. And indeed, if I comment out [... Lot of Columns...] everything goes as planned - index is used, no key lookup is necessary and everything is very fast. But in full form, SQL Server (according to execution plan, as shown by SSMS) tries to do key lookup *for each row*, before OFFSET ... FETCH 25. The reason is obvious (I believe) - Sql Server realizes that UNION ALL concatenates multiple tables, so after UNION it is too late to do key lookup (because after UNION SQL Server knows only ID, but doesn't know which results table it came from). ID is globally unique, but query optimizer probably doesn't know that. Of course I'm able to work this around with something like: SELECT Id, StatusDate, [ some way to select columns from appropiate table depending on result.tbl ] FROM ( ( SELECT Id, StatusDate, 'Results_201505' as tbl FROM Results_201505 WHERE A = 0, B = 1, C = 3 ) UNION ALL ( SELECT Id, StatusDate, 'Results_201504' as tbl FROM Results_201504 WHERE A = 0, B = 1, C = 3 ) UNION ALL ( SELECT Id, StatusDate, 'Results_201503' as tbl FROM Results_201503 WHERE A = 0, B = 1, C = 3 ) ) as result ORDER BY StatusDate DESC OFFSET xxx ROWS FETCH NEXT 25 ROWS ONLY But my question is - is there a better way? Some kind of hint to optimizer would be perfect, but if it's not possible - what's most elegant/fastest/best way to handle this query?

I have a query that is taking about 10 seconds to run and I am trying to optimize it. All the time is spent in the key lookup which is fine however in this case if I mouse over the key lookup in the actual execution plan it says Expected Rows : 1 Estimated Executions : 3341 Actual Rows : 0 Executions : 0 I understand in this case the statistics are expecting the index seek to find rows which is fine but as no rows were returned in the Index Seek why would the key lookup take any time? or even be shown in the query plan at all?

I have a query inner joining multiple tables all of which are partitioned and have 10 partitions each: SELECT A.COL1, B.COL2, C.COL3 FROM A INNER JOIN B ON A.ID = B.ID INNER JOIN C ON A.ID = C.ID WHERE COL20 < 10000 ---- COL20 IS NOT THE PARTITION KEY COLUMN In the actual query execution plan, for one of the tables, there is a non-clustered index scan with a key lookup. When I look at the properties for Key lookup in the actual execution plan, it looks like it's getting partition pruned. I'm confused as to why that would happen, is this like a negative impact on the system. I understand that key lookups themselves are bad, but why does it show that only 7 partitions have been accessed? The properties say: Key Lookup (Clustered) ---------------------------------- Actual number of rows: 215805 Actual Partition Count: 7 Actual Partitions Accessed: 3..9 My concern is around how the lookup works. In case, when there is no partitioning, when the lookup fetches the data from the data page for a particular key, would it do a seek or a scan to reach to this page? Would a direct key lookup perform better than a key lookup with partition pruning?