CODEWITHSUNDEEP
sqlsql-serverperformancesql-server-2008
Surendra
Surendra

Reputation: 41

Tuning up SQL Server

I am using SQL Server 2008 and I want to tune up all stored procedures which are taking long time

Whenever I execute a stored procedure individually I am getting the result within no time..

Whenever we run the 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 is the problem for that?

Upvotes: 4

Views: 118

Answers (2)

SQLDiver
SQLDiver

Reputation: 2018

If the code is running fine in SSMS, but not in .NET, it could be due to parameter sniffing. Try adding the following query hint to the end of every select statement:

OPTION (RECOMPILE)

If this now gives you similar results for both SSMS and .NET, you have parameter sniffing. One way to get around this is the recompile option, as stated above. However, this recompiles the query every time, which should only be considered as a last resort. A better way is to use local variables, so in your stored procedures, you reassign the parameters to variables and use these in the select queries, like so:

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

There are other workarounds using query hints, as discussed here:

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

Upvotes: 1

Neville Kuyt
Neville Kuyt

Reputation: 29649

You have to approach this logically to find out where the problem actually is before you can tune/optimize/whatever.

First, confirm that it is actually the database that's the problem - it might be the .Net application. Put a profiler on the .Net application, and check that it is indeed spending time with the database queries, and that it's not the application code that's the problem.

Next, run performance monitor on the database server; check for memory, CPU, disk I/O and network. If any of those are spiking or consistently at 100%, you may have a hardware bottleneck. It's often much, much cheaper to upgrade your hardware. Of course, database problems are likely to manifest in CPU or memory spikes, so this isn't foolproof - but if you're running on substandard hardware, upgrading is still much more cheaper.

Next, run SQL Server Profiler on the server. Experiment with filtering out the noise - it's quite a noisy tool - and look for long-running queries while multiple users are using the application. Often, you'll see just a few queries that are responsible for the majority of performance problems (but not always). Create a list of slow-running (I use 1 second as the cut-off point).

Once you have your list of slow-running queries, on a development environment, optimize them. Look at the query plan to make sure they're using the right indexes, and look at the way they are written. If you're still stuck, come back and post specific queries and sample data for us to look at.

Upvotes: 1

Related Questions