| Author |
Topic |
|
Zard0z
Starting Member
8 Posts |
 Posted - 2010-01-09 : 21:38:18
|
Hi all.I'm using SQL Server database from an ASP.NET site, but I figured that the problem doesn't come from .NET.There is a number of requests, and one of them sometimes causes connection timeout (takes more than 30 sec).It does so only after it hasn't been accessed for a while. I tried to run the web app on different IIS servers using the same DB - first request causes timeout, subsequent requests take a few seconds. After there are no requests for a while (~30 minutes, I don't know), first request will cause timeout again, and after that other requests work OK.There are two databases that I access, I thought it's something about SQL server side DB connection pool, or database going to some kind of mode from which it is awakened, but seems that I am wrong.One request to that DB goes OK, and second one is the troublemaker.I also thought that it can be about compiling the request, so I put it into a stored proc. It totally didn't help.What else can be the cause? Please help, I spent like 3 days googling and trying stuff.Read about "Auto Close" option for the database, but I'm afraid to enable it, because people say it's a don't for production database, which is the only one I have.The request is here: IF (SELECT ReportDisks FROM DB1.dbo.Table1 WHERE ID=@ID)>0 SELECT DL.ID, DL.HeartBeatID, DL.HostName, DL.VolumeName, DL.VolumeUtilization, DL.ts FROM DB2.dbo.Table2 AS DL JOIN (SELECT MAX(X.ID) AS ID, X.HostName, X.VolumeName FROM (SELECT TOP 50 ID,HostName,VolumeName FROM DB2.dbo.Table2 WHERE DeviceID = @ID) AS X GROUP BY HostName,VolumeName) AS IDs ON IDs.ID = DL.IDEND Table2 is a log of reports of disk utilization, with PK on ID, DESC.The idea is to get the latest report for given PC for all it's hosts and volumes |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2010-01-09 : 22:58:47
|
| There are two possible reasons that I can think of: data cache and procedure cache. The first is if the data isn't in memory, it has to go to the disks to grab it. The second is if the execution plan isn't in memory, it has to compile one for you. Both issues can be avoided by having a sufficient amount of memory allocated to SQL Server. How big is the database and how much memory is SQL Server allowed to consume?You certainly don't want to enable auto close. I don't even enable it on test systems due to the performance penalty.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog"Let's begin with the premise that everything you've done up until this point is wrong." |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Zard0z
Starting Member
8 Posts |
Posted - 2010-01-09 : 23:25:18
|
| The table has like 4 million records, the DB is 113000 MB, sql server has 6 GB of RAM allocated. New entries are inserted every 15 minutes or so."Auto Update Statistics", "Auto Update Statistics Async" and "Auto Create Statistics" are enabled. If async is on, does it mean the timeout is not about statistics? |
|
|
|
Zard0z
Starting Member
8 Posts |
Posted - 2010-01-09 : 23:28:37
|
| *24 million |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2010-01-09 : 23:58:49
|
| 6GB is probably not enough for a database of that size.Run SQL Profiler to collect trace data. Add the Showplan XML event so that you can grab the execution plans. Then reproduce the issue with the slow response time and the much quicker response time and then compare the two execution plans. If the execution plans are the same, the slow query is likely due to the data not being in cache. If it's just a SELECT query like you've shown, you may want to run a periodic job that runs the same query to keep the data in cache.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog"Let's begin with the premise that everything you've done up until this point is wrong." |
|
|
|
Zard0z
Starting Member
8 Posts |
Posted - 2010-01-10 : 09:17:52
|
| Tara,Thank you! Will dig this way. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Zard0z
Starting Member
8 Posts |
Posted - 2010-01-10 : 19:28:28
|
| Something really evil is going on!I moved the request to a stored proc. If I call it from Management Studio, it takes less than a second.If it is called from a ASP.NET on a server, the first call will take >30 seconds. If it is called from one server, on another there will be no delay.So does ASP.NET connect to SQL server somehow differently that Management Studio does? It kinda ruins the version with caching... or does it not? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-01-11 : 09:44:19
|
quote:
Originally posted by Zard0z
Something really evil is going on!I moved the request to a stored proc. If I call it from Management Studio, it takes less than a second.If it is called from a ASP.NET on a server, the first call will take >30 seconds. If it is called from one server, on another there will be no delay.So does ASP.NET connect to SQL server somehow differently that Management Studio does? It kinda ruins the version with caching... or does it not?
how is asp.net connecting to sql server now? |
|
|
|
Zard0z
Starting Member
8 Posts |
Posted - 2010-01-11 : 10:12:24
|
| Connection string in web.config and SqlDataSource on the page, SelectCommand="sp_name" |
|
|
|
Zard0z
Starting Member
8 Posts |
Posted - 2010-01-11 : 10:42:37
|
| Can it be because same SQL user login is used for a lot of operation from different applications? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-01-11 : 10:48:28
|
quote:
Originally posted by Zard0z
Can it be because same SQL user login is used for a lot of operation from different applications?
is there any major h/w difference b\w two servers? |
|
|
|
Zard0z
Starting Member
8 Posts |
Posted - 2010-01-11 : 11:06:13
|
| Which two servers? Different ASP.NET servers are my dev workstation and production IIS server.There are like 3 more applications that use the one SQL server, running on the same production IIS server. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-12 : 04:33:55
|
| You mentioned "Read about "Auto Close" option for the database" at the outset.Can I just check that Auto Cose is OFF / DISABLED for this database.If connection is slow from a particular ASP.NET worth checking that the connection string is "efficient". It might be causing DNS resolution to go out of the building in order to come back in again, for example! |
|
|
|
|
First request takes a loooong time