If you open the stored procedure and break it down into parts, is there any part that runs slower on LIVE than TEST, or would you say the whole thing is proportionally as slow on LIVE?
Is it a TESSITURA SP or a local one that uses local tables. I was wondering if I ran it against our TEST system I could see how long it takes to run here.
If the SP does Inserts/Deletes/Updates, are there any triggers on the tables that doesn’t exist in TEST?
From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Mohiuddin Faruqe Sent: 22 October 2013 15:32 To: Wayne Evans Subject: RE: [Tessitura Technical Forum] Tessitura Slowness [Scanned]
We had tried this but it did not work out - dropped all the cached execution plans for this particular procedure by running DBCC FREEPROCCACHE command in both Live and Test, and ran the SQL job with the pre-optimized version of this procedure – still it was taking more than a minute in Live (a dedicated server with 32GB RAM) but only few milliseconds in Test (a virtual serve with 4GB RAM)! Still trying to find out the reason for this anomaly.
Mo
From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Mohiuddin Faruqe Sent: Monday, October 21, 2013 11:14 AM To: Mohiuddin Faruqe Subject: RE: [Tessitura Technical Forum] Tessitura Slowness
Thanks Mark for sending this. This is close to the issue we were having and may also explain our situation. We will try this and see how it goes for us.
Best,
From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Mark Ridley Sent: Monday, October 21, 2013 10:03 AM To: Mohiuddin Faruqe Subject: RE: [Tessitura Technical Forum] Tessitura Slowness
Hi
We had a similar issue with one of the standard reports where a report would not complete if run in the client but the sp worked fine on the DB.
When copying live over test it worked fine in both DB and client. This was with ap_raise_bills sp (for direct debits)
We ran the following script
SELECT plan_handle, st.text FROM sys.dm_exec_cached_plans CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st WHERE st.text LIKE N'%ap_raise_bill%';
Found that there were 2 query plans in the database.
We then ran DBCC FREEPROCCACHE(plan_handle) to clear each plan and this resolved the issue.
Mark
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
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.
Natinal Ballet of Canada
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!
-- View this message online at http://www.tessituranetwork.com/Community/forums/p/10420/32316.aspx#32316 or reply to this message -- 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!