June 18, 2007 at 4:21 pm
The other day a developer wrote a single INSERT statement (had lots of JOINS, etc. from massive tables) that ran for hours. Instead of the usual 6.9 GB of free RAM on the server we went down to about 225MB free and the Windows 2003\sql 2005\x64 server froze.
Most unexpetced and unplaeasent. How can one deal with this type of issue?
TIA,
barkingdog
June 18, 2007 at 10:35 pm
Onw way is that you can set a max memory configuration to SQL Server so that it does not go beyond that limit. But when you say that your server went to freeze state it would mean that either you have to wait for your query to complete or restart the server to release resources. If you would have handled transaction it would be fine in such scenarios so that they rollback all data without commitng.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
June 19, 2007 at 8:21 am
You can break up the insert as well, use a transaction if needed, and insert in batches rather than one large statement. That could help with memory.
June 19, 2007 at 8:42 am
Steve,
You wrote "You can break up the insert as well,".. I sense what you me but could you please show me an example of what you are referring to?
Barkingdog
P.S. To make matters worse the sql is generated by an application, not human hand. It is a single "INSERT into from SELECT" that takes about 160 lines, contains sql casts, substrings, charindex, isnumeric, JOINS, and a Group By for good luck!
June 20, 2007 at 8:03 am
1) Get (MUCH) bigger hardware.
or
2) Use the query governor cost limit to prevent 'runaway' queries from executing. In conjunction with this you may run a profiler to trap said calls and use that information to both figure out how to actually GET it to run and also fix the code that creates it in the first place!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply