Chances are if you’re experiencing one of these two lock wait types you have a very high transaction application. In our case it’s true to the extent that we have greater then 3x the amount of connection resets on our connection pool then any other application using SQL Server in the firm (on just one instance about 178k connections per minute).

In our few extremely high transaction tables we wind up running into an issue with disposing of stale data. To solve this we partition the tables by day of year (and in some cases hour of day). Every day (or hour) we swap out the partition to a blank table and truncate that swap table for a sub second data cleanup. And because of this we’ve both partitioned and sorted our cluster index by this field.

A PAGELATCH_EX or PAGELATCH_SH alert is actually not related to IO yet you’ll run into it with the scenario I explained above. What winds up happening is that while we have a pretty beefy data server, we have contention on the last page in the table only allowing us to process a few inserts at a time. This is because our clustered index is sorting on day of year ascending which creates a hot spot at the end of the table.

In order to solve this, all we had to do is force our inserts to hit multiple parts of the table at once to prevent a single page lock from slowing down the entire system. In this part of our application we were collecting metrics data from all workstations in the firm and we had a column MachineName for every row in the table. We decided to add MachineName to the clustered index which then forced each insert to each hit a unique page in our table.

From then on, you’re sitting on the speed of your disk IO. Provided you have a decent storage setup you’ll be leaning on your log volume. The rest is pretty well documented in Google, I just didn’t find anything that easy for PAGELATCH issues so I figured I would share.

I was asked this question a few months ago and liked it due to its simplicity, and how it can be done on paper which is how we’ve been administering our interview tests (this is currently not on my test).

Imagine you have a packaging system and there is a table that defines what packages a user can access. There are two columns uid (user id) and pid (package id). Every row in this table provides a specific user with access to a package, for example there may be rows [1,1] or [3,5] where as user 1 has access to package 1 or user 3 has access to package 5. A user can have access to multiple packages which would result in rows like [1,1], [1,2], [1,3], etc… However, a user will be considered a “Super User” if they have a pid of -1 assigned to their user id. Often times a user is given access to multiple packages only to later be given “Super User” access. Write a query for this table to display all user access however if a user is a “Super User”, only display the one row for them demonstrating that they are a “Super User”.

Now, more often then not people will come up with some hackish query involving “UNION” or “IN”. When they do, tell them both are horrible for performance if not for any reason other then there are multiple executions within one query. Ask them to try and rewrite it using joins and not “UNION” or “IN”. This brain teaser is simple and should demonstrate their confidence with joins and ability to think beyond “Step 1, Step2″ answers. There is no one right answer, I’ve included mine below:

So you are a web developer and the search engines just aren’t picking up your deep links. Maybe your links aren’t that deep but you want to provide more memorable URLs. Either way the solution lines in .htaccess file and mod_rewrite. Once I tried to do URL reformatting within PHP and not only was it ugly and hacky, it really didn’t work. Plus http://fiebert.com/index.php/experience just doesn’t look as sweet as http://fiebert.com/portfolio/experience.

This whole revamping of your URL structure is actually fairly easy. The first step is creating a file “.htaccess” in your root folder. From there we just decide exactly how you want to structure your URLs. This is the first part of my file:

Now to start replacing URLs like http://fiebert.com/college.php?project=perl with http://fiebert.com/portfolio/college/perl gets a little more complex. The htaccess file uses some basic regular expression (regexp) syntax, this is from the htaccess docs

Text:
.           Any single character
[chars]     Character class: One  of chars
[^chars]    Character class: None of chars
text1|text2 Alternative: text1 or text2 (ie. "or")

Quantifiers:
?           0 or 1 of the preceding text
*           0 or N of the preceding text	(hungry)
+           1 or N of the preceding text

Grouping:
(text)	Grouping of text
	(either to set the borders of an alternative or
	for making backreferences where the nth group can
	be used on the target of a RewriteRule with $n)

Anchors:
^           Start of line anchor
$           End   of line anchor

Escaping:
\char		escape that particular char
		(for instance to specify special characters.. ".[]()" etc.)

Keeping that in mind, below is how I replace http://fiebert.com/college.php?project=perl with http://fiebert.com/portfolio/college/perl

I’ve been working on an e-commerce site for a client and I really start thinking about the importance of SEO (Search Engine Optimization) and PR (Google’s Page Rank); suddenly it dawns on me how easy it is to improve your rank. Now I know I’m probably the last web developer in the world to learn and implement SEO but just in case there is someone else out there who doesn’t know I will detail for you how to improve your rank.

For Google and its infinitely complex algorithm to rank your website, it really just boils down to two things, popularity and coding standards. A seasoned veteran who makes a living off of SEO alone may curse my ignorance but really just because someone can make a living off of one thing doesn’t mean its all that complex. Hell, I use to think flying a plane was complicated until I heard all the stories about 13 year old Flight Sim geeks hijacking airplanes and flying cross country.

To break down your websites “popularity”, all you need to do is look at who links to you. The more links you have, the more popular you are. However that isn’t the end of it, because if I go and buy 100 domain names and link them all to http://www.vfork.org or http://www.codedrink.com it wont necessarily raise their page rank. See at the time of writing this my blog’s page rank is 1, and in Google speak that means I’m very unpopular. How do you get the popular kids to link to you? Easy, post stupid comments all over the internet. For example, if you goto digg, any number of the top 10 stories will be blogs with a page rank of 8, create an account on them, make a post and be sure to fill out your profile with your web address. Give Google a week and you should have a pretty solid PR (vfork.org, my sandbox, has a PR of 4 after only one night of work).

The other factor is coding standards. Google uses internet tags and CSS structure to judge what content on your website is important. H1 tags are really important so throw some keywords in there and it will do wonders. Make sure you comply with HTML and CSS coding standards, broken code can hurt your rank. Also another note is look at your URL. If it looks like index.php?blargh=crap&foo=bar then chances are you aren’t getting the PR you deserve. Use mod_rewrite and change your link structure similar to this blog. Don’t know how to do it? Give me a day or so and I’ll write that up for you too.

Oh yea, First Post!