Index tuning under heavy load...

I am trying to find the best way to tune/defrag the indexes on a database
which stays under heavy load 24/7/365
About 1-5 million records added daily...By the end of the day query
performance decreses dramatically.
DBCC DBREINDEX does the job the best but it is an OFFLINE operation and is
unacceptable in my case as the table becomes offline and users get hung...
ALTER INDEX REBUILD (WITH ONLINE) works ok, still queries are running slower
during this operation and it does not do as good of a job as DBREINDEX.
What are my options? Is there a solution to this...?I should add that we are on SQL Server 2005
"Michael Kansky" <mike@.zazasoftware.com> wrote in message
news:eAiuJRM6HHA.464@.TK2MSFTNGP02.phx.gbl...
>I am trying to find the best way to tune/defrag the indexes on a database
>which stays under heavy load 24/7/365
> About 1-5 million records added daily...By the end of the day query
> performance decreses dramatically.
> DBCC DBREINDEX does the job the best but it is an OFFLINE operation and is
> unacceptable in my case as the table becomes offline and users get hung...
> ALTER INDEX REBUILD (WITH ONLINE) works ok, still queries are running
> slower during this operation and it does not do as good of a job as
> DBREINDEX.
> What are my options? Is there a solution to this...?
>|||If you are on 2005 you should be using ALTER INDEX not DBCC xxx. Reindexing
is a very resource intensive operation and if you want to do this in a 24x7
operation you need to have the hardware to support it or performance will
suffer. But I would argue that your fill factors are not properly set to
limit fragmentation if by the end of the day performance suffers from
fragmentation. But fragmentation should not impact a properly tuned OLTP
system that much anyway. If you do a lot of scans it can hurt you but you
should find out why you are scanning and address that. Perhaps partitioning
is called for here as well. If you can partition such that most of the new
rows are in a different partition than the rest you won't need to rebuild
all the indexes.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Michael Kansky" <mike@.zazasoftware.com> wrote in message
news:%23LrM9cM6HHA.3940@.TK2MSFTNGP05.phx.gbl...
>I should add that we are on SQL Server 2005
>
> "Michael Kansky" <mike@.zazasoftware.com> wrote in message
> news:eAiuJRM6HHA.464@.TK2MSFTNGP02.phx.gbl...
>>I am trying to find the best way to tune/defrag the indexes on a database
>>which stays under heavy load 24/7/365
>> About 1-5 million records added daily...By the end of the day query
>> performance decreses dramatically.
>> DBCC DBREINDEX does the job the best but it is an OFFLINE operation and
>> is unacceptable in my case as the table becomes offline and users get
>> hung...
>> ALTER INDEX REBUILD (WITH ONLINE) works ok, still queries are running
>> slower during this operation and it does not do as good of a job as
>> DBREINDEX.
>> What are my options? Is there a solution to this...?
>|||I submit that it isn't index frag that is hurting here, but rather the
statistics. New records won't be reflected in the stats (assuming a large
table in which 1-5M rows doesn't trigger an automatic stats update). Thus
queries involving the newly inserted rows won't have optimal query plans
(such as index seeks).
Set up a job to refresh your stats several times throughout the day. Be
careful of the type of scan performed. You need the scan to go quickly.
For those indexes that receive values throughout the range of the index
(like on LastName for example), fragmentation can become an issue. Pick a
reasonable fillfactor to avoid lots of page splits between rebuilds.
Picking this number is part art, but mostly science.
--
TheSQLGuru
President
Indicium Resources, Inc.
"Michael Kansky" <mike@.zazasoftware.com> wrote in message
news:eAiuJRM6HHA.464@.TK2MSFTNGP02.phx.gbl...
>I am trying to find the best way to tune/defrag the indexes on a database
>which stays under heavy load 24/7/365
> About 1-5 million records added daily...By the end of the day query
> performance decreses dramatically.
> DBCC DBREINDEX does the job the best but it is an OFFLINE operation and is
> unacceptable in my case as the table becomes offline and users get hung...
> ALTER INDEX REBUILD (WITH ONLINE) works ok, still queries are running
> slower during this operation and it does not do as good of a job as
> DBREINDEX.
> What are my options? Is there a solution to this...?
>|||I think you are absolutely right...
And i do need a really fast scan while updating stats because i think it
places share locks on the table..
Will this be the fastest scan i can achieve:
UPDATE STATISTICS Database.Table
WITH SAMPLE 5 PERCENT;
Thank you,
michael
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:uNHfyoX6HHA.5160@.TK2MSFTNGP05.phx.gbl...
>I submit that it isn't index frag that is hurting here, but rather the
>statistics. New records won't be reflected in the stats (assuming a large
>table in which 1-5M rows doesn't trigger an automatic stats update). Thus
>queries involving the newly inserted rows won't have optimal query plans
>(such as index seeks).
> Set up a job to refresh your stats several times throughout the day. Be
> careful of the type of scan performed. You need the scan to go quickly.
> For those indexes that receive values throughout the range of the index
> (like on LastName for example), fragmentation can become an issue. Pick a
> reasonable fillfactor to avoid lots of page splits between rebuilds.
> Picking this number is part art, but mostly science.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "Michael Kansky" <mike@.zazasoftware.com> wrote in message
> news:eAiuJRM6HHA.464@.TK2MSFTNGP02.phx.gbl...
>>I am trying to find the best way to tune/defrag the indexes on a database
>>which stays under heavy load 24/7/365
>> About 1-5 million records added daily...By the end of the day query
>> performance decreses dramatically.
>> DBCC DBREINDEX does the job the best but it is an OFFLINE operation and
>> is unacceptable in my case as the table becomes offline and users get
>> hung...
>> ALTER INDEX REBUILD (WITH ONLINE) works ok, still queries are running
>> slower during this operation and it does not do as good of a job as
>> DBREINDEX.
>> What are my options? Is there a solution to this...?
>|||Be a little careful of too low of a sample. If the problem is due to rows
that the optimizer doesn't know about you may get similar results with only
a 5% sample. This is usually a try and see, then adjust methodology.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Michael Kansky" <mike@.zazasoftware.com> wrote in message
news:OD5EkRY6HHA.5844@.TK2MSFTNGP02.phx.gbl...
>I think you are absolutely right...
> And i do need a really fast scan while updating stats because i think it
> places share locks on the table..
> Will this be the fastest scan i can achieve:
> UPDATE STATISTICS Database.Table
> WITH SAMPLE 5 PERCENT;
> Thank you,
> michael
>
> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
> news:uNHfyoX6HHA.5160@.TK2MSFTNGP05.phx.gbl...
>>I submit that it isn't index frag that is hurting here, but rather the
>>statistics. New records won't be reflected in the stats (assuming a large
>>table in which 1-5M rows doesn't trigger an automatic stats update). Thus
>>queries involving the newly inserted rows won't have optimal query plans
>>(such as index seeks).
>> Set up a job to refresh your stats several times throughout the day. Be
>> careful of the type of scan performed. You need the scan to go quickly.
>> For those indexes that receive values throughout the range of the index
>> (like on LastName for example), fragmentation can become an issue. Pick
>> a reasonable fillfactor to avoid lots of page splits between rebuilds.
>> Picking this number is part art, but mostly science.
>> --
>> TheSQLGuru
>> President
>> Indicium Resources, Inc.
>> "Michael Kansky" <mike@.zazasoftware.com> wrote in message
>> news:eAiuJRM6HHA.464@.TK2MSFTNGP02.phx.gbl...
>>I am trying to find the best way to tune/defrag the indexes on a database
>>which stays under heavy load 24/7/365
>> About 1-5 million records added daily...By the end of the day query
>> performance decreses dramatically.
>> DBCC DBREINDEX does the job the best but it is an OFFLINE operation and
>> is unacceptable in my case as the table becomes offline and users get
>> hung...
>> ALTER INDEX REBUILD (WITH ONLINE) works ok, still queries are running
>> slower during this operation and it does not do as good of a job as
>> DBREINDEX.
>> What are my options? Is there a solution to this...?
>>
>

Index tree

I can understand the number of table records will affect the width of the
tree. But why it also affect the depth of the tree ? Or how the key size
will afftect depth and width of the index tree ?
Alan,
The b-tree will increase its depth to improve the query time by reducing
the number of I/Os. It does this by shortening the path to the data, or
leaf node.
Let's say you have an index on surname. The more data in the table, the
greater the depth of the b-tree will result in fewer I/Os. I'm finding
this topic difficult to describe in a newsgroup posting, so hopefully
the following articles on B-Trees will help you:
B-tree algorithms
http://www.semaphorecorp.com/btp/algo.html
Binary tree
http://en.wikipedia.org/wiki/Binary_tree
binary tree
http://planetmath.org/encyclopedia/BinaryTree.html
I think B-Tree actually stands for Balanced Tree, not Binary Tree; but
the two terms are often used synonymously.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Alan wrote:
> I can understand the number of table records will affect the width of the
> tree. But why it also affect the depth of the tree ? Or how the key size
> will afftect depth and width of the index tree ?
>
|||On Tue, 7 Dec 2004 15:03:09 +1100, Alan wrote:

>I can understand the number of table records will affect the width of the
>tree. But why it also affect the depth of the tree ? Or how the key size
>will afftect depth and width of the index tree ?
Hi Alan,
I'll use an example to explain. Let's assume you have a nonclustered index
with 800 bytes in the indexed columns and another 800 bytes in the
clustered key.
The leaf pages store both the indexed values and the corresponding values
in the clustered index (as locator to the actual row). Other pages (root,
intermediate) store only the indexed values. Since each page is about 8K,
a leaf page holds values for 5 rows; other pages have pointers for 10
rows.
If the number of rows in the table is 10, we need two leaf pages (assuming
no empty space, which will not always be the case in practice) to hold all
these rows. The root page will have the indexed values of the first row on
leaf page 1 and the first row on page 2; the rest of the root page remains
empty. This B-tree has depth 2 (root is level 1; leaf at level 2).
If we add another 40 rows for a total of 50, we need 10 leaf pages. The
root page will have the indexed values of the first row on each of these
10 leaf pages and no room to spare.
One extra row, bringing the total to 51, means we now need 11 leaf pages.
Since the root page can only point to max 10 pages, we need to add a
level. The new B-tree will have one root, two intermediate (level-2) and
11 data pages. One intermediate page will have the indexed values of the
first row on the first 5 or 6 leaf pages; the other intermediate page has
the indexed values of the first row on the remaining leaf pages; the root
page will only hold the indexed values of the first row of the two
intermediate pages. Note that we now have depth 3: root at level 1,
intermediate at level 2 and leaf at level 3.
We can now continue to add rows. When there are 500 rows, there are 100
leaf pages, 10 intermediate pages (each pointing to 10 leaf pages) and 1
root page (pointing to the 10 intermediate pages). All these pages are
completely full: when the 501st row is added, another level has to be
added to the index and it is now at depth 4.
The above shows how number of rows affects the B-tree depth. To see how
key size affects B-tree depth as well, imagine what happens if the indexed
columns are not 800 but 80 bytes: now you can store the indexed values of
not 10 but 100 rows in non-leaf pages. For the leaf pages, the capacity
would increase to (8K / (80 + 800)) = 9 rows. If the size of the clustered
index would decrease as well, this number would rise even further. This of
course means that you can add more rows until all leaf, intermediate and
root pages are full and another level has to be added.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
sql

Index tree

I can understand the number of table records will affect the width of the
tree. But why it also affect the depth of the tree ? Or how the key size
will afftect depth and width of the index tree ?Alan,
The b-tree will increase its depth to improve the query time by reducing
the number of I/Os. It does this by shortening the path to the data, or
leaf node.
Let's say you have an index on surname. The more data in the table, the
greater the depth of the b-tree will result in fewer I/Os. I'm finding
this topic difficult to describe in a newsgroup posting, so hopefully
the following articles on B-Trees will help you:
B-tree algorithms
http://www.semaphorecorp.com/btp/algo.html
Binary tree
http://en.wikipedia.org/wiki/Binary_tree
binary tree
http://planetmath.org/encyclopedia/BinaryTree.html
I think B-Tree actually stands for Balanced Tree, not Binary Tree; but
the two terms are often used synonymously.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Alan wrote:
> I can understand the number of table records will affect the width of the
> tree. But why it also affect the depth of the tree ? Or how the key size
> will afftect depth and width of the index tree ?
>|||On Tue, 7 Dec 2004 15:03:09 +1100, Alan wrote:
>I can understand the number of table records will affect the width of the
>tree. But why it also affect the depth of the tree ? Or how the key size
>will afftect depth and width of the index tree ?
Hi Alan,
I'll use an example to explain. Let's assume you have a nonclustered index
with 800 bytes in the indexed columns and another 800 bytes in the
clustered key.
The leaf pages store both the indexed values and the corresponding values
in the clustered index (as locator to the actual row). Other pages (root,
intermediate) store only the indexed values. Since each page is about 8K,
a leaf page holds values for 5 rows; other pages have pointers for 10
rows.
If the number of rows in the table is 10, we need two leaf pages (assuming
no empty space, which will not always be the case in practice) to hold all
these rows. The root page will have the indexed values of the first row on
leaf page 1 and the first row on page 2; the rest of the root page remains
empty. This B-tree has depth 2 (root is level 1; leaf at level 2).
If we add another 40 rows for a total of 50, we need 10 leaf pages. The
root page will have the indexed values of the first row on each of these
10 leaf pages and no room to spare.
One extra row, bringing the total to 51, means we now need 11 leaf pages.
Since the root page can only point to max 10 pages, we need to add a
level. The new B-tree will have one root, two intermediate (level-2) and
11 data pages. One intermediate page will have the indexed values of the
first row on the first 5 or 6 leaf pages; the other intermediate page has
the indexed values of the first row on the remaining leaf pages; the root
page will only hold the indexed values of the first row of the two
intermediate pages. Note that we now have depth 3: root at level 1,
intermediate at level 2 and leaf at level 3.
We can now continue to add rows. When there are 500 rows, there are 100
leaf pages, 10 intermediate pages (each pointing to 10 leaf pages) and 1
root page (pointing to the 10 intermediate pages). All these pages are
completely full: when the 501st row is added, another level has to be
added to the index and it is now at depth 4.
The above shows how number of rows affects the B-tree depth. To see how
key size affects B-tree depth as well, imagine what happens if the indexed
columns are not 800 but 80 bytes: now you can store the indexed values of
not 10 but 100 rows in non-leaf pages. For the leaf pages, the capacity
would increase to (8K / (80 + 800)) = 9 rows. If the size of the clustered
index would decrease as well, this number would rise even further. This of
course means that you can add more rows until all leaf, intermediate and
root pages are full and another level has to be added.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

index question

Hi,
I have two tables -- A & B, which have primary key and
content lots of records respectively. B table has
foreign key refer to A table. If I create a index for
this foreign key in table B, will it improve performance
when I join these two tables in my query? Any suggestion
to improve the performance during join?
Thank you.
YulingYes It may improve a performance
Also look at join hints on BOL.
"Yuling" <ytu@.creativelabs.com> wrote in message
news:044601c35ade$51d3cb20$a601280a@.phx.gbl...
> Hi,
> I have two tables -- A & B, which have primary key and
> content lots of records respectively. B table has
> foreign key refer to A table. If I create a index for
> this foreign key in table B, will it improve performance
> when I join these two tables in my query? Any suggestion
> to improve the performance during join?
> Thank you.
> Yuling|||You should generally index all primary and foreign keys when doing joins...
If there are where clauses, you may see performance improvements if you
create non-clustered index on one of the highly selective where clause
criteria.
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it community
of SQL Server professionals.
www.sqlpass.org
"Yuling" <ytu@.creativelabs.com> wrote in message
news:044601c35ade$51d3cb20$a601280a@.phx.gbl...
> Hi,
> I have two tables -- A & B, which have primary key and
> content lots of records respectively. B table has
> foreign key refer to A table. If I create a index for
> this foreign key in table B, will it improve performance
> when I join these two tables in my query? Any suggestion
> to improve the performance during join?
> Thank you.
> Yuling

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 joined columns

I have and joined between two tables like this:
......TB1 join TB2 on TB1.col1=TB2.col3 and TB1 .clo2=TB2.Col4
There a lot of records in TB2 ,so I wondered to make an index on Col3 and
Col4 of TB2.Now my question is that according to the join above it's better
to have a compound index on col3,col4 or each inividually?
Thanks for your help.
RayHi Ray,
I recommend you to create a composite Non clustered index on Col1 and Col2
of Table 1 , Col3 and Col4 of Table 2.
This index will be very good if you have a clustered index on Key column.
You can also use the Index tuning Wizard for Index recommendations. As well
see the Logical and Physical reads, Cpu usage before and
after the creation of index.
Thanks
Hari
SQL Server MVP
"RayAll" <RayAll@.microsft.com> wrote in message
news:%238E3mJjOFHA.3296@.TK2MSFTNGP15.phx.gbl...
>I have and joined between two tables like this:
> ......TB1 join TB2 on TB1.col1=TB2.col3 and TB1 .clo2=TB2.Col4
>
> There a lot of records in TB2 ,so I wondered to make an index on Col3 and
> Col4 of TB2.Now my question is that according to the join above it's
> better to have a compound index on col3,col4 or each inividually?
> Thanks for your help.
> Ray
>

Index gone in merge replication

I have a merge replication and I inserted 5000 records in the main publisher
and they were inserted in the alternate publisher and in 1 subscriber, but
replicating in the other 2 subscribers is very slow is taking forever. And
sometimes it fails by timeout. I see the server and sql is consuming 99%
because he's trying to insert these records and goes 100 by 100.
I looked at the table structure and the only thing different I see is the
index_2109250569 for rowguid field. Is this causing the problem that
replicating the records is taking hours? May I create this missing index in
the table without any impact for replication?
Thanks in advance
Jennyfer
I am not so sure it wouldn't have an impact (it might have an impact when
you are reinitializing the replication or things like that).
Anyways, I would create it to see if that improves the performance.
The worst case scenario if that the agent tries in the future to create the
index and it can't (because it is already there) and then it fails saying
that (and the solution would be to drop the index).
Jos.
"Jennyfer Barco" <pdwhitt@.nospam.wdsinc.com> wrote in message
news:uG2bY4h5FHA.724@.TK2MSFTNGP14.phx.gbl...
> I have a merge replication and I inserted 5000 records in the main
publisher
> and they were inserted in the alternate publisher and in 1 subscriber, but
> replicating in the other 2 subscribers is very slow is taking forever. And
> sometimes it fails by timeout. I see the server and sql is consuming 99%
> because he's trying to insert these records and goes 100 by 100.
> I looked at the table structure and the only thing different I see is the
> index_2109250569 for rowguid field. Is this causing the problem that
> replicating the records is taking hours? May I create this missing index
in
> the table without any impact for replication?
> Thanks in advance
> Jennyfer
>

Index Fragmentation and Datatype Issue

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

Index Fragmentation and Datatype Issue

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

Index Fragmentation and Datatype Issue

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

Index Fragmenation

I'm having trouble with slow performance due to index fragmentation. I'll insert 20000 records into a table and then have index fragmentation of like 67%! The database is so slow query this data back (over 3 minutes!). After rebuilding the index on the primary key (the only index) the same query takes less than 2 seconds. I'm using GUID as my primary key and I have recently switched from using NEWID() to using the new NEWSEQUENTAILID() to generate them. Can anyone suggest why I'm still having such a hard time with fragmentation?Unless you really NEED a GUID because you need an id that HAS to be unique across an entire network, I'd use an integer or bigint. There are a lot of articles out there regarding the performance hit associated with GUID's primarily due to page splits if memory serves. Is your primary key also a clustered index?
|||Yes it is a clustered index. I thought the problem with GUIDs in general is that they are pseudo-random. That is why I went to the newsequentialid() function that generates sequential guids across a machine.|||Index fragementation caused by using GUIDs in indexed columns shouldn't be causing the issues you're seeing.

While it is certainly true that you'll see very high index fragmentation for these columns (our production DB often has 95%+ fragmentation), this isn't necessarily going to kill your performance. We did extensive performance comparisons before decided to go with nearly 100% GUIDs are primary keys. There was certainly a perf difference, but it was negligible overall.

In fact, INSERT performance may actually increase thanks to the fact that disk hot spots are far less common.

I would examine the query plans before/after your defragmentation/rebuild your index. I think something else must be going on here.|||Its very likely that you have out of date statistics on the table that is giving you a bad query plan after the inserts. Rebuilding the index automatically updates statistics. Try just running UPDATE STATISTICS TableName after the INSERT and see if that gives you a better query plan.

Index Fill Ratio

Iâ'm trying to copy a very large amount of data about 10,000,000 records from
one table to another using DTS. And while Iâ'm doing the task although I can
select data from the destination table using the sql query analyzer, I canâ't
execute the same select statement using ADO connection through a web site.
Please advise A.S.A.P
Thanks and Best Regards,Hi
I'd doing such tasks at the end of the day where workload is off.
See TRANSACTION ISOLATION LEVEL in the BOL
"Ehab ELGEDDAWY" <Ehab ELGEDDAWY@.discussions.microsoft.com> wrote in message
news:4287D94F-12C2-40F3-8CFC-DD443400FB22@.microsoft.com...
> I?m trying to copy a very large amount of data about 10,000,000 records
> from
> one table to another using DTS. And while I?m doing the task although I
> can
> select data from the destination table using the sql query analyzer, I can?t
> execute the same select statement using ADO connection through a web site.
> Please advise A.S.A.P
> Thanks and Best Regards,
>

Index Fill Ratio

I’m trying to copy a very large amount of data about 10,000,000 records from
one table to another using DTS. And while I’m doing the task although I can
select data from the destination table using the sql query analyzer, I can’t
execute the same select statement using ADO connection through a web site.
Please advise A.S.A.P
Thanks and Best Regards,
Hi
I'd doing such tasks at the end of the day where workload is off.
See TRANSACTION ISOLATION LEVEL in the BOL
"Ehab ELGEDDAWY" <Ehab ELGEDDAWY@.discussions.microsoft.com> wrote in message
news:4287D94F-12C2-40F3-8CFC-DD443400FB22@.microsoft.com...
> Im trying to copy a very large amount of data about 10,000,000 records
> from
> one table to another using DTS. And while Im doing the task although I
> can
> select data from the destination table using the sql query analyzer, I cant
> execute the same select statement using ADO connection through a web site.
> Please advise A.S.A.P
> Thanks and Best Regards,
>

Index Fill Ratio

I’m trying to copy a very large amount of data about 10,000,000 records fr
om
one table to another using DTS. And while I’m doing the task although I c
an
select data from the destination table using the sql query analyzer, I can
t
execute the same select statement using ADO connection through a web site.
Please advise A.S.A.P
Thanks and Best Regards,Hi
I'd doing such tasks at the end of the day where workload is off.
See TRANSACTION ISOLATION LEVEL in the BOL
"Ehab ELGEDDAWY" <Ehab ELGEDDAWY@.discussions.microsoft.com> wrote in message
news:4287D94F-12C2-40F3-8CFC-DD443400FB22@.microsoft.com...
> Im trying to copy a very large amount of data about 10,000,000 records
> from
> one table to another using DTS. And while Im doing the task although I
> can
> select data from the destination table using the sql query analyzer, I can
t
> execute the same select statement using ADO connection through a web site.
> Please advise A.S.A.P
> Thanks and Best Regards,
>

Index enquiry

hi all,
I have a table with 700K records with the primary key as cluster index.
TableA {
chrRef char(10), -- key
chrStatus char(2),
dtTrade datetime,
dtSettle datetime,
....
}
When query the table by filter records on non-primay key, the performance is
acceptable (less than 10K records)
e.g. select * from TableA where dtTrade = '20060101'
When records has been grown to 700K, the query is quite slow. I have added
an index (IX_dtTrade) on the column "dtTrade" in order to reduce the query
time. However, i found that SQL server did not use the index (IX_dtTrade) to
speed up the query. SQL server still using the cluster index to retrieve
records. From the help, i found that there was a method to force SQL server
to use the index. As a result, the query time reduce a lot.
e.g. select * from TableA with index (IX_dtTrade) where dtTrade = '20060101'
For this case,
1) Is there any setup so that the SQL server will use the index (IX_dtTrade)
automatically without explicit the cluase (with index ())?
2) When create index, what is the difference between single index and
compound index in SQL server? It seems that when an index is created on the
column "dtTrade" (IX_dtTrade) and "dtTrade, dtSettle" (IX_dtTrade_dtSettle),
the query time is same.
3) If there are many queries filter on columns "dtTrade", "dtSettle" and
"chrStatus", create an index on each column or a compound index on the three
columns?
3) Is it the only way to speed up the query time by creating index on target
column? (provided that no change on number of records)
Any suggestions? Thank in advance!!
Regards,
MartinCheck the execution plan to see how many rows are estimated to be returned. If SQL Server estimates
a large number of rows, it will consider a scan more efficient than using a non-clustered index.
This is because using a non-clustered index, SQL Server will navigate the index, and *for each row*
access the data page. Imagine if you return 10 000 rows, then you have 10 000 data page accesses,
even if the whole table perhaps fits on 5 000 pages. This is easier to explain with a white-board.
Assuming the estimate is off, we need to figure out why. It could be several reasons, for instance:
Bad statistics.
The query you showed us is not what you are running.
You use a stored procedure and the data you search for is a parameter.
The data you search for is a variable.
The condition for the data isn't expressed as in your example.
As for your questions:
> 1) Is there any setup so that the SQL server will use the index (IX_dtTrade) automatically without
> explicit the cluase (with index ())?
There's no "magic button" for this. See my above elaboration.
> 2) When create index, what is the difference between single index and compound index in SQL
> server? It seems that when an index is created on the column "dtTrade" (IX_dtTrade) and "dtTrade,
> dtSettle" (IX_dtTrade_dtSettle), the query time is same.
An index on several columns, say (a, b) , can be good for conditions like:
A = 2 AND B = 7
But not for:
B = 45
So you need to know your queries in order to create a good indexing strategy. If you are uncertain,
start by one index per column.
> 3) If there are many queries filter on columns "dtTrade", "dtSettle" and "chrStatus", create an
> index on each column or a compound index on the three columns?
See above.
> 3) Is it the only way to speed up the query time by creating index on target column? (provided
> that no change on number of records)
If you don't give SQL Server any way to limit which rows it need to look for in order to determine
which satisfies your condition, well, then SQL Server need to look at each row. An index does just
that.
Also, don't do SELECT *. Only return the columns you need. The main importance for this isn't
perhaps to reduce network bandwidth. It is that you probably lose the ability to cover your queries
with a non-clustered index. Such an index has all the columns that the query need in it and SQL
Server doesn't have to access the data page for each row, the answer is in the index page.
This is a big topic, so I suggest you start studying and reading a bit. Books Online has some good
sections which is a good start. Kalen Delaney's "Inside SQL Server" book is very good at describing
these constructs.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Atenza" <Atenza@.mail.hongkong.com> wrote in message news:%23q8OTNn9GHA.788@.TK2MSFTNGP05.phx.gbl...
> hi all,
> I have a table with 700K records with the primary key as cluster index.
> TableA {
> chrRef char(10), -- key
> chrStatus char(2),
> dtTrade datetime,
> dtSettle datetime,
> ....
> }
> When query the table by filter records on non-primay key, the performance is acceptable (less than
> 10K records)
> e.g. select * from TableA where dtTrade = '20060101'
> When records has been grown to 700K, the query is quite slow. I have added an index (IX_dtTrade)
> on the column "dtTrade" in order to reduce the query time. However, i found that SQL server did
> not use the index (IX_dtTrade) to speed up the query. SQL server still using the cluster index to
> retrieve records. From the help, i found that there was a method to force SQL server to use the
> index. As a result, the query time reduce a lot.
> e.g. select * from TableA with index (IX_dtTrade) where dtTrade = '20060101'
> For this case,
> 1) Is there any setup so that the SQL server will use the index (IX_dtTrade) automatically without
> explicit the cluase (with index ())?
> 2) When create index, what is the difference between single index and compound index in SQL
> server? It seems that when an index is created on the column "dtTrade" (IX_dtTrade) and "dtTrade,
> dtSettle" (IX_dtTrade_dtSettle), the query time is same.
> 3) If there are many queries filter on columns "dtTrade", "dtSettle" and "chrStatus", create an
> index on each column or a compound index on the three columns?
> 3) Is it the only way to speed up the query time by creating index on target column? (provided
> that no change on number of records)
> Any suggestions? Thank in advance!!
> Regards,
> Martin
>|||thank you for your suggestion!!! really useful!
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23UbAQXn9GHA.3384@.TK2MSFTNGP05.phx.gbl...
> Check the execution plan to see how many rows are estimated to be
> returned. If SQL Server estimates a large number of rows, it will consider
> a scan more efficient than using a non-clustered index. This is because
> using a non-clustered index, SQL Server will navigate the index, and *for
> each row* access the data page. Imagine if you return 10 000 rows, then
> you have 10 000 data page accesses, even if the whole table perhaps fits
> on 5 000 pages. This is easier to explain with a white-board.
> Assuming the estimate is off, we need to figure out why. It could be
> several reasons, for instance:
> Bad statistics.
> The query you showed us is not what you are running.
> You use a stored procedure and the data you search for is a parameter.
> The data you search for is a variable.
> The condition for the data isn't expressed as in your example.
> As for your questions:
>> 1) Is there any setup so that the SQL server will use the index
>> (IX_dtTrade) automatically without explicit the cluase (with index ())?
> There's no "magic button" for this. See my above elaboration.
>
>> 2) When create index, what is the difference between single index and
>> compound index in SQL server? It seems that when an index is created on
>> the column "dtTrade" (IX_dtTrade) and "dtTrade, dtSettle"
>> (IX_dtTrade_dtSettle), the query time is same.
> An index on several columns, say (a, b) , can be good for conditions like:
> A = 2 AND B = 7
> But not for:
> B = 45
> So you need to know your queries in order to create a good indexing
> strategy. If you are uncertain, start by one index per column.
>
>> 3) If there are many queries filter on columns "dtTrade", "dtSettle" and
>> "chrStatus", create an index on each column or a compound index on the
>> three columns?
> See above.
>
>> 3) Is it the only way to speed up the query time by creating index on
>> target column? (provided that no change on number of records)
> If you don't give SQL Server any way to limit which rows it need to look
> for in order to determine which satisfies your condition, well, then SQL
> Server need to look at each row. An index does just that.
> Also, don't do SELECT *. Only return the columns you need. The main
> importance for this isn't perhaps to reduce network bandwidth. It is that
> you probably lose the ability to cover your queries with a non-clustered
> index. Such an index has all the columns that the query need in it and SQL
> Server doesn't have to access the data page for each row, the answer is in
> the index page.
> This is a big topic, so I suggest you start studying and reading a bit.
> Books Online has some good sections which is a good start. Kalen Delaney's
> "Inside SQL Server" book is very good at describing these constructs.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Atenza" <Atenza@.mail.hongkong.com> wrote in message
> news:%23q8OTNn9GHA.788@.TK2MSFTNGP05.phx.gbl...
>> hi all,
>> I have a table with 700K records with the primary key as cluster index.
>> TableA {
>> chrRef char(10), -- key
>> chrStatus char(2),
>> dtTrade datetime,
>> dtSettle datetime,
>> ....
>> }
>> When query the table by filter records on non-primay key, the performance
>> is acceptable (less than 10K records)
>> e.g. select * from TableA where dtTrade = '20060101'
>> When records has been grown to 700K, the query is quite slow. I have
>> added an index (IX_dtTrade) on the column "dtTrade" in order to reduce
>> the query time. However, i found that SQL server did not use the index
>> (IX_dtTrade) to speed up the query. SQL server still using the cluster
>> index to retrieve records. From the help, i found that there was a method
>> to force SQL server to use the index. As a result, the query time reduce
>> a lot.
>> e.g. select * from TableA with index (IX_dtTrade) where dtTrade =>> '20060101'
>> For this case,
>> 1) Is there any setup so that the SQL server will use the index
>> (IX_dtTrade) automatically without explicit the cluase (with index ())?
>> 2) When create index, what is the difference between single index and
>> compound index in SQL server? It seems that when an index is created on
>> the column "dtTrade" (IX_dtTrade) and "dtTrade, dtSettle"
>> (IX_dtTrade_dtSettle), the query time is same.
>> 3) If there are many queries filter on columns "dtTrade", "dtSettle" and
>> "chrStatus", create an index on each column or a compound index on the
>> three columns?
>> 3) Is it the only way to speed up the query time by creating index on
>> target column? (provided that no change on number of records)
>> Any suggestions? Thank in advance!!
>> Regards,
>> Martin
>

Index corruption

Hi all.
I've got a database that people are posting data to
through the index. The other day, someone posted a
series of records and called a select statment from the
index to check their work. When they did, three entries
were missing. I went back and found that the data was on
the disks and could be regenerated by recreating the
index tables.
Now I'm trying to figure out why this happened and
whether I can expect that it will happen again. I
restored the last .bak file prior to the incident and
walked through the transaction logs up to the event,
running checkdb with each. The first trans log after the
event reported that it could not be restored due to an ID
being newer on the previous trans log.
I've now restored the first .bak file after the incident
and run checkdb. I'm getting this error:
Server: Msg 8929, Level 16, State 1, Line 1
Object ID 1541580530: Errors found in text ID 25723928576
owned by data record identified by RID = (1:171739:3) id
= 390945.
If I do a select statement for this ID, I get:
Server: Msg 601, Level 12, State 3, Line 1
Could not continue scan with NOLOCK due to data movement.
Does anyone have an ideas for why this happened? Other
actions I should try? Or the likelihood that this will
happen again?
Thanks greatly,
~BrendaHi Brenda,
From you description, it seems that you have resolved this problem and you
want to point out what induce this issue. If I have misunderstood, please
feel free to let me know.
Could you also send me the following logs/files so I can perform a more
analysis of what is happening:
1. SQLDiag.txt from the SQL Server 2000. To generate this file you need to
use the command line utility SQLDiag.exe -I instance_name . For more
information about the utility check SQL Books On-Line article "sqldiag
Utility"
2. System and application event logs saved as text files. To do that in
Windows NT
4.0 Event Viewer go to Log menu, choose Save As. From "Save As Type" dialog
box choose Text Files (*.TXT).
3. Any previous reports/output files of Maintenance Plan like the one you
have already attached.
4. Any DBCC CHECKDB results if you have run those manually, outside the
Maintenance Plan.
I am standing by for your response.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.

INDEX CLUSTER QUESTION

Hi
USING SQL SERVER 2000
We have a clustered index in a Table with a 100 millions of records.
This table grows 1.000.000 of records per month.
We have a table of clients and another one of accounts.
Both using Clustered Index.
What index to use?
why?
We have performance problems!!!
help me !!
thanks
MacisuUnless you are using query hints, SQL Server's query optimizer decides which
index to use.
Query Hints:
http://msdn.microsoft.com/library/d...r />
_8upf.asp
Clustered Indexes:
http://msdn.microsoft.com/library/d...>
_05_5h6b.asp
Performance Tuning Guide for Date Warehouses:
http://www.microsoft.com/technet/pr...n/rdbmspft.mspx
"Macisu" <Macisu@.discussions.microsoft.com> wrote in message
news:A85C3AFA-1243-4F6F-BCD7-2C7A6FD13EAE@.microsoft.com...
> Hi
> USING SQL SERVER 2000
> We have a clustered index in a Table with a 100 millions of records.
> This table grows 1.000.000 of records per month.
> We have a table of clients and another one of accounts.
> Both using Clustered Index.
> What index to use?
> why?
> We have performance problems!!!
> help me !!
>
> thanks
> Macisu
>|||How are they currently indexed and what are you using for Primary Keys?
/*
-Paul Nielsen
www.SQLServerBible.com
www.SolidQualityLearning.com
*/
"Macisu" <Macisu@.discussions.microsoft.com> wrote in message
news:A85C3AFA-1243-4F6F-BCD7-2C7A6FD13EAE@.microsoft.com...
> Hi
> USING SQL SERVER 2000
> We have a clustered index in a Table with a 100 millions of records.
> This table grows 1.000.000 of records per month.
> We have a table of clients and another one of accounts.
> Both using Clustered Index.
> What index to use?
> why?
> We have performance problems!!!
> help me !!
>
> thanks
> Macisu
>

Index building performance

I am building some tables with 10s of millions of
records. Each table has 1 or 2 indexes. Is it faster to
create the index when I create the table, then let it
build as I load the data. OR Should I load all my data
then let SQL build the index?
Thanks.Depends really. Say you've got a clustered index on a
table that you are going to bulk insert from a .txt file
into. You would have much better performance if you built
the clustered index after the data was all in place.
However, when you say building tables, do you mean just
insert? Or are you doing any insert/ update type of deals.
We very often do insert/ updates. (If already exists
update where column1 = this else insert.) For this
example, an index on column1 would be very benificial.
insert into
>--Original Message--
>I am building some tables with 10s of millions of
>records. Each table has 1 or 2 indexes. Is it faster to
>create the index when I create the table, then let it
>build as I load the data. OR Should I load all my data
>then let SQL build the index?
>Thanks.
>.
>|||You'll get better performance by loading data into a table with no
indexes and then building the indexes (including primary key and unique
constraints) afterward. The exception is you can load data into a table
with only a clustered index and get good performance if the data is
sorted in clustered index sequence.
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"DB" <daveblair@.adelphia.net> wrote in message
news:1384801c38473$1a3b5f70$a601280a@.phx.gbl...
> I am building some tables with 10s of millions of
> records. Each table has 1 or 2 indexes. Is it faster to
> create the index when I create the table, then let it
> build as I load the data. OR Should I load all my data
> then let SQL build the index?
> Thanks.