Have you ever found yourself in a situation where you needed to analyze the performance of a SQL Server database, but couldn’t access the actual data due to privacy issues? It can be quite challenging to optimize queries and identify performance bottlenecks without having access to the underlying data. However, there is a clever trick that can help you recreate the same scenario as the production server, even without the actual data.

Recently, I had the opportunity to work on a performance tuning project in Dubai. We were tasked with optimizing a database, but the client was unable to provide us with any data due to privacy concerns. This posed a significant challenge as we needed the data to accurately assess the performance of the queries.

Fortunately, I learned a valuable technique from Ruben, a SQL Server expert from Solid Quality Mentors, Spain. He suggested that we could generate the table schema and create statistics accordingly. By doing so, we could mimic the production server environment on our development server, even without the actual data.

Here’s how we accomplished this:

CREATE TABLE dbo.MyTable
(
    ID INT,
    Name VARCHAR(50)
)
WITH STATS_STREAM = 0x010000000100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000