SQL Server: Stored procedure is taking time to execute once concurrent users execute or access from UI

MohitPandit
MohitPandit used Ask the Experts™
on
Hello Folks,

I've a stored procedure in SQL Server along with lots of calculation & computation. Once, I execute it takes about 11 seconds.
But once concurrent users executes then it takes average 42 seconds.
FYI,
1. I am having SQL Server 2017 at local and production with Azure' DB as a service.
2. Isolation level is "Read Committed Snapshot"
3. I've marked NOLOCK hint with all physical tables in stored procedure.
4. Yes, the procedure is also using Temp Tables too.

Do you have any thoughts, why it is taking lots of time?

Best Regards
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Mark WillsTopic Advisor, Page Editor
Distinguished Expert 2018

Commented:
Well, in SQL 2017 the NOLOCK hint shouldnt be needed.

It can actually create schema locks - which can be a lot more painful.

What is the SP doing ? Returning rows, updates, inserts, deletes - whatever else ? Would obviously be inserting into Temp tables, so its more about the interactions with live tables.

The temp tables are context / session sensitive so probably OK in principle, but could put more pressure on tempdb.

You should look at wait states and memory pressure. Possibly making the temp tables memory tables instead (even though they will use tempdb) or derived, and removing the NOLOCK query hint and check  wait states + IO + Memory/CPU before and after will help identify bottlenecks.

Also check any joins and where clauses to make sure they encourage indexes being used.

Would also be worthwhile altering the proc using RECOMPILE so it picks up latest query stats/plans.

Is it possible to see the stored procedure ?

Kinda difficult to say other than philosophically ponder potential problems.
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
>> Once, I execute it takes about 11 seconds. In my perspective, 11 seconds for a single execution in OLTP is a pretty long one and can get into these issues. Try to see whether this can be reduced further by tuning the queries.

How many records your query returns?

>> But once concurrent users executes then it takes average 42 seconds.

Is there any blocking identified within these concurrent sessions.
Share across the execution plan of the procedure or else check the Execution plan and create any missing indexes to improve the performance of the query.

>> Yes, the procedure is also using Temp Tables too.

How many records are written into the temp tables, if large consider creating indexes on these tables.
If small and can be avoided, kindly try avoiding the temp table..

Author

Commented:
Okay, thanks for your comments and feedback. First, I'll convert temp tables in derive tables and let you know.

Best Regards
Should you be charging more for IT Services?

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Mark WillsTopic Advisor, Page Editor
Distinguished Expert 2018

Commented:
Anything we can help you with ?

Author

Commented:
Actually, the procedure is calling nested procedure i.e. Monthly, Yearly and Daily.
So,
1. I made changes for business for default view like Monthly view (earlier I was rendering all three views at one go)
2. Converted temp tables into derive tables.

Now, the current status.
1. if execute main procedure with single users then 2 seconds
2. If execute main procedure with multiple users (17) then average taking 10 to 12 seconds.

But still, while concurrent users are taking more time. Do you have more suggestions?

Best Regards
Topic Advisor, Page Editor
Distinguished Expert 2018
Commented:
Hmmm.... would really need to look at the procedures.

10 down to 2 and 40 down to 10 is a good start :)

And when you say 'view" do you mean a database view, or a perspective as viewed by the user.

I would say (again) check wait states while the procedures are running. There is likely to be some kind of resource contention.

There is good information at : https://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts/ and a link to "capturing wait states" toward the end of the article.

add the WITH RECOMPILE especially if the tables have changed a bit
ALTER PROCEDURE ........  
WITH RECOMPILE  
AS  
BEGIN
    SET NOCOUNT ON;  

Open in new window


Is there any transactions ? should wrap with a BEGIN TRANSACTION and COMMIT TRANSACTION

Check the predicates on joins and where clauses - are there indexes on the underlying table to match ?

Check use of date functions - try not to use functions on database items used in joins or where clauses - make the variable part o all the work.

e.g. rather than : Select [date] from [table] where  month([date]) = month('20180203')
better to say  :  Select [date] from [table] where [date] >= '20180201' and [date] < '20180301'

but starting to clutch at straws without seeing the proc(s)
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
Kindly answer the questions or pointers raised by Mark and please share the Procedure script along with Execution plan so that we can help further..
Vitor MontalvãoMSSQL Senior Engineer
Distinguished Expert 2017

Commented:
Mind that is very hard for us to help you based only in your description of the problem. Only with the stored procedure code we can effectively help you.

Author

Commented:
@All - the procedure sharing would not be possible due to NDA.
But, I can work with aforesaid comments.
@Topic Advisor - thanks, I'll check for wait state and let you know.

Author

Commented:
View means, a perspective as viewed by the user.
No transaction implemented.
Mark WillsTopic Advisor, Page Editor
Distinguished Expert 2018

Commented:
@MohitPandit

How are you going with this ?

Author

Commented:
After implementing below things,
1. Remove iterations
2. Minimize temp tables
3. Place missing index
4. The main thing, learnt about Wait Locks, Latches and Spin Locks and understand Running, Suspended and Run able statuses and reduce I/O, less Cartesian product  and use Index Seek.

Thank you
Mark WillsTopic Advisor, Page Editor
Distinguished Expert 2018

Commented:
>>  The main thing, learnt about Wait Locks, Latches and Spin Locks and understand Running, Suspended and Run able statuses and reduce I/O, less Cartesian product  and use Index Seek.

That is absolutely brilliant to hear.... Well done !!!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start Today