Years ago, I came across an article about a gentleman who made a cat camera. Without boring you too much, this camera gave him a detailed view (including GPS info) about what his cats got up to all day. I got excited about the idea of finding out what my pets did all day. Not having…
The cloud is robust and reliable. The cloud solves all of our scaling needs. The cloud makes my poop smell like roses. While all of these statements are theoretically true it takes some effort to make them true in reality, especially when a database is involved. Who Is Deploying SQL Server in EC2? A question…
If you're only indexing to make reads faster, you need to think again. Indexes can make every operation in the database faster, even deletes. The Problem: Deletes are Very Slow I was lazily researching new development techniques one day when I received an email from a client asking why deletes could be slow. I rattled…
Everyone poops and everyone grows. We all reach that growth stage in our own time, but sooner or later we all encounter it. Once you reach a growth stage you will experience some of these common emotions.
Denial
It's 8:37AM and your developers just barged into your cubicle yelling, "The database can't handle the load!" What's your first reaction? If you're like me, you probably threw a stapler at them. If you're a bit more level-headed, you most likely said something like, "That's unpossible!"
Deadlocks can kill an application's performance. Users will complain about the app being slow or broken. Developers will ask the DBA to fix the problem, DBAs will push the problem back on developers. The next thing you know, the office looks like Lord of the Flies.
What is a Deadlock?
Aggregations and grouping can be a pain in the rear for the novice SQL developer. Way back in 2003, the ANSI/ISO standards people figured this out and added windows and ranking functions to the standard. In 2005, Microsoft added a few of these functions (ROW_NUMBER(), RANK(), DENSE_RANK(), and NTILE()) as well as the OVER() clause…
Let's face it, there are a lot of cool new things you could be learning right now. It seems like there's a new technology coming out every 12.8 seconds. Why the hell would you want to spend your free time learning a crufty old language like SQL? My reasons, let me show you them.
It's portable
Anyone who has worked with replication has run into problems caused by conflicting data. I've lost enough sleep over replication to know that having an effective plan for conflict resolution is important to keeping replication running and healthy.
What Causes Conflicts?
Scaling up is hard, scaling out is even harder. We all know that we can scale reads by adding some kind of replication or read-only copies of databases or using a massive caching layer. What happens when we have to scale writes? Borrowing Brent's grocery store metaphor, a database that is effectively scaled for reads…
Did you know that SQL Server's locking has a name? It's called two-phase locking. If we're really getting specific about it, SQL Server uses what's called strong strict two-phase locking or SS2PL. We'll get there in a few minutes, right now we're going to take a look at what makes up two-phase locking.
But First, Some History
The fastest query is one you never execute. The premise is that one of the slowest parts of starting up an application isn't starting the application itself, it's loading the initial application state. This can become a problem when you're loading many copies of your application on many servers, especially you're in the cloud and…
