SQL Server 2012 Memory Problem

  • I am doing some research on a production server that we have that is essentially choking on a some simple inserts. I have a simple query that is importing around 3 million records into a table that we then send off to a vendor. Up until a few days ago - the query took about 50 seconds to run. However - yesterday that simple query jumped to 30+ minutes. Any attempt to cancel the query (which will cause a rollback I know) takes longer than 40 minutes. I checked windows task manager and I see that SQL server is taking 92 % of all memory. Now I now SQL will take the memory and won't give it back. But I watched the memory jump (after a restart of the server) from 12 % to 92 %. Now the table that all the records is truncated before the job runs - but I am a little baffled as to what is actually happening. I have the latest server pack installed on SQL server.

    Basic info about the Server

    OS - Windows Server 2012

    273 GB hard Drive

    25 GB of RAM

    64 bit

    Does anyone have any suggestions or insight as to what could be going wrong? I have about 15 jobs that run throughout the day - but this server is dedicated to sql server. Could it be the table is corrupted? There is not index on the table - since I use it for just a staging table to grab the data and counts before I export it to a file. So I am just in need of some advice from anyone that has seen anything similar on their own servers.

    Thanks in advance to all who have any comments.

  • Query, table definitions, execution plan please. Also, what are the wait types that you're seeing while the insert is running.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • It's probably your code, or your statistics, or the lack of an index. Those are much more likely than fundamental memory issues, but they can all lead to memory issues. Best thing to do to get a good answer is get us what Gail said, at least the execution plans.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply