T-SQL Exercise: Simplest data-loading script to produce worst query performance

Linchi Shea — Fri, 22 May 2009 19:21:00 GMT

Here is a T-SQL scripting exercise in case you have a few minutes to spare or are bored with whatever else you are doing.

Objective

The task is to write a simple T-SQL script to generate and load 4,000,000 rows into a test table. The objective is to make the following simple test query to have the worst performance in terms of elapsed time:

DBCC DROPCLEANBUFFERS

SELECT COUNT(*) FROM dbo.test;

The longer the elapsed time, the worst it is.

Constraints

Of course, there must be a number of constraints, and they are as follows:

· The test table can have any number of columns of any fixed-length data types. However, the first column should be an integer column with values from 1 through 4,000,000, inclusive.

· The sum of the all the column widths are between 1000 bytes plus or minus 20 (i.e. between 980 bytes and 1020 bytes).

· No NULL is allowed for any column.

· No undocumented features are allowed in the script.

· When the test query is run, the test table must meet the following condition:

§ The test table has 4,000,000 rows.

§ The total size of data and index must be less than 7.5GB, as measured by sp_spaceused.

§ Avg. page density is greater than 50% as reported by DBCC SHOWCONTIG().

· The test query shown above must be run as is without any change.

Why?

Well, it’ll reveal a lot of about how data is stored and how the simple test query is processed.

If you would like to share your solution, and I hope you do, please post it here in the comment. Or if you want it better formatted, I can append it to the main text of this post.

Search results matching tags 'Scripts' and 'Query Processing'

T-SQL Exercise: Simplest data-loading script to produce worst query performance