Watch your Fill Factor

I was asked to troubleshoot an issue today with a 3rd party web application that was throwing some SQL Timeout exceptions while attempting to insert data into a database.

With the exception of long running queries, most timeout errors are caused by a few issues:

• Runaway transaction logs
• High fill factors
• Missing clustered indexes

While I could probably write entire articles on each of these, I'm going to concentrate on the fill factors.

First, it's important to know that SQL Server itself does not have any timeouts; rather timeouts are implemented by the data provider (the .NET SQL providers) or the client (query analyzer, etc).

So what exactly is the fill factor? The fill factor is a parameter you set on indexes (both clustered and non-clustered); it controls the percentge of free space to leave on each page to leave room for future growth. Lower fill factors leads to less page splits. So why is this important? Well, page splits are expensive - they take a lot of CPU and disk resources to complete. So if you have a busy DB (lots of inserts into a table), and a high fill factor, then SQL server will have to constantly split pages to accomodate the growth, which might take longer then your timeout, resulting in a timeout error!

According to MSDN documentation (http://msdn2.microsoft.com/en-us/library/aa933139(SQL.80).aspx):

"Fill factor value is a percentage from 0 to 100 that specifies how much to fill the data pages after the index is created. A value of 100 means the pages will be full and will take the least amount of storage space. This setting should be used only when there will be no changes to the data, for example, on a read-only table. A lower value leaves more empty space on the data pages, which reduces the need to split data pages as indexes grow but requires more storage space. This setting is more appropriate when there will be changes to the data in the table."

In my particular case, I was able to resolve the timeout issue by changing the fill factor from 90% to 40%. Why 40% you might ask? The customer did not have overly large amounts of storage and decreasing the fill factor increases storage requirements - I could have set this to 0 but I didn't want to have an out-of-space condition to deal with, so 40% seemed reasonable to me.

Setting the fill factor via script is relatively easy and involves dropping the constraint and re-adding it with the new fill factor.

ALTER TABLE dbo.MyTableName
DROP CONSTRAINT PK_MyTableName
GO
ALTER TABLE dbo.MyTableName ADD CONSTRAINT
PK_MyTableName PRIMARY KEY CLUSTERED (
MyTableNameID
) WITH FILLFACTOR = 40 ON [PRIMARY]

Just something to watch out for when designing your DB's!

Read More

JavaScript Gotcha: Check for undefined before null

Do you see anything wrong with this line?

if (a !== null typeof(a) !== 'undefined') {
// Do something with a
}

... Well neither did I - but I've had users complaining of an application just giving them a "white screen" - typical of a JavaScript error to abort everything else. The second time I looked at it I could see what the problem was - I was checking for "a !== null" but a did not yet exist! The correct way to do this is

if(typeof(a) !== 'undefined' && a !== null) {
// Do something with a
}

Note to self: Always check undefined before null!

Read More

JavaScript Hashtable based on MS AJAX

I was looking to implement a JavaScript hashtable and after find a number of near identical implementations, I decicded to modify one that I found at http://alexrazon.blogspot.com/2006/11/javascript-hashtable-implementation.html to be based on the MS Ajax Patern.

Anyways, here it is:

Mbccs.Psp.Web.Manager.Hashtable = function() {
 this._hash = new Array();
 this._keys = new Array();
 this._getEnum = new Array();
 this._count = 0;
}

Mbccs.Psp.Web.Manager.Hashtable.prototype = {
 getAt : function(index) {
  return this._hash[this._getEnum[index]];
 },
 
 get : function (key) {
  return this._hash[key];
 },

 remove : function (key) {
  for (var i = this._keys.length - 1; i >= 0; i--) {
   if (this._keys[i] == key) {
    this._keys.splice(i, 1);
    this._getEnum.splice(i, 1);
    this._hash[key] = null;

    this._count = this._keys.length;
   }
  }
 },

 put : function (key, value) {
  if (value == null)
   return null;
  if (this._hash[key] == null) {
   this._keys[this._keys.length] = key;
   this._count = this._keys.length;
   this._getEnum[this._count - 1] = key;
  }

  this._hash[key] = value;
 }
}

Mbccs.Psp.Web.Manager.Hashtable.registerClass('Mbccs.Psp.Web.Manager.Hashtable');

if(typeof(Sys) !== 'undefined')
 Sys.Application.notifyScriptLoaded();

Read More

Office 2003 SP3 Released

Microsoft seemingly quietly released Office 2003 SP3 - if you're not already running Office 2007, you can download the service pack at http://www.microsoft.com/downloads/details.aspx?familyid=e25b7049-3e13-433b-b9d2-5e3c1132f206&displaylang=en

Read More

Why does iTunes setup need to close Outlook?!

Everytime I update iTunes I remember why I left it so long - the install process is quite annoying! Can someone please explain to me why it's necessary to close Microsoft Outlook and Internet Explorer while installing iTunes?

It drives me even more crazy when I see Apple advertisements poking at certain annoyances in Windows yet Apple is usually the worst offender - Apple clearly does not understand the Windows environment, even though most of their software users are Windows users.

Now only if I could understand why it creates shortcuts everywhere meanwhile it only has 2 checkboxes to disable a few of them. grr!

Read More

Persisting the scroll position of child DIV's using MS AJAX

• Download source and demo project - 29.5 Kb

Introduction

While the ScriptManager and UpdatePanel found in Microsoft AJAX do a good job of persisting your pages scroll position during partial post back operations, you might be surprised to find out the same is not for scrollable child DIV's contained within an UpdatePanel.

The PersistentScrollPosition control presented in this article seeks to remedy this issue using a client-side behavior and ASP.NET server control implemented using Microsoft AJAX.

Background

While it is certainly not my intention to review the internals of the UpdatePanel and PageRequestManager or implementing any of the client-side components (Sys.Component, Sys.UI,Behavior, Sys.UI.Control), a quick understanding can go a long way into understanding and resolving this particular problem. There are two key items to keep in mind for this control:

1. Client-side components are disposed of and recreated during the partial post back lifecycle so you can't use the controls instance to store any data you need to survive this.
   

2. The HTML output of the UpdatePanel is completely replaced during a partial post back (assuming it was triggered) through the innerHTML property, which is why the scroll position problem exists in the first place.

Using the code

For those who just want the solution, using the code is straight forward. The control has one property you need to set named ControlToPersist. This is a string which takes the ID of the server-side container DIV (it must have runat="server").

<asp:UpdatePanel runat="server" ID="UpdatePanel" UpdateMode="always">
<ContentTemplate>
<asp:Button runat="server" ID="btnPostBack" Text="Post Back" OnClick="btnPostBack_Click" />
<br />
<div style="width:590px;height:400px;overflow-y:scroll;overflow-x:hidden;" runat="server" id="persistMe">
<p>
Lorem ipsum dolor sit amet, consectetuer adipiscing elit...</p>

</div>
<mbc:PersistentScrollPosition runat="server" ID="psf1" ControlToPersist="persistMe" />
</ContentTemplate>
</asp:UpdatePanel>

Building the Control

The control consists of two parts, both of which for the most part are very "cookie cutter". On the server side, we inhert from Control and implement IScriptControl and INamingContainer and create a HiddenField during initialization to store our scroll position in between partial post backs.

public class PersistentScrollPosition : Control, IScriptControl, INamingContainer
{
protected override void OnInit(EventArgs e)
        {
            base.OnInit(e);

            // Create hidden control for storage
            storage = new HiddenField();
            storage.ID = "storage";        
            Controls.Add(storage);        
        }}

When creating the script descriptors for the client-side initiation, we pass through the scrollable DIV's ClientID as the controls ElementID and we pass in a reference to the HiddenField's DOM element using the AddElementProprety method of the ScriptComponentDescriptor class.

public IEnumerable<scriptdescriptor /> GetScriptDescriptors()
        {
            ScriptComponentDescriptor scd =
new ScriptBehaviorDescriptor("Mbccs.WebControls.PersistentScrollPosition", Control.ClientID);        
            scd.AddElementProperty("storage", storage.ClientID);
            yield return scd;
        }

On the client-side, the control inherits from the Sys.UI.Behavior base class. Upon control intialization, it hooks into two events:
The scroll DOM event of the DIV, and the EndRequest event of the Sys.WebForms.PageRequestManager class. The EndRequest event is when the scroll state is restored, but I'll get to that shortly.

    initialize : function() {
        Mbccs.WebControls.PersistentScrollPosition.callBaseMethod(this, 'initialize');
    
        this._scrollDelegate = Function.createDelegate(this, this._onScroll);
        this._endRequestDelegate = Function.createDelegate(this, this._onEndRequest);
    
        var prm = Sys.WebForms.PageRequestManager.getInstance();
        prm.add_endRequest(this._endRequestDelegate);
    
        $addHandler(this.get_element(), 'scroll', this._scrollDelegate);
    
    }

When the DIV is scrolled, the x,y scroll position is serialized and stored in the HiddenField server-side control we created earlier.

_onScroll : function(e) {            
        this._storage.value =
Sys.Serialization.JavaScriptSerializer.serialize(this._getScrollPosition());
    },

_getScrollPosition : function() {
        var el = this.get_element();
    
        if (el) {
            return {
                x: el.scrollLeft  0,
                y: el.scrollTop  0
            };
        }
    }

To prevent null's from floating around, the x,y coordinates are coerced into 0's if either the scrollLeft or scrollTop properties are null.

The EndRequest event is fired when "an asyncronous postback is finished and control has been returned to the browser," (http://asp.net/AJAX/Documentation/Live/ClientReference/Sys.WebForms/PageRequestManagerClass/default.aspx) so it's a perfect time to restore our scroll state.

_onEndRequest : function(sender, args) {        
        var o = null;    
        if(this._storage.value !== '')
            o = Sys.Serialization.JavaScriptSerializer.deserialize(this._storage.value);
        
        if (o) {
            var el = this.get_element();
            el.scrollLeft = o.x;
            el.scrollTop = o.y;        
            this._storage.value = '';
        }
    }

The dispose method isn't usually a place of any particular interest, but its worthy of noting that it you need to unhook the DIV's scroll event prior to calling dispose on the base class.

dispose : function() {            
        $removeHandler(this.get_element(), 'scroll', this._scrollDelegate);
    
        Mbccs.WebControls.PersistentScrollPosition.callBaseMethod(this, 'dispose');            
    
        var prm = Sys.WebForms.PageRequestManager.getInstance();
        prm.remove_endRequest(this._endRequestDelegate);            
    
        delete this._endRequestDelegate;
        delete this._scrollDelegate;                            
    }

Read More

Strongly typed URL's for ASP.NET

I came across an interesting article today on The Code Project titled Strongly typed URL's for ASP.NET by Brian Chavez which is a Visual Studio add-in that creates strongly typed classes for your site's file structure and navigation.

It's actually a neat concept - it creates a static class with strongly typed members that map to the actual files - if you ever change a filename, instead of worrying about updating it across the site, just use the generated class and you won't have any problems!

The only thing that I can see getting annoying is having to continually update the generated class - I suppose you could add this to your build process, but when first building a site, it might get tedious as you are constantly adding new files to the project structure.

In any event, it's worth a look!

Read More