DaveWentzel.com            All Things Data

SQL Server

Service Broker Demystified - Why is there no ALTER CONTRACT statement?

The concept of "contracts" in Service Broker is initially confusing to most data professionals.  I like to think of a contract as a constraint applied to a message.  Then why can't you ALTER a contract?  Because a contract is really more like a "legally-binding contract."  There are good reasons why contracts can't/shouldn't be altered.

Service Broker Demystified - Contracts and Message Types

Contracts and Message Types are the "table constraints" of the Service Broker world.  Like table constraints, they aren't required, but they keep you from doing stupid stuff with your Service Broker design.  In this post I'll cover some confusing aspects of contracts and message types.  

Service Broker Demystified - [DEFAULT] is not the DEFAULT

Contracts and Message Types have defaults, but the default is not [DEFAULT].  This leads to confusion to folks new to Service Broker.  In this post I'll clear up the confusion and give you some tricks to keep things clear.  

People tell me all the time that they don't want to use Service Broker because it is too confusing.  I started a blog series called Service Broker Demystified to dispel these misconceptions.  Today we are going to cover the [DEFAULT] objects, which are not really default objects...sometimes. 

Service Broker Demystified - SET ENABLE_BROKER vs NEW_BROKER

Service Broker can be really confusing and causes data professionals to shy away from using it.  One example is setting up SSB.  It's easy, until you need to make a copy of your database.  Then the fun begins.  In this post I'll explain when you want to use ENABLE_BROKER vs SET NEW_BROKER.

Service Broker Demystified - Encryption Weirdness

Encryption is one of those things that makes Service Broker difficult to learn.  To set up encryption correctly is a lengthy process.  There are ways to safely not use encryption.  In this post I'll show you all of the issues with encryption in SSB and how to avoid and fix the problems.  

Service Broker Demystified Series

This blog series will focus on the common complaints I hear about SQL Server Service Broker...namely that it is too confusing for a data professional to master.  The fact that there is no (good) GUI or monitoring tools doesn't help either.  In this series I'll try to simplify things and explain why things work the way they do.  SSB is a really great technology and I find new uses for it almost every day.  

Why isn't my Java catching SQL errors...or...How I learned to stop worrying and love the ring_buffer target

Today's Story

"Hey Dave, I found a bug in SQL Server."

I hadn't even sat my bad down yet.  This was going to be one of those days.  "I'll bet you did find a bug.  SQL Server is horrendously buggy.  We should migrate our app to Cassandra immediately."  

Java Joe could detect the derision and changed his tack.  "Well, can you check out this code for me.  Clearly it throws as error in SSMS but not when I call it from Java.  

(At this point I'll save you from viewing the Java.  Essentially this was the code and, yep, the Java was not, indeed, throwing the very obvious error.)

Time to begin basic Java debugging.  "Have you tried this code from SSMS and if so, did it throw the error correctly?"  I already knew the answer, but I wanted to lead Java Joe down the troubleshooting path.  

"Yes I did and it errors correctly in SSMS, just not in Java which is why I thought maybe it was a bug in a SQL Server driver somewhere.  jdb does not show the error in the stack either.  I'm at a loss so I was hoping you could look."  

"Just because you don't see the error using jdb doesn't mean the error isn't coming back from SQL Server in the output.  The simple fact is, we use so many "middleware" components in our applications that somewhere, somehow, one of those is hoovering the error without re-throwing it to the next tier.  We use jdbc drivers, both 2.0 and 3.0 at places, websphere, jdo/kodo in places, hibernate, ruby, groovy, highly-customized versions of Spring, as well as our own in-house developed DAO (data access objects) factory.  There's probably even more layers between the user and the database error that I'm not aware, forgot, or am repressing.  Somewhere in one of those layers you have the equivalent of an old VB "On Error Resume Next" (arguably, a FINALLY block) and you don't know it. "  

This is common sense to me but I knew I would have to prove this fact to Java Joe.  "You see, this is a basic syllogism of error handling.  SQL throws an error, Java does not throw it.  Therefore, the problem is somewhere higher on the stack."  

I didn't quite have Java Joe convinced.  "But I tried everything and I beg to differ but there is nothing wrong with any of our tiers.  Is there any way you can do some magic on SQL Server to ensure that the database engine is really throwing the error?  Maybe SQL Server DDL errors are not passed to any non-SSMS utilities, or something."

Ah, programmers are often scared of their debuggers.  At this point I would generally get angry, but I already had a simple utility in my toolbelt ...an extended Events script.  

And here is the important stuff:

click to download

Note that we are creating a ring_buffer TARGET to capture any error_reported event with the given severity.  You can download the script here.  

Extended Events Targets

I see a lot of people that hate the ring_buffer target.  I really couldn't agree more that there are issues with it, specifically:

  • You can lose data.  The ring_buffer is a "FIFO ring buffer" of a fixed memory size.  So, in some situations you may miss some captured data because it was forced out.   But this shouldn't be a surprise.  It is called a "ring buffer" for a reason.  Caveat emptor!
  • No GUI.  You have to parse the XML which can be tedious.  But that's why we have pre-package scripts to help us.  

So, yep, those are some good reasons why you shouldn't RELY on the ring_buffer for important work.  But when I hear that people HATE something the contrarian in me wants to know when I should LOVE it.  

Reasons to Love the ring_buffer Target

I've found two really good uses for the ring_buffer that may help you out.  

  1. When I need to capture some data for trending purposes and I don't mind losing it under adverse scenarios.  
  2. Finding errors being thrown real time, such as my allegory above.  

ring_buffer Usage for Trending

Example:  saving deadlocks for later analysis.  The ring_buffer has a set max_memory and when that is reached the oldest events are purged, FIFO-style.  Some deadlock graphs are huge and can fill the ring_buffer quickly.  

Why not just use event_file then and not have to deal with ring_buffer issues?  

  1. Not everyone has the necessary filesystem access to their SQL Servers to write and delete files.  
  2. If you are tracing a lot of events you risk writing huge event_files.  If your Ops Guys monitor your servers it won't be long before they want to know what is going on.  
  3. Big event_files are slow to query.  

PerformanceCollector uses ring_buffer targetsIf you don't have these issues then PLEASE consider using event_file instead.  Murphy's Law says that even though you don't NEED your trend data now, when you experience severe issues someday you'll curse choosing the ring_buffer when it loses a good chunk of your forensic data.  

My PerformanceCollector utility uses ring_buffer to look for any deadlocks.  On a schedule it will write the contents of the ring_buffer to a permanent table and restart the ring_buffer.  If a few deadlocks roll off the end of the ring_buffer I really don't care.  I'm monitoring for deadlocks to look for trends...basically, bad data access patterns that we've introduced into our application that we need to fix before they get out of control.  

Watching Errors Real Time

So back to our story...Java Joe needed hard evidence that an error was indeed being thrown, as well as what the actual error was.  I had already prepared a stored procedure for just this purpose (Java Joe wasn't the first developer to have this problem...Ruby Ray had a similar problem weeks before).  The stored proc takes a parameter of START, STOP, or ANALYZE.  You definitely don't want this running 24x7 since EVERY error thrown will be logged.  You need to run ANALYZE BEFORE you run STOP.  The process of stopping a ring-buffered session wipes out the buffer which means your data is gone.  

Let's look at our example

Here is our example code again, except this time I annotated it to show the basic workflow.  

  1. Start the event session.  Choose an error severity that you need.  The default is 16.  
  2. run whatever code (or your application) that should be generating errors that you wish to see
  3. run ANALYZE which will generate the output below.
  4. run STOP to stop the event session.  This will save some system resources and about 4MB of memory.  

So, what is the output?

The proc outputs two result sets.  One lists all of the "error_reported" events that were thrown.  The second decodes the XML a bit to make it easier to look at exactly what the errors were.  

Let's take a look at the data.  

Note that we actually threw two errors.  


This is actually expected based on how I constructed the example.  The first error is 1505 (row 8 above).  The second, not in the screenshot above, is error 1750 (Could not create constraint.  See previous errors.). 

And that is exactly what I would expect.


Occasionally you may find yourself in need of capturing errors that are occurring on a running system real-time. The way to do this is to use Extended Events.  But the rigamarole required to set up an event session that logs to a file, and then have to query that file, can be cumbersome.  Sometimes you just need something quick and dirty.  For those times a ring_buffer target works wonderfully.  The example above I've used NUMEROUS times to assist developers who are scared of their debuggers to see actual SQL Server errors that are somehow being goobled up in one of their data access technologies.  

You have just read "Why isn't my Java catching SQL errors...or...How I learned to stop worrying and love the ring_buffer target" on davewentzel.com. If you found this useful please feel free to subscribe to the RSS feed.  

DBCC AUTOPILOT and hypothetical indexes

I gotta admit. I never heard of DBCC AUTOPILOT or how to manually use hypothetical indexes.

SQL Server Performance Tuning with Hypothetical Indexes


DevOps in the real world: Who should be responsible for index maintenance?

Larger ISVs usually employ at least two types of data professionals, the data developer and the database administrator.  In some situations, especially if DevOps is practiced, the lines may be a bit blurred regarding responsibilities.  I find it interesting in my consulting engagements which data professional is responsible for index maintenance.  Who is responsible for indexes at your company?  Who decides what indexes should exist?  Who creates them?  Who ensures proper index hygiene occurs (defragmentation, manual update stats)?  Which group should be responsible for these tasks?  

Informally I've found at my engagements that when both groups exist that it is the DBAs that are responsible for all aspects of indexing.  I think this is just flat-out wrong.  And I'm probably in the minority on this though.  Here's my reasoning:  

  • DBAs probably don't know all of the data access paths, and the most important ones we wish to optimize in the application.  Unfortunately your developers may not either, but that's a different issue.  
  • If you practice some variant of "database change review", where any and all schema changes to a database go through a review system to ensure they follow standards are are modeled sensibly, then you should have a good feel for what indexes should exist prior to those tables hitting your production systems.  
  • If you are doing performance testing then your developers should further understand the data access patterns.  Performance testing won't find every issue but the most egregious issues should be caught.  
  • Too many shops allow production DBAs to alter indexing schemes without consulting the data developers at all.  In EVERY case I've seen disaster because of this.  Here's a few examples:
    • we implemented queue tables as heaps and the DBAs didn't like that so they added clustered indexes and our queues experienced odd locking and concurrency behavior.  Contrary to google, sometimes heaps are good.  
    • DBAs applied indexes based on recommendations in the missing index DMVs and the plan caches.  The net result was cases where the same column in a table was the leading index column for 5 or more indexes, differing only by INCLUDED columns.  These DMVs must be countered with common sense and knowledge of your data.  
    • DBAs removed indexes because the cardinality at the time when they checked the index was LOW.  It's common for some tables to have a ProcessedStatusCode column that is binary (processed/not processed).  Throughout most of the day every row is in the processed state (1) until overnight batch processing starts.  Indexes on a ProcessedStatusCode column are critical, albeit counter-intuitive.  Many people believe that indexes on low cardinality tables are useless, but in situations like this they are required.  
    • DBAs removed indexes because sys.dm_db_index_usage_stats showed that data access was heavily skewed to updates and very little to seeks/scans/lookups.  The problem is, the indexes were needed during month-end processing and were suddenly gone when they were required.  

Contrary to what you are probably thinking, this blog post is not an "us vs them", anti-DBA rant.  "But you said above that it is flat-out wrong that DBAs are responsible for indexing."  


The resolution to all of the issues above is simple.  Collaboration.  Take the last example where important indexes were missing during month-end processing.  Clearly the DBAs had some valid reasons to remove the indexes and the data devs had valid reasons for needing them.  After our month-end processing went from 12 hours to 7 days due to the missing indexes, and after everyone stopped playing the blame game and hootin and hollerin, we collaborated and determined that we could actually build those indexes a day or so before month-end processing and then remove them later.  These indexes are huge and the chargeback for the SAN storage was not insignificant.  

This was a non-zero-sum event.  We managed to save on storage, our system was faster during critical month-end processing (those indexes were less fragmented), and the system was marginally faster during standard daily processing where the update overhead to those indexes could be eliminated. So, here's a case where a bit of collaboration led to one of those truly rare "win-win" situations.  And that's what we all want during conflict resolution.  We don't want to hurt anyone's feelings.  

(I found this graphic on an EXTREMELY popular DevOps site.  Maybe I'm being picky but I still see a separation of Dev and Ops.  Where is the collaboration?  I don't understand how this graphic couldn't be titled "Waterfall")

This is just another example where DevOps works.  My last few blog posts I pointed out that there is a misconception that DevOps means combining developers and ops guys.  It isn't.  It is about about situations like this.  Nobody is perfect.  I've worked in just as many shops where EVERY column in a table had an index because the developer figured that if a few indexes are good then more must be better.  Indexing is one of those cases where it really is difficult to determine which group should have ultimate responsibility, the developers or the DBAs.  Simple collaboration and following DevOps is the key.  

You have just read another DevOps post on davewentzel.com. If you found this useful please feel free to subscribe to the RSS feed.  

Find and fix untrusted foreign keys

Seems like most of the databases I work on have untrusted foreign keys.  This can happen for a few different reasons that I covered in my post nodetitle.  I think the biggest reason is that people temporarily disable the FK to do a data load/purge and then forget that the CORRECT syntax for re-enabling the constraint and making it trusted, is a bit goofy.  


Yeah, notice CHECK is in there twice, the first as a verb and the second as a noun (yes I know that grammatically those aren't nouns and verbs, the point is one is an object, the other is declaring an action on the object).  

Here is a script that will help you determine if you have untrusted FKs.  It is dynamic SQL and it does NOT actually do any work.  It is "SQL that generates SQL" that you can run at your leisure.  On larger tables you may want to run both the validation script and the WITH CHECK CHECK scripts off hours to avoid concurrency issues, lock escalation, etc.  Might also be worthwhile to index the underlying FK.  (The PK will already be indexed).  

The script can be found in my git repo.  Here's a screenshot of the header.



You have just read Find and fix untrusted foreign keys on davewentzel.com. If you found this useful please feel free to subscribe to the RSS feed.  



Subscribe to RSS - SQL Server