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

  • 3
    Have you check the power setting on the server ? it should be high performance. Also, check Trace Flag - 2235 --> KB2413549 - Using large amounts of memory can result in an inefficient plan in SQL Server. Also, are you testing on the same about of data / indexes, etc ? – Kin Shah Oct 12 '15 at 2:23
  • Thanks for that. I'll check both of those suggestions as soon as I can. It looks like that's what we will need to do. – Joshua F Oct 12 '15 at 5:05
  • Is there any maintenance (index, etc.) running on the client site during this window? Look at the execution plans for both and make sure they are the same. – Jonathan Fite Oct 19 '15 at 20:09
  • The processes are over night and no one is on the system at that time so that it can update without any client issues. We are currently looking into the solution that @kin has suggested but we don't have any servers that we can test it on yet. We only have live databases which we are reluctant to test on but when an opportunity arises we'll get on to it. – Joshua F Oct 20 '15 at 0:01
  • I'd say, besides looking into the hardware, you might wanna check PerfMon and its counters, like e.g. for your logical disk, memory. Further DMVs like wait stats and query plans should be analyzed to review where the execution time remains. Only than you can eliminate resources not to be the problem and subsequently figuring out the ones that are causing the stalled execution. – Dennis Winter Oct 20 '15 at 21:13
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.

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?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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