Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

SQL Server 2012 Memory Problem Expand / Collapse
Posted Tuesday, December 10, 2013 8:30 PM


Group: General Forum Members
Last Login: Monday, March 24, 2014 10:07 AM
Points: 13, Visits: 70
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.
Post #1521725
Posted Wednesday, December 11, 2013 12:03 AM



Group: General Forum Members
Last Login: Today @ 11:52 AM
Points: 41,530, Visits: 34,446
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 2008, MVP
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

Post #1521755
Posted Wednesday, December 11, 2013 3:46 AM



Group: General Forum Members
Last Login: Today @ 8:05 AM
Points: 14,802, Visits: 27,276
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
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1521802
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse