Prev: select statement ,sum with calculation
Next: Return a specific string based on positions of Y/N data
From: Jay on 5 Dec 2009 12:47
Ron, are you asking if two indexes will be used on the query at the same
time for your select? The second being used to narrow the results from the
first and thus reducing overall query time for ONE query?

"Ron" <Ron(a)discussions.microsoft.com> wrote in message
news:5D0782F7-1320-4349-A79C-05ADA4D4AF63(a)microsoft.com...
> Thanks Gert and Jay for your answers. Yes I see what you both mean, and
> understand. Your time appreciated.
>
> I suppose that when I see a 'seek' here, it doesn't necessarily mean that
> there isn't still a gain to be made somewhere. Ie the 'scan' you mention
> on
> index6 after the 'seek' on application id might benefit from a
> nonclustered
> index on index6?(specifically if the LIKE was SARGable ie (INDEX6 LIKE
> 'W%'))
>
> Any thoughts?
>
> "Gert-Jan Strik" wrote:
>
>> Ron,
>>
>> In case it wasn't already clear from Jay's response: the clustered index
>> is seeked because the predicate ApplicationID=99 uses the leading column
>> of this index, and because it is the clustered index, the index entries
>> include the rest of the row data, so they include INDEX6.
>>
>> What might not be clear when you read the query plan, is that the index
>> seek is only used to locate the first row (with ApplicationID=99). From
>> that point on, it will scan through the index to validate your other
>> predicate (INDEX6 LIKE '%W%'). It will do this until ApplicationID > 99
>> or until 5000 matching rows are found.
>>
>> --
>> Gert-Jan
>> SQL Server MVP
>>
>>
>> Ron wrote:
>> >
>> > Hi, yes, here is the full query, exec plan (SHOWPLAN_TEXT), table and
>> > Index.
>> > (I tried to reduce it down to an example for simplicity before, and
>> > fell into
>> > the trap of providing less than useful info). I've tried to lay this
>> > out as
>> > best as possible....
>> > Just to repeat, my question is why is there a clustered index seek,
>> > when
>> > index6 isn't in the clustered index (and is not SARGable?). Any
>> > thoughts
>> > greatly appreciated.
>> >
>> > ----Execution Plan----
>> >
>> > |--Top(TOP EXPRESSION:((5000)))
>> > |--Clustered Index
>> > Seek(OBJECT:([_archive].[dbo].[Docs].[IX_DOCUMENTS]
>> > AS [D]), SEEK:([D].[ApplicationID]=(99)),
>> > WHERE:([dbtest].[dbo].[Docs].[INDEX6] as [D].[INDEX6] like '%W%')
>> > ORDERED
>> > FORWARD)
>> >
>> > ----Query
>> >
>> > SELECT TOP 5000 [DocumentID], [ImportDate] FROM Docs AS D
>> > WHERE APPLICATIONID=99
>> > AND D.INDEX6 LIKE '%W%'
>> >
>> > ----Index
>> >
>> > CREATE CLUSTERED INDEX [IX_DOCUMENTS] ON [dbo].[Docs]
>> > (
>> > [ApplicationID] ASC,
>> > [IsTest] ASC,
>> > [BatchID] ASC,
>> > [ImportDate] DESC
>> > )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB
>> > =
>> > OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF,
>> > ALLOW_ROW_LOCKS
>> > = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
>> > GO
>> >
>> > ----Table
>> >
>> > CREATE TABLE [dbo].[Docs](
>> > [ID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
>> > [DocumentID] [uniqueidentifier] NOT NULL,
>> > [ApplicationID] [smallint] NOT NULL,
>> > [BatchID] [int] NOT NULL,
>> > [ImportDate] [datetime] NULL,
>> > [ValidFrom] [datetime] NOT NULL,
>> > [ValidTo] [datetime] NULL,
>> > [IsTest] [bit] NOT NULL,
>> > [DocumentName] [varchar](255) NOT NULL,
>> > [Extension] [varchar](50) NOT NULL,
>> > [IsCompressed] [bit] NOT NULL,
>> > [IsAttachmentOnly] [bit] NOT NULL,
>> > [Document] [image] NULL,
>> > [INDEX1] [varchar](255) NULL,
>> > [INDEX2] [varchar](255) NULL,
>> > [INDEX3] [varchar](255) NULL,
>> > [INDEX4] [varchar](255) NULL,
>> > [INDEX5] [varchar](255) NULL,
>> > [INDEX6] [varchar](255) NULL,
>> > [INDEX7] [varchar](255) NULL,
>> > [INDEX8] [varchar](255) NULL,
>> > [INDEX9] [varchar](255) NULL,
>> > [INDEX10] [varchar](255) NULL,
>> > [INDEX11] [varchar](255) NULL,
>> > [INDEX12] [varchar](255) NULL,
>> > [INDEX13] [varchar](255) NULL,
>> > [INDEX14] [varchar](255) NULL,
>> > [INDEX15] [varchar](255) NULL,
>> > [UsageCount] [int] NOT NULL,
>> > [PageJump] [int] NULL,
>> > [DocumentSize] [int] NULL,
>> > [IsApproved] [bit] NOT NULL,
>> > [CustomProperties] [xml] NOT NULL,
>> > [IsEnabled] [bit] NOT NULL,
>> > [msrepl_tran_version] [uniqueidentifier] NOT NULL,
>> > CONSTRAINT [PK_Docs] PRIMARY KEY NONCLUSTERED
>> > (
>> > [ID] ASC
>> > )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY
>> > =
>> > OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
>> > CONSTRAINT [DocumentID] UNIQUE NONCLUSTERED
>> > (
>> > [DocumentID] ASC
>> > )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY
>> > =
>> > OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
>> > ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
>> .
>>


From: Erland Sommarskog on 5 Dec 2009 15:12
Gert-Jan Strik (sorrytoomuchspamalready(a)xs4all.nl) writes:
> I am not sure if you made a typo, or have had different experiences, but
> I would NEVER expect to see a clustered index SEEK for the query
>
>> SELECT ... FROM tbl WHERE INDEX6 LIKE '%W%'
>
> because the pattern starts with a '%'. As you know, this disqualifies an
> index seek.

No, there was no typo, but there might have been an oversimplification.
When I ty this:

exec sp_executesql
N'select count(*) from Orders WHERE CustomerID LIKE ''%'' + @s',
N'@s nvarchar(20)', '%W%'

I do indeed get an Index Seek (non-clustered). I don't get this when I
inline the constant.

But of course not a real Seek, this is the Seek Predicate

Start: [Northgale].[dbo].[Orders].CustomerID >
Scalar Operator(LikeRangeStart(N'%'+[@s]));
End: [Northgale].[dbo].[Orders].CustomerID <
Scalar Operator(LikeRangeEnd(N'%'+[@s]))


--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

From: Gert-Jan Strik on 5 Dec 2009 15:56
You must be using a different version (probably 2008?), because however
hard I try, I keep getting an Index Scan on SQL Server 2005, even with
using sp_executesql.

--
Gert-Jan
SQL Server MVP

Erland Sommarskog wrote:
>
> Gert-Jan Strik (sorrytoomuchspamalready(a)xs4all.nl) writes:
> > I am not sure if you made a typo, or have had different experiences, but
> > I would NEVER expect to see a clustered index SEEK for the query
> >
> >> SELECT ... FROM tbl WHERE INDEX6 LIKE '%W%'
> >
> > because the pattern starts with a '%'. As you know, this disqualifies an
> > index seek.
>
> No, there was no typo, but there might have been an oversimplification.
> When I ty this:
>
> exec sp_executesql
> N'select count(*) from Orders WHERE CustomerID LIKE ''%'' + @s',
> N'@s nvarchar(20)', '%W%'
>
> I do indeed get an Index Seek (non-clustered). I don't get this when I
> inline the constant.
>
> But of course not a real Seek, this is the Seek Predicate
>
> Start: [Northgale].[dbo].[Orders].CustomerID >
> Scalar Operator(LikeRangeStart(N'%'+[@s]));
> End: [Northgale].[dbo].[Orders].CustomerID <
> Scalar Operator(LikeRangeEnd(N'%'+[@s]))
>
> --
> Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se
>
> Links for SQL Server Books Online:
> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
> SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
From: Erland Sommarskog on 5 Dec 2009 17:45
Gert-Jan Strik (sorrytoomuchspamalready(a)xs4all.nl) writes:
> You must be using a different version (probably 2008?), because however
> hard I try, I keep getting an Index Scan on SQL Server 2005, even with
> using sp_executesql.

Actually, I ran it on SQL 2005. I get the same result on SQL 2008.

Maybe the collation matters. My collation is Finnish_Swedish_CS_AS.


--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

From: Ron on 6 Dec 2009 06:55
Hi Jay, Yes I am, but I think reviewing your answers and those from Gert and
Erland have now clarified my understanding on this.

"Jay" wrote:

> Ron, are you asking if two indexes will be used on the query at the same
> time for your select? The second being used to narrow the results from the
> first and thus reducing overall query time for ONE query?
>
> "Ron" <Ron(a)discussions.microsoft.com> wrote in message
> news:5D0782F7-1320-4349-A79C-05ADA4D4AF63(a)microsoft.com...
> > Thanks Gert and Jay for your answers. Yes I see what you both mean, and
> > understand. Your time appreciated.
> >
> > I suppose that when I see a 'seek' here, it doesn't necessarily mean that
> > there isn't still a gain to be made somewhere. Ie the 'scan' you mention
> > on
> > index6 after the 'seek' on application id might benefit from a
> > nonclustered
> > index on index6?(specifically if the LIKE was SARGable ie (INDEX6 LIKE
> > 'W%'))
> >
> > Any thoughts?
> >
> > "Gert-Jan Strik" wrote:
> >
> >> Ron,
> >>
> >> In case it wasn't already clear from Jay's response: the clustered index
> >> is seeked because the predicate ApplicationID=99 uses the leading column
> >> of this index, and because it is the clustered index, the index entries
> >> include the rest of the row data, so they include INDEX6.
> >>
> >> What might not be clear when you read the query plan, is that the index
> >> seek is only used to locate the first row (with ApplicationID=99). From
> >> that point on, it will scan through the index to validate your other
> >> predicate (INDEX6 LIKE '%W%'). It will do this until ApplicationID > 99
> >> or until 5000 matching rows are found.
> >>
> >> --
> >> Gert-Jan
> >> SQL Server MVP
> >>
> >>
> >> Ron wrote:
> >> >
> >> > Hi, yes, here is the full query, exec plan (SHOWPLAN_TEXT), table and
> >> > Index.
> >> > (I tried to reduce it down to an example for simplicity before, and
> >> > fell into
> >> > the trap of providing less than useful info). I've tried to lay this
> >> > out as
> >> > best as possible....
> >> > Just to repeat, my question is why is there a clustered index seek,
> >> > when
> >> > index6 isn't in the clustered index (and is not SARGable?). Any
> >> > thoughts
> >> > greatly appreciated.
> >> >
> >> > ----Execution Plan----
> >> >
> >> > |--Top(TOP EXPRESSION:((5000)))
> >> > |--Clustered Index
> >> > Seek(OBJECT:([_archive].[dbo].[Docs].[IX_DOCUMENTS]
> >> > AS [D]), SEEK:([D].[ApplicationID]=(99)),
> >> > WHERE:([dbtest].[dbo].[Docs].[INDEX6] as [D].[INDEX6] like '%W%')
> >> > ORDERED
> >> > FORWARD)
> >> >
> >> > ----Query
> >> >
> >> > SELECT TOP 5000 [DocumentID], [ImportDate] FROM Docs AS D
> >> > WHERE APPLICATIONID=99
> >> > AND D.INDEX6 LIKE '%W%'
> >> >
> >> > ----Index
> >> >
> >> > CREATE CLUSTERED INDEX [IX_DOCUMENTS] ON [dbo].[Docs]
> >> > (
> >> > [ApplicationID] ASC,
> >> > [IsTest] ASC,
> >> > [BatchID] ASC,
> >> > [ImportDate] DESC
> >> > )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB
> >> > =
> >> > OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF,
> >> > ALLOW_ROW_LOCKS
> >> > = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
> >> > GO
> >> >
> >> > ----Table
> >> >
> >> > CREATE TABLE [dbo].[Docs](
> >> > [ID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
> >> > [DocumentID] [uniqueidentifier] NOT NULL,
> >> > [ApplicationID] [smallint] NOT NULL,
> >> > [BatchID] [int] NOT NULL,
> >> > [ImportDate] [datetime] NULL,
> >> > [ValidFrom] [datetime] NOT NULL,
> >> > [ValidTo] [datetime] NULL,
> >> > [IsTest] [bit] NOT NULL,
> >> > [DocumentName] [varchar](255) NOT NULL,
> >> > [Extension] [varchar](50) NOT NULL,
> >> > [IsCompressed] [bit] NOT NULL,
> >> > [IsAttachmentOnly] [bit] NOT NULL,
> >> > [Document] [image] NULL,
> >> > [INDEX1] [varchar](255) NULL,
> >> > [INDEX2] [varchar](255) NULL,
> >> > [INDEX3] [varchar](255) NULL,
> >> > [INDEX4] [varchar](255) NULL,
> >> > [INDEX5] [varchar](255) NULL,
> >> > [INDEX6] [varchar](255) NULL,
> >> > [INDEX7] [varchar](255) NULL,
> >> > [INDEX8] [varchar](255) NULL,
> >> > [INDEX9] [varchar](255) NULL,
> >> > [INDEX10] [varchar](255) NULL,
> >> > [INDEX11] [varchar](255) NULL,
> >> > [INDEX12] [varchar](255) NULL,
> >> > [INDEX13] [varchar](255) NULL,
> >> > [INDEX14] [varchar](255) NULL,
> >> > [INDEX15] [varchar](255) NULL,
> >> > [UsageCount] [int] NOT NULL,
> >> > [PageJump] [int] NULL,
> >> > [DocumentSize] [int] NULL,
> >> > [IsApproved] [bit] NOT NULL,
> >> > [CustomProperties] [xml] NOT NULL,
> >> > [IsEnabled] [bit] NOT NULL,
> >> > [msrepl_tran_version] [uniqueidentifier] NOT NULL,
> >> > CONSTRAINT [PK_Docs] PRIMARY KEY NONCLUSTERED
> >> > (
> >> > [ID] ASC
> >> > )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY
> >> > =
> >> > OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
> >> > CONSTRAINT [DocumentID] UNIQUE NONCLUSTERED
> >> > (
> >> > [DocumentID] ASC
> >> > )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY
> >> > =
> >> > OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
> >> > ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> >> .
> >>
>
>
> .
>
First  |  Prev  |  Next  |  Last
Pages: 1 2 3 4 5
Prev: select statement ,sum with calculation
Next: Return a specific string based on positions of Y/N data