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.
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]
In a two-part article over the next two months, I’m going to address an important issue for the SQL Server community: the future direction of coding for SQL Server, as directed by Microsoft. I’ll start by telling you a bit about the current situation with writing code on and for SQL Server, and, in the next installment, talk more about the ramifications brought on by the current coding environment.
I’m curious if you agree with my assertions. You also have the added advantage of hindsight, since I wrote these a while ago.
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.
Fast Features for Quest Tools. Someday soon, I promise.
I’ve been trying hard to get more videos out the door about the SQL Server tools from Quest Software (my employer). I’d sent a new one over to the program marketing team a while back, but it’s not showing up in the Coffee Break Bytes section like it should. These little videos show a single feature in detail, usually taking no more than 5 minutes to watch in their entirety.
In the meanwhile, check out the excellent video series here. And the blogs are really good too, available on the interwebs here.
Thinking about cloud computing?
Well, there aren’t many tools out there that will make your life easier than Toad for Cloud Databases. As is most cloud technology, this particular tool is still in beta. But there’s no better time to help set the direction and features of a tool with as outstanding a record as Toad. Get the details and download the beta here.
And if you like providing feedback to the development team, don’t overlook the IdeaPond where you can provide your own ideas and vote on others that are already posted. (Currently targeted towards Oracle users, but I say let’s crash that party)!
You may have seen a new super sleuth around Quest.com and Toad World – The Data Detective!
Everyone wants good data, but not everyone wants to work hard to get it
The team at Quest Software has created a new comic series where users can learn how Toad for Data Analysts will help you find, understand and report on data. Plus, you can take part in a contest to win a free one-year license of TDA!
The first in the comic book series is posted here. And there’s one each month. If you contact me directly, I can help you get to the front of the line for the contest!
