Wizards are mythical creatures. In the case of the SQL Server wizards, the myth is that a SQL Server database can be designed and administered by someone with very little knowledge or experience.
Years ago, the marketing department at Microsoft decided to sell SQL Server as an “administration-free” database. A key element in this strategy was the creation of dozens, maybe over a hundred, wizards that step the user through common DBA tasks by point and click rather than existing methods that require more knowledge and experience.
Many of the SQL Server wizards are useful in the hands of a qualified DBA and do save a lot of time. They are helpful to less experienced users as well.
However there are a few wizards that perform database design tasks.These wizards substitute Microsoft-styled “best practices” for the individual decisions that need to be made when creating a database and its tables.
These “best practices” might suit the needs of some databases, but one size does not fit all. Acceptance of the wizard’s defaults can result in a design that will negatively impact performance for the life of the database.
It is true that many successful companies would not exist if they had to pay for professional help to create their first database. The wizards helped these intelligent but inexperienced people create a working database that met their needs at the time. That is an undeniably strong point in favor of these wizards. However, there is also a strong downside to inexperienced people using wizards to design an enterprise database. The problems fall into these categories.
- Many of the wizards are designed around a lowest common denominator. They try to reduce the complexities and differences of individual SQL Server instances to a set of standard practices applied to all. They default to the use of those practices even in situations where they might be inappropriate or harmful to performance. An experienced DBA will know when to over-ride the wizard’s defaults, but an inexperienced person will not.
- The wizards allow and encourage untrained users to make critical database and server changes without fully understanding the consequences of their actions.
- By masking the details of database operations behind a point and click interface, the wizards often prevent a novice DBA from learning the things a good DBA should know .
The table creation wizard
The table creation wizard allows anyone to create a database table without understanding the important decisions that must be made. For example, the wizard defaults to creating a primary key on an identity column using a clustered index.
This does three good things. It insures that the table has a primary key. It insures that the table has a clustered index. It also insures that the clustered index is not going to cause page splitting.
Very often these are the right things to do. These defaults often work well for a department-scale database that will never have gigabytes of data or hundreds of concurrent users. However, there is a serious problem hidden in all this when it is applied to databases that are expected to grow to an enterprise scale.
The placement of the clustered index for best performance is a complex decision that the wizard is incapable of making intelligently. An identity column is not generally the best place for your clustered index despite what Microsoft suggests. The wizard’s default placement of the clustered index often negates the performance value of the clustered index.
Wizards promote myths
As a strange consequence of the table creation wizard’s widespread use, the defaults of the wizard seem to have replaced the fundamental concepts of relational database design in the minds of a substantial number of new SQL Server DBAs. I have had many conversations with DBAs who insist that the primary key index MUST be clustered. Some believe that a clustered index is required to be unique. They also believe a non-clustered index is not allowed on a primary key. None of these beliefs are true and the misconceptions often result in a poorly performing database.
The defaults of the table creation wizard have also muddied the concept of a primary key in the minds of some. The identity column supplied by the wizard provides a unique but meaningless value for each row in the table. Designers often do not look in the data itself for the uniqueness that relational principles mandate.
Of course the table creation wizard doesn’t force anyone to accept its defaults. Many experienced DBA’s use the wizard every day to save time. But the simplicity of clicking through the wizard accepting all defaults makes it very easy for an inexperienced database designer to shoot his or her foot off.
Database tuning advisor (DTA)
The best thing that can be said about the DTA is that it can create a lot of indexes quickly. Unfortunately, it is not very good at creating the right indexes. The algorithm it uses to identify missing indexes is crude to say the least. Left to its own choices, it will create a huge number of indexes, most of which will never be used. These useless indexes will only take up space and they will require updating whenever the indexed data changes. They will also slow down inserts in the database.
The DTA comes up with some very seductive predictions of improved performance. It might promise 98% performance improvement if you create a particular index. I have followed through on that enough times to know that in a vast majority of cases those predictions are wildly optimistic. Often the index is never used at all.
For someone who has absolutely no knowledge of indexing, this wizard might be better than nothing, but it is not much better. At any rate, if a database is designed by someone with absolutely no knowledge of indexing, things are not likely to go well, DTA or no DTA.
The bright spot in all of this is that the performance problems that can result from wizard-driven design are usually brought about by business success. You won’t encounter problems if your database doesn’t grow and the number of people connecting to it doesn’t increase. It is when you have 1000 shoppers on your website and 100 gigabytes of data in the tables that the design-driven performance problems will begin to drive you crazy.
To avoid this, it is imperative to have a qualified person involved during the design stage of your database so you don’t have to rely on simplistic choices made by a wizard. If you cannot afford qualified help, you should qualify yourself to the degree possible.
This begins with an understanding of relational database design. Most formal studies of database theory begin with reading An Introduction to Database Systems by Christopher Date. This classic by the iconic Mr. Date is a good place for you to start too if you want to substitute intelligent design decisions for the simplistic and automated decisions made by the wizard.