SQL Server – Faster Queries in SSMS 2008R2 Compared to SSMS 2016

I'd be inclined to point a finger at the storage of the poorly performing server.

Sounds like you're in the unfortunate position of having a storage admin telling you everything is ok when it quite possibly isn't. Only way to be sure is to prove it.

If you have the luxury of a maintenance window, run a batch of SQLIO tests against each server and compare the results. @BrentOzar has a good intro article on SQLIO which also covers some alternative tools such as Crystal Diskmark, which might be enough to prove the point in your case. Run the tests in collaboration with the storage admin, so they can witness the issues first hand. Perhaps arm yourself with How to Prove It’s a SAN Problem before hand.

If IO testing proves fruitless or impossible, throw sp_whoisactive or a similar monitoring solution at the problem. Kendra Little's Collecting Data from sp_WhoIsActive in a Table would be the best approach for a long running ETL process.

Before doing any of the above, take 1 minute to check something very very basic that crops up time and time again... the NTFS allocation unit size on each of the arrays.

Fulltext isn't going to help without refactoring to use the full text functions ( CONTAINS, FREETEXT or their table equivalents ). It also doesn't really work with leading wildcard. Hacks are available, but basically you're going to struggle to write a semantically equivalent query for fulltext. For the future consider redesigning for fulltext which has stemming ( run, runner, running ) and thesaurus ( jogger ) which could serve your searches much better than two wildcards.

SSD is unlikely to help you unless you are memory bound. Your tables (at only 500k records) are probably in-memory most of the time. Can you confirm the size of the dJobs table, and server RAM?

Enterprise Edition could help where the limitation of 64GB RAM / lesser of 4 sockets or 16 cores goes up to 8, but you're going to need a really powerful box to notice a difference. For example, the 4 really means you could have something like 4 quad-core processors totalling 16 cores, with HT enabled, you're already at 32 logical processors. The general recommended server maxdop for this type of OLTP machine would be 8 anyway. I think this unlikely to benefit because your query has more fundamental problems but you never know.

Non-clustered indexes (particularly on dJobs) are unlikely to help because the query has so many columns from this table in the SELECT and many criteria in the WHERE clause. A non-clustered would have to be so wide to cover it would be practically a duplicate of the clustered index, therefore overly expensive to maintain. As the query sorts by jobID DESC, I considered a descending index but haven't trialled this.

Partitioning, (Enterprise only) is really a great feature, but again is unlikely to help you. I did a quick investigation of partitioning on dbo.dJobs.jobJobStatus column, eg I imagine you only have a small percentage of Jobs 'active' at any one time, eg a few hundred, even a few thousand from the 500,000 records. Partition elimination would probably be cancelled out by the OR OR OR approach. Parallel scans of multiple partitions are also an Enterprise feature:

This would probably work:

SELECT TOP 20 *
FROM dJobs
    LEFT JOIN dClients on cltClientID = jobClientId
    LEFT JOIN dUsers on regUserId = jobCoordinator
    LEFT JOIN dJobStatus ON jbsID = jobJobStatus
WHERE
    (
    jobjobStatus IN ( SELECT jbsid FROM djobstatusgroupmapping WHERE jsgid = 0 )
    )
ORDER BY jobID DESC

This probably won't work:

SELECT TOP 20 *
FROM dJobs
    LEFT JOIN dClients on cltClientID = jobClientId
    LEFT JOIN dUsers on regUserId = jobCoordinator
    LEFT JOIN dJobStatus ON jbsID = jobJobStatus
WHERE
    (
    jobjobStatus IN ( SELECT jbsid FROM djobstatusgroupmapping WHERE jsgid = 0 )
    OR ( 0=0 ) OR ( 0=0 )   --<< this 'OR always true' means 'get the whole table'
    )
ORDER BY jobID DESC

This leads me into the query. The OR OR OR approach basically means 'always get the whole table'. The TOP 20 masks this design problem. The TOP also probably pushed the plan towards Nested Loops which Jon suggested was suspect. What also stands out to me about this nightmareish "scan all columns" constructed query is that you bascially have two copies of the same query (and therefore tables), one to count, one for the resultset. This might be more efficient if the data went into an intermediate table and the count was done from there for example.

Finally, this brings me to the only only thing that would actually help you (without a large-scale refactor of the code): data deletion or archiving. As mentioned, I imagine you only have a small percentage of Jobs 'active' at any one time. Carve off the 'inactive' ones into a different table. Create a view over the top of the two tables for reporting. Set up a nightly job to copy out the old records.

Having only a few thousand active jobs in your main table will most likely transform your query performance.

Some recommended reading:

Erland Sommarskog's article on these "search all columns" queries Dynamic Search Conditions in T-SQL http://www.sommarskog.se/dyn-search-2008.html

Querying Multiple Columns (Full-Text Search) http://technet.microsoft.com/en-us/library/ms142488(v=sql.105).aspx

I hope that helps!