SQLNikon

[contact-form][contact-field label=’Name’ type=’name’ required=’1’/][contact-field label=’Email’ type=’email’ required=’1’/][contact-field label=’Website’ type=’url’/][contact-field label=’Comment’ type=’textarea’ required=’1’/][/contact-form]

Virtual Book Club: SQL Server Execution Plans, Chapter 4

October 1, 2013December 13, 2017Doug

Continuing with my review of Chapter 3, here are my thoughts on Chapter 4. This chapter was a deeper dive into some of the more complex plans that can be generated from T-SQL statements like APPLY, CTEs, MERGE, views, as well as how indexes are chosen.

Things I learned

Writing queries is an art form, you need to understand all the T-SQL statements and how they affect the query plan. Don’t get stuck on always using one technique over another. For example, adding a WHERE clause to a sub-select may out perform an APPLY statement (based on the data set). The point here is by understanding how each T-SQL statement will interact with your data, you can try different approaches to solving your problem and reach the results in the most efficient way.

Using ROLLBACK can be a useful approach when needing to view the actual execution plan for UPDATE, INSERT, DELETE, or MERGE statements. This allows the actual query plan to be generated but the DML is not committed to the database allowing you to repeatedly change the query to generate the best quality plan.

Likes

Grant did a great job of teaching how to dissect large query plans. Just like when you are trying to each an elephant, you need to approach large query plans one operator at a time. I also thought the way Grant explained views and indexes was very thorough. I now have a better understand of when a view might be expanded to the table level or when an index might be passed over because of bad statistics.

The other thing I noticed is how Grant was able to sharpen our understanding of T-SQL and database fundamentals in the process of this chapter. Being able to learn how the query optimizer generates plans while learning better uses of sub-queries, APPLY, CTEs, MERGE, views, and indexes is a great thing.

Dislikes

As with most chapters thus far, no complaints on the material presented. Just a minor typo on the T-SQL Listing 4.7. The parameter being passed to the SP should be @BusinessEntityID and not @EmployeeID.

On to chapter 5!

Doug Purnell

Virtual Book Club: SQL Server Execution Plans, Chapter 3

September 19, 2013December 13, 2017Doug

Continuing with my review of Chapter 2, here are my thoughts on Chapter 3. This chapter was short and sweet. XML plans were the main focus and this chapter gave me some great ideas on how to use XQuery to make reading the XML based plans easier.

Things I learned

I’ve always stayed away from XML. In my many years working in the database world I have not had the need to interact with XML data. Querying XML based query plans with XQuery is first time I’ve seen a benefit of using XML as a DBA. For larger plans it will be easier to query the XML data instead of trying to hunt down operations with in the graphical view.

You can use XQuery to query .sqlplan files, XML data stored in columns or directly from the plan cache. Caution needs to be used when querying directly against the plan cache as this can have performance side effects.

Likes

I liked how Grant told us upfront in the first part of chapter that if you’re not dealing with SQL Server instances before version 2005 that text-based query plans may not be something you need to understand. Besides being a deprecated feature, XML offers plenty of advantages over text-based plans.

Dislikes

Nothing to dislike in this chapter. There was just a minor typo in the supplied script for chapter 3. The book references Listing 3.18 but in the script file it is listed as 3.19

On to chapter 4!

Doug Purnell

Virtual Book Club: SQL Server Execution Plans, Chapter 2

September 17, 2013December 13, 2017Doug

Continuing with my review of Chapter 1, here are my thoughts on Chapter 2. You could think of Chapter 1 as walking into the shallow end of the pool and Chapter 2, swimming into the deep end, but in a good way. I never felt like I was drowning in the material, all the topics were presented in a way that made it easy to comprehend.

Things I learned

Blocking/Non-Blocking determines when an operator can begin processing data. Blocking operators need to wait for all the data before they can begin outputting data (Hash-Join is an example of a blocking operation). Non-Blocking, on the other hand, can begin processing as soon as the data starts arriving (Merge-Join is an example of a non-blocking operation). This subtle idea can help you better understand how data flows between your operators.

Sorting is something I always used as a presentation technique but in the world of query plans and table joins it can have a big impact on the optimizer’s plan generation. For example, a merger-join will only be used on sorted data, period. But if you have two non-sorted lists, hash-join may be a better option. Because hash-joins builds temporary table with in tempdb, too many of them can cause tempdb performance issues.

Depending on the data sets, hash-join may be a good fit (think small data sets). They should also be warning sign. Pay attention to missing indexes, a missing WHERE clause, or calculations in the WHERE clause that can make it non-sargable (unable to use an index).

When using SET STATISTICS IO ON to better understand logical and physical page reads, scan count was something that I was aware of but didn’t fully understand. There is a great blog post on MSDN that explains when you will see scan count 0, 1 or N. Just think of it as how many times a scan is started looking for data in a data set.

When generating a query plan for an UPDATE statement, the optimizer has to first determine what rows need to be updated. This is accomplished by first doing scan/seek operation. Pay attention to the WHERE clause of your UPDATE statement as this will determine how efficient the select portion of the UPDATE statement will be.

Likes

Even though there was plenty more information presented in chapter 2 over chapter 1, everything was clear and concise. Following along with the scripts on my local instance of SQL Server made the query plans easier to follow and I was spend more time understanding how the optimizer behaves.

Dislikes

There were only a few things that I would have liked to see presented better. Under the Nested Loops Join section, the tool top defines it as “For each row in the top (outer) input, scan the bottom (inner) input, and output matching rows. But Grant explained it as “… scanning the outer data set (the bottom operator in a graphical execution plan) once for each row in the inner set”. Grants explanation seemed on contradict the tool tip so I had to spend extra time reading through this section to understand what was actually happening within the nested loops join.

Scan count was referenced under the Filtering Data section but no time was given to explain if a low/high scan count was a sign of possible problems. But, as I do with other blog posts, when I hit a term and I don’t understand I search for it. I was able to find a blog post under MSDN that did a great job of explaining how it works as well as samples to show what would cause 0, 1, and N scan counts.

On to chapter 3!

Doug Purnell

Virtual Book Club: SQL Server Execution Plans, Chapter 1

September 5, 2013December 13, 2017Doug

This is actually my first book review. Following Denis’s lead, I’m going to discuss what things I learned, what I liked about the chapter and areas I disliked. As with speakers, feedback is important for authors. Hopefully our discussion of the book will help Grant on his third edition.

Things I learned

Did you know that you can save a XML version of the execution plan and share them with others? Well, neither did I. This would be a great way to get feedback on better ways to build queries. In fact SQLPerformance.com offers the ability to upload your plan directly from SQL Sentry’s Plan Explorer to their Q&A site. The SQL Server community has a wealth of information and there are always folks available to help you with your questions.

I’m a big fan of Idera’s SQL Diagnostic Manager. This tool makes it easy to see trouble areas within my SQL Server instances. The only down side is that I’m not using the DMO statements on a daily basis. With DMOs you’re able to pull a specific plan from the plan cache. This is useful when you’re developing queries and need to view a plan that may have been saved from a previous execution of your query. To interact with some DMOs, you need to use the APPLY operator because the DMOs are table-valued functions. I’ve known about APPLY but have not had the need to use it in my daily work.

Likes

Grant did a great job introducing the query engine and how a plan is ultimately built and saved for later use. Having a strong understanding of how the execution plan is built will help me write better queries. Grant didn’t throw too much material at me during the first chapter. This allowed me to comprehend the material better without feeling overwhelmed.

Dislikes

I didn’t find anything in this chapter that came across as a dislike and I’m guessing this trend will continue for the remainder of the book.

I’m looking forward the chapter 2 next week and the rest of the book. My goal is to be able to read execution plans better than seeing how thick the lines are between operators.

Doug Purnell

Work Stoppage Events (Meetings)

June 19, 2012December 13, 2017Doug

As the calendar reminder comes up with the dreaded 15 minute warning for another work stoppage event, it occurred to me how import it is to prepare for meetings to make them as productive as possible. I like to use Evernote to document all meetings and projects. Each morning I take a look at the scheduled meetings and start a new note for each of them by creating three sections:

Items to Discuss
Notes
To Do

Based on the meeting, I go through my master to do list and create bullets under ‘Items to Discuss’ to update the status of each item. I also comb through my inbox and add any issues that might be relevant to the meeting. It is important to spend time updating this section of meeting preparation, this can ultimately serve as a personal agenda so you can get all you points across to the rest of the attendees.

During the meeting I use the ‘Notes’ section to record items that pertain to my responsibilities and if actionable, they are recorded under the ‘To Do’ section. Once I have time to review my notes from the meeting, I’ll create new tasks in my master to do list from the ‘To Do’ section and give each item a priority based on the other tasks. I like to follow Brian Tracy’s (b|t) approach to prioritizing tasks based on his book Eat That Frog.

This is a great book that talks to procrastination and how to prioritize your biggest tasks (frogs) first instead of the smaller and easier tasks. By using his system, I’m able to knock out my big frogs and feel the weight being lifted off my shoulders. Before using his system, I would go through the day completing lots of smaller tasks, feeling great, but always knowing that the ‘big frog’ was always going to be sitting on my desk staring at me.

On a weekly basis I sit down with my manager for a one on one status meeting to talk through my priorities. I would suggest this for everyone to make sure your priorities line up with what is expected of you. Before each status meeting I’ll prepare the same way as any other meeting by creating a meeting template with in Evernote. For my one on one meetings, I like to review completed tasks for the week and set priorities for the upcoming week. Going over completed tasks is a great way to start the meeting off. It will give your manager an update of items you’ve accomplished for the week and set the tone for the upcoming week. Starting off on a good note leads to much more productive meeting in my opinion.

While I’m not in meetings and keeping my instances of SQL Server running their best, I like to use Thomas A. Limoncelli’s (b|t) recommendations in his book Time Management for System Administrators. This is a great book for those just getting started or the seasoned veterans looking for ways to maximize their day. One of his points in the book is to how to handle interruptions. Interruptions can be handled in three ways:

Delegate It
Record It
Do it

Everyone dreads that simple question, “You got a minute?”, that rarely lasts under a minute. You get two or more of these questions a day, and you can throw a wrench into any plans you had for completing your priority tasks. When someone asks you for help, they just want to be acknowledged. This is where Tom’s process helps, first you acknowledge the question, then determine where it lies with your other priorities.

If you have the opportunity to delegate it, this is always your best option, get the monkey off your back! If it needs your attention and can wait, record it as a tasks, give the person asking the question an estimate to complete and go back to doing your higher priority items. The last option, and the most damaging, is doing the task at that instance. This is the best outcome for the person asking the question because they receive instant gratification, but requires you to break your concentration on your current task and shift your focus to something new. Don’t forget to record this as a completed item in your master to do list. These ‘little’ interruptions can push other tasks out, so make sure your manager is updated on interruptions during your one on one.

I hope these tips and two book recommendations help you prioritize your day and take control of interruptions!

Doug Purnell

Working smarter, not harder, as a DBA

June 11, 2012December 13, 2017Doug

The butterflies were flying! This was my first presentation for Triad SQL PASS user group, and any group for that matter. I started attended our local SQL PASS chapter back in the fall of 2011 while I was between jobs. Kevin Goode was the one who got me thinking about doing a presentation, and after I had a few months under my current position as DBA, I came up with a topic: how to work smarter as a DBA. I found that starting a new DBA position requires working on understanding your environment quickly without disrupting the day-to-day business. This presentation gives you insight into tools that keep our servers running smooth and the training that keeps me up to date of the latest trends.

Here is the link to the PowerPoint slides.

Here is the link to the DBA Standards Document.

Here is the link to the server specific install settings and configuration.

Here is the link to the backup script I use each night.

Here is the link to the server side security audit script.

Here is the link to the database side security audit script.

Here are the links to the setup scripts that are used after the install of SQL Server 2008 R2. Just edit the files based on your environment:

Doug Purnell

Database Mail Configuration Error 264

January 22, 2012December 13, 2017Doug

As most of you know, the Database Mail feature in SQL Server 2008 is the life blood of any notification system for your instances. If Database Mail fails to send notifications, you may not be warned if a job fails, or worst, a backup job. We had a server last week that was exhibiting a weird behavior within Database Mail. I was receiving mail from all parts of the Database Mail environment except for Agent Job Notifications. I was able to send test emails from Database Mail, able to receive maintenance plan summary reports and even receive emails from within maintenance plans using the notification task. Why was Agent Job Notifications the only area that was mis-behaving? As I started diagnosing the issue I checked the main areas that usually stumped me in the past:

Verified database mail profile was enabled under SQL Server agent (Alert System Page)
Verified profile security was public and default
Restarted SQL Server Agent

The above three items usually resolved any issue I have with Database Mail. Did some more testing with my Agent Job and sure enough, still not working. Then I started digging into the logs, under SQL Server 2008 you can see logs from SQL Server, SQL Server Agent, Database Mail, and Windows all under the same screen. I dove into the Database Mail Log and there was an error for “[264] An attempt was made to send an email when no email session has been established”. Never seen this before. I started searching Google and found similar issues but none seemed to fit my situation or solve my problem. Because we have a simple setup for Database Mail, one account and one profile, I decided to rebuilt the configuration.

I deleted the profile and account, and then went back in and went through a complete rebuilt of the configuration. I used the same account and same profile settings as before. After I had the Database Mail configuration complete, I needed to make sure the profile was enabled under SQL Server Agent. Right click on SQL Server Agent and click Properties. Under the Alert System page, make sure Enable Database Mail is enabled and that your mail system is selected along with the correct profile name. With that done, I need to restart the SQL Server Agent. Upon restart the 264 error did not appear and I was able to receive emails from my Agent Job Notifications. Don’t know what caused the issue in the first place, but the rebuild seemed to clear things up.

Doug Purnell

#meme15 for Twitter

January 16, 2012December 13, 2017Doug

Jason Strate (blog|Twitter) has started a new meme project about social networking for SQL Server professionals. This month’s theme is all about Twitter. We have been asked to answer the following two questions:

Why should the average Jane or Joe professional consider using Twitter?
What benefit have you seen in your career because of Twitter?

I consider myself an average Joe SQL Server professional. I’m a DBA for a private University and enjoy solving business problems with data. Up until June of 2010, I did not have a Twitter account and was not involved in the #SQLFamily. When I was getting back into the DBA field after going to the dark side (management) for a few years, I found Twitter as the catalysts for learning. I was able to follow a few big hitters in the #SQLFamily and with in the first day, started reading tweets for webcasts and blog posts.

This is the key for me using Twitter, there is so much good information posted everyday to keep you informed about SQL Server topics. The beauty of Twitter is most of the #SQLFamily don’t post the same as your friends do on Facebook. Twitter has become a medium for disseminating information. Now, there are plenty of tweets from celebrities and even your friends that are just plain noise, this is where the #SQLFamily stands out. Most of the people I follow tweet about topics related to SQL Server. I enjoy reading new blogs and being notified of webcasts where I can learn new things.

As I stated earlier, I found Twitter as my means of getting back into the DBA world. I started with Brent Ozar’s Twitter Book and created an account. From there it was as easy as following Brent Ozar (blog|Twitter), Glenn Berry (blog|Twitter), Thomas Larock (blog|Twitter) and Aaron Bertrand (blog|Twitter). From just those four people, I was introduced to so many more and started following them. Each time I started following someone new, I was introduced to few other people and the Twitter snowball kept growing. As of Jan 2012, I’m following ~150 people and it’s hard to keep up during the day and usually spend nights catching up on all the tweets for the day.

The benefits of Twitter helped me land a great DBA job in higher education. I had plenty of experience under SQL Server 2000 & 2005 but only limited direct interaction with SQL Server 2008 & 2008 R2. This is where the webcasts and blogs that I was notified about on Twitter helped me fill the gaps and use that knowledge to practice in my home lab. Without Twitter, I would have not had the #SQLFamily behind me and would have struggled getting back in the game.

Doug Purnell

2012 To Do’s for #SQLFamily

January 9, 2012December 13, 2017Doug

As I sat down and wrote out my goals for 2012 for my job, they didn’t take into account my blogging, user groups and #SQLFamily. This is why this is a perfect #mememonday project. You can read more about #mememonday here from Thomas Larock (Blog, Twitter).

I’m fairly new to the #SQLFamily. I didn’t start getting involved till June 2011 and up to now, didn’t participate in monthly blogging like #mememonday or #tsql2sday. For the past 8 months have just been digesting information like I was eating at Golden Corral. Twitter, webcasts and RSS feeds can take hours a day to stay updated. I still find it hard to get through all the Twitter feeds a day and finally had to limit the number of RSS subscriptions under Google Reader so those didn’t get behind as well. As I write this, I can see TweetDeck notifications going off every few minutes.

So here are my personal goals list for #SQLFamily in 2012:

Continue to attend Triad PASS and Triad BI PASS. The part that I’m going to work on this month is to blogging about my experiences. I’ve seen a few other posts related to this item and thought it was a great idea. This will help others that have not started, to attend their local PASS chapters to see the topics discussed how much fun they are.
Start attending the virtual PASS chapters. There are some great virtual chapters held every week that discuss topics that you may not otherwise receive at your local events. Here to, I aim to blog about each one to keep my writing skills fresh.
Present at Triad PASS in June 2012. Our local Triad PASS chapter leader, Kevin Goode, asked me to present and I decided to jump in. This will be my first time ever presenting anything outside of lunch and learns through work. I have not picked a topic yet, but because I started a new job in November 2011, I’m thinking about walking though my management processes for my new SQL Server instances.
Stay involved in #mememonday events. This was started by Thomas Larock (Blog, Twitter) and I’m looking forward to these monthly blogs. This is a great way to blog about something that you otherwise may not write about. I feel too many times and get in my comfort zone of daily DBA tasks and don’t stretch my boundaries to learn new things with in SQL Server.
Start blogging for #tsql2sday. This was started by Adam Machanic (blog, Twitter) and just like with #mememonday, will give me the opportunity to participate in blogs about items I’m unfamiliar with. This will require researching a topic and writing about it. I’m looking forward to this one.
Midnight DBA. Sean (blog, Twitter) and Jen (blog, Twitter) make this live broadcast a blast. I’m guilty of falling asleep before the show starts but my goal this year is to catch at least 2 shows a month live. The pre and post shows are just as fun as the main show. They cover technical topics with a fun twist and their sense of humor keeps me laughing through the entire show. This will be another event that I plan to blog about.
Brent Ozar PLF’s Tech Triage Tuesday. This is a great half hour weekly webcast that covers all aspects of SQL Server. Brent Ozar (blog, Twitter) and his team, cover topics from performance tuning to SANs, each week is a must see. As you can guess, I’m going to blog about these as well.

My goals this year for #SQLFamily are all around bringing awareness to the training opportunities that are available to everyone. Whether you are able to attend these events live or watch them from the comfort of your home, my goal is to spark interest in all the events. This is how I was introduced to #SQLFamily back in June 2011 and I hope I can do the same for other SQL Server DBAs and developers out there.

Have a great year #SQLFamily,

Doug Purnell