How to Perform a SQL Server Query Tune on a Table Without Rows

Are you dealing with performance issues in a production environment? Sometimes we can found queries called behind a DMZ or PCI Environment.

Tuning these queries is not always a straightforward process.
I mean this because the SQL Server instance behind a DMZ or PCI environment in many scenarios can difficult to tune; it’s not easy to establish a connection from an uncontrolled environment.
Also, it’s not a good idea to open back-doors to the developers or DBA’s when we can get issues related to sensitive data like GDRP, HIPPA, PCI, etc.

What options do we have?

Let’s assume the following scenario:

We have a SQL Server instance behind a PCI environment, where we have sensitive financial data that only can be accessed from a limited list of servers.
We cannot install performance tools in these server boxes.
Per PCI compliance, we cannot restore the database on a development environment using the same data we had within the controlled environment.

1. Tune the query

SELECT 
TOP 10 * 
FROM sys.dm_exec_request