|
|
Simple-Talk columnist
-
Posted Monday, January 11, 2010 2:45 PM |
Project managers in IT departments have well-established
ways of describing the different phases of a development project, depending on
the methodology. It all looks very scientific, but forgets the fact that project
teams are just groups of people that, like any other human group, don’t always
behave in rational ways. This has been well-known to the psycho-dynamic
psychologists who studied group behavior, such as Kurt Lewin , Bruce Tuckerman
and Wilfred Bion. The group dynamic of working teams, they noticed, tends to
run in phases. (eg. Forming, Storming, Norming, Performing , Adjourning.
Tuckerman 1965). When groups are under pressure, these phases are different,
and much less adaptive.
I’ve had plenty of experience, both as members of IT teams,
and managing them, and it has convinced me over the years that the group
dynamics of a development team under pressure are neglected at your peril: Even
a well-led task-oriented group has to be very carefully ‘facilitated’. You may,
as a project manager have got ticks in all the boxes, and the team that does the
development may, as individuals, be perfectly sane and well-adjusted, but the
group can, when set to a task for up to 60 hours a week in difficult conditions,
go completely barking mad.
A development team that is being poorly led, facilitated
and directed seems to me to revert so something more like pack behavior, and
tends to go through the following phases, led usually by the pack members
exhibiting the most ‘alpha-male’ characteristics.
The Euphoric Phase.
The excitement of a project
infects the group. Developers flirt with new frameworks and technologies;
everything seems possible and the timescales seem ridiculously generous.
Beautiful, crafted objects and routines are designed, full of comments.
Standards that conform to the industry’s best practices are decided, and
resolutions about meticulous testing are made. Savvy business managers always
choose this phase to introduce extra scope into the project. It will be embraced
by the participants.
The Chill Wind
This is a brief phrase: so brief
that its existence is controversial, like the Quark. The first seeds of doubt
resound around the group with positive feedback, and the celebratory Gluhwein
flush suddenly leaves the cheeks. The sheer angle of the project path home sinks
in, and suddenly confidence collapses.
The Slough of Despond.
The mood of the group has flipped
suddenly, and despair grips the team. The natural competitiveness within the
group focuses on the race to come up with the most doom-laden predictions for
the project. Developers mutter amongst their colleagues about the utter shambles
of the state of the project. Testers get over-excited when they find bugs, and
make huge lists, wringing their hands like Ancient Greek professional mourners.
There is much puzzlement over the increase in the projects’ scope. (See:
Festinger’s concept of Cognitive Dissonance)
The Search for the Guilty.
This is the phase of the project
where a mob consciousness takes over. The mob thrashes around looking for
someone to blame for their state of despair and doom. (cf Lloyd Demaus’s classic
analysis of the Nixon Tapes). The obvious candidates are those developers who
have got on quietly despite the excitement around them and consequentially done
the most work, as they, logically, have created the most bugs. The DBA usually
makes a good candidate too, as they will have been reluctant to have supported
the radical domain-specific data-model that had been proposed by the developers
when heady with euphoria, and will be viewed by the group as being reactionary.
Sacrifices to the fates are planned to assuage their anger. Anyone with an urge
for professional assassination of their colleagues does well to choose this
phase; a negative remark always hurts most when delivered mid project.
The Depressive Position.
After the switchback ride of
elation and despair, reality breaks in. Depending on the nature of the reality,
this could be more uncomfortable for the group than the previous psychotic
despair. They weep for the lost opportunities of the euphoric phase, and tremble
at the closeness of the deadlines. The taste for retribution vanishes.
The Death March
With all creative energies now
spent, the group decides that fate has decreed a long trudge to project
completion, and there is a weary acceptance of this fact. Even realistic and
helpful technical solutions are shrugged off, since their doom seems inevitable
and inescapable. They develop a touching but misguided faith in technologies of
their youth and innocence, as they become more introspective and passive, in
final acceptance of their fate.
Recriminations
Most likely, at some stage in the
death-march, the entire project will be put out of its’ misery by IT management.
If, however, the survivors reach home base, then the bitter recriminations will
start as to whose fault the debacle was. This seems to be a cleansing process
since there is no longer any taste for retribution for anyone perceived to be
guilty. In fact the cleansing process is so effective that the participants
develop a curious amnesia about the mistakes of the project, and the delusions
they suffered. The developers are ready for the next project where, in the first
heady stage, euphoria will once again grip the team.
When we, as an industry, develop a new development
methodology, I always hope, vainly, that the basic lessons of successful team
working have percolated to the IT industry. It is always a bitter
disappointment to me to discover that we continue to ignore the basic pressure
points that prevent people in groups from working productively together, even
when all the participants have the best of intentions.
See:
- Bion, W. R. 1961. Experiences in Groups: And Other Papers. Tavistock
- Demause L Foundations of Psychohistory Chapter 6:
Historical Group Fantasies
- Festinger, Leon; Schachter, Stanley and Back, Kurt. Social
Pressures in Informal Groups; a Study of Human Factors in Housing. Palo Alto,
California: Stanford University Press, 1950.
- Lewin, K. (1947) Frontiers in group dynamics 1. Human
Relations 1, 5-41.
- Miner, J. B. (2005). Organizational Behavior: Behavior 1: Essential Theories of Motivation and Leadership. Armonk:
M.E. Sharpe
- Tuckman, B. 1965. Developmental sequence in small groups. Psychological bulletin, 63, 384-399.
- Weisbord, Marvin R., Productive Workplaces Revisited (2004)
ISBN 0-7879-7117-0
|
-
Posted Monday, December 07, 2009 4:59 PM |
Sometimes, one sits back from the screen, pushes back the keyboard, and thinks 'I've done a good job there'. But is a victory over technology always going to cut any ice with the customer?
It often seems to take the participants by surprise when an IT development project slips its dates; sometimes it is the developers who are least expecting it. Why?
I worked, for some time, as a Manager for a large multinational. By chance, two different divisions wanted exactly the same application for working out the manufacturing cost of a new product. One division wanted a state-of-the-art windowing system, built with all the latest TLAs; the other division just wanted "something that worked". Two different cultures became locked in conflict, like giant lizards. In the end, they grunted, and went their separate ways, unable to agree on using the same product, or commissioning the same development.
As a result, two development projects were born, one radical and exciting, and one conservative and dull. I was called in by both divisions as a technical advisor, so ended up keeping a watching brief on both projects. It was a betting man's dream. The two projects had the same aim, but entirely different visions of how to achieve it. As I still bore the campaign scars from my years in managing my own development projects, I was keen to define clear points in the development process where both they and the business divisions could agree that an aim had been fully met with a deliverable, and that we had a common criterion for successful delivery. I broke down each project into bite-sized chunks, or tasks; specific, measurable, agreed upon, realistic and time-based. It looked reasonable and everyone agreed to these goals. I then sat back and observed, like one of the spectators quaffing champagne from the upland of the Chersonese as they gazed at the Charge of the Light Brigade.
The radical project instantly became the subject of fascination within the company, and the manager in charge found himself giving dazzling presentations to senior executives. I occasionally visited the development office. Bearded, scholarly developers were everywhere; workstations with vast windowing screens proliferated. The office was fitted out in gloriously contemporary fashion, with lots of expensive Danish furniture. Several university departments took an interest in the project, and it got a glowing write-up in the glossy Computer Mags, with a photo of the manager standing by a Unix Workstation, looking imposing and important.
Meanwhile, the conservative project was taken on by a rather anonymous, and unspectacular, outsourcing company, whose local office was a shabby place somewhere in the Essex mud-flats. The red-brick and asbestos building, with metal window frames, seemed to have once belonged to a jobbing engineering company. The genial staff had the appearance of middle-aged secondary teachers, but had a quiet and unflappable demeanor. I visited them once, but I strain to remember much about them.
After six months, the radical project had achieved some spectacular demonstrations, and reports of its progress were sweeping the industry. Breakthroughs had been made, 'new standards' created in user interface design. Their Artificial Intelligence system for doing the costing was acclaimed by many academics. And yet, in terms of real deliverables, the project wasn't making any tangible progress at all against the benchmarks. The project fell into a cycle of exciting demonstrations followed by agonizing delay.
By contrast, and to my enormous surprise, the team from the Essex mud-flats had delivered their project ahead of schedule: It was good, and pleased their users. It ran on existing workstations and was so simple to use that we saved a huge amount on our training budget. They’d even managed to introduce many of the GUI components on which the radical project had majored.
As the department awaiting the completion of the radical project began to gaze enviously at the completed conservative one, it became was clear what had to be done. Scrapping such an exciting project seemed like shooting one’s pet dog, but when the company finally decided to act, many in the team were incredulous. They had achieved so much, they all felt. Ultimately, however, they were like a team of actors who had attempted, and failed, to give the audience "Macbeth" when all they really wanted in the first place was "Charleys’ Aunt"
And so the radical project died, and both departments adopted the conservative application, without complaint.
What sticks in the mind, after all the intervening years is that the complex windowing technology that was being used was so tricky that almost any progress was seen as a glorious achievement. It is the very nature of a development framework that it aims to dazzle. The developers had entered a fascinating world of animated windows, transparent dialog boxes, complex grids, hierarchical graphs and the like. Once embroiled in this world, they got so engrossed in the detail that they didn’t spot the painful fact that they weren't delivering tested functionality to reasonable timescales.
One can easily be led to believe that if a framework is hard work, but looks 'sexy' when mastered, and the developer breaks into a sweat, then progress is being made. The problem is that the intrinsic appeal of a technology can distract a team from heading in a single-minded way to the goal of a deliverable solution. However visually-appealing a technology is, it has, in the end, got to save time for its customers, and be more efficient to use than the system it replaces. That, surely, is the Conditio sine qua non
|
-
Posted Sunday, November 15, 2009 9:17 PM |
When I started work, more years ago than I care to think about, there was a more relaxed approach to vituperation in the workplace. Of course, the nineteenth century was the classic era for the art. Brunel in 1842, for example wrote a memo to one of his employees who had messed up a technical drawing….
Plain, gentlemanly language seems to have no effect on you. I must try stronger language and stronger measures. You are a cursed, lazy, inattentive apathetic vagabond, and if you continue to neglect my instructions, and show such infernal laziness, I shall send you about your business. I have frequently told you, amongst other absurd, untidy habits, that that of making drawings on the back of others was inconvenient, by your cursed neglect of that you have wasted more of your time than your whole life is worth, in looking for the altered drawings you were to make of the Station – they won’t do. I must see you again on Wednesday.
Yes, the engineer of the day dished it out very much as he saw it. Alas, such frankness is impossible nowadays. The art of litigation has reached such heights that it is readily possible to convince a jury against all the sober evidence of the resilience of the human spirit, that such frank communication amounts to ‘verbal abuse’. We all now have to restrain our natural inclinations to plant brisk emails onto people. We don’t know what happened to the recipient of the note above but I have every confidence that his later career prospered.I imagine him going home that evening with a spring in his step, and telling the wife 'Darling, Brunel actually noticed my work today!'
You would imagine, from the screeds of the advice given out to managers, that the receiver of such a memo nowadays would find his soul crushed, and his career in tatters. My own experience has been quite the reverse.
My friend Keith, whose rise as an executive in a vast multinational seemed unstoppable, due mainly to his charcoal suit, ‘intellectual’ glasses and hot ‘one-pagers’, suddenly get a severe reprimand from the higher echelons of the company. He’d been showing a lady executive from an important purchaser around various company sites. He was driving her along the motorway between visits when he was struck by a surge of testosterone that led him, uncharacteristically, to attempt a sudden seduction of her. This maneuver is risky at any time, but most inadvisable whilst driving an extremely expensive company-owned car along a motorway. Unfortunately, his guardian angel must have turned her back on the steamy scene, and the car crashed. It was all rather messy, but nobody was badly hurt. I saw Keith soon after he had received his reprimand, and he was somewhat apprehensive about his career. I put a consoling arm on his shoulder, and told him confidently that he had absolutely no need for pessimism.In fact, i told him, it might do his career a power of good.
I was right, of course. Careers in large companies depend as much as talent or application on the mysterious factor called ‘visibility’. People remember a name long after the reason why they’d heard of it. Keith’s career received a boost from the incident. Whenever a vacancy for a promotion came up, people struggled for names, and somehow Keith’s name always got in there.
“Who is there who could take on the post. How about Keith? Oh yes, heard of him. Now what was it; not sure, but he’s a presentable chap, and he writes pretty hot management summaries. Let’s get him in and interview him.”
And so it went. The power of ‘visibility’ kicked in and Keith continued his brisk progression through the company hierarchy. He had the grace to thank me for my advice and encouragement at a dark moment. I merely shrugged and assured him that the same had happened to me in the past. I'd learned that it is always best to hang on and see what happens. It is visibility as much as talent that gets you up the promotion ladder in a large company.
|
-
Posted Monday, October 19, 2009 2:16 PM |
You know how it is, you’re racing to finish a nice software product that you feel sure is going to be a hit. Then the phone rings
“Hi. Bruce here at PersonnelFinder International. How are you doing?”
“Oh go boil your head, Bruce!”
“No, Phil, you’re supposed to say ‘fine, thanks’, and I say ‘Good, good’. You are no respecter of protocol. Anyway, I have a job for you, in the City of London with a bank. “
“No, I have a brilliant application I’m writing that the public will just love and…”
He mentioned a sum of money.
“Not even for that much a month will I leave the creation of this masterpiece I….”
“No, Phil, a week.”
And so it was, several years ago, that I arrived outside the rather shabby, but enormous, concrete offices of the Imperial Bank, in a dingy street behind Fenchurch Street. I was uneasily pressed into the regulation charcoal suit, black shoes, white shirt and tie that looked vaguely ‘university’ but wasn’t.
I was ushered into my office. Office? Suddenly, I felt vaguely uneasy. All my work in the City of London had been in large Spartan open-plan offices where everyone wallowed together in an egalitarian soup. It was even one of the perks in these places to dabble a bit in the market from insider information screamed into phones by the traders. Here, by contrast, it was quiet, with a large window that gave a glorious view over the street. I had a large, but rather shabby, desk too, and a desultory plastic pot plant..
I soon pressed on with the application that I’d been hired to produce. This meant a great deal of liaison with senior members of the IT and banking staff. Puzzlingly, none of these guys had offices as splendid as mine, I discovered. In fact, the more senior they were, the more likely they were to be squirreled away in the concrete bowels of the building. I only rarely managed to arrange a meeting in my splendid office. Managers seemed slightly nervous to come, and occasionally gazed apprehensively out of the window as if frightened of the light, like werewolves.
I shrugged and decided that, at long last, they had recognized the value of my contributions to banking. Hmmm.. I still felt uneasy. At one point, I met the guy who used to have my splendid office. In vain I probed for an explanation as to why he now occupied a dark corner near the lift-shaft with no natural light. He just looked shifty under cross-examination.
It was a month or so later that I was told of the real reason for my elevation to prestige office. As always, the fount of revelation was the Facilities Management office. As is traditional in the City of London, the role of managing facilities is given to a stroppy cockney git, on the principle of fighting fire with fire ‘Be stirring as the time; be fire with fire; Threaten the threatener and outface the brow of bragging horror’ (Shakespeare: King John, 1597). We bonded immediately, due to our shared love of Young’s bitter and our intense contempt of the establishment.
During a pleasant evening in a dark Victorian pub opposite the bank, he became garrulous. It was the height of the IRA bombing of London, and the Imperial Bank’s prestigious headquarters had, the previous year, been the target of a ‘successful’ bomb attack. So had other banks, and it was obvious that the IRA intended to destroy London’s ability to function as a financial centre. These bombs were detonated in vans which relied mainly on blast for their effect. Anyone near a window was likely to be shredded by flying glass. Experience had shown that anyone in the concrete core of the building was safe, whereas those unfortunate people near a window were not.The phase of bombing had quietened somewhat but the area was evidently still on high alert for another outrage.
My friend, with some exaggeration, explained that the management had decided that it seemed wrong to give any offices of a major retail bank the appearance of a deserted building, so all contractors were given offices near the windows. The more dispensable you were, the nearer the light. Contractors, he added gleefully, were given particularly favored spots as the bank had no liability at all to them in the event of them being slaughtered by a terrorist action. My friend, the stroppy cockney git, laughed into his beer: Unkindly, I thought.
Once I’d been told this, my splendid office somehow held less attraction for me. It was hard to concentrate on my work whenever a large unmarked van parked in the street below. I felt somehow as I imagine a pig would, that discovers that the true reason why he is looked after so assiduously by the farmer and fed so well is not the result of the respect due to his dignity.
I tried to transfer to a less distracting spot near the centre of the building, and close to the fire escape. They laughed. This was going to be tricky.
One day, I’d popped in to see the Stroppy Cockney Git in his cozy place near the spine of the building and noticed a laptop lying abandoned on a side-table.
“Yours?” I asked,
“Nah, Mate, It’s the trouble and strife’s.” (trans: ‘No, dear fellow, it belongs to my wife’). “The Blahddy fink ‘s cattle-trucked”. (Trans: ‘alas, the device is broken’), ” Blahddy ear-ache I’m get’n too”.
“I’ll fix it for you.”
In those days, I was handy with a screwdriver, and had the Blahddy thing purring in a couple of hours, though, as always, there was a screw left. I never got to understand why. A sign or message from the divinity?
He was pathetically grateful when I returned the laptop, and was very amenable to the request I put in to deck out my office with the very best office furniture that he could lay his hands on. It seems I had averted a ‘domestic’.
A few days later, I held a meeting in my office to discuss the progress of the project. The bank's management were strangely reluctant, but in view of the importance of the project, they came. The managers recoiled in shock at the new splendor of the office, almost forgetting their nervous apprehension about being too near the windows during any terrorist outrage. The shock of the office furniture had really got in amongst them. The meeting was brief and terse. There were significant looks between them: it went against all their instincts. The room was one thing, the furniture quite another.
My next request for a move to a cozy dark office home that was surrounded by concrete in the sheltering core of the building near the basement was met by an uncharacteristic willingness. It was near the fire-escape too. I felt very proud: I was moving down in the world, I felt.
|
-
Posted Sunday, August 23, 2009 8:21 PM |
‘Ridiculous,’ he commented, ‘you’ll be saying it is possible to draw graphs from stored procedures next!’ I’d been talking to the guy about keeping an open mind about what was possible at the database level. He didn’t like my 'can-do' attitude to database development. I didn’t bother to tell him it was a ‘dun-it’ attitude: I’d been producing graphs from stored procedures for years.
I still have a guilty twinge when I confess to doing so, as if it were some unspeakable practice that polite society was not quite ready for: but there are reasons for doing this.
In the dark days of old, when a ‘Development Environment’ meant a dark room full of empty coffee cups and congealing pizza on paper plates, the best way of developing any complex application, was to first create a console on which one could display the state of the machine, along with debug information and diagnostic data. Even now, with sophisticated ready-made tools to help, I still do this when designing commercial database applications, but now I use web technology so I can access the system remotely, and I am not reliant on other people to flesh things out. I don’t do it instead of Reporting Services, of course, it is as well as, at a different time in the development process, and for a different audience: mainly myself.
This sort of monitor is guarded by an interface that allows only standard reports, served from a handful of stored procedures. The ASP.NET scripting is almost nothing but a means to deliver the graph so it can be rendered by the browser.
If I can save a few minutes from the task of polling production systems, then the effort of providing all the status information you need on a few screens is worthwhile. For a rapid assimilation of data, and for spotting potential trouble, nothing beats the graphical representation of data.
One would normally choose to use Reporting Services, of if using PHP, one of the excellent graphing packages such as JPGraph,( http://www.aditus.nu/jpgraph/index.php) but occasionally a bought-in product is either too complex for the job, presents security issues, or doesn’t quite do the task you want.
HTML may seem an unpromising material with which to draw a graph, especially if driven from a stored procedure. This is not entirely the case. It can be used for block graphs, but as I’m generally using block graphs for monitoring purposes, this isn’t really a big issue
Let us imagine we want to draw a block graph of visits to a site over a month. HTML allows you to position entities at absolute positions in the browser window With CSS, the only HTML entities one really needs are DIVs and SPANs. One can assign CSS classes to them so that they will behave like all the other tags (other than table-tags) that you’ve become used to. You can also draw blocks with a wide range of fills and borders. For this exercise we’ll just use DIVs and some old-fashioned HRs so as to keep things simple
The principle is that we have code (normally a stored procedure) that generates the entire script for the HTML page. There are several alternative ways of getting this to the browser. You can save the results to a file via a number of methods and send it to the site via FTP (nice and secure, but no good for ephemeral data), or use a server script such as PHP or ASP.NET.
Here is the graph that we'll produce.
....and here, in its simplest form, is the SQL that produces it. Normally, it would be a stored procedure, and I generally hand it to the calling application as a VARCHAR output variable, but I thought I ought to keep things bare-bones here.
SET NOCOUNT ON
DECLARE @SampleData TABLE
(
TheID INT IDENTITY(1, 1),
theDate DATETIME,
Visits INT
)
--we use dummy data of visits per day to a site
INSERT INTO @sampleData SELECT '01 Mar 2009', 15470
INSERT INTO @sampleData SELECT '02 Mar 2009', 15750
INSERT INTO @sampleData SELECT '03 Mar 2009', 14630
INSERT INTO @sampleData SELECT '04 Mar 2009', 8110
INSERT INTO @sampleData SELECT '05 Mar 2009', 9950
INSERT INTO @sampleData SELECT '06 Mar 2009', 13970
INSERT INTO @sampleData SELECT '07 Mar 2009', 14020
INSERT INTO @sampleData SELECT '08 Mar 2009', 20930
INSERT INTO @sampleData SELECT '09 Mar 2009', 14810
INSERT INTO @sampleData SELECT '10 Mar 2009', 13850
INSERT INTO @sampleData SELECT '11 Mar 2009', 8090
INSERT INTO @sampleData SELECT '12 Mar 2009', 7890
INSERT INTO @sampleData SELECT '13 Mar 2009', 10440
INSERT INTO @sampleData SELECT '14 Mar 2009', 11400
INSERT INTO @sampleData SELECT '15 Mar 2009', 12600
INSERT INTO @sampleData SELECT '16 Mar 2009', 12220
INSERT INTO @sampleData SELECT '17 Mar 2009', 15810
INSERT INTO @sampleData SELECT '18 Mar 2009', 9720
INSERT INTO @sampleData SELECT '19 Mar 2009', 8350
INSERT INTO @sampleData SELECT '20 Mar 2009', 11480
INSERT INTO @sampleData SELECT '21 Mar 2009', 11030
INSERT INTO @sampleData SELECT '22 Mar 2009', 13470
INSERT INTO @sampleData SELECT '23 Mar 2009', 19010
INSERT INTO @sampleData SELECT '24 Mar 2009', 17050
INSERT INTO @sampleData SELECT '25 Mar 2009', 8950
INSERT INTO @sampleData SELECT '26 Mar 2009', 9520
INSERT INTO @sampleData SELECT '27 Mar 2009', 12460
INSERT INTO @sampleData SELECT '28 Mar 2009', 14020
INSERT INTO @sampleData SELECT '29 Mar 2009', 12370
INSERT INTO @sampleData SELECT '30 Mar 2009', 13470
INSERT INTO @sampleData SELECT '31 Mar 2009', 16310
--declare all our constants and variables
DECLARE @YHeight INT,
@XLength INT,
@LeftMargin INT,
@BottomMargin INT,
@gap INT,
@BlockWidth INT,
@Scale INT,
@MaxScale INT,
@ii INT,
@iiMax INT,
@BlockInterval INT,
@verticalLabelOffset INT,
@Multiplier INT,
@MaxData INT,
@Points NUMERIC(9,2)
--Firstly we set the basic parameters, which we treat as constants
SELECT @YHeight = 500, --the height of the graph in Pixels
@XLength = 800, --the width of the graph in Pixels
@verticalLabelOffset = -10,--the offset for the Y labels
@LeftMargin = 40, --the left marging in Pixels
@BottomMargin = 30, --the bottom margin
@Gap = 8, --the gap between blocks
@Points = 20 -- the number of label points (roughly!)
--and a little helper table to decide on Y axis labels
DECLARE @ReasonableScale TABLE ( multiplier CHAR )
INSERT INTO @ReasonableScale ( Multiplier )--the units that we label
SELECT '1'
UNION ALL
SELECT '2'
UNION ALL
SELECT '5'
--now we calculate the scale from the data
SELECT @MaxData = MAX(visits)
FROM @sampleData
SELECT @multiplier = FLOOR(CEILING(LEFT(@MaxData, 2) / @points) * @MaxData
/ LEFT(@MaxData, 2))
/* this makes me squirm! The next bit makes me want to put my hands over my eyes. Do not do this at home! */
SELECT TOP 1
@multiplier = ( r.multiplier + REPLICATE('0', LEN(@multiplier) - 1) )
FROM @ReasonableScale r
ORDER BY ABS(@multiplier - ( r.multiplier + REPLICATE('0',
LEN(@multiplier) - 1) )) ASC
/* somehow, we have worked out the scale and the label points */
--so now we can calculate block widths and the vertical scale for the blocks
SELECT
@BlockWidth=((@XLength-@Leftmargin)/COUNT(*))-@gap,
@BlockInterval=@BlockWidth+@Gap,
@Scale=CEILING(@Multiplier*@Points/@yHeight*1.00) FROM @sampleData
--and draw the YAxis labels and lines
DECLARE @yaxes TABLE (html VARCHAR(2555))
--for putting in the HTML
SELECT @ii=0,
@iiMax=20*@Multiplier
WHILE @ii<=@iiMax
BEGIN
IF @ii>@MaxData
BREAK ;
INSERT INTO @YAxes (html)
SELECT '<div class="YAxisLabel" style="bottom:'
+CONVERT(VARCHAR(4), (@ii/@scale)+@VerticalLabelOffset
+@bottomMargin)+'px">'+CONVERT(VARCHAR(8), @ii)
+'</div>'
IF (@ii%(2*@Multiplier)=0
AND @ii>0)
INSERT INTO @YAxes (html)
SELECT '<hr class="AxisLine" style="bottom:'
+CONVERT(VARCHAR(4), (@ii/@scale)+@bottomMargin)
+'px">'
SELECT @ii=@ii+@Multiplier
END
--now we can define each CSS class and calculate the positioning and dimensions
SELECT [html] = '
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<title>Sample Activity chart</title>
<style type="text/css">
<!--
div #graph{
font-family: Verdana, Geneva, Arial, Helvetica, sans-serif;
}
#graph .axes{
position: absolute;
bottom: '+CONVERT(VARCHAR(5), @BottomMargin)+'px;
height: '+CONVERT(VARCHAR(5), (@ii/@scale)+@bottomMargin)+'px;
width: '+CONVERT(VARCHAR(5), @XLength-@LeftMargin)+'px;
border-top: 1px solid Silver;
border-right: 1px solid Silver;
border-left: 2px solid;
border-bottom: 2px solid;
left:'+CONVERT(VARCHAR(5), @LeftMargin)+'px;
}
#graph .title
{
position: absolute;
background-color: White;
bottom: '+CONVERT(VARCHAR(5), @YHeight-30)+' px;
height: 30px;
width:500px;
left:'+CONVERT(VARCHAR(5), (@XLength-500)/2)
+'px;
text-align: center;
font: italic normal normal 20px Verdana Geneva Arial Helvetica sans-serif;
}
#graph .column{
position: absolute;
background-color: #efefef;
bottom: '+CONVERT(VARCHAR(5), @BottomMargin+1)+'px;
height: 100px;
width: '+CONVERT(VARCHAR(15), @BlockWidth)
+'px;
border: 1px solid;
font-size: 1px;
}
#graph .XAxisLabel{
position: absolute;
bottom: 10px;
font: italic normal normal 14px Verdana Geneva Arial Helvetica sans-serif;
}
#graph .YAxisLabel{
position: absolute;
bottom: 0px;
left:0px;
text-align: right;
font: italic normal normal 11px Verdana Geneva Arial Helvetica sans-serif;
width:'+CONVERT(VARCHAR(5), @LeftMargin-3)+'px;
}
#graph .AxisLine{
position: absolute;
bottom: 100px;
left: '+CONVERT(VARCHAR(5), @LeftMargin+1)+'px;
width:'+CONVERT(VARCHAR(5), @XLength-@leftmargin)+'px;
font-size: 15px;
border: none;
color: silver;
height: 1px;
}
-->
</style>
</head>
<body>
<div id="graph">
<!-- here is the basic axes and borders of the graph-->
<div class=axes> </div>
'
UNION ALL
SELECT html
FROM @YAxes--the vertical axis labels and lines
UNION ALL
--do the title
SELECT [html] = '<div class=title>Activity chart from '
+CONVERT(CHAR(11), MIN(theDate), 113)+' to '
+CONVERT(CHAR(11), MAX(TheDate), 113)+'</div>'
FROM @SampleData
UNION ALL
--the indvidual columns or blocks
SELECT [html] = '<div class="column" style="left:'+CONVERT(VARCHAR(8), 1
+@LeftMargin+((TheID-1)*@BlockInterval))++'px; height: '
+CONVERT(VARCHAR(4), visits/@scale)+'px;'+'"> </div>'
FROM @SampleData
UNION ALL
SELECT [html] = '<div class="XAxisLabel" style="left:'
+CONVERT(VARCHAR(8), @LeftMargin+((TheID-1)*@BlockInterval))+'px;">'
+CONVERT(CHAR(6), TheDate, 113)+'</div>'
FROM @SampleData
WHERE TheID%4=0
UNION ALL
SELECT [html] = '
</body>
</HTML>'
This produced a simple little HTML page like this. (remember to execute it with the output set to text, and the number of characters displayed in each column set to something reasonable.) When this is rendered
within a browser, it will give you the graph in the illustration above
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<title>Sample Activity chart</title>
<style type="text/css">
<!--
div #graph{
font-family: Verdana, Geneva, Arial, Helvetica, sans-serif;
}
#graph .axes{
position: absolute;
bottom: 30px;
height: 555px;
width: 760px;
border-top: 1px solid Silver;
border-right: 1px solid Silver;
border-left: 2px solid;
border-bottom: 2px solid;
left:40px;
}
#graph .title
{
position: absolute;
background-color: White;
bottom: 470 px;
height: 30px;
width:500px;
left:150px;
text-align: center;
font: italic normal normal 20px Verdana Geneva Arial Helvetica sans-serif;
}
#graph .column{
position: absolute;
background-color: #efefef;
bottom: 31px;
height: 100px;
width: 16px;
border: 1px solid;
font-size: 1px;
}
#graph .XAxisLabel{
position: absolute;
bottom: 10px;
font: italic normal normal 14px Verdana Geneva Arial Helvetica sans-serif;
}
#graph .YAxisLabel{
position: absolute;
bottom: 0px;
left:0px;
text-align: right;
font: italic normal normal 11px Verdana Geneva Arial Helvetica sans-serif;
width:37px;
}
#graph .AxisLine{
position: absolute;
bottom: 100px;
left: 41px;
width:760px;
font-size: 15px;
border: none;
color: silver;
height: 1px;
}
-->
</style>
</head>
<body>
<div id="graph">
<!-- here is the basic axes and borders of the graph-->
<div class=axes> </div>
<div class="YAxisLabel" style="bottom:20px">0</div>
<div class="YAxisLabel" style="bottom:45px">1000</div>
<div class="YAxisLabel" style="bottom:70px">2000</div>
<hr class="AxisLine" style="bottom:80px">
<div class="YAxisLabel" style="bottom:95px">3000</div>
<div class="YAxisLabel" style="bottom:120px">4000</div>
<hr class="AxisLine" style="bottom:130px">
<div class="YAxisLabel" style="bottom:145px">5000</div>
<div class="YAxisLabel" style="bottom:170px">6000</div>
<hr class="AxisLine" style="bottom:180px">
<div class="YAxisLabel" style="bottom:195px">7000</div>
<div class="YAxisLabel" style="bottom:220px">8000</div>
<hr class="AxisLine" style="bottom:230px">
<div class="YAxisLabel" style="bottom:245px">9000</div>
<div class="YAxisLabel" style="bottom:270px">10000</div>
<hr class="AxisLine" style="bottom:280px">
<div class="YAxisLabel" style="bottom:295px">11000</div>
<div class="YAxisLabel" style="bottom:320px">12000</div>
<hr class="AxisLine" style="bottom:330px">
<div class="YAxisLabel" style="bottom:345px">13000</div>
<div class="YAxisLabel" style="bottom:370px">14000</div>
<hr class="AxisLine" style="bottom:380px">
<div class="YAxisLabel" style="bottom:395px">15000</div>
<div class="YAxisLabel" style="bottom:420px">16000</div>
<hr class="AxisLine" style="bottom:430px">
<div class="YAxisLabel" style="bottom:445px">17000</div>
<div class="YAxisLabel" style="bottom:470px">18000</div>
<hr class="AxisLine" style="bottom:480px">
<div class="YAxisLabel" style="bottom:495px">19000</div>
<div class="YAxisLabel" style="bottom:520px">20000</div>
<hr class="AxisLine" style="bottom:530px">
<div class=title>Activity chart from 01 Mar 2009 to 31 Mar 2009</div>
<div class="column" style="left:41px; height: 386px;"> </div>
<div class="column" style="left:65px; height: 393px;"> </div>
<div class="column" style="left:89px; height: 365px;"> </div>
<div class="column" style="left:113px; height: 202px;"> </div>
<div class="column" style="left:137px; height: 248px;"> </div>
<div class="column" style="left:161px; height: 349px;"> </div>
<div class="column" style="left:185px; height: 350px;"> </div>
<div class="column" style="left:209px; height: 523px;"> </div>
<div class="column" style="left:233px; height: 370px;"> </div>
<div class="column" style="left:257px; height: 346px;"> </div>
<div class="column" style="left:281px; height: 202px;"> </div>
<div class="column" style="left:305px; height: 197px;"> </div>
<div class="column" style="left:329px; height: 261px;"> </div>
<div class="column" style="left:353px; height: 285px;"> </div>
<div class="column" style="left:377px; height: 315px;"> </div>
<div class="column" style="left:401px; height: 305px;"> </div>
<div class="column" style="left:425px; height: 395px;"> </div>
<div class="column" style="left:449px; height: 243px;"> </div>
<div class="column" style="left:473px; height: 208px;"> </div>
<div class="column" style="left:497px; height: 287px;"> </div>
<div class="column" style="left:521px; height: 275px;"> </div>
<div class="column" style="left:545px; height: 336px;"> </div>
<div class="column" style="left:569px; height: 475px;"> </div>
<div class="column" style="left:593px; height: 426px;"> </div>
<div class="column" style="left:617px; height: 223px;"> </div>
<div class="column" style="left:641px; height: 238px;"> </div>
<div class="column" style="left:665px; height: 311px;"> </div>
<div class="column" style="left:689px; height: 350px;"> </div>
<div class="column" style="left:713px; height: 309px;"> </div>
<div class="column" style="left:737px; height: 336px;"> </div>
<div class="column" style="left:761px; height: 407px;"> </div>
<div class="XAxisLabel" style="left:112px;">04 Mar</div>
<div class="XAxisLabel" style="left:208px;">08 Mar</div>
<div class="XAxisLabel" style="left:304px;">12 Mar</div>
<div class="XAxisLabel" style="left:400px;">16 Mar</div>
<div class="XAxisLabel" style="left:496px;">20 Mar</div>
<div class="XAxisLabel" style="left:592px;">24 Mar</div>
<div class="XAxisLabel" style="left:688px;">28 Mar</div>
</body>
</HTML>
Now, as you can imagine, my code doesn't look like this. I've simplified things down. I'd do the weekends in a different color, I'd probably do a moving average, I'd usually have a stored procedure that rendered several graphs, taking the data as XML. Once you get started, it is amazing what you can do to enhance the graph to make it easier to spot problems, and other things that need investigating. With this sort of reporting system, I've managed to detect attempts at intrusion, and several database issues that I've caught before they became problems. Sure, it has been very useful, but I still feel a twinge of guilt when I explain what I've done.
Somehow writing graphs in stored procedures doesn't seem natural.
|
-
Posted Monday, August 03, 2009 11:40 AM |
|
/*
One of the great advantages of doing development work on SQL Server machines with very modest hardware specifications is that mistakes are visible. The mistake of doing ad-hoc dynamic queries from an application will soon reveal itself. You might get a complaint from the developers that a database is running slowly. You'll notice a very high CPU usage, up to 100% but without any blocking during the periods of slow performance.
In extreme cases, You may even receive errors such as
Error: 701, Severity: 17, State: 1
There is insufficient system memory to run this query.
..or..
Msg 8645, Level 17, State 1, Procedure , Line 1
A time out occurred while waiting for memory resources to execute the query. Re-run the query.
You may be seeing the effects of the running of too many ad-hoc queries. The high CPU can be due to the query optimiser compiling a large number of Ad-Hoc queries. The memory stress is due to the extra memory required to store these. In other words, some developer is spraying a large number of Ad-hoc queries at the database instead of using either stored procedures or prepared statements. the chances are, they're doing something 'suboptimal' or, as we used to call it, Daft.
A compiled execution plan takes around 70KB depending on its size, and a stored procedure plan takes two to three times as much, depending on its complexity. The difference is that there will be only one plan for each stored procedure. With ad-hoc queries, you run the risk of having a separate plan for each query. We'll illustrate this in this article, and show you what to look out for.
Query plans have to be stored for re-use in the procedure cache, and SQL Server takes buffers from the LRU Buffer data store to do this. The word 'stolen' is a bit misleading as this is a perfectly legitimate exercise. Stolen pages are buffer cache pages that are 'stolen' to use for other server memory requests. Stolen pages are used for several miscellaneous server purposes such as procedure cache, sorting or for hashing operations (query workspace memory). It is also used as a generic memory store for allocations that are smaller than 8 KB, to store internal data structures such as locks, transaction context, and connection information. This is a simple way of allocating memory to routine tasks but if a server receives a huge rate of ad-hoc queries, it can lead to trouble. Unless SQL Server determines that it can automatically parameterize a query, or it determines that it is the same query, it is forced to generate a new execution plan. You are seeing the consequences of this with the starvation of memory for data buffers.
The first thing to check is the number of query plans being compiled. The SQL Server Performance Monitor will show many SQL Compilations/sec are being done. Ideally, the ratio of SQL Recompilations/sec to Batch Requests/sec should be very low.
DBCC MemoryStatus will indicate a rise in the number of stolen pages, and there are a host of DMVs that can point to the cause of the problems.
Once you are sure as to what is causing the problem, then there are several things you can do. Ideally, the best cure is to use stored procedures. If this solution isn't available for some reason, then develop parameterized queries in the applications so that SQL Server is enabled to reuse an existing plan. It is possible to force SQL Server to use a compiled plan for ad-hoc queries, but as it is so easy to form correct parameterised queries, this is hardly worth considering.
Let's show you an example. First, we'll prepare a 'person' table and populate it with, say, half a million rows.
*/
IF NOT EXISTS (SELECT 1 FROM information_schema.tables
WHERE table_name LIKE 'Person')
CREATE TABLE [dbo].[Person](
[Person_id] [int] IDENTITY(1,1) NOT NULL,
[ContactTitle] [varchar](50) NOT NULL CONSTRAINT [DF_Person_ContactTitle] DEFAULT (''),
[Title] [varchar](20) NOT NULL CONSTRAINT [DF_Person_Title] DEFAULT (''),
[FirrstName] [nvarchar](50) NOT NULL CONSTRAINT [DF_Person_FirstName] DEFAULT (''),
[NickName] [nvarchar](50) NOT NULL CONSTRAINT [DF_Person_NickName] DEFAULT (''),
[LastName] [nvarchar](50) NOT NULL CONSTRAINT [DF_Person_creator] DEFAULT (USER_NAME()),
[DateOfBirth] [datetime] NULL,
[insertiondate] [datetime] NULL CONSTRAINT [DF_Person_insertiondate] DEFAULT (GETDATE()),
[terminationdate] [datetime] NULL,
CONSTRAINT [PK_dbo_Person] PRIMARY KEY CLUSTERED
(
[Person_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
/*Now we are going to simulate a programmer failing to come to grips with how to get information from a table and trying to do it by iterating through the rows using Ad-Hoc queries
*/
SET NOCOUNT ON --as we don't want it
--Lets start with a clean sheet.
CHECKPOINT --Writes all data pages that have been entered into the buffer cache and modified but not yet written to disk, for the current database to disk.
GO
DBCC DROPCLEANBUFFERS --remove all buffers from the buffer pool.
DBCC FREEPROCCACHE --Removes all elements from the plan cache
GO
--We'll create a temporary table to put our results into
--drop the temporary table if it exists and create it
IF EXISTS (SELECT 1 FROM tempdb.information_schema.tables
WHERE table_name LIKE '#names%') DROP TABLE #names
CREATE TABLE #names ([name] VARCHAR(80))
--now let's simulate a simple loop through a table looking for a particular name in a "Person" table, assuming that a programmer is making the simple mistake of using procedural code
DECLARE @ii INT --iteration counter
DECLARE @id VARCHAR(10)
SELECT @ii=1
WHILE @ii<2000 --and just look through 2000 rows.
BEGIN
SELECT @ii=@ii+1, @id=CONVERT(VARCHAR(5),@ii)
EXECUTE ('insert into #names (name) Select Lastname from person where person_ID='+@id)
END
--Hmm. Lets see what is in the query-plan cache. Just peep at a sample of plans
SELECT TOP 5 TEXT
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE cacheobjtype ='Compiled Plan'
/* look at that in the cache! Each execution has created a cached plan
insert into #names (name) Select Lastname from person where person_ID=2000
insert into #names (name) Select Lastname from person where person_ID=1999
insert into #names (name) Select Lastname from person where person_ID=1998
insert into #names (name) Select Lastname from person where person_ID=1997
insert into #names (name) Select Lastname from person where person_ID=1996
How much memory did that lot take up, I wonder?*/
SELECT SUM(size_in_bytes)/1024/1024 AS "Megs"
(SUM(size_in_bytes)/1024)/COUNT(*) AS [Average size(k)],
AVG(refcounts) AS "average ref."
AVG(Usecounts) AS "average use"
ObjType
FROM sys.dm_exec_cached_plans cp
WHERE cacheobjtype ='Compiled Plan'
GROUP BY Objtype
/*
Megs Average size(k) average ref. average use ObjType
------ --------------- ------------ ----------- --------
78 40 2 1 Adhoc
So that simple loop in procedural code within an application took 78 megs of memory!
Now we've seen the damage, let's see if using a prameterised query is any better. We'll make up a little test harness.
*/
GO
CHECKPOINT
GO
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
GO
SET NOCOUNT ON
-- create a temporary table
IF EXISTS (SELECT 1 FROM tempdb.information_schema.tables
WHERE table_name LIKE '#names%') DROP TABLE #names
CREATE TABLE #names ([name] VARCHAR(80))
--create a temporary timer table
DECLARE @log TABLE
(
Log_ID INT IDENTITY(1, 1),
"Event"VARCHAR(40),
"Stolen Pages"INT,
"iterations"INT,
InsertionDate DATETIME DEFAULT GETDATE()
)
DECLARE @ii INT, @IterationsToDo INT
DECLARE @id VARCHAR(8)
DECLARE @StolenPageCounter INT
--DBCC MemoryStatus
--find out the stolen page counter value
SELECT @StolenPageCounter=cntr_value FROM SYS.SYSPERFINFO
WHERE COUNTER_NAME='STOLEN PAGES'
AND OBJECT_NAME LIKE '%BUFFER MANAGER%'
SET @IterationsToDo=5000
INSERT INTO @log (event, "Stolen Pages"Iterations) SELECT 'Ad-hoc queries', @StolenPageCounter,@IterationsToDo
SELECT @ii=1
WHILE @ii<=@IterationsToDo
BEGIN
SELECT @ii=@ii+1, @id=CONVERT(VARCHAR(5),@ii)
EXECUTE ('insert into #names (name) Select Title + '' ''+Firstname+ '' ''+Lastname from person as philfactor where person_ID='+@id)
END
SELECT @StolenPageCounter=cntr_value FROM SYS.SYSPERFINFO
WHERE COUNTER_NAME='STOLEN PAGES'
AND OBJECT_NAME LIKE '%BUFFER MANAGER%'
INSERT INTO @log (event, "Stolen Pages" Iterations) SELECT 'Parameterised Queries', @StolenPageCounter,@IterationsToDo
SELECT @ii=1
WHILE @ii<=@IterationsToDo
BEGIN
SELECT @ii=@ii+1
/* the first time that the sp_executesql statement is executed,
SQL Server generates a parameterized plan for the SELECT statement
from person with id as the parameter. For all subsequent executions,
SQL Server reuses the plan with the new parameter value */
EXEC sp_executesql N'
insert into #names (name) Select Title + '' ''+Firstname+ '' ''+Lastname
from person as factorphil
where person_ID=@id',
N'@id int',
@id=@ii
END
SELECT @StolenPageCounter=cntr_value FROM SYS.SYSPERFINFO
WHERE COUNTER_NAME='STOLEN PAGES'
AND OBJECT_NAME LIKE '%BUFFER MANAGER%'
INSERT INTO @log (event,"Stolen Pages" SELECT 'Completed parameterised query', @StolenPageCounter
--first, we'll see what cached plans we have.
SELECT COUNT(*) AS [No.],
CONVERT(NUMERIC(9, 2),
SUM(size_in_bytes * 1.00) / 1024 / 1024) AS Mb,
(SUM(size_in_bytes) / 1024)/COUNT(*) AS [Average size (K)],
AVG(refcounts) AS "average ref."
AVG(Usecounts) AS "average use"
ObjType
FROM sys.dm_exec_cached_plans cp
GROUP BY Objtype
ORDER BY [Mb] DESC
/*
No. Mb Average size (K) average ref. average use ObjType
------ ------- ---------------- ------------ ----------- --------
5003 195.82 40 2 1 Adhoc
8 0.26 33 1 4 View
1 0.04 40 2 5000 Prepared
1 0.01 8 1 5000 Proc
*/
SELECT event,
[Pages Stolen] = (SELECT "Stolen Pages"
FROM @log f
WHERE f . Log_ID = g . Log_ID + 1)-"Stolen Pages"
[Time (Ms)] = DATEDIFF(ms, [InsertionDate],
(SELECT [InsertionDate]
FROM @log f
WHERE f . Log_ID = g . Log_ID + 1
)
)
FROM @log g
WHERE Log_ID < ( SELECT MAX (Log_ID) FROM @log )
/*
so we see that it is much faster to use parameterized queries and the number of stolen pages is far less
event Pages Stolen Time (Ms)
-------------------------- ------------ -----------
Ad-hoc queries 21193 3906
Parameterised Queries 4492 970
So now, lets pick out the two queries from the Procedure Cache, recognising them by the two strings I embedded into them
*/
SELECT 'parameterised query' AS [query type],
SUM(size_in_bytes)/1024 AS [memory consumed (K)],
COUNT(*) AS [number of plans]
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE TEXT LIKE '%factorphil%'
UNION ALL
SELECT 'Ad-Hoc query' AS [query type],
SUM(size_in_bytes)/1024 AS [memory consumed (K)],
COUNT(*) AS [number of plans]
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE TEXT LIKE '%philfactor%'
/*
query type memory consumed (K) number of plans
------------------- ------------------- ---------------
parameterised query 3072 2
Ad-Hoc query 205728 5001
*/
GO
SELECT LEFT([type], 20) AS [type], SUM(single_pages_kb) AS [Total_kb]
FROM sys.dm_os_memory_clerks
WHERE TYPE IN ('CACHESTORE_SQLCP','CACHESTORE_PHDR','CACHESTORE_OBJCP')
GROUP BY TYPE
ORDER BY SUM(single_pages_kb) DESC
/*
we can see the huge figure again from the DMV sys.dm_os_memory_clerks
type Total_kb
-------------------- --------------------
CACHESTORE_SQLCP 202928
CACHESTORE_PHDR 312
CACHESTORE_OBJCP 8
but probably the best way of viewing the damage is from this DMV sys.dm_os_memory_cache_counters
*/
SELECT
LEFT([name], 20) AS [name],
LEFT([type], 20) AS [type],
SUM([single_pages_kb] + [multi_pages_kb]) AS cache_kb,
SUM([entries_count]) AS No_Entries
FROM sys.dm_os_memory_cache_counters
WHERE TYPE IN ('CACHESTORE_SQLCP','CACHESTORE_PHDR','CACHESTORE_OBJCP')
GROUP BY [type], [name]
ORDER BY cache_kb DESC
/*
name type cache_kb No_Entries
------------- -------------------- -------- -------
SQL Plans CACHESTORE_SQLCP 202768 5005
Bound Trees CACHESTORE_PHDR 312 10
Object Plans CACHESTORE_OBJCP 8 0
Note that:
CACHESTORE_SQLCP are our ad-hoc cached SQL statements or batches that aren't in stored procedures, functions or triggers. Thes consist of dynamic ad-hoc SQL sent to the server by an application.
CACHESTORE_PHDR are algebrizer trees for views, constraints and defaults. An algebrizer tree is the parsed SQL text that resolves the table and column names.
CACHESTORE_OBJCP are compiled plans for stored procedures, functions and triggers.
So there you have it. This is a problem that can be very insidious, but once you know what to watch out for, it is very easy to put right. You just make sure that the developers do not try using too many ad hoc queries.
*/
|
-
Posted Friday, May 22, 2009 4:53 PM |
Stan, the SQL Hero, had a meteoric career at the large Financial Services Company where I worked. He burned red-hot when he hit the upper atmosphere of the company, and caused a brief flash before hitting the ground.
Stan ‘the man’ was a Sybase and SQL Server expert. Over a quiet glass of wine in the Clubhouse, my friend Mick, who was the Development Manager, proudly told me of his luck in recruiting such an exceptional individual. The agency had put it pretty strong. This guy had certificates beginning with ‘M’ where ordinary mortals didn’t sprout certificates. Mick was ***-a-hoop. I was loath to dampen his sunny mood, but he was a chum and I felt it only fair to warn him.
“You’re playing with fire, Mick; you just want a guy who can quietly slot into the team and work diligently within it to bring his knowledge to it without causing disruptions. You don’t need a hotshot, but someone who is socially aware, with solid and wide-ranging competence. “
It was no use: Mick’s mind was made up. Here, he thought, was someone who could cause a radical shake-up in the department and, to use his macho term ‘kick ass’.
I was working as a technical architect, plotting out the high-level strategy for the migration of a number of systems, so I was slightly out-of-touch with the everyday life of IT Development. However, I couldn’t help noticing, after a while, that Mick had gone quiet on the subject of Stan ‘the man’. One day, however, the Production IT Manager popped to my office, looking a bit flustered. He’d become alarmed by a financial reporting system which had been submitted by Development for release and deployment. Basically, The Production Manager had to decide whether to accept responsibility for maintaining this application as a production system. “Ah, Phil; you know a bit about SQL Server don’t you?”.
I gave him the Stare of Death, but his psychic force-fields were in place. “I’ve dabbled a bit.” I replied.
“Cop a look at this”, he said, slapping a listing of the database build-script on the desk in front of me. Hmm. Odd. Was that a One True Lookup Table? Aiee! Did that stored procedure really execute the contents of a column called ‘executable’? I glanced, with rising panic, through the listing. It was the work of intellectual brilliance. Had the investigators of the Roswell Incident found a listing, it might have looked a bit like this. In order to service a request for a financial report, this application created screeds of dynamic SQL based on a number of different conditions, and then did a CUBE which then could be queried on a number of different rotations. One would be able to see what was executed via a trace but not from the listing. It was the first time I’d seen the SQL Code actually stored in a database table.
“This is clever stuff, and I don’t pretend to understand it all”. Uncharacteristically, I tried to break the bad news gently, but he flinched on the word ‘clever’.
‘Phil, if you can’t, how the hell can I maintain it with the team I’ve got?’ His voice rose in pitch as panic set in.
‘Well, there are three database developers in the team that wrote it. Couldn’t we just get one of them to train your people up, and write up sufficient documentation to assist your group?’
He gave me a quizzical look. ‘The only guy who understands this stuff is the guy that wrote it; Stan ‘The Man’. The other two members of the team are on the point of going off sick with the effort of repressing their homicidal urges against Stan. Stan’s evidently much too busy rubbing the noses of the developers in their ‘Newbie’ database mistakes to have the time to provide adequate Database documentation. He’s cutting a swathe through the Dev Databases finding all sorts of ‘worst practices’ and recounting them in amusing terms to anyone who’ll listen.”
“Well, I’d refuse to allow it into production until it meets the full requirements for documentation and support as laid out in the corporate Computer Manual pp. 135:159”
“Oooh! Is that fair? Come on, Phil, harsh.”
I gave him the Clint Eastwood look. “Sometimes, it must be attack in overwhelming force.”
Next time I bumped into Mick, the head of IT development, he definitely looked a bit part-worn. Either the stress of losing a round of golf had got to him or else Stan had already finished his meteoric trajectory in the dirt. I didn’t need to say anything, but merely signaled to the barman to come up with a sympathetic Jack Daniels.
By the second tot, Mick poured out his woes. Stan was a SQL rock star, but had not kept a close eye on the audience reaction. The average IT team is a delicate lattice of sensibilities, emotions, affiliations and resentments. Give me the complexity of a corporate data model long before the interplay of human interactions within a development team. Stan had solved countless database issues, but had left a trail of interpersonal wreckage and humiliation in his wake. Before long, realizing that he had become indispensible, and that his new, and essential, reporting database could not be maintained without his continuing expertise, he had just asked for his salary to be doubled.
“Mick”, I said, “you know what I was taught when I first became an IT Manager? Rule one, if one of your IT staff becomes indispensible, then you should dispense with him immediately. Heroes are the stuff of fantasy. Armies are best staffed by people who get on with the job, inspire their team, and keep their heads down. It is teams that deliver good business applications, not individuals.”
He downed the rest of the glass and waved for another. “So, it is time to refuse his request for a pay-rise, and transfer him to the Compliance team?” We shook our heads sadly. Stan was about to take the Big Sleep.
I was happily dosing off in front of a network diagram in the strategy office a fortnight later, musing over the impermanence of IT careers, when the IT director sidled into the room shiftily.
”Phil? Are you well, Good good good. Yes Splendid. Nice to hear that, Phil.” Pause. “Ah! To get to the point, Stan ‘The Man’ has handed in his notice, and I’ve allowed him to go without working his notice. We’re in a bit of a pickle though. “ I missed the alarm I should have felt.
“The fact is, Phil, that the Business is pretty aerated about the possibility that the new Financial Reporting System might not be delivered on time. Mick says the remaining members Dev Database team can’t fix it. It is beyond them. The Production DBAs say you’re the only one who could turn it around, so I’m seconding you temporarily to the Dev group to fix it.”
Theatrically, he dropped the horrid build script listing on my desk. It fluttered open on a page that had a SQL statement with twelve joins in it. I closed my eyes: it was going to be a long month.
|
-
Posted Friday, May 01, 2009 7:11 PM |
A tip for managers: never alienate your DBAs. If you do, it is liable to lead to painful consequences. I speak from experience. Having suffered one of those occasional "reverses in career fortune", I wound up taking an appointment as a Team Leader/DBA for the IT support team of a German startup Telecommunications company.
I was intently staring at my screen one afternoon, as DBAs often do, when Jorgen, one of the senior managers, strode up to my desk. He was a rising star in the company due to the huge uptake of his new Italian coupon-based phone service. This was actually one project for which we weren't providing database support; Jorgen had recently sent an email tirade to the IT director stating that he was dispensing with the services of Central IT because we were too slow and hidebound. He was relying instead on 'his own dynamic staff'.
As far as Jorgen was concerned, our job was simply to supply, on request, management reports for the board detailing the stupendous success of his project.
"Is the report on my Italian phone service ready yet?" he snapped, with his usual level of tact.
"Good afternoon, Jorgen" I chirped, tearing my eyes reluctantly from the screen. "I trust that all is well with you. Unfortunately we're still working on the reports and I don't see them being ready until the end of the week."
"I'm meeting the CEO tomorrow afternoon and I must have it then. You promised it to me yesterday."
"Not so. You asked when I thought it would be ready. I told you I thought it would be ready yesterday. I was wrong in thinking it would be ready".
He should have asked me why I'd been wrong. He didn't.
"If I don't get it before the meeting on Thursday, there will be someone else in your shoes by Friday!"
The sentence echoed around the open-office area. I was supposed to be cowed into submission. Instead I laughed. "Sorry, but that's impossible"
"And why is that?" he said, coloring up. All eyes in the office were upon us.
"Because I'm wearing sandals"
He flounced out of the room. For a moment I savored the comic absurdity of the company putting so much faith in an idiot savant whose entire cerebral energy was focused on making money and, incidentally, enemies.
It is easy to come to believe that a DBA's many working hours spent staring into screens was merely a ruse designed to infuriate senior managers. In fact, in this case, before Jorgen had rudely interrupted me, I had been intensively following the evil activities of 'The Porker'.
The Porker, named evidently for his girth and disgusting eating habits, wasn't alone in trying to defraud large sums of money from telecoms companies. In those days, plenty of people were at it. The denationalization of the state-held phone companies had induced a rash of startups, like ours, staffed by people completely inexperienced in the wicked world of telephone fraud.
Jorgen had experienced a meteoric career in the denationalized telecoms companies. He had hypnotized our CEO into believing that he was uniquely qualified to spearhead the introduction of profitable new telephone products into the market. He could, he explained, succeed where the the old hidebound state-owned companies could merely fluster, because he had experience, energy, vision and confidence.
The Porker suffered no such self-delusion. He was experienced in telephone fraud. From his rooms in Manchester, he operated an extraordinarily ingenious operation that probed the weaknesses of all the new services. The scam was simple. The Porker, and his colleagues, set up a range of premium-rate services, each one using a newly-introduced premium-rate phone number. He then rang them via all the new telephone companies. If they charged him correctly, he moved on, having lost little. However, if a company failed to spot that it was a premium-rate number, he would immediately bombard his own premium-rate number with automated calls via that service provider. He would then get the payment for the call to his premium-rate number from his own provider, who would then bill the hapless telecoms company who routed the call to them. All he would have ‘invested’ for each call would have been a charge for the local rate. For every pound he spent, he would get a fifty-fold return.
There were many such scams, from the crude to the breathtakingly subtle, but The Porker's scam was typical. I often used to meet with fellow Telecomms DBAs, in a pub off Holborn that served extremely good pints of Youngs bitter, to discuss the latest activities of The Porker and his ilk.
As a team, we did all we could to ensure that our tariff tables were kept up-to-date and that customers were correctly billed. If it was a 'prepaid' service, then we ensured that the deduction was correct. However, it is an imperfect world, and occasionally, a fraudster such as the Porker would probe our defenses and find a weakness. In order to spot fraud in any business system, your detection system must look for the flurry of unusual activity by which the fraudster inevitably gives himself away. You'll never be able to guess beforehand all the potential avenues for fraud. All you can do is to spot the signs of such activity and puzzle out what they're up to. The DBA becomes like a carnivorous beast, all senses honed at spotting the rats. His senses are his queries, and his database tools. Our team had become very adept at producing queries that were sensitive enough, and unobtrusive enough, to catch the scams before we experienced heavy financial loss. We'd then react quickly to correct the tariff and slam the door shut on the likes of The Porker.
I was just one in a long line of DBAs that Jorgen had alienated. I was also the last, as it turned out. Through underestimating the forensic skills required of his IT support, he was uniquely vulnerable – and foolish. I settled back to writing the report on his Italian coupon service. A management report is one of the most frightening things a DBA can be responsible for. It has to be right. If you get a sales report wrong, it can lead to people being unfairly sacked or rewarded. If you get a profit or loss figure wrong, your management can commit huge resources to the wrong part of the business, and starve the real revenue-earners. I could see that there was something wrong with Jorgen's report, which is why I couldn't let it out. Jorgen's rudeness had merely served to supercharge my natural obstinacy.
The team combed through the raw data obsessively. When we spotted the problem, it was obvious and frightening. Jorgen's prepaid coupon service was losing us millions. The Tariff tables hadn't been updated, and the rats were crawling all over it. Coupons were being bought all over Italy, and being used to phone premium-rate numbers that we were mis-charging at standard rates. What looked like a brilliant and fast-expanding service was actually a feeding frenzy for the fraudsters.
As soon as the whole horror was apparent, we laid it out before the IT director. He gave a low whistle and we immediately had a long conference call with the CEO. The following day we followed a plan. Jorgen, as expected, stormed up to my desk early in the morning.
"Where is my f***ing report?"
"Jorgen, how good to see you again. We're almost finished and we will deliver the report at the board meeting. I've cleared this with the IT Director and CEO "
"I told you I needed it before the meeting you ****". I raised an eyebrow: I'd never found myself likened to anyone's external genitalia before.
"I can appreciate your point of view, and I'm sorry I can't comply. These reports have to be rigorously cross-checked." His powers of expression ran out and he stormed off.
At the allotted time, I picked up ten copies of my report and popped upstairs to the board-room, where the meeting was already in progress. Jorgen was in full flow with a PowerPoint slide onscreen that showed the splendid growth in users of his Italian coupon service. I popped a copy of the report in front of every board member and sat on a chair next to the IT director.
When Jorgen had finished his presentation, the CEO turned to me with slight conspiratorial smile. 'Phil will now deliver the latest report on the new service, from the perspective of Central IT'
I stood up and told the board that, while the user figures for Jorgen's new service were indeed impressive, it had in fact lost the company a total of 5.6Million pounds sterling. This loss was directly attributable to a failure to add provision for proper fraud detection, brought about by his reckless refusal to use the services of central IT. After running through the figures, I sat down with a smile. Are all DBAs subjected to this sort of stressful activity, I wondered?
Jorgen was struck dumb. He sat there, puce in the face, staring ahead and sweating profusely. After a strangled cry, he suddenly erupted.
"That **** Phil Factor" he yelled, gesticulating wildly at me "He was smiling as he told you all that. That ignorant old b*stard wants me to fail! He was actually f***ing smiling!"
He had started to flex his biceps in a slightly alarming manner, so I nodded in an amiable way to the assembled board members and made a strategic exit.
On the Friday morning, I ambled into the office, sat down at my desk and took a long look down at my sandals. The feet in them were, reassuringly, my own. Shortly afterwards, I saw old Hans, the Facilities man, clearing out the content of Jorgen's desk and office, and unceremoniously dumping them into a cardboard box. The IT director later told me that Jorgen had tried every trick he knew to extricate himself, but had been encouraged by the unanimous board to spend a lot more time with his family. His departure caused barely a ripple of controversy; the telecoms industry has always been adept at burying its dead without disturbing its shareholders with all the gory details.
No: it is never a good idea to alienate your DBAs.
|
-
Posted Sunday, April 05, 2009 7:39 PM |
Right. Peace at last. Inbox down to zero, nitpick forms filled in,
back to the code. I’ve got the code all cached in my skull. I know
every variable. Every ounce of concentration is focused on stepping
through the code or working out why the result I expected didn’t
happen. Now what is the best strategy to flush out that bug? Ah, I
know, could it be….
‘Could I borrow that pen a moment, please?’
Wave hand airly, and dismissively. Please dear god, make him go
away. Clamp the headphones over the ears. Phew. He takes the hint.
What was it? Oh yes, the test strategy, it was something odd about that error message that rang a bell. Why were there three….
Phone warbles
‘Hello. Phil Factor here.’
‘Have you a few spare minutes to go over the proposal for the change to the project spec for the MIS project ?’
‘If I was into spare minutes, Dave, I’d be working down the
urgency stack until I got to the change proposal. When I get there,
you’ll be the first to know. Bye’ Goddaammmit. Can’t anyone understand? A developer’s job is
like no other, except a performing musician or athlete. It requires
every ounce of concentration. A minute’s interruption and you’ve lost
half an hour’s work, painfully gathering the threads together. Now
where was I? That string in the error message should be a vital clue.
There was something in the back of my mind… gone now. Never mind, just
look at the wording of the trace …
Aargh! Here comes the department bore. There is always someone
who hasn’t got enough to do, and hasn’t the wit to find work. He’s
coming this way, with a glint in his eye. Gaze avoidance; gaze
avoidance. Now I know why people have three screens. Phew, he’s veered
off to the water cooler to find a kindred spirit at a loose end to talk
to.
Programmers are a victim of modern architecture. There is an
assumption that they work in teams like oxen pulling a plough.
Open-plan offices are perfect for the modern programming team, it is
thought. Open-Plan offices are, by amazing coincidence, far cheaper to
build. When I was a small lad, programmers had their own quiet
offices where they could escape to do their coding work, or could even
crawl into a nice quiet booth in the server room. Now we are supposed
to be gregarious team-players, ready at all times to chat to
passers-by, or switch our focus at the drop of a hat, respond to
blooming IM messages that pop up on the screen, or answer fatuous
questions shouted across the office. Yes, for set periods of time in
the working day, you will find me to be the soul of modern team spirit
and affability, engaging and interactive, but when I’m coding you could
be a potted plant for all I care.
I phoned a chum the other day who is a famous author. His secretary
answered the phone. Could I talk to Steve please?' 'Sorry Phil, could
he please call you back, he's meditating at the moment'. Wow. Super
cool.
We are stuck in some architectural orthodoxy that says that the open
plan office is the best thing for us, so we are forced to make the best
of it, sadly. Surprisingly, there seems to be no sign that is
universally recognized that means, ‘I wish to seem a gregarious team
player who is prepared to interact at a moment’s notice, look
admiringly at photos of your children, give you technical help just
because it is less trouble for you than looking it up on Google,
discuss what was on the telly last night, and so on, but, on the other
hand, if I don’t get this module working right, all sorts of things are
going to slip, so please pretend I’m not here.’ A special hat? A look
of sinister malevolence? Suggestions anyone?
|
-
Posted Monday, March 30, 2009 1:37 PM |
When you make your house secure, you might want to do the
obvious things such as putting good locks on the doors, and fasteners on the
windows, but you are still interested on peeping through the curtains to watch
for suspicious activity in the street. If people in balaclavas are carrying
ladders and eyeing up the side of the house, or checking sight-lines, you’d want
to know about it, surely.
Why take a different attitude when you put a website out
there, particularly if it is a trading site with lots of confidential
information? Wouldn’t you want to see what the villains are up to? A couple of
years ago, I did just that. I felt like the little pig that had the house of
brick. Although I was confident that I’d done enough to prevent the big bad wolf
from blowing the site over, I wanted to know if he was eyeing up the chimney
with a view to a sneaky entrance, especially if I could light a fire when he’s
descending.
It was a fascinating and salutary experience. I was not
aware of the extent of the activity that bombards a site to try to break it. It
is a constant stream.
Some attacks are so simple,
yet seem to be still worthwhile for the hacker. Surely there aren't sites
around still with this sort of vulnerability? Just as an example, there was a
continuous stream of 404s (returning a 404 error to the requester to tell them
the file was not found), resulting from people trying to find the location of
the admin site. Hmm. Up to no good here. The combinations were endless.
Responding to 404s is dumb, as it allows an automaton to go through a very long
list; login.asp, login.php, index.asp, admin.php, maint.aspx, and so on until
they get a successful request. As an experiment I made a lot of completely bogus
admin pages to react to these sorts of requests. It caused a temporary rush of
excitement to some east European somewhere. Here, I discovered, is one of the
few ways that the long-suffering site-maintainer can fight back, and laugh like
a drain as a human being attempts to hack into your bogus site-admin console.
There is a delicious pleasure from constructing a completely bogus database,
using SQL Data Generator, and watching the hackers break in, but be careful, as
you don't want to provoke a retaliatory DOS attack. The payback is that you get
to see the whole process of an attack.
I had great fun seeing the ingenuity of
these attempts to log in as the administrator of my dummy site admin screen.
Mostly, they use a shared script, probably a Perl script, though there are
plenty of applications that will do a brute-force attack. There is no shortage of
places on the internet that you can download these tools. You don't have to be
clever to use them, just to think them up in the first place. Although a few
were completely unintelligible to me, most are simple.
This is the way they do it. They get the
Admin login screen. They check the form, and see what the variables are, just by
looking at the <form> block. They see if it is a POST or a GET. When they know
how to send login data to the database they are away. They rely on a very
elementary mistake. It is anticipating that user input
is either incorrectly filtered for string literal escape characters such as '', or user input
that has not been checked for correctness. It relies on SQL statements being
constructed on the fly, and executed, rather than parameterized.
The hacker determines, from the FORM
block, the name of the script file, ASPX or PHP, perhaps, to log in to the site.
Imagine that the parameters represent user ID and password. The <FORM> block
will tell the hacker all he/she needs to know.
The hacker passes any UserID he wants,
let’s use the example ‘DAVE’, and passes, as the password the string ‘’’ or 1=1
-- (there are around fifty combinations of Magic strings, which are variations
on this.) This will sometimes get him logged in as administrator. Game over.
Even today, there is a chance that the
ASP file will not check to see if there are single quotes in the password. It
will just take the GET or POST variable and stuff it into a SQL query, hoping it
will look like this....
SELECT USER_Identity FROM MyUsers WHERE userid='DAVE' AND password='secret'
…and will expect to return a non-zero recordset if the userid and password were correct. Instead, the query will look
like this!
SELECT USER_Identity FROM MyUsers WHERE USER='DAVE' AND password='''' OR 1=1 --'
Our password value,
which is one of the many ‘Magic Strings’ …
''' OR 1=1
--
… has had delimiters added and the resulting code had
returned every row. Try it.
--just to demonstrate the technique
--we build a bogus list of users and their passwords
CREATE TABLE MyUsers (User_Identity INT IDENTITY(1,1),
UserID VARCHAR(20),
Password VARCHAR(30))
INSERT INTO MyUsers(UserID, Password)
SELECT 'Dave', 'voluble12'
INSERT INTO MyUsers(UserID, Password)
SELECT 'Tony', 'cockeyed3546'
INSERT INTO MyUsers(UserID, Password)
SELECT 'Robyn', 'workinghard408354'
INSERT INTO MyUsers(UserID, Password)
SELECT 'Bob', 'MyPasswordIsSecure2396845'
--This is the SQL Statement the programmer wanted to construct
SELECT USER_Identity FROM MyUsers WHERE userID='dave' AND password='voluble12'
--this is the string that the hacker likes to inject
SELECT USER_Identity FROM MyUsers WHERE USER='DAVE' AND password='''' OR 1=1 --'
GO
--although the 'dynamic SQL crimes are generally committed in ASP, here is
--a stored procedure that commits the crime.
ALTER PROCEDURE spLogin (@userID VARCHAR(80), @Password VARCHAR(80))
AS
EXECUTE ('Select USER_Identity from MyUsers where userID='''
+@UserID
+''' and password='''+@Password+'''')
GO
--innocent procedure call
EXECUTE spLogin 'dave', 'voluble12'
--U BIN HAKKED!!!
EXECUTE spLogin 'THE HAKKER', ''''''' or 1=1 --'
Now, you wouldn’t believe that anyone is
dumb enough to keep an exploit like this open would you? The rookie programmer
scoops up the name of the userID, and the password and does no sanity checks.
He/She then constructs the SQL on the fly rather than using parameters or,
better, calling stored procedures. He doesn't look at the contents of the result that is handed back to him, even to see how many rows there are.
Notice that if getting the admin login
to your website isn’t enough for you, you can always do more extreme things such
as delete the user table
EXECUTE spLogin 'THE DESTROYER', ''''''' drop table MyUsers --'
--Or look for backup files with a view to FTPing them to you
EXECUTE spLogin 'THE CURIOUS', ''''''' execute xp_cmdshell ''dir c:\windows'' --'
This is just the start, of course. You
don’t have to be able to access the Admin screen to get the information from a
database. As long as the programmer has forgotten to parameterize his queries,
forgotten to check for escape characters, or has failed to check for the
validity of the input, in just one of the scripts that call the database, then
your database is at the mercy of the hacker.
The hacker can use a technique such as
blind SQL injection or inference SQL injection. This essentially does a binary
chop to read strings character-by-character so as to get from the database a
whole realm of interesting information. All that the hacker needs is an
indication of when a query returns rows and when it doesn’t. Time is on his side, as the website probably has no alerts for unusual activity. (a binary chop for character string causes a spike)
Some of the easiest hacks have been
possible because the result of a query is always rendered on the browser. It is
so tempting to create a single generic routine for rendering all data returned
from the database as an HTML table, or list.
Of course, if the database users are set
up properly, none of this is possible as the login from the website only has
access to the Interface stored procedures, and you don’t use dynamic SQL in
these. You have to assume that the hacker is somehow going to be able to
pick up the login credentials from the site, and you must plan accordingly. I
use the extra precaution of a time-limited, and revokeable, authorization
key issued from the database. You can then relax as you study the amazing complexity of the attempts to
get at your data. When I say ‘relax’ I’m only referring to ‘as relaxed as the
third little pig whilst the big bad fox paces around the brick house, huffing
and puffing’. You can’t predict in advance what the hacker will try. I always
create a log that stores all accesses to the interface, along with the
parameters passed. It really takes no extra time to do this. I read the weblogs
into the database as well, to check for unusual spikes of activity. It’s always
best to know what attacks are being made to your websites.
|
-
Posted Friday, March 20, 2009 8:36 PM |
"C'mon Phil! You can't blame developers all the time! You must have encountered a really bad DBA at least once in your working life!"
The developer flushed angrily as I rambled on, recounting some of the hilarious mistakes that developers make when tackling databases. Somehow, my SQL horror stories didn't strike him as amusing.
"Well, strangely enough, yes. Once, the boot was on the other foot, entirely. There was a time when I directly experienced the sort of hell that Dante would have dreamed up for the sinful soul of the dead developer".
I leant back in my chair, and put down the soothing cup of Darjeeling tea.
"But we escaped relatively unscathed in the end…."
The scene goes watery, and there is weird music…..
It was during one of those sporadic downturns in the industry that I was ejected from a rather cozy job with an international Financial Services Provider. I'd been a Technical Architect specializing in documenting and maintaining the corporate data model. A CV that mistakenly lists this sort of esoteric skill is anathema: the phone never rang, and my CV hung limply on all the jobsites. Eventually, I did what everyone does in these circumstances and reverted to being a VB developer. I'd used the language now and again, though I'd never previously admitted to it. It was like confessing to unnatural practices.
Soon, I was sitting at a desk in a vast office-block in London, heading up a newly formed Dev Team of six developers and ten testers. We were tasked with writing a Database-driven ECommerce application.
As there was not a line of code written, the testers busied themselves scanning social networking sites on the internet and trying to look important. I looked around for the Development DBA. There wasn't one. The Project manager told me in hushed tones that they had just acquired an expert in SQL Server. Apparently, he used to be a big cheese in Microsoft but was now an independent consultant; one of their top people, I was told. I felt it prudent to keep quiet about my many years of Oracle and SQL Server experience in view of the fact I'd majored more on my VB skills in my job-application. No lies, you understand; just balderdash.
By the time Benjie finally arrived, the team had worked itself into a fever-pitch of anticipation. A real Microsoft expert! Wow! I phoned a few of my Microsoft contacts but they couldn't place his name. Benjie, a most affable and charming Nigerian, was introduced to the team by the deferential project manager. After a month of work, he produced a ER diagram of the database for the application. I was slightly apprehensive, since it looked wildly complicated. 'What,' I remember asking 'is an EventHospitalityGiftItem table for?' I gave the diagram a bleak, but thorough, inspection. It was scarily awful. I was full of forebodings.
A couple of weeks later, he had created the database and provided a stored procedure-based Interface. I'm all in favor of stored procedures, except that in this case the spCustomer procedure had 92 parameters, all of which, he said, had to be supplied on every call. The devs hadn't been consulted, and they started to give vent to their discontent about the endless problems this interface was going to cause them.
Unfortunately, the project manager was still hypnotized by the aura of Microsoft magic surrounding Benjie. Under this zealous protection, Benjie basked in the glory of his facile dictatorship, and the wacky interface was imposed on us, despite all our protests.
And so began a period of Developer Hell. For a month, we suffered The Curse of The incompetent DBA. Nothing in the database worked reliably, so the Developers became very adept at their error handling routines. The design was so mad that even the simplest data sets were tedious to extract. All our requests for extensions to the interface were refused on grounds that it wouldn’t scale, offended against the relational model, was against the corporate data model, was a security risk, would overload the server, or was unnecessary. We had, of necessity, to add business logic on the client that should have been done on the server. The database became a no-go area. It was worse for me, as I could see simple solutions for problems that kept us programming into the evenings, and ,besides, I had used all of Benjie’s excuses myself in the past. There is nothing worse than having to program to a cockeyed database schema.
Eventually, the team got so restless that we decided that something radical had to be done to save the situation. In desperation, I phoned a friend who worked for Microsoft in Ireland, at their support center.
"Keith, I'm working on an ECommerce project with a guy here that used to be with Microsoft. His name's Benjie. Do you know anything about him?"
"Benjie!" trilled Keith, "Give him my love. What a nice guy! We took him on as part of our first response team at the call-centre, as he had such a good phone manner. The trouble was he knew almost nothing about the technology, so we had to let him go in the end."
"Well, he's turned up here as our database expert" I told him, dejectedly.
There was a strange wheezing sound from the other end of the phone.
"Database expert you say? Gimme a break! My pet terrier 'Buster' knows more about databases than Benjie!!" Keith hooted with laughter. "He's a VB programmer by trade, I believe: well that's what he told us. He's quite a character though. I remember when he offered to show us his Mapouka Dance at our Christmas party. Epic: we still talk about it to this day.'
I immediately set off in search of Benjie. When I found him, I could see immediately that he was already in a state of considerable panic. It seems that, through canteen-gossip, perhaps, the Production DBA team had got wind of the news that a database-maniac was on the loose. The Production Manager and senior DBA had just marched into our project manager's office and demanded a formal review of Benjie's work.
I added to his misery by telling him that I'd rumbled his CV. His eyes swiveled in guilty remorse. He looked so dejected and morose that I felt a surge of pity for the chap. Anyway, my own CV as a VB developer wasn't exactly whiter than white. In fact, there was a strange symmetry here. Here was a VB programmer masquerading as a DBA and there was I, a DBA, pretending to be a VB programmer. Damn, there must be some way out of this. There was a quiet pause as I tried to think of something and Benjie blinked sadly and stared miserably out of the window. Suddenly the solution came to me. ‘Benjie,’ I started, ‘I have a solution, but it will hurt; you, not I…’
The following week, the Production Manages arrived, with a team of DBAs in tow, to perform the review. He gave Benjie the look that a huntsman gives to a cornered fox. However, in contrast to the mayhem and confusion that had defined the previous weeks, today all was sweetness and light in the office. The developers all looked relaxed, contented, and busy. Had any bird been able to do more than cough asthmatically in that part of London, you'd have heard sweet birdsong and Elysian harmony.
The server was given a thorough review. No stone was left unturned. The data model, documentation, build scripts and interface specification were all scrutinized in great detail…and pronounced it to be near-perfect! The DBA team left, frowning in puzzlement.
I'd completely re-written the database over the weekend, with the help of a couple of the Developers who knew the ropes.
Benjie had happily agreed to my terms. This included the condition that he must reveal to we developers the secrets of the the quivering Mapouka Dance. Also, from now on, he was to be just a figurehead and wasn't allowed to touch the database without my approval. However, he had squeaked with delight when I showed him how to rewrite his 92-parameter stored procedure. He seemed amazed that one could provide default values to stored procedures in SQL Server, and that one could also detect what parameters had been supplied.
From that point on, I and an accomplice administered the database and did all the database dev work, while Benjie happily settled into his new role. We kept the secret without problems. Any seasoned DBA knows how to look busy without really doing anything, but I was able to give Benjie a few good tips such as …
- If you are seen to be studying an ER diagram, nobody thinks you are idle
- Always have animated performance graphs on your screen, displaying as many criteria as possible.
- Have several open DBA reference books on your desk.
- Never automate any of the routine tasks such as scripting out, doing backups, testing restores, BCPing out tables, running and monitoring data feeds
The project proceeded harmoniously, and met its dates. As the recession eased, I was able to crawl out from my agreeable shelter and get back to my primary career. Benje left at the same time, unsurprisingly.
That should be the end of the story but, to this day, I still keep hearing stories about Benjie's subsequent career. His CV positively glowed after his success at our project. He is such an engaging character that he continues to carve out a career for himself around London. Last I heard, he was working as a DBA for a media company.
Long after his IT contributions are forgotten, I will still cherish his descriptions of the quivering Mapouka Dance, and of the musical language of the Yoruba.
|
-
Posted Friday, March 06, 2009 9:11 AM |
Tea drinking is important to developing software. It matters how you drink it, as well as how you prepare the tea.
It was a very long time ago, whilst working in a development team with a well-known computer company in Japan that I first realized there was more to drinking tea than dumping a typhoo teabag into a mug. One day, we’d reached that point where we were all completely jaded, when someone suggested going to see a Geisha for chakai. I had no idea what to expect and I was slightly apprehensive as I didn’t know the Japanese for ‘Excuse me Madam, but I’d rather keep my clothes on if you don’t mind’. We piled off into a bus and ended up in a beautiful wooden house where an elderly Japanese geisha, dressed in exquisite traditional kimono, entertained us to tea. We sat cross-legged around a table as she acted as hostess with both skill and dignity, entertaining us with a stream of jokes which had all the Japanese speakers convulsed in laughter. Even I couldn’t help giggling, though she could have been reading the telephone directory for all I knew. It worked like a charm. We piled back to work in a completely changed mood. I was amazed and delighted; and discovered that it was standard practice to ‘clear the brain’ in this way when things got stressed and out-of-proportion.
For Britain, the ceremony is quite different, but just as important. It is usually known as ‘cha’, the Chinese name, taken from army slang. First, the teapot; this must be brown earthenware, and has always been known as ‘Brown Betty’ for some reason. Brown Betty’s shape has been refined over hundreds of years. It is never dribblesome. (There is a book called ‘The Dribblesome Teapot’ by Norman Hunter, purely about a ‘Brown Betty’ that dribbles), It is brown because that color conserves the heat best. The Tea is of great importance. I favor a high-quality Assam, full-bodied, black as sin, unblended. Just to describe it make me tremble with excitement. A Sri Lanka Kanoy can be wonderful, producing a luscious bright-golden infusion. Darjeeling is excellent, with its rich, fruity taste. For variety, I’d always include Keemun or Oolong. Of course, on a hot day, a Celon tea is wonderful, served as Ice tea. People who claim they don’t like tea have probably drunk only disgusting reject leaves sent by puzzled producers in India over to the Midlands of England where it is mistakenly considered a delicacy.
I have a rather unconventional liking for Gunpowder tea, rolled in pellets, and unfermented. This Chinese green tea can be sipped for hours without the caffeine effect becoming unpleasant. In London, you will always find me in Soho, in an excellent Chinese restaurant where they happily bring you endless Brown Bettys full of piping hot Gunpowder tea.
I’m very much on the liberal wing as far as the preparation of tea goes. As I have previously pointed out in a blog, the art is to extract the caffeine without too much tannin; whilst conserving the aroma and flavor. The aroma relies on very volatile essential oils. You make a mistake and the flavor has gone. Warm the pot first. Always boil fresh water (soft water is best) and infuse the tea for three to five minutes, perhaps longer in hard-water areas, stirring the pot occasionally. Use one rounded teaspoonful of tea for each cup required. You will find that a six-minute infusion is required if tea is to be drunk with milk because the casein in the milk reacts with the Tannin.
Tea should be served in a quiet room away from the phones and screens. I have never been able to get hold of a professional Master of ceremonies, such as a Geisha, but one can take this role in turns. The Master of Ceremonies works very like a Chairman. Nobody is allowed to dominate the conversation, or to get overly technical. The ceremony ends in fifteen minutes. Any mention of post-it notes, pigs or chickens is banned.
The subject of Biscuits is contentious. I have even heard tell of chocolate biscuits being served, but I think that the line must be drawn here. If biscuits are served, they should be plain, so as not to detract from the delicate flavor of the tea. Dunking is anathema.
A final word about Milk and Sugar: Milk is allowable, so long as the tea is prepared specially for the addition of milk. Sugar ruins anything it is added to, and destroys the taste of tea. The Health Stasi who interfere with our natural god-given wholesome human right to the occasional Sumatran Cigar would expend their beastly energies to far greater good by pursuing the disgusting habit of adding sugar to tea.
Ever since I was initiated to the idea of Tea Ceremony in development teams, I have introduced it many times with great success. Unlike Agile Scrums, the practice leads to peace, harmony and a clear head. I once encountered some resistance when introducing the tea ceremony to a development team of rough Essex people near Southend. They called the glorious Formosa Oolong that I bought for them ‘Poofy Tea’, a name that somehow stuck. They took a long time to come to terms with the idea that Dunking was an unnatural vice, and that sugar was a vile drug. They knuckled down eventually, became converts, and ended up enthusiastically trying all sorts of way-out teas. One day, I shall tell the story of the tranquilizing effects of Elderflower Tea, Hop Tea and Apricot tea in the workplace.
|
-
Posted Wednesday, March 04, 2009 2:00 PM |
It is with huge embarrassment that I offer advice on how to Blog. Blogging is not an elitist thing; there is no right or wrong way of doing it. As you can legitimately blog to yourself in private with ‘Dear Diary’ confessions, blogs do not even need to be entertaining or informative.
I’ve been writing about IT on and off since around 1980. The only qualification I can therefore offer to underpin my advice is that I’ve been doing it a long time. Blogging? Well, in a sense; the short paragraph, the review, the commentary: it is all the same art. A blog is really nothing more than another way of publishing words. I’ve been doing genuine blogging for a long time, though not always about IT. The special, exciting, thing about a blog is that you get feedback: if people do, or don’t, like what you write, then you soon hear about it. In that way, it is more like writing for the stage, which I’ve also done.
If you are confident that you have a good technique worked out for Blogging then please read no further. My only mission here is to help anyone who is short of ideas, or who are struggling to pick up an audience.
There is no real difference in the way you write a Blog that you want people to read voluntarily, and an article in a magazine, or a newspaper. There are a number of books that will tell you the secrets of writing short interesting articles (or blogs). I use, as a guide, books on how to write, written by people whose writing I admire, and read for pleasure: Yes, they really exist.
If you want to blog regularly, then keep a small notebook with you. (a real notebook, since computers are hopeless for this.) Whenever an idea occurs to you or you hear of an idea, write it down, one item per page. Then try talking to people about the idea. If there is a flicker of interest, read up as much as you can about the idea. It is important not to write anything other than notes in your notebook at this stage. Develop each idea gradually, picking up facts and opinions over a period of time. Note these down in the notebook. Use as many sources as possible, and don't rely on the internet. Keep as many ideas as possible on the go at any one time. Keep them dead simple. This can give you a rather different attitude to misfortune. I can remember having a nasty accident whilst trying to construct a Greenhouse and thinking, as I got stitched together ‘Oh good, something I can blog about’.
If ideas don't come, then take a list of topics and put them together at random. A famous author used to go to a copy of the bible, and get two quotations at random until something jelled. Just change the book, probably, to one that is relevant to the topic, and it will work for you. I would not suggest using the bible for a technical blog, but there are fascinating topics to be had from Books online. To illustrate the point, I just stabbed wildly at MSDN, and came up with ‘Cross Apply’ & ‘Computed column’ Hmm.. could there be a blog there? ‘Global cursors’ & ‘ fetching rows’. Hey, someone else is using this technique.
Some ideas will flower naturally into a blog or article. Others need forcing. To force an idea, write 'Why? What? Who? How? Where? When? Which?' down the page, and then try to answer all these questions. If the idea is good, this works every time. Once you have the components of your blog, change them about, re-sequence them and chop them until suddenly a good blog posting appears like magic. If this doesn't work, scrap it. Be prepared to bin half your ideas.
When you have a rough Blog assembled, work on it. Take stuff out whenever you can. Using Twitter is a salutary reminder of the value of brevity. The great PG Wodehouse used the trick of sticking bits of paper with his work in progress, on the wall, at a height commensurate with his opinion of their quality. Only when work reached the ceiling did he send it to his publishers.
Apply a few rules
- Keep it simple. One idea only is the best.
- Don't decide on the length of a blog until you've written it.
- Read the results out loud and edit what you've written until it sounds natural.
- Never use slang, unless it is a gimmick on the post.
- Do not leave out words in sentences, as this makes it hard for people who have English as a second language
- Rework the first paragraph until it is perfect. Otherwise, it is the only bit that will ever be read.
- Ask other people for help
- Never feel proud of what you've done. Everything can be improved, usually by shortening it.
- Get frank opinions from the sort of people who you’d want to read your Blog.
If you write naturally about your work and interests, and be led by your enthusiasms, then the results are generally going to be far more popular, and generate more interest. Readers are very quick to detect whether you are talking from sheer enthusiasm and exuberance. It will shine through.
|
-
Posted Thursday, January 22, 2009 6:40 PM |
|
‘Technical Interviews’ have got to stop. They are a disgrace to the IT profession. Two MVPs who I asked the question ‘Have you ever passed a technical interview’ have admitted ‘Never’. I’d like more successful developers to confess their inability to remember much more than their name under the pressure of a technical interview. The most extreme geeks all have brains that blue-screen with a temporary aphasia under stress.
I have a new proposal to make. Instead of employing developers after asking them a series of trivia based from trawling obscure facts in MSDN, you should see how good they are at Table Tennis, Table footie, or guitar. I might include pool/Billiards too.
I intend to justify this apparently ludicrous assertion
There are many theories of what makes a good developer. In the 1960s, when the shortage of people with IT skills first became apparent, people were selected on their mathematical skills. This proved to be disastrous, and IT departments soon filled with strange geeky folk devoid of social skills, or any understanding of real business applications. This puzzled the recruiters who consulted the psychologists. The Psychologists pondered over this and, in the fashion of the time, came up with a set of problem-solving tests based on Hans Eysenck’s intelligence tests. In Britain, these seemed so obviously appropriate that they even appeared on adverts ‘If you can solve this puzzle in five minutes, call xxxxx’. Subsequent exercises in validation showed that they were no more successful than chance in successfully predicting a good IT programmer. In fact, it just told you that the person was good at puzzles; it also suggested that they didn’t get out enough in the evenings. The psychologists then took the more sensible approach of testing real successful IT programmers, and seeing what their skill-set consisted of. It turned out that good programmers were very articulate. Otherwise, they had no obvious dominant skill in common.
Selecting programmers who were good at language skills just didn’t look right. It had no ‘Face Validation’, and it didn’t impress the brash new profession of ‘HR’. They wanted tests like they had at school. Where there is a demand, there are always people prepared to supply it, and soon a lucrative market in Technical tests developed. The validation of any psychometric test of ability is a highly technical subject which I won’t bore you with. Please just remember that, because these many tests of technical competence have never been scientifically validated, they are no more effective in selecting the good candidates than testing to see if the applicant can sing ‘Somewhere over the rainbow’ in a high voice. The classic Microsoft interview questions, such as ‘Why are manhole covers round?’ shouldn’t be used until you can prove that successful programmers believe (wrongly, it turns out) that it is to stop the covers dropping into the hole, more frequently than do the duffers.
Besides fluency with spoken language, there is another essential component, a personality trait that is necessary for a good developer. This is a cussed stubbornness that won’t let go of a problem until he or she has solved it. It is that will to go through pain, boredom, hunger, and lack of sleep in pursuit of doing something that is almost impossible…. Such as playing guitar properly, solving advanced physics and mathematical problems, playing badminton or table Tennis well.
The best programmers I’ve ever met are astonishingly alike in this respect. If they can’t get something in their lives to work as well as they want, it turns into a man-machine struggle of epic proportions. If I give someone a programming puzzle to solve in an interview, the good programmers don’t necessarily know the answer; the good programmers refuse to finish the interview until they’ve got the answer right. It suddenly rears up in their minds as being even more important than the interview. The whole objective of getting a job is temporarily put to one side in pursuit of a solution.
The same personality, if she, or he, finds himself unable to play table-tennis properly, or play ‘tiptoe through the tulips’ on the ukulele, or beat the regulars at Pool, will bloody practice until she, or he, does or renders himself or herself incapacitated in the attempt.
If you think that this is an absurd theory, you will be surprised to hear that the standard American test of mathematics, which is prefaced by a rather long-winded form for name, address etc. , has an almost perfect correlation between the Mathematics score and the ability to fill in the preliminary form accurately. If the applicant has the mental stamina and obstinacy to apply to fill in the preliminary form you can be certain that he or she will be good at Maths. That same cussedness turns out a good IT programmer, or a good table-tennis player, or any skill that required dogged determination.
So, if I ever have to interview you for a technical development job, have no fear of being taxed with knowledge of obscure parameters to DBCC functions you would never dream of using, or obscure behavior of Transaction rollback in linked servers; No, beyond establishing a sound ability to understand questions and solve general programming problems, I shall be there to challenge you to a game of table football, croquet, pool, or any other skill that can only be achieved by bone-headed stubbornness and practice beyond the patience of an ordinary mortal.
|
-
Posted Sunday, January 18, 2009 4:37 PM |
On Friday, I'd managed to
work myself into a rage about something. I then sat down and wrote the following function in TSQL that tells you the date of Easter for any year you wish. Afterwards, I felt sublimely at peace with the world. Perhaps I remain an unreconstructed geek, after all.
This is one bit of code that that I shall not attempt to document. I'll
explain why afterwards.
ALTER FUNCTION Easter ( @input_date DATETIME )
/*
calculates the date of easter for the given year. This calculation
is the current one approved by the vatican. It differs from
the greek orthodox.
e.g.
DECLARE @Easter TABLE
(
year INT,
Easter DATETIME
)
DECLARE @TheYear DATETIME
SELECT @TheYear = DATEADD(year, -15, CURRENT_TIMESTAMP)
WHILE DATEDIFF(year, @TheYear, '1 Jun 2020') > 0
BEGIN
INSERT INTO @Easter ( Year, Easter )
SELECT DATEPART(year, @TheYear),
dbo.easter(@TheYear)
SELECT @TheYear = DATEADD(year, 1, @TheYear)
END
SELECT year,
CONVERT(CHAR(11), Easter, 113) AS [Easter Day]
FROM @easter
*/
RETURNS DATETIME
WITH EXECUTE AS CALLER
AS BEGIN
DECLARE @y INTEGER,
@dy INTEGER,
@easter VARCHAR(20),
@easter_month INTEGER,
@easter_day INTEGER ;
SET @y = DATEPART(YEAR, @input_date) ;
SET @dy = ( ( 19 * ( @y % 19 ) + ( @y / 100 ) - ( ( @y / 100 ) / 4 ) - ( ( ( @y / 100 ) - ( ( ( @y / 100 ) + 8 ) / 25 ) + 1 ) / 3 ) + 15 ) % 30 ) + ( ( 32 + 2 * ( ( @y / 100 ) % 4 ) + 2 * ( ( @y % 100 ) / 4 ) - ( ( 19 * ( @y % 19 ) + ( @y / 100 ) - ( ( @y / 100 ) / 4 ) - ( ( ( @y / 100 ) - ( ( ( @y / 100 ) + 8 ) / 25 ) + 1 ) / 3 ) + 15 ) % 30 ) - ( ( @y % 100 ) % 4 ) ) % 7 ) - 7 * ( ( ( @y % 19 ) + 11 * ( ( 19 * ( @y % 19 ) + ( @y / 100 ) - ( ( @y / 100 ) / 4 ) - ( ( ( @y / 100 ) - ( ( ( @y / 100 ) + 8 ) / 25 ) + 1 ) / 3 ) + 15 ) % 30 ) + 22 * ( ( 32 + 2 * ( ( @y / 100 ) % 4 ) + 2 * ( ( @y % 100 ) / 4 ) - ( ( 19 * ( @y % 19 ) + ( @y / 100 ) - ( ( @y / 100 ) / 4 ) - ( ( ( @y / 100 ) - ( ( ( @y / 100 ) + 8 ) / 25 ) + 1 ) / 3 ) + 15 ) % 30 ) - ( ( @y % 100 ) % 4 ) ) % 7 ) ) / 451 ) + 114 ;
SET @easter_month = @dy / 31 ;
SET @easter_day = ( @dy % 31 ) + 1 ;
-- assumes proprietary, non-ANSI local temporal format
SET @easter = CASE @easter_month
WHEN 3 THEN 'Mar'
ELSE 'Apr'
END ;
SET @easter = @easter + SPACE(1) + CAST(@easter_day AS VARCHAR(2)) + ', '
+ CAST(@y AS VARCHAR(4))
RETURN CAST(@easter AS DATETIME) ;
END ;
The story of the date of Easter is tinged with farce. By the third century, Christians had settled down to the idea of celebrating the anniversary of Jesus' resurrection. Unfortunately, nobody at the time had thought of jotting down the date
when it happened. They felt sure that it had happened some time in the Jewish month of Nisan, at around the full moon. The Jewish calendar was lunar, and Christians were forced to rely on the Jews to tell them when the
month approached. Even then, calculating the Sunday after the full moon
was almost impossible then, so the date chosen varied between Christian communities. At the council of Nicaea,
in 325 AD, Constantine determined that all Christians should celebrate on the same day, and they decided that Easter should be on the first Sunday after the first full moon after the spring equinox, but not if it occurred on the same day as the Jewish Passover. Constantine wasn't a 'small detail' man and optimistically concluded "we ought not to have anything with the (Jewish calendar), for the Saviour has shown us another way".
He hadn't. They should have chosen a fixed date.
At this stage, I can imagine the astronomers, the geeks of the day, weeping, for
much the same reasons as we weep now when managers promise the unattainable from
IT. Even the fixed date would have been a compromise: Caesar's calendar was flawed, any fixed date for the vernal equinox drifted by 11 minutes a year. Calculating the
date of the full moon required a precise calculation of the sun, orbit of the earth and phases of the moon.
and had to take into account the drift of the calendar. It also had to compensate for the elliptical orbits of the planets, and the various gravitational effects.
They couldn't do the calculation accurately then, and the struggle over the
centuries to get the right answer funded the dim flickering light of
science, even at the darkest of times. In the nineteenth century, a millennium
and a half later, Vatican scholars finally produced a complex fourteen-part algorithm to calculate the date, and it is still in use today.
I nominate this as the longest, and most expensive, IT project ever.
The formula used gets the right date, but it is difficult to comprehend, and
seems flawed to me. Nevertheless, it is the way Easter is calculated, so there
is no sense in correcting it, unless you wish to start a new Christian sect. Meanwhile, the Greek Orthodox church have a different set of calculations for calculating Easter, but that is another story
|
|
|