Hello all -
So the last few days our localized "update ticket history" table procedure has taken over a day and half to update our history table. So... I know the proc is working at least! But a proc that runs for 1 day, 12 hours + doesn't do us any good with as many exchanges we have.
The localization we have is two fold and it's cosmetic only: We like our seat location to read "Sec: 1, Row A, Seats 101,102" rather than the Tessitura standard of (what I believe to be) '1 A 102,103'. to get that, all i did was copy the FT_HISTORY_LOCATION and add in the text for "Sec " and "Row ".
The second localization is simply the TP_UPDATE_TICKET_HISTORY proc modified to pull it's location data from the localized Function Table rather than the standard Function Table.
So maybe it's pulling too much data? We do have 6 seasons running with a 14 month date range (to catch the earliest of sales). So the questions here are:
- Do you break out your jobs to one for each season?
- How many days back do you go? We used to go 30 but I'm cutting it to 10 now that the proc is taking so long in hopes that helps
- How long does your proc usually take?
Any input is appreciated so i can see if I'm just way off the course or if I need to mod these jobs from here on out to go by season individually. Thanks!
RJ
Have you tried running the update procedure manually in SSMS with the parameters that are usually being used? What could potentially be happening here is that a bad plan has gotten stuck in the plan cache for this procedure that is causing SQL server to use join types that are not optimal for typical runs of the procedure.
If you do see the code only taking long to run when part of the typical procedure, you may have a parameter sniffing or plan cache issue. "Slow in the Application, Fast in SSMS?" is a masterclass in how these issues work. You may also want to grab the Brent Ozar First Responder Kit and run sp_BlitzCache to find the execution plan being used by your query. BlitzCache can show you what command to run to purge just that cached plan from the database (instead of purging the ENTIRE plan cache, which will slow things down until the server has had a chance to recompile everything that was previously in the cache -- this is equivalent to what happens to the plan cache after a restart).