Very Poor Performance - Identical DBs but Different Performance

[BTabios]

Hello Everyone,

I have a very complex performance issue with our production database.
Here's the scenario. We have a production webserver server and a
development web server. Both are running SQL Server 2000.

I encounted various performance issues with the production server with
a particular query. It would take approximately 22 seconds to return
100 rows, thats about 0.22 seconds per row. Note: I ran the query in
single user mode. So I tested the query on the Development server by
taking a backup (.dmp) of the database and moving it onto the dev
server. I ran the same query and found that it ran in less than a
second.

I took a look at the query execution plan and I found that they we're
the exact same in both cases.

Then I took a look at the various index's, and again I found no
differences in the table indices.

If both databases are identical, I'm assumeing that the issue is
related to some external hardware issue like: disk space, memory etc.
Or could it be OS software related issues, like service packs, SQL
Server configuations etc.

Here's what I've done to rule out some obvious hardware issues on the
prod server:
1. Moved all extraneous files to a secondary harddrive to free up space
on the primary harddrive. There is 55gb's of free space on the disk.
2. Applied SQL Server SP4 service packs
3. Defragmented the primary harddrive
4. Applied all Windows Server 2003 updates
Here is the prod servers system specs:
2x Intel Xeon 2.67GHZ
Total Physical Memory 2GB, Available Physical Memory 815MB
Windows Server 2003 SE /w SP1

Here is the dev serers system specs:
2x Intel Xeon 2.80GHz
2GB DDR2-SDRAM
Windows Server 2003 SE /w SP1

I'm not sure what else to do, the query performance is an order of
magnitude difference and I can't explain it. To me its is a hardware or
operating system

related issue.

Any Ideas would help me greatly!

Thanks,
Brian T

[Erland Sommarskog]

(BT*****@gmail.com) writes:

I encounted various performance issues with the production server with
a particular query. It would take approximately 22 seconds to return
100 rows, thats about 0.22 seconds per row. Note: I ran the query in
single user mode. So I tested the query on the Development server by
taking a backup (.dmp) of the database and moving it onto the dev
server. I ran the same query and found that it ran in less than a
second.

I took a look at the query execution plan and I found that they we're
the exact same in both cases.

Now, that's so amazing, that I would double-check.

The only thing I can think of beside faulty hardware is severe
ragmentation, but in such case the fragmentation would be carried over with
the backup. I note your post that you mention defragmening the
primary hardware. That sounds like your running defrag on file-system
level. This can be a good thing, but the most important is to run
defragmentation with DBCC DBREINDEX or INDEXDEFRAG within the database.

Of course, it could be the case that the production machine is completely
swamped with work, but I guess you would have noticed this.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Stu]

Building on Erland's suggestion that the production server might be
swamped, are you pulling data that might be affected by query
contention? In other words, are any of the tables you reference in
your query also involved in other complex queries? If someone were
updating that table, for example, your query could have been impacted
by blocking.

Just a thought,
Stu
Erland Sommarskog wrote:

(BT*****@gmail.com) writes:

I encounted various performance issues with the production server with
a particular query. It would take approximately 22 seconds to return
100 rows, thats about 0.22 seconds per row. Note: I ran the query in
single user mode. So I tested the query on the Development server by
taking a backup (.dmp) of the database and moving it onto the dev
server. I ran the same query and found that it ran in less than a
second.

I took a look at the query execution plan and I found that they we're
the exact same in both cases.

Now, that's so amazing, that I would double-check.

The only thing I can think of beside faulty hardware is severe
ragmentation, but in such case the fragmentation would be carried over with
the backup. I note your post that you mention defragmening the
primary hardware. That sounds like your running defrag on file-system
level. This can be a good thing, but the most important is to run
defragmentation with DBCC DBREINDEX or INDEXDEFRAG within the database.

Of course, it could be the case that the production machine is completely
swamped with work, but I guess you would have noticed this.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx