Search results matching tags 'Performance', 'Testing', and 'Disk I/O'

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.

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.