Asp.net sql server 2005 timeout issue

Question

HI We am getting time outs in our asp.net application. We are using sql server 2005 as the DB. The queries run very fast in the query analyser . However when we check the time through the profiler it shows a time that is many times more than what we get in query analyser. (paramter sinffing is not the cause) Any help is much appreciated

thanks

We are on a SAN Cleared the counters. The new counters are

ASYNC_NETWORK_IO 540 9812 375 78

WRITELOG 70 1828 328 0

The timeout happens only on a particular SP which a particular set of params. if we change the params and access the app it works fine. We ran the profiler and found that the SP batchcompleted statement comes up in the profiler after the timeout happens on asp.net side. If we restart the server everything works fine

if we remove the plan from the cache the app works fine. However we have taken into consideration parameter sniffing in the sp. what else could be the reason

Answer 1

If I was to take a guess, I would assume that the background database load from the webserver is elevating locks and causing the whole thing to slow down. Then you take a large-ish query and run it and that causes lock (and resource) contension.

I see this ALL THE TIME with companies complaining of performance problems with their client-server applications when going from one SQL server to a cluster. In the web-world, we get those issues much earlier.

The solution (most times) to lock issues with one of the following: * Refactor your queries to work better (storing SCOPE_IDENTITY instead of calling it 5 times for example) * Use the NO LOCK statement everywhere it makes sense.

EDIT:

Also, try viewing the server with the new 2008 SQL Management Studio 'Activity Monitor'. You can find it by right-clicking on your server and selecting 'Activity Monitor'.

• Go to the Processes section and look at how many processes are 'waiting'. Your wait time should be near-0. If you see alot of stuff under 'Wait Type', post a screen shot and I can give you an idea of what the next step is.
• Go to the Resource Waits section and see what the numbers look like there. Your waiters should always be near-0.
• And 'Recent Expensive Queries' is awesome to look at to find out what you can do to improve your general performance.

Edit #2:

How much slower is it? Your SAN seems to be taking up about 10 seconds worth, but if you are talking 20 seconds vs. 360 seconds, then that would not be relevent, and there is no waits for locks, so I guess I am drawing a blank. If the differene is between 1 second and 10 seconds then it seems to be network related.

Answer 2

I don't have the answer for you, because I'm not a guru. But I do remember reading on some SQL blogs recently that SQL 2008 has some extra things you can add to the query/stored procedure so it calculates things differently. I think one thing you could try searching for is called 'hints'. Also, how SQL uses the current 'statistics' makes a difference too. Look that up. And how the execution plan is only generated for the first run--if that plan doesn't work with different parameter values because there would be a vast difference in what would be searched/returned, it can present this behavior I think.

Sorry I can't be more helpful. I'm just getting my feet wet with SQL Server performance at this level. I bet if you asked someone like Brent Ozar he could point you in the right direction.

Answer 3

I've had this exact same issue a couple of times before. It seemed to happen to me when a particular user was on the site when it was deployed. When that user would run certain stored procedures with their ID it would timeout. When others would run it, or I would run it from the DB, it would run in no time. We had our DBA's watch everything they could and they never had an answer. In the end, everything was fixed whenever I re-deployed the site and the user was not already logged in.

Answer 4

I've had similar issues and with my case it had to do with the SP recompiling. Specifically it was my use of temp tables vs table variables.

Answer 5

Run the following script to create this stored proc:

CREATE PROC [dbo].[dba_SearchCachedPlans]
    @StringToSearchFor VARCHAR(255)
AS
/*---------------------------------------------------------------------- 
Purpose: Inspects cached plans for a given string. 
------------------------------------------------------------------------ 
Parameters: @StringToSearchFor - string to search for e.g. '%<MissingIndexes>%'. 
Revision History: 
      03/06/2008  [email protected] Initial version 
Example Usage: 
1. exec dba_SearchCachedPlans '%<MissingIndexes>%'
2. exec dba_SearchCachedPlans '%<ColumnsWithNoStatistics>%'
3. exec dba_SearchCachedPlans '%<TableScan%'
4. exec dba_SearchCachedPlans '%CREATE PROC%MessageWrite%'    
-----------------------------------------------------------------------*/ 
BEGIN 
    -- Do not lock anything, and do not get held up by any locks. 
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 

    SELECT TOP 100
            st.TEXT AS [SQL],
            cp.cacheobjtype,
            cp.objtype,
            DB_NAME(st.dbid) AS [DatabaseName],
            cp.usecounts AS [Plan usage],
            qp.query_plan
    FROM    sys.dm_exec_cached_plans cp
            CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
            CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp
    WHERE   CAST(qp.query_plan AS NVARCHAR(MAX)) LIKE @StringToSearchFor
    ORDER BY cp.usecounts DESC 
END

Then execute:

exec dba_SearchCachedPlans '%<MissingIndexes>%'

And see if you are missing any recommended indexes.

When SQL server creates a plan it saves it, along with any recommended indexes. Just click on the query_plan column text to show you the graph. On the top there will be recommended indexes you should implement.

Answer 6

Hi,

Have a look at the discussion section in the following article: http://www.sqlservercentral.com/articles/Performance/63638/

Thanks Ian