THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Linchi Shea

Checking out SQL Server via empirical data points

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

All the previously posted results (May 25th and May 29th) 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

go

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 25th update and the May 29th 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.

Published Sunday, June 07, 2009 10:31 PM by Linchi Shea

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Tibor Karaszi said:

Let's start with some background on forwarding pointers: Forwarding pointers in heaps can be a mess to

August 28, 2009 6:39 AM

Leave a Comment

(required) 
(required) 
Submit

About Linchi Shea

Checking out SQL Server via empirical data points

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement