April 27, 2017 at 1:24 am
How do you tune a query with response time of 300 ms to perform in same way for 500 users?
Entity framework with LINQ is being used in application when same query is captured in SQL Server profiler and executed in SSMS it is taking just 300 ms (less than a second to respond). All missing indexes are created.
When executed by 100+ users simultaneously(users using same page in application) database server CPU is running with 83% utilization and for some users query is taking more than a minute.
Is it really DB issue? can we something to query? or stop using LINQ?
Thanks.
April 27, 2017 at 1:49 am
santosh.hungund - Thursday, April 27, 2017 1:24 AMHow do you tune a query with response time of 300 ms to perform in same way for 500 users?
Entity framework with LINQ is being used in application when same query is captured in SQL Server profiler and executed in SSMS it is taking just 300 ms (less than a second to respond). All missing indexes are created.
When executed by 100+ users simultaneously(users using same page in application) database server CPU is running with 83% utilization and for some users query is taking more than a minute.
Is it really DB issue? can we something to query? or stop using LINQ?
Thanks.
Can you post the query and the actual execution plan please?
😎
If the execution is taking 300ms and you have 100 concurrent users executing it then 30000ms or 0.5 minutes + blocking is what you can expect.
Did you blindly create the missing indices by the execution plan's suggestions?
One option is to change the code to a stored procedure and then call it from LINQ.
April 27, 2017 at 3:20 am
Eirikur Eiriksson - Thursday, April 27, 2017 1:49 AMsantosh.hungund - Thursday, April 27, 2017 1:24 AMHow do you tune a query with response time of 300 ms to perform in same way for 500 users?
Entity framework with LINQ is being used in application when same query is captured in SQL Server profiler and executed in SSMS it is taking just 300 ms (less than a second to respond). All missing indexes are created.
When executed by 100+ users simultaneously(users using same page in application) database server CPU is running with 83% utilization and for some users query is taking more than a minute.
Is it really DB issue? can we something to query? or stop using LINQ?
Thanks.Can you post the query and the actual execution plan please?
😎
If the execution is taking 300ms and you have 100 concurrent users executing it then 30000ms or 0.5 minutes + blocking is what you can expect.
Did you blindly create the missing indices by the execution plan's suggestions?
One option is to change the code to a stored procedure and then call it from LINQ.
Indexes are created taking suggestion of execution plan. Also we validated table size and whether indexed columns are used in join expression or in where clause.
Finally we concluded to use stored procedure. Yet to see the performance.
April 27, 2017 at 7:46 am
Without seeing the query and the execution plan, I have nothing to offer you except the link below to my books on query performance tuning and reading execution plans.
If you can post the information that's been requested, we can get more specific on the help.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply