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?
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]
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.
The issue you mentioned mostly happens due to the following reasons:
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
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.
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:
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:
Make sure that the view does not include large binary columns since it highly affects the query execution.
Even I do not think this should be the cause in that case. Try to check the available resources on your machine. For Example:
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.
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.
The last thing to mention is that there are several recommendations to improve the performance of Visual Studio. Feel free to check them: