index and key: select

Showing posts with label select. Show all posts

Friday, March 30, 2012

index tuning wizard

I have a query that looks something like this:
select fields from tableA
select fields from tableB
select fields from tableC
...
I want to try to run the Index Tuning Wizard on all of the queries in the
window. However, whenever I try, the ITW tells me "There are no events in
the workload. Either the trace file contains no sql batch or RPC events or
the sql script contained no sql queries." If I select one line and run the
ITW, it works fine - no matter which line I run. I tried adding GO between
each line but that didn't help.
Any ideas how I can get this to work?
Thanks, AndreTurns out it was because there was a syntax error in one of the queries.
Even though the parse completed successfully, when i ran all 84 of the
queries, one of them bombed out. I fixed it, and the ITW is working it's
little self away.
Thanks for the help.
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:u0PefPTVDHA.2248@.TK2MSFTNGP10.phx.gbl...
> What version and service pack are you on ? The following works fine for me
> on SQL2000 SP3
> use Northwind
> go
> select * from Customers where Country = 'Mexico'
> select * from Customers where City = 'México D.F.'
> select * from Customers where CustomerID = 'ALFKI'
> If I highlight the above in QA and invoke the ITW it generates
> recommendations
> --
> HTH
> Jasper Smith (SQL Server MVP)
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "Andre" <AndreGetsEnoughSPAM@.nospam.com> wrote in message
> news:O5mb2LSVDHA.2364@.TK2MSFTNGP09.phx.gbl...
> I have a query that looks something like this:
> select fields from tableA
> select fields from tableB
> select fields from tableC
> ...
> I want to try to run the Index Tuning Wizard on all of the queries in the
> window. However, whenever I try, the ITW tells me "There are no events in
> the workload. Either the trace file contains no sql batch or RPC events or
> the sql script contained no sql queries." If I select one line and run
the
> ITW, it works fine - no matter which line I run. I tried adding GO
between
> each line but that didn't help.
> Any ideas how I can get this to work?
> Thanks, Andre
>
>

Index Table

Hi ,
I have created 3 fields in for my table , FieldA , B and C all asc order.
What is the diff if I perform the query like :
A) Select * From TableA
Where FieldA = 'A' And FieldB = 'B' And FieldC = 'C'
B) Select * From TableA
Where FieldC = 'C' And FieldB = 'B' And FieldC = 'A'
Do the performance is the same ?
Travis TanThe optimizer should create the same execution plan for both of these
statements therefore performance should be the same.
--Brian
(Please reply to the newsgroups only.)
"Travis" <Travis@.discussions.microsoft.com> wrote in message
news:B49A12BC-10AE-4309-A9B5-C5BAEAB69329@.microsoft.com...
> Hi ,
> I have created 3 fields in for my table , FieldA , B and C all asc
> order.
> What is the diff if I perform the query like :
> A) Select * From TableA
> Where FieldA = 'A' And FieldB = 'B' And FieldC = 'C'
> B) Select * From TableA
> Where FieldC = 'C' And FieldB = 'B' And FieldC = 'A'
> Do the performance is the same ?
>
> --
> Travis Tan|||Brian ,
Sorry , What I means is I create an index in TableA with FieldA , B and C
all asc order.
What is the diff if I perform the query like :
A) Select * From TableA
Where FieldA = 'A' And FieldB = 'B' And FieldC = 'C'
B) Select * From TableA
Where FieldC = 'C' And FieldB = 'B' And FieldC = 'A'
Travis Tan
"Brian Lawton" wrote:

> The optimizer should create the same execution plan for both of these
> statements therefore performance should be the same.
> --
> --Brian
> (Please reply to the newsgroups only.)
>
> "Travis" <Travis@.discussions.microsoft.com> wrote in message
> news:B49A12BC-10AE-4309-A9B5-C5BAEAB69329@.microsoft.com...
>
>|||Same answer:
>The optimizer should create the same execution plan for both of these
>statements therefore performance should be the same.
Uytkownik "Travis" <Travis@.discussions.microsoft.com> napisa w wiadomoci
news:48D82684-DF75-4740-BC4F-9604F33235A8@.microsoft.com...[vbcol=seagreen]
> Brian ,
> Sorry , What I means is I create an index in TableA with FieldA , B and
> C
> all asc order.
> What is the diff if I perform the query like :
> A) Select * From TableA
> Where FieldA = 'A' And FieldB = 'B' And FieldC = 'C'
> B) Select * From TableA
> Where FieldC = 'C' And FieldB = 'B' And FieldC = 'A'
>
> Travis Tan
>
> "Brian Lawton" wrote:
>

Index Table

Hi ,
I have created 3 fields in for my table , FieldA , B and C all asc order.
What is the diff if I perform the query like :
A) Select * From TableA
Where FieldA = 'A' And FieldB = 'B' And FieldC = 'C'
B) Select * From TableA
Where FieldC = 'C' And FieldB = 'B' And FieldC = 'A'
Do the performance is the same ?
Travis Tan
The optimizer should create the same execution plan for both of these
statements therefore performance should be the same.
--Brian
(Please reply to the newsgroups only.)
"Travis" <Travis@.discussions.microsoft.com> wrote in message
news:B49A12BC-10AE-4309-A9B5-C5BAEAB69329@.microsoft.com...
> Hi ,
> I have created 3 fields in for my table , FieldA , B and C all asc
> order.
> What is the diff if I perform the query like :
> A) Select * From TableA
> Where FieldA = 'A' And FieldB = 'B' And FieldC = 'C'
> B) Select * From TableA
> Where FieldC = 'C' And FieldB = 'B' And FieldC = 'A'
> Do the performance is the same ?
>
> --
> Travis Tan
|||Brian ,
Sorry , What I means is I create an index in TableA with FieldA , B and C
all asc order.
What is the diff if I perform the query like :
A) Select * From TableA
Where FieldA = 'A' And FieldB = 'B' And FieldC = 'C'
B) Select * From TableA
Where FieldC = 'C' And FieldB = 'B' And FieldC = 'A'
Travis Tan
"Brian Lawton" wrote:

> The optimizer should create the same execution plan for both of these
> statements therefore performance should be the same.
> --
> --Brian
> (Please reply to the newsgroups only.)
>
> "Travis" <Travis@.discussions.microsoft.com> wrote in message
> news:B49A12BC-10AE-4309-A9B5-C5BAEAB69329@.microsoft.com...
>
>
|||Same answer:
>The optimizer should create the same execution plan for both of these
>statements therefore performance should be the same.
Uytkownik "Travis" <Travis@.discussions.microsoft.com> napisa w wiadomoci
news:48D82684-DF75-4740-BC4F-9604F33235A8@.microsoft.com...[vbcol=seagreen]
> Brian ,
> Sorry , What I means is I create an index in TableA with FieldA , B and
> C
> all asc order.
> What is the diff if I perform the query like :
> A) Select * From TableA
> Where FieldA = 'A' And FieldB = 'B' And FieldC = 'C'
> B) Select * From TableA
> Where FieldC = 'C' And FieldB = 'B' And FieldC = 'A'
>
> Travis Tan
>
> "Brian Lawton" wrote:

Wednesday, March 28, 2012

Index size.

Where can I get the size of the index to add to the below query.
select
t.name [Table],
i.Name [Index],
c.name [Column],
ic.key_ordinal [Key Ordinal],
is_included_column [Included],
is_descending_key [Descending key],
i.type_desc [Index Type],
i.is_unique [Unique Index],
i.is_disabled [Index disabled],
i.fill_factor [Fill Factor],
i.is_hypothetical [hypothetical]
from
sys.indexes i
inner join sys.index_columns ic
on i.index_id = ic.index_id
and i.object_id = ic.object_id
inner join sys.columns c
on c.column_id = ic.column_id
and c.object_id = ic.object_id
inner join sys.tables t
on i.object_id = t.object_id
where t.name='Orders'
order by
t.object_id,
i.index_id,
is_included_column,
c.column_id
Thanks
Shiju SamuelHi
SELECT object_name(a.[object_id]) as TableName,a.index_id,
isnull(b.name,'HEAP') as IndexName, sum(a.page_count) as
pages,sum(a.page_count)*1.0/1024 as Mb
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL,NULL, NULL, 'DETAILED')
AS a
JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id =b.index_id
group by a.[object_id],a.index_id, b.name
order by pages desc;
"Shiju Samuel" <shiju.samuel@.gmail.com> wrote in message
news:a3495e0c-cb67-4dfd-9eca-671e8bee8e0d@.y43g2000hsy.googlegroups.com...
> Where can I get the size of the index to add to the below query.
> select
> t.name [Table],
> i.Name [Index],
> c.name [Column],
> ic.key_ordinal [Key Ordinal],
> is_included_column [Included],
> is_descending_key [Descending key],
> i.type_desc [Index Type],
> i.is_unique [Unique Index],
> i.is_disabled [Index disabled],
> i.fill_factor [Fill Factor],
> i.is_hypothetical [hypothetical]
> from
> sys.indexes i
> inner join sys.index_columns ic
> on i.index_id = ic.index_id
> and i.object_id = ic.object_id
> inner join sys.columns c
> on c.column_id = ic.column_id
> and c.object_id = ic.object_id
> inner join sys.tables t
> on i.object_id = t.object_id
> where t.name='Orders'
> order by
> t.object_id,
> i.index_id,
> is_included_column,
> c.column_id
> Thanks
> Shiju Samuel|||Thanks Uri.

Index seek vs. index scan

Convert([smtp_mail].[pli_id])=[@.P0]))
> 0 1 Filter
> Filter
> WHERE

Convert([smtp_mail].[pli_id])=[@.P0])
> 41.7855 0 8.41E-005 89
> 0.0386413 [smtp_mail].[id]
> PLAN_ROW
> 0 1
> 147 1 |--Index
> Scan(OBJECT

[SecurWrap].[SecurityServer].[smtp_mail].[ux_smtp_mail])) 0
> 2 1 Index Scan Index Scan
> OBJECT

[SecurWrap].[SecurityServer].[smtp_mail].[ux_smtp_mail])
> [smtp_mail].[pli_id], [smtp_mail].[id] 145 0.0383192 0.000238
> 89
> 0.0385572 [smtp_mail].[pli_id], [smtp_mail].[id]
> PLAN_ROW 0 1
> ========================
> SQL Server Query Analyze
> =======================
> Rows Executes StmtText
> StmtId
> NodeId Parent PhysicalOp LogicalOp Argument
> DefinedValues EstimateRows EstimateIO EstimateCPU AvgRowSize
> TotalSubtreeCost OutputList Warnings Type Parallel
> EstimateExecutions
> -- -- --
> --
> -- -- -- -- --
> -- -- -- -- --
> -- -- -- -- --
> --
> 1 1 Index
> Seek(OBJECT

[stress].[SecurityServer].[smtp_mail].[ux_smtp_mail]),
> SEEK

[smtp_mail].[pli_id]=[@.1]) ORDERED FORWARD) 0 1
> Index Seek Index Seek
> OBJECT

[stress].[SecurityServer].[smtp_mail].[ux_smtp_mail]),
> SEEK

[smtp_mail].[pli_id]=[@.1]) ORDERED FORWARD [smtp_mail].[id] 1
> 0.00320343 7.96E-005 11 0.00328303 [smtp_mail].[id]
> PLAN_ROW 0 1
>
> Thank you for your prompt response!
> --
> Mitra
>
> "mitra" wrote:
|||Interesting problem. Some developers where I work had a similar issue -
fairly well indexed tables but all of their queries coming through JDBC
from their java app were doing clustered index scans (not using the
indexes essentially) and thrashing the life out of the processors in the
box, resulting in really bad performance obviously.
As a DBA it was fairly puzzling to me because the schema all looked
fairly nice (including their indexes, although I suggested a few more
given their workload) and their queries logically should have worked
fine and in QA they did. In the end it came down to the fact that the
JDBC driver was implicitly converting all of their text data to Unicode,
but the underlying data was all non-unicode (varchar, char & text), so
SQL Server was implicitly converting the underlying data to unicode
during the plan compilation phase because all the unicode datatypes
(nvarchar, nchar & ntext) have a higher precedence than their respective
non-unicode datatypes. That meant it couldn't use the indexes defined
on the string data because the implicit conversion made the expressions
non-SARGable (I think, extrapolating, that was the basic issue).
Anyway, the developers finally discovered this from the client side
(well, middle tier actually) and changed some setting on the JDBC driver
and it all suddenly started working perfectly. I doubt I would have
ever figured that one out, as I only had access to the DB & what I could
glean from profiler, and not the middle tier drivers or client-side code.
I've left some voicemail for the dev guy, who found the problem and
changed the setting, to find out what setting it was. If he gets back
to me about it then I'll post the JDBC driver setting/switch.
Hope this helps.
*mike hodgson*
blog: http://sqlnerd.blogspot.com
mitra wrote:

>Alejandro,
>I appreciate for pointing out the "convert" and questioning why sql server
>is using it.
>We looked into our code and confirmed that our query did not include
>"convert". We added "convert" to our query and ran it in Query Analyzer. This
>time Sql Server did an Index Scan.
>Obvioulsy, jdbc driver (jTDS) is doing the convert, why we don't know. Any
>Idea?
>The column data type is char(60).
>We thought of testing the jdbc driver by changing the data type char(60) to
>Varchar(60) and see if it would do the convert again. It did not!
>Is there a way that we could enforce jdbc driver not to pass the select
>statement with convert? Currently, we are reluctant to make any schema
>changes.
>Again I appreciate your prompt help.
>Thank you so much!
>
|||It's part of the connection string. The parameter is
*SendStringParametersAsUnicode* and it's true by default.
Connection Parameters:
SendStringParametersAsUnicode
Determines whether string parameters are sent to the SQL Server
database in Unicode or in the default character encoding of the
database. True means that string parameters are sent to SQL Server
in Unicode. False means that they are sent in the default encoding,
which can improve performance because the server does not need to
convert Unicode characters to the default encoding. You should,
however, use default encoding only if the parameter string data that
you specify is consistent with the default encoding of the database.
Default value is true.
Apparently it's documented in the documentation that comes with the
Microsoft JDBC driver (I haven't played with MS-JDBC myself). Here's a
pretty good page (on the WebLogic website) that talks about it (I can't
readily find an official Microsoft page outlining the JDBC connection
parameters):
http://e-docs.bea.com/wls/docs81/jdb...sqlserver.html
Hope this helps.
*mike hodgson*
blog: http://sqlnerd.blogspot.com
Mike Hodgson wrote:
[vbcol=seagreen]
> Interesting problem. Some developers where I work had a similar issue
> - fairly well indexed tables but all of their queries coming through
> JDBC from their java app were doing clustered index scans (not using
> the indexes essentially) and thrashing the life out of the processors
> in the box, resulting in really bad performance obviously.
> As a DBA it was fairly puzzling to me because the schema all looked
> fairly nice (including their indexes, although I suggested a few more
> given their workload) and their queries logically should have worked
> fine and in QA they did. In the end it came down to the fact that the
> JDBC driver was implicitly converting all of their text data to
> Unicode, but the underlying data was all non-unicode (varchar, char &
> text), so SQL Server was implicitly converting the underlying data to
> unicode during the plan compilation phase because all the unicode
> datatypes (nvarchar, nchar & ntext) have a higher precedence than
> their respective non-unicode datatypes. That meant it couldn't use
> the indexes defined on the string data because the implicit conversion
> made the expressions non-SARGable (I think, extrapolating, that was
> the basic issue).
> Anyway, the developers finally discovered this from the client side
> (well, middle tier actually) and changed some setting on the JDBC
> driver and it all suddenly started working perfectly. I doubt I would
> have ever figured that one out, as I only had access to the DB & what
> I could glean from profiler, and not the middle tier drivers or
> client-side code.
> I've left some voicemail for the dev guy, who found the problem and
> changed the setting, to find out what setting it was. If he gets back
> to me about it then I'll post the JDBC driver setting/switch.
> Hope this helps.
> --
> *mike hodgson*
> blog: http://sqlnerd.blogspot.com
>
> mitra wrote:
|||Mike,
Thanks a lot for sharing that with the group.
Regards,
Alejandro Mesa
"Mike Hodgson" wrote:

> It's part of the connection string. The parameter is
> *SendStringParametersAsUnicode* and it's true by default.
> Connection Parameters:
> SendStringParametersAsUnicode
> Determines whether string parameters are sent to the SQL Server
> database in Unicode or in the default character encoding of the
> database. True means that string parameters are sent to SQL Server
> in Unicode. False means that they are sent in the default encoding,
> which can improve performance because the server does not need to
> convert Unicode characters to the default encoding. You should,
> however, use default encoding only if the parameter string data that
> you specify is consistent with the default encoding of the database.
> Default value is true.
> Apparently it's documented in the documentation that comes with the
> Microsoft JDBC driver (I haven't played with MS-JDBC myself). Here's a
> pretty good page (on the WebLogic website) that talks about it (I can't
> readily find an official Microsoft page outlining the JDBC connection
> parameters):
> http://e-docs.bea.com/wls/docs81/jdb...sqlserver.html
> Hope this helps.
> --
> *mike hodgson*
> blog: http://sqlnerd.blogspot.com
>
> Mike Hodgson wrote:
>

Index seek vs. index scan

Convert([smtp_mail].[pli_id])=
[@.P0]))
> 0 1 Filter
> Filter
> WHERE

Convert([smtp_mail].[pli_id])=[@.P0])
> 41.7855 0 8.41E-005 89
> 0.0386413 [smtp_mail].[id]
> PLAN_ROW
> 0 1
> 147 1 |--Index
> Scan(OBJECT

[SecurWrap].[SecurityServer].[smtp_mail].[ux_
smtp_mail])) 0
> 2 1 Index Scan Index Scan
> OBJECT

[SecurWrap].[SecurityServer].[smtp_mail].[ux_smtp_
mail])
> [smtp_mail].[pli_id], [smtp_mail].[id] 145 0.0383
192 0.000238
> 89
> 0.0385572 [smtp_mail].[pli_id], [smtp_mail].[
;id]
> PLAN_ROW 0 1
> ========================
> SQL Server Query Analyze
> =======================
> Rows Executes StmtText
> StmtId
> NodeId Parent PhysicalOp LogicalOp Argument
> DefinedValues EstimateRows EstimateIO EstimateCPU AvgRowSize
> TotalSubtreeCost OutputList Warnings Type Parallel
> EstimateExecutions
> -- -- --
> --
> -- -- -- -- --
> -- -- -- -- --
> -- -- -- -- --
> --
> 1 1 Index
> Seek(OBJECT

[stress].[SecurityServer].[smtp_mail].[ux_smt
p_mail]),
> SEEK

[smtp_mail].[pli_id]=[@.1]) ORDERED FORWARD) 0 1
> Index Seek Index Seek
> OBJECT

[stress].[SecurityServer].[smtp_mail].[ux_smtp_mai
l]),
> SEEK

[smtp_mail].[pli_id]=[@.1]) ORDERED FORWARD [smtp_mai
l].[id] 1
> 0.00320343 7.96E-005 11 0.00328303 [smtp_mail].[id
]
> PLAN_ROW 0 1
>
> Thank you for your prompt response!
> --
> Mitra
>
> "mitra" wrote:
>|||Interesting problem. Some developers where I work had a similar issue -
fairly well indexed tables but all of their queries coming through JDBC
from their Java app were doing clustered index scans (not using the
indexes essentially) and thrashing the life out of the processors in the
box, resulting in really bad performance obviously.
As a DBA it was fairly puzzling to me because the schema all looked
fairly nice (including their indexes, although I suggested a few more
given their workload) and their queries logically should have worked
fine and in QA they did. In the end it came down to the fact that the
JDBC driver was implicitly converting all of their text data to Unicode,
but the underlying data was all non-unicode (varchar, char & text), so
SQL Server was implicitly converting the underlying data to unicode
during the plan compilation phase because all the unicode datatypes
(nvarchar, nchar & ntext) have a higher precedence than their respective
non-unicode datatypes. That meant it couldn't use the indexes defined
on the string data because the implicit conversion made the expressions
non-SARGable (I think, extrapolating, that was the basic issue).
Anyway, the developers finally discovered this from the client side
(well, middle tier actually) and changed some setting on the JDBC driver
and it all suddenly started working perfectly. I doubt I would have
ever figured that one out, as I only had access to the DB & what I could
glean from profiler, and not the middle tier drivers or client-side code.
I've left some voicemail for the dev guy, who found the problem and
changed the setting, to find out what setting it was. If he gets back
to me about it then I'll post the JDBC driver setting/switch.
Hope this helps.
*mike hodgson*
blog: http://sqlnerd.blogspot.com
mitra wrote:

>Alejandro,
>I appreciate for pointing out the "convert" and questioning why sql server
>is using it.
>We looked into our code and confirmed that our query did not include
>"convert". We added "convert" to our query and ran it in Query Analyzer. Th
is
>time Sql Server did an Index Scan.
>Obvioulsy, jdbc driver (jTDS) is doing the convert, why we don't know. Any
>Idea?
>The column data type is char(60).
>We thought of testing the jdbc driver by changing the data type char(60) to
>Varchar(60) and see if it would do the convert again. It did not!
>Is there a way that we could enforce jdbc driver not to pass the select
>statement with convert? Currently, we are reluctant to make any schema
>changes.
>Again I appreciate your prompt help.
>Thank you so much!
>|||It's part of the connection string. The parameter is
*SendStringParametersAsUnicode* and it's true by default.
Connection Parameters:
SendStringParametersAsUnicode
Determines whether string parameters are sent to the SQL Server
database in Unicode or in the default character encoding of the
database. True means that string parameters are sent to SQL Server
in Unicode. False means that they are sent in the default encoding,
which can improve performance because the server does not need to
convert Unicode characters to the default encoding. You should,
however, use default encoding only if the parameter string data that
you specify is consistent with the default encoding of the database.
Default value is true.
Apparently it's documented in the documentation that comes with the
Microsoft JDBC driver (I haven't played with MS-JDBC myself). Here's a
pretty good page (on the WebLogic website) that talks about it (I can't
readily find an official Microsoft page outlining the JDBC connection
parameters):
http://e-docs.bea.com/wls/docs81/jd...ssqlserver.html
Hope this helps.
*mike hodgson*
blog: http://sqlnerd.blogspot.com
Mike Hodgson wrote:
[vbcol=seagreen]
> Interesting problem. Some developers where I work had a similar issue
> - fairly well indexed tables but all of their queries coming through
> JDBC from their Java app were doing clustered index scans (not using
> the indexes essentially) and thrashing the life out of the processors
> in the box, resulting in really bad performance obviously.
> As a DBA it was fairly puzzling to me because the schema all looked
> fairly nice (including their indexes, although I suggested a few more
> given their workload) and their queries logically should have worked
> fine and in QA they did. In the end it came down to the fact that the
> JDBC driver was implicitly converting all of their text data to
> Unicode, but the underlying data was all non-unicode (varchar, char &
> text), so SQL Server was implicitly converting the underlying data to
> unicode during the plan compilation phase because all the unicode
> datatypes (nvarchar, nchar & ntext) have a higher precedence than
> their respective non-unicode datatypes. That meant it couldn't use
> the indexes defined on the string data because the implicit conversion
> made the expressions non-SARGable (I think, extrapolating, that was
> the basic issue).
> Anyway, the developers finally discovered this from the client side
> (well, middle tier actually) and changed some setting on the JDBC
> driver and it all suddenly started working perfectly. I doubt I would
> have ever figured that one out, as I only had access to the DB & what
> I could glean from profiler, and not the middle tier drivers or
> client-side code.
> I've left some voicemail for the dev guy, who found the problem and
> changed the setting, to find out what setting it was. If he gets back
> to me about it then I'll post the JDBC driver setting/switch.
> Hope this helps.
> --
> *mike hodgson*
> blog: http://sqlnerd.blogspot.com
>
> mitra wrote:
>|||Mike,
Thanks a lot for sharing that with the group.
Regards,
Alejandro Mesa
"Mike Hodgson" wrote:

> It's part of the connection string. The parameter is
> *SendStringParametersAsUnicode* and it's true by default.
> Connection Parameters:
> SendStringParametersAsUnicode
> Determines whether string parameters are sent to the SQL Server
> database in Unicode or in the default character encoding of the
> database. True means that string parameters are sent to SQL Server
> in Unicode. False means that they are sent in the default encoding,
> which can improve performance because the server does not need to
> convert Unicode characters to the default encoding. You should,
> however, use default encoding only if the parameter string data that
> you specify is consistent with the default encoding of the database.
> Default value is true.
> Apparently it's documented in the documentation that comes with the
> Microsoft JDBC driver (I haven't played with MS-JDBC myself). Here's a
> pretty good page (on the WebLogic website) that talks about it (I can't
> readily find an official Microsoft page outlining the JDBC connection
> parameters):
> http://e-docs.bea.com/wls/docs81/jd...ssqlserver.html
> Hope this helps.
> --
> *mike hodgson*
> blog: http://sqlnerd.blogspot.com
>
> Mike Hodgson wrote:
>
>

Index seek vs. index scan

Hello,
The execution plan captured by SQL Profiler shows us that the SQL Server is
doing an Index Scan on this particular select statement running through our
java application:
Select col_A from tbl_name WHERE col_B like 'string'
col_A is an identity col, PK , and is clustered index.
col_B is a unique constraint non clustered index.
However, when the same select statement is executed using SQL Server Query
Analyzer, the execution plan shows us that the SQL Server did an Index Seek.
Any idea why we are experiencing an Index Scan running the query through our
java application vs. an Index Seek running the same query in SQL Server Query
Analyzer?
Thank you,
--
Mitramitra,
Something called my attention, why is sql server using "convert" in the
execution plan?
> WHERE:(Convert([smtp_mail].[pli_id])=[@.P0])
It seems that the data type of the parameter is diff than the data type of
column [smtp_mail] and it has greater precedence.
What is the datatype of column [smtp_mail]?
AMB
"mitra" wrote:
> The query was done via jdbc using a prepared statement. The select statement
> is very simple -
> PreparedStatement stmt = conn.prepareStatment("SELECT id from tablex where
> colA = ?");
> stmt.setString(1, "abcdedfg");
> ResultSet rslt = stmt.executeQuery();
> (There is no "LIKE" clause)
> Both execution plans follow. The SQL Profiler first showing the index scan
> and the Query Analyzer second showing the index seek.
> NOTE: These particular samples came from two different databases however we
> are getting the same result when the queries are run against the same
> database..
> ==============================> SQL Server Profiler
> ===============================> Rows Executes StmtText
> StmtId NodeId Parent PhysicalOp
> LogicalOp Argument
> DefinedValues EstimateRows EstimateIO EstimateCPU
> AvgRowSize TotalSubtreeCost OutputList Warnings
> Type Parallel EstimateExecutions
> -- -- --
> -- -- -- --
> -- --
> -- -- -- --
> -- -- -- --
> -- -- --
> 1 1 Filter(WHERE:(Convert([smtp_mail].[pli_id])=[@.P0]))
> 0 1 Filter Filter
> WHERE:(Convert([smtp_mail].[pli_id])=[@.P0])
> 41.7855 0 8.41E-005 89
> 0.0386413 [smtp_mail].[id] PLAN_ROW
> 0 1
> 147 1 |--Index
> Scan(OBJECT:([SecurWrap].[SecurityServer].[smtp_mail].[ux_smtp_mail])) 0
> 2 1 Index Scan Index Scan
> OBJECT:([SecurWrap].[SecurityServer].[smtp_mail].[ux_smtp_mail])
> [smtp_mail].[pli_id], [smtp_mail].[id] 145 0.0383192 0.000238 89
> 0.0385572 [smtp_mail].[pli_id], [smtp_mail].[id]
> PLAN_ROW 0 1
> ========================> SQL Server Query Analyze
> =======================> Rows Executes StmtText
> StmtId
> NodeId Parent PhysicalOp LogicalOp Argument
> DefinedValues EstimateRows EstimateIO EstimateCPU AvgRowSize
> TotalSubtreeCost OutputList Warnings Type Parallel
> EstimateExecutions
> -- -- --
> --
> -- -- -- -- --
> -- -- -- -- --
> -- -- -- -- --
> --
> 1 1 Index
> Seek(OBJECT:([stress].[SecurityServer].[smtp_mail].[ux_smtp_mail]),
> SEEK:([smtp_mail].[pli_id]=[@.1]) ORDERED FORWARD) 0 1
> Index Seek Index Seek
> OBJECT:([stress].[SecurityServer].[smtp_mail].[ux_smtp_mail]),
> SEEK:([smtp_mail].[pli_id]=[@.1]) ORDERED FORWARD [smtp_mail].[id] 1
> 0.00320343 7.96E-005 11 0.00328303 [smtp_mail].[id]
> PLAN_ROW 0 1
>
> Thank you for your prompt response!
> --
> Mitra
>
> "mitra" wrote:
> > Hello,
> >
> > The execution plan captured by SQL Profiler shows us that the SQL Server is
> > doing an Index Scan on this particular select statement running through our
> > java application:
> > Select col_A from tbl_name WHERE col_B like 'string'
> > col_A is an identity col, PK , and is clustered index.
> > col_B is a unique constraint non clustered index.
> >
> > However, when the same select statement is executed using SQL Server Query
> > Analyzer, the execution plan shows us that the SQL Server did an Index Seek.
> >
> > Any idea why we are experiencing an Index Scan running the query through our
> > java application vs. an Index Seek running the same query in SQL Server Query
> > Analyzer?
> >
> > Thank you,
> > --
> > Mitra|||How are you executing this statement from your client app?
- Are you constructing the statement dinamically and sending it to sql server?
- Are you executing a stored procedure that expect some parameters?
AMB
"mitra" wrote:
> Hello,
> The execution plan captured by SQL Profiler shows us that the SQL Server is
> doing an Index Scan on this particular select statement running through our
> java application:
> Select col_A from tbl_name WHERE col_B like 'string'
> col_A is an identity col, PK , and is clustered index.
> col_B is a unique constraint non clustered index.
> However, when the same select statement is executed using SQL Server Query
> Analyzer, the execution plan shows us that the SQL Server did an Index Seek.
> Any idea why we are experiencing an Index Scan running the query through our
> java application vs. an Index Seek running the same query in SQL Server Query
> Analyzer?
> Thank you,
> --
> Mitra|||Also,
> > java application vs. an Index Seek running the same query in SQL Server
using which index?
> > Select col_A from tbl_name WHERE col_B like 'string'
are you using wildcard characters in the string used with the like operator?
Can you post both execution plans?
AMB
"Alejandro Mesa" wrote:
> How are you executing this statement from your client app?
> - Are you constructing the statement dinamically and sending it to sql server?
> - Are you executing a stored procedure that expect some parameters?
>
> AMB
> "mitra" wrote:
> > Hello,
> >
> > The execution plan captured by SQL Profiler shows us that the SQL Server is
> > doing an Index Scan on this particular select statement running through our
> > java application:
> > Select col_A from tbl_name WHERE col_B like 'string'
> > col_A is an identity col, PK , and is clustered index.
> > col_B is a unique constraint non clustered index.
> >
> > However, when the same select statement is executed using SQL Server Query
> > Analyzer, the execution plan shows us that the SQL Server did an Index Seek.
> >
> > Any idea why we are experiencing an Index Scan running the query through our
> > java application vs. an Index Seek running the same query in SQL Server Query
> > Analyzer?
> >
> > Thank you,
> > --
> > Mitra|||The query was done via jdbc using a prepared statement. The select statement
is very simple -
PreparedStatement stmt = conn.prepareStatment("SELECT id from tablex where
colA = ?");
stmt.setString(1, "abcdedfg");
ResultSet rslt = stmt.executeQuery();
(There is no "LIKE" clause)
Both execution plans follow. The SQL Profiler first showing the index scan
and the Query Analyzer second showing the index seek.
NOTE: These particular samples came from two different databases however we
are getting the same result when the queries are run against the same
database..
==============================SQL Server Profiler
===============================Rows Executes StmtText
StmtId NodeId Parent PhysicalOp
LogicalOp Argument
DefinedValues EstimateRows EstimateIO EstimateCPU
AvgRowSize TotalSubtreeCost OutputList Warnings
Type Parallel EstimateExecutions
-- -- --
-- -- -- --
-- --
-- -- -- --
-- -- -- --
-- -- --
1 1 Filter(WHERE:(Convert([smtp_mail].[pli_id])=[@.P0]))
0 1 Filter Filter
WHERE:(Convert([smtp_mail].[pli_id])=[@.P0])
41.7855 0 8.41E-005 89
0.0386413 [smtp_mail].[id] PLAN_ROW
0 1
147 1 |--Index
Scan(OBJECT:([SecurWrap].[SecurityServer].[smtp_mail].[ux_smtp_mail])) 0
2 1 Index Scan Index Scan
OBJECT:([SecurWrap].[SecurityServer].[smtp_mail].[ux_smtp_mail])
[smtp_mail].[pli_id], [smtp_mail].[id] 145 0.0383192 0.000238 89
0.0385572 [smtp_mail].[pli_id], [smtp_mail].[id]
PLAN_ROW 0 1
========================SQL Server Query Analyze
=======================Rows Executes StmtText
StmtId
NodeId Parent PhysicalOp LogicalOp Argument
DefinedValues EstimateRows EstimateIO EstimateCPU AvgRowSize
TotalSubtreeCost OutputList Warnings Type Parallel
EstimateExecutions
-- -- --
--
-- -- -- -- --
-- -- -- -- --
-- -- -- -- --
--
1 1 Index
Seek(OBJECT:([stress].[SecurityServer].[smtp_mail].[ux_smtp_mail]),
SEEK:([smtp_mail].[pli_id]=[@.1]) ORDERED FORWARD) 0 1
Index Seek Index Seek
OBJECT:([stress].[SecurityServer].[smtp_mail].[ux_smtp_mail]),
SEEK:([smtp_mail].[pli_id]=[@.1]) ORDERED FORWARD [smtp_mail].[id] 1
0.00320343 7.96E-005 11 0.00328303 [smtp_mail].[id]
PLAN_ROW 0 1
Thank you for your prompt response!
--
Mitra
"mitra" wrote:
> Hello,
> The execution plan captured by SQL Profiler shows us that the SQL Server is
> doing an Index Scan on this particular select statement running through our
> java application:
> Select col_A from tbl_name WHERE col_B like 'string'
> col_A is an identity col, PK , and is clustered index.
> col_B is a unique constraint non clustered index.
> However, when the same select statement is executed using SQL Server Query
> Analyzer, the execution plan shows us that the SQL Server did an Index Seek.
> Any idea why we are experiencing an Index Scan running the query through our
> java application vs. an Index Seek running the same query in SQL Server Query
> Analyzer?
> Thank you,
> --
> Mitra|||Alejandro,
I appreciate for pointing out the "convert" and questioning why sql server
is using it.
We looked into our code and confirmed that our query did not include
"convert". We added "convert" to our query and ran it in Query Analyzer. This
time Sql Server did an Index Scan.
Obvioulsy, jdbc driver (jTDS) is doing the convert, why we don't know. Any
Idea?
The column data type is char(60).
We thought of testing the jdbc driver by changing the data type char(60) to
Varchar(60) and see if it would do the convert again. It did not!
Is there a way that we could enforce jdbc driver not to pass the select
statement with convert? Currently, we are reluctant to make any schema
changes.
Again I appreciate your prompt help.
Thank you so much!
--
Mitra
"Alejandro Mesa" wrote:
> mitra,
> Something called my attention, why is sql server using "convert" in the
> execution plan?
> > WHERE:(Convert([smtp_mail].[pli_id])=[@.P0])
> It seems that the data type of the parameter is diff than the data type of
> column [smtp_mail] and it has greater precedence.
> What is the datatype of column [smtp_mail]?
>
> AMB
> "mitra" wrote:
> > The query was done via jdbc using a prepared statement. The select statement
> > is very simple -
> >
> > PreparedStatement stmt = conn.prepareStatment("SELECT id from tablex where
> > colA = ?");
> > stmt.setString(1, "abcdedfg");
> > ResultSet rslt = stmt.executeQuery();
> >
> > (There is no "LIKE" clause)
> >
> > Both execution plans follow. The SQL Profiler first showing the index scan
> > and the Query Analyzer second showing the index seek.
> >
> > NOTE: These particular samples came from two different databases however we
> > are getting the same result when the queries are run against the same
> > database..
> >
> > ==============================> > SQL Server Profiler
> > ===============================> > Rows Executes StmtText
> > StmtId NodeId Parent PhysicalOp
> > LogicalOp Argument
> > DefinedValues EstimateRows EstimateIO EstimateCPU
> > AvgRowSize TotalSubtreeCost OutputList Warnings
> > Type Parallel EstimateExecutions
> > -- -- --
> > -- -- -- --
> > -- --
> > -- -- -- --
> > -- -- -- --
> > -- -- --
> > 1 1 Filter(WHERE:(Convert([smtp_mail].[pli_id])=[@.P0]))
> > 0 1 Filter Filter
> > WHERE:(Convert([smtp_mail].[pli_id])=[@.P0])
> > 41.7855 0 8.41E-005 89
> > 0.0386413 [smtp_mail].[id] PLAN_ROW
> > 0 1
> > 147 1 |--Index
> > Scan(OBJECT:([SecurWrap].[SecurityServer].[smtp_mail].[ux_smtp_mail])) 0
> > 2 1 Index Scan Index Scan
> > OBJECT:([SecurWrap].[SecurityServer].[smtp_mail].[ux_smtp_mail])
> > [smtp_mail].[pli_id], [smtp_mail].[id] 145 0.0383192 0.000238 89
> > 0.0385572 [smtp_mail].[pli_id], [smtp_mail].[id]
> > PLAN_ROW 0 1
> >
> > ========================> > SQL Server Query Analyze
> > =======================> > Rows Executes StmtText
> > StmtId
> > NodeId Parent PhysicalOp LogicalOp Argument
> >
> > DefinedValues EstimateRows EstimateIO EstimateCPU AvgRowSize
> > TotalSubtreeCost OutputList Warnings Type Parallel
> > EstimateExecutions
> > -- -- --
> > --
> > -- -- -- -- --
> >
> > -- -- -- -- --
> > -- -- -- -- --
> > --
> > 1 1 Index
> > Seek(OBJECT:([stress].[SecurityServer].[smtp_mail].[ux_smtp_mail]),
> > SEEK:([smtp_mail].[pli_id]=[@.1]) ORDERED FORWARD) 0 1
> > Index Seek Index Seek
> > OBJECT:([stress].[SecurityServer].[smtp_mail].[ux_smtp_mail]),
> > SEEK:([smtp_mail].[pli_id]=[@.1]) ORDERED FORWARD [smtp_mail].[id] 1
> > 0.00320343 7.96E-005 11 0.00328303 [smtp_mail].[id]
> > PLAN_ROW 0 1
> >
> >
> > Thank you for your prompt response!
> >
> > --
> > Mitra
> >
> >
> > "mitra" wrote:
> >
> > > Hello,
> > >
> > > The execution plan captured by SQL Profiler shows us that the SQL Server is
> > > doing an Index Scan on this particular select statement running through our
> > > java application:
> > > Select col_A from tbl_name WHERE col_B like 'string'
> > > col_A is an identity col, PK , and is clustered index.
> > > col_B is a unique constraint non clustered index.
> > >
> > > However, when the same select statement is executed using SQL Server Query
> > > Analyzer, the execution plan shows us that the SQL Server did an Index Seek.
> > >
> > > Any idea why we are experiencing an Index Scan running the query through our
> > > java application vs. an Index Seek running the same query in SQL Server Query
> > > Analyzer?
> > >
> > > Thank you,
> > > --
> > > Mitra|||On Tue, 6 Sep 2005 17:19:43 -0700, "mitra"
<mitra@.discussions.microsoft.com> wrote:
>Is there a way that we could enforce jdbc driver not to pass the select
>statement with convert? Currently, we are reluctant to make any schema
>changes.
Wrapping the SQL in an SP, where you can specify the types of the
parameters, is often helpful.
J.|||You are not setting the size of the parameter so SQL Server will make a best
attempt. I don't know much about Java but I would be very suppressed to
find that it doesn't allow for you to specify a datatype and size other than
a String.
--
Andrew J. Kelly SQL MVP
"mitra" <mitra@.discussions.microsoft.com> wrote in message
news:BC5C89FB-E999-4ACA-B498-A0684EC3C42C@.microsoft.com...
> The query was done via jdbc using a prepared statement. The select
> statement
> is very simple -
> PreparedStatement stmt = conn.prepareStatment("SELECT id from tablex where
> colA = ?");
> stmt.setString(1, "abcdedfg");
> ResultSet rslt = stmt.executeQuery();
> (There is no "LIKE" clause)
> Both execution plans follow. The SQL Profiler first showing the index scan
> and the Query Analyzer second showing the index seek.
> NOTE: These particular samples came from two different databases however
> we
> are getting the same result when the queries are run against the same
> database..
> ==============================> SQL Server Profiler
> ===============================> Rows Executes StmtText
> StmtId NodeId Parent PhysicalOp
> LogicalOp Argument
> DefinedValues EstimateRows EstimateIO EstimateCPU
> AvgRowSize TotalSubtreeCost OutputList
> Warnings
> Type Parallel EstimateExecutions
> -- -- --
> -- -- -- --
> -- --
> -- -- -- --
> -- -- -- --
> -- -- --
> 1 1 Filter(WHERE:(Convert([smtp_mail].[pli_id])=[@.P0]))
> 0 1 Filter
> Filter
> WHERE:(Convert([smtp_mail].[pli_id])=[@.P0])
> 41.7855 0 8.41E-005 89
> 0.0386413 [smtp_mail].[id]
> PLAN_ROW
> 0 1
> 147 1 |--Index
> Scan(OBJECT:([SecurWrap].[SecurityServer].[smtp_mail].[ux_smtp_mail])) 0
> 2 1 Index Scan Index Scan
> OBJECT:([SecurWrap].[SecurityServer].[smtp_mail].[ux_smtp_mail])
> [smtp_mail].[pli_id], [smtp_mail].[id] 145 0.0383192 0.000238
> 89
> 0.0385572 [smtp_mail].[pli_id], [smtp_mail].[id]
> PLAN_ROW 0 1
> ========================> SQL Server Query Analyze
> =======================> Rows Executes StmtText
> StmtId
> NodeId Parent PhysicalOp LogicalOp Argument
> DefinedValues EstimateRows EstimateIO EstimateCPU AvgRowSize
> TotalSubtreeCost OutputList Warnings Type Parallel
> EstimateExecutions
> -- -- --
> --
> -- -- -- -- --
> -- -- -- -- --
> -- -- -- -- --
> --
> 1 1 Index
> Seek(OBJECT:([stress].[SecurityServer].[smtp_mail].[ux_smtp_mail]),
> SEEK:([smtp_mail].[pli_id]=[@.1]) ORDERED FORWARD) 0 1
> Index Seek Index Seek
> OBJECT:([stress].[SecurityServer].[smtp_mail].[ux_smtp_mail]),
> SEEK:([smtp_mail].[pli_id]=[@.1]) ORDERED FORWARD [smtp_mail].[id] 1
> 0.00320343 7.96E-005 11 0.00328303 [smtp_mail].[id]
> PLAN_ROW 0 1
>
> Thank you for your prompt response!
> --
> Mitra
>
> "mitra" wrote:
>> Hello,
>> The execution plan captured by SQL Profiler shows us that the SQL Server
>> is
>> doing an Index Scan on this particular select statement running through
>> our
>> java application:
>> Select col_A from tbl_name WHERE col_B like 'string'
>> col_A is an identity col, PK , and is clustered index.
>> col_B is a unique constraint non clustered index.
>> However, when the same select statement is executed using SQL Server
>> Query
>> Analyzer, the execution plan shows us that the SQL Server did an Index
>> Seek.
>> Any idea why we are experiencing an Index Scan running the query through
>> our
>> java application vs. an Index Seek running the same query in SQL Server
>> Query
>> Analyzer?
>> Thank you,
>> --
>> Mitra|||This is a multi-part message in MIME format.
--010607080102080004040000
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Interesting problem. Some developers where I work had a similar issue -
fairly well indexed tables but all of their queries coming through JDBC
from their java app were doing clustered index scans (not using the
indexes essentially) and thrashing the life out of the processors in the
box, resulting in really bad performance obviously.
As a DBA it was fairly puzzling to me because the schema all looked
fairly nice (including their indexes, although I suggested a few more
given their workload) and their queries logically should have worked
fine and in QA they did. In the end it came down to the fact that the
JDBC driver was implicitly converting all of their text data to Unicode,
but the underlying data was all non-unicode (varchar, char & text), so
SQL Server was implicitly converting the underlying data to unicode
during the plan compilation phase because all the unicode datatypes
(nvarchar, nchar & ntext) have a higher precedence than their respective
non-unicode datatypes. That meant it couldn't use the indexes defined
on the string data because the implicit conversion made the expressions
non-SARGable (I think, extrapolating, that was the basic issue).
Anyway, the developers finally discovered this from the client side
(well, middle tier actually) and changed some setting on the JDBC driver
and it all suddenly started working perfectly. I doubt I would have
ever figured that one out, as I only had access to the DB & what I could
glean from profiler, and not the middle tier drivers or client-side code.
I've left some voicemail for the dev guy, who found the problem and
changed the setting, to find out what setting it was. If he gets back
to me about it then I'll post the JDBC driver setting/switch.
Hope this helps.
--
*mike hodgson*
blog: http://sqlnerd.blogspot.com
mitra wrote:
>Alejandro,
>I appreciate for pointing out the "convert" and questioning why sql server
>is using it.
>We looked into our code and confirmed that our query did not include
>"convert". We added "convert" to our query and ran it in Query Analyzer. This
>time Sql Server did an Index Scan.
>Obvioulsy, jdbc driver (jTDS) is doing the convert, why we don't know. Any
>Idea?
>The column data type is char(60).
>We thought of testing the jdbc driver by changing the data type char(60) to
>Varchar(60) and see if it would do the convert again. It did not!
>Is there a way that we could enforce jdbc driver not to pass the select
>statement with convert? Currently, we are reluctant to make any schema
>changes.
>Again I appreciate your prompt help.
>Thank you so much!
>
--010607080102080004040000
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=UTF-8" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>Interesting problem.Â Some developers where I work had a similar
issue - fairly well indexed tables but all of their queries coming
through JDBC from their java app were doing clustered index scans (not
using the indexes essentially) and thrashing the life out of the
processors in the box, resulting in really bad performance obviously. 
 
As a DBA it was fairly puzzling to me because the schema all looked
fairly nice (including their indexes, although I suggested a few more
given their workload) and their queries logically should have worked
fine and in QA they did.Â In the end it came down to the fact that the
JDBC driver was implicitly converting all of their text data to
Unicode, but the underlying data was all non-unicode (varchar, char
& text), so SQL Server was implicitly converting the underlying
data to unicode during the plan compilation phase because all the
unicode datatypes (nvarchar, nchar & ntext) have a higher
precedence than their respective non-unicode datatypes.Â That meant it
couldn't use the indexes defined on the string data because the
implicit conversion made the expressions non-SARGable (I think,
extrapolating, that was the basic issue). 
 
Anyway, the developers finally discovered this from the client side
(well, middle tier actually) and changed some setting on the JDBC
driver and it all suddenly started working perfectly.Â I doubt I would
have ever figured that one out, as I only had access to the DB &
what I could glean from profiler, and not the middle tier drivers or
client-side code. 
 
I've left some voicemail for the dev guy, who found the problem and
changed the setting, to find out what setting it was.Â If he gets back
to me about it then I'll post the JDBC driver setting/switch. 
 
Hope this helps. 
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
-- 
 mike
hodgson 
blog: <a
href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a>

</div>
 
 
mitra wrote:
<blockquote cite="mid3AD861E2-2627-412A-BBD5-3E3A173C8D3D@.microsoft.com"
type="cite">
<pre wrap="">Alejandro,
I appreciate for pointing out the "convert" and questioning why sql server
is using it.
We looked into our code and confirmed that our query did not include
"convert". We added "convert" to our query and ran it in Query Analyzer. This
time Sql Server did an Index Scan.
Obvioulsy, jdbc driver (jTDS) is doing the convert, why we don't know. Any
Idea?
The column data type is char(60).
We thought of testing the jdbc driver by changing the data type char(60) to
Varchar(60) and see if it would do the convert again. It did not!
Is there a way that we could enforce jdbc driver not to pass the select
statement with convert? Currently, we are reluctant to make any schema
changes.
Again I appreciate your prompt help.
Thank you so much!
</pre>
</blockquote>
</body>
</html>
--010607080102080004040000--|||This is a multi-part message in MIME format.
--080701050701000804060200
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
It's part of the connection string. The parameter is
*SendStringParametersAsUnicode* and it's true by default.
Connection Parameters:
SendStringParametersAsUnicode
Determines whether string parameters are sent to the SQL Server
database in Unicode or in the default character encoding of the
database. True means that string parameters are sent to SQL Server
in Unicode. False means that they are sent in the default encoding,
which can improve performance because the server does not need to
convert Unicode characters to the default encoding. You should,
however, use default encoding only if the parameter string data that
you specify is consistent with the default encoding of the database.
Default value is true.
Apparently it's documented in the documentation that comes with the
Microsoft JDBC driver (I haven't played with MS-JDBC myself). Here's a
pretty good page (on the WebLogic website) that talks about it (I can't
readily find an official Microsoft page outlining the JDBC connection
parameters):
http://e-docs.bea.com/wls/docs81/jdbc_drivers/mssqlserver.html
Hope this helps.
--
*mike hodgson*
blog: http://sqlnerd.blogspot.com
Mike Hodgson wrote:
> Interesting problem. Some developers where I work had a similar issue
> - fairly well indexed tables but all of their queries coming through
> JDBC from their java app were doing clustered index scans (not using
> the indexes essentially) and thrashing the life out of the processors
> in the box, resulting in really bad performance obviously.
> As a DBA it was fairly puzzling to me because the schema all looked
> fairly nice (including their indexes, although I suggested a few more
> given their workload) and their queries logically should have worked
> fine and in QA they did. In the end it came down to the fact that the
> JDBC driver was implicitly converting all of their text data to
> Unicode, but the underlying data was all non-unicode (varchar, char &
> text), so SQL Server was implicitly converting the underlying data to
> unicode during the plan compilation phase because all the unicode
> datatypes (nvarchar, nchar & ntext) have a higher precedence than
> their respective non-unicode datatypes. That meant it couldn't use
> the indexes defined on the string data because the implicit conversion
> made the expressions non-SARGable (I think, extrapolating, that was
> the basic issue).
> Anyway, the developers finally discovered this from the client side
> (well, middle tier actually) and changed some setting on the JDBC
> driver and it all suddenly started working perfectly. I doubt I would
> have ever figured that one out, as I only had access to the DB & what
> I could glean from profiler, and not the middle tier drivers or
> client-side code.
> I've left some voicemail for the dev guy, who found the problem and
> changed the setting, to find out what setting it was. If he gets back
> to me about it then I'll post the JDBC driver setting/switch.
> Hope this helps.
> --
> *mike hodgson*
> blog: http://sqlnerd.blogspot.com
>
> mitra wrote:
>>Alejandro,
>>I appreciate for pointing out the "convert" and questioning why sql server
>>is using it.
>>We looked into our code and confirmed that our query did not include
>>"convert". We added "convert" to our query and ran it in Query Analyzer. This
>>time Sql Server did an Index Scan.
>>Obvioulsy, jdbc driver (jTDS) is doing the convert, why we don't know. Any
>>Idea?
>>The column data type is char(60).
>>We thought of testing the jdbc driver by changing the data type char(60) to
>>Varchar(60) and see if it would do the convert again. It did not!
>>Is there a way that we could enforce jdbc driver not to pass the select
>>statement with convert? Currently, we are reluctant to make any schema
>>changes.
>>Again I appreciate your prompt help.
>>Thank you so much!
>>
--080701050701000804060200
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=UTF-8" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>It's part of the connection string.Â The parameter is SendStringParametersAsUnicode
and it's true by default. 
</tt>
<blockquote><tt>Connection Parameters:</tt> 
 
<tt>SendStringParametersAsUnicode</tt> 
 
<tt>Determines whether string parameters are sent to the SQL Server
database in Unicode or in the default character encoding of the
database. True means that string parameters are sent to SQL Server in
Unicode. False means that they are sent in the default encoding, which
can improve performance because the server does not need to convert
Unicode characters to the default encoding. You should, however, use
default encoding only if the parameter string data that you specify is
consistent with the default encoding of the database. Default value is
true. 
</tt></blockquote>
<tt>Apparently it's documented in the documentation that comes with the
Microsoft JDBC driver (I haven't played with MS-JDBC myself).Â Here's a
pretty good page (on the WebLogic website) that talks about it (I can't
readily find an official Microsoft page outlining the JDBC connection
parameters): 
<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=http://e-docs.bea.com/wls/docs81/jdbc_drivers/mssqlserver.html</a> ">http://e-docs.bea.com/wls/docs81/jdbc_drivers/mssqlserver.html">http://e-docs.bea.com/wls/docs81/jdbc_drivers/mssqlserver.html</a> 
 
Hope this helps. 
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
-- 
 mike
hodgson 
blog: <a
href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a>

</div>
 
 
Mike Hodgson wrote:
<blockquote cite="midOUsRAz3sFHA.664@.tk2msftngp13.phx.gbl" type="cite">
<meta content="text/html;charset=UTF-8" http-equiv="Content-Type">
<tt>Interesting problem.Â Some developers where I work had a similar
issue - fairly well indexed tables but all of their queries coming
through JDBC from their java app were doing clustered index scans (not
using the indexes essentially) and thrashing the life out of the
processors in the box, resulting in really bad performance obviously. 
 
As a DBA it was fairly puzzling to me because the schema all looked
fairly nice (including their indexes, although I suggested a few more
given their workload) and their queries logically should have worked
fine and in QA they did.Â In the end it came down to the fact that the
JDBC driver was implicitly converting all of their text data to
Unicode, but the underlying data was all non-unicode (varchar, char
& text), so SQL Server was implicitly converting the underlying
data to unicode during the plan compilation phase because all the
unicode datatypes (nvarchar, nchar & ntext) have a higher
precedence than their respective non-unicode datatypes.Â That meant it
couldn't use the indexes defined on the string data because the
implicit conversion made the expressions non-SARGable (I think,
extrapolating, that was the basic issue). 
 
Anyway, the developers finally discovered this from the client side
(well, middle tier actually) and changed some setting on the JDBC
driver and it all suddenly started working perfectly.Â I doubt I would
have ever figured that one out, as I only had access to the DB &
what I could glean from profiler, and not the middle tier drivers or
client-side code. 
 
I've left some voicemail for the dev guy, who found the problem and
changed the setting, to find out what setting it was.Â If he gets back
to me about it then I'll post the JDBC driver setting/switch. 
 
Hope this helps. 
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
-- 
 mike
hodgson 
blog:
<a href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a>

</div>
 
 
mitra wrote:
<blockquote
cite="mid3AD861E2-2627-412A-BBD5-3E3A173C8D3D@.microsoft.com"
type="cite">
<pre wrap="">Alejandro,
I appreciate for pointing out the "convert" and questioning why sql server
is using it.
We looked into our code and confirmed that our query did not include
"convert". We added "convert" to our query and ran it in Query Analyzer. This
time Sql Server did an Index Scan.
Obvioulsy, jdbc driver (jTDS) is doing the convert, why we don't know. Any
Idea?
The column data type is char(60).
We thought of testing the jdbc driver by changing the data type char(60) to
Varchar(60) and see if it would do the convert again. It did not!
Is there a way that we could enforce jdbc driver not to pass the select
statement with convert? Currently, we are reluctant to make any schema
changes.
Again I appreciate your prompt help.
Thank you so much!
</pre>
</blockquote>
</blockquote>
</body>
</html>
--080701050701000804060200--|||Mike,
Thanks a lot for sharing that with the group.
Regards,
Alejandro Mesa
"Mike Hodgson" wrote:
> It's part of the connection string. The parameter is
> *SendStringParametersAsUnicode* and it's true by default.
> Connection Parameters:
> SendStringParametersAsUnicode
> Determines whether string parameters are sent to the SQL Server
> database in Unicode or in the default character encoding of the
> database. True means that string parameters are sent to SQL Server
> in Unicode. False means that they are sent in the default encoding,
> which can improve performance because the server does not need to
> convert Unicode characters to the default encoding. You should,
> however, use default encoding only if the parameter string data that
> you specify is consistent with the default encoding of the database.
> Default value is true.
> Apparently it's documented in the documentation that comes with the
> Microsoft JDBC driver (I haven't played with MS-JDBC myself). Here's a
> pretty good page (on the WebLogic website) that talks about it (I can't
> readily find an official Microsoft page outlining the JDBC connection
> parameters):
> http://e-docs.bea.com/wls/docs81/jdbc_drivers/mssqlserver.html
> Hope this helps.
> --
> *mike hodgson*
> blog: http://sqlnerd.blogspot.com
>
> Mike Hodgson wrote:
> > Interesting problem. Some developers where I work had a similar issue
> > - fairly well indexed tables but all of their queries coming through
> > JDBC from their java app were doing clustered index scans (not using
> > the indexes essentially) and thrashing the life out of the processors
> > in the box, resulting in really bad performance obviously.
> >
> > As a DBA it was fairly puzzling to me because the schema all looked
> > fairly nice (including their indexes, although I suggested a few more
> > given their workload) and their queries logically should have worked
> > fine and in QA they did. In the end it came down to the fact that the
> > JDBC driver was implicitly converting all of their text data to
> > Unicode, but the underlying data was all non-unicode (varchar, char &
> > text), so SQL Server was implicitly converting the underlying data to
> > unicode during the plan compilation phase because all the unicode
> > datatypes (nvarchar, nchar & ntext) have a higher precedence than
> > their respective non-unicode datatypes. That meant it couldn't use
> > the indexes defined on the string data because the implicit conversion
> > made the expressions non-SARGable (I think, extrapolating, that was
> > the basic issue).
> >
> > Anyway, the developers finally discovered this from the client side
> > (well, middle tier actually) and changed some setting on the JDBC
> > driver and it all suddenly started working perfectly. I doubt I would
> > have ever figured that one out, as I only had access to the DB & what
> > I could glean from profiler, and not the middle tier drivers or
> > client-side code.
> >
> > I've left some voicemail for the dev guy, who found the problem and
> > changed the setting, to find out what setting it was. If he gets back
> > to me about it then I'll post the JDBC driver setting/switch.
> >
> > Hope this helps.
> >
> > --
> > *mike hodgson*
> > blog: http://sqlnerd.blogspot.com
> >
> >
> >
> > mitra wrote:
> >
> >>Alejandro,
> >>
> >>I appreciate for pointing out the "convert" and questioning why sql server
> >>is using it.
> >>We looked into our code and confirmed that our query did not include
> >>"convert". We added "convert" to our query and ran it in Query Analyzer. This
> >>time Sql Server did an Index Scan.
> >>Obvioulsy, jdbc driver (jTDS) is doing the convert, why we don't know. Any
> >>Idea?
> >>The column data type is char(60).
> >>We thought of testing the jdbc driver by changing the data type char(60) to
> >>Varchar(60) and see if it would do the convert again. It did not!
> >>
> >>Is there a way that we could enforce jdbc driver not to pass the select
> >>statement with convert? Currently, we are reluctant to make any schema
> >>changes.
> >>
> >>Again I appreciate your prompt help.
> >>
> >>Thank you so much!
> >>
> >>
>sql

index scan and seek help...

hi,
I create a view within 3 tables unoin and every table has a cluster index
(corgn ,cssym).
create view vsc1
as
(select * from tableA
union
select * from tableB
union
select * from tableC)
select * from vsc1 where corgn = '2213' and cssym = '200502'
It's very weired the execution plan shows one of the tables, says tableA,
is clustered index scan instead of clustered index seek.
I recreate the view with only one table (tableA) and found the execution
plan shows a clustered index seek.
Why SQL SERVER has such a different execution plan?
Any help is very appreciated.
moash wrote:
> hi,
> I create a view within 3 tables unoin and every table has a cluster
> index (corgn ,cssym).
> create view vsc1
> as
> (select * from tableA
> union
> select * from tableB
> union
> select * from tableC)
> select * from vsc1 where corgn = '2213' and cssym = '200502'
> It's very weired the execution plan shows one of the tables, says
> tableA, is clustered index scan instead of clustered index seek.
> I recreate the view with only one table (tableA) and found the
> execution plan shows a clustered index seek.
> Why SQL SERVER has such a different execution plan?
> Any help is very appreciated.
I guess your tables differ in any of
- size
- distribution
- indexes
You would have to provide table definitions including indexes and an
outline of the volume in those tables for more concrete answers.
Kind regards
robert
|||An index scan does not always mean a full scan. If you look closely it
usually says scanning an index or a particular range of rows from the index.
If the value you chose had several rows that matched it the leaf level of
the index can be scanned to retrieve all the matching rows after the initial
seek.
Andrew J. Kelly SQL MVP
"moash" <moashPPP@.hotmail.com> wrote in message
news:%238pmbE1ZFHA.1448@.TK2MSFTNGP09.phx.gbl...
> hi,
> I create a view within 3 tables unoin and every table has a cluster index
> (corgn ,cssym).
> create view vsc1
> as
> (select * from tableA
> union
> select * from tableB
> union
> select * from tableC)
> select * from vsc1 where corgn = '2213' and cssym = '200502'
> It's very weired the execution plan shows one of the tables, says tableA,
> is clustered index scan instead of clustered index seek.
> I recreate the view with only one table (tableA) and found the execution
> plan shows a clustered index seek.
> Why SQL SERVER has such a different execution plan?
> Any help is very appreciated.
>
|||Also, just as an FYI... the union operator automatically eliminates
duplicate rows, which can be very expensive... If you know there will not be
dupe rows, or do not care, use the Union All command instead.
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"moash" <moashPPP@.hotmail.com> wrote in message
news:%238pmbE1ZFHA.1448@.TK2MSFTNGP09.phx.gbl...
> hi,
> I create a view within 3 tables unoin and every table has a cluster index
> (corgn ,cssym).
> create view vsc1
> as
> (select * from tableA
> union
> select * from tableB
> union
> select * from tableC)
> select * from vsc1 where corgn = '2213' and cssym = '200502'
> It's very weired the execution plan shows one of the tables, says tableA,
> is clustered index scan instead of clustered index seek.
> I recreate the view with only one table (tableA) and found the execution
> plan shows a clustered index seek.
> Why SQL SERVER has such a different execution plan?
> Any help is very appreciated.
>
|||Actually, I import these 3 tables to another DB in the same instance,with
same view and clustered indexes ,
and I found the execution plan showed all table are index seek.
But when I try to import these 3 tables with different table name to the
same DB,
the execution paln just nothing changed , tableA is always clustered index
scan instead of clustered index seek.
My box is 4 2.80 ZEON CPU,8G RAM(AWE enabled), SQL SERVER Tranditional
Chinese SP3,WIN2003 Enterprise,RAID 5
Any help is very appreciated.

> Also, just as an FYI... the union operator automatically eliminates
> duplicate rows, which can be very expensive... If you know there will not
be[vbcol=seagreen]
> dupe rows, or do not care, use the Union All command instead.
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> (Please respond only to the newsgroup.)
> I support the Professional Association for SQL Server ( PASS) and it's
> community of SQL Professionals.
> "moash" <moashPPP@.hotmail.com> wrote in message
> news:%238pmbE1ZFHA.1448@.TK2MSFTNGP09.phx.gbl...
index[vbcol=seagreen]
tableA,[vbcol=seagreen]
execution
>
|||How I can send u these infromation?

> moash wrote:
> I guess your tables differ in any of
> - size
> - distribution
> - indexes
> You would have to provide table definitions including indexes and an
> outline of the volume in those tables for more concrete answers.
> Kind regards
> robert
>
|||Also, to add to everyone else's comments, whenever you specify something
like SELECT *, chances are if there is a "Covering" Index, the Optimizer
will weigh the cost of scanning that index verses seeking a different index
and then incuring a Bookmark Lookup. The best of the two would be a
filtered "scan" on the clustered index because all columns are covered.
Since you specified that the View should return all columns, it is a safe
bet that the optimizer chose the cluster index scan over a seek because of
the covering effect.
You should always specify every column returned, explicitly, even if it is
every column.
Make this minor modification and see if it makes any difference, even when
you call the View.
Sincerely,
Anthony Thomas

"moash" <moashPPP@.hotmail.com> wrote in message
news:%238pmbE1ZFHA.1448@.TK2MSFTNGP09.phx.gbl...
hi,
I create a view within 3 tables unoin and every table has a cluster index
(corgn ,cssym).
create view vsc1
as
(select * from tableA
union
select * from tableB
union
select * from tableC)
select * from vsc1 where corgn = '2213' and cssym = '200502'
It's very weired the execution plan shows one of the tables, says tableA,
is clustered index scan instead of clustered index seek.
I recreate the view with only one table (tableA) and found the execution
plan shows a clustered index seek.
Why SQL SERVER has such a different execution plan?
Any help is very appreciated.

index scan and seek help...

hi,
I create a view within 3 tables unoin and every table has a cluster index
(corgn ,cssym).
create view vsc1
as
(select * from tableA
union
select * from tableB
union
select * from tableC)
select * from vsc1 where corgn = '2213' and cssym = '200502'
It's very weired the execution plan shows one of the tables, says tableA,
is clustered index scan instead of clustered index seek.
I recreate the view with only one table (tableA) and found the execution
plan shows a clustered index seek.
Why SQL SERVER has such a different execution plan?
Any help is very appreciated.moash wrote:
> hi,
> I create a view within 3 tables unoin and every table has a cluster
> index (corgn ,cssym).
> create view vsc1
> as
> (select * from tableA
> union
> select * from tableB
> union
> select * from tableC)
> select * from vsc1 where corgn = '2213' and cssym = '200502'
> It's very weired the execution plan shows one of the tables, says
> tableA, is clustered index scan instead of clustered index seek.
> I recreate the view with only one table (tableA) and found the
> execution plan shows a clustered index seek.
> Why SQL SERVER has such a different execution plan?
> Any help is very appreciated.
I guess your tables differ in any of
- size
- distribution
- indexes
You would have to provide table definitions including indexes and an
outline of the volume in those tables for more concrete answers.
Kind regards
robert|||An index scan does not always mean a full scan. If you look closely it
usually says scanning an index or a particular range of rows from the index.
If the value you chose had several rows that matched it the leaf level of
the index can be scanned to retrieve all the matching rows after the initial
seek.
Andrew J. Kelly SQL MVP
"moash" <moashPPP@.hotmail.com> wrote in message
news:%238pmbE1ZFHA.1448@.TK2MSFTNGP09.phx.gbl...
> hi,
> I create a view within 3 tables unoin and every table has a cluster index
> (corgn ,cssym).
> create view vsc1
> as
> (select * from tableA
> union
> select * from tableB
> union
> select * from tableC)
> select * from vsc1 where corgn = '2213' and cssym = '200502'
> It's very weired the execution plan shows one of the tables, says tableA,
> is clustered index scan instead of clustered index seek.
> I recreate the view with only one table (tableA) and found the execution
> plan shows a clustered index seek.
> Why SQL SERVER has such a different execution plan?
> Any help is very appreciated.
>|||Also, just as an FYI... the union operator automatically eliminates
duplicate rows, which can be very expensive... If you know there will not be
dupe rows, or do not care, use the Union All command instead.
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"moash" <moashPPP@.hotmail.com> wrote in message
news:%238pmbE1ZFHA.1448@.TK2MSFTNGP09.phx.gbl...
> hi,
> I create a view within 3 tables unoin and every table has a cluster index
> (corgn ,cssym).
> create view vsc1
> as
> (select * from tableA
> union
> select * from tableB
> union
> select * from tableC)
> select * from vsc1 where corgn = '2213' and cssym = '200502'
> It's very weired the execution plan shows one of the tables, says tableA,
> is clustered index scan instead of clustered index seek.
> I recreate the view with only one table (tableA) and found the execution
> plan shows a clustered index seek.
> Why SQL SERVER has such a different execution plan?
> Any help is very appreciated.
>|||Actually, I import these 3 tables to another DB in the same instance,with
same view and clustered indexes ,
and I found the execution plan showed all table are index seek.
But when I try to import these 3 tables with different table name to the
same DB,
the execution paln just nothing changed , tableA is always clustered index
scan instead of clustered index seek.
My box is 4 2.80 ZEON CPU,8G RAM(AWE enabled), SQL SERVER Tranditional
Chinese SP3,WIN2003 Enterprise,RAID 5
Any help is very appreciated.

> Also, just as an FYI... the union operator automatically eliminates
> duplicate rows, which can be very expensive... If you know there will not
be
> dupe rows, or do not care, use the Union All command instead.
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> (Please respond only to the newsgroup.)
> I support the Professional Association for SQL Server ( PASS) and it's
> community of SQL Professionals.
> "moash" <moashPPP@.hotmail.com> wrote in message
> news:%238pmbE1ZFHA.1448@.TK2MSFTNGP09.phx.gbl...
index[vbcol=seagreen]
tableA,[vbcol=seagreen]
execution[vbcol=seagreen]
>|||How I can send u these infromation?

> moash wrote:
> I guess your tables differ in any of
> - size
> - distribution
> - indexes
> You would have to provide table definitions including indexes and an
> outline of the volume in those tables for more concrete answers.
> Kind regards
> robert
>|||Also, to add to everyone else's comments, whenever you specify something
like SELECT *, chances are if there is a "Covering" Index, the Optimizer
will weigh the cost of scanning that index verses seeking a different index
and then incuring a Bookmark Lookup. The best of the two would be a
filtered "scan" on the clustered index because all columns are covered.
Since you specified that the View should return all columns, it is a safe
bet that the optimizer chose the cluster index scan over a seek because of
the covering effect.
You should always specify every column returned, explicitly, even if it is
every column.
Make this minor modification and see if it makes any difference, even when
you call the View.
Sincerely,
Anthony Thomas
"moash" <moashPPP@.hotmail.com> wrote in message
news:%238pmbE1ZFHA.1448@.TK2MSFTNGP09.phx.gbl...
hi,
I create a view within 3 tables unoin and every table has a cluster index
(corgn ,cssym).
create view vsc1
as
(select * from tableA
union
select * from tableB
union
select * from tableC)
select * from vsc1 where corgn = '2213' and cssym = '200502'
It's very weired the execution plan shows one of the tables, says tableA,
is clustered index scan instead of clustered index seek.
I recreate the view with only one table (tableA) and found the execution
plan shows a clustered index seek.
Why SQL SERVER has such a different execution plan?
Any help is very appreciated.sql

Index scan

If i have only one index and that being a clustered index on say a datetime
column namely date1 and if my query is
select col1,col2 from table1 where date1 between '2/1/2008' and '2/3/2008'
If I look at the query plan and it says its doing an index scan, does that
mean its actually touching each and every page of that table or will it
somehow start at the page that has the first entry for '2/1/2008' and
continues through the linked list at the leaf level of the pages and stops
after it reaches '2/3/2008' ?
How is this different if instead of a clustered index, its a non clustered
index ?
ThanksJohn
It is doing Clustered Index Scan , you meant? If you have CI on the table
that means SQL Server logicaly orders all data by Clustered Index Key.
It depends on the query , an optimizer may or may not decide to do a scan
, for example the table is pretty small .
In this case it scans index pages toread the data which ordered (logicaly)
by date column
In you case I'd suggest t create an index on col1,col2 and dt columns called
COVERING index.
> How is this different if instead of a clustered index, its a non clustered
> index ?
The difference is that clusterd index contains at the bottom level the
actual data , while noclustetred contains pointers to the data pages.
"John Doe" <Johndoe@.jd.com> wrote in message
news:eXI36LscIHA.484@.TK2MSFTNGP06.phx.gbl...
> If i have only one index and that being a clustered index on say a
> datetime column namely date1 and if my query is
> select col1,col2 from table1 where date1 between '2/1/2008' and '2/3/2008'
> If I look at the query plan and it says its doing an index scan, does that
> mean its actually touching each and every page of that table or will it
> somehow start at the page that has the first entry for '2/1/2008' and
> continues through the linked list at the leaf level of the pages and stops
> after it reaches '2/3/2008' ?
> How is this different if instead of a clustered index, its a non clustered
> index ?
> Thanks
>|||Uri,
In my case, its doing a clustered index scan and wanted to know if as a
result, its touching all the pages that may have dates prior to '2/1' and
after '2/3' as my query is only seeking to obtain data between '2/1/2008 and
'2/3/2008' and as you mentioned that data in the CI is ordered. Let me know
how the storage engine fetches the pages.
Also if it was a non clustered index on date1 instead of a clustered index,
i take it that the leaf level of the non clustered index is also
ordered..right ?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OBuZpSscIHA.3932@.TK2MSFTNGP05.phx.gbl...
> John
> It is doing Clustered Index Scan , you meant? If you have CI on the table
> that means SQL Server logicaly orders all data by Clustered Index Key.
> It depends on the query , an optimizer may or may not decide to do a scan
> , for example the table is pretty small .
> In this case it scans index pages toread the data which ordered (logicaly)
> by date column
> In you case I'd suggest t create an index on col1,col2 and dt columns
> called COVERING index.
>
>> How is this different if instead of a clustered index, its a non
>> clustered index ?
> The difference is that clusterd index contains at the bottom level the
> actual data , while noclustetred contains pointers to the data pages.
>
> "John Doe" <Johndoe@.jd.com> wrote in message
> news:eXI36LscIHA.484@.TK2MSFTNGP06.phx.gbl...
>> If i have only one index and that being a clustered index on say a
>> datetime column namely date1 and if my query is
>> select col1,col2 from table1 where date1 between '2/1/2008' and
>> '2/3/2008'
>> If I look at the query plan and it says its doing an index scan, does
>> that mean its actually touching each and every page of that table or will
>> it somehow start at the page that has the first entry for '2/1/2008' and
>> continues through the linked list at the leaf level of the pages and
>> stops after it reaches '2/3/2008' ?
>> How is this different if instead of a clustered index, its a non
>> clustered index ?
>> Thanks
>>
>|||Let me get back to the original question. SQL Server may decide to go for
partial scan (reading a range of values) if data is ordered.
And yes, data is ordered logically by the key in both cases, difference
being that in the CI, ALL data is in the leaf level.
MC
"John Doe" <Johndoe@.jd.com> wrote in message
news:uT7oOcscIHA.748@.TK2MSFTNGP04.phx.gbl...
> Uri,
> In my case, its doing a clustered index scan and wanted to know if as a
> result, its touching all the pages that may have dates prior to '2/1' and
> after '2/3' as my query is only seeking to obtain data between '2/1/2008
> and '2/3/2008' and as you mentioned that data in the CI is ordered. Let me
> know how the storage engine fetches the pages.
> Also if it was a non clustered index on date1 instead of a clustered
> index, i take it that the leaf level of the non clustered index is also
> ordered..right ?
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:OBuZpSscIHA.3932@.TK2MSFTNGP05.phx.gbl...
>> John
>> It is doing Clustered Index Scan , you meant? If you have CI on the table
>> that means SQL Server logicaly orders all data by Clustered Index Key.
>> It depends on the query , an optimizer may or may not decide to do a
>> scan , for example the table is pretty small .
>> In this case it scans index pages toread the data which ordered
>> (logicaly) by date column
>> In you case I'd suggest t create an index on col1,col2 and dt columns
>> called COVERING index.
>>
>> How is this different if instead of a clustered index, its a non
>> clustered index ?
>> The difference is that clusterd index contains at the bottom level the
>> actual data , while noclustetred contains pointers to the data pages.
>>
>> "John Doe" <Johndoe@.jd.com> wrote in message
>> news:eXI36LscIHA.484@.TK2MSFTNGP06.phx.gbl...
>> If i have only one index and that being a clustered index on say a
>> datetime column namely date1 and if my query is
>> select col1,col2 from table1 where date1 between '2/1/2008' and
>> '2/3/2008'
>> If I look at the query plan and it says its doing an index scan, does
>> that mean its actually touching each and every page of that table or
>> will it somehow start at the page that has the first entry for
>> '2/1/2008' and continues through the linked list at the leaf level of
>> the pages and stops after it reaches '2/3/2008' ?
>> How is this different if instead of a clustered index, its a non
>> clustered index ?
>> Thanks
>>
>>
>|||John
> Also if it was a non clustered index on date1 instead of a clustered
> index, i take it that the leaf level of the non clustered index is also
> ordered..right ?
Think about CI as phone book which is 'ordered' by LastName ,so if you
want to search by Lastname is easy to traverse and get it
But what if you want to search by NCI -FirstName, then you will have to
page by page which requires an 'extra work'
I'd suggest to take some course or buying a book to underastand the
structure and behaviour.
"John Doe" <Johndoe@.jd.com> wrote in message
news:uT7oOcscIHA.748@.TK2MSFTNGP04.phx.gbl...
> Uri,
> In my case, its doing a clustered index scan and wanted to know if as a
> result, its touching all the pages that may have dates prior to '2/1' and
> after '2/3' as my query is only seeking to obtain data between '2/1/2008
> and '2/3/2008' and as you mentioned that data in the CI is ordered. Let me
> know how the storage engine fetches the pages.
> Also if it was a non clustered index on date1 instead of a clustered
> index, i take it that the leaf level of the non clustered index is also
> ordered..right ?
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:OBuZpSscIHA.3932@.TK2MSFTNGP05.phx.gbl...
>> John
>> It is doing Clustered Index Scan , you meant? If you have CI on the table
>> that means SQL Server logicaly orders all data by Clustered Index Key.
>> It depends on the query , an optimizer may or may not decide to do a
>> scan , for example the table is pretty small .
>> In this case it scans index pages toread the data which ordered
>> (logicaly) by date column
>> In you case I'd suggest t create an index on col1,col2 and dt columns
>> called COVERING index.
>>
>> How is this different if instead of a clustered index, its a non
>> clustered index ?
>> The difference is that clusterd index contains at the bottom level the
>> actual data , while noclustetred contains pointers to the data pages.
>>
>> "John Doe" <Johndoe@.jd.com> wrote in message
>> news:eXI36LscIHA.484@.TK2MSFTNGP06.phx.gbl...
>> If i have only one index and that being a clustered index on say a
>> datetime column namely date1 and if my query is
>> select col1,col2 from table1 where date1 between '2/1/2008' and
>> '2/3/2008'
>> If I look at the query plan and it says its doing an index scan, does
>> that mean its actually touching each and every page of that table or
>> will it somehow start at the page that has the first entry for
>> '2/1/2008' and continues through the linked list at the leaf level of
>> the pages and stops after it reaches '2/3/2008' ?
>> How is this different if instead of a clustered index, its a non
>> clustered index ?
>> Thanks
>>
>>
>|||On Feb 19, 12:45=A0pm, "John Doe" <John...@.jd.com> wrote:
> If i have only one index and that being a clustered index on say a datetim=e
> column namely date1 and if my query is
> select col1,col2 from table1 where date1 between '2/1/2008' and '2/3/2008'=
> If I look at the query plan and it says its doing an index scan, does that=
> mean its actually touching each and every page of that table or will it
> somehow start at the page that has the first entry for '2/1/2008' and
> continues through the linked list at the leaf level of the pages and stops=
> after it reaches '2/3/2008' ?
> How is this different if instead of a clustered index, its a non clustered=
> index ?
> Thanks
With clustered indexes records are stored on the leaf level and
therefore faster but non-clustered indexes leaf level pages have
locations to the page of searched record and therefore slower. HTH.|||If you have a clustered index, yes, it will go to the first entry in the
table with a datetime between '2/1/2008' and '2/3/2008', do a partial scan
of the clustered index and stop as soon as it finds an datetime >
'2/3/2008'. BTW, it is best in SQL Server to specify datetimes as
'yyyy-mm-ddThh:mm:ss.mmm'
or 'yyyy-mm-ddThh:mm:ss'
or 'yyyymmdd'
When you use a date format like '2/1/2008', that is Feb 1 in some locations
and Jan 2 in others. But '20080201' is Feb 1 everywhere.
For a nonclustered index, the answer is, "it depends". If the index is
nonclustered, then SQL Server knows the rows for any given value of dt might
be scattered throughout the physical table. So, for example, the first row
might be in page 1000, followed by a bunch of rows in other pages, followed
by another row in page 1000. But by this time page 1000 might not be in
memory anymore, so the page must be physically read again (this could
possibly happen many times). So SQL Server attempts to keep statistics on
how many rows are in each range and will attempt to estimate what percentage
of the table your query will return. If it is a small percentage, it will
use your index on dt, go to the first entry in the index with a date >='2/1/2008', start there and scan the index until it reaches a row with a dt
> '2/3'2008' and then stop. For each row it finds in the index on dt, it
will then use the clustered index to find the actual row and return your
values. But if it is a large percentage, then SQL Server will just scan the
entire clustered index (that is, it won't use the nonclustered index on dt
at all), because it knows that way it only has to read each physical page in
the clustered index once.
As Uri points out, your nonclustered index can be what is known as a
"covering index" for your query. This occurs when every column you need for
your query is in the index (either explicitly as part of the key or in the
INCLUDED columns or implicitly because the column is in the key of the
clustered index). When that happens, SQL Server knows it can satisify the
query requirements without ever reading from the table, so it will use the
index and only scan the part of the index it needs for the range of data you
want.
Tom
"John Doe" <Johndoe@.jd.com> wrote in message
news:uT7oOcscIHA.748@.TK2MSFTNGP04.phx.gbl...
> Uri,
> In my case, its doing a clustered index scan and wanted to know if as a
> result, its touching all the pages that may have dates prior to '2/1' and
> after '2/3' as my query is only seeking to obtain data between '2/1/2008
> and '2/3/2008' and as you mentioned that data in the CI is ordered. Let me
> know how the storage engine fetches the pages.
> Also if it was a non clustered index on date1 instead of a clustered
> index, i take it that the leaf level of the non clustered index is also
> ordered..right ?
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:OBuZpSscIHA.3932@.TK2MSFTNGP05.phx.gbl...
>> John
>> It is doing Clustered Index Scan , you meant? If you have CI on the table
>> that means SQL Server logicaly orders all data by Clustered Index Key.
>> It depends on the query , an optimizer may or may not decide to do a
>> scan , for example the table is pretty small .
>> In this case it scans index pages toread the data which ordered
>> (logicaly) by date column
>> In you case I'd suggest t create an index on col1,col2 and dt columns
>> called COVERING index.
>>
>> How is this different if instead of a clustered index, its a non
>> clustered index ?
>> The difference is that clusterd index contains at the bottom level the
>> actual data , while noclustetred contains pointers to the data pages.
>>
>> "John Doe" <Johndoe@.jd.com> wrote in message
>> news:eXI36LscIHA.484@.TK2MSFTNGP06.phx.gbl...
>> If i have only one index and that being a clustered index on say a
>> datetime column namely date1 and if my query is
>> select col1,col2 from table1 where date1 between '2/1/2008' and
>> '2/3/2008'
>> If I look at the query plan and it says its doing an index scan, does
>> that mean its actually touching each and every page of that table or
>> will it somehow start at the page that has the first entry for
>> '2/1/2008' and continues through the linked list at the leaf level of
>> the pages and stops after it reaches '2/3/2008' ?
>> How is this different if instead of a clustered index, its a non
>> clustered index ?
>> Thanks
>>
>>
>

Friday, March 30, 2012

index tuning wizard

Index Table

Index Table

Wednesday, March 28, 2012

Index size.

Index seek vs. index scan

Index seek vs. index scan

Index seek vs. index scan

index scan and seek help...

index scan and seek help...

Index scan

index and key

Blog Archive

About Me