Omar AL Zabir Blog

For last two weeks we were having pretty hard time at
Pageflakes. The
database server was having 100% CPU, and 100% IO usage most
of time. It was running hot, almost about to go to a coma.
It’s a 64 bit Dual Core Dual Xeon Dell server with 2 GB RAM running
around 30 GB database on 4 SCSI drives. So, it’s more or less the
best hardware money can buy (except for RAM ofcourse). But
still the performance counter looked like this:

Pretty horrible situation isn’t it? Users are having connection
timeout on their browser. Event Log is full of “SQLConnection:
Timeout”. User’s can’t see their page. Those who can see their
pages, have serous poor performance and slow response from the
server. You can imagine the rest. Email flood, phone calls,
management screaming on your ears etc etc.

After a lot of diagnostics, we came to conclusion that SQL
Server 2005 is the culprit. So, we ran SQL Profiler on a different
server (don’t ever run SQL Profiler on the same server where you
database is running). We saw there’s an SP which is taking
thousands of read and cpu cycles.

So, we were pretty much sure that SP was the culprit. So, we
took one of the long executing ones and ran it through Execution
Plan to see what goes wrong.

So, we first did this in order to see IO usage during the SP
execution:

set statistics IO on

GO

Here’s the output after we run the SP:

Table ‘RSSItem’. Scan count 1, logical reads 40, physical reads
0, read-ahead reads 0, lob logical reads 0, lob physical reads 0,
lob read-ahead reads 0.

Nothing special. Runs pretty quick, very low read count. Looks
like no read count at all, completely cached and returned from
memory. So, SQL Server 2005 is doing pretty good caching on
it. After this, we verified whether the index was being
used properly or not:

‘Clustered Index Seek’ the best possible thing. If it was
Clustered Index Scan, then we would have thought there’s something
wrong. So, we were making best use of Index also. We were pulling
our hairs out.

So, I decided to look at the SP in order to see if I can find
out something. I stared at this for half an hour:

ALTER PROCEDURE 
[dbo].[prcRSSItemGetByChannelIDPageSize]

    @ChannelID 
int
,

    @StartIndex 
int
,

    @PageSize 
int

AS

WITH 
SelectedRSSItems(
[ID],[ChannelID],[Hash],...)

AS

(

    
SELECT

        [ID],

        [ChannelID],

        [Hash],

        [Title],

        [Guid],

        [Description],

        [EncodedContent],

        [Link],

        [PublishDate],

        [XML],

        [SavedCount],

        ROW_NUMBER() 
OVER

(
ORDER
BY 
[PublishDate]

DESC
)

AS

[RowNumber]

    
FROM

        dbo.[RSSItem]

    
WHERE

        [ChannelID] = @ChannelID 

)

SELECT 

        *

FROM

        SelectedRSSItems

WHERE

        RowNumber 
BETWEEN

(@StartIndex
+ 1) 
AND

(@StartIndex
+ @PageSize)

ORDER BY 
PublishDate

desc

Those who are wondering what’s with the “WITH” block. This is
called Common Table Expression (CTE). This is a new feature in SQL
Server 2005 which is the best so far for paging. Previously we had
to page rows by doing any of the following:

1. Copy primary key in a temporary table after sorting and
   use IDENTITY column to generate row number. Then select rows by
   joining primary key from temporary table to the actual table. The
   paging is done based on row number on the temporary table. Very
   expensive. You end up creating temporary table on every call to the
   SP. Too many IO on tempdb.
2. Use subquery. Very complicated SQL. Not so good result.
3. Use cursor to skip rows and select only those which fall within
   the page. It was the best solution so far.

Now you have the mighty ROW_NUMBER() function which we all have
been waiting for 10 years!

Back to our disaster, you see the SQL is nice and clean, nothing
suspicious. But SQL Server 2005 is drowning taking us and a
thousands of users all over the world with it.

Finally, it struck my mind! SQL Server is doing something on the
PublishDate field because it needs to sort the rows based on
it.

ROW_NUMBER()

OVER
(
ORDER BY
[PublishDate]
DESC
)
AS
[RowNumber]

What if I make a new Non-Clustered index on ChannelID and
PublishDate? The query filters using ChannelID and sorts using
Publishdate. So, it should get everything it needs from the
index.

I created a brand new index on ChannelID and PublishDate. It
took 12 mins to create the index. Then the CPU usage looked like
this:

We are saved! We are back in business!

So, what we learn from this experience? We learn nothing.

This blog post is dedicated to my friend Shahed who wrote the
above SP two weeks ago and gave us a lot of fun all these
days.

Today I am trying to use Windows Live Writer Beta. Previously I
have tried using One Note 2007, MS Word 2007, InfoPath template.
None of these were good enough. Although MS Word 2007 is really
good for writing and quick publishing, but it generates bloated
html, makes you page heavy and also codes look horrible. None of
these have the feature to upload pictures with post. So, you have
to go to the blog website, upload pictures and then edit the post
using the web editor and insert pictures in it. If you do all these
from the web, then the desktop blogging experience does not improve
at all.

Now Windows Live Writer seems to have solved all these problems.
Let’s try publishing a picture:

It’s a pretty interesting picture. For last one week, our
servers running Pageflakes
were almost dying with super high CPU and IO usage. Here you see a
64bit Dual Core Dual Xeon DELL server is at its knees. We finally
resolved the problem last night. It was a near death experience for
us. I will soon write about it. I tried a long time to put some
picture. Did not find any. So, just testing with this.

Let’s try the Google Map embedding. This is my country and Dhaka
is the city where I live.

Let’s try some codes from Visual Studio:

    [WebMethod]

    [WebOperationAttribute(true, ResponseFormatMode.Json, false)]

    public int GetPageVersionNo(int pageId)

    {

        using (TheFacade facade = new TheFacade())

        {

            return facade.ThePageflakes.GetPageVersionNo(pageId, Profile.UserName);

        }

    }

OK, here’s a catch. You cannot paste html to this editor.
You need to switch to Html View in order to paste the html.
But this is much better than going to web editor and doing
it.

So, far it really felt the best editing experiencing. Going to
publish it now. Hope it works…

• If you don’t need unicode support (multiple
  language) in fields, use the non-unicode types. For ex, use
  varchar instead of nvarchar. All the text data types that start
  with “n” support unicode and thus take 2 bytes per character.
  That’s double the size of the non-“n” counter parts. Normally we
  need unicode only for fields which somehow gets into the UI e.g.
  First Name, Title, Description etc. But fields like “FileName”,
  “Path”, “Url” etc do not need unicode support at all (when your
  server is in some English speaking country, not in Japan, China
  etc). So, you can use varchar in these cases and save 50%
  space.
• Try to avoid using text data types as primary
  key. Searching over a text data type is lot more expensive than
  searching on integer data types. I have seen tables with primary
  key on “Url”, “FileName”, “Full Name” type fields. Think about what
  happens when you make a text field primary key. Database Engine has
  to match characters in the field data whenever you do queries like
  “WHERE FileName=’something'”. It has to match “all” the characters
  in order to find a complete match. That’s way more expensive than
  just doing “WHERE ID=10” which only takes one 32bit comparison
  which the processor can do billions of times per second. You can
  easily get around using text fields as primary key by adding an
  integer “ID” column which is an auto number and using that ID
  column on hyperlinks and page navigations. For example, instead of
  using hyperlinks like
  www.pageflakes.com/downloadfile.aspx?filename=TestFile.html
  you can do it as www.pageflakes.com/downloadfile.aspx?id=10
• Fixed length text data types are much faster to
  compare and store than variable length data types. Whenever you use
  fixed length data types like char(255), database engine allocates
  fixed 255 bytes in the row and it knows there’s no way it’s going
  to increase. So, it need not maintain a ‘length’ indicator, nor
  does it need to increase/decrease row size whenever the content
  changes. This results in less fragmentation. Less fragmentation
  means less hard drive usage and less moving around in different
  parts of the hard drive. So, use fixed length data types whenever
  possible and especially to those fields which appear in WHERE
  clause. But beware that when field length fields are compared, you
  need to account for the trailing spaces. If you are comparing WHERE
  FirstName = ‘Omar’ and first name field is defined as char(15) then
  you actually need to do WHERE FirstName = ‘Omar’ + (11
  spaces).
• Do not use expression on the left side of the
  WHERE clause. For example, you could do the above like this: WHERE
  Rtrim(FirstName) = ‘Omar’. But that makes the database engine to
  trim every single first name it runs through. Similarly, if you do
  WHERE Age+10 > 20 it has to do the sum for every row it runs
  through. The idea is to use the expression on the right side so
  that database engine can run that expression once and then compare
  the resultant with the field data on the left. So, do this: WHERE
  Age > 10

• You MUST NOT run a query which has fields in
  WHERE clause but those fields are not part of any index. As soon as
  you run a query which has some field in the WHERE cause not part of
  any index, database engine has to run through ENTIRE table to find
  a match. That’s when CPU and Hard drive usage goes sky
  high.
• Look at your WHERE clause fields. If you have
  AND, then make an index which contains all the fields in ANDs. For
  ex, WHERE A=1 AND B=2 AND C=3, then make an index based on A, B, C
  and it must be in this exact order.
• If you have multiple queries on the same table
  and each has different fields in WHERE clause, create different
  indexes. For ex, if you have a query which does WHERE A=1 and then
  there’s another query which does WHERE B=1, create two separate
  index. One with field A and one with field B.
• The above applied for clauses which has OR. For
  example, WHERE A=1 OR B=2

• Find number of posts made by each
  user
• Order them in descending order. Highest number of
  posts become Rank 1.
• Get the top 10 rows.

1. First write an email in Text
   Format.
2. Save the email. It will go
   to “Drafts” folder. Now drag that email to the folder which
   contains all the emails you want to reply to.
3. Now press ALT+F11 and paste
   the macro at the end of the post.
4. Hit F5 and wait for a long
   time. You will see, Outbox has all the reply
   emails.

Sub ReplytoALLEmail()

    Dim objFolder As Folder



    Dim objTemplate As MailItem

    Set objTemplate =
Application.ActiveExplorer.Selection.Item(1)



    Set objFolder = objTemplate.Parent



    Dim objItem As MailItem

    Dim objReply As MailItem



    Dim dic As New Dictionary



    Dim strEmail As String



    Dim strEmails As String



    Dim strBody As String

    strBody = objTemplate.Body



    For Each objItem In objFolder.Items

        If Not (objTemplate = objItem) Then



            strEmail = objItem.SenderEmailAddress



            If Not dic.Exists(strEmail) Then

                strEmails = strEmails + strEmail + ";"



                dic.Add strEmail, ""



                Set objReply = objItem.Reply()



                objReply.Body = strBody + vbCrLf + vbCrLf +
objItem.Body



                On Local Error Resume Next

                objReply.Send

            End If

        End If



    Next



    Debug.Print strEmails

End Sub

When you have a page full of lots of HTML, it will be best if
the whole page can be cached on the browser. You can do this using
HTTP Response caching header either by injecting them manually or
by using @OutputCache tag directive on ASPX pages.

But if part of the page is dynamic and part of the page is
static (like header, left side menu, foother, right side menu,
bottom part) etc where static parts of the page occupy a
significant amount of html, then if you could cache those parts on
the browser, you could save a lot of bytes that gets downloaded
every time the page downloads. On most of the websites, you will
find the header, navigation menu, footer, bottom part,
advertisements are mostly static and thus easily cacheable. If the
whole page size is say 50KB, at least 20KB is static and 30KB might
be dynamic. If you can use client side caching of page fragments
(not ASP.NET’s server side page output cache), you can save
40% download time easily.

ASP.NET offers you page fragment caching using @Outputcache
which is good, but that caching is on server side. It cache the
output of user controls and serves them from server side cache. You
cannot eliminate the download of those costly bytes. It just saves
some CPU process. Nothing much for users in it.

The only way to cache part of page is by allowing browser to
download those parts separately and making those parts cacheable
just like images/CSS/javascripts get cached. So, we need to
download page fragments separately and make them cached on the
browser’s cache. IFRAME is an easy way to do this but IFRAME
makes the page heavy and thus not follow CSS of the page. There are
many reasons why IFRAME can’t work. So, we have a better way,
we will use Javascript to render content of the page and
javascript will get cached on the browser’s cache.

So, here’s the idea:

• We will split the whole page into multiple parts
• We will generate page content using Javascript. Each cacheable
  part comes from javascript and javascript renders the HTML of
  it.
• The parts which are cachable gets cached by the browser and
  thus never downloaded again (until you want them to be). But those
  parts which are non-cachable and highly dynamic, does not get
  cached by browser.

So, let’s think of a page setup like this:

Here only one part is dynamic and the rest is fully cacheable.
So, the Default.aspx which renders this whole page looks like
this:

<%@PageLanguage="VB"AutoEventWireup="false"%>

<%@OutputCacheNoStore="true"Location="None"%>

DOCTYPEhtmlPUBLIC"-//W3C//DTDXHTML 1.0 Transitional//EN""http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<htmlxmlns="http://www.w3.org/1999/xhtml">

<headrunat="server">

    <title>MyBig Fat Pagetitle>

head>

<body>

<formid="form1"runat="server">

<tablewidth="100%"border="1">   

<tr>

<td>Somelogo heretd>

<td><scriptid="Script1"src="Header.aspx"type="text/javascript">script>td>

tr>       

<tr>

<td><scriptid="LeftMenu"src="LeftMenu.aspx"type="text/javascript">script>td>

<tdbgcolor="lightgrey"><div>

This is the dynamic part which gets changed on every load. Checkout the time when

it was generated: <%=DateTime.Now %>div>td>

tr>       

<tr>

<tdcolspan="2"><scriptid="Footer"src="Footer.aspx"type="text/javascript">script>td>

tr>

table>

form>

body>

html>

The page looks like this:

You see, the cached parts are 30 mins older. Browser has not
downloaded those parts at all and thus saved a significant amount
of data transfer. The only part that was downloaded was the dynamic
part.

When you load the page first time, all 4 files are downloaded.
But the last 3 files get cached and never downloaded until
browser’s cache expires. So, on second visit, only one file
downloaded and thus saves a significant amount of data
transfer.

Let’s look at one of the files Header.aspx which gets
cached. Nothing fancy here, it’s a regular ASPX page:

The interesting thing here is the “ContentType”
which I have set to “text/html/javascript”. This is not
something built-in, I have introduced this type.

When you put an ASPX inside a Script tag, it surely does not
work because < script
id
=”Script1″
src
=”Header.aspx”
type
=”text/javascript”>
expects javascript output, not html output. If html output is
provided, browsers simply ignores it. So, we need to convert the
output of Header.aspx into Javascript which when downloaded and
executed by the browser, emits the original html that was generated
when ASP.NET executed the page.

We use HTTP Module to intercept all .aspx calls and when the
page is about to be written to the output, we check if the content
type is “text/html/javascript”. If it is, this is our
cue to convert the page output to javascript representation.

If you want to know details about HTTP Module and how to use
Response Filter to modify page output, please read this wonderful
article:

http://www.aspnetresources.com/articles/HttpFilters.aspx

It really explains all the things. I would recommend you read
this article first and then continue with the rest.

We have made a response filter named Html2JSPageFilter.js
(available in the code download), which overrides the Write method
of Stream and converts the entire HTML of the page to javascript
representation:

    publicoverridevoidWrite(byte[]buffer, intoffset, intcount)

    {

        stringstrBuffer = System.Text.UTF8Encoding.UTF8.GetString(buffer, offset, count);

        //---------------------------------

        //Wait for the closing  tag

        //---------------------------------

        Regexeof = newRegex("",RegexOptions.IgnoreCase);

        if(!eof.IsMatch (strBuffer))

        {

            responseHtml.Append (strBuffer);

        }

        else

        {

            responseHtml.Append (strBuffer);

            string finalHtml = responseHtml.ToString ();

            //extract only the content inside the form tag tag ASP.NET generatesin all .aspx

            intformTagStart = finalHtml.IndexOf(");

            intformTagStartEnd = finalHtml.IndexOf('>',formTagStart);

            intformTagEnd = finalHtml.LastIndexOf("");

            stringpageContentInsideFormTag = finalHtml.Substring(formTagStartEnd + 1,formTagEnd - formTagStartEnd - 1);

First we get the entire page output, then we get only what is
inside the

            //Remove the __VIEWSTATE tag because page fragments don't needviewstate

            //Note this will make all ASP.NET controls in the page fragments gomad which 

            //needs viewstate to do their work.

            Regexre = newRegex("()",RegexOptions.IgnoreCase);

            pageContentInsideFormTag =re.Replace(pageContentInsideFormTag, string.Empty);

            ///Convert the HTML to javascript string representation

            stringjavascript2Html = 

                pageContentInsideFormTag.Replace("r","")

                .Replace("n","")

                .Replace("   ","")

                .Replace(" ","")

                .Replace("  ","")

                .Replace("","")

                .Replace("'","'");

            //Generate the document.write('...') which adds the content in thedocument

            stringpageOutput = "document.write('"+ javascript2Html + "');";

    voidIHttpModule.Init(HttpApplicationcontext)

    {

        context.ReleaseRequestState += newEventHandler(InstallResponseFilter);

    }

    privatevoidInstallResponseFilter(objectsender, EventArgse) 

    {

     HttpResponseresponse = HttpContext.Current.Response;

     if(response.ContentType == "text/html/javascript")

     {

         response.ContentType = "text/javascript";

         response.Filter = newHtml2JSPageFilter(response.Filter);

     }

    }

        <httpModules>

            <addname="Html2JSModule"type="Html2JavascriptModule"/>

        httpModules>

IIS 6 has builtin gzip compression ability which can compress
output of dynamic webpages (.aspx) and webservices (.asmx). The
compression is really good and can easily reduce 60% download time.
You should always turn this feature on in your production server.
The CPU usage is not that high compared to the reduction of
download time for users. Your users will love the significant
download time reduction when you turn it on.

Now, on internet you will find a lot of solutions. I tried all
of them which appears in first 30 Google search results. But failed
to make any of them work properly. Finally I was able to make it
work, but I realized you have to do it in a very specific way and
in specific order. Here it goes:

• Go to IIS Manager from Administrative Tools
• Right click on your computer name (not on websites or Default
  Web Site)
• Choose All Tasks-> Restart IIS
• From the drop down, choose “Stop IIS” and click OK.
• IIS is not stopped. Make sure it’s not still running
• Now go to C:WINDOWSSYSTEM32INETSRV
• Make a copy of the file Metabase.xml. This is a dangerous
  file, don’t play around with it. Make sure you have a backup before
  you do what I am going to tell you now.
• Open the metabase.xml in Notepad. Don’t use any other editor
  besides Notepad, Notepad2 or Visual Studio.
• Find using “IIsCompressionScheme”
• You will find a match which looks like this:
  “<IIsCompressionScheme Location=”/LM/W3SVC/Filters/Compression/deflate”
  There are two nodes named IIsCompressionScheme and one node with plural IIsCompressionSchemes.
• Delete these nodes.
• Once you have deleted the 3 nodes, paste the text from this
  link in in their position:
  http://tinypaste.com/630fc

Go to www.pipeboost.com
and enter the URL to ensure you are getting compressed content.
Before you do so, make sure you have visited your site for a while
in your local browser so that the pages got the chance to get
themselves compressed.

Say you have written a message which has lots of users in the
recipient list. Now you don’t want to send the mail to all those
users who can see each others email address. You want to send the
message one by one. Here’s what you do:

• After composing the message save it to make it go to Drafts
  folder.
• Select the message
• Press ALT+F11 to bring the Visual Basic Editor.
• Now paste the following code:

Sub SendIndividually()

Dim objItem As MailItem
Set objItem = Application.ActiveExplorer.Selection.Item(1)

Dim objTo As Recipient
Dim objClonedMail As MailItem

On Local Error Resume Next

For Each objTo In objItem.Recipients

Set objClonedMail = objItem.Copy()
objClonedMail.To = “”
objClonedMail.Recipients.Add objTo
objClonedMail.Send

Next

End Sub

• Hit F5 and wait for a while. You will see Outbox is full of
  copy of the message with individual recipient in the “To”

Read my previous post on how to generate a message with all the
sender’s email address from all the message in a folder. You can
combine both approach to send everyone important notice.

Sometimes you want to send some important notice to everyone who
has ever mailed you. Let’s say you have a folder named “Friends” in
Outlook where you store all the emails from your friends. Now you
want to get all of their email addresses. Pretty difficult work if
you have thousands of such mails. Here’s an easy way.

• Select the folder in Outlook and press ALT+F11. It will open
  Visual Basic Editor.
• Double click on ThisOutlookSession from the Project tree.
• Paste the following function:

Sub GetALLEmailAddresses()

Dim objFolder As Folder
Set objFolder = Application.ActiveExplorer.Selection

Dim dic As New Dictionary
Dim strEmail As String
Dim strEmails As String

Dim objItem As MailItem
For Each objItem In objFolder.Items

strEmail = objItem.SenderEmailAddress
If Not dic.Exists(strEmail) Then
strEmails = strEmails + strEmail + “;”
dic.Add strEmail, “”
End If

Next

Debug.Print strEmails
End Sub

Hit F5 and it will run for a while. Then press Ctrl+G. You will
see the email addresses in the “Immediate
Window”.

Copy the whole string and you have all the email addresses from
all the emails in the selected Outlook folder. There will be no
duplicate address in the list.