CODEWITHSUNDEEP
sqlsql-serverperformancestored-procedureslong-running-processes
developer
developer

Reputation: 1675

Performance impact of long running stored procedure

I have a requirement to call a stored procedure which can take up to 15 minutes for each call on SQL Server 2008 database.

Will it cause performance impact on other systems accessing the same database / database server? If so, how can I minimise the performance impact?

Update: The store procedure compares audit history records and it needs to call another stored procedure depending on the type of the record so it has to be done row by row. I can change the cursor to while loop however there could be more than 3 million rows to process (depending on the filter criteria).

Upvotes: 0

Views: 2360

Answers (4)

Ethan Li
Ethan Li

Reputation: 1001

We have similar issue with our server. My experience is SQL server is quite resource intensive application, if you have such a sp, it will have a huge impact on the server's performance.

Here are few things you can check to improve performance of the sp, or make use less resource:

1, run query analyzer to check if you are missing indexes, add them if any.

2, if you use temp table, try not to use it. Tempdb is use by SQL server for every database, if you use it a lot in your sp, when it runs, it is going to block other queries which use temp table. Alternatively, you can use cte.

3, redo you sp to make smaller, perhaps you can break it into few smaller one which can be run in a parallel.

4, this is a wired one. If you pass parameters to the sp, in the sp, assign these parameters in to local variables, and use these variables in the sp, rather than the parameters. SQL server generates better execution plan with local variables. You will see huge speed improvement and. You won't believe it.

If you can post the sp, I am happy to help you to make faster

Upvotes: 0

Metaphor
Metaphor

Reputation: 6405

I can usually get away with not using a cursor by using a join with the list of IDs that need to be processed. Your cursor query can usually serve as a WITH clause or an internal join to select the rows to be processed within the statement that does the processing.

But do check the indexes first. That's the most detrimental cause of slow queries. At least every column in the where clauses and all ON columns in joins should be indexed.

Upvotes: 2

CathalMF
CathalMF

Reputation: 10055

Option 1. Run as is but this will most likely cause performance issues.

Option 2. Rework query to improve performance if possible. Surely it cant be taking 15 minutes unless your SQL is badly written or you are just working with massive amounts of data. Maybe post the SQL??

Option 3. As you mentioned you could do the work in .NET.

Upvotes: 0

Metaphor
Metaphor

Reputation: 6405

Take a look at the CPU activity on the server when the proc is active. If you know how to use PerfMon, you can look at some SQL Server counters too.

I'd say that a 15 minute query is probably a candidate for optimization. Make sure you have all necessary indexes and avoid using cursors.

Upvotes: 0

Related Questions