SQL Server: Smarter Way of Query Load Testing at Testing Server

Most import decision by Database Administrator is that a query on development server, where only hundred or thousand of rows exists, can also perform flawless according to given benchmarks when we will deploy same query on production server, where number of rows could be in millions.

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,
        pd.Description
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.

SELECT OBJECT_NAME(object_id),rows FROM sys.partitions

WHERE object_id IN
(object_id('Production.ProductModel'),
object_id('Production.ProductModelProductDescriptionCulture') ,
object_id('Production.ProductDescription') ,
object_id('Production.Product'))        
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.

Now we will deceive SQL Server Optimizer for number of rows of 'Production.Product' table. Simple use following update statistics BUT with undocumented options i.e. ROWCOUNT and PAGECOUNT

 UPDATE STATISTICS Production.Product WITH ROWCOUNT = 10000000, PAGECOUNT = 1000000

Execute our first SELECT query but with “DBCC FREEPROCCACHE” and it will show a different execution plan, as SQL Server Optimizer thought now number of rows are 10000000.

Now we have a better picture, that what will be the execution plan if number of rows are increased to 10000000 and it will be helpful to place new indexes and to take deceision like applying partition scheme.

To restore, actual number of rows, just rebuild all indexes on 'Production.Product' table.
ALTER INDEX ALL ON Production.ProductDescription REBUILD

Note: Don’t use this method on production server.