Keeping up with SQL Server

I gave a session for the recent PASS Marathon entitled “Modern T-SQL for Better Performance.” During the session, I talked mostly about windowing functions. These functions introduced in 2005 and 2012 not only make writing T-SQL code easier, but often it’s better performing as well. In many cases, they can help you avoid self-joins and cursors

After introducing hundreds – if not thousands – of people to windowing functions over the years (presentations, articles, books), I still find that it’s a topic that most people don’t understand or have taken the time to learn about them. That’s disappointing to me because I’ve tried so hard to spread the word about this great functionality. After presenting my T-SQL window function session at events, people would tell me that they had no idea about functions like LAG and would start using them right away, often on problems they had been trying to solve for some time.

For most folks, it’s easier and faster to keep doing things the same way that it’s always been done even if there is a better way. There is truth, especially when coding, in the old idiom “if it’s not broke, don’t fix it.” Just because there is something new doesn’t mean developers should revisit all the existing code and apply those new techniques. Chances are, they will end up introducing more bugs. On the other hand, for new development or when trying to find ways to improve the performance of existing code, it’s worth taking a look at new functions and techniques.

SQL Server has improved in more ways than new T-SQL functionality. Over ten years ago, Microsoft introduced Extended Events (aka XEvents) for SQL Server. At first, there was no user interface and X-Query was used to query the data collected, so it wasn’t too popular. Eventually, people like Erin Stellato and Grant Fritchey began to sing the praises of using XEvents over the old Profiler and Trace and show others how to use them. Not only was there more functionality and less impact on the server compared to Trace, Microsoft eventually added a GUI to XEvents to make it easier to use.

I must admit that I was late to the XEvents party. It was so easy to fire up Profiler (not production, my local dev machine) to figure out what query SSRS or an app was sending to SQL Server. When I needed to show a customer that a query called a user defined function a million times, it was quick and easy to demonstrate that with Profiler. Converting Profiler sessions to Trace to capture events on a production server was just too easy!

At one point, I made a commitment to never use Profiler or Trace again, but I soon found myself falling back on old habits. It took a lot more commitment to finally get comfortable and learn that it wasn’t so difficult. Eventually I even created a Redgate University Module demonstrating how to set up custom metrics with XEvents and created a session to help people make the transition.

Keeping up with the new stuff is worth it. You never know when you might need it.

Commentary Competition

Enjoyed the topic? Have a relevant anecdote? Disagree with the author? Leave your two cents on this post in the comments below, and our favourite response will win a $50 Amazon gift card. The competition closes two weeks from the date of publication, and the winner will be announced in the next Simple Talk newsletter.

The post Keeping up with SQL Server appeared first on Simple Talk.

from Simple Talk https://ift.tt/32VqQ4k
via