Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL Server Agent 2012 - Slow on startup and closure Expand / Collapse
Author
Message
Posted Tuesday, April 2, 2013 4:29 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, May 20, 2013 5:33 AM
Points: 3, Visits: 30
Dear SQL users,

Since couple of weeks we've been working with SQL Server 2012 (Microsoft SQL Server 2012 - 11.0.2100.60 (X64) Standard Edition (64-bit) (Build 7601: Service Pack 1). We still have our old production centre which works on SQL Server 2005.

The problem lies within the SQL Server Agent, all Agent settings are similar to our good working 2005 environment. A simple job with single t-sql step 'select @@version' takes over 40 seconds in 2012 and < 1 second in 2005 environment. Running the query in 2012 query window it has the same (expected) performance of < 1 sec.

What is it that makes the 2012 Agent so slow?

Interesting is that the job step takes 6 seconds and the total job takes 40 seconds.

Any ideas??

Thanks in advance,
Gr. Erik


  Post Attachments 
SS2012.png (6 views, 6.72 KB)
Post #1437814
Posted Thursday, April 4, 2013 7:23 PM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 9:13 AM
Points: 80, Visits: 344
The sql server agent job runs under the credentials of the service account.

Question 1 - Do you have the same performance issue when logging into SSMS using that account?


The min / max memory settings are sometimes forgotten about when building out a new server.

Question 2 - Did you use the correct settings?

http://www.sqlservercentral.com/blogs/glennberry/2009/10/29/suggested-max-memory-settings-for-sql-server-2005_2F00_2008/


Question 3 - Is this a physical or virtual server that you have SQL Server installed on? If it is a virtual server, there can be issues with the ballon memory manager, I am not a VM expert.


Question 4 - Make sure you have the latest service pack and cummulative updates for SQL Server 2012.


Please answer these questions. It will give me a better idea of what you are dealing with.



John Miner
Crafty DBA
www.craftydba.com
Post #1439070
Posted Friday, April 5, 2013 5:09 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, May 20, 2013 5:33 AM
Points: 3, Visits: 30
SQL Server and Agent both run under LocalSystem.
Q1: In SSMS query window nog performance delay, its <1 second.
Q2: Yes, 12GB, 3GB free of RAM
Q3: Its virtual (hypervisor)
Q4: Installed 11.0.0.3000 this morning (SP1), still same issue

I've managed to narrow the problem, it looks like a delay on SQL Server Agent. When i reboot the SQL Server Agent through services it takes up to 45 seconds to stop the server and 5 seconds to start it. Same on my old SQL 2005 server is done under 10 seconds.

What causes the delay in stopping SQL Server Agent?


Erik



j.miner (4/4/2013)
The sql server agent job runs under the credentials of the service account.

Question 1 - Do you have the same performance issue when logging into SSMS using that account?

The min / max memory settings are sometimes forgotten about when building out a new server.

Question 2 - Did you use the correct settings?

http://www.sqlservercentral.com/blogs/glennberry/2009/10/29/suggested-max-memory-settings-for-sql-server-2005_2F00_2008/


Question 3 - Is this a physical or virtual server that you have SQL Server installed on? If it is a virtual server, there can be issues with the ballon memory manager, I am not a VM expert.


Question 4 - Make sure you have the latest service pack and cummulative updates for SQL Server 2012.


Please answer these questions. It will give me a better idea of what you are dealing with.

Post #1439161
Posted Friday, April 5, 2013 2:11 PM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 9:13 AM
Points: 80, Visits: 344
Definitely a VM hypervisor issue.

I would do a google search. Maybe post under a VM / SQL forum.

We use hyper-v for test boxes only. Do not have this issue.

All production boxes are physical hardware!

Makes like fast and easy, the way I like it!




John Miner
Crafty DBA
www.craftydba.com
Post #1439469
Posted Monday, April 8, 2013 4:36 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, May 20, 2013 5:33 AM
Points: 3, Visits: 30
Thanks, I will try a hypervisor/sql forum!
Post #1439740
Posted Thursday, April 11, 2013 9:39 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 6, 2013 5:36 AM
Points: 3, Visits: 10
We have it working ok under Hyper-V.
Maybe something specific to your hyper-v setup.


Matt Jones
IT Support Analyst
ITS | [http://www.theitsolution.co.uk/london-it-support-computer-repair/it-support-london.html]IT Support London[/url] | IT Support Chelsea
Post #1441340
Posted Friday, April 12, 2013 7:06 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 11:40 AM
Points: 293, Visits: 810
Could be this again:
http://kb.vmware.com/selfservice/microsites/search.do?language=en_US&cmd=displayKC&externalId=1012225

More info here:
http://jonmorisissqlblog.blogspot.com/2013/04/event-id-7000-sql-serverservice-failed.html
http://www.sqlservercentral.com/Forums/Topic1300021-2799-2.aspx
http://social.msdn.microsoft.com/Forums/en-US/sqlsetupandupgrade/thread/a576aa0e-5a08-4c9c-a583-af1815cd8b8e/
Post #1441985
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse