The Subs Renewal Notices procedure takes an awful bit longer now. In v10, it took maybe 10-12 minutes. Now in v11 it runs for an hour.
Hi David,
Here are two things you may want to consider:
1. Have you used SQL Profiler while running Subs Renewal Notices? If not, that may give some insight as to what part of the procedure is taking so long, which may be helpful in identifying next steps. When I use SQL Profiler, I often use the Tuning template and then customize it based on what I’m looking for. (Although it is often useful as-is.)
2. After our v11 migration, there were some things that were running way longer than they should. After I narrowed down more specifically what statements were troublesome, I found some particular joins were not working as efficiently as they should. To make a long story short, I rebuilt all indexes using the procedure Tessitura has for that (I do not remember its name offhand). That resolved the issue. We have a procedure that reorganizes or rebuilds indexes based on the level of fragmentation, but I suspect due to the number of changes v11 made, that an index or two was damaged or corrupted in some way. Of course, if you already run Tessitura’s procedure for rebuilding all indexes on a scheduled basis, then that obviously is not the issue!
I hope that helps!
Thanks,
David
From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of David Vivino Sent: Thursday, March 14, 2013 5:45 PM To: David Frederick Subject: [Tessitura Technical Forum] Long Wait for Renewal Notices
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!
I would suggest indexes also. You can **against TEST** capture the entire process using profiler, save the results to a file and in mgt studio on the tools menu, there is an analyse and recommendation tool (ive forgotten the name - will check when I get in). You basically load the profiler output into this, point at your testdb and run and it will give you recommendations.
The tool is called "Database Engine Tuning Advisor".
As David F says, you will need to rebuild your indexes periodically to keep this up-to-date. I think ours are checked each night and if they are too fragmented (I forget the percentage value we've set as our threshold), they get rebuilt.