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

    WHERE SCHEMA_NAME = 'utils'
    AND SCHEMA_OWNER = 'dbo')
                   WHERE ROUTINE_NAME = 'usp_GenerateIndexesScript'
                     AND ROUTINE_TYPE = N'PROCEDURE')
        EXEC ('CREATE PROCEDURE [utils].[usp_GenerateIndexesScript] AS BEGIN SELECT 1 END')
    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\'
    WHERE SCHEMA_NAME = 'utils'
    AND SCHEMA_OWNER = 'dbo')
                   WHERE ROUTINE_NAME = 'GenerateIndexesScript'
                     AND ROUTINE_TYPE = N'PROCEDURE')
        EXEC ('CREATE PROCEDURE [utils].[GenerateIndexesScript] AS BEGIN SELECT 1 END')
    ALTER PROCEDURE utils.usp_GenerateIndexesScript
         @IncludeFileGroup   bit = 1
        ,@IncludeDrop     bit = 1
        ,@IncludeFillFactor   bit = 1
        ,@IncludeCompression bit = 1
        ,@destinationparameter NVARCHAR(1000) = NULL
        -- 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)
                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)
                        SET @sCreateSql = @sCreateSql + 'UNIQUE '
                --END IF
                -- Check if the index is clustered
                IF (IndexProperty(@TableId, @IndexName, 'IsClustered') = 1)
                        SET @sCreateSql = @sCreateSql + 'CLUSTERED '
                --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],
                          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)
                        IF (@IxFirstColumn = 1)
                                SET @IxFirstColumn = 0
                                --check to see if it's an included column
                                IF (@IxIsIncl = 0) AND (@IxIncl = 1)
                                        SET @IxIsIncl = 1
                                        SET @sCreateSql = @sCreateSql + @NewLine + ')' + @NewLine + 'INCLUDE' + @NewLine + '(' + @NewLine
                                        SET @sCreateSql = @sCreateSql + ',' + @NewLine
                                --END IF
                        --END IF
                        SET @sCreateSql = @sCreateSql + @Tab + '[' + @IxColumn + ']'
                        -- check if ASC or DESC
                        IF @IxIsIncl = 0
                                IF @Desc = 1
                                        SET @sCreateSql = @sCreateSql + ' DESC'
                                        SET @sCreateSql = @sCreateSql + ' ASC'
                                --END IF
                        --END IF
                        FETCH NEXT
                         FROM IndexColumns_cursor
                         INTO @IxColumn, @IxIncl, @Desc
                --END WHILE
                CLOSE IndexColumns_cursor
                DEALLOCATE IndexColumns_cursor
                SET @sCreateSql = @sCreateSql + @NewLine + ') '
                IF (@IncludeFillFactor = 1) OR (@IncludeCompression = 1)
                        SET @sCreateSql = @sCreateSql + @NewLine + 'WITH (' 
                IF @IncludeFillFactor = 1
                        SET @sCreateSql = @sCreateSql  + 'FillFactor = ' + Cast(@FillFactor as varchar(13)) 
                    IF @IncludeCompression = 1
            IF @IncludeFillFactor = 1
            SET @sCreateSql = @sCreateSql + ','
                        SET @sCreateSql = @sCreateSql  + 'DATA_COMPRESSION = PAGE'
                     IF (@IncludeFillFactor = 1) OR (@IncludeCompression = 1)
                        SET @sCreateSql = @sCreateSql  + ')' + @NewLine
                --END IF
                IF @IncludeFileGroup = 1
                        SET @sCreateSql = @sCreateSql + 'ON ['+ @FileGroupName + ']' + @NewLine
                        SET @sCreateSql = @sCreateSql + @NewLine
                --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
                        PRINT @sDropSql
                        PRINT 'GO'
                       SET @SQLOutput = @SQLOutput + @sDropSql + @NewLine
                       SET @SQLOutput = @SQLOutput + 'GO' + @NewLine 
                --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 WHILE
        CLOSE Indexes_cursor
        DEALLOCATE Indexes_cursor
        -- @SQLOutput contains the output to place in a file
    IF LEN(@destinationparameter) > 0
      DECLARE @destinationfile varchar(500)
      DECLARE @destinationpath NVARCHAR(1000)
      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'
        --PRINT @SQLOutput

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

    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

    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'

    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

    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.

    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

    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 ...