Tuesday 22 June 2010

Generate Indexes Script - usp_GenerateIndexesScript

This is a procedure that generates scripts for your indexes.
Yes, you can do that by a few right click actions in Management Studio, but what if you need to automate it?
Included in this version-
  • File output - needs OLE
  • File Groups
  • Drop Statements
  • Fill Factor
  • Compression
  • Table Schemas

    IF NOT EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.SCHEMATA 
    WHERE SCHEMA_NAME = 'utils'
    AND SCHEMA_OWNER = 'dbo')
    BEGIN
        EXEC('CREATE SCHEMA utils AUTHORIZATION dbo')
    END
    GO
    
    IF NOT EXISTS(SELECT 1
                    FROM INFORMATION_SCHEMA.ROUTINES
                   WHERE ROUTINE_NAME = 'usp_GenerateIndexesScript'
                     AND ROUTINE_TYPE = N'PROCEDURE')
    BEGIN
        EXEC ('CREATE PROCEDURE [utils].[usp_GenerateIndexesScript] AS BEGIN SELECT 1 END')
    END
    GO
    
    /* 
    Adapted from http://www.sqlservercentral.com/Forums/Topic401784-562-2.aspx (by Jonathan AC Roberts. )
    
    Modifications 10/06/2010 By R.Doering - http://sqlsolace.blogspot.com
    
     1) Changed Schema of routine to Utils
     2) Changed Name from INFGenerateIndexesScript to GenerateIndexesScript
     3) Added Schemas to script
     4) Reformatted for clarity
     5) Compression Option added
    
    --  Usage: 
    
    EXEC utils.usp_GenerateIndexesScript 
      @IncludeFileGroup    = 1
        ,@IncludeDrop      = 1
        ,@IncludeFillFactor    = 1
        ,@IncludeCompression  = 1    
        ,@destinationparameter  = '\\Server\sqlbackup$\Indexes\'
    */ 
    
    IF NOT EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.SCHEMATA 
    WHERE SCHEMA_NAME = 'utils'
    AND SCHEMA_OWNER = 'dbo')
    BEGIN
        EXEC('CREATE SCHEMA utils AUTHORIZATION dbo')
    END
    GO
    
    IF NOT EXISTS(SELECT 1
                    FROM INFORMATION_SCHEMA.ROUTINES
                   WHERE ROUTINE_NAME = 'GenerateIndexesScript'
                     AND ROUTINE_TYPE = N'PROCEDURE')
    BEGIN
        EXEC ('CREATE PROCEDURE [utils].[GenerateIndexesScript] AS BEGIN SELECT 1 END')
    END
    GO
    
    ALTER PROCEDURE utils.usp_GenerateIndexesScript
    (
         @IncludeFileGroup   bit = 1
        ,@IncludeDrop     bit = 1
        ,@IncludeFillFactor   bit = 1
        ,@IncludeCompression bit = 1
        ,@destinationparameter NVARCHAR(1000) = NULL
     
    )
    AS
    
    BEGIN
        -- Get all existing indexes, but NOT the primary keys
        DECLARE Indexes_cursor CURSOR
            FOR SELECT 
         SC.Name   AS SchemaName
         , SO.Name   AS TableName
                        , SI.Object_Id     AS TableId
         , SI.[Name]         AS IndexName
         , SI.Index_ID       AS IndexId
         , FG.[Name]       AS FileGroupName
         , CASE WHEN SI.Fill_Factor = 0 THEN 100 ELSE SI.Fill_Factor END  Fill_Factor
                  FROM sys.indexes SI
                  LEFT JOIN sys.filegroups FG
                         ON SI.data_space_id = FG.data_space_id
                  INNER JOIN sys.objects SO
         ON SI.object_id = SO.object_id
         INNER JOIN sys.schemas SC
         ON SC.schema_id = SO.schema_id
                 WHERE ObjectProperty(SI.Object_Id, 'IsUserTable') = 1
                   AND SI.[Name] IS NOT NULL
                   AND SI.is_primary_key = 0
                   AND SI.is_unique_constraint = 0
                   AND IndexProperty(SI.Object_Id, SI.[Name], 'IsStatistics') = 0
                   AND FG.[Name]  IS NOT NULL
                 ORDER BY Object_name(SI.Object_Id), SI.Index_ID
    
        DECLARE @SchemaName  sysname
        DECLARE @TableName   sysname
        DECLARE @TableId     int
        DECLARE @IndexName    sysname
        DECLARE @FileGroupName sysname
        DECLARE @IndexId     int
        DECLARE @FillFactor    int
    
        DECLARE @NewLine nvarchar(4000)     SET @NewLine = CHAR(13) + CHAR(10)
        DECLARE @Tab   nvarchar(4000)     SET @Tab = Space(4)
    
     DECLARE @SQLOutput nvarchar(max) SET @SQLOutput = ' '
     
        -- Loop through all indexes
        OPEN Indexes_cursor
    
        FETCH NEXT
         FROM Indexes_cursor
         INTO @SchemaName, @TableName, @TableId, @IndexName, @IndexId, @FileGroupName, @FillFactor
    
        WHILE (@@Fetch_Status = 0)
            BEGIN
    
                DECLARE @sIndexDesc nvarchar(4000)
                DECLARE @sCreateSql nvarchar(4000)
                DECLARE @sDropSql  nvarchar(4000)
    
                SET @sIndexDesc = '-- Index ' + @IndexName + ' on table ' + @TableName
                SET @sDropSql = 'IF EXISTS(SELECT 1' + @NewLine
                              + '            FROM sysindexes si' + @NewLine
                              + '            INNER JOIN sysobjects so' + @NewLine
                              + '                   ON so.id = si.id' + @NewLine
                              + '           WHERE si.[Name] = N''' + @IndexName + ''' -- Index Name' + @NewLine
                              + '             AND so.[Name] = N''' + @TableName + ''')  -- Table Name' + @NewLine
                              + 'BEGIN' + @NewLine
                              + '    DROP INDEX [' + @IndexName + '] ON [' + @SchemaName + '].[' + @TableName + ']' + @NewLine
                              + 'END' + @NewLine
    
                SET @sCreateSql = 'CREATE '
    
                -- Check if the index is unique
                IF (IndexProperty(@TableId, @IndexName, 'IsUnique') = 1)
                    BEGIN
                        SET @sCreateSql = @sCreateSql + 'UNIQUE '
                    END
                --END IF
                -- Check if the index is clustered
                IF (IndexProperty(@TableId, @IndexName, 'IsClustered') = 1)
                    BEGIN
                        SET @sCreateSql = @sCreateSql + 'CLUSTERED '
                    END
                --END IF
    
                SET @sCreateSql = @sCreateSql + 'INDEX [' + @IndexName + '] ON [' + @SchemaName + '].[' + @TableName + ']' + @NewLine + '(' + @NewLine
    
                -- Get all columns of the index
                DECLARE IndexColumns_cursor CURSOR
                    FOR SELECT SC.[Name],
                               IC.[is_included_column],
                               IC.is_descending_key
                          FROM sys.index_columns IC
                         INNER JOIN sys.columns SC
                                 ON IC.Object_Id = SC.Object_Id
                                AND IC.Column_ID = SC.Column_ID
                         WHERE IC.Object_Id = @TableId
                           AND Index_ID = @IndexId
                         ORDER BY IC.key_ordinal
    
                DECLARE @IxColumn   sysname
                DECLARE @IxIncl     bit
                DECLARE @Desc     bit
                DECLARE @IxIsIncl     bit     SET @IxIsIncl = 0
                DECLARE @IxFirstColumn  bit     SET @IxFirstColumn = 1
    
                -- Loop through all columns of the index and append them to the CREATE statement
                OPEN IndexColumns_cursor
                FETCH NEXT
                 FROM IndexColumns_cursor
                 INTO @IxColumn, @IxIncl, @Desc
    
                WHILE (@@Fetch_Status = 0)
                    BEGIN
                        IF (@IxFirstColumn = 1)
                            BEGIN
                                SET @IxFirstColumn = 0
                            END
                        ELSE
                            BEGIN
                                --check to see if it's an included column
                                IF (@IxIsIncl = 0) AND (@IxIncl = 1)
                                    BEGIN
                                        SET @IxIsIncl = 1
                                        SET @sCreateSql = @sCreateSql + @NewLine + ')' + @NewLine + 'INCLUDE' + @NewLine + '(' + @NewLine
                                    END
                                ELSE
                                    BEGIN
                                        SET @sCreateSql = @sCreateSql + ',' + @NewLine
                                    END
                                --END IF
                            END
                        --END IF
    
                        SET @sCreateSql = @sCreateSql + @Tab + '[' + @IxColumn + ']'
                        -- check if ASC or DESC
                        IF @IxIsIncl = 0
                            BEGIN
                                IF @Desc = 1
                                    BEGIN
                                        SET @sCreateSql = @sCreateSql + ' DESC'
                                    END
                                ELSE
                                    BEGIN
                                        SET @sCreateSql = @sCreateSql + ' ASC'
                                    END
                                --END IF
                            END
                        --END IF
                        FETCH NEXT
                         FROM IndexColumns_cursor
                         INTO @IxColumn, @IxIncl, @Desc
                    END
                --END WHILE
                CLOSE IndexColumns_cursor
                DEALLOCATE IndexColumns_cursor
    
                SET @sCreateSql = @sCreateSql + @NewLine + ') '
    
                IF (@IncludeFillFactor = 1) OR (@IncludeCompression = 1)
                    BEGIN
                        SET @sCreateSql = @sCreateSql + @NewLine + 'WITH (' 
                      END
    
                IF @IncludeFillFactor = 1
                    BEGIN
                        SET @sCreateSql = @sCreateSql  + 'FillFactor = ' + Cast(@FillFactor as varchar(13)) 
                    END
         
                    IF @IncludeCompression = 1
                    BEGIN
            IF @IncludeFillFactor = 1
           BEGIN
            SET @sCreateSql = @sCreateSql + ','
           END
                        SET @sCreateSql = @sCreateSql  + 'DATA_COMPRESSION = PAGE'
                    END
                    
                     IF (@IncludeFillFactor = 1) OR (@IncludeCompression = 1)
                    BEGIN
                        SET @sCreateSql = @sCreateSql  + ')' + @NewLine
                    END
                                
                --END IF
    
                IF @IncludeFileGroup = 1
                    BEGIN
                        SET @sCreateSql = @sCreateSql + 'ON ['+ @FileGroupName + ']' + @NewLine
                    END
                ELSE
                    BEGIN
                        SET @sCreateSql = @sCreateSql + @NewLine
                    END
                --END IF
    
                PRINT '-- **********************************************************************'
                PRINT @sIndexDesc
                PRINT '-- **********************************************************************'
    
    
      SET @SQLOutput = @SQLOutput + '-- **********************************************************************' + @NewLine
            SET @SQLOutput = @SQLOutput +  @sIndexDesc + @NewLine
            SET @SQLOutput = @SQLOutput +  '-- **********************************************************************' + @NewLine
      SET @SQLOutput = @SQLOutput + @NewLine
    
    
                IF @IncludeDrop = 1
                    BEGIN
                        PRINT @sDropSql
                        PRINT 'GO'
                        
                       SET @SQLOutput = @SQLOutput + @sDropSql + @NewLine
                       SET @SQLOutput = @SQLOutput + 'GO' + @NewLine 
                        
                    END
                --END IF
    
                PRINT @sCreateSql
                PRINT 'GO' + @NewLine  + @NewLine
               
                SET @SQLOutput = @SQLOutput + @sCreateSql + @NewLine
                SET @SQLOutput = @SQLOutput + 'GO' + @NewLine  
    
    
                FETCH NEXT
                 FROM Indexes_cursor
                 INTO @SchemaName, @TableName, @TableId, @IndexName, @IndexId, @FileGroupName, @FillFactor
            END
    
        --END WHILE
        CLOSE Indexes_cursor
        DEALLOCATE Indexes_cursor
        
        -- @SQLOutput contains the output to place in a file
        
    
    IF LEN(@destinationparameter) > 0
      BEGIN
      DECLARE @destinationfile varchar(500)
      DECLARE @destinationpath NVARCHAR(1000)
      DECLARE @as_at DATETIME
      DECLARE @databasename VARCHAR(100)
      DECLARE @servername VARCHAR(100)
    
      SET @servername = CAST(SERVERPROPERTY('MachineName') AS VARCHAR(100))
      SET @databasename = CAST(DB_NAME() AS VARCHAR(100))
      SET @as_at = GETDATE()
    
      SET @destinationpath = @destinationparameter + @servername
      EXEC sql_tools.Utils.usp_OLECreateFolder @newfolder = @destinationpath
      SET @destinationpath = @destinationpath + N'\' + @databasename
      EXEC sql_tools.Utils.usp_OLECreateFolder @newfolder = @destinationpath
      SET @destinationpath = @destinationpath + N'\' + REPLACE(REPLACE(CONVERT(NVARCHAR(20),@as_at,120),' ','.'),':','')
      EXEC sql_tools.Utils.usp_OLECreateFolder @newfolder = @destinationpath
    
      SET @destinationfile = @destinationpath + '\' + @databasename + '_Indexes_'+ REPLACE(REPLACE(CONVERT(NVARCHAR(20),@as_at,120),' ','.'),':','') + '.sql'
    
      EXEC sql_tools.utils.usp_OLEwritefile  @FileName = @destinationfile
            , @TextData = @SQLOutput
            , @FileAction = 'CREATENEW'
    END    
        
        --PRINT @SQLOutput
    END
    
    GO
    

    You execute the procedure like this -
    EXEC sql_tools.utils.usp_GenerateIndexesScript 
    @IncludeFileGroup  = 1
        ,@IncludeDrop = 1
        ,@IncludeFillFactor = 1
        ,@IncludeCompression  = 1    
        ,@destinationparameter = '\\SERVER07\sqlbackup$\Indexes\'
        
    GO
    

    Bookmark : Performance Impact of Profiler Tracing

    It's widely known that setting up tracing via sql scripts is far more desirable than using Profiler and watching in real time.

    Linchi Shea has taken the trouble of posting on exactly that subject >

    Linchi Shea : Performance Impact: Profiler Tracing vs. Server Side SQL Tracing

    Thursday 17 June 2010

    Bookmark : SSIS Expression Editor & Tester

    Allan Mitchell and Darren Green of SQLIS/Konesans have developed a tool for editing and testing SSIS Expressions.

    If you've ever used SSIS in depth, you'll appreciate just how helpful this will be.

    It's hosted over on codeplex >
    CodePlex : SSIS Expression Editor & Tester

    Thanks guys, will be trying it out shortly.

    Tuesday 15 June 2010

    Goals for 2010 :Update

    Back in January, I set myself some goals. Now we’re half way through 2010, it’s time to review what I’ve (not) achieved...

    Improve my web presence -

    Tidy sqlsolace, improve tagging and searchability -

    Achieved :
    1. I’ve added fixed ‘pages’ to my blog (look up, the categories at the top!) These make it easy to find frequently accessed material. Pages are a new blogger feature.
    2. A published search widget from google (search box on the right hand side) is proving far more effective than the blogger one at the top of the page.
    3. I’ve also gone back and manually tagged a fair few posts I published without categorising.
    To do :
    1. Continue categorising / tagging posts
    2. Review old posts for duplicate content, typos etc.
    3. Produce more 101 posts and summary pages, deep linking older content.
    Review (& either scrap or post) my backlog of scripts and notes -

    Achieved : 30 posts from old notes

    To do : 20 posts (have text files and screenshots to support these ready)

    Review and remove old/unprofessional material from my personal web site

    Achieved : Nothing!

    To do : As above. Need to be strict and review content for relevance and professionalism.

    Community - 

    Publish further scripts on other sites (September 2009 : had 1st published on SSC) -

    Achieved : Have had a further 4 scripts published @ SQLServerCentral

    To do : Am aiming for a further 10 this year

    Publish an article

    Achieved : Nothing!

    To do : Write one and get it published!.

    Attend more Sql Server events / meetups

    Achieved : 1 (SQLBits VI in London, April 2010)

    To do : More!!!

    Network more 

    Achieved : LinkedIn connections with people I’ve genuinely worked with and respect. I don’t see the value of randomly adding others I cannot vouch for.

    To do : More (real contacts, outside of Linked-in and the blogosphere)

    Learning – 

    Continue with SSIS development to further my ETL skills.

    Achieved : 3 SSIS Solutions are now in production

    To do : More Advanced SSIS
    There are 2 barriers to achieving this goal –
    1) business requirements and data volume levels are not yet requiring it.
    2) our disk subsystem (purchased by a previous incumbent to my role) makes parallel processing difficult to explore.

    Obtain Microsoft BI certifications

    Acheived : Nothing!

    To do : Start learning!

    Tackle the mountain of books, whitepapers and printed articles in my office.

    Achieved :
    Couple of chapters of SQL Server MVP Deep Dives
    Half of Inside Microsoft SQL Server 2008 T-SQL Querying

    Reading technical books is proving more difficult than I thought, due to -
    1. Limited personal time (young kids at home)
    2. Even less quality time (trying to start a book at 8pm when kids are in bed and my brain is mush)
    3. I find myself making notes on the content I read
    4. I stop reading to try out scripts and ideas.
    To do : Finish the above books, and get on with some more!

    Monday 14 June 2010

    Sharing for anonymous access - Enabling the guest account on Windows 2008

    In Windows 2008 the Guest account diasbled by default.
    No matter what permissions i gave a share or NTFS permissions on the folder, I could not access it without a login prompt.
    Users were crossing from a (deliberately) untrusted domain on a separate network.

    I don't use anonymous access usually, but this was just as a file store.
    It was resolved by >

    Enabling the guest account on Windows 2008

        * Computer Management > Local Users and Groups > Users
        * Right click Guest > Properties
        * Untick "Account is disabled"

    Friday 11 June 2010

    Windows 7 Home Premium - No Remote Desktop!!!!

    Got a new Acer Aspire Revo today to use for movies and music.
    Windows 7 Home Premium came preinstalled, along with at least 20 games and other bloatware I had to uninstall!

    Anyway, I digress. The real issue is the absence of RDP (remote desktop) on the pc. I was relying on this to be able to administer remotely (leaving the tv unaffected).

    Fortunately I don't appear to be alone in wanting this functionality, and a quick search revealed other's frustrations and (of course) a solution....

    Windows 7 RTM concurrent remote desktop patch
    http://thegreenbutton.com/forums/t/79427.aspx

    Windows Update Settings via Group Policy

    Here I show how to use Group Policy to set Windows Update settings.
    This is using Windows 2008 Active Directory.

    1) Create a security group for the to place computers to be updated -


    2) I've chosen to create a GPO for the settings -


    3) Navigate to -
    Computer Configuration > Policies > Administrative Templates > Windows Components > Windows Updates


    Configure the settings as follows -

    4) Configure Automatic Updates -
    I've chosen to download and install every day at 20:00.

    NB : If the download source is Windows Update then this would be a daft setting as it is uncontrollable.
    I'm using an internal WSUS server to control updates so this setting will only apply updates at 20:00 on the day they have been authorized for the computer.


    5) Specify intranet Microsoft update service location -
    The address of the WSUS instance.


    6) Automatic Updates detection frequency -
    Fairly self explanatory, how often to check update source.


    7) Allow Automatic Updates immediate installation -
    Again, self explanatory - install straight after updates?


    8) Enable client-side targeting -
    Specify the group created in step 1.

    Thursday 10 June 2010

    SSIS : Tuning Buffer Size

    Tuning the Buffer Size for the data flow task in SSIS

    The buffer size used is the smaller of 2 figures.

    1) defaultbuffersize
    2) defaultmaxbufferrows * row width

    defaultbuffersize has a default of 10 MB (10485760 B)

    You can change this to amaximum of 100 MB (104857600 B), which I have done and is the figure I have used here.


    Calculating Row Width

    I'm using a simple example of a single target table here.

    The row width calculation gets messy if you try it from the table definition i.e. adding the sizes of columns (and remembering 2 bytes per character for unicode columns etc)

    I chose to use the table size data and work out the average -

    sp_spaceused 'dbo.bigtable'

    namerowsreserveddataindex_sizeunused
    bigtable14445146163064384 KB44740776 KB18317304 KB6304 KB

    So, data size in KB 44740776

    data in Bytes = 44740776 * 1024 = 45814554624 B

    Divide by row count ,
    = 45814554624 / 144451461

    317.16 Bytes/row

    Row Width = 317 Bytes.

    From the row width figure and the desire to put as much in the buffer as possible (to match the 100MB defaultbuffersize) we can calculate defaultmaxbufferrows.


    Calculating DefaultMaxBufferRows

    100MB buffer = 104857600 bytes
    Row Width = 317 Bytes.

    defaultmaxbufferrows = BufferSize / Row Width

    = 104857600 / 317
    = 330781 rows

    So to ensure maximum buffer usage here we set -

    defaultbuffersize = 104857600
    defaultmaxbufferrows = 330781 (though I'd most likely round down to a the nearest hundred).

    Monday 7 June 2010

    Bookmark : Create Indexes from the Missing Indexes DMV

    I looked into Using Missing Indexes DMVs to generate index suggestions a while back. I've just found a better version on Seth Phelabaum's site however.

    Create Indexes from the Missing Indexes DMV utilises one of Glenn Berry's performance queries and constructs CREATE INDEX statements for you.

    It's a handy script that I've already used.
    I did have to add the following to the statements though
    WITH (MAXDOP=1,ONLINE=ON,DATA_COMPRESSION=PAGE)

    MAXDOP because I don't want index creation to use multiple processors.
    ONLINE because I'm using Enterprise SQL 2008 and don't want to lock tables
    DATA_COMPRESSION because again I'm on Enterprise SQL 2008 and am utilising it's excellent inbuilt table and index compression.

    Friday 4 June 2010

    SSC : The Ultimate Running Requests Reporter

    Jesse Roberge, author of many SqlServerCentral scripts, has come up with this, 'The Ultimate Running Requests Reporter'.

    I mention as I was impressed and I've susbsequently implemented in my tools database.

    It expands on the multiple variations of sp_who3 available to download, providing you with a more detailed snapshot of what is running at an instant.

    Executing it returns the following -
    • SessionID
    • RequestID
    • DatabaseID
    • DatabaseName
    • LoginName
    • HostName
    • ProgramName
    • ClientInterfaceName
    • BlockedBySessionID
    • BlockingRequestCount
    • WaitType
    • WaitTime
    • CPUTime
    • ElapsedTime
    • Reads
    • Writes
    • LogicalReads
    • PendingIOCount
    • RowCount
    • GrantedQueryMemoryKB
    • Score
    • BatchText
    • BatchTextLength
    • StatementStartPos
    • StatementEndPos
    • StatementTextLength
    • StatementText
    • QueryPlan
    Link : The Ultimate Running Requests Reporter

    Thursday 3 June 2010

    What files are in my backup?

    An oversimplified example here , but revisiting a DBA basic as moving datafiles info a new configuration.

    RESTORE FILELISTONLY
    FROM DISK = 'c:\AdventureWorks2008.bak'
    


    This lists the file locations and sizes of data files inside a backup.
    By default, i.e. without specifying WITH MOVE, these are the locations that a RESTORE operation will move data back to.

    My real scenario has a fact table partitioned into years and over multiple data files.


    MS SQL Tips : What is in your SQL Server backup files?

    TSQL : % Percentage complete of running requests

    SELECT percent_complete ,*
    FROM sys.dm_exec_sessions s
    INNER JOIN sys.dm_exec_requests r
    ON s.session_id = r.session_id
    

    MSSQLTips : Finding a SQL Server process percentage complete with dynamic management views

    Update 14/06/2011 - Sweet SQL Lass has a version to show backup/restore progress. It nicely casts the time elapsed and remaining into minutes too. Sweet SQL Lass : How far has my backup/restore got?

    Wednesday 2 June 2010

    Bookmark : ASYNC_NETWORK_IO , NETWORKIO Wait Types

    NETWORKIO (SQL 2000) , ASYNC_NETWORK_IO (SQL 2005+) 
    These occur with a delay in network throughput. It could be the network itself, or a slow application processing the results.

    An Excellent post explaining them>

    Joe Sack : Troubleshooting ASYNC_NETWORK_IO, NETWORKIO

    Rename Filegroup

     How to change the logical name of a filegroup...

    ALTER DATABASE databasename MODIFY FILEGROUP [OldName] NAME = [NewName]

    simples ...