Re: Inexplicable slowdowns

Willy Denoyette [MVP] wrote:
"Frank Rizzo" <none@xxxxxxxx> wrote in message news:u2dJyeaYHHA.448@xxxxxxxxxxxxxxxxxxxxxxx
Luke Zhang [MSFT] wrote:
Hello,

What is the task of the particular thread? What code it will execute? If you have source code on hand, you may add some debug code in it, for example, log exact time in a file after each of steps has been finished, so that you can know which code cause the delay.

The task is to basically schedule an entity into a time slot. So the tasks has to consider hundreds of business rules and find a time slot. The operation is totally memory based (and can take a bit of time). At the end of the operation, the results are written to the database. However, I profiled the SQL operation and it is taking no time at all.

I also noticed that the server was operating at the end of available physical memory (typically 200 mb free out of total of 6GB available) and as a result of perfmon monitoring, I saw that these slowdowns occur when the available physical memory dips to 100mb or so. I am thinking that the operation is forced into the paging file/virtual memory. However, I still can't explain the magnitude of the slowdown, e.g. operation that normally takes a second will take 80 seconds.


I don't get it, your service is a 32 bit process, so, can only consume 2GB (or 3GB) of memory, so the question is - who's consuming the other 3-4GB?
What other applications are you running on this machine? Are there external SQL clients, and how many are they? Did you configure SQL to use all available memory? Did you configure SQL to use all processors?
Honestly, I think SQL server is the culprit.

Oh, I know that SQL Server is the culprit. It's set to consume a max of 3GB. My application consumes 2.5GB (it's compiled with LARGEADDRESSAWARE). And there is a bunch of other smaller apps that are pushing the limit of the memory. I've asked the DBA to kick the SQL Server to 2.5GB, but the guy and his superiors won't budge, so I am left with a bunch of customers who are screaming bloody murder.

Anyway, the part that confounds me is that why an operation that typically takes 1 second could take 80 seconds when only virtual memory is available.

Regards.



Willy.


.

Relevant Pages

  • Re: Locking pages in memory
    ... the account SQL runs under) the permission to lock pages in memory. ... Server Standard, Enterprise, and Developer editions: Required for SQL Server ...
    (microsoft.public.sqlserver.setup)
  • Re: Worker Threads
    ... In order to use more than 4GB of memory you must use AWE. ... One is you must have Enterprise Edition of SQL ... set in the Boot.ini and AWE enabled in SQL Server. ...
    (microsoft.public.sqlserver.setup)
  • Re: FTS Performance in SQL 2005
    ... Looking for a SQL Server replication book? ... The Memory Usage and VM Size never increase over about 65 MB and 20MB. ... Can you post you query plans and the output of statistics IO ... cost relative to the whole batch, ...
    (microsoft.public.sqlserver.fulltext)
  • Re: SBS 2003 SP2 Memory
    ... MY PF is up to 4.75GB and free Physical memory is at 1.8GB. ... about the SQL instances and watching their individual need over time. ... part if your server rarely uses the pagefile. ... each of them based on usage so watch their RAM usage for a week or more and ...
    (microsoft.public.windows.server.sbs)
  • Re: SQL .mdf privacy violation - huge file contains personal contents
    ... The .NET runtime handles memory management ... ... in memory, archived to disk, but never deleted. ... them into temp files, which again, would be a privacy violation. ... SQL Server pre-allocates space for transaction logs, fill-space, etc. ...
    (microsoft.public.dotnet.languages.vb)