Wednesday, December 30, 2009

Goals for 2010

I have been thinking a lot about what where I wanted to focus next year and have been writing things down as they came to me. I have a lot of personal goals I want to accomplish next year like reading more non-technical books, but I am going to keep this list related to SQL Server. I did a lot of things last year that I deemed personal accomplishment like writing my first book, presenting at my first SQL Server event, and even becoming an MVP. This year I am going to focus more on hard-core learning and continue branching out and trying new things. I tend to add and remove things from my list throughout the year as they become relevant or irrelevant, but here is what I am starting out with.

  • Give an Online presentation – Every presentation I have done so far has been in front of a group of people. I want to venture out and experience the world of virtual presentations.
  • Present at one of the national SQL Server events – I started to put a goal here to submit a presentation for a national event, but how hard is it really to submit something. I think the ultimate goal would be to actually get accepted and present. My first thought is that I had no control over whether I was accepted or not. Then I realized I was wrong. I know there is certain criteria, and I can do my best to meet it.
  • Present at a minimum of four local events – SQLSaturday, User Groups, Code Camps, etc. 
  • Record a series of videos for – I think recording videos would be another good way to sharpen my presentation skills.
  • Read all the whitepapers from the MCM Pre-Reading List - There are several links here that point to blog categories. I want to knock out all of the whitepapers first and if I have time, I will circle back around to the blogs.
  • Get SQL 2008 Certifications
    • MCITP: Database Administrator
    • MCITP: Database Developer

Tuesday, December 15, 2009

Yes, Table Variables and Temp Tables both use the tempdb

Pinal Dave recently wrote a blog post called SQL SERVER – Difference TempTable and Table Variable – TempTable in Memory a Myth which basically said that he often hears that Table Variables are stored in memory and Temp Tables are stored in the tempdb. He went on to show an example by loading rows into both and showing the page count. He also asked if anyone knew of another method to prove that both Temp Table and TableVariable are created in tempdb.

I found this post interesting, because it seems like I have answered that same questions at least a few times in the last couple of weeks. So here is my take on it. The best way for me to prove that they both reside in the tempdb (other than pointing to this kb article) is just to look for it there.

The Code

Let’s take a look at the code we can use to look for the objects in the tempdb.

  1. Create a Table Variable and immediately insert the current time.
  2. Wait ten seconds and then create a Temp Table.
  3. Select the values inserted into the tables.
  4. Look for the objects in the tempdb.
  5. Cleanup by dropping the Temp Table.

WAITFOR DELAY '00:00:10'


SELECT DT AS TableVariableTime FROM @TableVariable
SELECT DT AS TempTableTime FROM #TempTable

SELECT * FROM tempdb.sys.objects
WHERE type = 'U'


You can see the output of the preceding code in the following image.


As you can see the TableVariableTime corresponds to the object created in the tempdb named #73BA3083, which is just hexadecimal of its object_id 1941581955, and the TempTableTime corresponds to the #TempTable object created ten seconds later. For space reasons, I didn’t include the entire object name for the Temp Table, but each object that gets created will have unique name as well.

Just for Fun

Now that I know I have objects in the tempdb, what do you think will happen if I try to manipulate them by the name in the tempdb instead of the names we gave them? Well, one of the first things I learned when I started working with SQL is that if I know the name of a table I should be able to insert some data. I can get the name of the table that exists while I am running the code and execute the INSERT statement using dynamic SQL by using the following code.



SELECT @Name = name 
FROM tempdb.sys.objects
WHERE type = 'U' AND create_date > DATEADD(s,-2, GETDATE()) 

SET @SQL = 'INSERT INTO tempdb.dbo.[' + @Name + '] DEFAULT VALUES'
PRINT 'Table Name: ' + @Name
PRINT 'Query: ' + @SQL


Ehh. Wrong Answer.


You can see in the preceding image that SQL Server ignores the request and generates an error. So, while the objects exist, and you even know the name, the only way to interact with them is through the front end.

Sunday, December 13, 2009

What is your Biggest Weakness?

David Stein (BlogTwitter) started a chain post the other day titled “What is your Biggest Weakness?” The Classic Interview Question. He tagged Brent Ozar (BlogTwitter) who tagged Thomas LaRock (BlogTwitter) who then tagged me. So, here goes…

I think one of the hardest things I have had to overcome since becoming a DBA is that I have always been the type of person to just “wing it” or just play things out and see what happens. I have never been the type of person to book a room in advance when traveling, learn a song note for note when playing music, follow a recipe when cooking, or follow directions when assembling a product. Besides, most of these instructions are common sense anyway.

Instructions by ppz.
Do not plug the coffee machine into the baby!

Even when I was a developer, this mentality worked out great. I knew what the end result had to be, and as long as I met all the requirements on or before the deadline, everyone was happy. Nobody really cared what happened in between. Meaning, I could just do my favorite thing and “wing it”.  

While this philosophy has gotten me through most of my life without a hitch, I found this not to be the case when I changed my career to a DBA. All of a sudden I was working on highly critical production systems and the smallest mistake could be disastrous. It took me a while to come around, but I soon started creating checklists, back out plans, and even scripts with step-by-step instructions.

I still don’t really like to have a detailed plan for most things, but I really have to consider the possible outcomes now before I throw caution to the wind. If I have an extra washer left over after putting together a grill, I’ll probably be okay. On the other hand, if I forget to backup a database and I need to rollback an upgrade, I may be sitting in the basement the next day with a can of bug spray looking for my red stapler and wondering if anyone’s seen my paycheck.

I’m gonna tag some Florida folks Kendal Van Dyke (BlogTwitter)  and Jack Corbett (BlogTwitter) to see what they have to say.

Thursday, December 10, 2009

Top 10 Policy-Based Management Links


I have been doing a lot of research on Policy-Based Management lately, so I thought I would publish a top 10 list on the PASS website. It hasn’t showed up there yet, so I thought I would post it here as well. There are a lot of other good links, so if I’ve missed your favorite, you can add it in the comments of this post.

Top 10 Policy-Based Management Links

1. Using the New Policy-Based Management Framework in SQL Server 2008

2. MSDN SQL Server Policy-Based Management Blog

3. SQL Server 2008 Policy-Based Management

4. Enterprise Policy Management Framework with SQL Server 2008

5. Evaluating Policies On Demand Through PowerShell

6. SQL Server 2008 Compliance Guide

7. Governing Your Enterprise with Policy-Based Management

8. Programming Policy-Based Management with SMO (6 Part Series)

9. Deploying SQL Server 2008 Based on Payment Card Industry Data Security Standards

10. Defining Complex Server "Health" Policies in SQL 2008

Lone Heading and Subheadings

I’ve been doing a lot of writing this year, and the editor for Apress that I have been working with published an article today that I thought was worth sharing.

One of the mistakes that I made when I started writing was creating a level 1 heading and then immediately creating a level 2 heading without introducing it. That was an easy fix, but he goes a step further and talks about why you shouldn’t have a single subheading. It is definitely worth reading if you are currently doing or plan on doing any writing.

Friday, October 23, 2009

It’s Bingo Time!

1761310512_bf275545d1[1]We're only a few days away from the PASS Summit now, and you need to make sure to print out some bingo cards so you can have a little extra fun, have a chance to win some prizes, and meet a lot of new people.
A few weeks ago the idea started going around about a Twitter bingo game and thanks to some hard work from a few people in the SQL Community, it looks like the game’s on. You can read the official rules on the SQLServerPedia website, but here are a few things you should keep in mind.
  1. Make sure to print out three cards (one for each day).
  2. Make sure to refresh the page between printing to get a new card.
  3. Make sure to print a copy of the rules, because they change each day.
When you track down a person on the square, they will give you a code word you need to write on their space. I am one of the people you need to track down so I will be tweeting my whereabouts throughout the day using the official twitter bingo hash tag #sqlbingo. You don’t have to use twitter to be eligible, but it will give you an advantage when trying to find the people on your card.
You can find out who all has signed up to be a square as well as some other good information on Stuart Ainsworth’s blog. You may even have a slight advantage if you start following everyone ahead of time.

New Whitepaper on Consolidation Using SQL Server 2008

I just finished reading a new whitepaper by Allan Hirt on Consolidation Using SQL Server 2008. It's 60 pages, so it is long enough to include some good detail, but not so long that you can't sit down and read it all at once. I have a special interest in consolidation and I would recommend that anyone starting or already going through a consolidation project take a few minutes to read this whitepaper.
A few of the highlights from the whitepaper that I really liked are...
  • A good sample list of the guiding principals that will govern the consolidation project
  • What kind of information you need to gather about servers during a consolidation project as well as why you need to collect it (even a detailed list of performance counters)
  • The different types of consolidation along with the considerations you need to be aware of for each one
The whitepaper is full of other great advice as well such as the options you have for moving your databases and considerations for administering a consolidated environment once the process is complete.

Wednesday, October 21, 2009

Birds Of A Feather Lunch At PASS

PASS has organized a Birds of a Feather lunch for the Summit on Tuesday November 3rd and I was given the opportunity to host a table. Each table is going to be talking about a specific topic during lunch, so I suggest you find something that interests you and try to find a seat at that particular table. I am going to be discussing Central Management Servers in SQL Server 2008. This is not going to be a presentation or lecture; it’s just going to be a bunch of people sitting at a table eating lunch and talking about SQL.

Apress also contacted me and wanted to know if I could use any books to give away at PASS, so I will be handing out a few copies of Pro SQL Server 2008 Administration and Pro SQL Server 2008 Mirroring during lunch. I have also heard that some other authors may be giving away a few books as well. Here is the list of topics that will be discussed.

32Bit/64Bit - What's the difference?
Agile Development in BI
App Dev Smackdown
Change Data Capture
Chow down on corruption
Common T-SQL Programming Mistakes
Data Mining
Data Warehousing Concepts
Database Mirroring
DBA Best Practices
DBA Dashboards
DBA/Developer Team Development
Don't be afraid of XML!
Dynamic Management Views
Erland on Error Handling and Dynamic SQL
Full Text Search and File Stream
Gemini's impact on SSAS/Data Warehouse projects
Getting Started with T-SQL
Got Query Plan Reuse? Is it good?
Failover Clustering
High Availability with Log Shipping
High Availability in Virtualized Environments
High Availability in Virtualized Environments
Is Business Intelligence an Oxymoron?
IT Consulting - Notes from the field
Key Performance Indicators -Monitoring Your Business
Kimberly Tripp Covers Indexes (and lunch)
Location Based BI With SQL Server 2008
Moving from Technologist to Manager
Multi Server Management
Multiply Yourself with Central Management Servers
Performance Analysis/Tuning
Performance Tuning Analysis Services
Policy-Based Management
Powershell in SQL Server
What Is Professional Development for a SQL Professional?
Relational Database Design
Social Networking: Geek DBA to Butterfly
SQL Azure
SQL Server Compact - The little database that could
SQL Server Consolidation
SQL Server Reporting Services
SQL Server Reporting Services on Analysis Services
SSAS Calculation Measure Groups at Different Grains
SSIS Scripts & Custom Objects
Starting up your own MicroISV business
Storage Best Practices
Table Partitioning
Thinking in MDX
Troubleshooting SSIS (even the oddball issues)
Understanding Execution Plans
Virtualization (cohosted)
John Paul
Virtualization (cohosted)
Visual Studio Database edition & TFS: How are you using it?
Why and How to Participate in the SQL Server Community

I have found myself wanting to be at multiple places at once when planning my itinerary for the PASS Summit, and this is another one of those times. I think this is great idea and Mike Walsh (even though he has said multiple times that this was not his idea) deserves a special thanks for helping organize this.

Tuesday, October 6, 2009

Take A Minute To Help The PASS Virtulization Virtual Chapter


The PASS Virtualization Virtual Chapter is holding a breakfast session  at the PASS Summit to answer all of your virtualization questions, but first we would like you to answer a few of ours. Please take a few minutes to fill out a survey with the following questions.

  • Have you virtualized dev, production, DR, large database servers?
  • If you’ve virtualized SQL Server, what’s been the biggest benefit?
  • If you haven’t, what’s the biggest barrier?
  • What information do you need to confidently virtualize SQL Server?
  • What significant problems do virtualization vendors still have to solve?
  • Have you got any databases you’d like to virtualize, but the 3rd-party-vendor won’t support it?
  • What webcast topics would you like to see covered?

In case you missed the link to the survey above, here is a really long hyperlink for you.

After you fill out the survey, make sure to go sign up for the breakfast session if you are going to the the PASS Summit and want to learn more about virtualization.

Friday, September 25, 2009

Query To Make Sure Data Purity Checks Will Be Performed

When you upgrade a database from 2000 to 2005 or 2008 one of the things you need to do is run the DBCC CHECKDB command with the DATA_PURITY option at least one time to set a flag in the boot page of the database and from that point forward, every DBCC CHECKDB operation will perform the data purity checks.
I have been looking for a way to programmatically check to see if the flag was set and never really found anything. The other day, I read a blog post by Paul Randal on how to tell if the flag was set.
CHECKDB From Every Angle: How to tell if data purity checks will be run?
I found this very interesting, because I have been wondering about what flag this was for a while. However, this was still too manual for me to do. It’s just easier to run the command against all of your databases if you are not sure if the flag is set.
Then I read Sankar Reddy's blog post today, and then it clicked. I can use the same logic to check for the data purity flag.
Database Internal version; Create Version and Current Version
Note: If you read the preceding blog posts, the following code will make more sense.
I created a query based on the logic in Paul’s blog and the code in Sankar’s blog to come up with the following script.


      ParentObject VARCHAR(255),
[Object] VARCHAR(255),
Field VARCHAR(255),
[Value] VARCHAR(255

     DatabaseName VARCHAR(255),
     ParentObject VARCHAR(255),
[Object] VARCHAR(255),
Field VARCHAR(255),
[Value] VARCHAR(255

EXEC master.dbo.sp_MSFOREACHDB 

WHERE Field 'dbi_DBCCFlags' AND 
      Value AND
      DatabaseName NOT IN ('master','model'


Next, I took a backup of a 2000 database and restored it to my 2008 instance just to make sure I had a database that fit the criteria. After running the script, you see the following results.
As you can see, I have several databases on my 2008 instance and the only one without the data purity flag set is the CMS database I restored from the 2000 instance. I thought this was really cool and maybe a few of you will find this useful as well.

Monday, September 21, 2009

A Simple Active/Passive Configuration Change To Reduce Downtime

Okay. 1472549010_d64a719594 It’s not what you think. By “Active/Passive”, I am talking about voice, and by “Downtime” I am talking about sleeping.

The Problem

When I started writing for Apress, one of the editors told me I needed to watch out for writing in the passive voice. He said that writing in the passive voice tends to put readers to sleep. My first thought was… “Great. I’m not an English Major. Now I have to worry about writing all this material and whether or not I am using the passive voice. Oh yeah, and how am I even going to know if I am writing in the passive voice anyway?” I am not going to get into the difference between the active and passive voice, but there is a good article here if you are interested.

The Solution

Now it’s time to get into the “Configuration Change” part of the title. Luckily, there is a setting in Word you can change that will put that nice little squiggly green line under any text that is written (oops that’s passive) you write in the passive voice.

Here is how you make the change in Word 2007.

Here is how you make the change in earlier versions.

Once you make the change, you can right-click on the underlined text and get a nice little suggestion just like you would with misspelled words.


In the above picture you can see that it recommended a completely new sentence for the first sentence. The phrase “has been known” makes the second sentence passive and you can rewrite the second sentence with the third sentence to remove the error.

I know this isn’t necessarily about SQL, but it is something that has helped me out a lot this year and wanted to share it.

Friday, September 11, 2009

Upcoming Speaking Engagements

I did a presentation at couple of SQLSaturdays (one in Birmingham and one in Pensacola) called SQL Server Preproduction Tasks and quickly found out that there was too much information for me to cover in a single session. After thinking a little more about the topic, I figured many times servers are not performing well because they were either not configured properly or they have not been properly maintained. That being said, I broke the presentation into two sessions; SQL Server Configuration and Automating Routine Maintenance. I plan to start presenting them as a two-part series.

I will be presenting Automating Routine Maintenance at the Columbus GA SQL Server Users Group on September 23 and at SQLSaturday #21 (which looks like it is going to be awesome btw) on October 17. I know everyone has there own way of doing things, but I would like to cover as much of the basics as I can and let people adjust based on their own judgment. If anyone wants to preview the scripts or the presentation, just ping me and I will send them to you.

Thursday, September 10, 2009

Be Careful When Uninstalling SQL 2008 R2 CTP

When SQL 2008 R2 CTP came out, I immediately downloaded it to try out the new features. I already had an instance of SQL 2005 and SQL 2008 running on the same machine. When you install SQL 2008 R2 it overwrites the management tools for SQL 2008.
A few weeks later I uninstalled SQL 2008 R2 and went to install the SQL 2008 management tools again. Every time I tried to run the install I encountered the following errors.
Unable to get installer types in the C:\Program Files\Microsoft SQL Server\100\Tools\Binn\Microsoft.SqlServer.Management.PSSnapins.dll assembly.
Followed by…
Could not load file or assembly 'Microsoft.SqlServer.BatchParser, Version=
I tried uninstalling all SQL components and anything to do with PowerShell with no success. I chalked it up to something specific to my system until I saw Mladen Prajdić (Blog Twitter) post something on Twitter the other day having the same issue.
I’m not sure if anyone else is having this issue, or if it just hasn’t surfaced very much yet. Here is a post on the SQL Forums with more details. To date, Mladen or myself have not been able to overcome this issue. I may have to reformat my laptop to get SQL 2008 installed again unless someone comes up with a resolution for this soon.

Wednesday, September 2, 2009

Finally Accomplished My First Goal Of The Year


After a little luck and a few months with very little sleep, I finally got a hard copy of my first book today. I was thinking about goals toward the end of last year and one of the things on the list was to get published. I sent an email to Apress on 12/1/2008 with a couple of book proposals and they responded the next day. One of the editors said “I did google you a bit, and I found your articles on SQL Server Central. In particular, your profanity piece caught my eye:” If I hadn’t written a few articles for SQLServerCentral, I may have never gotten the opportunity to write this book (Thanks Steve). He said my proposals would conflict with some books they had in the works, but they were looking for someone to write a “Pro SQL Server 2008 Administration” book.

What I originally had in mind was writing a chapter or two for a book; not a whole book. That being the case, I reached out to one of my old co-workers for help and he agreed to help me out. The editor sent us all of the official paperwork and by mid December we were good to go. There was a little bit of a learning curve at first, (writing style, word templates, proper configuration for images, etc.), but once we got going it started to get a little easier.

This was one of the hardest goals that I set for myself and was actually able to accomplish. Basically, if you want to do something, write it down as a goal and then do it. This time last year, I never though I would have a book published (I always hated writing in school btw) and now I am finishing up my second one and doing a little tech reviewing on top of that. Now I guess I can move on to some of the other goals I had. Where is that list anyway…

Thursday, August 20, 2009

Ramping Up The Reading

Catching up on some reading by Jason's Travel Photography.I haven’t gotten in nearly as much reading in this year as I would have liked. I have spent a lot of time this year writing a couple of books for Apress (Pro SQL Server 2008 Administration and Pro SQL Server 2008 Mirroring). However, I am completely finished with the Administration book and I’m in the final editing phases of the Mirroring book, so I should be able to buckle down and get some extra reading in the last few months of the year.

I did recently finish tech reviewing Beginning T-SQL 2008 by Kathi Kellenberger, so I’m going to count that one. I would definitely recommend this book to anyone wanting to get up to speed with T-SQL fast. She starts out by installing the Express Edition of SQL Server and going over the basics, and quickly builds on each topic with every chapter. I really like the way she has placed exercises throughout the chapters allowing the reader to test what they have learned. The way this book is laid out would be very useful as a training guide as well. She not only covers how write T-SQL, she explains why you would use certain techniques over others also taking performance into consideration throughout the book.

I also got Grant Fritchey’s book SQL Server 2008 Query Performance Tuning Distilled a few months back and finally finished it today. I strongly believe every Production DBA needs to supplement their skills with a performance tuning book. You can’t do performance tuning justice with just one chapter in a general administration book. I even referenced this book in one of my chapters. I also didn’t focus on performance tuning; I focused on managing query performance (i.e. plan guides and resource governor) because I knew there was no way to cover everything in just one chapter. Grant’s book covers about everything you need to know to find performance problems and then make them go away. I especially liked the Index Analysis chapter and the chapter on Bookmark Lookups. My eyes generally glaze over when I start reading about indexes. For some reason, if I am having trouble sleeping, I just need to start reading about indexes. However, I found it very interesting the way he explained the differences between clustered and nonclusterd indexes and even gave good advice on when each one works best. Definitely a good read.

I am currently reading SQL Server 2008 Internals and The SQL Server Tacklebox. Hopefully I can get through those pretty fast so I can get around to the following books…

Professional SQL Server 2008 Internals and Troubleshooting

Microsoft SQL Server 2008 Reporting Services Step by Step

I also want to read something on System Administration/Engineering and Networking, but I’m not sure what yet. If anyone has any good recommendations from a DBA perspective let me know. Maybe I can get everything in by the end of the year.

Friday, July 31, 2009

SQL Server 2008 Light PowerPoint Template

Mladen Prajdić created a SQL Server 2008 Dark PowerPoint template that looks really good. I decided to create a light version after I downloaded a presentation from Jorge Segarra and we both used Mladen’s template. I am by no means a graphic artist, but here is what I came up with.

You can download a copy here if you like.

Tuesday, July 28, 2009

Columbus GA SQL Server Users Group (7/30/2009)

Here is the info for the next meeting. This is actually the first time I will be presenting there. Make sure to stop by and check it out.

Ken Simmons will cover Multi-Server Administration by taking advantage of two new features in SQL Server 2008: Policy-Based Management and Central Management Servers. You will learn how to manually create a policy, export current state as policy, and import predefined policies. Finally, you will see how to use a Central Management Server to evaluate policies across your entire environment (even if all of your servers are not running SQL Server 2008).

July 30, From 4:30 - 6:30
At the Columbus Public Library.

4:30 P.M.-4:45 P.M.  Announcements and business
4:45 P.M.-5:45 P.M.  Main Presentation
5:45 P.M.-6:00 P.M.  Question-and-answer period
6:00 P.M.-6:30 P.M.  Wrap up, Informal meetings

DLL Hell…

DLL hell by reegmoJust as I thought I could remove the phrase “DLL Hell” from my vocabulary, I had a nasty DLL issue today with SQL Server. After installing SP3 for SQL Server 2005 over the weekend, users started complaining about jobs failing today. After further investigation, it was only jobs that were running SSIS packages. After looking at a few packages it seemed that only the Data Flow task was having issues. You could not even create a new Integration Services project and drag the Data Flow task over without getting the following error.

"Failed to create the task.

Additional information:

The designer could not be initialized. (Microsoft.DataTransformationServices.Design)"

The closest thing I could find to the issue I was having was this thread on msdn ( After trying to register the dtspipeline.dll I received yet another error message “Invalid access to memory location”.  At this point I wanted to just reinstall Integration Services and see if that would fix the problem, but this was on a production server and I wanted to make sure that I didn’t impact any of the users or interrupt any of the processes that were still working. I renamed the bad dtspipeline.dll and copied a new one with the same version from a machine that was working and registered it using regsvr32. The DLL registered successfully and the jobs started working again.

I wanted to document this because I could not find a lot of cases of this issue logged online. I also could not find any resolutions other than trying to reinstall Integration Services. Hopefully this may save someone else some time in the future.

Saturday, June 20, 2009

Columbus GA SQL Server Users Group Meeting

Here is the info for the next meeting.  If you are in the area feel free to attend.

Whitney Weaver, from Magenic Technologies will be implementing the new SQL Server 2008 feature Change Data Capture (CDC) as a vehicle for tracking changes made in the instance. The session will cover the implementation of CDC, the pain points removed by the feature,  and the process of retrieving captured data.

June 23, From 4:00 - 7:00
At the Columbus Public Library.

4:00 P.M.-4:15 P.M.  Registration, networking
4:15 P.M.-4:30 P.M.  Announcements and business
4:30 P.M.-5:30 P.M.  Main Presentation
5:30 P.M.-6:00 P.M.  Question-and-answer period
6:00 P.M.-7:00 P.M.  Informal meetings, raffle drawings, etc.

Speaker Bio:
Whitney Weaver is a Principal Consultant with Magenic Technologies.  He focuses on data technologies within the Microsoft product line.  Over the past 10 years he has implemented SQL Server solutions for a number of industries including Energy Management, Financial Services, Health Insurance, Manufacturing, and State Government.  Whitney regularly speaks at user groups and other developer events.

Friday, June 12, 2009

Using Hash Values to Find Similar Queries

There were a lot of capabilities added in SQL Server 2005 with the introduction of Dynamic Management Views and Functions, but there were a couple of columns added to the sys.dm_exec_query_stats and sys.dm_exec_requests DMV's that are going to make life a lot easier when trying to indentify queries for tuning purposes.

You can now use the query_plan_hash and the query_hash columns to group queries based on similar logic and execution plans. In other words, you can find those small queries that run several times that wouldn't normally be on your radar. For example, let's say you run the following queries. Be carful not to free your procedure cache on a production machine. I am doing this for demo purposes only.

USE AdventureWorks
SELECT * FROM Person.Address WHERE Postalcode = '98011'
SELECT * FROM Person.Address WHERE Postalcode ='98011'
SELECT * FROM Person.Address WHERE Postalcode = '98011'
SELECT * FROM Person.Address WHERE Postalcode = '98011';
SELECT * FROM Person.Address WHERE Postalcode = 'K4B 1T7'
SELECT *  FROM Person.Address WHERE Postalcode = 'K4B 1T7'

Now if you take the query from Books Online for 2005 for Obtaining information about the top five queries by average CPU time and modify it a little to add a GROUP BY so you can also see the number times a query has executed and get the Total CPU Time, you can see that only two of the queries were similar enough to be grouped together.

USE AdventureWorks

Sum(total_worker_time / execution_count) AS [Total CPU Time],
               Sum(execution_count)AS executioncount,
               Substring(st.TEXT,(qs.statement_start_offset / 2) + 1,
                         ((CASE qs.statement_end_offset
                             WHEN -1
                             THEN Datalength(st.TEXT)
                             ELSE qs.statement_end_offset
                           END - qs.statement_start_offset) / 2) + 1) AS statementtext
FROM     sys.dm_exec_query_stats AS qs
         CROSS APPLY sys.Dm_exec_sql_text(qs.sql_handle) AS st
GROUP BY Substring(st.TEXT,(qs.statement_start_offset / 2) + 1,
                   ((CASE qs.statement_end_offset
                       WHEN -1
                       THEN Datalength(st.TEXT)
                       ELSE qs.statement_end_offset
                     END - qs.statement_start_offset) / 2) + 1)
ORDER BY Sum(total_worker_time / execution_count) DESC;


Now here's the cool part. In SQL Server 2008, you can use the query_hash to aggregate all of the queries and get the true execution counts and CPU measures.

Sum(query_stats.total_worker_time/query_stats.execution_count) AS [Total CPU Time], 
              Sum(execution_count)AS executioncount, 
              Min(query_stats.statement_text) AS statementtext
FROM     (SELECT qs.*,
                 Substring(st.TEXT,(qs.statement_start_offset / 2) + 1,
                           ((CASE statement_end_offset
                               WHEN -1
                               THEN Datalength(st.TEXT)
                               ELSE qs.statement_end_offset
                             END - qs.statement_start_offset) / 2) + 1) AS statement_text
          FROM   sys.dm_exec_query_stats AS qs
                 CROSS APPLY sys.Dm_exec_sql_text(qs.sql_handle) AS st) AS query_stats
GROUP BY query_stats.query_hash


Awesome. Yet another reason to upgrade to 2008. The query_plan_hash works the same except the hash value is based off the execution plan, so you can find queries that may not produce the same query_hash but will produce the same query_plan_hash.

The Coconut Initiative

Okay, I guess it's my turn. I got tagged by Tim Mitchell on a blog post started by Tim Ford called "Give Me A Coconut and Six Months" Here is the scenario... orientation_film_button

"So You're On A Deserted Island With WiFi and you're still on the clock at work. Okay, so not a very good situational exercise here, but let's roll with it; we'll call it a virtual deserted island. Perhaps what I should simply ask is if you had a month without any walk-up work, no projects due, no performance issues that require you to devote time from anything other than a wishlist of items you've been wanting to get accomplished at work but keep getting pulled away from I ask this question: what would be the top items that would get your attention?"

I thought this was an interesting question, so I have been following the responses trying to figure out what I would do. This is kind of a hard question for me because early last year I decided to put myself on this "virtual deserted island" at least once a day. Unfortunately most of the time I'm on the island it's between 9:00 p.m. and 1:00 a.m., so I guess one of the first things I could do is catch up on some sleep. I would also like to read more. I read a lot of blog posts and articles on the web, but I never can seem to get through all of the books on my list.

From a work perspective, I always try to set some time aside each week to research the new features in SQL Server 2008 or even some of the features that have been around for a while that I have never used. If there were no distractions, I could spend more time getting some hands on experience with some of these features. I have been trying to focus on performance tuning recently, so that is where I would probably spend most of my time. I knew that this was not one of my strong areas, so I made it a goal to improve my performance tuning skills this year. I don't want to just be able to tune a query; I want to know why a query is acting a certain way and why what I did made it act differently. Maybe if I was on this virtual island I could finally get finished with Grant's book.

So now, I'm going to tag Rodney Landrum because I was just bugging him in Pensacola about writing more blog posts. Sorry Rodney, you can't break the chain or you will have seven years of bad hard drives, or something like that...

I am also going to tag Sylvester Carstarphen because he just started a blog and I can't think of a better way for him to get started than getting tagged right off the bat.

Wednesday, June 10, 2009

The Best Thing I Learned at PASS

PASS is having a "Best Thing I Learned at PASS" contest and first prize is free entry into the PASS Summit or a four-night hotel stay during the conference. A total of ten prizes are available and this is also a great opportunity to spread the word about PASS. If you have been to a previous PASS conference and want to attend again this year, this is an opportunity to good to pass (no pun intended, at least I don't think it was, ok maybe a little).

I attended my first PASS conference in 2007 in Denver and I didn't know exactly what to expect. I signed up for the two pre-sessions prior to the conference and was able to sit through a full day training session with Itzik Ben-Gan the first day and Kalen Delaney on the second. Needles to say, I learned more about T-SQL and SQL Internals in two days than I thought was humanly possible. Just when I thought my brain couldn't hold anymore, I went to several sessions the next three days listening to some of the best minds in the business speak about any SQL topic you can imagine. I think this is one of the areas that make attending the PASS Summit invaluable (it spawns ideas that you can take with you and apply in your environment). You may not remember everything from every presentation, but you can remember enough to know that you need to research a little more on certain topics when you get back to work. But, I think the best thing I took away from the PASS Summit was not something I learned; it was motivation. We tend to get stuck in our daily routines and every now and then we need something like the PASS Summit to recharge our batteries and rejuvenate our outlook on our jobs. When you combine all this with the great networking opportunities and after parties the PASS Summit is one of the best SQL events that you'll ever attend.

I could have gone on but one thing I didn't mention above is that there is a 250 word limit and I'm at 249 according to Word. So, I guess that will have to do. I am really going to try to attend the PASS Summit this year, even if I have to foot some of the bill. As you can see from the above paragraph, I think the PASS Summit is well worth the money.

Tuesday, June 2, 2009

The PASS Virtualization Virtual Chapter needs your help!

I am one of lucky people that have been chosen to be one of the committee members in the PASS Virtualization Virtual Chapter. Okay the name is a little confusing, I know. PASS is in the process of converting all of their Special Interest Groups (SIGs) to Virtual Chapters; and this one just happens to be on SQL Virtualization.

I think Virtualization has a big future in the IT industry (as if it hasn’t already had a major impact), but to be honest I haven’t had a lot of experience using SQL Server on Virtual Machines. I am more of a “buy the biggest server you can and pack as many databases on it you can” kind of guy. I know I have a few questions about Virtualization mainly around Licensing and Administration, but I wanted to collect as many questions and/or comments as I could from the SQL world out there, compile the data, and answer some of the questions DBA’s have about SQL Server and Virtualization. If you could drop me an email at with all of your Virtualization questions/comments (let’s leave it SQL related here) that would be awesome.

Here are some of the questions/comments I have about Virtualization. Maybe they will spark a few questions of you own.


It seems like you would pay a lot more money carving up a server and paying for each SQL instance, when you could just pay for a license for the entire server and install as many instances as you want. Can you just pay for an instance per proc for the entire server or do you still have to license each VM as if it were a different server?


I can think of a few reasons that VM’s would make administration harder over a consolidated SQL Server for example, you would have to patch each VM separately. I know everything has pros and cons, so what are some of the ways using a VM will help me as an administrator over server consolidation?

There are a few reasons I can think that VM’s may help from an Administrative perspective. One is the ability to have different maintenance windows for each application. Another is segregation of applications and resources from one another.

Monday, June 1, 2009

My take on SQLSaturday

I attended my first SQLSaturday on May 30th in Birmingham. I made the drive there and back from Columbus, GA (about 2.5 hours each way). It was a long day, but I had a great time. I met a lot of friendly people and learned a few things too. It’s really nice to be around such a large group of people that all share the same passion for SQL as I do. When you talk to people, their eyes don’t actually glaze over. I did a presentation on Preproduction Tasks. I had a 60 minute slot and could have gone longer. I have posted the presentation on the resources page of the Columbus GA SQL Server Users Group website (complete with scripts and all) for anyone that would like a copy. The files should also be located on the SQLSaturday website for the event here. I will be presenting in my hometown of Pensacola at SQLSaturday #14 and I’m looking forward to meeting many of the people I interact with online. You will definitely find me at any of the the SQLSaturday’s that come near Columbus, GA from now on and who knows I may be able to host a SQLSaturday here soon.

Thursday, May 21, 2009

That’s A Rap?

You may have noticed a lot of DBA's trying to go around rapping this week. For example, Steve Jones posted DBA's Delight earlier this week with no explanation (braver than me). If I am going to make the world read my sad attempt at writing a rap, I figure I at least owe them an explanation. So here's the scoop. Michelle Ufford and Ward Pond started a SQL Rap Contest the prize is an iPod touch.

Now that you know the motivation behind what you are about to read. Here is my submission...

WARNING! You may notice a yellow tint on your monitor due to the amount of cheese contained in the following post.

Vanilla Ice Parody

All right, DROP the Database and Listen
The Cube is back with a whole new Dimension

Caffeine has a hold on me tightly
I'm gettin paged now daily and nightly

Will it ever stop, yo I don't know
I work all night, its no joke

To the extreme I kill your process like a vandal
Cause I got SQL Skills, (oops that's Paul Randal)

If there is a problem, yo I'll solve it
Haven't run CHECKDB with Data Loss yet

This job is kind of crazy (dun, dun, dun, duh-na-na-nah)
Haven't seen the Sunlight lately (dun, dun, dun, duh-na-na-nah)

Rollin, out version 10.0
With my files sized right so my logs won't grow

Oh yeah, I've got Enterprise Edition
I'm using Resource Governor, with a mission

Putting, all the databases on Standby
For the logs, that I should apply

Installs, I've got down to a science
Service Packs, I know how to apply it

If I learn something yo, I blog it
Check out my site when your browser resolves it (

This job is kind of crazy (dun, dun, dun, duh-na-na-nah)
Haven't seen the Sunlight lately (dun, dun, dun, duh-na-na-nah)

(Word to your Mutha)

Here are a few other submissions I tracked down.

SQL 2008 Data Collection Woes

I finally got around to playing around with the SQL 2008 Data Collector. I got the database set up, ran some tests, and everything seemed to be going great.  Well, there are a few jobs that get created when you set up the Data Collector.  Since I use this machine for presentations, I wanted to go ahead and clean up the jobs so I could have a clean Instance for my demos.  Easier said than done. Turns out that once you set up the Data Collector, the jobs are there for good.  I did some searching and found this Connect entry (Here). Hopefully the capability will be added to remove these jobs in a future release, but for now, just be aware that if you set up the Data Collector, you may be stuck with some unwanted jobs on your system.

Monday, May 18, 2009

Columbus GA SQL Server Users Group

I’ve got the Users Group up and running in Columbus GA (  Our first meeting was a big success.  I was expecting about 12 people and over 40 people showed up. You can read about the first meeting on the SQL Pass website (Here).

Here is the info for the next meeting.  If you are in the area feel free to attend.  I have booked a bigger room this time that holds 70 people, since the other room was only supposed to hold 35.  Also, send me an email if you are interested in speaking or sponsoring on of the upcoming meetings.

Michael McClellan, a Lead Client Server Software Analyst at TSYS will be providing a real world example of creating an SSIS Package to load a DataMart using the AdventureWorks database.  He will provide a document containing step by step instructions to recreate the package along with the completed package file for you to download following the presentation.

May 27, From 4:00 - 7:00
At the Columbus Public Library.

4:00 P.M.-4:15 P.M.  Registration, networking
4:15 P.M.-4:30 P.M.  Announcements and business
4:30 P.M.-5:30 P.M.  Main Presentation
5:30 P.M.-6:00 P.M.  Question-and-answer period
6:00 P.M.-7:00 P.M.  Informal meetings, raffle drawings, etc.

We will giving away some shirts provided by Edgewood Solutions, a few books from Apress and Microsoft, and the complete set of DVDs covering every session from the 2008 PASS conference currently going for $595. The food for this months meeting will be provided courtesy of Confio Software.