Extremely Poor Query Performance - Identical DBs Different Performance

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 ***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 ***|||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 ***

eXtremely Long Time in Execution Query

Hi all,

I have a query, rather complex one to deal with more than 1 million rows, used to run 40 minutes in SQL Server 2000 in query analyzer. Now, it has been 10 hours in SQL Server 2005 in management studio. And still has not finished yet! Anything can go wrong here. Basically nothing changes, except for I have my server upgrade from SQL Server 2000 to SQL Server 2005. Seems something is wrong crazy in SQL Server 2005. Any suggestions?

Thanks,

Ning

Reminds me of some hotfix descriptions - Have you checked knowledgebase articles for "slow query"? Here's an example that relates to fast forward-only queries:

"FIX: The query performance is very slow when you use a fast forward-only cursor to run a query in SQL Server 2005": http://support.microsoft.com/default.aspx/kb/926024

|||

I found the reason, but not sure why, either do I have a way to fix that.

In one of my select sentence, I have a field, nvarchar(2000), if I exclude that field in the select statement then everything is back to speed. Otherwise, we are cralwing like 1000 lines per minute, so 1 million lines will be 1000 minute ... (without that field we are at 100,000 per minute). I am not sure why a field can cause such a big deal 100 times in performance diff ...

Microsoft has to explain this ...

Thanks,

Ning

|||

I am wondering if it is joining to the table containing this field early in the query and so having to carry the up to 2K a row of data round (for a million rows - that's up to 2G). This would probably show as an explosion in the size of tempdb (as this is where it is likely to be caching this data during the query).

Was it just the field or the table providing the field which you removed from the query. If it was the table you might try putting it at the bottom of the from clause and using the FORCE ORDER Query hint. However this will prevent the query planner rearranging any of the tables so you might want to put them in the order specified by the plan for the fast query without that field in.

Extremely complex Select statement

Hello,
I'm having real problems defining a SELECT statement for
the following scenario:
I have a Requirements table, each row in this table has
at least 1 row in the RequirementsWeeks table which
defines values for N weeks for each requirement.
Also, each requirement has a product value and a tool
value and each product belongs to a prodCategory and each
tool belongs to a ToolCategory (these associations are
stored in separate tables)
Here's an example of the records I have:
Requirements: RequirementsWeeks:
Product Tool Id ReqId Week Value
A TA 1 1 8 0.20
C TB 2 1 9 0.35
2 8 2.56
2 9 3.52
Products: Tools:
Id ProductName ProCategoryID Id ToolName ToolCatID
1 A 45 1 TA 68
2 B 53 2 TB 68
3 C 45
What i want to have is:
For all pairs product-tool in requirements that matches
ToolCatID And ProdCatID return the sum for week N
For example:
ToolCatID:68 And ProdCatID:45 the sum will return 0.20 +
2.56
And I need this to be done for All posible combinations
of ToolCatID and ProdCatID.
Any thoughts? I don't want to loop through a single SQL
statement that receives toolCat and prodCat as parameters,
I thought there's a way for SQL to manage this.
Thank you a lot for your time! I'm really desperate!Yes, exactly. Thanks a lot for your time, I've already
found a solution, which is to divide the problem in two
parts: The statement to select each row, and the one to
select that for all possible combinations of toolcat and
product cat(which was a very easy select). This seems to
work well but if you have a better idea please let me know.
Thank you so much for your time!! I really appreciate it!
Marcela
>--Original Message--
>So based on your example is this what you want the output
to be
>Toolcatid ProDCatID Sum Week
>68 45 0.2 + 2.56 8
>68 45 0.35+3.52 9
>68 53 0.2 + 2.56 8
>68 53 0.35+3.52 9
>
>"Marcela" <marcela.villalobos@.yahoo.com> wrote in message
>news:057b01c35bb1$af9e9310$a401280a@.phx.gbl...
>> Hello,
>> I'm having real problems defining a SELECT statement
for
>> the following scenario:
>> I have a Requirements table, each row in this table has
>> at least 1 row in the RequirementsWeeks table which
>> defines values for N weeks for each requirement.
>> Also, each requirement has a product value and a tool
>> value and each product belongs to a prodCategory and
each
>> tool belongs to a ToolCategory (these associations are
>> stored in separate tables)
>> Here's an example of the records I have:
>> Requirements: RequirementsWeeks:
>> Product Tool Id ReqId Week Value
>> A TA 1 1 8 0.20
>> C TB 2 1 9 0.35
>> 2 8 2.56
>> 2 9 3.52
>> Products: Tools:
>> Id ProductName ProCategoryID Id ToolName
ToolCatID
>> 1 A 45 1 TA 68
>> 2 B 53 2 TB 68
>> 3 C 45
>>
>> What i want to have is:
>> For all pairs product-tool in requirements that matches
>> ToolCatID And ProdCatID return the sum for week N
>> For example:
>> ToolCatID:68 And ProdCatID:45 the sum will return
0.20 +
>> 2.56
>> And I need this to be done for All posible combinations
>> of ToolCatID and ProdCatID.
>> Any thoughts? I don't want to loop through a single SQL
>> statement that receives toolCat and prodCat as
parameters,
>> I thought there's a way for SQL to manage this.
>> Thank you a lot for your time! I'm really desperate!
>>
>
>.
>