Q&A with Adam Machanic
Who’s Afraid of SQLCLR?
You could almost hear the gasps of horror when Microsoft integrated the .NET Common Language Runtime (CLR) into SQL Server 2005. Run C# or VB.NET code inside SQL Server? For many database developers and administrators, it was like an alien had just burst through SQL Server’s midsection.
But SQL Server MVP Adam Machanic says that more and more developers and DBAs are discovering that far from destroying SQL Server performance and security, SQLCLR is actually a powerful ally in solving complex business and technical problems. In his Nov. 2 PASS Summit Unite 2009 pre-conference seminar, “SQLCLR from Beginner to Expert,” Adam explores the best practices for creating robust, secure, and high-performing SQLCLR frameworks. Here, he gives us a sneak peek.
Q: A lot of our Q&A interviews talk about the biggest mistakes, misconceptions, or myths that surround particular technologies. Let’s change that up a bit by talking first about what you see people doing right with SQLCLR. What concrete benefits are you seeing in the SQL Server world?
A: SQLCLR, when properly applied, can greatly help with the performance of a variety of different query types. Many times, complex queries can be simplified, run through C# or VB.NET logic, and the end result—to many SQL Server developers' surprise—is that performance can improve by a huge margin. The trick is doing things properly and keeping a few basic ideas in mind: limiting memory utilization, making sure that code is solid and well reviewed, and adhering to .NET development best practices, which are not always quite the same as T-SQL best practices.
Q: What is the SQLCLR adoption rate? Should SQLCLR have a place in every development shop?
A: SQLCLR adoption, from what I can tell, is rather low at the moment but growing every day as developers discover the wide variety of problems it can help them solve more easily and more efficiently. As a consultant, I have worked with a dozen applications over the past three years in a wide variety of industries, and in almost every case, I've found a solid application for SQLCLR. It is a flexible, open technology stack that can really help drive results and make development tasks both faster and more effective.
Q: Any confusion about what SQLCLR is for and when to use it?
A: Many developers think that SQLCLR is a way to avoid writing T-SQL, but the reality couldn't be any further from the truth. SQLCLR works in conjunction with T-SQL, creating a broader set of options so that when you're faced with a problem, you can choose the right way to solve it. Not every problem is well-suited to T-SQL, and likewise, not every problem is an exact match for what SQLCLR offers. By using them together, we can create more robust solutions that take advantage of the best that each paradigm has to offer.
Q: Do you still see fear surrounding what SQLCLR can open up SQL Server to in terms of security or performance problems? What would you tell SQL Server developers who still aren’t sure about SQLCLR?
A: The unfortunate truth is that many people fear SQLCLR due to a lack of understanding about the technology, how it works, and what it provides. The fact is that, properly applied, SQLCLR will help performance, not hurt it. I spend 75% of my time troubleshooting performance issues for my customers, and SQLCLR has been a fantastic tool in helping me speed up difficult queries, especially those involving time series and other sequential data patterns. When I find good use cases for the technology, my SQLCLR solutions generally outperform their T-SQL equivalents by at least 2-to-1, and sometimes by an order of magnitude or more. In my PASS Summit seminar, I show in detail some of the solutions I've come up with so that attendees can take my experiences back to the office and create great SQLCLR solutions of their own.
Another fear is security, and this is totally unfounded. SQLCLR was designed from the ground up to be as secure as possible, and security—or lack thereof—is squarely in the hands of the DBA managing the server. If the DBA does not feel comfortable with a given assembly, it can be "sandboxed" in what is called the "safe permission set," which is a totally secure mode that limits the assembly to localized data access in addition to math and string operations. DBAs should learn and embrace this technology so that they can make educated decisions about when to limit assemblies and when to let them into less restrictive modes. SQLCLR is an incredibly powerful tool, and locking it out on the basis of lack of understanding only limits potential productivity. In the seminar, I teach developers and DBAs how best to work with SQLCLR in a secure fashion, allowing for technology adoption while at the same time letting DBAs sleep easy at night knowing that their databases are not in any danger.
Q: Considering those who are using SQLCLR, what are the biggest or most common problems? What do developers need to watch out for?
A: SQLCLR should not be looked at as a panacea. It's not going to solve all of your problems, but when properly applied, it will solve many of them. Developers like to jump on a new technology and ride it—for better or for worse—as far as it will take them, and the SQLCLR world is no different. I've heard horror stories about developers rewriting an entire application worth of stored procedures using SQLCLR, causing memory problems and severely limiting scalability. This is not the correct use case for the technology; just like with any other tool, it should be used in moderation, in situations in which it’s appropriate.
Q: What’s the difference between a SQLCLR beginner and an expert, and how do you plan to take people from one level to the other in your seminar?
A: The seminar, at its core, is all about best practices: how best to use SQLCLR, when and where to apply SQLCLR, and how to maximize the technology in a number of scenarios. A SQLCLR expert keeps one thing at the front of his or her mind at all times: memory. Memory is the most expensive, and arguably the most important, resource on a database server. SQL Server loves to chew up as much memory as it can get, and so do CLR modules. Both are fighting for the same memory, and developers who attend my seminar will learn how to develop SQLCLR in such a way as to balance memory consumption. The result is ultra-fast code that will scale while keeping end users (and management) as happy as can be.
Hear more from Adam and other SQL Server experts at PASS Summit Unite 2009 in Seattle, WA. See the lineup of pre/post-conference seminars, review the full conference agenda, and register today.
Adam Machanic—a Boston-based independent database consultant, writer, and speaker—specializes in high-availability OLTP and large-scale data warehouse applications and optimizing data-access layer performance for data-intensive applications. A SQL Server MVP, he writes for many Web sites and magazines, including SQLblog, Simple Talk, Search SQL Server, SQL Server Professional, CoDe, and VSJ. He has also contributed to several SQL Server books, including SQL Server 2008 Internals (Microsoft Press 2009) and Expert SQL Server 2005 Development (Apress 2007).