Blog

Celebrity cooking shows are popular around Brent Ozar Unlimited®. We watch Top Chef for the creative cooking as much as the human drama. Contestants on Top Chef face huge challenges – they’re working alone, have a limited set of tools, have a fixed set of ingredients, and operate under ridiculously strict time guidelines. What makes the creativity of chefs even more interesting is the limitations they’re under: contestants have to work with strange, disgusting or difficult ingredients in order to win the favor of the judges.

Building successful software isn’t much different. Sure, there’s a team of collaborators to help us make decisions; but everyone on that team is frequently responsible for one area of the application. Success or failure depends on your ability to work with the requirements you’re given and please the final judges – the end users.

The Rules of the Contest

Top Chef contestants work within the rules of the contest – they have a limited amount of time with a limited number of ingredients to make an appealing meal. The core ingredients and the time period are non-negotiable. The show’s producers call these the rules, in the world of software these are business requirements, and in the world of programming we might call these application invariants. But no matter what you call them, these things can’t change. Requirements might be something like

• A picture can’t be viewed until four thumbnails have been generated.
• Property listings cannot be viewed until approved by the listing agent.

Contestants on Top Chef don’t immediately start cooking – although careful editing makes it look that way. Development teams, even agile teams, shouldn’t get immediately start coding once they have their hands on requirements. It’s important to look carefully at the requirements and make sure it’s possible to deliver something that the business (the judges) are happy to see. Winning chefs don’t immediately reach for a bottle of Frank’s Red Hot to give a dish a bit of pizzazz, they consider all of the options and match their condiments to the meal, so why do we always reach for the same tools?

It’s easy to be lulled by the familiar – hot dogs and burgers are easy, but they don’t win Top Chef. While you don’t need award winning code to win the game of delivering software, you do need to make the right choices to make life easier.

Working with black chicken and monkfish liver may not be the easiest thing, but contestants on Top Chef are routinely able to turn strange ingredients into masterpieces. Business requirements spell out how our applications have to behave at the end of the day, but you’ll notice that it doesn’t matter how you get there. Just make sure you get there – solve the business problem and move on.

Start with the Ingredients

It isn’t uncommon for Top Chef contestants to scrap their first ideas after a few minutes of work. Likewise, don’t be afraid to throw away your first idea. If you’re a pack rat, write your idea on a piece of paper and hide it from yourself. It’s okay to come back to your first idea, but it’s important to think about the problem in a different way.

What are the core ingredients of your application? Just because you have chicken, that doesn’t mean you should make chicken cordon bleu. Ask yourself, “What am I supposed to create?” A few applications I’ve come across in the last year are:

• Single sign-on systems
• Hosted property listings
• Utility easement tracking
• Document tracking and signing

Each of these applications has a different set of features and functionality. Would you use the same solution for each one? Looking at it a different way – would you serve the same meal for the Superbowl as you would for Christmas dinner?

Make an itemized list of the ingredients that you have on hand. Your requirements are your ingredients. They drive the way the users will interact with the data. As you investigate the requirements, make sure you ask the users questions like, “Do you need point in time recovery for easement property maps?” or “Is it a requirement that a user have a first name, last name, bio, and profile picture or would a user name and password be acceptable?” Understanding your requirements drives your choices.

It’s All About the Ingredients

Under all of your application code, you need somewhere to store your data. One of the Top Chef judges frequently asks “Where’s the protein?” when served a salad. As you work through application requirements, use these to ask yourself “Where’s the data?”

Top Chef contestants typically aren’t told that they need to make sweet glazed salmon, they’re told to use a set of ingredients and produce a fine meal. It’s up to the chef to determine whether to use rémoulade or tartar sauce and it’s up to you to make technical decisions. The business user isn’t going to know the answers to your technical questions, but they do know that a user only needs a user name and password to use the application.

Use the business requirements to help make your database design decisions – if an image doesn’t need to be transactionally consistent with all other data, you don’t need to store it in your relational database. The rules of the contest – the business requirements – should shape how you design your application. They give you both the restrictions and freedom you need to be creative.

What Will They Eat?

Food falls into distinct cuisines. If I gave you a choice between sushi or tapas, you’d be able to make an informed choice because you know the ingredients and style used for each style of cooking.

As you evaluate the business requirements, dig deeper and imagine the types of answers that users might look for in the data. Will users look for property along the path of a tornado where repairs need to be made? Are users searching for houses with specific features – e.g. find single family homes with 2 or more bathrooms and an attached garage? Or are users’ questions difficult to predict and completely free form?

Understanding how people will use the data guides the choices we make. If users will be performing free form text searches, a full text search engine like SOLR should be considered. If an application is pure OLTP, it’s possible that you can use a key-value database. Understanding application requirements means that you can decide whether you need to use SQL Server or you can investigate other options.

Some of the database cuisines to consider are:

• Relational database (SQL Server, PostgreSQL)
• Document database (CouchDB, MongoDB)
• Text Search (Lucene/SOLR, Elastic Search)
• Key-Value database (Riak, Cassandra)

The processing of picking a database can lead to conflict. Developers have their favorite new technologies they want to try and entrenched products are frequently favored above all others. Understanding how one database meets application requirements is important – if you don’t know which ingredients you have, you don’t know what to make; if you don’t understand the application invariants involved, you can’t know which option is the best.

Ultimately, making sure you pick the right tool for the job can lead to faster development, easier support, and better throughput.

What Do the Judges Think?

The most important thing, though, is what the judges think. It doesn’t matter if you’ve made the greatest chicken salad sandwich ever, if your work doesn’t meld with the judges’ expectations you won’t be taking home the prize. Understanding how the requirements influence the ways that users will work with data is critical if you want to be successful. Once you know how people will work with the tools, you’ll be able to make the right decisions for your application.

It’s difficult to define why some things are wildly successful in a sea of forgettable products. Some recipes have a mysterious umami that comes from a subtle dash or two of magical sauce. In business and technology there’s an indefinable edge that sets people and teams apart. How do you get that special something?

Here’s one strategy businesses use all the time: layer products to build new, deeply specialized meta-products. First, they create a service or application they can sell. It generates revenue, but it also generates data. Sometimes they have to adjust (or heaven forbid, pivot) the product, but they get good at it. They start making money.

After a while they apply analysis to harvest meta-information from the product. The meta-information is used to create new, different products. These products may offer extra insights to existing customers, but they may also suit new and different customers, too. BLAMMO, the business is more diverse and successful.

These techniques aren’t just for packaged products. This is also what helps companies create services that seem a little magical, because they’re simultaneously simple and complex. We use these principles to make our own SQL Server Training super sharp and effective for students.

This isn’t something that only we can do. You can use the same techniques to find your own secret sauce.

Step 1: To Get Ahead, Look Back

One of our primary consulting products is our Critical Care® Sessions. We work closely with a client to identify their pain points and the bottlenecks in their SQL Server environment– issues can be anywhere from the storage layer to SQL configuration to query optimization and execution. We deliver recommendations for the client to implement within the first couple of weeks, the next month, and the next quarter. We tailor each recommendation to the client’s specific needs.

After a few weeks have passed, we meet again. We work with the client to find out how much they’ve gotten done. What’s been easy? What’s been difficult? Are there any questions we can help out with?

We listen very carefully, because this is an important source of information. Following up with your customers and finding out what has worked for them and why some tasks are trickier for them than normal is one of the most important things you can do. This tells us:

• Where misunderstandings commonly occur
• What types of data successfully gets buy-in to make difficult changes
• Which scripts and tools are really critical to get people that data

The truth is this: it’s easy to know facts. It’s difficult to be effective. Because of this, we constantly collect data on what helps people get things done and evolve our tools.

This information is certainly helpful to our consulting product itself– it keeps us at the top of our game. But we also get an important component of our secret sauce from this: we can train people in the classroom to be effective using the techniques and scripts we’ve refined.

To apply this step yourself, create a habit of regularly meeting with customers and listening to feedback. Make notes. You won’t always be able to implement every idea you have, but keep track of what you find along the way. Steps 2 and 3 will give you a chance to harvest that information.

Step 2: Identify Your Differentiators

Very smart people are sometimes terrible teachers. We knew we could teach on a wide variety of subjects. But how did we build a great training?

The key is to identify what sets you apart and narrow your scope to projects where you can impact your audience deeply. We don’t want to fill a training room with warm bodies and talk about general SQL Server topics. We want to get the right people into the room so that we can address their immediate problems and have them jumping out of their seats to get going.

To create great training, we identified what we do better than anyone else. These are our differentiators:

1. We’ve built tools and scripts we can quickly train people to use effectively to diagnose server health and performance and tackle tough indexing problems.
2. We’ve created specialized materials to help developers learn the critical factors to SQL Server performance in a short amount of time
3. We have refined queries and techniques to teach developers to recommend effective changes in their environment
4. We have an army of slide decks, blog posts, and even animated gifs to help developers solve performance problems, no matter how niche– so making Q&A awesome is no problem.

Knowing these differentiators made our mission clear. We knew we could change people’s careers with a short 2 day class, targeted at a specific audience (developers), and give them immediately useful scripts and techniques to improve performance.

We applied also looked at differentiators for how we offer the training itself. What new things could we do to help people access the training and make it work for them in a new way?

• We created a business justification form to help people show the value of their training to management before signing up.
• We also included two two-hour group Q&A webcasts for just the people in the training session two weeks after the event. You know how most trainings leave you with a feeling of confusion about a few topics when you get back to your desk and try to apply what you learned? These Q&A webcasts mean our attendees can get answers for those difficult situations.

Define your differentiators for yourself. What do you do better than anyone else in your environment? What needs do you fill, or could you fill, that nobody else covers well? Where are the gaps and needs in your workplace that you find interesting?

Step 3: Don’t Kill Yourself with Focus

You’ll never get permission to have an idea.

We didn’t start our business with the plan of giving our own training events. We love consulting. We speak in front of people all the time– we give our weekly webcasts and speak at local and national conferences, but we primarily think of ourselves as people who go out and initiate change rather than people who stand behind a podium and lecture. We naturally focused on consulting as our core business.

Focus is great and it can help make you successful. But too much focus makes you predictable. It keeps you from really using your secret sauce.

We give training because we were open to a new idea. We realized that two day training is perfect to teach a developer what they need to know about performance tuning SQL Server to design changes for real-world environments. It’s just enough time to get them out of the office and teach them how to use the right tools, but not so long that they’re swamped under an incredible tidal wave of email when they return.

Set aside time at least once a month to think about how your job could be dramatically different. Don’t restrict yourself by immediate needs or how much time you have available. Give yourself room to dream of what you’d be really proud of doing in six months or a year.

Here’s the key: don’t wait until you’re having problems to make your work more awesome. Be creative and act on your aspirations when you’re already successful and busy. That’s often when you’ll have your best ideas! We added our training offering at a time when we were already very successful, simply because we knew we could offer a very high quality product and help developers in a way nobody else can.

The Secret Sauce Isn’t Out of Reach

Good news: if you read our newsletter every week or even just made it to this point in the post, you’re probably a specialist already. You may sometimes feel like a small fish in the big SQL Server pond, but that’s just human nature. If you’re even in the pond you are well on your way toward becoming very good at jobs that are difficult to fill.

Skills that can set you apart like becoming great at performance tuning SQL Servers are completely within your reach. A few steps to find the right path is all it takes.

There are many ways to scale out your database; many of these techniques require advanced management and expensive add-ons or editions. Database sharding is a flexible way of scaling out a database. In this presentation, Jeremiah Peschka explains how to scale out using database sharding, covers basic techniques, and shares some of the pitfalls. This talk is for senior DBAs, database architects, and software architects who are interested in scaling out their database.

https://www.youtube.com/watch?v=W6pFKihvqH4

More resources are available over in our sharding article.

Without looking in your wallet, do you know how much cash you have? Most of us know within a few dollars. Now, without looking in your SQL Server, do you know much data is cached in memory? You probably don’t and that’s okay; you shouldn’t know how much data SQL Server is caching in memory. We can’t control how much data SQL Server is caching, but we can control how we cache data.

Different Types of Cache

There are a lot of different ways to approach caching. One of the most prevalent ways involves thinking about cache in two different levels (much like CPU cache): first level cache and second level cache.

First level cache is an immediate, short-lived cache that works within a single session to attempt to minimize database calls. Unfortunately, first level cache is only used for the duration of a current session or transaction (depending on your terminology). This is very short lived and it’s only useful to the current process. While helpful, first level cache has a limited scope.

There’s another type of cache: second level cache. Second level cache exists outside of the current process and can be shared between multiple transactions, processes, servers, or even applications. When we talk about adding cache to an application, we really mean second level cache.

A Bit of Cache

Even the most basic of ORMs have a little a bit of cache available. The first level cache is used as a short lived buffer to reduce the amount of work that the ORM has to do. First level cache is used for caching objects in the current transaction and query text. Although this cache can be helpful for the current process, this cache isn’t shared across multiple processes or even multiple database batches. If we want to have a more robust cache, we have to look elsewhere.

ORMs like Entity Framework or the LLBLGen Framework don’t have a second level cache. It’s up to developers to add a cache when and where they need it. This exposes developers to additional concerns like cache invalidation, cache updates, and query caching. All of these features and functionality may not be necessary, but that’s an acceptable trade off – it’s up to developers to implement cache features in ways that support application requirements.

Although it takes up developer time, building the second level cache yourself has the benefit of creating a cache that’s suited to the application’s requirements. For many application level features, this is good enough. It’s important, though, that developers pick a caching layer capable of meeting their operational requirements. Operational requirements include horizontal scalability, redundancy and fail over, recovery of cached data, or customizable cache expiration on an object-by-object basis.

These basic ORMs aren’t really all that basic – they have full features in other parts of the ORM, but they only offer basic support for automatic caching through the ORM.

A Lot of Cache

You’ve got memory. You want to use it to cache data. What’s the easiest way to do that?

One of the easiest approaches to adding caching to your application is to use a framework that supports it out of the box. A number of ORMs, including both Hibernate and NHibernate, provide this support. Enabling cache is easy – just change a few lines in a configuration file and the cache will be available to your application. Things start getting tricky, though, when you examine the richness of the caching that’s provided by these tools.

Power comes with a price. When you’re getting starting with tools like Hibernate or NHibernate, there’s a lot to take in and many developers overlook these features. Developers can choose on an object by object basis which caching strategy should be applied. Based on business requirements we can choose to treat certain cacheable objects as read only while others can be used as a read/write cache. Some objects can be cached while others bypass the secondary cache entirely – there’s a lot of complexity for developers to manage.

While this can be overwhelming, this flexibility serves a purpose – not all features of an application have the same requirements. Some features can serve old data to users, other features need to be up to the minute or up to the second. Giving developers the ability to make these choices means that there is a choice to be made. Even if it’s a difficult one, developers can choose how the application behaves and can tailor performance and functionality to business requirements.

Making the Choice

If you’ve already got an existing project and you’re planning on adding a caching layer, don’t think that you have to re-implement your data access layer just to get better support for caching. Both approaches have their benefits and it’s far more important to be aware of which data needs to be cached and the best way to cache it.

No one knows everything, especially about SQL Server. When you encounter a problem, especially for the first time, you may not know how to solve it. I’ve seen some crazy approaches taken to fix problems – like duct tape on a car. Many times, instead of fixing the root cause of a problem, such as a poorly-performing query, a server-level change is made.

Here are three rolls of duct tape to avoid.

Clearing the Plan Cache Regularly

“We found that the system would be running fine, then a user would run a report with a certain set of parameters, and it would slow down. Running DBCC FREEPROCACHE every day solved that problem.”

When you run DBCC FREEPROCACHE, all the query execution plans in memory are dropped. The next time every query is run, SQL Server will have to compile a plan, and this is expensive. CPU usage will increase. Queries will run slower the first time they are executed. Is it worth dumping the hundreds or thousands of plans that are saved for reuse for the sake of one or two that are bad?

If a query runs acceptably most of the time, but runs very slowly at other times, check into what else is running on the server at the same time, and also investigate statistics and parameter sniffing. Often, you’ll find that a simple change can fix the problem – without taking drastic measures that affect the whole server.

Shrinking Files Regularly

“Tempdb grows from its normal 25-30 GB to 100 GB every Wednesday night. Our drive is only 100 GB in size, so I set up a job to shrink it back down every Thursday morning.”

Or, your log file doubles in size once a week, so you create a job to run DBCC SHRINKFILE weekly, too.

If this happens, there is something running in your database that is causing the data or log file to grow. A large index is rebuilt frequently. A batch job is run to insert a large amount of data all at once, instead of in sections. You need to find the source of the problem and fix it. Constantly shrinking and regrowing files leads to fragmentation, and fragmentation leads to poor performance.

Combining Development and Production Databases on the Same Server

“Our database performance has been really bad the last month. We are testing new features in our development database, which is on the same server as our production database, and I think that might be the problem.”

When you write new code, you need to test it first. You absolutely should have separate development and production databases. You also should keep them on separate servers. When they share a server, they share resources, and poorly performing queries that are being tested in development can bring production to a screeching halt.

You have many options for setting up a development server. Trying to find hardware? Use an older server that has reached the end of its production lifecycle, or, if you have a virtual environment, request a virtual SQL Server. Worried about licensing? For development only, you can purchase a copy of SQL Server Developer Edition for about $50 per user.

Learning from the Past

These are only three of the valuable lessons I’ve learned from working with SQL Server. Finding the root cause of a performance problem and solving it is a much better long-term solution than implementing a short-term workaround.

Developers and database administrators frequently clash on major questions about how to scale application performance. Are they just being stubborn, or is one side really right? Jeremiah and I recently gave a talk on this topic to the AtlantaMDF User group and we wanted to open this debate to the world.

Someone’s Right and Someone’s Wrong

Developers and DBAs are usually being stubborn, and for good reason. DBAs tend to be tasked with being the protectors of an environment, while developers are on a mission to ship more features, make changes, and bring in more money. Each side has a reason for their views, but on most controversial topics, one side actually is more right than the other.

ORMs

Most DBAs believe that Object Relational Mapping tools (ORMs) write terrible code and that stored procedures are a better data access layer. This belief is widespread because database administrators struggled for years with early versions of NHibernate and Entity Framework.

Some of the growing pains hurt: everything from type mismatches to hilariously long and tangled queries have burned people. But developers embrace ORMs for good reasons. These tools really do help build, test, and ship features more quickly. If you become a SQL Server performance tuning specialist, you can get the best of both worlds– but you have to let go of some of your hangups about ugly SQL queries.

Application Caching

It’s hard to argue against application caching… at least unless you’ve tried to implement it and come up frustrated. DBAs argue that developers should cache everything, while developers can tell you how that isn’t as easy as it sounds.

We know that usually there IS a place for caching, you just need to know how to find the “biggest bang for your buck” opportunities. (We’ve got some slides and scripts below to get you started.)

Access to Production

Who hasn’t had this argument over and over? Usually developers are fighting to get into production and DBAs are fighting to keep them out. Lots of time gets wasted on this topic needlessly.

We created tools like sp_BlitzIndex® to help people share information for performance tuning more easily. You just gotta find the right tools and the right level of access that lets everyone do their jobs (and keep their jobs).

Get the Scripts

Download ’em here.

Join Us Live

We train developers and DBAs diagnose the real bottlenecks in their SQL Servers and use data to get beyond the problems we just described. Join us at a live training event to learn how to performance tune SQL Server.

Let’s say you get your hands on a brand new version of SQL Server that you haven’t played with before, and you want to know what system objects have changed – DMVs, DMFs, system stored procs, yadda yadda yadda. Sure, you could read the documentation – but ain’t nobody got time for that, and they’re probably leaving out the juicy stuff, right? Here’s what I do (thanks to improvement suggestions from Kendra):

First, create a linked server between the new version and the old version. This lets us query across servers and join things together. In my examples, I’ll be running my queries on the new SQL Server, which I’ll be calling local, and the linked server will point to the remote server.

Identify new system objects with this query:

We’re getting all local system objects that don’t have a matching system object on the other (remote) server. The “172.16.189.131” is the server name for our older SQL Server box that we’re comparing against – our linked server name. The results give you the new functions, stored procs, you name it:

We’re joining on object_id, but that’s a gamble – Microsoft could use different object_ids between versions if they wanted to. If we wanted to filter those out, we could join on name, schema_id, and type_desc (view, stored proc, etc), but that gets a little sketchy. For example, there’s now two CHECK_CONSTRAINTS views in SQL Server 2012 with different object_ids, and we wouldn’t catch that if we were only joining on names. In the below screenshot, the top result set is from SQL Server 2012, and the bottom is from SQL Server 2008R2:

Next, we want to find new or changed columns in existing objects. Here’s how:

Note that we’ve commented out a join on name – if you’re worried that existing columns might have changed names, that’s how you’d catch it. The results:

Presto – we can see what new instrumentation fields Microsoft is giving us. This helps script authors do a better job of testing their changes across multiple versions. When someone sends me an sp_Blitz® improvement that relies on a DMV or field I’m not familiar with, I can quickly check multiple servers to see if it’s available across all of my lab servers, or which version it appeared in.

Here are several scripts that can catch all kinds of changes: