Sql-server – SQL Server query on server taking longer than laptop

performancesql server

Our program uses an SQL server database as its back-end (SQL server 2008-r2 – 2014). The program is OK for performance but the Database seems to slow down a bit too much.

As part of the nightly process we need to run quite a lot of queries which currently is finishing too late. (Start at 12:00 midnight end at 9:00 am. People start work then and need the database) This is OK for now but we are still setting up the database and more batch jobs need to be setup.

For example with the same database on my laptop and the server one of the queries that we need to improve takes 2 minutes on my work laptop and 3 hours on our clients server. The clients server is new and has been built to our specs that we gave them(Specs below).(Server is dedicated to our SQL instance.)

So the server is taking 9000% times longer to run the query than my work laptop.
This is not just for the one query or one client server. This is happening with most of our client servers and their queries.

I understand it should be faster on my laptop but I'm thinking not by that much.
So my question is: What on our clients server environments can be making that type of difference? and What can I do about it?

Specs

OS Version: Windows Enterprise Ed

Memory 96 GB

Processor: 2 * 8 Core HyperThreaded CPU's

Controller 1

Disk subsystem 1: Raid 1 for OS – 2 * 300GB 10K HDD

Disk subsystem 2: Raid 10 for SQL Server Data – 8 * 300GB 15K HDD

Disk subsystem 3: Raid 10 for SQL Server TempDB – 4 * Enterprise grade SDD 256GB

Controller 2

Disk subsystem 4: Raid 10 for SQL Server Logs 8 * 146GB 15K HDD

Disk subsystem 5: Raid 10 for SQL Server Non-clustered Indices – 4 * Enterprise grade SDD 512GB

Best Answer

We had an issue with a select statement using like '%findthis%' was causing slowness issues. One server it'd run fast whereas the other server would be quite slow. We were able to remove one of the % signs to '%findthis' allowing the optimizer to correctly interpret the query--on both servers yielding similar performance results. (%findthis% is simply bad form, but it wasn't our code to change unfortunately).

Long story short, make sure your SQL Server versions are identical. For example, run select @@version and look at the numeric sequences like 10.50.6000.34.

Our versions were almost identical but our test server was running faster than production because it was running a trace flag--and a minor patch. The next patch for SQL Server would have included the flag so I'm told.

Run DBCC TRACESTATUS(-1) on both servers to make sure they're running the same trace flags as well. I forget which trace flag was running in test that made it faster, but the newest patch to production SQL would have enabled it. It's been a while and most people are out for the holidays, or I'd have more specifics on which flag and which patch.

Related Question