Question about snapshot article defaults and indexes

I have recently been comparing the size of the source and replicated
tables and realized that indexes are not being replicated. The GLOBAL
(Applies to all) article defaults for the snapshot are set as follows:
Copy objects to destination
Indexes for primary keys are always copied
Include declared referential integrityCHECKED
Clustered indexesCHECKED
Nonclustered indexesCHECKED
User triggersNOT CHECKED
Extended propertiesNOT CHECKED
CollationNOT CHECKED
I these defaults should transfer the indexes correctly so I had to look
for another culprit. We have a script which creates three tables daily
so I decided to look at the article defaults for those tables and found
that only the 'Include declared referential integrity box is CHECKED.
QUESTION: What is the stored procedure where these variables can be
set? Is it sp_addarticle and if so what fields?
If we need to use the replicated db what would have been the
implication of not having the proper indexing. Performance?
to set them use the schemaoption parameter of sp_addarticle.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com

question about performance issues w/SQL2000 with NO indexes

hello,
I've been assigned to do performance tuning on an SQL2000 database
(around 10GB in size, several instances).
So far, I see a single RAID5 array, 4CPU (xeon 700MHZ), 4GB RAM.

I see the raid5 as a bottleneck. I'd setup a raid 10 and seperate the
logs, database and OS(win2k).

The one thing that was a bit odd to me was that I was told this place
doesn't use indexes. The company is a house builder. They are pretty
large.

The IT manager isn't a programmer so she couldn't explain to me why no
indexes are used. She told me the programmers just don't use indexes.

Before I start investing more time on this, I'd really like to learn
about why you wouldn't want to use indexes - especially on such a large
database!

Thanks,

OskarI can think of no good reason to NOT use indexes; that should be a
basic ingedient to any performance improvement attempt. It should also
be transparent to any development staff they have (in other words,
programmers should suggest what indexes they think would be
appropriate, but in most cases they shouldn't worry about developing
them on an as-needed basis).

If you are going to add indexes, you may also want to place your
clustered indexes on your data drive, and your other indexes on the log
drive; this may help improve speed as well. I typically try to have a
third drive available for indexes, but sometimes that's not an option.

Just my .02.

Stu|||Hi

First take the system architect out of the building and have him shot.
Then take the IT Manager outside and have her shot for hiring such an
architect.
Then take each developer out and have them shot for not knowing better.

Add indexes. They are one of the basic design fundamentals required with any
database.

Regards
----------
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland

IM: mike@.epprecht.net

MVP Program: http://www.microsoft.com/mvp

Blog: http://www.msmvps.com/epprecht/

"Stu" <stuart.ainsworth@.gmail.com> wrote in message
news:1118415503.154653.85890@.g49g2000cwa.googlegro ups.com...
>I can think of no good reason to NOT use indexes; that should be a
> basic ingedient to any performance improvement attempt. It should also
> be transparent to any development staff they have (in other words,
> programmers should suggest what indexes they think would be
> appropriate, but in most cases they shouldn't worry about developing
> them on an as-needed basis).
> If you are going to add indexes, you may also want to place your
> clustered indexes on your data drive, and your other indexes on the log
> drive; this may help improve speed as well. I typically try to have a
> third drive available for indexes, but sometimes that's not an option.
> Just my .02.
> Stu|||Hi

First take the system architect out of the building and have him shot.
Then take the IT Manager outside and have her shot for hiring such an
architect.
Then take each developer out and have them shot for not knowing better.

Add indexes. They are one of the basic design fundamentals required with any
database.

Regards
----------
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland

IM: mike@.epprecht.net

MVP Program: http://www.microsoft.com/mvp

Blog: http://www.msmvps.com/epprecht/

"Stu" <stuart.ainsworth@.gmail.com> wrote in message
news:1118415503.154653.85890@.g49g2000cwa.googlegro ups.com...
>I can think of no good reason to NOT use indexes; that should be a
> basic ingedient to any performance improvement attempt. It should also
> be transparent to any development staff they have (in other words,
> programmers should suggest what indexes they think would be
> appropriate, but in most cases they shouldn't worry about developing
> them on an as-needed basis).
> If you are going to add indexes, you may also want to place your
> clustered indexes on your data drive, and your other indexes on the log
> drive; this may help improve speed as well. I typically try to have a
> third drive available for indexes, but sometimes that's not an option.
> Just my .02.
> Stu|||pheonix1t (pheonix1tAThoustonDOTrrDOTcom@.com.com) writes:
> I've been assigned to do performance tuning on an SQL2000 database
> (around 10GB in size, several instances).
> So far, I see a single RAID5 array, 4CPU (xeon 700MHZ), 4GB RAM.
> I see the raid5 as a bottleneck. I'd setup a raid 10 and seperate the
> logs, database and OS(win2k).
> The one thing that was a bit odd to me was that I was told this place
> doesn't use indexes. The company is a house builder. They are pretty
> large.
> The IT manager isn't a programmer so she couldn't explain to me why no
> indexes are used. She told me the programmers just don't use indexes.
> Before I start investing more time on this, I'd really like to learn
> about why you wouldn't want to use indexes - especially on such a large
> database!

Seems like you have an easy job. Run Profiler to catch a day's workload,
run the Index Tuning Wizard over the result, create indexes. If the
system does not really have any indexes and is still standing up on
that hardware, it's pointless to improve it.

And least of all, the RAID. The only way that system can survive is
because it's able to hold the data in cache.

Then again, I would suspect that if you run this query:

select *
from sysindexes
where indid >= 1 and indid < 255
and indexproperty(id, name, 'IsStatistics') = 0
and indexproperty(id, name, 'IsHypothetical') = 0

That a couple of indexes will show up.

Yet, then again, just because there are indexes, does not mean that
they are the right indexes, so Profiler and Index Tuning Wizard may
still be what you should look at.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||pheonix1t (pheonix1tAThoustonDOTrrDOTcom@.com.com) writes:
> I've been assigned to do performance tuning on an SQL2000 database
> (around 10GB in size, several instances).
> So far, I see a single RAID5 array, 4CPU (xeon 700MHZ), 4GB RAM.
> I see the raid5 as a bottleneck. I'd setup a raid 10 and seperate the
> logs, database and OS(win2k).
> The one thing that was a bit odd to me was that I was told this place
> doesn't use indexes. The company is a house builder. They are pretty
> large.
> The IT manager isn't a programmer so she couldn't explain to me why no
> indexes are used. She told me the programmers just don't use indexes.
> Before I start investing more time on this, I'd really like to learn
> about why you wouldn't want to use indexes - especially on such a large
> database!

Seems like you have an easy job. Run Profiler to catch a day's workload,
run the Index Tuning Wizard over the result, create indexes. If the
system does not really have any indexes and is still standing up on
that hardware, it's pointless to improve it.

And least of all, the RAID. The only way that system can survive is
because it's able to hold the data in cache.

Then again, I would suspect that if you run this query:

select *
from sysindexes
where indid >= 1 and indid < 255
and indexproperty(id, name, 'IsStatistics') = 0
and indexproperty(id, name, 'IsHypothetical') = 0

That a couple of indexes will show up.

Yet, then again, just because there are indexes, does not mean that
they are the right indexes, so Profiler and Index Tuning Wizard may
still be what you should look at.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||The only reason I can think of is that someone over-indexed or
improperly indexed the schema at one time, and the next guy reacted by
dropping all of them. I hope he left the UNIQUE and PRIMARY KEY
constraints.|||The only reason I can think of is that someone over-indexed or
improperly indexed the schema at one time, and the next guy reacted by
dropping all of them. I hope he left the UNIQUE and PRIMARY KEY
constraints.|||"pheonix1t" wrote:

> hello,
> I've been assigned to do performance tuning on an SQL2000 database (around
> 10GB in size, several instances).
> So far, I see a single RAID5 array, 4CPU (xeon 700MHZ), 4GB RAM.
> I see the raid5 as a bottleneck. I'd setup a raid 10 and seperate the
> logs, database and OS(win2k).
> The one thing that was a bit odd to me was that I was told this place
> doesn't use indexes. The company is a house builder. They are pretty
> large.
> The IT manager isn't a programmer so she couldn't explain to me why no
> indexes are used. She told me the programmers just don't use indexes.
> Before I start investing more time on this, I'd really like to learn about
> why you wouldn't want to use indexes - especially on such a large
> database!

In addition to the other comments, I've heard about companies where that
happened this way:

- Bad app is designed where everything runs dog slow

- Users complain that data entry is slow

- Some developer reads that indexes "speed reads, slow writes" and so drops
all indexes and tells the bosses that "reports might run slow, but data
entry will speed up"

- In the middle of all this, hardware gets upgraded, temporarily masking the
problem

Unfortunately, I've seen this happen, but with "constraints" substituted for
"indexes". Figuring out some quick indexes to get you started will be easy.
Fixing the possibly broken data will be harder.

I can envision a few possible situation where indexes wouldn't be used: a
SQL Server used only as a staging server between OLAP and OLTP systems where
all data scrubbing would involve table scans anyway. Also, evaluating
hardware by thrashing it with wild queries unsupported by indexes. However,
I can't think of a single time in an OLTP system where I wouldn't want
indexing (unless I were a saboteur hired by the competition :)

Craig|||"pheonix1t" wrote:

> hello,
> I've been assigned to do performance tuning on an SQL2000 database (around
> 10GB in size, several instances).
> So far, I see a single RAID5 array, 4CPU (xeon 700MHZ), 4GB RAM.
> I see the raid5 as a bottleneck. I'd setup a raid 10 and seperate the
> logs, database and OS(win2k).
> The one thing that was a bit odd to me was that I was told this place
> doesn't use indexes. The company is a house builder. They are pretty
> large.
> The IT manager isn't a programmer so she couldn't explain to me why no
> indexes are used. She told me the programmers just don't use indexes.
> Before I start investing more time on this, I'd really like to learn about
> why you wouldn't want to use indexes - especially on such a large
> database!

In addition to the other comments, I've heard about companies where that
happened this way:

- Bad app is designed where everything runs dog slow

- Users complain that data entry is slow

- Some developer reads that indexes "speed reads, slow writes" and so drops
all indexes and tells the bosses that "reports might run slow, but data
entry will speed up"

- In the middle of all this, hardware gets upgraded, temporarily masking the
problem

Unfortunately, I've seen this happen, but with "constraints" substituted for
"indexes". Figuring out some quick indexes to get you started will be easy.
Fixing the possibly broken data will be harder.

I can envision a few possible situation where indexes wouldn't be used: a
SQL Server used only as a staging server between OLAP and OLTP systems where
all data scrubbing would involve table scans anyway. Also, evaluating
hardware by thrashing it with wild queries unsupported by indexes. However,
I can't think of a single time in an OLTP system where I wouldn't want
indexing (unless I were a saboteur hired by the competition :)

Craig

question about performance issues w/SQL2000 with NO indexes

hello,
I've been assigned to do performance tuning on an SQL2000 database
(around 10GB in size, several instances).
So far, I see a single RAID5 array, 4CPU (xeon 700MHZ), 4GB RAM.

I see the raid5 as a bottleneck. I'd setup a raid 10 and seperate the
logs, database and OS(win2k).

The one thing that was a bit odd to me was that I was told this place
doesn't use indexes. The company is a house builder. They are pretty
large.

The IT manager isn't a programmer so she couldn't explain to me why no
indexes are used. She told me the programmers just don't use indexes.

Before I start investing more time on this, I'd really like to learn
about why you wouldn't want to use indexes - especially on such a large
database!

Thanks,

OskarI can think of no good reason to NOT use indexes; that should be a
basic ingedient to any performance improvement attempt. It should also
be transparent to any development staff they have (in other words,
programmers should suggest what indexes they think would be
appropriate, but in most cases they shouldn't worry about developing
them on an as-needed basis).

If you are going to add indexes, you may also want to place your
clustered indexes on your data drive, and your other indexes on the log
drive; this may help improve speed as well. I typically try to have a
third drive available for indexes, but sometimes that's not an option.

Just my .02.

Stu

question about log file size when alter Huge table

I have the next question, and i would like to hear what do you think
about, and if is there a better solution for "my problem"

here is the question, I have a huge table with 60GB of data (image
files). The problem happen always when i try to ALTER the structure of
the table. For example I change a field char(3) to char(4)...the
sqlserver then performs the "alter table" command...that must be
something similar than "insert into the new table + drop the actual
table" and for that I need about 60GB o space for my LOG file, and
takes hours to complete the operation.

Is this the only way to alter a single field in my table??

I would like to heard you opinions...Thanks..

ALberto"Gordowey" <albertoiriarte@.gmail.com> wrote in message
news:1130717670.681491.136070@.g47g2000cwa.googlegr oups.com...
> I have the next question, and i would like to hear what do you think
> about, and if is there a better solution for "my problem"
> here is the question, I have a huge table with 60GB of data (image
> files). The problem happen always when i try to ALTER the structure of
> the table. For example I change a field char(3) to char(4)...the
> sqlserver then performs the "alter table" command...that must be
> something similar than "insert into the new table + drop the actual
> table" and for that I need about 60GB o space for my LOG file, and
> takes hours to complete the operation.

Sounds about right.

> Is this the only way to alter a single field in my table??

Well, you might be able to ADD a new column to your table, that might work
better. (especially if it has no default).

Or, it might be easier to BCP the data out, truncate the table, BCP the data
in.

(in this case I don't think native format would work so you'd have to
experiment.)

> I would like to heard you opinions...Thanks..
> ALberto|||Gordowey (albertoiriarte@.gmail.com) writes:
> I have the next question, and i would like to hear what do you think
> about, and if is there a better solution for "my problem"
> here is the question, I have a huge table with 60GB of data (image
> files). The problem happen always when i try to ALTER the structure of
> the table. For example I change a field char(3) to char(4)...the
> sqlserver then performs the "alter table" command...that must be
> something similar than "insert into the new table + drop the actual
> table" and for that I need about 60GB o space for my LOG file, and
> takes hours to complete the operation.

Yup, that's it.

> Is this the only way to alter a single field in my table??

Rather than having ALTER TABLE to all that under the covers, you could do
it your self. The twist is that then you can do the insert in batches,
and truncate the transaction log between the turns. (Simplest is to run
in simple recovery mode if you can.) When you compose the batches, use
the clustered index for the table, else selection of the batches may be
horribly slow.

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

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

Question about Font size

I'm trying to set my font to any size smaller than 8. Is that possible? I am trying to use portrait orientation instead of landscape but I can't fit everything on one page using portrait orientation with 8 point font.

Thanks!

Yes, that should be possible. Did you get an error when you set it to smaller than 8pt?|||

Its not that I received an error, it's that I can't figure out how to do it. While I am constructing the report in layout view, the smallest font size available is 8. If I change the font to a 6, it reverts right back to 8. How can I set the font size in layout view to smaller than 8?

Thanks!

|||Are you designing the report in Report Designer? Which version are you using?|||

Hi aferoce,

It is working fine in SSRS 2005.Please check in which version you are working.

Question about Font size

I'm trying to set my font to any size smaller than 8. Is that possible? I am trying to use portrait orientation instead of landscape but I can't fit everything on one page using portrait orientation with 8 point font.

Thanks!

Yes, that should be possible. Did you get an error when you set it to smaller than 8pt?|||

Its not that I received an error, it's that I can't figure out how to do it. While I am constructing the report in layout view, the smallest font size available is 8. If I change the font to a 6, it reverts right back to 8. How can I set the font size in layout view to smaller than 8?

Thanks!

|||Are you designing the report in Report Designer? Which version are you using?|||

Hi aferoce,

It is working fine in SSRS 2005.Please check in which version you are working.

Question about database size and performance...

I have a project where I will be loading ~ 242 Million Rows of data to a
table and then adding to this on a monthly basis. Maybe 3 million a month
added. This will be used mainly for reporting purposes.
Is there a limit as to how many rows a table in SQL can have? SQL Server
2000 is what I am using?
As for performance what recommendations would you have? Is there a website
out there that discussses this in length?
Thanks,
RogRoger wrote:
> I have a project where I will be loading ~ 242 Million Rows of data
> to a table and then adding to this on a monthly basis. Maybe 3
> million a month added. This will be used mainly for reporting
> purposes.
> Is there a limit as to how many rows a table in SQL can have? SQL
> Server 2000 is what I am using?
> As for performance what recommendations would you have? Is there a
> website out there that discussses this in length?
> Thanks,
> Rog
What will you be doing with the data once it's loaded in the table?
In general, proper index design so queries run as efficiently as
possible is very important on large tables. Just as table design is
important. For example, if you have large varchar or char columns that
are NULLable and many rows might contain NULL, it may be better to
create another table with a 1:1 relationship to store the large data.
Keeping row size as small as possible is better.
SQL Server has no trouble handling tables of this size. Or dishing out
data from a query. It will give you correct results whether the queries
are tuned or not or whether the table is well-designed. The question is,
how long are your users willing to wait for a response.
If you have the DDL for the table, consider posting it here for
comments. And then tune your queries.
David G.|||As David said, attention to table and index design details are important
with large tables. The maximum rows per table is limited only by available
storage in SQL 2000. We have tables with billions of rows that perform
quite well on appropriately sized hardware.
You also need to consider administration, maintenance and your availability
requirements. For example, partitioning financial data into separate tables
by quarter can reduce the time it takes to load data, purge data, rebuild
indexes, etc. Multiple filegroups can provide more granular backup/recovery
options. The downside is additional administration and schema complexity.
This may or may not affect performance, depending on how data are accessed.
You might check out the chapter RDBMS Performance Tuning Guide for Data
Warehousing in the SQL 2000 Resource Kit:
2061.mspx" target="_blank">http://www.microsoft.com/resources/...r />
2061.mspx
Hope this helps.
Dan Guzman
SQL Server MVP
"Roger" <rogoflap@.gmail.com> wrote in message
news:Oex4dY7iEHA.536@.TK2MSFTNGP11.phx.gbl...
> I have a project where I will be loading ~ 242 Million Rows of data to a
> table and then adding to this on a monthly basis. Maybe 3 million a month
> added. This will be used mainly for reporting purposes.
> Is there a limit as to how many rows a table in SQL can have? SQL Server
> 2000 is what I am using?
> As for performance what recommendations would you have? Is there a
website
> out there that discussses this in length?
> Thanks,
> Rog
>|||Thanks for the insite from all of your responses..... I will post my
schema here in a short while... Days to see what suggestions you might
have.
Thanks,
Rog
"Roger" <rogoflap@.gmail.com> wrote in message
news:Oex4dY7iEHA.536@.TK2MSFTNGP11.phx.gbl...
> I have a project where I will be loading ~ 242 Million Rows of data to a
> table and then adding to this on a monthly basis. Maybe 3 million a month
> added. This will be used mainly for reporting purposes.
> Is there a limit as to how many rows a table in SQL can have? SQL Server
> 2000 is what I am using?
> As for performance what recommendations would you have? Is there a
website
> out there that discussses this in length?
> Thanks,
> Rog
>

Question about database size and performance...

I have a project where I will be loading ~ 242 Million Rows of data to a
table and then adding to this on a monthly basis. Maybe 3 million a month
added. This will be used mainly for reporting purposes.
Is there a limit as to how many rows a table in SQL can have? SQL Server
2000 is what I am using?
As for performance what recommendations would you have? Is there a website
out there that discussses this in length?
Thanks,
RogRoger wrote:
> I have a project where I will be loading ~ 242 Million Rows of data
> to a table and then adding to this on a monthly basis. Maybe 3
> million a month added. This will be used mainly for reporting
> purposes.
> Is there a limit as to how many rows a table in SQL can have? SQL
> Server 2000 is what I am using?
> As for performance what recommendations would you have? Is there a
> website out there that discussses this in length?
> Thanks,
> Rog
What will you be doing with the data once it's loaded in the table?
In general, proper index design so queries run as efficiently as
possible is very important on large tables. Just as table design is
important. For example, if you have large varchar or char columns that
are NULLable and many rows might contain NULL, it may be better to
create another table with a 1:1 relationship to store the large data.
Keeping row size as small as possible is better.
SQL Server has no trouble handling tables of this size. Or dishing out
data from a query. It will give you correct results whether the queries
are tuned or not or whether the table is well-designed. The question is,
how long are your users willing to wait for a response.
If you have the DDL for the table, consider posting it here for
comments. And then tune your queries.
David G.|||As David said, attention to table and index design details are important
with large tables. The maximum rows per table is limited only by available
storage in SQL 2000. We have tables with billions of rows that perform
quite well on appropriately sized hardware.
You also need to consider administration, maintenance and your availability
requirements. For example, partitioning financial data into separate tables
by quarter can reduce the time it takes to load data, purge data, rebuild
indexes, etc. Multiple filegroups can provide more granular backup/recovery
options. The downside is additional administration and schema complexity.
This may or may not affect performance, depending on how data are accessed.
You might check out the chapter RDBMS Performance Tuning Guide for Data
Warehousing in the SQL 2000 Resource Kit:
http://www.microsoft.com/resources/documentation/sql/2000/all/reskit/en-us/part5/c2061.mspx
Hope this helps.
Dan Guzman
SQL Server MVP
"Roger" <rogoflap@.gmail.com> wrote in message
news:Oex4dY7iEHA.536@.TK2MSFTNGP11.phx.gbl...
> I have a project where I will be loading ~ 242 Million Rows of data to a
> table and then adding to this on a monthly basis. Maybe 3 million a month
> added. This will be used mainly for reporting purposes.
> Is there a limit as to how many rows a table in SQL can have? SQL Server
> 2000 is what I am using?
> As for performance what recommendations would you have? Is there a
website
> out there that discussses this in length?
> Thanks,
> Rog
>|||Thanks for the insite from all of your responses..... I will post my
schema here in a short while... Days to see what suggestions you might
have.
Thanks,
Rog
"Roger" <rogoflap@.gmail.com> wrote in message
news:Oex4dY7iEHA.536@.TK2MSFTNGP11.phx.gbl...
> I have a project where I will be loading ~ 242 Million Rows of data to a
> table and then adding to this on a monthly basis. Maybe 3 million a month
> added. This will be used mainly for reporting purposes.
> Is there a limit as to how many rows a table in SQL can have? SQL Server
> 2000 is what I am using?
> As for performance what recommendations would you have? Is there a
website
> out there that discussses this in length?
> Thanks,
> Rog
>

Question about database size and performance...

I have a project where I will be loading ~ 242 Million Rows of data to a
table and then adding to this on a monthly basis. Maybe 3 million a month
added. This will be used mainly for reporting purposes.
Is there a limit as to how many rows a table in SQL can have? SQL Server
2000 is what I am using?
As for performance what recommendations would you have? Is there a website
out there that discussses this in length?
Thanks,
Rog
Roger wrote:
> I have a project where I will be loading ~ 242 Million Rows of data
> to a table and then adding to this on a monthly basis. Maybe 3
> million a month added. This will be used mainly for reporting
> purposes.
> Is there a limit as to how many rows a table in SQL can have? SQL
> Server 2000 is what I am using?
> As for performance what recommendations would you have? Is there a
> website out there that discussses this in length?
> Thanks,
> Rog
What will you be doing with the data once it's loaded in the table?
In general, proper index design so queries run as efficiently as
possible is very important on large tables. Just as table design is
important. For example, if you have large varchar or char columns that
are NULLable and many rows might contain NULL, it may be better to
create another table with a 1:1 relationship to store the large data.
Keeping row size as small as possible is better.
SQL Server has no trouble handling tables of this size. Or dishing out
data from a query. It will give you correct results whether the queries
are tuned or not or whether the table is well-designed. The question is,
how long are your users willing to wait for a response.
If you have the DDL for the table, consider posting it here for
comments. And then tune your queries.
David G.
|||As David said, attention to table and index design details are important
with large tables. The maximum rows per table is limited only by available
storage in SQL 2000. We have tables with billions of rows that perform
quite well on appropriately sized hardware.
You also need to consider administration, maintenance and your availability
requirements. For example, partitioning financial data into separate tables
by quarter can reduce the time it takes to load data, purge data, rebuild
indexes, etc. Multiple filegroups can provide more granular backup/recovery
options. The downside is additional administration and schema complexity.
This may or may not affect performance, depending on how data are accessed.
You might check out the chapter RDBMS Performance Tuning Guide for Data
Warehousing in the SQL 2000 Resource Kit:
http://www.microsoft.com/resources/d...rt5/c2061.mspx
Hope this helps.
Dan Guzman
SQL Server MVP
"Roger" <rogoflap@.gmail.com> wrote in message
news:Oex4dY7iEHA.536@.TK2MSFTNGP11.phx.gbl...
> I have a project where I will be loading ~ 242 Million Rows of data to a
> table and then adding to this on a monthly basis. Maybe 3 million a month
> added. This will be used mainly for reporting purposes.
> Is there a limit as to how many rows a table in SQL can have? SQL Server
> 2000 is what I am using?
> As for performance what recommendations would you have? Is there a
website
> out there that discussses this in length?
> Thanks,
> Rog
>
|||Thanks for the insite from all of your responses..... I will post my
schema here in a short while... Days to see what suggestions you might
have.
Thanks,
Rog
"Roger" <rogoflap@.gmail.com> wrote in message
news:Oex4dY7iEHA.536@.TK2MSFTNGP11.phx.gbl...
> I have a project where I will be loading ~ 242 Million Rows of data to a
> table and then adding to this on a monthly basis. Maybe 3 million a month
> added. This will be used mainly for reporting purposes.
> Is there a limit as to how many rows a table in SQL can have? SQL Server
> 2000 is what I am using?
> As for performance what recommendations would you have? Is there a
website
> out there that discussses this in length?
> Thanks,
> Rog
>

question ?

Does anyone know how i can find out the size of a file
using transact sql. The xp_getfiledetails is not working.
Any ideas?
Thanxs
Hi
You may want to look at
EXEC @.HR = sp_OACreate 'Scripting.FileSystemObject', @.FSO OUT
"" <anonymous@.discussions.microsoft.com> wrote in message
news:347201c48f3c$b875bcb0$a601280a@.phx.gbl...
> Does anyone know how i can find out the size of a file
> using transact sql. The xp_getfiledetails is not working.
> Any ideas?
> Thanxs
|||what is the error you get from the xp?... are you executing it from the
correct database..
In addition to using the file system object in script you could also
xp_cmdshell with a command...
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
"" <anonymous@.discussions.microsoft.com> wrote in message
news:347201c48f3c$b875bcb0$a601280a@.phx.gbl...
> Does anyone know how i can find out the size of a file
> using transact sql. The xp_getfiledetails is not working.
> Any ideas?
> Thanxs

question ?

Does anyone know how i can find out the size of a file
using transact sql. The xp_getfiledetails is not working.
Any ideas?
ThanxsHi
You may want to look at
EXEC @.HR = sp_OACreate 'Scripting.FileSystemObject', @.FSO OUT
"" <anonymous@.discussions.microsoft.com> wrote in message
news:347201c48f3c$b875bcb0$a601280a@.phx.gbl...
> Does anyone know how i can find out the size of a file
> using transact sql. The xp_getfiledetails is not working.
> Any ideas?
> Thanxs|||what is the error you get from the xp?... are you executing it from the
correct database..
In addition to using the file system object in script you could also
xp_cmdshell with a command...
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
"" <anonymous@.discussions.microsoft.com> wrote in message
news:347201c48f3c$b875bcb0$a601280a@.phx.gbl...
> Does anyone know how i can find out the size of a file
> using transact sql. The xp_getfiledetails is not working.
> Any ideas?
> Thanxs

question ?

Does anyone know how i can find out the size of a file
using transact sql. The xp_getfiledetails is not working.
Any ideas?
ThanxsHi
You may want to look at
EXEC @.HR = sp_OACreate 'Scripting.FileSystemObject', @.FSO OUT
":)" <anonymous@.discussions.microsoft.com> wrote in message
news:347201c48f3c$b875bcb0$a601280a@.phx.gbl...
> Does anyone know how i can find out the size of a file
> using transact sql. The xp_getfiledetails is not working.
> Any ideas?
> Thanxs|||what is the error you get from the xp?... are you executing it from the
correct database..
In addition to using the file system object in script you could also
xp_cmdshell with a command...
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
":)" <anonymous@.discussions.microsoft.com> wrote in message
news:347201c48f3c$b875bcb0$a601280a@.phx.gbl...
> Does anyone know how i can find out the size of a file
> using transact sql. The xp_getfiledetails is not working.
> Any ideas?
> Thanxs