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?
OS Version: Windows Enterprise Ed
Memory 96 GB
Processor: 2 * 8 Core HyperThreaded CPU's
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
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