Index size

Hi!
If I have an index on a nvarchar-column and change this column to varchar,
will the index size decrease? and the db-size too?
Thanks
//MalinNumber of data pages will probably decrease. So will number of pages used by
the index. This will
mean that number of pages used in the database will decrease.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Malin Davidsson" <malin.davidsson(at)aus.teleca.se> wrote in message
news:ejc4dEZ0GHA.3656@.TK2MSFTNGP04.phx.gbl...
> Hi!
> If I have an index on a nvarchar-column and change this column to varchar,
will the index size
> decrease? and the db-size too?
> Thanks
> //Malin
>|||Hi,
Add on to Tibor; I just verfied te scenario with an example. As Tibor
mentioned while converting from NVarchar to Varchar
data type the Index size decreased.
Thanks
Hari
SQL Server MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eTN$BLa0GHA.3464@.TK2MSFTNGP03.phx.gbl...
> Number of data pages will probably decrease. So will number of pages used
> by the index. This will mean that number of pages used in the database
> will decrease.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Malin Davidsson" <malin.davidsson(at)aus.teleca.se> wrote in message
> news:ejc4dEZ0GHA.3656@.TK2MSFTNGP04.phx.gbl...
>

Index size

Hi!
If I have an index on a nvarchar-column and change this column to varchar,
will the index size decrease? and the db-size too?
Thanks
//MalinNumber of data pages will probably decrease. So will number of pages used by the index. This will
mean that number of pages used in the database will decrease.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Malin Davidsson" <malin.davidsson(at)aus.teleca.se> wrote in message
news:ejc4dEZ0GHA.3656@.TK2MSFTNGP04.phx.gbl...
> Hi!
> If I have an index on a nvarchar-column and change this column to varchar, will the index size
> decrease? and the db-size too?
> Thanks
> //Malin
>|||Hi,
Add on to Tibor; I just verfied te scenario with an example. As Tibor
mentioned while converting from NVarchar to Varchar
data type the Index size decreased.
Thanks
Hari
SQL Server MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eTN$BLa0GHA.3464@.TK2MSFTNGP03.phx.gbl...
> Number of data pages will probably decrease. So will number of pages used
> by the index. This will mean that number of pages used in the database
> will decrease.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Malin Davidsson" <malin.davidsson(at)aus.teleca.se> wrote in message
> news:ejc4dEZ0GHA.3656@.TK2MSFTNGP04.phx.gbl...
>> Hi!
>> If I have an index on a nvarchar-column and change this column to
>> varchar, will the index size decrease? and the db-size too?
>> Thanks
>> //Malin
>sql

Index Seek (or) Index Scan in Execution Plan

Hi all,
I have one table. Where :
DonorID Int (Identity) Primary Key
FirstName Varchar(25)
LastName Varchar(25)
...
...
I have One nonclustred index on Lastname another nonclustred index on
(lastname, firstname).
Suppose I Execute the Query:
select * from TABLE where lastname like 'abott%' (This Query uses
Index Seek on the Compound Index)
But if I use the below Query:
select * from TABLE where lastname like 'smith%' (This Query uses
Index Scan)
But
select * from TABLE (index = ind_CMP_name) where lastname like 'smith%'
(But this Query uses the Index Seek)
NOTE: ind_CMP_name is the Compound Index.
Why there is the Difference, One Query uses Index Seek while other uses
Index Scan, even if both the query uses the same where condition on same
column?
Thanks
Prabhat
Hi all,
In Adition to Above Post / Question I have 2 More Questions:
1) Is the Index Seek is Faster or Index Scan? and Why?
2) How Can I Replace the Index = IndexName in the Above Post? (I Think the
Index = is used only for backward compatibility in SQL Server 2000)
Thanks in Advance for any Suggestion and help for these 2 posts...
Thanks
Prabhat
"Prabhat" <not_a_mail@.hotmail.com> wrote in message
news:#J1NJu$vEHA.3096@.TK2MSFTNGP14.phx.gbl...
> Hi all,
> I have one table. Where :
> DonorID Int (Identity) Primary Key
> FirstName Varchar(25)
> LastName Varchar(25)
> ...
> ...
> I have One nonclustred index on Lastname another nonclustred index on
> (lastname, firstname).
> Suppose I Execute the Query:
> select * from TABLE where lastname like 'abott%' (This Query
uses
> Index Seek on the Compound Index)
> But if I use the below Query:
> select * from TABLE where lastname like 'smith%' (This Query uses
> Index Scan)
> But
> select * from TABLE (index = ind_CMP_name) where lastname like 'smith%'
> (But this Query uses the Index Seek)
> NOTE: ind_CMP_name is the Compound Index.
> Why there is the Difference, One Query uses Index Seek while other uses
> Index Scan, even if both the query uses the same where condition on same
> column?
> Thanks
> Prabhat
>
|||The 2 queries in your list are not the same. They are searching for
different rows, and a different number of rows will be returned for each.
This is called selectivity - If a very small percentage of rows in the table
will be returned ( 3-5%) then the query is very selective. Index Seeks are
better for very selective queries and index scans or better for queries with
low selectivity. SQL Server's optimizer is smart enough to figure this out
and (generally) choose a good plan..
see inline
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Prabhat" <not_a_mail@.hotmail.com> wrote in message
news:%23ByYLXAwEHA.3908@.TK2MSFTNGP12.phx.gbl...
> Hi all,
> In Adition to Above Post / Question I have 2 More Questions:
> 1) Is the Index Seek is Faster or Index Scan? and Why?
Index seek does a binary search from the root to the leaf level, a Scan
reads through part of all of the leaf level... So scans generally do more
IO than seeks.
> 2) How Can I Replace the Index = IndexName in the Above Post? (I Think the
> Index = is used only for backward compatibility in SQL Server 2000)
>
It is preferable to not use index hints, but if performance is killing
you...( update statistics first, then see if you get better response).
select yad yad from table WITH (index = whatever)
Be sure to use the with clause for compatilibility with SQL 2005
> Thanks in Advance for any Suggestion and help for these 2 posts...
> Thanks
> Prabhat
>
> "Prabhat" <not_a_mail@.hotmail.com> wrote in message
> news:#J1NJu$vEHA.3096@.TK2MSFTNGP14.phx.gbl...
> uses
>
|||Hi Wayne,
Thanks for your Suggestions.
Reg the 2 Queries:
Yes They are searching for different Rows. And the 1st Query is Retrieving 2
Rows while the 2nd Query returns 5622 Rows.
So As you told SQL Server optimizer will Use Index Seek for 1st Query and
Index Scan for 2nd Query?
Then If I use "Index=" Keyword in the Second Query then that the 2nd Query
uses the Index Seek. Why is like that?
And Now If I write :
select * from TABLE with(index = ind_CMP_name) where lastname like 'smith%'
So This is Better then using Only "Index=" as this is Also Supported in
2005?
Thanks
Prabhat
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:e6A#GRBwEHA.1400@.TK2MSFTNGP11.phx.gbl...
> The 2 queries in your list are not the same. They are searching for
> different rows, and a different number of rows will be returned for each.
> This is called selectivity - If a very small percentage of rows in the
table
> will be returned ( 3-5%) then the query is very selective. Index Seeks are
> better for very selective queries and index scans or better for queries
with[vbcol=seagreen]
> low selectivity. SQL Server's optimizer is smart enough to figure this out
> and (generally) choose a good plan..
> see inline
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Prabhat" <not_a_mail@.hotmail.com> wrote in message
> news:%23ByYLXAwEHA.3908@.TK2MSFTNGP12.phx.gbl...
> Index seek does a binary search from the root to the leaf level, a Scan
> reads through part of all of the leaf level... So scans generally do more
> IO than seeks.
the[vbcol=seagreen]
> It is preferable to not use index hints, but if performance is killing
> you...( update statistics first, then see if you get better response).
> select yad yad from table WITH (index = whatever)
> Be sure to use the with clause for compatilibility with SQL 2005
Query[vbcol=seagreen]
uses[vbcol=seagreen]
'smith%'[vbcol=seagreen]
uses[vbcol=seagreen]
same
>
|||Prabhat
If you use the (INDEX = ..) hint you are FORCING SQL Server to use the index
you tell it to use, whether or not that is a good choice. If you measure the
peformance (perhaps SET STATISTICS IO ON) you will see that when you force
the index, the performance is worse than when you let SQL Server make its
own choice, and it chooses to do the scan.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Prabhat" <not_a_mail@.hotmail.com> wrote in message
news:uuZ%23H4BwEHA.2944@.TK2MSFTNGP12.phx.gbl...
> Hi Wayne,
> Thanks for your Suggestions.
> Reg the 2 Queries:
> Yes They are searching for different Rows. And the 1st Query is Retrieving
> 2
> Rows while the 2nd Query returns 5622 Rows.
> So As you told SQL Server optimizer will Use Index Seek for 1st Query and
> Index Scan for 2nd Query?
> Then If I use "Index=" Keyword in the Second Query then that the 2nd Query
> uses the Index Seek. Why is like that?
> And Now If I write :
> select * from TABLE with(index = ind_CMP_name) where lastname like
> 'smith%'
> So This is Better then using Only "Index=" as this is Also Supported in
> 2005?
> Thanks
> Prabhat
> "Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
> news:e6A#GRBwEHA.1400@.TK2MSFTNGP11.phx.gbl...
> table
> with
> the
> Query
> uses
> 'smith%'
> uses
> same
>
|||Hi Kalen,
Thanks for reply.
I use the "Index =" mainly for 2 reasons.
1) My SQL Query uses 2 Conditions in where clause. And I can see that there
is a Index Scan Involve in that Query. So I prefer "Index =" which make
Index Seek.
2) In Some cases My output should be Order by Lastname, FirstName. And Also
the query will have the Where Clause as above. So Here also i can see some
time it uses Index Scan. And I use a Compound Index on Lastname, Firstname -
To get the order. So I use the Index= in this case also.
You can see the Example of Query in the Main (TOP / original Post).
Kindly suggest.
Thanks
Prabhat
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:#h9EUmCwEHA.1264@.TK2MSFTNGP12.phx.gbl...
> Prabhat
> If you use the (INDEX = ..) hint you are FORCING SQL Server to use the
index
> you tell it to use, whether or not that is a good choice. If you measure
the[vbcol=seagreen]
> peformance (perhaps SET STATISTICS IO ON) you will see that when you force
> the index, the performance is worse than when you let SQL Server make its
> own choice, and it chooses to do the scan.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Prabhat" <not_a_mail@.hotmail.com> wrote in message
> news:uuZ%23H4BwEHA.2944@.TK2MSFTNGP12.phx.gbl...
Retrieving[vbcol=seagreen]
and[vbcol=seagreen]
Query[vbcol=seagreen]
each.[vbcol=seagreen]
Think[vbcol=seagreen]
on
>
|||Prabhat wrote:
> Hi Kalen,
> Thanks for reply.
> I use the "Index =" mainly for 2 reasons.
> 1) My SQL Query uses 2 Conditions in where clause. And I can see that
> there is a Index Scan Involve in that Query. So I prefer "Index ="
> which make Index Seek.
> 2) In Some cases My output should be Order by Lastname, FirstName.
> And Also the query will have the Where Clause as above. So Here also
> i can see some time it uses Index Scan. And I use a Compound Index on
> Lastname, Firstname - To get the order. So I use the Index= in this
> case also.
> You can see the Example of Query in the Main (TOP / original Post).
> Kindly suggest.
> Thanks
> Prabhat
>
Yes, you are correct that using the hint forces SQL Server to use the
index. But what Kalen is trying to explain to you is that using a
table/clustered index scan operation on the table when many rows are
returned is usually more cost effective for SQL Server. Unless you
dealing with a covering index, SQL Server has to perform a bookmark
lookup for each matching row. And all these bookmark lookups are very
costly when you consider SQL Server has to perform 5,000+ of them. In
that case, SQL Server chose to use a scan operation instead because it
is easier and faster for it to scan the table.
Now SQL Server does not always make the right decision. That's why
having updated statistics in your tables is important. But to force SQL
Server to always use the index misses the point. You are trying to
outthink the SQL Server query optimizer and that's a tough battle to win
in the long run.
David Gugick
Imceda Software
www.imceda.com
|||Thanks David for your Suggestion. Can U please tell me what exactly a
Covering Index? And Does that Help in my case?
Thanks
Prabhat
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:O3nP1vLwEHA.2944@.TK2MSFTNGP12.phx.gbl...
> Prabhat wrote:
> Yes, you are correct that using the hint forces SQL Server to use the
> index. But what Kalen is trying to explain to you is that using a
> table/clustered index scan operation on the table when many rows are
> returned is usually more cost effective for SQL Server. Unless you
> dealing with a covering index, SQL Server has to perform a bookmark
> lookup for each matching row. And all these bookmark lookups are very
> costly when you consider SQL Server has to perform 5,000+ of them. In
> that case, SQL Server chose to use a scan operation instead because it
> is easier and faster for it to scan the table.
> Now SQL Server does not always make the right decision. That's why
> having updated statistics in your tables is important. But to force SQL
> Server to always use the index misses the point. You are trying to
> outthink the SQL Server query optimizer and that's a tough battle to win
> in the long run.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>
|||Hi All,
Index Seek or Bookmark Lookup - Cost?
=============================
I have the below 2 Queries:
(1)
select top 100 donorid, firstname, lastname, state, zip, phonenum, country,
olddonorid, sourceid
from donor (index = ind_donor_name)
where lastname >= 'nath' and sourceid = 'flcc'
(2)
select top 100 donorid, firstname, lastname, state, zip, phonenum, country,
olddonorid, sourceid
from donor (index = ind_donor_name)
where lastname like 'nath%' and sourceid = 'flcc'
NOTE: ind_donor_name is the Compound Index on LastName, FirstName.
Even if Both the Queries are not Same in Where Condition, But Still refers
to the same Index. But I see a Different is Cost in Execution Plan.
that is:
the 1st Query Cost 1% in Index Seek and 99% in Bookmark Lookup.
But the 2nd Query Cost 51% in Index Seek and 49% in Bookmark Lookup.
[Note: Please refer the Discussions in this thread for more details...]
So As per the Above Cost Criteria which Plan is Best?
Thanks
Prabhat
"Prabhat" <not_a_mail@.hotmail.com> wrote in message
news:#J1NJu$vEHA.3096@.TK2MSFTNGP14.phx.gbl...
> Hi all,
> I have one table. Where :
> DonorID Int (Identity) Primary Key
> FirstName Varchar(25)
> LastName Varchar(25)
> ...
> ...
> I have One nonclustred index on Lastname another nonclustred index on
> (lastname, firstname).
> Suppose I Execute the Query:
> select * from TABLE where lastname like 'abott%' (This Query
uses
> Index Seek on the Compound Index)
> But if I use the below Query:
> select * from TABLE where lastname like 'smith%' (This Query uses
> Index Scan)
> But
> select * from TABLE (index = ind_CMP_name) where lastname like 'smith%'
> (But this Query uses the Index Seek)
> NOTE: ind_CMP_name is the Compound Index.
> Why there is the Difference, One Query uses Index Seek while other uses
> Index Scan, even if both the query uses the same where condition on same
> column?
> Thanks
> Prabhat
>
|||Prabhat wrote:

>Hi Steve,
>My requrement is to search for "nath" (I know both the queries are
>different). In 1st case i am listing All > nath and in 2nd case like nath.
>That Does not matter.
>
But that's why the estimated execution costs are different. You haven't
shown the plans, but the plans may be identical, and just have different
costs because of the difference in the estimated number of rows
returned. If the queries return the same results, it's possible that
the actual running times are the same. Have you run the queries with
set statistics io on to see if there's a difference?
Sorry, but I still don't understand why if you want to search for
"nath", you are comparing plans that do something else.
SK

>Suppose I have 2 same queries with 2 diferent approach with that 2 Execution
>Plan, Then Which Plan I should go for?
>Some Additional Hint:
>1st Query Cost 98.35% relative to the batch
>while the 2nd Query Cost 1.65% relative to the Bacth.
>Thanks
>Prabhat
>
>"Steve Kass" <skass@.drew.edu> wrote in message
>news:eupyWfOwEHA.2624@.TK2MSFTNGP11.phx.gbl...
>
>country,
>
>country,
>
>refers
>
>
>

Index Seek (or) Index Scan in Execution Plan

Hi all,
I have one table. Where :
DonorID Int (Identity) Primary Key
FirstName Varchar(25)
LastName Varchar(25)
...
...
I have One nonclustred index on Lastname another nonclustred index on
(lastname, firstname).
Suppose I Execute the Query:
select * from TABLE where lastname like 'abott%' (This Query uses
Index Seek on the Compound Index)
But if I use the below Query:
select * from TABLE where lastname like 'smith%' (This Query uses
Index Scan)
But
select * from TABLE (index = ind_CMP_name) where lastname like 'smith%'
(But this Query uses the Index Seek)
NOTE: ind_CMP_name is the Compound Index.
Why there is the Difference, One Query uses Index Seek while other uses
Index Scan, even if both the query uses the same where condition on same
column?
Thanks
PrabhatHi all,
In Adition to Above Post / Question I have 2 More Questions:
1) Is the Index Seek is Faster or Index Scan? and Why?
2) How Can I Replace the Index = IndexName in the Above Post? (I Think the
Index = is used only for backward compatibility in SQL Server 2000)
Thanks in Advance for any Suggestion and help for these 2 posts...
Thanks
Prabhat
"Prabhat" <not_a_mail@.hotmail.com> wrote in message
news:#J1NJu$vEHA.3096@.TK2MSFTNGP14.phx.gbl...
> Hi all,
> I have one table. Where :
> DonorID Int (Identity) Primary Key
> FirstName Varchar(25)
> LastName Varchar(25)
> ...
> ...
> I have One nonclustred index on Lastname another nonclustred index on
> (lastname, firstname).
> Suppose I Execute the Query:
> select * from TABLE where lastname like 'abott%' (This Query
uses
> Index Seek on the Compound Index)
> But if I use the below Query:
> select * from TABLE where lastname like 'smith%' (This Query uses
> Index Scan)
> But
> select * from TABLE (index = ind_CMP_name) where lastname like 'smith%'
> (But this Query uses the Index Seek)
> NOTE: ind_CMP_name is the Compound Index.
> Why there is the Difference, One Query uses Index Seek while other uses
> Index Scan, even if both the query uses the same where condition on same
> column?
> Thanks
> Prabhat
>|||The 2 queries in your list are not the same. They are searching for
different rows, and a different number of rows will be returned for each.
This is called selectivity - If a very small percentage of rows in the table
will be returned ( 3-5%) then the query is very selective. Index Seeks are
better for very selective queries and index scans or better for queries with
low selectivity. SQL Server's optimizer is smart enough to figure this out
and (generally) choose a good plan..
see inline
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Prabhat" <not_a_mail@.hotmail.com> wrote in message
news:%23ByYLXAwEHA.3908@.TK2MSFTNGP12.phx.gbl...
> Hi all,
> In Adition to Above Post / Question I have 2 More Questions:
> 1) Is the Index Seek is Faster or Index Scan? and Why?
Index seek does a binary search from the root to the leaf level, a Scan
reads through part of all of the leaf level... So scans generally do more
IO than seeks.
> 2) How Can I Replace the Index = IndexName in the Above Post? (I Think the
> Index = is used only for backward compatibility in SQL Server 2000)
>
It is preferable to not use index hints, but if performance is killing
you...( update statistics first, then see if you get better response).
select yad yad from table WITH (index = whatever)
Be sure to use the with clause for compatilibility with SQL 2005
> Thanks in Advance for any Suggestion and help for these 2 posts...
> Thanks
> Prabhat
>
> "Prabhat" <not_a_mail@.hotmail.com> wrote in message
> news:#J1NJu$vEHA.3096@.TK2MSFTNGP14.phx.gbl...
> uses
>|||Hi Wayne,
Thanks for your Suggestions.
Reg the 2 Queries:
Yes They are searching for different Rows. And the 1st Query is Retrieving 2
Rows while the 2nd Query returns 5622 Rows.
So As you told SQL Server optimizer will Use Index Seek for 1st Query and
Index Scan for 2nd Query?
Then If I use "Index=" Keyword in the Second Query then that the 2nd Query
uses the Index Seek. Why is like that?
And Now If I write :
select * from TABLE with(index = ind_CMP_name) where lastname like 'smith%'
So This is Better then using Only "Index=" as this is Also Supported in
2005?
Thanks
Prabhat
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:e6A#GRBwEHA.1400@.TK2MSFTNGP11.phx.gbl...
> The 2 queries in your list are not the same. They are searching for
> different rows, and a different number of rows will be returned for each.
> This is called selectivity - If a very small percentage of rows in the
table
> will be returned ( 3-5%) then the query is very selective. Index Seeks are
> better for very selective queries and index scans or better for queries
with
> low selectivity. SQL Server's optimizer is smart enough to figure this out
> and (generally) choose a good plan..
> see inline
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Prabhat" <not_a_mail@.hotmail.com> wrote in message
> news:%23ByYLXAwEHA.3908@.TK2MSFTNGP12.phx.gbl...
> Index seek does a binary search from the root to the leaf level, a Scan
> reads through part of all of the leaf level... So scans generally do more
> IO than seeks.
the[vbcol=seagreen]
> It is preferable to not use index hints, but if performance is killing
> you...( update statistics first, then see if you get better response).
> select yad yad from table WITH (index = whatever)
> Be sure to use the with clause for compatilibility with SQL 2005
Query[vbcol=seagreen]
uses[vbcol=seagreen]
'smith%'[vbcol=seagreen]
uses[vbcol=seagreen]
same[vbcol=seagreen]
>|||Prabhat
If you use the (INDEX = ..) hint you are FORCING SQL Server to use the index
you tell it to use, whether or not that is a good choice. If you measure the
peformance (perhaps SET STATISTICS IO ON) you will see that when you force
the index, the performance is worse than when you let SQL Server make its
own choice, and it chooses to do the scan.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Prabhat" <not_a_mail@.hotmail.com> wrote in message
news:uuZ%23H4BwEHA.2944@.TK2MSFTNGP12.phx.gbl...
> Hi Wayne,
> Thanks for your Suggestions.
> Reg the 2 Queries:
> Yes They are searching for different Rows. And the 1st Query is Retrieving
> 2
> Rows while the 2nd Query returns 5622 Rows.
> So As you told SQL Server optimizer will Use Index Seek for 1st Query and
> Index Scan for 2nd Query?
> Then If I use "Index=" Keyword in the Second Query then that the 2nd Query
> uses the Index Seek. Why is like that?
> And Now If I write :
> select * from TABLE with(index = ind_CMP_name) where lastname like
> 'smith%'
> So This is Better then using Only "Index=" as this is Also Supported in
> 2005?
> Thanks
> Prabhat
> "Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
> news:e6A#GRBwEHA.1400@.TK2MSFTNGP11.phx.gbl...
> table
> with
> the
> Query
> uses
> 'smith%'
> uses
> same
>|||Hi Kalen,
Thanks for reply.
I use the "Index =" mainly for 2 reasons.
1) My SQL Query uses 2 Conditions in where clause. And I can see that there
is a Index Scan Involve in that Query. So I prefer "Index =" which make
Index Seek.
2) In Some cases My output should be Order by Lastname, FirstName. And Also
the query will have the Where Clause as above. So Here also i can see some
time it uses Index Scan. And I use a Compound Index on Lastname, Firstname -
To get the order. So I use the Index= in this case also.
You can see the Example of Query in the Main (TOP / original Post).
Kindly suggest.
Thanks
Prabhat
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:#h9EUmCwEHA.1264@.TK2MSFTNGP12.phx.gbl...
> Prabhat
> If you use the (INDEX = ..) hint you are FORCING SQL Server to use the
index
> you tell it to use, whether or not that is a good choice. If you measure
the
> peformance (perhaps SET STATISTICS IO ON) you will see that when you force
> the index, the performance is worse than when you let SQL Server make its
> own choice, and it chooses to do the scan.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Prabhat" <not_a_mail@.hotmail.com> wrote in message
> news:uuZ%23H4BwEHA.2944@.TK2MSFTNGP12.phx.gbl...
Retrieving[vbcol=seagreen]
and[vbcol=seagreen]
Query[vbcol=seagreen]
each.[vbcol=seagreen]
Think[vbcol=seagreen]
on[vbcol=seagreen]
>|||Prabhat wrote:
> Hi Kalen,
> Thanks for reply.
> I use the "Index =" mainly for 2 reasons.
> 1) My SQL Query uses 2 Conditions in where clause. And I can see that
> there is a Index Scan Involve in that Query. So I prefer "Index ="
> which make Index Seek.
> 2) In Some cases My output should be Order by Lastname, FirstName.
> And Also the query will have the Where Clause as above. So Here also
> i can see some time it uses Index Scan. And I use a Compound Index on
> Lastname, Firstname - To get the order. So I use the Index= in this
> case also.
> You can see the Example of Query in the Main (TOP / original Post).
> Kindly suggest.
> Thanks
> Prabhat
>
Yes, you are correct that using the hint forces SQL Server to use the
index. But what Kalen is trying to explain to you is that using a
table/clustered index scan operation on the table when many rows are
returned is usually more cost effective for SQL Server. Unless you
dealing with a covering index, SQL Server has to perform a bookmark
lookup for each matching row. And all these bookmark lookups are very
costly when you consider SQL Server has to perform 5,000+ of them. In
that case, SQL Server chose to use a scan operation instead because it
is easier and faster for it to scan the table.
Now SQL Server does not always make the right decision. That's why
having updated statistics in your tables is important. But to force SQL
Server to always use the index misses the point. You are trying to
outthink the SQL Server query optimizer and that's a tough battle to win
in the long run.
David Gugick
Imceda Software
www.imceda.com|||Thanks David for your Suggestion. Can U please tell me what exactly a
Covering Index? And Does that Help in my case?
Thanks
Prabhat
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:O3nP1vLwEHA.2944@.TK2MSFTNGP12.phx.gbl...
> Prabhat wrote:
> Yes, you are correct that using the hint forces SQL Server to use the
> index. But what Kalen is trying to explain to you is that using a
> table/clustered index scan operation on the table when many rows are
> returned is usually more cost effective for SQL Server. Unless you
> dealing with a covering index, SQL Server has to perform a bookmark
> lookup for each matching row. And all these bookmark lookups are very
> costly when you consider SQL Server has to perform 5,000+ of them. In
> that case, SQL Server chose to use a scan operation instead because it
> is easier and faster for it to scan the table.
> Now SQL Server does not always make the right decision. That's why
> having updated statistics in your tables is important. But to force SQL
> Server to always use the index misses the point. You are trying to
> outthink the SQL Server query optimizer and that's a tough battle to win
> in the long run.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||Hi All,
Index Seek or Bookmark Lookup - Cost?
=============================
I have the below 2 Queries:
(1)
select top 100 donorid, firstname, lastname, state, zip, phonenum, country,
olddonorid, sourceid
from donor (index = ind_donor_name)
where lastname >= 'nath' and sourceid = 'flcc'
(2)
select top 100 donorid, firstname, lastname, state, zip, phonenum, country,
olddonorid, sourceid
from donor (index = ind_donor_name)
where lastname like 'nath%' and sourceid = 'flcc'
NOTE: ind_donor_name is the Compound Index on LastName, FirstName.
Even if Both the Queries are not Same in Where Condition, But Still refers
to the same Index. But I see a Different is Cost in Execution Plan.
that is:
the 1st Query Cost 1% in Index Seek and 99% in Bookmark Lookup.
But the 2nd Query Cost 51% in Index Seek and 49% in Bookmark Lookup.
[Note: Please refer the Discussions in this thread for more details...]
So As per the Above Cost Criteria which Plan is Best?
Thanks
Prabhat
"Prabhat" <not_a_mail@.hotmail.com> wrote in message
news:#J1NJu$vEHA.3096@.TK2MSFTNGP14.phx.gbl...
> Hi all,
> I have one table. Where :
> DonorID Int (Identity) Primary Key
> FirstName Varchar(25)
> LastName Varchar(25)
> ...
> ...
> I have One nonclustred index on Lastname another nonclustred index on
> (lastname, firstname).
> Suppose I Execute the Query:
> select * from TABLE where lastname like 'abott%' (This Query
uses
> Index Seek on the Compound Index)
> But if I use the below Query:
> select * from TABLE where lastname like 'smith%' (This Query uses
> Index Scan)
> But
> select * from TABLE (index = ind_CMP_name) where lastname like 'smith%'
> (But this Query uses the Index Seek)
> NOTE: ind_CMP_name is the Compound Index.
> Why there is the Difference, One Query uses Index Seek while other uses
> Index Scan, even if both the query uses the same where condition on same
> column?
> Thanks
> Prabhat
>|||Prabhat,
You are comparing apples to oranges. The queries are different, so
what do you mean "which plan is best"?
If I said "I can either pay for a new bicycle with a check or pay for
a new television with cash. Which is better?" -- well, it depends on
whether you need a bicycle or a television.
SK
Prabhat wrote:

>Hi All,
>Index Seek or Bookmark Lookup - Cost?
>=============================
>I have the below 2 Queries:
>(1)
>select top 100 donorid, firstname, lastname, state, zip, phonenum, country
,
>olddonorid, sourceid
>from donor (index = ind_donor_name)
>where lastname >= 'nath' and sourceid = 'flcc'
>(2)
>select top 100 donorid, firstname, lastname, state, zip, phonenum, country
,
>olddonorid, sourceid
>from donor (index = ind_donor_name)
>where lastname like 'nath%' and sourceid = 'flcc'
>NOTE: ind_donor_name is the Compound Index on LastName, FirstName.
>Even if Both the Queries are not Same in Where Condition, But Still refers
>to the same Index. But I see a Different is Cost in Execution Plan.
>that is:
>the 1st Query Cost 1% in Index Seek and 99% in Bookmark Lookup.
>But the 2nd Query Cost 51% in Index Seek and 49% in Bookmark Lookup.
>[Note: Please refer the Discussions in this thread for more details...]
>So As per the Above Cost Criteria which Plan is Best?
>Thanks
>Prabhat
>"Prabhat" <not_a_mail@.hotmail.com> wrote in message
>news:#J1NJu$vEHA.3096@.TK2MSFTNGP14.phx.gbl...
>
>uses
>
>
>

index question?

I do have a dump table "table1"

Table1:

UniqueId varchar(4)

scandate datetime

courseno varchar(5).

This table is not related to any other tables in the database. This is used to track the student attendance for a particular course. There can 60000 distinct uniqueid's in the table and 300 disticnt courses. I query this table most of the time on uniqueid and coursecrn combination.

This table can grow up to 5 million records.

can anybody tell on what fields do I have to create indexes and also of what type(clustered or non-clustered). This table doesnt have a primary key.

Thanks

Sandeep:

It appears to me that at the very least you need an index based on (1) uniqueID, (2) courseNo and (3) scanDate. This index looks like it might be a candidate for a primary key; however, this also looks like a staging table. If that is the case it might be best to just index it, allow duplicates so that the data can be staged and deal with the duplicates later -- but without more information it's hard to say. I choose uniqueID first simply because it has the higher cardinality. Adding this index reduced logical IO from 11,000 to 3. Time from 313 ms to 0 ms. Test query for testing this index:

select * from table1 where courseNo = '2' and uniqueId = '1'

My guess is that you will also need an index based on (1) courseNo, (2) scanDate and (3) uniqueId. When I ran a query that did not include uniqueId as part of the filtering criteria this index brought a big performance boost because it avoided the table scan. Additing this index reduced logical IO from 11,000 to 9. Time went from 343 ms to 0 ms.

Test query for testing this index:

select * from table1 where courseNo = '2' and scanDate between '9/1/2006' and '9/30/2006'

You might also need an index based on scanDate. This depends on whether or not you have any queries that have scanDate as the filter criteria but do filter based on either courseNo or uniqueId. If I were creating this index I would also include the other two columns in the index to avoid potential bookmark lookups. I didn't create this hypothetical index and also did no associated tests.

Dave

|||

Dave:

I will tell you my database structure. This is simple. I have only three tables.

Student: Uniqueid int (PK), lname varchar(35), fname varchar(35)

courses: Courseno(PK), coursetime, courseday.

Table1(this is dump table): uniqueid, scandate, courseno.

This dump table is not related to student or courses table bc without a record in students table then student can attend a class.

If student attends a class for 45 days in a semester for a particular course then dump table will have 45 records. say if he swipes his id thrice (thinking something wrong ) then there will be three records in the dump table. This dump table is also the final table. If i want to see the attendance of students in a particular course i query this table. do you think I need a table out of this dump table or just adding the indexes if fine for this table.?

as far i understand from ur reply, you suggest to create non-clustered index on uniqueid, scandate, coursecrn?

|||

Sandeep:

This is not one of the indexes that I suggested. I suggested two indexes. I suggest the first index be based on (1) uniqueID, (2) courseNo and (3) scandate; I suggest the second index be based on (1) courseNo, (2) scandate and (3) uniqueID. My knee-jerk reaction is that the indexes should be sufficient for checking attendence of students in classes. It is not possible to give more than a knee-jerk response without knowing the full set of requirements and without knowing the entire usage spectrum of your application and these tables.

Dave

|||

do the order of the fields mentioned in index makes a difference?

|||

Yes, it makes a difference; moreover, there may be a good reason to want to change the order of the columns to meet particular needs of your application. Is there a particular reason that you are potentially interested in creating an index based on the order you stated -- uniqueid, scandate, coursecrn?

I chose a different order because of your first statement that " ... I query this table most of the time on uniqueid and coursecrn combination. ..." The order I chose provides the narrowest target for a query that does not include scandate as part of the filter criteria.

Dave

|||i just mentioned the order as an example...I will create two non-clustered indexes as you suggested. one more question: say, If the dump table has 10 million records and when we insert 300 more records to that ( as the table has indexes and it will re-organize the table when a new record is inserted) am i going to have a peformance issue? or not much|||

I don't think you will see much negative performance impact as long as you perform normal periodic maintenance. I was just working on something else and I realized something. It seems to me that if scanDate is a true datetime variable -- that is, the time portion of the data is generated and included -- that the logical key to your table is (1) uniqueID and (2) scanDate. The reason I believe this is that it is not physically possible for a student to be doing more than 1 badge scan at any one time. If that is true, then there is reason to set up the primary key based on these two columns.

Dave

|||

ya scandate is datetime datatype.

is this the final solution you suggest:

1. create primary key on uniqueid and scandate( is it clustered or non-clustered)

2. non-clustered on uniqueid, scandate, coursrno

3. non-clustered on courseno, scandate, uniqueid

can you please correct me if i am wrong?

|||

Sandeep:

What we need to do is run a query to verify whether or not the primary key can be applied. If it can, I dont think we will need index #2. Try running this query:

select uniqueID,
scanDate,
count(*)
from table1
group by uniqueID, scanDate
having count(*) > 1

if no rows are returned, then the primary key will work; if it will not, then we need to forget about the primary key.

Dave

|||

dave,

it doesnt return any rows so i think we can create primary key on uniqueid and scandate.

one more question: why dont we need this non-clustered index on courseno, scandate, uniqueid if we can create the primary key? sorry to ask you many questions, I am new to this indexing...

Thanks

|||

Sandeep:

I think that you DO still need index you referred to as number 3 -- the one based on (1) courseno, (2), scandate and (3) uniqueID. To some extent I am "guessing" at this because you sand that "most" of your queries were based on courseno and uniqueID. Again, when I tested a select that filtered records based on courseNo and a date range but did NOT filter based on uniqueID the select performed a table scan. What that means is that SQL Server examines each and every one of the 3 million records (my mock-up was 3 million instead of 10 million) to see if it selected by our filtering criteria. When we create the index, the number of records examined is reduced from 3 million to something like 400 -- and that is a huge reduction.

The real issue is whether or not we need the index you referred to as number 2. I have a good deal of doubt that it is worth the price of the trade-off. Also, if it is needed we can always defer that decision and add it in if it turns out to be especially beneficial. The issue with index #2 is that it is almost the same as the primary key. While there might be some queries that will benefit a little from index #2 it is likely that most of these queries will still run almost as fast off the primary key. Hence, the benefit of index number 2 might not be great.

The other factor for considering an index is the cost of the index. This table will have 10 million rows. There is an overhead cost of maintaing any index. In this case I doubt that the potential gain -- probably a minimal gain -- that will come with index #2 is not worth the cost of the index. That would NOT be the case if there was no primary key!

I will run a couple of quick tests tomorrow morning to put some numbers together to do an actual comparison between the primary key and index #2 so that you can make an appraisal.

Dave

|||

Sandeep:

I used my mockup table with the primary key composed of (1) uniqueID and (2) scandate and the index composed of (1) courseNo, (2) uniqueID and (3) scanDate and loaded the mockup table with 32767 different uniqueID and 300 different courseNo with about 90 different scan dates to create a mockup table with about 3 million rows. I used this mockup for the basis of my tests.

First I tested with this query:

select * from table1 where uniqueId = '1' and scanDate between '9/1/2006' and '9/30/2006'

This query took 0 ms with 3 logical reads; the query plan was based on a clustered index seek, therefore, filtering was efficient. 22 rows were returned.

I next tested this query:

select * from table1 where courseNo = '1' and scanDate between '9/1/2006' and '9/30/2006'

This query took 0 ms with 10 logical reads; the query plan was based on an index seek of the nonclustered index, therefore, filtering was efficient. 2290 rows were returned.

Finally, I tested this query:

select * from table1 where courseNo = '2' and uniqueId = '1'

This query took 0 ms with 3 logical reads; the query plan was based on a clustered index seek, therefore, filtering was efficient. 92 riws were returned.

After this, I added the index based on (1) uniqueId, (2) scanDate and (3) courseNo and reran all of the queries. The index plans of two of the queries was changed to use this index; however, there was no measurable difference. Execution time was still 0 ms and the required amount of logical reads did not change.

Dave

|||

Dave...Thanks a lot for your effort. what is the meaning of logical read?.

"the index composed of (1) courseNo, (2) uniqueID and (3) scanDate " is the order correct? because yday we discussed tht the index should be on courseno, scandate, uniqueid....

|||

Sandeep:

A "logical read" is one of the measurements of the work performed to satisfy a query. In this case the "3 logical reads" is three seeks of 8K data pages or 24K -- a very small amount of work. When we were performing either the table scan or the clustered index scan -- that is when we read without any indexes or a primary key -- we were reading 11,000 logical reads -- but my mockup was for 3 million rows of data instead of 10 million rows of data. 10 million rows of data would translate into about 38,000 logical reads -- about 308 Meg of data as opposed to 24K. This is a difference of a very large magnitude. This difference really becomes our primary motivation for indexing.

Related to the index needs:

The reason that the index needs to be based on (1) courseNo, (2) uniqueID and (3) scandate is to support queries that filter based on courseNo and uniqueID. If the order is changed to (1) courseNo, (2) scandate and (3) uniqueID there will be an increase in the number of logical reads required to fetch the data for this kind of search.

Dave

Index on result of function

I have a table with about 28 million records in it. Each row has an ID (PK), logged (datetime), IP varchar(15)

The data grows at about 14 million records per year. I'm going to be running queries on the table that extract the MONTH or YEAR from the logged column. In Foxpro tables I would have created indexes on YEAR(logged) and MONTH(logged) so my queries would run faster. Is this possible/necessary in SQL Server?

Yes. You can achive this using the indexed views.

Create different views for each year & index it.

|||Bes, this table sounds like a good candidate for the new table partitioning method of SQL 2005. You could partition by the year and month... There would be separate indexes on each partition slice and SQL Server would direct a query to just the partition needed and the query would run much faster... but... you need Enterprise Edition for paritioning. If you have Enterprise, then it's something to check out... Bruce|||

Bruce,

It's good to know there is another way to do it. The little I've read about Indexed Views indicates they'll increase my maintenance and I should only use them in special cases.

We're not running Enterprise (too much $ for dual CPUs), but if depending on how we use this data maybe we'll be able to justify it.

Thanks!

Brian

|||

I think creating a couple of computed column(s) and creating an index on those field(s) will give you the best combination of query performance and maintenance. Lots of modifications to data in the base table in an indexed view could cause a server to grind to a halt. The index maintenance on the computed columns should be minimal.

alter table MyTable add MyDateYear AS YEAR(MyDate)

alter table MyTable add MyDateMonth AS Month(MyDate)

CREATE INDEX IX_MyTable_Year_Month ON MyTable(MyDateYear, MyDateMonth)

index on a view

Hello,
I have a table (TAB) and A View with alias (VIEW)
Table cod varchar 3
descr carchar 60
my view cod alias COd1
descr alias DES
Now i need a index on view with key COD1
I can't create it.
Can you help me
Carlo
On Thu, 17 Feb 2005 15:48:06 GMT, cmarano wrote:

>Hello,
>I have a table (TAB) and A View with alias (VIEW)
>Table cod varchar 3
> descr carchar 60
>
>my view cod alias COd1
> descr alias DES
>Now i need a index on view with key COD1
>I can't create it.
>Can you help me
Hi Carlo,
I think I can help you, but first I need to get a better picture of what
you're trying to achieve.
What I read from your message is that you have a view that is simply the
same as your table, but with different column names, and that you are now
trying to index that view. I hope that I have misread you, though, as this
would simply result in the same data redundantly being stored at two
different locations in the database.
The best way to clarify your problem is to post:
* Actual table structure, as CREATE TABLE statements - please include all
constraints and all properties (see www.aspfaq.com/5006)
* Some rows of illustrative sample data to give me an idea of the kind of
data you're handling (posted as INSERT statements)
* The CREATE VIEW statement used to create the view you want to index
* The reason for wanting to index your view (in other words: what are you
hoping to achieve)
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Hello,
I have two applications !
In one i have a tabel : TAB with 2 fields cod and descr , length 3 and 60
( varchar)
In the other application i have to read e write in this table , but i have
different field name :
cod1 and des (alias). I repair to this with a view .
Now i have to chain this table with key (cod1) this is the problem.
the create statment :
CREATE TABLE [CO_ZONE] (
[cod_zona] [varchar] (3) COLLATE Latin1_General_BIN NOT NULL ,
[des_zona] [varchar] (30) COLLATE Latin1_General_BIN NOT NULL ,
[dat_obsoleto] [datetime] NULL ,
[prg_net] [timestamp] NOT NULL ,
CONSTRAINT [XPKCO_ZONE] PRIMARY KEY CLUSTERED
(
[cod_zona]
) ON [PRIMARY]
) ON [PRIMARY]
GO
Table contents : IT Italy
US U.S.A.
.. ......
Creat view statments:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE VIEW dbo.TABCE05F
WITH SCHEMABINDING
AS
SELECT TOP 100 PERCENT cod_zona AS T5COAR, des_zona AS T5DEAR
FROM dbo.CO_ZONE
ORDER BY cod_zona
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Best regards, Carlo
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> ha scritto nel messaggio
news:h6t911p41hpuus9vmnts2vfd073a7v1ffk@.4ax.com...
> On Thu, 17 Feb 2005 15:48:06 GMT, cmarano wrote:
>
> Hi Carlo,
> I think I can help you, but first I need to get a better picture of what
> you're trying to achieve.
> What I read from your message is that you have a view that is simply the
> same as your table, but with different column names, and that you are now
> trying to index that view. I hope that I have misread you, though, as this
> would simply result in the same data redundantly being stored at two
> different locations in the database.
> The best way to clarify your problem is to post:
> * Actual table structure, as CREATE TABLE statements - please include all
> constraints and all properties (see www.aspfaq.com/5006)
> * Some rows of illustrative sample data to give me an idea of the kind of
> data you're handling (posted as INSERT statements)
> * The CREATE VIEW statement used to create the view you want to index
> * The reason for wanting to index your view (in other words: what are you
> hoping to achieve)
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
|||On Fri, 18 Feb 2005 14:58:31 GMT, cmarano wrote:

>Hello,
>I have two applications !
>In one i have a tabel : TAB with 2 fields cod and descr , length 3 and 60
>( varchar)
>In the other application i have to read e write in this table , but i have
>different field name :
>cod1 and des (alias). I repair to this with a view .
>Now i have to chain this table with key (cod1) this is the problem.
(snip)
Hi Carlo,
Thanks for posting your explanation and the CREATE TABLE and CREATE VIEW
statements.
In this case, there is no reason to index the view. In fact: if you do,
SQL Server will have to maintain a copy of all data in the table and
change that copy whenever the data in the table changes. You double the
storage space required, give SQL Server extra work to do on updates and
you gain nothing from it.
To be able to use your other application without changing it, you just
need a normal (non-indexed) view. In queries, SQL Server will substitute
the view's name with the view's definition (and since that is simple, it
comes at no extra cost). Similar, updates to the view will be translated
back into updates to the table - and again, at a performance price you
won't notice, since it's a very simple one-on-one translation.
You also don't need the TOP 100 PERCENT in the view (you'll get all rows
by default - only use TOP if you want less than all rows) and you should
remove the ORDER BY (it's not guaranteed to work anyway - it'll be only
used to determine which rows are or are not in the TOP 100 PERCENT, not to
determine the ordering of rows returned by the view).
CREATE VIEW dbo.TABCE05F
AS
SELECT cod_zona AS T5COAR, des_zona AS T5DEAR
FROM dbo.CO_ZONE
GO
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

Index on a varchar column?

In an effort to improve the speed of queries against my main table,
I'll be indexing a column whose data type is varchar(50).

Would I be better off (better performance) if I changed the column's
data type to some numeric type? I would have to update the column's
data to accomodate this, but I would do it if this offers a
performance gain.

-- Bill
Bill wrote:

> In an effort to improve the speed of queries against my main table,
> I'll be indexing a column whose data type is varchar(50).
> Would I be better off (better performance) if I changed the column's
> data type to some numeric type? I would have to update the column's
> data to accomodate this, but I would do it if this offers a
> performance gain.
> -- Bill

If the varchar field is usually using all or most of the 50
characters, *and* it can be changed to an integer, then your
index pages and data pages will certain become smaller, meaning
that more can be in memory at a time, and fewer levels to the
index, etc. Also, the comparison of one integer to another is
faster than varchar-to-varchar.
If the varchar field is most often just a 4-character code,
then the savings will be much less. FInally, consider whether there
is any human-readability value to the varchar content. If not, then
I'd do it...

Joe Weinstein at BEA|||Joe Weinstein <joeNOSPAM@.bea.com> wrote in message news:<3FDDF197.1040901@.bea.com>...
> Bill wrote:
> > In an effort to improve the speed of queries against my main table,
> > I'll be indexing a column whose data type is varchar(50).
> > Would I be better off (better performance) if I changed the column's
> > data type to some numeric type? I would have to update the column's
> > data to accomodate this, but I would do it if this offers a
> > performance gain.
> > -- Bill
> If the varchar field is usually using all or most of the 50
> characters, *and* it can be changed to an integer, then your
> index pages and data pages will certain become smaller, meaning
> that more can be in memory at a time, and fewer levels to the
> index, etc. Also, the comparison of one integer to another is
> faster than varchar-to-varchar.
> If the varchar field is most often just a 4-character code,
> then the savings will be much less. FInally, consider whether there
> is any human-readability value to the varchar content. If not, then
> I'd do it...
> Joe Weinstein at BEA

Thanks for the help, Joe.

The values in the varchar(50) field are invariably of this format:
abc_1234. Always eight characters in length, alpha alpha alpha
underscore digit digit digit digit.

Maybe if I change the columns data type to char(8), then index?

-- Bill|||If the data is by deinition always eight characters, why use define it
as having a size of 50 OR having a variable size?

With a field width of eight, I don't think that you'll realize any speed
improvements by converting to an integer.

HTH

=======================================
Everyone here speaks SQL; some are more fluent, others less. When
describing your SQL object (table, etc.), do so in the language that we
all understand - SQL, not English. It makes it easier to understand
your issue and makes it more likely that you will get the assistance
that you are asking for.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||
Bill wrote:

> Joe Weinstein <joeNOSPAM@.bea.com> wrote in message news:<3FDDF197.1040901@.bea.com>...
>>Bill wrote:
>>
>>
>>>In an effort to improve the speed of queries against my main table,
>>>I'll be indexing a column whose data type is varchar(50).
>>>
>>>Would I be better off (better performance) if I changed the column's
>>>data type to some numeric type? I would have to update the column's
>>>data to accomodate this, but I would do it if this offers a
>>>performance gain.
>>>
>>>-- Bill
>>
>>If the varchar field is usually using all or most of the 50
>>characters, *and* it can be changed to an integer, then your
>>index pages and data pages will certain become smaller, meaning
>>that more can be in memory at a time, and fewer levels to the
>>index, etc. Also, the comparison of one integer to another is
>>faster than varchar-to-varchar.
>> If the varchar field is most often just a 4-character code,
>>then the savings will be much less. FInally, consider whether there
>>is any human-readability value to the varchar content. If not, then
>>I'd do it...
>>
>>Joe Weinstein at BEA
>
> Thanks for the help, Joe.
> The values in the varchar(50) field are invariably of this format:
> abc_1234. Always eight characters in length, alpha alpha alpha
> underscore digit digit digit digit.
> Maybe if I change the columns data type to char(8), then index?

Well, a varchar field doesn't waste 50 chars for an 8-char value,
so the index size will really only drop from 8 bytes to 4 (per entry).
There will be a *little* improvement with an int column, in data volume
and in comparison speed.|||Joe Weinstein <joeNOSPAM@.bea.com> wrote in message news:<3FDDF197.1040901@.bea.com>...
> Bill wrote:
> > In an effort to improve the speed of queries against my main table,
> > I'll be indexing a column whose data type is varchar(50).
> > Would I be better off (better performance) if I changed the column's
> > data type to some numeric type? I would have to update the column's
> > data to accomodate this, but I would do it if this offers a
> > performance gain.
> > -- Bill
> If the varchar field is usually using all or most of the 50
> characters, *and* it can be changed to an integer, then your
> index pages and data pages will certain become smaller, meaning
> that more can be in memory at a time, and fewer levels to the
> index, etc. Also, the comparison of one integer to another is
> faster than varchar-to-varchar.
> If the varchar field is most often just a 4-character code,
> then the savings will be much less. FInally, consider whether there
> is any human-readability value to the varchar content. If not, then
> I'd do it...
> Joe Weinstein at BEA

What Joe said is right.
There are also a couple more things to consider. Sql can always search
numbers faster than text. If all you have in the field are numbers,
then absolutely change it. If you have text in the field. . . If the
data length is closer to 10 or 20 changing it to char might speed
things up. Also if this is the field that is searched most often make
it the clustered index (the order the data is stored on disk). Put
your sql strings in query analyzer and look at the execution plan.
You may find you have table scans on other things that are slowing it
down.
HTH
Pachydermitis|||Neither the use of varying size nor the use of 50 for a data item of
eight characters are erroneous in the sense that they yield incorrect
data; their just, well, inexact. They don't waste space, they just
offend the sensibilities.

The reduction in index size from eight to four should, technically,
allow for faster selects. I just don't think that the speed up will be
significant.

The proof is in the pudding, so try it to see.

HTH

=======================================
Everyone here speaks SQL; some are more fluent, others less. When
describing your SQL object (table, etc.), do so in the language that we
all understand - SQL, not English. It makes it easier to understand
your issue and makes it more likely that you will get the assistance
that you are asking for.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Index for username/password

Does this make sense for a logon table:

CREATE TABLE Logon
(
ID INT NOT NULL IDENTITY PRIMARY KEY,
name VARCHAR(15) NOT NULL,
password VARCHAR(15) NOT NULL
)
GO
CREATE UNIQUE INDEX IX_Logon_Name ON Logon(name)
CREATE INDEX IX_Logon_NameAndPassword ON Logon(name,password)
GO

I do want the name to be unique but also will search frequently on both
name & password. Is this how it should be done? I don't fully
understand the difference between placing a single index in name &
password VS one on both name & password.Cecil (cecilkain0@.yahoo.com) writes:
> Does this make sense for a logon table:
> CREATE TABLE Logon
> (
> ID INT NOT NULL IDENTITY PRIMARY KEY,
> name VARCHAR(15) NOT NULL,
> password VARCHAR(15) NOT NULL
> )
> GO
> CREATE UNIQUE INDEX IX_Logon_Name ON Logon(name)
> CREATE INDEX IX_Logon_NameAndPassword ON Logon(name,password)
> GO
> I do want the name to be unique but also will search frequently on both
> name & password. Is this how it should be done? I don't fully
> understand the difference between placing a single index in name &
> password VS one on both name & password.

I don't see the purpose of the ID column? Why not make the name the primary
key?

The index on (name, password) does not seem very useful here. Usually an
index on the form (uniquecolumn, othercolumn) is not meaningful, but it
can be sometimes, to achieved so-called covered queries. But as long as
the table does not have lots of other columns, it's difficult to see a
case for it here.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||>>I don't see the purpose of the ID column? Why not make the name the primary
>>key?

I was thinking of doing that, but I intend for the Logon table to be
like an ID card. Only for efficient identification. I wanted to reuse
this table design in multiple projects that would require
authentication.

So if I later had an employee say, that needs to login, rather than add
a username,password to the Employee table I could simply add a LogonID
field to the employee table to link it w/ their identification record
in the Logon table.

Do you think this is a bad idea?

Also I thought it would be faster to always use an int ID as my primary
key instead of a string for searching and joining.

If I were to have a foreign key linking to the logon table I'd have to
stick the whole string as the foreign key instead of just an int. So it
was my plan to make sure each table had an int primary key even if it
was possible to uniquely id a record by an already present column like
username.

Again, do you think this is a bad idea? What would you name the foreign
key to a varchar username field? usernameID? It just seems like it
should be a number to me if it has ID appended to it. I like using ID
becuase I know it is a key of somekind when I see it but maybe I
shouldn't do that.

I was reading a post by someone earlier who suggested to me that all
field names be unique across my schema. So if I understand him
correctly:

LogonID, LogonName, & LogonPassword would be better field names.
LogonPassword seems sorta like overkill compared to just password but
if you're going to be unique you might have another field called
password in another table so I guess you'd have to do it that way.
Almost like table-qualifying each field name.

I'm starting a simple DB from scratch so I'm trying to use as good a
practices as I can and would be very interested in your reccomendations
Erland. Thanks.|||Cecil wrote:

> >>I don't see the purpose of the ID column? Why not make the name the primary
> >>key?
> I was thinking of doing that, but I intend for the Logon table to be
> like an ID card. Only for efficient identification. I wanted to reuse
> this table design in multiple projects that would require
> authentication.

Name would still be unique though wouldn't it? So it should still have
a unique constraint on name.

Storing passwords in the database is an inherent security flaw. Don't
store them, encrypted or otherwise. If you must, store a secure hash of
the password. If you are using SQL Server 2005 then use the built in
encryption / authentication. Where possible, use integrated security
rather than invent your own.

--
David Portas
SQL Server MVP
--|||David Portas wrote:

> Cecil wrote:
> > >>I don't see the purpose of the ID column? Why not make the name the primary
> > >>key?
> > I was thinking of doing that, but I intend for the Logon table to be
> > like an ID card. Only for efficient identification. I wanted to reuse
> > this table design in multiple projects that would require
> > authentication.
> Name would still be unique though wouldn't it? So it should still have
> a unique constraint on name.

Apologies, I see that you have declared a unique INDEX on name. A
unique CONSTRAINT is virtually equivalent however and is usually the
preferred choice rather than an index.

--
David Portas
SQL Server MVP
--|||I agree Windows Auth is the way to go, but this DB is for a website and
as such, Windows Auth is not practical.
I was planning to encrypt the password using .NET before storing it in
the DB.

I'm not sure what the built in encryption / authentication SQL2005 has
other than Windows Auth. Is there another feature?

I used an unique index on name because I wished to have fast lookups of
names. I thought an index was how to best accomplish this, No?

I'm not possitive when to use indexex on a column and when to do so on
multiple columns. I don't get the difference.|||I'd still have the 'ID' column but make it a surrogate key instead and use
that on other tables, may be a permissions, for example...

create table Logon (
id int not null identity constraint sk_logon unique clustered,

name varchar(15) not null constraint pk_logon primary key
nonclustered,

password varchar(15) not null
)

In other tables you would use Logon.id and not Logon.name, so if you had a
permissions table say you'd do it like this...

create table Permission (
id int not null identity constraint sk_permission unique
nonclustered,

logon_id int not null references Logon( id ),
security_ticket_id int not null references SecurityTicket ( id ),

constraint pk_Permission primary key clustered ( logon_id,
security_id )
)

Then in the application use 'id' everywhere, it encapsulates the data and
allows for 'name' to change without breaking the application logic.

Tony.

--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials

"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns973A9A6DF18F1Yazorman@.127.0.0.1...
> Cecil (cecilkain0@.yahoo.com) writes:
>> Does this make sense for a logon table:
>>
>> CREATE TABLE Logon
>> (
>> ID INT NOT NULL IDENTITY PRIMARY KEY,
>> name VARCHAR(15) NOT NULL,
>> password VARCHAR(15) NOT NULL
>> )
>> GO
>> CREATE UNIQUE INDEX IX_Logon_Name ON Logon(name)
>> CREATE INDEX IX_Logon_NameAndPassword ON Logon(name,password)
>> GO
>>
>> I do want the name to be unique but also will search frequently on both
>> name & password. Is this how it should be done? I don't fully
>> understand the difference between placing a single index in name &
>> password VS one on both name & password.
> I don't see the purpose of the ID column? Why not make the name the
> primary
> key?
> The index on (name, password) does not seem very useful here. Usually an
> index on the form (uniquecolumn, othercolumn) is not meaningful, but it
> can be sometimes, to achieved so-called covered queries. But as long as
> the table does not have lots of other columns, it's difficult to see a
> case for it here.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||Yeah I think that's a good idea Tony.
That's essentially what I had in mind, perhaps making the ID a
surrogate key does better model what I'm doing w/ it.|||Cecil (cecilkain0@.yahoo.com) writes:
> So if I later had an employee say, that needs to login, rather than add
> a username,password to the Employee table I could simply add a LogonID
> field to the employee table to link it w/ their identification record
> in the Logon table.
> Do you think this is a bad idea?

The ID is superfluous when you have a natural key in the username.
Sometimes surrogates keys are called for.

> Also I thought it would be faster to always use an int ID as my primary
> key instead of a string for searching and joining.

Or it's slower. Say you want to display list which includes the username.
If the username is the foreign key, it's already in the table. With an
ID, you will have to join to the Logins table. And the ID column makes
the table larger, and more space means worse performacne.

The true story, that this is the wrong place to look for performance in,
Whatever you do, it is not likely to have any measurable effect, as I
suspect the volumes will be modest here. Manageability is much more
important, and a username without ID appears more manageable here. The one
case where an ID is nicer, is when a user wants to change his username.

> If I were to have a foreign key linking to the logon table I'd have to
> stick the whole string as the foreign key instead of just an int. So it
> was my plan to make sure each table had an int primary key even if it
> was possible to uniquely id a record by an already present column like
> username.

That's a bad plan. Surrogates are sometimes called for. For instance,
an Orders table typically as an integer key generated by the system.
But an OrderDetails table should have a two-column key with OrderID
and RowNumber (or ProductId).

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Cecil (cecilkain0@.yahoo.com) writes:
> I agree Windows Auth is the way to go, but this DB is for a website and
> as such, Windows Auth is not practical.
> I was planning to encrypt the password using .NET before storing it in
> the DB.
> I'm not sure what the built in encryption / authentication SQL2005 has
> other than Windows Auth. Is there another feature?

SQL 2005 has a whole slew of encryption stuff with asymmetric keys,
symmetric keys, certificates and God knows what. And they are not
dependent on how you log in.

Encryption is not my best subject, but you are probably right encrypting
the password already in the app. Sending it in clear text over the wire
is not that good.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||The ID thing is a misconception by many DBA's, what really happens in an
application is this...

Consider a list of names in say a drop down list, you would code the value
part as the 'id' and the text part as the 'name'.

When a user selects an entry from the drop down you pass the 'id' back to
the database and not the 'name', you then use the 'id' on the query etc...
Basically, the 'name' is used only as meta data for display; of course, if
its a textbox then the user enters the 'name' and thats used, but
applications tend not to work like that - most choices are drop downs,
checkboxes, radio buttons; users don't always remember the full text of
'name'.

Now bear the above in mind and re-read your reasoning, suddenly you have
very narrow tables and you get better performance because joins are on 4
bytes rather than 20 / 30 etc... storage is reduced because of the same
reason. When passing back results, all the joining is done on the 'id' and
you only pass back the 'name' for the small subset of data you are
presenting to the user.

An example schema is as follows :-

create table Logon (
id int not null identity constraint sk_logon unique clustered,

name varchar(15) not null constraint pk_logon primary key
nonclustered,

password varchar(15) not null
)

In other tables you would use Logon.id and not Logon.name, so if you had a
permissions table say you'd do it like this...

create table Permission (
id int not null identity constraint sk_permission unique
nonclustered,

logon_id int not null references Logon( id ),
security_ticket_id int not null references SecurityTicket ( id ),

constraint pk_Permission primary key clustered ( logon_id,
security_id )
)

I think, if I have time I'll write an article over this surrogate key stuff
and how it should be used in the application - it seems to be one of the
biggest misunderstood methods in the db space at the moment.

Tony.

--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials

"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns973AE2E0716BBYazorman@.127.0.0.1...
> Cecil (cecilkain0@.yahoo.com) writes:
>> So if I later had an employee say, that needs to login, rather than add
>> a username,password to the Employee table I could simply add a LogonID
>> field to the employee table to link it w/ their identification record
>> in the Logon table.
>>
>> Do you think this is a bad idea?
> The ID is superfluous when you have a natural key in the username.
> Sometimes surrogates keys are called for.
>> Also I thought it would be faster to always use an int ID as my primary
>> key instead of a string for searching and joining.
> Or it's slower. Say you want to display list which includes the username.
> If the username is the foreign key, it's already in the table. With an
> ID, you will have to join to the Logins table. And the ID column makes
> the table larger, and more space means worse performacne.
> The true story, that this is the wrong place to look for performance in,
> Whatever you do, it is not likely to have any measurable effect, as I
> suspect the volumes will be modest here. Manageability is much more
> important, and a username without ID appears more manageable here. The one
> case where an ID is nicer, is when a user wants to change his username.
>> If I were to have a foreign key linking to the logon table I'd have to
>> stick the whole string as the foreign key instead of just an int. So it
>> was my plan to make sure each table had an int primary key even if it
>> was possible to uniquely id a record by an already present column like
>> username.
> That's a bad plan. Surrogates are sometimes called for. For instance,
> an Orders table typically as an integer key generated by the system.
> But an OrderDetails table should have a two-column key with OrderID
> and RowNumber (or ProductId).
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||Tony Rogerson (tonyrogerson@.torver.net) writes:
> The ID thing is a misconception by many DBA's, what really happens in an
> application is this...
> Consider a list of names in say a drop down list, you would code the value
> part as the 'id' and the text part as the 'name'.
> When a user selects an entry from the drop down you pass the 'id' back to
> the database and not the 'name', you then use the 'id' on the query etc...
> Basically, the 'name' is used only as meta data for display; of course, if
> its a textbox then the user enters the 'name' and thats used, but
> applications tend not to work like that - most choices are drop downs,
> checkboxes, radio buttons; users don't always remember the full text of
> 'name'.
> Now bear the above in mind and re-read your reasoning, suddenly you have
> very narrow tables and you get better performance because joins are on 4
> bytes rather than 20 / 30 etc... storage is reduced because of the same
> reason.

There are of course lots of situations where this strategy is the way
to go. For instance, say that users want to be able to define customer
groups and add customers to them, for statistical purposes or whatever.
Since it is likely that the user would like to have long descriptive
names for their groups, the names are not really good for a key. Not
the least since the users may want to change the group names everyonce
in a while.

A username in a login table is a little different. Usernames are
typically fairly short. They are also less prone to changes. In fact,
you could consider it a business rules that they should not change.

There is always a trade-off in these situations. An id may take up
less space - but you will have to join to the Logins table each time.
And performance is not everything. One advantage with using the login
name as key, is that when you review auditing data or columns, you
see the username directly without joining. The same argument applies
to a customer group as well, but I far more have reason to look at
user-id columns from Query Analyzer than customer-groups ids.

> I think, if I have time I'll write an article over this surrogate key
> stuff and how it should be used in the application - it seems to be one
> of the biggest misunderstood methods in the db space at the moment.

I think most knowledgeable SQL users knows this concept well. The
difficult part is to know when to use it, and when to not. Usernames
is a case where I think a surrogate is a bad idea.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||I suspect you're both right. I'm much more of a programmer than a DBA
which is why I like to get opinions on table deign from more
knowledgable people than myself. But I think Tony is right in implying
as a programmer your table design decisions may seem odd when in fact
the "extra key" in a table to a web-app may be a small price to pay for
an efficient and reliable key in a long list of items on a web page, or
even a rich client for that matter.

Index Creation Question

I have a table with structure
payment_id varchar(10)
date_received datetime
order_id_response_stat varchar(10)
order_response_error_msg varchar(10)
The payment_id was the clustered index and primary key.
The problem now is this field should allow duplicates so I need to change
the indexing on this table. These table is used mostly for INSERTS and will
have about 1 million rows in future. The only occasional select will be base
d
on payment_id and date_received fields.
I dont know which options will be best for me under this situation as I want
to get max perf gains be using an index.
Can anyone pls help me"Anup" <Anup@.discussions.microsoft.com> wrote in message
news:8991B556-271E-4A51-BB70-2125E0F11F6B@.microsoft.com...
> The payment_id was the clustered index and primary key.
> The problem now is this field should allow duplicates so I need to change
OK, so what's your new primary key? Think about data integrity first --
then consider performance.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--|||Thats my question.
I can either create a composite index on payment and date and as a primary
key.
Can create identidy colmun as primary key
But I want to make sure that creating these wont impact the performance.
Pls somebody help what to do and the best scenario
"Adam Machanic" wrote:

> "Anup" <Anup@.discussions.microsoft.com> wrote in message
> news:8991B556-271E-4A51-BB70-2125E0F11F6B@.microsoft.com...
> OK, so what's your new primary key? Think about data integrity first
--
> then consider performance.
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
>|||From an insert point of view, it might make more sense to put the date first
in the composite key, assuming that you insert the payments as they're made.
But that might have a negative impact on selects, if you retrieve the data
based on the payment id. You should probably experiment on your end to find
the best combination.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Anup" <Anup@.discussions.microsoft.com> wrote in message
news:6A5A06FA-3087-4E8E-B926-4C30C98710A0@.microsoft.com...[vbcol=seagreen]
> Thats my question.
> I can either create a composite index on payment and date and as a primary
> key.
> Can create identidy colmun as primary key
> But I want to make sure that creating these wont impact the performance.
> Pls somebody help what to do and the best scenario
> "Adam Machanic" wrote:
>

Index Creation Question

I have a table with structure
payment_id varchar(10)
date_received datetime
order_id_response_stat varchar(10)
order_response_error_msg varchar(10)
The payment_id was the clustered index and primary key.
The problem now is this field should allow duplicates so I need to change
the indexing on this table. These table is used mostly for INSERTS and will
have about 1 million rows in future. The only occasional select will be based
on payment_id and date_received fields.
I dont know which options will be best for me under this situation as I want
to get max perf gains be using an index.
Can anyone pls help me"Anup" <Anup@.discussions.microsoft.com> wrote in message
news:8991B556-271E-4A51-BB70-2125E0F11F6B@.microsoft.com...
> The payment_id was the clustered index and primary key.
> The problem now is this field should allow duplicates so I need to change
OK, so what's your new primary key? Think about data integrity first --
then consider performance.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--|||Thats my question.
I can either create a composite index on payment and date and as a primary
key.
Can create identidy colmun as primary key
But I want to make sure that creating these wont impact the performance.
Pls somebody help what to do and the best scenario
"Adam Machanic" wrote:
> "Anup" <Anup@.discussions.microsoft.com> wrote in message
> news:8991B556-271E-4A51-BB70-2125E0F11F6B@.microsoft.com...
> >
> > The payment_id was the clustered index and primary key.
> >
> > The problem now is this field should allow duplicates so I need to change
> OK, so what's your new primary key? Think about data integrity first --
> then consider performance.
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
>|||From an insert point of view, it might make more sense to put the date first
in the composite key, assuming that you insert the payments as they're made.
But that might have a negative impact on selects, if you retrieve the data
based on the payment id. You should probably experiment on your end to find
the best combination.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Anup" <Anup@.discussions.microsoft.com> wrote in message
news:6A5A06FA-3087-4E8E-B926-4C30C98710A0@.microsoft.com...
> Thats my question.
> I can either create a composite index on payment and date and as a primary
> key.
> Can create identidy colmun as primary key
> But I want to make sure that creating these wont impact the performance.
> Pls somebody help what to do and the best scenario
> "Adam Machanic" wrote:
>> "Anup" <Anup@.discussions.microsoft.com> wrote in message
>> news:8991B556-271E-4A51-BB70-2125E0F11F6B@.microsoft.com...
>> >
>> > The payment_id was the clustered index and primary key.
>> >
>> > The problem now is this field should allow duplicates so I need to
>> > change
>> OK, so what's your new primary key? Think about data integrity
>> first --
>> then consider performance.
>>
>> --
>> Adam Machanic
>> Pro SQL Server 2005, available now
>> http://www.apress.com/book/bookDisplay.html?bID=457
>> --
>>
>>

index bloat?

Hi,

I have a table with about 305 million rows, and a composite primary key
that consists of an ascending int and an ascending varchar(18), which
is typically of length 13. Even if all the keys used the full 18
characters of the varchar, it seems to me each key should be 22 bytes,
so the index should be roughly 6.4GB. However, the size of the index as
shown in EM is about 24GB, and this is slowing everything down
considerably. Does anyone else think this index size is a little
excessive, or know why it should be so large?

Thanks,
Sethsql_server_2000_user (sethpurcell@.comcast.net) writes:
> I have a table with about 305 million rows, and a composite primary key
> that consists of an ascending int and an ascending varchar(18), which
> is typically of length 13. Even if all the keys used the full 18
> characters of the varchar, it seems to me each key should be 22 bytes,
> so the index should be roughly 6.4GB. However, the size of the index as
> shown in EM is about 24GB, and this is slowing everything down
> considerably. Does anyone else think this index size is a little
> excessive, or know why it should be so large?

Is that a clustered index or a non-clustered iodex?

A clustered index has the data pages in the leafs of the index node, so
size of index is basically size of data.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Just a guess, but is your primary key a clustered index? The clustered
index represents the actual contents of each row in your database, so it
would make sense that it would be larger than only the indexed fields.

If this is the case, you might want to change that index to not be
clustered and choose a more appropriate field to make a clustered index.

Thanks,
Tony

"sql_server_2000_user" <sethpurcell@.comcast.net> wrote in message
news:1123161597.120447.165590@.g49g2000cwa.googlegr oups.com:

> Hi,
> I have a table with about 305 million rows, and a composite primary key
> that consists of an ascending int and an ascending varchar(18), which
> is typically of length 13. Even if all the keys used the full 18
> characters of the varchar, it seems to me each key should be 22 bytes,
> so the index should be roughly 6.4GB. However, the size of the index as
> shown in EM is about 24GB, and this is slowing everything down
> considerably. Does anyone else think this index size is a little
> excessive, or know why it should be so large?
> Thanks,
> Seth|||Yes! It is intentionally a clustered index. But if the db includes the
row data pages when it reports the size of a clustered index, why is
the size of the index not just equal to the size of the table (105GB?)

Thanks,
Seth|||I think I would take a look at the FILL FACTOR of the index (the % of
the index that is created empty), and possibly do a DBCC SHOWCONTIG to
show the current fragmentation and DBCC REINDEX on the table during a
time of little or no activity if I thought that the tables contents
might be fragmented.

You can look up any of the all-caps terms in Books Online if you want
more info about the commands.

Good luck,
Tony Sebion

"sql_server_2000_user" <sethpurcell@.comcast.net> wrote in message
news:1123168931.364988.273310@.g47g2000cwa.googlegr oups.com:

> Yes! It is intentionally a clustered index. But if the db includes the
> row data pages when it reports the size of a clustered index, why is
> the size of the index not just equal to the size of the table (105GB?)
> Thanks,
> Seth|||thanks for the tips. the fill factor is zero on the index; i'll
definitely look for fragmentation. what i don't understand is, the
query was fast yesterday (4 secs), and today it's slow (10 mins), and
the only change i made to the table was that i added an index on an
unrelated field. (the new index is 12GB)|||Now that you mention it, you will want to make sure you REINDEX all the
other indexes for that table after you're done reindexing the clustered
one. I'd start there and see what things look like when that is
complete.

Tony

"sql_server_2000_user" <sethpurcell@.comcast.net> wrote in message
news:1123174187.544688.200820@.g44g2000cwa.googlegr oups.com:

> thanks for the tips. the fill factor is zero on the index; i'll
> definitely look for fragmentation. what i don't understand is, the
> query was fast yesterday (4 secs), and today it's slow (10 mins), and
> the only change i made to the table was that i added an index on an
> unrelated field. (the new index is 12GB)|||sql_server_2000_user (sethpurcell@.comcast.net) writes:
> Yes! It is intentionally a clustered index. But if the db includes the
> row data pages when it reports the size of a clustered index, why is
> the size of the index not just equal to the size of the table (105GB?)

From where did you get that? Enterprise Manager? EM is not known to report
sizes very well. I tried to have a look at it, but was not able to dig
out any sizes at all from EM. (Yeah, that's right, I don't use EM that
often.)

Anyway, your real problem appears to be:

> what i don't understand is, the query was fast yesterday (4 secs), and
> today it's slow (10 mins), and the only change i made to the table was
> that i added an index on an unrelated field. (the new index is 12GB)

It appears that some how this cause a shake-up for the query plan. When
you create a new index, statistics are updated with fullscan. Since the PK
is the clustered index, the PK columns are included in the non-clustered
index as well, as the index keys for the clustered index work as row
locator. I don't know if this causes statistics on the PK to be updated
as well. It could also be that SQL Server makes an incorrect estimate
and thinks that the new index is good for the query.

It would help if you posted the query, the CREATE TABLE statement for
the table, as well as the CREATE INDEX statements.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Wow, you guys are great. Yeah, I know EM returns wack object sizes, but
I thought they were kind of close; maybe not. Anyway, here's a test I
find interesting:

query 1:

select * from loan_history where time_period=196 and exloan_id in (
select top 3 exloan_id from loan where deal_no='ML4W1')

query 2:

select * from loan_history where time_period=196 and exloan_id in
('CTSMYX0010515778', 'CTSMYX0010525710', 'CTSMYX0010527475')

Query 1 takes a long time - i don't know how long because i cancel it
after a few minutes. Query 2 is <1 sec. the odd thing is, the subquery
of query 1 is also <1 sec! ?

The query that used to be fast and is now slow, requiring a huge amount
of reads, is simply:

select * from loan
inner join loan_history lh
on loan.exloan_id = lh.exloan_id
where loan.deal_no='ML4W1'
and time_period='196'

Here's the output of DBCC SHOWCONTIG on loan_history (305mm rows):

DBCC SHOWCONTIG scanning 'loan_history' table...
Table: 'loan_history' (949578421); index ID: 1, database ID: 7
TABLE level scan performed.
- Pages Scanned........................: 7497085
- Extents Scanned.......................: 941533
- Extent Switches.......................: 1489125
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 62.93%
[937136:1489126]
- Logical Scan Fragmentation ..............: 93.40%
- Extent Scan Fragmentation ...............: 2.39%
- Avg. Bytes Free per Page................: 535.3
- Avg. Page Density (full)................: 93.39%
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.

And for loan (9.8mm rows):

DBCC SHOWCONTIG scanning 'loan' table...
Table: 'loan' (1413580074); index ID: 1, database ID: 7
TABLE level scan performed.
- Pages Scanned........................: 462476
- Extents Scanned.......................: 58046
- Extent Switches.......................: 77279
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 74.81% [57810:77280]
- Logical Scan Fragmentation ..............: 96.45%
- Extent Scan Fragmentation ...............: 0.54%
- Avg. Bytes Free per Page................: 329.5
- Avg. Page Density (full)................: 95.93%
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.

Sorry for the long post, but here's the create table statement for
loan:

CREATE TABLE [loan] (
[SERIES_NO] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[POOL_ID] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[DEAL_NO] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[GROUP_NO] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[LOAN_ID] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[EXLOAN_ID] [varchar] (18) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[LOAN_NO] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[ADD_DATE] [int] NULL ,
[PROP_ZIP] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[STATE] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PROP_TYPE] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UNITS_NO] [int] NULL ,
[OCCUPANCY] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ORIG_DATE] [int] NULL ,
[MATURITY] [int] NULL ,
[FIRST_PMT] [int] NULL ,
[ORIG_AMT] [money] NULL ,
[CLOSE_BAL] [money] NULL ,
[CLOSE_INT] [float] NULL ,
[SALE_PRICE] [money] NULL ,
[APP_VALUE] [money] NULL ,
[PROD_TYPE] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TERM] [int] NULL ,
[INIT_RATE] [float] NULL ,
[UNDER_RAT1] [float] NULL ,
[UNDER_RAT2] [float] NULL ,
[LOAN_TYPE] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PURPOSE] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PMT_FREQ] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LOAN_SRC] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BUYDOWN] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DOCUMENT] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PMI_CODE] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CONVERT] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[POOL_INS] [bit] NULL ,
[RECOURSE] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LTV] [float] NULL ,
[SERV_FEE_R] [float] NULL ,
[NEGAM] [bit] NULL ,
[NEG_LIMIT] [float] NULL ,
[INDEX_ID] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MARGIN] [float] NULL ,
[P_RATE_CAP] [float] NULL ,
[P_RATE_FLO] [float] NULL ,
[P_PAY_CAP] [float] NULL ,
[P_PAY_FLO] [float] NULL ,
[L_RATE_CAP] [float] NULL ,
[L_RATE_FLO] [float] NULL ,
[RATE_RESET] [int] NULL ,
[PAY_RESET] [int] NULL ,
[FIRST_RATE] [int] NULL ,
[FIRST_PAY] [int] NULL ,
[AMORT_TERM] [int] NULL ,
[DOC_RAW] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FICO] [int] NULL ,
[LIEN] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[GRADE_RAW] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[GRADE_MIC] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PP_PEN] [bit] NULL ,
[PP_TERM] [int] NULL ,
[F_RATE_CAP] [float] NULL ,
[PMI_LEVEL] [float] NULL ,
[PLEDGE_AMT] [money] NULL ,
[EFF_LTV] [float] NULL ,
[FIRST_LTV] [float] NULL ,
[SECOND_LTV] [float] NULL ,
[COMB_LTV] [float] NULL ,
[SERVICER] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ORIGINATOR] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[SILENT_SECOND] [bit] NOT NULL ,
CONSTRAINT [PK_loan] PRIMARY KEY CLUSTERED
(
[EXLOAN_ID]
) ON [PRIMARY]
) ON [PRIMARY]
GO

And for loan_history:

CREATE TABLE [loan_history] (
[TIME_PERIOD] [int] NOT NULL ,
[POOL_ID] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[DEAL_NO] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[GROUP_NO] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[SERVICER] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LOAN_ID] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[EXLOAN_ID] [varchar] (18) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[LAST_INT_P] [int] NULL ,
[BALANCE] [money] NULL ,
[INT_RATE] [float] NULL ,
[TOTPMT_DUE] [money] NULL ,
[SCH_PRINC] [money] NULL ,
[SCH_MNTH_P] [money] NULL ,
[MBA_STAT] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OTS_STAT] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DELIQ_HIST] [char] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EXCEPTION] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FC_START_D] [int] NULL ,
[FC_END_D] [int] NULL ,
[FC_END_TYP] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PAYOFF_D] [int] NULL ,
[PAYOFF_R] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[REO_DATE] [int] NULL ,
[INV_BAL] [money] NULL ,
[NEXT_INT_R] [float] NULL ,
[LOSS_AMT] [money] NULL ,
[INET_RATE] [float] NULL ,
[YYYYMM] AS ((floor(([TIME_PERIOD] / 12)) + 1989) * 100 +
[TIME_PERIOD] % 12 + 1) ,
CONSTRAINT [PK_loan_history] PRIMARY KEY CLUSTERED
(
[TIME_PERIOD],
[EXLOAN_ID]
) ON [PRIMARY]
) ON [PRIMARY]
GO

Thanks for all the help, I really appreciate it.
Seth|||sql_server_2000_user (sethpurcell@.comcast.net) writes:
> query 1:
> select * from loan_history where time_period=196 and exloan_id in (
> select top 3 exloan_id from loan where deal_no='ML4W1')
> query 2:
> select * from loan_history where time_period=196 and exloan_id in
> ('CTSMYX0010515778', 'CTSMYX0010525710', 'CTSMYX0010527475')
> Query 1 takes a long time - i don't know how long because i cancel it
> after a few minutes. Query 2 is <1 sec. the odd thing is, the subquery
> of query 1 is also <1 sec! ?

Well, it's not the case that SQL Server first runs the subquery and given
that result optimizes the outer query. It optimizes everything in one
go.

Then again, it's difficult to say why it would fail here, since it knows
that it will get (at most) three rows from loan, and thus only have to
read three rows from loan_history.

You forgot to include indexes for the tables, but I assume that here is
a non-clustered index on loan.deal_no?

What query plan do you have for the slow query here? Run the query
embedded in SET SHOWPLAN_TEXT ON /OFF. (In separate batches.)

> The query that used to be fast and is now slow, requiring a huge amount
> of reads, is simply:
> select * from loan
> inner join loan_history lh
> on loan.exloan_id = lh.exloan_id
> where loan.deal_no='ML4W1'
> and time_period='196'

For this query it would probably be better if the index on
loan.deal_no was clustered, possibly then it should be (deal_no, exloan_id).
But this could have consequences for other queries.

Again, it would be interesting to see the query plan.

By the way, using SELECT * in production code is not good practice.
Better to list all columns you reallyneed.

> Here's the output of DBCC SHOWCONTIG on loan_history (305mm rows):

Thanks. The tables appears to be in decent shape. Not perfect, but
certainly not alarming.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Yes, there's a non-clustered index on loan.deal_no.

I assume you said that having the table clustered by loan.deal_no would
be advantageous for this query because it would minimize the number of
page reads?

The tables don't look bad?? I thought the logical fragmentation of both
tables was horrendous, given that books online says 0-10% is tolerable
and they're at 93% and 96%.

Here's the plan for query1:

|--Nested Loops(Left Semi Join, OUTER
REFERENCES:([loan_history].[EXLOAN_ID]))
|--Compute
Scalar(DEFINE:([loan_history].[YYYYMM]=(floor([loan_history].[TIME_PERIOD]/12)+1989)*100+[loan_history].[TIME_PERIOD]%12+1))
| |--Clustered Index
Seek(OBJECT:([LoanPerformance].[dbo].[loan_history].[PK_loan_history]),
SEEK:([loan_history].[TIME_PERIOD]=196) ORDERED FORWARD)
|--Filter(WHERE:([loan_history].[EXLOAN_ID]=[loan].[EXLOAN_ID]))
|--Top(3)
|--Index
Seek(OBJECT:([LoanPerformance].[dbo].[loan].[IX_loan_deal_no]),
SEEK:([loan].[DEAL_NO]='ML4W1') ORDERED FORWARD)

Here's the plan for query2:

|--Compute
Scalar(DEFINE:([loan_history].[YYYYMM]=(floor([loan_history].[TIME_PERIOD]/12)+1989)*100+[loan_history].[TIME_PERIOD]%12+1))
|--Clustered Index
Seek(OBJECT:([LoanPerformance].[dbo].[loan_history].[PK_loan_history]),
SEEK:([loan_history].[TIME_PERIOD]=196 AND
[loan_history].[EXLOAN_ID]='CTSMYX0010515778' OR
[loan_history].[TIME_PERIOD]=196 AND [loan_history].[EXLOAN_ID]='C

(the line was truncated in the QA window)

Here are the indices on loan:

CREATE INDEX [IX_loan_deal_no] ON [dbo].[loan]([DEAL_NO]) ON
[PRIMARY]
CREATE INDEX [IX_loan_fico] ON [dbo].[loan]([FICO]) ON [PRIMARY]
CREATE INDEX [IX_loan_state] ON [dbo].[loan]([STATE]) ON [PRIMARY]
CREATE INDEX [IX_loan_orig_date] ON [dbo].[loan]([ORIG_DATE]) ON
[PRIMARY]
CREATE INDEX [IX_loan_grade_mic] ON [dbo].[loan]([GRADE_MIC]) ON
[PRIMARY]
CREATE INDEX [IX_loan_loan_no] ON [dbo].[loan]([LOAN_NO]) ON
[PRIMARY]
CREATE INDEX [IX_loan_series_no] ON [dbo].[loan]([SERIES_NO]) ON
[PRIMARY]

and on loan_history:

CREATE INDEX [IX_loan_history_deal_no] ON
[dbo].[loan_history]([DEAL_NO]) ON [PRIMARY]
CREATE INDEX [IX_loan_history_balance] ON
[dbo].[loan_history]([BALANCE]) ON [PRIMARY]

The last index was the one I added recently.

What I don't understand is, I had an SP that uses this simple join
query that ran in 4 secondss yesterday and today it takes 2 hours, and
I'm at a loss to understand what changed (besides the index I added)
that may have caused this. I can accept that it is supposed to take two
hours, but then why did it used to take four seconds?? When I hover the
cursor over a stage in the execution plan in QA, it gives me
ridiculously low CPU and I/O costs for operations - nothing like the 40
minutes of wall-clock time I'm seeing. The execution plan for the query
run by the SP are all index seeks, nested loop joins - no bookmark
lookups, I don't get it. And that little 'top 3' subquery, I just can't
fathom - I mean, it's only three rows, what on earth can it be doing
for 10 minutes??

Thanks again,
Seth|||Actually, I think know what it's doing - reading off disk. If I check
sysprocesses, the process shows lastwaittype=PAGEIOLATCH_SH and the
waitresource is just spinning through hundreds and hundreds of pages...
just for three rows.|||sql_server_2000_user (sethpurcell@.comcast.net) writes:
> I assume you said that having the table clustered by loan.deal_no would
> be advantageous for this query because it would minimize the number of
> page reads?

The idea is that SQL Server then could start with finding all rows
with that deal_no, and the look up in loan history. I am the assuming
that the number of rows per deal_no is moderate.

> The tables don't look bad?? I thought the logical fragmentation of both
> tables was horrendous, given that books online says 0-10% is tolerable
> and they're at 93% and 96%.

You are right. I mainly looked at Scan Density (which is decent) and
Extent Scan Fragmentation (which is excellent).

> Here's the plan for query1:

So what it does is to do a clustered index seek on loan_history - but
only on time_period. So in practice it scans all rows for time_period = 196
and for each row looks it up if exloan_id matches the subquery. Assuming
that there are a couple of million rows for one time_period, that is
going to take a long time!

Had the the clustered index on loan been on (deal_no, exloan_id), it
could at least have done a merge join. But that would be slow too.
I would rather expect some thing like first running the subquery, and
then to a nested loop join to loan_history and using both columns of
the clustered index.

> Here's the plan for query2:

But was the quick one. What about the join:

select * from loan
inner join loan_history lh
on loan.exloan_id = lh.exloan_id
where loan.deal_no='ML4W1'
and time_period='196'

I can't see that you included the plan for that one.

> (the line was truncated in the QA window)

Tools->Options->Results, here you can change "Max characters per column"
to rectify this.

> When I hover the cursor over a stage in the execution plan in QA, it
> gives me ridiculously low CPU and I/O costs for operations - nothing
> like the 40 minutes of wall-clock time I'm seeing.

Of course! I mean it's not picking a bad plan, because it's evil, but
because the estimates are wrong.

> The execution plan for the query run by the SP are all index seeks,
> nested loop joins - no bookmark lookups, I don't get it.

As we saw above, a nested loop join in the wrong place can be quite
bad. If you include the plan, I might be able to say more.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Here's the plan for the actual join:

StmtText
|--Nested Loops(Inner Join, OUTER REFERENCES:([lh].[EXLOAN_ID]))
|--Compute
Scalar(DEFINE:([lh].[YYYYMM]=(floor([lh].[TIME_PERIOD]/12)+1989)*100+[lh].[TIME_PERIOD]%12+1))
| |--Clustered Index
Seek(OBJECT:([LoanPerformance].[dbo].[loan_history].[PK_loan_history]
AS [lh]), SEEK:([lh].[TIME_PERIOD]=196) ORDERED FORWARD)
|--Clustered Index
Seek(OBJECT:([LoanPerformance].[dbo].[loan].[PK_loan]),
SEEK:([loan].[EXLOAN_ID]=[lh].[EXLOAN_ID]),
WHERE:([loan].[DEAL_NO]='ML4W1') ORDERED FORWARD)

I got the CPU and I/O numbers from the execution plan that came up
after running the query, not from the estimated execution plan you can
get without running the query, so I thought they were the actual
measurements, not estimates.

So you're saying the problem is the nested loop join is in the wrong
direction? I guess what I expect the optimizer to do is: get the
exloan_id from the loan table for the few thousand records with the
specified deal_no, and then do an index seek in the primary key of the
loan_history table (since I specify the time period and the subquery
provides the other part of the composite key), thus doing just a few
thousand index seeks on a clustered index to get the row data. If this
is right, then why doesn't the optimizer do this? Can I get it to do it
this way? Do I have to give hints to the query optimizer, or do I need
to do something to the tables? Should I rebuild them with a >0 fill
factor to defrag anyway? And is it somehow possible that the optimizer
was generating the correct execution plan, and that by adding an index,
it broke? Because I swear, the queries were taking like 5 seconds
before.

Thanks a lot,
Seth|||sql_server_2000_user (sethpurcell@.comcast.net) writes:
> I got the CPU and I/O numbers from the execution plan that came up
> after running the query, not from the estimated execution plan you can
> get without running the query, so I thought they were the actual
> measurements, not estimates.

No, they are still estimates.

> So you're saying the problem is the nested loop join is in the wrong
> direction?

Yes, it's the very same problem with this query: it starts off in
loah_history and scanning everything with time_period = 196.

> I guess what I expect the optimizer to do is: get the exloan_id from the
> loan table for the few thousand records with the specified deal_no, and
> then do an index seek in the primary key of the loan_history table
> (since I specify the time period and the subquery provides the other
> part of the composite key), thus doing just a few thousand index seeks
> on a clustered index to get the row data.

Yeah, that sounds like a bad plan. Maybe you can get a job as query
optimizer. :-)

> If this is right, then why doesn't the optimizer do this?

Good question. More good questions?

> Can I get it to do it this way? Do I have to give hints to the query
> optimizer,

Yes, an "OPTION (FORCE ORDER)" at the end of the query should do the
trick, but don't go there yet.

> or do I need to do something to the tables? Should I rebuild them with a
> >0 fill factor to defrag anyway? And is it somehow possible that the
> optimizer was generating the correct execution plan, and that by adding
> an index, it broke? Because I swear, the queries were taking like 5
> seconds before.

Well, the one thing I can think of is that by adding an index, SQL
Server updated the statistics for the PK, and this new information
left the optimizer in a maze. But given the distribution, I don't
really see how it could into this mess.

Let's try this: run "UPDATE STATISTICS loan WITH FULLSCAN" and try the
query. (It could be that with new statistics for loan_history, but
antiquated statistics for loan, you get the bad plan.) If the plan is
equally rotten try "UPDATE STATISTICS loan_history WITH FULLSCAN".

If the plan is still bad, at this point reindexing only helps to make
run a little less slow. That is, reindexing as such does not affect
the query plan. But since it will include a complete update of
statistics, it could have an effect of query plan directly.

Anyway, if the plan is still bad at this point, try the query hint.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||I updated statistics on the loan table and it had no effect.

Was that a typo and you meant to say it sounds like a good plan?
Because I forced this plan with the following query:

select * from (
select * from loan
where loan.deal_no='ML4W1') as x
inner join loan_history as lh
on x.exloan_id=lh.exloan_id
where time_period=196
option (force order, loop join)

and it is so fast it makes me cry - 1 second if cached, 5 seconds if
not cached and a large deal. Here's the plan:

StmtText
|--Nested Loops(Inner Join, OUTER REFERENCES:([loan].[EXLOAN_ID])
WITH PREFETCH)
|--Bookmark Lookup(BOOKMARK:([Bmk1000]),
OBJECT:([LoanPerformance].[dbo].[loan]))
| |--Index
Seek(OBJECT:([LoanPerformance].[dbo].[loan].[IX_loan_deal_no]),
SEEK:([loan].[DEAL_NO]='ML4W1') ORDERED FORWARD)
|--Compute
Scalar(DEFINE:([lh].[YYYYMM]=(floor([lh].[TIME_PERIOD]/12)+1989)*100+[lh].[TIME_PERIOD]%12+1))
|--Clustered Index
Seek(OBJECT:([LoanPerformance].[dbo].[loan_history].[PK_loan_history]
AS [lh]), SEEK:([lh].[TIME_PERIOD]=196 AND
[lh].[EXLOAN_ID]=[loan].[EXLOAN_ID]) ORDERED FORWARD)

I tried this query:

select * from loan
inner join loan_history lh
on loan.exloan_id = lh.exloan_id
where loan.deal_no='ML4W1'
and time_period='196'
option (force order)

and it takes a couple minutes if not cached, 5 seconds if cached -
definitely better than a couple hours, but nowhere near my
corrected-nested-loop. Here's the plan:

StmtText
|--Merge Join(Inner Join,
MERGE:([loan].[EXLOAN_ID])=([lh].[EXLOAN_ID]),
RESIDUAL:([lh].[EXLOAN_ID]=[loan].[EXLOAN_ID]))
|--Bookmark Lookup(BOOKMARK:([Bmk1000]),
OBJECT:([LoanPerformance].[dbo].[loan]))
| |--Index
Seek(OBJECT:([LoanPerformance].[dbo].[loan].[IX_loan_deal_no]),
SEEK:([loan].[DEAL_NO]='ML4W1') ORDERED FORWARD)
|--Compute
Scalar(DEFINE:([lh].[YYYYMM]=(floor([lh].[TIME_PERIOD]/12)+1989)*100+[lh].[TIME_PERIOD]%12+1))
|--Clustered Index
Seek(OBJECT:([LoanPerformance].[dbo].[loan_history].[PK_loan_history]
AS [lh]), SEEK:([lh].[TIME_PERIOD]=196) ORDERED FORWARD)

And now for the obvious/hard question: now that I know what the right
execution plan is, how can I get the query optimizer to generate it -
*without the hints*? Because some users of the DB are using report
designing software that will just generate the join query with no
hints, and I don't want them sitting there for hours when they don't
have to. Does the optimizer not choose the merge join because it
requires a bookmark lookup? Does it not choose my nested loop join
because the statistics are wrong somewhere?

Thanks again,
Seth|||sql_server_2000_user (sethpurcell@.comcast.net) writes:
> I updated statistics on the loan table and it had no effect.

And you used FULLSCAN?

Did you also update statistics (WITH FULLSCAN) on loan_history?

> Was that a typo and you meant to say it sounds like a good plan?

Yes. Don't believe everything I say. :-)

> Because I forced this plan with the following query:
> select * from (
> select * from loan
> where loan.deal_no='ML4W1') as x
> inner join loan_history as lh
> on x.exloan_id=lh.exloan_id
> where time_period=196
> option (force order, loop join)
> and it is so fast it makes me cry - 1 second if cached, 5 seconds if
> not cached and a large deal. Here's the plan:

And that is indeed the plan we are looking for!

> and it takes a couple minutes if not cached, 5 seconds if cached -
> definitely better than a couple hours, but nowhere near my
> corrected-nested-loop. Here's the plan:

Certainly better, but still scanning the entire time_period 196.

> And now for the obvious/hard question: now that I know what the right
> execution plan is, how can I get the query optimizer to generate it -
> *without the hints*? Because some users of the DB are using report
> designing software that will just generate the join query with no
> hints, and I don't want them sitting there for hours when they don't
> have to. Does the optimizer not choose the merge join because it
> requires a bookmark lookup? Does it not choose my nested loop join
> because the statistics are wrong somewhere?

I have no idea. I have posted a question to our internal MVP forum
to get some suggestions. So, OK, I have a vague guess: time_period
is a bit too unselective to be the first column in the index. The
statistics for the index, has a distribution histogramme for this
column only.

Thus, here is an idea: Make the PK on loan_history unclustered,
and then add a clustered index on (exloan_id, time_period), that is
the reverse key. But this is a very costly operation to do on a
305 million row table, and there are other queries that could take
a tool. (Hm, if you add a non-clustered index on the reverse only?
Could that help?)

There is also the idea that I've mentioned before, make the
index on loan.deal_no the clustered on that table. That would at
least be faster to implement. But that is also likely to have
ramifications on other queries.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||
sql_server_2000_user wrote:
> I updated statistics on the loan table and it had no effect.
>
> And now for the obvious/hard question: now that I know what the right
> execution plan is, how can I get the query optimizer to generate it -
> *without the hints*? Because some users of the DB are using report
> designing software that will just generate the join query with no
> hints, and I don't want them sitting there for hours when they don't
> have to. Does the optimizer not choose the merge join because it
> requires a bookmark lookup? Does it not choose my nested loop join
> because the statistics are wrong somewhere?

This can be hard, but one place to start that I don't
see done in this thread is to find the bad estimate, if
there is one causing the problem. Usually this will be
a bad rowcount estimate (rowcount estimates cannot
always be improved with better statistics, so this can
happen even after FULLSCAN) - sometimes it is a row size estimate.

First, though, just one thought - You noted that the index is
very fragmented, though the density is good. This could be
a problem costing the seek on time period 196. The optimizer might
expect all the qualifying rows to be physically contiguous, but
they might be scattered all over the place. If the optimizer
expects 40-50 rows per page, and perhaps 1,000,000 qualifying
rows, the I/O for the scan will be ~20,000 pages, or ~150MB.
If instead, the index is so fragmented that to follow the
logical order requires 10 times as many pages, you could move
from an "all in memory" query to a disk-grinder.

I'm not sure whether the optimizer considers fragmentation at
all in planning - I assume not, but could be wrong.

Can you find out how many pages contain rows with time period
196? You could clear the buffers and run a simple select like
select <some columns only in the clustered index>
from loan_history
where time_period = 196

It would be good here also to see if the estimated and actual
row count for that value are close in this simple query.

Then look at estimates in the actual slow query and see if any
are bad. If you run one of the too-slow queries (the slowest
you can stand to run) after typing CTRL-K for "show execution plan",
you will see the estimated execution plan with a couple of
extra actual values in addition to most of the estimates you can
get with CTRL-L. In particular, you will see the actual number
of rows (Row Count) and number of executions (Number of Executes)
of each operator.

Compare (Estimated Rowcount)*(Estimated number of executes) with
(Row Count) for the key operators. You will have to get the
estimated number of executes from CTRL-L, because it is not repeated
in the actual plan. Note that the actual rowcount already accounts
for the number of executes, so don't multiply it in.

Also for the key operators, look at Estimated Row Size.
It should be the average row size of an index row, and if it is
very far off, it can cause the wrong plan to be chosen. I
doubt this is your problem, but it can't hurt to look.0

If you see bad estimates, let us know.

If this is the problem, and it can't be fixed with
statistics, you might consider trying to change the estimate
with extra predicates that don't affect the query result.
Changing the estimate to a smaller number can be done by
repeating or adding predicates that have no real effect but
which the optimizer expects will restrict the number of rows.
Making estimates larger in the hope of leading the optimizer
away from an index that is not helping might be possible
by adding superfluous OR conditions, or with a non-SARGable
predicate that cannot use statistics and will use a generic
estimate.

Perhaps some of this will help, in addition to what Erland's
good advice is doing.

Steve Kass
Drew University

> Thanks again,
> Seth|||Yes, I updated statistics with a full scan, and no, I didn't update the
loan_history statistics - this would be a really large job, and they
haven't changed since before the behavior changed, so I didn't think
that could be a problem.

As an experiment, I built a table identical to loan_history but with
the primary key reversed (still clustered). I populated the table with
10% of the original loan_history table, and here is the simple join and
its execution plan:

from loan
inner join loan_history lh
on loan.exloan_id=lh.exloan_id
where loan.deal_no='EQ604'
and time_period=81

StmtText
|--Merge Join(Inner Join,
MERGE:([loan].[EXLOAN_ID])=([lh].[EXLOAN_ID]),
RESIDUAL:([lh].[EXLOAN_ID]=[loan].[EXLOAN_ID]))
|--Bookmark Lookup(BOOKMARK:([Bmk1000]),
OBJECT:([LoanPerformance].[dbo].[loan]))
| |--Index
Seek(OBJECT:([LoanPerformance].[dbo].[loan].[IX_loan_deal_no]),
SEEK:([loan].[DEAL_NO]='EQ604') ORDERED FORWARD)
|--Compute
Scalar(DEFINE:([lh].[YYYYMM]=(floor([lh].[TIME_PERIOD]/12)+1989)*100+[lh].[TIME_PERIOD]%12+1))
|--Clustered Index
Seek(OBJECT:([LoanPerformance].[dbo].[loan_history].[PK_loan_history]
AS [lh]), SEEK:([lh].[TIME_PERIOD]=81) ORDERED FORWARD)

As you can see, it's identical to the plan generated on the original
table when I use the option (force order) hint, which is good but still
not as good as the nested loop join I came up with. Why can't it find
the nested loop plan? What do I know about the table that the optimizer
doesn't? Could this be due to the different scales of the tables?
Perhaps somewhere between 10% and 100% it becomes more efficient to use
my nested loop join as opposed to this merge join, and if the tables
were the same size, I would see this?

I am working on the rowcount/rowsize comparisons.

Thanks for all the great help,
Seth|||Wow, this is really interesting:

I made a spreadsheet with one row per time period, showing the join
method, row estimates and actual row counts, execution time, etc., and
discovered some interesting things. A key aspect of this query is how
the number of rows in loan_history increases as the time_period
increases, and how this causes the execution plan to change. This
should give you some idea of how the number of records increases:

time_period record count
40 596
50 6178
60 12998
70 37802
80 65614
90 108234
100 227347
110 410063
120 801426
130 1404053
140 1957924
150 2487100
160 3473813
170 4435225
180 5622040
190 7571203

Pretty much exponential. So now that you know that, here's what QA does
with this query:

select *
from loan
inner join loan_history lh
on loan.exloan_id=lh.exloan_id
where loan.deal_no=@.deal_no
and time_period=@.time_period

time_period join method
39-40 loop; loan_history on top
41-42 merge; loan on top
43-44 merge; loan_history on top
45-95 merge; loan on top
96-194 loop; loan on top
195-197 loop; loan_history on top

It all makes a lot of sense and is very very fast *until* you hit time
period 195, when it all goes to hell. Here are the row count estimates
and actual values for various time periods in the above ranges (there
are 596 rows in loan for the deal I used in my test queries):

time_period top est. bottom est. top actual bottom actual
40 647 1 596 596
42 1089 1450 596 597
44 2785 1089 596 597
95 1089 145187 596 64429
194 1089 2265 2265 1
197 1 1 6,570,403 2265

I'm sure you see the problem: the estimates for time_period 197 are
nuts.

Some of the other comparisons between estimates and actual values look
a little funny to me, but there's one thing I noticed that left me
completely perplexed: I thought the optimizer was basing its plan
purely on the number of rows, but time period 197 currently has *fewer
rows* than time period 194 (6,570,403 vs 8,260,954), yet time period
197 uses the same join method as 195 & 196, which have 8,261,776 and
8,273,743 rows, respectively. So how is it picking the broken plan, and
why? Is there any way I can tell if the stats are screwed up besides
rebuilding them and seeing if the problem goes away? It seems like
stats were built that essentially say time periods 195, 196 and 197
have almost no rows, since the plan is the same as that used in the
earliest two time periods - and the rows for these time periods were
added after the initial load of the table, when I was doing things to
the table that almost certainly caused stats to be updated. I think I'm
going to look into stats for loan_history, and see if I can get these
plans back on track. But how can I avoid this in the future? Do I have
to update stats every time I add a new time period? Is this normal?

Thanks everybody for all your help!
Seth|||Ok, well, I'm a dunce: dbcc show_statistics (loan_history,
pk_loan_history) shows lovely stats for all values of time_period up to
194, then nothing. I checked the stats a while ago, when I was under
the impression that the optimizer was generating the wrong plan for all
time periods, and I didn't notice this then.

UpdatedRowsRows SampledStepsDensityAverage key length
Jul 1 2005 10:40AM 2810359682810359761560.022.0

I find it strange that the number of rows sampled is greater than the
number of rows in the table.

But why hasn't it updated stats? The index is clustered, so I can't
even set statistics norecompute.

Thanks,
Seth|||Ok, I found what I was looking for: autostats is triggered after 20% of
the rows in a table are updated, and this is why there are simply no
stats for the last few time periods. About 56 million updates need to
happen before autostats runs, and the rowcount is currently at 24
million, so time to update statistics manually.

Thanks again,
Seth|||sql_server_2000_user (sethpurcell@.comcast.net) writes:
> As an experiment, I built a table identical to loan_history but with
> the primary key reversed (still clustered). I populated the table with
> 10% of the original loan_history table, and here is the simple join and
> its execution plan:

Judging from that plan, you somehow failed to reverse the key. I mean:

> Seek(OBJECT:([LoanPerformance].[dbo].[loan_history].[PK_loan_history]
> AS [lh]), SEEK:([lh].[TIME_PERIOD]=81) ORDERED FORWARD)

would not be possible with a reverse key.

Anyway, it seems that you have performed one hell of a job to track
this down yourself, and found the answer. I must say that I'm full of
admiration for your feat. It's very unusual to see someone put so
much work into his problem - and also report back to the newsgroup.
Big thanks for doing this!

> It seems like stats were built that essentially say time periods 195,
> 196 and 197 have almost no rows, since the plan is the same as that used
> in the earliest two time periods - and the rows for these time periods
> were added after the initial load of the table, when I was doing things
> to the table that almost certainly caused stats to be updated. I think
> I'm going to look into stats for loan_history, and see if I can get
> these plans back on track. But how can I avoid this in the future? Do I
> have to update stats every time I add a new time period? Is this
> normal?

Yes, as you have found this is kind of normal.

There is a problem with contiguously growing keys. In our system
a colleague of mine has set up a job that reindexes tables. But to
avoid that this job takes too long time to run, he uses SHOWCONTIG,
and if fragmentation is moderate, he skips the table.

Some time ago, one of our customers reported that one certain function
was slow, and I tracked it down to stored procedure. The customer
had just gotten 8.10 of our system, and the procedure was indeed changed
in that version. But, the strange thing was that the query that was
slow had not changed. And as it was a plain join of four tables, I
didn't really feel like tweaking it.

I found with the stats_date() function that statistics for one of tables
was a tad old, and an UPDATE STATISTICS WITH FULLSCAN on this table and
another resovled the issue. These tables both have monotonically growing
(or almost monotonically) primary keys that are clustered. The good
thing with that is that you don't get fragmentation. The bad thing is
that statistics may not be updated, if you skip tables when you run
you maintenance job. So my colleauge is now finding a strategy for how
run UPDATE STATISTICS on the tables he does not reindex.

But why had this happened with the new version? Well, since the procedure
had changed, the query plan was flushed from the cache, new balls - and,
oops bad plan.

And now I know why your new index opened Pandora's box: that caused
the query plans for the query to be flushed. So until you created the
index, you had the old statistics - and an old query to go with it.

Thus, it seems that if you just don't do anything, and don't rock the
boat nothing will happen. But since the most trivial thing to flush a
plan from the cache is a server reboot, it's a fragile strategy.

So you should schedule UPDATE STATISTICS with some frequency on the
table. It does not have to be WITH FULLSCAN, but you may need a
higher sample percent than the default. Since you know the presumptions,
you should be able to monitor this, and find out what works and what
does not.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||I'm glad to hear you worked this out. Thanks
very much for posting so much detail, and the
resolution. That's nice for those of us reading
now and will also help anyone with a similar
problem who finds this thread in the future.

SK

sql_server_2000_user wrote:
> Ok, I found what I was looking for: autostats is triggered after 20% of
> the rows in a table are updated, and this is why there are simply no
> stats for the last few time periods. About 56 million updates need to
> happen before autostats runs, and the rowcount is currently at 24
> million, so time to update statistics manually.
> Thanks again,
> Seth|||Looks like you caught another one of my mistakes - when I cut and
pasted my query I forgot to change the table name to loan_history2. I
investigated this (with the correct query this time) and the join
method the optimizer picks is the nested loops with two index seeks,
independent of the chosen time period. This makes sense, because the
number of rows for any given value of the first column of the index
(now exloan_id) is now basically constant, not varying by four orders
of magnitude like the time-period based index is.

Ah, the query plan cache - this is another thing I poked around in
early on and then decided it wasn't the problem, hahaha. Thanks for
explaining this to me, it makes perfect sense now. It would have been
awful to have had this happen for the first time after some maintenance
reboot of the server - I'm glad it happened beforehand and I'm on top
of it now. I will consider where to go from here, as far as reindexing
to defragment, setting up a plan to update statistics after loading new
data, etc.

The statistics are updating as I write this - and have been for the
past few hours.

Thanks for everything,
Seth|||sql_server_2000_user (sethpurcell@.comcast.net) writes:
> Ah, the query plan cache - this is another thing I poked around in
> early on and then decided it wasn't the problem, hahaha.

I also feel kind of stupid that I did not think of this earlier. That
would have led us faster to the outdated statistics.

Oh well, while it's a whole lot of work, exercises like this one
are good lessons for the future. (What I didn't say in my war story
was that a second customer ran into the same problem when they later
got 8.10. That time I could just tell our helpdesk folks "try this".

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp