Editors Note: this post was updated in June 2014 to link to an online index rebuild bug that can cause corruption. 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…
When us geeks want something, we just point at it, look at our manager and say, "I want this. Buy it for me."Oddly, it doesn't seem to work.To get what we want, we have to think like a manager.Step 1. Define a user's Pain Point.You need someone else on your side - not another geek,…
Our live webcast topic this week was Q&A - you could bring any HA/DR questions, and we could avoid your answers. Just kidding. Here's the webcast - we apologize for the audio, WebEx is still getting their act together. It cleans up after the first couple of minutes.
https://www.youtube.com/watch?v=G1URY5M09ic
SQL Server needs to have a reasonably correct estimate of how many rows your query is going to handle. If it doesn't, things can go terribly, terribly wrong. Most often, the problem is that SQL Server under estimates its work. In this case, the query optimizer thinks it has just a little bit of work…
When you query sys.dm_os_wait_stats, or check your waits with sp_Blitz® or sp_BlitzFirst®, one of your biggest wait types is probably CXPACKET. Let's talk about what's really going on. Our SQL Server Stand-In: a Class. Pretend that you're a schoolteacher in a classroom with a couple dozen students. You've got a class project that you need to…
Last week, I went to summer camp for geeks, and I had a great time! That Conference is a three-day, developer-focused, family-friendly technology conference. Camp was held for the first time in 2012. I heard such great things about it from last year’s campers that I decided to go this year! The sessions focused on…
You’d love to know how to make your SQL Servers more highly available, but you’re just not sure what “clustering” is all about. We can help! In this 30 minute video Kendra Little shows you how failover clusters make your SQL Servers easier to manage. She uses diagrams and drawings to teach you the strengths and weaknesses of failover clusters and shows why clustering is becoming even more critical for high availability and disaster recovery.
Performance tuning is a weird art to learn, because you do it in public. Sure, you may puzzle over a technical problem at your desk or revise code on your own-- but when it comes to really tuning hard problems, 99% of the time you're doing it with other people. That's the nature of real…
One of the big reasons you spend big money on server-quality hardware is to get cool stuff to make administration easier. Each hardware vendor provides their own software tools - Dell includes OpenManage, IBM includes Director, and HP includes their System Management Homepage.
To illustrate how it works without violating anybody's NDAs, I picked up a used HP DL380 off eBay to use as a demo.
I'm going to tell you a secret. Index tuning is complicated, but it's something you can become great at. You just need to practice it regularly.
Here's that one step: stop thinking index tuning is a problem for Future You.
I about to head out to a vast waterpark in the Midwest to ThatConference, where I'm going to hang out with 1000 developers, their families, and some wild animals. I'm giving a session Monday called "How to Make Deploying Database Changes Fun Again". I've had a blast distilling everything I've learned over the years about how to…
When they finally sat down to define their relationship, he was stunned to discover that he had a child table.
...
I came across an interesting case recently where queries were failing in a high transaction SQL Server. We knew that queries were occasionally failing with error severity 20 because SQL Server Agent alerts were periodically firing. However, those built in alerts don't capture exactly what has failed-- they just say "Something failed big. Good luck with that."
Recently, Jes asked the team an index tuning question: "If a query has an index hint in it, will the optimizer ever suggest a missing index for that query?"
I immediately loved the question because I'd never really thought about it before. I typically think of index hints as being a very risky game and avoid them whenever I can-- after all if someone drops the index you've hinted, any query hinting a non-existent index will start to fail. (That's a really bad day!)
Every release lately, Microsoft has been turning the screws on Standard Edition users. We get less CPU power, less memory, and few (if any) new features. According to Microsoft, if you want to use more than $500 worth of memory in your server, you have to step up to Enterprise Edition. Seriously? Standard Edition licensing…
Do you want to make your database server faster?
If you answered no, you can go back to manually checking that all of last night’s backups succeeded.
Did you know that you can ship a bobcat 1/30th of the time and still maintain 97% positive feedback on ebay?
What other statistical lies are lurking out there for you to find?
When you download and start playing with SQL Server 2014 CTP1, here's a few of the new instrumentation tables and views:New Internal Tablesplan_persist_context_settingsplan_persist_plan - Hey, lookie there! Implies that we can persist execution plans beyond a SQL Server service restart.plan_persist_queryplan_persist_query_textplan_persist_runtime_statsplan_persist_runtime_stats_intervalNew Viewscolumn_store_row_groupsdm_db_merge_requestsdm_db_xtp_checkpoint - Note that both the DMVs and the SSMS UI imply that Hekaton will be…
Some query hints sound too good to be true. And, unfortunately, usually they aren't quite as magical as they might seem.
Frustration with unpredictable execution times
People often learn about parameter sniffing when query execution times stop being predictable. Occasionally you'll hear about a stored procedure taking much longer than normal, but the next time you look, it might be faster.
When it comes to indexes, SQL Server is really helpful. It lets you see what indexes queries are asking for both in execution plans, and missing index dynamic management views ("DMVs"). I like to look at the DMV missing index requests using sp_BlitzIndex®.
When you look at missing index requests, it's always important to remember one of the biggest things: these missing index requests won't ever ask for or recommend a specific clustered index.
