I submitted this as a daily WTF but it did not get posted, so I figured I would share the story here.
Recently a client, Biller Cunts (as I like to refer to them, due to their employment base being 99% female and extremely mean/rude/etc), was complaining about slow performance in an application, that was (you guessed it) written by a friend of one of their higher ups.
After receiving this report, we of course did performance analysis of their SQL server and network. We found the server was bottlenecked about 85% of the time waiting on disk I/O. For weeks, we tried all the different configurations and optimizations we possibly could to improve disk performance to no avail. We got the server reading at an average of 60 or so MB/sec (that's megabytes, not bits), which in our opinion was very good for a RAID 5 array running from an onboard controller with 128 MB cache. We could not optimize anymore other than to recommend the client upgrade their hardware. The application was still running slow and the client was growing increasingly unhappier.
Our theory was the server was spending all of its time reading from the disk due to poorly coded software, poorly written SQL queries and bad database design. At this point, we wanted to go through the database design and make recommendations, but the client would not allow it. We informed the client of this and our theory why the performance was slow and of course the client had a theory for the issues as well (or rather, an anti-theory) - "We have no idea what is going on but at this point I am finding it hard to believe that all of our problems are related to the [application name] software", M wrote.
And so we ran SQL Server Profiler (because the client had no idea what it did, they allowed it) and watched the horror of the database queries that were unoptimized, riddled with LEFT OUTER JOINs, and constantly querying based on unindexed columns. We informed the client about the performance data we gathered from SQL Server Profiler and pointed out where the program would run the same poorly written query, that took 50 seconds to execute, not 1, not 2, but 6 or 7 times in a row, no more than a second after it had received the results from running it the previous time. But the client insisted "[application name] is not the cause of all of the problems", "we need to have a pow wow again".
At this point we looked through the database design (while the client wasn't looking), since we couldn't look through the code, and found amazing table definitions like the following:
CREATE TABLE [dbo].[Client Info](
[Id] [int] IDENTITY(1,1) NOT NULL,
...
[Jr] nvarchar NULL,
...
CONSTRAINT [aaaaaClient Info3_PK] PRIMARY KEY NONCLUSTERED (
[Id] ASC
))...
CREATE CLUSTERED INDEX [Client Info1] ON [dbo].[Client Info] (
[Jr] ASC
)
CREATE TABLE [dbo].[Unit Entries] (
[ID] [int] IDENTITY(1,1) NOT NULL,
...
[File Number] nvarchar NULL,
...
CONSTRAINT [aaaaaUnit Entries_PK] PRIMARY KEY NONCLUSTERED (
[ID] ASC
))...
CREATE CLUSTERED INDEX [Unit Entries11] ON [dbo].[Unit Entries] (
[OrgID] ASC,
[File Number] ASC
)
There were many more just like it. The next time the client asked why their network (read [application name]) was running slow, I couldn't help but respond: "It's clustered, Jr!"