Hi everyone, I know it's hard to identify this type of problem without a lot of specifics, but I'm going to ask this "general" question anyway, in hopes that someone can steer me in the right direction to start tracking this down.
We have an older development server, running SQL Server 2012 Express. The disks are all typical magnetic hard drives. We also have a newer server, running SQL Server 2008 R2 Standard, with sold state drives, significantly more powerful CPUs, and plenty of memory (100GB, of which only 28GB approx is being used by SQL Server thus far).
The newer server was noticeably slower than the older one, despite the better hardware, so we chose a query (which happened to have several joins) and started to compare them. (the data on both servers is identical - the older server data was just refreshed by restoring a backup from the newer server).
The newer server typically takes twice as long (based on the output of "set statistics time on") as the older server. When I compare the output from "set statistics io on" they are dead on identical. One just takes twice as long as the other.
There is no significant load on either server. There isn't a lot of disk i/o going on or anything else I can see that would cause the newer server to be slower.
The queries we're testing with do not appear to trigger any parallelism, and the server settings for memory, cpus, parallelism, etc seem to be the same on both servers.
At this point I have no explanation why the "better" machine would take twice as long to execute a query...
If anyone has any pointers at what I should do next to track this down, I'd appreciate hearing from you.
asked Nov 10, 2016 at 04:06 PM in Default
Check one more thing: power management. Both in Windows and in the BIOS.
answered Nov 11, 2016 at 09:07 AM
Check 2 things:
1st make sure that the drives are laid out the same as on the old server, including RAID levels. Make sure that the databases are placed on the drives the same way. Yes, even with SSDD I have had it make a difference.
2nd check the controller to make sure that it is configured the same. Some set theirs to 50/50 where 50% is read and 50% is write. For an OLTP database I prefer 25/Read and 75 write while OLAP is reversed.
answered Nov 10, 2016 at 07:43 PM