Re: ASPX pages failing when SQL Server busy

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: William \(Bill\) Vaughn (billvaRemoveThis_at_nwlink.com)
Date: 10/20/04 

Date: Wed, 20 Oct 2004 13:23:44 -0700

Sure, TempDb is used quite a bit as a "scratchpad" to hold temporary
(intermediate) products and to sort the data. What kind of hard drives do
you have? Is the database segmented so it's not putting TempDb on the same
spindle as the OS and the other databases. Sure, you might be disk bound. If
you're sharing space with IIS, that could also hurt performance as could the
complexity of your SP. It could be that the query plan does not match the
set of parameters you're using. It could be that your connection pool might
be filling up as inbound customers can't get a connection because the ones
that are in use are still busy. It could be a dozen dozen other things as
well. 

-- 
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
"Luke" <Luke@discussions.microsoft.com> wrote in message 
news:7E5D4A67-DC60-4DC3-B2EF-01435E559D0C@microsoft.com...
> First thing, I would't run the web server on the same machine as 
> SQLServer.
> It will kill the consistency of your site's performance.
>
> "John" wrote:
>
>> I have a single server running SQL Server 2000 sp3a, and acting as a 
>> ASP.NET
>> webserver (.net 1.1 sp1). W2k sp4. Dual Xeon processor, 2 gigs RAM.
>>
>> Everything works fine, but sometimes when I execute a particular stored
>> procedure (on server console using command line, nothing .net), my 
>> webapps
>> will fail intermitently. Custom errors are turned on, so at this stage I
>> can't give you error codes! But my assumption is that it's a SQL problem,
>> not a .NET one.
>>
>> The sproc does lots of joining, aggrigates and sorting, and makes the 
>> tempdb
>> grow from nothing to over 3 gigs. The database it's actually in is around
>> 300mb in size, and doesn't grow that much itself. All db's are in 
>> 'simple'
>> recovery model. The sproc contains no explicit transactions or temp 
>> tables.
>>
>> Can anyone suggest what's going wrong?? I suspect tempdb, if only because 
>> of
>> it's massive growth. Could it be getting too big for the filesystem??? (I
>> have lots of disc space free, but maybe W2k can't cope with a single file
>> larger than x?)
>>
>> Thanks,
>>
>> John
>>
>>
>>

Relevant Pages

  • Re: The log file for database tempdb is full
    ... What would that query have to do with the tempdb ... which are automatically created in the tempdb database. ... Luckyly it is a test server so I have just ...
    (microsoft.public.sqlserver.server)
  • Re: The log file for database tempdb is full
    ... Sometimes the autogrowth can't happen fast enough and you can get errors ... which are automatically created in the tempdb database. ... Luckyly it is a test server so I have just ...
    (microsoft.public.sqlserver.server)
  • Re: Tempdb after restart
    ... If SQL has to auto-grow tempdb there are severe ... performance penalties. ... Microsoft SQL Server MVP ... I support the Professional Association for SQL Server ...
    (microsoft.public.sqlserver.server)
  • Re: Performance differences local vs. tempdb table
    ... As for the server my client works on (the big long ... process) his user database and tempdb are on two ... different drives. ... >> DECLARE @I INT ...
    (microsoft.public.sqlserver.server)
  • Re: tempdb is skipped. You cannot run a query that requires tempdb
    ... you must be using sqldatareader which utilizes forwardonly, readonly cursor ... and sqlserver likes to use tempdb for its worktable. ... > Has anyone seen the SQL Server error: ... > We're running a .Net web application with a SQL Server ...
    (microsoft.public.sqlserver.programming)