Click here to Skip to main content
12,887,625 members (32,286 online)
Click here to Skip to main content
Add your own
alternative version

Stats

5.4K views
3 bookmarked
Posted 17 Sep 2012

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

, 17 Sep 2012 CPOL
Rate this:
Please Sign up or sign in to vote.
Smarter Way of Query Load Testing at Testing Server

Introduction

The most important decision by a Database Administrator is a query on development server, where only hundreds or thousands of rows exist, can also perform flawlessly according to given benchmarks when we will deploy the same query on the production server, where the number of rows could be in millions.

One solution is to insert millions of rows in the testing environment and then check the 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 in which the base table contains only dozen of rows, but can act like they have millions 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 the above query) contains, check with the 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 the first query, you can find that in the execution plan, SQL Server Optimizer took number of rows estimate from its table statistics and it's showing correct estimated and actual number of rows.

Now we will deceive SQL Server Optimizer for number of rows of 'Production.Product' table. Simply use the 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 decision like applying partition scheme.

To restore actual number of rows, just rebuild all indexes on 'Production.Product' table.

ALTER INDEX ALLON Production.ProductDescription REBUILD 

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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

aasim abdullah
Team Leader CureMD
Pakistan Pakistan
Aasim Abdullah is working as SQL Server DBA with CureMD (www.curemd.com) based in NY, USA. He has been working with SQL Server since 2007 (Version 2005) and has used it in many projects as a developer, administrator, database designer. Aasim's primary interest is SQL Server performance tuning. If he finds the time, he like to sketch faces with graphite pencils.

You may also be interested in...

Pro

Comments and Discussions

 
-- There are no messages in this forum --
Permalink | Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.170424.1 | Last Updated 18 Sep 2012
Article Copyright 2012 by aasim abdullah
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid