Search results matching tags 'SQL Server 2012' and 'Tips'http://sqlblog.com/search/SearchResults.aspx?o=DateDescending&tag=SQL+Server+2012,Tips&orTags=0Search results matching tags 'SQL Server 2012' and 'Tips'en-USCommunityServer 2.1 SP2 (Build: 61129.1)Squishy Limits in SQL Server Express Editionhttp://sqlblog.com/blogs/kevin_kline/archive/2013/03/28/squishy-limits-in-sql-server-express-edition.aspxThu, 28 Mar 2013 12:19:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:48447KKline<p style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;font-size:13.333333969116211px;line-height:18.99305534362793px;">It's an old story you've probably heard before. &nbsp;Provide a free version of your software product with strict limitations on performance or other specific capabilities so that folks can give it a try without risk, while you minimize the chance of&nbsp;cannibalizing&nbsp;sales of your commercial products. &nbsp;Microsoft has take this strategy with&nbsp;<a href="http://www.microsoft.com/en-us/sqlserver/editions/2012-editions/express.aspx">SQL Server Express Edition</a>, not only to increase adoption in the student market but also to counter the threat of open-source (i.e. free) relational databases like MySQL for entry-level applications.</p><p style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;font-size:13.333333969116211px;line-height:18.99305534362793px;">One such limitation of SQL Server Express Edition is that it supports no more than 1GB of RAM for the instance. &nbsp;Of course, you could have many Express Edition instances on a single Windows server, each with its own 1GB of RAM.</p><p style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;font-size:13.333333969116211px;line-height:18.99305534362793px;">But what does that metric of 1GB of RAM actually mean? &nbsp;The key thing to remember is that the restriction is for&nbsp;<em><strong>buffer</strong><strong>&nbsp;cache.&nbsp;</strong></em><strong>&nbsp;</strong>Since SQL Server has many other caches, even when not counting the plan cache, there are plenty of other caches within SQL Server. &nbsp;(Run a query against&nbsp;<em>sys.dm_os_memory_clerks</em>&nbsp;if you'd like to see some of the others). &nbsp;Because only the buffer cache has the strict 1GB limitation, you can actually watch SQL Server Express Edition's memory working set size grow to around 1.4-1.5GB due to the other memory caches at play.</p><p style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;font-size:13.333333969116211px;line-height:18.99305534362793px;">Pawel Potasinski, a SQL Server MVP from Poland (<a href="http://twitter.com/pawelpotasinski">Twitter</a>&nbsp;|&nbsp;<a href="http://sqlgeek.pl/">Blog</a>), once&nbsp;<a href="http://sqlgeek.pl/2010/08/23/pl-sql-server-limity-w-sql-server-2008-r2-express-edition/">posted an interesting repro</a>&nbsp;for this behavior:</p><p style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;font-size:13.333333969116211px;line-height:18.99305534362793px;padding-left:30px;"><span style="font-family:Consolas, Monaco, monospace;font-size:12px;line-height:18px;">-- Assess amount of databases resident in buffer cache</span></p><pre style="font-size:12px;line-height:18px;font-family:Consolas, Monaco, monospace;padding-left:30px;">SELECT
CASE
WHEN database_id = 32767 THEN 'mssqlsystemresource'
ELSE DB_NAME(database_id)
END AS [Database],
CONVERT(numeric(38,2),(8.0 / 1024) * COUNT(*)) AS [MB in buffer cache]
FROM sys.dm_os_buffer_descriptors
GROUP BY database_id
ORDER BY 2 DESC;
GO</pre><pre style="font-size:12px;line-height:18px;font-family:Consolas, Monaco, monospace;padding-left:30px;">-- Assess amount of tables resident in buffer cache
SELECT
QUOTENAME(OBJECT_SCHEMA_NAME(p.object_id)) + '.' +
QUOTENAME(OBJECT_NAME(p.object_id)) AS [Object],
CONVERT(numeric(38,2),(8.0 / 1024) * COUNT(*)) AS [MB In buffer cache]
FROM sys.dm_os_buffer_descriptors AS d
INNER JOIN sys.allocation_units AS u ON d.allocation_unit_id = u.allocation_unit_id
INNER JOIN sys.partitions AS p ON (u.type IN (1,3) AND u.container_id = p.hobt_id) OR (u.type = 2 AND u.container_id = p.partition_id)
WHERE d.database_id = DB_ID()
GROUP BY QUOTENAME(OBJECT_SCHEMA_NAME(p.object_id)) + '.' + QUOTENAME(OBJECT_NAME(p.object_id))
ORDER BY [Object] DESC;
GO</pre><pre style="font-size:12px;line-height:18px;font-family:Consolas, Monaco, monospace;padding-left:30px;">-- Fill up Express Edition's buffer allocation
IF OBJECT_ID(N'dbo.test', N'U') IS NOT NULL
DROP TABLE dbo.test;
GO</pre><pre style="font-size:12px;line-height:18px;font-family:Consolas, Monaco, monospace;padding-left:30px;">CREATE TABLE dbo.test (col_a char(8000));
GO</pre><pre style="font-size:12px;line-height:18px;font-family:Consolas, Monaco, monospace;padding-left:30px;">INSERT INTO dbo.test (col_a)
SELECT REPLICATE('col_a', 8000)
FROM sys.all_objects
WHERE is_ms_shipped = 1;</pre><pre style="font-size:12px;line-height:18px;font-family:Consolas, Monaco, monospace;padding-left:30px;">CHECKPOINT;
GO 100</pre><p style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;font-size:13.333333969116211px;line-height:18.99305534362793px;padding-left:30px;"><em>&nbsp;The bottom line for the hard memory limit of SQL Server Express Edition is "Yes, it's limited. &nbsp;But it's a squishy limit. Not a hard limit."</em></p><p style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;font-size:13.333333969116211px;line-height:18.99305534362793px;"><span style="line-height:19px;">Although your mileage may vary, I'd bet a dollar that you'll find more than 1GB in the active working set for your instance of SQL Server Express Edition. &nbsp;I am curious, however, if you're seeing much variation between versions and even service packs of SQL Server? &nbsp;Let me know if you try this out on more than one version and/or service pack level of SQL Server. &nbsp;Did it change much between versions? &nbsp;Let me know!</span></p><p style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;font-size:13.333333969116211px;line-height:18.99305534362793px;">Enjoy,</p><p style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;font-size:13.333333969116211px;line-height:18.99305534362793px;">-Kevin</p><p style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;font-size:13.333333969116211px;line-height:18.99305534362793px;"><a href="http://twitter.com/kekline">-Follow me on Twitter!</a></p><p style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;font-size:13.333333969116211px;line-height:18.99305534362793px;"><a href="http://twitter.com/kekline"></a><br><a href="https://plus.google.com/u/1/113032055249023350257?rel=author">Google Author</a></p><p style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;font-size:13.333333969116211px;line-height:18.99305534362793px;">&nbsp;</p>Learn More About SQL Server IO and Query Tuning in These Webcastshttp://sqlblog.com/blogs/kevin_kline/archive/2012/12/14/learn-more-about-sql-server-io-and-query-tuning-in-these-webcasts.aspxFri, 14 Dec 2012 18:50:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:46662KKline
<p style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;line-height:19px;">I'm doing two new webcasts next week on Wednesday, December 19th, one in the morning and the other after lunch.</p>
<p style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;line-height:19px;">&nbsp;</p>
<h2 style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;line-height:19px;">SSDs are a Game Changer for SQL Server Storage</h2>
<p style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;line-height:19px;">No, session is not exclusively about SSDs. &nbsp;But this is my first session on IO and storage tuning that emphasizes SSDs over hard disks. &nbsp;As Bob Dylan said "Times, they are a'changin'". &nbsp;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. &nbsp;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. &nbsp;Register now for:<a title="Kevin Kline's Storage IO Best Practices for SQL Server" href="http://bit.ly/UcXYI3">&nbsp;Storage IO Best Practices for SQL Server and a New Approach to Solving Application Performance Issues</a>.</p>
<p style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;line-height:19px;">&nbsp;</p>
<h2 style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;line-height:19px;">Write Better SQL Queries</h2>
<p style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;line-height:19px;">The next webcast on Wednesday, December 19th at 2 PM EST, is with me, Aaron Bertrand &nbsp;(<a href="https://twitter.com/#!/AaronBertrand">Twitter&nbsp;</a>|&nbsp;<a href="http://sqlblog.com/blogs/aaron_bertrand/rss.aspx">Blog</a>)&nbsp;and SQLCruise Impresario &amp; Microsoft MVP Tim Ford &nbsp;(<a href="https://twitter.com/#!/sqlagentman">Twitter</a>&nbsp;|&nbsp;<a href="http://www.ford-it.com/sqlagentman/">Blog</a>)&nbsp;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. &nbsp;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&nbsp;<a target="_blank" href="http://elink.sqlsentry.net/c/1/?aId=67857085&amp;requestId=b34612-273953cd-e600-4a18-979a-a9f2ded860bd&amp;rId=lead-a407ed107f65de119513001e0b614992-c233a49718324979b0d8efc0614ff5d0&amp;ea=aunefuonetre=pbz=vagrepreir&amp;dUrl=http%3A%2F%2Fsqlcruise.com%2F2013-cruises%3F_cldee%3DbmhhcnNoYmFyZ2VyQGludGVyY2VydmUuY29t&amp;uId=0">SQLCruise Miami</a>, a $1,395 value! &nbsp;Register now for:&nbsp;<a title="SQL Server Query Tuning Best Practices, Hosted by Kevin Kline, Aaron Bertrand, and Tim Ford" href="http://bit.ly/UskPPm">SQL Server Query Tuning Best Practices, Hosted by Kevin Kline and Aaron Bertrand with special guest Tim Ford</a></p>
<p style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;line-height:19px;">&nbsp;</p>
<p style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;line-height:19px;">I hope to see you at both of these sessions next week! &nbsp;Best regards,</p>
<p style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;line-height:19px;">-Kev</p>
<p style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;line-height:19px;"><a title="Kevin E. Kline on Twitter" href="http://twitter.com/kekline">-Follow me on Twitter!</a></p>Quick Tip - Speed a Slow Restore from the Transaction Loghttp://sqlblog.com/blogs/kevin_kline/archive/2012/11/14/quick-tip-speed-a-slow-restore-from-the-transaction-log.aspxWed, 14 Nov 2012 15:59:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:46209KKline<p style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;line-height:19px;">Here's a quick tip for you:</p><p style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;line-height:19px;">During some restore operations on Microsoft SQL Server, the transaction log redo step might be taking an unusually long time. &nbsp;Depending somewhat on the version and edition of SQL Server you've installed, you may be able to increase performance by tinkering with the readahead performance for the redo operations. &nbsp;To do this, you should use the MAXTRANSFERSIZE parameter of the RESTORE statement. &nbsp;For example, if you set MAXTRANSFERSIZE=1048576, it'll use 1MB buffers.</p><div style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;line-height:19px;"><div align="left">If you change the MAXTRANSFERSIZE, keep an eye on the PerfMon objects for Buffer Manager and Readahead IO. &nbsp;You may also wish to keep an eye on LOGBUFFER wait stats.</div><div align="left"><br></div><div align="left">I'd love to hear your feedback. &nbsp;Have you tried this technique? &nbsp;Did it work as advertised? &nbsp;Did it require some changes to work on a specific version or edition?</div></div><div align="left" style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;line-height:19px;"><br></div><div align="left" style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;line-height:19px;">Many thanks,</div><div align="left" style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;line-height:19px;"><br></div><div align="left" style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;line-height:19px;">-Kev</div><div align="left" style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;line-height:19px;"><br></div><div align="left" style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;line-height:19px;">-<a title="Kevin E. Kline's Twitter Feed" href="http://twitter.com/kekline">Follow me on Twitter!</a></div>Two New Slide Decks. Plus, the Week in Colorado.http://sqlblog.com/blogs/kevin_kline/archive/2012/08/20/two-new-slide-decks-plus-the-week-in-colorado.aspxMon, 20 Aug 2012 15:03:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:44792KKline<p style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;line-height:19px;"><a href="http://kevinekline.com/wp-content/uploads/2012/08/IMAG2488.jpg"><img class="alignright wp-image-2027" title="Kevin and the SpringSQL Leadership" alt="" width="240" height="143" style="border:0px;cursor:default;float:right;" src="http://kevinekline.com/wp-content/uploads/2012/08/IMAG2488-300x179.jpg"></a>I had the honor of traveling the great state of Colorado last week, speaking at the PASS chapters in&nbsp;<a title="Boulder, CO SQL Server Users Group" href="https://groups.google.com/forum/?fromgroups#!forum/boulder-sql-server-users-group">Boulder</a>,&nbsp;<a title="Colorado Springs, CO SQL Server Users Group" href="http://www.springssql.sqlpass.org/">Colorado Springs</a>, and&nbsp;<a title="Denver, CO SQL Server Users Group" href="http://denver.sqlpass.org/">Denver</a>. &nbsp;At all three events, we had a stellar attendance and, at least&nbsp;<a title="A Huge Crowd for the Denver SQL Server User Group!" href="http://img.ly/m6ZG">in Denver, broke all the records</a>&nbsp;in recent memory both in terms of overall attendance and in first-timers. &nbsp;Denver, in fact, was standing room only and had nearly 30 first time attendees. &nbsp;Great news! &nbsp;I also want to give a special shout-out of thanks and appreciation to&nbsp;Chris Shaw (<a href="https://twitter.com/#!/SQLShaw">Twitter</a>&nbsp;|&nbsp;<a href="http://chrisshaw.wordpress.com/feed/">Blog</a>) whose hard work and tenacity ensured that all of Colorado got to see me speak. From left to right, Gabriel Villa (<a title="Gabriel Villa on Twitter" href="http://twitter.com/extofer">Twitter</a>), me, Chris Shaw, and Rebecca Mitchell (<a title="Rebecca Mitchell on Twitter" href="http://twitter.com/sqlprincess">Twitter</a>). &nbsp;If it weren't for Chris, I wouldn't have been there. &nbsp;Thanks for putting in the time, amigo!</p><h2 style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;line-height:19px;">New Slide Decks!</h2><p style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;line-height:19px;">During the 3-day jaunt, I presented two of my more popular sessions. &nbsp;These are updated slide decks, in case you want to download them here:</p><p style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;line-height:19px;"><a title="End-to-End Troubleshooting for Microsoft SQL Server" href="http://kevinekline.com/wp-content/uploads/2012/08/UG-End-to-End-Troubleshooting.zip">UG - End-to-End Troubleshooting</a></p><p style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;line-height:19px;">and</p><p style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;line-height:19px;"><a title="Top 10 DBA Blunders on Microsoft SQL Server" href="http://kevinekline.com/wp-content/uploads/2012/08/UG-Top-10-SQL-Server-Administration-Mistakes.zip">UG - Top 10 SQL Server Administration Mistakes</a></p><p style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;line-height:19px;"><a href="http://kevinekline.com/wp-content/uploads/2012/08/IMAG2492.jpg"><img class="alignright wp-image-2033" title="Kevin &amp; Steve Murchie" alt="" width="125" height="210" style="border:0px;cursor:default;float:right;" src="http://kevinekline.com/wp-content/uploads/2012/08/IMAG2492-179x300.jpg"></a>Be sure to check in the Slides area of the website, if you want to see the links for SpeakerRate, and in the case of several of my presentations, white papers, video recordings, etc. It's the People that Matter</p><h2 style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;line-height:19px;">A Blast from the SQLPASS Past!</h2><p style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;line-height:19px;">I've always tried to maintain the relationships I built with the founding members of the&nbsp;<a title="The Professional Association for SQL Server" href="http://www.sqlpass.org/">PASS</a>&nbsp;board of directors. &nbsp;After their time on the PASS board, almost all of them have moved on from SQL Server to other adventures. &nbsp;Pam Smith, the first president of the organization, is now a professor. &nbsp;Guy Brown, the second president, is now the director of IT at his same employer, rather than just SQL Server as when he was on the PASS board. &nbsp;A few, such as Kurt Windisch, a former VP of PASS, and my good friend&nbsp;Joe Webb (<a href="https://twitter.com/#!/joewebb">Twitter</a>&nbsp;|&nbsp;<a target="_blank" href="http://www.webbtechsolutions.com/blog">Blog</a>), are still active in the SQL Server space. &nbsp;One relationship that I've enjoyed over the years is with PASS' original Microsoft liaison and now a Denver-area software entrepreneur Steve Murchie (at right) running his own healthcare IT outfit. &nbsp;Steve has been a source of inspiration to me and also of great advice for all things startup-related. &nbsp;It was great to connect with Steve and catch up on his latest doings.</p><p style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;line-height:19px;">I also got to enjoy an evening out with the local attendees after the Denver user group meeting. &nbsp;It was great to hang out with folks there. &nbsp;I got to meet&nbsp;<a title="Kevin Cox on deck for 24HOP of SQLPASS.ORG" href="http://www.sqlpass.org/24hours/fall2012/SessionsbySchedule/SpeakerDetails.aspx?spid=480">Kevin Cox</a>&nbsp;(<a title="Kevin Cox's Twitter Feed" href="http://twitter.com/KevinCoxSQL">twitter</a>), a member of Microsoft's incredibly talented&nbsp;<a title="The Microsoft SQL Server Customer Advisory Team" href="http://www.sqlcat.com/">SQLCAT</a>&nbsp;group, and for whom I was a technical editor on a SQL Server v6.5 book back in the Neanderthal era. &nbsp;That shows just how old both Kevin and I actually are. &nbsp;Other cool folks that I got to meet included&nbsp;<a href="http://twitter.com/stevewake">Steve Wake</a>,&nbsp;<a href="http://twitter.com/mike_fal">Mike Fal</a>,&nbsp;<a href="http://twitter.com/marcbeacom">Marc Beacom</a>,&nbsp;<a href="http://twitter.com/jasonkassay">Jason Kassay</a>,&nbsp;<a href="http://twitter.com/jasonhorner">Jason Horner</a>&nbsp;and my ol' buddy,&nbsp;<a href="http://twitter.com/greeleygeek">Kelly the Greeley Geek</a>.</p><p style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;line-height:19px;">On top of that, long-time SQL Server MVP and all-around awesome guy&nbsp;Steve Jones (<a href="https://twitter.com/#!/way0utwest">Twitter</a>&nbsp;|&nbsp;<a href="http://feeds.feedburner.com/sqlmusings">Blog</a>) visited. &nbsp;I kept him out way too late that night which, to be honest, isn't usually my style. &nbsp;But the good conversation propelled us on past midnight.</p><h2 style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;line-height:19px;">The Good Folks at SQL Server Professional and Windows IT Professional Magazines</h2><p style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;line-height:19px;"><a href="http://kevinekline.com/wp-content/uploads/2012/08/IMAG2486.jpg"><img class="alignright wp-image-2036" title="Kevin and the Ladies of SQLMag" alt="" width="240" height="143" style="border:0px;cursor:default;float:right;" src="http://kevinekline.com/wp-content/uploads/2012/08/IMAG2486-300x179.jpg"></a>I've written for SQL Server Professional (formerly the artist known as "SQLMag") in some form or another starting from my first cover article for them way back in the mid 1990's. &nbsp;My&nbsp;<a title="Kevin Kline's Tool Time column at SQL Server Professional Magazine" href="http://www.sqlmag.com/blogcontent/seriespath/tool-time-blog-16">Tool Time column</a>&nbsp;has been going strong there since, oh, around 2006 iirc. &nbsp;For most of the time I've known the folks at SQLMag, they were located in Loveland, CO but they were able to move to some incredibly nice digs just up the road in Fort Collins. &nbsp;In all the many years, I've written for them, I'd never been to their offices - until now. &nbsp;It was great to visit and break bread with Megan (to my right), Blair (across), and Jaylee (across and to my right)!</p><p style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;line-height:19px;">I've always supported SQLMag and encourage you to subscribe. &nbsp;On top of the goodness already in the digital magazine, there are some neat developments coming down the pipeline with SQLMag which I think we'll all enjoy. &nbsp;Be sure to subscribe today! &nbsp;(You can click the badge on the left or simply go to&nbsp;<a title="SQL Server Professional Magazine" href="http://www.sqlmag.com/">http://www.sqlmag.com</a>).</p><h2 style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;line-height:19px;">&nbsp;What's Next?</h2><p style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;line-height:19px;">My current travel schedule is pinned up on&nbsp;<a title="Kevin Kline's appearance schedule" href="http://kevinekline.com/2012/07/31/come-see-me-ill-probably-be-just-down-the-street-soon/">this blog post HERE</a>. &nbsp;However, I also know of a couple on-line appearances and probably a trip between the long gap between now and my next in-person appearance at the&nbsp;<a title="Orlando SQL Saturday 151" href="http://www.sqlsaturday.com/151/eventhome.aspx">Orlando SQL Saturday</a>&nbsp;at the end of&nbsp;September, where I'll also be teaching a pre-conference seminar (<a title="SQL Server Configuration and Tuning Seminar" href="http://www.eventbrite.com/event/3895236758?ref=ebtn">register HERE for the seminar</a>).</p><h3 style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;line-height:19px;">SSWUG</h3><p style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;line-height:19px;">The first on-line event to note is my a presentation by&nbsp;<a title="SQL Server Worldwide User Group" href="http://www.sswug.org/">SSWUG</a>&nbsp;of my&nbsp;<a title="Kevin Kline and SSWUG bring you &quot;Leadership Skills for IT Professionals&quot;" href="http://www.vconferenceonline.com/event/home.aspx?id=769">Leadership Skills for IT Professionals video series</a>, starting on August 24th. &nbsp;Sign up using the hyperlink (note that a video plays immediately upon loading the webpage, in case you want to be ready to pause or stop it). &nbsp;You can also buy a DVD set of the 14 hours of leadership training content.</p><h3 style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;line-height:19px;">24HOP - The 24 Hours of PASS Event</h3><p style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;line-height:19px;">I'll also be speaking on the topic of influence in the next&nbsp;<a title="The 2012 24 Hours of PASS session schedule" href="http://www.sqlpass.org/24hours/fall2012/SessionsbySchedule.aspx">24 Hours of PASS coming up on September 20th and 21st</a>. &nbsp;Registration for the twenty-four hours of around the clock presentations is completely free and well worth your time. &nbsp;Check the schedule for the event and register! &nbsp;Even if you can only watch one or two sessions (or even zero sessions), be sure to register so that you'll automatically be notified when the sessions become available as streaming media.</p><p style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;line-height:19px;">&nbsp;</p><p style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;line-height:19px;">Enjoy!</p><p style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;line-height:19px;">-Kev</p><p style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;line-height:19px;">-Follow me on&nbsp;<a title="Kevin Kline on Twitter" href="http://twitter.com/kekline">Twitter</a>,&nbsp;<a title="Kevin Kline on LinkedIn" href="http://linkedin.com/kekline">LinkedIn</a>, and&nbsp;<a title="Kevin Kline on Facebook" href="http://facebook.com/kekline">Facebook</a></p><p style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;line-height:19px;">&nbsp;</p>Flexibility When Waiting on Lockshttp://sqlblog.com/blogs/kevin_kline/archive/2012/05/17/flexibility-when-waiting-on-locks.aspxThu, 17 May 2012 13:29:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:43427KKline<p style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;line-height:19px;">Speaking at a recent&nbsp;<a title="SQL Saturday Events around the world" href="http://www.sqlsaturday.com/">SQL Saturday</a>, 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. &nbsp;They were also interested in some alternatives to using the &nbsp;SQL Server syntax like<a title="Transact-SQL Syntax for the WITH (NOLOCK) table hint" href="http://msdn.microsoft.com/en-us/library/ms187373.aspx">&nbsp;the WITH (NOLOCK) hint</a>, since that might have unintended consequences due to allowing reads on uncommitted data.</p><p style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;line-height:19px;">One alternative I suggested is the SET LOCK_TIMEOUT&nbsp;<em>n&nbsp;</em>statement. &nbsp;Since most of the attendees hadn't heard of this statement, I figured it'd make a good blog post. &nbsp;</p><p style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;line-height:19px;">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). &nbsp;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. &nbsp;0 means don't wait at all and -1, the default, means wait forever. &nbsp;Once changed,&nbsp;the new setting stays in effect for the remainder of the connection. &nbsp;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.</p><p style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;line-height:19px;">You can also get the same behavior by using the&nbsp;READPAST locking hint.</p><p style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;line-height:19px;">Hope this helps with those troublesome locking situations! &nbsp;Enjoy,</p><p style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;line-height:19px;">-Kev</p><p style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;line-height:19px;">-Follow me on&nbsp;<a title="Kevin Kline's Twitter Feed" href="http://twitter.com/kekline">Twitter</a></p>