Index question

Hello,
I have a telephone log table that tracks the following: (phone number, time
of day, duration of call). This table that is populated with over 1/2 a
million telephone numbers per day. My question is what is the appropriate
way of indexing a table with this volume of inserts?
Thanks in advance,
sck10
Something like that usually does well with a clustered index on the datetime
but it depends on how you need to access the data. What are your typical
queries like?
Andrew J. Kelly SQL MVP
"sck10" <sck10@.online.nospam> wrote in message
news:uTER80kzEHA.3656@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I have a telephone log table that tracks the following: (phone number,
> time
> of day, duration of call). This table that is populated with over 1/2 a
> million telephone numbers per day. My question is what is the appropriate
> way of indexing a table with this volume of inserts?
> --
> Thanks in advance,
> sck10
>
|||Hi sck10,
Addtionally to MVP Andrew J. Kelly's suggestions, you would also make the
decision based on Index Tuning Wizard in Query Analyzer. Here is some
guidelines for you
Index Tuning Wizard for Microsoft SQL Server 2000
http://msdn.microsoft.com/library/de...us/dnsql2k/htm
l/itwforsql.asp
INF: Index Tuning Wizard Best Practices
http://support.microsoft.com/kb/311826
Support WebCast: Effective Indexing and Statistics with SQL 2000
http://support.microsoft.com/kb/325024
Hope this helps and if you have any questions or concerns, don't hesitate
to let me know. We are always here to be of assistance!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||It depends on whether this data is being inserted in real time
or if the data is loaded from provider files, and also on whether
the data is constantly being queried, or if it is only accessed for
occasional reports and bill generation. If it's bulk loaded
from provider files, or if it's processed only for reports and
bills, you have more flexibility, and can index it in a way
that's useful to what you do with the data. If it's loaded
in real time, but not queried in real time, it could even be
appropriate to load it into an unindexed table, or into
a table with an artificial clustered key like a column with
the identity property. If the hardware cannot spit
out the same call twice, you don't need a natural key while
the data is loading.
Can you say more about where this data comes from,
what kinds of queries you run against it, and whether
there are typically few or many connections querying
the data at once?
Steve Kass
Drew University
sck10 wrote:

>Hello,
>I have a telephone log table that tracks the following: (phone number, time
>of day, duration of call). This table that is populated with over 1/2 a
>million telephone numbers per day. My question is what is the appropriate
>way of indexing a table with this volume of inserts?
>
>

Index question

Hello,
I have a telephone log table that tracks the following: (phone number, time
of day, duration of call). This table that is populated with over 1/2 a
million telephone numbers per day. My question is what is the appropriate
way of indexing a table with this volume of inserts?
Thanks in advance,
sck10Something like that usually does well with a clustered index on the datetime
but it depends on how you need to access the data. What are your typical
queries like?
Andrew J. Kelly SQL MVP
"sck10" <sck10@.online.nospam> wrote in message
news:uTER80kzEHA.3656@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I have a telephone log table that tracks the following: (phone number,
> time
> of day, duration of call). This table that is populated with over 1/2 a
> million telephone numbers per day. My question is what is the appropriate
> way of indexing a table with this volume of inserts?
> --
> Thanks in advance,
> sck10
>|||Hi sck10,
Addtionally to MVP Andrew J. Kelly's suggestions, you would also make the
decision based on Index Tuning Wizard in Query Analyzer. Here is some
guidelines for you
Index Tuning Wizard for Microsoft SQL Server 2000
http://msdn.microsoft.com/library/d...-us/dnsql2k/htm
l/itwforsql.asp
INF: Index Tuning Wizard Best Practices
http://support.microsoft.com/kb/311826
Support WebCast: Effective Indexing and Statistics with SQL 2000
http://support.microsoft.com/kb/325024
Hope this helps and if you have any questions or concerns, don't hesitate
to let me know. We are always here to be of assistance!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||It depends on whether this data is being inserted in real time
or if the data is loaded from provider files, and also on whether
the data is constantly being queried, or if it is only accessed for
occasional reports and bill generation. If it's bulk loaded
from provider files, or if it's processed only for reports and
bills, you have more flexibility, and can index it in a way
that's useful to what you do with the data. If it's loaded
in real time, but not queried in real time, it could even be
appropriate to load it into an unindexed table, or into
a table with an artificial clustered key like a column with
the identity property. If the hardware cannot spit
out the same call twice, you don't need a natural key while
the data is loading.
Can you say more about where this data comes from,
what kinds of queries you run against it, and whether
there are typically few or many connections querying
the data at once?
Steve Kass
Drew University
sck10 wrote:

>Hello,
>I have a telephone log table that tracks the following: (phone number, time
>of day, duration of call). This table that is populated with over 1/2 a
>million telephone numbers per day. My question is what is the appropriate
>way of indexing a table with this volume of inserts?
>
>sql

Index question

Hello,
I have a telephone log table that tracks the following: (phone number, time
of day, duration of call). This table that is populated with over 1/2 a
million telephone numbers per day. My question is what is the appropriate
way of indexing a table with this volume of inserts?
--
Thanks in advance,
sck10Something like that usually does well with a clustered index on the datetime
but it depends on how you need to access the data. What are your typical
queries like?
--
Andrew J. Kelly SQL MVP
"sck10" <sck10@.online.nospam> wrote in message
news:uTER80kzEHA.3656@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I have a telephone log table that tracks the following: (phone number,
> time
> of day, duration of call). This table that is populated with over 1/2 a
> million telephone numbers per day. My question is what is the appropriate
> way of indexing a table with this volume of inserts?
> --
> Thanks in advance,
> sck10
>|||Hi sck10,
Addtionally to MVP Andrew J. Kelly's suggestions, you would also make the
decision based on Index Tuning Wizard in Query Analyzer. Here is some
guidelines for you
Index Tuning Wizard for Microsoft SQL Server 2000
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/htm
l/itwforsql.asp
INF: Index Tuning Wizard Best Practices
http://support.microsoft.com/kb/311826
Support WebCast: Effective Indexing and Statistics with SQL 2000
http://support.microsoft.com/kb/325024
Hope this helps and if you have any questions or concerns, don't hesitate
to let me know. We are always here to be of assistance!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||It depends on whether this data is being inserted in real time
or if the data is loaded from provider files, and also on whether
the data is constantly being queried, or if it is only accessed for
occasional reports and bill generation. If it's bulk loaded
from provider files, or if it's processed only for reports and
bills, you have more flexibility, and can index it in a way
that's useful to what you do with the data. If it's loaded
in real time, but not queried in real time, it could even be
appropriate to load it into an unindexed table, or into
a table with an artificial clustered key like a column with
the identity property. If the hardware cannot spit
out the same call twice, you don't need a natural key while
the data is loading.
Can you say more about where this data comes from,
what kinds of queries you run against it, and whether
there are typically few or many connections querying
the data at once?
Steve Kass
Drew University
sck10 wrote:
>Hello,
>I have a telephone log table that tracks the following: (phone number, time
>of day, duration of call). This table that is populated with over 1/2 a
>million telephone numbers per day. My question is what is the appropriate
>way of indexing a table with this volume of inserts?
>
>

Index problem

I have a table, which contains address information,
have non-clustered index on phone no, search on phone
number was very quick, now users requested that search on
address be possible too, I created non-clustered index on
address which speeds up search by address but search by
phone number is extremely slow, query plan shows that
optimizer is using this new index instead of index on
phone number even during phone search, I updated stats
with full scan and still same result.
Dbcc show_statistics shows better selectivity for index on
address than phone number.
It is a very complicated dynamic query which I cannot
force optimizer to use specific index.
Any help will be appreciatedI would play with "set statistics IO on" to see your logical reads.
How are they asking for the address and phone number? (SQL syntax)
When they only ask for phone number how do they ask this? For example, if I
said
select phonenumber from table where street = 'main'
SQL would not use your nonclustered index on phone(unless you had a
clustered index on street)
You might consider covering non-clustered indexes.
HTH
--
Ray Higdon MCSE, MCDBA, CCNA
--
"Sam" <sam.moayedi@.moneymanagement.org> wrote in message
news:044b01c39350$a27e2a70$a401280a@.phx.gbl...
> I have a table, which contains address information,
> have non-clustered index on phone no, search on phone
> number was very quick, now users requested that search on
> address be possible too, I created non-clustered index on
> address which speeds up search by address but search by
> phone number is extremely slow, query plan shows that
> optimizer is using this new index instead of index on
> phone number even during phone search, I updated stats
> with full scan and still same result.
> Dbcc show_statistics shows better selectivity for index on
> address than phone number.
> It is a very complicated dynamic query which I cannot
> force optimizer to use specific index.
> Any help will be appreciated
>|||The sp goes something like this
select * from table where phoneno='xxxxxxxxx'
It is more complicated than|||Then your NC index on phone number definitely won't help, it would make no
sense to go out and find phone numbers only to redirect back to the table,
did you say you had a clustered index? Again, I would play with my indexes
using "set statistics IO on"
If you were saying
select phone_no from table where phone_no = 'blah'
Then your NCI would act as a covering index and SQL would naturally go to
the index and not touch the table.
HTH
--
Ray Higdon MCSE, MCDBA, CCNA
--
"Sam" <sam.moayedi@.moneymanagement.org> wrote in message
news:05f701c3935f$d57f1ec0$a401280a@.phx.gbl...
> The sp goes something like this
> select * from table where phoneno='xxxxxxxxx'
> It is more complicated than|||Sam
Have you checked for fragmentation on the phone number
index? Although it mostly tends to affect clustered
indexes, I have seen very similar problems with fragmented
non-clustered indexes.
Regards
Johnsql