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
Thanks Wayne.
From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Wayne Evans Sent: Friday, October 18, 2013 8:47 PM To: Mohiuddin Faruqe Subject: RE: [Tessitura Technical Forum] Tessitura Slowness
DO you mirror the dB as well as log ship. That will have a significant affect if the mirror is lower spec. Log shipping should make no difference.
Is it all queries/SPs or just one. of its just one, even though you've rebuilt the indexes script out a copy and delete and re-add (hope its not a big table) I've had times where it all appears OK and indexes report back fine, but the query in the so was so simple it could only be them.
Are your stats up to date. I'm not near a PC, but I think they are just below indexes.
If it's all query's/SP's, I'd be looking at server side. Is it a real or virtual server. If its real have you any growth restrictions on tempdb (unlikely). Are there any fail lights on the disk. If you have HP servers, is the disk cache battery connected and caching enabled (speeds up IOps)
If its virtual, what else is on that server. How many CPUs have you given that server (if it's virtual dB this may be the cause- I can go into it more if this is the first the case) vs how many have you allocated for others. Have you over provisioned the memory
Finally, if you open task manager on LIVE, is your allocated memory around to or lower than 30GB. If it's over SQL may have taken all the memory and left the O/S with nothing.
Hope some of these suggestions point you a bit closer
From: Mohiuddin Faruqe <bounce-mohiuddinfaruqe8297@tessituranetwork.com> Sent: 10/18/2013 12:42:58 PM
Thanks Wayne for your suggestion. Yes, we rebuilt/reorganized all the indexes. We also had checked the execution plan of the query before running it from live and test – in both cases it had the same execution plan.
We had installed the v12 Data Migration Toolkit about a month or so ago – this was the only other change we made in our live environment in addition to moving the Tess client and log shipping files to different servers. But we installed the toolkit in both live and test – so affect should be the same, if there was any.
From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Wayne Evans Sent: Friday, October 18, 2013 12:49 PM To: Mohiuddin Faruqe Subject: Re: [Tessitura Technical Forum] Tessitura Slowness
Could be a broken index (Reorganise All & Rebuild All from individual table --> indexes) or an external factor (like you mention).
If it doesn't change data, you can run this process in TEST and LIVE from management studio and turn on "Actual Execution Plan"
Management Studio --> New Query, type: exec mystoredprocedure parameter1, parameter2
Query menu --> Include Actual Execution Plan.
Execute the query
Compare the results from your LIVE and TEST "Execution Plan" tab results. Chances are you will spot which bit is slow and why from there.
From: Mohiuddin Faruqe <bounce-mohiuddinfaruqe8297@tessituranetwork.com> Sent: 10/18/2013 11:13:50 AM
This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!