Using Performance Dashboard to Increase Performance on SQL Server 2005

There is a script that runs on one of our servers that scrapes the output of a log page and emails the result to a set of admins.  Lately, however, the log databases have grown so large that some of the SQL queries run by this page were timing out.

Luckily, I found out about SQL Server Performance Dashboard Reports, a set of reports you can install to find out, among other things, where all the CPU is going, which queries are the most resource consuming, and how to add indexes to increase performance.

Installation

To get started, simply download the installer from the link above and install it on the machine running SQL Server.  My default installation directory looked like this:

C:\Program Files\Microsoft SQL Server\90\Tools\PerformanceDashboard

Inside this directory are all the files needed to run the reports, a setup script, and a helpful CHM.  In addition to running the installer, a system administrator needs to run the setup.sql script located in this directory on every instance of SQL Server that you want profile.

Accessing Custom Reports

Then, to access the report in SQL Server Management Studio, right click on your server instance, select "Reports" then "Custom Reports" and open the performance_dashboard_main.rdl report in the installation directory.

Browse Expensive Queries and Create Missing Indexes

You can now browse the most expensive queries by CPU, Duration, Logical Reads/Writes, Physical Reads, or CLR Time.  Under "Miscellaneous Information", I found a link for "Missing Indexes".  This brought up a list of 4 indexes that it recommended that I create to improve database performance.  Suffice it to say that after creating the suggested indexes, CPU time was reduced an order of magnitude for the script.

For more information, I recommend this article:

http://www.sql-server-performance.com/articles/per/bm_performance_dashboard_2005_p2.aspx

Copy Text to Clipboard in Ubuntu from the Command Line

I'm writing a small Perl script that reads in some files and does some calculations and spits the text to STDOUT. All I ever do with this text is copy it to my clipboard and paste it in an Excel spreadsheet.  But I wondered...is there some way I can copy the text to my clipboard without having to select exactly what I want and hitting Ctrl-Shift-C?  The answer is surprisingly easy!

The following was done on Ubuntu:

$ sudo apt-get install xsel
$ echo -n "Put me in the clipboard." | xsel -ib
$
[hit Shift-Insert]
$ Put me in the clipboard.

Voila! The -i switch tells xsel to work in input mode (as opposed to append or follow mode) and the -b switch tells it to work with the clipboard.  Check out the man page for more cool stuff you can do with it.