I'm working on a particularly database intensive application at the moment. We use Entity Framework code first for our back-end database reads and writes, and have a WCF Data Services wrapper for REST read access from the UI.

Over the last few weeks as load has increased I've started noticing the occasional SQL deadlock and timeout on production. Suddenly yesterday we reached some sort of tipping point and were inundated with timeouts. The cause of most of these was found just by running SQL Profiler and watching some unnecessary queries spill down the screen (multiple functions inside a view, bad idea). Once the chaos was largely over, I started looking at whether our database indexes were actually useful and how to verify this with EF and WCF DS.

Configuring SQL Profiler for Entity Framework

These are the SQL Profiler settings I found worked best to capture both EF and WCF DS queries (with Show All Columns ticked):

SP:StmtCompleted

SP:StmtStarting

SQL:BatchCompleted

SQL:BatchStarting

This combination allows you to see CPU time and how long statements took, as well as then letting you load the trace into Database Tuning Advisor (DTA). If you want to check for timeouts and deadlocks as well, make sure you include:

Deadlock graph

Lock:Deadlock

Lock:Deadlock Chain

Lock:Timeout (timeout > 0)

Back to the Database Tuning Advisor, it specifically requires SQL:BatchCompleted and SP:StmtCompleted. The details of how to get that running is on MSDN. Set the SQL Profiler trace to output to a file (if you want to analyse on your local machine) or database table. Let it run for a few hours while the tasks you want to optimise are being performed by your users. If you're game you can run this on production for more accurate results, but a test environment is preferred provided you can recreate a typical load.

Analyzing SQL Profiler results in the Database Tuning Advisor

I found the most useful things from the DTA are the SQL script of recommendations in combination with the "Index usage report (recommended)". These will get you 90% of the way to some quick optimizations. After running the DTA, save the SQL script from the Actions / Save Recommendations menu. Then switch over to the Reports tab, choose the report just mentioned, and then right-click on the table of results and click "Export To File" (this feature is not obvious!).

Copy both files over to your local machine. Open the script in a text editor, and use Excel to open the XML report. Sort by "PercentUsage" and look at how the indexes you already have compare with the DTA recommendations (that start with _dta_index). You can easily find its recommended indexes in the SQL script and adapt them to your needs - just give them good names! Also consider dropping the indexes that don't show up at all as each database write usually causes an update of the index which can take time and cause further timeout issues.

I've worked on numerous InfoPath projects over the last two years, covering each version – 2003, 2007 and 2010. As time has gone on, I've started to develop a twitch whenever someone mentions "InfoPath". Now to be clear, the product has its place and there are times when it is appropriate for use. However there are also things to be very cautious of, even to the point where alternatives should be considered such as custom development.

I've tried to tone down this post, but there is a fair bit of frustration here that is hard to eradicate! Here's my top list of gotchas.

Business logic is hidden

The default way that InfoPath names its data structures as a user creates them looks like this: field23 or group22. The UI never encourages the user to give fields names that describe their purpose. You could argue it actively discourages it by introducing the friction of of a dialog before a field can be renamed. It would be nice if a field was in "rename mode" when a control associated with it was first dragged onto the form!

To add behaviour to a form, the user attaches "rules" to fields. This is an n rules to 1 field relationship. As it's not unusual to complete the design of a form with up to 50 or even many more fields, many of these rules make up the end result, spread throughout the form. This causes serious maintenance difficulties as the only good UI for finding and working on these rules is on a per-field basis. There is a Logic Inspector "feature" provided with InfoPath but its UI is very poorly designed and there's no way to relate rules to each other. This makes it easy to forget what a rule is there for.

Using InfoPath seems like such an easy solution. In just a few clicks you can have a very powerful form without thinking about it! And therein lies the rub. I'd argue that many people don't put in the effort to properly think through the design of a form, and wind up with unwieldy beasts of complexity. InfoPath provides no assistance in making a form self-documenting and expects you to remember what field23 is for, or Rule 5. Over time we can end up with no-one really knowing how the thing is supposed to work, and forced to unravel a web of rules and fields to find out.

The loaded gun feature - custom code

The most innocent of requirements can require custom code and sometimes aren't discovered until later down the track once a form has been largely developed and you're locked in to the InfoPath platform. The problem with code is that it requires a developer, adds another level of complexity, and changes the deployment method to SharePoint entirely (requiring involvement from the IT department's SharePoint administrator). Adding code is something that even Microsoft don't recommend:

Create InfoPath forms with as little code as possible, and put the bulk of your code into middle tier services and workflows.

If you're adding code anyway, think about why you are using InfoPath and not developing a custom solution. If your deployment target is SharePoint, newer features such as LINQ to SharePoint are very easy to implement.

Do you prefer a messy or broken form?

If you have developed a form and decide one part of it has to be deleted or replaced with something else then you have two options:

You can leave the old fields and groups unused in the underlying data structure but not show them. This means over time you'll have leftover cruft with a purpose that no-one can remember.

You can delete them and break the ability to open any older forms. Removing aspects of the data structure is a strict no-no and InfoPath cannot handle this situation.

The multiple environment nightmare

InfoPath loves to hard-code references to everything, particularly data sources, so if you want to follow a best practice process of deploying to a test environment first, you're asking for more maintenance headaches. Some of the issues are alleviated by deploying a form as a feature that can dynamically correct these references but this requires Visual Studio and locks you into this method of deployment for the life of the form.

It's also possible to hit problems with InfoPath's caching. This tries to be helpful but sometimes can be overzealous or just plain gets confused between different environments. Unfortunately it can result in your testers using the test version of a form in production before the update has been deployed!

Too many bugs

You're never gonna know when one's going to bite you and cost serious time, again and again and again. There are bugs and unexpected behaviour in the client, when deploying forms with code, and in Forms Services. Pretty embarrassing when you think you have a form "just right" and it breaks in front of your demonstration audience.

The InfoPath sales pitch

Microsoft's selling point is that InfoPath is part of Office and any savvy business user can maintain these forms. However this greatly depends on the complexity of the form and the user's expertise. Many people struggle as forms get more complicated for the reasons mentioned here (plus more), and introducing multiple environments or custom code makes it nigh impossible.

As mentioned earlier, InfoPath has its place. Make sure your reasons for using it are the right ones.

Enough!

I could go on! But hopefully this gives you some things to think about before falling into the InfoPath deep end. InfoPath has its place for simpler solutions and usually doesn't require a developer resource. Just be careful not to let it cost you dearly later. Fingers crossed that Microsoft will change some fundamental flaws the product has had for a while now in the next version.

Note: This is an old post, but valid for any solution that deploys DLLs to the Global Assembly Cache (GAC) in Windows.

Do you ever find that your solution files have been "successfully deployed" according to SharePoint, yet the updated code isn't taking effect? Perhaps you are trying to debug but breakpoints aren't being hit when normally there's no problem. Yet for some reason rebooting the server fixes the problem...

If that's the case, the reason is probably file locking, most likely to occur if you have assemblies that need to be deployed to the Global Assembly Cache (GAC). If you ever find yourself in this situation, download a copy of the excellent Process Explorer tool, and follow these steps to verify:

Start Process Explorer on the server affected.

Press Ctrl+F, or in the menu click Find, Find Handle or DLL..., or just click the binoculars icon on the toolbar:

Type the name of the assembly DLL and click Search.

Under the Handle or DLL column, look for any assemblies located in the path C:\WINDOWS\assembly\temp. These lines tell you what process is locking the assembly.

The C:\WINDOWS\assembly\temp folder is Windows' equivalent of "assembly purgatory" where the assembly will sit until Windows can deploy it. If the currently deployed assembly cannot be overwritten as it is already locked by a process then the DLL will stay there until the process releases the lock or ends, or until Windows is restarted (also ending the process).

This problem can occur when a SharePoint WSP solution containing custom code is upgraded, or even when deploying files to the GAC with Visual Studio open.

One final tip - check every server in the farm for locked files after deploying, as not all run the same processes. One may have locked the DLL you are trying to update!