index and key: particular

Showing posts with label particular. Show all posts

Wednesday, March 28, 2012

Index size.

hi,
from where we can find out index size..of clus index or al indexes of any particular table.
regardsHave a look at sp_spaceused. If you want to do this non-
programatically, you can use the taskpad view of tha
database.
Regards,
Paul Ibison|||thanks..got it.

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 related problems? Whats happening here?

All queries for a particular table seems to be slow. It has one
clustered index on the primary key column which of data type INT and
has identity insert ON. This table has < 10000 rows and is fast with
response in all other circumstances. The clustered index is at a fill
factor of 90% and I have toyed upto 70% fillfactor.
When it is slow I ran DBCC SHOWCONTIG and there were signs of
fragmentation which didn't look very serious. The BOL says it is not
reliable for smaller tables.
I run DBCC INDEXDEFRAG on a particular database. The results suggest
that there were 72 pages and 72 pages were moved and 0 deleted. Still
no improvement in performance.
I run DBCC DBREINDEX and viola query runs fast... I am happy but what
is happening here?
All help is welcome and appreciated...
ThanksDid you do a lot of updates/inserts/deletes and you didn't update statistics?
http://sqlservercode.blogspot.com/
"MasterNone" wrote:
> All queries for a particular table seems to be slow. It has one
> clustered index on the primary key column which of data type INT and
> has identity insert ON. This table has < 10000 rows and is fast with
> response in all other circumstances. The clustered index is at a fill
> factor of 90% and I have toyed upto 70% fillfactor.
> When it is slow I ran DBCC SHOWCONTIG and there were signs of
> fragmentation which didn't look very serious. The BOL says it is not
> reliable for smaller tables.
> I run DBCC INDEXDEFRAG on a particular database. The results suggest
> that there were 72 pages and 72 pages were moved and 0 deleted. Still
> no improvement in performance.
> I run DBCC DBREINDEX and viola query runs fast... I am happy but what
> is happening here?
>
> All help is welcome and appreciated...
> Thanks
>|||I had been monitoring the inserts they are of the order of 10-11 for a
table of 7500 rows. There were the same number of updates but not to
the primary key/indexed column. Currently the Autoupdate Statistics
option is turned on.|||MasterNone wrote:
> I had been monitoring the inserts they are of the order of 10-11 for a
> table of 7500 rows. There were the same number of updates but not to
> the primary key/indexed column. Currently the Autoupdate Statistics
> option is turned on.
Please post table DDL and your slow queries. You should also look at the
query plan with QA. A common cause for the phenomenon you seem to observe
is that the index is not used at all.
Regards
robert

Monday, March 19, 2012

Index on Binary Checksum column

I've created a column with binary_checksum, then create an index on that particular column.

When I'm using DTS to transfer some data into that table, I've got this error message:

INSERT failed because the following SET options have incorrect settings: 'ARITHABORT'

any leads?

ThanksI've done a normal insert via query analyser, it works fine.

Odd, after I removed the index, iDTS insert works fine again. :|
Is this a DTS bug? Or are there any settings in DTS I have to configure?

Friday, March 9, 2012

Index Fragmentation and Datatype Issue

I've got an issue where by certain types of records in a particular table are
becoming fragmented. The table is made up of an ID Column (identity), a
Reference Column[FK](int) , a Date Column (timedate) and an Account ID Column
[FK](varchar) and some other ones.
The none-clustered index comprises these four columns in ascending order.
For the most part this is fine, records are appended to the table and
fragmentation doesn't occur. However the indexes have started to become
fragmented for certain types of records.
The records which seem to be causing the fragmentation use predominantly
numeric account codes. Has anyone experienced a problem similar to this where
by the data type has caused index fragmentation?
Many thanks,
=============
VB .NET Developer
http://www.rocketscience.uk.com
Why would you have an index like that? Since the identity column is unique
the rest of the index is pretty much useless. And if this is a
Non-clustered index it has no bearing on the amount of fragmentation in the
table itself or the placement of new rows into the table. That is controlled
by the clustered index or if it is a Heap you have no control over where the
rows get placed. You might want to have a look at these:
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
Index Defrag Best Practices 2000
http://www.sql-server-performance.co...showcontig.asp
Understanding DBCC SHOWCONTIG
http://www.sqlservercentral.com/colu...illfactors.asp
Fill Factors
http://www.sql-server-performance.co...ed_indexes.asp
Clustered Indexes
Andrew J. Kelly SQL MVP
"JumpingMattFlash" <JumpingMattFlash@.discussions.microsoft.com> wrote in
message news:9A4EFB0E-0B3F-4024-AC6A-ECA0FCE52D4A@.microsoft.com...
> I've got an issue where by certain types of records in a particular table
> are
> becoming fragmented. The table is made up of an ID Column (identity), a
> Reference Column[FK](int) , a Date Column (timedate) and an Account ID
> Column
> [FK](varchar) and some other ones.
> The none-clustered index comprises these four columns in ascending order.
> For the most part this is fine, records are appended to the table and
> fragmentation doesn't occur. However the indexes have started to become
> fragmented for certain types of records.
> The records which seem to be causing the fragmentation use predominantly
> numeric account codes. Has anyone experienced a problem similar to this
> where
> by the data type has caused index fragmentation?
> Many thanks,
> --
> =============
> VB .NET Developer
> http://www.rocketscience.uk.com
|||JumpingMattFlash a écrit :
> I've got an issue where by certain types of records in a particular table are
> becoming fragmented. The table is made up of an ID Column (identity), a
> Reference Column[FK](int) , a Date Column (timedate) and an Account ID Column
> [FK](varchar) and some other ones.
> The none-clustered index comprises these four columns in ascending order.
> For the most part this is fine, records are appended to the table and
> fragmentation doesn't occur. However the indexes have started to become
> fragmented for certain types of records.
> The records which seem to be causing the fragmentation use predominantly
> numeric account codes. Has anyone experienced a problem similar to this where
> by the data type has caused index fragmentation?
> Many thanks,
In fact in your case frag is probably due to UPDATE on Account ID.
When choosing VARCHAR the storage does store the data exactly at the
length of the data. Wich mean if you have choose VARCHAR(32) and
inserting a 8 char value, only 8 char will be use in the complete row.
After if you update this data to enlarge it, for instance by a data wich
is 12 char length, it is impossible to store the value of this column in
the original row. So a new storage emplacement is choose but the
complete row stay at the old place. Only the new value is store outside
and pointer are placed form the original row to say where this data has
been moved !
This is fragmentation.
So, in indexes, VARCHAR is not the good choice when updates can occur...
A +
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************

Index Fragmentation and Datatype Issue

I've got an issue where by certain types of records in a particular table ar
e
becoming fragmented. The table is made up of an ID Column (identity), a
Reference Column[FK](int) , a Date Column (timedate) and an Account ID C
olumn
[FK](varchar) and some other ones.
The none-clustered index comprises these four columns in ascending order.
For the most part this is fine, records are appended to the table and
fragmentation doesn't occur. However the indexes have started to become
fragmented for certain types of records.
The records which seem to be causing the fragmentation use predominantly
numeric account codes. Has anyone experienced a problem similar to this wher
e
by the data type has caused index fragmentation?
Many thanks,
--
=============
VB .NET Developer
http://www.rocketscience.uk.comWhy would you have an index like that? Since the identity column is unique
the rest of the index is pretty much useless. And if this is a
Non-clustered index it has no bearing on the amount of fragmentation in the
table itself or the placement of new rows into the table. That is controlled
by the clustered index or if it is a Heap you have no control over where the
rows get placed. You might want to have a look at these:
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
Index Defrag Best Practices 2000
http://www.sql-server-performance.c..._showcontig.asp
Understanding DBCC SHOWCONTIG
http://www.sqlservercentral.com/col...
illfactors.asp
Fill Factors
http://www.sql-server-performance.c...red_indexes.asp
Clustered Indexes
Andrew J. Kelly SQL MVP
"JumpingMattFlash" <JumpingMattFlash@.discussions.microsoft.com> wrote in
message news:9A4EFB0E-0B3F-4024-AC6A-ECA0FCE52D4A@.microsoft.com...
> I've got an issue where by certain types of records in a particular table
> are
> becoming fragmented. The table is made up of an ID Column (identity), a
> Reference Column[FK](int) , a Date Column (timedate) and an Account ID
> Column
> [FK](varchar) and some other ones.
> The none-clustered index comprises these four columns in ascending order.
> For the most part this is fine, records are appended to the table and
> fragmentation doesn't occur. However the indexes have started to become
> fragmented for certain types of records.
> The records which seem to be causing the fragmentation use predominantly
> numeric account codes. Has anyone experienced a problem similar to this
> where
> by the data type has caused index fragmentation?
> Many thanks,
> --
> =============
> VB .NET Developer
> http://www.rocketscience.uk.com|||JumpingMattFlash a écrit :
> I've got an issue where by certain types of records in a particular table
are
> becoming fragmented. The table is made up of an ID Column (identity), a
> Reference Column[FK](int) , a Date Column (timedate) and an Account ID
Column
> [FK](varchar) and some other ones.
> The none-clustered index comprises these four columns in ascending order.
> For the most part this is fine, records are appended to the table and
> fragmentation doesn't occur. However the indexes have started to become
> fragmented for certain types of records.
> The records which seem to be causing the fragmentation use predominantly
> numeric account codes. Has anyone experienced a problem similar to this wh
ere
> by the data type has caused index fragmentation?
> Many thanks,
In fact in your case frag is probably due to UPDATE on Account ID.
When choosing VARCHAR the storage does store the data exactly at the
length of the data. Wich mean if you have choose VARCHAR(32) and
inserting a 8 char value, only 8 char will be use in the complete row.
After if you update this data to enlarge it, for instance by a data wich
is 12 char length, it is impossible to store the value of this column in
the original row. So a new storage emplacement is choose but the
complete row stay at the old place. Only the new value is store outside
and pointer are placed form the original row to say where this data has
been moved !
This is fragmentation.
So, in indexes, VARCHAR is not the good choice when updates can occur...
A +
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************

Index Fragmentation and Datatype Issue

I've got an issue where by certain types of records in a particular table are
becoming fragmented. The table is made up of an ID Column (identity), a
Reference Column[FK](int) , a Date Column (timedate) and an Account ID Column
[FK](varchar) and some other ones.
The none-clustered index comprises these four columns in ascending order.
For the most part this is fine, records are appended to the table and
fragmentation doesn't occur. However the indexes have started to become
fragmented for certain types of records.
The records which seem to be causing the fragmentation use predominantly
numeric account codes. Has anyone experienced a problem similar to this where
by the data type has caused index fragmentation?
Many thanks,
--
============= VB .NET Developer
http://www.rocketscience.uk.comWhy would you have an index like that? Since the identity column is unique
the rest of the index is pretty much useless. And if this is a
Non-clustered index it has no bearing on the amount of fragmentation in the
table itself or the placement of new rows into the table. That is controlled
by the clustered index or if it is a Heap you have no control over where the
rows get placed. You might want to have a look at these:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
Index Defrag Best Practices 2000
http://www.sql-server-performance.com/dt_dbcc_showcontig.asp
Understanding DBCC SHOWCONTIG
http://www.sqlservercentral.com/columnists/jweisbecker/amethodologyfordeterminingfillfactors.asp
Fill Factors
http://www.sql-server-performance.com/gv_clustered_indexes.asp
Clustered Indexes
--
Andrew J. Kelly SQL MVP
"JumpingMattFlash" <JumpingMattFlash@.discussions.microsoft.com> wrote in
message news:9A4EFB0E-0B3F-4024-AC6A-ECA0FCE52D4A@.microsoft.com...
> I've got an issue where by certain types of records in a particular table
> are
> becoming fragmented. The table is made up of an ID Column (identity), a
> Reference Column[FK](int) , a Date Column (timedate) and an Account ID
> Column
> [FK](varchar) and some other ones.
> The none-clustered index comprises these four columns in ascending order.
> For the most part this is fine, records are appended to the table and
> fragmentation doesn't occur. However the indexes have started to become
> fragmented for certain types of records.
> The records which seem to be causing the fragmentation use predominantly
> numeric account codes. Has anyone experienced a problem similar to this
> where
> by the data type has caused index fragmentation?
> Many thanks,
> --
> =============> VB .NET Developer
> http://www.rocketscience.uk.com|||JumpingMattFlash a Ã©crit :
> I've got an issue where by certain types of records in a particular table are
> becoming fragmented. The table is made up of an ID Column (identity), a
> Reference Column[FK](int) , a Date Column (timedate) and an Account ID Column
> [FK](varchar) and some other ones.
> The none-clustered index comprises these four columns in ascending order.
> For the most part this is fine, records are appended to the table and
> fragmentation doesn't occur. However the indexes have started to become
> fragmented for certain types of records.
> The records which seem to be causing the fragmentation use predominantly
> numeric account codes. Has anyone experienced a problem similar to this where
> by the data type has caused index fragmentation?
> Many thanks,
In fact in your case frag is probably due to UPDATE on Account ID.
When choosing VARCHAR the storage does store the data exactly at the
length of the data. Wich mean if you have choose VARCHAR(32) and
inserting a 8 char value, only 8 char will be use in the complete row.
After if you update this data to enlarge it, for instance by a data wich
is 12 char length, it is impossible to store the value of this column in
the original row. So a new storage emplacement is choose but the
complete row stay at the old place. Only the new value is store outside
and pointer are placed form the original row to say where this data has
been moved !
This is fragmentation.
So, in indexes, VARCHAR is not the good choice when updates can occur...
A +
FrÃ©dÃ©ric BROUARD, MVP SQL Server, expert bases de donnÃ©es et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modÃ©lisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************

Wednesday, March 7, 2012

Index Exists?

I asked a question earlier about how to tell of a field exists. Now I'm
needing a query to tell if a particular index (index name) exists.I know I can SELECT INDEXPROPERTY.
Is this the recommended approach?
"Les Stockton" wrote:

> I asked a question earlier about how to tell of a field exists. Now I'm
> needing a query to tell if a particular index (index name) exists.
>|||Index names are not unique by themselves; the index name must be unique only
within the scope of the parent table or view.
IF EXISTS(
SELECT *
FROM sysindexes
WHERE
id = OBJECT_ID('dbo.MyTable') AND
name = 'IndexName'
)
PRINT 'exists'
ELSE
PRINT 'does not exist'
Note that indexes may also support primary key and unique constraints. You
might want to keep this in mind, depending on the reason you are checking
for existence.
Hope this helps.
Dan Guzman
SQL Server MVP
"Les Stockton" <LesStockton@.discussions.microsoft.com> wrote in message
news:104B48DE-467E-4235-B3DE-FA72D8893D4F@.microsoft.com...
>I asked a question earlier about how to tell of a field exists. Now I'm
> needing a query to tell if a particular index (index name) exists.
>|||This method can work as can the sysindexes method I suggested. In fact,
INDEXPROPERTY is probably a better method.
Hope this helps.
Dan Guzman
SQL Server MVP
"Les Stockton" <LesStockton@.discussions.microsoft.com> wrote in message
news:DCCEB372-A292-48BA-9432-7B499962B64C@.microsoft.com...
>I know I can SELECT INDEXPROPERTY.
> Is this the recommended approach?
> "Les Stockton" wrote:
>|||I tried the following from inside EnterpriseManager, but it doesn't return
anything.
SELECT INDEXPROPERTY(OBJECT_ID(TEST_MASTER.UserPreferences'),
'PK_UserPreferences', 'IndexID') AS IdxID
Before doing this, I did a right-click in the list of tables in the
database, and selected
"All Tasks" and then "Manage Indexes". I am able to list that the
UserPreferences table has an index called PK_UserPreferences, which
corresponds to the UserID field in the table.
Any ideas why this isn't working?
I go into the
"Dan Guzman" wrote:

> Index names are not unique by themselves; the index name must be unique on
ly
> within the scope of the parent table or view.
> IF EXISTS(
> SELECT *
> FROM sysindexes
> WHERE
> id = OBJECT_ID('dbo.MyTable') AND
> name = 'IndexName'
> )
> PRINT 'exists'
> ELSE
> PRINT 'does not exist'
> Note that indexes may also support primary key and unique constraints. Yo
u
> might want to keep this in mind, depending on the reason you are checking
> for existence.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Les Stockton" <LesStockton@.discussions.microsoft.com> wrote in message
> news:104B48DE-467E-4235-B3DE-FA72D8893D4F@.microsoft.com...
>
>|||You're missing a single quote (') after OBJECT_ID(
Also, make sure you are in the context of the database that contains the
UserPreferences table when you run the query. INDEXPROPERTY will return NULL
if the object id cannot be found in the current database. Use USE
<databasename> to set the context to the correct database.
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"Les Stockton" <LesStockton@.discussions.microsoft.com> wrote in message
news:72470E25-FF59-439D-BBFF-10CEA09BD301@.microsoft.com...
>I tried the following from inside EnterpriseManager, but it doesn't return
> anything.
> SELECT INDEXPROPERTY(OBJECT_ID(TEST_MASTER.UserPreferences'),
> 'PK_UserPreferences', 'IndexID') AS IdxID
> Before doing this, I did a right-click in the list of tables in the
> database, and selected
> "All Tasks" and then "Manage Indexes". I am able to list that the
> UserPreferences table has an index called PK_UserPreferences, which
> corresponds to the UserID field in the table.
> Any ideas why this isn't working?
> I go into the
> "Dan Guzman" wrote:
>|||Still doesn't work. Test_Master is the name of the database. I name it
there with the table, as well as I am in the context of the database when
running this query.
It still shows nothing returned.
"Gail Erickson [MS]" wrote:

> You're missing a single quote (') after OBJECT_ID(
> Also, make sure you are in the context of the database that contains the
> UserPreferences table when you run the query. INDEXPROPERTY will return NU
LL
> if the object id cannot be found in the current database. Use USE
> <databasename> to set the context to the correct database.
> --
> Gail Erickson [MS]
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
> "Les Stockton" <LesStockton@.discussions.microsoft.com> wrote in message
> news:72470E25-FF59-439D-BBFF-10CEA09BD301@.microsoft.com...
>
>|||> Still doesn't work. Test_Master is the name of the database.
If Test_Master is the database name, then the format you're using in the
OBJECT_ID clause ((TEST_MASTER.UserPreferences') is incorrect. What you have
indicates that TEST_MASTER is the object owner, not the database name. The
correct format must either be 'TEST_MASTER.OwnerName.UserPreferences' or
'TEST_MASTER..UserPreferences'. If dbo is the table owner, then use
'TEST_MASTER.dbo.UserPreferences'
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"Les Stockton" <LesStockton@.discussions.microsoft.com> wrote in message
news:08DEBC04-6886-4876-A681-C02557732ADD@.microsoft.com...
> Still doesn't work. Test_Master is the name of the database. I name it
> there with the table, as well as I am in the context of the database when
> running this query.
> It still shows nothing returned.
> "Gail Erickson [MS]" wrote:
>|||For a bit less fuss with index properties:
http://milambda.blogspot.com/2005/0...-with-kick.html
ML
http://milambda.blogspot.com/|||Hi Les
A two part name indicates the owner of an object, and then the object name.
So TEST_MASTER.UserPreferences would indicate an object called
.UserPreferences owned by a user called TEST_MASTER.
If you have no such user, you will get null.
As Gail said, you must be in the db to use indexproperty, so you can repalce
TEST_MASTER with the object owner, whether it is dbo or some other user.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Les Stockton" <LesStockton@.discussions.microsoft.com> wrote in message
news:08DEBC04-6886-4876-A681-C02557732ADD@.microsoft.com...
> Still doesn't work. Test_Master is the name of the database. I name it
> there with the table, as well as I am in the context of the database when
> running this query.
> It still shows nothing returned.
> "Gail Erickson [MS]" wrote:
>
>

Sunday, February 19, 2012

index and primary key

By defining a numeric field in table as primary key, will the table be indexed on that particular field?yes, a primary key always gets an index, that's how the database system determines if a value exists already or not (for uniqueness)|||am extending my qn a littl bit

suppose the table has the following structure

myTable
(
myPK bigint identity (Primary key)
myUniqNo bigint
myName varchar (50)
)

can i create an index on myUniqNo, if myUniqNo is unique..|||You can create an index on almost any column, whether it is unique or not. You can create a unique constraint or a unique index on a column if there are no duplicate values in the column.

I'd recommend using a constraint instead of an index unless there is some specific, compelling reason for using the index.

-PatP|||yes you can

but then, if myUniqNo is going to be unique, why do you want an IDENTITY column as the primary key?

and by the way, why bigint? are you planning on having over 2 billion rows?|||but then, if myUniqNo is going to be unique, why do you want an IDENTITY column as the primary key?A surrogate key for the existing surrogate key? That way they can allow updates to their existing column?

Ow, ooo, ow! Quit throwing things, that hurts!!!

-PatP|||Yes, but then they can change the "key" without having to cascade all of the updates...|||I'd recommend using a constraint instead of an index unless there is some specific, compelling reason for using the index.Can you tell us why you'd recommend that? Do you know that when you create a unique constraint you implicitly create a unique index?|||Yes I'm sure Pat knows...I think Pat is spouting party line...M$ reccomends that as well...

Never could figure out why...or maybe we did and I forgot...|||About the only thing that a unique constraint has going for it as opposed to a unique index is that you can have a foreign key dependent on a unique constraint. After that, it gets a bit fuzzy. Does anyone know of any articles where the order of checks is done for an insert in SQL Server? For example, are check constraints checked before foreign keys are? Or do some triggers fire before computed columns are generated? That sort of information might give some insight.|||When UNIQUE constraint gets created, a UNIQUE index gets created at the same time with the same name. If you drop the constraint the index gets dropped with it, also implicitly. Trigger never gets to execute if uniqueness is violated either due to constraint or unique index.

Talking about differences, the only one I see is that while constraint is very strict in respect to controlling RI, unique index can be altered in such a way, where in a batch of 100 rows attempted to be inserted there is 1 duplicate row, 99 will be successfully inserted. Nothing can be done to accomplish the same with unique constraint. That's why MS (and Pat) recommend using constraints over indexes.|||Yes I'm sure Pat knows...I think Pat is spouting party line...M$ reccomends that as well...Not hardly... The only time I "spout party line" is when I'm actually at the party.

Creating a constraint creates metadata. Some programs use metadata now, and more will in the future. Metadata is an important stepping stone toward getting real "relational algebra" tools (especially things like OLAP), which will make life lots easier for everyone as they become more readily available.

-PatP|||dont forget the null.
you have to mention the 1 null...|||according to this thread (http://www.dbforums.com/t998479.html) there is a dodgy way around the "only 1 null in a unique index" problem, but i haven't confirmed that it works, i just bookmarked it|||Not hardly... The only time I "spout party line" is when I'm actually at the party.

Creating a constraint creates metadata. Some programs use metadata now, and more will in the future. Metadata is an important stepping stone toward getting real "relational algebra" tools (especially things like OLAP), which will make life lots easier for everyone as they become more readily available.

-PatP

Good Point...so where's the party?|||according to this thread (http://www.dbforums.com/t998479.html) there is a dodgy way around the "only 1 null in a unique index" problem, but i haven't confirmed that it works, i just bookmarked it

according to ruprect, its as simple as setting the column to not null. :D|||"...unique index can be altered in such a way, where in a batch of 100 rows attempted to be inserted there is 1 duplicate row, 99 will be successfully inserted. "

That is one I haven't seen before.

Got code?|||Never mind. Didn't read your post clearly.

Wednesday, March 28, 2012

Index size.

Index seek vs. index scan

Index seek vs. index scan

Index seek vs. index scan

Index related problems? Whats happening here?

Monday, March 19, 2012

Index on Binary Checksum column

Friday, March 9, 2012

Index Fragmentation and Datatype Issue

Index Fragmentation and Datatype Issue

Index Fragmentation and Datatype Issue

Wednesday, March 7, 2012

Index Exists?

Sunday, February 19, 2012

index and primary key

index and key

Blog Archive

About Me