Howdy everyone,

   So first off, I should probably say that my posts might be a bit more far and few between (not that they are all that often as it stands :). A long time co-worker, Kartik, will probably be manning the ship on the blog as I have decided to expand my skills a bit and work on SQL Server Analysis Services. Figured I had been helping people build the processes which actually executed their business logic, now I wanted a bit more experience with the tools they use to analyze what exactly they have been doing at their business. This blog, though, will remain a BizTalk blog since it was never meant to be "my" blog ... just look at the name. Okay, enough of that. :)

 As a "parting gift", I managed to convince the team to let me add a small enhancement to persistence as long as I did not push changes to the UI. Ostensibly, this was really meant for PSS, but personally, I would rather have everyone else fix / figure out their problems without having to call PSS. So don't tell them I told you, but here you go :)

As part of default dehydration now, the orchestration engine also persists, in the clear, the name of the shape on which it was blocked. I have seen many customers which turn on shape level tracking for orchestration just so that if things get "hung up" and lots of orchestrations end up in the dehydrated state, they can open the orchestration in the debugger view and see what it has executed and what it is waiting on. Using orchestration debugger for this is just a tremendous overkill and adds overhead to the processing and now you have to manage the potential buildup of this information in the tracking db. All just so you can know what an orchestration is blocked on. With R2, you have the potential to figure this out yourself. :)

Unfortunately, we did not get this into the UI. Would have been really cool to view it in the MMC and dehydrated orchestrations would have the string of the blocking shape. But we gave you a start and now if we ever did add the UI tools, we wouldn't have to change to underlying db schema or any of the engine. So here are the details.

In the Instances table in the messagebox there is a new column called nvcLastAction. This column contains a guid for dehydrated instances. Sorry, all I had access to in the engine was a guid. However, in the DTA database you will find a table called dta_ServiceSymbols which contains some xml for each service which is deployed, regardless of whether you have any tracking on. This xml is used to translate the guids into string names for some of our tools. It is relatively easy to look at this and build a little tool which will map the guid to the actual shape name. Sorry I have not written the tool ... Remember about being very careful when looking directly in the messagebox. Be careful of locking tables and causing all sorts of blocking issues. Use NOLOCK hints and never, ever, ever update / delete anything. If you are at all unsure about rules around this, please see http://blogs.msdn.com/biztalk_core_engine/archive/2004/09/20/231974.aspx which is specific to 2004 and mostly obsolete for 2006 with the new enhancements to the MMC which makes these direct queries unnecessary, but the rules for quering are still valid. Be very, very, very careful. If you change things or cause problems by introducing blocking, your chances of support are pretty much nill.

 Couple of caveats. We did not do extensive testing on this for parallels where you might be blocking on more than one shape. It could technically pick either shape with no gaurantees. Also, we are simply depending on the orchestration engine to provide this information as we hook in to grab it in the same fashion as the tracking interceptors do.

So there you go. Have some fun with it. Perhaps if you ask nicely, you could convince someone to put this in for 2006 SP1. You have to ask nicely, though. :)

Hope everyone is having a great day.

Lee

This question has come up a lot in internal and external discussions. It got to the point where we had to put out a KB article as so many DBAs out there thought that it was okay to go and "tweak" the settings (http://support.microsoft.com/kb/912262/en-us). So here is an attempt on my part to explain what you can and can't do in relatively simple terms and why.

What can't you do?

You may not make any changes which could effect how our queries are executed (ie their execution plan). I will list some specific things which can affect the query plans, but they are just examples. If you know of a setting which I don't list and you aren't sure, then don't change it. Here are a couple of things to understand around this:

1) Think of the sql code (stored procedures) which we ship as uncompiled C# (or whatever your favorite language is) code. Every customer who installs our product gets their own compiled version of this code courtesy of the SQL Server engine. You can read up on documentation on how the sql engine performs this compilation (or go to one of Paul Randall's talks at Tech Ed or some other conference) but it is important to realize that it is being done. SQL even has a performance counter which you can monitor called recompiles which will show you how often "code" is being recompiled potentially while your applicatin is running at full throttle. SQL Server uses various statistics about the table layout and cardinality of indexes and such to attempt to chose the best plan to reduce IO and provide the best performance. One thing that the optimizer cannot anticipate is the locking side-effects certain plan choices could have. It is therefore possible that in an environment in which lots of threads are accessing the database at very high rates, a plan which might be optimized for potential IO access could cause tremendous locking and hence be a much worse overall plan choice.

2) The next very, very important thing to understand is that while the database is installed on your server, it is not "your" database. It is our (or if you catch me on the wrong day, not thinking, "my") database. When you have performance problems with it, the buck stops over here, not at your DBAs desk. For that reason, it is unbelievably important that the system runs the same on every installation. If every customer who called in with a question was forced to gather a dump of the plan cache from within SQL Server and send it to us so that we could reconstruct exactly what sql server is doing, it would make support pretty much impossible.

3) Access to the messagebox is very controlled and very limited. Via our Admin MMC (in bts 2006) you can construct somewhat flexible queries against the data, but even that is somewhat limited. From the runtime, there are a set of precanned generic stored procedures and some per-host templated stored procedures which provide all the access that the runtime needs. Since we have locked down access to the database, we can understand all of the potential queries. With that in mind, we have done everything we possibly can to "hardcode" the query plans for all of our stored procedures. This means using index hints, force order hints, occassionally join hints, norecompile hints (why recompile if you are just going to generate the same plan) and every trick we can come up with the tie the hands of the optimizer (part of the sql engine which calculates the best query plan). The sql server team is well aware of what we are doing. Technically in SQL 2005 there is support for USE PLAN where we could really hardcode the plan completely, but (in order of importance): (a) we support SQL 2000, (b) it is only supported on SELECT statements which only ~30% of our code and (c) it is non-trivial for generated stored procedures like the ones we use for per host access. Along with attempting to force the optimizer to chose the plan we want, we also disable some of the features of SQL Server which "help" the optimizer chose the best plan. This includes statistics generation and updating. Statistics provide information about the layout of a table so that the optimizer might decide it is more efficient to scan the clustered index than to seek over the non-clustered index (as an example). Since we are hardcoding the plan, statistics are not so important. We also disable parallelism (setting at the server level MAX DOP to 1). Parallelism provides more options for the optimizer to chose amongst for plans. Typically it is very useful for queries over very large databases (warehouse type applications). We are a OLTP (online transaction processing) style app which executes the same sets of sprocs on multiple threads at very high rates. We already provide our own parallelism. I "apologize" that we chose to set this at the server level which could cause it to effect any other database you have installed on that server, but you may not change this setting or else we will simply push back on any support until you switch it back (no matter how large a customer you are). If you have only one server, install an alernate instance of sql server on the same machine and put us on the separate instance. In our testing, when SQL Server choses a plan with parallelism in it for one of our queries it can cause orders of magnitude performance degradataion.

4) We are not perfect (but we try :). Occassionally we do not include enough hints so that the optimizer can chose another plan which is worse. I have personally fixed two bugs in which we needed to add hints or slightly change the structure of our query because the optimizer chose a less than optimal plan. It can happen, but we do our darned'ist and I have only had I think 3 cases of it in about 3 years which is not bad. It also not fair to blame this on the optimizer. As I said above, since we are hardcoding the plans for all of our queries, we turn off a lot of the features which might help the optimizer (but add overhead to the overall system).

A quick list of things which can effect the query plans:

Statistics (don't enable these)

Parallelism (don't turn this on )

table structure (don't add indexes, columns, triggers, ... If you do you will hear silence when you call for help)

Stored procedures (don't change them. You can look all you want, but no touching)

So what can you do?

You can change the underlying storage model for our data on the physical disks. By this I mean stuff like file groups and files and such. By default all of our tables are setup on a single (PRIMARY) filegroup which has one file. This was admittedly perhaps not the best decision. You are more than welcome to create multiple files for a given file group and also to create multiple filegroups and move our tables around. This can give potentially significantly better IO performance and hence overall system performance. We are currently working on a paper (and perhaps tool to automate) which will give recommendations on how to layout out tables given as many phsical disks as you have access to. You can also change setings related to how sql pre-allocates space for the files and for some of their internal structures. None of this effects how our plans are executed, it simply allows for faster IO access and better overall performance. One interesting settings which we do not enable by default but have been playing with for a little while now is "TEXT IN ROW". This table level option is used to tell sql server whether to store the image column data in the same data page as the clustered index or in another data page. By default this is set to off. If you think about an application like Outlook / Exchange this makes a lot of sense, as the image data (you email message) is potentially only loaded when the user clicks on a specific email to read it (disregarding preview pane). Storing the image data in a separate page allows more rows to be stored in one page and hence less IO is needed to read a large set of data. However, in BizTalk, when a message is delivered to a subscribing service, that message is going to be "processed" and hence loaded every time (we do special handling for large messages to fragment them already). So instead of an optimization, we incur an extra IO to lookup the image data in the separate data page. I don't gaurantee it will always provide a performance benefit, but we have seen some decent gains when processing small messages. As always you should test this out in a QA environment first. You can read more about text in row simply by searching for "SQL SERVER text in row" and picking one of the links. Text in row is enabled at the table level, so you need to have some understanding of our table structures. Really, there are only two tables we need to worry about. The first table is the Spool. This table contains for every message, its MessageID, a timestamp, some other properites, and the blob form of the message context. The second table you need to worry about is called the parts table. This table contains both the part property bag (for things like content type and charset) as well as the first fragment for each part. Enabling text in row on these tables could provide a benefit for high throughput systems which are processing smaller messages. Since the size of a page can only be 8K, if your messages are greater than this, enabling text in row probably won't help since sql will be forced to push the data to a separate page anyways (although we do compress your data so it is smaller in storage than you might think). You can also play around with the max size for the image column before it is offloaded to a separate page (http://msdn2.microsoft.com/en-us/library/ms173530.aspx). As always, have someone with database experience read over this and understand it before testing anything out. A quick script to turn on the option would look like:

exec sp_tableoption N'Spool', 'text in row', 'ON'

exec sp_tableoption N'Parts', 'text in row', 'ON‘

So the conclusion is that you should never do anything which effects the way our queries are executed but you are more than welcome to try and optimize the IO access to improve performance. Hope this helps some of you out there.

Lee

Addendum: 

I was asked about Index Rebuilds. First, we do not support online index defragmentation. That process involves page locks which we explicitly disable as part of our installation on all of our tables. Page locks make lock acquisition ordering almost impossible and will cause us to deadlock all over the place. We are very carefull about how we acquire locks and expect that the granularity is at the row level so that our ordering is enforced. Having said that, offline rebuilding of indexes is perfectly okay and supported. In our labs, we have not seen significant performance gains from doing this but it is true that our indexes will fragment since many of them are on guids (most). SQL Server team is not a huge fan of guid based indexing, but we have done numerous tests which show that as long as you don't ever scan the table, they can perform better than identity based indexes (for our specific workloads). They will of course get fragmented, but usually our tables don't get too big as data flows in and out of them at a relatively steady pace. If you have large amounts of data which is expected to build up in the messagebox, you are more than welcome to periodically do index rebuilds during scheduled downtime. The same applies to the tracking database where you can rebuild indexes during downtime.

So it has been a while. In the mean time, WE SHPPED!!!! It is such a great feeling. I have been on this team for almost 7 years now and this release is defnitely the best (one would of course hope that every release is better than the previous but you never know :) :). I am super happy to have this out there and hope you are all going to be really happy with it and we are already working on really cool stuff for the next release. There is rarely a break from inovating. :) :) However, I did manage to take two weeks recently and go to New Zealand which is an absolutely amazing country. You Kiwis really know what is up. Even if a town has only 5 buildings (one of which will always sell fish and chips and the other will have postcards of sheep), you can always find an information bldg which will tell you everything to do, book it for you and send you on your way. It is the best country for tourists. I went surfing, caving, kayaking, glacier hiking, tramping, river surfing, bungy jumping and lots more. It was awesome. I am also apparently now a Hurricanes fan cause the Crusaders win too much and the Blues, well, they are the blues. :) :)  I think I should spend more time with customers in New Zealand ... we'll see.

My role on the team has changed a bit and now I have a team to help work on all the great features. Sumitra S has been working with me for a while now and I am going to work on getting her to post to this blog as appropriately. She rocks and along with a bunch of work on the messagebox core functionality she wrote the (internal) OM which exposes all of the data and operations in the Admin MMC Group Hub Page. Also joining us now is Adrian Hamza after working on the Share Point adapter most recently He has his own blog which you should all checkout at http://blogs.msdn.com/ahamza. He is great and we are really looking forward to his work on the core engine. Craig C has also joined the team and will be helping define some of the long term direction we are going towards. Should make an awesome team. :) :) :)

So now maybe I will give you some good technicall details so that you are not just reading fluff. :) One thing I have really wanting to post are some learnings I have had with using Yukon and 2 tips that could really help some people avoid the gotchas that we hit when porting code directly that must run on both Yukon and Shiloh (I'd post more but it is 1 am).

1) After a number of conversations with the optimizer team, we discovered a hidden gem in the use of WITH SCHEMABINDING on UDFs. Our UDF simply took in three datetime variables and did a somewhat complex comparison of them and returned 1 or 0. It did not touch any tables and so did not affect any data. In Yukon there is a new feature / attribute associated with a UDF called SystemDataAccess. This property can be seen via:

SELECT OBJECTPROPERTYEX(OBJECT_id(‘<MyFunction>’), 'SYSTEMDATAACCESS')

Without the WITH SCHEMABINDING option, this property is always set to 1 so the optimizer assumes that you might access the data and will add sort / spool to your plan to protect itself from data changes. Adding the schemabinding hint, causes the function to be parsed when it is created and then you can see this property set to 0 and the sort / spool disappears. It is really quite nice and can be a very large perf improvement which I believe will be KB’d at some point. In our case, this is a major gain in the performance of dequeue for large queue sizes. Thanks to the SysRepublic guys for discovering the perf issue with large queues and triggering this discovery. You will find that our peformance for large queues is better on Yukon in some scenarios because of this .

2) Always owner qualify tables and sprocs / udfs which you execute. SQL plan cache access actually uses this as part of its lookup in the cache and missing this can cause a certain amount of extra cache misses and plan (re)compiles resulting in higher CPU utilization and potentially lower performance. What I mean is instead of "SELECT * FROM Foo" you would write "SELECT * FROM dbo.Foo" assuming the owner of Foo is the dbo. Unfortunately this is something we found too late and it actually didn't make it into our RTM bits as it was not a functional bug and was such an extensive change that we could not risk our RTM date since we had reached our perf goals. We are pushing to QFE this, though, and I will let you know if / when this happens. All it means is that in certain scenarios, on Yukon, we would use more CPU than on Shiloh.

These two things are just so wierd that I figured I would post about them as case one could be a big gain for those who do this (which might not be a lot of you) and case two is probably all of us and these are fixes that can do which have no effect on Shiloh but can have large gains on Yukon. Hope this helps some of you and I expect the Yukon guys will KB this stuff at some point (as well as investigating why #2 is currently worse than Shiloh).

Next time, I will post something cool about BizTalk, but now I have a coulpe more things to catch up on before I go to sleep. :)

Thx

Lee

Since I have seen enough questions on this, I will officially post here about it (and we might have a KB Article on it ... not sure how that works for BETAs though). There is a new sql agent job you will find on the messagebox database server called ManageMessageRefCountLog_<messagebox>. In the BETA 2 bits you will find this job fails occassionally on both SQL 2000 and 2005. This is a known issue which we are working to fix in RTM. The failure is ignorable. The error stems from this job attempting to start a secondary sql agent job. If that job is already running we get an error. The approach we were using to determine if the job was already running had a race in it, but we did not get to fix this for BETA2. The job is scheduled to restart itself every minute so even if it fails, it will just restart again. This job is part of our cleanup process for deleting messages. The expectation is that this job will run forever. The only negative side effect the failure of this job can have is if you are running in VERY high performance testing, a one minute down time of this job could cause enough spool growth to trigger our throttling mechanism. In this case, you would see throttling kick in due to spool growth and you could see this. We are of course working to fix this for RTM for this reason and for the fact that customers don't seem to like the red X that shows up when it fails. :) :) If you don't know how to monitor throttling counters, I recommend reading through our documentation on this and then checking out the MessageAgent perf counters which are now accessible via perfmon. Significant enhancements have been made here to help maintain a healthy system so you should read up on it.

Thx

Lee

Have you ever wanted to speak to Microsoft developers of a specific feature of BizTalk Server? I am sure your answer was “Yes let me at them”, so the Business Process Integration Division is extending an invitation to all customers to join our key feature developers, program managers, and testers in the following newsgroups:

We’ve been working very hard over the past year to connect with folks just like you and want to include you in our community of Most Valuable Professionals (MVP), developers, information technology professionals, chief information officers, chief executive officers, or any other role within large, medium, and small companies that hang out in our online newsgroup communities. We want to have you join in this vibrant online community to ask those questions you always wanted to ask but did not know where to go. Well, now you know where to go, we want you to come on in and join us!

If you are new to BizTalk Server, try out the NewUser newsgroup, Microsoft.public.biztalk.newuser.

We’re offering two levels of interaction with Microsoft Corporation employees as follows:

1. Managed Newsgroup Support
2. Unmanaged Newsgroup Support

Managed Newsgroup Support

MSDN managed newsgroups are available in English to MSDN Universal, Enterprise, Professional and Operating Systems subscribers to receive free technical support on select Microsoft technologies as well as to share ideas with other subscribers. MSDN managed newsgroups provide:

• Unlimited on-line technical support - keep your PSS incidents
• A commitment to respond to your post within two business days
• Over 200 newsgroups to choose from
• Spam protection for your e-mail address when posting items

Go to the following URL to sign up: http://msdn.microsoft.com/newsgroups/managed .  These newsgroups are monitored by Microsoft support engineers and product group team member as described above.

Unmanaged Newsgroup Support

MSDN unmanaged newsgroups are available to all individuals.

Go to the following URL to participate: http://msdn.microsoft.com/newsgroups. These newsgroups are monitored by Microsoft product group members, other customers like you, most valuable professionals, and various other individuals.

Questions, suggestions, and direct feedback can be sent to me.

James Fort

BPI Community Lead

mailto:jfort@microsoft.com 

Okay, peridiodically the marketing team / other teams ask me to post things to make sure the word is getting out about events. My guess is that all of you are reading scott woodgate's blog (see my links) and if you are not you should, so these will probably be duplicates, but please read them and in this case do the feedback so we can have a strong tech ed session this year (I will be there, so please stop by my talk and we can chat :). Also, I think I have wrangled the owner of the orchestration compiler and now one of the key developers on the engine also to put some info on the blog. That would be great stuff. He is the ultimate source of information related to compiler questions and how to express certain design patterns in your orchestration. Looking forward to hearing from Paul. Okay, the Tech-Ed announcements:

Industry leaders will conduct special in-depth workshops on Sunday, June 5, from 1 P.M. to 6 P.M. These concentrated “Deep Dive” sessions will last five hours and provide an in-depth look at important technology areas and solutions. 

The pre-con will start off with an overview session led by Rebecca Dias that gives an overview of SO, Microsoft solutions in the SO space and how they work together. It is then followed by a session by Yasser Shohoud on how to build web service on .Net. The session is followed by a webservices lab. This is then followed by a HIS session by Paul Larsen that shows how to extend the reach of SOA to mainframe via HIS. Following Paul’s session, the earlier web services lab is extended with HIS to expose legacy IBM systems as a web service. Mike Wood’s session picks up from there and shows cases how to orchestrate, expose and integrate with web services. Mike’s talk is followed by another hands-on-lab session where we extend the web services / HIS lab with orchestration in the middle orchestrating these services and maybe followed by exposing the resulting orchestration again as a web service.

Title: Building Simple and Complex Web Services using the .Net Framework, BizTalk Server and Host Integration Server

Are you convinced that Service Orientation is the way to go, but don’t yet have the technical skills to begin implementation or are confused by the plethora of choices Microsoft offers you to build Service Oriented systems? Do you want fundamentals on Microsoft’s integration technologies and guidance on trade offs if you use one solution vs. the other for different applicable scenarios? If so, this is a training you won’t want to miss. This training will provide you the guidance and information you need to make an informed decision on how to best construct your integration solution using Microsoft technologies that will ensure your integration solution is extensible, maintainable and inter-operable. Our experts will give you highlights of each technology by demonstrating how you can construct loosely-coupled, composite, and secure systems that bridge corporate firewalls based on core .NET Framework technologies, expose line of business applications in your IBM datacenters as web services using Host Integration Server, and orchestrate the activities of disparate web services into a composite application using Microsoft’s Business Process Management solution BizTalk Server. To solidify your learning, each session is followed by a lab that gives you hands on experience on the technologies just covered. These labs builds upon one another, so by the end of the day you will have the experience of building a real world Service Oriented solution using all the concepts and technologies you just learned. 

I am pretty sure they want you to register for the class and I think the tech ed site is at www.msteched.com

Thanks

Lee

Just want to let everyone know that it is out and it is goodness. Woodgate has posted links to it as well as some information on where to download the .Net Framework GDR on which we have a dependency just in case you miss it in our docs. Check out his links for more information.

Info on .Net GDR

Download SP1

Thx

Lee