June 26, 2013 at 9:52 am
I am seeing something in Sql 2008 (Express and R2) that I do not totally understand, I am hoping someone can elaborate.
BACKGROUND: 18 months ago I was given the project of "band-aid" fixing a reconciliation application that was outgrowing Microsoft Access. The database where the main table lived continually outgrew 2GB in size, causing them to split into separate files and made things increasingly difficult.
There was quite an extensive front end in access with various queries that queried queries that dumped files to process the information this department needed to process on a weekly basis. To get around this size limitation, I re-wrote the import process so that the file they receive daily gets imported into a SQL database. Then, each user has an ODBC link to the SQL Express database, and the Access database (with a few modifications) links to this 1 large table. I knew this was a quick fix kind of thing, and that was what was expected of me at the time.
This was running on a Windows XP workstation, SQL 2008 Express with 4GB RAM.
FAST FORWARD: We recently upgraded our VMWare environment, and I am looking to move this database onto a virtual machine with more resources. Yesterday I built the VM, Windows 2008 R2 OS, 8GB RAM, 4 vCPUs. I took a restore of the SQL Express database to the 2008 R2 VM machine.
I thought I would do some quick comparisons. So the largest table in the database has 13.8M rows (16 Columns). I just did select * from mydb.dbo.bigtable.
The SQL Express, Win XP machine finished 5:18 sec and the Win2k8R2, 2008R2 SQL, 8GB RAM VM returned in 5:40 sec. I would have guessed the VM would have performed better.
Does this sound correct??
Thanks in advance for any replies
sb
June 26, 2013 at 10:45 am
I'm assuming you cleared the data cache on the Windows XP machine before you ran the select?
---------------------------------------------------------
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]
Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
June 26, 2013 at 10:54 am
Lots to look at. As mentioned, warm v cold cache can matter. Did you run it multiple times on the VM?
You restored, did you update statistics, rebuild indexes (only the latter needed in most cases) to ensure you don't have issues there or fragmentation.
More vCPUs doesn't mean more power. It should, but depends on how heavily loaded your host is. Make sure your memory isn't overallocated, and above all, you need to make sure you have good IO throughput. Lots of times the default settings with IO aren't necessarily better than a physical machine.
June 26, 2013 at 11:07 am
I hadn't cleared the cache first, but I just did by running:
checkpoint;
go
dbcc dropcleanbuffers;
go
Then ran the query again. Completed in 5:25.
I did not update statistics, I will do this and repost the results.
The hardware shouldn't be too taxed, each host has 128 GB ram, and the host this VM is currently on only has 3 other windows 2003 servers running on it. I wasn't really thinking the vCPU would help, but I expected the RAM to help a lot.
How can I go about checking the IO throughput?
Thanks
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply