Paul Randal (Blog|Twitter) recently kicked off this meme by listing the 5 things he thinks SQL Server should get rid of. Michelle Ufford (Blog|Twitter) tagged me on her response, so here is my list of 5 things I think SQL Server should get rid of.

Profiler

Yes, I went there and I honestly believe Profiler is evil. Here is why:

1. It is overused. I regularly do sweeps of my development servers looking for people using profiler so I can reach out to those people and teach them how to use DMVs and read query plans.
2. Not very many people know how to use Profiler correctly. This list includes me. I always struggle setting up traces and usually need multiple tries to get the data I am looking for.
3. An innocent mistake can cause a production outage. I get that monitoring almost any process running on a server may impact the process you are monitoring, skewing the results. The problem I have is that it is possible to kick off a trace and never have a chance to stop it without stopping and starting the SQL Server service.

If I was asked how I would make Profiler better I would offer more templates so that there is a trace set up for almost every common task. I would also add a “Danger Mode” and a “Safe Mode” to options for setting up a trace. The “Safe Mode” would be the default mode where tracing would be done asynchronously, dropping events when server performance is being impacted by more than 5-10%. “Danger Mode” would make sure that no events get dropped but would also include the risk of severe performance issues on a server.

Database Object Encryption

I do not want code running on my servers that I cannot see. Nothing is more frustrating than tracking down the stored procedure that is killing performance only to see “Encrypted Text”. My feelings on this have gotten to the point where I will recommend against buying any product that has encrypted database objects.

Maintenance Plans

I can see why people like Maintenance Plans, they provide a nice UI for managing system maintenance. The problems start when you have more than a couple of servers and want to make changes to all of them. Using Maintenance Plans it is necessary to find the plan on the server, double-click it and wait for it to open. Then, because no two maintenance plans seem to be the same, figure out how the plan works and where to make changes. I prefer instead to run the same set of SQL scripts and jobs on all of my servers. If I have to make changes to several servers I write a script and run it on all of them at the same time, confident that all servers have the same settings and will behave the same way.

The sa Account

The sa account has to go. Ideally, SQL Server logins in general would go. In my perfect world SQL Server would only support Windows logins. For situations where a user needs to be able to run an application that hits a database as themselves but not do those same things when running Access or Management Studio we should be able to sign an executable with a certificate that we could then load into SQL Server. The user-certificate combination would then show as a separate login that could be granted access to objects.

Scalar Functions

I had forgotten how much I dislike scalar functions until I saw Simon Sabin’s (Blog|Twitter) 24 Hours of Pass presentation on the subject. As Simon said, the only benefit of scalar functions is modular code. Modular code is not a benefit when it causes a performance impact so scalar functions must go. Adam Machanic (Blog|Twitter) beat me to the punch on this one but it was a good enough point that I felt safe stating it again.

Conclusion

So that does it for my 5 things. Have 5 things of your own and haven’t been tagged yet? Consider yourself tagged, just link back to this post so I know I tagged you.