SQL Server Agent 2012 - Slow on startup and closure

  • 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

  • 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

  • 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.

  • 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

  • Thanks, I will try a hypervisor/sql forum!

  • 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

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply