September 29, 2010 at 10:54 pm
Comments posted to this topic are about the item Moving Indexes with Powershell and SMO
Cheers
http://twitter.com/widba
http://widba.blogspot.com/
September 30, 2010 at 2:55 am
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.."
September 30, 2010 at 7:23 am
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.
September 30, 2010 at 7:52 am
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/
September 30, 2010 at 9:34 am
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.
September 30, 2010 at 10:40 am
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!".
September 30, 2010 at 10:50 am
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/
September 30, 2010 at 1:58 pm
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
September 30, 2010 at 2:47 pm
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/
September 30, 2010 at 6:53 pm
Nice Article - thanks
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
October 1, 2010 at 5:00 am
Helpful indeed.
However, wouldn't it be even more helpful if there was an equivalent of sp_helptext for indexes and tables...
October 1, 2010 at 5:24 am
>>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 =
'user‘
print @tblname
select 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 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.."
October 4, 2010 at 6:39 am
RNNR ?
October 4, 2010 at 7:29 am
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.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply