Performance Question

Hi

We have a SQL server on Win2k. the physical size of the db is about
40G and the main table has approx 65m rows in it. At the moment the
entire database is on one data file. The entire server including the
OS is on a RAID 0 array with one RAID controller.

My question is would I get any performance benefit if I was to have
more than several data file rather than one big data file, bearing in
mind that there is only one disk controller.

I am mainly interested in read performance rather than write.

I am just starting out with MS SQL

Thanks for any info<rc@.die@.you@.!spammers.sandworm.demon.co.uk> wrote in message
news:3ephc0pgs6bq4rpfrmphsi0ibck95factj@.4ax.com...
> Hi
> We have a SQL server on Win2k. the physical size of the db is about
> 40G and the main table has approx 65m rows in it. At the moment the
> entire database is on one data file. The entire server including the
> OS is on a RAID 0 array with one RAID controller.
> My question is would I get any performance benefit if I was to have
> more than several data file rather than one big data file, bearing in
> mind that there is only one disk controller.

My understanding is with SQL 6.0 and I think 7.0 this would make a
difference (as SQL could have multiple file pointers).

This is no longer the case with SQL 2000.

Also, RAID 0 is a disaster waiting to happen.

Remember, if you have a 2 disk RAID 0 set, you've just doubled your chances
of a catastrophic failure.

If you have a 3 disk set, it's 3x, etc.

> I am mainly interested in read performance rather than write.

Well, first move to a "real" RAID solution, 1, 10, 5 or the like.

Separate out files (say OS, data, logs) to separate PHYSICAL arrays (doesn't
help to repartition your existing array into 3 logical disks... you don't
get any more physical heads.)

And make sure you have RAM. Lots of it.
> I am just starting out with MS SQL
> Thanks for any info|||Hi

To add to Gregs advice you may want to get yourself the following book:
http://www.microsoft.com/mspress/books/4944.asp

and this article and other:
http://www.sql-server-performance.c...re_planning.asp

This may also help
http://www.acnc.com/04_01_10.html

John

<rc@.die@.you@.!spammers.sandworm.demon.co.uk> wrote in message
news:3ephc0pgs6bq4rpfrmphsi0ibck95factj@.4ax.com...
> Hi
> We have a SQL server on Win2k. the physical size of the db is about
> 40G and the main table has approx 65m rows in it. At the moment the
> entire database is on one data file. The entire server including the
> OS is on a RAID 0 array with one RAID controller.
> My question is would I get any performance benefit if I was to have
> more than several data file rather than one big data file, bearing in
> mind that there is only one disk controller.
> I am mainly interested in read performance rather than write.
> I am just starting out with MS SQL
> Thanks for any info|||Sorry, the server is on a RAID 0+1 array, i.e the strip is mirrored
On Fri, 11 Jun 2004 06:16:55 GMT, "John Bell"
<jbellnewsposts@.hotmail.com> wrote:

>Hi
>To add to Gregs advice you may want to get yourself the following book:
>http://www.microsoft.com/mspress/books/4944.asp
>and this article and other:
>http://www.sql-server-performance.c...re_planning.asp
>This may also help
>http://www.acnc.com/04_01_10.html
>John
><rc@.die@.you@.!spammers.sandworm.demon.co.uk> wrote in message
>news:3ephc0pgs6bq4rpfrmphsi0ibck95factj@.4ax.com...
>> Hi
>>
>> We have a SQL server on Win2k. the physical size of the db is about
>> 40G and the main table has approx 65m rows in it. At the moment the
>> entire database is on one data file. The entire server including the
>> OS is on a RAID 0 array with one RAID controller.
>>
>> My question is would I get any performance benefit if I was to have
>> more than several data file rather than one big data file, bearing in
>> mind that there is only one disk controller.
>>
>> I am mainly interested in read performance rather than write.
>>
>> I am just starting out with MS SQL
>>
>> Thanks for any info
>>
>>
>|||Hi

If the controller has multiple channels it will help if you configure a
different sub-systems for OS and logs using new disks. These will share
resources on the controller so if you can add memory to it that will also
help. Adding extra disks to the current array will help spread the disk
usage more, but may not show as much gain as having a new sub-system there
is an example in "Microsoft SQL Server 2000T Performance Tuning Technical
Reference" that shows how adding a disk to an existing array can help.

John

Using multiple filegroups in their own files will help backup and
organisation.
"rc" <rc@.spam.com> wrote in message
news:i3pic0pca4dg6ga3cj4e1vr3as98e558uu@.4ax.com...
> Sorry, the server is on a RAID 0+1 array, i.e the strip is mirrored
> On Fri, 11 Jun 2004 06:16:55 GMT, "John Bell"
> <jbellnewsposts@.hotmail.com> wrote:
> >Hi
> >To add to Gregs advice you may want to get yourself the following book:
> >http://www.microsoft.com/mspress/books/4944.asp
> >and this article and other:
> >http://www.sql-server-performance.c...re_planning.asp
> >This may also help
> >http://www.acnc.com/04_01_10.html
> >John
> ><rc@.die@.you@.!spammers.sandworm.demon.co.uk> wrote in message
> >news:3ephc0pgs6bq4rpfrmphsi0ibck95factj@.4ax.com...
> >> Hi
> >>
> >> We have a SQL server on Win2k. the physical size of the db is about
> >> 40G and the main table has approx 65m rows in it. At the moment the
> >> entire database is on one data file. The entire server including the
> >> OS is on a RAID 0 array with one RAID controller.
> >>
> >> My question is would I get any performance benefit if I was to have
> >> more than several data file rather than one big data file, bearing in
> >> mind that there is only one disk controller.
> >>
> >> I am mainly interested in read performance rather than write.
> >>
> >> I am just starting out with MS SQL
> >>
> >> Thanks for any info
> >>
> >>
> >|||"rc" <rc@.spam.com> wrote in message
news:i3pic0pca4dg6ga3cj4e1vr3as98e558uu@.4ax.com...
> Sorry, the server is on a RAID 0+1 array, i.e the strip is mirrored

Ok, that's good. :-)sql

Performance problems with query

Guys,
I'm stumped. While its not pertinent to the
matter, we are running a Vignette content management
system on Win2k with Sql 2000 Enterprise on a cluster.
The server has 2 Gig of RAM , 2 CPU's and the database
size is 1.5G.
The query below is fired at login. The indexes
seem fine based on the query plan. When I look through
profiler, the query below takes a very high # of CPU
cycles and reads. It consistently takes more than 1.5
seconds to execute the query below. I did a dbcc pintable
for ALL the tables in the query and that did not help
either. It seemed to make it worse (3 seconds and above)
Any idea what could be the issue here? The server
is not really heavily taxed.
The tables are small. They have very few rows.
VGNCCB_ROLE 939
VGNCCB_ROLE_JT 62389
VGNCCB_GROUP_USER_JT 1364
The problem Query:
select
ROLE_ID,
NAME,
DESCRIPTION,
CREATE_DATE,
MODIFIED_DATE
FROM
vign.VGNCCB_ROLE -- Clustered Indexed on Role ID
WHERE
ROLE_ID in
(select ROLE_ID
FROM
vign.VGNCCB_ROLE_JT -- Non clustered indexes
on USER_NAME AND non clustered on GROUP_ID
WHERE
USER_NAME = 'testRole' or GROUP_ID in (select
GROUP_ID
FROM
vign.VGNCCB_GROUP_USER_JT -- Non clustered
index on USER_NAME
WHERE
USER_NAME = 'testRole'))
I'd appreciate it if someone could follow me in this
thread to completion. Such a simple query should not take
this long.
TIA,
Jack
.See my reply to your previous post from this morning.
"Jack A" <anonymous@.discussions.microsoft.com> wrote in message
news:fc6001c43e8b$7d505750$a301280a@.phx.gbl...
> Guys,
> I'm stumped. While its not pertinent to the
> matter, we are running a Vignette content management
> system on Win2k with Sql 2000 Enterprise on a cluster.
> The server has 2 Gig of RAM , 2 CPU's and the database
> size is 1.5G.
> The query below is fired at login. The indexes
> seem fine based on the query plan. When I look through
> profiler, the query below takes a very high # of CPU
> cycles and reads. It consistently takes more than 1.5
> seconds to execute the query below. I did a dbcc pintable
> for ALL the tables in the query and that did not help
> either. It seemed to make it worse (3 seconds and above)
> Any idea what could be the issue here? The server
> is not really heavily taxed.
> The tables are small. They have very few rows.
> VGNCCB_ROLE 939
> VGNCCB_ROLE_JT 62389
> VGNCCB_GROUP_USER_JT 1364
>
> The problem Query:
> select
> ROLE_ID,
> NAME,
> DESCRIPTION,
> CREATE_DATE,
> MODIFIED_DATE
> FROM
> vign.VGNCCB_ROLE -- Clustered Indexed on Role ID
> WHERE
> ROLE_ID in
> (select ROLE_ID
> FROM
> vign.VGNCCB_ROLE_JT -- Non clustered indexes
> on USER_NAME AND non clustered on GROUP_ID
> WHERE
> USER_NAME = 'testRole' or GROUP_ID in (select
> GROUP_ID
> FROM
> vign.VGNCCB_GROUP_USER_JT -- Non clustered
> index on USER_NAME
> WHERE
> USER_NAME = 'testRole'))
> I'd appreciate it if someone could follow me in this
> thread to completion. Such a simple query should not take
> this long.
>
> TIA,
> Jack
> .
>

Performance problems with query

Guys,
I'm stumped. While its not pertinent to the
matter, we are running a Vignette content management
system on Win2k with Sql 2000 Enterprise on a cluster.
The server has 2 Gig of RAM , 2 CPU's and the database
size is 1.5G.
The query below is fired at login. The indexes
seem fine based on the query plan. When I look through
profiler, the query below takes a very high # of CPU
cycles and reads. It consistently takes more than 1.5
seconds to execute the query below. I did a dbcc pintable
for ALL the tables in the query and that did not help
either. It seemed to make it worse (3 seconds and above)
Any idea what could be the issue here? The server
is not really heavily taxed.
The tables are small. They have very few rows.
VGNCCB_ROLE939
VGNCCB_ROLE_JT62389
VGNCCB_GROUP_USER_JT1364
The problem Query:
select
ROLE_ID,
NAME,
DESCRIPTION,
CREATE_DATE,
MODIFIED_DATE
FROM
vign.VGNCCB_ROLE -- Clustered Indexed on Role ID
WHERE
ROLE_ID in
(select ROLE_ID
FROM
vign.VGNCCB_ROLE_JT -- Non clustered indexes
on USER_NAME AND non clustered on GROUP_ID
WHERE
USER_NAME = 'testRole' or GROUP_ID in (select
GROUP_ID
FROM
vign.VGNCCB_GROUP_USER_JT -- Non clustered
index on USER_NAME
WHERE
USER_NAME = 'testRole'))
I'd appreciate it if someone could follow me in this
thread to completion. Such a simple query should not take
this long.
TIA,
Jack
..
See my reply to your previous post from this morning.
"Jack A" <anonymous@.discussions.microsoft.com> wrote in message
news:fc6001c43e8b$7d505750$a301280a@.phx.gbl...
> Guys,
> I'm stumped. While its not pertinent to the
> matter, we are running a Vignette content management
> system on Win2k with Sql 2000 Enterprise on a cluster.
> The server has 2 Gig of RAM , 2 CPU's and the database
> size is 1.5G.
> The query below is fired at login. The indexes
> seem fine based on the query plan. When I look through
> profiler, the query below takes a very high # of CPU
> cycles and reads. It consistently takes more than 1.5
> seconds to execute the query below. I did a dbcc pintable
> for ALL the tables in the query and that did not help
> either. It seemed to make it worse (3 seconds and above)
> Any idea what could be the issue here? The server
> is not really heavily taxed.
> The tables are small. They have very few rows.
> VGNCCB_ROLE 939
> VGNCCB_ROLE_JT 62389
> VGNCCB_GROUP_USER_JT 1364
>
> The problem Query:
> select
> ROLE_ID,
> NAME,
> DESCRIPTION,
> CREATE_DATE,
> MODIFIED_DATE
> FROM
> vign.VGNCCB_ROLE -- Clustered Indexed on Role ID
> WHERE
> ROLE_ID in
> (select ROLE_ID
> FROM
> vign.VGNCCB_ROLE_JT -- Non clustered indexes
> on USER_NAME AND non clustered on GROUP_ID
> WHERE
> USER_NAME = 'testRole' or GROUP_ID in (select
> GROUP_ID
> FROM
> vign.VGNCCB_GROUP_USER_JT -- Non clustered
> index on USER_NAME
> WHERE
> USER_NAME = 'testRole'))
> I'd appreciate it if someone could follow me in this
> thread to completion. Such a simple query should not take
> this long.
>
> TIA,
> Jack
> .
>

Performance problems with query

Guys,
I'm stumped. While its not pertinent to the
matter, we are running a Vignette content management
system on Win2k with Sql 2000 Enterprise on a cluster.
The server has 2 Gig of RAM , 2 CPU's and the database
size is 1.5G.

The query below is fired at login. The indexes
seem fine based on the query plan. When I look through
profiler, the query below takes a very high # of CPU
cycles and reads. It consistently takes more than 1.5
seconds to execute the query below. I did a dbcc pintable
for ALL the tables in the query and that did not help
either. It seemed to make it worse (3 seconds and above)

Any idea what could be the issue here? The server
is not really heavily taxed.

The tables are small. They have very few rows.

VGNCCB_ROLE939
VGNCCB_ROLE_JT62389
VGNCCB_GROUP_USER_JT1364

The problem Query:

select
ROLE_ID,
NAME,
DESCRIPTION,
CREATE_DATE,
MODIFIED_DATE
FROM
vign.VGNCCB_ROLE -- Clustered Indexed on Role ID
WHERE
ROLE_ID in
(select ROLE_ID
FROM
vign.VGNCCB_ROLE_JT -- Non clustered indexes
on USER_NAME AND non clustered on GROUP_ID
WHERE
USER_NAME = 'testRole' or GROUP_ID in (select
GROUP_ID
FROM
vign.VGNCCB_GROUP_USER_JT -- Non clustered
index on USER_NAME
WHERE
USER_NAME = 'testRole'))

I'd appreciate it if someone could follow me in this
thread to completion. Such a simple query should not take
this long.

TIA,
Jack
...[posted and mailed, please reply in news]

Jack A (InformixMail@.yahoo.com) writes:
> The query below is fired at login. The indexes
> seem fine based on the query plan. When I look through
> profiler, the query below takes a very high # of CPU
> cycles and reads. It consistently takes more than 1.5
> seconds to execute the query below. I did a dbcc pintable
> for ALL the tables in the query and that did not help
> either. It seemed to make it worse (3 seconds and above)

DBCC PINTABLE is a command that very rarely is useful. If you have a
situation that you have a table that is referred to rearely, but
when it is referred to, you want the answers directly. Then you
have a case. Since these tables are referred to at log in and small,
I would assume that they are in memory anyway.

I could think of a possible rewrites of the query, but since this appears
to come from a third-party app, you don't seem to have any use for
that.

Without having the full information about the tables it is difficult
to say, but if it is correct that VGNCCB_ROLE_JT does not have a
clustered index, I think it is time to add one, and that would be
on (ROLE_ID). That could make the two indexes on USER_NAME and GROUP_ID
covering for the query, and could save you some bookmark lookups.

Another idea is to build an indexed view, and hope that SQL Server
will find the indexed view when looking for a query plan. But I am
not sure this is possible. And in any case, you need to have Enterprise
Edition for this to work.

I would encourage you to post the complete CREATE TABLE and CREATE INDEX
scripts for the tables. That makes it a little easier to guess.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||OK , Here goes with the table structure. BTW I've run DBCC reindex.

TABLE: VGNCCB_ROLE
PK__VGNCCB_ROLE__1FA39FB9clustered, unique, primary key located on
PRIMARY -- ROLE_ID

TABLE: VGNCCB_ROLE_JT
index_nameindex_descriptionindex_keys
PK__VGNCCB_ROLE_JT__218BE82Bclustered, unique, primary key located on
PRIMARY- ID
VGNCCB_ROLE_JT_INDEX1nonclustered located on PRIMARY- USER_NAME
VGNCCB_ROLE_JT_INDEX2nonclustered located on PRIMARY- GROUP_ID

TABLE: VGNCCB_GROUP_USER_JT
index_nameindex_descriptionindex_keys
PK__VGNCCB_GROUP_USE__1DBB5747clustered, unique, primary key located
on PRIMARY- ID
VGNCCB_GROUP_USER_JT_INDEX1nonclustered located on PRIMARY -GROUP_ID
VGNCCB_GROUP_USER_JT_INDEX2nonclustered located on PRIMARY-
USER_NAME|||Jack A (InformixMail@.yahoo.com) writes:
> OK , Here goes with the table structure. BTW I've run DBCC reindex.

Thanks, but I explicitly asked for CREATE TABLE and CREATE INDEX statements.
That could permit me see if it is possible to build an indexed view.

Also, in VGNCCB_ROLE_JT, I can't even see that there is a ROLE_ID
column.

You can script tables and indexes in Enterprise Manager or Query Analyzer.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

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

Performance problems with query

Guys,
I'm stumped. While its not pertinent to the
matter, we are running a Vignette content management
system on Win2k with Sql 2000 Enterprise on a cluster.
The server has 2 Gig of RAM , 2 CPU's and the database
size is 1.5G.
The query below is fired at login. The indexes
seem fine based on the query plan. When I look through
profiler, the query below takes a very high # of CPU
cycles and reads. It consistently takes more than 1.5
seconds to execute the query below. I did a dbcc pintable
for ALL the tables in the query and that did not help
either. It seemed to make it worse (3 seconds and above)
Any idea what could be the issue here? The server
is not really heavily taxed.
The tables are small. They have very few rows.
VGNCCB_ROLE 939
VGNCCB_ROLE_JT 62389
VGNCCB_GROUP_USER_JT 1364
The problem Query:
select
ROLE_ID,
NAME,
DESCRIPTION,
CREATE_DATE,
MODIFIED_DATE
FROM
vign.VGNCCB_ROLE -- Clustered Indexed on Role ID
WHERE
ROLE_ID in
(select ROLE_ID
FROM
vign.VGNCCB_ROLE_JT -- Non clustered indexes
on USER_NAME AND non clustered on GROUP_ID
WHERE
USER_NAME = 'testRole' or GROUP_ID in (select
GROUP_ID
FROM
vign.VGNCCB_GROUP_USER_JT -- Non clustered
index on USER_NAME
WHERE
USER_NAME = 'testRole'))
I'd appreciate it if someone could follow me in this
thread to completion. Such a simple query should not take
this long.
TIA,
Jack
.See my reply to your previous post from this morning.
"Jack A" <anonymous@.discussions.microsoft.com> wrote in message
news:fc6001c43e8b$7d505750$a301280a@.phx.gbl...
> Guys,
> I'm stumped. While its not pertinent to the
> matter, we are running a Vignette content management
> system on Win2k with Sql 2000 Enterprise on a cluster.
> The server has 2 Gig of RAM , 2 CPU's and the database
> size is 1.5G.
> The query below is fired at login. The indexes
> seem fine based on the query plan. When I look through
> profiler, the query below takes a very high # of CPU
> cycles and reads. It consistently takes more than 1.5
> seconds to execute the query below. I did a dbcc pintable
> for ALL the tables in the query and that did not help
> either. It seemed to make it worse (3 seconds and above)
> Any idea what could be the issue here? The server
> is not really heavily taxed.
> The tables are small. They have very few rows.
> VGNCCB_ROLE 939
> VGNCCB_ROLE_JT 62389
> VGNCCB_GROUP_USER_JT 1364
>
> The problem Query:
> select
> ROLE_ID,
> NAME,
> DESCRIPTION,
> CREATE_DATE,
> MODIFIED_DATE
> FROM
> vign.VGNCCB_ROLE -- Clustered Indexed on Role ID
> WHERE
> ROLE_ID in
> (select ROLE_ID
> FROM
> vign.VGNCCB_ROLE_JT -- Non clustered indexes
> on USER_NAME AND non clustered on GROUP_ID
> WHERE
> USER_NAME = 'testRole' or GROUP_ID in (select
> GROUP_ID
> FROM
> vign.VGNCCB_GROUP_USER_JT -- Non clustered
> index on USER_NAME
> WHERE
> USER_NAME = 'testRole'))
> I'd appreciate it if someone could follow me in this
> thread to completion. Such a simple query should not take
> this long.
>
> TIA,
> Jack
> .
>

Performance problem on copy of db

I have a performance problem here I could really do with some help on.
I am running SQL Server 7 on Win2k on a Dual Xeon box.
I moved the database from a much older machine and had some initial problems
running a particular complex query because of parallelism. Essentially,
doing an sp_updatestats and using only 1 of the 2 CPUs for parallel
execution of queries solved these problems. Clearly, this was an issue with
the more powerful processors on the new box.
Anyhow, once the performance problem was solved I made a copy of the
database using Backup/Restore and then changes were made to the data in the
new database copy (essentially, the application that uses the db was rolled
out to a different group of users).
All was well when I was last involved in this project back in October.
Recently, however, the same performance problems reappeared following a
reboot. I managed today to solve the performance problems on the original
database by running sp_updatestats.
However, running sp_updatestats on the second database did not solve the
performance problems. I've tried making another copy of the well-performing
database and the copy performs fine. Not sure what I was trying to prove by
doing this, but I was running out of ideas!
I also did a copy of the badly performing db using backup and restore and,
surprise, surprise, this made no difference.
Now that several months have passed, the copy I made contains different data
to the original so I can't simply overwrite it.
Apart from running sp_updatestats, I'm at a bit of a loss in terms of what
else to try. To give you some idea of the magnitude of the problem, the
query in question takes 30 seconds to run on the "good" database and 30
minutes to run on the "bad" one.
Looking at the execution plan, SQL Server does it completely differently.
The good db involves lots of "nested loops/inner join" processes. The bad
db involves lots of "has match/inner join" processes and these, I think, are
what slow things down. The good db has a "subtree cost" of 3.43 whereas the
bad one has something like 250.
Any ideas appreciated.Paul Welsh wrote:

quote:

---

> Looking at the execution plan, SQL Server does it completely differently.
> The good db involves lots of "nested loops/inner join" processes. The bad
> db involves lots of "has match/inner join" processes and these, I think, a
re
> what slow things down. The good db has a "subtree cost" of 3.43 whereas t
he
> bad one has something like 250.

---

sounds like you could have different indexes on the two databases.
you should also read up on fragmentation (dbcc showcontig, dbreindex,
dbindexdefrag, and defragging heaps).

Performance problem on copy of db

I have a performance problem here I could really do with some help on.
I am running SQL Server 7 on Win2k on a Dual Xeon box.
I moved the database from a much older machine and had some initial problems
running a particular complex query because of parallelism. Essentially,
doing an sp_updatestats and using only 1 of the 2 CPUs for parallel
execution of queries solved these problems. Clearly, this was an issue with
the more powerful processors on the new box.
Anyhow, once the performance problem was solved I made a copy of the
database using Backup/Restore and then changes were made to the data in the
new database copy (essentially, the application that uses the db was rolled
out to a different group of users).
All was well when I was last involved in this project back in October.
Recently, however, the same performance problems reappeared following a
reboot. I managed today to solve the performance problems on the original
database by running sp_updatestats.
However, running sp_updatestats on the second database did not solve the
performance problems. I've tried making another copy of the well-performing
database and the copy performs fine. Not sure what I was trying to prove by
doing this, but I was running out of ideas!
I also did a copy of the badly performing db using backup and restore and,
surprise, surprise, this made no difference.
Now that several months have passed, the copy I made contains different data
to the original so I can't simply overwrite it.
Apart from running sp_updatestats, I'm at a bit of a loss in terms of what
else to try. To give you some idea of the magnitude of the problem, the
query in question takes 30 seconds to run on the "good" database and 30
minutes to run on the "bad" one.
Looking at the execution plan, SQL Server does it completely differently.
The good db involves lots of "nested loops/inner join" processes. The bad
db involves lots of "has match/inner join" processes and these, I think, are
what slow things down. The good db has a "subtree cost" of 3.43 whereas the
bad one has something like 250.
Any ideas appreciated.Paul Welsh wrote:
> Looking at the execution plan, SQL Server does it completely differently.
> The good db involves lots of "nested loops/inner join" processes. The bad
> db involves lots of "has match/inner join" processes and these, I think, are
> what slow things down. The good db has a "subtree cost" of 3.43 whereas the
> bad one has something like 250.
sounds like you could have different indexes on the two databases.
you should also read up on fragmentation (dbcc showcontig, dbreindex,
dbindexdefrag, and defragging heaps).