Extremely Poor Query Performance - Identical DBs Different Performance

[Brian Tabios]

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

*** Sent via Developersdex http://www.developersdex.com ***

[simonjw83]

Brian
Before looking at hardware try running
UPDATE STATISTICS tablename for relevant tables with indexes
and see if it makes a difference to performance
Brian Tabios wrote:

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

*** Sent via Developersdex http://www.developersdex.com ***

[Henrik Fyhn]

I've seen this before and acutally it's quite common.

The key to your problem is different execution plans.

A SP can have several copies of a execution plan.

a) Different SET statements to the connection
b) You don't call the SP from QA with proper owner prefix (e.g. dbo)
c) In a multitple CPU environment you will have one scheduler (UMS) for
each SPID, and you might experience that you get the same exection plan
until your thread is closed.

Make sure that you update the statistics whenever you experience such
problems. The stats are stored in server, not in the databases.

So what do you do?
If a procedure gets slow, you can recompile the procedure with
sp_recompile. If this doesn't help, use DBCC FREEPROCCACHE and run the
procedure agin.

SP's are often recompiled in an OLTP environment. Common reason is
change in statistics. Sometimes the optimizer makes a poor choice in
execution plan due to variance in the parameteres it recevies when
recompiling. It's smart to look into what parameters are sent to the
procedure and see if there are great changes. Also pay attention to
complex procedures with if-else and case-statements. Keep it simple!

Regards,
Henrik

*** Sent via Developersdex http://www.developersdex.com ***