SQLServerCentral / Discuss Content Posted by Zach Mattson / Article Discussions / Article Discussions by Author / Moving Indexes with Powershell and SMO / Latest Posts

RE: Moving Indexes with Powershell and SMO

Kendal Van Dyke — Mon, 04 Oct 2010 07:29:40 GMT

[quote][b]RichB (10/1/2010)[/b][hr]Helpful indeed.However, wouldn't it be even more helpful if there was an equivalent of sp_helptext for indexes and tables...[/quote]This sounds a bit off topic from what the discussion was about. But in any case, check out [url=http://msdn.microsoft.com/en-us/library/ms187335.aspx]sp_help[/url] for tables and Kim Tripp's [url=http://www.sqlskills.com/BLOGS/KIMBERLY/post/A-new-and-improved-sp_helpindex-(jokingly-sp_helpindex8).aspx]sp_helpindex8[/url] for indexes.

RE: Moving Indexes with Powershell and SMO

matt.birchall-942349 — Mon, 04 Oct 2010 06:39:48 GMT

RNNR ?

RE: Moving Indexes with Powershell and SMO

katesl — Fri, 01 Oct 2010 05:24:05 GMT

>>wouldn't it be helpfulIt takes a few minutes to write exactly the helpful query you need on tables and indexes, no need to wishUse 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 sysnameset @tblname ='user‘ print @tblnameselect left(sc.name,30) as columnName-- + ',', left(st.name,10) as datatype, sc.max_length, sc.is_identity, sc.is_computed, sc.is_nullable, case when sdc.name 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 sdc.name is null then 0 else 1 end as valueIsSuppliedfrom sys.columns scjoin sys.types ston st.user_type_id = sc.user_type_idleft join sys.default_constraints sdcon sc.object_id = sdc.parent_object_idand sc.column_id = sdc.parent_column_idwhere sc.object_id in (select object_id from sys.objects where type = 'u' and name = @tblname) order by valueIsSupplied, sc.column_id

RE: Moving Indexes with Powershell and SMO

RichB — Fri, 01 Oct 2010 05:00:51 GMT

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

RE: Moving Indexes with Powershell and SMO

SQLRNNR — Thu, 30 Sep 2010 18:53:09 GMT

Nice Article - thanks

RE: Moving Indexes with Powershell and SMO

WI-DBA — Thu, 30 Sep 2010 14:47:38 GMT

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.

RE: Moving Indexes with Powershell and SMO

jdorameqes — Thu, 30 Sep 2010 13:58:02 GMT

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. http://sqlpsx.codeplex.com/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.--JD

RE: Moving Indexes with Powershell and SMO

WI-DBA — Thu, 30 Sep 2010 10:50:07 GMT

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

RE: Moving Indexes with Powershell and SMO

aureolin — Thu, 30 Sep 2010 10:40:13 GMT

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

RE: Moving Indexes with Powershell and SMO

Steve Jones - SSC Editor — Thu, 30 Sep 2010 09:34:58 GMT

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.

RE: Moving Indexes with Powershell and SMO

WI-DBA — Thu, 30 Sep 2010 07:52:19 GMT

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.

RE: Moving Indexes with Powershell and SMO

Kendal Van Dyke — Thu, 30 Sep 2010 07:23:47 GMT

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 [url=http://kendalvandyke.blogspot.com/2010/09/index-operations-showdown-drop-create.html]in this blog post[/url] and found that drop & create is 2-3X costlier in reads, CPU, and duration.

RE: Moving Indexes with Powershell and SMO

katesl — Thu, 30 Sep 2010 02:55:16 GMT

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.serversselect 'I can query ' + srvrName from @srvrs

Moving Indexes with Powershell and SMO

WI-DBA — Wed, 29 Sep 2010 22:54:15 GMT

Comments posted to this topic are about the item [B]Moving Indexes with Powershell and SMO[/B]