Year: 2017

TSQL Tuesday #96: Folks Who Have Made a Difference

Well, this is my first T-SQL Tuesday! Here’s the original post: link. I’m very excited to participate. The host for this month is Ewald Cress (blog | twitter ) and the subject is talking about people who’ve made a difference. I’ve been working with SQL Server for a few years and I have many people to be grateful for! I thought I’d start by sharing the story that I use in my presentation, Reading Execution Plans Successfully.

My first SQL job was a Junior BI Developer, right out of college. I was learning from Treye Nekola , a Sr Database Administrator. At the time, I could write a SELECT statement, but I had trouble with JOINs and WHERE clauses. Sometimes my queries would run for minutes a time, even with NOLOCK hints they were taking a while. Treye had the wisdom to call me out on it, and I said something very foolish. I said, “I don’t care how long my query takes as long as it gets results.” Treye just looked at me, knowing how stupid that sounded. I let it go, because I was embarrassed. He was right, and my queries needed work. Over the next months, Treye showed me why my queries were poorly written, and with a godly amount of patience, he set me on the right path. I will forever be grateful to Treye for being an awesome mentor and friend!

The next person I’d like to appreciate is Brent Ozar. I doubt Brent knows much about me, but his material and training has seriously improved my skills. Treye showed me his blog, and I started to learn about the SQL Server online resources. Brent’s also the person who inspired me to start presenting. At PASS Summit 2016, Brent had a session titled 500-Level Guide to Career Internals. At this session, Brent encouraged the audience to start blogging and speaking. I haven’t blogged as much as I should, but I did take his advice on speaking. It has seriously improved my life to speak because I get to teach people and meet tons of really excellent people in the database world. It is so rewarding to teach people a new skill or fact.

And finally, I appreciate Andy Yun. I think I first met Andy at SQL Saturday Iowa City. We were both speakers and spent time talking in the speaker room. We parted ways for a while, but Andy mentioned he was planning on making a trip to the Iowa SQL user groups so I was sure to see him again. We met up before his presentation in Des Moines, Iowa, where I told him that I was taking a job in Illinois, and I was looking for advice. By sheer luck, it was the exact same city that Andy was living in! I had no idea he lived in the exact same suburb as I was headed to. Since that day, Andy has been a close friend and mentor to me. I’m incredibly grateful for the guidance and help he’s given me, in life and career. I say without hesitation that Andy has made a massive difference in my life.

I would like to make some honorable mentions to some people I couldn’t fit in this post, and I’m sure there’s many more that I couldn’t fit in this post.

 

Thank you for being great people:

Ted Schnieders

Bill Andersen

Nicole Loyd

Fred Botts (leader of the Des Moines SQL Server User Group)

 

Thoughts on Performance Tuning methodology

This post is going to be a change of pace for the blog. I want to talk about how I approach performance tuning, specifically when talking about queries. This won’t be a tutorial, just somewhat of a journal post.

The other day, I was working on optimizing a query in a development environment with another database administrator (DBA). We were comparing execution plans for a new index and looking to see if our query wanted the index. We created several variations of the index and measured whether SQL Server would use the index when running a SELECT statement against the table. Once the query used the index, we decided to look at SQL Server’s IO Statistics . We compared several variations of the index before I noticed that we had been running a SELECT * against the table.

I think this was an example where we should have been testing with an actual workload. My thought process is that a SELECT * is usually not a good representation of the table. If a table is very wide (meaning it has many columns), SQL Server may choose to scan the Clustered Index of a table instead of using a well-designed Nonclustered Index. In the system I was working on, most queries will have a specific set of columns that they want to load. We didn’t keep our testing consistent with the actual system workload.

I’m going to try to make a demo of this scenario in a future blog post. In the mean time, here’s a list of the things that I think are important for consistent testing of performance:

  • Same version of SQL Server, or at least set to similar server settings
  • Table structure including data types
  • Same indexes
  • Same set of data used for testing
  • If possible, same system memory and CPU
  • If possible, similar existing Execution Plan Cache

I listed the last two as possible because sometimes it’s not possible to have the same hardware in Dev as Prod, and I don’t think it’s realistic to expect that on larger SQL Servers. In this case, simply generating an Estimated Execution Plan from the production environment can be good enough.

Using Live Query Statistics on older SQL Servers

First, thanks to everyone who attended my session at SQL Saturday #634 Iowa City! I spoke about execution plans and how to begin reading them. Here’s a link to my session.

I had an attendee come up afterwards who mentioned that Live Query Statistics could be used with the newest version of Management Studio to watch query execution plans live on older versions of SQL Server. I was completely unaware of this, and this feature is really awesome. Live Query Statistics allows you to watch what indexes or actions SQL Server is doing while your query executes. This works really well if your query runs for one or more minutes and you want to see what is taking this much time.

Once I learned this, I was curious what versions this feature can be used on. I figured there were some limitations, so I made sure I had the latest release of Management Studio (Standalone), at the time of this post, it’s 17.1. It appears to me that the oldest version of SQL Server where this will work is SQL Server 2014, based on a test I ran locally and documentation.

Here’s the screenshot, directly from Microsoft’s documentation on Live Query Statistics:

This is super cool since it’s a lot of work to upgrade a SQL Server, and this lets me use these features without upgrading (yet).

 

I’m presenting at Iowa SQL Server User Group!

I’ll be at Gravitate (Link) on Thursday, 2/23/2017 at 6 PM for my first presentation! The subject is “Execution Planning for Success!” Click here to download my presentation:

Execution Planning for Success

I’ll be posting the demos after the presentation for anyone who would like to run the demos themselves.

By continuing to use the site, you agree to the use of cookies. more information

The cookie settings on this website are set to "allow cookies" to give you the best browsing experience possible. If you continue to use this website without changing your cookie settings or you click "Accept" below then you are consenting to this.

Close