Search results matching tags 'Storage' and 'Scripts'

T-SQL Exercise: Simplest data-loading script to produce worst query performance – May 25th Update

Linchi Shea — Tue, 26 May 2009 12:52:00 GMT

This is a quick update on the T-SQL exercise I posted a few days ago. The goal was to write a simple T-SQL script to generate and load 4,000,000 rows into a table so that the following query would produce the worst performance, i.e. take longest time to finish:

DBCC DROPCLEANBUFFERS

SELECT COUNT(*) FROM dbo.test;

The original intent was to highlight some pitfalls in data loading that may lead to bad query performance. But then I thought why take all the fun away by having too many constraints, and why not just let it loose and see how bad it can get if one is to do it intentionally.

I must say that just looking at the scripts from Denis, Adam, and Tibor, I came to realize that things were not quite what I had thought they were.

For instance, I did not anticipate that a heap table could have performed the table scan so terribly in Tibor’s script. I thought that SQL Server would apply an allocation order scan and would optimize on the physical order regardless of forward pointers. Adam’s script surprised me with a freshly created clustered index. I thought that even if you could create a clustered index to push the limit on the fillfactor, the clustered index would defragment the data enabling SQL Server to scan the table efficiently, easily outweighing whatever gains (actually performance loss) you might get from lowering the page density.

So predicting the query performance turned out to be not very precise at all. It’s better to actually test these scripts out. I ran Adam’s script, Tibor’s script, and my script in a database whose data file was placed on an internal RAID-1 set (i.e. two mirrored physical drives). The internal drive (which was also the system drive C) was used to avoid dealing with the confounding factors because of SAN cache, storage virtualization, and so on. In addition, the test database was given 45GB for data and 20GB for log, significantly larger than what would be required during the tests.

All tests were done with SQL Server 2008 Enterpries x64 Edition (build 10.0.1600) on Windows Server 2003 Enterprise x64 Edition SP2 on an old HP ProLiant DL365 G1 with four cores and 4GB of physical memory.

Adam Machanic’s script

if exists (select * from sysobjects where name = 'test')

drop table test

go
create table test (
   x int not null,
   y char(896) not null default (''),
   z char(120) not null default('')
)

insert test (x)

select r

from

(

select row_number() over (order by (select 1)) r

from master..spt_values a, master..spt_values b

) p

where r <= 4000000

create clustered index ix_x on test (x, y)
with fillfactor=51

Tibor Karaszi’s script

if exists (select * from sysobjects where name = 'test')

drop table test

go
CREATE TABLE test (

x int NOT NULL

,x2 int NOT NULL

,y char(10) NOT NULL DEFAULT ('')

,z char(10) NOT NULL DEFAULT('')

)

DECLARE @rows int = 6666666, @toKeep int = 4000000, @diff int

INSERT test (x, x2)

SELECT TOP(@rows)

ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS r

,ROW_NUMBER() OVER (ORDER BY (SELECT 1)) % 10 AS s

FROM master..spt_values a CROSS JOIN master..spt_values b

ALTER TABLE test ALTER COLUMN y char(892)

ALTER TABLE test ALTER COLUMN z char(100)

CHECKPOINT

DECLARE @rows int = 6666666, @toKeep int = 4000000, @diff int

DELETE TOP(@rows - @toKeep)
FROM test WHERE x2 IN(2, 4, 6, 8)

Note that Tibor’s script generated 3768021 rows instead of 4,000,000 rows in my tests.

Linchi Shea’s script

if exists (select * from sysobjects where name = 'test')

drop table test

go
create table test(
   c1 int,
   c2 int,
   c3 char(256) default ' ',
   c4 char(740) default ' ')

create clustered index cix_test on test(c2, c3)

set nocount on

declare @i int

set @i = 1

begin tran

while @i <= 4000000

begin

insert test(c1, c2)

select @i,

case when @i % 2 = 0 then @i else 4000000 - @i end

if @i % 100000 = 0

begin

commit tran

begin tran

end

set @i = @i + 1

end

commit tran

To ensure that when the test table was created, the database was in the same state. The test table was dropped at the beginning of each script. And at that point, the database was empty with no user data.

After the data was loaded by each script, the test query (including DBCC DROPCLEANBUFFERS) was run three times, and each time the query elapsed time was logged. The following table shows the recorded elapsed times.

Test run	Elapsed time (second)
Adam’s script – test run 1	123
Adam’s script – test run 2	121
Adam’s script – test run 3	120
Tibor’s script – test run 1	205
Tibor’s script – test run 2	207
Tibor’s script – test run 3	177
Linchi’s script – test run 1	408
Linchi’s script – test run 2	354
Linchi’s script – test run 3	358

Afterwards, for validation, the above entire process was repeated one more time. In other words, the test query was run six times for each data-loading script. The test query elapsed times were consistent with those recorded in the above table.

Now, in no way I’d claim that my script has managed to produce the worst query performance. I’m pretty sure that you can find a way to load data to produce much worse query performance.

In addition, as Adam and Tibor observed, the results may be dependent on many factors including the test system configurations. Although on a given test system the test results should be consistent, it could be misleading to compare the results across different test systems.

A case in point is that if you disable read-ahead reads entirely, the query performance would be much worse than any of the elapsed times posted above. So perhaps, your dataset can be loaded so that it induces SQL Server not to make effective use of read-ahead reads, and therefore get worse query performance.

I’m looking forward to seeing more scripts with worse query performance than the three scripts referenced in this post.

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.

Performance impact: file fragmentation and SAN - Part VI

Linchi Shea — Tue, 30 Dec 2008 21:19:00 GMT

A script to fragment a test file

Well, this post won't cover any actual test results and is not specific to SAN. But in the spirit of full disclosure, I've attached the script I used to control the fragmentation level of the test files mentioned in this series of posts. The script is written in Perl, but can be easily done in any language.

The script does not itself fragment any file. Rather, it fragments the entire disk drive into non-contiguous blocks, or fragments, of a given size. A file (such as a database file) created afterwards is then necessarily fragmented into these fragments.

It takes three parameters at the beginning of the script: (1) $DRIVE to specify which drive the test file will be placed, (2) $FRAGMENT_SIZE to specify the size of the fragments in bytes, and (3) $FREE_SIZE to specify how much free space you want to have after the script completes. So if you want to examine the behavior of a 10GB test file that is fragmented into 128KB fragments, you may want to specify $FREE_SIZE to be slightly bigger than 10GB.

The script begins by creating an empty directory tree with enough directories so that each directory will hold 200 files of the specified size. It then proceeds to create 200 files in each of these directories until the drive is completely filled up. Finally, it randomly deletes some of these files until the free space on the drive reaches the value specified by $FREE_SIZE.

Hope you find this script useful in case you'd like to check out the impact of file fragmentation yourself.

Finding Disk Partition Offsets Made Easy

Linchi Shea — Mon, 24 Nov 2008 23:21:00 GMT

There were discussions on disk misalignment on this site. See my previous post on “Performance Impact of Disk Misalignment”, and Kevin Kline’s blog on “How to Improve Application and Database Performance up to 40% in One Easy Step”

But thanks to Jimmy May's PASS 2008 presentation on the I/O performance impact of disk partition misalignment, I have been hearing a lot about this topic yet again. Even though I thought the issue has long since been settled, it turns out that there is a huge room for improvement in practice.

Now, in practice, if you need to check whether your disk drives are properly aligned, it can be a labor intensive effort if you have to resort to diskpar.exe or diskpart.exe to get the offset of each disk drive in your environment unless you only have a few servers. If you do have a large number of servers, and want to scan the disk drives on your servers for their partition alignment, you may find the attached utility useful.

In the attached zip file, you’ll find an executable—find_partition_offset.exe—that takes a server name on the command line and gives you the offsets of the partitions on a given server, and a Perl script—find_partition_offset.pl—that is a wrapper around find_partition_offset.exe. The Perl script takes the name of a text file that has a list of server names, one on each line, and loops through these servers to report the partition offsets. You can execute the Perl script as follows:

cmd>perl find_partition_offset.pl servers.txt

For your convenience, I’ve included the C# source code for find_partition_offset.exe.