none
Slow query processing RRS feed

  • Question

  • We currently have an ETL job hat runs at night. This Job is taking longer to complete than it normal does. We are not seeing any CPU or Memory or blocking issues, need some help troubleshooting the issue.

    Kiran

    Monday, November 11, 2019 3:09 PM

All replies

  • Hi Kiran,

    It's difficult to advice on this as question has almost no details in it.

    To get started, first try to find the SQL/SSIS code part of your ETL job that is taking longer than usual. Later you can focus on optimizing it or post the findings if unable to optimize.


    Regards,
    Vaibhav

    Monday, November 11, 2019 6:28 PM
  • What does the job do? Perhaps networking issue ?

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, November 12, 2019 3:58 AM
    Answerer
  • Hi juniorkiran,

    For query slowly issue, you can try to compare the Execution plan and Extended Event between normal and abnormal to capture the issue.  You’d better share us more information about your issue.

    Best regards,
    Cathy 

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Tuesday, November 12, 2019 5:43 AM
  • What information u need? I am ready to provide

    Kiran

    Tuesday, November 19, 2019 4:40 PM
  • I was able to narrow down one of the part that is taking the longest and made a job just for it.  If I run the same job in our test environment it takes 22 minutes to complete.  In Production it takes 30 minutes.  It does seem to be when our SSRS server is creating the reports is where the big time difference is.  I find that our test environment seems to be consistent where our production environment is the one that varies.  Also to note both our test and production run the same code and amount of data we mirror the data over to test.

    Kiran

    Tuesday, November 19, 2019 4:52 PM
  • I was able to narrow down one of the part that is taking the longest and made a job just for it.  If I run the same job in our test environment it takes 22 minutes to complete.  In Production it takes 30 minutes.  It does seem to be when our SSRS server is creating the reports is where the big time difference is.  I find that our test environment seems to be consistent where our production environment is the one that varies.  Also to note both our test and production run the same code and amount of data we mirror the data over to test.

    Kiran

    Tuesday, November 19, 2019 4:52 PM
  • SSRS generates a fair amount of traffic to one of the two DB's ReportServer or ReportServerTempDB. There should be disk IO to the data files for these, but there are log writes. I would check the log write activity for these two DB, including disk latency (Avg sec /write?)

    Also, is there a difference between the Prod and Test environments? Prod might be configured with two or more SSRS systems for continuity. It used to be (2005 era) that this configuration caused problems rather than solved. a running reporting would periodically switch from one to the other


    jchang


    • Edited by jchang61 Tuesday, November 19, 2019 6:22 PM
    Tuesday, November 19, 2019 6:18 PM
  • I am not seeing any slowdown in disk IO.  Everything seems between 1 - 2ms.  The wait times I am seeing have to do with PREEMPTIVE_OLEDBOPS.  We do have a linked server to Analysis Services.    Please also note that this  Analysis Service is on the same machine.  This seems to be one of the bigger wait times on the system. 

     


    Kiran

    Thursday, November 21, 2019 3:39 PM
  • sorry, I meant there should be no disk IO to the ReportServerTempDB. There is a table in there with 1 row per active connection, that gets updated with a big session value, so it does have log writes

    jchang

    Thursday, November 21, 2019 4:02 PM
  • So how does this correlate to my original question.

    Kiran

    Friday, November 22, 2019 12:53 PM
  • I am not seeing any slowdown in disk IO.  Everything seems between 1 - 2ms.  The wait times I am seeing have to do with PREEMPTIVE_OLEDBOPS.  We do have a linked server to Analysis Services.    Please also note that this  Analysis Service is on the same machine.  This seems to be one of the bigger wait times on the system.

    A PREEMPTIVE wait type means that there is work done outside SQL Server. In this case, presumably inside Analysis Services, so your bottleneck may be on that end.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Friday, November 22, 2019 10:56 PM
  • Sorry, let me update you on some of the tests that we have done and the next steps.  Please let me know what your thoughts are and what else we can look into.

     

    1. Memory – available memory is larger in production – this does not appear to be an issue
    2. CPU – CPU is not spiked, handles the test load with no issues
    3. Job has a fairly consistent pattern of taking longer each time it is run.  If you restart the SSRS service it returns to about 4 hours in Production vs 3 hours in test (this is total amount of time)  We were able to Isolate one area that is running slow and the difference is 35 – 38 minutes on Production and 23 – 27 minutes on Test.
    4. The actual query runs faster in production through SSMS
    5. The query plans on both servers look the same.
    6. Verified that connection polling is being used
    7. Both environments have had the latest Cumulative Updates applied – Production is still consistently taking an hour longer than test with the same workload
    8. Wait types in SQL Server are the same in both environments- Query store was running in production and not in test, so that was turned off, but did not have any real effect
    9. The reports are written to the c:\ drive on the server itself – so there is not an issue of network latency
    10. The production server has been isolated on the VM host, so there is no contention with other VMs
    11. Usage of SSRS is not an issue during the times the job is running
    12. Accounts have been changed to use SQL server accounts instead of network accounts no changes

     

    Yesterday we did some other tests.  We used our test job that we created and pointed Test SSRS server at production data and we were able to run the test job is 25 minutes.  When we pointed our Production SSRS server at test data it took 40 minutes to run


    Kiran

    10 hours 29 minutes ago
  • This may be an overly obvious question, but I ask it anyway: is the data the same in test and production? If the test database is an old copy of production, it may be that data volumes have grown and this is why the job is slower.

    You say that the query plans "look the same", but sometimes the difference may be small details. SSMS has a showplan compare feature, although I personally find it difficult to use. When I want to compare plans that seems to be identical at first glance, I use Beyond Compare from Scooter Software. Beyond Compare does not understand query plans as such, but it is a very powerful diff tool.

    You say that production has more memory, this can actually cause issues, because with more memory the optimizer may settle for a plan that requires a higher memory grant, and then that plan is severely misestimated and there is lot of spills to disk. But would this be the case, the plans would not look the same, I guess.

    Point 4 looks interesting. That indicates that there is a plan issue after all. As for why a query may run faster in SSMS than in the application, this is something I explain in my article Slow in the Application, Fast in SSMS?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    9 hours 40 minutes ago