By Kaye Garten
Sure, database maintenance is usually left to the “DBA guys,” but occasionally as a developer you’ll be pressed into service. So, try out these two SQL Server maintenance tips and learn to change database owners and defrag your indexes with ease. Who knows? You could even teach those DBA gurus a new trick or two.
Reassigning database owners
When restoring or creating databases, have you noticed that SQL Server sets the database owner to your NT authenticated name? To keep consistency among different databases, not to mention for security reasons, you may want to consider using the system procedure sp_changedbowner to change the database owner to a user like the system administrator (SA). You might also consider writing a script that traverses all the user databases and reassigns the database owner to SA.
The system procedure sp_changedbowner has a parameter, @map, that defaults to a null value if omitted and remaps aliases of the old database owner to the new database owner, SA.
To demonstrate, let’s first create a database that should be as small as the model system database and run the command sp_helpuser to get a listing of newly created users:
CREATE DATABASE test
This should list the database owner (db_owner) as the login you used in SQL Server. If you used Windows NT authentication, there should be a NULL LoginName and a value for the SID.
Next, let’s add a couple of logins: ISUser1 and ISUser2. Designate ISUser1 as an alias to db_owner, and change the database owner to SA:
EXEC sp_addlogin @loginame = 'ISUser1', @passwd = 'ISUser1', @defdb = 'master'
EXEC sp_addlogin @loginame = 'ISUser2', @passwd = 'ISUser2', @defdb = 'master'
EXEC sp_addalias @loginame = 'ISUser1', @name_in_db = 'dbo'
EXEC sp_changedbowner @loginame = 'sa', @map = 'TRUE'
The output should show SA as the db_owner and ISUser1 as a user that is aliased to db_owner.
Now we will designate ISUser2 as the new database owner using sp_changedbowner. We will use the @map parameter for the procedure and assign it to a false value that will drop users that are aliases.
EXEC sp_changedbowner @loginame = 'ISUser2', @map = 'FALSE'
The output should show that ISUser2 is now the new database owner and that ISUser1 has been dropped. Next, we need to drop the test database to wrap up this demonstration.
DROP DATABASE test
Perform maintenance with DBCC INDEXDEFRAG
Performing index maintenance used to be a drag, but with SQL Server 2000, Microsoft has introduced the maintenance command DBCC INDEXDEFRAG, which has some advantages over SQL Server 7.0’s DBREINDEX command. The chief advantage is that the former is an online operation; therefore, users can continue to work on a database while this command is running. This is because the operation doesn't require resources to be locked to the extent that they were with DBREINDEX, which reduces blocking contention as well.
The DBCC INDEXDEFRAG operation works on small segments of data, which allows the operation to be stopped at any time and track the work it has completed. The operation will also report back every five minutes with the estimated percentage completed.
From a technical standpoint, DBCC INDEXDEFRAG rearranges the physical leaf level of an index on currently allocated pages for the index. After the operation completes, the physical order of the index will then match the logical order of the index, thereby speeding up index scanning performance.
The operation also arranges the index pages in the same space allocated to the index. SQL Server will use the fill factor as a target for buffering space on pages for the index, depending on denseness of the index data and the amount of allocated space for the index. Pages that are left empty after the operation will be released, making the index more compact than it originally was.
There are a few drawbacks to DBCC INDEXDEFRAG that you need to be aware of:
- If there are two indexes on a table sharing the same space of an extent and each index is not contiguous to itself, it may be more beneficial to rebuild the indexes, allowing the index pages to be contiguous.
- If fragmentation is heavy on an index, DBCC INDEXDEFRAG may perform slower than the DBREINDEX command. If fragmentation is light on an index, however, DBCC INDEXDEFRAG should be considerably faster than DBREINDEX, with the added bonus of the operation being online.
- Nonleaf index pages are not reordered.
- DBCC INDEXDEFRAG will not update statistics.