May 21, 2015 at 8:06 am
SQLRNNR (5/21/2015)
Grant Fritchey (5/21/2015)
One of your top queries is from Activity Monitor. You should probably not have it open all the time. You're actually adding load to the server by doing that.Hahaha - Activity Monitor is one of the biggest causes of performance issues. So many clients open that and leave it open and wonder why performance sucks. Not quite as bad as running profiler against the server having performance issues - but it is up there.
Really? I did not know that.
Then again, I only open it when I'm trying to troubleshoot issues. But still, thanks for the info Grant and Jason.
May 21, 2015 at 8:27 am
Grant -
Have you ever seen I/O contention lead to high CPU usage?
I ran into this on a host they decided to use for all nightly backups at a corporate level.
If queries are part of the issue, any that may have been changed for the upgrade might be a place to start.
Although I tend to think if nothing has changed in the db, or the application, a quick look at the host might lend some clues.
Both SQL Server and the OS have much better memory management than in the past.
But something as simple as over committing memory on the host machine ( 4GB to 8GB to 32 GB, plus whatever else may have been added to the host) might be part of the issue.
Same host is part of the picture, but unless you manage the host, a lot of other things can come into play.
And checking base configuration - like lock pages in memory - is probably worth a check.
Comparing with the 2000 instance if still available, might lead to something overlooked.
May 21, 2015 at 8:37 am
From a DOS prompt (or even PowerShell) on the server, please post the results from running this:
powercfg -GetActiveScheme
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
May 21, 2015 at 9:04 am
Greg Edwards-268690 (5/21/2015)
Have you ever seen I/O contention lead to high CPU usage?
I have, but you tend to see it in the wait stats and the perfmon counters. High CPU, high page latch IO waits, low page life expectancy, high lazy writes, high disk activity.
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 21, 2015 at 9:28 am
Greg Edwards-268690 (5/21/2015)
Grant -Have you ever seen I/O contention lead to high CPU usage?
I ran into this on a host they decided to use for all nightly backups at a corporate level.
If queries are part of the issue, any that may have been changed for the upgrade might be a place to start.
Although I tend to think if nothing has changed in the db, or the application, a quick look at the host might lend some clues.
Both SQL Server and the OS have much better memory management than in the past.
But something as simple as over committing memory on the host machine ( 4GB to 8GB to 32 GB, plus whatever else may have been added to the host) might be part of the issue.
Same host is part of the picture, but unless you manage the host, a lot of other things can come into play.
And checking base configuration - like lock pages in memory - is probably worth a check.
Comparing with the 2000 instance if still available, might lead to something overlooked.
I've seen I/O contention cause everything except the First World War (and I have my suspicions). Most of the time it mainly evidences as I/O (of course) problems and memory problems (and no, I remain convinced, we don't have memory problems here).
There were changes. They upgraded from 2000 to 2014 and went to a bigger machine/VM (although that bit is more than a little unclear) with more memory. Evidently it all ran perfectly fine on 2000 with 4gb of memory, but now, on 2014, with (I think currently) 20gb of memory, it's all gone to hell.
From what everyone has said, my leading suspicions are (in rough order):
Anti-virus
VM configuration
Server configuration (no NOT memory, I suspect something CPU related)
Queries that have always caused pain but for some reason no one noticed before (the number of times I've seen this, "You just upgraded and everything has gone south... What's that... well, yeah, it always did this, but you made a change so now we know we can complain").
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
May 21, 2015 at 9:41 am
Grant Fritchey (5/21/2015)
From what everyone has said, my leading suspicions are (in rough order):Anti-virus
VM configuration
Server configuration (no NOT memory, I suspect something CPU related)
Queries that have always caused pain but for some reason no one noticed before (the number of times I've seen this, "You just upgraded and everything has gone south... What's that... well, yeah, it always did this, but you made a change so now we know we can complain").
Queries that ran fine previously because the limited resources didn't allow them to get out of control. More CPUs means more things can run at the same time and means that more queries can interfere with each other, block each other, take resources that the other now doesn't have, etc
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 21, 2015 at 10:18 am
GilaMonster (5/21/2015)
Grant Fritchey (5/21/2015)
From what everyone has said, my leading suspicions are (in rough order):Anti-virus
VM configuration
Server configuration (no NOT memory, I suspect something CPU related)
Queries that have always caused pain but for some reason no one noticed before (the number of times I've seen this, "You just upgraded and everything has gone south... What's that... well, yeah, it always did this, but you made a change so now we know we can complain").
Queries that ran fine previously because the limited resources didn't allow them to get out of control. More CPUs means more things can run at the same time and means that more queries can interfere with each other, block each other, take resources that the other now doesn't have, etc
Oh yeah, absolutely.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
May 21, 2015 at 6:22 pm
Grant Fritchey (5/21/2015)
One of your top queries is from Activity Monitor. You should probably not have it open all the time. You're actually adding load to the server by doing that.Let me start this by putting a couple of caveats. I'm not there. I can't see your servers or their configuration. That means that anything I'm about to say is wrong.
From what I can see, you're hitting CPU issues WAY more than anything that suggests memory problems. So, please, walk away from the memory. Don't mess with it any more. Let's focus on what the problem might be.
I still think it could be something on the VM, but I'm not going to attempt to advise you there.
On SQL Server, first, change the Cost Threshold.
Next, is the 2000 instance still running? I'm curious what the cost threshold there is. Also, I'm curious if the max degree of parallelism on the 2000 server and the new server are at the default, 0. If they're different, what are they?
Assuming it's not the VM and the values above are the same, then I think we're down to tuning queries.
I already changed cost of threshold from 5 to 40 as suggested.
Power option is changed from Balanced to High Performance.
For SQL Server 2000, cost of threshold is maintained as 5 under the setting name Parallelism, and checked "enabled all available processors". I remember when using SQL 2000, memory usage just 50% from total.
May 21, 2015 at 6:53 pm
Grant Fritchey (5/21/2015)
Greg Edwards-268690 (5/21/2015)
Grant -Have you ever seen I/O contention lead to high CPU usage?
I ran into this on a host they decided to use for all nightly backups at a corporate level.
If queries are part of the issue, any that may have been changed for the upgrade might be a place to start.
Although I tend to think if nothing has changed in the db, or the application, a quick look at the host might lend some clues.
Both SQL Server and the OS have much better memory management than in the past.
But something as simple as over committing memory on the host machine ( 4GB to 8GB to 32 GB, plus whatever else may have been added to the host) might be part of the issue.
Same host is part of the picture, but unless you manage the host, a lot of other things can come into play.
And checking base configuration - like lock pages in memory - is probably worth a check.
Comparing with the 2000 instance if still available, might lead to something overlooked.
I've seen I/O contention cause everything except the First World War (and I have my suspicions). Most of the time it mainly evidences as I/O (of course) problems and memory problems (and no, I remain convinced, we don't have memory problems here).
There were changes. They upgraded from 2000 to 2014 and went to a bigger machine/VM (although that bit is more than a little unclear) with more memory. Evidently it all ran perfectly fine on 2000 with 4gb of memory, but now, on 2014, with (I think currently) 20gb of memory, it's all gone to hell.
From what everyone has said, my leading suspicions are (in rough order):
Anti-virus
VM configuration
Server configuration (no NOT memory, I suspect something CPU related)
Queries that have always caused pain but for some reason no one noticed before (the number of times I've seen this, "You just upgraded and everything has gone south... What's that... well, yeah, it always did this, but you made a change so now we know we can complain").
Correction: We are upgrade from 2000 (VM) to 2012 (VM) and both from the same machine.
Both servers are allocated with 4 virtual processors with same speed 2.0Ghz.
Both server still alive now and 2000 is going to kick off soon.
May 21, 2015 at 7:46 pm
Now experiencing high memory usage and below is the screen shot:
May 21, 2015 at 8:47 pm
I don't recall reading it, so apologies if this is repetitive.
What is the total memory allocated to this VM?
Do you have a memory reservation set for this VM at the host so the memory cannot be stolen and used by another VM?
What is the max memory setting on this SQL Instance?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 21, 2015 at 9:32 pm
Grant Fritchey (5/18/2015)
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.
If we consider that the code was working fine on the old version of SQL and seems to be causing huge problems on the new box, I'd say it was a pretty good poke. I've never recommended installing a CU previously in my life BUT, because of the problems they've had in 2012 even through SP2, I strongly recommend that before anything else occurs on this problem, to get the 2012 up to CU5 even if it means downtime.
--Jeff Moden
May 21, 2015 at 9:33 pm
SQLRNNR (5/21/2015)
I don't recall reading it, so apologies if this is repetitive.What is the total memory allocated to this VM?
Do you have a memory reservation set for this VM at the host so the memory cannot be stolen and used by another VM?
What is the max memory setting on this SQL Instance?
Memory allocated to this VM is 32GB.
Below is the history that I set for Max and Min for SQL Instance:
1) Min and Max (Default)
2) Min (0) and Max (28GB)
3) Min (4096) and Max (24GB)
4) Min (5120) and Max (20GB) => Latest
I still have the performance issue until now.
Before that, from time to time I did the following switching the max memory from
24GB to 4096GB, wait a while then switch it back to 24GB, I saw memory usage from task manager actually went down.
But this is only effective for around 2 times u can do.. After this no impact after performing following statement. The only thing to do is restart service.
EXEC sp_configure 'max server memory (MB)', 4096;
RECONFIGURE WITH OVERRIDE;
GO
May 21, 2015 at 9:43 pm
Jeff Moden (5/21/2015)
Grant Fritchey (5/18/2015)
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.
If we consider that the code was working fine on the old version of SQL and seems to be causing huge problems on the new box, I'd say it was a pretty good poke. I've never recommended installing a CU previously in my life BUT, because of the problems they've had in 2012 even through SP2, I strongly recommend that before anything else occurs on this problem, to get the 2012 up to CU5 even if it means downtime.
Before I tried apply CU6 on my server, actually I have another SQL Server 2012 which currently running without SP2. My colleague ask my assistant to apply SP2 but it failed. After perform SP2, I can't start the SQL Server, SQL Agent services..
It mentioned that master database is corrupted.
Here is the log getting from Log folder in SQL Server:
2015-05-15 16:11:59.91 spid4s Creating procedure sp_add_jobserver...
2015-05-15 16:11:59.92 spid4s Error: 10011, Severity: 16, State: 1.
2015-05-15 16:11:59.92 spid4s Access denied.
2015-05-15 16:11:59.92 spid4s Error: 917, Severity: 15, State: 1.
2015-05-15 16:11:59.92 spid4s An upgrade script batch failed to execute for database 'master' due to compilation error. Check the previous error message for the line which caused compilation to fail.
2015-05-15 16:11:59.92 spid4s Error: 912, Severity: 21, State: 2.
2015-05-15 16:11:59.92 spid4s Script level upgrade for database 'master' failed because upgrade step 'msdb110_upgrade.sql' encountered error 917, state 1, severity 15. This is a serious error condition which might interfere with regular operation and the database will be taken offline. If the error happened during upgrade of the 'master' database, it will prevent the entire SQL Server instance from starting. Examine the previous errorlog entries for errors, take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion.
2015-05-15 16:11:59.92 spid4s Error: 3417, Severity: 21, State: 3.
2015-05-15 16:11:59.92 spid4s Cannot recover the master database. SQL Server is unable to run. Restore master from a full backup, repair it, or rebuild it. For more information about how to rebuild the master database, see SQL Server Books Online.
2015-05-15 16:11:59.92 spid4s SQL Server shutdown has been initiated
2015-05-15 16:11:59.92 spid4s SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.
Check from internet, I might due to McAfee Anti Virus blocking something.
May 21, 2015 at 9:50 pm
audiocool (5/21/2015)
SQLRNNR (5/21/2015)
I don't recall reading it, so apologies if this is repetitive.What is the total memory allocated to this VM?
Do you have a memory reservation set for this VM at the host so the memory cannot be stolen and used by another VM?
What is the max memory setting on this SQL Instance?
Memory allocated to this VM is 32GB.
Below is the history that I set for Max and Min for SQL Instance:
1) Min and Max (Default)
2) Min (0) and Max (28GB)
3) Min (4096) and Max (24GB)
4) Min (5120) and Max (20GB) => Latest
I still have the performance issue until now.
Before that, from time to time I did the following switching the max memory from
24GB to 4096GB, wait a while then switch it back to 24GB, I saw memory usage from task manager actually went down.
But this is only effective for around 2 times u can do.. After this no impact after performing following statement. The only thing to do is restart service.
EXEC sp_configure 'max server memory (MB)', 4096;
RECONFIGURE WITH OVERRIDE;
GO
Have you disabled McAfee? These symptoms wreak of a process outside of SQL Server consuming memory.
All in all, you have a ton of configuration and performance issues and hiring a consultant really would be the fastest way to get these problems resolved.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 15 posts - 61 through 75 (of 107 total)
You must be logged in to reply to this topic. Login to reply