Testing framework needed

[katbear]

Hi,

I am attempting to troubleshoot a "mysterious" performance problem. We have an Excel vba application that calls a function in SQL Server 2000.

The function performs very differently from Excel than it does from QA. That is, it's a lot slower from Excel. Also, the performance changes due to data loads in relevant tables that the function queries, (in both QA and Excel).

I am trying to come up with a LIST of all possible things I should be checking that could help me pinpoint WHY the performance between the two environments is so different.

So far I have:

* connection settings, such as arithabort (make sure these are the same!)

* table statistics (are they changing, thereby causing recompiles?)

* syscacheobjects (right now there are TWO different compile plans for the same function, even though I though setting arithabora ON from vba would solve the problem, but no)

* parameter sniffing

Any experience, advice greatly appreciated!!

 

[unclejimbob]

Parameter sniffing - don't know if this is of any particular help but I was caught by the parameter sniffing issue a few weeks ago; I had managed to solve it, I just didn't know why it was happening


Anyway for what its worth I wound up setting a local variable to the value of the parameter and then replacing the parameter where it was being used in the stored procedure query with the local variable. This is just one of the 'standard ways' to get around the problem and the most convenient (for me anyway). This could be something for you to try - not necessarily to fix your issue but certainly to eliminate one of the possible culprits.

good luck
ujb

 

[ESquared]

Study as much as you can about execution plans, the caches for these, recompilation, parameter sniffing, and so on.

And perhaps it's worth mentioning that you can get actual execution plans saved using Query Profiler, it's one of the data values that can be saved.

[COLOR=#aa88aa black]Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.[/color]