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
GO
DBCC FREEPROCCACHE
GO
SELECT * FROM Person.Address WHERE Postalcode = '98011'
GO
SELECT * FROM Person.Address WHERE Postalcode ='98011'
GO
SELECT * FROM Person.Address WHERE Postalcode = '98011'
GO
SELECT * FROM Person.Address WHERE Postalcode = '98011';
GO
SELECT * FROM Person.Address WHERE Postalcode = 'K4B 1T7'
GO
SELECT *  FROM Person.Address WHERE Postalcode = 'K4B 1T7'
GO

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
GO

SELECT   TOP 5 
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;

image

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.

SELECT   TOP 5 
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
ORDER BY [Total CPU Time] DESC;

image 

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 cyberjunkyks@yahoo.com 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.

Licensing

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?

Administration

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.