(pointer) SQL Server slows down after a couple hours

Posted on 2007-10-08
Last Modified: 2008-01-09
Still having trouble with SQL Server. I would appreciate any insight the experts have to offer.

Please see original question here (500 points):

Question by:phxvigo
    LVL 8

    Expert Comment

    by:Bradley Haynes
    There are several things that jump out at me. First, whenever possible run SQL Server on its' own box.
    Second, check your queries to optimize performance. You want queries to SEEK not SCAN which is done via Indexing, and providing numerical search criteria as often as you can.
    Have you set up Maintenance jobs?
    Reducing fragmentation or in database "speak" lack of contiguous space allocation is done with the Maintenance Plans you set up.
    These are vital DBA  tasks.

    note: I posted this comment on the original question as well.
    LVL 5

    Expert Comment

    This is not an easy question to answer (doh!) as you'll need to collect the windows performance data and the profiler activity for a period when it transitions from good performance to poor. From those you'll need to work out if the CPU, disk,memory or network are the bottleneck and use the Profiler data to identify what transactions occurred at that time. I do this kind of work and it is very rare that you can just look at a system and go - oh yes there's the cause.
    As the system isn't dedicated to SQL only, it may be that other processes are starting up and taking all of the memory from SQL - hence it goes back to the disk for the data. Does the Application process have a memory leak and it takes all of the memory? Have you tried setting SQL to have a fixed min/max memory allocation - say 2GB?
    LVL 8

    Accepted Solution

    You can also use Sysinternals to view activity in real time. Get it here:

    There forum is:

    Author Comment


    Thanks for the reply. I have been using perfmon to monitor the general system state, but haven't found much more than I've already said. I don't think it's a CPU issue as the CPU usage is around 20% average. It seems like a memory issue as it decides it needs to read from disk, however perfmon says the system still has free physical memory. The SQL target server memory in perfmon does not change or go down. The problem seems to occur after SQL total server memory reaches the target server memory, but not immediately. Are there any other metrics which might indicate what is going on?

    Perhaps SQL server is using its allocated memory for something other than cacheing, and not releasing that memory. When it hits its max memory, it runs out of room for cacheing data. But what is it using it's memory for, and why isn't it releasing it? I don't know, just grasping at straws...
    LVL 5

    Expert Comment

    I'm going to check out/reply to the original posting

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Introduction In my previous article ( I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    Via a live example, show how to shrink a transaction log file down to a reasonable size.
    Viewers will learn how the fundamental information of how to create a table.

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now