2012/05/16

Why It's Good To Be Wrong

Most people don't like to be proven wrong. It seems like a basic instinct: we want to be right all the time. Being wrong is a sign of weakness, and we may be looked down upon by our peers for that weakness. Even when there are no peers involved and the realization of being wrong dawns upon us by way of self discovery, we admonish ourselves.

Perhaps it has something to do with the survival of the fittest as we evolved - if you were right about where the predators were, you could take steps to avoid them. If you were wrong, you died. If you were right about how to find food and shelter, you survived. If you were wrong, you died. Today, if you are right about what decisions to make to earn a good living for yourself and provide for your family, your family does better than if you get it wrong. Whatever the reasons, it seems an almost universal human trait that we do not like being wrong.

And yet, not so much on a life changing level, but more on a day-to-day basis, I look forward to being proven that I was wrong. And my humble opinion is that we all should feel this way. Here is why.

When I find that I have been wrong about something, I have just learned something new, and I have just been corrected. It makes me a better professional, a better practitioner of whatever art I am practicing. For instance, I recently said at my workplace that a database backup created on one version of SQL Server could not be restored onto a different version of SQL Server. A good friend (thanks D2) pointed out the error, and I looked it up, and sure enough I was wrong. Knowing this makes me a better database developer. There is no shame or ego that stands in the way of admitting that I was wrong today, because it makes me right going forward. Every time somebody shows you that you were wrong, and you accept it, you have just made yourself a little bit better educated.

Please do not take this to mean that you should want to be wrong. No, the goal still is to be right. Rather, this is about your attitude when faced with the possibility that you just may have been wrong. Hence, crazy as it sounds, I look forward to being proven wrong. When that happens, I accept it and embrace it, and I am better off for it. If you show me I am wrong, I will thank you for it.

So, have you been wrong today?

2012/05/12

Defrag All Indexes On a Database

I recently had the need to look up all fragmented indexes on a specific database, to determine if they were the reason for less than optimal performance. I wrote the following script, which allows you to pick a specific database to check for fragmented indexes, and decide to just view them or reorganize/rebuild them, and also the fragmentation threshold percentage at which to run these operations. Then I thought it might be useful to others too, so here it is (below).

I am the original developer of this script. I offer it freely to anybody to use, just leave my name in the header where it is.

Thanks,
Hakim Ali


/****************************************************************************************************
Hakim Ali (hakim.ali@sqlzen.com) 2012/05/04
****************************************************************************************************/



-----------------------------------------------------------------------------------------------------
------------------------------------------------------- SET DESIRED VALUES HERE ---------------------
-----------------------------------------------------------------------------------------------------
declare @Database_Name nvarchar(100);                    set @DATABASE_NAME = ''
declare @Rebuild_Threshold_Percent nvarchar(10);        set @REBUILD_THRESHOLD_PERCENT = 30
declare @Reorganize_Threshold_Percent nvarchar(10);        set @REORGANIZE_THRESHOLD_PERCENT = 30
declare @Execute_Rebuild bit;                            set @EXECUTE_REBUILD = 0        -- set to 1 to rebuild
declare @Execute_Reorganize bit;                        set @EXECUTE_REORGANIZE = 0        -- set to 1 to reorganize. If rebuilding, no need to do this.
-----------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------



----------------------------------
-- Initial checking
----------------------------------
declare @Error nvarchar(500)
declare @SQL nvarchar(max)
declare @Online nvarchar(50)

set @Database_Name = ltrim(rtrim(isnull(@Database_Name,'')))

set @Error = 'Database name required.'
if (@Database_Name = '')
begin
    raiserror(@Error,16,1)
    goto the_end
end

set @Error = 'Database "' + @Database_Name + '" does not exist.'
if not exists (select name from sys.databases where name = @Database_Name)
begin
    raiserror(@Error,16,1)
    goto the_end
end

set @Online = ''
if (@@version like '%enterprise edition%')
begin
    set @Online = ' with (online = on)'
end



set @SQL = '
----------------------------------
-- Create table to hold results
----------------------------------
declare @fragmented_indexes table
(            ID                            int identity(1,1)
            ,Database_Name                nvarchar(1000)
            ,[Schema_Name]                nvarchar(1000)
            ,Table_Name                    nvarchar(1000)
            ,Index_Name                    nvarchar(1000)
            ,Fragmentation_Percent        money
            ,Num_Rows                    int
            ,Page_Count                    int
            ,Index_Type                    nvarchar(1000)
            ,Reorganize_SQL                nvarchar(4000)
            ,Rebuild_SQL                nvarchar(4000)
)



----------------------------------
-- Populate table
----------------------------------
insert into @fragmented_indexes
(            Database_Name
            ,[Schema_Name]
            ,Table_Name
            ,Index_Name
            ,Fragmentation_Percent
            ,Num_Rows
            ,Page_Count
            ,Index_Type
)
select        distinct
            Database_Name
                = db_name(database_id)
            ,[Schema_Name]
                = sch.name
            ,Table_Name
                = parent.Name
            ,Index_Name
                = indx.name
            ,Fragmentation_Percent
                = left(isnull(phys_stats.avg_fragmentation_in_percent,0),5)
            ,Num_Rows
                = x.rowcnt
            ,Page_Count
                = phys_stats.page_count
            ,Index_Type
                = phys_stats.index_type_desc
from        sys.dm_db_index_physical_stats(
                db_id('''+@Database_Name+'''),
                default,
                default,
                default,
                ''detailed''
            ) phys_stats
inner join    ['+@Database_Name+'].sys.indexes indx
            on indx.object_id = phys_stats.object_id
            and indx.index_id = phys_stats.index_id
inner join    ['+@Database_Name+'].sys.objects parent
            on parent.object_id = phys_stats.object_id
inner join    ['+@Database_Name+'].dbo.sysindexes x
            on x.id = indx.object_id
inner join    ['+@Database_Name+'].sys.schemas sch
            on sch.schema_id = parent.schema_id
where        1 = 1
and            isnull(indx.name,'''') <> ''''
and            x.rowcnt > 0
and            (phys_stats.avg_fragmentation_in_percent >= '+@Rebuild_Threshold_Percent+'
            or
            phys_stats.avg_fragmentation_in_percent >= '+@Reorganize_Threshold_Percent+')



update        @fragmented_indexes
set            Reorganize_SQL =
                ''alter index [''+Index_Name+''] on [''+Database_Name+''].[''+[Schema_Name]+''].[''+Table_Name+''] reorganize''
            ,Rebuild_SQL =
                ''alter index [''+Index_Name+''] on [''+Database_Name+''].[''+[Schema_Name]+''].[''+Table_Name+''] rebuild'+@Online+'''



----------------------------------
-- View results
----------------------------------
select        ID
            ,Database_Name
            ,[Schema_Name]
            ,Table_Name
            ,Index_Name
            ,Fragmentation_Percent
            ,Num_Rows
            ,Page_Count
            ,Index_Type
from        @fragmented_indexes
order by    Database_Name
            ,[Schema_Name]
            ,convert(money,Fragmentation_Percent) desc
            ,Table_Name
'

----------------------------------
-- If rebuild/reorganize option set...
----------------------------------
if (@Execute_Rebuild = 1)
begin
    set @SQL = @SQL + '
    declare @current_sql nvarchar(max)
    while exists (select top 1 Rebuild_SQL from @fragmented_indexes)
    begin
        set @current_sql = (select top 1 Rebuild_SQL from @fragmented_indexes)
        execute sp_executesql @current_sql
        --select @current_sql
        delete @fragmented_indexes where Rebuild_SQL = @current_sql
    end
    '
end
else if (@Execute_Reorganize = 1)
begin
    set @SQL = @SQL + '
    declare @current_sql nvarchar(max)
    while exists (select top 1 Reorganize_SQL from @fragmented_indexes)
    begin
        set @current_sql = (select top 1 Reorganize_SQL from @fragmented_indexes)
        execute sp_executesql @current_sql
        --select @current_sql
        delete @fragmented_indexes where Reorganize_SQL = @current_sql
    end
    '
end



----------------------------------
-- Go!
----------------------------------
execute sp_executesql @SQL
the_end:


Simplicity is Not Simple!

It is far too easy to build something complex. But simplicity - and a semblance of ease and intuition - now that takes a lot of work. But let that not be a deterrent to try and achieve it. And do not mistake the preference for simplicity as an excuse to shy away from complex problems. Quite the opposite: let's take on difficult problems, and engineer a solution so elegant that it makes the problem seem easy to solve. Let's find some database Zen !