May 18, 2015 at 8:22 am
After I adjust the maximum memory as mentioned as above which is 24GB out of my actual machine 32GB, it stay peak at 94 to 95%.
I'm not sure whether this is the bug from SQL Server 2012 SP2 or I need to do some fine tuning on application but I really never encounter this is my old SQL Server 2000 with 4GB of RAM only. This is the part I really not understand. Same databases are moved over to new SQL Server 2012 with 32GB. I expect to get some performance gain over the old server. Hmmm...
I read from Microsoft website there is a release on Cumulative Update 5 which cover some of the issue which are memory leak, CPU spike. To apply this not an easy as I can do it at now. Downtime is require and after apply this CU5 not sure whether it will solve the issue I faced at the moment.
May 18, 2015 at 8:41 am
audiocool (5/18/2015)
Check the activity monitor and found out there is a dead lock happened. Kill the process and it run again.
If it was a deadlock, you wouldn't have needed to kill it. SQL has a deadlock detector which detects and kills deadlocks automatically. It was probably a blocked process.
Killing processes is not a solution, you're forcing them to roll back and probably re-run.
As for this being a bug, my money's on no. You need to diagnose, identify and result the root cause. Yes, patches and CUs are good and necessary and should be applied.
If this is the bug you're referring to, look at the conditions under which it can occur:
https://support.microsoft.com/en-za/kb/3032476
When you try to add columns with variable data type online in a temp table, and the columns have a default constraint, a memory leak occurs in USERSTORE_SCHEMAMGR. Additionally, the memory leak may cause the resource monitor to spin, and this could lead to CPU spikes.
So specifically when code like
ALTER TABLE #SomeTable ADD SomeColumn VARCHAR(50) DEFAULT 'This is the default value'
is run
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
May 18, 2015 at 8:46 am
hey Gaill
i agree that fixing it to any arbitrary value doesnt solve the issue . i told him to do so because i thought doing this SQL will not take all the memory and application will run smoothly till the time he read something about troubleshooting .
and just to troubleshoot
easier way
if possible run the Trace on SQL server from other client machine (not from the same server) for 1 whole day or at list for peak hour and analysis this trace in tunning adviser . and check the recommendations
it will help you lot in tuuning the datbase and queries
May 18, 2015 at 8:47 am
Didn't read in close detail, but here is my take on some things:
Several others beat me to it but I will add my hat to the ring of "you need to get professional help" to have any good probability of finding and resolving your issues, certainly in a timely manner. And if your shop floor cannot function I would say timeliness is of the essence here!!! Myself and numerous others do this type of work for a living and many issues can be found and resolved within a matter if hours or even minutes. Low-hanging fruit is ALWAYS there, ESPECIALLY in shops (which is most of them) that do not have a dedicated/trained/experienced DBA on staff. Sadly I am starting an international trip tomorrow and cannot assist further. I have done this so much and for so long (~45000 man hours now), I very rarely see anything interesting any more, and this actually sounds interesting. 😎
Given acceptable performance on SQL 2000 with MUCH less capable box and now things just go out to lunch it could be some magic-bullet config or single query (or bug) that is causing things to go wonky. It could also be virtualized and the VM admin screwed things up or over subscribed or ballooned the memory and your box is getting starved.
You need to immediately look up Dedicated Administrator Connection in Books Online and get it set up for remote access if you can't get into the SQL Server at all. This could allow forensics when things are totally gummed up.
Get sp_whoisactive and put it on box and learn to use it. AWESOME free script from Adam Machanic on SQLBlog.com. He has a great 30-day blog series on it's awesomeness.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 18, 2015 at 8:50 am
Almighty (5/18/2015)
if possible run the Trace on SQL server from other client machine (not from the same server) for 1 whole day or at list for peak hour and analysis this trace in tunning adviser . and check the recommendations
No, absolutely not. Firstly Profiler GUI (trace from another client) can crash busy servers and second DTA is terrible at over-recommending and recommending badly.
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
May 18, 2015 at 9:00 am
STAY AWAY FROM DTA!!!!!!!!!! I once had to spend over 200 man hours of my time unwinding the mess a client created with rampant use of it. They eventually made their system almost non-functional with it!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 18, 2015 at 10:12 am
audiocool (5/17/2015)
What I did is restart SQL Server Service. Then it solve for temporary.
That's to be expected. You've killed all the connections and cleared the buffer pool 😉
audiocool (5/17/2015)
Details Specification:------------------------
OS: Windows Server 2012
RAM: 32GB
Processor: 4 core with 2.0Ghz
SQL Server 2012
-------------------
Minimum Memory: 4GB
Maximum Memory: 24GB
I'm assuming that's the spec for the SQL VM, what are the specs for the host server itself?
Shawn Melton (5/17/2015)
What architecture is the OS and SQL Server (32bit or 64bit)
There is no 32 bit server edition of Windows or SQL server 2012
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
May 18, 2015 at 10:18 am
audiocool (5/18/2015)
After I adjust the maximum memory as mentioned as above which is 24GB out of my actual machine 32GB, it stay peak at 94 to 95%.I'm not sure whether this is the bug from SQL Server 2012 SP2 or I need to do some fine tuning on application but I really never encounter this is my old SQL Server 2000 with 4GB of RAM only. This is the part I really not understand. Same databases are moved over to new SQL Server 2012 with 32GB. I expect to get some performance gain over the old server. Hmmm...
I read from Microsoft website there is a release on Cumulative Update 5 which cover some of the issue which are memory leak, CPU spike. To apply this not an easy as I can do it at now. Downtime is require and after apply this CU5 not sure whether it will solve the issue I faced at the moment.
You're really just poking at this.
You need to come at it from a much more systematic approach. Please, go get the book that Gail recommended. It will help you understand what's happening on your system. It sounds like you've hit some pretty serious issues. It doesn't sound like it's something that's likely to get fixed just by changing a single setting on the server.
"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
May 18, 2015 at 11:01 am
GilaMonster (5/18/2015)
Almighty (5/18/2015)
Adjust the max memory of sql server to 80 % of total memory and so that you wont face the issue againIf you look at his original post, his max server memory is already below 80% of total memory. 24GB max server memory on a 32GB memory server is 75%.
Fixing problems such as the ones described are not as simple as changing one setting to an arbitrary value.
Piling on my 2Cents, Have few servers with similar spec and same amount of memory, few hints on how to keep them stable: No Trace, No RDP, SQL Max Mem normally no higher than 20Gb. The high CPU usually starts when the OS is pressurized and starts paging out the memory, the effect of that snowballs and results in a very sluggish SQL performance.
😎
Obviously it only takes a single bad query to rock the boat so be on the lookout for those.
May 19, 2015 at 1:33 am
Hi Guys,
I knew that no matter if I pump in more RAM to the new SQL Server, it will just slowly consume it compare to current situation.
For my case, the memory usage I saw in task manager is it always between 30.2/32GB (95%), and 30.2 is moving +- for around one day start from yesterday. Is this figure normal ?
Here is the screenshot:
May 19, 2015 at 2:26 am
GilaMonster (5/18/2015)
This is indeed normal behaviour for SQL, it will take all the memory it's allowed to have and not give it back.
30GB in use is not hard, especially if there's stuff other than SQL Server or if SQL using CLR or other things that allow it to consume memory outside of the buffer pool
This is why Grant and I aren't suggesting you add memory, but are recommending that you do some systematic investigation and diagnosis to identify the root cause of the slow downs, time outs, high CPU and the like. You're not going to fix this by trying stuff at random. See the book I recommended.
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
May 19, 2015 at 4:32 am
audiocool (5/19/2015)
Hi Guys,I knew that no matter if I pump in more RAM to the new SQL Server, it will just slowly consume it compare to current situation.
For my case, the memory usage I saw in task manager is it always between 30.2/32GB (95%), and 30.2 is moving +- for around one day start from yesterday. Is this figure normal ?
Here is the screenshot:
It's very normal for SQL Server to consume the amount of memory that you give it, yes. That's not an indication of anything bad. It's expected. You must try to gather more complete metrics. Read the book recommendation. It's free to download. If nothing else, look at sys.dm_os_wait_stats to see what is currently causing the system to run slowly.
"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
May 19, 2015 at 4:50 am
Perry Whittle (5/18/2015)
audiocool (5/17/2015)
Details Specification:------------------------
OS: Windows Server 2012
RAM: 32GB
Processor: 4 core with 2.0Ghz
SQL Server 2012
-------------------
Minimum Memory: 4GB
Maximum Memory: 24GB
I'm assuming that's the spec for the SQL VM, what are the specs for the host server itself?
:Whistling:
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
May 19, 2015 at 8:52 am
my previous post attached picture with memory usage of 95% (30.2/32GB). The figure stay the same until I went back from work. Now (after 4 hours), I remote in and check the memory usage, it is now 100% (31.9/32GB)
Surprisingly, the application still running fine. What I do not understand is the memory usage stay almost one day for 95% and why suddenly it shoot up to 100% and it did not fall down to 95% again. I monitor for around 1 hour now, it still stay at 100%.
May 19, 2015 at 9:11 am
Don't fixate on one thing, you need a lot of stats and a lot of information to start diagnosing.
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
Viewing 15 posts - 16 through 30 (of 107 total)
You must be logged in to reply to this topic. Login to reply