Service Broker–WAITFOR and Activated Queues

We’ve implemented Service Broker for handling a small portion of the transactions we want to catch and apply into our Operational Data Store. Mostly we wanted to make sure we accounted for actual DELETE operations and handled those records correctly. We can use Change Data Capture at this time on our source systems and Service Broker seemed to fit the bill. We still handle the remaining inserts and updates through SSIS.

While running Service Broker, we noticed a pretty constant CPU hum on the receiving server. That seemed odd, but we had a lot of trouble tracking it down. Regular Profiler traces didn’t show any running TSQL, “normal” Service Broker traces weren’t showing much, either. We just saw SQL Server running at a pretty constant 20% even with nothing seemingly happening.

I want to give a public thank you to Mark Hill (twitter) for doing a little extra digging and catching the root cause of this. In our tinkering with Service Broker, Queues, Activation, Stored Procs, and such we missed some very important information along the way.

We had written a stored procedure that would be used on the Receiving side of our Service Broker queue to run as the “Activated” proc. Inside the stored procedure, we included a WHILE loop to process anything that was in the queue. If nothing, exit.  That seemed pretty simple – if something comes into the queue, process it and stop when nothing is left.  End of story, right?

Sadly, this is where we missed a small, but important, fact about Service Broker. If you have an activated stored procedure that doesn’t use the WAITFOR command, Service Broker, we execute that stored proc as many times as possible looking for something to process. We looked at it, turned off the Activation on the queue and saw the CPU drop to nothing. We re-activated the queue and saw the CPU shoot up again. We tweaked the stored procedure after that and added a WAITFOR command with a timeout of 60000. After doing this, we saw the stored procedure run to process everything in the queue, then go idle, which was exactly the intended behavior in the first place.

I’m not going to pretend to be an expert on Service Broker. We had tried to code with the intention of being able to use this stored procedure as an Activated stored procedure or as one called from an external process to work through the queue. While that may be possible, it was unnecessary in our actual usage. Adding a WAITFOR command around our queue processing eliminated our extra, non-essential CPU usage and stopped trying to execute a stored procedure for no reason.

Resources

Pro SQL Server 2008 Service Broker ( Amazon | Apress )

WAITFOR (Also see this performance article – it’s the little details that get you)

Quickly script permissions in SQL 2005+

I wanted an easy way to generate a script to recreate all DB object and schema permissions for a database. Searching online, I found several different examples, but none quite did everything I needed. I put this together from some examples and added it to my toolkit. This script will generate the appropriate T-SQL to grant permissions to DB Objects and Schemas.

 

SELECT
state_desc + ' ' + permission_name +
' on ['+ ss.name + '].[' + so.name + ']
to [' + sdpr.name + ']'
COLLATE LATIN1_General_CI_AS as [Permissions T-SQL]
FROM SYS.DATABASE_PERMISSIONS AS sdp
JOIN sys.objects AS so
     ON sdp.major_id = so.OBJECT_ID
JOIN SYS.SCHEMAS AS ss
     ON so.SCHEMA_ID = ss.SCHEMA_ID
JOIN SYS.DATABASE_PRINCIPALS AS sdpr
     ON sdp.grantee_principal_id = sdpr.principal_id

UNION

SELECT
state_desc + ' ' + permission_name +
' on Schema::['+ ss.name + ']
to [' + sdpr.name + ']'
COLLATE LATIN1_General_CI_AS as [Permissions T-SQL]
FROM SYS.DATABASE_PERMISSIONS AS sdp
JOIN SYS.SCHEMAS AS ss
     ON sdp.major_id = ss.SCHEMA_ID
     AND sdp.class_desc = 'Schema'
JOIN SYS.DATABASE_PRINCIPALS AS sdpr
     ON sdp.grantee_principal_id = sdpr.principal_id
order by [Permissions T-SQL]
GO

I know that a lot of you may have something that does this already, but figured it can’t hurt to have another snippet available to generate permission statements. If you make any enhancements, let me know. I’d love to keep this up to date so it’s helpful to others.

SQL 2008 R2 – SSIS Data Export Oddities

I don’t know if anyone else has had these sorts of issues, but we work quite a bit with conversions of various source data into our SQL Server database. I recently was handed a backup from SQL 2008 R2. I figured that wasn’t a huge problem. Restore onto my local test box, use SSIS to push over to a SQL 2005 instance so it can work its way to Production. No big deal, right?

The first time I tried this using the SQL Native Client 10.0 on both sides, I ran into an errors with the mappings. I can’t quite figure that one out because from what I can see, there’s nothing at all in the source database using a feature that would not be available in SQL 2005.

I tried exporting to an MS Access MDB file. No luck from SQL 2008 R2 with the default settings because of an invalid size error on a varchar to longtext conversion.

I was able to successfully export using SNAC from SQL 2008 R2 to a SQL 2008 instance. So from there I thought I could upload directly to the SQL 2005 instance. No such luck using SNAC – again. I was able to export from there to an Access MDB file and pull that into SQL 2005.  I don’t quite get why that worked, but figure I’ve got some odd mapping in the XML files defining the defaults that I’m missing.

 

I was recently asked to repeat this task and figured there had to be a better way. This time I restored the DB to my R2 instance – no issues. I then used the SNAC client to access my R2 instance, but set up an OLEDB connection to my SQL Server 2005 target. For some reason, the mappings are just different enough that this worked with no issues. I was able to transfer directly. I now wish I’d tried that the first time, but I’d already blown a couple of hours on it.

If anyone else has encountered that and knows why SNAC from 2008 R2 doesn’t seem to work directly to SNAC on 2005, I’d love to know the reasons as well. If not and you encounter something similar, maybe trying the OLEDB connections will work for you.

SQL 2008 Merge and PK/FK Constraints

We ran into this issue a while back.  SQL 2008 has a “feature” that will not allow inserts into a table on either side of a Foreign Key relationship.  If you attempt to insert into a table involved in the FK relationship, you get an error something like:

The target table 'TableName' of the INSERT statement cannot be on either side of a (primary key, foreign key) relationship when the FROM clause contains a nested INSERT, UPDATE, DELETE, or MERGE statement. Found reference constraint 'ConstraintName'.

This is documented in Connect 435031. There is one workaround documented in the ticket – drop the FK constraint, run the MERGE query, then re-create the constraint. Happily, this bug is still open as the requirement to drop a constraint just to run a MERGE statement kind of defeats the purpose of putting a constraint on the table in the first place. While dropping the constraints will work, there is another possible workaround. I was able to run this without any issues while leaving my FK Constraint in place.

1. Create a temp table that matches the definition of the table into which you want to perform your insert.
2. Instead of inserting into your base table like you normally would in a merge, write your code to INSERT #Tablename at the beginning of the MERGE process. You’ll still use your main table and staging table inside the MERGE INTO … USING query.
3. After the Merge, insert into the main table using the values in your Temp table.

Here’s a shell of an example. Line 11 below contains the main difference to the MERGE query from a “normal” MERGE.

   1: --1: Create Temp table

   2: CREATE TABLE #MyFactTable(

   3:     ID INT NULL

   4:     , CustomerName VARCHAR(100) NULL

   5:     , SourceID INT NULL

   6:     , OutputAction VARCHAR(100) NULL

   7: );

   8:  

   9: --2: INSERT into the temp table instead of your normal target table

  10: --   Merge query will be the same otherwise

  11: INSERT INTO #MyFactTable (ID, CustomerName, SourceID, OutputAction)

  12: SELECT so.ID, so.CustomerName, so.SourceID, so.output_action

  13: FROM (

  14:     MERGE INTO dbo.MyFactTable AS t

  15:     USING Staging.MyFactTable AS s

  16:     ON ( s.ID = t.ID

  17:         AND s.NewLoad = 0 )

  18:         

  19:     WHEN MATCHED AND ( s.SourceID <> t.SourceID )

  20:             AND s.NewLoad = 0            

  21:     THEN UPDATE 

  22:         SET RecordState = 0

  23:         , UpdatedDate = getdate()

  24:         

  25:     WHEN NOT MATCHED BY TARGET AND s.NewLoad = 0 THEN

  26:         INSERT (ID, CustomerName, SourceID)

  27:         VALUES (s.ID, s.CustomerName, s.SourceID)

  28:         OUTPUT $action AS OutputAction

  29:             , ID

  30:             , CustomerName

  31:             , SourceID

  32:         ) AS so (OutputAction, ID, CustomerName, SourceID)

  33:         WHERE OutputAction = 'UPDATE'  ;

  34:  

  35: --3: Perform the final insert into your target table

  36: INSERT INTO MyFactTable (ID, CustomerName, SourceID)

  37: SELECT DISTINCT ID, CustomerName, SourceID

  38: FROM #MyFactTable ;

  39:  

  40: --4: Clean up your temp objects.

  41: DROP TABLE #MyFactTable ;

I’d love to hear if anyone else has a workaround for this that doesn’t involve dropping and recreating the FK constraint. If this is an issue for you, please vote for the Connect Ticket.

Book Review: Windows Phone 7 Plain & Simple

Overall, Windows Phone 7 Plain & Simple is a good book for people new to Windows Phone 7 and especially for people new to Smart Phones. Michael does a great job illustrating how to use the phone and get up to speed quickly. He walks quickly through the general operation, setting up the phone, and entering text in the first couple chapters. The next several chapters discuss using the most popular features of WP7: calling, e-mail, text messages, calendar, browsing the web, and using the maps/navigation. Next we learn more about music, video, taking pictures and videos, and using the Marketplace. We end with a quick session on using WP7's Office Hub for documents, spreadsheets, and OneNote. This is followed by a discussion on using the Zune software to synchronize files between your phone and your PC. (Michael doesn't mention the Mac software to do this. It's capable, but more limited.)

Having followed the progress of WP7, I found that I knew a lot of the information in this book. There were still a couple interesting bits of information that were news to me, but not many. Michael didn’t write this for people like me who have followed WP7 for some time. He wrote for people who aren’t really familiar with smartphones and especially with the changes that WP7 brings. For those people, this will be a useful read and a quick reference while they get used to their device. I plan to show this to my family members who have WP7 devices and aren't quite sure what to do with them.

The positives:

• The information is very well presented, easy to follow, and broken out into logical sections.
• Michael's casual writing is well-suited for this book.
• Readers will not feel intimidated by their new phone and are encouraged to try things out.
• Michael presents some information that you may not easily find by reading articles online.

The negatives:

• I don't see this as a book that will be a long-lasting reference book. Once the reader has used the phone for a month or so, the lessons from this book should be second-nature.
• WP7 already has two planned updates at the time of this review. A minor update in January 2011 that will reportedly enable Copy/Paste functionality and a larger one in February 2011. The errata for this book will need to be updated to take these changes into account as some information may be outdated.

Conclusion:
If you can find Windows Phone 7 Plain & Simple for a reasonable price and are new to the platform, pick it up. It's a short, but informative, read. If you've followed WP7 for a while, this is not likely the book for you as you'll know most of the information here already. You may still want to pick up a copy to share with family or friends who are new to the phone. It could save you "support" calls.

Please visit the Windows Phone 7 Plain & Simple page at O’Reilly for more information about this book.

Disclaimer: I received a free copy of this book in electronic format in return for providing an honest review. I was not compensated in any other way.

Book Review: Cooking For Geeks

(I know this isn’t SQL-related, but I think this may be a great read for people with similar interests.)

This is not a cookbook. If you’re looking for a new collection of recipes, this is not the book for you. If you like shows like Good Eats or enjoy seeing how food is prepared and served, you’ll almost certainly love Cooking for Geeks. If you like experimenting in the kitchen and knowing why food turns out the way it does, pick up this book!

As a geek, I loved Jeff’s analogy: Recipes are code. Follow a recipe as written and you generally get good results. Forget the where clause and you could have unrecoverable errors. Introduce your own changes and you could get something great or you could get something horrible that requires a lot of cleanup. Recipes may have bugs or need corrections. Perhaps there’s more than one way to the same result. Oh, and don’t forget to comment your recipe. Otherwise you might not be able to recreate something fantastic.

Each chapter of Cooking for Geeks deals with different concepts, each with their own scientific background. Common utensils, ingredients, time/temperature, baking, additives (chemicals), and even some geeky fun with hardware or unusual cooking techniques – all are included in a way that not only gives some neat recipes, but the science behind the recipes.

To me, the most interesting parts were on baking and the chemical reactions that take place as heat is applied. It was great reading exactly why food turns out with all of its various nuances. That science got me thinking about ways to tweak the outcome of various recipes I follow and was just fun to read.

You can get your own copy of Cooking for Geeks direct from O’Reilly.

Disclaimer: I received an electronic review copy of this book, though I’d likely have wanted to read and review this anyway.

SSIS, OLEDB Data Sources, and Table Variables

I ran across an interesting problem when trying to tune some SSIS data source performance issues the other day. I had noticed that we were doing a pretty large select out of a table filtered on a handful of date-related columns. We then pulled in most of the data from the table. Well, SQL Server saw this query and apparently figured that since we needed most of the columns, a table scan would be the most efficient way to get that data.  Since in real life, we only need a very small percentage of those rows, I decided to rewrite the select to pull in the PK values first, then pull in the data based on that subset.

My first attempt was to use a Temp Table. Sadly, using a temp table does not work in SSIS Data Sources, even if you put that inside a stored procedure. Disappointed, but not deterred, I decided to use a Table Variable. I quickly made the changes and tested them with some values I knew would returned quite a few results. Great – no worries.  I was still using a stored procedure at the time and used some of our standard code to start it off so had no issues.

Ultimately, I didn’t want to write a bunch of stored procedures that I would need to maintain outside of SSIS just for this particular load.  We had the code already inside of the OLE DB Sources, just not optimized as much as I’d like. I started pulling out the old queries, adding a table variable, populating it with the necessary PK values, the using that to filter out the main data set.  Save, deploy, run, see major speed increase.

Then, I looked at the actual insert/update counts. Yes, it ran much faster, but in the process, it didn’t pick up any results.  I was pretty confused because the step was successful, the code ran as expected through SSMS, there were no errors or warnings. I re-verified that running that same code got results in SSMS; it did.  I started doing some searching to see what might be happening and found this post by Dustin Ryan.

In short, it seems that if you have row counts being returned (default behavior), you return a number of rows affected when you insert the table variable, which results in a “Done in Proc” type message, which in turn tells SSIS that the data flow is done and moves on to the next step.  The workaround?  Add SET NOCOUNT ON; to the beginning of your OLEDB Source query (or make sure this is in your stored proc – we tend to put that in our procs by default).

Once again, thanks to Dustin Ryan for his post on this topic. There’s a good possibility I’d still be struggling with the root cause of this without that assistance.