November 27, 2007 at 3:21 pm
I recently moved my SQL Server 2005 db from a shared db server to a dedicated virtual server with MaximumASP.com. The database powers my .asp-based website, which typically has around 200 active visitors browsing at any given time (about 40,000 visitors/day).
The problem I'm having is that my queries are timing out sometimes. And it's not like they're terribly complex queries...there's no reason that I can see why they should take more than a second to run, and yet...15 seconds passes and they time out. The problem is that it's not consistent--I can run the query in SQL Mgmt Studio, and it runs nice and quick...but on the live site, it times out occasionally.
The server has 768MB of memory, and my database is about that same size. Not sure if that's the reason? I have it set to use AWE, and up to 600MB of memory. It never seems to use more than about 85MB, though (the sqlserver.exe process, that is).
Chances are, if you're a SQL Server expert, you've already realized I'm no expert in this stuff. I know just enough to get the job done--and sometimes not that much! I'm mulling over the idea of trying to hire a SQL guru to fix this for me, but I would really like to figure it out myself. Any tips you can give me would be awesome. Thanks!
Regards,
Eric Schlange
November 27, 2007 at 3:33 pm
Eric,
Timeouts are usually caused by resource contention. The problem that you have with using Virtual servers is that you may be sharing resouces with other Virtual servers running on the same hardware. For this reason, I would recommend to stay away from Virtual server environments for Production database servers. I know that VS software has come along way in terms of dividing out server resources, but you have to plan very carefully. Is this server hosting other Virtual instances? Are they sharing the same physical disks?
November 27, 2007 at 3:39 pm
Yes, the server is hosting other virtual instances (up to 25 other accounts), and I would assume, they're sharing the same physical disks. The 768MB of memory is dedicated to just my account.
I figured performance of this server would be plenty for this database, since it was performing decently on the other shared DB server (which was hosting who knows, 50+ databases?). I've always been pleased with MaximumASP's support and performance. Hmmm...
November 28, 2007 at 4:37 am
It's most likely the server, but I'd gather some information to back yourself up. Use profiler to get a days worth of data, hopefully including a timeout or two. Go through the data and verify that the same procedure run with the same parameters ran for 15 seconds one time and less than a second the other. Get an execution plan for that query that shows that it doesn't do table scans or bookmark lookups or what have you. In other words, get all the information to make your case together and then present it to them.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
November 28, 2007 at 4:54 am
You wrote that you have AWE enabled but only use 600 MB of RAM. AWE memory is over 4GB so you better disable that again.
Now to the timeout problem, I've been called to troubleshoot these kind of problems several times and while db locking could be the reason for your timeouts, in 99% of the cases the problem is on the application side. You said yourself that the queries aren't very complex and when you execute them in SSMS the run within less than a second. The problem only occurs once your ASP application receives the results and tries to do something.
[font="Verdana"]Markus Bohse[/font]
November 28, 2007 at 8:08 am
Ah, I didn't know that about AWE! I just disabled it.
So, I guess the big question is whether this is an issue on the application side, or the db server side. Anybody else care to chime in on this? The application is really pretty straightforward--just a forum, basically. On the db server side, I've done the execution plan for the queries that time out, and there's nothing resource-intensive going on (no table scans, etc).
November 28, 2007 at 8:23 am
>>On the db server side, I've done the execution plan for the queries that time out, and there's nothing resource-intensive going on (no table scans, etc).
Right, but you've done that at a single point in time, which is insufficient data to support the conclusion that no scans are happening.
You need to follow Grant's advice above - run Profiler and capture server activity over a longer time period.
A query that appears OK and doesn't scan when run by you manually, may well become a resource problem under load, when index stats get out of date, or when run with specific parameters.
November 28, 2007 at 8:23 am
Gather statistics on both sides of the equation. Guessing or pointing at various bad signs won't help. You need a reasonably thorough picture of what is occurring. For example, do the timeouts coincide with high usage times within your site, or are they completely random?
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
November 28, 2007 at 8:34 am
Thanks for the advice, guys!
For whatever reason, I don't have Profiler installed on the server (I'm NOT using SQL Server Express). I'll need to go in and set that up as you advised...I'll let you know of the results.
November 28, 2007 at 8:45 am
You don't need the Profiler gui. Actually, better still if you don't have it. There are several ways you can get the scripts, including generating them locally through your own profiler and then running them on the server, but you can call the profiler directly from SQL Server, and it is there, no install required. Look up sp_trace_create in BOL.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
November 28, 2007 at 9:31 am
Keep in mind that by allowing SQL Server to use 600MB, you've now restricted the OS to 168MB of RAM. if that's a 2003 box - that's 88MB shy of the minimum recommended (which is usually half of what it need to run well) just to run the OS without IIS. If you're running anything else on here (like IIS, which is going to want 256MB for itself) - you're KILLING this virtual instance. Nevermind the resource contention which just hurts it even more. You're no doubt paging out a LOT. Forget about caching, etc... - you're not going to get any of that.
I'd see if I could cobble a machine together to have these specs - load whatever the ISP has on it, and see what kind of load it can handle. Then assume 40% or more slower, since those resources aren't dedicated to you. I doubt it's 40,000 users/day...
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
November 28, 2007 at 10:31 am
I've got the trace set up. Any ideas on what specific columns I should be tracing? Having never done this before, I'm not sure if it's standard to trace everything, or just a couple of them, etc. Here's what I'm tracing now...
-- Trace DatabaseID for SQL:BatchCompleted events
exec sp_trace_setevent @TraceID,@eventid=12,@columnid=1,@on=@on
-- Trace DatabaseID for SQL:BatchCompleted events
exec sp_trace_setevent @TraceID,@eventid=12,@columnid=3,@on=@on
-- Trace ApplicationName for SQL:BatchCompleted events
exec sp_trace_setevent @TraceID,@eventid=12,@columnid=10,@on=@on
-- Trace LoginName for SQL:BatchCompleted events
exec sp_trace_setevent @TraceID,@eventid=12,@columnid=11,@on=@on
-- Trace Duration for SQL:BatchCompleted events
exec sp_trace_setevent @TraceID,@eventid=12,@columnid=13,@on=@on
-- Trace StartTime for SQL:BatchCompleted events
exec sp_trace_setevent @TraceID,@eventid=12,@columnid=14,@on=@on
-- Trace Reads for SQL:BatchCompleted events
exec sp_trace_setevent @TraceID,@eventid=12,@columnid=16,@on=@on
-- Trace Writes for SQL:BatchCompleted events
exec sp_trace_setevent @TraceID,@eventid=12,@columnid=17,@on=@on
-- Trace CPU for SQL:BatchCompleted events
exec sp_trace_setevent @TraceID,@eventid=12,@columnid=18,@on=@on
November 28, 2007 at 12:21 pm
I didn't see it, so I'd add the TextData column as well. Other than that, you've covered the basics.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
November 29, 2007 at 10:31 am
1) Why don't you simply increase your timeout from 15 sec to 30, 60 or more seconds? keep you from losing customers until you get things optimized.
2) Since SQL doesn't use much mem, reduce the setting on max memory. The OS prolly needs more than your sql cap allows if your data size does increase over time.
3) Since queries run fast in SSMS but occassionally slow in web app, parameter sniffing could be issue. Do you use stored procedures or direct-execute ADO/ADONET code? If former, consider using WITH RECOMPILE option to ensure optimal query plans.
4) Have you done any index analysis since moving from test to production? Often bad query plans don't cause perf issues until you have a reasonable amount of data.
5) Hire a pro for a performance review. This can be done completely remotely and if you get the right person will provide a tremendous ROI for you.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply