Search results matching tags 'Transact-SQL Programming' and 'Tips'

Learn More About SQL Server IO and Query Tuning in These Webcasts

KKline — Fri, 14 Dec 2012 18:50:00 GMT

I'm doing two new webcasts next week on Wednesday, December 19th, one in the morning and the other after lunch.

SSDs are a Game Changer for SQL Server Storage

No, session is not exclusively about SSDs. But this is my first session on IO and storage tuning that emphasizes SSDs over hard disks. As Bob Dylan said "Times, they are a'changin'". This session on Wednesday, December 19th at 11:30 AM EST, sponsored by Astute Networks, takes you through all of the basics of storage and IO tuning, regardless of the underlying storage technology. I'll show you how SQL Server handles storage structures, how to identify IO activity on Windows and SQL Server, and best practices for minimizing IO bottlenecks. Register now for: Storage IO Best Practices for SQL Server and a New Approach to Solving Application Performance Issues.

Write Better SQL Queries

The next webcast on Wednesday, December 19th at 2 PM EST, is with me, Aaron Bertrand (Twitter | Blog) and SQLCruise Impresario & Microsoft MVP Tim Ford (Twitter | Blog) as we take you through the query tuning process, discussing important DMVs to use during query tuning, as well as demonstrating several essential query tuning techniques that every SQL developer should know. Not only are we presenting an hour of top quality technical content, we’ll also be giving away some cool prizes, including the grand prize of a paid registration for the upcoming SQLCruise Miami, a $1,395 value! Register now for: SQL Server Query Tuning Best Practices, Hosted by Kevin Kline and Aaron Bertrand with special guest Tim Ford

I hope to see you at both of these sessions next week! Best regards,

-Kev

-Follow me on Twitter!

Flexibility When Waiting on Locks

KKline — Thu, 17 May 2012 13:29:00 GMT

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,

-Kev

-Follow me on Twitter

Dev Advice: Make a Tiny Dev Database Act Like a HUGE Prod Database

KKline — Fri, 16 Mar 2012 14:30:00 GMT

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.

Enjoy!

-Kev

-Follow me on Twitter