Search results matching tags 'SQL Server 2012' and 'Performance'http://sqlblog.com/search/SearchResults.aspx?o=DateDescending&tag=SQL+Server+2012,Performance&orTags=0Search results matching tags 'SQL Server 2012' and 'Performance'en-USCommunityServer 2.1 SP2 (Build: 61129.1)One Preparation that makes SSMS Crash Dumps Easy to Survivehttp://sqlblog.com/blogs/kevin_kline/archive/2014/02/10/one-preparation-that-makes-ssms-crash-dumps-easy-to-survive.aspxMon, 10 Feb 2014 15:50:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:52766KKline<p style="margin-bottom:1.3em;font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;font-size:14.44444465637207px;line-height:21px;">&nbsp;</p><p style="margin-bottom:1.3em;font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;font-size:14.44444465637207px;line-height:21px;"><a href="http://aalamrangi.wordpress.com/2011/10/25/recover-queries-after-a-sql-server-management-studio-ssms-crash/"><img class="aligncenter size-medium wp-image-5837" alt="ssms_query_recover_dialog" width="300" height="294" style="border:0px;cursor:default;display:block;margin-left:auto;margin-right:auto;" src="http://kevinekline.com/wp-content/uploads/2014/02/ssms_query_recover_dialog-300x294.png"></a></p><p style="margin-bottom:1.3em;font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;font-size:14.44444465637207px;line-height:21px;text-align:center;"><em>Uh oh!</em></p><p style="margin-bottom:1.3em;font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;font-size:14.44444465637207px;line-height:21px;">So you're plugging along in SQL Server Management Studio (SSMS) when it suddenly goes belly up. Now you're staring at various dialog boxes telling you that SSMS crashed. Usually the first dialog box you get will ask you if you'd like to close OR the program. If you choose to close the program, you'll be presented with the opportunity to recover your lost SQL scripts once you reopen SSMS, as shown above.&nbsp;&nbsp;(Image above courtesy of&nbsp;<a href="http://aalamrangi.wordpress.com/2011/10/25/recover-queries-after-a-sql-server-management-studio-ssms-crash/">Aalam Rangi</a>).</p><p style="margin-bottom:1.3em;font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;font-size:14.44444465637207px;line-height:21px;">But let's say that closing the program represents a&nbsp;<em>big</em>&nbsp;issue for you due to lost time, productivity, etc. You want to go the&nbsp;<em>other&nbsp;</em>route - you want to DEBUG! So, what's the easiest way to get a crash dump or to debug SSMS from this state?</p><p style="margin-bottom:1.3em;font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;font-size:14.44444465637207px;line-height:21px;"><span style="line-height:1.5em;">In my personal experience, the natural choice and the&nbsp;<em>only&nbsp;</em>choice I'm ever presented&nbsp;is to debug in Visual Studio. But I'm not really a Visual Studio guy. And I find that those times I've attempted to follow the debug route have left me with very little useful information.</span></p><p style="margin-bottom:1.3em;font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;font-size:14.44444465637207px;line-height:21px;">But it turns out that, with a little earlier preparation, you can get a postmortem crash dump using a very nicely detailed set of steps detailed at at&nbsp;<a title="http://www.codeproject.com/KB/debug/automemorydump.aspx&lt;br /&gt;&lt;br /&gt; CTRL + Click to follow link" style="line-height:1.5em;" href="http://www.codeproject.com/KB/debug/automemorydump.aspx">http://www.codeproject.com/KB/debug/automemorydump.aspx</a><span style="line-height:1.5em;">. &nbsp;In a nutshell, you'll use the Microsoft debugger&nbsp;<a title="Microsoft WinDbg" href="http://www.microsoft.com/whdc/ddk/debugging/installx86.mspx">WinDBG</a>&nbsp;and, along with a few setting changes, configure your workstation to automatically take a memory dump when SSMS crashes.</span></p><p style="margin-bottom:1.3em;font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;font-size:14.44444465637207px;line-height:21px;">Once you've got your workstation set up to automatically grab a memory dump upon a crash, you have to interpret the results. I'm not going to duplicate excellent guidance provided by Microsoft at the&nbsp;<a href="http://blogs.msdn.com/b/psssql/archive/2012/03/15/intro-to-debugging-a-memory-dump.aspx">CSS SQL Server Engineering Blog</a>. &nbsp;So be sure to give that post a read to flesh out your understanding of taking and reading memory dumps.</p><p style="margin-bottom:1.3em;font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;font-size:14.44444465637207px;line-height:21px;">Equipped with this information and a few steps of preparatory work, and you're now ready to conquer SQL Server memory dumps!</p><p style="margin-bottom:1.3em;font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;font-size:14.44444465637207px;line-height:21px;">Have you every had a crash in SSMS? How did you troubleshoot and resolve the problem? Let me know what you think.</p><p style="margin-bottom:1.3em;font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;font-size:14.44444465637207px;line-height:21px;">Thanks,</p><p style="margin-bottom:1.3em;font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;font-size:14.44444465637207px;line-height:21px;">-Kevin</p><p style="margin-bottom:1.3em;font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;font-size:14.44444465637207px;line-height:21px;"><a href="http://twitter.com/kekline">-Follow me on Twitter!</a><br><a href="https://plus.google.com/u/1/113032055249023350257?rel=author">-Google Author</a></p><p style="margin-bottom:1.3em;font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;font-size:14.44444465637207px;line-height:21px;">&nbsp;</p><p style="margin-bottom:1.3em;font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;font-size:14.44444465637207px;line-height:21px;">&nbsp;</p><p style="margin-bottom:1.3em;font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;font-size:14.44444465637207px;line-height:21px;"><b>&nbsp;</b></p>SQLRally and SQLRally - Session materialhttp://sqlblog.com/blogs/hugo_kornelis/archive/2013/11/09/sqlrally-and-sqlrally-session-material.aspxSat, 09 Nov 2013 12:41:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:51662Hugo Kornelis<p>I had a great week last week. First at <a href="http://www.sqlpass.org/sqlrally/2013/nordic/Home.aspx" mce_href="http://www.sqlpass.org/sqlrally/2013/nordic/Home.aspx">SQLRally Nordic</a>, in Stockholm, where I presented a session on how improvements to the OVER clause can help you simplify queries in SQL Server 2012 enormously. And then I continued straight on into <a href="http://www.sqlpass.org/sqlrally/2013/amsterdam/Home.aspx" mce_href="http://www.sqlpass.org/sqlrally/2013/amsterdam/Home.aspx">SQLRally Amsterdam</a>, where I delivered a session on the performance implications of using user-defined functions in T-SQL.</p> <p>I understand that both events will make my slides and demo code downloadable from their website, but this may take a while. So those who do not want to wait can download the material from this blog post.</p> <p>Both SQLRally events have recorded all their sessions. It will obviously take a while to edit and publish all those recordings – but those who missed my session and want to check it out with my explanations know that if they wait a while, they can watch the recording online on the SQLRally websites.</p> <p>I once more would like to thank all volunteers who organized these events, all the sponsors who helped fund them … and most of all, all attendees who made my time in Stockholm and Amsterdam amazing. You were a great crowd, both during my session and in the many chats I had during the breaks.</p>Wait Statistics in Microsoft SQL Server http://sqlblog.com/blogs/kevin_kline/archive/2013/10/09/wait-statistics-in-microsoft-sql-server.aspxWed, 09 Oct 2013 14:59:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:51271KKline<p style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;font-size:13.333333969116211px;line-height:18.99305534362793px;">&nbsp;(Originally appearing at&nbsp;<a style="font-family:Arial, Helvetica, sans-serif;font-size:10pt;" href="http://kevinekline.com/2013/10/09/wait-statistics-in-sql-server/">http://kevinekline.com/2013/10/09/wait-statistics-in-sql-server/</a>).</p><p style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;font-size:13.333333969116211px;line-height:18.99305534362793px;"><img class="aligncenter size-medium wp-image-417" alt="CB033389" width="300" height="240" style="border:0px;cursor:default;display:block;margin-left:auto;margin-right:auto;" src="http://kevinekline.com/wp-content/uploads/2010/02/retirement-300x240.jpg"></p><p style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;font-size:13.333333969116211px;line-height:18.99305534362793px;">When it comes to troubleshooting in relational databases, there's no better place to start than wait statistics. &nbsp;In a nutshell, a wait statistic is an internal counter that tells you how long the database spent waiting for a particular resource, activity, or process. &nbsp;Since wait statistics are categorized by type, one look will quickly tell the variety of problem that needs your attention, assuming you know meaning for Microsoft's lingo for each wait type.</p><p style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;font-size:13.333333969116211px;line-height:18.99305534362793px;">(As an aside, wait stats were implemented in the earliest days of relational database computing because the early RDBMSes ran on multiple operating systems. So the database vendors needed a reliable method of troubleshooting database performance which was independent of the OS).</p><p style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;font-size:13.333333969116211px;line-height:18.99305534362793px;">Wait stats help you clue in to the best approach and path for troubleshooting. &nbsp;For example, if your top wait stat showed a lot of time spent on acquiring locks, you could pretty well rest assured that trying to tune networking latency would be a total waste of your time. &nbsp;Conversely, wait stats could also help you understand that perhaps the 'usual suspects' of poor database performance (IO, CPU, etc) weren't actually worth consideration.</p><p style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;font-size:13.333333969116211px;line-height:18.99305534362793px;">Because wait statistics are broad, they're typically where you&nbsp;<em>start</em>&nbsp;your troubleshooting process but not where it ends. &nbsp;That's because wait statistics don't actually point to the smoking gun that's causing the true performance problem. &nbsp;For example, let's say your SQL Server is experiencing an unusually high amount of RESOURCE_SEMAPHORE waits. &nbsp;Resource semaphores relate to query compilation and reserving memory for queries as they're being compiled. &nbsp;But on a busy system,WHICH of your queries are contributing to this wait? &nbsp;It can require a lot more investigation to figure that part out. &nbsp;(I've considered spending some more time in future posts walking through the entire troubleshooting process. If you're interested let me know in the comments).</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;">Now, in SQL Server, it is possible to determine the wait stats accrued by a given thread or even a specific query or transaction. &nbsp;But this information is only retained by SQL Server while "in flight". &nbsp;Long-term retention of wait stat information is only for the broad categories.</span></p><h3 style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;line-height:18.99305534362793px;"><em>WAIT STATS THEN...</em></h3><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;">For some historical perspective, you have to go back to the white paper&nbsp;</span><em style="line-height:19px;">SQL Server 2005 Waits and Queues</em><span style="line-height:19px;">&nbsp;(By Tom Davidson) to see where it all began for SQL Server. &nbsp;(</span><a style="line-height:19px;" href="http://download.microsoft.com/download/4/7/a/47a548b9-249e-484c-abd7-29f31282b04d/Performance_Tuning_Waits_Queues.doc">http://download.microsoft.com/download/4/7/a/47a548b9-249e-484c-abd7-29f31282b04d/Performance_Tuning_Waits_Queues.doc</a><span style="line-height:19px;">)</span></p><p style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;font-size:13.333333969116211px;line-height:18.99305534362793px;">Prior to SQL Server 2005, wait stats of a sort where identifiable using the DBCC SQLPERF(UMSSTATS) and DBCC SQLPERF(WAITSTATS). &nbsp;These commands are still around, btw. &nbsp;You can see these early indications of UMSStats (User Mode Scheduler) and wait stats in Microsoft KB articles like&nbsp;<em>Description of WAITTYPE and LASTWAITTYPE (</em><a style="line-height:19px;" href="http://support.microsoft.com/kb/822101">http://support.microsoft.com/kb/822101</a>) and other early blog posts.</p><p style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;font-size:13.333333969116211px;line-height:18.99305534362793px;">Wait Stats started to come into major prominence when folks like&nbsp;Joe Sack (<a href="http://www.sqlskills.com/blogs/joe/">blog</a>&nbsp;|&nbsp;<a href="https://twitter.com/josephsack">twitter</a>)<br>(<a href="http://blogs.msdn.com/jimmymay/archive/2009/04/27/wait-stats-by-joe-sack.aspx">http://blogs.msdn.com/jimmymay/archive/2009/04/27/wait-stats-by-joe-sack.aspx</a>)&nbsp;and&nbsp;Jimmy May (<a href="http://blogs.msdn.com/b/jimmymay/">blog</a>&nbsp;|&nbsp;<a href="http://twitter.com/aspiringgeek">twitter</a>)&nbsp;&nbsp;started to write about them&nbsp;(<a href="http://blogs.msdn.com/b/jimmymay/archive/2009/04/26/wait-stats-introductory-references.aspx">http://blogs.msdn.com/b/jimmymay/archive/2009/04/26/wait-stats-introductory-references.aspx</a>)</p><p style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;font-size:13.333333969116211px;line-height:18.99305534362793px;">And if you didn't get them then, you definitely need the SQL Server Diagnostic Queries by&nbsp;Glenn Berry (<a href="http://glennberrysqlperformance.spaces.live.com/feed.rss">blog</a>&nbsp;|&nbsp;<a href="https://twitter.com/#!/GlennAlanBerry">twitter</a>)&nbsp;, which have a number of wait stat queries already written for you. &nbsp;Glenn started this collection of queries back in 2005 and has kept it up to date ever since. &nbsp;The latest version of queries are at&nbsp;<a href="http://sqlserverperformance.wordpress.com/2012/07/08/sql-server-2012-diagnostic-information-queries-july-2012/">http://sqlserverperformance.wordpress.com/2012/07/08/sql-server-2012-diagnostic-information-queries-july-2012/</a>.</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;">I also started to put a lot of attention on them, such as when MCM and UK MVP Christian Bolton (<a href="http://sqlblogcasts.com/blogs/christian/default.aspx">Blog</a>&nbsp;|&nbsp;<a href="https://twitter.com/#!/christianbolton">Twitter</a>)&nbsp;and I did the webcast The 5-Minute SQL Server Health Check (</span><a href="http://sqlblogcasts.com/blogs/christian/archive/2009/11/16/webcast-now-on-demand-the-5-minute-sql-server-healthcheck.aspx">http://sqlblogcasts.com/blogs/christian/archive/2009/11/16/webcast-now-on-demand-the-5-minute-sql-server-healthcheck.aspx</a>).</p><h3 style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;line-height:18.99305534362793px;"><em>...AND WAIT STATS NOW</em></h3><p style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;font-size:13.333333969116211px;line-height:18.99305534362793px;">Nowadays, wait stats are quite well documented. &nbsp;You can get a great review of all of the wait stats for SQL Server simply by looking at the Books Online (BOL) topic&nbsp;(<a href="http://msdn.microsoft.com/en-us/library/ms179984.aspx">http://msdn.microsoft.com/en-us/library/ms179984.aspx</a>).</p><p style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;font-size:13.333333969116211px;line-height:18.99305534362793px;">The downside of Microsoft's documentation in BOL is that it tells you a nice bit of info about each of the wait stats, but not how to remediate them if they are turning into a problem on your SQL Server. &nbsp;But times have changed - there's so much good information that all you need (most of the time) is to perform an internet search for 'SQL Server my_problem_wait_stat' and you'll probably get at least one good hit by Microsoft customer support or an MVP blogger. &nbsp;All you need to do before the search is to find the type of wait stat that's causing the problem.</p><p style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;font-size:13.333333969116211px;line-height:18.99305534362793px;">&nbsp;</p><h2 style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;line-height:18.99305534362793px;text-align:center;"><em>These days, all you need to</em></h2><h2 style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;line-height:18.99305534362793px;text-align:center;"><em>solve a wait stat&nbsp;</em><em>problem is an</em></h2><h2 style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;line-height:18.99305534362793px;text-align:center;"><em>internet search for 'SQL Server</em></h2><h2 style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;line-height:18.99305534362793px;text-align:center;"><em>&nbsp;my_problem_wait_stat'</em></h2><p style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;font-size:13.333333969116211px;line-height:18.99305534362793px;">&nbsp;</p><p style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;font-size:13.333333969116211px;line-height:18.99305534362793px;">There are also a couple good books and eBooks on the topic. &nbsp;Kalen Delaney's&nbsp;<em>Inside SQL Server&nbsp;</em>books, especially Chapter 2 in the edition sitting on my shelf, are outstanding. &nbsp;Joes2Pros also has a nice, succinct book on wait stats here (<a href="http://joes2pros.com/?wpsc-product=sql-wait-stats-joes-2-pros">http://joes2pros.com/?wpsc-product=sql-wait-stats-joes-2-pros</a>).</p><p style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;font-size:13.333333969116211px;line-height:18.99305534362793px;">Jonathan Kehayias (<a href="http://sqlblog.com/blogs/jonathan_kehayias/rss.aspx">blog</a>&nbsp;|&nbsp;<a href="https://twitter.com/#!/SQLPoolBoy">twitter</a>), of&nbsp;<a href="http://sqlskills.com/">SQLSkills.com</a>, has a great eBook in short form for free and a longer, more comprehensive version for a small fee. (<a href="https://www.simple-talk.com/books/sql-books/troubleshooting-sql-server-a-guide-for-the-accidental-dba/">https://www.simple-talk.com/books/sql-books/troubleshooting-sql-server-a-guide-for-the-accidental-dba/</a>) at Simple-Talk.</p><p style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;font-size:13.333333969116211px;line-height:18.99305534362793px;"><span style="font-size:13.333333969116211px;line-height:18.99305534362793px;">What are your favorite wait stat resources? Have you written a blog post that broadens or deepens our knowledge of wait stats in SQL Server? If so, I'd love for you to post a comment here with a link back to your article! &nbsp;Let me know what you think. &nbsp;Thanks,</span></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;">-Kevin</span></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><br><a href="https://plus.google.com/u/1/113032055249023350257?rel=author">-Google Author</a></p>Decks and demos – Session material for Silicon Valley Code Camphttp://sqlblog.com/blogs/hugo_kornelis/archive/2013/10/05/decks-and-demos-session-material-for-silicon-valley-code-camp.aspxSat, 05 Oct 2013 04:38:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:51219Hugo Kornelis<p>This weekend, I will be presenting two sessions at <a href="http://www.siliconvalley-codecamp.com/" mce_href="http://www.siliconvalley-codecamp.com/">Silicon Valley Code Camp</a>, in Los Altos Hills, CA. On Saturday, I will have an early start – the first time slot of the day, at 9:45 AM, I will present on how <a href="http://www.siliconvalley-codecamp.com/Session/2013/t-sql-user-defined-functions-or-bad-performance-made-easy" mce_href="http://www.siliconvalley-codecamp.com/Session/2013/t-sql-user-defined-functions-or-bad-performance-made-easy">T-SQL user-defined functions</a> can easily wreck your performance – and how you can prevent that.</p> <p>On Sunday afternoon (1:15 PM), I will then present <a href="http://www.siliconvalley-codecamp.com/Session/2013/powerful-t-sql-improvements-that-reduce-query-complexity" mce_href="http://www.siliconvalley-codecamp.com/Session/2013/powerful-t-sql-improvements-that-reduce-query-complexity">a session on the OVER clause</a>, focusing on how both the SQLL Server 2005 version and the enhanced SQL Server 2012 syntax of this feature can help you solve common problems without having to resort to ill-performing and unmaintainable monster queries.</p> <p>Both sessions are quite demo-heavy, so I hope a lot of attendees will download the demo code and play with it for themselves. I have therefor attached the demo code for these sessions to this post. Oh, and the slide deck is included as well.</p>Hear the SQL Server 2012 story on DotNetRockshttp://sqlblog.com/blogs/kevin_kline/archive/2013/05/31/hear-the-sql-server-2012-story-on-dotnetrocks.aspxFri, 31 May 2013 15:32:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:49300KKline<p style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;font-size:13.333333969116211px;line-height:18.99305534362793px;text-align:center;"><a href="http://www.dotnetrocks.com/default.aspx?showNum=876"><img class="aligncenter wp-image-5682" alt="DotNetRocks" width="473" height="309" style="border:2px solid black;cursor:default;display:block;margin-left:auto;margin-right:auto;" src="http://kevinekline.com/wp-content/uploads/2013/05/DotNetRocks.jpg"></a></p><p style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;font-size:13.333333969116211px;line-height:18.99305534362793px;">I was privileged to have a chat with my buddies over at www.dotnetrocks.com, Carl Franklin and Richard Campbell, episode number 876 (876!). Listen to&nbsp;<a title="Kevin Kline Updates US on SQL Server DotNetRocks.com" href="http://www.dotnetrocks.com/default.aspx?showNum=876">the most popular internet audio talk show for .NET developers!</a>&nbsp; Here's the abstract:</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><em>Carl and Richard talk to Kevin Kline about the latest features in SQL Server 2012. The conversation starts out talking about the new features that developers will love, like windowing - no need for cursors anymore, you can request a window of records from a set and move easily window-to-window. Kevin also talks about the new column store index that is especially useful with repeating data. There's also a discussion on the role of SQL Server in an increasingly NoSQL world, along with cool new technologies like Hadoop, Cassandra and Hekaton. Kevin closes with an offer of some free tools at SQL Sentry, including Plan Explorer, a tool to help you understand the query plans that SQL Server makes from your queries. Check it out!</em></span></p><p style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;font-size:13.333333969116211px;line-height:18.99305534362793px;">Let me know what you think. &nbsp;Thanks,</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="https://plus.google.com/u/1/113032055249023350257?rel=author">- Google Author</a></p>Read the New TPC Database Benchmarking Serieshttp://sqlblog.com/blogs/kevin_kline/archive/2013/04/22/read-the-new-tpc-database-benchmarking-series.aspxMon, 22 Apr 2013 18:17:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:48816KKline<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;">Let's talk about database application benchmarking.</span></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;">This is a skill set which, in my opinion, is one of the major differentiators between a journeyman-level DBA and a true master of the trade. In this article published in my monthly column at&nbsp;<a target="_blank" href="http://www.dbta.com/"><em>Database Trends &amp; Applications magazine</em></a>, I'll give you a brief introduction to TPC benchmarks and, in future articles, I'll be telling you how to extract specific pieces of valuable information from the published benchmark results.</span></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;">But let's get started with an overview …&nbsp;</span><a target="_blank" style="line-height:19px;" href="http://www.dbta.com/Articles/Columns/SQL-Server-Drill-Down/Introduction-to-TPC-Database-Benchmarks-86891.aspx">read more.</a></p><p style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;font-size:13.333333969116211px;line-height:18.99305534362793px;"><span style="font-family:Arial, Helvetica, sans-serif;font-size:small;"></span></p><p style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;font-size:13.333333969116211px;line-height:18.99305534362793px;">Many thanks,</p><p style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;font-size:13.333333969116211px;line-height:18.99305534362793px;"><span style="font-family:Arial, Helvetica, sans-serif;font-size:small;"></span></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;"><span style="font-family:Arial, Helvetica, sans-serif;font-size:small;"></span></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><br><a href="https://plus.google.com/u/1/113032055249023350257?rel=author">- Google Author</a></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>The Curious Case of the Optimizer that doesn’thttp://sqlblog.com/blogs/hugo_kornelis/archive/2012/05/03/the-curious-case-of-the-optimizer-that-doesn-t.aspxThu, 03 May 2012 22:13:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:43164Hugo Kornelis<p class="MsoNormal"><span style="mso-ansi-language:EN-US;">The
optimizer is the part of SQL Server that takes your query and reorders and
rearranges your query to find the optimal execution plan. In theory.</span></p>
<p class="MsoNormal"><span style="mso-ansi-language:EN-US;">&nbsp;</span></p>
<p class="MsoNormal"><span style="mso-ansi-language:EN-US;">In
practice, that doesn’t always work out well. Often, the optimizer manages to
come up with brilliant ways to execute a complex query very efficiently – but sometimes,
it misses an option that appears to be so simple that you can only stare in
utter amazement at the execution plan before going to the Connect site.</span></p>
<p class="MsoNormal"><span style="mso-ansi-language:EN-US;">&nbsp;</span></p>
<p class="MsoNormal"><span style="mso-ansi-language:EN-US;">Here is an
example I recently ran into. I tested it on SQL Server 2012 and on SQL Server
2008 R2, and it reproduces on both. Execute the query below in the
AdventureWorks sample database, with the option to Include Actual Execution Plan
enabled (Ctrl+M), or request an Estimated Execution Plan (Ctrl-L).</span></p>
<p class="MsoNormal"><span style="mso-ansi-language:EN-US;">&nbsp;</span></p>
<p class="MsoNormal" style="mso-layout-grid-align:none;text-autospace:none;"><span style="font-size:10.0pt;font-family:Consolas;color:blue;mso-ansi-language:EN-US;">SELECT</span><span style="font-size:10.0pt;font-family:Consolas;mso-ansi-language:EN-US;"><span style="mso-spacerun:yes;">&nbsp;&nbsp; </span><span style="color:teal;">TerritoryID</span><span style="color:gray;">,<br>
</span><span style="mso-spacerun:yes;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </span><span style="color:teal;">Name</span><span style="color:gray;">,<br>
</span><span style="mso-spacerun:yes;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </span><span style="color:teal;">SalesLastYear</span><span style="color:gray;">,<br>
</span><span style="mso-spacerun:yes;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </span><span style="color:teal;">SalesYTD</span><span style="color:gray;">,<br>
</span><span style="mso-spacerun:yes;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </span><span style="color:fuchsia;">RANK</span><span style="color:gray;">()</span> <span style="color:blue;">OVER </span><span style="color:gray;">(</span><span style="color:blue;">ORDER</span> <span style="color:blue;">BY</span> <span style="color:teal;">SalesLastYear</span><span style="color:gray;">)</span> <span style="color:blue;">AS</span> <span style="color:teal;">Rank1</span><span style="color:gray;">,<br>
</span><span style="mso-spacerun:yes;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </span><span style="color:fuchsia;">RANK</span><span style="color:gray;">()</span> <span style="color:blue;">OVER </span><span style="color:gray;">(</span><span style="color:blue;">ORDER</span> <span style="color:blue;">BY</span> <span style="color:teal;">SalesYTD</span><span style="color:gray;">)</span> <span style="color:blue;">AS</span> <span style="color:teal;">Rank2</span><br>
<span style="color:blue;">FROM</span><span style="mso-spacerun:yes;">&nbsp;&nbsp;&nbsp;&nbsp; </span><span style="color:teal;">Sales</span><span style="color:gray;">.</span><span style="color:teal;">SalesTerritory</span><br>
<span style="color:blue;">ORDER</span> <span style="color:blue;">BY</span> <span style="color:teal;">SalesLastYear</span><span style="color:gray;">;</span><br style="mso-special-character:line-break;">
<br style="mso-special-character:line-break;">
</span><span style="mso-ansi-language:EN-US;"></span></p>
<p class="MsoNormal"><span style="mso-ansi-language:EN-US;">When
following the flow of data (by reading the execution plan from right to left),
we see that the data is first read (with an unordered clustered index scan,
since there is no better index available), then sorted by SalesLastYear, so
that Rank1 can be computed (using two Segment operators and a Sequence Project operator
– don’t ask). After that, the rows are sorted again, now by SalesYTD, and we
see another combination of two Segment and one Sequence Project, for the
calculation of Rank2. And then, finally, the rows are re-sorted by SalesLastYear
so that they can be returned in the requested order.</span></p>
<p class="MsoNormal"><span style="mso-ansi-language:EN-US;">&nbsp;</span></p>
<p class="MsoNormal"><span style="mso-ansi-language:EN-US;">Now the big
question is: why does the plan include two sort operators that both sort the
data in the same (SalesLastYear) order? If the task of the optimizer is to find
smart ways to rearrange computation order for better performance, why doesn’t
it simply compute Rank2 first and Rank1 after that? In that case, the rows are
already in the SalesLastYear order after the last Sequence Project, so no extra
sort is needed. The execution plan of the query below confirms this suspicion:</span></p>
<p class="MsoNormal"><span style="mso-ansi-language:EN-US;">&nbsp;</span></p>
<p class="MsoNormal" style="mso-layout-grid-align:none;text-autospace:none;"><span style="font-size:10.0pt;font-family:Consolas;color:blue;mso-ansi-language:EN-US;">SELECT</span><span style="font-size:10.0pt;font-family:Consolas;mso-ansi-language:EN-US;"><span style="mso-spacerun:yes;">&nbsp;&nbsp; </span><span style="color:teal;">TerritoryID</span><span style="color:gray;">,</span><br>
<span style="mso-spacerun:yes;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </span><span style="color:teal;">Name</span><span style="color:gray;">,</span><br>
<span style="mso-spacerun:yes;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </span><span style="color:teal;">SalesLastYear</span><span style="color:gray;">,</span><br>
<span style="mso-spacerun:yes;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </span><span style="color:teal;">SalesYTD</span><span style="color:gray;">,</span><br>
<span style="mso-spacerun:yes;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </span><span style="color:fuchsia;">RANK</span><span style="color:gray;">()</span> <span style="color:blue;">OVER </span><span style="color:gray;">(</span><span style="color:blue;">ORDER</span> <span style="color:blue;">BY</span> <span style="color:teal;">SalesYTD</span><span style="color:gray;">)</span> <span style="color:blue;">AS</span> <span style="color:teal;">Rank2,</span><br>
<span style="mso-spacerun:yes;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </span><span style="color:fuchsia;">RANK</span><span style="color:gray;">()</span> <span style="color:blue;">OVER </span><span style="color:gray;">(</span><span style="color:blue;">ORDER</span> <span style="color:blue;">BY</span> <span style="color:teal;">SalesLastYear</span><span style="color:gray;">)</span> <span style="color:blue;">AS</span> <span style="color:teal;">Rank1</span><br>
<span style="color:blue;">FROM</span><span style="mso-spacerun:yes;">&nbsp;&nbsp;&nbsp;&nbsp; </span><span style="color:teal;">Sales</span><span style="color:gray;">.</span><span style="color:teal;">SalesTerritory</span><br>
<span style="color:blue;">ORDER</span> <span style="color:blue;">BY</span> <span style="color:teal;">SalesLastYear</span><span style="color:gray;">;</span><br style="mso-special-character:line-break;">
<br style="mso-special-character:line-break;">
</span><span style="mso-ansi-language:EN-US;"></span></p>
<p class="MsoNormal"><span style="mso-ansi-language:EN-US;">Indeed, the
execution plan of this query includes only two Sort operators instead of the
three we had in the first execution plan. If you include both queries in a single
batch, you’ll see an estimated cost of 59% for the first query, and 41% for the
second. (Some people think that the percentages shown in an Actual Execution
Plan are an indication of the actual cost; that is not the case – the percentages
are computed from the <b style="mso-bidi-font-weight:normal;"><i style="mso-bidi-font-style:normal;">Estimated</i></b> Subtree Cost property of
the left-most SELECT operator). The SalesTerritory table is too small to
measure any actual performance differences, but I tried queries with a similar
pattern on the SalesOrderDetail table (121,317 rows), and on
FactProductInventory in AdventureWorksDW (776,286 rows) and I did see an actual
difference in execution time. No surprise, but now I know for sure!</span></p>
<p class="MsoNormal"><span style="mso-ansi-language:EN-US;">&nbsp;</span></p>
<p class="MsoNormal"><span style="mso-ansi-language:EN-US;">So, we have
seen that simply reordering the two columns that use an OVER clause reduces the
query cost by about 30%. How is it possible that such a simple, basic
reordering strategy is not considered by the optimizer? Surely, this can only be
a bug?</span></p>
<p class="MsoNormal"><span style="mso-ansi-language:EN-US;">&nbsp;</span></p>
<p class="MsoNormal"><span style="mso-ansi-language:EN-US;">That’s what
Fabiano Neves Amorim thought when he filed <a href="https://connect.microsoft.com/SQLServer/feedback/details/679342">this bug
on Connect</a>. But, as you can see, the bug has been closed as “By design”. That
probably doesn’t mean that someone wrote a design document telling the
optimizer team to make sure that OVER clauses must always be evaluated in the
order in which they appear in the query, even if a different order would be
cheaper. I rather think of it as “missing an optimization opportunity is not a
bug; the results are still correct, just a bit slower – so we’re going to close
this “bug” report”. In this case, maybe the optimization opportunity was not
identified during the design phase, or it was just too hard to implement. The
latter statement may sound ridiculous at first (how can such a basic rewrite be
too hard?), but you have to be aware that the optimizer does not operate on the
original query text, but on an internal representation that is based on
mathematics and set theory. Rewrites that may be complex in the query text may
be obvious in this representation, but the reverse can also be true – so I’m
prepared to accept the comment that Andrew Richardson made on behalf of
Microsoft to the above Connect item: that it would be fairly complicated for
the Query Optimizer.</span></p>
<p class="MsoNormal"><span style="mso-ansi-language:EN-US;">&nbsp;</span></p>
<p class="MsoNormal"><span style="mso-ansi-language:EN-US;">That does
not mean I agree with the rest of Andrew’s comment. He suggests that this is a
case where we should not rely on the optimizer, but rewrite our queries
ourselves, especially since it’s such an easy rewrite in this case. Well, I
would agree with that, except that: (a) this missed optimization opportunity is
not documented, so how are developers supposed to know that they may need to
reorder columns in a SELECT clause for optimal performance? (that is one of the
reasons for this blog post); and (b) the behavior of the optimizer in this
situation is not documented, so it can change at any time; I’d hate to rewrite
all my queries and then find that the sysadmin just installed a patch and now
the optimizer always starts with the <b style="mso-bidi-font-weight:normal;">last</b>
instead of the first OVER clause (or, worse, I don’t find it and just get all
the bad performance right back).</span></p>
<p class="MsoNormal"><span style="mso-ansi-language:EN-US;">&nbsp;</span></p>
<p class="MsoNormal"><span style="mso-ansi-language:EN-US;">However,
Andrew is right in so far that, at this time, rewriting queries does
consistently improve performance in all my tests. So at this time, rewriting
does seem to be the right thing to do. Just keep in mind that you have to test
all your queries, not only on your test server but also on your production
hardware, and that you’ll have to repeat these tests on a regular basis (at
least after each patch, CU, service pack, or other change).</span></p>
<p class="MsoNormal"><span style="mso-ansi-language:EN-US;">&nbsp;</span></p>
<p class="MsoNormal"><span style="mso-ansi-language:EN-US;">The basic
rewrite pattern is simple – for each query that uses OVER clauses with
different ORDER BY subclauses as well as an ORDER BY clause on the query that
matches one of the ORDER BY subclauses, make sure that the OVER clause that
uses the matching ORDER BY comes last in the SELECT list. If you have a client
that expects the columns in a particular order, the rewrite becomes a bit more
complex – in that case, you have to use a CTE that includes the OVER clauses in
the optimized order, and then you can reorder the columns in the query that
references the CTE – as shown in this example:</span></p>
<p class="MsoNormal"><span style="mso-ansi-language:EN-US;">&nbsp;</span></p>
<p class="MsoNormal" style="mso-layout-grid-align:none;text-autospace:none;"><span style="font-size:10.0pt;font-family:Consolas;color:blue;mso-ansi-language:EN-US;">WITH</span><span style="font-size:10.0pt;font-family:Consolas;mso-ansi-language:EN-US;"> <span style="color:teal;">MyCTE</span><br>
<span style="color:blue;">AS </span><span style="color:gray;">(</span><span style="color:blue;">SELECT</span><span style="mso-spacerun:yes;">&nbsp;&nbsp; </span><span style="color:teal;">TerritoryID</span><span style="color:gray;">,</span><br>
<span style="mso-spacerun:yes;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </span><span style="color:teal;">Name</span><span style="color:gray;">,</span><br>
<span style="mso-spacerun:yes;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </span><span style="color:teal;">SalesLastYear</span><span style="color:gray;">,</span><br>
<span style="mso-spacerun:yes;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </span><span style="color:teal;">SalesYTD</span><span style="color:gray;">,</span><br>
<span style="mso-spacerun:yes;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </span><span style="color:fuchsia;">RANK</span><span style="color:gray;">()</span> <span style="color:blue;">OVER </span><span style="color:gray;">(</span><span style="color:blue;">ORDER</span> <span style="color:blue;">BY</span> <span style="color:teal;">SalesYTD</span><span style="color:gray;">)</span> <span style="color:blue;">AS</span> <span style="color:teal;">Rank2</span><span style="color:gray;">,</span><br>
<span style="mso-spacerun:yes;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </span><span style="color:fuchsia;">RANK</span><span style="color:gray;">()</span> <span style="color:blue;">OVER </span><span style="color:gray;">(</span><span style="color:blue;">ORDER</span> <span style="color:blue;">BY</span> <span style="color:teal;">SalesLastYear</span><span style="color:gray;">)</span> <span style="color:blue;">AS</span> <span style="color:teal;">Rank1</span><br>
<span style="mso-spacerun:yes;">&nbsp;&nbsp;&nbsp; </span><span style="color:blue;">FROM</span><span style="mso-spacerun:yes;">&nbsp;&nbsp;&nbsp;&nbsp; </span><span style="color:teal;">Sales</span><span style="color:gray;">.</span><span style="color:teal;">SalesTerritory</span><span style="color:gray;">)</span><br>
<span style="color:blue;">SELECT</span><span style="mso-spacerun:yes;">&nbsp;&nbsp; </span><span style="color:teal;">TerritoryID</span><span style="color:gray;">,</span><br>
<span style="mso-spacerun:yes;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </span><span style="color:teal;">Name</span><span style="color:gray;">,</span><br>
<span style="mso-spacerun:yes;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </span><span style="color:teal;">SalesLastYear</span><span style="color:gray;">,</span><br>
<span style="mso-spacerun:yes;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </span><span style="color:teal;">SalesYTD</span><span style="color:gray;">,</span><br>
<span style="mso-spacerun:yes;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </span><span style="color:teal;">Rank1</span><span style="color:gray;">,</span><br>
<span style="mso-spacerun:yes;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </span><span style="color:teal;">Rank2</span><br>
<span style="color:blue;">FROM</span><span style="mso-spacerun:yes;">&nbsp;&nbsp;&nbsp;&nbsp; </span><span style="color:teal;">MyCTE</span><br>
<span style="color:blue;">ORDER</span> <span style="color:blue;">BY</span> <span style="color:teal;">SalesLastYear</span><span style="color:gray;">;<br style="mso-special-character:line-break;">
<br style="mso-special-character:line-break;">
</span></span><span style="mso-ansi-language:EN-US;"></span></p>
<p class="MsoNormal"><span style="mso-ansi-language:EN-US;">These
rewrites are indeed the best option for the time being – but I still think that
the optimizer should be improved to do these rewrites internally. So I decided
to file <a href="https://connect.microsoft.com/SQLServer/feedback/details/740437/avoid-extra-sort-in-queries-with-multiple-over-clauses">a
new item on Connect</a>, this time labeling it as a suggestion instead of a
bug. If you agree with me that this would be a great investment of Microsoft’s
engineering time, then please add your vote to this item. (Or vote it down if
you think I’m just being ridiculous). But don’t stop there! Microsoft knows me;
they know I’m a geek who plays around with options like this and then runs into
this issue. No real production databases were hurt during the production of
this blog. And if I am the only one, then, frankly, I myself will say that they
have better things to do with their engineering time. However, if I know that this
affects real people, I can make a much stronger case to Microsoft for getting
this fixed.</span></p>
<p class="MsoNormal"><span style="mso-ansi-language:EN-US;">&nbsp;</span></p>
<p class="MsoNormal"><span style="mso-ansi-language:EN-US;">So – go out
to your production servers and find if you use queries with this pattern (two OVER
clauses with different ORDER BY and an ORDER BY on the final query), then check
to see if you should rewrite them. And then report back – add a comment here or
on the Connect item; share if this affected you, and how much performance you
were able to gain as a result of the rewrite.</span></p>
<p class="MsoNormal"><span style="mso-ansi-language:EN-US;">&nbsp;</span></p>
<p class="MsoNormal"><span style="mso-ansi-language:EN-US;">If
Microsoft knows that their customers would actually benefit, they’ll be much
more inclined to add this improvement to the optimizer then if it’s just about
me, a geek moaning about an edge case that no one in the real world would ever
run into.</span></p>