SQL Performance

[ABerkowicz]

Hi - I have a question. We have had some performance issues with an application and have done a lot of traces. if there aren't any performance problems, the application routine processes in minutes. When there are performance issues, the application processing can take over 2 hours.

It appears to be very spuratic but the trend is that when performance issues happen, the amount of time to process is consistently growing and that the trace revealed that a specific query executes about 3000 + times.

When we take a backup of the data and restore it to a test environment, we do not see the performance issue on the test server. So, here are some questions:

We have a lot of integrations that write to the table in the query causing 3000 + writes. I am wonderfing if indexes are defraged and/or the fill factor can cause the issue on production but not on test/development machines.

Just looking for some other ideas on why production has issue but the same dataset would not have issue on separate machine...we will be looking at hardware and other services running and competing for same resources - At first, we didnt see anything....

Any thoughts are welcomed. Thanks!!!

 

[Qik3Coder]

You may want to look at load. If you are calling the query 3k times, the first thing you will want to do is check your design.

The second thing, make sure you aren't unecessarily locking the table. With 3k hits to a sproc/query you are bound to hit a lock issue if anyone else even thinks about touching the same table.

If [blue]you have problems[/blue], I want [green]source code[/green] AND [green]error messages[/green], none of this [red]"there was an error crap"[/red]