Saturday, June 27, 2009

900 Bytes - Index Limit

SQL Server 2005 and 2008 place a limit on the maximum key size when creating a non-clustered index. The current limitation is 900 bytes. This means that the total size of the keyed columns included in the index definition cannot exceed 900 bytes. SQL Server will issue an error or a warning at the time of index creation depending on the columns specified.

Fortunately there are some options available to overcome this limitation.

INCLUDE COLUMNS

The first is to leverage the INCLUDE statement in CREATE INDEX. This feature enables us to remove some of the columns in the index key but still allows them to participate in the index. SQL Server places the data of these columns in the leaf level of the index. The benefits here are:
1) The Index can fully cover a query if the SELECT statement contains columns that could otherwise not participate in the index key.
2) The overall size of the index would be reduced.

HASHBYTES

The second option would be to add a column to the table of interest that would contain the hash value of the column to be searched on. This new column can then be indexed to provide better performance for searching. SQL Server provides a function, HASHBYTES, that returns the hash of the input provided. Algorithms supported by HASHBYTES include MD2, MD4, MD5, SHA, and SHA1. More detail on hash functions can be found here from RSA Labs

Updating the new column with the hash value can be accomplished like this:

UPDATE Table XYZ set ColA_Hash = HASHBYTE('SHA1',ColA)

With an index in place, filtering in the WHERE clause using the HASHBYTE of the interested text should result in improved performance.

WHERE
HASHBYTE('SHA1',text) = ColA_Hash

The use of HASHBYTES is an alternative but it should be noted that this will not provide a solution to searches filtered using the LIKE operator.

Thursday, June 18, 2009

HOW TO: Enable Service Broker Using SBA

The first step in creating Service Broker applications is to make sure the Service Broker instance for the database is enabled. This means that message delivery is activated and available.

To enable the instance using Service Broker Assistant, right-click 'Service Broker' in the object explorer. Select 'Configure Service Broker...'. The configuration window will open and you will see 'Enable Service Broker' as the first option under the 'Service Broker Activation' section.



If 'Enable' is not selected, select it and click 'OK'. That's it. Service Broker is now enabled for that database.

Note: If the database has been restored from a file, there is the possibility that it will try to use the same Service Broker instance GUID. You will get an error message similar to the following:

The Service Broker in database [DB_NAME] cannot be enabled because there is already an enabled Service Broker with the same ID.

If this happens, choose the 'Reset Service Broker' option to obtain a new GUID, then try again to enable the Service Broker.

Tuesday, June 16, 2009

Service Broker Assistant Event Logging Example

The 'Event Logging' example provides a simple application that registers for, receives, and archives event notification messages.

Enable Service Broker

Check that the Service Broker is enabled in the database by selecting 'Configure Service Broker...'


Create Queue

Next, create a queue to store the notification messages. Name it 'NotifyQueue' and set it's 'Status' to available selecting the check box.


Create Service

Now, we can create a service, which is the component that ties together a contract and a queue together for creating the application. Name the service 'NotifyService' and select the queue created above. Choose the 'PostEventNotification' contract, a default contract that is in each database.


Create Log Tables

For this example, we will use 2 tables. The first holds event notification information in relational form:

CREATE TABLE [dbo].[LoggedEvents] (
EventNumber INT IDENTITY PRIMARY KEY,
EventType NVARCHAR(256),
EventTime DATETIME,
LoginName sysname NULL,
UserName sysname NULL,
ServerName sysname NULL,
DatabaseName sysname NULL,
SchemaName sysname NULL,
ObjectName sysname NULL,
ObjectType sysname NULL,
TSQLCmdText NVARCHAR(MAX) NULL
) ;


The second stores the event type and time along with the entire raw XML message:

CREATE TABLE [LoggedEventsXML] (
EventNumber INT IDENTITY PRIMARY KEY,
EventType NVARCHAR(256),
EventTime DATETIME,
EventData XML
) ;


Create Activation Procedure

The stored procedure that will be activated with each event notification is next.

CREATE PROCEDURE [dbo].[LogEventsProc]
AS
SET
NOCOUNT ON;
DECLARE @message_body XML,
@message_type_name NVARCHAR(256),
@dialog UNIQUEIDENTIFIER ;
-- This procedure continues to process messages in the queue until the
-- queue is empty.
WHILE (1 = 1)
BEGIN
BEGIN TRANSACTION
;
-- Receive the next available message
WAITFOR (
RECEIVE TOP(1) -- just handle one message at a time
@message_type_name=message_type_name, --the type of message received
@message_body=message_body, -- the message contents
@dialog = conversation_handle -- identifier of the dialog this message was received on
FROM NotifyQueue
), TIMEOUT 2000 ; -- if the queue is empty for two seconds, give up and go away
-- If RECEIVE did not return a message, roll back the transaction
-- and break out of the while loop, exiting the procedure.
IF (@@ROWCOUNT = 0)
BEGIN
ROLLBACK TRANSACTION
;
BREAK ;
END ;
-- Check to see if the message is an end dialog message.
IF (@message_type_name = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog')
BEGIN
PRINT
'End Dialog received for dialog # ' + CAST(@dialog AS NVARCHAR(40)) ;
END CONVERSATION @dialog ;
END ;
ELSE
BEGIN
-- Extract the event information using XQuery.
-- Use XQuery to extract XML values to be inserted into the log table
INSERT INTO [dbo].[LoggedEvents] (
EventType,
EventTime,
LoginName,
UserName,
ServerName,
DatabaseName,
SchemaName,
ObjectName,
ObjectType,
TSQLCmdText
)
VALUES
(
CAST(@message_body.query('/EVENT_INSTANCE/EventType/text()') AS NVARCHAR(256)),
CAST(CAST(@message_body.query('/EVENT_INSTANCE/PostTime/text()') AS NVARCHAR(MAX)) AS DATETIME),
CAST(@message_body.query('/EVENT_INSTANCE/LoginName/text()') AS sysname),
CAST(@message_body.query('/EVENT_INSTANCE/UserName/text()') AS sysname),
CAST(@message_body.query('/EVENT_INSTANCE/ServerName/text()') AS sysname),
CAST(@message_body.query('/EVENT_INSTANCE/DatabaseName/text()') AS sysname),
CAST(@message_body.query('/EVENT_INSTANCE/SchemaName/text()') AS sysname),
CAST(@message_body.query('/EVENT_INSTANCE/ObjectName/text()') AS sysname),
CAST(@message_body.query('/EVENT_INSTANCE/ObjectType/text()') AS sysname),
CAST(@message_body.query('/EVENT_INSTANCE/TSQLCommand/CommandText/text()') AS NVARCHAR(MAX))
) ;
-- Insert the message body as XML into the loggedeventsXML table
INSERT INTO [dbo].[LoggedEventsXML] (
EventType,
EventTime,
EventData
)
VALUES
(
CAST(@message_body.query('/EVENT_INSTANCE/EventType/text()') AS NVARCHAR(256)),
CAST(CAST(@message_body.query('/EVENT_INSTANCE/PostTime/text()') AS NVARCHAR(MAX)) AS datetime),
@message_body
) ;
END ;
COMMIT TRANSACTION ;
END ;



Alter Queue

Now, we have to go back and alter the queue we created to use that procedure. Select the 'Activation' check box. The activation options are now enabled.

  • Select the stored procedure, LogEventsProc
  • Set the 'Max Queue Readers' to 2
  • Select 'Execute as Self'
  • Check 'Activate stored procedure'



Click 'OK'

Create Event Notifications

With Service Broker, we can setup event notifications to alert our logging application when certain events take place. We can choose from server, database or queue level events. In this example, we will choose the server level event 'CREATE_DATABASE' and the database level event 'CREATE_TABLE'.

Right-click 'Service Broker' in the SSMS object explorer and select 'Create Event Notifications...'


Create the server level event 'CreateDatabaseNotification'. Select the 'Server' scope. Click the '...' next to 'DDL event type' and select 'CREATE_DATABASE' event.



Click the '...' next to 'Remote service name' and select the service we created earlier, 'NotifyService'. You can leave the 'Broker service GUID' as 'current database', or browse to the Service Broker instance where the service was created.

Click 'OK'.

Follow the same steps to create the database level event, but choose the 'CREATE_TABLE' event instead.



You will now see the 2 event notifications listed in the grid below.


We are now ready to test the application. Execute a script to create a database and a table. Nothing fancy, we just want to see the results of the application.


Run 2 select statements to view the contents of the tables.

The first result set is from the relational table log and the second is the message orientated log (with the raw XML posted below it.)




<EVENT_INSTANCE>
<EventType>CREATE_TABLE</EventType>
<PostTime>2009-06-15T19:37:42.313</PostTime>
<SPID>51</SPID>
<ServerName>IRON\CARBON</ServerName>
<LoginName>sa</LoginName>
<UserName>dbo</UserName>
<DatabaseName>Application</DatabaseName>
<SchemaName>dbo</SchemaName>
<ObjectName>TestNotificationTable</ObjectName>
<ObjectType>TABLE</ObjectType>
<TSQLCommand>
<SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" />
<CommandText>CREATE TABLE TestNotificationTable (Column1 int, Cloumn1 int)</CommandText>
</TSQLCommand>
</EVENT_INSTANCE>

Monday, June 8, 2009

Now Available - Service Broker Assistant Standard Edition

We are excited to announce the release of our latest product Service Broker Assistant. Initially available in Standard Edition, Service Broker Assistant is an SSMS add-in that eases the development of creating SQL Server Service Broker objects. Common tasks normally accomplished through T-SQL can now be created through an easy to use UI that is made available directly in object explorer.

Service Broker Assistant Standard Edition is available for $99 and is compatible with both SQL Server 2005 and 2008. We are currently offering a 20% discount for the next 7 days. Enter "LaunchDiscount" in the Discount Code box at checkout to receive 20% off Service Broker Assistant.

With Service Broker Assistant you can accomplish the following tasks:

Service Broker Configuration
Create and Alter:

Message Types
Contracts
Services
Queues
Routes
Service Endpoints
XML Schema Collections
Remote Service Bindings

To learn more about Service Broker Assistant:
Service Broker Assistant

Here are some sample screen shots:

Configuration





Create Contract




Create XML Schema Collection


The professional version of Service Broker Assistant is coming soon. To learn more about the added features in professional edition check out our feature comparison page.

Monday, June 1, 2009

Sneak Peek: Service Broker Assistant

Below are some screen shots of the soon-to-be-released Standard edition of SBA.

Creating a Service Broker Endpoint...



Adding A Route...


New Service...