Im Zuge einer Neueinrichtung einer Merge-Replikation auf einem Produktivsystem hatten wir auf der Abonnenten-Seite mit massiven Performance-Problemen zu kämpfen. Diese traten gefühlsmäßig immer dann auf, wenn die Replikation lief. Die CPU-Auslastung war sehr gering, aber die Datenträgerwarteschleife schoss teilweise auf 250!
Augenscheinlich war aber alles korrekt eingerichtet.

Um dem Problem auf die Schliche zu kommen, erwies sich die Systemtabelle dm_exec_query_stats als äußerst hilfreich. Hierüber kann man sich Ausführungsstatistiken der letzten Befehle abholen. Unter anderem werden hier CPU-Zeit, Lese- und Schreibzugriffe protokolliert.

SELECT TOP 5 
    last_physical_reads,  
    sql_handle,  
    plan_handle 
FROM sys.dm_exec_query_stats   
ORDER BY last_physical_reads DESC

In den Spalten sql_handle und plan_handle werden zwei Handle zurückgeliefert über die man Zugriff auf das Kommando im Klartext sowie den Execution Plan bekommt.

Diese kann man sich mit den Systemfunktionen sys.dm_exec_sql_text und sys.dm_exec_text_query_plan zurückgeben lassen.
Ich führte also folgendes Statement aus

SELECT * FROM sys.dm_exec_sql_text(0x020000006D3E963947E734508EEE5BDFDA222AE4C2FE9432)

und bekam wie vermutet ein Replikationskommando zurück

SELECT TOP 100 mc.tablenick, mc.rowguid, mc.generation, mc.lineage, mc.colv1, t.*
FROM  [Shop].[dbo].[MSmerge_contents] mc, [Shop].[dbo].[OrderItemCondition] t WHERE
mc.generation = 45940 AND mc.tablenick = 26251010
AND mc.rowguid = t.rowguidcol
ORDER BY mc.tablenick, mc.rowguid

Ich führte das Kommando mit der Option “Include Actual Execution Plan” aus und bekam die Meldung, dass ein Index fehlt.

CREATE NONCLUSTERED INDEX [<Name of Missing INDEX, sysname,>]
ON [dbo].[OrderItemCondition] ([rowguid])

Tatsächlich fehlte ein Index auf der rowguid-Spalte, nach Anlegen dieses Index verringerte sich die Aktualisierungszeit der Replikation von rund 7 Minuten auf eine Minute. Die Datenträgerwarteschleife verhält sich seitdem auch ruhig und die Performanceeinbrüche existieren nicht mehr.
Normalerweise werden solche wichtigen Indizes von den Replikationstools automatisch angelegt. Warum das in diesem Fall nicht passiert ist kann ich nicht sagen, ich bin aber froh, das Problem gefunden und gelöst zu haben.

If you want to add a menu item to the ObjectExplorer’s context menu, the proceeding is quite simple.
Create a class that inherits from ToolsMenuItemBase

public class MenuItem : ToolsMenuItemBase
{
    public MenuItem()
    {
         this.Text = "New menu item";
    }
 
    protected override void Invoke()
    {
        // do something
    }
 
    public override object Clone()
    {
        return new MenuItem();
    }
}

and attach it to the ObjectExplorer’s menu

IObjectExplorerService objectExplorer = ServiceCache.GetObjectExplorer();
objectExplorer.GetSelectedNodes(out nodeCount, out nodes);
INodeInformation node = (nodeCount > 0 ? nodes[0] : null);
 
if (_tableMenu == null)
{
    _tableMenu = (HierarchyObject)node.GetService(typeof(IMenuHandler));
 
    MenuItem item = new MenuItem();
    _tableMenu.AddChild(string.Empty, item);
}

What if you now intend to create a menu item that again contains a sub menu? ToolsMenuItemBase provides a method called AddChild. Using this method should be the obvious way to create sub menus.
But any call like item.AddChild(string.Empty, new SubMenuItem()) simply does nothing.

Using Reflector, I found a solution to achieve the desired behaviour.
The trick is to implement an interface called IWinformsMenuHandler. You will need to create a method GetMenuItems. It is here where you can create a new menu hierarchy.

The new class looks like this:

public class MenuItem : ToolsMenuItemBase, IWinformsMenuHandler
{
	public MenuItem()
	{
	}
 
	protected override void Invoke()
	{
	}
 
	public override object Clone()
	{
		return new MenuItem();
	}
 
	public System.Windows.Forms.ToolStripItem[] GetMenuItems()
	{
		ToolStripMenuItem item = new ToolStripMenuItem("Menu Item");
 
		ToolStripMenuItem subItem = new ToolStripMenuItem("Sub item");
		subItem.Click += new EventHandler(SubItem_Click);
 
		item.DropDownItems.Add(subItem);
		item.DropDownItems.Add(new ToolStripSeparator());
		item.DropDownItems.Add(new ToolStripMenuItem("Sub item2"));		
 
		return new ToolStripItem[] { item };
	}
}

When implementing IWinformsMenuHandler setting the properties of ToolsMenuItemBase will be useless. E.g. this.Text = "Menu item" will be relpaced by ToolStripMenuItem item = new ToolStripMenuItem("Menu Item");. Also Invoke() will never be called, but the event handler of your ToolStripMenuItem.

I am glad to introduce a new addin for SSMS 2008 you might find useful.
The addin attaches a new function to the ObjectExplorer’s context menu.
You can easily generate INSERT statements for all data of a selected table.

Find the setup and sourcecode on my codeplex project CodePlex: DataScripter

If you missed the Storage node in the Object Explorer of your SSMS installation to manage your SQL Express fulltext catalogs, here is the solution!
Find below my new addin for both SSMS 2005 and SSMS 2008.

Once installed the addin, you’ll find a new context menu entry when you select a database:

A click on the menu entry opens the management dialog:

Find the setup and sourcecode on my CodePlex project http://www.codeplex.com/FulltextManager.

There are few good articles if you are interested in developing addins for SQL Server Management Studio 2005.

Basically the concepts are similar to writing an addin for Visual Studio. I linked two sites that deal with addins for SSMS 2005.
http://aspalliance.com/1374_Extend_Functionality_in_SQL_Server_2005_Management_Studio_with_Addins.all
http://jcooney.net/archive/2007/11/26/55358.aspx

Even when SQL Server Management Studio 2008 CTP had been released, the development concepts for addins remained unchanged to those in SSMS 2005.

Surprisingly the final release of SSMS 2008 brought some significant changes to the addin interface. However, there is no need to panic if you have written an addin for SSMS 2005 and you now want to run it in SSMS 2008. Although the changes Microsoft made will break your addin, the work needed to reanimate it is kept to a minimum:

If you try running your SSMS 2005 addin in SSMS 2008 for the first time it will fail with a typecast exception.
Let’s take a look at the OnConnect-Method in your Connect class, where you typically stored a reference to the DTE2-Application object.

public class Connect : IDTExtensibility2
{
        private DTE2 _applicationObject;
        private AddIn _addInInstance;
 
        public void OnConnection(object application, ext_ConnectMode connectMode, object addInInst, ref Array custom)
        {
                _applicationObject = (DTE2)application;
                _addInInstance = (AddIn)addInInst;
 
                // do something with the application, e.g. reference the Commands
                Commands2 commands = (Commands2)_applicationObject.Commands
        }
}

As you run this code in SSMS 2008, the typecast exception will occur in Line 8.
So what now? We need the Application reference to create toolbar items or windows. The application-object we get in SSMS 2008 is simply void, there are no properties, no methods, no implemented interface: just empty?!?!

This is a problem, but will not break our necks! One more time Reflector saves our lives: Using Reflector I figured that ServiceCache now has a new static property called ExtensibilityModel.
And this property provides all the functionality we formerly knew from the application object.

Now to convert your addin from SSMS 2005 to SSMS 2008, simply change the application-object calls to ServiceCache.ExtensibilityModel and it will work:

public class Connect : IDTExtensibility2, IDTCommandTarget
{
        private AddIn _addInInstance = null;
 
        public void OnConnection(object application, ext_ConnectMode connectMode, object addInInst, ref Array custom)
        {
                _addInInstance = (AddIn)addInInst;
 
                // do something with the commands
                Commands2 commands = (Commands2)ServiceCache.ExtensibilityModel.Commands;
        }
}

Although Microsoft changed the addin model to make our lives harder, we will still be able to develop useful addins for SQL Server Management Studio 2008.

Find some nice addins on my codeplex project SSMSAddins: http://www.codeplex.com/SSMSAddins.