Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Sql Performance Tuning Expand / Collapse
Posted Wednesday, June 26, 2013 9:52 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, July 13, 2015 10:25 AM
Points: 35, Visits: 101
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
Post #1467753
Posted Wednesday, June 26, 2013 10:45 AM

Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Tuesday, October 11, 2016 4:41 AM
Points: 713, Visits: 3,323
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

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
Post #1467777
Posted Wednesday, June 26, 2013 10:54 AM



Group: Administrators
Last Login: Today @ 9:45 AM
Points: 34,362, Visits: 18,570
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.

Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1467781
Posted Wednesday, June 26, 2013 11:07 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, July 13, 2015 10:25 AM
Points: 35, Visits: 101
I hadn't cleared the cache first, but I just did by running:

dbcc dropcleanbuffers;

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?

Post #1467790
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse