Configure SQL Server

Question

I am using SQL Server 2008 and I want to configure all stored procedures that are time consuming

Whenever I execute a stored procedure separately, I get the result in a short time.

Whenever we run a load test for 100 users, the results are pretty bad.

I am using a .NET application with SQL Server 2008

Is there any way to find out what the problem is for this?

Answer 1

If the code works fine in SSMS, but not in .NET, this may be due to the sniffing parameter. Try adding the following query at the end of each select statement:

OPTION (RECOMPILE)

SSMS .NET, sniffing. - , . , . , , :

create proc myproc (@param1 int, @param2 varchar(20))
as

declare @var1 int
declare @var2 varchar(20)

set @var1 = @param1
set @var2 = @param2

select
   <columns>
from
   <table>
where
       <column x> = @var1
   And <column y> = @var2

, , :

http://blogs.msdn.com/b/turgays/archive/2013/09/10/parameter-sniffing-problem-and-workarounds.aspx

Answer 2

, , , //.

, , - .Net. .Net- , , .

; , CPU, - . - 100%, . , , . , , , CPU , - , .

SQL Server Profiler . - - , . , ( ). ( 1 ).

, . , , , , . , , .