The SEND command was introduced to SQL Server when SQL Server 2005 was released. It’s kind of like the INSERT command except that it is used only with the SQL Server Service Broker. With the service broker you put data into a queue instead of a table. Insert of inserting a command into the queue, you send a message to the queue, kind of like sending an email to someone else. The service broker uses this same kind of idea (not really, but email is something that everyone gets, so it’s a half way decent anology).
When you use the SEND commad you also have to use the BEGIN DIALOG command to get everything ready. So before you can send the message you have to start a dialog, which then starts a conversation, with the service which will be doing the heavy lifting. This is done with the BEGIN DIALOG command as shown in the code sample below.
After you have the dialog handle in the @dialog_handle variable you use this handle to send the actual message to the conversation which was started when you begin the dialog. In our example below we are just going to send the contents of the sys.tables DMV to the the queue.
DECLARE @xml AS XML
DECLARE @dialog_handle AS uniqueidentifier
SET @xml = (SELECT * FROM sys.tables FOR XML AUTO)
BEGIN DIALOG @dialog_handle
FROM SERVICE [tcp://codecamp/AW/sample_send_service]
TO SERVICE 'tcp://codecamp/AW/sample_receive_service'
ON CONTRACT [tcp://codecamp/AW/sample_contract];
SEND ON CONVERSATION @dialog_handle
MESSAGE TYPE [tcp://codecamp/AW/sample_messagetype]
Confused yet? Shortly there will be a Back To Basics post about the RECEIVE command which is how you “read” the message. At the end of that post I’ll include a link to some sample code which I’ve used a few times in my sessions which should help wrap this all up.
I know that this is way outside the normal content for my blog, but oh well.
A friend at work has entered a comic book cover contest. Needless to say he’s very talented. His picture is below. The reason that I’m posting this is it’s a public voting contest, and he needs your help. The winner of the contest will have their cover published as one of the covers of the comic when it’s released. His name is Jamie Tyndall, you’ll find his artwork second from the bottom of the list.
Myself and many other people have been saying for quite a while now that you need to align your disks before putting data on them. I know have some information on how you can figure out just how much potential performance you are loosing by not aligning.
Before we can begin to figure this out we need to know what the average work load for the disk is going to be. In the SQL Server world this is easy. SQL Server does everything in 8k pages within 64k extents. Each time it needs to read from the disk it reads the 64k extent from the disk and each time it writes to the disk it writes the 64k extent. So our data size is 64k.
We take this number and divide by 64. So in our case 64/64 = 1. 1 as a percentage is 100%, so 100% of our data reads and writes are requiring two physical reads or writes.
If you are in the exchange world everything is done in 8k reads and writes. So in this case 8/64 = 0.125 or 12.5% of the reads and writes are requiring two physical reads or writes.
Now for SQL Server just because we are doubling the number of operations doesn’t mean that by fixing this you will double your disk speed. What it means is that if your disks are running at 100% utilization you can probably reduce your disk load by 50%. But if your disk utilization is 30% your disk activity won’t be any faster as your disks are not running at capacity. Will you see a performance improvement, yes. Will it be as high as if your disk was at 100%, no. Should you still fix the alignment problem? Yes.
To fix the problem isn’t easy. You have to remove all the data from the disk, and delete the partition, then recreate the partition using the DISKPAR.EXE (Windows 2000) or DISKPART.EXE (Windows 2003/2008) with the ALIGN=64 setting. To remove the data from the disk you will either need to migrate to a new disk within the server, or backup the database, fix the alignment then restore the database.
Routes are only needed when sending service broker messages from one server to another. They define the SQL Server and TCP Port which the sending SQL Server will connect to in order to deliver the message. If you are sending the message to a mirrored database, then you can specify the mirror as well as the primary database.
If you needed to you could setup three or more servers in a chain and have them forward the messages from one server to another. This would be handy if you needed to get messages through firewalls and the source and destination servers were not permitted to talk to each other. The only requirement to do this is that one server in each conversation must be a paid for edition of SQL Server. In other words two SQL Express instances can not send messages to each other directly. Those messages much be forwarded through a Workgroup, Standard, or Enterprise instance.
Before you can create a Service Broker Route, you have to have an endpoint on the remote machine. For this purpose we’ll assume that the Service Broker endpoint was created on port 1234. Our local server is SQL1 and the remote machine will be SQL2. The only other piece of information that you need to know is the Service Broker Instance GUID from SQL2. This is found in the service_broker_guid column of the sys.databases DMV on server2 (fifth column from the right in SQL 2005). If the GUID is all 0s then you need to enable the service broker by using the ALTER DATABASE Command.
The CREATE ROUTE syntax is very easy.
CREATE ROUTE RouteName
WITH SERVICE_NAME = 'ServiceName',
BROKER_INSTANCE = 'ae8505fa-b84d-4503-b91f-3252825ccf09', /*Use your GUID here*/
If your target is using database mirroring set the MIRROR_ADDRESS to the name and port number of the mirror. This way in the event of a fail over the sending server can continue to deliver messages.
If you need the route to expire on a specific date, for example you are sending data to a partner and you want to automatically stop sending them the messages when the contract ends, add the LIFETIME parameter with the number of seconds until the route expires. If the LIFETIME is omitted the route will never expire.
Services are used to bind contracts to queues. They are also used to database to database, or server to server routing of messages via routes (we’ll talk about them later). Unlike most other objects within SQL Server, the name of the service is case sensitive, no matter what collation you have set for your database. When using server to server queueing the service names must be identical, including case as SQL Servers uses a hash of the service name to locate the correct service.
(Service names are not actually case sensitive if you arne’t using routing, but if you are going to start routing the messages later it’s better to start off getting them in the correct case.)
The syntax to create the service is pretty straight forward.
CREATE SERVICE ServiceName
ON QUEUE QueueName (ContractName)
If the service will only be the initiator then the contract name can be omited if preferred. If you want to create a multi-user contact you can specify additional contracts by simply making a comma seperated list of contract names.
CREATE SERVICE ServiceName
ON QUEUE QueueName;
CREATE SERVICE ServiceName
ON QUEUE QueueName (ContractName1, ContractName2);
A ran across a bit of a problem when upgrading my SQL 2008 CTP 5 (November 2007) SSIS Packages to SQL 2008 RC0. Apparently Microsoft has changed the way that they handle the script tasks within the SSIS packages. Because of this when I edit all of the script tasks within my SSIS package the scripts were all blank.
The official fix from Microsoft is to install the older CTP version that you edited the scripts in on another machine and open the un-updated version of the SSIS package on that machine, and copy the code for the scripts into the RC0 version of the SSIS package.
The only object which I’ve had to do this on was a .NET Script Task. I had this problem when going from CTP 5 to CTP 6 as well as from CTP 5 to RC0. This will not effect migrating from SQL 2005 to SQL 2008 as SQL 2005 and SQL 2008 CTP 5 do not use the same scripting engine in the back end.
Microsoft has just released SQL Server 2008 RC 0. This release should be pretty close to the final version expected out later this year.
I’ve just published another tip over on SearchSQLServer.com. This one is titled SQL Server memory configurations for procedure cache and buffer cache.
To determine how much memory your SQL Server database uses for buffer cache and procedure cache, factor in the SQL version you’re using, whether it’s x86, x64 or Itanium and the amount of memory allocated to the SQL Server instance. I talk about how the system configures memory and how it allocates the amount of buffer and procedure cache that’s available.Denny
While working on a new part of our production system we begun getting the following error message back from the SQL Server.
Server: Msg 8623, Level 16, State 1, Line 1
Internal Query Processor Error: The query processor could not produce a query plan. Contact your primary support provider for more information.
Now I’ve seen this error before, but it was supposed to have been fixed in Cumulative Update 3 for SQL Server 2005 SP 2 but apparently it hasn’t been. I’m currently running build 3239 which is Cumulative Update 7. (I’m running the x64 edition.)
The only difference between my query and what’s documented in the KB article is that I was doing a basic insert statement.
INSERT INTO Table3
There are obviously no complex queries being run here. However I did have relational integrity enabled through foreign keys. I ended up having to remove the foreign key constraints from this table for the insert statement to work correctly.
Hopefully someone finds this info useful.
You can follow along with the Microsoft development teams progress on Microsoft Connect. It’s feedback number 332815.