Wise Man or Wise Guy

Thoughts on SQL Server Certification

Jack Corbett — Mon, 15 Mar 2010 15:00:00 GMT

This is probably part 1 of a series, but no guarantees.

Why Certify?

I've been working with SQL Server for 10 years and for most of that time I've thought certification was unnecessary. When I first started out my boss was not a fan of MS certifications, so I wasn't interested either. Once I had some experience, I thought that my experience was enough. So what changed my mind? I moved and got to know people who, in my opinion, know a lot more about SQL Server than I do, so I was challenged to "up my game" and one way to do that is through certification. The certification requires that I study about areas of SQL Server that I have not had the opportunity to work with or needed to know about, areas like clustering, log-shipping, online restores, to name just a few.

Another reason is that I enjoy presenting (teaching) and I think I'd like to continue to do that and maybe make some money doing it. So, I decided that one way to do that is to become and MCT (Microsoft Certified Trainer) and in order to do that you need to be an MCITP.

Finally, after having already decided to go down the certification path, I read this article in ComputerWorld, Opinion: Certifications are no longer Optional. I don't know how accurate that might be, but I also think that certifications can't hurt me.

So What Have I Done?

I recently took (and passed) 70-432: TS: Microsoft SQL Server 2008 Implementation and Maintenance.

Preparation

I prepared for the test by going through Buck Woody's (@BuckWoody) blog series on his preparation for this test. I also purchased the MCTS Self-Pace Training Kit (Exam 70-432): Microsoft SQL Server 2008-Implementation and Maintenance. I purchased the book because it comes with practice test software which I thought would be valuable. The training kit was helpful, but a little shallow, I really expected it to be delve deeper into the subjects. The case study presented at the end of each chapter was the most helpful part of the book as it really caused me to think about what I thought was the best way to solve the problem(s) presented. I actually had a harder time doing the practice test(s) than I did with the real test. I don't think I passed the real test because of this book, but it did help augment my experience and the other resources.

The Test

Having never taken a certification test before and this being about 15 years after the last test I took that actually meant anything, I was a bit concerned about being able to pass. This concern was based on the fact that I had NOT passed a practice test. I'll share that I have always been a good test taker, especially when the test is multiple choice, which the certification test is. I got to the test center about 30 minutes before the test was scheduled (as recommended) and was registered and at the test station about 20 minutes later. Now, the recommendation is to allow 2 hours and 45 minutes for the test so I expected a long test. Well, I was leaving the test center 40 minutes after I sat down at the test station. During that time I did the practice test, as it was my first time, did the pre-test MS survey, took the test, reviewed every question on the test, took the post-test MS survey, and the post-test Prometric survey. You can decide how hard the test was.

Test Value

The test definitely covered all the areas it said it would cover, but I thought it should have been twice as long as there was not enough depth to the questions. I am pretty sure I could have passed without studying. I definitely could have passed without studying as much as I did. Would I expect someone who passed this test to be able to sit down and set up a cluster or replication? No. I would expect to be able to give them a scenario and have them give me an basic solution that would be meet business requirements around availability and recovery. So it has some value, but could be better.

What's Next?

I've scheduled 70-450: PRO: Designing, Optimizing and Maintaining a Database Server Infrastructure using Microsoft SQL Server 2008. I'll have another post about this test when I get there.

PASS Releases Survey Results & Summit Location – Reactions

Jack Corbett — Thu, 11 Mar 2010 16:00:00 GMT

The Impetus

So after Andy Warren’s (@SqlAndy) blog post, PASS Update #24, last week and the follow-up posts by myself (A Swing and a Miss By PASS) and Brent Ozar (@BrentO) (PASS Summit Location Voting Results), PASS released the survey results and the location of the 2011 and 2012 Summits(Seattle) in this month’s Connector newsletter and on the PASS web site. The post includes the reason for delaying releasing the results and why they chose to stay in Seattle. If you read the PASS post you also need to read the comments as there are those that agree the decision and reasoning and those that disagree with it. You should also read Tom LaRock’s(@SQLRockstar) response to my post (and Brent’s I assume) which I appreciated.

Thanks Deserved

First I want to applaud the decision by the PASS leadership to get the results out immediately and especially including the locations they chose and why. I don’t necessarily agree, but I think that getting the information out to the community is more important than the decision on the location. One thing I believe is that the membership/community is PASS so the policy of the board should be to share everything and only vote on what should NOT be public. I get the feeling that the current policy is the opposite, let’s vote on what to release.

Other Reactions

There were several blog posts responding to the release of the data. Here are the ones I know about:

Grant Fritchey (@GFritchey) was first with Location of the PASS Summit
Brent Ozar was next with The PASS Summit Location Survey Results
Simon Sabin (@simon_sabin) chimed in with PASS Location fixed for the next 2 years – Are they listening? and then PASS Summit Location followup - result analysis
K. Brian Kelley (@kbriankelley) added his thoughts with PASS Summit to Stay in Seattle

The PASS Explanation

Reason #1

“There is a disparity between wanting to have Summit on the East Coast and the desire to have access to plentiful, top-notch Microsoft resources at every PASS Summit. For all three questions pertaining to the importance of Microsoft resources, 69%- 84% of respondents maintain that having access to many, and varied, Microsoft resources is important to their Summit experience.”

“We would not be able to achieve anywhere near the same level of support from Microsoft as we do when Summit is held in Seattle. We would lose out on at least 50% and likely 75% of Microsoft presenters, developers, and SQLCAT and CSS staff – all things a majority of survey respondents listed as important or very important.”

Based on the results from these 3 questions:

Hundreds of Microsoft developers available – 45% Very Important, 33% Somewhat Important
Availability of CSS and SQLCAT Experts in face-to-face environment – 36% Very Important, 33% Somewhat important
Access to Many and varied Microsoft Presenters – 48% Very Important, 36% Somewhat Important

I’d argue that a more important question is, how many people who attended the Summit took advantage of the hundreds of MS developers or the CSS and SQLCAT experts. If the “hundreds” we got in Seattle was say 300 would the top 75-150 of these be enough? How many of the “hundreds” in Seattle came from non-Washington MS locations? The one MS session I attended the presenters were not from Redmond and at least one attendee was on a flight to Charlotte with several MS employees from Charlotte.

Then the other question about having MS presenters, well of the Top 10 Sessions for Summit 2009 there were 2 MS presenters and 5 non-MS presenters, so that tells me that there are some good speakers outside of MS. I will say that MS presenters did dominate the most popular BI Tracks.

Reason #2

“Based on Microsoft's release cycle history (major release cycles run approximately every 3 years, with minor ones often coming in between), 2011 or 2012 will likely be a launch year. It would be disappointing for the community to lose out on the advantages of being in Seattle during a potential release year.”

I can’t argue with that. It would be nice to have the most MS presence in a release year, but Brent makes a good point in his post:

“If major release cycles run every 3 years, and minor ones come in between, then the odds of having a release on any given year is 2/3! Every year is a potential release year. As far as I’m concerned, if Microsoft wants to promote a release, they can come to the community. This is exactly their strategy in Europe – they’re coordinating with community leaders to host regional events all over Europe for the launch of SQL Server 2008 R2. Why does the community have to come begging to Microsoft’s doorstep? They’re the ones making money off us.”

Reason #3

“Seattle is a very cost-effective location for a conference the size and scope of Summit. We were surprised to find that most East Coast locations we considered would cost substantially more and would likely raise registration prices and negatively impact the budget available to many other PASS activities, including Chapter resources and online events. We should also point out that survey respondents listed the cost of moving Summit to the East Coast as the least important of four PASS priorities (others included: Chapter resources, events such as 24 Hours of PASS, and the PASS website).”

Cost more for who? Aaron Nelson made a point on Twitter that his hotel in Charlotte (one of the PASS investigated locations) for SQLSaturday was $59/night vs. the $200/night most hotels near the convention center in Seattle were. Grant also mentions cost in his post:

“For example, doing a quick search on Travelocity, no details, accepting defaults, meeting half-way, in Dallas, would cost $216 instead of $399. That’s almost $200 in savings. Even if Rushabh is right and we’d have to increase the cost, let’s say $150/attendee, that’s still offset by the flight.”

My Conclusion on the Data

Despite all of these thing in the data nearly 60% of respondents said that the PASS Summit should be held OUTSIDE of Seattle EVERY OTHER YEAR vs. only 19% thinking that the Summit should ALWAYS be in Seattle. Maybe I’m spinning the data to agree with my opinion, let’s move the Summit around, but I still think those are very strong numbers. For the record I did not vote for moving every year, I voted for every 4 years.

A Mini-Summit on the East Coast?

The final line of the editorial is:

“We are listening to all of you who desire more PASS presence on the East Coast, and are currently looking into holding a smaller conference there in the near future. We’ll share more information as it becomes available”

I don't want to sound ungrateful, but why would you do 2 events? So now instead of the PASS Summit being THE SQL Server event, it is one of the SQL Server events. Also, how does this mesh with all the reasons the board gave for keeping THE Summit in Seattle? It’ll still cost more than a similar event in Seattle. You still won’t get the MS presence that is so important, or if you do, why wouldn’t you get that presence if you held THE Summit on the East coast? Is there a strategic reason for doing this or is it being done to pacify the “voters”? I hope that there is a strategic reason for it and if there isn’t I hope that resources aren’t used for it.

I understand that the board believes it is making the best decision for PASS, but I disagree. I want to see how this decision meets the goals of PASS: Connect, Share, Learn.

I’m interested in what you think. I know there are people who strongly agree with the decision to stay in Seattle, so let me know why. Comment on this post or post on your blog and link to it in the comments. I think anything that gets the community involved will turn out to be a good thing for PASS.

March OPASS Meeting Recap

Jack Corbett — Wed, 10 Mar 2010 19:20:00 GMT

We had a great turnout of 25 people for last night’s OPASS Meeting sponsored by Quest Software.

We started the meeting with the PASS Chapter Deck which has lots of information on it. I did get one comment later that there was too much on the deck to process.

Networking

After announcements, Andy Warren (@sqlandy) led a networking exercise that worked on remembering names. The exercise started at check-in when we didn’t have name tags. Then we were all given a number tag, a pen, and a piece of paper. Andy then had each person, in random order, stand up and say their name twice. After everyone had said their name we then went in numerical order and wrote down each person’s name, if we remembered it. I did well only missing 2 and one was because that person did not stand up when we got their number. The other I missed because I couldn’t hear the name very well. There were a couple of conclusions:

The more clearly and loudly the person said their name the easier it was to remember.
If there was something extra added when the person said their name it was easier to remember. For example, one person said, “My name is Bob, and now I’ll say it backwards, Bob”. Everyone remembered him.

Featured Presentation

David Pless, Senior Premier Field Engineer with Microsoft, presented on Performance Tuning and Optimization in SQL Server 2005/2008. He did a good job talking about different server and database configuration options, gotchas, and how to use DMV’s to find issues. David had a lot of good information, and wasn’t able to get through his complete presentation in just over an hour. The consensus was that we need to have him back for a part 2. David had 2 things that he stresses that I’ll share:

Autogrow is fine, but set it to a fixed amount, not a percent.
Update Statistics. Stale statistics are a common cuase of performance issues.

The featured presentation was recorded and is available at: https://www323.livemeeting.com/cc/usergroups/view?id=QFG3GW

We finished up the evening with a Raffle with many items from Quest, plus some from User Group Support Services.

After Meeting

As usual there were good discussions after the meeting as well. David had brought his 16 year-old daughter with him so I spent some time talking with her. I also spoke with Mike Antonovich about some contacts he had given me at the last meeting. Then Andy, Kendal Van Dyke (@SQLDBA) and I, spent some time talking about community, SQLSaturday, and PASS. Always an interesting discussion as we each have our own ideas and it’s fun to see where they mesh and where we disagree.

As usual I encourage you to make it to meetings whenever you can, where ever you are. Online meetings and events are fine, but there is nothing quite like being there in person and having discussions with your peers.

OPASS March Meeting with David Pless

Jack Corbett — Mon, 08 Mar 2010 19:44:00 GMT

March 9 6-8:30pm

This month's meeting sponsored by

Live Meeting: https://www.livemeeting.com/cc/usergroups/join?id=QFG3GW&role=attend

Speaker: David Pless

David has been a Senior Premier Field Engineer with Microsoft for three years. As a PFE he performs health checks, conducts chalk talks, and full training workshops. He also works on reactive and proactive engagements on SQL Server 2005 and 2008. He mainly supports the Southeast focusing on Florida, but can be anywhere in the world.

Core areas are performance tuning and optimization, mirroring, Reporting Services (SSRS), and Integration Services (SSIS) and other areas suchas consolidation, table partitioning, resource governor, performance data warehouse, SQL Server auditing and more.

Prior to working for Miscrosoft David was a freelance consultant and also worked for Intellinet as an MCS Partner Consultant starting the SQL Server practice at the Atlanta based consulting form. PRior to Intellinet David was a DBA for CheckFree Corpportation in Norcross, Georgia for over 6 years. David has worked with SQL Server since version 6.5 and has been an active member of the SQL Server community for years.

Topic: SQL Server 2005/2008 Performance Tuning and Optimization Techniques

This discussion will cover how to troubleshoot performance issues on SQL Server 2005 and 2008. It will cover performance impact analysis using DMVs, Set Options in SQL Server, and Profiler and perfmon anlaysis. It also includes SQLNexus, PAL (Performance Analysis of Logs), RML Utilities, SQLDiag, and other tools. Finally we will discuss reading graphical query plans and what you can learn from reading the XML plans.

If time permits we will also cover Performance Data Warehousei n SQL Server 2008, new features in 2008 regarding missing indexes, viewing query plans, and Extended Events.

Goals:

Build a repeatable performance base to verify I/O's, identify missing indexes, identify out of date statistics, and more.
Become aware of new SQL Server tools that help the DBA better analyze and attack performance issues such as SQL Nexus, PAL, RML Utilities, XML Notepad to read XML Plans, and more.
As tim epermits to be aware of new features in SQL Server 2008 to get a handle on perfomance such as Perfomance Datawarehouse, changes to SQL Server Management Studio, Resource Governor, and Extended Events

Please email webmaster@opass.org to RSVP.

For directions visit the OPASS web site.

Hope to see you there, either in person or virtually.

A Swing and a Miss by PASS

Jack Corbett — Mon, 08 Mar 2010 16:00:00 GMT

On Saturday my son had his second baseball game. When it was his turn to hit I reminded him to keep his eye on the ball because in his first game he had a struck out on a swing and a miss because he didn’t keep his eye on the ball. I think that the PASS board has taken their eye off the ball resulting in a swing and a miss by not publishing the results of the January survey about where members would like to see the Summit held. Last Friday (3/72010), Andy Warren (@sqlandy) shared that the board had gone over the results of the recent survey and voted on where to hold the next few Summits in his PASS Update #24 blog post. The interesting part of the blog post is this:

I’ve asked for the data to be posted on sqlpass.org, so far we haven’t managed that, hopefully soon. I also requested that we release the full detail records scrubbed of identifying data, but it was determined that doing so was too complicated!

I’m not the first one to comment on this as Brent Ozar (@BrentO) quickly responded in his blog post, PASS Summit Location Voting Results. I have to say that I agree with what he has to say, I don’t care how complicated it is to publish the results, you have to do it. Not publishing those results leaves open the possibility that, no matter what the board has decided to do for future Summits (apparently we can’t know where future Summits will be until the current Summit), it isn’t what the survey showed. I want to make it clear that I’m not criticizing the board for whatever decision that they made regarding the location of upcoming Summit’s, the criticism is that they should release the results of the survey. We, the members, are PASS and have the right to know those results.

I’ve served on church board’s (not as big as PASS, but similar) before and I understand that sometimes a board needs to di what they believe is in the best interests of the organization, even if it is not popular with members. I call that leadership, but please share the results and also the reasons why you made the decision you made. The membership was asked what we thought, so we need to know the corporate results. We also should know the decision that was made regarding future Summit locations. We were asked for our input, so we need to know what it is and what was done with it. This could be an issue that influences decisions to run for the PASS board and decisions on who to vote for and the election is BEFORE the Summit.

To go along with that last sentence, I am now considering applying for the PASS board. I don’t know that I have all the right qualifications, but I think I’ll being doing it for the right reason, I’m passionate about PASS and the SQL Server Community and want to see it grow. I’ve also been quick to speak out and offer criticism’s of decisions, and I believe that if you are going to be critical you need to be willing to take a leadership role. There are a lot of great people in PASS (including ALL the board members), but we, those not already on the board, all have reasons for not running. I’ve decided that I’ll always be able to find reasons NOT to do it, so now I’m looking for reasons TO do it.

If you are passionate about PASS, I encourage YOU to do the same.

Timing Matters

Jack Corbett — Thu, 04 Mar 2010 16:00:00 GMT

A few weeks ago I got an email from my backup administrator with a message like this:

Set type               : Backup
Set status             : Completed
Set description        : Weekday Backup

Resource name          : \\Server\Backups
Logon account          : System Logon Account
Encryption used        : None

Agent used                     : Yes
Advanced Open File Option used : No

Byte count             : n bytes
Rate                   : n MB/Min

Files                  : n
Directories            : n
Skipped files          : 1
Corrupt files          : 0
Files in use           : 0

Start time             : DOW, Month DD, YYYY 12:25:48 AM
End time               : DOW, Month DD, YYYY 12:29:26 AM

Media used             : media name
File is in use         : \\Server\Backups\BackupFolder\database_name_tlog_YYYYMMDDHHMM.BAK

So I checked the job schedule because when the transaction log backup job runs, it also deletes old backups. The Log backup job was scheduled to run on the 1/2 hour and according to this report, the BackupExec job finished BEFORE 12:30 AM. I sent this information to the backup administrator mentioning that it seemed like there must be a time difference between the BackupExec server and the SQL Server. It turns out that the the SQL Server was 62 seconds ahead of the BackupExec server.

Apparently BackupExec grabs a list of files to be backed up and then iterates over the list backing up each file, but when it got to this file the SQL Server Agent job had deleted the file. Thus, BackupExec couldn’t backup the file.

I find this interesting because wouldn’t this also mean that BackupExec would miss any files created during the backup process? I’m not a expert on backups, beyond knowing how to do SQL Server backups and the fact that I’ve changed tapes and started BackupExec jobs a couple of times, so maybe this is expected and accepted behavior.

Speaker Evaluations for SQLSaturday #32 – Tampa

Jack Corbett — Mon, 01 Mar 2010 16:00:00 GMT

I recently received my Speaker Evaluations for my session, Why I Use Stored Procedures, from Pam Shaw (@pamshaw) and as is my custom I thought I’d share them here. My event recap is available here. THe basic layout of the evaluation is to rate if the session met, did not meet, or exceeded your expecations and then a 1-5 rating of the speakers presentation skills (1 being the worst). Here are my results:
Presentation Expectations:

Did Not Meet	Met	Exceeded
0	11	4

This basically means that I did what I said I’d do in the session description. I gave my reasons why I use stored procedures and, I think, some good examples as to why.
Speaker Rating:

1	2	3	4	5	Avg
0	0	0	7	8	4.5

I’d say that I’m happy with all 4’s and 5’s and a 4.5 average. I take this to mean that I didn’t put anyone to sleep and that I at least sounded like I knew what I was talking about.
Comments:
I had 2 comments. Very Clear and Nice Demo. Not a whole lot to glean from that other than I apparently made the point(s) I wanted to make and stayed on topic.
I really enjoy speaking and would do more if time and finances permit it. If you run a user group and need speakers fell free to contact me and I’ll see what I can do.

The Journey from Smart to Good

Jack Corbett — Thu, 25 Feb 2010 16:00:00 GMT

“It’s easier to be smart than good.”

I heard that quote in a recent sermon at church and it is one I can very much relate to in more than one area of my life. In context the pastor was discussing the difference between knowing what the Bible says (being smart) and applying what the Bible says (being good) and all too often I am more smart than good in that area, that’s a topic for another blog, but really the quote can be applied to many areas, including being a SQL Server DBA and that’s what I want to talk about here.

All too often I’m smart, rather than good when it comes to being a DBA. I allude to this in my 2010 Goals post in the Professional Development section goal #2. I’m great at learning new things and always have been, but not as good at putting them into practice. I KNOW a lot about SQL Server, but, for a variety of reasons (fear of failing, size of SQL Server implementations, etc…), I haven’t APPLIED that knowledge. One other thing I think that comes into play for me, anyway, is the three levels of knowledge as discussed in this blog post (note: while the ideas are good I don’t agree with some of the wording). Basically it says there are three areas of knowledge:

The things you know you know.
The things you know you don’t know.
The things you don’t know you don’t know.

The goal is to have area 3 be as small as possible and area 1 as large as possible. My issue in regard to being a SQL Server DBA is that I have moved a big chunk of area 3 into area 2, which just makes me feel inadequate, but in reality that means I’m making progress. The next step is to move some of area 2 into area 1.

So what’s the plan for going from smart to good? I think the first step is to understand that there is a difference, then begin applying what you already know. Then just continue to learn AND apply.

Remember it’s a journey not a destination and the good DBA’s I know realize that you never really arrive.

So where are you in the journey?

What’s up with the reads?

Jack Corbett — Mon, 22 Feb 2010 16:00:00 GMT

So the other day I read Andrew Kelly’s article, Do You Have Hidden Cache Problems, in the latest electronic edition of SQL Server Magazine, and decided to run the query he listed in the article to see if my server was suffering from this problem:

SELECT
    SUM(single_pages_kb + multi_pages_kb) AS "CurrentSizeOfTokenCache(kb)"
FROM
    sys.dm_os_memory_clerks
WHERE
    name = 'TokenAndPermUserStore'

My result was 189344 kb. In the article Andrew states that:

…if its under 10MB, you should be fine. I’d start getting concerned between 10MB and 50MB. If the amount of the consumed memory is over 50MB, you’re probably affected by this problem.

Well, my value converts to nearly 185MB!!! I guess I must have this problem. The article says this about the cause:

On a system with reuses query plans effectively, this cache would be as large as only a few megabytes,… But on a 64-bit system with lots of ad hoc or dynamic SQL queries, this cache can grow to hundreds of megabytes in size.

So apparently my lightly loaded server (1 main application on it right now) and a max of about 50 user connections has a lot of ad hoc or dynamic SQL hitting it. Well, since the application is a third-party application I decided to run this query to see what I had in the procedure cache for ad hoc queries:

SELECT
    DEQS.execution_count,
    DEQS.total_worker_time,
    DEQS.total_physical_reads,
    DEQS.total_logical_reads,
    DEQS.total_elapsed_time,
    DECP.refcounts,
    DECP.usecounts,
    DECP.cacheobjtype,
    DECP.objtype,
    DEST.text
FROM
    sys.dm_exec_query_stats AS DEQS JOIN 
    sys.dm_exec_cached_plans AS DECP
        ON DEQS.plan_handle = DECP.plan_handle CROSS APPLY
    sys.dm_exec_sql_text(DEQS.sql_handle) AS DEST
WHERE
    DECP.objtype = 'ADHOC'
ORDER BY
    DEQS.execution_count DESC

Hmmm, 4800+ rows returned. As looked a little deeper I noticed that rows 10-12 based on execution count were the same query with different WHERE clauses. The queries looked like this:

SELECT TOP 1
    Column1
FROM
    Table WITH (NOLOCK)
WHERE
    (Column2 = 'Column Default Value' Or
     Column2 = 'Another Column Value') AND
    (Column3 = 'Column Default Value') AND
    Column4 = 'Value' AND
    Column5 = 'Value'
ORDER BY
    Column2,
    Column3

Interesting. Then I looked a little further down the list and found some more of the same query. So I reran my query adding WHERE text Like '’%KeyWord1%KeyWord2%KeyWord3%’ and found over 600 rows for this “single” query in the cache. Now, those of you who read my blog know that I am a believer in stored procedures for data access and if you aren’t using stored procedures then use parameterized SQL. This is a great example of a query that needs to be encapsulated into stored procedure or parameterized.

It gets even better. I took one of the queries and ran it to see what it returned. It returned nothing! So I ran SELECT * FROM Table to see what was in the table, nothing at least at that time! The fun part is that running the application query does a Clustered Index Seek, but takes 2 scans to do it according to STATISTICS IO and the SELECT * takes does a Clustered Index Scan but only scans the index once according to STATISTICS IO. So I looked at the clustered index on the table and it is (based on my query):

Column2, Column3, Column4, Column5, Column6

So the only column in the table not included in the clustered index is Column1 (the value being returned). As I looked at the query, it hit me. There is an OR in the where clause, so in the Query Plan you have 2 SEEK predicates thus 2 scans of the index. If you remove the OR you get one SEEK predicate and 1 scan.

So what does all this mean? Well, for me it solidifies 2 things:

Use stored procedures or at the least parameterized SQL.

OR’s are bad and should be avoided as much as possible

So what am I going to do? I honestly don’t know for sure. Because there are no complaints about performance I don’t feel the need to address the TokenAndPermUserStore issue and I’m not sure what I’d do anyway besides possibly using Forced Parameterization. I think I will contact the vendor (they’ve been good to work with so far) to find out what the query really does and to find out why it isn’t parameterized or in a stored procedure since most of their other stuff is. I’m open to suggestions.

SQLSaturday Acquired by PASS – Reactions

Jack Corbett — Thu, 18 Feb 2010 16:00:00 GMT

The Announcement

On February 2nd, PASS (@sqlpass), here, and Andy Warren (@sqlandy), here, announced ownership of the SQLSaturday ~~franchise~~ brand was transferred to PASS. Since I am friends with Andy I know that this is something that he and his partners (Steve Jones (@way0utwest) and Brian Knight (@BrianKnight)) have wanted to do for awhile.

The Reactions

After the announcement there were several posts in reaction to the acquisition (if I missed any, please add them in the comments):

Andy Warren Explains the move - SQLSaturday & PASS – More Details
Andy Leonard (@AndyLeonard) interviews Andy Warren including the reason for the move - Interview with Andy Warren about SQL Saturday, PASS, and More
Steve Jones gives his take - SQLSaturday Bequeathed to PASS
Marlon Rubinal (@marlonrubinal) – is very positive about the change - SQL Saturday Acquired By PASS
Tim Ford (@sqlagentman) takes a more skeptical view of the move - A SQL Saturday Sequel: Van Halen or Van Hagar?
Tim Mitchell (@tim_mitchell) is positive, but also had some concerns - The PASS Acquisition of SQL Saturday

The Reason

Having been a co-organizer of SQLSaturday #21 – Orlando I have some understanding of what it takes to put on an event (Andy really did most of the work so I don’t understand all of it). Plus, being friends with Andy and seeing him regularly I also have some insight into the amount of work he put into helping events and maintaining/improving the tools (web site). Based on this I understand why Andy, Steve, and Brian would want to pass the administration of SQLSaturday to PASS, an organization with event experience and a full-time staff. Remember, SQLSaturday was not a profit center for Andy, Brian, and Steve, any time Andy (or either of the others) put into SQLSaturday was time not spent trying to make money.

My Take

You can see in my post, What Should Pass Strive To Be?, under point 3 that I believe PASS should be supporting/administering regional events like SQLSaturday. Based on that I believe that from the perspective of PASS this acquisition is a great thing. Having said that I understand and, really, agree with the concerns Tim Ford and Tim Mitchell raise. I think that if PASS remains true to the founding principles of SQLSaturday which I understand to be:

Provide a day of quality, free SQL Server focused training that is organized and administered by local people. Key word free. Yes, some events have charged for lunch, but getting that lunch is optional. This is facilitated by providing:

An event framework with a minimum of rules
An administrative application (SQLSaturday web site) for registration; accepting, approving, and scheduling sessions; messaging for sponsors, speakers, and registrants; and processing funds collected from sponsors and lunch fees

Provide another outlet for local speakers.
Provide a growth path for local speakers. Chapter session –> SQLSaturday –> National/International Conference

and builds upon them then this will be great for both parties, if not then I think it is likely that previous local SQLSaturday organizers will start organizing non-PASS branded events to continue the spirit of these principles. PASS needs to understand that many people in the SQL Server community are committed to these principles.

What Can/Should PASS Do to Make SQLSaturday Better?

To reference my, What Should Pass Strive To Be?, post again (point 4), I think one areas PASS can help is to develop regional leadership teams that assist the local Chapter Leaders in organizing a SQLSaturday. For example, in the southeastern United States you have Andy Warren, Pam Shaw, Stuart Ainsworth, Karla (Remail) Landrum who have all done or by the summer will have done multiple events, use their experience to help others.

Provide assistance in contacting sponsors. Chapters tend to lean on local companies and recruiters who have been great supporters of the events thus far, but the size alone of the event means that they need more than that and organizers may or may not have that contact information.

Continue to enhance the administrative tools provided via the SQLSaturday web site. One of the things I see as missing is volunteer management, and I did mention it to Andy. The session scheduling application could be tweaked to schedule volunteers. This has 2 benefits:

The volunteers know when and what they will be doing ahead of time so they can schedule their day appropriately.
The organizers will see where they need to fill in gaps and make better use of the volunteers’ time.

Conclusion

I really think this change makes sense. PASS has tried to do community events with the Community Connection branding, but struggled to get it off the ground due to lack of administrative tools and the fact that SQLSaturday was out there and had the tools. So now they have the tools and hopefully, the will, to grow the event.

SQL Developers Please Use the Table Name/Alias to Prefix Columns

Jack Corbett — Tue, 16 Feb 2010 16:00:00 GMT

Note: After I completed this post Aaron Bertrand added this subject to his “Bad Habits to Kick” series for myself and Jay as we suggested on Twitter. I wrote this post because I knew Aaron was headed to the Winter Olympics so I didn’t think he’d get to it for few weeks and I needed an idea. Thanks for getting to it, Aaron.
This post is almost an extension of Aaron Bertrand’s (@AaronBertrand) excellent “Bad Habits to Kick” series (if you aren’t reading his blog you should) and was inspired by this tweet:

Basically Jay was looking at a query like this (hard for me to write in SSMS because of SQLPrompt):

SELECT
    Sales.SalesOrderHeader.SalesOrderID,
    OrderDate,
    ShipDate,
    Status,
    Sales.SalesOrderHeader.TerritoryID,
    SubTotal,
    TaxAmt,
    Freight,
    TotalDue,
    Comment,
    OrderQty,
    UnitPriceDiscount,
    LineTotal,
    SP.SalesPersonID,
    SP.TerritoryID,
    SalesQuota
FROM
    Sales.SalesOrderHeader JOIN 
    Sales.SalesOrderDetail AS SOD
        ON Sales.SalesOrderHeader.SalesOrderID = SOD.SalesOrderID JOIN
    Sales.SalesPerson AS SP
        ON Sales.SalesOrderHeader.SalesPersonID = SP.SalesPersonID

Now, for the original person writing this code it makes perfect sense and they know which column belongs in which table, but, if there are performance issues and you call someone in to help, they won’t know what tables the columns belong to without looking at the schema. As a matter of fact, the original author likely won’t remember the tables the columns belong to 6 months later either. In my opinion the above query should look like this:

SELECT
    SOH.SalesOrderID,
    SOH.OrderDate,
    SOH.ShipDate,
    SOH.Status,
    SOH.TerritoryID,
    SOH.SubTotal,
    SOH.TaxAmt,
    SOH.Freight,
    SOH.TotalDue,
    SOH.Comment,
    SOD.OrderQty,
    SOD.UnitPriceDiscount,
    SOD.LineTotal,
    SP.SalesPersonID,
    SP.TerritoryID,
    SP.SalesQuota
FROM
    Sales.SalesOrderHeader AS SOH JOIN 
    Sales.SalesOrderDetail AS SOD
        ON SOH.SalesOrderID = SOD.SalesOrderID JOIN
    Sales.SalesPerson AS SP
        ON SOH.SalesPersonID = SP.SalesPersonID

I know without going anywhere else what table each column belongs to and it is consistent. I even think the Aliases meet with Aaron’s standard.

I know I can be a bit OCD with formatting T-SQL, but I like to be able to read the code quickly and at a glance. See this post for my standards (I’ve actually slightly changed because SQLPrompt doesn’t, at least not that I’ve found, do EXACTLY what I like).

What do you think?

February OPASS Meeting Recap

Jack Corbett — Wed, 10 Feb 2010 21:23:00 GMT

Introduction

Last night we had our February OPASS meeting with Ron Dameron (@RonDBA) presenting on Database Hardening using PowerShell. We broadcast the main presentation using Live Meeting and this time I remembered to hit record, so the session will be available on the OPASS web site at some point.

Attendance

We had a decent turnout of around 20 which is around our average, but this was lower than we expected because we had a lot of RSVP’s come in. There were a couple of reasons for the lower than expected turnout, weather and traffic. It was pouring at the start time of the meeting which probably caused some folks to decide to go home. If you had to come through Orlando to get there the traffic was backed up everywhere because of accidents, likely due to the weather. So we lost some attendance and had some late arrivals. We also had 6 remote attendees and we were a little disappointed that the folks who didn’t make it in person didn’t attend the Live Meeting.

Announcements

We started the meeting with announcements and the chapter deck from PASS. There was a lot of information in the deck and, in my opinion it could have been trimmed down a bit. I hit the highlights and moved fairly quickly through them. I added a couple of slides to the deck for local announcements and, on my laptop, a white font on the background was hard to read on the right-side and black font was readable so I went with the black font. When I got it up on the screen the black font was hard to read on the left-side. I needed to take a bit more time with that, as I certainly didn’t want to have a second deck just for my couple of slides.

Networking

We then had a longer than anticipated informal networking side where we discussed the recent Tampa SQLSaturday, the transition of SQLSaturday to PASS ownership, and other PASS topics. The delay was because we were waiting for the Pizza to arrive, apparently a 10 pizza order is tough to fill on time (see Andy Warren’s recap for more).

Featured Presentation

The featured presentation was Ron describing and demonstrating how he is using PowerShell to automate common tasks and to fulfill requests for audit type information (server settings, users, etc…). He’s on a team that is charged with automating, standardizing, and optimizing DBA tasks for each of their database platforms and as the SQL Server DBA on that team his responsibility is the ~600 SQL Servers (Servers not databases) in the company. Without Powershell some of the tasks he has had to do would be nearly impossible to do. It was good session that shows the power of PowerShell in a large environment and he also did a good job sharing how he learned PowerShell.

The Raffle

We finished up by raffling off some SWAG and it was nice that each of our first-time attendees won something! It’s always nice to reward new people with some nice SWAG!

Post-Meeting

After the meeting several people hung around for an hour or so to talk shop and to get to know each other better. I think the networking aspect of our group is getting better and people are seeing the value of building relationships in the group.

Andy (@SqlAndy), Kendal Van Dyke (@SQLDBA), and I looked at an interesting issue Kendal had seen at work and not been able to solve. We didn’t solve it either, and, hopefully, we’ll see a nice blog post from Kendal about it. We also talked about PASS and about growing speakers and providing a path for growth and how PASS can help newer speakers get out to more events. We ended up staying way too late, but it was great discussion.

Personal Conclusion

I say this often, but, if you have access to a user group and aren’t going, you need to start. Honestly, I felt horrible and wanted to go to bed when it was time to head to the meeting, but I felt great AFTER the meeting. I’m almost always energized and refreshed after attending a user group meeting because I get excited again about what I get to do by hearing about what other people are doing.

February OPASS Meeting Tonight

Jack Corbett — Tue, 09 Feb 2010 16:20:00 GMT

It's here! Our February meeting is tonight at 6pm at End To End Training, 225 S. Westmonte Drive, Suite 2010, Altamonte Springs, FL (see the OPASS web site for directions). While we would love for you to be there in person, the presentation will also be available on-line via Live Meeting at https://www.livemeeting.com/cc/mvp/join?id=24PPGB&role=attend.

Speaker: Ronald Dameron

Ronald Dameron is a Senior Database Administrator for the largest life insurer in the United States. He is most comfortable with Microsoft SQL Server as a database developer and DBA. He is currently exploring how PowerShell can simplify his life as a DBA and the new features of SQL Server 2005/8. Follow him on Twitter at @RonDBA and at his blog at http://ronalddameron.blogspot.com.

20 years of IT experience on a wide range of platforms to include: Mainframes (Yes, he does know what JCL is.) and Windows Server systems.
He now chants Standardization, Optimization, Automation in his sleep.

Topic: Database Hardening via PowerShell.

Ron will demonstrate how he uses PowerShell to handle the issues encountered in a Fortune 50 corporate environment that has over 500 database servers and 3600 databases.

Please email webmaster@opass.org to RSVP for in person attendance.

SSMS and Visual Studio Tip

Jack Corbett — Thu, 04 Feb 2010 16:00:00 GMT

Did you know that if you right-click on a tab in SSMS or Visual Studio you get the option to “Close all But this”?

Maybe everyone else already knew this, but at the January OPASS meeting Andy Leonard (@AndyLeonard) used this and it was the first time I had seen it. I use it almost every day now.

DBCC CHECKDB() on ResourceDB?

Jack Corbett — Wed, 03 Feb 2010 16:00:00 GMT

A few days ago I was answering a forum post about CHECKDB() and as part of the research I checked the Windows Application Log. I was surprised to see this entry:

Event Type:    Information
Event Source:    MSSQLSERVER
Event Category:    (2)
Event ID:    8957
Date:        1/30/2010
Time:        5:45:02 PM
User:        domain\username
Computer:    ServerName
Description:
DBCC CHECKDB (mssqlsystemresource) WITH all_errormsgs, no_infomsgs, data_purity executed by domain\username found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 0 seconds.

I’m using Ola Hallengren’s backup and maintenance scripts to do my integrity checks, but you can’t run DBCC CHECKDB() against ResourceDB, can you? Well, just to verify I tried it and got this message:

Msg 2520, Level 16, State 11, Line 1
Could not find database 'mssqlsystemresource'. The database either does not exist, or was dropped before a statement tried to use it. Verify if the database exists by querying the sys.databases catalog view.

So where did the application log come from? As usual BOL has the answer in the DBCC CHECKDB() entry:

Because the Resource database is modifiable only in single-user mode, the DBCC CHECKDB command cannot be run on it directly. However, when DBCC CHECKDB is executed against the master database, a second CHECKDB is also run internally on the Resource database. This means that DBCC CHECKDB can return extra results.

So while you can’t directly run the command SQL Server is taking care of it for you when you run it against the master database.

The question is, are you doing anything to BACKUP the Resource Database?