The Seattle area PASS group is organizing the SQL Saturday #26 in Redmond on October 2nd. There are many sessions to fill 3 tracks for a full day and all of them look quite interesting. The full schedule is available at http://www.sqlsaturday.com/schedule.aspx. The event is free and you get to hear presentations by such popular SQL persona as Kalen Delaney!

On the 10:15 slot yours truly will be talking about Asynchronous T-SQL processing. See you this Saturday at the new Commons MS campus in Redmond.

I just found that Microsoft has published a use case about the way MySpace is using Service Broker on their service as the core message delivery system for the Service Dispatcher. We’re talking here 440 SQL Server instances and over 1000 databases. Quote from the use case:

Service Broker has enabled MySpace to perform foreign key management across its 440 database servers, activating and deactivating accounts for its millions of users, with one-touch asynchronous efficiency. MySpace also uses Service Broker administratively to distribute new stored procedures and other updates across all 440 database servers through the Service Dispatcher infrastructure.

That is pretty impressive. I knew about the MySpace SSB adoption since the days when I was with the Service Broker team. You probably all know my mantra I repeat all the time “don’t use fire and forget, is a bad message exchange pattern and there are scenarios when the database may be taken offline”? Guess how I found out those ‘scenarios’… Anyway, I’m really glad that they also made public some performance numbers. Until now I could only quote the 5000 message per second I can push in my own test test environment. Well, looks like MySpace has some beefier hardware:

Stelzmuller: “When we went to the lab we brought our own workloads to ensure the quality of the testing. We needed to see if Service Broker could handle loads of 4,000 messages per second. Our testing found it could handle more than 18,000 messages a second.”

Some time ago I started noticing on my google searches a newcomer: stackoverflow.com. At first I dismissed this as yet another SEO hack to divert traffic to some re-syndicated content of the old user groups and forums. But I was wrong. Turns out stackoverflow.com is an enterprise backed by well known industry names like Joel Spolsky of Joel on Software fame. Apparently I’ve been living in a cave, since this new site is quite popular and even doing a Stack Overflow DevDays tour!

Now being that I’m a forums and newsgroup addict with a long history of MSDN abuse, I had to join this one and start showing off my amazing knowledge and wit. OK, you can all stop laughing now. I am a noob, so what? I still love to answer questions 😉 and not actually knowing the answer has never a stop for me. Imagination is more important than knowledge!

I spend now about 4 days on stackoverflow.com and I must say that I’m impressed. First of all, they do offer innovation in how the content is gathered and presented. The hierarchical forums model was obviously obsolete and it was showing its age: new users have a hard time figuring out which forum is the right one, monitoring the questions is difficult as the volume increases, there are often questions that obviously span multiple topics and picking the one forum for it severely restricts the exposure of the said question, and implicitly the quality of responses. Instead stackoverflow.com goes for a tags based model. When you ask a question you choose tags relevant for it and you can mix and match tags as diverse as linq, objective-c and php in one single question.

Now tags based contents isn’t exactly new, but the way is executed on stackoverflow.com takes it to a new level. Of course they offer tags based browsing of topics. But they also keep track of the tags you must often interact with (ask or answer). You can browse tags within current tags to get the questions that cover multiple tags of your interest. And the tags system is completely open, anyone can create new tags and they even have awards for successful tags.

The next innovation idea I like is how they try to blend the line between wiki and forums. Topics that prove to be popular and have a good answer can be promoted to wiki entries. This makes the entry serve the same reference role the ‘sticky’ posts serve in forums, but with better functionality. Also rooted in wikis (and craigslist too) is the idea of member provided social policing for the content: answers get voted up or down by community members. Not only that, but questions also get to be voted up or down, which is something I have not seen elsewhere. And ultimately questions can be closed, responses deleted. How is this different from the forum administrators? These people are not administrators, are just ordinary community members. You gain reputation, you earn privileges.

The reputation system is not new, by now almost any community forum has a reputation points system in place. But with stackoverflow.com they added also a system of badges that I feel comes straight from the video games world of achievements and vanity awards : you get bronze, silver or gold badges for achieving tasks in the stackoverflow.com ecosystem. You get your Teacher badge for answering a question and receiving an Up vote, you get the Student badge for answering a question that receives an up vote, or even a gold badge of Great Answer if it gets voted up 100 times. Now these are, of course, vanity awards. We all know though how efficient they are in keeping users hooked in! Me, I’m eager to get my Critic badge…

The only serious thing missing is the RSS syndication of views, but I hear is in the plans.

But most impressive is the quality of execution on these good ideas. The site is fast and responsive. It provides suggestion to similar questions as you type yours. It provides fast navigation to your questions and answers. Visual notifications for changes since your previous check. Suggestions for related topics (ie. common tags). Is true that I don’t know how many users it carries. Judging from the ~30K Teacher badges, I’d guess some 50k users registered and active, as a conservative estimate.

Is also nice to see such an effort started from a grassroots movement, and not from the political sponsorship of an industry player. Today’s developer has to deal in the course of a single day with an NSConnection question, related to an issue of Appache .htaccess mod_rewrite and PHP cookie handling and resulting in a SQL Server access problem. A site like the Social on MSDN would not happily sponsor and encourage such questions, nor would it nurture and grow the community leaders that can answer such end-to-end and cross platform questions.

I am still amazed when I walk into a development shop and I ask for their application database script and they offer to extract one for me. Really, your only definition of the database is the database itself? Now you wouldn’t keep your libraries as object code only and reverse engineer them every time you want to make a change, would you?

Now, all sarcasm aside, why is so hard to keep a database definition as source and keep it under version control? The reason is not that people are dumb, these are bright developers and they would do the right thing if it would fit into their natural work flow. The problem is that the tool set at their disposal as developers (usually the Visual Studio suite) is far far behind the capabilities of the database administration tool set (the SSMS). But the later is focused for the needs of administrators and the natural flow of actions is to visually modify some schema properties (add tables, define indexes etc) in a dialog and then click the ‘Do it!’ button. This hides actual scripts going on behind the scenes and does not lend itself naturally to the normal code/build/run/test/commit cycle of the developer desk.

Read the rest of this entry »

Sometimes your ERRORLOG is peppered with messages complaining about the service SqlQueryNotificationService-<guid> not existing or query notification dialogs being closed because they received an error message with the text Remote service has been dropped. I have blogged about this problem before: http://rusanu.com/2007/11/10/when-it-rains-it-pours/. Unfortunately this problem was not under your control as an administrator nor as a developer. It is caused by the way the SqlDependency component of ADO.Net deploys the temporary service, queue and procedure needed for its functioning. The problem could be caused by your application calling SqlDependency.Stop inadvertently but also by simple timing problems: http://rusanu.com/2008/01/04/sqldependencyonchange-callback-timing/.

Good news: Microsoft has shipped a fix for this issue: http://support.microsoft.com/kb/958006. According to the knowledge base article you need to install the following Cumulative Update depending on your current version of SQL Server deployed:

• For SQL Server 2005 SP2 you need CU 10.
• For SQL Server 2005 SP3 you need CU 1.
• For SQL Server 2008 you need CU 2.

If you have SQL Server 2008 SP1 deployed you do not need to install any fix because the issue is fixed in SP1 for 2008.

A new SQL Customer Advisory Team whitepaper was published recently: Service Broker: Performance and Scalability Techniques authored by Michael Thomassy.

The whitepaper documents the experience of a test done in Microsoft labs that measured the message throughput attainable between three initiators pushing data to a target. This scenario resembles a high scale ETL case. The test was able to obtain a rate a nearly 18000 messages per second, which is a rate that can satisfy most high load OLTP environments. To obtain this rate Michael and his team had to overcome the high contention around updates of the dialogs system tables. He presents a very interesting trick: create 149 dialogs that remain unused and only use every 150th. This way the updates done on the system tables occur on different pages and the high PAGELATCH contention on the page containing the dialog metadata during SEND is eliminated. A very clever trick indeed. But this is a typical OLTP insert/update trick and that is the very point of the whitetpaper: that typical OLTP techniques can and should be applied to Service Broker performance tuning.

Marcin Policht has concluded his series of articles dedicated to Service Broker in the Database Journal. Although the articles are part of a larger SQL Express coverage, they are not at all specific just to Express. I highly recommend them as a very good introduction to everything Service Broker on SQL Server 2005.

1. Introduction to Service Broker
2. Implementing Basic Service Broker Objects
3. Implementing Service Broker Conversation – I
4. Implementing Service Broker Conversation – II
5. Distributed Service Broker Environment – Endpoints
6. Distributed Service Broker Environment – Routing
7. Distributed Service Broker Environment – Conducting Dialogs
8. Configuring Certificate-based Authentication in SQL Server Express’ Distributed Service Broker Environment
9. Establishing Distributed SQL Server Express’ Service Broker Conversations Using Certificate-based Authentication
10. Configuring Transport Encryption in SQL Server 2005 Express Service Broker Conversation
11. Configuring Full Dialog Security in SQL Server 2005 Express Service Broker Conversation
12. Conducting Service Broker Conversation Using Full Dialog Security in SQL Server 2005 Express Service
13. Configuring Anonymous Dialog Security in SQL Server 2005 Express Service Broker Conversation
14. Service Broker Activation in SQL Server 2005 Express Edition
15. Security Context of Service Broker Internal Activation
16. Service Broker Transactional Support in SQL Server 2005 Express Edition
17. Service Broker Poison Message Handling

The problem around incorrectly measured IO times due to CPU time drift was fixed by the SQL Server 2005 SP3 release. I have talked about this issue and explained the problem in my post: http://rusanu.com/2008/10/28/event-id-833-io-requests-taking-longer-than-15-seconds.

Recently I wanted to go over again Rick Vicik papers on high performance programs on the Windows platform. These papers are a true Bible for anyone in need to write truly highly scalable and high performance server applications. They address the back end C/C++ programming and explain how to properly use the Windows threading, optimize I/O and specially the importance of data cache conscious programming, NUMA object allocations and access locality and impact of data sharing on performance. I do find however that many of the principles explained there apply just as well to C# and .Net programming. I wanted to refresh my memory on some issues so I searched for them and to my delight I found that Rick updated the papers for Vista and Windows 2008 and had posted them as a three part series on the Windows Performance blog and I wanted to share these with my blog audience:

• Designing Applications for High Performance – Part I
• Designing Applications for High Performance – Part II
• Designing Applications for High Performance – Part III

Mr. Denny has a take at the conversation reuse and recycle topic: http://itknowledgeexchange.techtarget.com/sql-server/improving-sql-service-broker-performance/. This solution allows for a more flexible policy on conversation reuse replacing the hard coded @@SPID affinity I used in my original post with an application specific settings table.