Joe Sack's SQL Server Blog

Until we meet again...

Joseph Sack — Fri, 30 Sep 2011 14:00:00 GMT

Today is my last day with Microsoft. After five and a half years, I recently decided to join SQLskills as "Employee #4".

Microsoft has been very good to me over the years and I'm grateful for the friendships made and the significant memories.

For the longer story of my move - I'll be posting soon to my new blog over at SQLskills.com. Very honored to be joining Kimberly, Paul and Jonathan. I'll be getting back to consulting, writing and mentoring. Paul and Kimberly already have clients lined up for me next Monday, so needless to say I'm really excited to start.

Until we meet again!

Announcing the new SQL MCM Program Manager

Joseph Sack — Tue, 28 Jun 2011 02:48:03 GMT

I’ve been waiting for a long running transaction to commit, so to speak, before announcing the big news…

I am truly happy to announce that Microsoft Learning has hired a new SQL MCM Program Manager to keep moving forward with our recent program changes and continue to expand the program’s value and impact:

Robert Davis (blog | twitter | linkedin) is the new SQL Server Microsoft Certified Master Program Manager!

There are many reasons why I’m happy about this hiring decision, but I’ll stick with my top three reasons here:

Robert is an active and tireless community contributor, author and speaker. His community interactions are frequent, consistent and high quality. I also appreciate that he helps people on both internal Microsoft and external facing forums.
He is a Microsoft Certified Master with a deep understanding of the program scope (depth and breadth). He also understands that while the program is elite it should not be elitist. As I’ve said several times before, there are many “uncertified masters” out there and the program has only just started to reach them.
Robert is also incredibly responsive and focused on taking care of the community and ensuring that people’s hard-earned efforts are recognized and appreciated.

I think Robert will take things well beyond where I left them and I’m looking forward to seeing all the great things that I know he will do.

Now – where did I go off to?

I actually transitioned to my new role a few weeks ago. I was a SQL Server support engineer for the last 4 ½ years with the Microsoft Premier Field Engineering team and I also served the dual role of acting SQL MCM Program Manager for the last two years.

The time was right for a big change and so I’ve joined the SQL Server Content & Community team in a Program Manager role. While there are several aspects of the position still in motion – I will start off in this role by working on various aspects of Microsoft’s SQL Server content strategy. I’ll also be involved in planning and driving the next wave of content for the upcoming release of SQL Server.

This is definitely a behind-the-scenes role and that suits me well. I do hope to blog at my same infrequent pace – but we’ll see. While this role gets away from support and far more into the PM space, I’ll still be digging in to some technical details as part of my new role (already encountered a very interesting scenario that will be fun to blog about when the time is right). So if something warrants comment I’ll try to share. Or I may ask others to share instead and this blog will become even dustier. We’ll see…

In the meantime, onward we go – and please join me in welcoming Robert Davis to his new role!

Measuring impact of “optimize for ad hoc workloads” via CACHESTORE_SQLCP

Joseph Sack — Mon, 28 Mar 2011 15:39:00 GMT

The cache store “CACHESTORE_SQLCP” represents cached ad-hoc query plans, server-side cursors and prepared statements. One way to gather memory allocation values for this specific cache store is by using the following query:

SELECT single_pages_kb, multi_pages_kb

FROM sys.dm_os_memory_clerks

WHERE type = 'CACHESTORE_SQLCP'

You can also see allocations to CACHESTORE_SQLCP via DBCC MEMORYSTATUS (example output below for one node):

CACHESTORE_SQLCP (node 0),KB

VM Reserved,0

VM Committed,0

Locked Pages Allocated,0

SM Reserved,0

SM Committed,0

SinglePage Allocator,12728

MultiPage Allocator,1968

There has already been much written about cache bloat due to ad hoc query plans and I won’t rehash it here (Kimberly Tripp has a few great posts on this topic – including one post that lists several posts from different authors on the subject). There are multiple ways to reduce this bloat (for example - move to using parameterized queries, force parameterization). One of the easier methods includes enabling the “optimize for ad hoc workloads” option.

The purpose of this post is to connect the dots between CACHESTORE_SQLCP and enabling the “optimize for ad hoc workloads" option (note that you can also use sys.dm_exec_cached_plans to see the impact on size_in_bytes by plan and reference the cacheobjtype of Compiled Plan versus Compiled Plan Stub). The following demonstration simply shows the impact of executing several ad hoc queries and measuring the significant allocation differences in CACHESTORE_SQLCP. In my testing – single_pages_kb was equal to 13,072 without plan stubs and then 760 after enabling the “optimize for…” option. This below demo is intended to be stepped through statement by statement:

-- Tested on 10.50.1765

-- This demo assumes optimize for ad hoc workloads is off

-- (And yes please only run this demo on a test environment)

USE [master]

EXEC sp_configure 'show advanced options',1

RECONFIGURE

EXEC sp_configure 'optimize for ad hoc workloads',0

RECONFIGURE

EXEC sp_configure 'show advanced options',0

RECONFIGURE

CREATE DATABASE [QueryBloat];

USE [QueryBloat];

CREATE TABLE dbo.Bloat (col01 uniqueidentifier);

INSERT dbo.Bloat

VALUES (NEWID())

GO 500

-- Clear out adhoc queries, prior to baseline

DBCC FREESYSTEMCACHE('SQL Plans')

-- Take baseline

SELECT single_pages_kb

FROM sys.dm_os_memory_clerks

WHERE type = 'CACHESTORE_SQLCP'

-- In my case, I saw single_pages_kb = 120

-- Now let's make some bloat

DECLARE @NEWID varchar(36)

DECLARE curBloat CURSOR FOR

SELECT col01

FROM dbo.Bloat

ORDER BY col01

OPEN curBloat

FETCH NEXT FROM curBloat

INTO @NEWID;

EXEC ('SELECT col01 FROM dbo.Bloat WHERE col01 = ' + '''' + @NEWID + '''')

WHILE @@FETCH_STATUS = 0

BEGIN

FETCH NEXT FROM curBloat

INTO @NEWID;

EXEC ('SELECT col01 FROM dbo.Bloat WHERE col01 = ' + '''' + @NEWID + '''')

END

CLOSE curBloat;

DEALLOCATE curBloat;

-- Checking again, I see single_pages_kb = 13,072

SELECT single_pages_kb

FROM sys.dm_os_memory_clerks

WHERE type = 'CACHESTORE_SQLCP'

-- Now let's enable "optimize for ad hoc workloads"

EXEC sp_configure 'show advanced options',1

RECONFIGURE

EXEC sp_configure 'optimize for ad hoc workloads',1

RECONFIGURE

EXEC sp_configure 'show advanced options',0

RECONFIGURE

-- Clear out adhoc queries for our second test

DBCC FREESYSTEMCACHE('SQL Plans')

-- Take baseline - I see single_pages_kb = 120

SELECT single_pages_kb

FROM sys.dm_os_memory_clerks

WHERE type = 'CACHESTORE_SQLCP'

-- Make some bloat again

DECLARE @NEWID varchar(36)

DECLARE curBloat CURSOR FOR

SELECT col01

FROM dbo.Bloat

ORDER BY col01

OPEN curBloat

FETCH NEXT FROM curBloat

INTO @NEWID;

EXEC ('SELECT col01 FROM dbo.Bloat WHERE col01 = ' + '''' + @NEWID + '''')

WHILE @@FETCH_STATUS = 0

BEGIN

FETCH NEXT FROM curBloat

INTO @NEWID;

EXEC ('SELECT col01 FROM dbo.Bloat WHERE col01 = ' + '''' + @NEWID + '''')

END

CLOSE curBloat;

DEALLOCATE curBloat;

-- Measuring impact - single_pages_kb = 760 (versus 13,072)

SELECT single_pages_kb

FROM sys.dm_os_memory_clerks

WHERE type = 'CACHESTORE_SQLCP'

ARP Caching and SQL Server Failover Cluster install failures

Joseph Sack — Sun, 27 Mar 2011 20:42:10 GMT

Now I’m definitely not a networking guy, so even the more basic networking concepts fill me with wonder. One area I learned about recently with regards to the following scenario was related to Address Resolution Protocol (ARP) caching.

Consider the following scenario… You’re performing a SQL Server Failover Cluster installation and it is failing with a message like “IP Address xyz is already in use. To continue, specify a different IP address.” You validate that this IP is not being used elsewhere and when you ping it, you don’t get a response.

Enter ARP and ARP caching as one area to validate. ARP is a protocol that maps IPs to Ethernet MAC addresses. A network router sitting in between a client and server can in turn have an ARP cache which holds a list of the IP/Mac mappings. The cached mappings can be dynamic (created automatically) or static (created manually). These mappings can have timeouts associated with them – causing disused entries to be removed after a set period of time. You can view an ARP cache on your PC (for example – I did this on my Windows 7 OS) using “arp –a” at the command prompt. And as you can imagine, it is possible to have bad IP/MAC mappings.

There is also the concept of a Proxy ARP where a network device answers ARP requests on behalf of another destination and re-routes out the request accordingly. This then relates to ARP cache poisoning where, in one manifestation of it, an ARP cache can be tampered with in order to point to a bad MAC address for a man-in-the-middle attack.

Back to SQL Server Failover Cluster install failures… The whole ARP caching concept is of particular interest to me in the context of SQL Server installations because I haven’t yet seen it listed on standard SQL Server clustering / install checklists as something to validate (perhaps because the aforementioned issue shouldn’t be too common).

So if you’re seeing an “already in use” message for your SQL FC install and you’re running out of ideas – ask your networking folks about ARP caching and look for bad entries that potentially need to be cleared out in order to continue with your install.

Forced Parameterization with Filtered Indexes

Joseph Sack — Sun, 27 Mar 2011 16:46:00 GMT

Consider the following scenario:

You have a database that has PARAMETERIZATION FORCED enabled.
You have a table using a filtered index.

Here is a demo setup so you can follow along:

-- Create demo database

USE [master]

CREATE DATABASE [FI_PF_Error_Demo];

-- Set new database to forced parameterization

ALTER DATABASE [FI_PF_Error_Demo]

SET PARAMETERIZATION FORCED WITH NO_WAIT;

-- Create a demo table (and population will not be necessary to demonstrate)

USE [FI_PF_Error_Demo]

CREATE TABLE dbo.FI_PF_Demo_T

(col01 int, col02 int, col03 int);

-- Our filtered index referencing col01 as key and col03 in filter predicate

CREATE NONCLUSTERED INDEX idx_FI_PF_Demo_T_col03

ON dbo.FI_PF_Demo_T(col01)

WHERE (col03 = 1924);

Now take the following query that uses the filtered index (using a hint to force this in the example, since there are no rows populated in this table):

-- Tested on version 10.50.1600

SELECT col01

FROM dbo.FI_PF_Demo_T

WITH (index = idx_FI_PF_Demo_T_col03 )

WHERE col03 = 1924;

The following error is raised upon execution:

Msg 8622, Level 16, State 1, Line 1

Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN.

Now try turning off forced parameterization:

ALTER DATABASE [FI_PF_Error_Demo] SET PARAMETERIZATION SIMPLE WITH NO_WAIT;

Now if you re-run the SELECT query, you won’t get error 8622.

So what’s going on?

First of all, you may see 8622 in various contexts – this isn’t just specific to this particular scenario. Because I’m using a hint, the QP is telling me I’m forcing a non-viable plan that will not be compiled. But in this demo – the root cause isn’t just about the hint I designated.
With forced parameterization enabled, the SELECT query I executed is getting parameterized first. So for example col03 = 1924 becomes col03 = @p1.
This means that my original value of 1924 is not being considered when compiling the plan. If I had a value not covered by the filtered index, then the filtered index I’m forcing in the hint will not potentially fulfill all potential values.
Switching back to simple parameterization works because now the query isn’t being parameterized and is compiled based on the 1924 value for col03.

Two good Mondays in a row for the SQL MCM program

Joseph Sack — Mon, 21 Feb 2011 23:24:25 GMT

The last two Mondays have brought us two new highly qualified Microsoft Certified Masters. If you’ve been involved with the SQL Server community for the last few years, it’s likely you’ve already heard their names – Maciej Pilecki and certified today, Simon Sabin.

First – a little about Maciej:

Maciej Pilecki, Trainer and Consultant, Project Botticelli Ltd.. Maciej is a SQL MVP and Microsoft Certified Trainer with many years of experience with SQL Server database development, optimization and troubleshooting. He travels around the world advising his many customers on how to improve performance and reliability of their SQL Server environments. He is also a prolific and highly rated speaker covering the SQL Server engine, SQLOS, Storage Engine, Query Optimizer and Performance tuning.

And – now background on Simon:

Simon Sabin, Principal Consultant (blog | twitter). Simon is the principal consultant for SQL Know How a company that delivers SQL Server Consultancy and Training. Specialising in the performance tuning and mentoring development teams to deliver high performance solutions utilising SQL Server. He has provided services for clients like Microsoft and Tesco. He has worked with SQL Server since 1998 and has always focused on high performance reliable systems. He has a particular expertise in the world of search, distributed architectures, business intelligence and application development. Making websites and applications run faster through scale out technologies and performance tuning the database.
Simon was awarded as an MVP in 2006 and now SQL MCM in 2011. He runs user groups in London and Cambridge (http://www.SQLSocial.com) and in 2007 founded SQL Bits (Sqlbits.com) the largest SQL Server conference in the Europe. He is a regular speaker at SQL Server events as well as writing for his blog http://sqlblogcasts.com/blogs/simons.

Welcome to the community and congrats on your achievement!

One less “uncertified Master”

Joseph Sack — Fri, 11 Feb 2011 02:47:58 GMT

News travels pretty fast, so I thought I would get the official word out tonight before this news became too stale.

As many already know, three months ago we announced some pretty significant changes to the SQL MCM program. I won’t recap those changes in great detail here, but the bottom line is that we wanted to remove some of the barriers of cost, duration and location from the SQL MCM certification requirements.

Why? One big reason was so we could reach more of the “uncertified Masters” we know are already out there.

And today there is now one less “uncertified Master”. Congratulations to Denny Cherry, our newest Microsoft Certified Master for SQL Server 2008!

A little background on Denny:

Denny Cherry, Sr. Database Administrator. Denny Cherry has over a decade of experience managing SQL Server, including MySpace.com’s over 175 million user installations (one of the largest in the world). Denny’s areas of technical expertise include system architecture, performance tuning, replication and troubleshooting. Denny currently holds several Microsoft Certifications related to SQL Server as well as being a Microsoft MVP. Denny is a longtime member of PASS and Quest Software’s Association of SQL Server Experts and has written numerous technical articles on SQL Server management.

Needless to say, we’re very happy (and proud) to have Denny joining the community.

Last batch of SQL MCM videos now published

Joseph Sack — Tue, 04 Jan 2011 03:27:42 GMT

Our last planned batch of SQL Server 2008 MCM Readiness Videos are now published – covering Partitioning, Snapshot Isolation, EXECUTE AS, SQL Injection, Replication, Partial Database Availability, Online Piecemeal Restore, optimizing procedural code, a demo of multi-server management, indexing strategies, index analysis, designing an availability strategy, Failover Cluster setup & troubleshooting and consolidation.

This is the final batch of videos. I hope we can extend something similar again in the future, but we’ll see. It will depend on several factors.

I hope that you find these videos to be helpful and for those of you studying for SQL MCM certification, best of luck to you!

Second batch of SQL MCM Readiness Videos now Released

Joseph Sack — Wed, 15 Dec 2010 21:43:09 GMT

We just added another 23 videos on Technet – making it a total of 56 videos available as of today. The new videos cover storage core concepts & guidelines, DAS vs SAN architecture, storage testing and monitoring, index internals, the clustered index debate, statistics, locking, restore internals, Waits and Queues, a clustering introduction, SQLOS concepts, database mirroring, multi-server management, PBM and Resource Governor.

A few things to be aware of:

· The main landing page shows a rotating random video, followed by 10 of the most recently posted videos.

· The “more” link leads to the full list of available videos (56 videos as of today). By the way - this link is an XML page – which is by design for Technet.

· Each individual video page has the option of streaming the page in the browser or downloading video or audio files in varying formats (typically about 10+ download links per video). Right now this means there are 560+ individual links that get updated by the Technet team. Although I do random checks upon publication, I definitely don’t test every link. There have been instances of broken links or other bugs – so if you find them, please ping me directly and I’ll investigate and submit a fix request to the Technet team. Depending on when an issue is found it can take a couple of days to fix, but it will get fixed.

I hope you find these videos to be valuable (whether or not you use them for SQL MCM readiness).

How do you know if you’re ready for SQL MCM?

Joseph Sack — Sun, 21 Nov 2010 21:07:00 GMT

“Know what you know. Know what you don’t know. Don’t ever confuse the two.” Per Farny, founder of the original Microsoft Certified Master and Architect program

How do you know if you are ready to take the Microsoft Certified Master SQL Server 2008 exams? This is a common question and a perfectly valid one, especially given the recently announced SQL MCM program changes.

Giving recommendations on this topic is a bit of a balancing act… We want to provide enough information to guide an individual’s preparation – but not so much detail that it makes it too easy to pass the exams. The legacy SQL MCM candidates faced a similar challenge in that the final Lab Exam scope and coverage was not disclosed in advance. Candidates only knew that the Lab Exam was going to test “hands-on, real world” advanced SQL Server competencies. This description is pretty open ended and sometimes not even the top experts were able to pass the Lab Exam on the first attempt.

So how do you know if you are ready? I’ll provide some guidance and recommendations based on what I’ve witnessed over the last year and a half – and also based on my own experience going through the program back in 2006 and taking the upgrade in 2008. My seven tips are as follows:

1. Read the Exam Topics and ask yourself if you have experience in this area. Be real with yourself. Have you done what is listed? The seven competencies aren’t incredibly detailed – but they do contain quite a bit of implied knowledge and experience. Make sure you feel comfortable in each area.

2. Watch the SQL MCM Readiness Videos. Do you feel comfortable with the subjects as you watch them? Did you already know most everything you’ve been watching? These videos give you an indication of the program’s depth and breadth – so these will act as a good hint of your overall readiness.

3. Look over the reading list – including the books and white papers. Also check out the reading list that SQLSkills.com put together (Microsoft doesn’t manage this list – but it’s put together by the original instructors for the SQL MCM program). Have you read most of this list already? Have you been reading this over time? If affirmative, that’s a good sign.

4. Look at the various people who have made it through the program to date. Look at their background on The Master Blog and ask yourself if it is similar to yours. We do have a wide variety of job roles in the SQL MCM community today, but there are certain themes that can be extracted from the data (years of experience, immersion in SQL Server, experience in mission critical environments). Have a look at the various announcements over the last year and see if your background is similar.

5. Do you know a SQL MCM? If so, talk to them about your background and experience and get their feedback. They aren’t going to coach you on the exams or give you answers, but they can compare your background with other people in the community and see if you’re close to the mark or need a few more years of experience.

6. Another option is to just jump right in and give the exams a try. This is potentially a more costly method because you may not pass the Knowledge Exam on the first attempt. Also, you cannot take the Lab Exam until you pass the Knowledge Exam. You should also know that the two exams are distinct entities – and while the Knowledge Exam acts as a first filter, the hands-on Lab Exam is the core of the SQL MCM program (just as it has been in the past). Taking the Knowledge Exam without certainty of your background is a perfectly valid approach if you have the means to do so – but just remember that there is a 90 day waiting period between exam attempts and that the content may change over time.

7. Instructor-led training more your style? Then my recommendation is you attend advanced training where available and depending on your budget and time. At Microsoft we’re working on releasing four new advanced instructor-led training courses that will be available through select Microsoft Learning Partners in the first half of 2011 (** Update - we put that aforementioned courseware on hold and are looking at other ways to get out advanced instruction to the broader SQL Server community - stay tuned **). Also, there are several top SQL experts that participated in the original SQL MCM training experience. If you have an opportunity to get advanced instruction from the likes of Paul Randal, Kimberly Tripp, Adam Machanic and Greg Low – this can only help you (their training was often viewed as the best part of the legacy SQL MCM experience). Other trainers like Kalen Delaney and Bob Beauchemin (who helped in the re-launch of the SQL MCM program) also provide advanced training options and an incredible experience. Regardless of where you get your advanced training, this may be another way to validate your readiness.

Last point I want to get across… Experience is paramount. Look over the scope of the exams and find ways to get hands-on experience.

Hope this helps.

Microsoft SQL Server 2008 Administration for Oracle DBAs

Joseph Sack — Sun, 21 Nov 2010 17:03:29 GMT

I just found out today that one of our own SQL MCMs (2005/2008) & MCA, Christian Bolton, is a co-author for the recently published book Microsoft SQL Server 2008 Administration for Oracle DBAs. I just wanted to congratulate Christian on yet another published work!

As an extra bonus - I was also informed that this book contains a list of the authors’ favorite MVP and Microsoft blogs. I was glad to see the SQL MCM community represented with Brent Ozar, Cindy Gross and Jens K. Suessmeyer. The list also included those who have helped the SQL MCM program for years (even back to the “SQL Ranger” days); Paul Randal and Kimberly Tripp – and also Kalen Delaney who helped with the recent SQL MCM re-launch program design work. There are four other names on the blog list that I hope join the community in the future too – all great minds – and I won’t name them here so you’ll have to buy the book in order to find out who they are.

A New Path to Microsoft Certified Master: SQL Server 2008

Joseph Sack — Tue, 09 Nov 2010 17:57:03 GMT

It’s a big day for the Microsoft Certified Master (MCM) program, as we announce the new MCM SQL Server 2008 certification path. Our customers want to be able to earn a SQL Server Microsoft Certified Master certification at a lower price, with more flexibility, and we’ve responded by evolving the SQL Server MCM program without lowering the bar on quality.

What changes have we made?

We’ve removed the three week training requirement, instead making the experience-based exams stand alone in recognizing and validating the world’s top SQL Server talent.
Candidates will now achieve MCM in SQL Server 2008 by passing these two new exams in the following order:
- Exam 88-970: SQL Server 2008 Microsoft Certified Master: Knowledge Exam (now available)
- Exam 88-971: SQL Server 2008 Microsoft Certified Master: Lab Exam (available in Q1 2011)
These new exams were written with the help of 25 SQL Server industry experts (internal and external to Microsoft) in order to ensure industry validity and integrity and will be available worldwide through a select network of high security Prometric testing centers.
Although experience is paramount in order to be successful in the exams, flexible readiness options will also be made available. In addition to the recommended reading list and an overview of skills that are being measured, we are also providing several hours of free online video Masters-level instruction presented by top SQL Server experts. In 2011 we also plan to release four new instructor led courses covering High Availability, Performance, Manageability and Security. These courses will be made available globally through select Microsoft Learning Partners.

It’s our goal to reach the SQL Server experts worldwide who may be qualified to achieve MCM certification, but who’ve run up against the previous barriers of time, cost and location. By reducing or removing these barriers, while keeping the integrity and value of the certification, we expect to grow the community of SQL Server MCMs and increase its visibility and awareness of its value. We’re very excited about the new program, and look forward to the continued growth of the SQL MCM community. Look for all the details about the program, the exams, and exam prep materials at http://www.microsoft.com/learning/en/us/certification/master-sql-path.aspx.

I’ll be blogging about all the details over the next few weeks and if you have questions, please contact me. If you’re attending SQL PASS, please stop by the MS Learning product booth in the expo hall!

High Availability and Disaster Recovery for Microsoft’s SAP Data Tier: A SQL Server 2008 Technical Case Study

Joseph Sack — Fri, 29 Oct 2010 19:57:00 GMT

Apologies for my radio silence lately. With SQL PASS next month, I'll be getting a little more verbose again.

In the meantime while I remain relatively quiet, I worked on a White Paper with Eric Holling (Microsoft IT) and with Sanjay Mishra (SQL CAT team) and contributors Juergen Thomas and Elke Bregler (along with a host of great reviewers) that just got published today:

High Availability and Disaster Recovery for Microsoft’s SAP Data Tier: A SQL Server 2008 Technical Case Study

It was a fun paper to work on, so I hope you find it to be informative.

Off Topic: How I chop wood and carry water

Joseph Sack — Thu, 17 Jun 2010 18:55:00 GMT

“Before enlightenment; chop wood, carry water. After enlightenment; chop wood, carry water.” Zen proverb

I see my daily work as nothing more than a high tech form of chopping wood and carrying water. Work can sometimes seem to be overwhelming or unmanageable, but in the end I think that everything can be broken down into small and manageable tasks. I’ve always been interested in hearing how others go about their daily work, and also understanding what works and what does not.

For the last year I’ve had two separate roles at Microsoft – SQL Premier Field Engineer and Acting Program Manager for the SQL Microsoft Certified Master and Architect program. I also just agreed to be more involved with my townhome association (hurrah – more work!) and so I have a renewed energy around keeping organized and productive (without getting too stressed).

For today’s post, I’m going to talk about my adopted work philosophies that have, at least so far, kept me organized, on track, and productive.

<Expectation warning> There is nothing amazingly original or ground breaking here – just various scraps and bits that I’ve picked up over time from wiser people over time</Expectation warning>:

Make the complicated simple. Even when projects are complicated and sprawling – you can still break them apart into smaller tasks. Those smaller tasks will need to be done in a certain order. Your smaller tasks still seem too complicated? Keep chopping. Too many tasks? Ask for help.

Single tasking? Yes. Single tasking has been my friend for years. When I’m completely focused on a task, that’s when the magic happens. Whether I’m reading an article, writing an email or listening to a customer question – when I give my full attention, it’s all good. Single tasking doesn’t mean I don’t handle multiple issues or tasks in a day – but rather, when I move to a task, I try to give it 100% attention.

Delegate and follow-up. I’m still learning how to delegate (more). Some people actually want to help - and you need to give people the opportunity to do so. Follow-up is equally important – and that’s also an area where I constantly calibrate (give people enough space to get something done but still keep things moving within a reasonable timeframe).

I set my cache to 15% “learn” and 85% “do”. In the SQL Server world, if you’re not continually reading and learning, you’re falling behind. For a typical 60 hour work week, I’m spending 9 hours dedicated to consuming new information. Ideally you can combine “learn” with “do”, but I still think it is important to establish isolated learning and “think” time.

Responsive is best. If I’m very busy, I still like to respond. My response might be “sorry – I’ll look at this next month and get back to you” – but at a minimum I want to acknowledge receipt of a request or question. If I haven’t responded to you – it is because your email went to my spam or you misspelled my email address. I don’t think that this is always possible at a larger scale, but right now I’m still in a place where I can be responsive.

Bad news? Get it out now. When I have something uncomfortable I need to communicate, I don’t let it block my day’s activities. I don’t sit on the information. Unless constrained by confidentiality concerns or NDA purposes, I get the bad news out quickly.

AAA? (Angry? Annoyed? Alarmed?) Hold that email. I’m not always good about this - and when I don’t follow this advice I always regret it. If I’m in a heightened state of irritation that is usually a sign to myself to hold off on the email.

Am I feeling righteous? I’m probably the one who is wrong. Since joining corporate America sixteen years ago - I’ve been through this cycle many times. If I’m feeling righteous or indignant, that’s my alert to check the facts about a situation before making bad assumptions. There is a good chance I’m the one who is wrong.

Physical order reflects mental order. My desk at home has a printer, a router, my laptop, a mouse, and that’s it. Everything else is filed away and out of sight. My desk at my customer site has a phone, hand sanitizer, laptop, caffeine, one metal tray and a pad of paper. That’s it. Clean workspace, clean mind. That isn’t everyone’s style, but it works for me.

That’s all today. Time to chop some wood.

Sharing your T-SQL Code? Test it on a case sensitive SQL instance

Joseph Sack — Tue, 01 Jun 2010 13:46:00 GMT

(Reposting from cache - as the MSDN blog migration seemed to have lost my latest post)

Do you share your T-SQL code with others? If you do, then first of all, thanks for sharing. But next question - did you test it on a case sensitive SQL instance?

Check out the following two queries:

SELECT DepartmentID
FROM HumanResources.Department

SELECT DepartmentId
FROM HumanResources.Department

Subtle difference indeed - and it is a difference that that I wouldn't have identified myself unless I had first tested the script on a case sensitive SQL instance (in my case, I use SQL_Latin1_General_CP1_CS_AS).

When you're coding a small admin or monitoring script, It is usually no big deal in the grand scheme of things (I don't mind making a few changes to "gift scripts" from others). But if you are sharing a big long script - you may exceed the number of changes that someone will tolerate before going with some other solution. My customer uses plenty of case sensitive servers, and it didn't take long before I changed my case insensitive ways... The same goes for demos - when creating demonstration scripts, test them on a case sensitive SQL instance.

Eve of SQL Rotation 9 and planning for future rotations

Joseph Sack — Sat, 01 May 2010 01:52:00 GMT

The ninth rotation of the SQL Microsoft Certified Masters program begins Monday. I'm really looking forward to meeting this next batch of candidates and future MCMs. A few days ago I drew up a summary of each attendee's experience and background and then emailed it to our instructors. I wanted the instructors to know about the significant accomplishments of the incoming group and also let them get a feel for their background and strengths.

This rotation starts up only a month after the previous rotation. In the future we'll be spacing the rotations more evenly apart. On the subject of schedules, we've added a Winter 2011 session to the previously announced sessions. The MCM registration page will reflect this new addition in a few days, but in the meantime, here are the dates for Rotations 10 - 12:

October 4 – 23, 2010
February 7 - 26, 2011
May 2 – 21, 2011

Although Rotation 9 starts only a few weeks after Rotation 8, we did get in a few changes and suggestions based on feedback from the previous rotations (one of the bigger changes involved refactoring the content for the High Availability day - which I myself am looking forward to watching).

Okay, time to pack my bags and get ready for the MCM marathon...

Friday afternoon case of the mystery scan versus seek

Joseph Sack — Fri, 23 Apr 2010 20:36:00 GMT

So I just got finished with a Friday afternoon head scratcher (that luckily had a resolution).

Problem:

Ad hoc query with a predicate that references a literal value does an index seek. 0 second execution.
Ad hoc query that defines a local variable with same set value does an index scan. 2 second execution.

Looking at the XML execution plan, we see that the "seek" query has values designated for ParameterCompiledValue and ParameterRuntimeValue. The "scan" just shows a value for runtime.

We experiment with RECOMPILE and OPTIMIZE FOR query hints. No luck.

I then look at the index scan operator and notice that there is an implicit data type conversion going on. The index seek, on the other hand, does not do an implicit data type conversion.

We look at the base table - and the column referenced in the predicate is varchar(24). We look at the local variable data type - and it is defined as Unicode - nvarchar(24). We change it to match the table - and now we're happily doing index seeks.

More confirmation that implicit data type conversion, in addition to silently eating up extra CPU, also can generate unnecessary I/O (not to mention 7 minutes of support time).

Gotcha: When Server-Scoped DDL Triggers don’t honor ROLLBACK

Joseph Sack — Thu, 08 Apr 2010 16:24:56 GMT

I’ve used DDL Triggers before to prevent database DROP DATABASE operations. For example:

CREATE TRIGGER ddl_ss_prevent_database_drop

ON ALL SERVER

FOR DROP_DATABASE

PRINT 'Will not drop database until you disable this trigger.'

ROLLBACK

If you try to drop a database with this DDL trigger in place, you’ll get the following message:

Will not drop database until you disable this trigger.

Msg 3609, Level 16, State 2, Line 2

The transaction ended in the trigger. The batch has been aborted.

What’s more – your DROP DATABASE truly is prevented, as one would expect.

But this behavior isn’t universal across all operations. Sometimes ROLLBACK isn’t possible. Take the following example of a DDL trigger on an ALTER_DATABASE event:

CREATE TRIGGER ddl_ss_tr_no_alter_db

ON ALL SERVER

FOR ALTER_DATABASE

PRINT 'No ALTER DATABASE allowed!'

ROLLBACK TRAN;

If you execute an ALTER DATABASE – you will indeed get an error message that looks like your operation was prevented – but this is not actually the case. For example – let’s say I want to increase a file size:

ALTER DATABASE [MR1] MODIFY FILE ( NAME = N'MR1_2', SIZE = 70600KB )

This returns:

No ALTER DATABASE allowed!

Msg 3609, Level 16, State 2, Line 1

The transaction ended in the trigger. The batch has been aborted.

But if I check the file size – sure enough – my ALTER DATABASE MODIFY FILE was actually executed and not aborted.

Why? Some DDL operations aren’t allowed to be rolled back in an atomic fashion in line with the DDL trigger. For example – DROP DATABASE can be prevented, but not ALTER DATABASE. At first I thought that the following BOL topic covers – in a roundabout way – what won’t be honored for rollbacks: “Transact-SQL Statements Allowed in Transactions” – however DROP DATABASE is on this list, so this isn’t consistent with what one sees with DDL triggers.

So my recommendation is as follows… If you are expecting a ROLLBACK for your DDL trigger – be sure to test that it actually rolls back the operation. Don’t rely just on the message back to your client session. You should actually verify that the operation was rolled back.

Free press doesn’t always mean lollypops and rainbows

Joseph Sack — Fri, 02 Apr 2010 21:17:00 GMT

(Cross posted on The Masters Blog)

The eighth rotation of the SQL Microsoft Certified Masters program is about to wrap up and tomorrow is the big day of exams. David Ikeda is covering this rotation, but last week I got a chance to sit with the existing candidates for a day of instruction. I was really glad to get a chance to meet this group of candidates in person. These are highly qualified individuals and I have no doubt that we’ll be welcoming several of them to the SQL MCM community over the next few months.

One unusual factor in this rotation is that we have a distinguished and well known SQL professional, trainer, author, and blogger in attendance - Brent Ozar. I’ve followed Brent’s blog before the MCM days, and the prospect of his attendance was (and is) a very good thing. Brent has been blogging about his SQL MCM journey and preparation over the last two months, including details on his day to day experiences over the last three weeks of training.

I was glad to see the blog posts – although I also thought that such transparency means that, with the exception of NDA discussions and exam details, Brent is in a position to discuss the good, bad and the ugly (and although daunting, I believe that this is a good thing). MCM is an unusually tough process and sometimes the right notes are hit and sometimes the notes are missed entirely. Sure enough - today (after a few weeks of blogging) – Brent detailed some valuable (but tough) feedback for the SQL MCM program today on his blog: SQL MCM: Now, the bad stuff.

Both David and I want the candidates to have an experience of their lifetime – and so when components of the training miss the mark – we take it seriously.

Unlike other Microsoft Official Curriculum offerings, MCM training is a bit more organic and malleable. For each rotation, we must be prepared to change based on feedback and external trends. Given Brent’s post – I just wanted to express that we’re definitely listening and that we’ll do what we did for the last rotation which is as follows:

· At the end of the rotation we’ll aggregate all the feedback from surveys, emails, blogs, and conversations (group and individual).

· We’ll then sift through it all – looking for consistent themes. What worked? What didn’t work? Some content is golden and consistently loved. Sometimes content is liked by one rotation, but not the next. Some content is new – but not working. Some content is too easy or out of scope. Some content is missing.

· Next, we then work to address the feedback (content feedback, flow of content, topics that are missing, topics that are too basic, lab work that need retooling)

So long story short – we are listening and we will act. My overall hope is that the candidates in this current rotation and those attending future rotations will find the experience to be worthwhile and come away from this with actionable knowledge and a career-long technical community (not to mention distinguishing themselves in the community). In the meantime, we’ll keep pushing.

ALTER INDEX ALL REBUILD behavior

Joseph Sack — Tue, 09 Mar 2010 16:10:13 GMT

I was asked a few questions about the behavior of using “ALTER INDEX ALL… REBUILD” and thought I would detail my answers here… Quick background - using the ALL argument with ALTER INDEX REBUILD instead of naming a specific index allows you to specify all indexes associated with the table.

For example – in the below command I’m rebuilding all indexes for the HumanResources.Department table:

ALTER INDEX ALL ON HumanResources.Department REBUILD;

So I received a few questions on this topic which I’ll detail here – along with the query I used to “prove” out the answers (and if you find varying results in your own testing, I would like to hear about it):

Question: When using ALTER INDEX ALL – are all indexes rebuilt at the same time?

Answer: No. Although individual index rebuilds can use parallelism, each rebuild is executed in a serial fashion.

Question: If I have a heap – does that get included too?

Answer: No. Only the clustered index (if exists) and associated nonclustered indexes get rebuilt. Heaps are ignored.

Question: In what order are the indexes rebuilt?

Answer: I tested this out using a query that I’ll show at the end of this blog post. The order is by index_id (for example – 1,2,3,4) – which is then clustered index first, followed by nonclustered indexes. Since the clustered index is always “1” – this reinforces this behavior. I only saw one exception to this rule, where the indexes were so small that they both appeared to kick off at 2010-03-09 09:52:53.230 – so index id “2” appeared before “1” – but I believe this is a precision consideration and I think the clustered index started ever so slightly before the nonclustered index.

In order to generate the answers to these questions, I used ALTER INDEX ALL against most tables in the AdventureWorks2008 database and then used the following query to identify rebuild timings and associated index types:

SELECT OBJECT_NAME(u.object_id) objnm,

u.index_id,

i.name indnm,

i.type_desc,

u.system_scans,

u.last_system_scan

FROM sys.dm_db_index_usage_stats u

INNER JOIN sys.indexes i ON

u.object_id = i.object_id AND

u.index_id = i.index_id

WHERE last_system_scan IS NOT NULL

ORDER BY OBJECT_NAME(u.object_id), last_system_scan

The key ingredient to this query is the last_system_scan column from sys.dm_db_index_usage_stats which gets updated after an index rebuild operation. In my test I cleared the stats of this DMV by restarting the SQL Server instance (so if you are doing your own testing – needless to say please don’t do that in production).

If you find any exceptions to the rule on using ALTER INDEX ALL in your own testing, drop by a comment or email.

SQL MCM Prep Week posts by Brent Ozar

Joseph Sack — Wed, 10 Feb 2010 16:51:00 GMT

Brent Ozar, who is a Microsoft SQL Server expert for Quest Software, Microsoft MVP, Editor-in-Chief at SQLServerPedia.com, and coauthor of Professional SQL Server 2008 Internals and Troubleshooting - has written a series of posts related to his SQL MCM training preparation. His posts to date include:

What is a Microsoft Certified Master?
Microsoft Exams 70-433 and 70-451 ("candid" feedback on one of our prerequisites for SQL MCM)
Interview with Joe Sack

I believe he has two more days of posts on this subject, so watch this space for additional observations and opinions about the SQL MCM program.

DBA 101: Collecting and Interpreting Failover Cluster Logs

Joseph Sack — Tue, 09 Feb 2010 18:16:00 GMT

When an unexplained outage occurs on a standalone server and you need to determine root cause, DBAs usually start with the collection and analysis of the following “artifacts”:

Event Log (Application, System)
SQL Error Logs
Perfmon stats during the timeframe of the outage (assumes collection either via counter log or another external tool like Ops Mgr)

If the outage was on a Failover Cluster, you’ll also want to grab the cluster log. How and where you retrieve the cluster log depends on the operating system:

For Windows Server 2003, you can find the cluster log under C:\Windows\Cluster (located under %systemroot%\cluster).
For Windows Server 2008, the overall method of event collection is via Event Tracing for Windows (ETW). The Windows Server 2003 “Cluster.log” no longer exists in Windows Server 2008. Instead, cluster events are now logged to an Event Trace Session File (.etl format).

There are a few ways to capture Failover Cluster activity in Windows Server 2008 – but for the scope of this blog post, I’ll focus on how to generate a cluster log similar to what you may have seen in Windows Server 2003. The cluster log(s) can be generated by the CLUSTER.EXE command. For example:

CLUSTER.EXE YourClusterName LOG /GEN /COPY:"C:\Temp\cluster.log"

The below figure shows an example of executing this command with administrator rights on one node of a two node Windows Server 2008 Failover Cluster:

Looking in the C:\Temp directory I see a new cluster.log folder and see two generated cluster log files (one file for each node):

For a more detailed description of cluster log generation in Windows Server 2008, along with some logging command option details, check out “How to create the cluster.log in Windows Server 2008 Failover Clustering” by Steven Ekren, Senior Program Manager of Clustering & High Availability.

Opening up a cluster log for the first time, your impression may be that the log is both cryptic and verbose. Similar to reading a SQL Error Log for the first time, your comfort level and ability will grow with experience and practice. Here are a few tips to help start you out on getting the most out of your cluster log analysis:

· As with Windows event logs – you’ll want to narrow down the time period where the Failover Cluster issue or outage was thought to have occurred.

· Time is logged in GMT format. Keep this in mind when narrowing down the time range of your Failover Cluster outage.

· Beware of downstream error messages (more recent error messages from a specific event). Try to find the first occurrence of an error or warning that preceded the event. For example, if a disk resource became unavailable, you might see an initial error regarding the disk resource – followed by several other errors from cluster resources that are dependent on the offline disk resource. Your task is to work your way back to the original event and not get thrown off track by downstream events (focus on cause – and not downstream effects).

· The default cluster logging level is “3” on a possible range between 1 through 5 (5 being the most verbose and highest in overhead). Level 3 translates to a logging of errors, warnings, and info details. You can find your logging level on a Windows Server 2008 Failover Cluster log by searching for the text “cluster service logging level”. For example:

00000ccc.0000ca4::2009/10/17-19:20:41.265 INFO [CS] cluster service logging level is 3

· Within the cluster log you’ll find the log entry category on each logged line after the GMT timestamp – showing INFO, WARN, and ERR. Similar to a Windows application or system event log – your attention should be focused on WARN and ERR to start with.

· If your issue has resulted in several node reboots– you may have lost the necessary data needed to determine root cause. A new ETL file is generated each time a node is rebooted and the cluster log history is kept (by default) for up to five reboots.

Here is a full description of what you can expect to see in a single log entry. The format is the same across entries – and I’ll deconstruct a single color-coded log event entry below:

00000df4.00000c04::2009/10/17-19:20:47.093 ERR [RHS] RhsCall::Perform_NativeEH: ERROR_MOD_NOT_FOUND(126)' because of 'Unable to load resource DLL mqclus.dll.

00000df4 Hex format of the PID that logged the process.
00000c04 Hex thread ID of the thread that logged the entry.
2009/10/17-19:20:47.093 Date and timestamp in GMT format
ERR Entry type (informational (INFO), warning (WARN), or error(ERR))
[RHS] Component or Resource DLL identifier. In this example, RHS represents the Resource Host Subsystem which hosts all resources in the Failover Cluster (via rhs.exe executable). Other common components you’ll see in the log include the [NM] Network Manager, [RES] Resource DLL logging, [IM] Interface Manager, [GUM] Global Update Manager and more. From a practical standpoint, interpreting these various codes assumes a deeper understanding of the underlying Windows Failover Clustering architecture. Don’t worry too much if you do not have this background - the error message found after this identifier will likely be more helpful to you.
RhsCall::Perform_NativeEH: ERROR_MOD_NOT_FOUND(126)' because of 'Unable to load resource DLL mqclus.dll. Event description / error message. Can vary significantly, and is your top “pointer” to what was happening at the specific moment in time of your outage or event.

I’ll end this blog post with a summarized list of cluster log evaluation tactics:

1. Focus on the time range of the outage or issue.

2. Windows Application and System event logs can add useful information in conjunction with the cluster log.

3. Focus on ERR and WARN entry types

4. Sometimes the error messages are intuitively written and can point you in the right direction. You may not find the root cause directly – but the log can point you in the right direction. When not intuitive – see the next step.

5. Pattern match on captured error messages using Microsoft KB articles or other preferred forums. If you have purchase Microsoft support, the engineer will most certainly want to see the cluster log you have collected.

That’s all for today. Happy cluster log surfing!

Why run the SQL MCM marathon?

Joseph Sack — Thu, 21 Jan 2010 16:04:00 GMT

I’ve been a casual jogger for about 14 years, and lately I’ve been debating whether or not I should get serious and sign up for a marathon. In order to make my decision (still pending) I generated a list of some pros and cons. During that process I realized some of my concerns sounded very familiar to what I’ve heard from potential SQL MCM candidates I’ve spoken to in the past.

For example, marathons can be grueling, painful, humiliating, aggravating and risky. Marathons demand planning, preparation, and practice. After all of your training and preparation – you’re still subject to variables outside of your control (weather conditions, your health, and terrain).

In spite of these risks and challenges – people still run marathons. Motivating factors vary. You could be running for your favorite charity, trying to lose some weight, get fit, or celebrate your recovery from an illness. A few of my friends run marathons just because they can. Basically they are motivated by the achievement itself – the “I did this” factor.

I realize too that like marathons, the SQL MCM program involves several risks and challenges. There are plenty of “uncertified Masters” out in the field that may never pursue this certification due to any one of the following reasons:

· It is expensive.

o Why so expensive? Some of the cost is due to operational and infrastructure overhead (lab equipment, rooms, content, planning, exam retakes, admin time, community management) and also because we get some of the top SQL trainers in the world to spend three weeks with our candidates – trainers such as Paul Randal, Kimberly Tripp, Adam Machanic, and Greg Low. There are also “opportunity costs” – the money you will not make when you’re attending the training (but ideally your certification will help bolster your earning potential on the back end). There are also travel costs – as the training is conducted on the Microsoft, Redmond campus.

· It is time consuming.

o Why is the training three weeks long? The timeframe is mostly defined by the vast amount of information that must be covered. We experimented with four weeks – but that was just too long (early rotations ran for four weeks). Two weeks is too short to contain the required material. Having one contiguous block of time also builds the “esprit de corps” – ideally leaving you with colleagues you can keep in touch with for years to come.

· It is hard.

o Why so hard? Successful candidates must pass three exams and one six hour qualification lab. This is the most advanced technical certification available at Microsoft – and we’re looking to validate and certify SQL Server professionals who can successfully design, deploy and implement solutions that meet the most complex business requirements. We go deep on the product – and then expect you to come up for air and really understand how all the parts fit together.

So with all of these challenges – why would one “run” in the SQL MCM marathon?

There are several benefits I can think of – but for now I’ll boil it down to three benefits that I personally think are most important:

· The Experience

o Three weeks of incredible training. When else would you get an excuse to completely submerge yourself in deep technical content for an extended period of time, in the heart of the Microsoft campus, surrounded with likeminded individuals?

· The People

o The networking opportunities and community building is absolutely incredible. You’ll meet new friends, amazing trainers, and exposure to Microsoft product and dev team members.

· The Validation

o Achieving SQL MCM means that you’ve successfully made it through an extraordinary challenge. You’ve entered a small community (66 people as of January 21, 2010) that you can go to for support and advice. Most importantly, this certification will allow you to distinguish yourself in today’s job market.

So if you’re considering the SQL MCM challenge – be sure to explicitly define which factors will motivate you to cross the finish line. The ongoing value of this certification will continue to evolve – but earning this certification is an achievement that you can always be proud of and is one that can never be taken away from you.

Gaps in Database ID sequence – one reason why this can happen

Joseph Sack — Mon, 28 Dec 2009 18:51:00 GMT

I spoke with a DBA today about the following scenario… When running DBCC CHECKDB in SQL Server 2005, he noticed that there were no entries for the hidden database snapshot in sys.databases; however he did see that a database_id value was shown when querying sys.dm_exec_requests (for the request running the DBCC command - WHERE command = 'DBCC TABLE CHECK'). For that request, the database_id had a value of “9” – which was N+1 of the maximum database value (where N = the maximum database_id in sys.databases).

His question to me then was – what happens if he creates a new database while the DBCC CHECKDB is still executing? Will it try to use the hidden value of “9”? Will it error out? Will it use a higher number?

I ran a quick test against AdventureWorks2008 and confirmed that indeed – the hidden snapshot database is not visible in sys.databases. I also confirmed that sys.dm_exec_requests shows a higher value for database_id based on the maximum database_id currently in use (so N+1).

Lastly, I created a new database concurrently with a DBCC CHECKDB, and sure enough – the resulting database_id was N+2 (or N+1 if you count the hidden snapshot). Specifically, my maximum database id was for AdventureWorks2008, and had a database_id value of “8”. A value of “9” was used for the DBCC CHECKDB hidden snapshot. A value of “10” was then used for my new database.

So if you ever see gaps in the sequence of database ids, this is one reason why this could happen. As a side note – he also asked if this could cause problems if an application depended on a specific database id. My reply was that an application should not be written to depend on a specific id. Such hard-coding and dependencies will surely cause issues down the road (for example, if you ever plan on migrating your database to a new SQL Server instance with a differing number of databases and IDs).

Updated SQL 2008 MCM Pre-reading list

Joseph Sack — Mon, 07 Dec 2009 22:55:00 GMT

David Ikeda and I have updated the SQL Server 2008 MCM Pre-reading list based on the last training rotation we conducted last October. You can download the PDF here.

As a seasoned SQL Server professional, ideally you'll already have read some, if not most of the material listed in this document. If not, you'll find that there is quite a bit of material you’ll need to read. Nonetheless I strongly recommend that anyone planning on attending the SQL MCM program review all of the material. Doing so will help prepare you for the three week whirlwind of information and testing.

** Also as a tip - we've noticed (and heard from previous rotations) that students tend to avoid pre-reading material for the XML, SQL Client, SQL CLR, and Service Broker topics. My recommendation is to not avoid reviewing this material. If this is not your comfort area, I suggest you hit these topics first, and make sure you have reviewed it thoroughly prior to training. If you do, you'll thank us once Week 2 of the training rolls around... **