3/30/2009Most people writing code about databases know that in rare cases, a SQL transaction can become deadlocked and killed by the server, but still be able to be retried successfully. (I will not be going into the theory in this post. More info here.) However, most developers forget to code for this because it is typically a very rare event. (If it happens often, you have other problems.) I've gotten plenty of questions through the years about how to code for this, and I thought I would write a quick post on how to do this in C# for reference.
What you need to do, is wrap all your calls to SQL commands with the following code:
bool victimOfDeadlock;
do
{
try
{
da.Fill(ds); //<--This is any line of code that executes a sql command that might become a victim of a deadlock.
victimOfDeadlock = false;
}
catch (SqlException sex)
{
victimOfDeadlock = sex.Number == 1205;
if (!victimOfDeadlock) throw;
}
} while (victimOfDeadlock);
This can/should be used around any call to your database, but not around commands that call functions that call the DB. In other words, only use it when things like .Fill, .ExecuteQuery, .ExecuteNonQuery, .ExecuteReader are actually called. It is a waste to place it around code that calls code that calls the DB, unless you are calling 3rd party code that you do not control AND know that retrying the statement won't have any unintended consequences. (If that isn't clear, I'm sure I'll be getting e-mails from a few of you. 8o) )
(Side note: Quite obviously, this would be a nightmare if you aren't using best practices and keeping 100% of your actual SQL calls inside a reusable data access layer in our code. Having a reusable DAL is critical to most every client side thing you want to do with SQL, unless you want to quickly descend into nightmare of maintaining the same code snippets repeatedly scattered throughout your entire code base.)
I deliberately kept this post rather brief, and skipped over a lot of theory. If a few people post that they want me to post the theory, I can write up more later. 10/20/2008This is a re-link to my HDC07 presentation. I've gotten a few e-mails from people who have looked here for my demo of how to get SQL Object (Stored Procedures, Functions, Table Definitions, etc.) to be automatically placed into source control without paying for any 3rd party tools. Apparently, my original posting of it wasn't very descriptive, and several people didn't realize it was the presentation I told them was on my site.
Everything is explained in this presentation, along with sample code. The only thing you need to provide is the scripts to move files placed on the file system into your particular flavor of source control. (sadly, there is no way for me to provide scripts for this to everyone. I don't have access to every source control flavor, and there is no way to do this in a general manner for all systems.) Everything else is done with Service Broker, SQL CLR, SMO and a Windows Service, with all the code provided for you in the sample code.
The end result will be that any time anyone runs a SQL command (via tool or command line) to create/update/delete a SQL object, you will get it automatically moved into source control, and the appropriate code reviewer will receive notification that things have changed. Say goodbye to undocumented changes that happen during deployments that make things work and "we'll get it entered into source control tomorrow. Seriously, we will. . ." 9/23/2008It took a little time, but I did find two work arounds for the NVARCHAR(MAX) issue I posted about earlier. Both of them require that you grab the entire string without streaming, but I've seen this work for strings >>7MB in size without any gross performance impacts.
If you are using Visual Studio to deploy, you need to use a SqlString parameter type and add a SqlFacet attribute to your function parameter and set MaxSize to -1. Here is a sample of what that looks like:
using System;
using System.Collections;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public
class
UserDefinedFunctions
{
[SqlFunction(FillRowMethodName = "GetRow", TableDefinition = "Item NVARCHAR(50)", DataAccess = DataAccessKind.None,
SystemDataAccess = SystemDataAccessKind.None)]
public
static
IEnumerable TableFromCSL([SqlFacet(MaxSize = -1)] SqlString commSeparatedList)
{
//NOTE:In SQL 2005, you could use SqlChars datatype to input a nvarchar(MAX) datatype.
// In SQL 2008 this no longer works, and the SqlString, by default truncates at 8000 chars.
// You must use the (ugly) method of using a SqlFacet Attribute on the parameter, and cannot use streaming.
//HACK: Reads everything in one chunk instead of using more robust streaming behavior
foreach (String item in commSeparatedList.Value.Split(','))
yield
return item;
}
public
static
void GetRow(object obj, out
SqlString Item)
{
Item = new
SqlString(obj.ToString());
}
}
- Your other method (which is effectively what VS deployment does when you add that attribute) is to turn off automatic deployment and manually write scripts to set the parameter type to NVARCHAR(MAX) when you manually deploy the function with CREATE FUNCTION or CREATE PROC.
9/13/2008I have gotten a ton of requests for this script, and instead of having everybody download my entire Becoming The SQL Guy
demo/slides/scripts, I am going to post the script here to make it easier for people's copy and paste pleasure:
DECLARE @Table NVARCHAR(128),
@Column NVARCHAR(128),
@sqlString NVARCHAR(512)
DECLARE c CURSOR
FOR
SELECT t.name TableName,
c.name ColumnName
FROM
sys.tables t
INNER
JOIN
sys.columns c ON t.object_id
= c.object_id
WHERE c.system_type_id IN
( 175, 167 )
--^^^^^^^ YOU MUST EDIT THIS FOR THE TYPES OF DATA YOU WANT TO SEARCH
--Get values you want from SELECT name, system_type_id FROM SYS.Types
OPEN c
FETCH
NEXT
FROM c INTO @Table, @Column
WHILE
@@FETCH_STATUS
= 0
BEGIN
SET @sqlString =
'IF EXISTS(SELECT '
+ @Column +
' FROM '
+ @Table
+
' WHERE cast(['
+ @Column
+
'] as varchar(50)) like ''%waldo%'') select '''
+ @Table -- <<< EDIT VALUE YOU ARE LOOKING FOR HERE
+
''' as TableName, '''
+ @Column +
''' as ColumnName '
;
PRINT
'Executing: '
+ @sqlstring
EXEC ( @sqlString )
FETCH
NEXT
FROM c INTO @Table, @Column
END
CLOSE c
DEALLOCATE c
9/12/2008
I've been a little bit frustrated working on my demos for my upcoming HDC presentation because of a bug that I have found in SQL 2008. Apparently the ability for a CLR function to take a string larger than 4000 characters is broken. When I pass in a SqlChars (NVARCHAR(MAX) from the TSQL side) argument into a CLR function and try to do either a .Read() or a .ToSqlString() method an InvalidOperationException is thrown. I am still looking for a fix, workaround or at least a reason why they would've changed this in SQL 2008. I will post more info when I find a workaround or hear back from the SQL team as to the cause.
Full details of the exception that gets thrown are as follows:
An error occurred while getting new row from user defined Table Valued Function :
System.InvalidOperationException: Accessing members of an object from a wrong thread.
System.InvalidOperationException:
at System.Data.SqlServer.Internal.ClrLevelContext.XvarProxyRead(CClrXvarProxy* pXvarProxy, UInt64 iPosition, Byte* pbBuffer, UInt32 cbCount)
at System.Data.SqlServer.Internal.ClrLevelContext.System.Data.SqlServer.Internal.IXvarProxyAccessor.XvarProxyRead(CClrXvarProxy* , UInt64 , Byte* , UInt32 )
at System.Data.SqlServer.Internal.StreamOnBlobHandle.Read(Byte* pbBuffer, UInt64 offset, UInt32 count)
at System.Data.SqlServer.Internal.XvarWlobStream.Read(Char[] buffer, Int32 offset, Int32 count)
at System.Data.SqlTypes.SqlChars.Read(Int64 offset, Char[] buffer, Int32 offsetInBuffer, Int32 count)
at UserDefinedFunctions.<TableFromCSL>d__0.MoveNext()
The only (incredibly lame) work around I know of is to find a SQL 2005 instance to host your CLR functions that need to input more than 4000 character strings. (SQL2005 has no issues with this functionality.) Not much of a work around, I know. If anyone else knows of a workaround to this, please let me know.
UPDATE: I have found work arounds!
9/3/2008I'm copying this from something I found on Chris Crowe's Blog. I'm copying it because I can no longer access his blog and am having to rely on Google's cache of the page to get the info:
You may find that when installing SQL Server 2008 you receive the follow error during installation.
"The SQL
Server
2005
Express
Tools are installed. To continue, remove the SQL
Server
2005
Express
Tools."
A couple of things to do:
Remove SQL Server 2005 Express Tools
Remove Red-Gate SQL Prompt if installed.
UPDATE:
SQL Prompt v3.9 (available for download from www.red-gate.com) addresses this problem and no longer blocks the installation of SQL Server 2008. Also, SQL Prompt v3.9 supports SQL Server 2008.
Tanya Joseph
Red Gate Software Ltd
10/16/2007Just a quick post while I'm hammering out the last details to my HDC presentation: I learned something new about service broker that I thought should get posted. Admittedly, it probably will not ever be useful in a production environment, but it may certainly help out some presenter somewhere. If you use service broker on a SQL server that uses Windows authentication, and you are on a domain machine that is NOT currently connected to the network, service broker will send all messages to the Transmission queue (the undeliverable queue) until you reconnect to the network. Quite obviously, production machines are not likely to need to disconnect from the network and be unable to communicate with the domain controller. But, laptops that you plan on using for demos at conferences do. And that has led me to panic for the last couple hours to determine why my demo that worked so pristinely for so many test runs suddenly hurls when I took my laptop off-network to practice my demo.
So, for the record, SQL service broker cannot deliver messages if it is using Windows authentication and cannot connect to the domain controller.
Once HDC is over, I'll get back to writing Part 2 of CTEs and performance, but I thought it would be useful post this for any other poor presenters who might be struggling with the same issue. 9/18/2007When I first read about Common Table Expressions in SQL 2005, I saw mentioned that they could increase performance, because the query optimizer could take advantage of existing indexes while a SQL 2000 style temp table could not. For the first several months I used CTEs, I never really investigated this idea. I just assumed that since CTEs were new, they would be faster. (Pretty bad assumption on my part.) After spending all the time that I have on optimizing my production database, I've found that CTEs generally change performance anywhere from a 90+% increase in speed to a order of magnitude decrease in speed.
First off, I did find some situations you could just about guarantee a healthy speed increase: recursive queries. The application I have been optimizing has a lot of tree structured data. I had several heavily called user defined functions and stored procedures that were originally written in SQL 2000 with while loops and temp tables. Moving them over to recursive queries showed consistent performance increases, from 60-90% increase in speed.
Beyond that, CTEs were hit and miss as far as performance goes. More on that in Part 2 9/11/2007I've had a lot of SQL thoughts over the last few weeks that I thought I ought to get written down. I've been working on a large client server application that was going through a hardware upgrade. The performance of the application before the upgrade was a tiny bit subpar and the company had made a huge financial commitment to shore up the application. There was one unforeseen issue: The company policies dictated that the new SQL server 2005 cluster would have its production environment in its Minneapolis and its Test cluster in Arizona, instead of having the database local to the user base. We quickly discovered in the test environment that the performance hit of the added latency of having the server clusters in other cities dwarfed the performance increase of having a faster dedicated server cluster over our old shared, 4-CPU single box locally.
So, the heat was on. The execs had spent into the 7 figures replacing hardware and paying the server groups to install and configure them, and the net result was a much slower configuration. Suddenly, all our the SQL optimizations that we had always thought about doing, but never had time to do were top priority. I went to work for several weeks trying every trick I've learned about SQL 2005 and testing for improvements. To my surprise, while many of the new tricks increased performance, other did nothing for performance, and sometime radically slowed down queries. For the next couple of weeks, I am going to try to discuss as many of the main points that I found out in a series of blog posts.
As a quick teaser, I found at least one SQL 2005 technique that was just a performance abomination: CROSS APPLYs. In four separate instances I took queries that were running in the 10-20 second range using cursors to perform the operation of applying a function result row by row. In all four of them, the performance tanked into the 1-5 minute range. I have no idea how these beasts can be as harmful to performance as they proved to be. But, sadly, it looks like that if performance is important, we will be leaving CROSS APPLYs in the classroom, and will continue to write SQL 2000-style cursors and temp tables to apply the function results row by row. 7/9/2007By popular request, I'm going to post my mnemonic device I routinely use when I work with Service Broker. Until a few people pointed it out to me, I didn't realize that I always write it on the whiteboard when I give presentations, and therefore it isn't in any of my downloadable presentations. So here it is:
THE QUESTION: With so many various components needed to make a service broker implementation function, how do you remember all of the objects that you need to setup?
HOW TO REMEMBER: Imagine you are a 20-something bachelor (This shouldn't be a stretch for many of us. . . .) One day, the unthinkable happens: You get a date! Beyond that, she agrees to a second date, and a third! You find that you are suddenly officially "dating." And then the miraculous event happens: You walk into your (normally filthy) computer room, and she has cleaned it for you!!! So Quickly She Cleans My Computer Room Regularly!
THE ANSWER:
- Server: Make certain that the server has service broker enabled on the database. Although it is enabled by default, it is fairly common to see DBAs and system administrators who have followed various internet security guides to turn off every single feature of the database. I've even downloaded various demo databases that have service broker turned off. To check that it is on, do a SELECT * FROM sys.databases and check the is_broker_enabled value and make sure it is set to 1. If not, execute an ALTER DATABASE database_name SET ENABLE_BROKER from the Master database. Make sure the database has no active connections when you execute the command.
- Queue: You need to create a queue object with a CREATE QUEUE command.
- Service: You need to make a service to deliver messages to the queue. A server always services a single queue, but theoretically you could have multiple services on a single queue. In practice, I've never seen a reason to make multiple services on a single queue. They are made with CREATE SERVICE statements.
- Conversations: Services talk to each other via conversations. They are created with CREATE DIALOG CONVERSATION commands, and ended with END CONVERSATION commands.
- Message Types: Conversations consist of messages. These messages are defined by Message Types that are created by CREATE MESSAGE TYPE commands.
- Contracts: The types of message types that can be used in a conversation is dictated by contracts. They are defined by CREATE CONTRACT
commands are tied to services when they are created. (You might have noticed that in the docs for CREATE SERVICE)
- Receivers: You need something to read the messages from the queue. It can be a windows service, a console app, a stored procedure, etc. Typically, it uses the RECEIVE command to read the messages on the queue.
- Routes: If you are using the service broker between databases, between instances, or between computers, you need to specify the routes for the messages to take. If you are going between databases, you simply need to do a CREATE ROUTE command on each database. If you are going between databases, don't forget to CREATE ENDPOINT
on each instance, and CREATE ROUTE in each instances MSDB database, because all incoming service broker messages are always received by the MSDB database and then routed to the appropriate database from the routing table in that database. And, of course, if you are going between machines, make sure that appropriate ports are opened in all involved firewalls.
Quite obviously, this little mnemonic device is not a comprehensive guide to service broker, but it has helped me with remembering what I need to set up tremendously. And, judging from the e-mails I've received from people I've given presentations to, it has helped some of you guys, too.
As you go through the check list, don't forget that for several new features (Event Notifications, SqlDependancy, etc.) several of the above are automatically taken care of for you, so you don't have to do much to set them up. |
|
|
|
|