Martin Griffiths
asked on
Performance issues with SQL Server 2016
We're having a few issues with integration services packages taking a long time to run on our new SQL Server 2016 server. Has anyone come up against issues where SSIS jobs hang indefinitely at the pre-execute phase?
Do you have any recommendations on what we can do optimise performance for SQL Server 2016 please? Is it worth setting maximum server memory for SQL for example?
Do you have any recommendations on what we can do optimise performance for SQL Server 2016 please? Is it worth setting maximum server memory for SQL for example?
ASKER
Yes, it's on the same server unfortunately as it will be difficult to justify the cost of having two SQL Server enterpise edition licenses.
The only justification is performance. And Microsoft recommends to separate them as well.
Anyway, if you can't move it then is better to give more memory otherwise SSIS can take it all and SQL Server engine will suffer from that.
How much memory to you have actually in the server?
Anyway, if you can't move it then is better to give more memory otherwise SSIS can take it all and SQL Server engine will suffer from that.
How much memory to you have actually in the server?
ASKER
Ok. You don't have a link to where Microsoft have issued this recommendation do you? That may aid our business case. We have four main SQL Server servers, 3 OLTPs and a BI server. We've always housed the SSIS packages on each of these servers. Is it therefore better to move all SSIS packages off these four on to a separate fifth server? We've got 16GB of RAM at the moment on that server. Are you saying allocate more memory to that server?
If you have SSAS in a dedicated server, then you can always move SSIS to that server.
I had that recommendation from a Microsoft engineer itself but will try to find some official article with that written.
I had that recommendation from a Microsoft engineer itself but will try to find some official article with that written.
ASKER
Ewh...I regret to say we have SSAS on the same server as well (although we've not used it yet). Ah, that would be really handy please Vitor. We may be able to get away with just Standard edition rather than enterprise for SSIS, although I think there are bigger differences for SSAS in editions.
Unfortunally I didn't find any official documentation.
In my previous company our landscape was dedicated SQL Server engine servers and SSAS and SSIS installed together in a different server. That landscape were defined with the recommendations of the Microsoft Engineering team and I can assure the performance boosted since the first minute we removed SSIS from the SQL Server engine machine.
In my previous company our landscape was dedicated SQL Server engine servers and SSAS and SSIS installed together in a different server. That landscape were defined with the recommendations of the Microsoft Engineering team and I can assure the performance boosted since the first minute we removed SSIS from the SQL Server engine machine.
ASKER
Thanks Vitor. Sounds like a new environment where we have our three OLTP servers (with no SSIS packages running on them), plus a BI server (enterprise edition) for our warehouses and reporting, and a new Enterprise (or possibly standard) server with just SSAS and SSIS running on it is the best configuration?
I noticed what I believe to be a misconception in one of the comments above: I believe that you can install SSIS on a machine that does not have SQL Server installed on it - obviously it'll have to be able to "see" a SQL Server machine, but that could be standard or even express edition! In that way you would not have to shell out for yet another copy of Enterprise Edition!
Refer to MS docs here: https://msdn.microsoft.com/en-us/library/ms143731.aspx
hth
Mike
Refer to MS docs here: https://msdn.microsoft.com/en-us/library/ms143731.aspx
hth
Mike
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Many thanks Vitor.
Do you have SSIS installed in the same box of SQL Server? If so, I highly recommend you to migrate SSIS to a dedicated server since it can steal resources from SQL Server engine.