Michael J. Swart

November 20, 2014

Developers, Ready for a New SQL Server Version?

Filed under: Miscelleaneous SQL,Technical Articles — Michael J. Swart @ 2:04 pm

Is your application ready to have its SQL Server upgraded to the next version? It’s important to find out because if you’re ready (and you know it) you can proceed with confidence. But if you’re not ready, you can choose to adjust your application, postpone the upgrade or do both.

You Only Live Once

But there’s some good news, chances are really good that your application is in fact ready for the next version of SQL Server. Focusing on the database engine I want to explore ways to find out for sure.

First, Inspect Your App for Discontinued Features and Other Changes

Bookmark Microsoft’s docs on the subject: SQL Server Database Engine Backward Compatibility. This is your main resource for your upgrade “certification” effort. Microsoft describes Deprecated and Discontinued features and it also describes breaking and behavior changes. The list is well-written and well-organized. Microsoft has really done well with these.

Look through your own application’s code using a code search or other means and check to see whether your application is affected by these changes. Luckily these lists have always been manageable. Microsoft introduces a lot of features, but they don’t deprecate a lot. It’s in their best interest to maintain backwards compatibility.

Just like breakfast, it’s important not to skip this step, it’s the most important one. I’ve seen this inspection step uncover more potential upgrade issues than any of the following steps.

Test Your App on a Production-like Instance

Now it’s time to test your application with the new version. But that’s easier said than done. You’ll need to:

  • Get a database server (easy) whose specs are close to what’s running in prod (less easy).
  • Find a database with the same size data as prod (a restored backup of prod if you can swing it).
  • The ability to generate a workload with the same volume and variety as seen in prod (really tricky).
  • Now upgrade to the next version of SQL Server and test.

Even if you can’t perfectly reproduce production hardware, production data or production workloads, most people expect this kind of testing. For that reason alone, this step shouldn’t be skipped. It gives people confidence.

Use the Deprecated Features Performance Counter

SQL Server provides performance counters that track when it uses a deprecated feature. And because they’re SQL Server performance counters, you can inspect the values from a query window like this:

select * 
from sys.dm_os_performance_counters
where object_name like '%Deprecated Features%'
and cntr_value > 0

These counters get reset when SQL Server is restarted. So look at a production server that has been running for a while.

But this list can be tricky. Even against the cleanest, quietest database server, you’re never going to see an empty result set. There are always a number of “problems” that get reported. And it’s not easy to sort out which ones matter. The list indicates that someone used a deprecated feature of SQL Server and that someone could be:

  1. Your application
  2. SQL Server system processes
  3. Some admin or other user writing ad hoc SQL
  4. SQL Server Management Studio

Try to determine which problems come from your application and whether you want to do anything about it.

Upgrade Advisor

The SQL Server Upgrade Advisor is a tool that Microsoft provides to help people when upgrading to a new version. It’s in Microsoft’s interest to make the whole process as painless as possible and they do a pretty good job.

The Upgrade Advisor will also look at Analysis Services, Reporting Services and Integration Services, but you’ll be focusing on the Database Engine. Optionally you can provide a workload in the form of a system trace. It’s a nice tool, but it’s rare that it will catch anything in your app that you haven’t caught already with the previous steps.

Other Considerations

Undocumented Features
Are there any undocumented trace flags that you’re using? If you’re depending on behavior of such trace flags, now’s a time to take a fresh look at what you’re trying to accomplish. The behavior is, by definition, undocumented and can change any time.

Database Compatibility Levels Other Than Latest
I like to avoid any compatibility level that’s not the most current. Mostly this is a supportability thing.

As a vendor, I want to support the fewest number of versions possible. If I begin to entertain different compatibility levels, this multiplies the number of environments I have to support.

But maybe you’re not a vendor. If you have just one single database to support. If you choose to take small steps through compatibility levels and versions, you’re probably doubling the amount of testing required.

Known Issues
If you’re not going to the latest Service Pack and/or Cumulative Update, then you should be aware of the fixes you’re choosing not to take.
It’s actually very difficult to keep up to date on what known issues are out there and which ones are severe enough to worry about. For example, if I only use the SQL Server database engine, I’m not going to be impacted by a DAX performance bug in SSAS, but I do care an awful lot about Data Corruption Bugs.
I like to read what Aaron Bertrand has to say on SQLBlog.com or SQLPerformance.com. He’s a good resource when trying to keep up with known issues in SQL Server.

Unknown Issues
Otherwise known as Avoid-RTM. This advice is less relevant lately and I think it might be for a couple reasons. Just speculating here:

  • The cynical part of me says that Microsoft will just wait a little bit and call the CU that month SP1 just to appease the people who are waiting.
  • The optimist in me sees that Microsoft lately has a really nice cloud-first deployment model where Azure customers are kicking the tires behind the scenes so that when RTM comes around, parts of the database have seen a lot of production hours they wouldn’t have otherwise. In the past I have been burned by database engine bugs in 2005 and 2008. I haven’t been burned by 2008 R2 or 2012. It’s anecdotal, but it makes me hopeful.

Does Version Matter

Not really, I wrote this post with no particular version in mind.

But I do want to mention one particular point that has to do with SQL Server 2014. The new cardinality estimator really affects the behavior of the database engine. It affects plans chosen by the query optimizer and hence it affects the performance of applications that use it.
This is why I advise everyone to give the upgrade to 2014 a little more scrutiny than an upgrade to previous versions. Look for performance regressions. This is especially true if you or other developers are fond of writing queries with a lot of query hints. I understand that regressions are more common in queries that make heavy use of hints.

October 3, 2014

Watch Out for Misleading Behaviour From SQL Server

Takeaway: To get consistent behaviour from SQL Server, I share a set of statements I like to run when performing tuning experiments.

Inconsistent Behaviour From SQL Server?

I often have conversations where a colleague wants to understand why SQL Server performs faster in some cases and slower in other cases.

The conversation usually starts “Why does SQL Server perform faster when I…” (fill in the blank):

  1. … changed the join order of the query
  2. … added a transaction
  3. … updated statistics
  4. … added a comment
  5. … crossed my fingers
  6. … simply ran it again

What’s Going On?

It can actually seem like SQL Server performs differently based on its mood. Here are some reasons that can affect the duration of queries like the ones above

  • You changed something insignificant in the query. What you may be doing is comparing the performance of a cached plan with a newly compiled plan. Examples 1 – 4 might fall under this scenario. If that’s the case, then you took a broken thing and gave it a good thump. This percussive maintenance may be good for broken jukeboxes, but maybe not for SQL Server.
  • What about those last two? Say you hit F5 to execute a query in Management Studio, and wait a minute for your results. You immediately hit F5 again and watched the same query take fifteen seconds. Then I like to point out that maybe all that data is cached in memory.

In order to do tune queries effectively, we need consistent behaviour from SQL Server, if only to test theories and be able to rely on the results. SQL Server doesn’t seem to want to give us consistent behaviour…

So Is It Possible To Get Straight Answers?

Best line from all Star Wars

But maybe we can get straight answers from SQL Server. Here’s a test framework that I like to use before all experiments when I want consistent behaviour:

/* Only do this on dev sql servers! */
CHECKPOINT 
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
SET STATISTICS IO, TIME ON
-- Ctrl+M in Management Studio to include actual query plan

The first two statements are meant to clear SQL Server’s cache of data. Because of write ahead logging, SQL Server will write data changes to disk immediately, but may take its time writing data changes to disk. Executing CHECKPOINT makes SQL Server do that immediately. After the checkpoint there should be no dirty buffers. That’s why DBCC DROPCLEANBUFFERS will succeed in dropping all data from memory.

The DBCC FREEPROCCACHE command will remove all cached query plans.

These commands give SQL Server a fresh starting point. It makes it easier to compare behaviour of one query with the behaviour of another.

The SET STATISTICS IO, TIME ON and the Ctrl+M are there in order to retrieve better information about the performance of the query. Often CPU time, Logical IO, and the actual query plan are more useful when tuning queries than elapsed time.

September 18, 2014

SQL Server Ignores Trailing Spaces In Identifiers

Filed under: Miscelleaneous SQL,SQLServerPedia Syndication,Technical Articles — Michael J. Swart @ 10:27 am

Takeaway: According to SQL Server, an identifier with trailing spaces is considered equivalent to the same identifier with those spaces removed. That was unexpected to me because that’s not how other programming languages work. My investigation was interesting and I describe that here.

The First Symptom

Here’s the setting, I work with a tool developed internally that reads metadata from a database (table names, column names, column types and that sort of thing). Recently the tool told me that a table had an unexpected definition. In this case, a column name had an extra trailing space. I expected the column name "Id" (2 characters), but my tool was reporting an actual value of  "Id " (notice the blank at the end, 3 characters). That’s what started my investigation.

But that’s really weird. What would lead to a space accidentally getting tacked on to a column name? I couldn’t think of any reason. I also noticed a couple other things. Redgate SQL Compare was reporting no discrepancies and the database users weren’t complaining at all, they seemed just fine. A bug in the in-house tool seemed most likely. My hunch was that there was a problem with the way we collecting or storing these column names (how did a space sneak in there?).

Where Are Column Names Stored?

I wanted to look at the real name of the column – straight from the source – so I ran:

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME LIKE 'Id%'

It told me that my tool wasn’t wrong. That the column was actually named "Id " with the space. So maybe Red Gate is getting its metadata from somewhere else? I know of a few places to get column information. Maybe Red Gate is getting it from one of those? Specifically I wanted to look closer at these views:

  • sys.columns
  • sys.syscolumns
  • INFORMATION_SCHEMA.COLUMNS

Because these objects are views, I used sp_helptext to learn that all the column names ultimately come from a system table called sys.syscolpars. But sys.syscolpars is a system table and you can’t look at its contents unless you connect to the database using the dedicated administrator connection. And that’s exactly what I did.

I learned that there is only one version of column names, only one place on disk that sql server persists the name of the column. It’s interesting because this implies that Red Gate’s SQL Compare trims trailing spaces from identifier names.

But Doesn’t SQL Server Care?

Well, there’s one way to check:

CREATE TABLE [MyTest] ( [id ] INT );
INSERT INTO [MyTest] VALUES (1);
 
SELECT [id ], [id] -- one column name with a space, one column name without
FROM [MyTest]; 
-- returns a dataset with column names as specified in the query.
go

Just like Red Gate’s SQL Compare, it seems like SQL Server doesn’t care about trailing spaces in identifiers either.

Google? Stackoverflow? Want to Weigh In?

A quick search led me to the extremely relevant Is SQL Server Naming trailing space insensitive?.

And that question has answers which link to the Books Online page documenting Delimited Identifiers. That page claims that “SQL Server stores the name without the trailing spaces.” Hmmm, they either mean in memory, or the page is inaccurate. I just looked at the system tables a moment ago and the trailing spaces are definitely retained.

The stackoverflow question also led me to a reported defect, the Connect item Trailing space in column names. This item was closed as “by design”. So this behavior is deliberate.

What do other SQL Vendors do?

I want to do experiments on SQL databases from other vendors but my computer doesn’t have a large number of virtual machines or playground environments. But do you know who does? SQL Fiddle
It’s very easy to use this site to see what different database vendors do. I just pick a vendor and I can try out any SQL I want. It took very little effort to be able to compile this table:

RDBMS CREATE TABLE... SELECT "id ", "id"...
MySQL Incorrect column name 'id '
Oracle Success "id": invalid identifier
PostgreSQL Success Column "id" does not exist
SQLite Success could not prepare statement (1 no such column: id)
SQL Server Success
ID ID
1 1

And What Does the ANSI standard say?

Look at the variety of behaviors from each vendor. I wonder what the “standard” implementation should be.

Mmmm... SQL Syntax rules.

I googled “ANSI SQL 92″ and found its wikipedia page and that led me to the SQL-92 Standard itself.

ANSI (paraphrased) says that

<delimited identifier> ::= <double quote><one or more characters><double quote>

And it also says explicitly that delimited identifiers can include spaces.

What About String Comparisons In General?

During my experiments on SQL Server I found myself executing this query:

SELECT *
FROM sys.columns
WHERE name = 'Id'

I was surprised to find out that my three-character "Id " column came back in the results. This means that SQL Server ignores trailing spaces for all string comparisons, not just for identifiers.

I changed my google search and looked for “sql server string comparison trailing space”. This is where I found another super-relevant document from Microsoft: INF: How SQL Server Compares Strings with Trailing Spaces.

Microsoft pointed to the ANSI standard again. I mean they explained exactly where to look, they pointed straight to (Section 8.2, , General rules #3) which is the section where ANSI explains how the comparison of two character strings is determined. The ANSI standard says that for string comparisons, the shorter string is effectively padded with trailing spaces so that comparisons can always performed on strings with an equal number of characters. Why? I don’t know.

And that’s where identifier comparisons come in. I found another part of the standard (Syntax rule #11) which tells me that Identifiers are equivalent if they compare as equivalent according to regular string comparison rules. So that’s the link between string comparisons and identifier comparisons.

Summary

There’s a number of things I learned about string comparisons. But does any of this matter? Hardly. No one deliberately chooses to name identifiers using trailing spaces. And I could have decided to sum this whole article up in a single tweet (see the title).

But did you figure out the head fake? This blog post is actually about investigation. The investigation is the interesting thing. This post describes the tools I like to use and how I use them to find things out for myself including:

  • Queries against SQL Server itself, the obvious authority on SQL Server behavior.
    • Made use of sp_helptext
    • Made use of the Dedicated Adminstrator Connection to look at system tables
  • Microsoft’s Books Online (used this twice!)
  • Microsoft Connect
  • Google
  • Stackoverflow
  • SQLFiddle
  • Wikipedia
  • the ANSI Standard

Maybe none of these resources are new or exciting. You’ve likely used many of these in the past. But that’s the point, you can find out about any topic in-depth by being a little curious and a little resourceful. I love to hear about investigation stories. Often how people find things can be at least as interesting as the actual lesson.

September 9, 2014

Take Care When Scripting Batches

Filed under: Miscelleaneous SQL,SQL Scripts,Technical Articles — Michael J. Swart @ 1:22 pm

Takeaway: When performing long-running modifications, I’m sure many of you enjoy using batches to increase concurrency. But I want to talk about a pitfall to be aware of. If you’re not careful, the method you use to implement batching can actually worsen concurrency.

... we don't need no stinkin' batches either

Why Use Batches?

Even without an explicit transaction, all SQL statements are atomic – changes are all or nothing. So when you have long-running modifications to make, locks on data can be held for the duration of your query and that can be too long. Especially if your changes are intended for live databases.

But you can make your modifications in several smaller chunks or batches. The hope is that each individual batch executes quickly and holds locks on resources for a short period of time.

But care is needed. I’m going to give an example to show what I mean. The example uses the FactOnlineSales table in the ContosoRetailDW database (available as a download here). The FactOnlineSales table has

  • one clustered index on OnlineSalesKey and no other indexes,
  • 12 million rows,
  • and 46 thousand database pages

Metrics to Use
In this example, I want to know how long each query takes because this should let me know roughly how long locks are held.
But instead of duration, I’m going to measure logical reads. It’s a little more consistent and in the examples below it’s nicely correlated with duration.

The Straight Query

Suppose we want to remove sales data from FactOnlineSales for the “Worcester Company” whose CustomerKey = 19036. That’s a simple delete statement:

DELETE FactOnlineSales WHERE CustomerKey = 19036;

This delete statement runs an unacceptably long time. It scans the clustered index and performs 46,650 logical reads and I’m worried about concurrency issues.

Naive Batching

So I try to delete 1,000 rows at a time. This implementation seems reasonable on the surface:

DECLARE	
	@RC INT = 1;
 
WHILE (@RC > 0)
BEGIN
 
  DELETE TOP (1000) FactOnlineSales
  WHERE CustomerKey = 19036;
 
  SET @RC = @@ROWCOUNT
 
END

Unfortunately, this method does poorly. It scans the clustered index in order to find 1,000 rows to delete. The first few batches complete quickly, but later batches gradually get slower as it takes longer and longer to scan the index to find rows to delete. By the time the script gets to the last batch, SQL Server has to delete rows near the very end of the clustered index and to find them, SQL Server has to scan the entire table.

In fact, this last batch performs 46,521 logical reads (just 100 fewer reads than the straight delete). And the entire script performed 1,486,285 logical reads in total. If concurrency is what I’m after, this script is actually worse than the simple DELETE statement.

Careful Batching

But I know something about the indexes on this table. I can make use of this knowledge by keeping track of my progress through the clustered index so that I can continue where I left off:

DECLARE
	@LargestKeyProcessed INT = -1,
	@NextBatchMax INT,
	@RC INT = 1;
 
WHILE (@RC > 0)
BEGIN
 
  SELECT TOP (1000) @NextBatchMax = OnlineSalesKey
  FROM FactOnlineSales
  WHERE OnlineSalesKey > @LargestKeyProcessed
    AND CustomerKey = 19036
  ORDER BY OnlineSalesKey ASC;
 
  DELETE FactOnlineSales
  WHERE CustomerKey = 19036
    AND OnlineSalesKey > @LargestKeyProcessed
    AND OnlineSalesKey <= @NextBatchMax;
 
  SET @RC = @@ROWCOUNT;
  SET @LargestKeyProcessed = @NextBatchMax;
 
END

The delete statements in this script performed 46,796 logical reads in total but no individual delete statement performed more than 6,363.

Graphically that looks like:

Logical Reads Per Delete Statement

Logical Reads Per Delete

The careful batching method runs in roughly the same time as the straight delete statement but ensures that locks are not held for long.
The naive batching method runs with an order or complexity (compared to the expected complexity of n) and can hold locks just as long as the straight delete statement.
This underlines the importance of testing for performance.

April 23, 2014

Removing Comments from SQL

Filed under: Miscelleaneous SQL,SQL Scripts,SQLServerPedia Syndication,Technical Articles — Michael J. Swart @ 10:20 am

Check out the following deliberately crazy SQL Script:

create table [/*] /* 
  -- huh? */
(
    "--
     --" integer identity, -- /*
    [*/] varchar(20) /* -- */
         default '*/ /* -- */' /* /* /* */ */ */
); 
go

It’s not surprising that my blog’s syntax colorer has trouble with this statement. But SQL Server will run this statement without complaining. Management Studio doesn’t even show any red squiggly lines anywhere. The same statement without comments looks like this:

create table [/*] 
(
    "--
     --" integer identity, 
    [*/] varchar(20) 
         default '*/ /* -- */' 
); 
go

I want a program to remove comments from any valid SQL and I want it to handle even this crazy example. I describe a handy method that lets me do that.

Using C#

  • In your C# project, find and add a reference to Microsoft.SqlServer.TransactSql.ScriptDom. It’s available with SQL Server 2012′s Feature Pack (search for “ScriptDom” and download).
  • Add using Microsoft.SqlServer.Management.TransactSql.ScriptDom; to your “usings”.
  • Then add this method to your class:
    public string StripCommentsFromSQL( string SQL ) {
     
        TSql110Parser parser = new TSql110Parser( true );
        IList<ParseError> errors;
        var fragments = parser.Parse( new System.IO.StringReader( SQL ), out errors );
     
        // clear comments
        string result = string.Join ( 
          string.Empty,
          fragments.ScriptTokenStream
              .Where( x => x.TokenType != TSqlTokenType.MultilineComment )
              .Where( x => x.TokenType != TSqlTokenType.SingleLineComment )
              .Select( x => x.Text ) );
     
        return result;
     
    }

… and profit! This method works as well as I hoped, even on the given SQL example.

Why I Prefer This Method

A number of reasons. By using Microsoft’s own parser, I don’t have to worry about comments in strings, or strings in comments which are problems with most T-SQL-only solutions. I also don’t have to worry about nested multiline comments which can be a problem with regex solutions.

Did you know that there’s another sql parsing library by Microsoft? It’s found at Microsoft.SqlServer.Management.SqlParser.Parser. This was the old way of doing things and it’s not supported very well. I believe this library is mostly intended for use by features like Management Studio’s Intellisense. The ScriptDom library is better supported and it’s easier to code with.

Let Me Know If You Found This Useful

Add comments below. Be warned though, if you’re a spammer, I will quickly remove your comments. I’ve had practice.

March 18, 2014

A Primer on Locks, Blocks and Deadlocks

Filed under: Miscelleaneous SQL,Technical Articles — Michael J. Swart @ 8:50 am

Lock, block and deadlock are three related terms in SQL Server. They have a specific meaning and are often confused with each other. I hope to clear them up here.

(I’m using a new visual format. This means you RSS readers will have to come by and see it in person.)

But There’s One More Thing…

As long as I’m experimenting with visual formats, check out the same content in another medium.
The Locking Primer Presentation

More On This

This was just a primer. The best resource page remains Kendra Little’s Locking and Blocking in SQL Server.

March 10, 2014

I Don’t Mind SELECT * Sometimes

Filed under: Miscelleaneous SQL,Technical Articles — Michael J. Swart @ 11:50 pm

T-SQL Tuesday LogoI’m participating in this month’s T-SQL Tuesday. The host – handsome genius – has asked us to argue against a popular opinion. So the opinion I’m tackling today is that SELECT * should always be avoided.

I’ve heard that a lot. For example, it seems to be a popular question on Stack Overflow. The consensus is that SELECT * is discouraged in favor of SELECT col1, col2, col3 where you select the columns you need and no others.

I’m going to look at some of the reasons that are given to support this advice. I’m also going to qualify when it might not matter so much.

No not always, only a Sith deals in absolutes.

Unused columns cause excessive load on the database.
Touché. This is true, SELECT * often causes SQL Server to use the clustered index because it’s usually the only covering one. This can waste an opportunity to use a more efficient index.

However, even when us developers follow the “Query only what you need” advice, our SELECT queries often do in fact need every single column. In this case, SELECT * is fine.

Columns are returned in an unknown order
That’s true. The fear is that an application will contain a bug when it depends on a column order that is incorrect. Well simply don’t do that. Most applications are able to retrieve row values in the record set by column name rather than column position. Identify columns by name. Then the column order doesn’t matter.

You can’t search code for the use of a column
That’s an interesting one. Say I’ve got a USERS table with a Title column. This column is getting dusty and so we plan to deprecate it and ultimately remove it.

It would be great to search our code base and find where this column gets used. Presumably the code that contains SELECT * would be missed in the search but any code that contains SELECT ... Title would be found.

But how big a problem is this? The only trouble is when an application uses SELECT * and then after it gets the results, it takes that data set and looks for the column Title by name. If that’s the case, then you will probably find that instance of the Title string in your code search.

The development effort is not really impacted (because SELECT * doesn’t need to change). The testing effort is not impacted terribly either. I can’t think of a scenario where Ctrl+F column name is the only thing standing in the way of a bug getting into production. If you can think of one, then it’s probably not the only quality issue that needs attention.

TL;DR

I don’t mind SELECT * when I’m using every column and when I don’t depend on the order of the columns.

February 13, 2014

Troubleshooting Concurrency – A Demo

Filed under: Miscelleaneous SQL,SQLServerPedia Syndication,Technical Articles — Michael J. Swart @ 10:00 am

Testing Concurrency

So you’ve read about how I generate concurrent activity by calling s_DoSomething super often. And you’ve even seen examples of how I define s_DoSomething. Now, you can watch me put all that stuff in action in this screencast.

Then download demo scripts and other files by clicking TroubleshootingConcurrency.zip and then Ctrl+S to save.

Click play and follow along.

The Transcript:

Intro

Hey everyone,
So I’ve posted a couple articles about ways to troubleshoot concurrency problems.
Now I want to demo what that looks like in action.
So what I’m going to do, I’m going to take a common concurrency problem.
In this case, I’m going to tackle the dreaded deadlock and I’m going to investigate the problem and I want to show you the actions that I take so you can see what I’m doing.
And I’ll be using my concurrency generator to help me test a solution that I come up with.
Ready? Here we go

Identify Deadlock To Troubleshoot

So now what I’m gonna do is I’m going to be troubleshooting a deadlock or a set of deadlocks.
And just to set up the scenario a bit, I would normally attack this problem if I’m handed a set of deadlocks to troubleshoot.
In this case <let’s look in here> what I have is a set of deadlock graphs.
Each graph is a file which contains a whole bunch of information about an instance of a deadlock that SQL Server detected and dealt with.
Lets take a look at one.
I’m going to open up in SQL Server Management Studio and first thing you notice is that there are four ovals and four rectangles.
The ovals are processes and the rectangles are resources.
The ovals are fighting over access to the resources and they happen to be fighting in a circle so they get deadlocked.
No one can do anything.
Everyone’s waiting on everybody else.
And you can actually see that SQL Server detected and chose this process as the deadlock victim.
All right, now that’s four processes.
Because I happen to know that each one of these deadlock graphs indicates the same problem that I’m trying to troubleshoot, I’m going to pick a deadlock graph that’s actually smaller.
I’m going to pick graph 112 and open it.
Yeah, I can deal with this.
This one only has two processes fighting over two resources.
This process owns an exclusive lock on this key lock.
I like to think of key locks as a row, just mentally.
And this one owns an exclusive lock on this row up here.
But they’re both waiting for the other guy to release that so they can get a shared lock on it.
Now shared locks and exclusive locks are mutually exclusive.
They are incompatible and so that’s why we wait.
So let’s look at the specifics.
This resource is a key lock which means it’s a row in the table MyPeople and that table’s in the schema Person which is in the database Adventureworks.
In particular it’s got an exclusive lock on a row in an index called pk_Person_MyPeople.
Because of the naming convention, I happen to know this is a primary key.
And it looks like this resource is the same kind of row, or the same kind of key lock.
And the processes, what do we know about this?
Not much. It’s running a stored procedure inside database 9 which is Adventureworks and the ObjectId of the procedure is given there.
I don’t know much else about that yet.
I do know that this process was running the same procedure.
Great, so I think now it’s time to look a little bit closer at this deadlock.

Find Root Cause

So the way I dig deeper into these deadlock graphs is by opening up these files in an xml editor.
It turns out that these deadlock graphs are simply defined as xml.
So you can open this up in notepad or your favorite xml editor.
I’m going to use Visual Studio, because that’s what I like to use.
There it is. Right away, you notice that it’s got the same information.
There’s the processes, but it’s got a lot more details.
So before we knew that this procedure…
Now we know that not only is this procedure called Adventureworks.dbo.s_Person_MyPeople_Delete, but that it was running that query when it deadlocked.
We also know that the other procedure was running the same thing s_Person_MyPeople_Delete.
I want to find out more about this procedure because it was the thing that was deadlocking.
So I’m going to copy. That’s in the clipboard now. I’m going to use Management Studio to be able to do that.
So (sp_helptext and results-to-text, f5) there’s the definition, because we’re connected to Adventureworks.
It’s a very simple statement. DELETE Person.MyPeople WHERE BusinessEntityId = @BusinessEntityId.
Now it’s very unusual for a procedure this small to participate in a deadlock.
Especially when Person.MyPeople …
Let’s look at this (results to grid alt+f1)
Okay, there’s the indexes… especially… this table has a clustered unique index on BusinessEntityId.
So I know that something else is going on here.
Maybe there’s a trigger
Or maybe there’s an indexed view on Person.MyPeople that needs to be updated every time there’s a modification.
Or maybe there’s a foreign key that needs to be enforced.
Something has to explain why after a modification, it continues to want to read other different rows in the same table.
In order to find out more, I’m actually going to look at the execution plan.
So (let’s copy paste and let’s see) I’m going to highlight that and display estimated execution plan.
Alright there we go, this gives me a lot more information.
Here are the things I notice.
There’s a missing index warning of course.
It looks like my unindexed foreign key theory is looking better.
Cause look, there’s the delete.
It goes into the table spool, the rows that are about to be deleted.
It comes out there and it looks … for each row, it makes sure that it scans the table in order to find … let’s see … Mentorid.
It’s looking for Mentorid.
It wants to assert that there are no rows where Mentorid is pointing to BusinessEntityId.
So my guess is that looking at the missing index details… sure enough it wants an index on MentorId.
It’s pretty clear to me that this table needs to enforce… (results-to-grid, alt+f1)
It needs to enforce this foreign key fk_MyPeople_MyPeople where MentorId references BusinessEntityId.
So it’s a foreign key to itself and it needs to scan the whole table in order to enforce that foreign key.
That’s looking like my root cause and I believe that adding the missing index that’s suggested here will help solve my deadlock problem.
But how do we know for sure? That’s next.

Verify Solution With Concurrency Generator

Okay, where are we?
We looked a little at this procedure s_person_MyPeople_delete and in particular, this query, this delete statement that was giving us some deadlock problems.
And we have a pretty good idea of its root cause.
Basically, this statement needed to scan Person.MyPeople in order to enforce a foreign key, a self referencing foreign key.
And it’s that reason that it’s participating in deadlocks.
So it’s my theory that if we have an index on MentorId that’s suggested in these missing index details.
If we create that index, it will not only speed up this query, but it will help concurrency and avoid deadlocks and that’s the solution I’m recommending.
But before I can say that I nailed this problem, I need to reproduce it and see that my fix solves the problem.
And I can do that using my load generator that I’ve been talking about in my blog lately.
First I want to define an s_DoSomething in tempdb. Let’s create
(use tempdb)
And I’m just going to modify an example from my blog.
And I’m going to alter a procedure s_DoSomething
Now I only care about an integer parameter right?
Because the stored procedure I care about only has the one parameter.
Let’s change it. There that looks pretty good.
But I actually want to modify that value.
In order to do that let’s look at Person.MyPeople
I’m going to look at the range of values. (max(businessentityid) and max(mentorid))
Let’s see how that does.
That’s interesting, so the largest MentorId is 10388.
So i want my integer value to be at least that and the range is that … so that range.
Okay, so just to avoid off by one errors, I’m going to call that nine and I’m going to shrink that range a bit.
So that sounds good. Let’s create it.
Oh, it doesn’t exist yet. There we go. That sounds good.
Now that s_dosomething is defined, I want to call it a lot of times concurrently.
This is done with the utility that I have.
This is the concurrency launcher and you can look at the definition of it, which is in my blog.
And after it’s compiled, I’m going to launch it.
And we wait a little while, and oh look there’s a deadlock and as things start blocking up, I expect a few more.
More blocking, more deadlocks, more, more, yeah, there we go.
Okay, I would say that we reproduced the problem.
I’m going to cancel that.
Let’s launch it again, just to make sure.
Deadlock, deadlock, deadlock deadlock, ahh, very good. Isn’t that great?
I can reproduce this problem at will.
Let’s see if my theory is sound.
My theory is that if I create this index (call it ix_myindex) name it something better later. (Tada)
My theory is that once that’s created, I should no longer see any concurrency problems.
Let’s launch it again.
I’m hoping to see nothing,
I’m hoping to see nothing,
It’s looking pretty good.
Wow, It completed.
So It completed which tells me that it has executed a procedure 500000 times.
All concurrently 50 at a time without a single deadlock.
That is encouraging news.
So now I would feel comfortable recommending this index as a solution to the set of deadlocks that I was troubleshooting.

Lemme explain… No there’s no time, Lemme Sum Up

So in conclusion, that’s an example of how I use my utility.
I find it useful enough that I’ve added a shortcut to my windows taskbar.
That gives me one-click concurrent database activity.
It helps me look closer at issues where processes suffer from unlucky timing or other problems that are hard to reproduce because of fussy concurrency conditions.
I hope you found this useful.

Cheers.

January 30, 2014

Building Concurrency Tests

Testing Concurrency

So last week, I explained different ways to generate concurrent activity. I have my own favorites, but you may have your own. So pick your favorite method; whichever method you picked, it will help you call the procedure s_DoSomething super-often.

Now comes the task of defining the procedure s_DoSomething. It can be whatever you like depending on what functionality you want to test or exercise. I want to demonstrate some patterns that I follow when I define that procedure. Those patterns all start with …

the Basic Definition

To test the concurrency of a single procedure just call it:

use tempdb
GO
 
CREATE PROCEDURE dbo.s_DoSomething AS
 
EXEC Adventureworks2012.dbo.uspGetManagerEmployees 14;

Of course, I could have changed the framework to call my procedure directly but I don’t out of habit. I always leave s_DoSomething in tempdb hard-coded in the framework.

With Arbitrary Parameter Values

Often the procedures I want to test are defined with parameters. If variety is important, but the parameter values are not, then that’s when the random tricks come in:

ALTER PROCEDURE dbo.s_DoSomething AS
 
DECLARE @someString nvarchar(100) = cast(newid() as nvarchar(100));
DECLARE @someInt int = RAND() * 100;
DECLARE @someDate datetime = dateadd(MINUTE, RAND() * 10000, getdate());
DECLARE @someLongerString nvarchar(1000) = REPLICATE(@someString,20);
 
EXEC Adventureworks2012.dbo.usp_ProcWithParameters
	@someString, @someInt, @someDate, @someLongerString;

With Less Arbitrary Parameter Values

Check out this next example. Pulling a value from the target database is often preferable to calling the procedure with a random integer value.

ALTER PROCEDURE dbo.s_DoSomething AS
 
DECLARE @BusinessEntityId int;
 
SELECT TOP 1 @BusinessEntityId = BusinessEntityID 
FROM AdventureWorks2012.HumanResources.Employee
ORDER BY newid();
 
EXEC AdventureWorks2012.dbo.uspGetEmployeeManagers @BusinessEntityId;

Calling More Than One Procedure

It’s as simple as calling one after the other. But sometimes I want the frequency of the calls “weighted”.

For example, I want to have a DELETE, INSERT and UPDATE statements called 10% of the time each. The remaining 70% of the time I want to call a SELECT statement. Then I have something like:

ALTER PROCEDURE dbo.s_DoSomething AS
 
declare @r int = RAND() * 10;
 
IF (@r = 0)
  -- delete 10% of the time
  DELETE AdventureWorks2012.Person.BusinessEntity
  WHERE BusinessEntityID = CAST(RAND()*1000 as INT);
 
IF (@r = 1)
  -- insert 10% of the time
  INSERT AdventureWorks2012.Person.BusinessEntity (rowguid)
  VALUES (newid());
 
IF (@r = 2)
  -- update 10% of the time
  UPDATE AdventureWorks2012.Person.BusinessEntity
  SET rowguid = newid()
  WHERE BusinessEntityID = CAST(RAND()*1000 as INT);
 
IF (@r > 2)
  -- select the rest of the time
  SELECT BusinessEntityId, rowguid, ModifiedDate
  FROM AdventureWorks2012.Person.BusinessEntity
  WHERE BusinessEntityID = CAST(RAND()*1000 as INT);

Counting Errors Concurrently

I want to track (server side) how often s_DoSomething fails. But I don’t want tracking to be a concurrency bottleneck itself. Here’s a cool trick for that:
First define these procedures:

create procedure dbo.s_IFailed as
go
 
create procedure dbo.s_IPassed as
go
 
alter procedure dbo.s_DoSomething as
 
begin try
    declare @i int = rand() * 10;
    select @i = 1 / @i -- might divide by 0!
    exec dbo.s_IPassed;
end try
begin catch
    exec dbo.s_IFailed;
end catch

This lets me use DMVs to monitor the success rate because I can check the execution count of my dummy procedures. For example,

exec sp_recompile 'dbo.s_IFailed'; -- reset counts from other tests.
exec sp_recompile 'dbo.s_IPassed';
go
 
/* run concurrent test here, or...*/
set nocount on; 
exec dbo.s_DoSomething;
go 10000
 
select object_name(object_id), execution_count 
from sys.dm_exec_procedure_stats
where object_name(object_id) in ('s_IFailed','s_IPassed')
/*
--         count
s_IPassed   9031
s_IFailed    969
*/

This relies on the DMV sys.dm_exec_procedure_stats which was introduced in 2008. It’s like a cheap do-it-yourself, performance counter.

Next

Next week I want to show a demo. I want to show this technique in action. I’ll be troubleshooting a common concurrency problem, the deadlock.

January 23, 2014

Generating Concurrent Activity

Filed under: Miscelleaneous SQL,SQL Scripts,SQLServerPedia Syndication,Technical Articles — Michael J. Swart @ 10:21 am
Testing Concurrency

I’ve discovered that DBAs use different methods to accomplish what amounts to the same thing: Generating concurrent activity on a server. I want to explore a number of methods I’ve seen. For each method, I want to call one particular procedure, many times at once, and often… Oh, and for free.

Why are we doing this?

  • For Testing: The whole point is to generate concurrent activity on the server. Testing with this kind of activity is one of the only ways to avoid resource contention issues.
  • For Demos: Concurrency issues are so common and so varied, that it’s not surprising how often we see demos that depend on this kind of generated database activity.
  • For Troubleshooting: This is my favorite reason. A concurrency issue can’t be fixed well unless it can be reproduced reliably on some test environment. That’s why the methods below have a spot on my troubleshooting toolbelt.

The whole idea is to get many workers active on SQL Server at once.

The exocomps were smart enough to exit on their own

For each method below, look out for the place where I specify these “parameters”

  • Number of virtual workers (50 in my example)
  • Number of iterations (for instance 10,000)
  • Connection string
  • Procedure to execute (s_DoSomething in my case)

One other thing to watch for is the overhead that this tool puts on the machine. Ideally, the method is suitable to run this from the same test environment that the SQL Server sits on. So I want my method to be fairly lightweight. This means that it’s best to handle iterations on SQL Server which cuts down on overhead associated with opening connections. So in most cases, instead of

exec dbo.s_DoSomething

I have

declare @i int = 0; while (@i < 10000) begin exec dbo.s_DoSomething; set @i+= 1; end

Notepad + DOS Method

I adapted this method from a clever trick I saw once. It was Paul Randal giving a demo on tempdb contention.  You can find a video of that demo by visiting this newsletter.
It’s a simple idea. You have two batch files, the first is called Run.bat:

echo off
sqlcmd -S MYSERVER\MYINSTANCE -E -Q "set nocount on; declare @i int = 0; while (@i < 10000) begin exec tempdb.dbo.s_DoSomething; set @i+= 1; end" > NUL
exit

and the second is called Run50.bat:

start run.bat
start run.bat
start run.bat
start run.bat
start run.bat
start run.bat
start run.bat
start run.bat
start run.bat
start run.bat
start run.bat
start run.bat
start run.bat
start run.bat
start run.bat
start run.bat
start run.bat
start run.bat
start run.bat
start run.bat
start run.bat
start run.bat
start run.bat
start run.bat
start run.bat
start run.bat
start run.bat
start run.bat
start run.bat
start run.bat
start run.bat
start run.bat
start run.bat
start run.bat
start run.bat
start run.bat
start run.bat
start run.bat
start run.bat
start run.bat
start run.bat
start run.bat
start run.bat
start run.bat
start run.bat
start run.bat
start run.bat
start run.bat
start run.bat
start run.bat

Click Run50.bat and boom: instant parallel activity. Now it is a little sloppy – it opens up 50 command windows – but it does the trick pretty nicely.

Using PowerShell

We should be able to do this in one line right? Yep. Adapted from a post from Kendra Little I came up with this:

foreach($worker in 1..50) `
{ `
  Start-Job `
    -ScriptBlock `
      { `
        Import-Module sqlps; `
        Invoke-Sqlcmd `
          -Query "set nocount on; declare @i int = 0; while (@i < 10000) begin exec tempdb.dbo.s_DoSomething; set @i+= 1; end" `
          -ServerInstance "MYSERVER\MYINSTANCE" `
          | Out-Null `
      } `
}

Except that I don’t think I got it quite right. Whatever I changed makes this method unsuitable.

The script schedules a job 50 times, but it takes about a minute just to schedule them all. Once they’re scheduled, the jobs take time to start and not enough of them work in parallel to generate the needed concurrent activity so I give this method a “skip”. If you’re really comfortable with powershell, maybe you can get this to work faster. If you can, let me know.

C#

This is my preferred method. It started out as the program I used to test upsert concurrency at http://michaeljswart.com/go/upsert but a friend at work showed me that .net 4.5 has some nifty new asynchronous methods that make it look nicer, perform faster and weigh lighter.

using System;
using System.Linq;
using System.Data.SqlClient;
using System.Threading.Tasks;
 
namespace AConsoleApplication {
    class Program {
 
        static void Main( string[] args ) {
 
            var cs = new SqlConnectionStringBuilder();
            cs.DataSource = @"MYSERVER\MYINSTANCE";
            cs.InitialCatalog = "tempdb";
            cs.IntegratedSecurity = true;
            cs.AsynchronousProcessing = true;
 
            string connectionString = cs.ToString();
 
            Task[] tasks = Enumerable.Range( 0, 50 )
                .Select( i => RunCommand( connectionString ) )
                .ToArray();
 
            Task.WaitAll( tasks );
        }
 
        private static async Task RunCommand(
                string connectionString
            ) {
 
            string sql = @"s_DoSomething";
 
            SqlConnection conn = new SqlConnection( connectionString );
            conn.Open();
 
            SqlCommand cmd = new SqlCommand( sql, conn );
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
 
            for( int i = 0; i < 10000; i++ ) {
                try {
                    await cmd.ExecuteNonQueryAsync();    
                } catch( Exception ex ) {
                    Console.WriteLine( ex.Message );
                }
            }
        }
    }
}

HammerDB

HammerDB (originally HammerOra) is a free utility that allows users to run benchmarks against various environments. Although it was originally built for running benchmarks on Oracle, the utility now works on Windows and for SQL Server (hence the name change). I was first introduced to the utility via Kendra Little (again):

Follow these links to learn how to use the tool for its typical function, running benchmarks. Then, once you know how to do that, it’s a quick step to repurpose the tool for your own concurrent activity. For example, replace HammerDB’s generated script with this one:

#!/usr/local/bin/tclsh8.6
package require tclodbc 2.5.1
database connect odbc "DRIVER=SQL Server Native Client 11.0;SERVER=MYSERVER\\MYINSTANCE;PORT=1433;TRUSTED_CONNECTION=YES";
odbc "set nocount on; declare @i int = 0; while (@i < 10000) begin exec tempdb.dbo.s_DoSomething; set @i+= 1; end"
odbc disconnect

It’s steady and lightweight and works really well.

SQLQueryStress

SQLQueryStress is a tool written by Adam Machanic that you can download and install for free.

SQLQueryStress

It’s fairly idiot proof and avoids a lot of the complexity of the other methods. For my own purposes, I want the utility to be lightweight. So I …

  • remember to turn off “Collect I/O Statistics”
  • remember to turn off “Collect Time Statistics”
  • Set “Number of iterations” to one.
  • Iterate in the query window, i.e. modify the query to call s_DoSomething 10,000 times.

My Ranking

Concurrent Activity Method Stars Notes
C# console app ★★★★½ It performs fastest and uses the least resources. Also, because I’m biased, it gets a small bump by not suffering from NIH issues.
HammerDB ★★★ Very fast (2nd fastest), but the interface is clumsy and modifications need tclsh experience <yuck> It’s best to use this for its intended purpose, as a benchmark tool.
Notepad and DOS ★★★ Quick and dirty and really fast. However, it’s still pretty clumsy. Interrupting a test part way through is difficult.
Powershell ★½ Yuck. I couldn’t get two runs that looked the same and it was near impossible to sort powershell cpu and memory pressure from SQL Server pressure.
SQLQueryStress ★★★★ It does the job well. It was a little difficult to interrupt a test. It also takes care to make it a lightweight framework.

Next Week

I’ll show some typical ways I define s_DoSomething.

Older Posts »

Powered by WordPress