4

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

Paul White
  • 94,921
  • 30
  • 437
  • 687
Joshua F
  • 41
  • 4

2 Answers2

1

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.

Paul White
  • 94,921
  • 30
  • 437
  • 687
Sting
  • 2,808
  • 13
  • 20
1

No i disagree. I dont believe it should be faster on your laptop given the spec you have just described.

I also dont believe a difference in version or flags should explain it either. A 9000% difference suggests something is going very very wrong.

You should expect to get at least the same or similar result on the server, or even better.

At this point there are too many variables in your post. I think you need to work through a process of elimination to pin down where issue exists.

Is it the server hardware? Is it the sql instance? Is it db specific? Is it the table/indexes/statistics? Is it the query itself? Is it anthing to do with load or external influences?

I would start by performing a simple test under the same conditions on both the server and laptop. For example a simple script which inserts a million rows of test data into a table. Run the same script on a few dbs to get a benchmark.

If you get comparable performance, then start looking at your tables. Create a maintenance script to rebuild your indexes and update stats. Run the same script on both dbs before you run your query.

When you run your query, check the execution plans in both environments. Are they the same?

Sir Swears-a-lot
  • 3,253
  • 3
  • 30
  • 48