SQL Server: Smarter Way of Query Load Testing at Testing Server
One solution is to insert millions of rows in testing environment and then check execution plan. But it’s really painful.
Thanks to SQL Server, which has provided a better solution, since SQL Server 2005. Yes, you can test a query that base table contains only dozen of rows but can act like they have million of rows (or as much as you want). Let’s try with a simple query at Adventure Works.
SELECT p.ProductID, p.Name, pm.Name AS ProductModel, pmx.CultureID,
FROM Production.Product AS p
INNER JOIN Production.ProductModel AS pm
ON p.ProductModelID = pm.ProductModelID
INNER JOIN Production.ProductModelProductDescriptionCulture AS pmx
ON pm.ProductModelID = pmx.ProductModelID
INNER JOIN Production.ProductDescription AS pd
ON pmx.ProductDescriptionID =pd.ProductDescriptionID
WHERE pm.Name = ‘Road-150’
How many rows each table (in above query) contains, check with following query.
WHERE object_id IN
AND index_id = 1
On execution of first query, you can find that in execution plan, SQL Server Optimizer took number of rows estimate from its table statistics and its showing correct estimated and actual number of rows.
UPDATE STATISTICS Production.Product WITH ROWCOUNT = 10000000, PAGECOUNT = 1000000
To restore, actual number of rows, just rebuild all indexes on ‘Production.Product’ table.
ALTER INDEX ALLON Production.ProductDescription REBUILD