February 3, 2017 at 8:09 am
We have lots of active SQL Server Agent jobs.
About 100.
15-20 of them run every 1 minute, 10 min, 30 min.
Including CDC Data Capture jobs.
Jobs do massive select, insert, less delete.
Lots of jobs run SSIS packages.
Server is experiencing performance problems. Very high Latency, Page Life Expectancy under the threshold constantly.
Shall we consider maybe moving SQL Agent Jobs to another box?
February 3, 2017 at 8:36 am
Just a thought... it would initially seem that the problem isn't SQL Server Agent, but what it is running. If you were able to setup Agent to run on a separate machine, it would still be running CDC, SELECT, INSERT, DELETE commands in the same intervals on the server hosting the actual databases, so would still experience the same performance problems. How much RAM do you have on the server, and approx. size of the databases on this server?
February 3, 2017 at 8:47 am
Chris is right. I take it you've got all this happening on one server. If you're thinking of a new server, then why not move not SQL Server Agent, but some of your databases. Depending on what you have, it may be an idea to move all your reporting databases to a new server, for example, and leave the OLTP databases where they are. You could then tweak some of the server settings (optimize for ad hoc, parallelism etc) that will have different optimum values for different kinds of environment.
John
February 3, 2017 at 8:51 am
Chris is right, moving the jobs to an Agent on a different server isn't going to achieve much, as the jobs are still going to interact with the server that is having performance problems .Looking at your specs is a very important first step. To add, what is the CPU usage like. Is it always sitting at 90%+, or full of peaks and troughs?
Do you also have any hungrier, less frequent jobs. Are they running during busy periods, can they be run when the server isn't so busy?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
February 3, 2017 at 8:58 am
We have:
One main OLTP database 3TB. There is a huge partitioned table that takes 2TB.
Other 7-8 databases much smaller sizes.
SQL Server is on VM.
RAM=128 GB
CPU usage is low (well, at least that's what we see on VM, how accurate it is i don't know)
VM is not dedicated. It is sharing resources.
I agree it all depends on what SQL code is executed from those jobs
but we're doing initial assessment of this Performance problem
and need to point possible bottlenecks first. For management.
Everything is on our plate:
Application Architecture changes:
------ move ETL part out of 3TB database to a separate database.
------ move Hangfire database (Messaging application) to a separate box
------ review SQL code (lots of cursors executing stored procedure that has another cursor....), concatenation in the JOINS, ugly WHERE clauses with 20 lines with concatenation, etc.
SQL Server settings changes:
------ MAX DOP
------ Minimum Memory
------ etc.
Review VM configuration
Review SAN storage
Review RAM
We just need to present to the management what are the main areas where change might be needed.
February 3, 2017 at 9:07 am
Guys,
What exactly means"....tweak some of the server settings (optimize for ad hoc, parallelism etc) ...." ?
February 3, 2017 at 9:12 am
I read on one of Brent Ozar blogs that it's a good idea to move SQL Server Agent to a separate box
because it's like another busy application that is stealing Memory from main SQL Server instance.
Especailly when running SSIS packages that need lots of memory (Data Flows with MERGE, DERIVE, SORT)
February 3, 2017 at 9:18 am
RVO - Friday, February 3, 2017 9:07 AMGuys,What exactly means"....tweak some of the server settings (optimize for ad hoc, parallelism etc) ...." ?
Optimize for ad hoc queries: if you set this on, query plans will only be cached after the second execution of the query. This reduces plan cache bloat where you have a lot of queries that run only once. If your workload consists chiefly of stored procedure executions, you wouldn't set this.
Max degree of parallelism: the maximum number of processors that a single query is allowed to employ
Cost threshold for parallelism: the query cost below which parallelism will not occur. The default is 5, which is far too low for almost all modern scenarios. What you increase it to depends on the type of workload
The above three settings can be found in the sys.configurations view and set using sp_configure. They're just three examples of server-wide settings that might be different on each of your servers if you were to separate databases out according to the type of workload.
John
February 3, 2017 at 9:21 am
RVO - Friday, February 3, 2017 9:12 AMI read on one of Brent Ozar blogs that it's a good idea to move SQL Server Agent to a separate box
because it's like another busy application that is stealing Memory from main SQL Server instance.
Especailly when running SSIS packages that need lots of memory (Data Flows with MERGE, DERIVE, SORT)
I don't think moving SQL Server Agent to another server would help with this. Certainly have SSIS on its own server if you have a lot of hungry packages running. But beware - you still have to license the server even if it's only running that one component!
John
February 3, 2017 at 11:17 am
RVO - Friday, February 3, 2017 9:12 AMI read on one of Brent Ozar blogs that it's a good idea to move SQL Server Agent to a separate box
because it's like another busy application that is stealing Memory from main SQL Server instance.
Especailly when running SSIS packages that need lots of memory (Data Flows with MERGE, DERIVE, SORT)
Sounds like you might just be moving problems to tax another server and then possibly introducing issues for the network.
Is there a reason you couldn't work on tuning the queries and packages? I would consider starting there rather than starting with the server itself. You could possibly need to move the packages but not necessarily. That introduces more than "just moving Agent to a different server". There are additional costs, efforts as well such as needing to have the same setup in other environments, making sure you have a good handle on kerberos issues for when they come up.
Since you started focusing on SQL Agent, I would guess that means that the issues with the server have been positively identified to be the jobs? So if you already got that far, have you identified which jobs? And have you gone through the stats, plans, etc for the SQL executing in those jobs? Have you identified some of the worst performing jobs or queries?
Sue
February 3, 2017 at 12:44 pm
John Mitchell,
Thanks for your replies.
I thought that the calling machine (where Agent is running)
will execute SSIS and use its Memory.
SSIS packages are stored as files on network drive.
February 6, 2017 at 10:22 am
RVO - Friday, February 3, 2017 12:44 PMJohn Mitchell,
Thanks for your replies.
I thought that the calling machine (where Agent is running)
will execute SSIS and use its Memory.
SSIS packages are stored as files on network drive.
Yes, moving integration services to a separate machine will run the packages on that separate machine and use it's memory, however, in the original post you stated "Jobs do massive select, insert, less delete" and so the SSIS packages would still need to perform these operations on the actual database itself through a database connection. You also mentioned that "VM is not dedicated. It is sharing resources." It may help to verify how much memory is actually being used by SQL Server vs other programs running on the VM.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply