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…
SQL Server Denali CTP3 is here, and today I'm excited to be able to talk about this new public preview of the next version of SQL Server. CTP1 came out back in November 2010, and CTP2 wasn't released to the public, so this is your first chance in several months to see progress. Here's what you want to know:
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
Managing data is about managing risk, but no matter how we good we are at managing risks, they're still risks.
We've seen several high-profile data failures recently:
You're overworked. You don't have the time to sit around reading blogs, keeping up on the latest industry news, and reading web comics.
That's where we come in. To stay on top of our game, we have to spend a couple days per week honing our skills. We've started a weekly email recapping the best stuff we've found in the community this week.
So you're lazy and you're company's broke - what to do? Here's my favorite free downloads to help manage SQL Server: Understand execution plans with SQL Sentry Plan Explorer - I dunno about you, but viewing execution plans in SQL Server Management Studio is a pain in my rear. The scrolling sucks, the cost numbers…
When you need SQL Server to handle more load, there's two big-picture ways to do it:
Scale up - use a more powerful SQL Server
Scale out - use more SQL Servers
Microsoft Atlanta is a new cloud-based SQL Server monitoring tool. Companies can deploy an agent on their SQL Server 2008 and 2008 R2 instances, and the agent will communicate up to Microsoft's servers in the cloud (through a secure gateway also installed inside the company's network.) Microsoft aggregates the data, analyzes it looking for problems, and displays it back to IT users like database administrators through a web-based dashboard.
SQL Server database administrators need to be able to quickly find out what queries and stored procedures are running slow. Microsoft includes sp_who and sp_who2 in SQL Server 2005 and 2008, but there's a much better tool, and it's completely free.
In this five minute tutorial video, I explain how to use sp_WhoIsActive from Adam Machanic (Blog - @AdamMachanic):
Microsoft records TechEd sessions, and you can watch the recording of mine. Here's the abstract:
You’ve heard it before: “It worked fine on my machine, but the users say it’s too slow.” Don’t blame the developers: they’re using SQL Server features that look great on paper, but in reality, they won’t scale up to production loads. Learn to recognize these common mistakes before they go into production and be armed with alternatives.
The SQL Server 2008 R2 release date was April 21, 2010. You can download SQL Server free as an evaluation version.
I've written a lot about the new features:
Crystal meth helps you lose weight and feel better about yourself. It's true. Wikipedia says so. Sure, the side effects of methamphetamine are more than just anorexia and euphoria, but if you're looking to be an upbeat, skinny person, meth gets the job done. SQL Server has a couple of similar features that might make…
This week I'm focusing on books and the book business. Today I'll be covering the money thing for authors, and tomorrow I'll be reviewing a book I like for both the content and the business. Enjoy!
Now that I'm holding a physical copy of our book in my hand, the pain of the experience has started to fade, and I can write about it with an open mind.
Paul Randal (Blog - @PaulRandal) tagged me in a post about the three events that brought him here. I had to think about this one for a while, and I even talked it over with Erika. Discovering BBSes Long before the public got access to the Internet, I was logging onto Bulletin Board Systems (BBSes)…
You've got questions, we've got answers.
No, not Radio Shack - Twitter and the SQLcommunity Slack. Right now, no matter when you're reading this, there are people on Twitter who can answer quick questions for you. Thanks to an excellent idea by Aaron Nelson (@SQLVariant), it's even easier now. In this post, I'll show how to ask questions and how to answer them.
How to Ask #SQLHelp Questions in Slack
At #SQLBits, I was quite excited to attend this session because of the presenter. Christian Bolton of COEO (Twitter) was the head author on our upcoming book SQL Server 2008 Internals and Troubleshooting, and he's one of the only 8 Microsoft Certified Masters on SQL Server that work outside of Microsoft. Physical vs Virtual Memory…
R2's new Data-Tier Application (DAC) capabilities give the DBA to manage databases as if they were more like virtual servers. Today I'm going to talk about what that means short-term and long-term.
Short-Term: Nothing To See Here, Move Along
I don't think most DBAs will see a .dacpack file in the wild for years:
When you're hiring a Windows desktop administrator, here's the best questions to ask. Includes questions like, "What's a Windows profile? When would you delete one, and what gets deleted?" These are also good for environments where the sysadmins have to wear multiple hats, and they also touch servers.
The good news: in the official press release announcing the winners of the Chicago-Mac sailing race, the Hannah Frances was included along with the winners. The bad news was the way we got mentioned: "While 33 boats chose to retire, most citing reasons related to the slow conditions such as lack of provisions or crew…
