SQLblog.com - The SQL Server blog spot on the web

SQL Server 2012 Early Adoption Cook Book <- starting to be available

Greg Low — Wed, 01 Feb 2012 22:28:00 GMT

I've been working on a team with Roger Doherty building parts of what's now become the SQL Server 2012 Early Adoption Cook Book . So, if you work on the bleeding edge of SQL Server and are keen to get your head around what's coming, this is a seriously good resource. Time to go and get it. The material is constructed as a large number of bite-sized pieces. Each presentation is about 15 minutes in length, and each demo is about 5 minutes. And there are lots of them. Look for recordings of these by...(read more)

Using XACT_ABORT ON may be faster than using TRY...CATCH

Alexander Kuznetsov — Wed, 01 Feb 2012 22:25:00 GMT

To ensure atomicity of transactions, we can use XACT_ABORT ON or wrap the transaction in TRY block and rollback in CATCH block. In some cases, the XACT_ABORT ON approach uses noticeably less CPU. I am posting repro scripts. Please run them, tweak them, and post your findings. Environment I've run my scripts on 2008 R2 Dev Edition. Snapshot isolation is enabled, READ_COMMITTED_SNAPSHOT is not enabled. Test data We are using the same test data as in my previous post: CREATE TABLE dbo.Toggle1 ( id INT...(read more)

Wrapping related changes in a transaction may use less CPU.

Alexander Kuznetsov — Wed, 01 Feb 2012 22:02:00 GMT

Wrapping related changes in a transaction is a good way to ensure data integrity. Besides, in some cases it just runs noticeably faster, using less CPU. As usual, I am posting repro scripts, which you can run, tweak, and see for yourself. Environment I've run my scripts on 2008 R2 Dev Edition. Snapshot isolation is enabled, READ_COMMITTED_SNAPSHOT is not enabled. Test data After applying Occum's razor, all we need is two tables with one row in each: CREATE TABLE dbo.Toggle1 ( id INT NOT NULL PRIMARY...(read more)

DTLoggedExec 1.1.2008.4 SP2 released!

Davide Mauri — Wed, 01 Feb 2012 17:15:44 GMT

I’ve released couple of hours ago the SP2 of my DTLoggedExec tool.

For those who don’t know it, it’s a DTEXEC replacement, useful to execute SSIS and having logging provided right from the engine and not from the package itself.

More info can be found here:

http://dtloggedexec.codeplex.com/

This SP2 release add an important feature to the CSV Log Provider. It's now possible to store a personalized label into each log, in order to make it easy to identify or group logs.

Let's say, for example, that you have 10 packages in your ETL solution, and each time you have to load your data, you need to execute all those 10 packages. In other words , you have a batch made of 10 packages. It would be nice if all logs - one for each package - can be identified as a whole and grouped together, in order to quickly identify all the log of a articular batch.

This will make things easier when you want to know the overal time consumed by each batch execution.

The new "ExecutionLabel" attribute will help to achieve this. A useful ExecutionLabel can be obtained using SQL Server Agents Token. For example:

LABEL=$(ESCAPE_SQUOTE(JOBID))_$(ESCAPE_SQUOTE(STRTDT))_$(ESCAPE_SQUOTE(STRTTM))

ChangeLog

Updated the CSV Log Provider in order to write the status of the log file in the header.
- An OPEN status means that the log file is being written.
- A CLOSED status means that the log files has been written correctly.
- A file can be loaded into the database log only if is in the CLOSED state.
CSV Log files have 2 additional rows in the header: one for the FileStatus and one for the ExecutionLabel values.
- File format has been update to 4 from 3. (Only the header section of the file has been changed).
CSV Log Provider will now display EndTime value to the Console
Updated the import-log-data.sql to correctly load file with format 3 (the old one) and 4 (the new one).
Updated database schema to version 19 in order to store the new ExecutionLabel value
Update samples in order to show how to use the new ExecuteLabel option

As usual the download is available for free here:

http://dtloggedexec.codeplex.com/releases/81526/download/336490

SSIS MicroTraining: SSIS Package Configurations

andyleonard — Wed, 01 Feb 2012 12:00:00 GMT

I will be delivering another free, online MicroTraining on SSIS Package Configurations Tuesday, 7 Feb 2012, at 10:00 AM EST. Join Andy Leonard as he demonstrates uses of SSIS Package Configurations for externalizing SSIS package variable values. Register here ! :{>...(read more)

Performance impact: the cost of NUMA remote memory access

Linchi Shea — Mon, 30 Jan 2012 23:17:20 GMT

These days if you get a new server-class machine to run SQL Server, you can almost be 100% sure that it’ll be running on NUMA hardware. The recent AMD Opteron and Intel Nehalem-based processors are all built on some form of NUMA architecture. The current consensus is that as the number of processors grows, their shared memory bus can easily get congested and becomes a major impediment to scalability. NUMA hardware solves this scalability challenge by dividing the processors into groups, with each...(read more)

Book Review (Book 8) - The Elements of Persuasion

BuckWoody — Mon, 30 Jan 2012 14:57:23 GMT

This is a continuation of the books I challenged myself to read to help my career - one a month, for year. You can read my first book review here. The book I chose for January 2012 was: The Elements of Persuasion by Richard Maxwell and Robert Dickman.

Why I chose this Book:

As I mentioned in a previous review, I think good storytelling is an essential part of any career. Communication is basic in not only our professional but personal lives, and everyone I’ve met responds well to stories, from children to executive audiences. Not only that, learning to tell a story helps you formulate concepts about the topic, which is yet another way of learning.

I heard about this book from a couple of folks, and it landed within search of “storytelling” and “business”. Whenever I just search for “Storytelling” I either end up with lists of stories (which is fine) or lists of children’s books on storytelling (which is also fine) but neither of these are quite what I’m looking for.

A quick search on Amazon and I located the book, and then a quick check of my various e-library offerings and I downloaded it to my laptop for reading.

What I learned:

This is a “selling” book, but not like you might think. It’s not a book of a quick sale like at a car-lot or a “quick-sale” environment. It’s more along the lines at the executive level and longer-term sales - those involve stories as well.

Sadly, this is another “business book” - the kind I normally don’t like much. There are typical case-study layouts with lots of examples, but in my mind not enough didactic information to actually help you develop a good story-telling mantra.

Even so, I learned some interesting things about the process these authors use. Some of the case studies are interesting, and I did pull out that a story should work towards a single, defining sentence. This isn’t unique to this book, but it is a reinforcement of what I’ve learned elsewhere. Although nothing to do with storytelling, I did like the reference to Lockheed’s “14 Rules”, which I hadn’t read before. They also break down the storytelling process into five elements, which is actually covered better (in my mind) in a book called “20 Master Plots” , which may actually be the storytelling book I’ve been searching for.

Or perhaps I should just write the one I’m looking for.

At any rate, not sure I would recommend this book to others - perhaps as a check-out, but not a purchase, at least if this is for the same reason I looked it up.

Raw Notes:

As I read, I take notes - it’s called “reading with a pencil”. These are the notes I made to myself, in no particular order and with no context other than the book itself:

Stories are interesting to us all.
Describes five elements in a story, but in fact this is for only one type of story. Other books describes more story types.
Very standard business book, but there are good tips in some of the chapters.
Explained how to connect with the audience, good points Spends a lot of time referring to other books The book of five rings Work towards a single, memorable sentence.
Changes partway through into stories about stories. This is better.
A mix of storytelling and sales, although this was touted for sales, feels much more like selling than storytelling, advertisements.
Interesting story about memory championships, where contestants memorize cards. They use unusual stories.
Look up Lockheed and the 14 rules

OT: A Good Reason to Attend #SQLBits

AlbertoFerrari — Mon, 30 Jan 2012 08:41:46 GMT

My 4yo son keeps asking me why I should leave home so often to attend SQL conferences around the world and today, after a couple of days of snow, he discovered a good reason, along with the best place for the SQLBits scarf....(read more)

Suggested Best Practises and naming conventions

jamiet — Sun, 29 Jan 2012 18:08:53 GMT

Once upon a time I blogged at http://consultingblogs.emc.com/jamiethomson but that ended in August 2009 when I left EMC. There is a lot of (arguably) valuable content over there however certain events in the past leave me concerned that that content is not well cared for and I don't have any confidence that it will still exist in the long term. Hence, I have taken the decision to re-publish some of that content here at SQLBlog so over the coming weeks and months you may find re-published content popping up here from time-to-time.

This is the third such blog post in which I suggest some best practises and naming conventions that you may choose to employ and which was originally published here (I have changed it slightly since then – spotters badge if you can find the differences!). The first post in this series can be found at [SSIS] OnPipelineRowsSent and the second at Dataflow mechanics.

I thought it would be worth publishing a list of guidelines that I see as SSIS development best practices. These are my own opinions and are based upon my experience of using SSIS over the past 18 months. I am not saying you should take them as gospel but these are generally tried and tested methods and if nothing else should serve as a basis for you developing your own SSIS best practices.

One thing I really would like to see getting adopted is a common naming convention for tasks and components and to that end I have published some suggestions at the bottom of this post.

This list will get added to over time so if you find this useful keep checking back here to see updates!

If you know that data in a source is sorted, set IsSorted=TRUE on the source adapter output. This may save unnecessary SORTs later in the pipeline which can be expensive. Setting this value does not perform a sort operation, it only indicates that the data it sorted.
Rename all Name and Description properties from the default. This will help when debugging particularly if the person doing the debugging is not the person that built the package.
Only select columns that you need in the pipeline to reduce buffer size and reduce OnWarning events at execution time
Following on from the previous bullet point, always use a SQL statement in an OLE DB Source component or LOOKUP component rather than just selecting a table. Selecting a table is akin to "SELECT *..." which is universally recognised as bad practice. (http://www.sqljunkies.com/WebLog/simons/archive/2006/01/20/17865.aspx). In certain scenarios the approach of using a SQL statement can result in much improved performance as well (http://blogs.conchango.com/jamiethomson/archive/2006/02/21/2930.aspx).
~~Use SQL Server Destination as opposed to OLE DB Destination where possible for quicker insertions~~ I used to recommend using SQL Server Destinations wherever possible but I've changed my mind. Experience from around the community suggests that the difference in performance between SQL Server Destination and OLE DB Destination is negligible and hence, given the flexibility of packages that use OLE DB Destinations it may be better to go for the latter. Its an "it depends" consideration so you should consider what you prefer based on your own testing.
Use Sequence containers to organise package structure into logical units of work. This makes it easier to identify what the package does and also helps to control distributed transactions if they are being implemented.
Where possible, use expressions on the SQLStatementType property of the Execute SQL Task instead of parameterised SQL statements. This removes ambiguity when different OLE DB providers are being used. It is also easier. (UPDATE: There is a caveat here. Results of expressions are limited to 4000 characters so be wary of this if using expressions).
Use caching in your LOOKUP components where possible. It makes them quicker. Watch that you are not grabbing too many resources when you do this though.
LOOKUP components will generally work quicker than MERGE JOIN components where the 2 can be used for the same task (http://blogs.conchango.com/jamiethomson/archive/2005/10/21/2289.aspx). Note that this is not always the case so test and measure, test and measure, test and measure!
Always use DTExec to perf test your packages. This is not the same as executing without debugging from SSIS Designer (http://www.sqlis.com/default.aspx?84).
Use naming conventions for your tasks and components. I suggest using acronyms at the start of the name and there are some suggestions for these acronyms at the end of this article. This approach does not help a great deal at design-time where the tasks and components are easily identifiable but can be invaluable at debug-time and run-time. e.g. My suggested acronym for a Data Flow Task is DFT so the name of a data flow task that populates a table called MyTable could be "DFT Load MyTable".
If you want to conditionally execute a task at runtime use expressions on your precedence constraints. Do not use an expression on the "Disable" property of the task.
Don't pull all configurations into a single XML configuration file. Instead, put each configuration into a seperate XML configuration file. This is a more modular approach and means that configuration files can be reused by different packages more easily.
If you need a dynamic SQL statement in an OLE DB Source component, set AccessMode="SQL Command from variable" and build the SQL statement in a variable that has EvaluateAsExpression=TRUE. (http://blogs.conchango.com/jamiethomson/archive/2005/12/09/2480.aspx)
When using checkpoints, use an expression to populate the CheckpointFilename property which will allow you to include the value returned from System::PackageName in the checkpoint filename. This will allow you to easily identify which package a checkpoint file is to be used by.
When using raw files and your Raw File Source Component and Raw File Destination Component are in the same package, configure your Raw File Source and Raw File Destination to get the name of the raw file from a variable. This will avoid hardcoding the name of the raw file into the two seperate components and running the risk that one may change and not the other.
Variables that contain the name of a raw file should be set using an expression. This will allow you to include the value returned from System::PackageName in the raw file name. This will allow you to easily identify which package a raw file is to be used by. N.B. This approach will only work if the Raw File Source Component and Raw File Destination Component are in the same package.
Use a common folder structure (http://blogs.conchango.com/jamiethomson/archive/2006/01/05/2559.aspx)
Use variables to store your expressions (http://blogs.conchango.com/jamiethomson/archive/2005/12/05/2462.aspx). This allows them to be shared by different objects and also means you can view the values in them at debug-time using the Watch window.
Keep your packages in the dark (http://www.windowsitpro.com/SQLServer/Article/ArticleID/47688/SQLServer_47688.html). In summary, this means that you should make your packages location unaware. This makes it easier to move them across environments.
If you can, filter your data in the Source Adapter rather than filter the data using a Conditional Split transform component. This will make your data flow perform quicker.
When storing information about an OLE DB Connection Manager in a configuration, don't store the individual properties such as Initial Catalog, Username, Password etc... just store the ConnectionString property.
Your variables should only be scoped to the containers in which they are used. Do not scope all your variables to the package container if they don't need to be.
Employ namespaces for your packages
Make log file names dynamic so that you get a new logfile for each execution.
Use ProtectionLevel=DontSaveSensitive. Other developers will not be restricted from opening your packages and you will be forced to use configurations (which is another recommended best practice)
Use annotations wherever possible. At the very least each data-flow should contain an annotation.
Always log to a text file, even if you are logging elsewhere as well. Logging to a text file has less reliance on external factors and is therefore most likely to contain all information required for debugging.
Create a new solution folder in Visual Studio Solution Explorer in order to store your configuration files. Or, store them in the 'miscellaneous files' section of a project.
Always use template packages to standardise on logging, event handling and configuration.
If your template package contains variables put them in a dedicated namespace called "template" in order to differentiate them from variables that are added later.
Break out all tasks requiring the Jet engine (Excel or Access data sources) into their own packages that do nothing but that data flow task. Load the data into Staging tables if necessary. This will ensure that solutions can be migrated to 64bit with no rework.
Don't include connection-specific info (such as server names, database names or file locations) in the names of your connection managers. For example, "OrderHistorySystem" is a better name than "Svr123ABC\OrderHist.dbo".

The acronyms below can be used at the beginning of the names of tasks to identify what type of task it is.

Task	Prefix
For Loop Container	FLC
Foreach Loop Container	FELC
Sequence Container	SEQC
ActiveX Script	AXS
Analysis Services Execute DDL	ASE
Analysis Services Processing	ASP
Bulk Insert	BLK
Data Flow	DFT
Data Mining Query	DMQ
Execute DTS 2000 Package	EDPT
Execute Package	EPT
Execute Process	EPR
Execute SQL	SQL
File System	FSYS
FTP	FTP
Message Queue	MSMQ
Script	SCR
Send Mail	SMT
Transfer Database	TDB
Transfer Error Messages	TEM
Transfer Jobs	TJT
Transfer Logins	TLT
Transfer Master Stored Procedures	TSP
Transfer SQL Server Objects	TSO
Web Service	WST
WMI Data Reader	WMID
WMI Event Watcher	WMIE
XML	XML

These acronyms should be used at the beginning of the names of components to identify what type of component it is.

Component	Prefix
DataReader Source	DR_SRC
Excel Source	EX_SRC
Flat File Source	FF_SRC
OLE DB Source	OLE_SRC
Raw File Source	RF_SRC
XML Source	XML_SRC
Aggregate	AGG
Audit	AUD
Character Map	CHM
Conditional Split	CSPL
Copy Column	CPYC
Data Conversion	DCNV
Data Mining Query	DMQ
Derived Column	DER
Export Column	EXPC
Fuzzy Grouping	FZG
Fuzzy Lookup	FZL
Import Column	IMPC
Lookup	LKP
Merge	MRG
Merge Join	MRGJ
Multicast	MLT
OLE DB Command	CMD
Percentage Sampling	PSMP
Pivot	PVT
Row Count	CNT
Row Sampling	RSMP
Script Component	SCR
Slowly Changing Dimension	SCD
Sort	SRT
Term Extraction	TEX
Term Lookup	TEL
Union All	ALL
Unpivot	UPVT
Data Mining Model Training	DMMT_DST
DataReader Destination	DR_DST
Dimension Processing	DP_DST
Excel Destination	EX_DST
Flat File Destination	FF_DST
OLE DB Destination	OLE_DST
Partition Processing	PP_DST
Raw File Destination	RF_DST
Recordset Destination	RS_DST
SQL Server Destination	SS_DST
SQL Server Mobile Destination	SSM_DST

No respect: NUMA affinity meets query parallelism

Linchi Shea — Sat, 28 Jan 2012 05:32:51 GMT

What happens when NUMA affinity meets query parallelism? It gets no respect! SQL Server allows you to affinitize a TCP port to a specific NUMA node or a group of NUMA nodes. Books Online has an article on How to: Map TCP/IP ports to NUMA Nodes . And this BOL article discusses various NUMA affinity scenarios. Recently, I have been playing with NUMA affinity on various servers with hardware NUMA, such as those with Intel X5690 and Intel E7-4870, running SQL Server 2008 R2 RTM (10.50.1600) and SQL Server...(read more)

T-SQL stored procedure for finding/replacing strings in a text file. Really?

Linchi Shea — Fri, 27 Jan 2012 05:33:11 GMT

I know people have been doing all sorts of things with T-SQL, and I have absolutely no issue with people trying to push the limit of what T-SQL can do, or what you can use it to accomplish, especially when it’s for demonstration or pedagogical purposes, or as an intellectual exercise. But then I bumped into an article on writing a T-SQL stored procedure to find and replace strings in a text file. That really unsettled me! Sure, when you are in a hurry, you need to grab a tool--any tool--to get the...(read more)

Bad Habits to Kick : Thinking a WHILE loop isn't a CURSOR

AaronBertrand — Thu, 26 Jan 2012 21:50:00 GMT

I see a lot of people suggest while loops instead of cursors in situations where row-based processing is required (or, at least, where folks think that row-based processing is required). Sometimes the justification is that constructing a while loop is simpler and more straightforward than constructing a cursor. Others suggest that a while loop is faster than a cursor because, well, it isn't a cursor. Of course the underlying mechanics still represent a cursor, it's just not explicitly stated that...(read more)

Optimize Table and Column Size in #PowerPivot and #BISM #Tabular #SSAS #VertiPaq

Marco Russo (SQLBI) — Thu, 26 Jan 2012 20:54:36 GMT

When you create a PowerPivot workbook or a BISM Tabular model (new in Analysis Services 2012), you store data by using the VertiPaq engine, which is an in-memory columnar database. Instead of considering the row of a table as the main unit of storage, it considers every column as a separate entity and stores data for every column in a separate way. This makes it very fast to query data for a single column, but requires a higher computational effort in order to retrieve data for several columns of...(read more)

Thoughts on Test Driven Database Development

jamiet — Thu, 26 Jan 2012 08:30:00 GMT

Test-Driven Development (TDD) is a software development practise that has been around for a few years. Wikipedia describes it as:

Test-driven development (TDD) is a software development process that relies on the repetition of a very short development cycle: first the developer writes a failing automated test case that defines a desired improvement or new function, then produces code to pass that test and finally refactors the new code to acceptable standards. Kent Beck, who is credited with having developed or 'rediscovered' the technique, stated in 2003 that TDD encourages simple designs and inspires confidence.

http://en.wikipedia.org/wiki/Test-driven_development

Since 2003 TDD practises have seen refinements such as Behavior-Driven Development and Uncle Bob's Three Rules of TDD, all the while TDD has pretty much become an accepted way of developing quality software. Accepted that is everywhere outside of the database development arena and that is the arena in which I spend my working life. TDD simply has not, in my opinion, caught on with database developers like it has our appdev brethren and I was reminded of this yesterday when Atul Thakor asked on Twitter:

anyone done TDD for database development and would they recommend it?

https://twitter.com/#!/atulthakor/status/161886007929733120

To which my answer was an emphatic:

(1) yes & (2) absolutely, yes
https://twitter.com/#!/jamiet/status/161894215217987585

I'll use this blog post to expand on that outside of 140 characters.

In October 2010 I undertook a mini-project for the client I was working for at the time (a bank) where a colleague and I were tasked with building the database portion of a system that would support reconciliation of our ETL processes. It was a nice piece of work in that it was small, well-scoped, time-bound, greenfield, did not have any external dependancies and had a technically savvy product owner. We sat down at the start and decided that this was an ideal opportunity to trial TDD as a method of developing a database; I would write the failing tests and my colleague would make the tests pass. We came up with some guiding principles and, although we didn't know it at the time, they were pretty close to Uncle Bob's three rules.

I used Visual Studio 2010's database unit testing framework¹ to write my tests and have them run as part of our Continuous Integration (CI) build (see Setting up database unit testing as part of a Continuous Integration build process). I would write the tests, check-in, the CI build would fail and my colleague would "get latest" in order to see what code he had to write to stop the build from failing. To cut a long story short the use of TDD was considered to be a great success; we shipped a working system on time/on budget and moreover, even though I didn't write a scrap of code that went into production I have never had more confidence that a system I was involved in building worked as intended. That's quite a statement. My confidence stemmed from the fact that as the test author I was ultimately responsible for ensuring that the system did what it was supposed to; I could qualify my confidence by pointing at our CI build and highlighting the number of tests that were passing and how that number had steadily increased as the project progressed.

By the time the project had finished the database consisted of (if memory serves me correctly) 6 tables and about 10 stored procedures or functions (so yes, very small). To test that we had roughly 70 tests that were getting run up to 20 times a day. The project had taken about two months from start-up to final delivery - you can make your own opinions as to whether you consider that prompt or tardy but our product owner was happy and that's pretty much all that counted as far as I was concerned.

Since that project I have moved onto other clients and at each one I have always extolled the use of database unit testing; we haven't always practised TDD but at each one we have been writing database unit tests and in the future I suspect that a client's willingness (or lack thereof) to use database unit testing will be a major factor in influencing whether we end up working together or not.

Are you a database developer doing database unit testing or perhaps even TDD? Let me know in the comments, I'd love to hear about others' experiences.

@jamiet

¹Yes, that linked-to article from 7 years ago is the best one I could find to describe what Visual Studio's Database Unit testing Framework actually is - sort it out Microsoft!

UPDATE: I have just remembered that Jamie Laflen has written an excellent whitepaper entitled Apply Test-Driven Development to your Database Projects that goes into much more detail about how to achieve database TDD using Visual Studio than I have here. Well worth a read.

Yet another gotcha: variables' scopes do not end where they should.

Alexander Kuznetsov — Wed, 25 Jan 2012 22:09:00 GMT

Be careful: unlike most other languages, T-SQL does not limit variables' scope to the block where the variable has been defined. For example, the following snippet compiles and runs: -- @to is not in scope yet -- the line below would not compile --SET @to = @to + 3 ; IF DATEPART ( weekday , GETDATE ()) = 3 BEGIN ; -- the scope of this DECLARE does not end when the block ends DECLARE @from INT , @to INT ; SELECT @from = 5 , @to = 7 ; SELECT Number FROM data.Numbers WHERE Number BETWEEN @from AND @to...(read more)

OT: Noisy v. Important

andyleonard — Wed, 25 Jan 2012 12:00:00 GMT

A new personal blog post: Discerning Between Noisy and Important...(read more)

Whatever happened to Twitter Annotations?

jamiet — Wed, 25 Jan 2012 09:46:00 GMT

In April 2010 Twitter announced a new feature that they would soon be introducing - Twitter Annotations. Put simply Twitter Annotations can be described as the ability to attach metadata to a tweet; think hashtags on steroids. Lots of people were quite excited about it:

I love to sit on the beach. One of the coolest things about the beach is the number of layers of visual depth. Look at the sand and it's beautiful, but zoom your eyes in closer and you'll see a whole layer of life running around on the sand that you didn't see before. Look even closer and you can see individual grains of sand, water and light dancing between them. Look closer still and you see that each grain of sand is a unique object with its own texture. If your eyes are strong enough, or you have a machine to help you, you can see even more layers by looking closer still. That's what Twitter is going to be like with the launch of Twitter Annotations this Summer. It's a beautiful vision, with huge potential
What Twitter Annotations mean by Marshall Kirkpatrick

Today at the Twitter Chirp Hack Day I talked with a ton of developers and the new feature they were most interested in. Adam Jackson echoed everyone I’ve heard today when he tweeted “Twitter Annotations is what I’ve been wanting FOREVER.”
Developers: how will we all get along with Twitter’s annotation feature? by Robert Scoble

Twitter announced a series of new features at its Chirp conference in April, ...the one that has the most potential to change the way the social network functions in fundamental ways is Annotations, which Twitter said would be rolled out in the second quarter of the year ... Annotations would allow developers (and Twitter itself, of course) to add additional information to a tweet — such as a string of text, a URL, a location tag or bits of data — without affecting its character count. In other words, such information would be metadata about the tweet or the user who posted it, and would be carried along as an additional payload as it traveled through the Twitter network. Apps and services could then collect that information and filter it or make sense of it. In some ways, Annotations are like Facebook’s open graph protocol, which also adds metadata to the behavior of users on certain sites when they’re logged in
Twitter Annotations Are Coming — What Do They Mean For Twitter and the Web? by Matthew Ingram

What <others> did not mention is what I think is potentially the most fascinating use of opening up annotations. Google’s success today is built on their page rank algorithm that measures the validity of a web page by the number of incoming links to it and the page rank of the sites containing those links – its a system built on reputation. Twitter annotations could open up a new paradigm however – let’s call it People rank- where reputation can be measured by the metadata that people choose to apply to links and the websites containing those links. Its not hard to see why Google and Microsoft have paid big bucks to get access to the Twitter firehose!
Interesting things – Twitter annotations and your phone as a web server by Jamie Thomson (i.e. me!)

Twitter themselves said in May 2010:

We will continue to move as quickly as we can to deliver the Annotations capability to the market so that developers everywhere can create innovative new business solutions on the growing Twitter platform.
The Twitter Platform

That was 20 months ago. The question I now ask is....where are they? Evidently I'm not the only one asking that question because in a thread entitled How can I try the Annotations API? in November 2011 Gustavo Frederico asked:

How can I try the Annotations API? I'm looking forward to trying it out.

To which Taylor Singletary (a Twitter employee) replied:

Annotations is still more concept then reality. Maybe some day we'll have more to say about them.

Hmmm...in 18 months the situation has have gone from "We will continue to move as quickly as we can to deliver the Annotations capability to the market" & "rolled out in the second quarter of the year" to "Annotations is still more concept then reality", that's quite a climb-down if you ask me. I have strong hopes that Twitter Annotations will be with us eventually but the deafening silence isn't particularly encouraging.

You might ask why I am bothered, I am only a SQL Server developer after all. That is true but I still consider that my job can loosely be defined as extracting value from data and from that perspective the onslaught of data (nay, structured data) that Twitter Annotations would bring should be of interest to both myself and my clients.

I am also fascinated as to how Twitter Annotations could work with Schema.org which is heavily backed by Google and Microsoft and which Microsoft are pushing as the backbone of Contracts in Windows 8 (Schema.org is mentioned in this video from the Build conference).

So, I ask again, whatever happened to Twitter Annotations? Does anyone know?

@jamiet

Performance impact: SQL2008 R2 audit and trace

Linchi Shea — Wed, 25 Jan 2012 03:29:27 GMT

We are told that SQL Server 2008 R2 Audit (and SQL Server 2008 Audit) has much less performance overhead than SQL Trace when we try to capture the same information. Knowing how SQL Server R2 Audit is implemented (i.e. on top of the extended events infrastructure), I’ve always taken that as a given and never bothered to check it out. Recently, I had to capture some object access information, and it turned out that SQL Audit was not the most convenient tool for the task. I had to go back to SQL Trace....(read more)

Team Foundation Server (TFS) in the Cloud - My Experience So Far

BuckWoody — Tue, 24 Jan 2012 12:45:13 GMT

I recently joined a software development project that involves not only myself and other internal Microsoft employees, but a partner and a customer as well. We are building a hybrid solution that uses assets on premises as well as Windows Azure for processing. When we put the team together we picked a methodology (Agile) for the project (we use multiple methodologies at Microsoft - whatever the project needs) and then we started talking about Source Control.

We’re all comfortable with various tools for check-in-check-out, branching, and so on. We have all used GIT, SVN, and TFS. Some of us have even used Source Safe in past, but that’s another post. Each company has a full set of Source Control systems in place. But using each other’s systems requires logins, firewalls and the like - so we decided to use the TFS Service Preview to run the entire project from “the cloud”. Here are my experiences with that.

The process was really simple. In fact, we talked about using the cloud TFS in the first SCRUM, and the team was working from the Work Items list that afternoon. The original account login provides a web interface to allow people to join the team. Each of us happened to have a Live.Com address, so we just invited those addresses to join and they got a link, like this:

projectname.tfspreview.com

I’m using Visual Studio, and it’s a requirement for TFS preview to have SP1 installed, and this patch: KB2581206

From there, I opened Visual Studio and navigated from the main menu to Team and then Connect to Team Foundation Server. I’m given this menu:

Selecting port 443 and HTTPS (for security) and then ensuring the lower link has the “tfs” appended as the location, I opened the project.

(This VSTS screenshot is of a project I did in my University of Washington class I teach - I never show client code or names in a blog post)

From there it’s a normal set of operations. Right now the preview doesn’t have some things I’d really like, such as an automated build or some of the testing tools, but you can read this blog entry to learn more about the entire sign-up process, and what the team has planned.

Each day I log in to the project, and I’m given this new sign-in option:

I click the option, and I open the environment, hit My Work Items query, and get to work. All in all, a seamless - although basic - experience. The speed at which we could set up and work on a project was really sweet. It’s remarkable how un-remarkable this is - I just do my work each day, everything is running and backed up in the cloud. I think that’s the point.

SQL Server 2012 : The "Launch Date" is not what you think it is

AaronBertrand — Mon, 23 Jan 2012 21:58:00 GMT

I see a lot of people getting really excited. There is a "virtual launch event" for SQL Server 2012 being held on March 7, 2012. You can read more about the event at http://sqlserverlaunch.com/ . Let me throw out a dose of reality: if you are not on a TAP or otherwise going live with a private build or release candidate, you will not be installing and deploying SQL Server 2012 on March 7th. I promise. This date will not mark a release, go-live, or general availability. These launch events are marketing-centric...(read more)

Bad Habits to Kick : Using AS instead of = for column aliases

AaronBertrand — Mon, 23 Jan 2012 17:31:00 GMT

This one is quite subjective, and I'm sure I will face plenty of opposition - not only because it's a preference thing and many people are married to their preferences, but also because it violates the strict interpretation of the standard. Personally, I'm more worried about the former than the latter - I have no concerns whatsoever that SQL Server will eradicate the = notation for column aliases, nor do I worry that the code I write needs to work when ported to Oracle, DB2, MySQL, etc. (I highly...(read more)

Happy new year 新年快乐 for my Chinese readers!

Greg Low — Sun, 22 Jan 2012 23:50:00 GMT

If you have Chinese friends, it's time to say 新年快乐 to them! (Xīnnián kuàilè -> Pronounced like Shin Nien Kwai Ler) They are welcoming in the year of the dragon....(read more)

Presenting at MADPass Wednesday 25 Jan 2012!

andyleonard — Sun, 22 Jan 2012 19:00:00 GMT

I am honored to present Building Your First SSIS 2012 Package to the Madison Professional Association for SQL Server (MADPass) Wednesday, 25 Jan 2012! If you read this blog and will be in the Madison, Wisconsin area Wednesday, stop by and introduce yourself! I’m the fat guy with a fu. :{>...(read more)

Performance impact: hyperthreading on Intel Westmere-EP processors (X5690)

Linchi Shea — Sun, 22 Jan 2012 06:41:23 GMT

Recently, I have been looking into the performance impact of enabling hyperthreading on various platforms with various SQL Server workloads. All the results I have shared so far are from a DL580 G7 with four Westmere-EX (E7-4870) processors. Overall, the results of enabling hyperthreading have been positive for both the tested reporting queries and the tested OLTP queries, although I did run into one exception where a reporting query workload performed better without hyperthreading . In all...(read more)

CPU benchmarking and time for an upgrade

John Paul Cook — Sat, 21 Jan 2012 21:59:05 GMT

Is your SQL Server running slower than you’d like? Is it your SQL Server configuration or your slightly old hardware? We need to use tools to gather information. My subjective impression is that my 2.2 GHz laptop outperforms my 3.0 GHz overclocked desktop in CPU bound tasks. Both are quad core machines. The desktop has a first generation quad and the laptop has a second generation quad core processor. To introduce objectivity, PassMark’s Performance Test was used to benchmark the two machines. If...(read more)