Monthly Archives: November 2009

How I Use GTD 50,000 Foot Goals

David Allen’s productivity book, Getting Things Done, has made me a better person.

Getting Things Done

After using its methods for years, I’m even more convinced it can help anybody in IT. Today, I’m going to explain how I worked before GTD, and how I work with it now.

How I Handled Tasks Before GTD

Years ago, I tracked my to-do list in a text file. I broke the file up into two sections, Work and Home. My Work section looked something like this:

ASAP:
 - Make nightly sales import job faster
 - Help Sharepoint team install new SQL cluster

ASAP Done:
 + Fix the backups on SQLPROD1

Other:
 - Call Microsoft to schedule data warehouse health check
 - Test email notification setups on all servers
 - Find out why SQL users can't log in when any DC reboots
 - Move remaining applications off SQL 2000

Other Done:
 + Install maintenance scripts on new 3rd party vendor server
 + Retrofit IBM x346s with remote administration card
 + Inventory RSA IP addresses

Everything I did was tactical – it was all about putting out fires. I arranged tasks in order of priority, with the first tasks being the most urgent. When someone walked into my cube with a new task, I would open my text file and say, “Where does this new task fit in with my priorities?” We would agree upon a position, and I’d add their task to my list.

I used this same approach with all incoming tasks – whether email, phone calls, or in person. Sometimes I’d send my actual text file, and sometimes I’d just walk them through it verbally over the phone. Upon seeing my task list, more often than not their eyes would bug out and they’d shake their heads. “Sorry – I need it fast, but it looks like you’re pretty busy. I’ll go bother someone else,” they’d say. That worked really well for me!

As I completed tasks, I changed the minus sign to a plus and moved it to the top of the “Done” section. Every now and then, when my manager asked what I’d been up to lately, I could whip out my trusty text file and copy/paste the appropriate sections into an email.

While that helped keep unimportant new tasks out of my way, it didn’t stop me from spending my time firefighting. My tasks were determined by what other people wanted from me. There’s nothing wrong with pleasing your customers, but – well, actually, there is something wrong with it. It’s the cliched movie character who focuses on making everybody else happy, yet never stops to take care of themselves.

GTD and the Big Picture

Unlike productivity methods like Franklin Covey that revolve around your ability to shell out big bucks for paper day planners, GTD is system-agnostic. If you’d like to track your GTD tasks with a text file, and if it works for you, that’s totally fine. There’s a bunch of ways you can accomplish GTD.

GTD wants you to get from Point A to Point B. It doesn’t dwell on whether you drive a Maserati, take a bus, or hoof it. Instead, it just focuses you on Point B.

GTD separates the means from the end, because you’ve probably lost focus on the end already.  GTD asks you to build a set of 50,000 foot goals for yourself – things that you ultimately want to accomplish before you die. Preferably, long then. The best way to illustrate it is to share my own 50k goals, which aren’t in any particular order because they’re all equally important:

  • Be very financially secure.
  • Be a fantastic partner for Erika.
  • Enjoy life while I can.
  • Be strong and healthy.
  • Be a good son and brother.
  • Be a good Catholic.

The more ambitious GTD practitioners create a set of 40,000 foot goals that map up to the 50k ones, and then a set of 30k foot goals. For example, under my “Be very financially secure” goal, my 40k foot goals are:

  • Increase my income. These are tasks that, when performed, result in money in my bank account shortly thereafter. 30k goals under here might include taking on side consulting work, but during the day, being a good employee matters here. I have to make my managers and internal customers happy if I want to get a raise. If I was really anal, I could map out a set of 20k goals like keeping my manager updated, staying abreast of the company’s overall goals, and so forth, but I’m not quite that serious about GTD (yet).
  • Increase my marketability. These are tasks that might not pay off tomorrow, but they have a high likelihood of putting me in a good position down the road. Blogging falls into this category, as does helping other people.
  • Reduce my expenses. Budgeting, rereading Dave Ramsey’s books, avoiding gadget purchases, and improving my credit rating all come into play here.
  • Improve my financial position. Tasks here might include increasing my savings, being properly insured, and meeting with a financial advisor.

That’s just the financial one. It might seem common-sense, but here’s where it gets interesting.

When I get a new idea or an assigned task, I line it up to one (or more) of these goals. If something doesn’t line up with these goals, it doesn’t get into my task list. If something conflicts with these goals, it doesn’t get into my task list. The rejected tasks are either delegated to someone else or politely refused.

That’s the theory, anyway.

Periodically Revisiting Goals and Tasks

In practice, I get distracted by bright shiny things. Some sexy new technology comes out, or I think somebody’s got a brilliant idea, or I don’t want to let somebody down, and next thing you know it I’ve got a couple of bad tasks in my list. I don’t realize it right away because I’m so enchanted with the idea itself, and I’m perfectly content to let it sit in the list.

I confuse “I can” with “I should.”

David Allen instructs GTD practitioners to periodically revisit their 50k goals and their task list to make sure everything lines up. I do this once per quarter on a long plane flight, and sure enough, I come up with things that have weaseled into my task list despite my best intentions. For example, this quarter I built a Twitter bot account to randomly spout off random jokes every couple of hours just because it was an interesting technical exercise. I don’t pull my hair out and wail about the time I put into it, but it’s just an example of how I got sidetracked by something fun. All work and no play does make Brent a dull guy, but it’s not like I don’t have enough fun stuff planned in the pipeline.

I use a hybrid of systems to manage my GTD goals and tasks. I track my 50k and 40k goals in a text file, and I keep my day-to-day tactical to-dos in RememberTheMilk.com. RememberTheMilk’s Pro level ($25/year) has a slick iPhone app that syncs over the air, and it works when disconnected too, like on planes. I can review my tasks on the plane, make changes to them, and RTM automatically syncs them when I’m back on the ground.

To do my quarterly review, I pull up my 50k/40k text file and save it with a new name for the new quarter, like GTD2009Q4.txt. I review these big-picture goals and make very small tweaks where necessary. After a few quarters, I’m surprised that these still adapt and evolve over time as I learn more about myself and what I want from life. After updating my goals, I leave those up on the screen and get out the iPhone. I scan through my tasks in RememberTheMilk and make sure they each line up to one of my 50k/40k goals, and that they’re in the right priority order.

I only prioritize things that I think I’m going to accomplish in the next couple of weeks. Generally I find that I get enough incoming fires to keep me pretty busy, and I don’t need to prioritize more than a dozen or so tasks at a time. The rest just stay in a murky pool at the bottom – I’ll get to ‘em when I’ll get to ‘em.

The Result: Peaceful Focus and Productivity

When I step off the plane, I know where I’m going – and I don’t mean baggage claim. I know what I need to do, and I’m perfectly comfortable with the volume of tasks. I know I can only accomplish a limited amount of things in the time I have each day, and I believe I’ve made the best choices possible given my long-term priorities.

GTD helps me to do a better job of saying no. I’m acutely aware of the opportunity cost of taking on new tasks, and I know what’s most important to me. For example, I can’t take on more work if it means spending less time with the ones I love, or if it causes me to sacrifice my health. Because I’m freshly aware of my big-picture goals, it’s that much easier for me to explain to myself why I wouldn’t take on a particular task.

GTD helps me to do a better job of saying yes, too. When something is truly important and lines up with my priorities, I’m more able to push things around in order to accomplish it. When someone comes up with an emergency task, I can do a much better job of being the guy to fix it, because I know exactly what I have to push aside in order to do it. For example, I can more easily say, “I’ll help you fix this, but I need you to get Sam to handle these three tasks for me right now.” When managers see someone so on top of their priority lists and make great tradeoff decisions, they’re more likely to give you the backup you need. If you just say, “But boss, I don’t want to work late again!” they’re more likely to tell you to suck it up.

I don’t think I’m a better employee due to Getting Things Done, but overall, I’m a much better person.

I’m certainly not done with my 50k tasks – I have one hell of a lot of work to do. However, every time I do a quarterly review, I’m pleasantly happy with any progress I’ve made. After all, before GTD, I didn’t even SEE those 50k goals!

Brent Ozar

Brent specializes in performance tuning for SQL Server, VMware, and storage. He's one of the very few Microsoft Certified Masters of SQL Server, a published author, and a Microsoft MVP. He likes travel, Jeeps, Apple gear, jokes, and writing about himself in the third person. Read more and contact Brent.

More Posts - Website

Follow Me:
TwitterFacebookLinkedInGoogle PlusYouTube

WorkTamer Conference in Vancouver January 2010

I’m comin’ to Canada!

Michael Lato of Datatamer put together a really neat one-day conference that focuses on the soft skills side of IT.  These presentations will help you get IT-related skills you need to get ahead in your career.

Here’s my presentations:

Inbox Zero: The Art of Email

No Photoshop Involved

No Photoshop Involved

If you’re using Outlook rules to filter server alert emails out of your inbox, or if you can’t even count the number of unread emails you’ve got, you’re not going to advance your career. Bosses notice who’s really on top of their email and they see it as a sign of a successful employee. I’ve taught dozens of IT staffers how to manage their priorities and inboxes better, and I can help you too.

In this session I’ll teach lessons on:

  • How to ruthlessly prioritize your tasks
  • How to triage your inbox
  • How to do it all without offending your boss and your customers

How to Get Promoted and Dump Your Pager

IT jobs can be a never-ending on-call cycle of racing into the office, fixing broken servers, and aborted vacations.  If you’re tired of having an electronic device handcuffed to your belt, there’s another way.  Learn several routes to a better career including consulting, management, and writing.

I’ve tried each of these routes, and I’ll talk about the pros and cons of each approach. I’ll share my proven methods to get ahead on each career track and get you started on your way.  After a decade of being on call 24/7, I’ve finally broken the cycle, and I know many others who’ve pulled it off too.  There’s hope!

0 to Blogger in One Hour

Blogging is your shortcut to the top of the resume stack. It differentiates you from the hundreds of other people out there who will beat you out for the very best jobs and highest billable rates.

In this session, I will spend 5-10 minutes showing you why it’s important to blog, and then focus on the mechanics of building a blog. By the end of the session, you’ll know exactly how to get started blogging and avoid many common pitfalls.

You can register for the WorkTamer conference in Vancouver, and we’ve got a couple more Canadian cities coming up too!

Brent Ozar

Brent specializes in performance tuning for SQL Server, VMware, and storage. He's one of the very few Microsoft Certified Masters of SQL Server, a published author, and a Microsoft MVP. He likes travel, Jeeps, Apple gear, jokes, and writing about himself in the third person. Read more and contact Brent.

More Posts - Website

Follow Me:
TwitterFacebookLinkedInGoogle PlusYouTube

Microsoft MVPs: NDA + Twitter = Yammer

Every year, Microsoft MVPs gather for a summit where they talk about <NDA> and <NDA>.  Just like any other conference, they want to talk about this stuff over social networking sites like Twitter, but they can’t because it’s NDA.

There’s an app for that.

Yammer

Yammer

Yammer is a free private version of Twitter.  You sign up with your company’s email address, and you can only see tweets (yams?) from people with that same domain name.  For example, us Questies use it to communicate about internal projects we’re working on. It’s not perfect – it doesn’t work with Twitter clients, for example, but at least there’s Windows desktop clients and even an iPhone client.

Wouldn’t it be cool if every MVP had an account on a specific domain (like, say, MVPs.org) and then we all used Yammer to communicate during the MVP summit (and beyond)?  Here’s one way someone could make happen:

  1. Set up an email server with Active Directory accounts for each MVP
  2. Do some kind of screen-scraping to find out when MVP profiles disappear, and then revoke the associated user’s AD account
  3. Profit!  Okay, not really, but you’d endear yourself to a lot of very high-profile IT people, and maybe get consulting gigs out of it, so possibly profit.

I’m throwin’ it out there because I’m an idea guy.  (Translation: I have no Exchange or screen-scraping skills.)

Brent Ozar

Brent specializes in performance tuning for SQL Server, VMware, and storage. He's one of the very few Microsoft Certified Masters of SQL Server, a published author, and a Microsoft MVP. He likes travel, Jeeps, Apple gear, jokes, and writing about himself in the third person. Read more and contact Brent.

More Posts - Website

Follow Me:
TwitterFacebookLinkedInGoogle PlusYouTube

Jamie Thomson’s whistlestop tour of SSIS addins

At #SQLbits, Jamie Thomson (Blog – Twitter) demoed the following SSIS add-ins:

  • Kimball SDC
  • Trace File Source
  • XMLify
  • File Watcher
  • Dynamic Data Flow
  • Rank Transform
  • Normaliser
  • Twitter
  • Compression

XMLify

Jamie find this useful when parsing error output from other SSIS steps, which often have several result sets with different columns.  XMLify dumps all of the data into a single XML file for easier error storage.

It’s currently only available for SQL 2005 on Codeplex, but Jamie’s updated it for 2008.  He expects to upload it to Codeplex shortly.

Kimball SDC

SSIS includes a Slowly Changing Dimensions wizard, but Jamie says it has extremely poor performance due to the amount of lookups it does, plus you can’t modify your work.  You have to reuse the wizard.  One workaround is the KimballSCD tool on Codeplex, which handles Type 1 and Type 2 dimensions.  These dimensions track the history of our data – for example, as our customers change from Single to Married to Divorced, we may want to capture those changes so we can view their status over time.

The KimballSCD component takes your incoming stream of data and dumps out separate streams with new records, changed records, deleted records, invalid input, and so forth.  It has a staggering array of configuration choices on how to handle errors.

Twitter Task

Built by Andy Leonard and Jessica Moss, this SSIS component can fetch and retrieve tweets.  Of course, when he went to demo Twitter.com, the page took forever to load.  Ah, the curse of using Twitter and WiFi networks for demos!

He demoed how to send tweets, then how to receive them and pipe the results through the Term Extraction task.  It’s basically data mining for text – it finds the most popular phrases in the stream of tweets.  Jamie asked if attendees found it interesting, and they definitely did.  A couple of attendees wanted to know when Twitter search integration would be included.  Doh!

Normaliser

This component takes a flat source like a list of orders that includes both header and detail info in the same row, and then normalizes it.  You have to pass in the data in the right sort number, because it uses those sorts to determine duplicates.  You pick which fields determine a header versus a detail record using checkboxes.

Rank Transform

The ranking T-SQL functions in SQL 2005 will take a set of data and add a rank column, and this SSIS component does the same thing.  Jamie says it’s still a little buggy, but he’ll work out the bugs in the next few days.  (Honesty is the best policy!)  The bugs are problems with the UI – checkboxes don’t show up as checked when they’re supposed to be.  The data under the covers works though.

It has nice options for rank, dense rank, row number, and row number by partition.  Rank and dense rank are two different ways to handle ties.

Compression Task

Pass in a file – but only one file at a time – and this task will gzip it.  It works for SQL 2005, and there’s a newer version coming on Codeplex to work in SQL Server 2008′s SSIS.  There’s an identical task to unzip.  I can see this as being useful for passing big XML files over networks.

File Watcher

This task watches a directory and waits for files to arrive.  When a file is created, it goes on to the next task in the package.  The File Watcher passes the name of the newly created file to the next task as well, so you can process that file.

Trace Reader

This takes a SQL Server trace file as a source, and then splits it out into three data outputs: short queries that took under 300ms, queries that took 300-1,000ms, and queries that took over a second.  Useful for DBAs who want to automate performance tuning on a large scale.

Dynamic Data Flow

The only non-free tool in the demo, Dynamic Data Flow is by a company called CozyRoc.  You can use one destination in SSIS, but populate different tables based on the contents of your data.

I bailed out just before the last task was shown because I had to get to my own session.

End of the Line

Jamie did an awesome job of demoing a lot of stuff in a short period of time without being overwhelming.  I can really appreciate how much work he had to put into these demos – every single component needed its own set of data.  Fantastic work.

Brent Ozar

Brent specializes in performance tuning for SQL Server, VMware, and storage. He's one of the very few Microsoft Certified Masters of SQL Server, a published author, and a Microsoft MVP. He likes travel, Jeeps, Apple gear, jokes, and writing about himself in the third person. Read more and contact Brent.

More Posts - Website

Follow Me:
TwitterFacebookLinkedInGoogle PlusYouTube

Simon Sabin’s talk on query performance

At #SQLbits, Simon Sabin (BlogTwitter) talked about car crash queries: queries that suddenly have bad performance out of nowhere, yet if you copy/paste them into SQL Server Management Studio, they run quickly.  The culprit is often parameter sniffing: the engine looks at the query and builds a plan, but if the parameters in the WHERE clause (or joins or whatever) change that plan may not work right anymore.

Simon Sabin onstage at SQLBits

Simon Sabin onstage at SQLBits

Simon likened it to getting walking directions.  If you ask for directions from Big Ben to the Millennium Wheel, your best option is walking.  But if you decide to go from Big Ben to the Eiffel Tower,walking is no longer the best option – because the statistics of the distance you’re trying to cover is suddenly different.  SQL Server’s statistics about the quantity and distribution of the data in your database help it determine how to build the best directions.

Scaling up to higher numbers of users also changes how queries perform.  If one query returns in 250 milliseconds, that doesn’t mean that 100 of those queries running simultaneously may not all finish in 250 milliseconds each.  He likened it to a grocery checkout line – the more people you put in line, the more the folks at the end of the line are going to have to wait.

A great example for parameter sniffing troubles is when the same query is run in both a stored procedure, and standalone.  It may perform poorly inside the stored proc, but when the DBA tries to troubleshoot it, it performs fine.  The two queries are using different execution plans.  Simon demoed how to compare the two plans, and how to find out why the bad plan isn’t optimal.

One way to get better plans is to get better statistics, and you have to do better than SQL Server’s default statistics.  SQL creates its own statistics for new tables, but they don’t work terribly well.  If you’re using table variables, those don’t get statistics at all, but on the plus side, they won’t cause recompiles when the data distributions change.  Temp tables, on the other hand, get statistics, but they incur recompiles when SQL Server sees that the temp table contents have changed dramatically and need statistics updates.

He demoed how SQL Server will create different query plans based on the exact text of the query.  If one query includes comments and another doesn’t, they can end up with different query plans and different performance.

In order to catch these problems, you have to monitor and baseline your application, then identify changes in reads, writes, and CPU time.  You don’t want to monitor duration, because you won’t catch things until it’s too late.

Brent Ozar

Brent specializes in performance tuning for SQL Server, VMware, and storage. He's one of the very few Microsoft Certified Masters of SQL Server, a published author, and a Microsoft MVP. He likes travel, Jeeps, Apple gear, jokes, and writing about himself in the third person. Read more and contact Brent.

More Posts - Website

Follow Me:
TwitterFacebookLinkedInGoogle PlusYouTube

Christian Bolton on SQL Server memory

At #SQLBits, I was quite excited to attend this session because of the presenter.  Christian Bolton of COEO (BlogTwitter) was the head author on our upcoming book SQL Server 2008 Internals and Troubleshooting, and he’s one of the only 8 Microsoft Certified Masters on SQL Server that work outside of Microsoft.

Physical vs Virtual Memory

Physical memory means the fast, volatile storage of the chips themselves.  We’re finding 64GB is very common these days.  If every app tried to access this memory directly, we’d run into performance problems quickly.  That’s why virtual address space came about.  On 32-bit OSs, the default settings leave 2GB for the kernel and 2GB for applications.  On 64-bit OSs, that breaks out to up to 8TB of kernel memory and 8TB of application (user mode) space.

All applications just work with virtual address space first – they don’t know whether they’re working with physical memory or the page file.  The allocation of memory to either chips or the page file is the job of the Virtual Memory Manager.

What Uses SQL’s Virtual Memory

The Buffer Pool is SQL’s main memory consumer, and that’s managed by the min and max memory settings in SQL Server.

Another consumer is VirtualAlloc – which just means anything that doesn’t use the buffer pool.  The heap manager, thread stacks, and multi-page allocations (things that need more than 1 page to store data).  For example, execution plans are normally quite small, but they can exceed one page, and we need to store them in memory.  Linked servers and extended stored procs also use VirtualAlloc to grab memory.

The buffer pool will never be bigger than physical memory.  If you’ve got 32GB of memory and a 64GB page file, your buffer pool won’t be bigger than 32GB.  On an x86 box, this can be problematic since you won’t have more than 2GB of user mode space by default.  Because there’s not much memory, SQL Server has to reserve some memory before the buffer pool grabs hold of the rest, and that’s called memtoleave.  It’s calculated with MemReserved + (NumThreads * StackSize).  By default, MemReserved is 256mb.  NumThreads is the max number of worker threads configured.  StackSize is .5mb on x86, 2mb on x64.  Christian had an excellent visual demo showing how the memory gets used up on different CPUs and memory amounts – I can’t begin to convey that here, but hey, that’s where his great memory chapter in the book comes in.

Christian demoed the RMLUtils to stress test SQL Server and affect the memtoleave space.  He asked how many attendees were using wait stats for performance analysis, and the number was about the same as my presentation’s attendees – around 10%.  Folks just aren’t using wait stats yet.  But if you turn around and ask how many of those 10% use it as their primary troubleshooting tool, it’s usually 100%.  Folks who use them, love them, as I’ve blogged before about wait stats.

SQL Server’s Memory Model

Memory nodes are the lowest level allocator, and you can view info about them in sys.dm_os_memory_nodes.

Memory clerks are the next level, and they’re used to access nodes.  When something in SQL wants memory, they ask the clerks, and the clerks allocate nodes.  There’s a general MEMORYCLERK_SQLGENERAL, and then heavy memory users get their own clerks, like MEMORY_CLERK_SQLBUFFERPOOL and MEMORYCLERK_SQLQUERYPLAN. You can check what they’re using with the DMV sys.dm_os_memory_clerks.

He talked about the different kinds of caches, and you can query them in sys.dm_os_memory_cache_counters.  He touched on how the plan cache has hard-coded maximum sizes based on your memory size – you don’t want plans running you out of buffer cache.  When you throw in the buffer pool and query memory too (for joins/hashes), it’s easy to see how SQL Server really needs all the memory it can possibly get.

Christian tied it all together by showing a slick DMV query that breaks out cache space usage by database.  I glanced through his blog but I couldn’t find it – I’ll hit him up for that later.

Best Practice: Lock Pages in Memory

Locking pages in memory makes sure SQL Server’s memory is not paged out to disk.  Otherwise, Windows may push it out to disk – often with bad drivers.  The first thing to do when running into SQL’s memory getting swapped out is to check for updated drivers, then tweak down SQL’s max memory setting, then use AWE/lock pages in memory, then consider upgrading to Windows 2008.  Even if you have bad drivers, Win 2008 won’t be as aggressive with trimming SQL Server’s address space.  Memory allocated using the AWE mechanisms can’t be trimmed.  Should you lock pages in memory on 64-bit systems?  Yes, because it locks pages in the working set.  Only the data cache is locked, though – other memory like the plan cache can still be trimmed.

You can lock pages in memory on Standard Edition, but you need to get on the right patch levels – see this knowledge base article for details.

Christian asked how many DBAs set the max server memory, and I was surprised that the vast majority of DBAs haven’t.  I’m a big cheerleader for setting that.  Glenn Berry recently wrote a set of guidelines for max server memory.  Christian recommended leaving 2GB for the OS, plus the memory for the worker threads we calculated earlier, plus 1GB for the multi-page allocations/linked servers/etc, plus 1-3GB for other applications (if necessary.)  This is a worst-case-scenario guideline because we just want to make sure our server isn’t paging to disk.

Bottom line – I learned a lot from this session, and this one alone was worth the price of admission!

Brent Ozar

Brent specializes in performance tuning for SQL Server, VMware, and storage. He's one of the very few Microsoft Certified Masters of SQL Server, a published author, and a Microsoft MVP. He likes travel, Jeeps, Apple gear, jokes, and writing about himself in the third person. Read more and contact Brent.

More Posts - Website

Follow Me:
TwitterFacebookLinkedInGoogle PlusYouTube

Jasper Smith on SQL 2008 R2 DBA features

At SQLBits, MVP Jasper Smith of SQLDBATips.com held a session on what’s newin SQL Server 2008 R2 for database administrators.

Application and Multi-Server Management

To illustrate the problem with server sprawl, Jasper gave some quick stats about a big enterprise.  Microsoft IT has around 5,000 SQL Server instances with 100,000 databases, averaging CPU utilization under 10%.

To help solve this problem, SQL 2008 R2 introduced Data-Tier Applications (DAC packs) as a new unit of deployment for T-SQL apps.  They contain developer intent as policies – for example, the developers may want high availability, 2 CPUs and 2 gigs of memory.  Of course, this can present problems down the road – developers will always want more resources.  Jasper demoed the SQL Server Control Point.

SQL Server Connection Director & Connection Plans

Right now, SQL Server connection strings are tied to the server name and database name.  It’s not easy for DBAs to move heavily utilized databases to a more powerful server because we have to touch every client.  The problem is made worse because over time, we need to continuously consolidate databases together as older databases seem to stay on old SQL Server versions.  Take SQL Server 2000 – many of us are still managing a handful of 7 or 2000 servers because a handful of apps just won’t work on newer versions.  We need to combine these old things onto the minimum number of servers possible, but database moves are painful.

There’s help coming in the long term, but not in SQL Server 2008 R2.  The CTPs of SQL Server and Visual Studio for next year’s release contain a feature called the Connection Director, but Microsoft has already said Connection Director won’t be included in the RTM builds.  Bummer!

The new declarative connection plans will let us specify connection strings that lives in Active Directory or on a file share as shown in Books Online.  We can specify a cache refresh interval much like DNS time-to-live parameters – we would put a long cache refresh interval in the plan normally, and then turn that time down shorter when we plan to do maintenance.  Jasper demoed doing exactly this, changing the plan while running an app that regularly connects to SQL.

Me likey!  Can’t wait for that to be introduced.

Brent Ozar

Brent specializes in performance tuning for SQL Server, VMware, and storage. He's one of the very few Microsoft Certified Masters of SQL Server, a published author, and a Microsoft MVP. He likes travel, Jeeps, Apple gear, jokes, and writing about himself in the third person. Read more and contact Brent.

More Posts - Website

Follow Me:
TwitterFacebookLinkedInGoogle PlusYouTube

Donald Farmer #SQLBits keynote on PowerPivot

At SQLbits, Donald Farmer (@DonaldDotFarmer) talked about IT in terms of data, and did it with an interesting picture story from the early 20th century.  Data today isn’t all that different from the messy desktops and clean library card catalogs of the last century.

Donald Farmer onstage at SQLbits

Donald Farmer onstage at SQLbits

IT professionals still break into the same 3 roles that existed a century ago:

  • Acquiring and storing data
  • Validating the data quality
  • Managing calculations on the data

Our roles, though, conflict with those of analysts – power users who want to do this work themselves.

Letting People Build Something Cool

Analysts do their slicing and dicing in our “approved” tools, but when they bang up against the ceilings of our capabilities, what do they do?  Universally, they export to Excel, and then start doing crazy things in this radically familiar environment.

To build the future of BI, Microsoft is delivering PowerPivot as an optional add-in for Excel 2010.  Excel 2010 installs side-by-side with older versions, so users can install it without violating corporate policies on using older Excels.  Farmer demoed PowerPivot with a >100 million row table using his laptop, sorting & dicing it in real time.  This demo still hasn’t gotten old for me yet – I love seeing that.  Since PowerPivot is a column-oriented data storage mechanism, it performs really well in memory.  It still takes a long time to get 100 million rows into PowerPivot (over the network, for example) but once it’s there, it flies.

PowerPivot data is read-only, though – once it’s been pulled in and compressed into column-based storage, it’s pretty much stuck.  If you need to make modifications, the easiest way is to use linked tables, and modify the linked ones.  Farmer demoed how to create relationships with those tables, and how PowerPivot helps detect which relationships don’t violate integrity rules.

To get that data into PowerPivot, one easy way is to use RSS. Business users can click the RSS icon on new SSRS reports, and Excel will open the feed to create a PowerPivot document.  From there, users can easily do their own slicing & dicing.

When we look at Excel spreadsheets from analysts, often the front couple of sheets are nicely formatted reports.  The rest of the sheets are various intermediate pivot tables and aggregates hitting other sheets of source tabs.  Excel’s new formulas in PowerPivot help you get those well-formatted aggregates faster with less intermediate steps.

I Made Something Cool! Now What?

Once users have built something cool in Excel, they didn’t have a solid way to share that with multiple users.  Farmer says SharePoint 2010 is like the ultimate file share for Excel because:

  • IT knows the files are backed up
  • IT knows who’s building the reports
  • IT knows who’s accessing the files (which means we can tell which reports we may need to take over)
  • People can consume the data without actually having PowerPivot or even Excel – it just works in the web

PowerPivot spreadsheets with slicers look fantastic in SharePoint.  I can envision people building their own report portals without BI team help.  There’s still a question of where the data’s coming from and how accurate it is, but that’s the case no matter how reports are delivered anyway.

Farmer says that this new self-service BI doesn’t interfere with real BI teams – you still need real BI in order to get analytics.  The self-service BI delivers the answers to the “background noise” of analytics requests – small, quick-hit requests that never end.  Farmer says self-service BI isn’t for the big projects, and the big tools aren’t right for self-servie needs either.

To try out PowerPivot, hit:

Brent Ozar

Brent specializes in performance tuning for SQL Server, VMware, and storage. He's one of the very few Microsoft Certified Masters of SQL Server, a published author, and a Microsoft MVP. He likes travel, Jeeps, Apple gear, jokes, and writing about himself in the third person. Read more and contact Brent.

More Posts - Website

Follow Me:
TwitterFacebookLinkedInGoogle PlusYouTube

Google Chrome OS vs iPhone: Handy Comparison

Feature Chrome OS iPhone
Runs apps Nope Yep
Local MP3 and movie storage Nope Yep
Have to buy it on a device Yep Yep

Right.  Because I’ve been really looking for a netbook with even less capabilities than my iPhone.

Brent Ozar

Brent specializes in performance tuning for SQL Server, VMware, and storage. He's one of the very few Microsoft Certified Masters of SQL Server, a published author, and a Microsoft MVP. He likes travel, Jeeps, Apple gear, jokes, and writing about himself in the third person. Read more and contact Brent.

More Posts - Website

Follow Me:
TwitterFacebookLinkedInGoogle PlusYouTube

SQL Server Events in Chicago

I’m jumping into the Chicago SQL Server community with both feet as soon as I get back.  I’m speaking at the next ChiPASS meeting on Thursday, December 10th in downtown Chicago.  I’ll be presenting the same Disaster Recovery 101 presentation I gave at the PASS Summit.  You must RSVP if you plan to attend, because they have to put you on the security list to get into the building.  And no, that has nothing to do with the fact that I’m presenting, ha ha ho ho.

I’m also submitting sessions for the upcoming SQLSaturday in Chicago next April, and you can join me!  It’ll be on April 17th, but the planning starts now, and we need your help to make the event successful.  SQLSaturdays are completely free events for the attendees, and speaking there is a great way to give back to the community.

You’ll also be polishing your presentation skills, which pays off when you’re up against tons of other speakers for coveted PASS Summit sessions.  Remember, if you get sessions approved for the Summit, you get a free registration!  Think of your SQLSaturday sessions as a down payment towards your Summit skills.

Enter your session abstract today, and as a thank-you, I’ll give each SQLSaturday Chicago speaker a personal thank-you gift at the event.  Don’t get too excited though – it won’t be a free TabletPC like those PDC attendees got (I’m not bitter.)

Update: I’ve gotten a couple of emails asking if I’d fly out to local user group meetings to speak.  I would love to, but the reality is that I have to focus on reaching the most number of people possible in relation to my travel time.  When I fly out to speak at a user group, I spend several hours in flight back & forth, plus I often have to overnight at a hotel because the user group meeting takes place after the last flight out.  All of that costs money and time, and frankly, doing that for a 10-30 person group for 1 hour is pretty painful.  I already travel too much (especially if you ask my wonderful girlfriend who already puts up with too much).  I’m completely open to doing remote presentations, but if I have to travel, I need to combine several events, or reach a lot more than 10-30 people.  Just like your company is asking you to do more with less, Quest is asking us to figure out how to reach more people with less, and usually that means virtual conferences or focusing on big (several-hundred-people) meetings.

Brent Ozar

Brent specializes in performance tuning for SQL Server, VMware, and storage. He's one of the very few Microsoft Certified Masters of SQL Server, a published author, and a Microsoft MVP. He likes travel, Jeeps, Apple gear, jokes, and writing about himself in the third person. Read more and contact Brent.

More Posts - Website

Follow Me:
TwitterFacebookLinkedInGoogle PlusYouTube