Help on Performance with growing database

Hi,
I'm using SQL 2000. I have a simple database as backend for warehouse to
scan the products using access as front end. It includes 2 tables: order and
product.
Since everyday we have about 3000 orders to come in, each order has a few
products. The database is growing fast.
I can see the scanning process is a bit of slow at the front end. So I kept
current year data in both tables, and moved older data (3 years) into
historical tables within the same database.
Now, the active tables are smaller, do I need to do trunk database since I
deleted records from the active tables? Would this make it better? I don't
know if I can do trunk one table instead of the whole database?
If anyone has better idea regarding this case, it will be welcomed.
Thanks
SarahI think you did the right thing by archiving the old data to historical
tables. Partitioning the data like that will decrease I/O.
You could go a step further and place the data on a separate set of physical
disks if you need to access the archive data concurrently with the "active"
data.
I couldn't tell from your description which table the scanning (I'm assuming
you were talking about a "scanner" and not referring to table/index scans)
was accessing. But either way it appears that you're referring to a read
operation. In this case you may want to investigate your indexes to see if
you can find a better index for the query. Use Query Analyzer and look at
the execution plan on the query and verify that it is using the index you
are expecting. Also, you can call SET IO_STATISTICS ON before the query runs
and check the output to look at scans and logical reads. This will help you
determine if your optimization efforts are improving things or making it
worse.
Mark
"Sarah" <sguo@.coopervision.com> wrote in message
news:uBPMh4$rGHA.4264@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I'm using SQL 2000. I have a simple database as backend for warehouse to
> scan the products using access as front end. It includes 2 tables: order
> and product.
> Since everyday we have about 3000 orders to come in, each order has a few
> products. The database is growing fast.
> I can see the scanning process is a bit of slow at the front end. So I
> kept current year data in both tables, and moved older data (3 years) into
> historical tables within the same database.
> Now, the active tables are smaller, do I need to do trunk database since I
> deleted records from the active tables? Would this make it better? I don't
> know if I can do trunk one table instead of the whole database?
> If anyone has better idea regarding this case, it will be welcomed.
> Thanks
> Sarah
>|||Hi Mark,
Thanks for your response. Since our users don't inquiry historical data very
often, can I leave them on the same disk?
Also what I meant scanning is that user uses scanner to collect the barcode
into the system. The user does read and write on both tables.
I'm not sure if "Trunk database" make it better?
Also I found "tempdb" is growing than it should be, is that because I've
been doing queries in Query Analyzer on daily basis? I checked the
properties, allow auto grow at 10%. How can I tell the grow step for
"tempdb"?
Thanks a lot,
Sarah
"Mark Miller" <mark_n_0-s*p..am@.maxpreps.com> wrote in message
news:epNlypAsGHA.4608@.TK2MSFTNGP04.phx.gbl...
>I think you did the right thing by archiving the old data to historical
>tables. Partitioning the data like that will decrease I/O.
> You could go a step further and place the data on a separate set of
> physical disks if you need to access the archive data concurrently with
> the "active" data.
> I couldn't tell from your description which table the scanning (I'm
> assuming you were talking about a "scanner" and not referring to
> table/index scans) was accessing. But either way it appears that you're
> referring to a read operation. In this case you may want to investigate
> your indexes to see if you can find a better index for the query. Use
> Query Analyzer and look at the execution plan on the query and verify that
> it is using the index you are expecting. Also, you can call SET
> IO_STATISTICS ON before the query runs and check the output to look at
> scans and logical reads. This will help you determine if your optimization
> efforts are improving things or making it worse.
> Mark
>
> "Sarah" <sguo@.coopervision.com> wrote in message
> news:uBPMh4$rGHA.4264@.TK2MSFTNGP04.phx.gbl...
>> Hi,
>> I'm using SQL 2000. I have a simple database as backend for warehouse to
>> scan the products using access as front end. It includes 2 tables: order
>> and product.
>> Since everyday we have about 3000 orders to come in, each order has a few
>> products. The database is growing fast.
>> I can see the scanning process is a bit of slow at the front end. So I
>> kept current year data in both tables, and moved older data (3 years)
>> into historical tables within the same database.
>> Now, the active tables are smaller, do I need to do trunk database since
>> I deleted records from the active tables? Would this make it better? I
>> don't know if I can do trunk one table instead of the whole database?
>> If anyone has better idea regarding this case, it will be welcomed.
>> Thanks
>> Sarah
>|||Sarah wrote:
> Hi Mark,
> Thanks for your response. Since our users don't inquiry historical data very
> often, can I leave them on the same disk?
> Also what I meant scanning is that user uses scanner to collect the barcode
> into the system. The user does read and write on both tables.
> I'm not sure if "Trunk database" make it better?
> Also I found "tempdb" is growing than it should be, is that because I've
> been doing queries in Query Analyzer on daily basis? I checked the
> properties, allow auto grow at 10%. How can I tell the grow step for
> "tempdb"?
>
It sounds like you have poorly written queries running against the
database. With proper indexes and efficient coding, your tables should
be able to handle literally MILLIONS of rows. High tempdb utilization
indicates the use of temp tables or large ordering/grouping operations.
I would begin here:
http://www.sql-server-performance.com/sql_server_performance_audit.asp
Use Profiler to identify the query that produces the highest number of
reads over an hour timespan, preferably a busy time of day. Determine
why that query produces the load that it does, and focus on optimizing
it. Repeat this process until your performance has improved.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Sarah,
Yes, you can leave the tables on the same disk. If by "Trunk database" you
want to shrink the database, no
that won't do anything other than reduce the current size of the db once
you've removed the historical data. So
unless disk space is an issue it won't do much for you. However, after
removing so many records from the table you might benefit from a either a
defrag or a rebuild on your indexes.
Tracy makes a valid point as well, you might want run profiler to look for
the offending query. My suggestions before we made under the assumption that
you already knew which query was slow based on the fact that you mentioned
the database only had 2 tables. I assumed that meant you probably didn't
have too many queries to weed through. But you should definately check the
query and verify the execution plan to make sure it is taking advantage of
your indexes. You want to see index seeks. If it is doing table scans check
your query. If you are seeing index scans it could still be the queries but
you'll want to start looking at the index and make sure they are optimal.
Mark