Worry no more! I have a query that will help you answer these pesky questions:

SELECT  DB_NAME() AS current_db_name,
        OBJECT_NAME(referencing_id) AS o_name ,
        UPPER(COALESCE(sed.referenced_server_name, '')) AS referenced_server_name,
        sed.referenced_database_name,
        sed.referenced_schema_name,
        sed.referenced_entity_name
FROM    sys.sql_expression_dependencies AS sed
WHERE   referenced_database_name <> DB_NAME()
        AND referenced_database_name <> 'msdb'
ORDER BY UPPER(referenced_server_name) ;

Want to run it across every database on your server? We got that, too.

DECLARE @command AS NVARCHAR(MAX);

SET @command = 'USE ?;

SELECT  DB_NAME() AS current_db_name,
        OBJECT_NAME(referencing_id) AS o_name ,
        UPPER(COALESCE(sed.referenced_server_name, '''')) AS referenced_server_name,
        sed.referenced_database_name,
        sed.referenced_schema_name,
        sed.referenced_entity_name
FROM    sys.sql_expression_dependencies AS sed
WHERE   referenced_database_name <> DB_NAME()
        AND referenced_database_name <> ''msdb''
ORDER BY UPPER(referenced_server_name) ;'

EXEC sys.sp_MSforeachdb @command1 = @command

Update: This only applies to SQL Server 2008. Nothing else. Thanks to an astute reader I have been corrected. Now we’re all smarter. Thanks, John.

Kendra Little (blog | twitter) completely called me out for my horrible, sneaky, developer ways. Ostensibly, I had found a solution for my rampant log growth problem. Unfortunately, I had cured the symptom and not the underlying issue. After growing tired of her savage abuse and criticism via gtalk, I looked for the source of the problem. No, not me. The other source of the problem.

I set up monitoring on the server in question, waited for the appropriate log death window, and then read my report. Before you think I’m using fancy tools that nobody can afford, I set up profiler and perfmon and then merged the results together.

The reports from the single server showed me… nothing, really. There was a lot of I/O and a backup job overlapped with a re-index by about 2 minutes. The logs also didn’t fill up. To be on the safe side, I adjusted the jobs and then sat around making frowny faces for a few minutes. Then I remembered that all of the servers are connected to the same SAN, so set up monitoring on the remaining production servers. An I/O issue on one server could start causing problems on all of the other servers.

This time around, the logs filled up, I received a ton of emails, and I also found out something important: all of my backups and re-indexing operations were running at the same time. My SAN was saturated on I/O throughput which was causing a the backup and re-indexing jobs to run slowly.

To solve the problem I looked at the average job run times and arranged the jobs so that they had much more downtime between them (to account for other issues that could slow down the jobs). This took a bit more effort than I thought just because of SLAs within the company. I also re-wrote the jobs so that the backups and re-indexes could never run at the same time and would, instead, occur in series. Once I had this change in place I waited and watched.

Sure enough, the incredible ever growing log file problem stopped happening (unless I do something dumb like move 30,000,000 rows of data). Moral of the story: make sure that you’re addressing the cause of the problem and not the symptoms.

I’ve been in love with data storage since I first opened up SQL*Plus and issued a select statement. The different ways to store data are fascinating. Even within a traditional OLTP database, there are a variety of design patterns that we can use to facilitate data access. We keep coming up with different ways to solve the problems what we’re facing in business. The problem is that as the field of computer science advances, and businesses increase in complexity, the ways that we store data must become more complex as well. Exponentially scaling storage complexity isn’t something that I like to think about, but it’s a distinct possibility.

General purpose OLTP databases are not the future of data storage and retrieval. They are a single piece in the puzzle. We’ve been working with OLTP systems for well over 20 years. OLAP is a newer entry, bringing specialized analytical tricks (which are counter intuitive to the way relational data is stored) to the masses. Hell, there are a number of general purpose analytical storage engines on the market. These general purpose analytical databases integrate well with existing databases and provide a complement to the transactional specialization of OLTP systems.

That’s the key, OLTP databases are purpose built transactional databases. They’re optimized for write operations because way back in the dark ages it was far more expensive to write data to disk than it was to read from disk. Data couldn’t be cached in memory because memory was scarce. Architectural decisions were made. The way that we design our databases is specifically designed to work within this structure. A well designed, normalized, database has minimal duplication of data. In OLTP systems this also serves to minimize the number of writes to disk when a common piece of data needs to be changed. I can remember when I was a kid and the United States Postal Service changed from using three letter state abbreviations to two letter abbreviations. I have to wonder what kind of difficulties this caused for many databases…

In the 40 years since E.F. Codd’s paper was published, the programming languages that we use have changed considerably. In 1970, COBOL was still relatively new. 1971 saw the the introduction of C, 1975 brought us MS Basic. 1979, 1980, and 1983 saw Ada, Smalltalk-80, Objective-C, and C++ ushering in a wave of object oriented languages. Suddenly programmers weren’t working on singular data points, they were working with a object that contained a collection of properties. The first ANSI SQL standard was codified in 1986. 1990 gave us Windows 3 and the desktop PC became more than a blinking cursor. The web exploded in 1996, 2001, and continues to explode again in a frenzy of drop shadows, bevels, mirror effects, and Flash.

Throughout the history of computing, we’ve been primarily working with tuples of data – attributes mapped to values; rows to you and I. This model holds up well when we’re working with a entity composed of a single tuple. What happens, though, when the entity becomes more complex? The model to retrieve and modify the entity becomes more complex as well. We can’t issue a simple update statement anymore, we have to go through more complex operations to make sure that the data is kept up to date.

Examples Should Make Things Clearer

Let’s take a look at something simple: my phone bill.

In the beginning…

Long ago, a phone bill was probably stored in a relatively simple format:

• Account Number
• Name
• Address
• Past Due Amount
• Current Amount Due
• Due Date

This was simple and it worked. Detailed records would be kept on printed pieces of paper in a big, smelly, damp basement where they could successfully grow mold and other assorted fungi. Whenever a customer had a dispute, a clerk would have to visit the records room and pull up the customer’s information. This was a manual process that probably involved a lot of letter writing, cursing, and typewriter ribbon.

Eventually, this simple bill format would prove to be unreliable (P.S. I’m totally making this up just to illustrate a point, but I’m guessing it went something like this). In our example, there’s no way to tell when a customer paid or which customer was billed.

After some tinkering…

After a few more iterations, you probably end up with a way of storing a customer’s information and bills that looks something like this:

This is a lot more complicated from both a design perspective and an implementation perspective. One of the things that makes this implementation more difficult is that there are a number of intermediate tables to work with and these tables can become hotspots for reads as well as writes.

When you look at that design, be honest with yourself and answer this question:

How often will you view a single service history or general charge row?

Think about your answer. The fact is, you probably won’t read any of those rows on its own. You might update one if a change comes in from an external source, but otherwise all of the charges, history, etc on any given phone bill will always be read as a unit. In this particular instance, we’re always consuming a bill’s entire graph) at once. Reading a bill into memory is an onerous prospect, not to mention that summarizing phone bills in this system is a read intensive operation.

Fixing the glitch

There are a lot of ways these problems could be worked around in a traditional OLTP database. However, that’s not the point. The point is that there are problems that require actual workarounds. OLTP databases work well for many use cases, but in this case an OLTP database becomes a problem because of the high cost of reading vs writing. (Why should we read-optimize a system that was designed to be write-optimized when writes will probably account for only 10% of our activity, maybe less?)

I’ve hinted at how we fix the glitch at the beginning of this article – we look for a specialized database. In our case, we can use something called a document database. The advantage of a document database is that we’re storing an organized collection of values in the database. This collection of values is similar to a traditional tabular database – we have groups of similar data stored in named collections. The distinction comes in how the data is accessed.

When we’re saving a phone bill, we don’t have to worry about calling multiple stored procedures or a single complex procedure. There’s no need to create complex mappings between a database and our code. We create an object or object graph in the application code and save it. The software that we use to connect to our document database knows how to properly translate our fancy objects into data stored on a disk somewhere.

This solution has several upsides:

• Related data is stored in close proximity on disk
• Documents do not require strict structure
• Documents may change properties without requiring complex changes to physical schema

Physical Proximity

My data is close together, so what?

In a traditional OLTP database, your data may be scattered across one or multiple disk drives. Physical drive platters will have to spin to locate the data on different parts of your storage medium. Drive read/write arms will have to move around in coordination with the spinning platters. The more complex your query, the more complex the dance your physical hardware will have to do; a simple high school slow dance turns into a tango.

In a document database, all of our data is stored together in a single record. When we want to read our bill, we just have to start reading at the beginning of the bill record and stop when we come to the end. There’s no need to seek around on the disk.

You might be worried that all of your data won’t be close together on disk. And you’d be right. However, many databases (including MongoDB) allow for the creation of secondary indexes to speed up data retrieval. The biggest question you need to ask yourself is “How will the applications be accessing the data?” In many applications we’re only acting on a single object. Even when our application isn’t acting on a single object, we can pre-aggregate the data for faster reporting and retrieval. When our application only works on a single object at a time, a document database provides distinct advantages – every time we need an object, we’ll be pulling back all of the data we need in a single read operation.

Strict Structure

Databases typically require data to be rigidly structured. A table has a fixed set of columns. The datatypes, precision, and nullability can vary from column to column, but every row will have the same layout. Trying to store wildly diverse and variable data in a fixed storage medium is difficult.

Thankfully, document databases are well-suited to storing semi-structured data – since our data is a collection of attributes, it’s very easy to add or remove new attributes and change querying strategies rapidly and in response to different data structure. Better yet, document databases let us be ignorant of how the data is stored. If we want to find all bills where the account holder’s last name is ‘Smith’ and they live in Virginia but the bill doesn’t have any county tax charges, it is very easy compared to constructing the query in a typical SQL database.

Using MongoDB our query might look like:

db.bills.find( { last_name : 'Smith' },
               { state : 'Virginia' },
               { charges : { type : 'county tax',
                             $exists : false } } )

Compared to similar SQL:

SELECT  b.*
FROM    bills b
        JOIN accounts a ON b.account_id = a.id
        LEFT JOIN charges c ON b.id = c.bill_id
                               AND c.type = 'county tax'
WHERE   a.last_name = 'Smith'
        AND a.state = 'Virginia'
HAVING  COUNT(c.id) = 0

And right about now, every DBA that reads this blog is going to be shaking with rage and yelling “But that SQL is perfectly clear, I don’t know how you can expect me to understand all of those curly brackets!” I don’t expect you to understand those curly brackets. Nor do I expect developers to understand SQL. The easiest way for us to develop is to use our natural paradigm. That’s why developers write code in C#, PHP, or Ruby and DBAs do their work in some dialect of SQL. MongoDB alleviates this because all the developers are doing is constructing a list of keys and values that must be matched before a document can be returned.

Changing the Schema

Changing the schema of an OLTP database can be an onerous task. You have to wait for, or schedule, down time. Modifications have to take place. Of course, the schema modifications need to take into account any actions (like triggers or replication) that may occur in the background. This alone can require significant skill and internal database engine knowledge to write. It’s not something that application developers should be expected to know. Why do I mention application developers? 99 times out of 100, they’re the ones who are working on the database, not a dedicated DBA.

Many newer, non-traditional, databases make it incredibly easy to change the schema – just start writing the new attribute. The database itself takes care of the changes and will take that into account during querying. When a query is issued for a new attribute, records without that attribute will be ignored (just like a column with a NULL value in a traditional database).

What about Analytics?

I don’t know a lot about analytical databases, in part because they require a different skill set than the one I’ve developed. I do know a few things about them, though.

Analytical databases are currently encumbered by some of the same problems as OLTP databases – data is stored in tables made up of rows and columns. Sure, these are called dimensions/facts and attributes, but the premise is the same – it’s a row-based data store.

Row-based data stores pose particular problems for analytic databases. Analytic databases throw most of the rules about normalization in the garbage and instead duplicate data willy nilly. Without joins, it’s very quick to query and aggregate data. But the problem still remains that there is a large quantity of repeated data being stored on disk.

Columnar databases attempt to solve this problem by compressing columns with similar values and using some kind of magical method to link up columnar values with their respective rows. Sounds complicated, right? Well, it probably is. Let’s say you have a table with 10,000,000,000 rows and the CalendarYear column is a CHAR(4). If there are only 25 different values for CalendarYear in the database, would you rather store 40,000,000,000 bytes of data or 100 bytes of data? I know which makes more sense to me.

Interestingly enough, there are two approaches being taken to solving this problem. The first is by creating single-purpose columnar databases. There are several vendors providing dedicated columnar databases. Other database developers are looking for ways to leverage their existing database engines and create hybrid row and columnar databases.

Looking Into the Future

There are a lot of interesting developments going on in the database world. Many of them seem to be happening outside of the big vendor, traditional database space. Most of this work is being done to solve a particular business need. These aren’t the traditional row-based OLTP systems that we’re all familiar with from the last 30 years of database development. These are new, special purpose, databases. It’s best to think of them like a sports car or even a race car – they get around the track very quickly, but they would be a poor choice for getting your groceries.

The next time you start a new project or plan a new server deployment, think about what functionality you need. Is it necessary to have full transactional support? Do you need a row-based store? How will you use the data?

What Do These People Want From Me?!

When I stepped up and ran for the PASS Board of Directors, I had no idea what I was doing. I knew that I wanted to help make PASS better. Better how? I had a few ideas. We were going to change the world! There would be speaker mentoring and education for all! A chicken in every garage and mittens on our feet! It turns out that my chickens and mittens proposal isn’t going so well, but we’re working on a speaker mentoring program and I’ve been working with Microsoft to get your feedback to them about certifications.

Enough about me! More about you!

Have a Plan

So, this Board of Directors thing, you should have a strong idea of what you would like to change. That could be changes to how we’re governed, increasing our revenue streams, or finding a new Summit location. The point is that you should have a vision of the future. Beyond a vision, it would be helpful if you have some ideas about how you’re going to fulfill that vision. You don’t need to have a complete business plan, although that never hurts.

I can’t stress how important having a strong vision is. The goal is to have Board members act in a strategic role. Rather than plan things and implement them, you should be coming up with broad plans. Once you’ve come up with plans and goals you’ll work with PASS HQ and a few key volunteers to implement things. That isn’t always how things work out, but that should be the goal.

Without a strong vision, it’s very easy to get swept along. This is a two year commitment. if you’re being swept along for two years, you’re going to feel very lost, purposeless, and burnt out. Make sure that you know you have something that you plan to accomplish. When you’re finished with your term you can look back and say “I did that and I’m damn proud of it.” I know that when I’m done, we will have happily gone through and codified and documented more of the abstract selection process. There will be less tribal knowledge and more of it will be available for easy public consumption. I’m going to be happy about that. Know what you’re doing. Have a goal, make a plan. Don’t be afraid to revise it.

The Time of Your Life

People always say that you need to be willing to donate “a few hours a week” of your time if you want to be on the PASS Board of Directors. (They say this for just about anything else, too.) So, how much time does it really require?

That all depends on how much work you take on, how much you can delegate, and your own personal comfort level. The amount of time that I spend on my own portfolio is not typical – that’s not to say that they work less, it’s just that my time commitments come in spikes where I’ll be incredibly busy for three or four months and then a lot of the year things are very quiet. I’m hoping that other Board members will comment here and put forward their opinions and help to add to this article and clear things up for the people who are thinking about running for the Board of Directors. The point is: there’s no fixed amount of time. It’s as much or as little time as you want to put into it.

There’s a one hour conference call once a month. As often as once a quarter there will be an in-person Board meeting. You’ll typically spend a day traveling to and from the meeting and two days on site. This ends up being four week days, in my experience. Of course, one of these in person Board meetings ends up being the PASS Community Summit. So that’s one less week that you have to take off from work.

Skills in Magic

What kind of skills do you need to be on the PASS Board of Directors? We’ve already covered one: planning. You should be reasonably good at planning. You don’t need to be a genius strategist, but you should be able to put together a simple plan and execute on it. These are the skills that you’ve built as a DBA or database developer. You know how to make a list, prioritize, delegate, and execute on that list. If you can do that effectively, you’re halfway there.

The other skills that you would use in running a business the size of PASS… it would help if you had them before starting, but they’re not necessary.

The Free MBA

The PASS Board of Directors is the cheapest and most entertaining MBA program I have found. Nowhere else will you find the opportunity to manage a 7 figure budget while dining on bacon.

You can quote me on that.

One of the greatest things about the PASS Board of Directors is that it really is like an MBA program. I’ve learned about budgeting on a 7 figure scale, about how decisions play out, how to think strategically, how to plan, delegate, project, and all of the other things that they talk about in fancy business classes. Instead of one instructor and a few students, you’re one student with many other instructors. There are five other board members, three members of the Executive Committee, four vendor board members, the immediate past president, and let’s not forget our non-voting representative from Europe. Right there, you’re going to be learning from fourteen other people.

In the last seven months, I’ve learned more about my abilities as a manager than in the rest of my career. I was speaking with a friend recently, and he asked me what I thought about the PASS Board of Directors. Knowing that this friend was in management I said “It’s an awful lot like being a manager without the benefit of a company car and a reserved parking space.” After a hearty chuckle, we proceeded to play golf using a corporate AmEx. Okay, maybe that was a lie. But we did talk about the experience. The one thing that struck me is what I’ve mentioned here – it’s been the greatest education about business and about myself. There are days when I joke that I don’t know why people complain about being managers, all you do is tell people to do something and wait until they get it done. There are other days when I want to shove my head in a mailbox and scream.

The biggest thing that I’ve learned is how to listen. I don’t always see eye-to-eye with my fellow board members. But I’ve learned to listen to what they’re saying and to try to understand where they’re coming from. Sure, we all say things like this, right? That’s what marriages and friendships are about. When you’re making large decisions about the PASS Community Summit or a spring event or a budgetary decision, you suddenly realize how little you listened before and how much you need to listen. You want to know something funny? I don’t find myself agreeing with people any more than I did before I started listening closely, but I do find myself understanding them. That alone has made a huge difference for me professionally and personally.

The Round Up

That’s not a ringing endorsement, but there are bad days. The good days and the learning experiences far outweigh the bad days and the stress.

In the end, it’s been worth it. If I had to do it again, would I still run for election? Absolutely.

Confused yet? Yeah, so am I.

Let’s backtrack: at work we recently upgraded to VS 2010 and TFS 2010 – this lets everyone on my team take advantage of the database developer edition as well as add a whole slew of automated testing features and doodads. Plus our management can tie in to our tasks

One of the problems that we ran into is connecting to TFS2010 from VS2008. Why would I need to do that? Visual Studio 2010 doesn’t have support for the SQL Server 2008 BI project types yet (SSRS, SSIS, etc). So, in order to work on SSIS packages, I need to keep VS 2008 around.

It’s pretty easy to connect Visual Studio 2008 to TFS 2010. Download this hotfix, install it, and then you’re up and running. That’s really all there is to it.

What’s the solution? Do we run summary jobs on a regular basis? Maybe.

Sometimes summary jobs aren’t enough. Sometimes we have to update summarized data in real time. Let’s break out AdventureWorks and look at an example.

Let’s say we have a report that shows us the total line item sales per customer broken out by year and month.

That’s pretty simple, right?

SELECT  p.BusinessEntityID ,
        p.FirstName ,
        p.LastName ,
        DATEPART(yyyy, soh.OrderDate) AS [Year] ,
        DATEPART(mm, soh.OrderDate) AS [Month] ,
        SUM(sod.LineTotal) AS LineItemTotal
FROM    Person.Person AS p
        INNER JOIN Sales.Customer AS c ON c.PersonID = p.BusinessEntityID
        INNER JOIN Sales.SalesOrderHeader AS soh ON c.CustomerID = soh.CustomerID
        INNER JOIN Sales.SalesOrderDetail AS sod ON soh.SalesOrderID = sod.SalesOrderID
GROUP BY p.BusinessEntityID ,
        p.FirstName ,
        p.LastName ,
        DATEPART(yyyy, soh.OrderDate) ,
        DATEPART(mm, soh.OrderDate)
ORDER BY p.BusinessEntityID ,
        [Year] ,
        [Month] ;

The problem is that this query may use a lot of CPU or disk resources. How can we avoid that? Earlier I mentioned using summary jobs. The problem is that the summary jobs will only run on a schedule and require that we maintain some kind of maintenance history of each row – even if that maintenance history is just a modification timestamp. Wouldn’t it be easier if our summary report just updated itself automagically?

It can! Enter the indexed view.

An indexed view is a regular view that has a clustered index on it. By adding the clustered index the view is persisted to disk. The upside of persisting the indexed view to disk is that there’s an automatic mechanism in place that will update the indexed view. An indexed view must have a unique clustered index so that SQL Server can identify the materialized row that must be updated in response to changes in the underlying data.

CREATE VIEW dbo.my_indexed_view
WITH SCHEMABINDING
AS
  SELECT  p.BusinessEntityID,
          p.FirstName,
          p.LastName,
          DATEPART(yyyy, soh.OrderDate) AS [Year],
          DATEPART(mm, soh.OrderDate) AS [Month],
          SUM(sod.LineTotal) AS LineItemTotal,
          COUNT_BIG(*) AS NumberOfRecords
  FROM    Person.Person AS p
          INNER JOIN Sales.Customer AS c ON c.PersonID = p.BusinessEntityID
          INNER JOIN Sales.SalesOrderHeader AS soh ON c.CustomerID = soh.CustomerID
          INNER JOIN Sales.SalesOrderDetail AS sod ON soh.SalesOrderID = sod.SalesOrderID
  GROUP BY p.BusinessEntityID,
          p.FirstName,
          p.LastName,
          DATEPART(yyyy, soh.OrderDate),
          DATEPART(mm, soh.OrderDate) ;
GO

CREATE UNIQUE CLUSTERED INDEX CX_my_indexed_view ON dbo.my_indexed_view (BusinessEntityID, [Year], [Month]) ;

We could have accomplished this using a summary table and triggers. Triggers are synchronous and updating the summary table will block our data changes from completing. In addition, if we ever want to bypass the trigger from updating (say during a huge data load) we need to disable and then enable the trigger again and then remember to update the summary table. With an indexed view we can just drop the clustered index and then recreate it when we’re done with our load processes.

Note: I can’t take full credit for this question and explanation. A SQL Saturday #42 attendee sent me an email with a question about using indexed views instead of triggers.

The people who put this event on – Dave Schutz, Stuart Johnson, Marc Kuyper, Jim Stoltz, and John Jakubowski – deserve a big round of applause. They put together a very strong event and it seemed like they were incredibly well prepared.

Despite Brent Ozar suffering from incredible jet lag, and another speaker not showing up, things went off well. Allen White and I were able to cover Brent’s time slots, but not his presentations. This led to the funny moment of an attendee walking into the room and saying “You’re not Brent Ozar.” I replied, “No, I am not, but I’m talking about SQL Server Internals, it’ll be fun.” They just said, “Oh,” and walked away. So, apparently, I’m nowhere near as engaging as Brent talking about his stupid and dangerous T-SQL tricks. Clearly they don’t know that I’m just stupid and dangerous.

Outside of that, things went very well. My Dynamic SQL talk went over well, although I think I need to re-work it. I felt like I was flipping back and forth between demos and code a bit too much. It gave the entire experience a jerky feel. I suspect I’m overly critical of the presentation since I’ve given it a few times, but it’s always good to improve.

My internals presentation wasn’t well attended – this is probably due to me not being Brent Ozar. However, that didn’t stop us from having fun talking about SQL Server Internals. If anything, having a room of 6 people made the topic more fun to talk about. We were able to digress onto different topics, delve a little bit deeper than normal, and have a lot of fun watching me not trip over an extension cord.

Last but not least was my Indexes and Other Free Performance Boosts. I had a full room on this – I suspect it had something to do with the word “Free” in the presentation title. This is a really fun presentation because it’s a whirlwind tour of indexes, keys, statistics, and how they work together to coax SQL Server into giving us data faster. Once again, I think I need to smooth up the code samples, but on the whole things went really well.

I don’t have any of the evaluations, but I hope the SQL Saturday team is able to get those out to the speakers soon. From the ones I glanced at, I did a passable job. I’m looking forward to the next event in Columbus.

I’m excited about the presentations I’m giving – I haven’t given the indexing presentation in a long time and it should be a lot of fun. And the Dynamic SQL presentation is one of the first in the day. It’s a nice easy way (I think) to get your day started.

A Dynamic World Demands Dynamic SQL

Dynamic SQL is a misunderstood and much maligned part of a DBA’s tool kit – it can be used to solve difficult business problems, respond to diverse data needs, and alleviate performance problems. Many DBAs reject dynamic SQL outright as a potential source of SQL injections, being poorly performing, or just for being a hacky solution in general. Not so! Jeremiah Peschka has been making extensive use of dynamic SQL throughout his career to solve a variety of problems. He’ll set about dispelling these misconceptions and demonstrate how dynamic SQL can become a part of every DBA’s tool kit.

Indexes And Other Free Performance Boosts

The database is often viewed as a major performance bottleneck. There are a number of quick, easy, painless techniques that can increase the performance of an application not just by a small amount, but by orders of magnitude. These techniques includes simple indexing techniques, T-SQL techniques, and general database application design patterns that give great gains in performance. In this session, you will learn how to look at a database to identify these problem areas and how to resolve common issues that you will encounter.

What else?

Let’s say you’re interested in something else. What should you go see? Well, Michael Swart (blog | twitter) put together a nice little blog post about How I plan to spend my weekend.

If you like business intelligence, I suggest you hit up Dave Rodabaugh’s presentations. I cannot speak highly enough of Dave’s work. Not only is he one of the brightest BI people I know, he’s also been a teacher, friend, and mentor to me for a long time.

There will be some kind of dinner/drinks/whatever going on afterwards at Barley’s Smokehouse (map). I plan on being there for a little bit. Even if you can’t make it to the event, head on over there around 6:00PM. I’ll be there.

I used my rudimentary knowledge of Service Broker, Event Notifications, and hackery to create a solution to my problem. For starters, I added the following stored procedure to every database:

CREATE SCHEMA dba ;
GO

IF OBJECT_ID(N'dba.ShrinkLog', N'P') IS NOT NULL
  DROP PROCEDURE dba.ShrinkLog ;
GO

CREATE PROCEDURE dba.ShrinkLog
  WITH EXECUTE AS OWNER
AS
  BEGIN
    DECLARE @error AS VARCHAR(2000) = 'LOG_FILE_AUTO_GROW encountered' ;
    RAISERROR (@error, 10, 1) WITH LOG

    DECLARE @size_in_mb AS DECIMAL(18, 5) ;
    DECLARE @max_log_size_in_mb AS DECIMAL(18, 5) = 10240 ;

    SELECT  @size_in_mb = ( size * 8.0 ) / 1024.0
    FROM    sys.database_files
    WHERE   data_space_id = 0 ;

    IF @size_in_mb > @max_log_size_in_mb
      BEGIN

        SET @error = 'log shrink occured on ' + DB_NAME() + ' at '
          + CONVERT(VARCHAR(50), GETDATE(), 101) + '. log file was '
          + CAST(@size_in_mb AS VARCHAR(50)) + ' mb in size.' ;

        RAISERROR (@error, 10, 1) WITH LOG

        DBCC SHRINKFILE (2, 1024) ;
      END
  END
GO  

Once I had my procedure in place, I needed a way to fire this procedure. Rather than mindlessly attempt to shrink the log files every X minutes, I decided that I should only shrink the file after a log growth has occurred. This means that, in theory, I could stop the log from growing… because, you know, I’m a bastard. We don’t want the log to NEVER grow, just shrink when it gets too big – which is possible because this might be a problem between log backups. That’s why there’s a check to see if the log file is greater than an arbitrary maximum size.

The next step was to actually figure out how to make this procedure fire. I wasn’t sure about it, but I thought that there was a SQL Server Event Notification that would fire whenever the log file grew. Turns out that I was right: LOG_FILE_AUTO_GROW.

Now, I knew from reading Mladen Prajdić’s blog that I could use the SQL Server Service Broker to respond to system events and, so, I did just that.

USE SystemEvents ;
GO

IF OBJECT_ID(N'dbo.LogShrinkResponse', N'P') IS NOT NULL
  DROP PROCEDURE dbo.LogShrinkResponse ;
GO

CREATE PROCEDURE dbo.LogShrinkResponse
AS
  BEGIN
    DECLARE @msgBody XML
    DECLARE @dlgId UNIQUEIDENTIFIER

  -- you can change this to get all messages at once
    WHILE ( 1 = 1 )
      BEGIN

        BEGIN TRANSACTION ;

        BEGIN TRY 

            ;
          RECEIVE TOP (1)
                @msgBody = message_body,
                @dlgId = conversation_handle FROM LogShrinkNotificationQueue ;

          IF @@ROWCOUNT = 0
            BEGIN
              IF @@TRANCOUNT > 0
                BEGIN
                  ROLLBACK ;
                END

              BREAK ;
            END

          DECLARE @database_name AS SYSNAME ;
          DECLARE @data AS XML ;
          DECLARE @statement AS NVARCHAR(2000) ;

          SET @data = EVENTDATA() ;

          SET @database_name = @data.value('(/EVENT_INSTANCE/DatabaseName)[1]',
                                           'NVARCHAR(128)') ;

          SET @statement = N'USE ' + @database_name
            + '; EXEC dba.ShrinkLog ; '

          EXEC sp_executesql @statement ;

          IF @@TRANCOUNT > 0
            BEGIN
              COMMIT ;
            END
        END TRY

        BEGIN CATCH
          IF @@TRANCOUNT > 0
            BEGIN
              ROLLBACK ;
            END

          -- write any error in to the event log
          DECLARE @errorNumber BIGINT ,
            @errorMessage NVARCHAR(2048) ,
            @dbName NVARCHAR(128)
          SELECT  @errorNumber = ERROR_NUMBER() ,
                  @errorMessage = ERROR_MESSAGE() ,
                  @dbName = DB_NAME()

          RAISERROR (N'Error WHILE receiving Service Broker message FROM queue LogShrinkNotificationQueue.
                        DATABASE Name: %s; Error number: %I64d; Error Message: %s',
            16, 1, @dbName, @errorNumber, @errorMessage) WITH LOG ;
        END CATCH ;
      END
  END
GO

CREATE QUEUE LogShrinkNotificationQueue
WITH STATUS = ON,
ACTIVATION ( PROCEDURE_NAME = dbo.LogShrinkResponse, MAX_QUEUE_READERS = 1, EXECUTE AS 'dbo'
) ;
GO

CREATE SERVICE LogShrinkNotificationService ON QUEUE LogShrinkNotificationQueue
([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]) ;
GO

CREATE ROUTE LogShrinkNotificationRoute
WITH SERVICE_NAME = 'LogShrinkNotificationService',
ADDRESS = 'LOCAL' ;

CREATE EVENT NOTIFICATION LogShrinkNotificationEvent ON SERVER FOR LOG_FILE_AUTO_GROW
TO SERVICE 'LogShrinkNotificationService', 'current database' ;
GO

/*
DROP PROCEDURE dba.ShrinkLog
DROP EVENT NOTIFICATION LogShrinkNotificationEvent ON SERVER
DROP ROUTE LogShrinkNotificationRoute
DROP SERVICE LogShrinkNotificationService
DROP QUEUE LogShrinkNotificationQueue
*/

Here’s what’s going on up there.

We create a procedure that will be called when the log shrink notification is caught. It loops through our queue and grabs the top message off each time and processes it. If there’s a message, it will pull the name of the database out of the XML from the event notification. We use the database name to build dynamic SQL that we execute to fire off the log shrink procedure.

The rest of the code just sets up the service broker queue, notification, routing, and event handler. It’s basic, boiler plate, code that tells SQL Server to set up service broker on the current database and listen for th LOG_FILE_AUTO_GROW event.

Want to know my favorite 2008R2 features? PostgreSQL 9.0 and MongoDB.

PostgreSQL and MongoDB are rapidly advancing features that solve my daily problems. I’m an OLTP guy. Honestly, I don’t care about the latest reporting ding dongs and doo dads. I already know PowerShell. I manage 6 production servers and we’re unlikely to grow, so these MDM and Utility Control Points don’t make me giddy with excitement.

I solve problems using SQL.

You know what makes me happy? Support for window functions or better yet, improved support for window functions. What about exclusion constraints? Or column level triggers so I don’t have to use branching logic in my triggers? Yes, I use triggers. Or any other of these features.

What about MongoDB? I’ve just started playing with it, but it solves a lot of the problems I face at work. Not just in the day job, but in side projects as well. I’ve bitched about O/R-Ms before, but one of the biggest problems that users of O/R-Ms (developers) face is that the ideal way to model data for object-oriented programming bears no resemblance to the ideal way to store relational data. A recent article about scaling Farmville hints at this – the developers of Farmville managed scale by storing everything in a key-value store (memcached) before persisting to a relational data store later. Digg does something similar with Cassandra. It’s not like these guys are idiots, the blog posts from Digg show that they know their stuff.

MongoDB lets me solve these problems. I can control the frequency of syncs to disk (just as I can in PostgreSQL) to improve raw write performance to memory. I only have to worry about storing data the way my application expects to see the data – arrays and hashes – without worrying about building many-to-many join tables.

What about DMVs and data integrity and a write-ahead log and indexes? MongoDB has instrumentation and indexes. Yeah, you sacrifice some durability but many applications don’t need that. Hell, Amazon has even designed their systems to account for the potential of failure.

When I start my next application, I’m going to look long and hard at the platform I’m building on. There’s a good chance it’s not going to be a relational database and a really good chance it’s not going to be using SQL Server. It’s not because I have a problem with SQL Server or even RDBMSes, but because there are other choices that give me the flexibility I need to solve the problems I’m facing.

This is nothing against SQL Server 2008 R2, it’s a great step forward in the direction that SQL Server seems to be going. Sometimes I wonder if SQL Server and I are on the same road.