Search results matching tag 'Testing'

Geek City: What Triggered This Post?

Kalen Delaney — Tue, 01 Jan 2013 00:00:00 GMT

I’d really like to get another post up onto my much neglected blog before the end of 2012. This will also start one of my New Year’s resolutions, which is to write at least one blog post a month. I’m going to tell you about a change in SQL Server that wasn’t announced in any “What’s New” list that I ever saw, perhaps because it was just a chance in internal behavior, and nothing that required any change in user applications.

Do you retest what you know is true for every new version? When I update my books, I do test all the scripts, but if there isn’t a script, I don’t retest every ‘fact’ that I have known for years is true. And sometimes, things change. And sometimes my reviewers notice those unreported changes, and sometimes they don’t.

You might be aware of the fact that SQL Server can perform UPDATE operations in two different ways. The UPDATE can be performed as a two-step process: delete the old row and then insert a whole new row, or, the UPDATE can be performed (much more efficiently) as an update-in-place. When the two-step UPDATE is performed, it is a LOT more work. Not only does SQL Server have to log the entire old row and the entire new row, but each nonclustered index is also modified twice, and each of those index changes also has to be logged. So it’s nice when an update-in-place is done, because only the bytes changed are logged, and only indexes on the updated columns are affected.

Prior to SQL Server 7, there were actually four different ways that UPDATE could be done. The two-step UPDATE had some variations that could make it even slower in some cases! But that was a long time ago, so I’m not going to go into the details now. But I will say that back then, in order to get an update-in-place to occur, there was a big long list of prerequisites that had to be met and if you missed just one, you’d get one of the slower UPDATE operations.

As of SQL Server 7, update-in-place became the default. The only time it doesn’t happen is when the row can’t stay in the same location (such as when you update a clustered index key column) or when SQL Server really needs the old and new versions of the row.

In SQL 7, one of the places that SQL needed the old and new version of the updates rows was when processing triggers. Triggers need the transaction log to get the contents for the DELETED and INSERTED pseudo-tables. And because triggers needed the entire old and new versions of the updated rows, the UPDATE was performed as a two-step operation. DELETE the old row, log the entire old row, and the INSERT the new row with the new values, and log the entire new row.

But as of 2005, we now have the version store, primarily used for SNAPSHOT isolation, but available for other uses as well. In SNAPSHOT isolation, the version stores stores ‘old versions’ of rows that have been updated or deleted. I knew that the version store was also used for triggers, but it only occurred to me just recently that maybe, because the old and new versions of the row were not needed from the log, perhaps UPDATEs did not always need to be performed internally as a two-step UPDATE.

So I decided to test it out.

-- DEMO: If there is an UPDATE trigger, are updates logged as DELETE + INSERT?
-- First build a new database.

USE master;
GO
IF (SELECT db_id('TestTrigger')) IS NOT NULL
DROP DATABASE TestTrigger;
GO
CREATE DATABASE TestTrigger;
GO
ALTER DATABASE TestTrigger SET RECOVERY SIMPLE;
GO
SELECT db_id('TestTrigger');
GO

USE TestTrigger;
GO

-- Just for a warmup, look at the function fn_dblog, which works in the current database

SELECT * FROM fn_dblog(null, null);
GO

-- Create a new table to work with
IF (SELECT object_id('objects')) IS NOT NULL
DROP TABLE objects;
GO
SELECT TOP 100 * INTO objects FROM sys.objects;
GO

-- Create a clustered index on the table
CREATE CLUSTERED INDEX objects_clustered on objects(name);
GO

-- First examine an update we know is NOT done in place,
-- i.e. updating a clustered key value

UPDATE objects SET name = 'newrowsets' WHERE name = 'sysrowsets';
GO

-- Look at last 10 rows; notice a LOP_DELETE_ROWS and LOP_INSERT_ROWS
-- The AllocUniteName column shows the object affected is the clustered index on dbo.objects
SELECT Operation, [Transaction ID], AllocUnitName FROM fn_dblog(null, null);
GO

-- Now examine an update we know is done in place,
-- i.e. updating an unindexed column on a table with no triggers
UPDATE objects SET parent_object_id = 1 WHERE name = 'sysfiles1';
GO

-- Look at last 3 rows; notice a LOP_MODIFY_ROW on the dbo.objects allocation unit
SELECT Operation, [Transaction ID], AllocUnitName FROM fn_dblog(null, null);
GO

-- Create an update trigger
-- Will the update be done with the siple LOP_MODIFY_ROW or with the LOP_DELETE_ROWS and LOP_INSERT_ROWS
CREATE TRIGGER trg_update_objects ON objects FOR UPDATE
as
SELECT * FROM DELETED; SELECT * FROM INSERTED;
RETURN;
GO

-- Now perform update again
UPDATE objects SET parent_object_id = 10 WHERE name = 'sysfiles1';
GO

-- Look at last 3 rows; notice a LOP_MODIFY_ROW
SELECT * FROM fn_dblog(null, null);
GO

Since the database is in SIMPLE recovery model, you can issue a CHECKPOINT before each UPDATE if you want to reduce the number of rows in the log to make it easier to examine.

So it seems that I need to update my course and some of my writings. There might also be special cases that still require that an two-step UPDATE be performed in the presence of triggers, but it seems like a two-step UPDATE is not ALWAYS required anymore. That is very good news!

I hope you all have a wonder-filled and joyous New Year!

~Kalen

CloudSeeder: CLR Stored Procedures For Creating CPU Pressure

Adam Machanic — Tue, 23 Oct 2012 19:49:00 GMT

Sometimes, in the interest of testing various scenarios that your server might encounter, it's useful to be able to quickly simulate some condition or another. I/O, memory, CPU pressure, and so on.

This latter one is something I've been playing with a lot recently. CPU pressure in SQL Server creates all sorts of interesting side-effects, such as exacerbating waits and making various other conditions much easier to reproduce.

In order to make this simpler, I've created the attached CLR library. This is a simple pair of stored procedures:

startSeeding takes two arguments: The first parameter is the number of threads you'd like to create, and the second is a bit, indicating whether or not the threads should be affinitized. If they are, the threads will run preemptively, and the CPU pressure will be of the external sort. If they are not affinitized, the pressure will be of the internal sort, and you'll be able to see SQL Server scheduler contention. Of course you can run the procedure two or more times and create a mix of threads, if that's what you'd like to do.

stopSeeding takes no arguments. It attempts to cancel all of the threads that have been started.

"Installing" the script is easy. Create a database on a server that has CLR enabled. Make the database trustworthy to bypass CLR security restrictions. And then run the script to create the objects. The code was compiled for .NET 3.5, so this should work on either SQL Server 2008, 2008R2, and 2012.

Be careful about how many threads you create. It's amazing how quickly things can get out of hand. Start small. Trust me on this.

On that note, I make absolutely no guarantees that this won't crash your server, or worse. As a matter of fact, that's kind of the point!

Enjoy, and let me know if you have any feedback, feature requests, or whatever.

Performance impact: driving up context switches/sec

Linchi Shea — Thu, 12 Jan 2012 05:23:52 GMT

Too many context switches per second are considered bad for your database performance. But how many is too many has never been clear. With the core count of new servers going up rapidly, it becomes even less clear how we should evaluate this counter to help understand the SQL Server behavior in the environments we support. Recognizing that any attempt to rehash what is already said/recommended out there will more likely be a disservice than a service, I’d like to look at it from a different angle, and hopefully contribute to its understanding with some data points.

Personally, I subscribe to the belief that one of the best ways to understand a behavior is to be able to create and manipulate the behavior on demand. And it naturally follows to ask: how can we drive up the value of the System\Context Switches/sec counter with a SQL Server workload?

Knowing how SQL Server schedules its tasks, I’d expect to be able to drive up context switches/sec by running a lot of very small tasks.

And that is indeed the case. Here is how it goes.

I first create two stored procedures that basically does nothing on the server side. These are just null transactions. (By the way, the parameters in these proc don’t mean anything. They are there because the client program I use expect them and I’m too lazy to modify the client program. Plus, it adds absolutely no value to modify the client code.)

CREATE PROC spStockLevel    @w_id          int,
                            @d_id          tinyint,
                            @threshold     smallint
AS
     SET NOCOUNT ON
     SELECT 5
GO

CREATE PROC spOrderStatus   @w_id int,
                            @d_id tinyint,
                            @c_id   int,
                            @c_last char(16) = ''
AS
   SET NOCOUNT ON
   SELECT 1, 'Jones', 'John', 'M', '2012-01-01', 2, 21, 2
GO

Then, I simulate 200 concurrent users by starting 200 threads on a client and each thread calling these two procs within an infinite loop with no wait between the calls. The following chart shows the sustained values of the Context Switches/sec on a DL580 G7 with four E7-4870 processors when different number of cores are enabled. In all the cases, hyperthreading is enabled. And note that each E7-4870 has 10 cores.

With this approach, the value of the Context Switches/sec counter is driven into 200,000 to 250,000 per second range. These are pretty high numbers. I have no idea if they can be driven even higher with a different approach. But I know that I have not seen the counter approaching this level in any real production environment. If you have, let us know what kind values you have seen and with what kind of workload.

I should also report that this null transaction workload fails to push the total processor time very high. See the following chart:

The maximum %Processor Time (_Total) that can be reached by this workload ~24%. And this is not only the case with the different number of core count, but also is the case no matter how many concurrent users (threads) are submitting the transactions.

It is worth noting, and it is evident from the chart, that the %Privileged Time (_Total) accounts for a very large percentage of the %Processor Time (_Total). In a real production environment, this would spell trouble. With this null transaction workload, I don’t know whether this should be expected and is by design, or something is not behaving properly and the %Privileged Time should be much lower. But I do know that when the transactions are actually doing something useful (e.g. by including some non-trivial SELECT statements), we’ll see the %Privileged Time (_Total) value go down rather quickly. For instance, with the workload used in this previous post, the %Privileged Time (_Total) is typically around 1% while %Processor Time (_Total) is near 100%. And with that workload (which is doing a lot more useful work in its transactions), the Context Switches/sec counter is typically observed to be less than 49,000.

How useful are these data points? I’m not really sure. Hey, at least we know that this particular workload can drive up the Context Switches/sec counter. And if this starts a conversation, it would be a plus.

Performance impact: hyperthreading for OLTP queries -- II

Linchi Shea — Wed, 11 Jan 2012 18:51:17 GMT

This is in part a response to a comment by Paul White (@SQL_Kiwi) to my previous post on the performance impact of enabling hyperthreading (HT) on OLTP queries, and in part due to my desire to capture a more complete set of test data for future investigation on this very topic. I’m posting below the results of re-running the same exact test as described in the previous post but with the SQL Server instance bumped up to build 10.50.2500 from 10.50.1600. The former is SQL Server 2008 R2 with Service Pack 1, whereas the latter is SQL Server 2008 R2 RTM.

In addition, I have included the core count as a formal test parameter, and tested the performance impact of enabling HT at the following core counts: 16, 20, 24, 32, and 40. The core count was controlled through the BIOS on machine reboot. The exact BIOS feature for controlling the number of cores is primarily under System Options –> Processor Options –> Enhanced Processor Core Disable (Intel Core Select). This allows one to enter the number of cores to be enabled per socket.

The results are as follows:

In the previous post, enabling HT is reported to increase the throughput by 5~7.5%. In the above chart, the increase in the throughput varies between 9% and 14%. So it may appear that SQL Server 2008 R2 SP1 responds slightly better to enabling HT than does SQL Server 2008 R2 RTM. However, because there is always a margin of error in any test, I’d assign more significance to the overall trends and patterns than the exact numbers. And in both cases, the over trends and patterns are similar in that this particular OLTP workload responded positively to enabling HT on the E7-4870 processors.

Performance impact: hyperthreading for OLTP queries

Linchi Shea — Fri, 06 Jan 2012 04:44:00 GMT

My previous post focuses on the performance impact of enabling hyperthreading (HT) on a machine with four Intel Westmere-EX processors on reporting queries. Let’s turn our attention to OLTP queries.

To oversimplify it, reporting queries are generally processed by scanning a large number of pages, whereas quick index seeks are the hallmark of OLTP queries.

The OLTP queries used to check out the hyperthreading impact are the two TPC-C read-only transactions (Order Status and Stock Level), slightly modified to work properly in the test environment. The need to modify these two TPC-C transactions is discussed in an early post. Briefly, it’s because in their original forms they don’t scale properly on this 80-cpu test machine running SQL Server 2008 R2. You can find the modified code of these two transactions in the attachment of this post.

The benchmark described in the same post is used to drive the tests whose results are reported here. If you want to find out more about the benchmark, please check out that post, so I won’t duplicate its description here.

Essentially, with this OLTP benchmark (or workload) I want to see how it behaves when I increase the load level by increasing the number of concurrent users, with and without hyperthreading on a DL580 G7 with four E7-4870 processors (10 cores per processor) and 264GB of RAM. The OS is Windows 2008 R2 Enterprise x64 Edition with SP2 and DBMS is SQL Server 2008 R2 Enterprise x64 Edition (10.50.1600). There is no intra-query parallelism; maxdop is set to 1 in all the cases.

Four test configurations are examined:

40 core with HT. That is 80 logical cpus. HT is enabled in the BIOS.
40 cores without HT. That is 40 logical cpus. HT is disabled in the BIOS.
20 cores with HT. That is 40 logical cpus. HT is enabled in the BIOS.
20 cores without HT. That is 20 logical cpus. HT is disabled in the BIOS.

The following chart shows the results when 200 simulated users are driving the workload concurrently. Note that with 200 users, all the processors on this test system are pushed to ~100%.

Compared with the results for the reporting queries (see the previous post), the performance gain from enabling HT on the 40 cores is very much more marginal with this OLTP benchmark. We see an increase of about 5% from ~7900 batch requests per second to ~8300 batch requests per second. Enabling HT on the 20 cores produces an improvement of similar magnitude (about 7.5%).

Going from 20 cores with HT to 40 cores without HT, however, gives the OLTP queries a dramatic throughput gain of about 67% from ~4730 batch requests per second to ~7900 batch requests per second. We see similar behavior with the reporting queries. Knowing how HT works, I think it is intuitive we expect a big gain, whether or not it should be 67%.

So for both the reporting queries and the OLTP queries that I have tested, the hyperthreading results are positive. So far so good!

How Microsoft helps you NOT break your Windows Azure Application: Storage Services Versioning

BuckWoody — Tue, 06 Dec 2011 14:42:57 GMT

One of the advantages of using Windows Azure to run your code is that you don’t have to constantly manage upgrades on your platform. While that’s a big advantage indeed, it immediately brings up the question - how do the upgrades happen? Microsoft upgrades the Azure platform in periodic increments, and the components that are affected are documented.

This brings up another question - upgrades mean change, and change can sometimes alter the way you might implement a feature. What if you have taken a dependency on some feature in your code that has been altered by an upgrade? Windows Azure does have an Application Lifecycle Management (ALM) Process, which I’ll reference at the end of this post. But beyond that, there are some features we’ve put into place that will help you manage many of these changes. One of those is being able to set the version of storage features you would like your code to use.

Windows Azure is made up of three main component areas: Computing, Storage and a group of features called the Application Fabric. You can use these components together or separately, depending on what you would like your application to do. In this post I’ll deal with the version control in the storage subsystem - in other posts I’ll explain how to track and in some cases control the versions of the other components you work with.

When you send a request to a Windows Azure resource, you’re actually using a REST call. That’s a three-part call to the system that has a request (called a URI), a header, and a body of code you want to send. So a typical call, such as to a table, might look like this example, which changes the properties of a Blob:

URI:
PUT http://myaccount.table.core.windows.net/?restype=service&comp=properties HTTP/1.1

Header:
x-ms-version: 2011-08-18
x-ms-date: Tue, 30 Aug 2011 04:28:19 GMT
Authorization: SharedKey
myaccount:Z1lTLDwtq5o1UYQluucdsXk6/iB7YxEu0m6VofAEkUE=
Host: myaccount.table.core.windows.net

Body:
<?xml version="1.0" encoding="utf-8"?>
<StorageServiceProperties>
    <Logging>
        <Version>1.0</Version>
        <Delete>true</Delete>
        <Read>false</Read>
        <Write>true</Write>
        <RetentionPolicy>
            <Enabled>true</Enabled>
            <Days>7</Days>
        </RetentionPolicy>
    </Logging>
    <Metrics>
        <Version>1.0</Version>
        <Enabled>true</Enabled>
        <IncludeAPIs>false</IncludeAPIs>
        <RetentionPolicy>
            <Enabled>true</Enabled>
            <Days>7</Days>
        </RetentionPolicy>
    </Metrics>
</StorageServiceProperties>

(Source of this code)

You can see that I’ve highlighted a portion of the header block - that’s where you set the version of the Storage Services you would like to use. You can find a list of the features introduced in each version here. It’s not a requirement of adding that element to the header, but it’s best practices to do so.

You don’t have to use REST calls directly, however. It’s more common to use the API in the Software Development Kit to just change the property in your IDE environment - the setting you’re looking for there is the Set Storage Service Properties call.

Interestingly, rather than a breaking change you might run into an unexpected behavior if you are not aware of these parameters. In some code I recently reviewed a newer feature from the storage system failed when it was called. On inspection I found that the developer had used an older codeblock from a previous version of the storage system - he was not aware you can set the version of storage in the call. We changed the header to the latest version, and everything worked as expected.

References:

The Storage Services Versioning and the changes for each version:

http://msdn.microsoft.com/en-us/library/windowsazure/dd894041.aspx

Windows Azure Application Lifecycle Management:

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

http://channel9.msdn.com/posts/Windows-Azure-Jump-Start-03-Windows-Azure-Lifecycle-Part-1

http://channel9.msdn.com/Events/TechEd/Australia/Tech-Ed-Australia-2011/COS201

Why generalizations are dangerous

AaronBertrand — Thu, 15 Sep 2011 23:19:00 GMT

A couple of years ago, John Sansom wrote a blog post comparing the performance of two different ways to get the maximum value from a column: MAX() and TOP (1).

http://www.johnsansom.com/performance-comparison-of-select-top-1-verses-max/

In the conclusion, he states:

When a clustered index is present on the table & column that is to be selected, both the MAX() operator and the query (SELECT TOP 1 ColumnName order by ColumnName) have almost identical performance.
When there is no clustered index on the table & column to be queried, the MAX() operator offers the better performance.

Now for this specific case, that is quite true. But I find it alarming when people take his conclusion and believe it applies to every single scenario - so much so that they make policy decisions around prohibiting the use of TOP 1 in all situations (regardless of whether the ordering is on an indexed column, a non-indexed column, or even an aggregate). This isn't John's fault, of course. It's just the nature of people who believe that something they see once means it applies everywhere (SELECT without ORDER BY, anyone?).

Recently a user asked the community to help them re-write a query that was currently using TOP 1 - not because the query was slow or produced the wrong results, but because they were told they could no longer use TOP 1. When asked why, they simply pointed at John's blog post above, and were going with the theory that TOP 1 is always slower and it must be eradicated from their codebase.

Not being a big fan of generalizations, I tried to explain that John's situation was not quite the same - for one he was getting the max value from a single column, whereas the query in question actually wanted the max from an aggregate in a subquery - but they didn't seem to want to have anything to do with it. So, naturally, I set out to run some tests myself. Here is the query that was subject to refactoring - essentially they wanted the team name with the highest number of members:

SELECT t.name
FROM   team AS t
INNER JOIN (SELECT TOP 1 COUNT(*) AS membercount,
                          teamID
                   FROM   dbo.teammember
                   GROUP BY teamID
                   ORDER BY COUNT(*) DESC) AS team_with_most_members
         ON t.id = team_with_most_members.teamID;

There were two other solutions offered (well, three, but one didn't work). One was from me:

SELECT t.name FROM
(
    SELECT
        TeamID,
        rn = ROW_NUMBER() OVER (ORDER BY c DESC)
    FROM
    (
        SELECT
            TeamID,
            c = COUNT(*)
        FROM dbo.TeamMember GROUP BY TeamID
    ) AS x
) AS y
INNER JOIN dbo.Team AS t
ON y.TeamID = t.ID
WHERE y.rn = 1;

And one was from Wil:

SELECT t.name FROM team AS t 
JOIN teammember AS tm ON tm.teamID = t.ID 
GROUP BY t.Name
HAVING COUNT(tm.id) = (SELECT MAX(members)
 FROM (SELECT COUNT(id) members FROM teammember GROUP BY teamid) AS sub)

So to compare the three options, I first created some sample tables. The first was the main Team table:

CREATE TABLE dbo.Team(ID INT PRIMARY KEY CLUSTERED, Name VARCHAR(32));

Then I created two versions of the TeamMember table, one with a clustered index on the TeamID column, and one with a non-clustered index:

CREATE TABLE dbo.TeamMember_A(ID INT IDENTITY(1,1) PRIMARY KEY NONCLUSTERED,
    TeamID INT, Name VARCHAR(32));

CREATE CLUSTERED INDEX x ON dbo.TeamMember_A(TeamID);

CREATE TABLE dbo.TeamMember_B(ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
    TeamID INT, Name VARCHAR(32));


CREATE INDEX x ON dbo.TeamMember_B(TeamID);

To populate them with a reasonable volume of data, I performed some creative queries against sys.objects. First I populated a table variable with a group of random rows based on object_id, with the first column being the TeamID, and the second column being the number of rows that I would insert into the TeamMember tables:

DECLARE @src TABLE
(
    TeamID INT, TeamSize INT
);

;WITH x(o,c) AS
(
    SELECT
        [object_id] % 10000,
        CONVERT(INT, [object_id] / 15000.0) + 1
    FROM sys.objects
)
INSERT @src SELECT TOP (30) o, c
    FROM x WHERE c > 0
    ORDER BY c DESC;

This created 30 values like this (the last several rows yield c = 1):

Now that I had a set of 30 teams to create, I could use a cursor and a while loop (fun, fun) to populate the TeamMember tables. Could I have used a numbers table and done a cross join or something to avoid the loop? Sure, but this was easy enough and it is not exactly production code.

DECLARE @TeamID INT, @TeamSize INT, @i INT;

DECLARE c CURSOR LOCAL STATIC FORWARD_ONLY READ_ONLY
    FOR SELECT TeamID, TeamSize
    FROM @src;

OPEN c;

FETCH NEXT FROM c INTO @TeamID, @TeamSize;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @i = 1;

    WHILE @i <= @TeamSize
    BEGIN
        INSERT dbo.TeamMember_A(TeamID, Name)
            SELECT @TeamID, CONVERT(VARCHAR(32), @TeamID);

        INSERT dbo.TeamMember_B(TeamID, Name)
            SELECT @TeamID, CONVERT(VARCHAR(32), @TeamID);

        SET @i += 1;
    END

    FETCH NEXT FROM c INTO @TeamID, @TeamSize;
END

CLOSE c;
DEALLOCATE c;

So a quick count showed we had 30 rows in the Team table, and about 1.3 million rows in the TeamMember tables (with individual teams ranging from one row to 140,000 rows).

Now we could put our three queries to the test! I opened up my trusty copy of SQL Sentry Plan Explorer and pasted the three queries into the Command Text pane. I clicked the "Actual Plan" button, which goes to the server, runs the query, retrieves the actual plan, and discards the results. First I ran it for the A table (clustered index on TeamID), and these were the results from the Statements Tree tab:

Similar results for the B table (the one with just a non-clustered index on the TeamID column):

In both cases, Wil's solution came out at about twice the cost as the original query (and my proposed replacement). You can see where the doubling comes in from Wil's graphical plan (an extra scan), compared to the other two (the screen shots are presented in the same order as the 6 rows shown in the Statements Tree rows above - click on any to embiggen):

Original - TeamMember_A

Aaron - TeamMember_A

Wil - TeamMember_A

Original - TeamMember_B

Aaron - TeamMember_B

Wil - TeamMember_B

I tried running the tests again with parallelism off, and while the plans looked a little different, the overall results were nearly identical (~50/25/25 split).

Conclusion

Generalizations are dangerous. Even if a situation looks similar to the one you've got, you owe it to yourself to test. Especially in a case where you've been asked to change your code to improve performance, and you've actually accepted a solution that performs worse.

Plan for Diagnostics in Cloud Computing From the Git-Go

BuckWoody — Tue, 06 Sep 2011 13:11:22 GMT

“Git-Go” is something we say in the South that means “right at the start”. I’ve seen several applications for on-premise systems that don’t have much in the way of diagnostics - the developers rely on a debugger, the event logs on the server and client workstation, and most of all, the ability to watch the system from end-to-end.

This approach is a mistake for an on-premise system, and it’s definitely a problem for a distributed architecture. You simply do not own all of the components from end to end in a cloud environment, nor are you always able to attach a debugger or other remote monitoring tools to the various areas within the code path. So you need to make sure that from the very outset of your design that you build in diagnostics. My personal preference is to build a system such that a control file turns on deeper information gathering from the system, up to a minimal level.

When I do that, I set a high level of logging, a medium level, and a moderate level. I normally use the deepest level of information during the testing and acceptance phase of the deployment, then switch to moderate and then the least level of information gathering. Also in my design I often set an error condition to begin gathering the deeper information along with the exception, where possible.

There are decisions you need to make as to where to store the diagnostics (many operations in the cloud cost money), how often you collect them, and so on. You can get a quick overview on using the diagnostics that come with Windows Azure here: http://www.azuresupport.com/2010/03/getting-started-with-windows-azure-diagnostics-and-monitoring/ This is where you should start first. More detail on that: http://msdn.microsoft.com/en-us/library/gg433048.aspx

My friend Dave Pallman has a great tool he’s released for free: http://davidpallmann.blogspot.com/2009/03/azure-application-monitor-now-on.html

If the issue is in storage apps: http://social.msdn.microsoft.com/Forums/en-US/windowsazuredata/thread/d84ba34b-b0e0-4961-a167-bbe7618beb83

If you have System Center, this is the quickest and easiest way to implement the monitoring – really handy: http://pinpoint.microsoft.com/en-us/applications/windows-azure-application-monitoring-management-pack-release-candidate-12884907699

Performance impact: The cost of doing small lookups in a large batch update

Linchi Shea — Mon, 04 Apr 2011 18:34:00 GMT

Lookup tables are widely used in database applications for good reasons. Usually, a lookup table has a small number of rows and looking it up with a join is fast, especially when the table is already cached.

Recently, I needed to update every row in many relatively large tables, each of which was identically structured, had ~25 million rows, and was ~30GB in size. The tables were denormalized to include both a lookup index column (i.e. CategoryID, which was an integer) and the corresponding lookup value column (i.e. CategoryName, which was a char(50)). The batch update I was performing was to ensure that the CategoryName column of these tables had the correct matching the value. The CategoryID to CategoryName mapping was defined in a small lookup table, CategoryLookup, with 10 rows.

Question

What would be the most efficient method to perform this batch update?

Three lookup methods

For the batch update scenario described above, you have three alternatives to lookup the CategpryName values (assume that the table to be updated is called Transactions):

Method 1—The inline CASE method, which performs an inline lookup with a CASE expression in the UPDATE statement. Okay, this is not really a lookup. But this method provides a baseline for comparison.

UPDATE Transactions

SET CategoryName = CASE

when CategoryID = 1 then 'abc1'

when CategoryID = 2 then 'abc2'

when CategoryID = 3 then 'abc3'

when CategoryID = 4 then 'abc4'

when CategoryID = 5 then 'abc5'

when CategoryID = 6 then 'abc6'

when CategoryID = 7 then 'abc7'

when CategoryID = 8 then 'abc8'

when CategoryID = 9 then 'abc9'

when CategoryID = 10 then 'abc10'

END

Method 2—The JOIN method, which relies on joining the Transactions table and the CategoryLookup table to do the lookup.

UPDATE t1

SET t1.CategoryName = t2.CategoryName

FROM Transactions t1 JOIN CategoryLookup t2

ON t1.CategoryID = t2.CategoryID

Method 3—The subquery method, which does a lookup with a subquery. Clearly, there is a join in the subquery.

UPDATE Transactions

SET CategoryName =

(SELECT CategoryLookup.CategoryName

FROM CategoryLookup

WHERE CategoryLookup.CategoryID=Transactions.CategoryID )

You can also do the lookup with a scalar function. But it’s so horrifically inefficient that you should not seriously consider it. It’s not interesting to include in this discussion. In addition, you could do the lookup with an inline table valued function, which has a similar performance profile as that of the inline CASE method.

It should be highlighted that method 2 (the JOIN method) and method 3 (the Subquery method) are not semantically identical. For instance, if the Transactions table has a CategoryID value that is not present in the CategoryLookup table, the Subquery method will, if permitted, set the CategoryName column to NULL, or the update will fail if NULL is not permitted, whereas the JOIN method will leave the CategoryName value unchanged. For the scenario we are interested in, the results of these two methods are identical. All the CategorID values in the Transactions table are also in the CategoryLookup table and the mapping from CategoryID to CategoryName in the CategoryLookup table is one to one.

I ran a series of controlled tests that mimicked the update scenario described previously. To keep the tests more manageable, I used a smaller and artificially created Transactions table that had 5,000,000 rows and was ~5GB in size. You can find the DDLs and the test script at the bottom of this post.

Test results and practical implications

I made sure that the results shown below were stable in that (1) they were taken from 50 repeated tests with a small number of outliers thrown out, and (2) the remaining results were inspected and made sure that the variances were relatively small among them and the values exhibited a consistent pattern.

Clearly, if you do a massive number of lookups (like what I did in this test), the cumulative cost can be quite visible. In fact, in this test using an inline CASE expression was more than twice as fast as lookups using either a subquery or a straight join. As the number of rows increases, you can expect to see this difference (or the cost of doing lookups) grow more prominent. So, if you are doing a very large batch update, it’s definitely worth replacing the table lookups with an inline CASE expression for better performance.

The difference between the CASE method and the table lookups (either the Subquery method or the JOIN method) remained stable across different test environments. But the difference between the Subquery method and the JOIN method was more subtle. In fact, if you run the same test in a different environment, you may see different relative performance between them. In some environments, the Subquery method can perform significantly better than the JOIN method.

Although there was a significant performance penalty when using Subquery or JOIN lookups in a massive update, this does not mean you should jettison using lookups in your individual transactions. Because the marginal cost of doing an individual lookup is infinitesimally small compared to many other performance-related factors, you’d lose much more in terms of code reuse, flexibility, and so on if you start to embed ‘lookups’ inline. To emphasize, note that the difference between the CASE method and the Subquery method in the test was ~34 seconds. Divide 34 seconds by the 5,000,000 lookups the update did, we get 6.8 microseconds as the marginal cost of an individual lookup.

There is no surprise that avoiding a massive number of table lookups could give you better performance. But it’s still good to be able to appreciate it with some concrete numbers. My update of all those 25-million-row tables mentioned at the beginning of this post took more than 10 hours to complete and I used the subquery method. Had I had the results reported here, I could have finished the same update process in five hours. That would have been a very nice saving!

Test setup

The lookup DDL and data:

drop table CategoryLookup

create table CategoryLookup(CategoryID int, CategoryName char(20))

with tmp(a, b) as (

select 1, 'abc' + cast(1 as varchar(5))

union all

select a+1, 'abc' + cast(a+1 as varchar(5))

from tmp

where a < 10

)

insert CategoryLookup

select * from tmp

create clustered index cix_CategoryLookup on CategoryLookup(CategoryID)

The Transactions test table DDL and data:

drop table Transactions

create table Transactions(CategoryID int,

CategoryName char(50),

filler char(1000))

set nocount on

declare @i int

set @i = 1

begin tran

while @i <= 5000000

begin

insert Transactions

select @i % 10 + 1, 'abc', 'filler'

if @i % 100000 = 0

begin

commit tran

begin tran

end

set @i = @i + 1

end

if @@trancount > 0

commit tran

sp_spaceused Transactions

create clustered index cix_Transactions on Transactions(CategoryID)

drop table test_log – this tale is used to log the test times

create table test_log (

Name varchar(50),

Num int,

StartTime datetime,

EndTime datetime NULL

)

The test script:

set nocount on

declare @dt datetime,

@i int

set @i = 1

while @i < 20 -- run the test 20 times

begin

set @dt = getdate()

insert test_log select 'CASE method', 10, @dt, NULL

update Transactions

set CategoryName = case

when CategoryID = 1 then 'abc1'

when CategoryID = 2 then 'abc2'

when CategoryID = 3 then 'abc3'

when CategoryID = 4 then 'abc4'

when CategoryID = 5 then 'abc5'

when CategoryID = 6 then 'abc6'

when CategoryID = 7 then 'abc7'

when CategoryID = 8 then 'abc8'

when CategoryID = 9 then 'abc9'

when CategoryID = 10 then 'abc10'

end

update test_log

set EndTime = getdate()

where StartTime = @dt

set @dt = getdate()

insert test_log select 'Subquery method', 10, @dt, NULL

update Transactions

set CategoryName =

(select CategoryLookup.CategoryName

from CategoryLookup

where CategoryLookup.CategoryID= Transactions.CategoryID )

update test_log

set EndTime = getdate()

where StartTime = @dt

set @dt = getdate()

insert test_log select 'JOIN method', 10, @dt, NULL

update t1

set t1.CategoryName = t2.CategoryName

from Transactions t1

join CategoryLookup t2 on t1.CategoryID = t2.CategoryID

update test_log

set EndTime = getdate()

where StartTime = @dt

set @i = @i +1

end

The reported results were obtained on a DL585 G1 with 64GB of RAM and eight 2.6GHz cores, running Windows Server Enterprise 2003 and SQL Server 2008 SP2 Enterprise x64 Edition. 50GB was allocated to the SQL Server instance.

Linked servers: how long do they stay connected?

Linchi Shea — Fri, 01 Apr 2011 00:36:00 GMT

I was looking at some of my old notes on linked servers and found a tidbit on how the linked server connections are managed by SQL Server. I'm posting it here because I don’t think the information is widely known.

When you make a linked server call from a SQL Server instance (say ServerA) to another SQL Server instance (say ServerB) over Microsoft Native Client OLEDB Provider, SQL Server on ServerA acts as a client to the instance on ServerB and will open or reuse a connection to ServerB. That connection will be managed by the SQL Server instance on ServerA.

If you check on ServerB with the following query, you should see that connection from ServerA (if it's still there):

select * from sysprocesses

where hostname = 'ServerA'

and program_name = 'Microsoft SQL Server'

And if no linked server calls from ServerA is using the connection, its status would be ‘dormant’.

The question is, “how long will ServerA keep the connection alive if no call is using it? And can you configure it?”

I can’t find any official documentation to answer these two questions. But my own tests appear to yield a consistent answer to the first question. That is, a dormant SQL Server linked server connection will stay for about 4~5 minutes, and will be closed after that. All my attempts to see if this number is configurable suggest that the answer is negative. If anyone knows a more authoritative answer, please post it.

Here is a simple test to determine how long a dormant connection stays alive.

On ServerB, use the previous query to ensure that there is no linked server connection from ServerA. If there is, kill the connection and ensure ServerA does not open a new one.

On ServerB, run this script:

declare @dt datetime

while not exists (select * from sysprocesses

where hostname = 'ServerA'

and program_name = 'Microsoft SQL Server')

waitfor delay '00:00:01'

set @dt = getdate()

while exists (select * from sysprocesses

where hostname = 'ServerA'

and program_name = 'Microsoft SQL Server')

waitfor delay '00:00:01'

select 'Duration' = datediff(second, @dt, getdate())

Go to ServerA and run the next script:

select * from openquery(ServerB, 'select @@servername')

I have been consistently getting the Duration values between ~260 seconds and ~300 seconds, which is about 4~5 minutes. If you have any test numbers of your own or run this test, please post your findings.