i have an application (vb.net console app) which loads data from text files
into a sql server 200 db. it performsn many upddates, deletes and inserts
very quickly; and we notice that with time, especially for the updates, the
app slows down to a crawl.

what can be done to sql server settings or db settings to improve the
perofmrnace for this type of app. itthe server is only used for this prpose,
and the data is then replicated to another server. is this whats refrred to
a OLAP applicaiton?


thanks
stephane

Report this post to a moderator

Hi,

> i have an application (vb.net console app) which loads data from text
files
> into a sql server 200 db. it performsn many upddates, deletes and inserts
> very quickly;

Try to re-consider indexes. Probably you have too many of them.
Then you can conside moving your transaction log file to separate disk and
use different filegroups for different tables and indexes.

> itthe server is only used for this prpose,
> and the data is then replicated to another server.

What kind of replication do you use? You may need to reconsider type of the
replication.

> is this whats refrred to
> a OLAP applicaiton?

It seems to me your system is pure OLTP.

Oleg.

Report this post to a moderator

Hi Stephane,

Consider parallel bulk inserts, for which you will need to have multiple
source files upon multiple disks (or multiple clients), and you will need
to follow:
{
http://msdn.microsoft.com/library/e...tun_1a_5gyt.asp
http://msdn.microsoft.com/library/e...pt_bcp_1968.asp
http://msdn.microsoft.com/library/e...pt_bcp_14fn.asp
}
Further information can be found in BOL by searching for >parallel bulk
insert<.

If the above will be difficult to implement (because clients may see a
performance hit due to the required temporary lack of most indexes, and/or
because clients will be blocked), then instead consider loading that data
into another permanent table and finally use a couple of sp_rename
executions (that are done to rename the current production table to
something like [drop this table after bulk insert and the next rename are
done] and to rename this other permanent table to the current production
table's name).

In addition or otherwise, you should be able to determine the source of a
slowdown by using the SQL Server counters documented within
http://msdn.microsoft.com/library/e...rfmon_89x0.asp,
http://msdn.microsoft.com/library/e...rfmon_0ugk.asp, and
http://msdn.microsoft.com/library/e...rfmon_9g6s.asp.

Thanks,

Bill Hollinshead
Microsoft, SQL Server

This posting is provided "AS IS" with no warranties, and confers no
rights. Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

Report this post to a moderator