December 10, 2013 at 8:30 pm
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.
December 11, 2013 at 12:03 am
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
December 11, 2013 at 3:46 am
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