You’ve heard the term ‘parameter sniffing’, but you’re always a little bit fuzzy on the details. Is this a bug in SQL Server? Could it be happening to you? Join Kendra in this free 30 minute video to learn the truth about this complex and often confusing feature in SQL Server.
Download demo scripts from the webcast here– the script contains commands that are only safe for test environments (not production): Parameter Sniffing Demo.sql.
On September 24, we published a blog post that we were looking for a new employee. We didn’t advertise on any job listing sites (even though there are some really good ones out there). We tweeted about it a few times, but most of our publicity was that single post. We received more than 70 applications for the job by email. We were truly impressed by the applicant pool– multiple well known international speakers and Microsoft Certified Masters applied for the job.
When I talk to hiring managers, I hear that it’s hard to find good SQL Server pros for a job these days. How did we attract such cool people?
The secret to getting employees that don’t suck: Write a job advertisement that doesn’t suck.
1. Explain what YOU will do for the employee
Most job ads are written as a list of demands of the employee. You MUST have experience with X. You really ought to know about Y and Z. Good luck if you can’t recite eight decimals of PI.
We explained what the job would be like for the employee. We clearly listed a couple of requirements that employees must have. But we also devoted lots of time to describing what we will do for the employee. This means you must describe not only standard benefits, like time off, but also explain:
- Training you’ll offer employees
- Other opportunities they’ll have to learn
- Whether or not flexible time/ working from home is available
- If you pay for certification attempts or job growth
- Times the employee doesn’t have to be on-call, and support processes that keep them from being randomized
Don’t make the common mistake of assuming people will think the job is awesome. Smart, talented, experienced people won’t just assume that at all– they’ll look for all the hidden signs that the job isn’t awesome. Show them what you’ll do for them!
2. Ask what you really want to know– and don’t ask for a resume
Are you hiring someone to write resumes as part of their job? If so ask for a resume. If not, why bother? Resumes tell you very little about an applicant. If you must have one as part of your HR requirements, you can get it later in the process.
In your job ad, ask for what you really want instead of a resume. Brent, Jeremiah and I worked together to figure out what basic things we could ask that would indicate whether the candidate would thrive in this job over time. We whittled down the list as much as possible to keep it simple. We asked for two things:
- Recent speaking experience
- A description of how the applicant has contributed to a technial community.
And that’s it. That’s all we wanted.
Asking for something out of the ordinary helps you understand your applicants. You can see how they think in answer your questions, rather than receiving a resume prepared for general consumption (and possibly crafted by a resume writing pro). You also save time by evaluating applicants against your specific criteria early, rather than having to hash that out later in phone interviews.
3. Have a personality. Ideally, your own personality.
We wrote our ad in the style of a “Missed Connection.” We like to play around with writing, and we like to have fun. We took the time to write our ad in a style that represents us honestly. If you’re not dry, boring, and corporate, don’t fill your ad entirely with bullet points and corporate-speak.
Understand that smart talented applicants see your ad as a description of who you are. If you like to have fun at work and want to attract fun people, show it!
How’d we know this stuff?
We’re naturally creative and charming (and modest). We followed our own experience, and it worked. But we also like data. Specifically, we like the data and conclusions drawn from the folks over at Stack Exchange on How to Write a Great Developer Job Listing.
You think locking and blocking may be slowing down your application, but you’re not sure how much of a problem it’s causing. Learn how to find lock waits, decode what they mean, and identify which tables and queries are involved in your worst blocking problems.
Want to use the tools mentioned in the webcast? Get tools, scripts, and more articles on locking here.
You’re a Database Administrator who gets the job done day in and day out– but you’re worried that maybe your skills aren’t quite up to par. How do you know if you’ve fallen behind? Join Kendra in this free webcast for a simple test to learn if your DBA knives are as sharp as they need to be.
Looking for the resources from the talk? Head on over to http://BrentOzar.com/go/dbaupgrade
This question came up in the pre-show chat for our weekly webcast: when you rebuild a nonclustered index offline, does it impact only the nonclustered index? Or does it impact the entire table?
Showing is more fun than telling! Let’s take a look.
First I restore AdventureWorks to slow storage
Sometimes slower storage is handy. To demo any questions like this, I like to restore a copy of AdventureWorks2012 onto a handy external Seagate drive. I love having SSDs in my workstation, but when it comes to wanting to test out things like blocking on a reasonably small amount of data, it pays to have some poky storage around.
Now I run a super slow nonclustered index rebuild
Once AdventureWorks is restored, I set up an offline index rebuild in one session window— and to make it take longer I set up my index rebuild command in a BAD way. I set fillfactor to 1 percent, which means SQL Server is going to explode this table and leave 99% of each page empty.
--Warning: this fillfactor is terrible for performance --Don't do this in production! ALTER INDEX [IX_TransactionHistory_ProductID] on [Production].[TransactionHistory] REBUILD with (fillfactor=1); GO
In another session, I query the table’s clustered index
While the index rebuild is running, I open a second window and run a SELECT query. This select query is designed to specifically read from the clustered index of the table and not use the nonclustered index at all:
SELECT Quantity FROM [Production].[TransactionHistory]; GO
Now I check out the blocking
Now, from a third session I check and see– is my SELECT from the clustered index blocked by the rebuild on the nonclustered index? To get all the details, I run Adam Machanic’s sp_whoisactive with a parameter to get information on locking. (This can be a bit expensive, so only use this option when you need it.)
exec sp_WhoIsActive @get_locks=1; GO
Sure enough, my SELECT query is blocked by my index rebuild.
Clicking on the ‘locks’ XML column for my blocked SELECT statement, I can see more detail:
<Database name="AdventureWorks2012"> <Locks< <Lock request_mode="S" request_status="GRANT" request_count="1" /> </Locks> <Objects> <Object name="TransactionHistory" schema_name="Production"> <Locks> <Lock resource_type="OBJECT" request_mode="IS" request_status="WAIT" request_count="1" /> </Locks> </Object> </Objects> </Database>
This confirms that the read has requested an intent shared lock on the Production.TransactionHistory object– but it can’t get the lock.
Finding: offline rebuilds of a nonclustered index prevent querying the clustered index
If you need to rebuild indexes offline, this means that you can’t minimize the impact by only rebuilding the nonclustered index– rebuilding them places locks on the object itself. We saw this by seeing that queries who just wanted an intent shared lock on the table were blocked.
If our indexes were large and tables needed to be available constantly, this could be a huge problem!
Enterprise Edition Doesn’t Fix Everything
With SQL Server Enterprise Edition, you can specify that you’d like to do an “ONLINE” rebuild. In this case things are a little different– users can access the table during most of the index rebuild. However, at the end of the rebuild SQL Server still needs an exclusive “Schema Modification” lock (SCH-M) to finish the operation– and in highly concurrent systems, that can still be an issue.
If you’d like to reproduce that issue at home to demo for yourself, it’s easy! Just start up the SELECT statement first and change the rebuild to use the “ONLINE” option.
But Wait, There’s More
This logic also applies to creating indexes– if you’ve got Enterprise Edition, you want to remember to always create your nonclustered indexes with the “ONLINE” option if you need to avoid blocking on the table. Even then, that pesky SCH-M lock can be a killer on very busy systems.
Want to Learn More about Indexes?
Check out a list of our articles all about SQL Server Indexes
In just a few weeks, Brent, Jes and I will be giving a full day pre-conference session at the SQLPASS 2013 Conference in Charlotte, North Carolina. Our pre-conference will be Tuesday, October 15, 2013.
There’s only a few seats left!
This pre-con’s going to be killer– we’ve got 240 folks registered and ready to learn. Capacity is capped at 300 due to logistics, so as of this writing there’s room for 60 more students.
What you’ll learn
You’re a developer or DBA stuck with a database server that’s not going fast enough. You’ve got a hunch that the biggest bottleneck is inside the database server somewhere, but where? In just one day, you’ll learn how to use powerful scripts to identify which queries are killing your server, what parts of the database server are holding you back, how to tackle indexing improvements, and how to identify query anti-patterns.
This pre-conference session will cover the following topics and more:
- How wait stats tell you where to focus your tuning
- How the plan cache shows you which queries are the worst
- How to make fast improvements by picking the right indexes
- How to identify and fix the most common query anti-patterns
How much does it cost?
When you register for the PASS Summit, our “Make SQL Server Apps Go Faster” is just $395.
If you’ve already registered for the Summit, email Shannon.Cunningham@sqlpass.org to get in on the fun.
And for those of you who scrolled down to the bottom of the post in your excitement looking for a call to action, here’s how to register.
You were helping a sysadmin understand the details of a request for a new virtual server. You whiteboarded a diagram to show how SQL Server uses memory. You got so excited when the sysadmin asked a hard question that you spilled coffee everywhere.
We were tuning queries on a video conference with developers from That Software Company next door. They said they wished you worked with them, because you ask such great questions at the coffee maker.
We’re looking for someone just like you.
We are: a small team of loving SQL commando medics. We parachute in when our clients need help. We diagnose what’s causing their problems and build a plan to solve their biggest pain points. We’re growing, and we’re hiring a full time SQL Server consultant.
The Job: What You’ll Do
As Employee #2, you’ll help clients make their SQL Servers faster and more reliable.
Your missions will usually be short term consulting projects. You will diagnose bottlenecks in storage, problems in Windows and SQL Server configuration, and figure out when queries are killing a server. You’ll help clients understand the root causes of their problem, and you’ll prescribe a custom action plan that solves their issues in the short, medium, and long term.
Although you’ll work from home most of the time, you’ll be an integral part of our team. You’ll adopt our SQL Critical Care™ toolkit and processes, but you’ll also get inspired, and suggest and build improvements for the whole team to use. You’ll work closely with me, Brent, Jeremiah, and Jes during the days: we use chat, email, and hangouts to ask questions and solve problems as a group.
You’ll contribute to our online video training. You’ll challenge yourself and learn something every week.
What We’ll Do for You
We work hard, and we take time off seriously. Our benefits include:
- 6 weeks paid vacation per year
- 1 paid conference, plus 1 week of learning (on projects to be announced soon) — each year. You don’t have to speak at the conference, and it doesn’t count as vacation time.
- Health insurance
- Full time telecommuting with a max of 1 week of travel per 2 months
- Paid certification attempts (whether you pass or fail)
- Home internet/VOIP/cell phone expenses paid
- $3,000 hardware/software budget every 2 years to pick your own tools (we like Apple gear, but you can pick whatever you want – you’re your own IT department)
- If you’re a Microsoft MVP, we pay for your travel and time for the MVP Summit each year.
- We’ve started a tradition of awesome company trips, and we have one booked for February 2014. You and your significant other will be invited along. (You just need a passport.)
To Apply, Send Us Two Things
First, we need to know where you’ve spoken recently and what you talked about. This can include all sorts of public speaking– it doesn’t have to be technical.
Next, tell us how you give back to a technical community. (It doesn’t have to be the SQL Server community.)
Email the details (be specific!) on both of these things to help@BrentOzar.com along with your name, and you’re in the running!
If you’ve only got the good intentions to start speaking soon, don’t apply. Get out there and do it, and tell us about it next time.
Don’t send us your resume. We only want the two items listed above right now.
Life’s not always that easy. It’s probably going to take a while to find the right person this time– and we’re willing to wait as long as we need to. But you never know, so drop us a line fast!
If SQL Server’s Transactional Replication was an animal, it would be a cockroach. It’s not pretty, but it’s resourceful and strong. Once you get even a small amount of replication in your environment it starts spreading everywhere. And if it gets out of control– well, in that case you’ve got no choice but to pretty much bomb everything, clear the fumes as quickly as possible, and reinitialize.
But unlike a cockroach, you can’t always just get rid of Transactional Replication. The bad news is, it’s here to stay.
In this post you’ll get a whirlwind introduction to Transactional Replication. I’ll explain why newer features like Change Tracking, Change Data Capture, and Availability Groups have failed at exterminating Transactional Replication. You’ll also learn what it takes to support replication in a mission critical environment.
SQL Server Transactional Replication basics
Transactional Replication lets you publish and filter individual database objects.
1) Publications define replicated articles. Transactional Replication lets you select individual tables to publish (called “articles”), allowing you to just publish a fraction of a very large database. It also lets you:
- Filter published data
- Use column filtering to only include critical columns for published tables, further targeting and limiting exactly what is published.
- Apply row filtering to put a “where clause” on a publication. This is a great way to keep a subscribing server from seeing rows that don’t belong to it.
- Publish stored procedures. This essentially takes procedure executions on a publisher and replays them on a subscriber
2) A fleet of SQL Server Agent jobs does magic. I just learned this: a group of cockroaches is called “an intrusion of cockroaches.” That seems like it probably applies to a whole lot of SQL Server Agent jobs, too.
Exactly how many jobs? So many jobs that you’ll lose count. Replication is complicated. It’s got a SQL Server Agent job to read the transaction log of where you’ve defined your publications (Log Reader Agent). There’s another job that helps run Snapshots (it helps you initialize or re-initialize the replication). Another helps apply the snapshot and move transactions around (the Distribution Agent).
There are also jobs for cleaning things up, refreshing monitoring, and all the general scuttling around that replication does.
3) The distribution database collects commands. Replication doesn’t send transactions directly to your subscribers. The Log Reader Agent snoops in your transaction log and figures out what changes have been made to the articles you’ve defined in your publications. When it finds changes, it sends them over to the distribution database.
You can configure distribution on the publishing server, or you can scale it out to an independent server. The important thing to know is that each change you make in your publisher gets translated into independent commands (insert, update, and delete), and these commands get inserted into the distribution database before it moves onward. No matter what you do, that process takes some time: replication isn’t instantaneous.
4) Commands get applied to subscribers. You may set up many subscriptions to a single publication. This is part of what’s cool about replication– a single publication can publish a narrow group of tables to be read by many subscribers (perhaps in different datacenters around the world).
You can elect to configure pull subscriptions or push subscriptions. In the “pull” model, each subscriber runs jobs that poll in data periodically. In the “push” model, the changes are more constantly pushed out to the subscribers from the distributor.
5) Old commands get cleaned up. Hopefully. Lots of commands may collect up in the distribution database. That can be a good thing– maybe a subscriber is offline for a few hours, wouldn’t it be sad if it couldn’t catch up?
But you don’t want to hoard too many commands, or your distribution database will bloat up like crazy. There are jobs that delete out old commands. In an active environment, balancing out cleanup, new activity, and pushing data to subscribers can be… challenging.
6) Then you learn a million factoids. We’ve barely scratched the surface. I haven’t shown you Replication Monitor, a tool whose GUI is so confusing that you don’t dare take your eyes off it. (It’s hard to avoid it, but do use it with care– I’ve had it cause blocking when run by multiple people concurrently in a couple versions of SQL Server.) I haven’t talked about tweaking Replication Agent Profile Settings or the limitations of tracer tokens. We haven’t debated when you might want to initialize from backup.
Transactional Replication is a complicated monster. It’s not something you can book-learn, it’s just got far too many configuration options and moving parts. Replication something you learn by trying it, having problems, fixing it, and then repeating the process.
Transactional Replication vs. Change Tracking
Two new replication-ish features were introduced in SQL Server 2008: Change Tracking and Change Data Capture. These two technologies are both completely separate from replication, and from each other.
Change Tracking, like transactional replication, is available in Standard Edition. That’s a good thing, but if you’re comparing the two features, people still pick replication.
Change tracking just answers a very basic question: has a row changed? It doesn’t capture the “old data” or send the “new data” anywhere– it just updates a marker letting you know that a row has changed since your application last polled it.
It’s up to the user to write an application to regularly poll the tables (and know what version it last tracked).
It’s also recommended for the user to enable snapshot isolation on the database using change tracking and explicitly use it when querying Change Tracking tables to make sure that you don’t miss data or cause blocking with Change Tracking cleanup processes. Snapshot isolation is great, but turning it on and managing it are not at all trivial.
Change Tracking also has some additional overhead. For each row modified in a tracked table, a row is added to a change table. For each transaction that commits, a row is inserted into an internal transaction table, also. (It doesn’t keep the previous values in those tables, so they’re small inserts, but on super active tables that’s extra writes hitting the transaction log of your database– and extra writes when those tables get cleaned up.)
Verdict: Replication is complicated, but Change Tracking ain’t simpler. Due to having much less custom code to write, more freedom with isolation levels, and no extra inserts in internal tables, most people decide that Transactional Replication beats Change Tracking.
Transactional Replication vs. Change Data Capture
How about Change Tracking’s distant cousin, Change Data Capture? This is an Enterprise Edition feature– it must surely perform better than the Standard Edition friendly Transactional Replication, right? Well, sorry, not necessarily.
I love the concept behind Change Data Capture. Imagine that you’re a database administrator managing a transactional replication publication. You have a table that holds advertising campaign keywords.
- Each row in the table contains a CampaignID column, a keyword column, and a BidPrice column
- Some campaigns have hundreds of thousands of keywords
- Tools let advertisers set up campaigns quickly with LOADS of keywords– and the keyword BidPrice
It’s the day before a big holiday. One of the big advertisers goes in, and sets up a huge campaign– 200K rows of changes roll into the table. They realize they messed up and deactivate it right away. 200K more changes roll in. Then they set up a new one. Then the tweak the BidPrice.
Suddenly replication is churning trying to get all these changes into the distribution database, not to mention handle what’s happening in any other replicated tables you have. Replicated commands start backing up.
You’re left saying, “But all I want is the most recent change they made!”
This is the scenario where Change Data Capture sounds like a great idea. The concept with “CDC” is that you write your own polling. If you poll every hour, you just pick up the latest version of the row (or whatever history you want), and take that with you. You don’t have to take every command, so it’s super light-weight.
But CDC isn’t all unicorns and magic, either.
When you enable Change Data Capture for a table, SQL Server starts tracking inserts, updates, and deletes in the transaction log (similarly to replication). But when you make changes, they’re read from the log and then inserted into “change tables” associated with the tracked tables. The change tables are automatically created in the same database as the tracked tables– and inserts into them are also logged operations. So for every modification you do on a change tracked table, you’re also doing an insert (and logging it) into a tracking table.
Data in the tracking tables needs to be cleaned up periodically, of course– and that also gets logged. You need to write your own polling mechanism to pull changes, and there’s limitations to how you can change schema of a table.
While our test scenario seemed like perhaps it would help us out, after checking out a whitepaper on tuning CDC, things don’t look so hot: the whitepaper recommends avoiding scenarios where rows can be updated immediately after insert, and also avoiding scenarios where large update scenarios can occur. Also, our dream of only pulling the latest, or “net” change if a row has been changed multiple times has a cost– that requires an additional index on each tracking table, and the whitepaper points out that this can have a noticeable performance hit. This isn’t seeming like such a great fit anymore– and worse, it sounds like if it doesn’t go well, it’s going to slow down our whole publishing database.
This comparison highlights that although transactional replication commands can get backed up in the distributor, the fact that replication allows distribution to be offloaded to its own server independent from the publisher is a real strength.
Verdict: Due to extra logging caused by internal change tables (and cleanup), plus the need to write custom code, plus limitations on schema changes, most people decide that Transactional Replication beats Change Data Capture.
Transactional Replication vs. Availability Groups
Now here’s a real competitor, right? SQL Server 2012 AlwaysOn Availability Groups allow you to scale out reads across multiple SQL Server instances. You can set up readable secondary copies of a database which have their own independent storage, memory, and server resources. The secondaries can be in either a synchronous or asynchronous mode. You can even offload backups to secondaries.
If anything can render Transactional Replication completely outdated and useless, this is the feature.
It’s absolutely true that Availability Groups are better than transactional replication for high availability. But that’s not surprising– Transactional Replication is terrible for high availability! Even in Standard Edition, a simple two node failover cluster beats Transactional Replication when it comes to HA. So, yes, Availability Groups wins here, but replication doesn’t even make second place. (By a long shot.)
But when it comes to scaling out reads, Availability Groups still aren’t perfect. SQL Server 2012 did introduce a cool new feature where temporary statistics are created for read only databases that helps this feature out, but it doesn’t quite do everything. Availability Group readable secondaries still have some limitations:
- You can’t create indexes on the secondaries– all indexes must be created on the primary
- The more indexes you create on the primary, the heavier your IO load and potential for data churn in maintenance– which makes it harder for secondaries to keep up
- The whole database goes in the availability group. You can’t just include a couple of tables.
Verdict: Using Availability Groups for scale out reads works in some scenarios, but it does not replace Transactional Replication.
Transactional Replication doesn’t solve your HA or DR pains
If you remember one thing from this article, make it this fact: Transactional Replication is not a good way to save your bacon if a server fails or a database gets corrupted. This isn’t a feature for high availability and disaster recovery– it’s much more of a programmability feature to distribute data from a few critical tables from one instance out to many subscribers.
Transactional Replication does NOT support any of the following:
- Failover (or fail-back)
- Connection string enhancements
- Load balancing
- Automatic page repair from corruption
- Protection of the replicated data (it can be updated, made incorrect, etc)
For high availability and disaster recovery, you want to look primarily at other features in SQL Server.
What if you need to write to multiple masters?
If you’ve got a datacenter in China, a datacenter in Europe, and a datacenter in the United States, you probably don’t want to send all of your writes to just one datacenter. You probably want to send writes to the closest local datacenter and have changes synchronize between databases in all of them.
There’s only one feature in SQL Server that helps with this: peer to peer replication. It’s an Enterprise Edition feature which is like two-way transactional replication. Peer to peer replication requires that you handle conflict resolution, and isn’t compatible with all newer features (example: Availability Groups).
Transactional replication has a feature called Updatable Subscriptions that allows changes at a subscriber to be sent back around to the publisher, but the feature has been deprecated in SQL Server 2012. You’re recommended to look to Peer to Peer replication instead.
Requirements for supporting replication
I have a confession to make: I like Transactional Replication. There’s something about it which I admire– its flexibility, its tenacity, its ability to endlessly survive and propagate itself through more and more versions of SQL Server while other features continuously try and fail to exterminate it.
I don’t often recommend that clients use Transactional Replication. Frequently, there are other ways to solve a problem without taking on the burden of supporting Transactional Replication. It’s just not that easy to support.
If you do think you need Transactional Replication, make sure you have the right staff in place. For mission critical data, you need to have multiple, dedicated SQL Server DBAs, an established on-call rotation, a good incident response system, root cause analysis, a pre-production environment, good Change Management processes, and the ability and desire to refine and extend production monitoring.
That may sound like a lot, but trust me, the first time someone asks, “why is the data on the subscriber so old?”, you’re going to realize why I listed a lot of things. One thing you can count on with replication is that there’s sure to be interesting times along the way.
Jeremiah, Brent and I will heading to the SQLIntersection Conference in Las Vegas from October 27-31. Here’s why you should join us.
You’ve got room to be heard
SQLIntersection is held in fast-paced Las Vegas, but it has a small town feel. This is a new conference. It doesn’t have the “hectic family reunion” feeling of some other big SQL Server events yet. That’s actually really cool! You’ve got loads of chances to ask the super talented presenters about how you can solve your own technical challenges. (It’s so great to be part of this group!)
To make the most of this, just remember:
- Presenters want your questions– we love it when you ask that question in the back of your mind during a session.
- Bring your business cards to trade. They don’t have to be fancy, and you can make your own. (People just want help remembering names.)
We’re giving a pre-con AND a post-con
Jeremiah, Brent and I are giving two full-day workshops. At our last SQLIntersection post-con, we demonstrated deadlocks by causing a presentation remote to explode, then later got into a bit of trouble because our class laughed so loudly they scared the neighbors. We’re going to keep it fun, action packed, and uber-geeky.
Join us for one (or both) of these events:
Accidental DBA Starter Kit
Brent, Jeremiah, and Kendra: Pre-Con Sunday, Oct 27
You’re responsible for managing SQL Servers, but you’ve never had formal training. You’re not entirely sure what’s going on inside this black box, and you need a fast education on how SQL Server works. In one day, you’ll learn how to make your SQL Server faster and more reliable. You’ll leave armed with free scripts to help you find health problems and bottlenecks, a digital set of posters that explains how SQL Server works, and an e-book that will keep your lessons moving forward over the next 6-12 months.
Scale Up or Scale Out: When NOLOCK Isn’t Enough
Brent, Jeremiah, and Kendra: Post-Con Thursday, Oct 31
Partitioning, replication, caching, sharding, AlwaysOn Availability Groups, Enterprise Edition, bigger boxes, or good old NOLOCK? You need to handle more data and deliver faster queries, but the options are confusing. In this full-day workshop, Brent, Kendra, and Jeremiah will share the techniques they use to speed up SQL Server environments both by scaling up and scaling out. We’ll share what features might save you hundreds of development hours, what features have been a struggle to implement, and how you can tell the difference. This workshop is for developers and DBAs who need to plan long term changes to their environment.
You can specialize using session tracks
I love that the SQLIntersection schedule is arranged by “track”. Want to focus on performance? You can attend three sessions in a row on perf-related topics. Interested in High Availability? You can do a whole block of that too.
If you’re looking to broaden your experiences instead of specialize, you can still cherry pick the sessions that you want to attend the most regardless of track. You can even go super broad and check out app dev sessions at DevIntersection at no extra cost.
Last chance to get a registration deal
For $1,994 before Sept 16th, you can get the Show Package: the conference, PLUS a pre-con or post-con of your choice, PLUS your choice of a Surface RT, Xbox, or a $300 gift card. (I know which one I’d pick– do you?)
For $2,394, you get all that plus ANOTHER pre-con or post-con — and that’s a whole lotta SQL Server training for one conference.
Use the discount code OZAR when you register to get another $100 off. (Clever code, isn’t it?) Register here.
Once up on a time, there was a database server with 500GB of data and a heavy read workload of dynamic queries. Data was updated frequently throughout the day and index tuning was a serious challenge. At the best of times, performance was dicey.
Then things went bad
Application performance plummeted. Lots of code changes had been released recently, data was growing rapidly, and the hardware wasn’t the absolute freshest. There was no single smoking gun– there were 20 smoking guns!
A team was formed of developers and IT staff to tackle the performance issue. Early in the process they reviewed maintenance on the database server. Someone asked about index fragmentation. The DBA manager said, “Of course we’re handling fragmentation!” But a few queries were run and some large, seriously fragmented indexes were discovered in production.
The DBA explained that fragmentation wasn’t the problem. She didn’t have automated index maintenance set up, but she periodically manually defragmented indexes that were more than 75% fragmented.
Bad, meet ugly
At this point the whole performance team flipped out. Trust disappeared. Managers squirmed. More managers were called in. The DBA tried to change the subject, but it was just too late. More than a week was wasted over Fragmentation-Gate. It was a huge, embarrassing distraction, and it solved nothing.
Here’s the deal– the DBA was right. Fragmentation wasn’t the root cause of the performance problem. The DBA was a super smart person and very talented at performance tuning, too! (And no, I’m not secretly talking about myself here– this is not the “royal she”.) But she made a strategic miscalculation: she should have set up occasional automated index maintenance to align with her team’s normal practices and standards.
Why you need automated index maintenance
When performance gets bad, one of the very first things people look at is whether systems involved are configured according to best practices. If you’re not following a best practice, you need to have a really good reason for it.
Regular index maintenance still has a lot of merit: even in Shangri-La, where your data all fits into memory and your storage system is a rockstar with random IO, index maintenance can help make sure that you don’t have a lot of empty space wasting loads of memory.
It’s still a good idea to automate index maintenance. Absolutely don’t go too crazy with it– monitor the runtime and IO use and run it only at low volume times to make sure it helps more than it hurts. Be careful, but don’t skip it.
How much downtime can you spare?
Before you implement index maintenance, find out how much time tables can be offline in each of your databases. Then, figure out what operations you want to use.
To Rebuild or Reorganize: That is the Question
First off: ‘Reorganize’ and ‘Rebuild’ are two different operations that each reduce fragmentation in an index. They work differently toward the same end. You don’t need to run both against the same index. (I sometimes find that people are doing both against every index in a maintenance plan. That’s just double the work and NOT double the fun.)
Rebuild: An index ‘rebuild’ creates a fresh, sparkling new structure for the index. If the index is disabled, rebuilding brings it back to life. You can apply a new fillfactor when you rebuild an index. If you cancel a rebuild operation midway, it must roll back (and if it’s being done offline, that can take a while).
Reorganize: This option is more lightweight. It runs through the leaf level of the index, and as it goes it fixes physical ordering of pages and also compacts pages to apply any previously set fillfactor settings. This operation is always online, and if you cancel it then it’s able to just stop where it is (it doesn’t have a giant operation to rollback).
Factors to consider:
- Standard Edition rebuilds ain’t awesome. If you’ve got SQL Server Standard Edition, index rebuilds are always an offline operation. Bad news: they’re also single-threaded. (Ouch!)
- Enterprise Edition rebuilds have gotchas. With SQL Server Enterprise Edition, you can specify an online rebuild — unless the index contains large object types. (This restriction is relaxed somewhat in SQL Server 2012). You can also use parallelism when creating or rebuilding an index– and that can save a whole lot of time. Even with an online rebuild, a schema modification lock (SCH-M) is needed at the time the fresh new index is put in place. This is an exclusive lock and in highly concurrent environments, getting it can be a big (blocking) problem.
- Rebuilding partitioned tables is especially tricky. You can rebuild an entire partitioned index online– but nobody really wants to do that because they’re huge! The whole idea behind horizontal partitioning is to break data into more manageable chunks, right? Unfortunately, partition level rebuilds are offline until SQL Server 2014.
- Reorganizing can be pretty cool. ‘Reorganizing’ an index is always an online op, no matter what edition of SQL Server you’re using. It doesn’t require a schema mod lock, so it can provide better concurrency. Reorganizing only defragments the leaf level of the index. On large tables it can take longer than a rebuild would take, too. But as I said above, it’s nice that you can reorganize for a while and then stop without facing a massive rollback.
SQL SERVER 2014: WAIT_AT_LOW_PRIORITY, MAX_DURATION, AND ABORT_AFTER_WAIT
I’m really excited about new index rebuild options that are shipping in SQL Server 2014. Check *this* out:
ALTER INDEX OhSoFragmented ON dbo.MyTable REBUILD WITH ( ONLINE = ON (WAIT_AT_LOW_PRIORITY (MAX_DURATION= 5, ABORT_AFTER_WAIT=BLOCKERS) ) );
So we’ve got new tools for those concurrency problems I was talking about with online rebuilds. Now, we can say how long we’re willing to wait to get that schema modification lock (in minutes), and if we have to keep waiting what should happen. (Options: kill those who are blocking us, kill ourself, or do nothing.) Obviously there are some situations where just killing the blockers could be a terrible problem, but I’m interested to experiment with this.
You Didn’t Answer the Question: Do I Use Rebuild or Reorganize?
Yeah, I totally dodged that question, didn’t I?
If you have a regularly scheduled downtime every weekend, you’re probably fine with straight up index rebuilds, even if you have Standard Edition. Single threaded offline index maintenance may not be the hottest thing in the world, but hey, if you’ve got time for it then embrace the simplicity.
If you have Enterprise Edition, embrace parallel index rebuilds– and use the ONLINE option for indexes that allow it if people need to access the database during your maintenance window.
If you have database mirroring or AlwaysOn Availability Groups, tread with caution– particularly with rebuilds. It’s easy to generate a ton of IO with index maintenance, and it could mean putting your secondaries or mirror so far behind that they can’t catch up.
Maintenance plans or custom scripts?
You can go the easy way and use SQL Server Maintenance Plans, but unfortunately they’re very simplistic: you can only say “rebuild all the indexes” or “reorganize all the indexes”. You cannot say, “If the index is 45% or more fragmented, rebuild it– otherwise do nothing.” If you don’t spend much time with SQL Server and you’ve got downtime available every weekend, this can be a decent option.
If you need to minimize downtime, custom index maintenance scripts are the way to go. Our favorite: Ola Hallengren’s maintenance scripts. These are super flexible, well documented, and … free! The scripts have all sorts of cool options like time boxing and statistics maintenance.
Some tips for using Ola Hallengren’s index maintenance scripts:
- Download and configure them on a test instance first. There’s a lot of options on parameters, and you’ll need to play with them.
- Get used the ‘cmdexec’ job step types. When you install the scripts you’ll see that the SQL Server Agent jobs run index maintenance using a call to sqlcmd.exe in an MSDOS style step. That’s by design!
- Use the examples on the website. If you scroll to the bottom of the index maintenance page you’ll find all sorts of examples showing how to get the procedure to do different useful things.
Find out when maintenance fails
Don’t forget to make sure that your maintenance jobs are successfully logging their progress. Set up Database Mail and operators so jobs let you know if they fail.
Tell your boss you did a good thing
Finally, write up a quick summary of what you did, why you chose custom scripts or maintenance plans, and why. Share it with your manager and explain that you’ve set up automated index maintenance as a proactive step.
Having your manager know you’re taking the time to follow best practices certainly won’t hurt– and one of these days, it just might help you out. (Even if it’s just by keeping everyone from following a red herring.)