Hi everyone !
Since I've installed SQL Server 2005 SP4 (which I can't uninstall), I'm getting into troubles when running some querries through ASP.NET pages (it takes 10 seconds to run), whereas, if I run the same query directly from SQL Server Management Studio, it works perfectly (less than 1 second).
Here is the configuration :
- Windows 2003 Server R2 SP2 - 4Gb RAM - 32 bits
- IIS 6.0
- SQL Server SP4 (9.0.5057)
- My IIS services is used to run 2 websites on it
I've checked many things to solve this issue :
- connections being closed properly into the code
- users accounts and rights (under IIS and SQL Server)
- web.config file settings
- SQL Server settings
The 2 websites are used daily by a various number of people, and before the SP4 update, everything was working fine.
Sometimes this low performance issue happens, sometimes not, and I'm pretty sure that IIS is the cause of this.
I've used the following script to look at the number of transactions created each time I launch of querry from a page, and sometimes it seems to create up to 30 transactions with the same content.
SessionID = s.Session_id,
DatabaseName = DB_NAME(resource_database_id),
JOIN sys.dm_exec_sessions s ON l.request_session_id = s.session_id
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
) a ON s.session_id = a.session_id
s.session_id > 50
I'm running out of ideas, any help would be greatly appreciated :-)
Thanks in advance !