Running sql queries from Powershell

Have you ever needed to test out the performance of sets of sql queries?

Recently we had some performance issues on a live infrastructure which needed diagnosing. Getting code released was slightly tricky so instead we concocted some scripts to allow us to run sql queries from Powershell. The advantage of these, we could run from different boxes in the farm to try and isolate where the issues arrived.

The key settings in this setup are:

The connection strings are much like your standard application connection strings.

To then run in bulk you can use the script shown below. Note, adjust the specific queries you want to execute:

Sitecore application pool crashes

Quick gotcha that caught me out. In an MVC project, previewing one page would cause the application pool to crash. After digging around (basically removing every component from the page) it turns out one of the controller renderings had no controller action set.

Easy to fix once found but took a while to track down as the logs and system event logs didn’t have many clues.

Sitecore prefetch cache

One area of Sitecore that doesn’t seem to get much attention is the Sitecore prefetch cache. Recently we’ve been investigating how to get the content editor working faster, especially with large content dbs. Under the hood Sitecore has prefetch caches for each database it uses. If you are tuning your site be sure to pay them some attention. The config files it uses live in /app_config/prefetch.

A word of caution before you begin, updating using the following approach can increase the app startup time dramatically. I’d recommend only applying within live environments as you don’t want to add minutes to app startup during dev. Another thing to be wary of is the cache sizes, during testing it wasn’t uncommon to reach 2000MB of data in the pre-fetch cache. If you tune up the capacities make sure you pay attention to other caches eg master[items] to ensure balance throughout the caching layer.

What does the prefetch cache give you out the box?

The ability to tune:

  • the size of the prefetch cache per database
  • the number of child items per children call
  • which items to cache
  • which items to load the children of
  • which items of template type to load

Depending on the setup of your Sitecore tree a combination of the above will need tuning.

Some tips

  • Speed up the CMS (master.config) media library by caching all the media folders:
  • Get a clear view of what is in all your caches with the more detailed admin page:
    https://marketplace.sitecore.net/en/Modules/Sitecore_Cache_Admin.aspx

What else can you do?
In most of our builds we setup a file which contains all the template ids we use in the app. An example would be:

This allows quick access to all the templates when asserting on specific content types. The following code maps this kind of file to the notation required by the prefetch cache configuration. When you run the page it will generate all the <template name=’… entries for the master.config.

Front end:

Back end:

How to build on this?
How about generating the config file during app startup?

Other info.
There is a detailed video from Sitecore at http://mediacontent.sitecore.net/wmv/CacheRecordingWebinar.mp4 about the caches. Alternatively read the Sitecore docs

How long does it take to open a Sitecore node?

In Sitecore builds a lot of the focus during development and performance tuning is understandably around the customer facing experience. Recently we’ve needed to investigate performance issues within the CMS itself. If you’ve ever dug into this you’ll know its a tricky application to analyse as a lot of the interactions are client-side.

One metric we wanted to gather was ‘how long does it take to open up a sitecore node’? With the help of support they suggested digging into the js file which glues all this together: ‘\sitecore\shell\applications\content manager\content editor.js’.

If you add in the following log entries the chrome console should reveal all:

and then to finish up:

and

load times

Depending on your findings you have a few options for improving performance. Be sure to check you’ve set the pre-fetch cache sizes (/app_config/prefetch/master.config) and the master db cache sizes.

Customizing the Sitecore Rich Text Editor

You have several options when customizing the Sitecore Rich Text Editor. It’s simple to setup custom sets of buttons and assign to different templates. If you are unsure on doing this I’d suggest googling ‘sitecore custom rich text editor’. This post details how to use the ‘Html Editor Configuration Type’ items.

Within the application there are various global settings which configure things like default tags to use for line breaks, which profile to use as default and a few more:

The problem we ran into was that for certain rich-text-editors we wanted the line breaks to be br tags, elsewhere p tags. This ruled out the setting above.

One hidden feature which is very useful is the ability to setup different configurations for each rte. If you look in the folder in core ‘/sitecore/system/Settings/Html Editor Profiles/Rich Text Default’ you will see an item of type ‘/sitecore/templates/System/Html Editor Profiles/Html Editor Configuration Type’. Here you can define a type, the default mapped to ‘Sitecore.Shell.Controls.RichTextEditor.EditorConfiguration,Sitecore.Client’

If you need to setup specific RTE’s to use br tags instead of p tags:

  • Setup your custom RTE profile in core
  • Create an item of type ‘/sitecore/templates/System/Html Editor Profiles/Html Editor Configuration Type’ called ‘Configuration Type’
  • Create a new class which inherits from ‘Sitecore.Shell.Controls.RichTextEditor.EditorConfiguration’
  • Update the ‘Configuration Type’ item to reference your new class
  • Override the methods you need – I found simply overriding SetupEditor was enough (see below).

An example of the code:

Sitecore parallel publishing

In version 7.2 Sitecore introduced the ability to really crank up the publishing performance of the application. There is a lot more information about how to enable Sitecore parallel publishing at http://www.sitecore.net/Learn/Blogs/Technical-Blogs/Reinnovations/Posts/2014/03/Enabling-Parallel-Publishing.aspx

During testing it was found the time it took to publish was noticeably shortened. See here for some stats.

One uncertainty was exactly what does parallel publishing do behind the scenes? What does ‘parallel’ actually mean?

  • The setting MaxDegreeOfParallelism configures the number of threads to use
  • Languages are published concurrently
  • You don’t get any more publish queues by increasing the number of threads. This means you can only run 1 publish at a time still.
  • Publishing targets are published to in sequence – e.g. if you have 2 targets: web1 & web2 and publish to both, items will appear in web1 first, once complete items will then be pushed to web2
  • Be careful cranking things up too much – you need to ensure the servers in use can cope with the load. The dbs will be under a lot more strain during the publishes
  • For any diagnostics check in the new log file generated specifically for publishing

How to prove the targets run in series?

I ran a simple experiment to prove this:

  1. Create 2 publish targets: web & web2
  2. Create folder structure:
    1. Parallel item (folder)
    2. + item for web (only allowed to publish to web)
    3. + item for web2 (only allowed to publish to web2)
  3. Added custom publish item processor with Thread.Sleep(###)
  4. Run publish with MaxDegreeOfParallelism set as 4
  5. Check web db and check web2 db via dbbrowser

The data arrived:

  • Web 2a
  • Web 2b
  • Web2 2a
  • Web2 2c

The publish logs and the UI also indicate its one target after another.

1048 08:44:27 INFO  Settings.Publishing.MaxDegreeOfParallelism:4

…

8588 08:44:45 INFO  [Publishing]: Starting to process 2 publishing options

8588 08:44:45 INFO  [PublishOptions]: root:{8960D17C-38F1-4DB0-8EBD-FD6B128D9E00}, language:en, targets:Web, database:web, mode:SingleItem, smart:False children:True, related:False

8588 08:44:45 INFO  [PublishOptions]: root:{8960D17C-38F1-4DB0-8EBD-FD6B128D9E00}, language:en, targets:Web2, database:web2, mode:SingleItem, smart:False children:True, related:False

…

7040 08:44:46 INFO  Starting [ParallelPublishing] – ProcessQueue

7040 08:44:46 INFO  Processing queue

7040 08:45:46 INFO  Processing re-try list (count=0)

7040 08:45:46 INFO  Finished [ParallelPublishing] – ProcessQueue in 60354 ms

7040 08:45:46 INFO  Publish Mode : SingleItem

7040 08:45:46 INFO  Created : 2

7040 08:45:46 INFO  Updated : 1

7040 08:45:46 INFO  Deleted : 0

7040 08:45:46 INFO  Skipped : 1

…

10136 08:45:46 INFO  Starting [ParallelPublishing] – ProcessQueue

10136 08:45:46 INFO  Processing queue

10136 08:46:46 INFO  Processing re-try list (count=0)

10136 08:46:46 INFO  Finished [ParallelPublishing] – ProcessQueue in 60101 ms

10136 08:46:46 INFO  Publish Mode : SingleItem

10136 08:46:46 INFO  Created : 2

10136 08:46:46 INFO  Updated : 1

10136 08:46:46 INFO  Deleted : 0

10136 08:46:46 INFO  Skipped : 1

Two submit buttons in Sitecore WFFM

We recently ran into an interesting challenge where the user wanted two submit buttons on their wffm form, the value of which then needed logging in the WFFM database. Note this approach does rely on javascript.

WFFM gives you the ability to setup custom fields (see Sitecore docs). I based this implementation on section 3.7.3

In your solution setup a custom ascx:

and then the code behind:

You then need to create the custom field within Sitecore. This wants to live in ‘/sitecore/system/Modules/Web Forms for Marketers/Settings/Field Types/Custom’. The only field you need to set is the UserControl field. In my example this was /forms/customformitem.ascx.

When you setup your form you can then select:
select the type

In the front end this looks like:
form

And finally, when you submit you get:
report

Sorry mr a@b.com!, you might receive a few test signups 🙂

Summarising the Sitecore log

The Sitecore log analyser is a great tool for viewing patterns in log files over time.

If getting access to log files is slow or you simply want a quick summary of certain log files then hopefully the following might help. You can select the date range, filter terms and then view all log entries which fulfil that pattern.

I’d consider it to live in the same realm as the ‘/sitecore/admin’ pages as it can reveal critical site information (hence the admin check).

The front end isn’t the most glamourous but serves its purpose:

and then the code behind:

GetItem on unpublishable Sitecore items

Consider the following code:

All pretty straight-forwards. It knowingly uses the master db as its only needed within the cms realm.

How about if the item in question in unpublishable? What then happens? You get null!!?? So, how to actually get the item?

If you wanted to expand the idea and make it a bit more reusable you could build a disposable switcher to apply the same changes.

Hopefully this helps as it had me stumped for a while!

Sitecore event handlers

One very useful area of Sitecore is the ability to subscribe to system events. Examples are things like when items are saved (item:saved), items are published etc.

All the out the box events can be seen within the <events> section of the config.

You can subscribe to events via config:

and raise your own custom events via:

One thing to note, events such as item:saved will be run when sometimes you don’t expect it. Examples are package installations.

You can guard against custom event handlers running during package installations if you check for the existence of the current httpContext:

For more info on how to make use of Sitecore events, have a look at http://sdn.sitecore.net/Articles/API/Using%20Events.aspx