I'm currently working on a property tax consulting startup, and I was noticing that some queries were running slowly. I'm familiar with database indexing, but I wanted to see what the new Database Tuning Advisor that comes with SQL Server 2005 could come up with. It supposedly helps database newbs tune their database without having to know how to create indexes, etc. Seasoned DBAs will probably gasp in horror (with good reason) at automated tuning processes, but what the heck...let's take her for a spin.

Don't Start Without a Trace

The first thing you want to do is create a trace file containing the SQL statements you want analyzed. So fire up SQL Server Profiler, click File->New Trace, and connect to your database. In the trace properties screen you'll want to use the Tuning template, click Save to File, choose a filename, check Server processes trace data, and click Run. See the screenshot below for details.

Now you need to execute the SQL statements that are running slowly. In my case, I just run through a number of test cases in my web application, ensuring realistic database calls. But you could also run SQL statements directly from Query Analyzer or SQL Server Management Studio. You'll need to ensure that your database command timeout is set high enough so your queries finish running instead of rolling back.

Once the SQL statements have finished, stop the trace and exit SQL Server Profiler. You now have a trace file containing all the SQL statements you want tuned. It's a binary file, so don't bother reading it in Notepad, although you can open it up and view it using the Profiler.

Generate Database Tuning Recommendations

Now it's time to tune your database by starting a new Session in the Database Engine Tuning Advisor (DTA -- don't ask why the E is dropped). The DTA groups its recommendations into Sessions. Each session starts out with a Workload (a list of SQL statements to evaluate, e.g. from your trace file) and a set of databases & tables to tune. A Session can either be used to generate tuning recommendations (e.g. Indexes, Statistics, etc), or evaluate pre-specified recommendations and let you know how well they work.

So, open up the DTA -- it'll start making a new Session for you automatically. Connect to your database, select the trace file, choose the database to be tuned from the database dropdown, and put checkboxes next to the databases/tables you want to tune. I didn't bother with the Tuning Options tab, but you can take a look out of curiosity.

Now click Start Analysis at the top, and wait a bit. The Tuning Advisor will run the SQL statements from the trace file, think a bit, and then spit out some recommendations. In my case (see below) it recommended some indexes and recomputing some statistics. It also gave a guesstimate on the speed improvement.

Using the Recommendations

Now you have a set of recommendations, and you have a few choices under the Actions menu item. You can Save Recommendations, which will generate a .sql script file you can apply to your database. You can Apply Recommendations, which just means the Tuning Advisor will apply them to your database, either now or at a specific time (like 2am) in the future. You can also Evaluate Recommendations, allowing you to try them out & see how much they improve performance, without permanently affecting your database. Before Saving, Applying, or Evaluating, you can uncheck any recommendations you want to skip.

I decided to evaluate the recommendations, which creates a new Session (11:32:46 AM) displayed a screen like the one below. Note how the tabs on the top allow you to switch between various Sessions. The tab on the left (the one from 11:08:53 AM) is the first Session that generated the recommendations.

Now that we've set up a "what-if" Session, go ahead and click Start Analysis again, and a Progress screen will come up. The Tuning Advisor will apply the changes, run some comparison tests, undo the changes, and display the results. In the reports tab, you can view reports like Statement detail report (see below) to see how much faster your queries are executing. You can also see how much disk space the indexes take up, and other information.

If you're happy with the results, you can go ahead and Save or Apply the recommendations.

But let's say that you want to try some of the recommendations but not others. If so, you can go back to the original set of recommendations (click its tab, or double click it in the Session Monitor window on the left), check/uncheck some recommendations you want to try, and Evaluate Recommendations again. The ability to play with different sets of recommendations and evaluate them without permanently affecting your database is handy and powerful.

Once I had a set of recommendations whose results I liked, I applied them to my database. I then fired up my application and run through a few test cases, and it ran much faster (as I suspected).

Final Thoughts

The SQL Server Database Tuning Advisor is a nice tool. It probably won't out-tune a skilled DBA, but it does a decent job in recommending indexes. I also like the ability to try "what if" scenarios to evaluate different indexes.

I had a few beefs, however. I didn't like the default names of the recommended indexes, but there was no way to edit them in the GUI. Similarly, there's no way to use the DTA to evaluate your own recommendations -- you can only evaluate recommendations that the DTA comes up with*.

Overall, though, it was a fun tool, and easy to use.

 

* - you can actually use the DTA with custom recommendations (i.e. ones that you came up with) by editing XML files and importing them into the DTA using a command line tool. You can also edit existing DTA recommendations by exporting them to XML, editing them, and re-importing them. I think that's pretty lame that you can't use the GUI for it, but oh well.

I was dealing with an ASP.NET SqlDataSource query that kept timing out, which was annoying because I was trying to run my application through the Database Tuning Advisor (a SQL Server 2005 tool that analyzes trace logs and recommends indexes). The timeouts were preventing me from getting through my whole test case, though.

So, I found a quick solution to setting the timeout value for a SqlDataSource: add a Selecting event handler and put

protected void mySqlDataSource_Selecting(object sender, SqlDataSourceSelectingEventArgs e)
{
e.Command.Command = 300; // 5 min timeout
}

Easy peasy. :)

So I happen to love edit & continue in C# with Visual Studio 2005. There are people who say it encourages sloppy programming (which is true), but for me, there are some things I just can't code as effectively unless I know the runtime state. For example, writing complicated regular expressions against the results of various database queries and algorithms. Do I know if the regular expressions will work exactly before I run the code? No. Do I know if the code will work for all data permutations, beforehand? No.

Hence, edit & continue's blessing. Fire away, hit your asserts or whatnot, and if things look wrong, adjust on the fly. Now, I don't suggest doing big code restructuring while in edit & continue (could get messy & hard to manage), but you are able to if you need to.

Anyhow, much to my dismay I noticed that Edit & Continue stopped working after a while. I couldn't figure out what the deal was, since I was always getting "Changes are not allowed when the debugger has been attached to an already running process or the code being debugged is optimized" whenever I tried to edit the code while debugging. I also noticed that not all my variables were showing up in the Locals & Auto debug windows (this is a hint for the punch line).

Michael Freidgeim has a nice workaround if you get stuck and my simple solution doesn't help you.

His post didn't help, and it took me a few minutes to realize that my project was in "Release" mode instead of "Debug." Whooooops! I guess I didn't notice that because I was able to mostly debug, even in release mode. But I shoulda caught that. Anyhow, flipping back to Debug mode fixed the issue.

So after upgrading my XP Pro laptop to 2GB of RAM, I'm no longer able to hibernate it. Which stinks. I instead get the helpful "Insufficient System Resources Exist to Complete the API" error. And no, having lots of free disk space doesn't do anything.

Apparently it's a known issue, with an unsupported hotfix that you have to get by calling Microsoft. Thankfully, Owen Cutajar's UGH post contains a link to download the fix. Let's see if it works...

Edit: Seems to work! :)

I was looking into ways to easily back up my local SQL Server database from the command line, and copy the resulting backup to my fileserver. That way I could kick off a backup manually or from a scheduled task. There are a few articles out there but they're more specific to SQL 2000 or earlier. Here's how to do it for SQL Server 2005.

I first made directory c:\sqlbackup and added a SQL script called backup.sql:

BACKUP DATABASE streamline TO DISK = 'c:\sqlbackup\streamline.bak' WITH INIT
GO
BACKUP LOG streamline TO DISK = 'c:\sqlbackup\streamline_log.bak' WITH INIT
GO

The above commands simply create log & database backups of the "streamline" database. I used the INIT parameter so that the backup files only create one version of the database (otherwise the backup files will grow & grow, containing every version of the database, each time you run a BACKUP command).

Then I made a simple batch file called backup.bat:

@echo off 
REM get today's date and time as one big string
for /f "tokens=2-4 delims=/ " %%i in ( 'date /t') do set theday=%%k%%i%%j
for /f "tokens=1-2 delims=: " %%i in ( 'time /t') do set thetime=%%i%%j
set now=%theday%%thetime%

REM create backup files
C:
cd "\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn"
sqlcmd -S localhost -U sa -P somepassword -i c:\sqlbackup\backup.sql -o c:\sqlbackup\log.txt

REM copy backup files to backup device
xcopy "C:\sqlbackup\*.*" \\10.1.10.50\ben_backup\sqlbackup\%now%\ /E /H /R 

The first section uses some DOS batch trickery to create a variable called %now% that contains the current day and time in the form of yyyymmddhhmm. The second section runs the backup script & writes the output to c:\sqlbackup\log.txt. The last section copies the all the backup files to my fileserver, inside a date & time specific folder. Notice how I used the %now% variable in the xcopy destination parameter.

Now my backup folder contains timestamped folders containing everything I need to restore my "streamline" database. You can use a similar technique to make file backups.

The problem

When the number of computers in your house starts to grow, a file server starts looking more attractive. A place to back up all your important files, as well as store central files (e.g. virtual machines, tools, photos, mp3s) that are used by multiple machines. Sure, you can burn em to CD or DVD, but that's so slow, manual, and old school. Where exactly can you back up those files?

Option 1: Get a DAS (Direct Attached Storage)

AKA an external USB drive. USB enclosures are easy to get, and you can definitely store a lot of files on them. I have a few lying around, and you don't even need an external power brick for the 2.5" drive enclosures. I decided against them, though, since I needed to share files between multiple computers. 

Advantages:

• Cheap
• Very quick & easy to use
• High transfer speed

Disadvantages:

• Can't share files to multiple computers at the same time
• Connecting to a different client involves unplugging, lugging, & plugging.

Option 2: Build a File Server

You could also build a full-fledged file server. Get a no-frills computer, load Linux or Windows on it, stick a bunch of drives in there, and share them across the LAN. I decided against this option for the time being since I wanted something cheap, easy, small, & quiet.

Advantages:

• A file server can be used for other things, like a print server, mail server, web server, etc. Which can be fun, a source of geek pride, and educational.
• Administration is via the server GUI, which you probably already know very well. And if you don't, it helps you build your resume. "Familiar with Windows Server administration" looks better than "Familiar with ECS EZ-NAS 2000."
• Very good hardware support, including gigabit LAN
• Support for RAID (software or hardware), so you can mirror two drives in case one fails
• Advanced features like disk quotas, permissions, etc. You could also set up a WebDAV or FTP server to be able to access your files over the internet.

Disadvantages:

• Cost: running a full OS (especially Windows 2003 or a fancy Linux GUI) can require more powerful hardware, meaning more money. More powerful machines also use more power.
• Size: a normal server can be kinda bulky.
• Time: installing & configuring an OS can take a while. It's not awful, but time is money!
• Noise: a full server can be noisy, too, unless you spend more money making it quiet.

Option 3: Get a SAN (Storage Area Network)

I'm not really considering this due to the cost, although you can create a SAN using a file server (above) that implements iSCSI. There is a good (albiet a bit older) walkthrough for Linux and Windows here and of course Wikipedia has plenty of blurbage on it.

Option 4: Store Files Online

You could FTP all your important files to your ISP's server (or your server), or use your Gmail account like a hard drive, or use an online storage provider like Web 2.0 darling Box.net, collaboration tool Basecamp, or others. I use this method for sharing files with others, as offsite backup, and before travelling, but not as my main file storage.

Advantages:

• No local equipment needed. You don't need to deal with hardware at all, which is attractive.
• Cheap, even free for smaller amounts of files. Like if you're just backing up source code, documents, etc.
• Access from anywhere, even on the road. If you have a net connection, you've got your file.
• Reliable: in theory your ISP, Gmail, Basecamp, etc will have better backup & power systems than you do. So you don't need to worry (as much) about your files getting destroyed due to fire, hardware failure, etc.

Disavantages:

• Slow, oh how slow. Basically as slow as your internet connection, which is probably 99% slower than your LAN. Which rules out videos and large files.
• Not a lot of storage. This is relative, of course. 1-5gb might be plenty for some people, but not for others. Upgrading to more storage might either be expensive or not an option.
• Out of your control. If the provider goes south, your files have, too.

Option 5: Get a NAS (Network Attached Storage)

This is the option I'm trying out. A NAS is basically external storage that you connect to your LAN. Your computers then access it over the network. You can always buy an enclosure for about $100 from NewEgg, etc (search on NAS), but I recently stumbled across NASLite and FreeNAS, embedded Linux kernels that turn an old PC (e.g. 200mhz!) into a NAS. Here is a very good and recent walkthough on FreeNAS, and here is a review on NASLite (plus a tip on making a 1.7mb floppy in order to boot NASLite). I'll post an update once I have either FreeNAS or NASLite running.

Advantages:

• Inexpensive, especially if you already have an old PC lying around. If you don't, you can always buy one from the Salvation Army, Craigslist, or vendors like RetroBox.
• Quiet: Old PCs don't need a lot of fans, and can often get away with passive cooling. The NAS units you buy are also pretty quiet.
• Size: Old PCs aren't small, but the dedicated devices are. And some of them are even cute, leading to a better WAF.
• Easy: Easy enough to get going.
• Stability: A simpler device means fewer moving parts, as well as very little (if any) risk of that server getting infected with trojans and viruses. That doesn't mean that an infected client can't stick a virus-laden file onto the NAS, though, so make sure all your client PCs have antivirus (hint: Google Pack has a free 6-month Norton Antivirus).

Disadvantages:

• Transfer speed: you're limited by the speed of your network and the amount of memory in the server. If you're running gigabit LAN, then it's decent, but if you're 100mbit or 10mbit, you'll start to notice transfer times for larger files compared to USB or Firewire. You can still stream mp3s & copy files around, but streaming video might be an issue for slower networks.
• Size: Old PCs can be big.
• Features: Cheaper NAS solutions don't offer RAID, which means if you lose the drive, your files are gone. FreeNAS does offer RAID, though, which is why I'm trying it first.

Conclusion

Hopefully the above helps you when considering storage solutions for your home network and important files. Keep an eye on DevelopmentNow for an update after my adventure with FreeNAS on a celeron 600.

A few weeks back I blogged about using UltraVNC for remote support. Since then I've used it a few times to troubleshoot problems for clients in Arizona, Chicago, etc and it works great. A picture truly is worth 1000 words. No more dialogues that go like this:

Client: I'm getting an error with the system.
Developer: What error message are you getting?
Client: I don't know, it's just blank.
Developer: Which part of the screen is blank?
Client: The system is blank, like I just said.
Developer: Hmm. Ok...can you send me a screenshot?
Client: How do I do that?
[5 minutes of screenshot tutorials go by, ending with the developer getting several blank emails but no actual screenshots]
Developer: Well, let's skip the screenshot for now. Can you go to the page with the error and copy and paste it into an email for me?
Client: Ok, here goes.
[a few minutes later, a blank email arrives in the developer's inbox]
Developer: That didn't come through, can you try selecting more of the system and pasting it into an email for me?
Client: *sigh* Ok, I'll try again. Here it comes.
[a few minutes later, an email containing the system's URL arrives in the developer's inbox]
Developer: Hmm. Can you just copy and paste the entire browser window into an email?
Client: Isn't that what I just did?

...and so on, ending in a frustrated client, an exasperated developer, a broken system, and world suffering. Of course, the issue will turn out to be an empty dropdown or something, which you could have found out in 10 seconds if you could actually see the client's computer screen.

So, if you have remote clients whose desktops you need to see for troubleshooting, software installation, diagnostics, & whatnot but you're too cheap to pay for WebEx, et al. you should check out UltraVNC SingleClick. I had some instructions in my previous blog post, but let me know if you want me to put up a step-by-step tutorial on setting it up.

ASP.NET 2.0's new membership provider allows for three different ways to protect user's passwords via the passwordFormat attribute:

• Clear: passwords are stored in clear text. Fine for non-sensitive applications.
• Encrypted: passwords are encrypted. Note that you will have to put a hard-coded decryption key in the <machineKey> tag in your web.config or machine.config. Otherwise you'll get a "You must specify a non-autogenerated machine key to store passwords in the encrypted format" error when trying to create users. To create a machineKey tag with a set of random tags, you can use my machineKey generator (source code included).
• Hashed: passwords are not stored in the database at all, only an SHA-1 hash. This means passwords can not be retrieved at all -- if a user forgets their password, they'll have to request a new, randomly-generated one.

Below is an example of a <membership> tag using the Encrypted password format.

        <membership defaultProvider="MySqlMembershipProvider" >
            <providers>
                <add name="MySqlMembershipProvider"
                connectionStringName="MyLocalSQLServer"
                applicationName="MyAppName"
                                 requiresUniqueEmail="false" enablePasswordRetrieval="true"
                                 enablePasswordReset="true" requiresQuestionAndAnswer="false"
                                 passwordFormat="Encrypted"
                                 minRequiredPasswordLength="4"
                                 minRequiredNonalphanumericCharacters="0"
                type="System.Web.Security.SqlMembershipProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />
            </providers>
        </membership>

I made some updates to the machineKey generation program I mentioned in my machineKey generation post. It now returns a complete machineKey tag for ASP.NET 1.1 or 2.0 that you can copy and paste into your web.config or machine.config. You can run the generator or download the code here.

Robert Scoble, blogger-under-notice from Microsoft, posted an interesting blurb about search ranking and secret blogger cliques. He suggested sticking the word brrreeeport in your blog to prove his point about tagging on Technorati.

I was struggling for a while with how to get a profile of the queries being sent to MySQL. All my searches suggested running the debug version of MySQL, but it would either log WAY WAY too much information, or else not log the queries at all. I wanted something akin to the SQL Server Profiler, so I could just see the SQL and proc calls. Is that so much to ask? 

I eventually found the solution in the NHibernate forums (of all places), and it doesn't require running the debug version of MySQL at all. Inside your MySQL option file (e.g. "C:\Program Files\MySQL\MySQL Server 5.0\my.ini" or some other location), add the following line inside the [mysqld] section:

log="C:/Program Files/MySQL/MySQL Server 5.0/mysql.log"

Restart the MySQL service, and you'll get a nice log file continuously updated with all queries being sent to MySQL. You can view it with TextPad or some other text editor that can auto-refresh files when they change.

 

If you're getting the above error when loading your Visual Studio project, you should first ensure that the web server on your PC is running. Then confirm that .NET is installed by running "aspnet_regiis -i" in your windows\microsoft.net\framework\v1.1.4322 folder. Then open up Control Panel->Administrative Tools->Internet Information Services, and ensure the web site is running.

If you try to start the "Default Web Site" on your PC and get a strange error, make sure you don't have Skype running. It listens on port 80, preventing IIS from doing so. Which prevents your web projects from running in Visual Studio. :)