Slow running SQL 2005 queries running from Virtual PC (Win XP Mode)

Question

Hi.

I've recently upgraded to a PC running Windows 7 Professional (x64) with 4G memory and a 350G HD. I develop using VB.net in VS 2005 for the 2.0 framework. We use a SQL 2005 back end. All tools are 32 bit.

Since I have so many problems running the IDE in Win 7, I am using a Virtual PC (Win XP mode) for development. Both the Virtual PC and the SQL Server I use for development are installed on my Win 7 x64 host.

Before I had my VM setup I was using RDC to connect to a second PC (Win XP) which I was using for development while I prepared the VM for use, which I've finally completed. The PC was my bosses 2nd PC and he expects to get it back soon.

Now, that I'm using the VM, any time I execute a query against the Dev SQL Server from the VM, it takes a very long time to return results. I can access the same tables on the production server from the VM and the results are returned instantly. If I execute a query against the dev SQL Server from the RDC PC, the queries return instantly. 

It is slow only when  I execute a query against the dev SQL Server from the VM and I can't figure out why. I've checked the speed using the Server Explorer in the IDE and by running apps that use tableadapters with simple 'Select * from [Table]' select commands. All are slow. If I go into the dataset designer and preview the data for a table adapter, again it's too slow. 

I am not sure where to look nor what to look for. Any suggestions or solutions would be welcome.

Thanks.

Marshall

 

---

Marshall Youngblood

Answer 1

One possibility would be that you didn't give enough memory to your Virtual PC.  A second possibility would be that there is some problem with the protocol used between your Windows 7 and the Virtual PC.  To check that, you should first make  some tests using SSMS running inside the Virtual PC. After that, if you are using a Windows account to connect to the SQL-Server, try with a SQL-Server Login Account.  Finally, try deactivating the Shared Memory and the Named Pipes protocols and keep only the TCP/IP protocol on the client machine (Windows 7) by using the SQL-Server Configuration Manager.  Make sure that this protocol is activated on the server machine (the Virtual PC) as well.  Don't forget to allow the port 1433 through your firewall (or deactivate it temporarily on both the client and the server machines).

You can repeat the last test with the Named pipes protocol by deactivating both the Shared Memory and the TCP/IP protocol.

You also don't say which provider (ODBC, OLEDB, Native SQL-Server Provider, etc.) you are using.

You should also try testing the performance of the Virtual PC by running some lengthy process (or even an infinite loop) both when the Virtual PC has the focus and while it doesn't have it.   With the older version of Virtual PC, there was a 66% change in performance when the virtual machine didn't have the focus and when running in the background.

Answer 2

Hi Marshall,

I agree with Sylvain. Have you tried adding more memory to Virtual PC, keeping only TCP/IP protocol enabled or upgrading to a new version of Virtual PC?  You could also open Performance Monitor on VM to monitor Network I/O and Memery usage.

---

Best Regards,
Peja

Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

Answer 3

A few points to make.

Due to my limited resources, the Win7 64 machine is running the SQL server instance I'm using for deveopment. I am just now finally able to get around to installing SSMS on the Virtual PC (VPC) to try some of the things you suggested. 

I beleive you were thinking the VPC was the running the server? It's the other way around. My deveopment environment is running in the VPC.

From the beginning it takes 18-19 seconds to execute a query that returns 420 rows. On the host (win 7 64) the time is so short it shows as zero time to execute.

I've set an exception in the Firewall on the VPC for port 1433 (TCP).

I've tried disabling named pipes and shared memory, leaving just TCP-IP on the server machine and it still ran slow without changes. When I left just Named Pipes enabled the same query ran on and on. I stopped it after 5 minutes. 272 rows were returned. I reconnected to the server and tried again. After 1 minute I still did not see any rows, so I stopped the execution of the query.  24 rows returned.  (A simple Select From query.)

After re-enabling TCP-IP the performance went back to returning the full result set, but it still took 17 seconds for 420 rows.

My OS came pre-installed with VPC and VPC WinXP Mode. That was about 3-4 months ago. I reinstalled the Virtual PC and downloaded this version of Virtual PC about 2 months ago, so I'm pretty sure I have the latest available.

As for the protocol, what protocol does SSMS use? I'm not sure but I'd assume it uses the native client? My applications, written using .NET 2.0 and VB 2005 uses the NET Framework Data Provider for SQL Server. I assume that's native as well?

I've upded the memory on the VPC from 1.5G to 2G, no change in performance.

Lastly, I will try the 'lengthy process' later today, but I'm not sure what that will prove. I always havwe focus when I use it, and if I understand that is a problem with older versions of VPC, and this is a newer version.

Thanks. I appreciate all of the tips. Do you have any more?

 

Marshall Youngblood

---

Marshall Youngblood

Answer 4

I'm not sure if you've found a solution to this but I've had the exact same problem and have been working on it for hours.  I finally stumbled on a solution.   I have absolutely no reason why, but installing Oracle Virtual box fixed all my speed issues in XP Mode.  I'm not using Virtual Box, I just installed it on the host and then ignore it.  During the install it must fix some network issue.  In any case a query in Access 2003 linking to SQL 2008 on the host now returns 20000 records instantly whereas before it was taking 10 seconds or more.

I hope this helps somebody.

Answer 5

hi, you may get your answer from the below link. check it out.

http://www.sqlservercentral.com/Forums/Topic886618-149-1.aspx

Answer 6

Thanks jbinoville, I was having a very similar problem where I was running .NET app in XP Mode and querying a SQL 2008 R2  database running on the Windows 7 host.  The application was taking several minutes to query and display the data from the SQL database.  After installing Oracle VM Virtual Box, it only took a few seconds.