index and key: statement

Showing posts with label statement. Show all posts

Friday, March 30, 2012

Index statement on SQL 2000 vs. SQL 2005

If I run the following statement on SQL 2000 Standard Edition, SP4, I get 2,
054 rows. I notice that the values returned in the IndexName column are not
all index names, but also include statistic names [_WA_Sys_...] and regular
column names. This is the statement:
SELECT
s.name AS TableName,
i.name AS IndexName
FROM sys.objects s
JOIN sys.indexes i
ON s.object_id = i.object_id
WHERE s.object_id > 99
If I take the same database used above, and restore it to SQL 2005, then run
the following statement on SQL 2005 Standard Edition, SP1, I get 647 rows. I
notice that the values returned in the IndexName column are all valid index
names, and DO NOT include statistic names and regular column names. This is
the statement:
SELECT
s.name AS TableName,
i.name AS IndexName
FROM sys.objects s
JOIN sys.indexes i
ON s.object_id = i.object_id
WHERE s.object_id > 99
In order to get the two to retrieve the same results, or stated another way,
in order to get the SQL 2000 version to return just index names (and not also
statistic names [_WA_Sys_...] and regular column names), what do I need to do?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200610/1
Found this on MSDN: "For Microsoft SQL Server 2000, use the predicate
sysindexes.status & 0x20=0 to identify real indexes."
http://msdn2.microsoft.com/en-us/library/ms190172.aspx
Roy Harvey
Beacon Falls, CT
On Mon, 23 Oct 2006 14:46:56 GMT, "cbrichards via droptable.com"
<u3288@.uwe> wrote:

>If I run the following statement on SQL 2000 Standard Edition, SP4, I get 2,
>054 rows. I notice that the values returned in the IndexName column are not
>all index names, but also include statistic names [_WA_Sys_...] and regular
>column names. This is the statement:
>SELECT
> s.name AS TableName,
> i.name AS IndexName
>FROM sys.objects s
>JOIN sys.indexes i
> ON s.object_id = i.object_id
> WHERE s.object_id > 99
>If I take the same database used above, and restore it to SQL 2005, then run
>the following statement on SQL 2005 Standard Edition, SP1, I get 647 rows. I
>notice that the values returned in the IndexName column are all valid index
>names, and DO NOT include statistic names and regular column names. This is
>the statement:
>SELECT
> s.name AS TableName,
> i.name AS IndexName
>FROM sys.objects s
>JOIN sys.indexes i
> ON s.object_id = i.object_id
>WHERE s.object_id > 99
>
>In order to get the two to retrieve the same results, or stated another way,
>in order to get the SQL 2000 version to return just index names (and not also
>statistic names [_WA_Sys_...] and regular column names), what do I need to do?
|||Thanks Ron, that did the trick.
However, how do I make sense of "(sysindexes.status & 0x20) = 0"? It appears
to be performing a bitwise logical AND operation. So what I am trying to make
sense, if I have a unique clustered index with a status of 18, then if I plug
that into a converter (18 & 0x20), it does not = 0, but 24. Can you
enlighten me how (18 & 0x20), for example = 0, or how "(sysindexes.status &
0x20) = 0"? Thanks.
Roy Harvey wrote:[vbcol=seagreen]
>Found this on MSDN: "For Microsoft SQL Server 2000, use the predicate
>sysindexes.status & 0x20=0 to identify real indexes."
>http://msdn2.microsoft.com/en-us/library/ms190172.aspx
>Roy Harvey
>Beacon Falls, CT
>[quoted text clipped - 26 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200610/1
|||You simply add that to the WHERE clause:
SELECT O.name AS TableName,
I.name AS IndexName
FROM sysobjects O
JOIN sysindexes I
ON O.id = I.id
WHERE I.id > 99
AND I.status & 0x20 = 0
Roy Harvey
Beacon Falls, CT
On Mon, 23 Oct 2006 16:32:18 GMT, "cbrichards via droptable.com"
<u3288@.uwe> wrote:
[vbcol=seagreen]
>Thanks Ron, that did the trick.
>However, how do I make sense of "(sysindexes.status & 0x20) = 0"? It appears
>to be performing a bitwise logical AND operation. So what I am trying to make
>sense, if I have a unique clustered index with a status of 18, then if I plug
>that into a converter (18 & 0x20), it does not = 0, but 24. Can you
>enlighten me how (18 & 0x20), for example = 0, or how "(sysindexes.status &
>0x20) = 0"? Thanks.
>Roy Harvey wrote:
|||What I am trying to say is this...
Let's say I run the following:
SELECT TOP 1 i.status
FROM sysobjects O
JOIN sysindexes I
ON O.id = I.id
WHERE I.id > 99
AND I.status & 0x20 = 0
I return the value: 18
My binary representation for 18 = 0011000100111000
My binary representation for 0x20 = 00110000011110000011001000110000
BOL says: "The & bitwise operator performs a bitwise logical AND between the
two expressions, taking each corresponding bit for both expressions. The bits
in the result are set to 1 if and only if both bits (for the current bit
being resolved) in the input expressions have a value of 1; otherwise, the
bit in the result is set to 0."
If I compare the binary representations, then I do not see how (in the query
statement above) "I.status & 0x20 = 0" or written with the returned value "18
& 0x20 = 0", when both bits = 1 on several instances.
I am not disputing the SQL statement works (returning only real indexes), as
it certainly does. What I am trying to understand is how the "Logical AND"
statement = 0.
Roy Harvey wrote:[vbcol=seagreen]
>You simply add that to the WHERE clause:
>SELECT O.name AS TableName,
> I.name AS IndexName
> FROM sysobjects O
> JOIN sysindexes I
> ON O.id = I.id
> WHERE I.id > 99
> AND I.status & 0x20 = 0
>Roy Harvey
>Beacon Falls, CT
>[quoted text clipped - 18 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200610/1
|||On Mon, 23 Oct 2006 17:24:10 GMT, "cbrichards via droptable.com"
<u3288@.uwe> wrote:

>My binary representation for 18 = 0011000100111000
0011000100111000 represents the ASCII CHARACTER STRING '18'. Binary
for the NUMBER 18 = 10010.

>My binary representation for 0x20 = 00110000011110000011001000110000
0x20 is hexidecimal 20, or decimal 32, or binary 100000; a single bit
is "on".
Roy Harvey
Beacon Falls, CT

Index statement on SQL 2000 vs. SQL 2005

If I run the following statement on SQL 2000 Standard Edition, SP4, I get 2,
054 rows. I notice that the values returned in the IndexName column are not
all index names, but also include statistic names [_WA_Sys_...] and regu
lar
column names. This is the statement:
SELECT
s.name AS TableName,
i.name AS IndexName
FROM sys.objects s
JOIN sys.indexes i
ON s.object_id = i.object_id
WHERE s.object_id > 99
If I take the same database used above, and restore it to SQL 2005, then run
the following statement on SQL 2005 Standard Edition, SP1, I get 647 rows. I
notice that the values returned in the IndexName column are all valid index
names, and DO NOT include statistic names and regular column names. This is
the statement:
SELECT
s.name AS TableName,
i.name AS IndexName
FROM sys.objects s
JOIN sys.indexes i
ON s.object_id = i.object_id
WHERE s.object_id > 99
In order to get the two to retrieve the same results, or stated another way,
in order to get the SQL 2000 version to return just index names (and not als
o
statistic names [_WA_Sys_...] and regular column names), what do I need
to do?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200610/1Found this on MSDN: "For Microsoft SQL Server 2000, use the predicate
sysindexes.status & 0x20=0 to identify real indexes."
http://msdn2.microsoft.com/en-us/library/ms190172.aspx
Roy Harvey
Beacon Falls, CT
On Mon, 23 Oct 2006 14:46:56 GMT, "cbrichards via droptable.com"
<u3288@.uwe> wrote:
[vbcol=seagreen]
>If I run the following statement on SQL 2000 Standard Edition, SP4, I get 2
,
>054 rows. I notice that the values returned in the IndexName column are not
>all index names, but also include statistic names [_WA_Sys_...] and reg
ular
>column names. This is the statement:
>SELECT
> s.name AS TableName,
> i.name AS IndexName
>FROM sys.objects s
>JOIN sys.indexes i
> ON s.object_id = i.object_id
> WHERE s.object_id > 99
>If I take the same database used above, and restore it to SQL 2005, then ru
n
>the following statement on SQL 2005 Standard Edition, SP1, I get 647 rows.
I
>notice that the values returned in the IndexName column are all valid inde
x
>names, and DO NOT include statistic names and regular column names. This is
>the statement:
>SELECT
> s.name AS TableName,
> i.name AS IndexName
>FROM sys.objects s
>JOIN sys.indexes i
> ON s.object_id = i.object_id
>WHERE s.object_id > 99
>
>In order to get the two to retrieve the same results, or stated another way
,
>in order to get the SQL 2000 version to return just index names (and not al
so
>statistic names [_WA_Sys_...] and regular column names), what do I need to do?[
/vbcol]|||Thanks Ron, that did the trick.
However, how do I make sense of "(sysindexes.status & 0x20) = 0"? It appear
s
to be performing a bitwise logical AND operation. So what I am trying to mak
e
sense, if I have a unique clustered index with a status of 18, then if I plu
g
that into a converter (18 & 0x20), it does not = 0, but 24. Can you
enlighten me how (18 & 0x20), for example = 0, or how "(sysindexes.status &
0x20) = 0"? Thanks.
Roy Harvey wrote:[vbcol=seagreen]
>Found this on MSDN: "For Microsoft SQL Server 2000, use the predicate
>sysindexes.status & 0x20=0 to identify real indexes."
>http://msdn2.microsoft.com/en-us/library/ms190172.aspx
>Roy Harvey
>Beacon Falls, CT
>
>[quoted text clipped - 26 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200610/1|||You simply add that to the WHERE clause:
SELECT O.name AS TableName,
I.name AS IndexName
FROM sysobjects O
JOIN sysindexes I
ON O.id = I.id
WHERE I.id > 99
AND I.status & 0x20 = 0
Roy Harvey
Beacon Falls, CT
On Mon, 23 Oct 2006 16:32:18 GMT, "cbrichards via droptable.com"
<u3288@.uwe> wrote:
[vbcol=seagreen]
>Thanks Ron, that did the trick.
>However, how do I make sense of "(sysindexes.status & 0x20) = 0"? It appea
rs
>to be performing a bitwise logical AND operation. So what I am trying to ma
ke
>sense, if I have a unique clustered index with a status of 18, then if I pl
ug
>that into a converter (18 & 0x20), it does not = 0, but 24. Can you
>enlighten me how (18 & 0x20), for example = 0, or how "(sysindexes.status &
>0x20) = 0"? Thanks.
>Roy Harvey wrote:|||What I am trying to say is this...
Let's say I run the following:
SELECT TOP 1 i.status
FROM sysobjects O
JOIN sysindexes I
ON O.id = I.id
WHERE I.id > 99
AND I.status & 0x20 = 0
I return the value: 18
My binary representation for 18 = 0011000100111000
My binary representation for 0x20 = 00110000011110000011001000110000
BOL says: "The & bitwise operator performs a bitwise logical AND between the
two expressions, taking each corresponding bit for both expressions. The bit
s
in the result are set to 1 if and only if both bits (for the current bit
being resolved) in the input expressions have a value of 1; otherwise, the
bit in the result is set to 0."
If I compare the binary representations, then I do not see how (in the query
statement above) "I.status & 0x20 = 0" or written with the returned value "1
8
& 0x20 = 0", when both bits = 1 on several instances.
I am not disputing the SQL statement works (returning only real indexes), as
it certainly does. What I am trying to understand is how the "Logical AND"
statement = 0.
Roy Harvey wrote:[vbcol=seagreen]
>You simply add that to the WHERE clause:
>SELECT O.name AS TableName,
> I.name AS IndexName
> FROM sysobjects O
> JOIN sysindexes I
> ON O.id = I.id
> WHERE I.id > 99
> AND I.status & 0x20 = 0
>Roy Harvey
>Beacon Falls, CT
>
>[quoted text clipped - 18 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200610/1|||On Mon, 23 Oct 2006 17:24:10 GMT, "cbrichards via droptable.com"
<u3288@.uwe> wrote:

>My binary representation for 18 = 0011000100111000
0011000100111000 represents the ASCII CHARACTER STRING '18'. Binary
for the NUMBER 18 = 10010.

>My binary representation for 0x20 = 00110000011110000011001000110000
0x20 is hexidecimal 20, or decimal 32, or binary 100000; a single bit
is "on".
Roy Harvey
Beacon Falls, CT

Index statement on SQL 2000 vs. SQL 2005

If I run the following statement on SQL 2000 Standard Edition, SP4, I get 2,
054 rows. I notice that the values returned in the IndexName column are not
all index names, but also include statistic names [_WA_Sys_...] and regular
column names. This is the statement:
SELECT
s.name AS TableName,
i.name AS IndexName
FROM sys.objects s
JOIN sys.indexes i
ON s.object_id = i.object_id
WHERE s.object_id > 99
If I take the same database used above, and restore it to SQL 2005, then run
the following statement on SQL 2005 Standard Edition, SP1, I get 647 rows. I
notice that the values returned in the IndexName column are all valid index
names, and DO NOT include statistic names and regular column names. This is
the statement:
SELECT
s.name AS TableName,
i.name AS IndexName
FROM sys.objects s
JOIN sys.indexes i
ON s.object_id = i.object_id
WHERE s.object_id > 99
In order to get the two to retrieve the same results, or stated another way,
in order to get the SQL 2000 version to return just index names (and not also
statistic names [_WA_Sys_...] and regular column names), what do I need to do?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200610/1Found this on MSDN: "For Microsoft SQL Server 2000, use the predicate
sysindexes.status & 0x20=0 to identify real indexes."
http://msdn2.microsoft.com/en-us/library/ms190172.aspx
Roy Harvey
Beacon Falls, CT
On Mon, 23 Oct 2006 14:46:56 GMT, "cbrichards via SQLMonster.com"
<u3288@.uwe> wrote:
>If I run the following statement on SQL 2000 Standard Edition, SP4, I get 2,
>054 rows. I notice that the values returned in the IndexName column are not
>all index names, but also include statistic names [_WA_Sys_...] and regular
>column names. This is the statement:
>SELECT
> s.name AS TableName,
> i.name AS IndexName
>FROM sys.objects s
>JOIN sys.indexes i
> ON s.object_id = i.object_id
> WHERE s.object_id > 99
>If I take the same database used above, and restore it to SQL 2005, then run
>the following statement on SQL 2005 Standard Edition, SP1, I get 647 rows. I
>notice that the values returned in the IndexName column are all valid index
>names, and DO NOT include statistic names and regular column names. This is
>the statement:
>SELECT
> s.name AS TableName,
> i.name AS IndexName
>FROM sys.objects s
>JOIN sys.indexes i
> ON s.object_id = i.object_id
>WHERE s.object_id > 99
>
>In order to get the two to retrieve the same results, or stated another way,
>in order to get the SQL 2000 version to return just index names (and not also
>statistic names [_WA_Sys_...] and regular column names), what do I need to do?|||Thanks Ron, that did the trick.
However, how do I make sense of "(sysindexes.status & 0x20) = 0"? It appears
to be performing a bitwise logical AND operation. So what I am trying to make
sense, if I have a unique clustered index with a status of 18, then if I plug
that into a converter (18 & 0x20), it does not = 0, but 24. Can you
enlighten me how (18 & 0x20), for example = 0, or how "(sysindexes.status &
0x20) = 0"? Thanks.
Roy Harvey wrote:
>Found this on MSDN: "For Microsoft SQL Server 2000, use the predicate
>sysindexes.status & 0x20=0 to identify real indexes."
>http://msdn2.microsoft.com/en-us/library/ms190172.aspx
>Roy Harvey
>Beacon Falls, CT
>>If I run the following statement on SQL 2000 Standard Edition, SP4, I get 2,
>>054 rows. I notice that the values returned in the IndexName column are not
>[quoted text clipped - 26 lines]
>>in order to get the SQL 2000 version to return just index names (and not also
>>statistic names [_WA_Sys_...] and regular column names), what do I need to do?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200610/1|||You simply add that to the WHERE clause:
SELECT O.name AS TableName,
I.name AS IndexName
FROM sysobjects O
JOIN sysindexes I
ON O.id = I.id
WHERE I.id > 99
AND I.status & 0x20 = 0
Roy Harvey
Beacon Falls, CT
On Mon, 23 Oct 2006 16:32:18 GMT, "cbrichards via SQLMonster.com"
<u3288@.uwe> wrote:
>Thanks Ron, that did the trick.
>However, how do I make sense of "(sysindexes.status & 0x20) = 0"? It appears
>to be performing a bitwise logical AND operation. So what I am trying to make
>sense, if I have a unique clustered index with a status of 18, then if I plug
>that into a converter (18 & 0x20), it does not = 0, but 24. Can you
>enlighten me how (18 & 0x20), for example = 0, or how "(sysindexes.status &
>0x20) = 0"? Thanks.
>Roy Harvey wrote:
>>Found this on MSDN: "For Microsoft SQL Server 2000, use the predicate
>>sysindexes.status & 0x20=0 to identify real indexes."
>>http://msdn2.microsoft.com/en-us/library/ms190172.aspx
>>Roy Harvey
>>Beacon Falls, CT
>>If I run the following statement on SQL 2000 Standard Edition, SP4, I get 2,
>>054 rows. I notice that the values returned in the IndexName column are not
>>[quoted text clipped - 26 lines]
>>in order to get the SQL 2000 version to return just index names (and not also
>>statistic names [_WA_Sys_...] and regular column names), what do I need to do?|||What I am trying to say is this...
Let's say I run the following:
SELECT TOP 1 i.status
FROM sysobjects O
JOIN sysindexes I
ON O.id = I.id
WHERE I.id > 99
AND I.status & 0x20 = 0
I return the value: 18
My binary representation for 18 = 0011000100111000
My binary representation for 0x20 = 00110000011110000011001000110000
BOL says: "The & bitwise operator performs a bitwise logical AND between the
two expressions, taking each corresponding bit for both expressions. The bits
in the result are set to 1 if and only if both bits (for the current bit
being resolved) in the input expressions have a value of 1; otherwise, the
bit in the result is set to 0."
If I compare the binary representations, then I do not see how (in the query
statement above) "I.status & 0x20 = 0" or written with the returned value "18
& 0x20 = 0", when both bits = 1 on several instances.
I am not disputing the SQL statement works (returning only real indexes), as
it certainly does. What I am trying to understand is how the "Logical AND"
statement = 0.
Roy Harvey wrote:
>You simply add that to the WHERE clause:
>SELECT O.name AS TableName,
> I.name AS IndexName
> FROM sysobjects O
> JOIN sysindexes I
> ON O.id = I.id
> WHERE I.id > 99
> AND I.status & 0x20 = 0
>Roy Harvey
>Beacon Falls, CT
>>Thanks Ron, that did the trick.
>[quoted text clipped - 18 lines]
>>in order to get the SQL 2000 version to return just index names (and not also
>>statistic names [_WA_Sys_...] and regular column names), what do I need to do?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200610/1|||On Mon, 23 Oct 2006 17:24:10 GMT, "cbrichards via SQLMonster.com"
<u3288@.uwe> wrote:
>My binary representation for 18 = 0011000100111000
0011000100111000 represents the ASCII CHARACTER STRING '18'. Binary
for the NUMBER 18 = 10010.
>My binary representation for 0x20 = 00110000011110000011001000110000
0x20 is hexidecimal 20, or decimal 32, or binary 100000; a single bit
is "on".
Roy Harvey
Beacon Falls, CT

Wednesday, March 28, 2012

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 Scanning

Please read following statements. statement 1 runs very faster and uses index scan. The statement 2 runs very slow uses table scan.

The Following things are already done.

DBCC DBREINDEX
Sp_updatestats
update statistics

It is only affecting one column (date_added) and also other statistic name start with _WA_SYS_* but date column starts as statistic_date_added. This has happened only for the last two days. All the production store procedure accessing date_columns now running longer.

Is there anybod can explain this and please post a solution. ?

Raj Sankar

Statement 1.
select * from rx_control where store_id = @.store_id and date_added between '08/01/02' and '08/20/02'
------------------------
statement 2.
------------
declare @.bdate as datetime
declare @.edate as datetime
declare @.rxid as int

set @.store_id = '52'
set @.rxid = '158315'
set @.bdate = '08/01/02'
set @.edate = '08/10/02'

select * from rx_control where store_id = @.store_id and date_added between @.bdate and @.edate
------------------------check with dbcc showcontig report and depending on this u should go for dbcc indexfrag on respective fields.

if still the problem persists send me the showcontig report for the respectve table if u can.|||I had problem like this or very similar.
Query optimizer creates an execution plan based on conditions. For the first statement optimizer knows date range exactly and creates the best execution plan.
The second statement is using local variables and optimizer creates common execution plan (without using particular conditions).
How to resolve this problem? In my situation I created additional table where parameters were saved and I was using join. It looks like a stupid decision but it works.
You can try to use this idea.

Good luck

Index rename

What's a SQL statement for renaming an existing index. I know how to add an
index at the time that the table is created, but how do you rename an
existing index.
E. coli Happens.
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200512/1Check out sp_rename in the Books Online.
Hope this helps.
Dan Guzman
SQL Server MVP
"HockeyFan via webservertalk.com" <u16651@.uwe> wrote in message
news:58d63becf3fe8@.uwe...
> What's a SQL statement for renaming an existing index. I know how to add
> an
> index at the time that the table is created, but how do you rename an
> existing index.
> --
> E. coli Happens.
> Message posted via webservertalk.com
> http://www.webservertalk.com/Uwe/Forum...amming/200512/1|||> What's a SQL statement for renaming an existing index.
Books Online is your friend.
EXEC sp_rename 'tablename.indexname', 'newname', 'index'|||too bad there's not a search.
Aaron Bertrand [SQL Server MVP] wrote:
>Books Online is your friend.
>EXEC sp_rename 'tablename.indexname', 'newname', 'index'
E. coli Happens.
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200512/1|||To make sure that I don't rename something else, I'm hoping to verify that
the "table.indexname" is also not the name of a column. I want to get the
correct item to rename. Maybe that can't be done with SP_Rename. since it
takes a string, not an object_ID.
HockeyFan wrote:
>too bad there's not a search.
>
>
E. coli Happens.
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200512/1|||> too bad there's not a search.
But there is. Also, I found the topic very quickly in the index by typing
'renaming indexes'.
Hope this helps.
Dan Guzman
SQL Server MVP
"HockeyFan via webservertalk.com" <u16651@.uwe> wrote in message
news:58d681f6f81ec@.uwe...
> too bad there's not a search.
> Aaron Bertrand [SQL Server MVP] wrote:
> --
> E. coli Happens.
> Message posted via webservertalk.com
> http://www.webservertalk.com/Uwe/Forum...amming/200512/1|||> To make sure that I don't rename something else, I'm hoping to verify that
> the "table.indexname" is also not the name of a column.
That's what the third parameter is for. Notice I say 'index' and not
'column'...|||> too bad there's not a search.
? There sure is.|||There's one that you can limit the search just to a particular book?
Aaron Bertrand [SQL Server MVP] wrote:
>? There sure is.
E. coli Happens.
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200512/1|||> There's one that you can limit the search just to a particular book?
Well, I searched for "rename index SQL Server" at msdn.microsoft.com and
sp_rename came up on the first page (and it was the first one that said
anything about Transact-SQL).
If you want an easier way to limit you search to SQL Server only, then
install Books Online on your own computer (if it isn't there already).
See http://www.aspfaq.com/2229 for information about where to find Books
Online on your own computer, and where to get it if you don't already have
it installed. The download for SQL Server 2000 is currently broken, so you
may want to download the SQL Server 2005 edition. However, note that some
content is not applicable to SQL Server 2000 (though the basic gist of the
sp_rename topic should get you going), and you will need to have the .Net
2.0 Framework installed.
A

Monday, March 26, 2012

index rebuilds

I thought there was a DBCC command that would rebuild all the indexes in a
database with one statement. Can anyone help please.> I thought there was a DBCC command that would rebuild all the indexes in a
> database with one statement. Can anyone help please.
DBCC DBREINDEX can rebuild all indexes for a single table. You can use a
cusros to loop trough all tables and dynamically prepare and execute the
DBCC command for each table. There is also a shortcut:
http://www.mssqlcity.com/FAQ/Devel/sp_msforeachtable.htm.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com|||Before you spend the time doing this, think about why you're doing it. You
should read the whitepaper
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
which explain when and how to reduced fragmentation in indexes.
Thanks
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
message news:#J8ghKrtEHA.1008@.tk2msftngp13.phx.gbl...
> > I thought there was a DBCC command that would rebuild all the indexes in
a
> > database with one statement. Can anyone help please.
> DBCC DBREINDEX can rebuild all indexes for a single table. You can use a
> cusros to loop trough all tables and dynamically prepare and execute the
> DBCC command for each table. There is also a shortcut:
> http://www.mssqlcity.com/FAQ/Devel/sp_msforeachtable.htm.
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> www.SolidQualityLearning.com
>

Friday, March 30, 2012

Index statement on SQL 2000 vs. SQL 2005

Index statement on SQL 2000 vs. SQL 2005

Index statement on SQL 2000 vs. SQL 2005

Wednesday, March 28, 2012

Index seek vs. index scan

Index seek vs. index scan

Index seek vs. index scan

Index Scanning

Index rename

Monday, March 26, 2012

index rebuilds

index and key

Blog Archive

About Me