May 17, 2015 at 9:04 pm
Hi,
I have a new SQL Server 2012 (SP2 applied) installed in VM machine with OS Windows Server 2012.
When the server run the first day of production, I monitor the memory usage keep on increasing. I checked from forum saying that this is normal behavior for SQL Server.
I keep on monitor it, now it come to 99%, CPU -more than 90% and shop floor and warehouse guys keep on calling application cannot be accessed.
What I did is restart SQL Server Service. Then it solve for temporary.
Please advise me what can I do to deal with this problem as I'm not really expert on Database.
Details Specification:
------------------------
OS: Windows Server 2012
RAM: 32GB
Processor: 4 core with 2.0Ghz
SQL Server 2012
-------------------
Minimum Memory: 4GB
Maximum Memory: 24GB
Before start run in production mode, I did the Integrity Check, Rebuild Index, Re-Organize Index, Update statistic
May 17, 2015 at 9:48 pm
Truthfully, I would get support from company or consultant that is familiar with performance tuning and troubleshooting. This is something that can go back and forth trying to get down to the root of the problem via a forum or email.
Right off the bat I would ask:
- What architecture is the OS and SQL Server (32bit or 64bit)
- Is SQL Server the only thing installed on the server?
- What method are you using to determine CPU is above 90%? How do you know it is SQL Server that is causing the CPU spike?
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
May 17, 2015 at 10:23 pm
Hi Shawn,
- OS and SQL Server 2012 are 64 bits
- The server equipped with SQL Server 2012 and McAfee Virus Scan Enterprise v8.8
- I monitor through the task manager in Windows Server. When that time, it occupied more than 90%
May 17, 2015 at 10:28 pm
Oh ya, how can I check if operator complain when they tried to access one of the database and it caused time out. I check another application which access different database but no issue on that.
May 18, 2015 at 12:49 am
audiocool (5/17/2015)
Hi,I have a new SQL Server 2012 (SP2 applied) installed in VM machine with OS Windows Server 2012.)...
Details Specification:
------------------------
OS: Windows Server 2012
RAM: 32GB
Processor: 4 core with 2.0Ghz
SQL Server 2012
-------------------
Minimum Memory: 4GB
Maximum Memory: 24GB
Quick thought, 8Gb of RAM for the OS and any other services than the SQL Server is somewhat meager, suggest you lower the Maximum Memory setting and monitor Available Memory.
😎
May 18, 2015 at 2:33 am
Shawn Melton (5/17/2015)
Truthfully, I would get support from company or consultant that is familiar with performance tuning and troubleshooting.
To be honest, I second this. If there's no one there who knows SQL, this could take weeks and weeks of back and forth to not fix it.
This is indeed normal behaviour for SQL, it will take all the memory it's allowed to have and not give it back. Restarting SQL is not a solution, it just means that resources have to be used to repopulate the caches.
Timeout and high CPU are indicators of poorly performing code. You need to identify it and fix it.
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 3:21 am
before we moved to SQL Server 2012, we are using SQL Server 2000 with only 4GB of RAM. No issue at all and low memory usage. No need to manage SQL Server 2000. The only thing to do is schedule maintenance plan to perform optimization/integrity check on server during plant shutdown.
Why there is so huge different in SQL Server 2012, infra team already allocated 32GB RAM which is 8 times more than previous but we still encounter the issue.
May 18, 2015 at 3:31 am
audiocool (5/18/2015)
No need to manage SQL Server 2000.
That, unfortunately, is the core of the problem. SQL Server has never been a 'does not need maintenance' system.
Could be any number of things. I'd personally lean towards badly written queries, possibly combined with server or database configs that aren't ideal. I've seen a number of cases where database that didn't have a DBA, didn't have anyone knowledgeable about databases were upgraded in hardware and the performance nosedived. More resources means more queries can run simultaneously and when those queries are written badly they interfere with each other and performance drops.
Can you get someone in? Can you afford to get a specialist in for a couple of days to look at the system and identify the problems?
If not, you may well spend weeks or months trying to identify the root causes and fix them. To give you an idea, I've just finished a project for someone who had been struggling with poor database performance for over a year. I identified the root problems within 4 days.
If you absolutely can't, I'll point you at some resources you can use to start studying and investigating, but it's likely to take a while.
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 4:01 am
Hi,
Many thanks for your valuable advise. Could you please guide me through the steps to troubleshoot the problems in my environment. I had around 10 databases with 2 super active databases where the data changed might happened in every second.
Now the memory usage is peak at 94 - 95% all the time for about 7 hours already. What can I do for this. Should I leave it or ? At the moment (memory at 94-95%), there is still no performance issue reported by Production floor and warehouse.
I'm very appreciate your help.
Sorry we don't have DBA here and everything learn from internet. 🙂
May 18, 2015 at 4:06 am
audiocool (5/18/2015)
Sorry we don't have DBA here and everything learn from internet. 🙂
Which is why I'm very strongly suggesting you get a 3rd party in. There are lots of good people in all areas of the world that could probably help you.
For self-study, the book "Troubleshooting: A guide for the accidental DBA" is a good place to start. Work through chapter 1, get as much data as you can, then go through the other chapters as you need depending on the problem you are trying to solve.
http://www.red-gate.com/community/books/accidental-dba
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 5:51 am
audiocool (5/18/2015)
Hi,Many thanks for your valuable advise. Could you please guide me through the steps to troubleshoot the problems in my environment. I had around 10 databases with 2 super active databases where the data changed might happened in every second.
Now the memory usage is peak at 94 - 95% all the time for about 7 hours already. What can I do for this. Should I leave it or ? At the moment (memory at 94-95%), there is still no performance issue reported by Production floor and warehouse.
I'm very appreciate your help.
Sorry we don't have DBA here and everything learn from internet. 🙂
Time Being What you Can Do is Allocate the Max Memory to SQL severer Approximately 80 % of total memory so that atlist you wont get run of of memory. and also check the scanning schedule of the antivirus because scanning also cause overhead for CPU.
and then go ahead with the suggestion of Gail, as it will take a time for you to understand DBA work and troubleshoot the issue
May 18, 2015 at 6:53 am
audiocool (5/18/2015)
Hi,Many thanks for your valuable advise. Could you please guide me through the steps to troubleshoot the problems in my environment. I had around 10 databases with 2 super active databases where the data changed might happened in every second.
Now the memory usage is peak at 94 - 95% all the time for about 7 hours already. What can I do for this. Should I leave it or ? At the moment (memory at 94-95%), there is still no performance issue reported by Production floor and warehouse.
I'm very appreciate your help.
Sorry we don't have DBA here and everything learn from internet. 🙂
Start with the book Gail recommended. Then, assuming you're still hitting query problems, move on to my book on performance tuning. It's not as easy as listing three steps and you'll have good performance. It's a lot of work and quite a bit of knowledge needed.
"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 6:54 am
audiocool (5/18/2015)
Hi,Many thanks for your valuable advise. Could you please guide me through the steps to troubleshoot the problems in my environment. I had around 10 databases with 2 super active databases where the data changed might happened in every second.
Now the memory usage is peak at 94 - 95% all the time for about 7 hours already. What can I do for this. Should I leave it or ? At the moment (memory at 94-95%), there is still no performance issue reported by Production floor and warehouse.
I'm very appreciate your help.
Sorry we don't have DBA here and everything learn from internet. 🙂
Adjust the max memory of sql server to 80 % of total memory and so that you wont face the issue again . and than go for learning SQl DBA work and troubleshooting as it may take time .
May 18, 2015 at 6:58 am
Almighty (5/18/2015)
Adjust the max memory of sql server to 80 % of total memory and so that you wont face the issue again
If 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.
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:14 am
Thank you so much for all your advise.
This evening I encounter the same issue happened last week which are shop floor unable to access to one of the database, at that point of time I really have no idea what is going on rather than just restart the service until today evening same issue happened again, same database not able to access..
Check the activity monitor and found out there is a dead lock happened. Kill the process and it run again. There is a background job which accessing (Read/delete) for the same table (> 100,000) in execution/min.
Viewing 15 posts - 1 through 15 (of 107 total)
You must be logged in to reply to this topic. Login to reply