CODEWITHSUNDEEP
sql-server-2014cardinality-estimation
James Anderson
James Anderson

Reputation: 21

Testing SQL Server 2014 new Cardinality estimator

I've upgraded a Pre-Prod server from SQL Server 2012 to SQL server 2014 Sp1.

It has been largely good however, In our brief manual testing we have found some issues where the new cardinality estimator has made significant decreases in performance on a handful of queries. (i.e. running the same query with database compatibility mode of 110 or (QUERYTRACEON 9481) and the same query is lightning fast).

Is there a (good) way to grab queries from our high transaction production servers and test them en masse on our Pre-Prod servers to see how large the problem is and which queries will be affected so we can mitigate them?

Thanks, James

Upvotes: 2

Views: 69

Answers (1)

steoleary
steoleary

Reputation: 9278

So there are 2 ways, you can do it by capturing a trace from SQL profiler and replaying that against your upgraded environment:

Replay a Trace File

This gets you an idea of individual queries that may perform poorly, but will not simulate the full load of a high transaction environment, to do this, you can use the little used feature of SQL server called Distributed Replay, to replay traces from multiple locations which will allow you to assess concurrency issues that you may run into with slower queries etc:

SQL Server Distributed Replay

Upvotes: 0

Related Questions