Search code examples
sql-servervisual-studioviewssisetl

Visual Studio Integration Services becomes unresponsive

I am developing ETL solutions in Visual Studio and as soon as I select a view from a SQL Server database, the Visual Studio freezes, and clicking anywhere results in the following notification: "Visual Studio is Busy".

It is very frustrating and I cannot finish creating my solution.

Any advice for making it faster and more responsive?

Solution

  • I. What happens when selecting a view as an OLE DB Source?

    I created an SQL Server Profiler trace to track all T-SQL commands execute over the AdventureWorks2017 database while I am selecting the [HumanResources].[vEmployee] view as an OLE DB Source.

    The following screenshot shows that the following command is executed twice:

    set rowcount 1
select * from [HumanResources].[vEmployee]

    enter image description here

    This means that the OLE DB source limit the result set of the query to a single row and executes the Select * command over the selected view in order to extract the required metadata.

    It is worth mentioning that the SET ROWCOUNT 1 causes SQL Server to stop processing the query after the specified number of rows are returned. This means that only one row is requested and not all the view's data.

    enter image description here

    II. Issue's possible reasons

    The issue you mentioned mostly happens due to the following reasons:

    (1) Third-party extensions installed in Visual Studio

    In that case, you should try to start Visual Studio in safe mode to prevent loading third-party extensions. You can use the following command

    devenv.exe /safemode

    (2) View querying a large amount of data

    Visual Studio may freeze if the view returns a huge amount of data or contains bad JOINS. You may solve this using a simple workaround. Alter the view's SQL and add a condition that only returns a few rows (For example SELECT TOP 1). Then, use this view while designing the package. Once done, remove the added condition.

    (3) Bad database design

    Moreover, it is highly important that your views are well designed and that the underlying tables have the appropriate indexes. Besides, check that you don't have any issues related to the database design. For example:

    (a) Index fragmentation

    The index fragmentation is the index performance value in percentage, which can be fetched by SQL Server DMV. You can refer to the following article for more information:

    (b) Large binary column

    Make sure that the view does not include large binary columns since it highly affects the query execution.

    (4) Hardware issues

    Even I do not think this should be the cause in that case. Try to check the available resources on your machine. For Example:

    (a) Drive out of storage

    If using windows, check the C: drive storage (default system databases directory) and the drive where the databases are stored and make sure they are not full.

    (b) Server is out of memory

    Make sure that your machine is not running out of memory. You can simply use the Task Manager to identify the amount of available memory.

    (5) Optimizing Visual Studio performance

    The last thing to mention is that there are several recommendations to improve the performance of Visual Studio. Feel free to check them: