HELP with large databases (desprate)

We have an SQL server with 5 databases on it. Databases 1 - 4 share data wit
h
database 5. The SP in databases 1- 4 directly link via the query to database
5.
These databases have tables anywhere from 1 to 40 million records per table.
Database 5 is where mist of the multi million row tables are. We constantly
deal with slow downs and hiccups where everything will run fine one minute
come to a crawl the next minute and then run fine again. Anytime we do a
nightly import, on average about 20,000 records, we seem to have to run a
quick indexing process to get things to speed up again. Our users will
complain about watching the paint dry one minute and praising use within the
same day for it going fast again. I have used EM Profiler to try and tweak
the performance of queries as much as possible.
We are trying to get linked servers to work, but this is a massive
undertaking b/c the SPs for Databases 1- 4 are so interwoven with database 5
it almost requires a rewrite. ? I use a hosting company for my personal SQL
server that has 75 databases just one server that my database is on. It
always seems lightning fast. Yet On a server with just 5 databases we are
dying here.
Have any of you dealt with databases this large? Can you give some
suggestions on how to maintain performance will we are testing to use linked
servers?
So far linked servers are giving me a headache. I can only get SQL Analyzer
to create the SP. If I try to build one use .NET and save it, it won’t sav
e
b/c it cant start a distributed transaction.
--
JP
.NET Software DeveloperJP,
Comparing a 75 database system to a 5 database system may or may not be
comparing "apples to apples" depending on the HW resources, software
(including SPs), DB and table sizes, indexing, etc.. etc...

>Our users will complain about watching the paint dry one minute and
>praising use within the
> same day for it going fast again.
This sounds like it could be a) a resource issue b) a blocking issue. You
would need to use Profiler, Sysmon, Current Activity and procs like sp_who,
sp_who2 and sp_blockcnt to help determine the cause.
You *might* also be dealing with a fragmentation issue. Run DBCC SHOWCONTIG
to help determine this. Also, ensure that the statistics for the indexes
are current and up-to-date.
Using linkedservers, IMO, may or may not help increase performance depending
on the cause of the performance issue. I would address the above items
first prior to switching over to distributed systems.
HTH
Jerry
"JP" <JP@.discussions.microsoft.com> wrote in message
news:79692A32-FA3E-43AA-A1D4-9B033BE54393@.microsoft.com...
> We have an SQL server with 5 databases on it. Databases 1 - 4 share data
> with
> database 5. The SP in databases 1- 4 directly link via the query to
> database
> 5.
> These databases have tables anywhere from 1 to 40 million records per
> table.
> Database 5 is where mist of the multi million row tables are. We
> constantly
> deal with slow downs and hiccups where everything will run fine one minute
> come to a crawl the next minute and then run fine again. Anytime we do a
> nightly import, on average about 20,000 records, we seem to have to run a
> quick indexing process to get things to speed up again. Our users will
> complain about watching the paint dry one minute and praising use within
> the
> same day for it going fast again. I have used EM Profiler to try and tweak
> the performance of queries as much as possible.
> We are trying to get linked servers to work, but this is a massive
> undertaking b/c the SPs for Databases 1- 4 are so interwoven with database
> 5
> it almost requires a rewrite. ? I use a hosting company for my personal
> SQL
> server that has 75 databases just one server that my database is on. It
> always seems lightning fast. Yet On a server with just 5 databases we are
> dying here.
> Have any of you dealt with databases this large? Can you give some
> suggestions on how to maintain performance will we are testing to use
> linked
> servers?
> So far linked servers are giving me a headache. I can only get SQL
> Analyzer
> to create the SP. If I try to build one use .NET and save it, it won't
> save
> b/c it cant start a distributed transaction.
> --
> JP
> .NET Software Developer|||I have used Profilier to try and tweak the quries as much as possible. We di
d
a full defrag about a month ago that took 3 days almost.
Could you elabrate on "blocking"?
--
JP
.NET Software Develper
"Jerry Spivey" wrote:

> JP,
> Comparing a 75 database system to a 5 database system may or may not be
> comparing "apples to apples" depending on the HW resources, software
> (including SPs), DB and table sizes, indexing, etc.. etc...
>
> This sounds like it could be a) a resource issue b) a blocking issue. You
> would need to use Profiler, Sysmon, Current Activity and procs like sp_who
,
> sp_who2 and sp_blockcnt to help determine the cause.
> You *might* also be dealing with a fragmentation issue. Run DBCC SHOWCONT
IG
> to help determine this. Also, ensure that the statistics for the indexes
> are current and up-to-date.
> Using linkedservers, IMO, may or may not help increase performance dependi
ng
> on the cause of the performance issue. I would address the above items
> first prior to switching over to distributed systems.
> HTH
> Jerry
> "JP" <JP@.discussions.microsoft.com> wrote in message
> news:79692A32-FA3E-43AA-A1D4-9B033BE54393@.microsoft.com...
>
>|||You seemed to imply that the performance problem is sporatic (rather than
consistently slow). Investigate into whether or not you have blocking
(multiuser contention) issues. One easy method is to run sp_who2 when a
process that normally runs fast seems to be "hanging", and refer to the
[blkby] column.
INF: Understanding and Resolving SQL Server 7.0 or 2000 Blocking Problems
http://support.microsoft.com/defaul...kb;EN-US;224453
If you have a situation where some users are performing heavy duty reporting
type queries against tables that are active with inserts and updates by
other users, then consider implementing a daily snapshot of production just
for reporting purposes.
DBCC SHOWCONTIG will reveal if index fragmentation is an issue and DBCC
INDEXDEFRAG can be periodically issued to help minimize it.
Microsoft SQL Server 2000 Index Defragmentation Best Practices
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
This is somewhat involved, but SQL Server Profiler can be used for
performance realted traces:
http://msdn.microsoft.com/library/d...
ethowto15.asp
http://support.microsoft.com/defaul...kb;en-us;325297
http://msdn.microsoft.com/SQL/sqlpe...html/sp04j1.asp
If not already, look into splitting the location of large tables across
multiple files and drives.
http://msdn.microsoft.com/library/d...>
_02_2ak3.asp
Scalability and Very Large Database (VLDB) Resources
http://www.microsoft.com/sql/techin...calability.mspx
"JP" <JP@.discussions.microsoft.com> wrote in message
news:79692A32-FA3E-43AA-A1D4-9B033BE54393@.microsoft.com...
> We have an SQL server with 5 databases on it. Databases 1 - 4 share data
> with
> database 5. The SP in databases 1- 4 directly link via the query to
> database
> 5.
> These databases have tables anywhere from 1 to 40 million records per
> table.
> Database 5 is where mist of the multi million row tables are. We
> constantly
> deal with slow downs and hiccups where everything will run fine one minute
> come to a crawl the next minute and then run fine again. Anytime we do a
> nightly import, on average about 20,000 records, we seem to have to run a
> quick indexing process to get things to speed up again. Our users will
> complain about watching the paint dry one minute and praising use within
> the
> same day for it going fast again. I have used EM Profiler to try and tweak
> the performance of queries as much as possible.
> We are trying to get linked servers to work, but this is a massive
> undertaking b/c the SPs for Databases 1- 4 are so interwoven with database
> 5
> it almost requires a rewrite. ? I use a hosting company for my personal
> SQL
> server that has 75 databases just one server that my database is on. It
> always seems lightning fast. Yet On a server with just 5 databases we are
> dying here.
> Have any of you dealt with databases this large? Can you give some
> suggestions on how to maintain performance will we are testing to use
> linked
> servers?
> So far linked servers are giving me a headache. I can only get SQL
> Analyzer
> to create the SP. If I try to build one use .NET and save it, it won't
> save
> b/c it cant start a distributed transaction.
> --
> JP
> .NET Software Developer|||You mentioned that you have problems after running large imports. Are you
using a bulk insertion method that sets the database to bulk-logged recovery
or the equivalent?
If your users are off-line during imports (or there are few users) you can
consider dropping indexes for the insert and then rebuilding the indexes
after the insert is complete. This makes the insert faster and updates your
indexes at the same time.
"JP" wrote:

> We have an SQL server with 5 databases on it. Databases 1 - 4 share data w
ith
> database 5. The SP in databases 1- 4 directly link via the query to databa
se
> 5.
> These databases have tables anywhere from 1 to 40 million records per tabl
e.
> Database 5 is where mist of the multi million row tables are. We constantl
y
> deal with slow downs and hiccups where everything will run fine one minute
> come to a crawl the next minute and then run fine again. Anytime we do a
> nightly import, on average about 20,000 records, we seem to have to run a
> quick indexing process to get things to speed up again. Our users will
> complain about watching the paint dry one minute and praising use within t
he
> same day for it going fast again. I have used EM Profiler to try and tweak
> the performance of queries as much as possible.
> We are trying to get linked servers to work, but this is a massive
> undertaking b/c the SPs for Databases 1- 4 are so interwoven with database
5
> it almost requires a rewrite. ? I use a hosting company for my personal S
QL
> server that has 75 databases just one server that my database is on. It
> always seems lightning fast. Yet On a server with just 5 databases we are
> dying here.
> Have any of you dealt with databases this large? Can you give some
> suggestions on how to maintain performance will we are testing to use link
ed
> servers?
> So far linked servers are giving me a headache. I can only get SQL Analyze
r
> to create the SP. If I try to build one use .NET and save it, it won’t s
ave
> b/c it cant start a distributed transaction.
> --
> JP
> .NET Software Developer|||We run multiple sites with similar large databases, and not every day but
perharps once a month, do experience a client slowness that lasts for about
a few minutes.
We realised that the database was expanding during these few minutes (and on
IDE servers, perharps upto 20-30 minutes).
To correct / avoid this, we have:
(a) Tried to create the databases of a size we think will not need
auto-expansion at regular user hours
(b) Keep the transactional log file as trimmed as possible.
Also,
(c) If DB is defragmented due to time, create a backup and re-install it,
possibly on a new and non-OS shared HD - if possible SCSI
(d) Get good Server hardware - IDE will work, but performance not to the
mark for Large databases (but this only has an visualable impact if your DB
design is good, amongst other things)
Sailesh.
"JP" <JP@.discussions.microsoft.com> wrote in message
news:79692A32-FA3E-43AA-A1D4-9B033BE54393@.microsoft.com...
> We have an SQL server with 5 databases on it. Databases 1 - 4 share data
> with
> database 5. The SP in databases 1- 4 directly link via the query to
> database
> 5.
> These databases have tables anywhere from 1 to 40 million records per
> table.
> Database 5 is where mist of the multi million row tables are. We
> constantly
> deal with slow downs and hiccups where everything will run fine one minute
> come to a crawl the next minute and then run fine again. Anytime we do a
> nightly import, on average about 20,000 records, we seem to have to run a
> quick indexing process to get things to speed up again. Our users will
> complain about watching the paint dry one minute and praising use within
> the
> same day for it going fast again. I have used EM Profiler to try and tweak
> the performance of queries as much as possible.
> We are trying to get linked servers to work, but this is a massive
> undertaking b/c the SPs for Databases 1- 4 are so interwoven with database
> 5
> it almost requires a rewrite. ? I use a hosting company for my personal
> SQL
> server that has 75 databases just one server that my database is on. It
> always seems lightning fast. Yet On a server with just 5 databases we are
> dying here.
> Have any of you dealt with databases this large? Can you give some
> suggestions on how to maintain performance will we are testing to use
> linked
> servers?
> So far linked servers are giving me a headache. I can only get SQL
> Analyzer
> to create the SP. If I try to build one use .NET and save it, it won't
> save
> b/c it cant start a distributed transaction.
> --
> JP
> .NET Software Developer