Two SQL Server maintenance tips

Indeed, database maintenance is often given to professional database administrators, but as a developer, you may occasionally need to do this work temporarily. So, try these two SQL Server maintenance tips: Easily change the database owner, defragment the index. Who would have thought you could even teach one or two new tricks to the database administrators?

To rename a database owner
When replying to or creating a new database, did you notice that SQL Server placed the owner of the database as your NT login name? Just to ensure consistency between the different databases (not to mention the security factor), you might consider using the system process sp_changedbowner to change the database owner to another user such as a system administrator (SA). You may have written such a script to scan all user databases and to rename the database owner to be a system administrator.

The system procedure Sp_changedbowner has a parameter, the @map, whose default value is null (NULL), which can remap the alias of the old owner of the database to a new database owner, such as a system administrator.

To demonstrate this process, let's start by building a database model as small as possible, and then run the Sp_helpuser directive to look at the newly created username list:

CREATE DATABASE Test
Go
Use test
Go
EXEC Sp_helpuser
Go

After the code executes, the output should list the database owner's list (db_owner). If you are using Windows NT Authentication, you should have a null login name and a SID value in the list.

Then let's add two logged-in users: ISUser1 and ISUser2 as db_owner aliases, and change the owner of the database to a system administrator: 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 '
EXEC Sp_helpuser

The output should show the system administrator as db_owner, ISUser1 as db_owner alias.

Now we use Process sp_changedbowner to specify ISUser2 as the new owner of the database. We will use the @map parameter of the process and assign the parameter to "no" so that the user will be aliased.

EXEC sp_changedbowner @loginame = ' ISUser2 ', @map = ' FALSE '

EXEC Sp_helpuser
Go

The output should show that ISUser2 is now the new owner of the database, and the ISUser1 is dropped to alias. Below, we should stop this database and end this demo process.

Use master
Go
DROP DATABASE Test
Go

Implement maintenance with DBCC INDEXDEFRAG commands
Maintaining the index is a tedious effort, but in SQL Server 2000, Microsoft has introduced a maintenance command DBCC INDEXDEFRAG, which has several advantages over the DBREINDEX command for SQL Server7.0. The main advantage is that it is an online operation, so that the user can still work continuously while the command is running. This is because it does not need the resources involved in locking operations at run time like Dbreindex, it can also reduce content blocking.

The DBCC Indexdefrag operates a small, small segment of data so that the operation can stop at any time and track the work it has done. The operation reports an estimated percentage of completed work at five-minute intervals.

From a technical standpoint, DBCC indexdefrag the physical leaf on the current allocation page where the target index is located. When the operation completes, the physical order of the target index corresponds to its logical order, so that the scan speed of the index can be accelerated.

The operation also rescheduled other index pages in the space assigned to the target index. SQL Server will be the space on the index buffer page for the purpose of a fill factor, based on the density of the index data and the amount of space allocated for the index. The empty pages will be released after the operation, which will make the index more compact.

DBCC Indexdefrag also has several drawbacks that require your attention:

If the two indexes in a table share the same space in one disk, and the two indexes are not adjacent, it is best to re-establish the index so that they are adjacent.
If there are too many fragments in the index, the DBCC INDEXDEFRAG command may execute at a slower rate than the Dbreindex command, but if there are not too many fragments in the index, then DBCC INDEXDEFRAG should be much faster than dbreindex, with DBCC The benefits of Indexdefrag replace Dbreindex are introduced online.
Non-leaf (nonleaf) index pages cannot be reordered.
DBCC Indexdefrag cannot update statistics.