Select columns affect execution plan?

Question

I have a query something like this. It has an execution plan using an index that I expect, up until the amount of data (i.e. the number of characters) returned by the SELECT goes over a boundary. At that point, the plan no longer uses the index and the query gets 100+ times slower.

If I use NVARCHAR(203), it is fast. NVARCHAR(204) is slow. Also, when it does not use the index, it totally burns up the CPU. At least it seems to me to be a data size problem, but I am looking for any insight.

I have changed oldValueString and newValueString to NVARCHAR(255) and things are a little better, but I still can't query all of the columns w/o losing the index in the plan.

SELECT
   [Lx_AuditColumn].[auditColumnPK],
   CONVERT(NVARCHAR(204), [Lx_AuditColumn].[newValueString])
FROM
   [dbo].[Lx_AuditColumn] [Lx_AuditColumn],
   [dbo].[Lx_AuditTable] [Lx_AuditTable]
WHERE
   [Lx_AuditColumn].[auditTableFK] = [Lx_AuditTable].[auditTablePK]
AND
   [Lx_AuditTable].[createdDate] >=  @P1
AND
   [Lx_AuditTable].[createdDate] <=  @P2
ORDER BY
   [Lx_AuditColumn].[auditColumnPK] DESC

This is the basic structure of tables (I eliminated some indexes and FK constraints).

CREATE TABLE [dbo].[Lx_AuditTable]
(
   [auditTablePK] [int] NOT NULL IDENTITY(1, 1) ,
   [firmFK] [int] NOT NULL ,
   [auditMasterFK] [int] NOT NULL ,
   [codeSQLTableFK] [int] NOT NULL ,
   [objectFK] [int] NOT NULL ,
   [projectEntityID] [int] NULL ,
   [createdByFK] [int] NOT NULL ,
   [createdDate] [datetime] NOT NULL ,
   CONSTRAINT [Lx_PK_AuditTable_auditTablePK] PRIMARY KEY CLUSTERED
   (
      [auditTablePK]
   ) WITH FILLFACTOR = 90
)
GO

CREATE INDEX [Lx_IX_AuditTable_createdDatefirmFK]
   ON [dbo].[Lx_AuditTable]([createdDate], [firmFK])
   INCLUDE ([auditTablePK], [auditMasterFK])
   WITH (FILLFACTOR = 90, ONLINE = OFF)
GO

CREATE TABLE [dbo].[Lx_AuditColumn]
(
   [auditColumnPK] [int] NOT NULL IDENTITY(1, 1) ,
   [firmFK] [int] NOT NULL ,
   [auditTableFK] [int] NOT NULL ,
   [accessorName] [nvarchar] (100) NOT NULL ,
   [dataType] [nvarchar] (20) NOT NULL ,
   [oldValueNumber] [int] NULL ,
   [oldValueString] [nvarchar] (4000) NULL ,
   [newValueNumber] [int] NULL ,
   [newValueString] [nvarchar] (4000) NULL ,
   [newValueText] [ntext] NULL ,
   CONSTRAINT [Lx_PK_AuditColumn_auditColumnPK] PRIMARY KEY CLUSTERED
   (
      [auditColumnPK]
   ) WITH FILLFACTOR = 90 ,
   CONSTRAINT [Lx_FK_AuditColumn_auditTableFK] FOREIGN KEY
   (
      [auditTableFK]
   ) REFERENCES [dbo].[Lx_AuditTable] (
      [auditTablePK]
   )
)
GO

CREATE INDEX [Lx_IX_AuditColumn_auditTableFK]
   ON [dbo].[Lx_AuditColumn]([auditTableFK])
   WITH (FILLFACTOR = 90, ONLINE = OFF)
GO

Good:

Bad:

Answer 1

With this setup - without knowing the table structure in detail (yet) - you should definitely:

• have a good clustered index on your table dbo.Lx_AuditColumn (something like a INT IDENTITY is almost perfect)
• a nonclustered index on Lx_AuditColumn.auditTableFK to speed up JOINs and referential integrity checks
• a nonclustered index on Lx_AuditColumn.AuditColumnPK (unless that's already the clustered PK, of course!)
• a nonclustered index on Lx_AuditTable.CreatedDate

Also: you should use the proper ANSI/ISO Standard INNER JOIN syntax (instead of just having a comma-separated list of tables to select from - see Bad habits to kick : using old-style JOINs for background info on this topic) - use this query:

SELECT
   [Lx_AuditColumn].[auditColumnPK],
   CONVERT(NVARCHAR(204), [Lx_AuditColumn].[newValueString])
FROM
   [dbo].[Lx_AuditColumn] [Lx_AuditColumn]
INNER JOIN
   [dbo].[Lx_AuditTable] [Lx_AuditTable] ON [Lx_AuditColumn].[auditTableFK] = [Lx_AuditTable].[auditTablePK]
WHERE
   [Lx_AuditTable].[createdDate] >=  @P1
   AND
   [Lx_AuditTable].[createdDate] <=  @P2
ORDER BY
   [Lx_AuditColumn].[auditColumnPK] DESC

Answer 2

While I cannot give an elegant solution to this problem (apart from trying the usual things like indexes, statistics, indexed views), I can hack-resolve the problem:

Convert the query to use JOIN syntax and apply a hint:

INNER HASH JOIN ...

This will force a hash join also also fix the join order.

This is not good, because SQL Server can no longer adapt to changing schema and data.

Answer 3

I changed oldValueString and newValueString to NVARCHAR(255) and things got a little better. However, it was after I forcefully re-created the table with the shortened columns that things got back to "normal". I added a bogus nvarchar(10) column, used design mode to convert it to an int (i.e. force designer to create a new table and copy the data), then deleted the extra column. Maybe bouncing the server or something else would have fixed this problem, but I was able to do it like with this w/o bouncing the server.