Performance optimization for SQL Server: decrease stored procedures execution time or unload the server?

Question

We have a web service which provides search over hotels. There is a problem with performance: a single request to the service takes around 5000 ms. Almost all of the time is spent in database by executing storing procedures. During the request our server (mssql2008) consumes ~90% of the processor time. When 2 requests are made in parallel the average time grows and is around 7000 ms. When number of request is increasing, the average time of response is increasing as well. We have 20-30 requests per minute.

Which kind of optimization is the best in this case having in mind that the goal is to provide stable response time for the service:

1) Try to decrease the stored procedures execution time

2) Try to find the way how to unload the server

It is interesting to hear from people who deal with booking sites.

Answer 1

It is interesting to hear from people who deal with booking sites. Thanks!

This has nothing to do with booking sites, you have poorly written stored procedures, possibly no indexes, your queries are probably not SARGAble and it has to scan the table every time. Are you statistics up to date?

run some procs from SSMS and look at the execution plans

Also a good idea to run profiler. How about your page life expectancy and buffer cache hit ratio, take a look at Use sys.dm_os_performance_counters to get your Buffer cache hit ratio and Page life expectancy counters to get those numbers

Answer 2

I think the first thing you have to do is to quantify what's going on on the server.

1. Use SQL Server Profiler to get an accurate picture of the activity on the server.
2. Identify which procedures / SQL statements take up the most resources
3. Identify high priority SQL operations consuming a lot of resources / taking time
4. Prioritize
5. Fix

Now, when I say "Fix", I mean that you should execute the procedure / statement manually in SSMS - Make sure you have "Show Execution Plan" turned ON.

Review the execution plan for parts that consume the most resources and then figure out how to correct that. You may need to create a new index, rewrite the SQL to be more efficient by using hints, etc.

Answer 3

You provide no detail to solve your problem. In general to increase performance of a stored procedure I look at:

1) remove any cursors or loops with set based operations
2) make sure all queries are using an index and using an efficient execution plan (check this with SET SHOWPLAN_ALL ON)
3) make sure there is no locking or blocking slowing it down (see the query given here)

without more info on the specifics, it is hard to make any suggestions.

Almost all of the time is spent in database by executing storing procedures.

how many procedures is the app calling? what do they do? are transactions involved? are the procedures recompiling each call? do you have any indexes? are statistics up to date? etc., etc... You need to give a lot more info, or any help here is a complete guess.