T-SQL Tuesday #51 – Don’t bet against CHECKSUM when performing backups

Automating repetitive tasksThis month’s T-SQL Tuesday is being hosted by Jason Brimhall (blog | Twitter).  Jason’s theme is about gambling with SQL Server.  What areas have you gambled with?  Are there tasks that you should be doing that you are betting won’t be needed?  One came to mind for me, CHECKSUM.  I’ve always been a fan of Maintenance Plans.  They are an easy way to get basic maintenance tasks established on an instance of SQL Server.  The major down side of the built-in maintenance plans is no support for the CHECKSUM media option for BACKUP DATABASE.  If you are performing backups without this option enabled, you run the risk of the pages in the backup media not matching the pages stored in the database.  This is because without the CHECKSUM option, SQL Server doesn’t know if the page written to the backup media was written out correctly.  The CHECKSUM option ensures that every page written out to your backup media has a checksum that matches the checksum of the page in the database.  The CHECKSUM option also goes as far as doing a checksum on the entire backup that the RESTORE statement can take advantage of.  There is a performance impact for using the CHECKSUM option but as everyone says, your backup is only as good as your restore.  If you bet against your restore, your odds of a resume updating event just increased.

Now that you understand the benefits of CHECKSUM for your backups, how do you integrate it into your backup process?  It’s time to graduate from the built-in Maintenance Plans and move to Ola Hallengren’s SQL Server Maintenance Solution.  The is by far the best maintenance scripts available within the SQL Server community.  Do yourself a favor and pull the scripts down to a test instance and give it a try.  I made the move a few months ago and have never looked back.

Don’t bet against CHECKSUM when performing your backups!

Doug Purnell

T-SQL Tuesday #50 – Automating Tasks

This month’s T-SQL Tuesday topic is the reason I love being a DBA, automating repetitive tasks.  Hemanth Damecharla (b|t) picked a timely topic, as you start the new year, are there certain tasks that you need to automate that tend to sit on the corner of your desk yelling at you?

This topic also reminds me of a chart I came across in the past describing the time savings associated with automating repetitive tasks.  As at most places, we are all asked to do more with fewer resources.  This often leads to additional workloads without the time in the day to complete them.  How many of these can you automate?

Automate some tasks!

Automate some tasks!

As I approach each task or even large projects, I like to start with a manual approach.  This gives me time to understand the business problem I’m solving and plan my attack for automation.  Some tasks don’t need to be automated, they may be solved by using simple T-SQL statement can be built to solve the problem.  As soon as I run across tasks that contain multiple data sources, SSIS is my go to tool.  SSIS allows me to build modular packages that can be reused and organized for automation.

Back in 2012 when PASS was in Seattle, WA, I attend a pre-con for SSIS Design Patters hosted by Andy Leonard, Tim Mitchell, Matt Mason, Michelle Ufford, and Jessica Moss.  This really opened my mind to what’s possible with SSIS.  I recommend the book  that sparked the pre-con if you’re interested in learning more.

As you start your automation effort, realize the upfront time spent may seem counter productive.  But the time invested now will pay benefits down the road.  Instead of having to manually run scripts (and having to be reminded to do so), your automated process will be running in the background making you look like a rock star!

Happy Automating

Virtual Book Club: SQL Server Execution Plans, Chapter 5

SQL Server Execution Plans

Everyone loves reading a good book, especially about SQL Server.  I’ve been on a training kick lately and the timing of this book club was perfect.  Denis Gobo (b|t) has experience starting a book club where he works and decided to bring it to the SQL Server community.  The goal of the club is to read a chapter each week and discuss things you learned, liked and disliked.  You can ready Denis’s original post here.  The first book chosen was written by Grant Fritchey (b|t) and is titled “SQL Server Execution Plans, Second Edition”.  You can get a free electronic copy or purchase a print copy from simple-talk.com.

Continuing with my review of Chapter 4, here are my thoughts on Chapter 5.  The optimizer is very good at what it does,  but no one is perfect.  There may be rare cases where the optimizer needs a hint to force a certain type of behavior.  Although this may bring immediate gains in query performance, over time, the new plan generated may cease to function better than the what the optimizer can compile on the fly.  The reason for this is data change.  As the data changes, statistics are updated and the selectivity of the data changes which causes indexes to become more or less useful.  This is why using hints is dangerous and should be used sparingly.

Things I learned

Besides using the occasional evil hint ‘WITH (NO LOCK)’, I don’t use hints.  Partly because I didn’t understand the query plans that well before reading this book and I had lots of respect for the optimizer.  Now that I understand how execution plans are generated, I can see a useful need for hints, testing.  To be able to change the behavior of the optimizer by using a Query, Join or Table hint is a powerful learning tool.  You can start from the optimizer’s auto generated plan and start poking it with hints to see how the I/O and query time changs.

If I were to start using hints, I would definitely see a need for a tracking document or metadata query to reevaluate the execution plans.  If the tables involved are static in nature, you may not need to bother with reworking the query plan hints but once a year.  On the flip side though, a monthly review may be necessary if the query is pulling data from volatile tables.

Likes

Even though controlling execution plans with hints is frowned upon, Grant did a great job going through examples of each hint and how you can use them to better understand the optimizer.  Grant may it very clear throughout the chapter to use hints at your own risk, but if you find a select operator has timed out looking for the best plan you may have an opportunity to use hints.

Dislikes

As with most chapters thus far, no complaints on the material presented.

On to chapter 6!

Doug Purnell

Virtual Book Club: SQL Server Execution Plans, Chapter 4

SQL Server Execution Plans

Everyone loves reading a good book, especially about SQL Server.  I’ve been on a training kick lately and the timing of this book club was perfect.  Denis Gobo (b|t) has experience starting a book club where he works and decided to bring it to the SQL Server community.  The goal of the club is to read a chapter each week and discuss things you learned, liked and disliked.  You can ready Denis’s original post here.  The first book chosen was written by Grant Fritchey (b|t) and is titled “SQL Server Execution Plans, Second Edition”.  You can get a free electronic copy or purchase a print copy from simple-talk.com.

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

SQL Server Execution Plans

Everyone loves reading a good book, especially about SQL Server.  I’ve been on a training kick lately and the timing of this book club was perfect.  Denis Gobo (b|t) has experience starting a book club where he works and decided to bring it to the SQL Server community.  The goal of the club is to read a chapter each week and discuss things you learned, liked and disliked.  You can ready Denis’s original post here.  The first book chosen was written by Grant Fritchey (b|t) and is titled “SQL Server Execution Plans, Second Edition”.  You can get a free electronic copy or purchase a print copy from simple-talk.com.

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

SQL Server Execution Plans

Everyone loves reading a good book, especially about SQL Server.  I’ve been on a training kick lately and the timing of this book club was perfect.  Denis Gobo (b|t) has experience starting a book club where he works and decided to bring it to the SQL Server community.  The goal of the club is to read a chapter each week and discuss things you learned, liked and disliked.  You can ready Denis’s original post here.  The first book chosen was written by Grant Fritchey (b|t) and is titled “SQL Server Execution Plans, Second Edition”.  You can get a free electronic copy or purchase a print copy from simple-talk.com.

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

SQL Server Execution Plans

Everyone loves reading a good book, especially about SQL Server.  I’ve been on a training kick lately and the timing of this book club was perfect.  Denis Gobo (b|t) has experience starting a book club where he works and decided to bring it to the SQL Server community.  The goal of the club is to read a chapter each week and discuss things you learned, liked and disliked.  You can ready Denis’s original post here.  The first book chosen was written by Grant Fritchey (b|t) and is titled “SQL Server Execution Plans, Second Edition”.  You can get a free electronic copy or purchase a print copy from simple-talk.com.

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