locked
Performance Issues SSDT vs BIDS RRS feed

  • Question

  • I have a report that in BIDS takes 3:45 minutes to run. I imported the report into SSDT and it takes 45:55 minutes to run. That's roughly 15 times as long. The report uses expressions in every field and relies a lot on the LookupSet function and underlying report specific code. If I remove the chart and replace it with a table for all of the datasets it takes 14 sec in BIDS and 28 Sec in SSDT (still twice as long).
    What can I do to improve the performance in SSDT?

    Some background on my environments:
    SSDT:
    Microsoft Visual Studio 2015 Shell (Integrated) Version 14.0.25420.01 Update 3
    Microsoft .NET Framework Version 4.7.02558
    Installed Version: IDE Standard
    Microsoft Visual Studio Tools for Applications 2015 00322-10000-00000-AA441
    Visual Basic 2015 00322-10000-00000-AA441
    Visual C# 2015 00322-10000-00000-AA441
    JavaScript Language Service 2.0
    JavaScript Project System 2.0
    SQL Server Analysis Services 14.0.1016.232
    SQL Server Data Tools 14.0.61712.050
    SQL Server Integration Services Version 14.0.1000.169
    SQL Server Reporting Services 14.0.1016.232
    Microsoft SQL Server Reporting Services Designers Version 14.0.1016.232

    BIDS:
    Microsoft Visual Studio 2008 Version 9.0.30729.4462 QFE
    Microsoft .NET Framework Version 3.5 SP1
    Installed Edition: Professional
    Microsoft Visual Basic 2008 91605-270-8171362-60651
    Microsoft Visual C# 2008 91605-270-8171362-60651
    Microsoft Visual Studio 2008 Tools for Office 91605-270-8171362-60651
    Microsoft Visual Web Developer 2008 91605-270-8171362-60651
    Hotfix for Microsoft Visual Studio 2008 Professional Edition - ENU (KB2538241) KB2538241
    Hotfix for Microsoft Visual Studio 2008 Professional Edition - ENU (KB944899) KB944899
    Hotfix for Microsoft Visual Studio 2008 Professional Edition - ENU (KB945282) KB945282
    Hotfix for Microsoft Visual Studio 2008 Professional Edition - ENU (KB946040) KB946040
    Hotfix for Microsoft Visual Studio 2008 Professional Edition - ENU (KB946308) KB946308
    Hotfix for Microsoft Visual Studio 2008 Professional Edition - ENU (KB946344) KB946344
    Hotfix for Microsoft Visual Studio 2008 Professional Edition - ENU (KB946581) KB946581
    Hotfix for Microsoft Visual Studio 2008 Professional Edition - ENU (KB947171) KB947171
    Hotfix for Microsoft Visual Studio 2008 Professional Edition - ENU (KB947173) KB947173
    Hotfix for Microsoft Visual Studio 2008 Professional Edition - ENU (KB947180) KB947180
    Hotfix for Microsoft Visual Studio 2008 Professional Edition - ENU (KB947540) KB947540
    Hotfix for Microsoft Visual Studio 2008 Professional Edition - ENU (KB947789) KB947789
    Hotfix for Microsoft Visual Studio 2008 Professional Edition - ENU (KB948127) KB948127
    Hotfix for Microsoft Visual Studio 2008 Professional Edition - ENU (KB953256) KB953256
    Hotfix for Microsoft Visual Studio 2008 Professional Edition - ENU (KB971092) KB971092
    Microsoft Visual Studio 2008 Professional Edition - ENU Service Pack 1 (KB945140) KB945140
    Security Update for Microsoft Visual Studio 2008 Professional Edition - ENU (KB2251487) KB2251487
    Security Update for Microsoft Visual Studio 2008 Professional Edition - ENU (KB972222) KB972222
    Security Update for Microsoft Visual Studio 2008 Professional Edition - ENU (KB973675) KB973675
    SQL Server Analysis Services Version 10.50.6000.34
    SQL Server Integration Services Version 10.50.6000.34
    SQL Server Reporting Services Version 10.50.6000.34
    Update for Microsoft Visual Studio 2008 Professional Edition - ENU (KB956453) KB956453
    Update for Microsoft Visual Studio 2008 Professional Edition - ENU (KB967143) KB967143

    Wednesday, August 8, 2018 2:01 PM

All replies

  • Are SSDT and BIDS on the same machine, or different machines?

    If different machines, are they are the hardware and configuration identical?

    HTH,


    Phil Streiff, MCDBA, MCITP, MCSA

    Thursday, August 23, 2018 6:30 PM
  • The same machine. I'm running them side by side as I transition from one environment to the other.
    Thursday, August 23, 2018 6:36 PM
  • Are you running the report against different backend databases or against the same database on same machine?

    Running both SSDT/BID reports from machine A against database on machine B, for example? Or, BIDS report against database on machine B and SSDT report against database on machine C?

    HTH,


    Phil Streiff, MCDBA, MCITP, MCSA

    Thursday, August 23, 2018 6:45 PM
  • Running against the same database. So to summarize; Both SSRS instances on the same machine running against the same database. The only difference is whether the report is being run through SSDT or BIDS. 
    Monday, August 27, 2018 12:36 PM
  • I've heard complaints of memory leaks in some versions of SSDT.

    Here's what I have on a Windows 2016 server:

    Microsoft SQL Server Data Tools for Visual Studio 2017 
    Version 15.7.6

    SQL Server Analysis Services   14.0.1016.285
    Microsoft SQL Server Analysis Services Designer 
    Version 14.0.1016.285

    SQL Server Data Tools   15.1.61804.210
    Microsoft SQL Server Data Tools

    SQL Server Integration Services   
    Microsoft SQL Server Integration Services Designer
    Version 14.0.3002.107

    You might try upgrading to newer versions of SSDT & SSRS to see if that fixes any known performance bugs.

    Maybe SSDT 15.1.61804.210 is optimized to run better on Windows 2016. I assume your server is Windows 2008 or 2012.

    HTH,


    Phil Streiff, MCDBA, MCITP, MCSA

    • Edited by philfactor Monday, August 27, 2018 1:10 PM
    Monday, August 27, 2018 1:07 PM