Search results matching tags 'SQL Server 2008' and 'Testing'

Spit it out already!

BuckWoody — Wed, 06 Jan 2010 14:11:11 GMT

You’ve probably seen that commercial where the chewing-gum company van stalks the guy who has been chewing the same piece of gum too long, and they attack him and make him chew another piece.

I feel like that with SQL Server 2000. Almost every shop I go into has at least one primary application running on SQL Server 2000. Now, don’t get me wrong – SQL Server 2000 is a fine piece of software engineering. From over TEN YEARS AGO. In “software time”, that’s like a thousand years or something.

While it was great for its day, the newer versions are faster, more secure, and more robust. And every time it doesn’t get upgraded, SQL Server is perceived as “not as fast/strong/etc” as other platforms (which are upgraded, of course).

Now, I’m not suggesting that anyone upgrade for upgrade’s sake. We all have work to do, and the last thing we need to do is change out a platform when there’s no need.

But there is a need. SQL Server 2000 isn’t in mainline support any more. That means it can be attacked easier and so on. And it doesn’t scale like the new offerings, nor does it have any of the new features the latest versions have.

“Oh”, you might say, “I don’t use those features anyway.” Well of course you don’t – you can’t if you still have SQL Server 2000! How do you know the ways you could help your organization if you don’t experiment with the new stuff?

But it isn’t the DBA I would chase down and steal gum from. It’s the vendors.

Every time I raise my eyebrows when I hear about the SQL Server 2000 installs, the DBA shrugs and says “The vendor won’t certify SQL Server X, so we have to stay at SQL Server 2000 or 2005.” And I say, that’s just lazy. Unless the vendor codes specifically for deprecated features, a simple test run during their software development should allow them to move forward. I’m not saying that’s an easy task, but certainly they’ve tested their software releases once in the last ten years, no? If not, doesn’t that make you nervous?

Anyhoo, spit out the SQL Server 2000. Or I might have to fire up the company van.

After the Upgrade, it runs differently…

BuckWoody — Tue, 01 Dec 2009 16:06:55 GMT

I got a question yesterday in the mail that I thought I would just answer here in a broad context. While I can’t troubleshoot or do performance tuning from a distance, there are some interesting concepts and suggestions this e-mail brings up:

“I have recently seen a change from SQL Server from 2005 to 2008 in where it handles CASE statements differently. Previously we saw a tremendous improvement in performance by using CASE statements instead of OR statements. However when one of our client upgraded to 2008 they began to notice unusually long runtimes with a few of these queries (orders of magnitude larger runtimes). Swapping it to an OR statement allows it to run in 0.075 seconds... so my question to you is do you know of any changes to the exectution engine that would account for this and what is your recommendation?”

It’s a great question. Basically it boils down to “I changed versions, now something acts differently.” Before we talk about what might be the issue, let’s talk about some things you should do after you upgrade from one version of SQL Server to another.

First, open the database properties and change the compatibility level to 10.0, unless you know you shouldn’t. Next, update your statistics – all of them. Third, ensure you have all of the proper service packs applied to the operating system and SQL Server. And finally, check the code you have for deprecated statements, or for places where you could optimize the code to use new statements or formats. There are other steps to follow, but these basics will help.

Now, with all of that done, let’s move on to things that work differently. First, you need to find out what the code is doing – and the primary way to do that is to examine the Query Execution Plan. There are a lot of resources to teach you how to do that, but the general idea is that you turn that plan on (in the Query menu), run the query, look at the graphical plan and check three items: the overall plan, the icons that show the highest percentage of use, and the thickest arrows. Evaluating this on the “before” system and the “after” system, and that will show you what changed.

Maybe.

The point is, the query might take exactly the same path, but a different component may show stress because you might have a different box or configuration. Perhaps the drive layouts changed (or should), you have more memory (or better access to it) and so on. In that case, you simply follow standard performance tuning methodologies to locate what’s waiting, and what is showing pressure.

Now to the question at hand – does SQL Server (any version) handle a CASE statement differently than an OR statement? Well, once again, the execution plan will show you that answer, but the CASE statement is used for a different purpose than an OR statement – without having all of the code it’s difficult to say which to use in a given situation. The best thing to do is to evaluate the documentation on each and decide which fits the situation best.

CASE: http://msdn.microsoft.com/en-us/library/ms181765.aspx

OR: http://msdn.microsoft.com/en-us/library/ms188361.aspx

Did we make changes to CASE or OR? Not directly – but each change to the product may have orthogonal implications, which is where I point you back to the steps I mentioned for the “after upgrade” process.

Performance impact: thread mode vs. fiber mode

Linchi Shea — Mon, 04 May 2009 19:56:00 GMT

SQL Server can run in one of two modes: thread mode or fiber mode. By default, SQL Server runs in thread mode in which a SQL Server worker is associated with a Windows thread throughout all phases of its execution. This can be changed with the sp_configure option ‘Lightweight Pooling’. When Lightweight Pooling is turned on, SQL Server runs in fiber mode in which a SQL Server worker is associated with a user-mode Windows construct called fiber. Switching among fibers is handled in the user mode with the objective of reducing the cost of calling into the kernel for context switches.

Whenever fiber mode is discussed, Microsoft has always piled a huge load of ominous warnings, generally related to the potential stability issues that may come up with fiber mode. And because of these warnings, fiber mode is rarely, if ever, used in real world production environments. Staying away from fiber mode is of course the right thing to do because many of the ‘external SQL Server components’ that may not be fiber-mode friendly are usually essential in a real production environment.

If you run a bare minimum SQL Server instance (e.g. no XML, no mail, no linked servers), can you expect performance gain from fiber mode? Generally speaking, you may not see any performance gain. But there are cases you can see performance gains.

I was curious whether I could see any performance gain at all on a low-end server such as a four-core HP ProLiant DL365 G1 with 4GB of RAM. Note that this is a rather old and obsolete server model.

It turns out that under certain workloads, fiber mode can give you a huge performance boost. For my tests, I populated a TPC-C like database with about 9GB of data, and ran read-only workloads against the database (with the two read-only transactions in TPC-C in a 50/50 transaction mix). I configured each client to wait for 20 milliseconds before submitting the next query. The following chart shows the difference in transaction throughput at various load levels (in terms of the number of simulated concurrent users running queries against the server) between thread mode and fiber mode. The data points were obtained on a SQL Server 2008 instance (Enterprise x64 Edition and the build level was 10.0.1600).

If we look at the cases for 400 and 600 users for example, the performance gain from thread mode to fiber mode was ~ 40% (with the thread-mode transaction throughput as the baseline). This was a huge gain. I ran the tests in various random orders and repeated the tests at various random times. The results were consistent and reproducible.

I was surprised to see such a difference myself for two reasons. One is that I didn’t see much of a performance difference between thread mode and fiber mode when I ran the same workload in the same test environment without the 20ms wait time. I observed only about 5% consistent but marginal gain, switching from thread mode to fiber mode when the next query was submitted immediately after the previous one was finished. I can’t explain why the wait time made the difference.

The other reason I was surprised is that I had never seen any performance gain from enabling lightweight pooling or fiber mode before, though I had never tried this specific workload and had never tried this on SQL Server 2008.

I’m not sure if there is any practical value in this post because stability concerns will and should always trump any potential performance gain, especially when that performance gain is rather elusive. But at least it’s good to report that I have seen some real performance gain with fiber mode instead of just hearing somebody else talking about it.

Performance impact: a large number of virtual log files – Part II

Linchi Shea — Thu, 12 Feb 2009 05:40:00 GMT

In my previous post on the performance impact of having a large number of virtual log files (VLFs) in a transaction log, I showed that a large number of VLFs could be very bad for SQL Server 2008 performance. The test workloads were large batch delete, update, and insert. In other words, they were single monolithic transactions that generated a large number of transaction records. Intuitively, these large transactions would cause SQL Server to cross many VLF boundaries and incur the penalty of crossing these boundaries.

The question then became: how about workloads that submit small transactions such as common OLTP workloads?

As it turns out, a typical OLTP workload may not experience any performance degradation at all, even when the number of VLFs is into the 20,000 range.

The supporting evidence lies in the test results of a TPC-C like benchmark that I ran against the same test databases dbVLF_small and dbVLF_large, whose setup was described in the previous post. These two test databases were configured identically except that one had 16 VLFs in its transaction log and the other had 20,000 VLFs in its transaction log. The test workload used the standard TPC-C read-write transaction mix, which means that the workload was relatively write heavy.

The test environment was the same as described in the previous post. The test database was sized for 100 TPC-C warehouses, which translates into ~9GB for all the data and indexes.

But I’ll skip any details regarding the test setup and test data, and go straight to present the test results, which are highlighted in the following chart:

For this OLTP workload, the number of VLFs in the transaction log did not make any significant difference. This, I guess, is good news. Did I hear a sigh of relief?

Performance impact: a large number of virtual log files – Part I

Linchi Shea — Mon, 09 Feb 2009 17:13:00 GMT

It is generally known that having a large number of virtual log files (VLFs) in a database transaction log is undesirable. A blog post by the Microsoft support team in Stockholm showed that a large number of virtual log files could seriously lengthen the database recovery time. Tony Rogerson also reported that lots of virtual log files were bad for triggers and log backups on SQL Server 2000.

This blog post explores two questions:

Is a large number of VLFs in a transaction log still a significant performance factor in SQL Server 2008?
Does a large number of VLFs have an adversely impact on application-related operations such as INSERT, UPDATE, and DELETE?

As I’ll show in the rest of this post, the answers to both questions are yes. Let me first describe the test, and then present the test results.

The test databases

I created two databases: dbVLF_small and dbVLF_large on the same SQL Server 2008 instance (Enterprise x64 Edition and the build number was 10.0.1600). The host was an HP DL585 with four single core 2.2GHz Opteron processors and 32GB of RAM (28GB of which was allocated to the buffer pool). The following table highlights the key properties of these two test databases:

Property	Property value
	dbVLF_small	dbVLF_large
Data file size	20GB	20GB
Data file location	E: drive	E: drive
Log file size	10GB	10GB
Log file location	F: drive	F: drive
Recovery mode	Full	Full
Number of VLFs	16	20,000

As you see, the only masterial difference between these two databases was the number of virtual log files. The dbVLF_small database had 16 VLFs (a small number of VLFs), and the dbVLF_large database had 20,000 VLFs (a large number of VLFs). It’s probably a bit extreme for a database to have 20,000 virtual log files. But it’s not completely unreasonable if you let your database log file to grow in small increments. Note that my intention is to highlight the impact in case if your database does end up with a large number of VLFs.

The test table

An identical table, called customer, was created in both databases. The DDL for the customer table is as follows:

CREATE TABLE customer

(

c_id int,

c_d_id tinyint,

c_w_id int,

c_first char(16),

c_middle char(2),

c_last char(16),

c_street_1 char(20),

c_street_2 char(20),

c_city char(20),

c_state char(2),

c_zip char(9),

c_phone char(16),

c_since datetime,

c_credit char(2),

c_credit_lim numeric(12,2),

c_discount numeric(4,4),

c_balance numeric(12,2),

c_ytd_payment numeric(12,2),

c_payment_cnt smallint,

c_delivery_cnt smallint,

c_data char(500)

)

CREATE UNIQUE CLUSTERED INDEX ci_customer on customer(c_w_id, c_d_id, c_id)

This was the customer table used in the TPC-C benchmark.

The test data

3,000,000 rows—with data as specified for the TPC-C customer table— were bulk copied into the customer table in both databases. The index was then rebuilt, and sp_spaceused showed the table size to be about 2GB.

The tests

Three simple tests were run against the customer table in each database. The common characteristics of these three tests were that they were a single large transaction.

The insert test

SELECT * INTO customer_tmp
FROM customer
WHERE 1=2;
go
INSERT customer_tmp
SELECT * FROM customer;

The update test

UPDATE customer
SET c_data = lower(c_data);

The delete test

DELETE FROM customer;

The test results

The following table summarizes the test results:

Test	Elapsed time (sec) in database: dbVLF_small	Elapsed time (sec) in database: dbVLF_large
The insert test	281	1069
The update test	174	1554
The delete test	193	936

The following chart gives a better visual representation of the same results:

In all the three cases, it was significantly (should I say dramatically) slower to perform these common SQL operations when the database had 20,000 VLFs than when the database had 16 VLFs. The insert was about four times slower. The update was about eight times slower, and the delete was about five times slower.

So the test results confirm that it is definitely a significant performance issue in SQL Server 2008 to have a large number of virtual log files in a transaction log. Moreover, the test results show that the problem can be felt by common SQL operations such as insert, update, and delete in large batch processes.

What does this all mean? First, pay attention to the often-repeated best practice advice: do not grow your database log files in small increments (because that is often how you introduce a large number of VLFs into your transaction log). For instance, if you know you’ll need 10GB for your log, it’s best to allocate 10GB in one fell swoop. Secondly, if you do find a large number of VLFs in your transaction log, it may be worth the maintenance effort to reduce the number. You can reduce the number of VLFs by first shrinking the log file to a minimum size, and then allocating the required space in one ALTER DATABASE statement. (SQL Server MVP Tibor Karaszi has a thorough discussion on shrinking log files at his site)

------

By the way, the 20,000 virtual log files were generated as follows. First, create the database in the full recovery mode with a 512KB initial log file size and 512KB file growth increment. Then, backup the database, and create a dummy table with a single char(8000) column. Finally, inserting data into this table in a loop until the log file size reaches 5000MB. This is essentially the same procedure used by the Microsoft support team in Stockholm in their post.

To prepare for the insert, update, and delete tests, backup the log, and expand the log file to 10GB.