For 6 months, I'd had intermittent reports of a problem with one of our bespoke apps. Every so often, the server would grind to a halt, and would stay frozen for anything up to 5 minutes. The box at the time was running SQL Server 2000 on a dual Xeon setup with 4GB of RAM.
As part of a general server refresh, and in part to alleviate this problem, I decided to shift the app over to a dedicated blade in our new HP Blade Centre. I also got sign off from the application developer to shift it over to SQL Server 2005. With that in mind, I configured the mother of all servers - Dual Quad Core Xeons, 16GB RAM, Teamed Dual 1GB LAN, two 15000RPM mirrors (for the OS and Transaction logs) and 500GB in a RAID5 array for the data files.
I installed the OS (2003 Enterprise), and SQL Server 2005 (again the Enterprise version) and transferred the data over...

For the first 3 or 4 hours, everything ran smoothly. I then started getting calls that the app was locking up, and that it was refusing new login attempts. Putting this down to some initial teething problems, I restarted the server... Everything was rosy again... For another couple of hours...

Firing up Activity monitor, one thing looked wrong instantly - RESOURCE_SEMAPHORE wait types (see below)

This wait type, as you may well know, indicates a lack of available resources (either processor time or RAM usually) to complete the query! It appeared that quadrupling the amount of RAM and the number of cores available had crippled the app!

Over the next three or four days, in conjunction with the application developer, I tried just about everything - setting the MAXDOP value to pretty much every possible value as outlined by MS here, restricting the app to fewer cores, reconfiguring AWE - you name it, I tried it!

Over the course of the week, I started collecting the wait stats - if you're familiar with SQL 2005 at all, you'll know that these indicate a major problem!

As you can see, the RESOURCE_SEMAPHORE waits appear to be generated by MS Access - the front end of the app is an MDE file that uses passthrough queries to communicate with the server. Having had major headaches with Access before, this was where I decided to focus my energy. Running some Wireshark network traces showed one particular query whose transmission seemed to coincide with a server slowdown. I won't list it in full - you can see it here if you really like! Every time it ran, a corresponding kernel CPU spike appeared on the SQL server (in red on the graph).

It appears that the query in question is fairly CPU intensive, and this, coupled with the way Access passthrough queries work, meant that huge amounts of memory and CPU time were being used to no purpose - this is shown by the huge ASYNC_NETWORK_IO wait times.

To prove this was the case, we created passthrough queries using the rogue code on a few machines...

We killed an 8 core, 16GB state-of-the-art blade with 23 connections!

The query in question ran when a specific screen was opened. Due to changes in the way resources are handled between SQL 2000 and SQL 2005, the failure threshold on 2000, even on a lower spec machine, was around 45 connections, which is why we'd seen it much less frequently.

All in all, myself and the developers identified another three queries that caused the same issue, and we managed to rewrite them. The application now handles 200 desktop users and hundreds more web connections without breaking a sweat...

It just goes to prove though that bigger is not always better!

This is a long video (nearly 2 hours!) but if you've got the time, it's worth watching...

If you're still using batch files for your login scripts and you need to install printers, probably the most flexible way of doing things is using the PrintUIEntry function of printui.dll. For example, the line below silently installs the network printer PRN00001 connected to a print server called Print1:

rundll32 printui.dll,PrintUIEntry /in /q /ga /n\\Print1\PRN00001

The following line sets it as the default printer:

rundll32 printui.dll,PrintUIEntry /y /n\\Print1\PRN00001

For more information on the printui.dll method, click here for the Microsoft guide, which also includes a complete list of switches.

We've recently been recruiting for desktop support contractors - mostly 1st and 2nd line stuff, XP only, Office 2003, and a bit of light network troubleshooting. There was a worrying lack of knowledge, even to the point of a supposed MCP giving a blank look when asked what the ping command does.

With that in mind, I wrote the following test - you can download the answer free version here, or the version with answers here.

I'm not claiming that the answers I've given are definitive, but should give an idea as to whether the person you're interviewing has at least a bit of a clue! Feel free to copy or amend it - I've been setting the pass mark as 9 questions for 2nd line, but you may wish to change that to represent your environment.

1) Download and double click Password.xla
2) If prompted, select "Enable Macros"
3) From the Tools menu in Excel, select Unprotect Sheet or Unprotect Workbook
4) Win adulation from your users