Baby SQL ... (^.^)

Bad Index Maintenance Plan

Our team had been complained about some performance problem when trying to query some data from their application. When I was checking on our database servers, I saw this maintenance plan - scheduled every day at 12AM.

Just in case, if you guys can't see the picture on the left, this is the detail of this someone's bad index maintenance plan:

Check Database Integrity –> Shrink Database (all user database, shrink database when it grows beyond 100MB, and amount of free space to remain after shrink is 10%, Return freed space to operating system) –> Reorganize Index –> Rebuild Index –> Update Statistics (Update all existing statistics, Full scan)

Regardless to any performance problem, I don’t think this is a good maintenance plan.

Rebuild Index or Reorganize Index

Use [sys.dm_db_index_physical_stats] to detect fragmentation in all or specific index. you can reorganize index with fragmentation between 30% – 70% , more than that just rebuild it.

Update Statistics

Reorganize index will not update the statistic in any way whereas rebuild index will update the statistic with full scan. That’s why you can update statistics after you reorganize index, but don't update statistics after rebuild index or reorganize index before rebuild index.

Update database user name to another login name

Why?

Error: 18456, followed by
Error: Login failed for user 'username'. Reason: Failed to open the explicitly specified database.

and then you find out that in your database, there is a user name that has no login name

How to fix it?

you can use sp_change_users_login to update database user name to another login name and usually used to fix orphaned user name (user name without login name), but only for SQL Server login and this is executed on database level.

sp_change_users_login ‘Report’ : search all user names that have no login name
sp_change_users_login ‘update_one’, ‘user name’, ‘login name’ : update the user name.

if you want to update orphaned user name for windows login name, you can drop the orphaned user name in your database, create the login name and then add it to your database again. (^.^)v