My task is to convert a legacy Stored Procedure written in SQL Server 2005 so that it can be used in parallel (multiple executions from different users). As of now the SP is designed and written to run one instance at a time. If other people have to call it then they have to wait for the first execution to be completed.
The SP is not using any sort of transactions or isolation levels or locks on resources.
The code is about 8000 lines. But it is not a single SP. It is a collection of many SPs, functions, Table Valued Functions over tables and views. The execution starts from a single parent SP and then based on the huge list parameters provided, around 50 different SPs get called during the execution. The call hierarchy can go to 3 to 4 levels deep in some cases.
The SP works over a bunch of tables (empty in the beginning) and fills it with data by calculations done over existing data in other tables and at the end empties them after transferring the output to other database. Most of the execution is done over and over again on these tables.
There is no error handling of any sort other than the basic check over the return value in the front end C# application.
I have spent a few days studying and understanding the SP and its flow. The purpose of the SP is to calculate a financial benchmark by analyzing already stored data wrt the provided parameters and eventually transfer the results to another database and truncate the temporary (but real) tables. These temporary tables are real tables with 'tmp_' prefix attached to them. The relations between these tables is pretty complex so it is out of question to translate everything to actual temporary tables or table variables.
I would like to express my doubts and would appreciate some expert opinion regarding my choices and any room(s) for improvement.
I have experience in developing database centered applications. Also I have designed database systems from scratch. I have used transactions but never locking. Based on my experience and knowledge obtained from The Interwebs (SO etc.) I think I must follow these guidelines to avoid any (existing/future) deadlocks:
- Ensure the database design is properly normalized.
- Have the application access database objects in the same order every time.
- Keep transactions as short as possible.
- During transactions, don't allow any user input.
- Avoid cursors.
- Consider reducing lock escalation by using the ROWLOCK or PAGLOCK hint.
- Consider using the NOLOCK hint to prevent locking.
- Use as low a level of isolation as possible for user connections.
The SP is running over a very complex database and changing its architecture other than adding a column is not an available option as the database is also being used by other parts of the application.
If I add too many transactions, I think that will impact the overall system performance. So I should manage a good balance between transactions and non-transactional code.
Cursors is a real issue as most of the SP code is running on cursors. I read that a few might be translated to CROSS/OUTER APPLY. But that would also be a big task as it is common in the code to find many unions on different and same tables inside the cursors.
To avoid deadlocks (which is what is happening right now, if I force multiple execution of the SP in its current form) I am planning to do the following at least:
- Put transactions inside internal SPs. Eventually have to place some nested transactions as well.
- I am going to use sp_getapplock() in very critical places with proper timeouts.
- Most of the critical tables are using Clustered PK indexes and thats all. I might have to create a few non-clustered index on columns used in the where clauses under joins for performance concerns.
- I may have to put retry logic in transactions to avoid dead stops in case of instances becoming deadlock victims. Appropriate isolation will have to provided as well.
TLDR: Have to convert legacy serial SP to parallel. Insights from experts is what I seek.
Aucun commentaire:
Enregistrer un commentaire