Working on my Presentations for SQL Connections Fall 2012

Over the last week I have pitched 8 presentations for SQL Connections in Vegas at the end of October 2012.  Hoping that 3 of my presentations get accepted.  Here is the list so far.

  • Using the New Analytic Functions in SQL Server 2012
  • Exploring the TSQL Enhancements in SQL Server 2012
  • Unleashing Common Table Expressions in SQL Server
  • Using SSRS 2012 Reports to Analyze SQL Server Health
  • Reusable BI Components in SSRS Reports
  • New DMV’s in SQL Server 2012 and 2008R2
  • Mitigating Risk and Minimizing Downtime with SQL Server Upgrades
  • Using the Under-appreciated OUTPUT Clause


It should be a good time, Vegas is always interesting at Halloween.


Risk Assessment Matrix for SQL Server Upgrades

A couple years ago in my Incident Safety Officer class with the fire department we studied risk assessment for any incident that the fire department may respond to.  Since that time I have used a similar risk assessment matrix for SQL Server upgrades, and other big SQL Server maintenance tasks.

The risk assessment matrix as it is used by the Incident Safety Officer with the fire department may make the difference in saving lives, or preventing accidents.  The risk assessment matrix can be used by the database administrator to reduce risk and to reduce the risk of being fired if something dos go wrong.

Here is how it works, start with the grid as shown here:

The left axis has probability from low to high, and the top axis has impact from low to high.

Then run through the possible things that could go wrong with the task that you are working on, and determine where they fit in the matrix.

Then focus on mitigating the risk with the things that fall into the high risk block.  Keep in mind that the High Probility, High Impact = High Risk items are the items that will get you fired, and the Medium Risk items are the things that might get you fired.

Give it a try next time you are doing a database upgrade, or some type of system maintenance.


How big is your procedure cache?

Part of SQL Server running queries is that once a query is analyzed, parsed and compiled, that compiled plan is kept in memory so that it can be quickly re-used in the future. If you are using proper parameterized queries, most of your queries will get kept around and re-used saving lots of CPU and memory on SQL Server. If you don’t use parameterized queries, you may be filling up your SQL Server memory with cached plans that may never get used again.  So take a look at how much memory is being taken up by your SQL Plans.


-- How big is the procedure cache

SELECT name,

SUM(single_pages_kb + multi_pages_kb) / 1024.0 mbused

FROM   sys.dm_os_memory_clerks

WHERE  name = ‘SQL PLans’

GROUP  BY name;


One thing to keep in mind with the procedure cache is that it is not the size that matters, but what you can do with it.

If the amount of memory being used in plans is greater that you expected, or greater than you like, you can use the SQL Server health reports to track down some of the culprits.

For more info, come see my presentation at SQL Saturday on March 17, 2012 in Vancouver BC.

SSRS – Using a subreport for navigation

As I have been working on my SQL Server Health Reports for my presentation at SQL Saturday 114 in Vancouver on March 17th, I have changed the way that I do things several times.  After attending a SSRS class “SSRS Beyond the Basics ” at SQL Saturday 108 in Redmond presented by @sqlbelle I learned a few tricks to help make this happen.

In a nutshell the SQL Server Health Reports project is a set of 11 reports to monitor some of the problems that creep into SQL Servers over time.  They all worked fine independent of each other, but I wanted them all to function as one application.  To do this I started building links from one report to another, then discovered that for each report that I add, I had to go back and edit all the previous reports to get them to link.  So here is the solution.

First I removed the headers from the old reports.  I discovered that you can’t put a subreport into a report header, so I just removed all the headers from the existing reports.

Then I set up a new report called Header.rdl shown here.  At first it looks a bit odd with the blue “Change Database: fields overlapping other fields, I will get to that soon.

Then I added 4 parameters to the header.rdl.

Database Name Parameter

The first parameter called Database Name is used and passed from report to report to keep the currently selected database around so that you don’t have to continually select the database name every time you switch reports.

ReportName Parameter

Used to show the large font at the top of the report with the name of that report.

ReportPath Parameter

The path to use to link back to the current report after changing the database.

Server Parameter

The name of the SQL Server that we are connecting to.  Currently the process only supports localhost, but I am working on expanding this.



Then in the header I implemented the ability to switch databases, which is very useful when browsing database metrics.  Next to the “Database: master” there is a + which indicates that clicking it will expand something.

Then I added a section that lists all of the databases on the currently selected server.  Which is initially not visible, but when you click the + it is shown, and it is shown over the current database name.

All of the database names are shown as links.  Clicking one of those links changes the @DatabaseName parameter and links back to the current report @ReportPath with the required parameters.  This is all accomplished on the action dialog on the settings page for each item in the list.

So clicking the link changes the database used in the report.


Next adding in the buttons with the action to switch between different reports.  The buttons are just text boxes colored to sort of look like buttons.  I added several gray buttons across the top of the report (yes I know that they need some UI work, gray buttons aren’t very interesting).

One button for each report in the project, with an action linking to that report passing along the required parameters.


And the action for each button looks like this for the DB Sizes button which links to the Database sizes report:


Then I added the header into each of the reports, and the the parameter for it.

From there it was just a matter of testing the linking between each of the reports, fixing any typo’s or other parameter errors, and getting it running.

Overall it took about an hour to set it all up and get it tested, but once it is set up it is now much easier to add a new report with a new link. To add this into a new report it takes about 1 minute to insert the sub-report and set the parameters for it.

Next step for another day, setting up a new SSRS template with a standard RDL for this project to save time for any new report that I add.

If you want to see it in action, or see the code behind it, download my SQL Server Health Reports project and take a look.  I hope you find this useful in your SSRS endeavors.


SSRS Editor Tip of the Day… CTRL + Arrows

I learned this from one of my co-workers.  A quick tip for formatting SSRS reports.

When you are in the SSRS report editor, and you want to move the currently selected object (or objects), you can move them with the arrow keys.  Most people who have used SSRS know that one, but did you know that if you use the CTRL key with the arrows that report objects move smaller jumps.  This helps when you are doing that final layout of a report to get everything positioned just right.

KeePass – for security and time savings

After a recent tour at a couple companies who are using the LEAN process, I have decided to take on LEAN in my everyday work.

This is the first in what I think will be many videos on the Lean Office topic.

Click the link below to watch the video.



For more info on KeePass see the detailed article with instructions on using KeePass.


Follow hash tag #leanOffice on Twitter