1

I administer a datawarehouse running on SQL Server 2016. This datawarehouse has most of its ETL processing done during a nightly load, except for a few SSIS packages that run throughout the day, then SSAS processing done afterward, so when I arrive in the morning, there's no active monitoring I can perform.

The processing of this nightly load fluctuates, sometimes more than expected, but with the current monitoring setup, there is none other than a sp_whoisactive loaded into a table every 30 minutes, it makes it difficult to try and tune this process. I can obviously look to the plan cache but with the nightly ETL taking about 6 hours, the plan cache does not hold all of the plans used for the night.

Has anyone come across any performance monitoring/logging tools, the cheaper the better, that have been useful when trying to investigate and tune nightly loads like this? I'm most interested in looking for information to help tune indexes or see when/where I have CPU or memory bottlenecks.

Improve this question
3
  • What do you use at the minute?
    – Vérace
    Oct 10, 2018 at 3:32
  • 1
    Have you enabled Query Store? learn.microsoft.com/en-us/sql/relational-databases/performance/… Do you have the sp_BlitzCache installed? brentozar.com/blitzcache Try turning on Query Store and capture BlitzCache to a table like whoisactive. That will get you started on identifying problem queries.
    – HandyD
    Oct 10, 2018 at 3:47
  • I've heard about query store, but haven't deep dived into it yet to see what the overhead is and other useful information when enabling it. I'll look into it and see if I can convince our production DBA to enable it. Thank you!
    – Will Schultz
    Oct 10, 2018 at 11:39

1 Answer 1

Reset to default
2

For SSIS you can implement logging on the packages. A good place to start looking would be at the ProcessInput and PrimeOutput steps of the components within the packages. The PipelineComponentTime event will be useful to check for these steps, as it records the time spent in each of them. You can read more about logging for SSIS here.

For SSAS you can use Extended Events to monitor the performance while the processing is being done. The ProgressReportEnd and ResourceUsage events are two that you may want into look into examing during the times the cubes are processed, and well as the RequestProcessBegin and RequestProcessEnd events. This link will further outline the use of Extended Events in SSAS.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.