Search code examples
sql-servert-sqltestingsql-server-2014

SQL query Performance testing regarding cache

The situation We are planning to migrate our entire (production)DWH to a new cluster. One of the requirements is that the new cluster is atleast as fast as the current cluster. This calls for performance testing with the current cluster serving as a baseline.

When conducting these tests we want both enviorments to be near identical in terms of behaviour.

I can already clone the user behaviour from the live production cluster and execute it on the new cluster. Leaving the cache to be tackled.

The catch Since we are going to compare this new cluster to the live production enviorment I can't simply clear the cache of both servers. Clearing the cache of the new cluster would be possible since it isn't in production yet. However I am not going to clear the cache of the live production cluster since this is still being used and will have a big impact on the performance.

I was wondering if it would be possible to clone/mimic the cache between the two clusters.

I'm also open for an entire different approach on this matter.

Solution

  • I think you are going about this the wrong way and here is why. I assume the following:

    • The new cluster's hardware is of the same vendor, quality, etc as the previous
    • The cores / CPU, RAM, etc is as good or better on the new instance
    • The instance is of the same version, or an upgraded version of SQL Server. Note, upgrading doesn't mean the queries will perform better in all cases
    • The storage is the same, or better (SAN / NAS configurations)
    • The server settings are the same (MAXDOP, etc)

    If these aren't true, then I don't see why you are conducting the test anyway since it wouldn't be comparing apples to apples. With that being said, I still don't see how the tests would be equal even if you could mirror the plan cache. You could create a brand new query that would be used for performance testing, and run it on both instances to compare their performance (it would use a new plan) but here's a big catch... you aren't going to kick off all the users from production instance, so your baseline query will contend for resources. Unless you have an identical mirror of your production server, which no users are using, I don't see how you're going to get an unbiased test.

    With all that being said, most often you are upgrading to faster, better hardware so one could feel safe that it would be faster, or at least not slower, assuming equal configurations. Additionally, there are tons of performance tuning blogs out there from Dave Pinal, Paul White, Brent Ozar, Paul Randall, Aaron Bertrand, etc... ranging from optimal server settings to query tuning. This alone, could be a night and day difference in performance along with proper DB maintenance (fixing index fragmentation, fixing queries with hundreds or thousands of plans which only get used once, fixing indexing in general, etc)