Monthly Archives: April 2009

The Maintenance Plan Wizard Can Lead You Astray

Posted on by

In theory, the SQL Server Maintenance Plan Wizard is supposed to make it easier for non-DBAs or novice DBAs to create database maintenance plans that help to optimize the performance of their SQL Server databases. The problem is that unless you really know what you are doing, you can hurt the performance of your SQL Server if you make poor choices. Unfortunately, the Wizard does not tell you if you make poor choices.

For example, check out the following screen, which is part of the SQL Server 2008 Maintenance Plan Wizard.

image

If you are an experienced DBA, your first thought should be, “Why are all the maintenance tasks checked?” This is because experienced DBAs know that you only need to perform some of these tasks, not all of them, and in fact, if you choose all of them, you can cause your server to waste a lot of valuable resources. For example, performing all three of the following tasks: reorganizing, rebuilding, and updating statistics is redundant, as rebuilding your indexes essentially performs the same tasks as reorganizing and updating statistics. In other words, if you rebuild your indexes, reorganizing and updating statistics is a duplication of effort. There are also some additional problems that can occur when selecting all of the options, but I don’t have time to write about them all now, but I think you get the point.

Now, let’s look at the above screen from the perspective of the non-DBA, or novice DBA who isn’t familiar with what maintenance plans do, or how they work. From their point of view, don’t you think that the more options you select, the better off your SQL Server will be? Isn’t more always better than less? What has prompted me to write about this topic is because I just reviewed a SQL Server instance that was installed and set up by a non-DBA, and as you have already probably guessed, they selected every maintenance task on the menu, thinking that more is better than less. Because the non-DBA made this choice (which is not all than uncommon), the maintenance plan took more than twice the time it really needed to run, wasting a lot of the server’s resources. There were some additional problems as well, but that is another story.

The point I want to make is that the Maintenance Plan Wizard can sometimes be useful in the hands of an experienced DBA, but it can be very dangerous in the hands of a non-DBA or novice-DBA. In fact, most experienced DBAs I know write their own maintenance plans because using the Wizard just has too many limitations and problems. If you are an experienced DBA, you know what I mean. If you are a non-DBA or novice DBA reading this, please take heed of my message, more is not better. If you decide to use the Wizard instead of writing your own custom maintenance plans, please do your research before you begin, learning what should, and what should not be done, within a maintenance plan.

PS: I have decided to write a book on how to use the Maintenance Plan Wizard properly. Look for it during the Fall of 2009.

Don’t Make Dumb Mistakes by Duplicating Indexes

Posted on by

Just today, I was reviewing the indexing of a database used by a third-party application. As part of my review, I ran a script against it that is used to identify duplicate indexes. The script I used was written Paul Nielsen, the author of the SQL Server Bible; and Itzik Ben-Gan. The script is available here.

After running Paul’s script against the database to find duplicate indexes, I discovered that almost every table had a duplicate index. As I delved into this a little closer, I discovered what the designer of this database had done.

First, it looked like he created primary keys using non-clustered indexes on an identity column for most of the tables in his database. Second, he then added a clustered index on the same identity column for each of these same tables. By doing so, he ended up having two identical indexes on the same identity column, one clustered and one non-clustered. Instead, the designer should have created his primary keys using clustered indexes instead of using non-clustered indexes. This would give him the same end effect, but requiring only a single index, not two. Obviously, by having duplicate indexes on most of his tables, he was causing SQL Server a lot of additional work to maintain extra indexes that were not needed.

If you have never checked your databases for duplicate indexes before, you should do so, as you might find duplicate indexes that can be removed, helping to boost the performance of your existing databases.

Online T-SQL Scripts and Copyright

Posted on by

There are thousands of Transact-SQL scripts available on the Internet. They provide a great way to learn how other people approach and solve the same kind of problems you have and, in many cases, will save you from having to “reinvent the wheel”. When I need a new script, the first thing I always do is to check out the sizeable script libraries on SQLServerCentral.Com,Codeplex.Com, and other SQL Server-related websites. If I can’t find what I need, then I usually resort to a Google search. If that turns up nothing, only then do I resort to writing it from scratch.

While these T-SQL script archives are a wonderful resource, they also have a few drawbacks. The issue of variable code quality has been well-discussed and you should always carefully evaluate and test any online scripts before using them. A lesser known, but equally important issue, is the one of copyright. Sometimes the situation is clear-cut. I have seen a few scripts that carry a copyright notice explicitly stating that you can’t use the script without first obtaining permission from the author. In a few, ideal, cases the script author will have included some form of notice stating that the code has been published with a free public license, such as the one described at www.fsf.org. In this case, you will know that using the code won’t get you into any potential legal trouble.

However, in the vast majority of cases, the scripts are published without any form of copyright notice. Most people assume, incorrectly, that these scripts are freely available for reuse. In the United States, by law, any creative work is automatically copyrighted by its author, even if the author does not specifically include a copyright notice within the work. In other words, you need to get express permission from the author to use their script.

In theory, if you reuse a script that does not have any copyright notice, you leave yourself, and the organization you work for, open to potential legal action from the script author, or from whoever owns the copyright for the script. Furthermore, making a small modification to a script does not necessarily protect you from copyright issues. If you base the code you write on someone else’s code, you still may be violating copyright law.

The issue of copyright can get particularly sticky if the script author does not actually “own” the script. For example, it is not uncommon, especially at larger organizations, for employee contracts to stipulate that any creative work that an employee produces, while on the job, belongs to the organization. Any DBA subject to such an agreement should not share scripts on the Internet without specific permission from their organization. The problem is that, in the absence of a clear copyright statement, there is no way of telling who owns the script or even if the script author had permission to post it.

Because there are so many grey areas in copyright law, I would like to encourage all T-SQL script writers to:

  1. Verify that you own the code you write and, if not, that you have permission to share it.
  2. Include a free public license with any code you publish.

This way, people using your script can rest assured they are complying with copyright laws. I’d like to hear what you think. What is the best source of online scripts? How do you deal with the dilemma of scripts without any form of copyright notice? Do you think T-SQL script writers should include a free public license with the code they share?

Summary of What PASS Members Want from the 2009 PASS Community Summit

Posted on by

PASS recently conducted a survey of its members, asking them what topics they would like to see covered at the 2009 PASS Community Summit. A total of 304 people responded to the survey, and the entire survey can be seen at the PASS Speaker’s Resource Page (you have to log on to the website to see the results). Given that the report exceeds 20 pages, I thought I would summarize the key results.

They include:

  • Database Administrator topics were the most sought after, with Database Developer topics coming in second.
  • Most of the attendees were seeking Advanced level topics, those that assumes a solid knowledge of technology, strong coding skills, and for DBAs with 4-6 years of DBA experience.
  • Sessions on SQL Server 2008 were the most desired, with SQL Server 2005 a distant second.
  • Best Practices- and Tips & Tricks-type sessions were highly rated.
  • The three most popular “database administration” topics that cover new features in SQL Server 2008″ included: Performance Data Collection, SQL Audit, and Policy-Based Management.
  • The three most popular “general database administration” topics included: Performance Monitoring and Tuning, DMVs, and Scalability.
  • The three most popular “high availability” topics included: Minimizing System Downtime, Supporting Mission Critical Applications, and Database Mirroring.
  • The three most popular “Database Architecture/Deployment” topics include: Virtualization, Database Storage Solutions, and Clustering.
  • The three most popular “Database and Application Development Features in SQL Server 2008” included: T-SQL Improvements, Table-Valued Parameters, and Filtered Indexes.
  • The three most popular “General Database Design & Development” topic included: Optimizing Queries and Execution Plans, Database Modeling/Database Design, and Application Development Best Practices.
  • The three most popular “Data Warehousing and Business Intelligence Features in SQL Server 2008” included: Reporting Services Enhancements, Report Design Enhancements in Business Intelligence Development Studio, and Integration Services.
  • The three most popular “General Data Warehousing and Business Intelligence” topics included: BI/DW Best Practices using the Microsoft BI Framework, Data Modeling and Schema Design for BI, and Planning, Developing, and Administrating Your BI Environment.
  • The three most popular “Analysis Services” topics included: Advanced Techniques (Tips & Tricks) Using Analysis Services 2005/2008, Building and Deploying Solutions Using Analysis Services in SQL Server 2005/2008, and MDX.
  • The three most popular “SSIS” topics included: Advanced Techniques for SSIS Package Development, Maximizing Your SSIS Performance, and Building and Deploying Solutions Using Integration Services in SQL Server 2005/2008.
  • The three most popular “Reporting Services” topics included: Report Deployment and Administration, Report Access and Delivery Features, and Designing and Creating Reports.
  • The three most popular “Professional Development” topics included: Leadership and Team Management Skills, Strategic IT Planning, and Project Management.
  • The three most popular “Communication” topics included”: Technical Presentations, Project Documentation, and Technical Writing.
  • The three most popular “Career Development” topics included: “Developing and Improving Your Career, Marking Yourself More Marketable, and Certification.

Like all summaries of reports, a lot of detail has been left out. If you are serious about submitting a speaker’s abstract, I highly recommend you review the entire report, as you will want to take this information into account when creating your abstracts.

If you are interested in becoming a speaker at the 2009 PASS Community Summit, be sure you get your abstracts in no later than April 10, 2009. After then, the call for speakers will be closed.