SQL Server CTE Performance

I had to review a stored procedure to figure out why it was causing the web application to timeout when being passed certain parameters. The web application was using this one stored procedure to not only show a search results page (with 9 columns) but also to show the detail for a particular row (showing all 39 columns in a separate window). The stored procedure was using a CTE to handle/enable paging. Reviewing the code showed at the end of the CTE, the code had the following:

  1.  SELECT * FROM CTE

This version of the code would take up to 4 minutes to display a result based upon the parameters used.

The fix was to change

  1.  SELECT *

to

  1.  SELECT ColumnName

and this improved the performance dramatically. Where the original code took up to 4 minutes to display a result, this change had the results display instantly (well, under a second).

The lesson, only use

  1.  SELECT * FROM OBJECT

when absolutely necessary (and when is that?).

Issues Installing Windows 8 Preview in VirtualBox – With Fix

I decided to install the Windows 8 Preview as a new VirtualBox virtual machine, since I use VirtualBox as my Virtual Machine software.

I ran into some snags at first though and couldn’t get the setup started. I usually setup all of my Windows virtual machines with the following configuration:







This configuration caused the setup to fail or make my CPU usage super high. In order to get it working I had to make one change:

So in words, I had to change the storage controller from SATA/AHCI to IDE/PIIX4. This allowed me to start the setup successfully.

SQL Server 2008 R2 Service Pack 1 Released

Today SQL Server 2008 R2 Service Pack 1 is released today.
Get it here.

  • Dynamic Management Views for increased supportability.
  • ForceSeek for improved querying performance.
  • Data-tier Application Component Framework (DAC Fx) for improved database upgrades. 
  • Disk space control in PowerPivot for SharePoint.
  • Support for 512e Drives.
  • Fixed various issues.

SQL Server Encryption

Recently I had to show that data can be encrypted in SQL Server 2008 database. The purpose was to not store the raw value in a table where anyone with access could see the sensitive data. The original process truncated the destination table and then populated the destination table with data pulled from the source table. Seeing that as an issue, I made a change to the process in addition to adding encryption. Continue reading SQL Server Encryption

SQL Server 2008 (and 2008 R2) Auditing

A person with a small amount of SQL Server DBA experience came to me and asked if there was an automatic job deletes users or changes user permissions. I told them there was no default job that does that. I then suggested they look at the audits to see if there was any activity. Come to find out, there were no audits on the installations. I decided to implement some audits so the question I was originally asked could be answered by viewing the audits.

This led to explaining how to use audits. In SQL Server 2008 (and 2008 R2), hereinafter referenced as SQL 2008, auditing is so easy, there is no reason not to do it. Basic process for creating an audit is to create audit and then define the specification for the audit. Continue reading SQL Server 2008 (and 2008 R2) Auditing

Team Foundation Server 2010 Command-Line Utility

On a system I use, I needed to rollback a changeset recently checked-in in Team Foundation Server 2010 but ran into a problem. The problem was that if I went to the directory where the command-line tool TF.exe was located, I would get an error stating the following:

Unable to determine the workspace. You may be able to correct this by running 'tf workspaces /collection:TeamProjectCollectionUrl'.

Of course doing what it says didn’t fix the problem. Continue reading Team Foundation Server 2010 Command-Line Utility