I have a problem that I can't seem to solve. I have 2 Dell T7500 Computers, Each running Windows 7 and SQL Server 2008 R2. Both have mirrored drives, 48GB RAM. Space is not a problem. Server1 has 8 cores and is 2 years old, Server2 has 12 cores and is about a year old. Ultimately, I don't think that the speed of the computers is the issue.
We have an Excel/VBA application running on both servers. The Excel apps run queries agains a SQL database. The newer, faster Server2 hangs at a particular point. It eventually finishes the routine it is running but Excel reports "Not responding" for a minute or so. I initially assumed it to be a VBA problem. However, I isolated the problem queries and moved them to Query Analyzer. Here are what I assume to be the relevant facts...
I have a database on Server1. Everything runs as normal. I backup Database1 from Server 1 and copy the .bak file to Server2 and restore. Most things seem to run fine on Server2 with the exception of two queries against the same table, T1.
T1 has 59,000 rows and is about 400MB of data (same on both servers)
When I run this query, "update T1 set [FV_FWD]= Null" it takes less than 2 seconds on Server1 but 29 seconds on Server2. I scripted the table to a new Query Analyzer window and used that to create a duplicate table, T2. I then inserted all the records from T1 into T2. So, I had two tables with 59,000 rows. If I run the same query against the duplicate table, "update T2 set [FV_FWD]= Null" it runs in under 2 seconds like it should. I realized that T2 lacked an index that T1 had so I created a matching index and it still ran in less than 2 seconds. I've decided that disk, memory or processor bottlenecks are not likely the issue since it CAN run fast against the duplicate table.
We are concerned we have some setting wrong that might have an effect of a variety of queries (they might be slower than they should be and we just don't know it.) I'm a novice at SQL so I open the floor. Does anyone know where I should look?
Thanks in advance for you help. I'm sure I've provided too much info and not enough at the same time!