Speaking at a recent SQL Saturday, an attendee in one of my sessions wanted to know how they could more flexibly react to locks on their application than to wait for blocks to occur and then kill the SPID at the head of the blocking chain. They were also interested in some alternatives to using the SQL Server syntax like the WITH (NOLOCK) hint, since that might have unintended consequences due to allowing reads on uncommitted data.
One alternative I suggested is the SET LOCK_TIMEOUT n statement. Since most of the attendees hadn’t heard of this statement, I figured it’d make a good blog post.
When using the statement, you can set this context for the connection, for a batch of code (such as a function or stored procedure), or for a single SQL statement (excluding a few DDL statements such as CREATE/ALTER DATABASE). By passing a numeric value with the set statement, you specify the number of milliseconds that the statement will wait for a lock to be released before returning a locking error. 0 means don’t wait at all and -1, the default, means wait forever. Once changed, the new setting stays in effect for the remainder of the connection. So you might want to set it back to the default if you want it to apply to only one statement, say a SELECT, in a big batch of statements.
You can also get the same behavior by using the READPAST locking hint.
Hope this helps with those troublesome locking situations! Enjoy,
Here’s an evergreen question. It’s a question that never completely goes away. But lately, I’ve been getting it a few times per week. So I thought it’s time to readdress the question, which usually takes some form of the following:
I can’t really do effective development on my little dev laptop because our production SQL Server database is 15 gazillionbytes, way too big for my workstation. What’s a uber-nerd to do?
Well, maybe they didn’t use the word “uber-nerd”. But you get my drift, right? The production database is really, really big – unmanageably big for keeping a local copy. So that means the dev either has to create a metadata-only version of the database, which won’t produce realistic query plans, or somehow crush their laptop under 15 gazillionbytes of MDF and LDF files.
Actually, you have a better alternative – a clone database, sometimes called a shell database. Here’s how I described a cloned database a few years ago here in my Tool Time column for SQL Server Pro Magazine:
In effect, a cloned database includes all of the schema objects of the database (e.g., tables, views, stored procedures), as well as the statistics and histograms (the so-called “statistics blob”). This metadata is quite small by volume but can tell you what estimated query plans look like outside of a large production environment and how those estimated query plans might change when SQL Server is upgraded. Cloned databases are especially useful when the data is confidential, classified, or subject to privacy laws.
The article gives you all the detail you need to effectively and quickly create a small version of a big, ol’ production database that produces the same query execution plans as you’d get on the prod server.
If you’re struggling with doing development on a big SQL Server database, learn the ropes on cloned databases asap! You’ll be glad you did.
When you’re developing new Transact-SQL code or modifying some existing code, do you just launch directly into programming?
I know that I did just that, for years. It wasn’t until I was trying to performance tune some existing code that I realized I hadn’t actually taken caching of data and execution plans into account. So all those modified stored procedures that I was so proud of might not actually be faster than the first generation of procedures because I hadn’t checked to ensure that I was testing cached programs against uncached programs (and, by extension, the data used by those programs). That’s easy enough to fix with a test harness. Test harness were originally an actual, physical harness used by engineers to clamp down parts of an electrical or mechanical device they were prototyping. Ours is no different. It locks down all of the assumptions about our code (like my early, false assumption that I didn’t need to clear the caches) and adds a metric or two for good measure – literally – so we can better measure what’s happening in that code.
Here’s what my test harness looks like:
/* Transact-SQL test harness by Kevin Kline, http://KevinEKline.com, Twitter at kekline */
/* Flush dirty pages from the buffer to the database files. */
CHECKPOINT;
/* Flush the data cache and procedure cache, respectively. For DEV environments only! */
DBCC DROPCLEANBUFFERS;
DBCC FREEPROCCACHE;
/* Enable statistics tracking for IO and timings. Remember, SET commands remain enabled during a session until disabled. */
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
-- Whatever SQL code you'd like to process goes below.
SELECT SalesOrderID
FROM Sales.SalesOrderHeader H
WHERE CustomerID = 344
GO
SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;
/* Textual Execution Plans, if desired.
SET SHOWPLAN_TEXT ON;
SET SHOWPLAN_TEXT OFF;
*/
I also like to include the execution plans a lot of the time. You might wonder why I don’t save the execution plans for the GUI in SSMS? Well, I’m a big advocate of scripting in general because I like to automate activities. By pulling the execution plans using scripts, I can use SQLCMD to schedule a large number of query executions during the evening and have the results ready for analysis when I come back into the office in the morning. Workin’ smarter, not harder, Baby!
So how does this test harness work for you? Do you use other elements in yours? If so, share your experiences here!
There are so many great tools out there for data professionals using Microsoft SQL Server. I really like to see all of these great tools made free to the public. On the other hand, I’m bummed that the tools are cast about in a very decentralized fashion. If you haven’t done migrations before, you might want to start with these good white papers first.
Here are a hand full of cool migration tools worth mentioning:
SQL Server Migration Assistant (SSMA) for Oracle: Migrate from Oracle to SQL Server 2005, SQL Server 2008 or SQL Server 2008 R2. I’m thinking about installing it on my SQL Servers even without even needing to migrate existing Oracle databases to SQL Server. Why? Well as an old Oracle hand, I came to really enjoy quite a few Oracle PL/SQL system packages (kind’a like a SQL Server system stored procedure, but often more powerful). As it turns out SSMA-Oracle includes stored procedures, extended stored procedures, and CLR routines that reproduce the functionality in most all of the cool and powerful Oracle packages like DBMS_PIPES. It’d be nice to have those on my SQL Servers just because I know them and like them.
Microsoft Services for Mission Critical Customers: Many enterprise customers running mission critical applications on SQL Server have asked for more – more service and support for their environments. This is an add-on that costs extra, but it’s worth it for those running the systems that keep the company in business.
If you’ve tried any of these tools out, I’m keen to hear your experiences. Did they work well for you? Did they work, though poorly? Did they fail utterly? Inquiring minds want to know.
I was just bragging about how Toad for SQL Server keeps getting better. In that post, I also pointed out a lot of great resources you can put to work immediately on improving your skills with this great tool. (Incidentally, there’s a freeware version without all of the features, but it’s still quite useful. And you can always use the beta product, if you want all of the features and many new features that are undergoing community testing.)
Ain't he handsome?
One of the reasons that Toad is so good is that it’s always been a community-driven product. Back when I used Oracle every day, TOAD was an acronym = Total Oracle Application Development. It didn’t take long for Toad to rise above the acronym transform into the eponymous term denoting “kick-butt database tool” just a few years before Toad began to go cross-platform. Now that Toad is solidly cross-platform with versions for DB2, MySQL, and Cloud to boot, it’s worth pointing out that Toad got to be what it is today entirely from community feedback. Back in the day, when I worked in Quest’s R&D team, the developers literally kept a checklist of cool suggestions from the community and worked against that to develop new features. My point isn’t to fully describe the inner workings of the Toad dev team, rather I wanted to highlight how incredibly important community feedback is to this tool and the developers behind it.
I always enjoy spending time with my friends from Atlanta, as well as meeting folks and making new friends. If you live in the Atlanta area, I hope you’ll join me on the evening of Wednesday, February 9th, 2011. Details are at the Atlanta SQL Server user group website.
It’s common knowledge that I have a terrible memory for many things. However, one of the few things that my memory is usually really good at is remember names & faces (and remembering stories, but that is another story as well).
It’s only in the last couple years that I’ve gotten to know Atlanta-area folks like Aaron Nelson (blog | twitter), at left. Aaron likes to tease me because the first time or two we talked after our initial introduction, I kept getting his name wrong. That’s uncharacteristic of me. But, hey, I was distracted because Aaron had brought his 12 year old daughter along and I kept wishing that I’d brought mine! Aaron has really good content on SQL Server and PowerShell on his blog and in his presentations. Don’t miss out!
The Flying Ainsworth Brothers
Another person in the Atlanta area I’ve enjoyed getting to know better is Stuart Ainsworth (blog | twitter). Stuart has really stepped up to the plate these last couple years as a leader and a volunteer. I’m really impressed with his efforts, especially because they’re in very important but rather invisible and thankless tasks. One example is his work in succession governance for PASS. Stuart, I salute you and thank you for your efforts.
I hope to see y’all there! And if I don’t, y’all come up and visit us in Nashville some time.
This presentation is a bit old, but largely still applicable. It will help you understand Microsoft tuning techniques. It will teach tips and tricks for making queries perform better, and how to use tools to tune SQL.
In my last column (published in the February e-edition and the March print edition of DBTA), I reviewed the overall coding landscape for SQL Server with special focus on LINQ to SQL, a new technology introduced by Microsoft in late 2008. LINQ to SQL promised to make developers’ lives much easier by allowing them to focus on writing programs in their favorite Visual Studio language and letting LINQ to SQL write all the Transact-SQL code. The problem is that LINQ to SQL writes very bad Transact-SQL code. [READ MORE]
I enjoy working at Quest Software partly because we do a lot to give back to the community. Here’s one example, a free poster describing all of the SQL Server 2005 and 2008 dynamic management views. Get it here. It’s the perfect compliment to the free system map poster available from Microsoft here.
O’Reilly Media on Facebook is a place for for anyone who is a fan of O’Reilly, whether you’ve been following their books, conferences, webcasts, and more since the very beginning or you recently jumped onto the bandwagon.
I really enjoy the newsletters and blogs from O’Reilly’s various channels, especially Tim O’Reilly’s commentary.
SQL in a Nutshell, now in it's 3rd Edition
And, of course, there’s my book – SQL in a Nutshell – published by O’Reilly for your consideration. This book provides a complete reference of the latest release of the ANSI SQL standard, as well as full coverage of support for the ANSI standard from the biggest database vendors – Oracle, Microsoft SQL Server, MySQL, and PostgreSQL.
