If you’re using AGs, don’t apply these patches:
- SQL 2012 SP2 CU3
- SQL 2012 SP2 CU4
- SQL 2014 CU5
until you read this Microsoft post about a breaking bug.
Your AG may stop synchronizing due to blocking between user queries and a system session. The fix is to disable automatic failover, restart the primary, and enable automatic failover again.
You’re a developer or a DBA, and you’re comfortable writing queries to get the data you need. You’re much less comfortable trying to design the right indexes for your database server.
Join me on Wednesday, Jan 28, for a free one-hour session explaining how the SQL Server engine looks at your indexes and builds your query results.
- The differences between clustered and nonclustered indexes
- How (and when) to make a covering index
- The basics of execution plans
- What determines sargability
- What SQL Server uses to estimate the memory your query needs
- What happens when those estimates are wrong
- And it’s all demoed with these Stack Overflow database pages (PDF)
I’m not kidding when I say it’s the best session I’ve ever written, and I’m really proud of it. This session is normally $29 in our online store, but thanks to Idera, this live version will be free. Register now.
When we first started our company, we laid out our goals in a shared Google Doc. We wrote about what we wanted to do, how we wanted to treat our employees and customers, and the growth numbers we wanted to achieve. One of our original goals was to hire one consultant per year.
In 2012, it was Jes Schultz Borland, and in 2013, it was Doug Lane. We held off adding a consultant in 2014 because we wanted to be financially able to hire two consultants at once in the next round. I’m not saying we WILL, I’m just saying we wanted to be ABLE to. See, one of our other goals is to be able to survive a 9/11-type business event – no incoming revenue for several months, and know that we can still pay everybody’s paychecks for that duration. That means we’re a much slower-growing consulting company, but everybody involved can sleep soundly at night.
In 2013, we also added a part-time admin assistant – my wife, Erika. She was previously a legal assistant at a downtown Chicago law firm, managing administrative and office work, so she’s been able to clean up a lot of our messes. (Scheduling training classes, selling videos via credit cards, helping out our accountants, and sending out Christmas cards with stickers requires a surprisingly high amount of time.) Having Erika on the team has really helped free us up to focus on the technology parts of the job that we love.
In 2014, we realized that just like admin duties, handling incoming sales requests had become a pretty big chunk of time too. Do the numbers: we have 5 consultants, and our main product is our 4-day SQL Critical Care®. That means we sign 4-5 new clients per week, which translates into hundreds of clients per year. That’s a lot of emails to answer, contracts to sign, and engagements to schedule. I do that work today in my spare time – and oddly, I really enjoy it – but if we’re going to scale this little shop, we’re gonna have to bring in a sales pro to manage this process full time.
So on our Employment Opportunities page, we added a
job description for our new salesperson.
We’re looking for a salesperson with experience. If you’ve been working with SQL Server for the last several years, and only SQL Server, it’s probably not a good fit. (That means most, if not all, of our readers, ironically.) However, you might know someone who’s a good fit, so we’re putting this out there.
We’re excited to bring them on board and get our sales process polished – because hey, that means we can hire more consultants and trainers, woohoo!
Update Jan 27 – we’ve got the resumes we need. Thanks everybody!
To figure it out, we have to define what the words senior and DBA even mean. I explain in this video.
To follow along, print this image out:
Kendra says: The part of this that resonated with me most was how to work with your existing management to show that you’ve been growing your skills – I wish I’d heard that back when I was struggling to figure out how to be a Senior DBA!
Jeremiah says: The best part of this was the reminder that I don’t need to be a specialist everywhere. If I had kept this advice in mind when I was struggling to become a Senior DBA.
Jes’s thoughts: my favorite advice from this video is to focus on 1-2 things to learn in the next 6-12 months, and make a plan for that. When I started, I assumed I’d be able to learn everything at the same rate – I know now that isn’t true. In 2015, I plan to learn more about Extended Events and virtualizing with VMware.
Doug says: Love it. It’s all true in my experience too — you can’t become a specialist without focus, you can’t focus without a plan, and you won’t get noticed for a promotion (or a new job) if you aren’t really good at a few things. No one in the SQL Server world ever made for themselves a reputation for being good at everything.
And the next step after finishing the video and building your learning plan: learn to say no.
When you’re trying to fix a problem (or even just researching something you’re curious about), do what scientists do.
1. Ask a question. “Why is this database so slow?”
2. Do background research. Hit Google, check Stack Exchange, maybe even read Books Online.
3. Construct a hypothesis. “I think this database is slow because I don’t have Lock Pages in Memory enabled.”
4. Test your hypothesis with an experiment. “On Saturday night during the change control window, I’ll enable Lock Pages in Memory. After that change, I believe that Page Life Expectancy will be higher during our normal business hour load periods – it’s currently 300, and I expect to see it go up.” Pick the exact metric you want to watch, and explain – in advance – how you expect that metric to change.
5. Analyze your data and draw a conclusion. “I measured PLE, but it still hovers around 200-300 during our weekday peak times. Therefore, enabling Lock Pages in Memory was not the solution I needed.”
6. If it didn’t get the result you expected, put it back. This is the part most folks miss – we make changes willy-nilly when we’re troubleshooting, and we leave behind all kinds of crazy sp_configure options, trace flags, and registry settings. The next time you have a problem on the server, it’s hard to know what settings we changed on purpose – and which unrelated ones we just button-bashed.
I can hear the experienced DBAs in the crowd screaming out that we should never run experiments in production, and that’s somewhat true. In a perfect world, we’d all have exact replicas of our production environment, and the ability to run production-quality loads. We don’t. Let’s start by taking the first step – making sure our production changes have the effect we expect.
Ever wanted to quickly and easily record how much CPU a query is using, how many logical reads it uses, what temporary objects are created, and whether it causes a recompile? sp_BlitzTrace™ is a free stored procedure that helps you quickly and easily use Extended Events in SQL Server 2012 and higher to observe what’s happening in one SQL Server session in close detail. Learn how you can install and run this tool to learn more about what your queries are doing.
Want to read other team members; take on the video? Scroll on down to read what people thought.
Jes thinks: What an easy way to get introduced to Extended Events! There can be so much to learn about them – this is an easy tool to get started with. This is a good way to measure the impact of queries.
Kendra thinks: I never want to write XQuery again.
Brent says: I’ve always smiled-and-nodded my way past Extended Events because it’s chock full of nuts. (“Oh hey, the ring buffer doesn’t work. Ooo look, you get to query XML. Hey, it’s different across different versions of SQL Server.”) This tool makes XE easy enough that even Brent can use it.
Jeremiah says: Extended Events are a really helpful tool, but there’s a steep learning curve. This is the kind of on ramp that I wanted when I first started using Extended Events.
Doug says: I’m a fan of this tool because 1) it gets useful information from XE without getting that XE smell on my hands, 2) it tells me all kinds of great stuff in the messages tab, including how to stop it, 3) it only does one session, so it’s not as dangerous as a tool that might attempt to watch everyone (sp_BlitzSauron).
You may have heard the term “commodity hardware” thrown around when describing solutions like Redis, Elasticsearch, or kCura’s new Data Grid.
Commodity hardware refers to cheap, standardized servers that are easy to buy off the shelf from any vendor. Here’s a typical example of a 2u, 2CPU commodity hardware server:
- Supermicro SYS-1028R-TDW 1U rack server
- Two Xeon E5-2600 v3 CPU sockets
- 16 RAM slots, holding up to 1TB RAM
- 8 2.5″ hot-swap SATA 3 drive bays
- 2 PCI-E 3.0 x16 full height half length slots
- $900 at NewEgg
Say we buy a few parts to get ‘er started:
- Two Intel Xeon E5-2623 v3’s (quad core) – $900 total
- 128GB RAM (using 8GB DIMMs) – $1,920
- Two 512GB SSDs for fast storage – $450
- Six 4TB hard drives for slow storage – $900
- Grand total: $5,070
Not bad. Want a little more power? Here’s a faster config:
- Two Intel Xeon E5-2623 v3’s (quad core) – $900 total
- 256GB RAM (using 16GB DIMMs) – $3,500
- 8 1TB SSDs – $2,600
- Grand total: $7,900.
The term “commodity hardware” used to mean really crappy gear, but when you look at these numbers, that’s not necessarily the case anymore. You can build yourself quite the army of pizza boxes.
When vendors say, “You can deploy our solution on commodity hardware,” they’re not saying you’re going to get amazing performance with 16GB of RAM and a couple of spinning rusty frisbees. It’s time to reset your expectations about what commodity means.
Performance tuning of independent software vendor (ISV) databases is a little tricky.
To understand who does what, let’s think through all of the work required with building and hosting a database application:
Typically the two parties involved – the vendor and the customer – start at opposite ends of the table. The software vendor starts by designing their tables, and gradually works down. The customer starts by buying hardware, and works their way up.
In the middle, things kinda fall apart.
When I was a DBA, I thought it was the vendor’s responsibility to put the right indexes on their tables. It’s their app, right? Aren’t they supposed to know how people query their own app? Why am I responsible for reinventing that wheel? Will I lose support if I add indexes?
Now that I’ve worked with a lot of ISVs, I see things differently. Here are their challenges:
Every customer uses the app differently. Some customers really hammer a particular feature hard, whereas others never use it at all. If you index for every possible feature/query, then the database will be dead slow for delete/update/insert operations.
Customers don’t give feature usage or index usage data back to the ISV. Software vendors have no way of knowing what indexes are working well out in the field, or sometimes even know what queries are running on the server. This comes back to our responsibilities grid at the beginning of the post – often even the customers aren’t running any performance monitoring software on their app at all.
Customers won’t upgrade to current versions quickly. Sometimes the ISV fixes performance by improving queries or adding the right indexes, but they only add it in the current version of the software. It takes development time to backport the fixes to older versions, and software vendors only have so much time. If you ask end users if they’d rather pay for new features or backporting stuff to older versions, they vote with their wallet.
Some customers have really bad DBAs (or none at all). Time and again, I’ve seen “DBAs” add dozens – or in one case hundreds – of indexes to an ISV’s tables in an effort to improve performance. The first few indexes make things better, so they start solving every problem by adding more indexes. You can guess how that ends up. As a result, the ISV’s support team starts making rules like “no customer is allowed to add indexes.”
Building a Successful Relationship With Your ISV
When you install a software vendor’s database, show them the list of responsibilities at the top of this post. Say, “I agree to do the stuff in my column – if you’ve got any guidance on things you’d like me to do for your application, send ‘em over, and I’ll be glad to follow them.” For example, the vendor may have a list of trace flags they’d like to enable for their app.
Then say, “For the stuff in the middle, who’s responsible?”
If the vendor agrees to tune indexes, then you’ll probably need to give them index usage data and plan cache data on a regular basis to help them tune. I like to be my software vendor’s best customer – I’ll say, “I’ll send you the output of sp_BlitzIndex® and sp_BlitzCache® every week or every month, whatever works for you. I’ll be glad to take my time working with you on the output, and together, we can make the software experience awesome for everybody.”
You’ll be surprised by how often they accept because they’re desperate to find customers willing to give them some diagnostic data about how their software works in the wild.
If the vendor says you’re responsible for tuning indexes, then:
- Instead of dropping their indexes, disable them, and let their support team know which ones you’re disabling
- Create your own indexes with your company’s initials, like BGO_CustomerNumber_CustomerName, so you can quickly identify which ones are yours versus which shipped with the product
- Run sp_BlitzIndex® every month to make sure your indexes are actually helping, not hurting
- When it’s time to deploy a new version of their app, enable the vendor’s indexes again first, drop your own indexes, run their deployment scripts, and then set things back the way you had ‘em (this way you minimize the chance of their deployment scripts breaking)
There’s no right or wrong choice – for example, Sharepoint customers aren’t allowed to touch the database, whereas SAP customers are encouraged to do their own index tuning through the SAP UI. The key is knowing which method your vendor supports right from the start. Otherwise, both sides just assume the other side is taking care of the problem.
Over the years, I’ve come up with some rather “interesting” answers to SQL Server problems including:
- Warming up SQL Server’s buffer pool by selecting all the data from all the tables
- Creating indexes with triggers
- Adding nonclustered indexes to clustered columnstore indexes
- Compressing backups the hard way
- Putting databases on a RAM drive
In this 20-minute video, I’ll explain what drove me to these crazy solutions, and it’ll be up to you to decide whether they’re awesome or awful.
For questions & answers about these, tune in to our Tuesday webcast.