I have a stored procedure that I've been testing on two different servers and it runs slower on the server with more memory and I can't figure out why.
Setup 1 is my virtual environment, consisting of Windows 7 x64 with SQL Server Express x32 10.50.2500 and 4GB of memory,
Setup 2 is a Virtual Machine running Server 2008 R2 x64 and SQL Server Standard x32 10.50.1600 with 8GB of memory.
Both setups have a second HD where the data resides.
The data consists of Visual FoxPro tables and has been set up as a linked server.
Setup 1 will run the SP as NETWORK SERVICE User,
Setup 2 will run as a User we specifically created with special permissions and because best practices indicate not to run SQL instances as NETWORK SERVICE.
When I run the Stored Procedure (SP) on Setup 1, the query takes just about 2 minutes to run.
When I run it on Setup 2, it takes just about 4 minutes to run.
The SP looks like this:
SELECT * FROM OPENQUERY(LinkedWS, 'SELECT soheader.sono, soheader.partno, soheader.part_desc, soheader.routeno, sotran.code, sotran.qty, rodetail.loadcenter, rodetail.cycletime, rodetail.laborgrade, soheader.mtlloc, sotran.t_date FROM (soheader INNER JOIN sotran ON soheader.sono=sotran.sono) INNER JOIN rodetail rodetail ON soheader.routeno=rodetail.routeno WHERE sotran.code="FG" AND soheader.mtlloc="MAIN"') UNION SELECT * FROM OPENQUERY(LinkedWS, 'SELECT soyheadr.sono, soyheadr.partno, soyheadr.part_desc, soyheadr.routeno, soytran.code, soytran.qty, rodetail.loadcenter, rodetail.cycletime, rodetail.laborgrade, soyheadr.mtlloc, soytran.t_date FROM (soyheadr INNER JOIN soytran ON soyheadr.sono=soytran.sono) INNER JOIN rodetail rodetail ON soyheadr.routeno=rodetail.routeno WHERE soytran.code="FG" AND soyheadr.mtlloc="MAIN"')
I'm looking for some help on how I can figure out why the SP is much slower on the (what should be) the faster system.
At first I thought it was because in Setup 2 originally the data was accessed across the network, where it took almost 4 minutes to run the SP. Once I moved the data over locally, the time went down to 2:45 minutes. But that is still a minute slower than my Setup 1.
After some good feedback I want to update the question:
I updated both Setups to SQL Server SP 3 last night. I also got the Execution plans for both which are as followed:
Setup 1 (VM test environment)
Setup 2 (VM live SQL environment)
The queries execute as followed:
The data consists of a VFP free table directory with the largest database file (.dbf) being about 300MB in size.