Moving Indexes with Powershell and SMO

  • Comments posted to this topic are about the item Moving Indexes with Powershell and SMO


  • I work as a SQL Server contractor/consultant, typically three to six month assignments at companies having "problems with the databases" so I've seen use of SQL Server at many companies. (I am also a mother-in-law, and my son and daughter-in-law have zero interest in programming.) I am responsible for knowledge transfer alongside fixing the problems. The source of many problems is application of a hodgepodge of techniques when straightforward T-SQL scripts referencing linked servers does the job more simply (and with better performance, although that matters less with 64-bit hardware). When a task does not involve file or Windows server operations, why use Powershell? Generally, programmers can maintain others' T-SQL better than they can use and maintain others' Powershell. Maintainability better serves a company than an extra shot of clever programming. It's important that programmers and the companies that employ them remember that SQL is the only language that can access database objects including indexes, however the SQL is contained in another language. So start from inside the database -- the system views -- and use T-SQL to generate SQL.

    declare @srvrs table (srvrName sysname, loopHasProcessed char(1) null)

    insert into @srvrs (srvrName)

    select name from sys.servers

    select 'I can query ' + srvrName

    from @srvrs

    "Look, those sheep have been shorn."
    data analyst replies, "On the sides that we can see.."

  • Zach,

    Interesting approach but is there a way to script the commands out as CREATE INDEX...WITH (DROP_EXISTING=ON) ON [FILEGROUP] instead of dropping the indexes first and then creating the new ones? I explored drop & create vs. create with drop_existing in this blog post and found that drop & create is 2-3X costlier in reads, CPU, and duration.

    Kendal Van Dyke[/url]

  • Very interesting Kendall - I was focusing on simplicity I suppose, I will try that out and re-post the new script if its possible.

    katesl - The simple reason to use powershell is that I can easily script out or move the indexes across 20 servers hosting 500 databases if I want. Its much more complex IMO to do that with TSQL, linked servers or OSQL. If you are talking one database, I concur, using this tool buys you nothing. I have a hard time believing Linked Servers(especially when dealing with 2000,2005,2008) would out perform this technique, but I have no proof. I don't consider this a knowledge transfer item, its a one time tool.


  • Nice article.

    I think the power of PowerShell comes not with handling tasks like this once, but with the reuse that can come with CMDLETs, across multiple systems and across time.

    I'd argue that using scripts you build from T-SQL is potentially as problematic as anything else. It is likely easier to read T-SQL now, but MS is hoping that changes over time as people get more used to Powershell. Better documentation, and most of this code has good docs, also help.

  • As a programmer turned DBA, I have two comments.

    First, your "Invoke SQL" subroutine should take an existing connection and a query. I don't know how PowerShell does with connection pooling, but recreating a connection for every index you move can slow things down if your connection aren't being pooled for some reason. Ideally, since you know you're doing a load of work all at once, you'd want to create a single connection to the server and re-use it for each sql statement to that server.

    Secondly, have your "Invoke SQL" routine return the dataset instead of the first table. This makes your script a little more flexible and a little more bulletproof. If that first table doesn't exist you can record an error rather than just waiting for the script to go "Boom!".

  • Good Point on pooling - I will make a note to work on an implementation of that.


  • Very nice article on PS and T-SQL. I've started using PS for many SQL tasks and just love it. One thing that I've implemented at my location is SQLPSX which are very useful and makes working with SQL Server through PS easy.

    One thing I noticed on in your script was how you created your file names, by appending each date object. A way to simplify this would be to use the format option on when you assign the date to your variable. It would be something like $Date = get-date -Format yyyyMMddmmss then you just append the $Date variable and done.


  • Funny, I saw the other day how to name the file much simpler than the hokey way I did - I think it was on Power Tip of the day. Good idea for sure.


  • Nice Article - thanks

    Jason...AKA CirqueDeSQLeil
    I have given a name to my pain...MCM SQL Server, MVP
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Helpful indeed.

    However, wouldn't it be even more helpful if there was an equivalent of sp_helptext for indexes and tables...

  • >>wouldn't it be helpful

    It takes a few minutes to write exactly the helpful query you need on tables and indexes, no need to wish

    Use this query to identify which columns in a table must have values supplied, and the dataypes of those columns,

    also to see the defaults that will be supplied for columns when you rely on default value.

    declare @tblname sysname

    set @tblname =


    print @tblname

    select left(,30) as columnName

    -- + ','

    , left(,10) as datatype

    , sc.max_length

    , sc.is_identity

    , sc.is_computed

    , sc.is_nullable

    , case when is null then 0 else 1 end as has_default

    , cast(left(isnull(sdc.definition,''),12) as varchar(12)) as defaultValue

    ,cast(sc.is_identity as int)+cast(sc.is_computed as int) + cast(sc.is_nullable as int) +

    case when is null then 0 else 1 end

    as valueIsSupplied

    from sys.columns sc

    join sys.types st

    on st.user_type_id = sc.user_type_id

    left join sys.default_constraints sdc

    on sc.object_id = sdc.parent_object_id

    and sc.column_id = sdc.parent_column_id

    where sc.object_id in (select object_id from sys.objects where type = 'u' and name = @tblname)

    order by valueIsSupplied

    , sc.column_id

    "Look, those sheep have been shorn."
    data analyst replies, "On the sides that we can see.."

  • RNNR ?

  • RichB (10/1/2010)

    Helpful indeed.

    However, wouldn't it be even more helpful if there was an equivalent of sp_helptext for indexes and tables...

    This sounds a bit off topic from what the discussion was about. But in any case, check out sp_help for tables and Kim Tripp's sp_helpindex8 for indexes.

    Kendal Van Dyke[/url]

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply