It is a common misconception that you need real production data, or production like data, to effectively tune queries in SQL Server. I am going to explain how you can compile the same execution plans as what your production environment would compile, so that you can tune them in a non-production environment, gaining these benefits.
- Keep sensitive data in production, such as personally identifiable information (PII).
- Save space by not duplicating any of the production data in lower environments.
- Save server resources by keeping development machines small.
- Speed up development machine provisioning by restoring smaller databases.
Methods for tuning the execution plans is out of scope for this post. This post covers the configuration of your non-production server and cloning of your production databases.