Tessitura Slowness

Prelude: Lately we were having some trouble with Tessitura slowness (users see hourglass and need to wait some time while making an order) and after doing some investigations we found that one of our local SQL job proc is taking more than a minute to run which was creating an occasional dead lock situation with the order processing. After adding an unlock hint to the local proc the deadlock issue was resolved but not the overall Tessitura slowness which caused the issue in the first place. The local proc have been running ok for the last couple of years and we did not do any major modification to it lately that will make it slower suddenly (in the last two months). Anyway, we resolved this slowness for this particular proc by creating an index on the local table which grew big over time and modifying the query a little bit.

Issue: Here is the real issue. When we run the same SQL job which is calling this particular proc in its original state (without the new index on the local table and no modification to the proc) in our test system (after doing a live to test copy and choosing a time when minimum to no transaction happens in our live system and no users are connected – so that we can replicate a similar scenario or close in live & test) we found that it takes only few milliseconds to execute this job in test but it takes more than a minute in live! Though theoretically it should run faster in live which has a much better hardware and software configuration (our live database server is a dedicated server with 32GB RAM vs 4 GB in test which is a virtual server). This leads us to believe that we may haven’t found the root cause of the Tessitura slowness yet. One explanation could be since the live server needs to handle additional service requests from different other servers, apps and many other SQL jobs that are not in test that’s why it’s taking more time to run the job. Could it be a plausible explanation for this paradoxical performance difference between test and live (few milliseconds vs more than a minute) in this particular case or there is more to it? Any thought or idea? All our live tables are regularly &  properly re-indexed, and there was no major changes in our live environment lately except moving the Tessitura client files and Tessitura database log shipping primary backup destination from one server to another server which I believe should not cause this slowness issue.

Advance thanks for any help or comment.

Mo

Natinal Ballet of Canada