Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Moving Indexes with Powershell and SMO Expand / Collapse
Author
Message
Posted Wednesday, September 29, 2010 10:54 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 6:58 AM
Points: 94, Visits: 580
Comments posted to this topic are about the item Moving Indexes with Powershell and SMO

Cheers
http://twitter.com/widba
http://widba.blogspot.com/
Post #995738
Posted Thursday, September 30, 2010 2:55 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, May 16, 2014 7:20 PM
Points: 63, Visits: 470
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.."
Post #995786
Posted Thursday, September 30, 2010 7:23 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 11:36 AM
Points: 361, Visits: 880
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
http://kendalvandyke.blogspot.com/
Post #995914
Posted Thursday, September 30, 2010 7:52 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 6:58 AM
Points: 94, Visits: 580
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.


Cheers
http://twitter.com/widba
http://widba.blogspot.com/
Post #995945
Posted Thursday, September 30, 2010 9:34 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 5:59 PM
Points: 31,082, Visits: 15,529
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.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #996037
Posted Thursday, September 30, 2010 10:40 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, September 26, 2014 12:13 PM
Points: 498, Visits: 977
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!".



Post #996070
Posted Thursday, September 30, 2010 10:50 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 6:58 AM
Points: 94, Visits: 580
Good Point on pooling - I will make a note to work on an implementation of that.




Cheers
http://twitter.com/widba
http://widba.blogspot.com/
Post #996074
Posted Thursday, September 30, 2010 1:58 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, September 2, 2014 8:21 AM
Points: 999, Visits: 136
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



Post #996264
Posted Thursday, September 30, 2010 2:47 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 6:58 AM
Points: 94, Visits: 580
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.



Cheers
http://twitter.com/widba
http://widba.blogspot.com/
Post #996303
Posted Thursday, September 30, 2010 6:53 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 9:28 PM
Points: 17,729, Visits: 15,597
Nice Article - thanks



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #996356
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse