Mark
asked on
SQL Server 2005 running VERY slow on Virtual Machine
In order to begin retiring a legacy system I've move an XP running SQL Server 2005 to a virtual machine (Oracle VirtualBox). Workstations on the LAN can access this database.
The problem is the queries take a long time and at least one of the queries from remote Access apps often timeout. This is true even when locally logged into the VM and running queries via local Access -- many, but not all queries timeout. Even in SQL Server Management Studio simple queries can take 44secs to 1+ minutes. In this condition this VM is unusable as an SQL Server. I know VMs are used as database servers all over the place, so this should not be a unique implementation.Is there something I can do to analyze SQL Server and up the performance?
The physical source machine from which this VM was cloned was an AMD Sempron 3100+ 1.8GHz, 2GB memory with a Benchmark of 452. The Linux Slackware64 14.2 VM Host has an Intel i7 4790K 4.0GHz, 4 cores, 8GB of memory with a Benchmark of 11184 8G -- 25 times faster than the AMD. I actually expected the VM to run faster than the old physical computer.
Ideas?
The problem is the queries take a long time and at least one of the queries from remote Access apps often timeout. This is true even when locally logged into the VM and running queries via local Access -- many, but not all queries timeout. Even in SQL Server Management Studio simple queries can take 44secs to 1+ minutes. In this condition this VM is unusable as an SQL Server. I know VMs are used as database servers all over the place, so this should not be a unique implementation.Is there something I can do to analyze SQL Server and up the performance?
The physical source machine from which this VM was cloned was an AMD Sempron 3100+ 1.8GHz, 2GB memory with a Benchmark of 452. The Linux Slackware64 14.2 VM Host has an Intel i7 4790K 4.0GHz, 4 cores, 8GB of memory with a Benchmark of 11184 8G -- 25 times faster than the AMD. I actually expected the VM to run faster than the old physical computer.
Ideas?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
If I was in your situation I would quiesce the database and detach it. The, I'd replace XP with a newer 64-bit OS such as WIndows 7, then re-install Sql Server and re-attach the database. This would allow you to install more ram, assuming the hardware supports it, and remove some of the element of an ancient operating system from the equation. I think you'd get more "bang for your buck" doing this... and, you should still look into the I/O concerns I enumerated earlier.
Just my 2 cents worth :)
Just my 2 cents worth :)
ASKER
To answer questions, I'm using a 32 bit XP OS and 32 bit SQL Server it has 2G of memory. It has a VDI hard disk (not VHD or VMDK), dynamic size up to 1TB, IDE controller. JesterToo: I don't know what you mean by " Is the VDI made up of multiple logical extents or a single one?". The VM is on the host's RAID drive.
VirtualBox is using between 30-54% of the host's CPU. A lot considering the poor performance of the XP.
SQL Server logfile info:
Which I/O stats should I look at? Hosts or Guests? iostat on Linux shows 27% iowait with the VM running. I'll check on that stat again when I stop the VM , but I cannot right now.
JesterToo:
This VM has taken me a week to get running. I'd really like to make an effort to improve the performance sufficiently for users to run the legacy app on occasion. So far is mentioned more RAM and a faster physical drive. Is there anything else I can do?
What about changing the type of VDI?
Can I add another CPU to the VM?
Can I increase the timeout in SQL Server?
VirtualBox is using between 30-54% of the host's CPU. A lot considering the poor performance of the XP.
SQL Server logfile info:
DB Name Log Size Log Space Status
master 0.7421875 60 0
tempdb 0.7421875 60.85526 0
model 1.742188 98.65471 0
msdb 2.242188 40.94077 0
osh2011 0.9921875 50.29528 0
CPU 0.9921875 45.47244 0
SYS 0.9921875 39.51772 0
OSH 0.9921875 58.31693 0
ST 0.9921875 57.77559 0
OSH2004 0.9921875 33.90748 0
OSH2005 0.9921875 32.8248 0
OSH2006 0.9921875 35.38386 0
OSH2007 0.9921875 34.15354 0
OSH2008 0.9921875 49.31102 0
OSH2009 0.9921875 56.29921 0
OSH2010 0.9921875 34.05512 0Which I/O stats should I look at? Hosts or Guests? iostat on Linux shows 27% iowait with the VM running. I'll check on that stat again when I stop the VM , but I cannot right now.
JesterToo:
If I was in your situation I would quiesce the database and detach it. The, I'd replace XP with a newer 64-bit OS such as WIndows 7 ...Well, this setup is hosting a legacy application which included the SQL Server 2005. It has trouble running on Windows 7. If this VM exercise fails my solutions will be to fire back up the physical box it came from and use that until it dies.
This VM has taken me a week to get running. I'd really like to make an effort to improve the performance sufficiently for users to run the legacy app on occasion. So far is mentioned more RAM and a faster physical drive. Is there anything else I can do?
What about changing the type of VDI?
Can I add another CPU to the VM?
Can I increase the timeout in SQL Server?
I'm using a 32 bit XP OS and 32 bit SQL Server it has 2G of memoryThis is the old server configuration, right? How about the new one?
ASKER
That's the VM configuration as well.
ASKER
I've bumped the memory to 4G (3.5G used), and 2 CPUs. No difference. It takes exactly as long as before to run a query.
Are there any other ideas? It seem incredible that a 4 cpu i7 running 25 times faster than the original XP CPU runs the VM slower than the original hardware.
Are there any other ideas? It seem incredible that a 4 cpu i7 running 25 times faster than the original XP CPU runs the VM slower than the original hardware.
ASKER
I've followed the advice here and have followed similar advice on the virtualbox forum. With the additional memory and 2 CPUs the apparent GUI response is much faster. I perceive desktop manipulations and program launches to be even faster than it was on the original source hardware. Yet SQL Server response is unchanged in terms of time to run a query and continued timeouts with the Access/VBA app. The virtualbox forum people believe this is not a VM issue at all. Rather that I should search for issues related to SQL Server 2005 slowness. For the moment I think that seems right. Therefore, I'm closing this question with many thanks, and posting a new one related specifically to SQL Server 2005, XP and slowness: https://www.experts-exchange.com/questions/29020288/SQL-Server-2005-running-VERY-slowly-on-new-hardware.html
Did you check that Sql Server uses all available RAM?
Also, will be good to check indexes and try manual queries to the DB. Possibly the problem not Sql Server but in some resources requested by the App which uses the Sql Server.
Some time ago I faced with a problem when my app connected to old sql server and I found it accidentally.
Also, will be good to check indexes and try manual queries to the DB. Possibly the problem not Sql Server but in some resources requested by the App which uses the Sql Server.
Some time ago I faced with a problem when my app connected to old sql server and I found it accidentally.
ASKER
Alexander A: thanks for your follow-up. I don't know how to check if SQL Server is using all available RAM. Can you advise? Manual queries using SQL Server Management Studio or remote Java app do work. However, an initial query with either of those interfaces will take 44-50 seconds. Subsequent runnings of the same query return immediately. 44-50 seconds is too long for Access/VBA. If I could increase the Access overall timeout to 1 minute, I might get around the whole problem because subsequent queries might run faster, but I don't know how to do that either. I'm researching this.
This is in Server Properties of Sql Server. Check memory settings.
The first query could take time, especially if it quite heavy, because Sql Server loads required data into memory.
You can schedule "test" query (similar to real one) right after Sql Server startup. Possible it will speed up next queries.
The first query could take time, especially if it quite heavy, because Sql Server loads required data into memory.
You can schedule "test" query (similar to real one) right after Sql Server startup. Possible it will speed up next queries.
ASKER
Server properties > Memory, says 2,147,483,647 MB, which seems wrong because that number by itself with no multiplier is 2.15G. Can I just set it to 0 to get as much as possible? The Help text doesn't say.
No. Only value
https://books.google.ru/books?id=zBIngL29O8cC&pg=PA359&lpg=PA359&dq=sql+server+2005+32+bit+use+more+than+2gb&source=bl&ots=dUyhbZtyD3&sig=h9Cxc5miDO94MPuSF3njXbBodqY&hl=ru&sa=X&ved=0ahUKEwiGxtDErNXTAhWDbZoKHY-_BicQ6AEIMzAH
You can try to set more memory for Sql Server 2005 to use.
AWE / 3GB options
You can try to set more memory for Sql Server 2005 to use.
AWE / 3GB options
How come you closed this question by accepting an answer that didn't help you?
You even confirmed that didn't help:
In a 32bit system, SQL Server can't use more that 2GB of memory. The trick is to enable AWE so it can allocate till 8GB RAM.
For that, connect to SQL Server instance and open a new query window and run the following code:
You even confirmed that didn't help:
Yet SQL Server response is unchanged in terms of time to run a query and continued timeouts with the Access/VBA app.
In a 32bit system, SQL Server can't use more that 2GB of memory. The trick is to enable AWE so it can allocate till 8GB RAM.
For that, connect to SQL Server instance and open a new query window and run the following code:
sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
GO
sp_configure 'awe enabled', 1
RECONFIGURE WITH OVERRIDEASKER
I will check out the AWE thing, Thanks
Vitor Montalvão: I closed this question because I determined the problem was not Virtual Machine related and had to do with issues at the XP/SQLServer level. I don't believe there's anything more that can be done with the VM. Further discussing SQL server topics in this thread seemed off-topic, which is why I close this one and opened a new thread: https://www.experts-exchange.com/questions/29020288/SQL-Server-2005-running-VERY-slowly-on-new-hardware.html
Vitor Montalvão: I closed this question because I determined the problem was not Virtual Machine related and had to do with issues at the XP/SQLServer level. I don't believe there's anything more that can be done with the VM. Further discussing SQL server topics in this thread seemed off-topic, which is why I close this one and opened a new thread: https://www.experts-exchange.com/questions/29020288/SQL-Server-2005-running-VERY-slowly-on-new-hardware.html
Your question is about running slow in a VM. Adding more RAM didn't help it nevertheless you choose to mark a comment with that suggestion as solution.
Try to check the AWE configuration and let me know if it helped.
Cheers
Try to check the AWE configuration and let me know if it helped.
Cheers
Can you tell us if you're using a 32bit or 64bit version of Windows and SQL Server?