Search results matching tags 'performance' and 'Disk I/O'

Performance impact: Logical scan fragmentation -- 100% may not be as bad as 85%

Linchi Shea — Mon, 22 Jun 2009 16:02:00 GMT

In this previous post I asked whether 100% logical scan fragmentation is always worse than 85% local scan fragmentation for table/index scans. (To be precise, I was talking about a B-tree table, i.e. a table with a clustered index).

The answer is no. 100% logical scan fragmentation is not always worse than 85% logical scan fragmentation in terms of table or clustered index scans. And the following is an example.

Two T-SQL scripts were used to populate a test table in separate tests. The key differences between these two scripts are highlighted in the code segments below in bold red.

Script I – populating data with 85% logical scan fragmentation

Script I below populates the test table with 2,000,000 rows. The clustered index key column is assigned values that alternate between small and large with the intention to fragment the clustered index.

create table test(c1 int, c2 int, c3 char(1000) default ' ')

create clustered index cix_test on test(c2)

set nocount on

declare @i int

set @i = 1

begin tran

while @i <= 2000000

begin

insert test(c1, c2)

select @i,

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

if @i % 100000 = 0

begin

commit tran

begin tran

end

set @i = @i + 1

end

commit tran

At the completion of Script I, the test table can be summarized as follows:

Property	Value
Logical scan fragmentation	~85%
Average page density	~54%
Pages scanned	466,668
Extents scanned	300,131
Rows	2,000,000
Reserved space	~3.6GB

Script II – populating data with 100% logical scan fragmentation

Script II below populates the test table with 3,000,000 rows. That’s right, 3,000,000 instead of 2,000,000. The clustered index key column is assigned values in the order exactly opposite of that for the clustered index.

create table test(c1 int, c2 int, c3 char(1000) default ' ')

create clustered index cix_test on test(c2)

set nocount on

declare @i int

set @i = 1

begin tran

while @i <= 3000000

begin

insert test(c1, c2)

select @i,

3000000 - @i; -- from high to low

if @i % 100000 = 0

begin

commit tran

begin tran

end

set @i = @i + 1

end

commit tran

At the completion of Script II, the test table can be summarized as follows:

Property	Value
Logical scan fragmentation	100%
Average page density	~88%
Pages scanned	428,572
Extents scanned	53,772
Rows	3,000,000
Reserved space	~3.3GB

Script III – the test query

At the completion of Script I and Script II, the following test script is run:

dbcc dropcleanbuffers

select COUNT(*) from test;

The test results

And the elapsed time of the SELECT statement is timed. The following table shows the performance of the test Script III:

Data	*SELECT COUNT() Performance (sec)**
Script I (85% fragmentation)	100
Script II (100% fragmentation)	27

The following chart shows the same data in the above table:

Observations

Note that since Script II populates the test in a more compact fashion (avg. page density = ~88% as opposed ~54% for Script I) and there would be fewer pages if the same number of rows are entered by both scripts. So, to make the comparison meaningful, I increased the number of rows for Script II to 3,000,000, and this resulted in the test table to have similar number of pages and similar sizes.

Why does the table scan performed significantly faster with the data that was 100% fragmented than with the data that was 85% fragmented? In fact, the query performed four times faster with the data that was 100% fragmented.

The key reason is that even though the data was 100% fragmented in terms of DBCC SHOWCONTIG or sys.dm_db_index_physical_stats, the pages were still considered ‘contiguous’, and SQL Server was able to post large reads and drove the I/O subsystem with significantly higher read-ahead reads.

More specifically, with the data populated by Script II (logical scan fragmentation = 100%), SQL Server was able to process the test query with significantly larger reads (~360KB/read vs. ~14KB ~37KB/read for the Script I data) and with significantly higher readahead reads (11,000~12,000 readahead pages/sec vs. 3,600~6,000 readahead pages/sec for the Script I data).

So, higher logical scan fragmentation does not necessarily mean worse table/index scan performance.

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

Linchi Shea — Mon, 08 Jun 2009 01:31:00 GMT

All the previously posted results (May 25^th and May 29^th) on this exercise were obtained with query parallelism disabled (i.e. the sp_configure ‘max degree of parallelism’ option was set to 1).

Since the following test query is sensitive to query parallelism, we need to see what impact query parallelism may have.

DBCC DROPCLEANBUFFERS

SELECT COUNT(*) FROM dbo.test;

It turns out that with parallelism the test query exhibited significantly different performance characteristics with the three data sets we have been using as test vehicles than without parallelism. And again, the nature of the storage system proved to be a significant confounding factor. The following table shows the results with query parallelism on both the internal drive (the C: drive) and the drive presented from a departmental level disk array (the E: drive), the same two drives used in the May 25^th update and the May 29^th update.

Drive	Test Run	Elapsed Time (second)
Internal (C:)	Adam’s script – test run 1	90
	Adam’s script – test run 2	88
	Adam’s script – test run 3	87
	Tibor’s script – test run 1	217
	Tibor’s script – test run 2	219
	Tibor’s script – test run 3	226
	Linchi’s script – test run 1	325
	Linchi’s script – test run 2	324
	Linchi’s script – test run 3	324
Disk array (E:)	Adam’s script – test run 1	74
	Adam’s script – test run 2	75
	Adam’s script – test run 3	72
	Tibor’s script – test run 1	93
	Tibor’s script – test run 2	97
	Tibor’s script – test run 3	89
	Linchi’s script – test run 1	51
	Linchi’s script – test run 2	51
	Linchi’s script – test run 3	51

Note the dramatic change in the relative impact of the data sets on the test query when the storage performance and query parallelism were added to the mix. In particular, note how the test query was able to perform much faster on the faster drive (drive E:) with some data sets.

I just want to present the data points in this post, and will dive in a bit in the next follow up.

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

Linchi Shea — Fri, 29 May 2009 16:23:00 GMT

This is another follow-up on the T-SQL exercise.

So the test query below is rather simple:

DBCC DROPCLEANBUFFERS

SELECT COUNT(*) FROM dbo.test;

But beneath its simple appearance, many factors are at play and interact in a complex way to influence the query performance. In other words, trying to predict its performance is not trivial. You may do better than predicting the stock market performance. But still it can be a rather unreliable business.

So in the May 25^th update, I reported on the results of running the test queries with the data file on a RAID-1 set with two internal disks. Yesterday, I triple checked the test result and the result could be re-produced at will.

To see whether or what impact a different storage system may have on the performance, I placed the data file on a departmental disk array behind a SAN. Unlike a typical enterprise class disk array, this departmental disk array does not have a huge amount of cache. In addition, the data block placement is effectively virtualized in that it uses a mapping table to place data across all the drives, making it effectively impossible to know or control where the data is placed at the OS level.

With the data file on this disk array, I repeated the same tests. Each time before the data was loaded, the test table was dropped and the database was empty with no user data.

Again, 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. Additional validation test runs were performed to ensure that the test results were re-producible, and they are re-producible.

The following table shows the recorded elapsed times.

Test run	Elapsed time (second)
Adam’s script – test run 1	74
Adam’s script – test run 2	82
Adam’s script – test run 3	81
Tibor’s script – test run 1	248
Tibor’s script – test run 2	235
Tibor’s script – test run 3	239
Linchi’s script – test run 1	155
Linchi’s script – test run 2	146
Linchi’s script – test run 3	148

First thing to note from the above table is that the ranking of the test query performance changed significantly. In this setup, Tibor’s data set caused the test query to have the worst performance.

But what is most striking is that while the test query performed significantly better with Adam’s and my data sets on the disk array than they did on the internal disks, the test query performed noticeably worse with Tibor’s data set on the disk array than it did on the internal disks.

This is getting more interesting. And I might add that this is yet another piece of evidence on the conspiracy of the every-table-must-have-a-clustered-index crowd :-)

I’ll come back to explore what might be the cause later.

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.

SQL Server Quiz: Is 100% logical scan fragmentation always worse than 85% logical scan fragmentation?

Linchi Shea — Fri, 22 May 2009 20:43:00 GMT

Logical scan fragmentation as reported by DBCC SHOWCONTIG is bad for queries that perform large scans such as table/index scans. Often, we hear that the higher the logical scan fragmentation percentage is, the worse it is for the scans.

So the question is: is 100% logical scan fragmentation always worse than 85% (or 80% or 90%) logical scan fragmentation for table/index scans?

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.

Disk I/O: Microsoft SQL Server on SAN Best Practices from SQL CAT's Mike Ruthruff (& Prem Mehra)

aspiringgeek — Sun, 01 Mar 2009 18:44:00 GMT

While at the PASS Community Summit in November 2008, I had the pleasure of attending a handful of excellent presentations. One of the best was delivered by Mike Ruthruff (& not just because he shilled for my presentation on disk partition alignment later that day—though I suspect he contributed to my session being SRO).

Mike is a member of the SQL Server Customer Advisory Team (SQL CAT). He authored the deck with contributions from SQL CAT patriarch Prem Mehra.

Most of you probably know Mike because he is the primary author of the landmark white paper we all know-&-love & have read over-&-over again because we know how unbelievably valuable it is:

SQL Server Predeployment I/O Best Practices
http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/pdpliobp.mspx

Mike provided a copy of his latest-&-greatest deck for publication here:

Microsoft SQL Server on SAN Best Practices

The deck includes the following:

Characteristics of SQL Server I/O operations
Best practices
- SQL Server Design Practices
- Storage Configuration
- Common Pitfalls
Monitoring performance of SQL Server on SAN
Emerging Storage Technologies
Additional Material In Appendix Section (not covered during session)
- How to validate a configuration using I/O load generation tools
- General SQL Server I/O characteristics
- How to diagnose I/O bottlenecks
- Sample Configurations

I think you'll enjoy this presentation—one of the best, perhaps the best of its kind ever assembled. ¡Yo! Only first-rate decks on this blog. Besides which, SQL CAT does nothing but the best. Get ready to be wowed by 50 slides of geekly goodness.

Administrivia

Jimmy May, MCM
SQL CAT Sr. Program Manager
SQL Server Customer Advisory Team, Business Platform Division
317.590.8650
http://blogs.msdn.com/jimmymay
Microsoft: Change the world or go home.

Performance impact: file fragmentation and SAN – Part V

Linchi Shea — Mon, 29 Dec 2008 21:36:00 GMT

SQL Server workloads

So far, the discussions in all the previous posts (1, 2, 3, and 4) on the performance impact of file fragmentation on a drive presented from a high-end enterprise-class disk array are related to disk I/O workloads. Ultimately, you want to know how file fragmentation may impact your SQL Server workloads.

In this post, I share with you some results from running three SQL Server workloads: (1) database backups, (2) checkpoints, and (3) table scans. These SQL Server workloads were run in the same three test scenarios as used in all the previous four posts:

· Non-fragmented. The database files were created on an empty drive,

· 2MB fragments. The database files were created on a drive whose free space had been fragmented into non-contiguous chunks with each chunk being contiguous and 2MB in size,

· 128KB fragments. The database files were created on a drive whose free space had been fragmented into non-contiguous chunks with each chunk being contiguous and 128KB in size,

The same database was used in all the tests. The database was about 9.5GB in size, and the table on which table scan was performed had 10 million rows and was about 4GB in size. DBCC DROPCLEANBUFFERS was run prior to each table scan.

The following chart is a summary of the SQL Server workload test results:

Clearly, file fragmentation did not have any significant performance impact on these SQL Server workloads. Because the performance of these workloads is dependent on the disk I/O throughput, we could have predicted this from the results of our disk I/O tests as reported in the previous four posts. However, it’s still comforting to see the prediction validated with real data points.

This concludes this series of posts on the performance impact of file fragmentation on a drive that is presented from a high-end enterprise class disk array. Overall, the impact was significantly less than what we have seen on a traditional directly attached disk drive. For I/O throughput, there was little to no impact. For I/O latency (or response time), file fragmentation can cause some I/O request to take much longer to complete.

In a future post(s), I’ll explore whether the same holds true with a drive presented from a lower end disk array.

Performance impact: file fragmentation and SAN – Part IV

Linchi Shea — Mon, 22 Dec 2008 16:34:00 GMT

Lies, damned lies, and statistics!

If you have read my three previous posts (1, 2, 3), you may walk away with an impression that on a drive presented from a high-end enterprise class disk array, Windows file fragmentation does not have a significant performance impact. And I’ve given you empirical data—oh yeah, statistics—to support that impression.

But that is not the whole story! No, I didn’t lie to you. The numbers I presented were solid. It’s just that the story is not yet finished.

In these previous posts on the performance impact of file fragmentation, I presented the I/O throughput data as the evidence. The arguments were valid, especially we did see file fragmentation causing the I/O throughput to degrade in a directly attached storage. But I/O throughput is but one I/O performance metric, and it is not enough to look at the I/O throughput alone.

Let me start with an analogy. So suppose we have an eight-lane super highway going from New York City to Los Angles. As we pumping (okay, driving) more cars from NYC to LA, we take measure at a checkpoint in LA to find out how many cars are crossing that checkpoint every hour, i.e. we are measuring the throughput of the super highway. Now, instead of building the eight-lane super highway straight from NYC to LA, we have it take a detour via Boston. At that same checkpoint in LA, we again measure the throughput. Everything else being equal, we should get the same throughput.

However, for a given car, the trip from NYC to LA would take a lot longer on this detoured highway.

An I/O path is similar to a super highway. While its throughput is an important measure, how long it takes for an I/O request to complete—I/O latency or response time—is also an important measure. The question is, will file fragmentation take your I/O traffic for a detour?

Indeed, empirical test data show that when a file is severely fragmented, the maximum I/O latency of large sequential reads and writes (e.g. 256KB reads and writes) can suffer significantly. The following chart shows the impact of file fragmentation on the maximum I/O latency. The data were obtained from the same tests whose throughputs were reported in Part III of this series of posts.

Clearly, when the test file was fragmented into numerous 128KB disconnected pieces, some of the 256KB reads suffered terribly latency degradation. And if your applications happen to be issuing these I/Os, you would most likely experience a performance degradation regardless whether the I/O path can maintain the same I/O throughput.

Having some valid statistics may seem to add force to an argument, which makes it so much easier to be misleading if the whole story is not told, and technically everything is valid, and nobody is lying. By the way, this is a trick often employed by the vendors, who tend to conveniently ignore the bad news, or intentionally bury it with statistics on the good news. In my book, that would be lies, damned lies, and statistics.

Performance Impact: file fragmentation and SAN – Part III

Linchi Shea — Wed, 10 Dec 2008 16:55:00 GMT

256KB Sequential Reads

In my two previous posts (1, 2), I highlighted the fact that while file fragmentation had a huge adverse performance impact on directly attached storage (DAS), it did not have much, if any, impact on the drive presented from a high end enterprise class disk array. That observation was derived from running disk I/O tests with 1KB sequential writes.

What about other disk I/O workloads? In this post, let look at the test results from running 256KB sequential reads on the same DAS and SAN drives.

To see the behavior in even more extreme, I also ran the tests with the test file fragmented into 60,000 fragments. Each fragment was 128KB in size. So I checked three test scenarios:

The 10GB file was created on a freshly formatted empty drive, thus without any fragmentation at all.
The freshly formatted drive was first filled to the full capacity with 2MB files, and then some of these 2MB files were randomly deleted to make sufficient room for the 10GB test file. In this case, the 10GB test file was fragmented into more than 3000 non-contiguous fragments.
The freshly formatted drive was first filled to the full capacity with 128KB files, and then some of these 128KB files were randomly deleted to make sufficient room for the 10GB test file. In this case the 10GB test file was fragmented into more than 60,000 non-contiguous fragments.

The following chart shows the results of many repeated tests, applying 256KB sequential reads at various load levels.

Again and clearly, as is the case with the 1KB sequential write tests, severe file fragmentation had no impact on the disk I/O performance of 256KB sequential reads on this drive presented from a high end enterprise class fibre channel disk array.

Note that the disk array had a cache that was much larger than 10GB. You may say, that’s cheating and not a fair comparison with a directly attached storage. Well, maybe, but that’s how high end disk arrays work.

Search results matching tags 'performance' and 'Disk I/O'

Performance impact: Logical scan fragmentation -- 100% may not be as bad as 85%

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

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

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

SQL Server Quiz: Is 100% logical scan fragmentation always worse than 85% logical scan fragmentation?

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

Disk I/O: Microsoft SQL Server on SAN Best Practices from SQL CAT's Mike Ruthruff (&amp; Prem Mehra)

Performance impact: file fragmentation and SAN – Part V

Performance impact: file fragmentation and SAN – Part IV

Performance Impact: file fragmentation and SAN – Part III

Disk I/O: Microsoft SQL Server on SAN Best Practices from SQL CAT's Mike Ruthruff (& Prem Mehra)