In 2010, a crack development team was formed inside of a Fortune 500 company. These developers promptly escaped the maximum security Project Management Office and instituted an Agile Scrum. Today, they survive as green-field developers. If you have a problem, if traditional corporate IT can’t help, and if you can find them, maybe you can hire… Alpha Team.

When Thom interviewed at said Fortune 500 company, he didn’t know he was interviewing for Alpha Team. He assumed that it would be like any other huge enterprise development shop- tedious line-of-business applications that helped ship widgets but didn’t do much more. The product and the team was sold to him as being very exciting, and he liked the idea of the stability a large company offered, so Thom joined the Alpha Team.

The team room was slightly larger than the inside of a large van. John, the team lead, greeted Thom with a sly grin. “Great to have you on the team. You’ll be sitting between Albert and Murdock. I hope you don’t have any plans for lunch- today’s our weekly team lunch. Good chance for you to get to know everyone.”

The team’s architect, Murdock, grabbed Thom for a few minutes to brief him on the application’s architecture. It wasn’t surprising: a SQL server backend, a web-service based middle-tier, and a hybrid ASP.NET and WebForms presentation tier. “This application is extremely flexible,” Murdock said. “That’s the main goal, really. We’ve got it set up so our business analysts have a lot of control over the display, so that we aren’t wasting time just changing field names around.” The exact details were simply described as “magic”, which Murdock didn’t have time to explain right then; “It’s documented, and I need to crank on a few tasks, our burndown is terrible this sprint.”

Albert showed Thom where to find the key documents. “You should start with the environment setup. I pity the fool that tries to set up their dev environment without reading that.” Thom spent the rest of the morning following Albert’s advice. The environment setup document covered how to install and configure Visual Studio. There was nothing unusual or surprising in the document, except perhaps its enthusiastic level of detail- it was over 100 pages of screenshots covering every possible screen and message you might encounter while configuring your environment.

Thom didn’t meet the product owner until lunch. “I’m sorry I wasn’t there in the morning, but you know how it is with meetings,” Templeton said. “User engagement, same old, same old, right? The good news is that I specced out a feature which we can sneak into this sprint. It should be easy for you to implement.”

“Oh, that sounds good,” Thom said. “Are you sure we should change the deliverables mid-sprint?”

Templeton shrugged it off. “Don’t think of it as changing the deliverables. This’ll just give you a head start on the next sprint. You’ll probably need the extra time while you’re learning the ropes.”

The new feature was a simple memo field on a transaction entry screen. It was a string field, with a 500 character max-length, and was informational only. Templeton had estimated it out as a 50 hour task, which seemed amazingly generous, but when Thom checked the backlog, “add a single field” was routinely estimated between 40 and 100 hours. Thom wondered: had he joined a team that padded out its estimates and spent only three days doing real work out of any given month? Or was there something much more wrong?

Thom dug into the docs and the code to try and find out. The first time he saw Albert’s documentation reference “the Data Dictionary”, he assumed it was a reference to the SQL Server data dictionary. “The Data Dictionary” kept appearing, again and again, which seemed odd. Then Thom saw this line:

The Data Dictionary should be stored at \CorpFileSrv01\d$\TransApp\DataDictionary.accdb. Contact the service desk to be granted write permissions. Use Access to edit the file.

Thom had found the “magic” in Murdock’s design, but it was dark, twisted and evil magic. For example, let’s say you wanted to add a memo field to the transaction screen in the accounting module. First, you needed to find out the ModuleID for the accounting module by looking in the Modules table. Then, you could go to the Screens table and find the record for the transaction entry screen. With that ScreenID, you could now add a record to the Elements table, which described the field. The elements table required you to specify the DisplayName of the field, the DataType, ServiceName, ApplicationName, and DatabaseName. You also had to create a set of records in the Validations table, which described the validation rules which should be applied tot he field.

Once Thom had entered a set of records to describe his “TransactionMemo” field, he could then add widgets to the ASP.NET page. The label needed to have the ID lbl_TransApp_Accounting_strMemo. “Memo” was the value in ApplicationName. The actual text box needed to be IDed txt_TransApp_Accounting_strMemo, while the read-only display of the memo field needed to be IDed txt_show_TransApp_Accounting_strMemo.

The idea was that business analysts could control the DisplayName without involving the developers. Since Access databases couldn’t be versioned by source control, and since the BAs were constantly changing things in production based on user feedback, there was only one version of the Access database, shared by production, test, development, QA, etc. Since Access isn’t exactly built around multi-user deployments, or trivial things like “security”, the BAs constantly changed columns they shouldn’t, breaking one or more environments in the process.

Thom decided to keep his head down, and just do his best. After a few months, all of the team lunches in the world couldn’t help his morale, and he was ready to quit. Ready, that is, until he heard about the Beta Team. You see, in 2014, a crack development team was formed inside of that Fortune 500 company. These developers escaped from legacy code. Today, they survive as “rearchitects ”, identifying and redesigning broken applications. If you have massive technical debt, if you have an unsupportable product, and if you can find them… maybe you can apply to work on the Beta Team.

In the winter of 2012-13, I was fired from the ill-rumored e-commerce company known as ShipPoint. Though I remained stalwart to the end, the wretched darkness embodied in ShipPoint's CTO and his twisted worshipers dogs me still, a malignant growth choking the very life out of my career aspirations. And although I fight every day to forget, to leave my time at ShipPoint behind, I still awaken in the uttermost black of night, shuddering, my mind wrenching itself free from nightmare's grip. I record this grim history only because I fear I may soon slip irredeemably into madness.

It was 2011 when, freshly downsized, I found myself wandering the LinkedIn Jobs Directory, seemingly in vain. I had almost made up my mind to hang out my shingle as a consultant when I received an email from a recruiter. I don't remember his name, nor the firm that he claimed to represent, only that he demanded that we meet in person; apparently he was privy to a lucrative opportunity whose details could only be revealed face to face. While suspicious, I must admit I was gripped by curiosity — tinged, I must now believe, with a touch of the wild. I met the recruiter, a grim, swarthy fellow of furtive glance and questionable heritage, in a refuse-choked alley far from the central business district. It was there, amidst the dumpsters and commercial-grade recycling bins, that I first heard in a grating croak the name whose syllables I would one day shudder to write.

"ShipPoint," he said in response to my question about their development environment, "is dedicated to becoming cutting-edge with their development tools and processes. They use Subversion, and I hear they have a focus on quality and testing." I proceeded through a phone interview, and then on to meet James Akeley, ShipPoint's development manager. Imploring that I call him "Jimmy", he proclaimed his easy-going attitude to be matched only by his and ShipPoint's commitment to quality. Though the pay was a bit on the low side, I accepted his offer. I was to start the following Monday, taking the train and then a bus to the ugly one-story building of nondescript gray that contained ShipPoint's offices, a geriatric hulk muttering tonelessly to itself as it wallowed in its crumbling and almost-abandoned office park by the seashore.

My first day at ShipPoint began as prosaically as one could expect with a simple task that would lead me through their codebase. As an e-commerce provider, ShipPoint's stock in trade was web applications written using ASP.NET, and I made careful note of several places where classic code smells made themselves apparent. The team went out to lunch, as was their custom. Jimmy drove, with Jack Mason, the second-most senior developer, in the passenger seat. Sharing the back with me was Rob Carter, the company's web designer — one who would prove himself my most stalwart companion in the unguessed-at trials that lay ahead. While our lunchtime discussion was generally mundane, with only Rob expressing any interest in developing familiarity with his new associate, I found an appropriate pause in the conversation to present Jimmy and Jack with the potential problems I had detected during my brief venture into the code. Given his repeated assertions regarding dedication to quality, I expected Jimmy, at least, to be keenly interested in my discoveries. My surprise was considerate when he and Jack rebuffed me, declaring that Dan Marsh — the CTO — didn't want us to spend time refactoring code. "He and the other executives think it's a waste of time," Jimmy explained, some small measure of remorse evident in his voice, while next to him Jack nodded his head approvingly. "They want us to focus on new deploying new features."

I was disappointed by this, and by the subsequent revelation that, though ShipPoint did indeed mandate Subversion for source control, Jimmy and Jack only ever copied all the files to a separate, timestamped folder before committing. While the two senior developers were hesitant to discuss their mysterious and unseen leader, I was eventually able to coax from Rob what little he knew of the enigmatic Mr. Marsh. It seemed Marsh wasn't a developer, but, after joining the company a decade prior, his possession of certain esoteric scraps of scripting knowledge qualified him as ShipPoint's sole IT person. His authority spread as the years went by, unquestioned by his superiors and the developers he eventually allowed to join his staff, until he now led all technological decision-making at ShipPoint from within the only private office on their floor, an office whose door opened by invitation only.

After several months of my attempted improvements being either stutteringly denied by Jimmy or gruffly rebuked by Jack, new allies arrived at ShipPoint. Arthur Gilman was a brave and clever youth who joined the company alongside his mentor. Walter Peaslee was a hoary old veteran who had been using .NET since the framework was in beta. If anyone could help me champion sane coding and source-management practices at ShipPoint, it was these dynamic individuals. And changes were surely needed, as the months had shown me deep-rooted stability issues that would cause pages to crash or take minutes to load. It had likewise become clear that the senior developers were unwilling or uninterested in tackling these issues, holding up Mr. Marsh's desire for them to complete his endless list of superficial improvements as reason to hack as quickly as possible, leaving Rob and me to fix up the messes they left behind them.

At Christmastime, a chink in the armor appeared. Jimmy announced that he was leaving the company, taking his passive deference to Mr. Marsh with him. I decided to take action, and, with the idealistic Arthur at my heels, endeavored to implement a few changes. First, set up a bug-tracking system and then begin using Subversion properly, setting my protégé to create branches that would let the team collaborate without creating multiple copies of the application's source. Jack agreed to the changes in principle, and victory seemed close at hand. Only no sooner had Arthur went live with the Subversion changes than a blood-curdling cry was heard from Jack's cubicle! His files, Jack insisted, were gone, and he accused us of the most sordid and calculated mayhem, insisting that we sought to discredit him before Mr. Marsh. Not waiting for Arthur to explain that the files had simply been moved to a branch folder, Jack stormed into the CTO's office. By the time we had returned perplexed to our workstations, a directive to return the source control repository to its previous state awaited us, bearing the CTO's imprimatur. This was merely a prelude of things to come as repeated future attempts to sanitize our source-control procedures (and reclaim the gigabytes of storage consumed by the many redundant copies of our source code) were met with similar fear, uncertainty, and doubt from Jack, rapidly followed by executive sanction.

In the venerable person of Walter Peaslee, I was sure a sane counterpart had been found to our volatile senior developer. But the hand of Marsh proved subtle. When attempting to bring Walter's vast experience to bear on our DevOps dilemma, great was my surprise when I found him languishing on a project to produce a report for ShipPoint's CEO. Harbored as the chief executive was on far alien shores, all features of the report required Mr. Marsh's approval. With a sigh that seemed to carry a weight beyond even his advanced years, Walter explained that the CTO would lead him on for weeks regarding the simplest decision, often ignoring multiple emails. With his calendar eternally full to ward off meetings, Mr. Marsh would eventually return terse feedback along the lines of "this is the wrong color", disregarding the actual functionality.

I was saddened, but not surprised, when Walter graciously notified me that he would be submitting his resignation at the end of the week. After being regaled with the sanity-challenging truth of his experience working with Mr. Marsh, I had not the heart to try to convince him to stay. Indeed, I wondered if he might have awakened to a reality that I, too, should embrace. Arthur, on the other hand, being young and impressionable perhaps to a fault, was distracted by a new assignment: the task of utterly redesigning the central UI of our flagship application. It was here, in this project, that the forces of order and of chaos manifest at the heart of ShipPoint would collide in a last, terrible sortie. My support had meanwhile been secured by a timely email from Mr. Marsh, promising to install me as the lead of a new team of developers, since, he astutely pointed out amidst aggravating hints that the two shared some dark and malignant tradition, Jack was content to be a lone wolf. I must admit that the appeal to my leadership aspirations led me to lapse into a period of content productivity, and as the months went by I mostly avoided Jack and his hasty, problematical contributions to the codebase wherever I could, bringing as much improvement to the features I implemented as possible without incurring the wrath of Jack or the dreaded and still unseen Mr. Marsh.

Arthur, alas, had no choice but to collaborate with Jack, who effectively owned the backend of the application he was redesigning. While I thought I had coached the young man to weather this abominable partnership, the elder developer proved maddeningly cunning. While Arthur attempted to coordinate front-end and back-end features in the hurried sprints that Mr. Marsh had demanded, each release was plagued by wave after wave of new bugs, lapping like a foetid, corrosive black tide at a bleak, doomed shore. It was only Rob's fortuitous glimpse of an email seen over Jack's shoulder that we determined Mr. Marsh had been secretly communicating a list of shadow features he had apparently sold to management, and Jack was hacking code at a maddening pace to deliver said features in each release. It was with grim resignation that I entered the repository and inspected the terrible results. I perceived that Arthur's excellent front-end work had been reduced to little more than window-dressing, twisted into whatever shapes Marsh and Jack required to realize their fiendish goals. When I opened the solution containing Jack's jealously-guarded back-end code, obfuscated though it was behind incomprehensible names like "Solution1" and "MvcProject4", only then did I begin to grasp the horror that had taken root beneath the facade of a UI redesign. I saw them in a limitless stream—flopping, hopping, croaking, bleating—surging inhumanly through the spectral moonlight in a grotesque, malignant saraband of fantastic nightmare! That interminable list of poorly-implemented features, its shapeless mass extending blasphemous profusions in all directions throughout the code. It seemed to surge and breathe even as I watched...

It was with a mind gone almost entirely over to the feverish that I found myself composing email after email to Mr. Marsh, laying bare the deleterious effect that this noxious circumvention of procedure was having on our product. Rob was good enough to support this dangerous endeavor, and together we believed we may have been turning the tide of the CTO's sentiment against Jack, whose bland reassurances had apparently blinded Mr. Marsh to the depth of the horror. This last flicker of naiveté on our part was efficiently snuffed when Arthur's employment was terminated without notice. Though no word from Mr. Marsh was forthcoming, Jack's smug explanation was that the youth was slowing down the delivery of critical new features, and, worse, his incompetent code changes were found to be at the root of the catastrophic server instabilities. Perceiving the tolling of a grim bell to have begun, Rob informed me he was thinking of getting out of the technology game altogether, returning to the simple pastoral life he had known while running an organic fruit stand outside a nearby beachfront town. I tried to reassure him that we would find a way to prevail, but in truth my own hope was waning. ShipPoint and its uncouth stewards had ground my desire to write excellent code and promote best practices down to their merest remainder. Deep within me a malaise had taken root, and I knew when I looked hard into the glass that the end was drawing near.

The harbinger came, as it so often does, with a revocation: came a day that Rob needed me to reconfigure something for him on the Production server that had long been my charge, when, upon attempting to connect, I was rebuffed by the server's protestations of an incorrect password. Under my questioning, Jack hesitantly and stutteringly informed me that the password had changed and he'd forgotten to update me. No sooner had he left to fetch the promised credential than my phone rang. Shouldering the receiver, I heard the voice of the spectral Mr. Marsh for the first time. Never have the words "Could you pop by my office for a sec?" been uttered in such a sardonic and inhuman tone as to induce in the listener a shocking wave of panic fear. I felt numb as leaden limbs carried me to the unopened door. Pulled into the dark recesses the portal revealed, I came face to face with unbounded horrors that defy description. Let me only say that the stated reason for my termination was "a change of corporate direction towards a smaller, more agile development team".

Though I survived my meeting with the terrible Mr. Marsh, I was rendered practically an invalid by my abruptly-curtailed employment at ShipPoint, and made my way to a relative's country home to engage in a lengthy convalescence. I received an email from Rob soon after my firing, informing me that he had left the company and was exiting the industry altogether, going so far as to delete his LinkedIn profile. The horrifying dreams in which I blindly shoveled hastily-implemented code into a branchless Subversion repository while pursued down lightless corridors by a shapeless unseen terror had begun to pass when the first job posting appeared. ShipPoint was calling, its unspeakable tendrils reaching out across the vast cosmic gulfs of the internet to ensnare unwary developers. And while I have sworn never to take a job without assurance of sane development practices again, I do not know that my programmer's soul will ever be entirely free of its taint...

So far I have not yet deleted my LinkedIn profile as Rob did. The tense extremes of horror are lessening, and I feel queerly drawn toward the job postings instead of fearing them. I see and do strange things in Subversion, and commit my changes with a kind of exaltation instead of terror. Stupendous and unheard-of splendors await me in Marsh's cube farm, and I shall seek them soon. Iä-R'lyeh! Codethulhu fhtagn! Iä! Iä! No, I shall not delete my LinkedIn profile—I cannot be made to delete my LinkedIn profile!

I shall coax Rob back into software development, and together we shall go to marvel-shadowed ShipPoint. We shall take the bus out to that brooding industrial park by the sea and dive down through black abysses of code to the Cyclopean and many-columned database, and in that lair of the Expert Beginners we shall dwell amidst wonder and glory for ever.

Photo credit: gagilas / Foter / CC BY-SA

Given the title, most of you are expecting a story that goes as follows: Boy meets girl. Girl writes phone number inside a book in a used book store. Ten years pass and boy searches for book. Boy finds book and gets girl. But not this time. Instead, Andrew is the source of a story that ranks much higher on the believability scale.

Andrew's father worked for SmallishNicheCo where, like many small companies in the early 1990s, the sum total of IT knowledge was non-existent. Every problem and request was passed to an outsourced help desk, which in the tradition of solid customer service for which IT help desks have become renown, could take up to 3 days to acknowledge receipt much less dispatch a person to solve the problem. If you needed to have the brightness on your monitor adjusted, you were looking at 5-7 days. And that was after passing the gauntlet of questions to ensure the problem was 'real' (Is your computer plugged in? How about your monitor?)

With next year's budget being worked out, Andrew's father offered up his son's services to help out with the corporate spreadsheets. Andrew was looking for work and was particularly keen to work with computers. Really keen. So keen, in fact, that his resume contained the line "I *love* working with computers". Keep in mind that this was before the advent of bold fonts. And at the time Andrew was 19. He knew a lot about DOS, a little bit about Excel, and absolutely nothing about crafting a compelling resume.

Andrew's initial 'try-out' was a two-week gig helping out the elderly financial controller with the aforementioned spreadsheets. An old-school curmudgeon, even for the 1990s, the controller was someone who thought that printer paper was better used as a scratch pad then as the surface for reports. Early in the gig, Andrew was asked to not use the computer to set up a table of columns for him, but to hand-rule and write it instead. Said request was dutifully ignored.

Prior to Andrew's arrival, there were no spare computers available. So a new one was purchased and one of the help desk analysts came out to lay the appropriate cabling and set up a network account. On the then gold standard of networking environments, Novell Netware 3.11. In spite of the fact that Netware had a dedicated server, the attached console provided no opportunity to administer the network. Probably to avoid the potential security hole of having employees brute-force hacking the password. Instead, the support person sat at Andrew's computer to set up the new account. And given his teenaged, computer-geek interest, it's not surprising that Andrew was standing back and watching with rapt attention.

Eyes staring down at the keyboard and hunched over as if to shield the keystrokes from Andrew's prying eyes, the support person quickly typed.

LOGIM SUPERVISOR
CBK

Looking up to the screen, he was surprised to see the commands. A quick 'CLS' cleared the screen and he logged in again. Successfully this time. But Andrew had already seen the instructions and stored that knowledge away for future use.

After a few days of gnawing curiosity, Andrew decided to see if the password still worked. It did. Andrew quickly logged back out, in case someone caught sight of his screen or a red "Intruder Alert" started flashing. Another few days passed with nothing untoward and Andrew tried again. The password still worked. This time, he started to poke around the system. There were lots of commands in the system directory, and so he tried out some of the ones that seemed less likely to cause permanent damage. Turns out that Andrew had access to everything in the network, including the user data. By changing a few security settings, he set his own login to have the same access rights as the supervisor account.

As you might expect, the need for stealth was completely unnecessary. No one had a clue about the IT systems and there was no remote access to the network. So the two week gig became a massive learning opportunity. Near the end of his two weeks, a staff member at SmallishNicheCo moved from one office to another and needed her default printer changed. This is the type of taxing request which would usually take 5 days through the help desk. Andrew overheard when the employee requested a support visit. Not one to miss a chance, Andrew offered to make the change himself. He did so successfully and three months later was offered a full-time position that included IT in his list of responsibilities. Over time, Andrew continued to construct more of a position for himself, creating interactive reporting programs for senior managers. As time passed, the CEO came to rely on Andrew's programs for all of the sales/budget information. Eight years later, IT became his only responsibility and after 14 years, the company had grown to include an IT staff of three, all working under Andrew.

That's right. A full-fledged and successful career all launched on the back of a misplaced 'M'

Photo credit: SuperFantastic / Foter / CC BY

It’s great to see that the Connect site leading to fixes in the product.

I was really pleased when SQL Server Data Tools for BI appeared for Visual Studio 2013. What I wasn’t pleased about where a number of UI issues that came with that version.

In particular, there was a problem with previewing Reporting Services reports. If I create a new report project, add a blank report, and drag on a text box:

Note that when I clicked the Preview button, the following appeared:

It appears that the preview is provided by a program called PreviewProcessingService.exe that I presume was meant to be launched on-demand in the background. If you closed the window, an error appeared in your preview. If you minimized it, you could happily ignore if from that point on.

I reported it in the Connect site, and am happy to see today that a new KB article appeared with a fix for it.

What the KB 2986460 article provides is a link to a new downloadable version of SSDT-BI for VS2013:

When the article first appeared, I downloaded the version immediately. It did not fix the problem. Unfortunately, the KB article appeared one week before the download was updated. If you downloaded it before and it did not fix the problem, unfortunately you will need to download it again. Here are the file properties of the version with the fix:

Be forewarned that the fix is a complete replacement that is 1GB in size:

It would be great if they can get to the point of patching these programs without the need for complete downloads but I’m very pleased to see it appear regardless.

I recently posted a blog outlining how the partitions of a table can be used in the calculation for the achievable max degree of parallelism (MAX DOP). http://blogs.msdn.com/b/psssql/archive/2014/09/04/a-partitioned-table-may-limit-the-runtime-max-dop-of-create-alter-index.aspx 

Discussing this with various peers I uncovered a perception that SQL Server was always limited to a max of 64 CPUs, even if the machine had more (128, 160, …)   This is not the case, instead the perception is semantic driven and once you realize how to leverage it maintenance operations can take advantage of more than 64 CPUs.

• A Windows processor group (K-Group) is limited to 64 CPUs
• The internal, SQL Server, affinity mask(s) align to K-Groups as 64 bit integer values
• The value of 64 comes up over and over in lots of documentation, including SQL Server Books Online. http://technet.microsoft.com/en-us/library/ms189094(v=sql.110).aspx
• Recommendations for MAX DOP abound, such as: http://support.microsoft.com/kb/2806535
• sp_configure ‘max degree of parallelism’ set to 0 means 64
• …

It is not hard to understand how the perception started or continues to propagate itself.

SQL Server Books Online states: “Setting maximum degree of parallelism to 0 allows SQL Server to use all the available processors up to 64 processors. “ and that is where most of us quit reading and assume the MAX DOP for SQL Server is limited to 64.

Instead if you read a bit further: “If a value greater than the number of available processors is specified, the actual number of available processors is used.”

Simply stated if you tell SQL Server to use more than 64 CPUs SQL Server will attempt to do just that.

Bob Dorr - Principal SQL Server Escalation Engineer

I’ve blogged previously about why I don’t love sp_updatestats. I recently found another reason that it’s not my friend. TL;DR: It doesn’t update statistics on indexed views. Now, the documentation doesn’t claim that it does, so there’s no bug here. The MSDN documentation clearly states:

But… how many of you thought about your indexed views and wondered whether those got updated? I admit I didn’t. I forget about indexed views, which is unfortunate because they can be really powerful when used appropriately. They can also be a nightmare to unravel when you’re troubleshooting, but I’m not going to argue their use today.  I just want you to be aware that they don’t get updated by sp_updatestats, and see what options you have.

Setup

Since the World Series just ended, we’re going to use the Baseball database for our testing. You can download it from the SQLskills Resources page. Once restored we’ll create a copy of the dbo,Players table, named dbo,PlayerInfo, load a few thousand rows into it, and then create an indexed view that joins our new table to the PitchingPost table:

USE [BaseballData];
GO
 
CREATE TABLE [dbo].[PlayerInfo](
	[lahmanID] [int] NOT NULL,
	[playerID] [varchar](10) NULL DEFAULT (NULL),
	[managerID] [varchar](10) NULL DEFAULT (NULL),
	[hofID] [varchar](10) NULL DEFAULT (NULL),
	[birthYear] [int] NULL DEFAULT (NULL),
	[birthMonth] [int] NULL DEFAULT (NULL),
	[birthDay] [int] NULL DEFAULT (NULL),
	[birthCountry] [varchar](50) NULL DEFAULT (NULL),
	[birthState] [varchar](2) NULL DEFAULT (NULL),
	[birthCity] [varchar](50) NULL DEFAULT (NULL),
	[deathYear] [int] NULL DEFAULT (NULL),
	[deathMonth] [int] NULL DEFAULT (NULL),
	[deathDay] [int] NULL DEFAULT (NULL),
	[deathCountry] [varchar](50) NULL DEFAULT (NULL),
	[deathState] [varchar](2) NULL DEFAULT (NULL),
	[deathCity] [varchar](50) NULL DEFAULT (NULL),
	[nameFirst] [varchar](50) NULL DEFAULT (NULL),
	[nameLast] [varchar](50) NULL DEFAULT (NULL),
	[nameNote] [varchar](255) NULL DEFAULT (NULL),
	[nameGiven] [varchar](255) NULL DEFAULT (NULL),
	[nameNick] [varchar](255) NULL DEFAULT (NULL),
	[weight] [int] NULL DEFAULT (NULL),
	[height] [int] NULL,
	[bats] [varchar](1) NULL DEFAULT (NULL),
	[throws] [varchar](1) NULL DEFAULT (NULL),
	[debut] [varchar](10) NULL DEFAULT (NULL),
	[finalGame] [varchar](10) NULL DEFAULT (NULL),
	[college] [varchar](50) NULL DEFAULT (NULL),
	[lahman40ID] [varchar](9) NULL DEFAULT (NULL),
	[lahman45ID] [varchar](9) NULL DEFAULT (NULL),
	[retroID] [varchar](9) NULL DEFAULT (NULL),
	[holtzID] [varchar](9) NULL DEFAULT (NULL),
	[bbrefID] [varchar](9) NULL DEFAULT (NULL),
PRIMARY KEY CLUSTERED 
([lahmanID] ASC) ON [PRIMARY]
) ON [PRIMARY];
GO
 
INSERT INTO [dbo].[PlayerInfo]
           ([lahmanID]
           ,[playerID]
           ,[managerID]
           ,[hofID]
           ,[birthYear]
           ,[birthMonth]
           ,[birthDay]
           ,[birthCountry]
           ,[birthState]
           ,[birthCity]
           ,[deathYear]
           ,[deathMonth]
           ,[deathDay]
           ,[deathCountry]
           ,[deathState]
           ,[deathCity]
           ,[nameFirst]
           ,[nameLast]
           ,[nameNote]
           ,[nameGiven]
           ,[nameNick]
           ,[weight]
           ,[height]
           ,[bats]
           ,[throws]
           ,[debut]
           ,[finalGame]
           ,[college]
           ,[lahman40ID]
           ,[lahman45ID]
           ,[retroID]
           ,[holtzID]
           ,[bbrefID])
SELECT [lahmanID]
           ,[playerID]
           ,[managerID]
           ,[hofID]
           ,[birthYear]
           ,[birthMonth]
           ,[birthDay]
           ,[birthCountry]
           ,[birthState]
           ,[birthCity]
           ,[deathYear]
           ,[deathMonth]
           ,[deathDay]
           ,[deathCountry]
           ,[deathState]
           ,[deathCity]
           ,[nameFirst]
           ,[nameLast]
           ,[nameNote]
           ,[nameGiven]
           ,[nameNick]
           ,[weight]
           ,[height]
           ,[bats]
           ,[throws]
           ,[debut]
           ,[finalGame]
           ,[college]
           ,[lahman40ID]
           ,[lahman45ID]
           ,[retroID]
           ,[holtzID]
           ,[bbrefID]
FROM [dbo].[Players]
WHERE [lahmanID] <= 10000;
 
CREATE VIEW [PlayerPostSeason]
WITH SCHEMABINDING
AS
	SELECT 
		[p].[lahmanID], 
		[p].[nameFirst], 
		[p].[nameLast], 
		[p].[debut], 
		[p].[finalGame], 
		[pp].[yearID], 
		[pp].[round], 
		[pp].[teamID], 
		[pp].[W], 
		[pp].[L], 
		[pp].[G]
	FROM [dbo].[PlayerInfo] [p]
	JOIN [dbo].[PitchingPost] [pp] ON [p].[playerID] = [pp].[playerID];
 
CREATE UNIQUE CLUSTERED INDEX [CI_PlayerPostSeason] ON [PlayerPostSeason] ([lahmanID], [yearID], [round]);
 
CREATE NONCLUSTERED INDEX [NCI_PlayerPostSeason_Name] ON [PlayerPostSeason] ([nameFirst], [nameLast]);

If we check statistics for the clustered and nonclustered indexes, we see they exist:

DBCC SHOW_STATISTICS ('PlayerPostSeason', CI_PlayerPostSeason) WITH STAT_HEADER;
GO
DBCC SHOW_STATISTICS ('PlayerPostSeason', NCI_PlayerPostSeason_Name) WITH STAT_HEADER;
GO

Index view statistics after initial creation

Now we’ll insert more rows into PlayerInfo:

INSERT INTO [dbo].[PlayerInfo]
           ([lahmanID]
           ,[playerID]
           ,[managerID]
           ,[hofID]
           ,[birthYear]
           ,[birthMonth]
           ,[birthDay]
           ,[birthCountry]
           ,[birthState]
           ,[birthCity]
           ,[deathYear]
           ,[deathMonth]
           ,[deathDay]
           ,[deathCountry]
           ,[deathState]
           ,[deathCity]
           ,[nameFirst]
           ,[nameLast]
           ,[nameNote]
           ,[nameGiven]
           ,[nameNick]
           ,[weight]
           ,[height]
           ,[bats]
           ,[throws]
           ,[debut]
           ,[finalGame]
           ,[college]
           ,[lahman40ID]
           ,[lahman45ID]
           ,[retroID]
           ,[holtzID]
           ,[bbrefID])
SELECT [lahmanID]
           ,[playerID]
           ,[managerID]
           ,[hofID]
           ,[birthYear]
           ,[birthMonth]
           ,[birthDay]
           ,[birthCountry]
           ,[birthState]
           ,[birthCity]
           ,[deathYear]
           ,[deathMonth]
           ,[deathDay]
           ,[deathCountry]
           ,[deathState]
           ,[deathCity]
           ,[nameFirst]
           ,[nameLast]
           ,[nameNote]
           ,[nameGiven]
           ,[nameNick]
           ,[weight]
           ,[height]
           ,[bats]
           ,[throws]
           ,[debut]
           ,[finalGame]
           ,[college]
           ,[lahman40ID]
           ,[lahman45ID]
           ,[retroID]
           ,[holtzID]
           ,[bbrefID]
FROM [dbo].[Players]
WHERE [lahmanID] > 10000;

And if we check sys.dm_db_stats_properties, we can see the row modifications:

SELECT  
	[sch].[name] AS [Schema],
	[so].[name] AS [ObjectName],
	[so].[type] AS [ObjectType],
    [ss].[name] AS [Statistic],
    [sp].[last_updated] AS [StatsLastUpdated] ,
    [sp].[rows] AS [RowsInTable] ,
    [sp].[rows_sampled] AS [RowsSampled] ,
    [sp].[modification_counter] AS [RowModifications]
FROM [sys].[objects] [so]
JOIN [sys].[stats] [ss] ON [so].[object_id] = [ss].[object_id]
JOIN [sys].[schemas] [sch] ON [so].[schema_id] = [sch].[schema_id]
OUTER APPLY [sys].[dm_db_stats_properties]([so].[object_id],
                                                   [ss].[stats_id]) sp
WHERE [so].[name] = 'PlayerPostSeason';

Rows modified in the indexed view, via sys.dm_db_stats_properties

And just for fun, if we check sys.sysindexes, we can see the modifications there as well:

SELECT  [so].[name], [si].[name], [si].[rowcnt], [si].[rowmodctr]
FROM [sys].[sysindexes] [si]
JOIN [sys].[objects] [so] ON [si].[id] = [so].[object_id]
WHERE [so].[name] = 'PlayerPostSeason';

Rows modified in the indexed view, via sys.sysindexes

Now sys.sysindexes is deprecated, but if you remember from my previous post, that’s what sp_updatestats uses to see what’s been modified. But… the object list for sys.indexes is driven by the query against sys.objects, which, if you remember, filters on user tables ('U') and internal tables ('IT'). It does not include views ('V') in that filter. As such, when we run sp_updatestats and check the output (not included for brevity), there is no mention of our PlayerPostSeason view.

Therefore, if you have indexed views and you’re relying on sp_updatestats to update your statistics, your view statistics are not getting updated. However, I would guess that most of you have the Auto Update Statistics option enabled for your databases. This is good, because with this option, view statistics will update if they've been invalidated. We know we’ve made over 2000 modifications to the indexes on PlayerPostSeason. If we query by a first name that’s selective, our query plan should use the NCI_PlayerPostSeason_Name index, and because statistics are out of date, they should get updated. Let’s check:

SELECT *
FROM [PlayerPostSeason]
WHERE [nameFirst] = 'Madison';
GO

Query plan from SELECT against nonclustered index

We can see in the plan that the NCI_PlayerPostSeason_Name nonclustered index was used, and if we check statistics:

Statistics after automatic update

Sure enough, the statistics for the nonclustered index have been updated.  But of course we don’t want to rely on auto update to manage statistics, we want to be proactive. We’ve got two options:

• Maintenance Task
• Custom Script

The update statistics maintenance task does update view statistics. This is not specifically called out anywhere in the UI, but if we create a maintenance plan with the update statistics task and run it, the statistics for the indexed view are updated.  The drawback of update statistics maintenance task is that it’s a sledge-hammer approach. It updates all statistics, regardless of whether it’s needed (it’s almost as bad as sp_updatestats). I prefer a custom script, where SQL Server only updates what’s been modified. If you’re not into rolling your own script, you can use Ola Hallengren’s script. It’s common to update statistics as part of your index rebuilds and reorgs.  For example, with Ola's script in the SQL Agent job you would have:

With this option, if statistics have been modified, they will be updated, and if we check the [dbo].[IndexOptimize] stored procedure we can see where Ola checks for modifications:

        -- Has the data in the statistics been modified since the statistics was last updated?
        IF @CurrentStatisticsID IS NOT NULL AND @UpdateStatistics IS NOT NULL AND @OnlyModifiedStatistics = 'Y'
        BEGIN
          SET @CurrentCommand10 = ''
          IF @LockTimeout IS NOT NULL SET @CurrentCommand10 = 'SET LOCK_TIMEOUT ' + CAST(@LockTimeout * 1000 AS nvarchar) + '; '
          IF (@Version >= 10.504000 AND @Version < 11) OR @Version >= 11.03000
          BEGIN
            SET @CurrentCommand10 = @CurrentCommand10 + 'USE ' + QUOTENAME(@CurrentDatabaseName) + '; IF EXISTS(SELECT * FROM sys.dm_db_stats_properties (@ParamObjectID, @ParamStatisticsID) WHERE modification_counter > 0) BEGIN SET @ParamStatisticsModified = 1 END'
          END
          ELSE
          BEGIN
            SET @CurrentCommand10 = @CurrentCommand10 + 'IF EXISTS(SELECT * FROM ' + QUOTENAME(@CurrentDatabaseName) + '.sys.sysindexes sysindexes WHERE sysindexes.[id] = @ParamObjectID AND sysindexes.[indid] = @ParamStatisticsID AND sysindexes.[rowmodctr] <> 0) BEGIN SET @ParamStatisticsModified = 1 END'
          END

For versions which support the sys.dm_db_stats_properties DMF, Ola checks it for any statistics that have been modified, and for versions that do not support the new sys.dm_db_stats_properties DMF, the sys.sysindexes system table is checked. My only complaint here is that the script behaves the same way as sp_updatestats: if at least one row has been modified, the statistic will be updated.

If you’re not into writing your own code for managing stats, then I would recommend sticking with Ola’s script. But if you do want to target your updates a bit more, then I’d recommend using sys.dm_db_stats_properties. This DMF is only available for SQL Server 2008R2 SP2 and higher, and SQL Server 2012 SP1 and higher, so if you’re on a lower version, you’ll need to use sys.indexes. But for those of you with access to sys.dm_db_stats_properties, here’s a query to get you started:

SELECT
	[sch].[name] AS [Schema],
	[so].[name] AS [ObjectName],
	[so].[type] AS [ObjectType],
	[ss].[name] AS [Statistic],
	[sp].[last_updated] AS [StatsLastUpdated] ,
	[sp].[rows] AS [RowsInTable] ,
	[sp].[rows_sampled] AS [RowsSampled] ,
	CAST(100 * [sp].[rows_sampled] / [sp].[rows] AS DECIMAL (18, 2)) AS [PercentSampled],
	[sp].[modification_counter] AS [RowModifications] ,
	CAST(100 * [sp].[modification_counter] / [sp].[rows] AS DECIMAL(18, 2)) AS [PercentChange]
FROM [sys].[objects] AS [so]
INNER JOIN [sys].[stats] AS [ss] ON [so].[object_id] = [ss].[object_id]
INNER JOIN [sys].[schemas] AS [sch] ON [so].[schema_id] = [sch].[schema_id]
OUTER APPLY [sys].[dm_db_stats_properties]([so].[object_id], [ss].[stats_id]) AS [sp]
WHERE [so].[type] IN ('U','V')
AND ((CAST(100 * [sp].[modification_counter] / [sp].[rows] AS DECIMAL(18,2)) = 10.0))
ORDER BY CAST(100 * [sp].[modification_counter] / [sp].[rows] AS DECIMAL(18, 2)) DESC;

Note that with sys.objects we filter on tables and views; you could alter this to include system tables. You can then modify the predicate to only retrieve rows based on the percentage of rows modified, or perhaps a combination of modification percentage and number of rows (for tables with millions or billions of rows, that percentage might be lower than for small tables).

Summary

The take home message here is pretty clear: I don’t recommend using sp_updatestats to manage statistics. Statistics are updated when one or more rows have changed (which is an extremely low threshold for updating stats) and stats for indexed views are not updated. This is not a comprehensive and efficient method for managing stats…and the update statistics task in a Maintenance Plan isn’t much better. It updates the indexed view statistics, but it updates every statistic, regardless of modifications. A custom script is really the way to go, but understand that Ola Hallengren’s script, if you’re updating based on modification, also updates when only row has been modified (but it at least gets the indexed views). In the end, for the best control, look to roll your own script for managing statistics. I’ve given you the base query to start. If you can block off a couple hours to practice your T-SQL writing and then test it out, you’ll have a working custom script ready for your databases before the holidays roll around.

The post Another Reason to Avoid sp_updatestats appeared first on SQLPerformance.com.

I was teaching a SQL 2014 class yesterday and the students were using the current SQL Server 2014 Enterprise (on Windows Server 2012 R2) template.

We were using the Table Memory Optimization Advisor (right-click a table in Object Explorer within SQL Server Management Studio). I had several people in the class that reported that when they got to the primary key migration screen, that they couldn’t interact with the screen because the checkboxes were not present in the displayed list of columns.

This is what the screen should have looked like:

This is what it did look like:

Note that there are no checkboxes in the left-hand column. I had never seen that happen before.

We tried clicking, etc. in the area (wondering if there was some odd font problem or something) to no avail. There seemed to be plenty of room for a checkbox so it seemed like there must be some logical reason why it didn’t want any of these columns as the primary key. But it only happened on some machines.

Eventually, one of the students resized the rows that were displayed. The checkboxes then appeared.

This is a basic UI issue. I’ve recorded it here in case anyone else runs into it.

Each year for the past few years the PASS Board of Directors has conducted a Q&A session with members at the PASS Summit. I’ve been on both sides of the fence. I thought it was important to hear and answer questions when I served on the Board, and it’s useful as a member to ask questions in front of the entire Board. It’s a different dynamic than you get when emailing a question, or even asking it one on one, it becomes part of their shared experience and that can shape things in good ways. It’s also good for the members that are able to attend because they get to hear thoughtful and often impassioned questions from other members, and I think they often find that they answers given are reasonable. I think the Board doesn’t do as well about following up on the questions that aren’t fully answered, something I’ll be looking for when I review the results of the Q&A this year. It’s an incredible opportunity for dialog and I wish the Board would do it more than once a year, maybe a “town hall” early in January or February after new Board members are seated and priorities are being set.

You can see the questions from previous Q&A sessions on the governance page, or just jump directly to the 2013 Q&A minutes.

Here are the questions I prepped to ask (knowing that other meeting attendees will ask some of them, so I have extras).  They are written in the order of the notes I had, not in the order of ‘most important’. There is a separate BA meeting this year and some of these might be better answered at that meeting. I’m not at the Summit this year, so if you’re attending and think any of these are worth asking, please do!

1. The Summit location has been confirmed in Board minutes for 2015, 2017, and 2018. Can you confirm that Seattle will be the location for 2016 per the comment from Bill Graziano at the Q&A last year? As a follow up to that, when can the members expect the Summit to be held somewhere besides Seattle and how will that selection process work?
2. Will the BAC always be held in California and why isn’t it held in Seattle instead?
3. Andy Leonard asked last year about having a budget summary prepended to the annual budget to help members understand changes. That was not implemented for the FY2015 budget. Can we expect that to happen for the FY2016 budget?
4. What is the total set aside for “finance reserves” at present and how much has that changed from the amount in reserves last year?
5. The volunteer portfolio has been disbanded for the second time and it was noted in a blog post by Thomas LaRock that the volunteer responsibilities will be shared by the Board. Does the Board believe the needed work for that portfolio was completed? If not, what work remains to be done and what goals are being set to complete that work?
6. Why are virtual chapters and chapters two portfolios instead of one? Wouldn’t it serve the members better to consolidate those and maintain the Volunteer portfolio, or perhaps a membership portfolio?
7. Is there a plan and commitment to fix voter registration/qualification prior to the CY 2015 Board election?
8. The BAC seems to be struggling to find focus per the discussions in the 2013 minutes to date. What goals need to be hit to continue the BAC beyond 2015 (noting that dates have been reserved for 2016 and 2017 already)?
9. Has the Board considered replacing BAC with a BI Conference, something that Microsoft held several times with good success?
10. This year the Board announced that “SQL Server” would only be used as part of the PASS name when legally required and the minutes preceding that announcement contained the statement that “the legal name no longer reflects the mission, membership, or direction of the organization”. I have a three part question:

1. If the legal name doesn’t reflect the mission, membership, or direction, why not change the name?
2. Was this change made to alleviate concerns from BAC attendees or potential attendees that it was a “SQL Server” event due to it being hosted by PASS?
3. Does the Board believe it acted in the best interest of its members by making this change and what if any input did the Board solicit from members prior to making the decision?

• There has been much discussion about the “BA community”, yet PASS seems to struggle to identify influencers for the BAC, has not driven the expected growth of the BAC, and there seems to be little activity to grow BAC chapters or SQLSaturday events. Does the Board believe that grassroots efforts are important to the success of BAC and if so, what steps will be taken prior to the 2015 BAC to grow the grassroots portion of the community? What goals have been set for BA Chapter growth in 2015?
• Many Board members seem reluctant to communicate via blogs or Twitter due to concerns about their NDA and the language in the by-laws that only officers can speak “officially” for PASS. Is the Board considering issuing any guidance that would allow Board members to speak publicly about their portfolios and their participation in Board discussions?
• The Board posted goals for the portfolios for 2014. With several portfolios changing owners, can we expect to see a summary of goals met/not met for those that are changing and for the remainder at the end of the year?
• Is the “speaker bureau” project still on the active list and if so, when can we expect it be completed?
• The selection process for the Summit was criticized this year for lack of transparency and concerns about an officer being selected to a present a paid seminar (for which payment was declined). What changes can we expect in the selection process for next year to address those concerns?
• Was there a final analysis completed of the 2013 Summit and if so, when will that be published? Specifically with regards to member, speaker, and sponsor feedback, costs, and logistics.
• I have the perception that the Board doesn’t think any public disagreement with decisions is fair given that they are volunteers. Is that a fair perception? Does the Board believe it is effectively engaging with those who take the take to voice an opinion, even if the opinion is critical?
• What does the Board think of what has been written on the PASSWatch blog?
• Can we get the numbers for how many meetings were held by each chapter and how many attendees were at each meeting for CY 2014? How many chapters were added or lost this year?
• PASS has had a number of security incidents this year. What plans does PASS have for a comprehensive security review in CY2015? Is there budget to support that effort?

This Summit’s presentation from Microsoft Research Labs is from Dr Rimma Nehme, bucking the trend of having presentations from Dr David DeWitt. I’m really pleased to be able to hear from her, because she’s an absolute legend.

Among her qualifications is work on the PDW Query Optimizer – a topic closer to me than probably any other area of SQL Server. I just wish I had known this a few minutes ago when I met her, but I’m sure she’ll chat more freely after her big presentation.

Today she’s talking about Cloud Computing, which is great because the cloud space has changed significantly in recent years, and it’s good to hear from Microsoft Research Labs again. For example, analysing the power-effectiveness of a data centre by comparing the total power used by a data centre against the computing power of a data centre. This leads to exploring more effective systems, such as evaporative cooling (which is used by many Australian homes and businesses, of course), making energy-responsibility a key component of cloud computing. With such an effort being put into cloud computing, the globally-responsible option is to use the cloud.

The five key drivers for cloud that Dr Nehme listed are:

• Elasticity
• No CapEx
• Pay Per Use
• Focus on Business
• Fast Time To Market

These are all huge, of course, and the business aspects are massive. It’s increasingly easy to persuade businesses to move to the cloud, but the exciting thing about the technologies that have been discussed this week is the elasticity point.

Microsoft is doing huge amounts of work to let people scale out easily. New technologies such as Stretched Tables will allow people to have hybrid solutions between on-prem and cloud like never before. With a background in the PDW Query Optimizer, Dr Nehme is the perfect person to be exploring what’s going on with spreading the load across multiple cloud-based machines for these scale-out solutions.

The cloud means that many database professionals worry about their jobs. I’m sure people felt the same way when the industrial revolution came through. People who work on production-lines have been replaced by robots, and database administrators who only do high availability don’t need to handle that in the cloud space. But they will not be redundant. Dr Nehme just said “Cloud was not designed to be a threat to DBAs”, and this is significant. The key here is that we have more data than ever, and we need to be able to use computing power effectively.

We can’t keep going with the amount of data that is appearing, and we need to be more responsible than ever.

Great keynote, Dr Nehme. I hope this is the first of many keynotes from you.

@rob_farley

We’re kicking off Thursday’s PASS Summit keynote in about 5 minutes, and the good news is that I have network connectivity today and I’ll really be live-blogging today.  Stay tuned for updates throughout the morning!

Ok, I guess I need to start with this picture of Brent Ozar and Grant Fritchey:

Brent and Grant with their rainbow leggings to support Doctors Without Borders

Brent and Grant will be wearing these lovely leggings today when they present as part of an effort by to help raise money for Doctors Without Borders.  You can still donate!

8:15 AM

We’re off and running with Adam Jorgensen, PASS EVP of Finance.  Adam is going to provide an update about the financial status of PASS.  Funny enough, doing this at Summit satisfies the requirements of the by-laws.  The largest source of revenue is the PASS Summit (not a surprise): 96% of the revenue for PASS is generated by Summit and the Business Analytics conference.  The money raised goes to provide activities throughout the community, and there are reserves of over one million dollars (pretty good).  These funds protect the PASS Community in case the Summit is cancelled due to a natural disaster.  In fiscal year 2016, PASS wants to focus on projects already scoped and also provide funds for new projects that are yet to be determined.  PASS publishes the budget every year.  The community members have access to this, and starting in 2016 portfolio-level budgets will be published so it’s easier to drill into the areas of PASS which interest members the most.  The focus in 2015 includes conferences, the global alliance program, investing in IT, community events, data culture, and the business and data analytics community.

8:22 AM

Adam finishes up and PASS President Tom LaRock comes on stage.  Tom takes a few minutes to say goodbye to members who are ending their term on the board, including Sri Sridharan and Olivier Matrat.  Sri managed the Volunteer profile within PASS and did a phenomenal job trying to bring more volunteers into the community to help PASS.  Tom also introduces new Board members: Sanja Mishra and Grant Fritchey.  Next up is Denise McInerney, EVP of Marketing.

Denise mentions that over 5000 people are watching today’s keynote online on PASStv in over 113 countries.  Denise starts by talking about her involvement with PASS, which started back in 2002 with a session she attended by Kimberly Tripp. (ES: SQLskills shout out!)  Denise then got involved locally and at the national level.  When you volunteer for PASS you help other members, and you broaden your own network.  A point from Denise: many of the people she met in the beginning are the ones she still turns to.  Denise announces this year’s PASSion Award winner: Andrey Korshikov, who is based in Russia and a SQL Server MVP and BI Developer.  Andrey is a PASS Regional Mentor and the founder of the Russian VC.  He’s managed four SQLSaturdays and three Russian editions of 24 Hours of PASS.

Denise also mentions those who were also nominated for the PASSion award – but I couldn’t type them all fast enough :)  She then highlights the PASS Outstanding Volunteers that have been recognized through the year and asks them to stand and be recognized.  (ES: It takes a village – there are so many fantastic people who contribute to this community.) 

On Friday, from 2:15 to 2:45, in room 307/308, there will be a Business Analytics Direction Board Discussion.  If you want to provide feedback about the Business Analytics Conference (taking place April 20-22, 2015 in Santa Clara, CA) and/or this direction that PASS is going, please attend the discussion.  Denise also reminds people to update their PASS profile, particularly if you want to volunteer and and get involved.

The next PASS Summit will be in Seattle, October 27-30, 2015.  Registration is already open!

 8:35 AM

Dr. Rimma Nehme finally takes the stage for her keynote: Cloud Databases 101.  She is a Principal Research Engineer at the Microsoft Jim Gray Systems Lab.

Dr. Nehme has been watching this conference for the past 5 years, and starts by thanking the organizers for inviting her, and mentions Dr. DeWitt.  And she was thinking about how she could be like Dr. DeWitt, and then realized, “trying to be a man is a waste of a woman”.  She won’t try to be like Dr. DeWitt, she will just be herself.  Yes.  Dr. Nehme was born in Belarus, she knows a little bit about databases from an academic and real-world perspective, and she is learning a lit bit about business too.  (Dr. Nehme is getting her MBA in her “spare time”…seriously…and did I mention that she’s also a mom of two kids?  SO impressive.)  Dr. Nehme is a big fan of the PASS Community.

Today’s topic is: What is a cloud database?  Our roadmap for today:

• Why Cloud?
• What’s a Cloud Database?
• How are they built?
• What’s my role as a DBA?
• Summary

Cloud technology is still relatively new, and it has “Shiny Object Syndrome” around it.  Dr. Nehme’s goal is to explain why cloud is special.  Basic equation to remember is that cloud = service.  More precisely defined: the cloud is computing and software resources that are delivered on demand, as a service that is always on, accessible from anywhere, and at any time.  This is also known as the 5the utility.  Why is it called cloud computing?  Blame the network people (not database people).  Cloud computing characteristics:

• on-demand self-service – demand for resources can be filled automatically
• location transparent resource pooling – resources are pooled to several customers
• ubiquitous network access – all resources available over the network that allows data exchange
• rapid elasticity – capability provided on-demand when needed, then releases
• measure service with pay per use – resource charges as based on the quantity used

Think about it: one woman or a man, and a credit card, can tap into some of the largest computing solution in the world.

A brief history: the wave of computing started in the 1960s.  The concept of computation was born in this time by one of the MIT professors.  In the 1990s, the first cloud application was offered.  In the 2002, Amazon Web Services was launched, and Windows and Google launched offerings in the 2008 timeframe.

Question: Where does the cloud live?  In a data center.  Let’s go on a virtual tour of a Microsoft Data Center.  The data center in Chicago looks like a fancy trailer park.  What’s inside those big containers?  Lots and lots of servers.  When we think of a data center we think of lots of servers, raised floors, etc.  There is more to it, there are transformers, cooling towers, chillers, UPS’, powers, and people.  One way to describe a data center is by its efficiency.  Optimizing for energy efficiency is a good thing.  We are socially responsibility to pay attention to our use of resources.  One way that efficiency is calculated is by using PUE = power usage effectiveness.  The formula is the total facility power divided by IT equipment power.  This is valuable as a broad efficiency ratio.  The PUE ration for a modular data center (hosting cloud resources) is 1.15, whereas for a traditional data center it is 2.0.  Interestingly enough, the cooling for the modular data center is 0%.  One example of how this is done is swamp cooling (aka evaporative cooling)…put cold water in front of fans.  ES: Rob Farley tells me this is how its done in Australia.  Data centers have significantly evolved since the late 1980s.  There are over 100 data centers in more than 40 countries – more than 1 million servers.  What does Microsoft consider with site selection?  There are over 35 factors, the top 3 are proximity to the customers, energy and fiber infrastructure, and the skilled workforce.

The main takeaways for why cloud: elasticity, no cap ex, pay per use, focus on business, and fast time to market.  This is why cloud computing is special.

What is a cloud database?  Everything in the cloud is a service.  So you’re getting a database, as a service.  Cloud services has 3 layers: infrastructure, platforms, and applications (and these are also all services).  The Microsoft Cloud has the same thing – infrastructure services, platform services (e.g. Windows Azure, SQL Azure), application services.  When you have a data center on site, you manage everything.  When it’s infrastructure as a service, part of that stack is outsourced to a vendor.  With platform as a services, you’re responsible for the application and data – everything else is outsourced to the vendor.  With software as a service – you outsource everything.  Dr. Nehme takes this and then does “pizza-as-a-service” analogy:

• On Premise = you buy everything and make the pizza at home
• IaaS = take and bake (pick up the pizza, you cook it at home)
• PaaS = pizza delivered
• SaaS = dining in the restaurant

IAAS in the database world – you must still manage provisioning, backups, security, scaling, failover, replication, tuning, performance, etc.  Dr. Nehme calls this lift and shift – take an earth version of a database and put it in the cloud.  Existing applications don’t need to be modified.  Just need to point to version of the DBMS in the cloud.

PAAS = DBMS as a service – select the cloud vendor, select a DBMS.  Here, the cloud vendor manages provisioning, backups, security, tuning, failover, etc.  There might be some changes to the language surface compared to an earth version of a database.

SAAS = select the cloud vendor, select a cloud app (SharePoint).  The whole stack is outsource to the cloud vendor.

Database as a service examples:

• Managed RDBMs (SQL Server)
• Managed No SQL (Doc DB, MongoHQ)
• Cloud-Only DBaaSS (Dynammo DB, Google F1)
• Analytics-as-a-Service (HDInsight, EMR)
• Object Stores (Azure storage, S3)

Why virtualization?  It’s a huge enabler for cloud computing.  Unfortunately, many servers are grossly underutilized.  Virtualization developed to put resources back to work.  However, there are bottlenecks with these resources.  What can be virtualized?  CPU, network, memory, and disk.  Keep in mind that there is no free lunch.  Virtualization comes with limitations.  Lose direct access to the computing resources.  Now have an indirect path.  Also, hiding the details of physical resources is unfortunate in terms of configurations.  In addition, virtualization always causes some degree of performance penalty.  Use cases are consolidation, migration and load balancing, and high availability.    For consolidation, if CPU requirements are high for one server, and IO requirements are high for a second server, consolidating those two might be ideal (and can also equate to energy-savings).

With migration and load balancing, assume one machine with a VM with a RDBMS that gets overloaded.  It could be migrated to another machine to help maintain performance.  And with high availability – one machine with a VM and then backup machine with VM image, will detect a failover, restart the image so the server stays up and available.

There are four common approaches to multi-tenancy (with a lodging analogy):

• Private OS (SQL Server in a VM) – private apartment
• Private Process/DB (MongoHQ) – private room
• Private Schema (Azure SQL DB) – share room
• Shared Schema (SalesForce) – share bed

What’s the big deal with this?  When you consider database as a service, what are the requirements for your database, for your data?  If application independence is important, don’t go with a shared schema approach.  You must do cost-benefit analysis.  Given pros and cons, what works best for you?

Service Level Agreements…when people talk about the cloud they talk about SLAs.  It’s a common understanding about services, guarantees, and responsibilities.  There is a legal component and a technical component.  Service level objectives are measurable characteristics such as performance goals, deadlines, constraints, etc.  Think of this in terms of availability and “nines”.  If you require four nines (99.99%) up time, that’s about 4 minutes of downtime per month.  Three nines (99.9%) is about 43 minutes per month.  Just one nine can make a big difference.  Container based hardware is three nines-reliable, but with SQL DB they are delivering four nines-reliability.

Three main concepts behind Azure SQL DB:

• Account – 0 or more servers
• Server – 1 or more databases
• Database – standard SQL objects

This was designed with high availability in mind.  This means that there are multiple replicas of data.  There is a primary and two secondaries.  If a node goes down, the secondary becomes a primary, then replicate again so end up with two secondaries.  Reads are completed on primary, writes replicated to secondaries.  Four layers:

• client – used by application to communicate directly to SQL Database
• services – the gateway between the clients connecting to the SQL DB and the platform layer where computation occurs; provisioning, billing, routing for connections
• platform – physical services that support the services layer above, includes SQL Server, management services
• infrastructure – IT admin of physical hardware and OS

Applications connect to the internet, go to the Azure cloud, get to the load balances, hit the gateway which are connected to the SQL DB nodes, and then under all that is the scalability and availability fabric which does failover, replication and load balancing.  What does the SQL node look like?  It’s a machine with a SQL instance with a single physical database for the entire node.  The database files and logs are shared across every logical database – might be sharing log files with someone else.  Each logical database is a silo with its own independent schema (sharing a room analogy).

What we if create a database or run a query, how does it work?  The Azure service will identify where to put the primary database (when creating a new one), then put secondaries on two other machines.  When a user comes in to do a query, the SQL Azure gateway service will identify where the primary is located, get to it, perform computation, and return results to user.  If you want to know more – go to the sessions here at PASS

Next up: my role as a DBA.  From Dr. Nehme: “I have to be honest, I tried to put myself in your shoes.”  She asks, do we still need a DBA in the cloud era?  Dr. Nehme says yes.  Cloud doesn’t have to an either/or choice.  You can augment on-premise systems with cloud (remember the stretched tables example for yesterday).  This is the time to refresh your skills and adjust to this era in the cloud.  The cloud was not designed to be a threat to DBAs.  The number of DBAs vs. the number of database apps vs hardware computer capacity.  The number of DBAs is much smaller than both.  This is where cloud computing can help.  Address issue of underutilized hardware and alleviate some of the work of overburdened DBAs.  Dr. Nehme’s recommendation is to take current skills, add cloud skills, and call yourself a Cloud DBA.

Some key things to remember:

1. cloud database = a service, designe dto reduce admin and operational costs (pay as you go, elasticity), there is a wide spectrum of solution (rent a database, cloud database).
2. If you get confused about cloud deployment options, remember the pizza analogy
3. Do the cost benefit analysis, and you need to embrace the cloud.  It presents a lot of opportunities.

Dr. Nehme finishes up and takes a minute to thank Dr. DeWitt and has him come on stage. She hints at possibility a keynote with both of them in the future.  I’d love that, but I’d also be happy to just hear Dr. Nehme again :)  Great session.  Perry is overwhelmed…

Perry after Dr. Nehme’s talk…

Edit: 10:09AM In my original publishing I referred to Dr. Nehme as Rimma…and I think it’s because Dr. DeWitt always refers to her that way (and she refers to him as David).  I updated the post to fix that.  And also wanted to add a new pic (Dr. Nehme’s keynote and having the chance to chat with her was one of this week’s highlights):

Dr. Nehme and me

The post PASS Summit 2014: Day 2 appeared first on Erin Stellato.

Two posts from me in one day?  What’s up with that?!  Well, today at the PASS Summit we also have the WIT luncheon, where Kimberly Bryant, who is the founder of Black Girls CODE, will be speaking.  I am live-blogging this event as well, so watch this post for updates starting around 12:15PM EST.  If you want to learn more about Black Girls CODE, check out this MSNBC interview.

12:15PM

Denise McInerney is introduced first – she asks how many people were at the first WIT lunch, back in 2002 (I am pretty sure Denise has been a part of WIT since its inception – a long-time leader within the community).  The WIT luncheon has grown a lot in the past 10+ years – today’s lunch has over 900 attendees.  Denise brings out Kimberly Bryant – such a different setting this year, just Denise and Kimberly on stage.  Denise is going to ask a few questions, then open it up to the audience and people watching on PASStv – you can tweet your questions and include the #passwit hash tag.

Black Girls CODE is a non-profit organization started in the Bay Area in 2011.  What really drove Kimberly to make a change when she recognized that her daughter, who was 12 at the time, might be following in her footsteps.  Never thought her daughter was an engineer.  But she was a heavy gamer (World of Warcraft, D&D) and spent a lot of time on the computer.  Her daughter was at the age where she could learn and create with a computer – and that was a life-changing moment for her.  Her daughter first wanted to grow up and be a game tester :)  Once she went up to a programming camp, she saw that the environment actually allowed her to create, not just be a participant.  As a parent, Kimberly noticed that she was only one of three girls at the summer camp, and the only person of color at the camp (out of about 40 campers total).  At that point, Kimberly knew she had to make a difference, not just for her daughter, but for other daughters.

Question from Denise: “Why is still so hard to get girls and young women interested in technology?”  Kimberly cites a Girl Scout study that showed tha tif you surveyed girls BEFORE they get to middle school, over half the girls show an interest in STEM, but by the time they get to high school it’s less than 5%.  In some cases, girls don’t have support from parents and teachers.  There are fewer opportunities for girls to flex the STEM skills.  Kimberly says she hates the pink aisle.  Do Legos need to be pink?  (ES: They don’t, I grew up without pink or purple Legos and played with them all the time.)

What type of programs does Black Girls CODE run?  The secret sauce is the environment of girls in the environment to do coding and engineering and they have relatable leadership – the women that come in to teach the programs.  Over 75-80% of the instructors are women.  They are reflections of what the girls can become, and that gives the girls the ability to see the possibility.  Kimberly had a counselor who said, ‘You’re good in math and science, you should go into engineering.”  Kimberly didn’t know what that looked like – what does an engineer do, what do they look like?  But if you’re able to actually see that, suddenly you have an idea of what you can really do.

Denise asked what languages are taught via Black Girls CODE?  Kimberly explained that in the beginning they didn’t know what the girls would be willing to learn, so it was open in the beginning.  The goal was to always teach them Ruby – and Kimberly had a core team that knew Ruby.  Also did some testing with Python, but do a lot with open source learning.  Have also started to talk to organizations about coding – she has talked with Lynn Langit, and Lynn’s program (Teaching Kids Programming) teaches Java.

Question from Denise: How can people who want to bring STEM education to kids get started doing that?  There are so many opportunities for technology professionals.  We are at the beginning of this code movement – but we are lacking in teachers that can teach these skills.  Look for opportunities to give back in the school district where your kids are.  We need more than after-school programs and camps.  Black Girls CODE has over 2000 volunteers across the US, there are multiple chapters.  There is a need to talk to students and parents about what we (as women) do in our careers.  (ES: I find it interesting that she mentioned that parents need to hear that discussion as well.)

Kimberly believes that kids can start learning about technology at grade 1.  (ES: I agree – my kids have had a tech class since kindergarten.  Last year, as a 3rd grader, my son put together a PowerPoint presentation.)  Starting to introduce technology in high school is too late.  We need computer science to be counted as a high school credit – it shouldn’t take the place of math or science, it’s in addition.

Denise: Many companies have released diversity statistics.  Does Kimberly talk to attendees about the culture of tech and what it might be like to have a career in tech.  Kimberly states that they do – they try to prepare students to be active participants, and also prepare them for what challenges they might face within the data environment.  Changing the community is not quick thing – it’s a continual effort and requires some difficult conversations (then followed by action).

Over half of the women who enter tech fields drop out at the half way point.  Kimberly says she can relate to this personally.  She understands what it’s like to get in to the career and then see the glass ceiling.  Often, women don’t have the support network to break through that glass ceiling.  The role of mentors, sponsors, and advocates is so important.  On average, most women CEOs come to one company and stay there for 20+ years – that’s how the majority of women CEOs get there.  Women need to stay in the pipeline longer in order to get to the top – but a welcoming and positive environment will help.  Advocates and sponsors for women, within those communities, are needed.  Need more male advocates and mentors to help women get to the next level.  Also, women need to be willing to take the risk to get to that next level.

Denise opens up the discussion for questions from the audience.  It’s mentioned that only 15% of attendees here at PASS Summit are women.  (ES: Really?  15%?)

One of themes from today and Kimberly’s message: mentors are needed.  Both and male and female.  Kids need role models, college students need them, women in technology need them.  (ES: I’d argue that everyone needs a mentor.  Ask yourself: can you be a mentor to someone?  I bet you can.  And don’t be afraid to go ask for – seek out – a mentor for yourself.)

Work culture cited as a top reason that women leave technology.  How do we change that?  Kimberly says to hire more women.  If there’s a company with its heart in diversity, and there’s isolation in the company still, need to change it from the ground up and from the top down, and to do that, need to get more women into the organization.  (ES: That’s not a complete answer, in my opinion, I think it’s more than just getting more women into a company.  You have to understand what the barrier is – what’s the resistance?  Then, you need to figure out how to change that.  And I don’t know if it’s a one-size-fits-all in terms of the barrier – there might be a huge variety of barriers.)

Input from an attendee: go to local school career fairs and talk about IT.  The issue isn’t having to choose between two candidates, it’s trying to get one qualified candidate.

Jes asks how we can get kids to understand that technology skills are important – they’re not just a degree.  Kimberly – we agree, technology skills provide just one tool in a person’s toolkit.  This is why it’s important to get computer science into school, so then it becomes a tool that they can use as they’re learning science, math, and even in non-science courses.

As women we need to be advocates for each other.  (ES: Agreed, we do.)

Kalen has a challenge to parents: talk to your boys about smart women and how they’re not someone to fear.

One of my mentors, Allen White, stands up to ask a question.  Allen has been in IT for 40 years.  He asks, “What can I focus on so I don’t make “bad” choices, since I am not a female, nor a person of color?”  Kimberly tells him to be cognizant, make his company inclusive, to help someone who’s “different” from him.  He’s done all that

The post PASS Summit 2014: WIT Lunch appeared first on Erin Stellato.

Capping off a fantastic third week of presentations (see #SQLSatSLC & #SQLSatOregon), I’m speaking Thursday 11/6/2014 at 1:30p at the Professional Association for SQL Server International Summit.  It’s an honor to be chosen to speak at this event for a fifth time.  This summit’s topic:

To The Cloud, Infinity, & Beyond: Top 10 Lessons Learned at MSIT

I’ll be joined by my colleague, Principal Architect Rob Beddard a brilliant, no-nonsense engineer who’s shepherding much of the MSIT effort.

Much of the content may not strike you as profound, e.g., you’ll know all about planning, or about the I/O challenges.  Yet I know from surveying my peers that many of us have gaps.  I’m confident you’ll walk away with new insights.  Here’re details.

Session Summary:  
Microsoft IT's Cloud strategy involves migrating hundreds of applications to Azure.  The scale on which it is being done may be unprecedented. Lessons Learned are numerous. This session exposes the most important—& painful—ones.

Dear Readers, think about that—we’re migrating literally thousands of apps.

Here’s additional info about the session.

Session Goal 1: Understand that forklifting applications to the Cloud has unexpected challenges.
Session Goal 2: Understand that many fundamental best practices which have served us well on-prem remain important—& now we have new nuances to master.
Session Goal 3: Understand the value of leveraging MSIT's lessons learned has the potential to save an organization weeks or months of time & hundreds of thousands, perhaps millions of dollars.

This is an exciting topic the work for which I'm intimately involved. I'm extremely enthusiastic about sharing the fruits of our efforts with the Community.  Join us!

Editor’s note: The following post was written by Dynamics CRM MVP Leon Tribe

Using Entity Mapping For Dynamics CRM Solution Documentation and Design

It is sometimes the case we are thrown at a CRM system with no documentation and very little knowledge of how the system works and how it has been customized. Perhaps the system has gone out of use and is being revitalised or it is a CRM system from a different country and being localized and repurposed.

Obviously, we can review the system’s customisations and use a tool like Tanguy Touzard’s ToolBox for Dynamics CRM with its Metadata Document Generator to generate a report of the custom fields, but this still does not give a lot of insight into how the system is used.

There is another way to gain insight into an otherwise unknown system, which is through the entity model. How the entities in the system are related to each other provides insight difficult to gain from other techniques.

To show what I mean, I spun up a trial through Microsoft Office Demos. This is a great way for partners to spin up demos because they last for 90 days, rather than the usual 30 days. Another site which also offers 90 day trials of Dynamics CRM and Office 365 is Microsoft Dynamics Demos, which uses DemoBuilder. Both offer vertical samples for pre-sales demonstrations and are well worth checking out.

One word of warning though, unlike the openly available 30 day trials which are up in minutes, these take a few hours to provision but, for the content, are well worth the wait.

Once up, I began mapping the entity relationships. Obviously, if I was to map all the entities in Dynamics CRM, the result would be very complex and largely unreadable so I applied the following rules:

•          Only map entities which have records stored against them in the system
•          Only map those visible in Advanced Find (eliminating a lot of ‘system’ entities like Activity Party and Audit History)

Following these rules, I started with the Account entity and crawled through the structure of the system, mapping related entities revealed in the top menu bar and on the form as lookups.

In the end I removed the Activity and User entities because they infiltrate the entire system. Their ubiquity does give a clear indication that the management of users and activities are key elements of the standard CRM system. Another rule I sometimes adopt, when dealing with production systems, is to start out only mapping custom entities as this is where the insights often lie in modified systems.

Once I got the result into Visio and tried to minimise entity lines crossing over, this was the result.

There are still two lines crossing over but to eliminate these was impossible (at least for me) without removing the contact entity.

Considering this as a network diagram, there are two sources of insights:

•          Which entities have the most connections
•          Which entities are clustered together

In the case of the first source, the key entities are:

•          Product (8 connections)
•          Lead (5 connections)
•          Contact (5 connections)
•          Account (5 connections)
•          Case (5 connections)

Focussing on these, we see these are the centres of the major parts of the CRM system:

•      Product Management (Product)
•      Contact Management (Accounts/Contacts)
•      Sales Management (Lead)
•      Enquiry/Service Management (Case)

These parts of the system also occupy regions in our diagram, addressing our second point.

We now have an insight into the ‘modules’ of our system which can be used to focus our investigation of the system to certain areas or can be used for sections in our documentation of the system.

If you are thrown at a system with minimal documentation, consider mapping the entities, like I have done above. This will provide insight into the key entities of the system and how it is modularized. This will give you a mental framework for approaching the system and also a way of attacking future investigations, focussing on specific areas.

About the author

Leon helps others make the world a better place by using technology to make them more efficient and effective. The technology he employs is Dynamics CRM. He has worked with CRM systems since the late 1990s and now works as a Principal Consultant for Oakton in Sydney, Australia. An engaging public speaker, highlights include presenting at Microsoft’s Headquarters at Redmond and co-presenting at Microsoft’s offices in Sydney to hundreds of not-for-profit representatives. In recognition of his passion and focus on Dynamics CRM, Leon was awarded Microsoft’s Most Valuable Professional (MVP award) in 2009.  Follow Leon on Twitter

 About MVP Mondays

The MVP Monday Series is created by Melissa Travers. In this series we work to provide readers with a guest post from an MVP every Monday. Melissa is a Community Program Manager, formerly known as MVP Lead, for Messaging and Collaboration (Exchange, Lync, Office 365 and SharePoint) and Microsoft Dynamics in the US. She began her career at Microsoft as an Exchange Support Engineer and has been working with the technical community in some capacity for almost a decade. In her spare time she enjoys going to the gym, shopping for handbags, watching period and fantasy dramas, and spending time with her children and miniature Dachshund. Melissa lives in North Carolina and works out of the Microsoft Charlotte office.

I’ve been suffering from tendonitis for a few months now, a combination of lifting our new 12kg addition to the family (Baby TechHead), and also the fact that I wasn’t able to be particularly active for many months during my recent treatment.

I’ve started to look at ways to be healthier whilst working day to day, and like many of you reading this, it involves using a keyboard and mouse/trackpad for extended periods of time.

I’m adjusting the way I work in a few areas, with the most significant being replacing my traditional desk with an electric height adjustable standup desk.  I’ll save the details on my new standup desk  for another post, though I can say that after using it for a couple of months, I much prefer this style of working.  But anyway I digress, as this wasn’t the topic for this post.  

This post is about my looking for a solution to help alleviate and, hopefully, assist in the healing of my tendonitis on my wrist.  I have in the past used ergonomic keyboards, mainly from Microsoft, and have found them good, though since beginning to use a second hand MacPro as my main work computer for the past few years I have found myself using the standard horizontal layout Apple Mac keyboard by default.  Which doesn’t really offer any ergonomic advantages.

With my right/mouse driving wrist being the hand needing the TLC I am looking for something to support my wrist whilst driving a mouse.  I’m familiar with the typical gel filled wrist support mouse mats that you can get, though have never tried one.  Reading reviews on Amazon and the like, these types of wrist support devices come with mixed reviews.  However, after some further trawling of the inter-web I stumbled across a wrist support device called, wait for it…. a Wrist Donut.  

What is a Wrist Donut I hear you say?  

Well, it’s a plastic bean/bead filled bean-bag that wraps around your wrist to support it at the right angle when either using your keyboard or mouse.  

Instantly attracted to the rather cool name I looked into the Wrist Donuts some more and after reading the rather compelling list of advantages  for using a Wrist Donut, I have purchased my own navy blue “donut” to try.  I also liked the idea that this wrist support was, apparently comfortable to wear, and was permanently (until you took it off of course ) attached to your wrist.  They come is decent selection of colours and you can get them customised by having something, such as your name/slogan, etc, added to them.  

My Wrist Donut should be arriving within the next couple of days, so I’ll let you know how I get on with it once it has arrived and I have been using it for a week or two, so as to give it a good try-out.

In the meantime, if you want to take a look, head on over to the Wrist Donut site here.   They are made by a small business based in Devon, which also attracted me to buying one for a try as I like to support local business where possible.

Have you suffered from tendanitis or similar and work in IT?  If so, what worked for you?  

The post Healthier IT Working – Wrist Support: Part 1 appeared first on TechHead and was written by Simon Seagrave.

One of my original goals was to be more reporter than editorial writer, but I’ve found that the editorial part is important too, though much harder to do. I’ve spent quite a few hours this week trying to write editorial comments to preface the summaries I’m doing of the minutes of the PASS Board of Directors meetings. Writing the summary isn’t too bad, but I find the editorial part challenging. What is worth calling out? Is my perception of a discussion or decision one that others will see, or need to see?  Am I assuming too much or too little about what the reader knows about the topic? Am I talking about the good along with the not so good? I’m leveraging various friends (thank you!) for input and have gotten good feedback. It’s hard to write editorial content without diving too far into activism, but at the same time an editorial with no ideas or views doesn’t illuminate as well as it should.

I’ve definitely learned that I have to watch for places where I’m impatient, frustrated, etc, about a topic. Those are often important topics, but all the more reason to try to find the way to explain the issue clearly and calmly. I had one today where I wrote that something “should have been done sooner” and the feedback I got was “does that matter?”, and that was right. It didn’t, at least as written, add value.

I used the word challenging, but I think daunting might be a better fit. Every time I hit publish on one of these I can do good, or not, and I can maintain my reputation or decrease it. High stakes, or at least it seems right now. I’m hoping to get a few more done prior to the Summit so I can get a wider range of feedback. It remains unclear to me if the approach and the vehicle are the correct ones.

Read more of this story at Slashdot.

Read more of this story at Slashdot.

Read more of this story at Slashdot.

After more than two years at WTF Inc., I thought I'd seen everything that could be done wrong actually done wrong in the worst possible way. Whether it was DBAs who wcouldn't administer a database if their lives depended upon it, managers who wcouldn't manage anything, or business people who simply could not understand the concept of save a dollar today, spend ten tomorrow to fix it.

After that dalliance, I'm back in my chosen field. While crazy things sometimes get done in insane ways, it's usually in the name of beating the competition to market, and (almost) always with the understanding that it will be fixed later - at a price.

However, this one struck me as sooo wtf that I'm not even going to try to anonymize it.

Every project from hello world on up has a source tree. It might be as simple as a single directory with one or more source files, or it could be an entire hierarchy of packages, common and external libraries, and so forth. The one thing they all have in common is that the one or more main programs in a project are all applications that pertain to that project. You never see two unrelated projects sharing the same source tree. They might share a common shared library, but not the same source tree. It just isn't done.

Or so I thought.

At my present firm, the culture specifies that for major architectural/code reviews, there must be one very senior member from an unrelated team/department present, and that individual has veto authority on anything that's said or presented. This is to allow an unbiased opinion to be offered without threat of reprisal by the manager.

As one of the more senior folks, I was volunteered for this task at a department that could only be linked back to me from five levels up. I didn't know any of these people, and so had no axe to grind. I went in with an open mind.

When they described their project, for the most part, their approach, level of scalability and parallelism, use of database, messaging and services, etc. made sense. Then they showed me their repository tree. It did not make sense. There were thirteen different (unrelated) projects in there. Together:

	MasterRepository
	  |
	  |-- classes
	  |-- srcproject1
	  |    |-- com
	  |         |
	  |         |-- company
	  |               |-- business
	  |               |-- comm
	  |               |-- gui
	  |               |-- services
	  |               |-- util
	  |-- srcproject2
	  |    |-- com
	  |         |
	  |         |-- company
	  |               |-- business
	  |               |-- comm
	  |               |-- gui
	  |               |-- services
	  |               |-- util
	  | ...
	  |-- srcproject13
	  |    |-- com
	  |         |
	  |         |-- company
	  |               |-- business
	  |               |-- comm
	  |               |-- gui
	  |               |-- services
	  |               |-- util

Naturally I queried why all the other projects were in there together, and why all of their source directories were configured as source directories in this project, I was told that they were told that it's easier this way.

Though I began to shudder, I just had to know, so I looked into the source trees. There were numerous classes with the same names and implementing the same interface at the same package path but in multiple source trees. Thus, auto-complete could pick any one of them because they all had the same signature, albeit subtly different implementations. As you might imagine, this led to all sorts of debugging fun at run time.If they were lucky, something would be null and it would dump the stack. If they were less than angelic, it wouldn't perform the calculation in quite the right way. If they had been particularly bad, if wouldn't perform the calculation in quite the right way only some of the time.

As if this wasn't far enough off the beaten path, I noticed that they were all using the same build/package/deploy mechanism, but at seemingly random intervals. It turns out that to prevent each of the teams from blocking any of the other teams from doing a deployment-at-will, all of the units of work were designed to be less than one day. That is, you had one day to design, code and test your work before committing. Thus, if the other team needed to deploy, they could grab the entire tree - including all of the tiny units of work done by the other teams - that compiled but didn't necessarily accomplish anything useful - package it up and deploy it.

Of course, if someone happened to be changing some piece of functionality that was shared, but hadn't yet made all of the one-day-of-work units that comprised the larger logical change, it was possible to get a melange of code that could best be described as: it might work.

Needless to say, the entire department was experiencing very high levels of instability, blocking deployment collisions when some piece of code absolutely could not be deployed without the rest of the related changes - without breaking the other projects.

When I pointed out the folly of all of this, they told me that the boss four levels up had experienced massive problems with multiple projects under his control, and decided that a single source tree would only need to be fixed once and would thus improve throughput. I told them that all of the projects had to be liberated separated into individual project source trees. Anything that was common would need to be its own project and released on its own schedule. When they were informed that this had to be done, they said they were under a mandate from four levels up. I went to the common manager five levels up, explained the situation, and that this was the reason for all the red on the dashboard.

The order to break it all apart was given.

Our environment is fully built using VMware Auto Deploy 5.1. All the clusters have their own ruleset and we’re very content on how easily we update our hosts and deploy new hosts. However, recently we ran into a major issue with Microsoft Cluster Services inside VMs in combination with VMware Auto Deploy. In a scenario with an “old fashioned” MCSC cluster across boxes with Physical RDMs shared between the two cluster nodes (VMs), these RDMs seem to be stopping an Auto Deployed ESXi host from reconnecting to vCenter Server after a reboot. 

A normal reboot of an Auto Deployed ESXi host goes like this:

– Power ON
– Ask DHCP for IP address and PXE boot server address
– Connect to TFTP server
– Talk to Auto Deploy Server
– Download ESXi image based on rule set
– Boot that image in a default configuration
– Report to vCenter Server
– Retrieve and apply host profile that holds the proper configuration settings
– Exit maintenance mode

When MSCS RDMs are present, the following will happen (taken from VMware KB 1016106): “During the start of an ESXi host, the storage mid-layer attempts to discover all devices presented to an ESXi host during the device claiming phase. However, MSCS LUNs that have a permanent SCSI reservation cause the start process to lengthen as the ESXi host cannot interrogate the LUN due to the persistent SCSI reservation placed on a device by an active MSCS Node hosted on another ESXi host.”

For a normal ESXi host, the “only” issue is the longer boot time, but with an Auto Deployed host, that longer scan time will give time outs in the process of connecting to vCenter Server and eventually fail to reconnect or application of the host profile leaving the host in a rather useless state. For us this means that for every host reboot we first need to disconnect the RDMs from the host, perform the reboot, make sure the host is configured correctly and then reconnect the RDMs. Not very convenient.

The mentioned VMware KB 1016106 explains how to use the “perennially-reserved=true” setting. This setting will be set on a per LUN (RDM) basis:

esxcli storage core device setconfig -d naa.id --perennially-reserved=true

For an Auto Deployed host this won’t work however, because the setting needs to be applied through a host profile which because of the slow scanning, is never applied. VMware Support acknowledged the issue and said there will be a fix in 5.1 Update 3 and 5.5 Update 3. Until that time we’re going back to local installed ESXi hosts that run VMs which are part of a MCSC cluster.

Read more of this story at Slashdot.

It’s been a bit light on technical posts here over the last few months but now that summer’s over I’ll be ramping up again with lots of cool stuff planned.

First up is a question that came up on the MCM distribution list this morning. There was a discussion of fast recovery (which I explained in detail in the post Lock Logging and Fast Recovery back in 2009), but in a nutshell is the ability of Enterprise Edition to allow access to a database after the REDO (rolling forward committed transactions) phase of crash recovery has completed and before the UNDO (rolling back uncommitted transactions) phase of crash recovery has completed. The idea is that UNDO can take much longer than REDO, so early access to the database is a good thing, hence it being an Enterprise Edition feature (from SQL Server 2005 onward).

The question essentially became: when is fast recovery used?

The answer is that it’s used whenever a database is started up and needs to have recovery run on it. This means fast recovery will be used:

• When SQL Server starts up after a crash or shutdown where a database was not cleanly shut down
• After a cluster failover
• After a database mirroring failover
• After an availability group failover
• When a database state is changed to ONLINE and crash recovery needs to be run

Note that I did not include:

• When restoring a database from backups
• When bringing a log shipping secondary database online (this is restoring from backups)

Fast recovery is NOT used during a restore operation. You’ll read in some places online that it is, but those places are incorrect.

So why isn’t it used during a restore sequence?

It’s to do with the underlying mechanism that allows fast recovery. Operations that make changes to a database are logged, and the log record includes a bitmap of what locks were held at the time (examples of this are in the blog post I referenced above). When crash recovery runs, the REDO phase also acquires all the locks necessary to do the UNDO phase, as the REDO phase knows which transactions in the log being recovered need to be rolled back. At the end of the REDO phase, access can be given to the database because recovery can guarantee that no user can block the UNDO phase, as the UNDO phase locks are already held.

So why doesn’t that mechanism work for restores? Well restore doesn’t do one REDO and one UNDO like crash recovery does. For each backup that is restored in the restore sequence, the REDO phase of recovery is performed. This avoids having a really long REDO phase at the end of the restore sequence (which could be, say, a week’s worth of transactions spread over tens or hundreds of backups), and having to have a huge transaction log to hold all those log records.

At the end of the restore sequence, all necessary REDO has already been performed, but the REDO operations have NOT been acquiring UNDO locks. The UNDO locks aren’t acquired because UNDO isn’t likely to be the next phase during a restore sequence. It’s likely to be another restore operation. In that case, it’s likely that some of the transactions that were uncommitted at the end of the last restore become committed during the next restore, so if UNDO locks had been acquired, they would have to be released again. This would involve either rescanning the log records involved or keeping track of which in-restore transactions had acquired which locks. Either of these would be complicated and time consuming, so the benefit hasn’t been deemed worthwhile for the engineering effort involved.

So no fast recovery during restores.

But hold on, I hear you say, database mirroring is just a constant REDO process so how come fast recovery works for that? Back in SQL Server 2005, when a database mirroring failover occurred, the database was momentarily set offline so that full crash recovery would be run when the database came back online, hence allowing fast recovery to work. From SQL Server 2008 onward, that doesn’t happen any more, so there is a mechanism that figures out what UNDO locks are necessary when a mirroring failover occurs, allowing fast recovery behavior. I guess technically that same mechanism could be ported over to the restore code base, but I think it would be difficult to do, and I don’t think there’s enough demand to make the engineering effort and possible destabilization of the restore code worthwhile.

Hope this helps explain things – let me know if you have any questions.

The post When is fast recovery used? appeared first on Paul S. Randal.

All my posts this year have been about knee-jerk reactions to wait statistics, but in this post I’m deviating from that theme to talk about a particular bug bear of mine: the page life expectancy performance counter (which I’ll call PLE).

What Does PLE Mean?

There are all kinds of incorrect statements about page life expectancy out there on the Internet, and the most egregious are those that specify that the value 300 is the threshold for where you should be worried.

To understand why this statement is so misleading, you need to understand what PLE actually is.

The definition of PLE is the expected time, in seconds, that a data file page read into the buffer pool (the in-memory cache of data files pages) will remain in memory before being pushed out of memory to make room for a different data file page. Another way to think of PLE is an instantaneous measure of the pressure on the buffer pool to make free space for pages being read from disk. For both of these definitions, a higher number is better.

What’s A Good PLE Threshold?

A PLE of 300 means your entire buffer pool is being effectively flushed and re-read every five minutes. When the threshold guidance for PLE of 300 was first given by Microsoft, around 2005/2006, that number may have made more sense as the average amount of memory on a server was much lower.

Nowadays, where servers routinely have 64GB, 128GB, and higher amounts of memory, having roughly that much data being read from disk every five minutes would likely be the cause of a crippling performance issue

In reality then, by the time PLE is hovering at or below 300, your server is already in dire straits. You’d start to be worried way, way before PLE is that low.

So what’s the threshold to use for when you should be worried?

Well, that’s just the point. I can’t give you a threshold, as that number’s going to vary for everyone. If you really, really want a number to use, my colleague Jonathan Kehayias came up with a formula:

Even that number is somewhat arbitrary, and your mileage is going to vary.

I don’t like to recommend any numbers. My advice is for you to measure your PLE when performance is at the desired level – that’s the threshold that you use.

So do you start to worry as soon as PLE drops below that threshold? No. You start to worry when PLE drops below that threshold and stays below that threshold, or if it drops precipitously and you don’t know why.

This is because there are some operations that will cause a PLE drop (e.g. running DBCC CHECKDB or index rebuilds can do it sometimes) and aren’t cause for concern. But if you see a large PLE drop and you don’t know what’s causing it, that’s when you should be concerned.

You might be wondering how DBCC CHECKDB can cause a PLE drop when it does disfavoring and tries hard to avoid flushing the buffer pool with the data it uses (see this blog post for an explanation). It’s because the query execution memory grant for DBCC CHECKDB is miscalculated by the Query Optimizer and can cause a big reduction in the size of the buffer pool (the memory for the grant is stolen from the buffer pool) and a consequent drop in PLE.

How Do You Monitor PLE?

This is the tricky bit. Most people will go straight to the Buffer Manager performance object in PerfMon and monitor the Page life expectancy counter. Is this the right approach? Most likely not.

I’d say that a large majority of servers out there today are using NUMA architecture, and this has a profound effect on how you monitor PLE.

When NUMA is involved, the buffer pool is split up into buffer nodes, with one buffer node per NUMA node that SQL Server can ‘see’. Each buffer node tracks PLE separately and the Buffer Manager:Page life expectancy counter is the average of the buffer node PLEs. If you’re just monitoring the overall buffer pool PLE, then pressure on one of the buffer nodes may be masked by the averaging (I discuss this in a blog post here).

So if your server is using NUMA, you need to monitor the individual Buffer Node:Page life expectancy counters (there will be one Buffer Node performance object for each NUMA node), otherwise you’re good monitoring the Buffer Manager:Page life expectancy counter.

Even better is to use a monitoring tool like SQL Sentry Performance Advisor, which will show this counter as part of the dashboard, taking into account the NUMA nodes on the server, and allow you to easily configure alerts.

Examples of Using Performance Advisor

Below is an example portion of a screen capture from Performance Advisor for a system with a single NUMA node:

On the right-hand side of the capture, the pink-dashed line is the PLE between 10.30am and about 11.20am – it’s climbing steadily up to 5,000 or so, a really healthy number. Just before 11.20am there’s a huge drop, and then it starts to climb again until 11.45am, where it drops again.

This is typically what you would see if the buffer pool is full, with all the pages being used, and then a query runs that causes a huge amount of different data to be read from disk, displacing much of what’s already in memory and causing a precipitous drop in PLE. If you didn’t know what caused something like this, you’d want to investigate, as I describe further down.

As a second example, the screen capture below is from one of our Remote DBA clients where the server has two NUMA nodes (you can see that there are two purple PLE lines), and where we use Performance Advisor extensively:

On this client’s server, every morning at around 5am, an index maintenance and consistency checking job kicks off that cause the PLE to drop in both buffer nodes. This is expected behavior so there’s no need to investigate as long as PLE rises up again during the day.

What Can You Do About PLE Dropping?

If the cause of the PLE drop isn’t known, you can do a number of things:

1. If the problem is happening now, investigate which queries are causing reads by using the sys.dm_os_waiting_tasks DMV to see which threads that are waiting for pages to be read from disk (i.e. those waiting for PAGEIOLATCH_SH), and then fix those queries.
2. If the problem happened in the past, look in the sys.dm_exec_query_stats DMV for queries with high numbers of physical reads, or use a monitoring tool that can give you that information (e.g. the Top SQL view in Performance Advisor), and then fix those queries.
3. Correlate the PLE drop with scheduled Agent jobs that perform database maintenance.
4. Look for queries with very large query execution memory memory grants using the sys.dm_exec_query_memory_grants DMV, and then fix those queries.

My previous post here explains more about #1 and #2, and a script to investigate waits occurring on a server and link to their query plans is here.

The "fix those queries" is beyond the scope of this post, so I’ll leave that for another time or as an exercise for the reader ☺

Summary

Don’t fall into the trap of believing any recommended PLE threshold that you might read online. The best way to react to PLE changes is when PLE drops below whatever your comfort level is and stays there – that’s the indication of a performance problem that you should investigate.

In the next article in the series, I’ll discuss another common cause of knee-jerk performance tuning. Until then, happy troubleshooting!

The post Knee-Jerk PerfMon Counters : Page Life Expectancy appeared first on SQLPerformance.com.

We are working to make Azure the best cloud platform for big data, including Apache Hadoop. To accomplish this, we deliver a comprehensive set of solutions such as our Hadoop-based solution Azure HDInsight and managed data services from partners, including Hortonworks. Last week Hortonworks  announced the most recent milestone in our partnership and yesterday we announced even more data options for our Azure customers through a partnership with Cloudera.

Cloudera is recognized as a leader in the Hadoop community, and that’s why we’re excited Cloudera Enterprise has achieved Azure Certification. As a result of this certification, organizations will be able to launch a Cloudera Enterprise cluster from the Azure Marketplace starting October 28. Initially, this will be an evaluation cluster with access to MapReduce, HDFS and Hive. At the end of this year when Cloudera 5.3 releases, customers will be able to leverage the power of the full Cloudera Enterprise distribution including HBase, Impala, Search, and Spark.

We’re also working with Cloudera to ensure greater integration with Analytics Platform System, SQL Server, Power BI and Azure Machine Learning. This will allow organizations to build big data solutions quickly and easily by using the best of Microsoft and Cloudera, together.  For example Arvato Bertelsmann was able to help clients cut fraud losses in half and speed credit calculations by 1,000x.

Our partnership with Cloudera allows customers to use the Hadoop distribution of their choice while getting the cloud benefits of Azure. It is also a sign of our continued commitment to make Hadoop more accessible to customers by supporting the ability to run big data workloads anywhere – on hosted VM’s and managed services in the public cloud, on-premise or in hybrid scenarios.

From Strata in New York to our recent news from San Francisco it’s exciting times ahead for those in the data space.  We hope you join us for this ride!

Eron Kelly
General Manager, Data Platform

Daniele worked at a pharmaceutical firm that had an old web application that allowed commercial customers to look up information. Since the data was quite complicated, there were numerous fields that needed to be queried in order to populate the form.

Unfortunately, as the amount of data in the system grew, the time to load the form grew as well. And grew. And grew.

Fortunately, the DBA in charge of setting up the underlying tables was actually quite capable at setting up tables with the proper relationships. For example, an address consists of street, city, zip, province and country. A country can contain multiple provinces which can contain multiple cities which can contain multiple zip codes, and so forth. As it was well organized, the database was not the problem; the source of the slowness was likely in the code.

And what code it was. The programmer that engineered this had to have revered this piece of brillance as well. They decided that they would support substantial data growth by querying the data in parallel. Yes, there would be a separate query for each field - run in a separate thread - in parallel. In other words, all of the queries had essentially the same where-clause (except for the joins); only the fields that were selected were different. For cases where one field depended upon another, the dependency was handled like this in the corresponding query classes, which all followed the same pattern:

   class StreetQuery implements Thread {
      // Street names can be duplicated. We need to know in which 
      // city this street resides in order to query for it.
      private CityQuery city;
	  
      private boolean finished = false;
	  
      public StreetQuery(CityQuery city) {
        this.city = city;
      }
	  
      public boolean isRunning() {
        return !finished;
      }
	  
      public void run() {
        // Wait until query on which we depend finishes
        while (city.isRunning());
		
        // do query here, using any results from dependent queries as needed
        finished = true;
      }
    }

Daniele replaced all of that with a single stored procedure and the delays were gone.

One can't help but wonder if the author of the original code might have been helping themselves to a few too many sample products...

Editor’s note: In partnership with Microsoft Press, MVPs have been contributing to an ongoing guest series on their official team blog. Today’s article is from Lync MVP Desmond Lee which is the 45^th in the series. 

PowerShell and SQL Stored Procedure

PowerShell is the window to managing many aspects of a Lync Server 201x environment. Coupled with the deployment of the Monitoring Server service (or separate role in 2010), extensive data can be collected to support operational and troubleshooting demands. By installing the optional Monitoring Reports on selected SQL Server Reporting Services (SSRS) instances, common types of reports are easily available at your disposal.

As Lync deployments become ever more popular and widespread, it became evident that the Lync Management Reports shipped with the product do not address certain reporting requirements needed in the field. Since persistent and dynamic Lync data are stored in various SQL databases in the backend as well as on each Front-End Server, the act of firing up SQL Management Studio, connecting to the right server and executing a SQL query will enable you to put together a user-defined report. You can find many excellent blog posts that walk you through the intricacies of constructing the often complicated looking SQL statements.

Microsoft discourages building SQL queries to pull information directly from the underlying database tables for a good reason. Besides the inherent complexity and tediousness involved, such as the use of multiple table JOINs, the database schema, table relationships and naming conventions may change in future updates and product versions. Hence the risk of breaking customized SQL queries is very real indeed.  Continue reading full article here.

About the author

Desmond is recognized as a Microsoft Most Valuable Professional (MVP Lync

Server) for his devoted passion and long-time volunteer work in the IT community. He is a Microsoft Certified Trainer (MCT) and founder of the Swiss IT Pro User Group (www.swissitpro.com). An established speaker at major international and regional events, Desmond contributes frequently to several highly rated publications and engages as a moderator in popular Microsoft public forums/newsgroups. You can follow his IT adventures at www.leedesmond.com 

About MVP Mondays

The MVP Monday Series is created by Melissa Travers. In this series we work to provide readers with a guest post from an MVP every Monday. Melissa is a Community Program Manager, formerly known as MVP Lead, for Messaging and Collaboration (Exchange, Lync, Office 365 and SharePoint) and Microsoft Dynamics in the US. She began her career at Microsoft as an Exchange Support Engineer and has been working with the technical community in some capacity for almost a decade. In her spare time she enjoys going to the gym, shopping for handbags, watching period and fantasy dramas, and spending time with her children and miniature Dachshund. Melissa lives in North Carolina and works out of the Microsoft Charlotte office.

What are the real world consequences to signing up for a Twitter or Facebook account through Tor and spewing hate toward other human beings?

Facebook reviewed the comment I reported and found it doesn't violate their Community Standards. pic.twitter.com/p9syG7oPM1

— Rob Beschizza (@Beschizza) October 15, 2014

As far as I can tell, nothing. There are barely any online consequences, even if the content is reported.

But there should be.

The problem is that Twitter and Facebook aim to be discussion platforms for "everyone", where every person, no matter how hateful and crazy they may be, gets a turn on the microphone. They get to be heard.

The hover text for this one is so good it deserves escalation:

I can't remember where I heard this, but someone once said that defending a position by citing free speech is sort of the ultimate concession; you're saying that the most compelling thing you can say for your position is that it's not literally illegal to express.

If the discussion platform you're using aims to be a public platform for the whole world, there are some pretty terrible things people can do and say to other people there with no real consequences, under the noble banner of free speech.

It can be challenging.

How do we show people like this the door? You can block, you can hide, you can mute. But what you can't do is show them the door, because it's not your house. It's Facebook's house. It's their door, and the rules say the whole world has to be accommodated within the Facebook community. So mute and block and so forth are the only options available. But they are anemic, barely workable options.

As we build Discourse, I've discovered that I am deeply opposed to mute and block functions. I think that's because the whole concept of Discourse is that it is your house. And mute and ignore, while arguably unavoidable for large worldwide communities, are actively dangerous for smaller communities. Here's why.

• It allows you to ignore bad behavior. If someone is hateful or harassing, why complain? Just mute. No more problem. Except everyone else still gets to see a person being hateful or harassing to another human being in public. Which means you are now sending a message to all other readers that this is behavior that is OK and accepted in your house.

• It puts the burden on the user. A kind of victim blaming — if someone is rude to you, then "why didn't you just mute / block them?" The solution is right there in front of you, why didn't you learn to use the software right? Why don't you take some responsibility and take action to stop the person abusing you? Every single time it happens, over and over again?

• It does not address the problematic behavior. A mute is invisible to everyone. So the person who is getting muted by 10 other users is getting zero feedback that their behavior is causing problems. It's also giving zero feedback to moderators that this person should probably get an intervention at the very least, if not outright suspended. It's so bad that people are building their own crowdsourced block lists for Twitter.

• It causes discussions to break down. Fine, you mute someone, so you "never" see that person's posts. But then another user you like quotes the muted user in their post, or references their @name, or replies to their post. Do you then suppress just the quoted section? Suppress the @name? Suppress all replies to their posts, too? This leaves big holes in the conversation and presents many hairy technical challenges. Given enough personal mutes and blocks and ignores, all conversation becomes a weird patchwork of partially visible statements.

• This is your house and your rules. This isn't Twitter or Facebook or some other giant public website with an expectation that "everyone" will be welcome. This is your house, with your rules, and your community. If someone can't behave themselves to the point that they are consistently rude and obnoxious and unkind to others, you don't ask the other people in the house to please ignore it – you ask them to leave your house. Engendering some weird expectation of "everyone is allowed here" sends the wrong message. Otherwise your house no longer belongs to you, and that's a very bad place to be.

I worry that people are learning the wrong lessons from the way Twitter and Facebook poorly handle these situations. Their hands are tied because they aspire to be these global communities where free speech trumps basic human decency and empathy.

The greatest power of online discussion communities, in my experience, is that they don't aspire to be global. You set up a clubhouse with reasonable rules your community agrees upon, and anyone who can't abide by those rules needs to be gently shown the door.

Don't pull this wishy washy non-committal stuff that Twitter and Facebook do. Community rules are only meaningful if they are actively enforced. You need to be willing to say this to people, at times:

No, your behavior is not acceptable in our community; "free speech" doesn't mean we are obliged to host your content, or listen to you being a jerk to people. This is our house, and our rules.

If they don't like it, fortunately there's a whole Internet of other communities out there. They can go try a different house. Or build their own.

The goal isn't to slam the door in people's faces – visitors should always be greeted in good faith, with a hearty smile – but simply to acknowledge that in those rare but inevitable cases where good faith breaks down, a well-oiled front door will save your community.