-1

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.

EDIT 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) enter image description here

Setup 2 (VM live SQL environment) enter image description here

The queries execute as followed:

Setup 1 enter image description here

Setup 2 enter image description here

The data consists of a VFP free table directory with the largest database file (.dbf) being about 300MB in size.

closed as off-topic by Max Vernon, RolandoMySQLDBA, Philᵀᴹ, Colin 't Hart, dezso Oct 5 '15 at 13:06

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – Max Vernon, RolandoMySQLDBA, Philᵀᴹ, Colin 't Hart, dezso
If this question can be reworded to fit the rules in the help center, please edit the question.

  • What version of SQL Server are you using? You should look at dba.stackexchange.com/questions/53726/… – Max Vernon Oct 8 '14 at 20:23
  • Max, Setup 1 has SQL Server Express 10.50.2500, Setup 2 has SQL Server Standard 10.50.1600 – campagnolo_1 Oct 8 '14 at 20:43
  • Where are the tempdb file located? If you are spilling to disk then that might be it. – Jonathan Fite Oct 8 '14 at 20:47
  • 2
    Post the actual execution plans using tool like sqlsentry's Plan Explorer + the table structure and any indexes you might have. Until then, we will be just shooting in dark - and it wont be any help to you +++ You are running 32 bit sql server with 8 GB RAM ?? How big is your database ? How is the current load on the database ? – Kin Shah Oct 8 '14 at 20:51
  • Kin, good feedback! I'm leaving for the day but will have more input tomorrow. – campagnolo_1 Oct 8 '14 at 21:01
1

It's interesting that SQL Express would run the stored procedure faster than standard edition, since Express has a 1GB limitation of memory.

Please check to see what the max memory settings are set to for Setup 2 (right-click the SQL server > Properties > Memory). I doubt it's set to anything lower than your Express setup, but it's still worth checking.

Is the stored procedure consistently taking 4 minutes each time? Or do subsequent executions cause it to take less time? Usually cached plans are better than ones that have to be generated.

Is this a new stored procedure you just created or has it always existed? Did this performance problem just start or has it been happening for a while?

You appear to be querying a linked server, so the databases on the local servers shouldn't matter. How far away is Setup 1 to the linked server vs Setup 2? You mentioned you thought "accessed across the network" might have been the problem so you moved the data over locally. What exactly did you do? Did you copy the database over to Setup 2? Did you also modify your stored procedure to no longer query the linked server?

Instead of running the entire stored procedure, try taking the 2 queries from the stored procedure and run them individually? See if the individual queries result in the same delays or if it's just one of them that's causing the problem.

Do you have direct access to the linked server? Can you try running the stored procedure (or the individual queries) on the linked server itself and see how long the queries should be taking?

There's a lot of different variables involved here, but unfortunately, I'm only able to "answer" instead of "comment". Sorry.

EDIT: Forgot to ask: what login did you use to make the Linked Connection on Setup 1 vs Setup 2? Are they the same or different? If the same, then you can ignore the rest of what I'm about to say. If they're different, then you need to find out what level access they have on the linked server. If they don't have sysadmin, db_owner, or db_ddladmin, then the linked connection won't use statistics for the remote query. For security purposes, you might not what to set any of these roles, which is understandable, but be aware of that limitation.

  • 3
    While this is a very helpful set of questions to ask to assess and triage the situation, I don't see how it answers the question at hand. Which, you' yourself state There's a lot of different variables involved here, but unfortunately, I'm only able to "answer" instead of "comment". Sorry. – billinkc Oct 5 '15 at 21:48
  • 2
    I disagree, on balance. The question is already closed, so no new answers can be posted. Anyone finding this Q & A via search will at least find some useful pointers from this answer. It is clearly too long for a comment. Overall, I feel it is better we keep this; deleting it would not improve the site. – Paul White Oct 6 '15 at 3:11

Not the answer you're looking for? Browse other questions tagged or ask your own question.