SQLServerPedia

As a DBA you will encounter processes, code and design decisions within your environment that require change for the better. The most common driver for such change is performance improvement. Oftentimes the changes you wish to drive will require design and implementation by someone other than yourself.

Proposing changes(improvements) to an authors existing code/process/design can be a prickly subject (See: The Politics of SQL Server Performance) and it is important that you approach such discussions effectively in order to achieve the result your desire.

"Avoid getting Sumo Slapped"

Here are some tips I have found to be helpful when considering how to provide effective feedback:

Approach with finesse

• Meet and or discuss the change directly with the author.
• Ideally discuss on a one-on-one basis, at least initially.
• Choose an appropriate time for your discussions.
  • If the author is busy troubleshooting a production item choose a more suitable time, when they will be more responsive to your approach.

Be clear and specific about what the current issue is

• What process, stored procedure, design decision is it?
• Identify any undesirable qualities
  • Slow query performance
  • High index fragmentation
  • Excessive CPU utilisation
• Touch on business level implications briefly and lightly.

Praise the good points of current work/implementations

• Show that you can see quality in the work of the author.
• Highlight what you currently like about the existing implementation.
  • Design
  • Problem solving approach
  • Feature usage
  • Anything…………..
• Perhaps something similar could be incorporated into the solution?

Put forward your proposed change

• Briefly highlight/summarise your proposed change/improvement.
• Avoid technical details and proof of concept at this stage unless they are invited specifically.

Set them up for success

• Offer to be available to provide support and assistance if desired but leave the next action in the hands of the author.

Put yourself in the position of the author

• Consider things from their perspective
• How would you wish to receive the feedback you are providing?

"I thought I told you no Cursors"

Providing great feedback in order to effectively promote change can a tricky task sometimes and incorporating these tips will assist you on your way.

Do you have any tips for providing great feedback? Feel free to share them using the comments section below.

We’ve looked at what we should do in order to provide great feedback but what about what not to do. It would be great to get some of your thoughts on this. Perhaps you can think of a time when you or a colleague received some not so well thought out feedback. What do you consider to be things to avoid when providing feedback?

Next time, How Not to Provide Feedback…….just kidding.

Be sure you don’t miss out on future posts by Subscribing to the Blog Feed. You can also receive this Blog via Email.

Like most DBA’s I’m sure you often find yourself delivering information to the business concerning the performance of your SQL Server platform but have you ever stopped to consider the wider implications of your actions?

As a SQL Server Database Administrator you are responsible for the overall health and well being of your SQL Server database servers. As part of this, one of your key responsibilities is ensuring that SQL Server is providing a sufficient level of performance.

You probably proactively manage and monitor the performance of your environment in order to validate this, perhaps through the creation and review of baseline performance metrics or with the use of third party monitoring tools.

You Are Monitoring Performance Right?

"Ensuring High Performance"

If you are not currently monitoring the performance of your environment you absolutely should be! As a DBA you have no excuse for not monitoring your SQL Server environment given that you can roll your own performance monitoring solution with very little effort. Doing so is essential to understanding both the resource demands placed on SQL Server and ensuring that it is delivering the performance required by your users.

For details on how to get started and roll your own performance monitoring solutions take a look at Brent Ozar’s excellent SQL Server Perfmon (Performance Monitor) Best Practices.

T-SQL Query Performance Monitoring

As part of your performance monitoring activity you will want to regularly review and identify the poorest performing T-SQL queries in your environment. There are a variety of different methods you can use to do this and a number of these are listed below.

How to Identify Poorly Performing Queries:

• Performance Dashboard Reports
• SQL Server DMV’s - How to identify the most costly SQL Server queries using DMV’s
• SQL Server Profiler
• Third Party Monitoring Tools – (Insert your favourite product here)

So now that you have identified the most poorly performing T-SQL queries, what next?

Providing Feedback to the Business

"Super DBA"

With your T-SQL query performance metrics and analysis in hand you’re keen to share the details of your findings with the business but before you go any further, just stop and think for a moment about what it is you are about to do.

You have in your hands a conclusive list of information that not only identifies the worst performing T-SQL queries for your database server but you also have a mountain of evidence in support of this, a clear and irrefutable case. The information you posses has the potential to make quite a few people look bad and possibly even stupid, especially if your analysis has identified some particularly poor coding practices.

So before you publicise your findings and publically declare to the entire development team that they’re a bunch of idiots and you have the data to prove it, I implore you to take heed of the number one rule in SQL Server Performance Politics…..

…..No One Likes a Smart Ass

Being the clever DBA that you are I am certain that you have an abundance of great ideas as to how to improve the performance of the queries you have identified. Whether it be through the creation of new indexes or even a complete overhaul of some of the worst T-SQL you have ever laid eyes upon.

Don’t bring attention to your own amazing work by putting down the work of others either. You will not win yourself any friends and there is no gain to be had from directly showcasing the mistakes of others. Simply put, if you are not the author of said source code then the best thing you can initially do is to just keep your mouth shut. This is done to give the author the time to digest the information you have provided.

So how do you go about effectively implementing change? Funny you should ask. Next up, How To Provide Great Feedback.

At the SQLBits conference in Great Britain last fall, Brent Ozar gave a presentation called “Using Perfmon and Profiler.”  He covered:

• Why Windows Task Manager is nothing more than a check-engine light
• His performance tuning lifecycle
• Why you need to capture baselines (and how to do it)
• Several sample tuning scenarios

You can watch this 52-minute presentation free courtesy of SQLBits:

If you liked this presentation, you can watch any of the SQLBits presentations at their web site, including:

• Designing for Simplification by Rob Farley
• Optimistic Concurrency Internals by James Rowland-Jones
• Strapped for Cache? SQL Server Memory by Christian Bolton
• When a Query Plan Goes Wrong by Simon Sabin

Subscribing or Downloading the Podcast

If you have an MP3 player or a portable video player and you want to download our videos automatically, you can subscribe to our podcast feeds here:

• MP4 (Apple) Video Feed
• WMV (Microsoft) Video Feed
• MP3 Audio-Only Feed
• Zune One-Click Subscribe for Video
• Zune One-Click Subscribe for MP3

You can also download this video to watch it later:

• MP4 (Apple) Video Download
• WMV (Microsoft) Video Download

At the SQLBits conference in Great Britain last fall, Brent Ozar gave a presentation called “T-SQL Tuning with Colin Chapman, Enzo Ferrari, and The Stig.”  He covered:

• Why Colin Chapman would check for indexes before adding new ones
• The importance of well-tested safety gear to performance tuning
• Why not monitoring your servers is like overdriving your headlights
• Just like races are lost in the pits, uptime records are lost during maintenance windows

You can watch this 41-minute presentation free courtesy of SQLBits:

If you liked this presentation, you can watch any of the SQLBits presentations at their web site, including:

• Designing for Simplification by Rob Farley
• Optimistic Concurrency Internals by James Rowland-Jones
• Strapped for Cache? SQL Server Memory by Christian Bolton
• When a Query Plan Goes Wrong by Simon Sabin

Subscribing or Downloading the Podcast

If you have an MP3 player or a portable video player and you want to download our videos automatically, you can subscribe to our podcast feeds here:

• MP4 (Apple) Video Feed
• WMV (Microsoft) Video Feed
• MP3 Audio-Only Feed
• Zune One-Click Subscribe for Video
• Zune One-Click Subscribe for MP3

You can also download this video to watch it later:

• MP4 (Apple) Video Download
• WMV (Microsoft) Video Download

Kevin Kline gave an hour-long presentation to the Nashville PASS Chapter on Query Performance Tuning.  He covered tips like NOCOUNT, SHOWPLAN, STATISTICS, and low-hanging fruit to make queries faster.

Subscribing or Downloading the Podcast

If you have an MP3 player or a portable video player and you want to download our videos automatically, you can subscribe to our podcast feeds here:

• MP4 (Apple) Video Feed
• WMV (Microsoft) Video Feed
• MP3 Audio-Only Feed
• Zune One-Click Subscribe for Video
• Zune One-Click Subscribe for MP3

You can also download this video to watch it later:

• MP4 (Apple) Video Download
• WMV (Microsoft) Video Download
• MP3 Audio-Only Download

It’s interesting to me that old and inaccurate performance tuning recommendations seem to have a life of their own.  In some ways, old performance tuning recommendations are like the Undead from some kind of cheesy, 1970’s zombie movie – no matter how many times you shoot them, they just keep coming back.

Here’s a good case in point, http://msdn2.microsoft.com/en-us/library/ms345118.aspx, a white paper discussing Performance Optimizations for the XML data type in SQL Server 2005.  The document states:

Multiple tempDB Files for Better Scalability of XML Variables and Parameters

XML variables and parameters use main memory as storage as long as their values are small. Large values, however, are backed by tempdb storage. In a multi-user scenario, if many large XML blobs occur, tempdb contention may become a bottleneck for good scalability. Creating multiple tempdb files reduces the storage contention and yields significantly better scalability. The next example illustrates how multiple tempdb files can be created.

Example: Creating Multiple tempdb Files

This example creates two additional data files for tempdb, each with an initial size of 8 MB, and two log files with an initial size of 1 MB.

Copy Code

USE TEMPDB

GO

ALTER DATABASE tempdb ADD FILE 

   (NAME = 'Tempdb_Data1', 

      FILENAME = 'C:\temp\Tempdb_Data1.MDF', SIZE = 8 MB),  

   (NAME = 'Tempdb_Data2', 

      FILENAME = 'C:\temp\Tempdb_Data2.MDF', SIZE = 8 MB) 

GO

ALTER DATABASE tempdb ADD log FILE 

   (NAME = 'Tempdb_Log1',

      FILENAME = 'C:\temp\Tempdb_Log1.LDF', SIZE = 1 MB),  

   (NAME = 'Tempdb_Log2',

      FILENAME = 'C:\temp\Tempdb_Log2.LDF', SIZE = 1 MB)

GO

These files can be removed by using the ALTER DATABASE tempdb REMOVE FILE command. For more information, see SQL Server Books Online.

There was a time when this recommendation made sense.  The intent behind multiple tempdb files prior to SQL Server 2005 was to avoid GAM contention on very high throughput scenarios.  Later, SQL Server 2005 introduced segmented GAMs even within a single tempdb file. 

This recommendation still holds for data files, especially on systems using NUMA processors, where IO should be aligned with one data file per NUMA socket.  (It also does no harm on an SMP system to organize your data files this way, hence the standardized recommendation.)

However, the recommendation fails when you get to the log portion of the equation.  Why?  It’s because data file IO is written using the proportional file algorithm where each data file has data written to it in round-robin style.  On the other hand, log files are written using the active file algorithm where LogFile1 is written first until full, then LogFile2 is written next until full, and so on…  Long story made short (too late, I know) – you get no performance gain from having multiple files in the log because all writes occur on only one file.  You can only get a performance gain from multiple files on the data portion of a database.

As an aside, Simon Sabin (http://sqlblogcasts.com/blogs/simons/) has a wonderful collection of addition myths, misunderstandings, and miscommunications.  I’m not sure if they’re put together in a single blog post, so put his blog on your watch list.

Cheers,

-Kev

P.S.  Thanks to Tony Rogerson (http://sqlblogcasts.com/blogs/tonyrogerson) and Geoff Hiten for the inspiration on this blog post, btw.

P.P.S.  I like how the MSDN article refers to SQL Server 2005 as SQL Server 9.0.  ;^)

SQLIO tests how fast your hard drives perform, but it’s not easy to use. Brent shows how to create a SQLIO batch file and analyze the results.

For more about SQLIO, check out the SQLIO tutorial wiki article.  We’ve got sample scripts you can use, and instructions on how to get the result data into a database for easier analysis.

If you have an MP3 player or a portable video player and you want to download our videos automatically, you can subscribe to our podcast feeds here:

• MP4 (Apple) Video Feed
• WMV (Microsoft) Video Feed
• MP3 Audio-Only Feed
• Zune One-Click Subscribe for Video
• Zune One-Click Subscribe for MP3

You can also download this video to watch it later:

• MP4 (Apple) Video Download
• WMV (Microsoft) Video Download
• MP3 Audio-Only Download

SQL Server’s Dynamic Management Views (DMVs) can tell you what indexes need to be added – without doing any tracing or performance monitoring. In this video tutorial, we’ll show you how.

In the podcast, we mention a few links:

• Script to find missing indexes
  
• T-SQL Script Library
• Kimberly Tripp on spring cleaning for your indexes

If you have an MP3 player or a portable video player and you want to download our videos automatically, you can subscribe to our podcast feeds here:

• MP4 (Apple) Video Feed
• WMV (Microsoft) Video Feed
• MP3 Audio-Only Feed
• Zune One-Click Subscribe for Video
• Zune One-Click Subscribe for MP3

You can also download this video to watch it later:

• MP4 (Apple) Video Download
• WMV (Microsoft) Video Download
• MP3 Audio-Only Download

SQL Server’s Dynamic Management Views (DMVs) can tell you what indexes need to be added – without doing any tracing or performance monitoring.  In this video tutorial, we’ll show you how.

In the podcast, we mention a few links:

• Script to find missing indexes
  
• T-SQL Script Library
• Kimberly Tripp on spring cleaning for your indexes

If you have an MP3 player or a portable video player and you want to download our videos automatically, you can subscribe to our podcast feeds here:

• MP4 (Apple) Video Feed
• WMV (Microsoft) Video Feed
• MP3 Audio-Only Feed
• Zune One-Click Subscribe for Video
• Zune One-Click Subscribe for MP3

You can also download this video to watch it later:

• MP4 (Apple) Video Download
• WMV (Microsoft) Video Download
• MP3 Audio-Only Download

SQL Server 2005 introduced a new way to design wide indexes for improved performance and less page splits. Sounds complex, but it’s easy, and it can make a real difference in performance. In this video tutorial, we’ll show you what it’s all about.

In the podcast, we mention a few links:

• Script to identify unused indexes
• T-SQL Script Library
• Kimberly Tripp on spring cleaning for your indexes

If you have an MP3 player or a portable video player and you want to download our videos automatically, you can subscribe to our podcast feeds here:

• MP4 (Apple) Video Feed
• WMV (Microsoft) Video Feed
• MP3 Audio-Only Feed
• Zune One-Click Subscribe for Video
• Zune One-Click Subscribe for MP3

You can also download this video to watch it later:

• MP4 (Apple) Video Download
• WMV (Microsoft) Video Download
• MP3 Audio-Only Download