Search results matching tags 'PowerShell' and 'partitioning'http://sqlblog.com/search/SearchResults.aspx?o=DateDescending&tag=PowerShell,partitioning&orTags=0Search results matching tags 'PowerShell' and 'partitioning'en-USCommunityServer 2.1 SP2 (Build: 61129.1)Updated Warehouse Re-Index Scripthttp://sqlblog.com/blogs/merrill_aldrich/archive/2013/01/14/updated-warehouse-re-index-script.aspxTue, 15 Jan 2013 00:30:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:47158merrillaldrich<p>As I talked about in my <a href="http://sqlblog.com/blogs/merrill_aldrich/archive/2013/01/13/reindexing-check-your-dop.aspx">last post</a>, I just went through a re-indexing project that took the partitioned fact rows from our warehouse and relocated them into new files. There are a lot of tables and indexes involved, so I have a PowerShell “helper” script to generate the actual T-SQL that moves the data. The idea is to find all the indexes that use a particular partition scheme in the database, and make the CREATE INDEX statements that would recreate them on the new partition scheme. This script doesn’t do the re-indexing work, it just outputs the T-SQL that would do the work, so that the SQL can be verified and/or edited and run later, in the context of other modifications to the warehouse.</p> <blockquote> <p>(This is a much improved version of the work described in this <a href="http://sqlblog.com/blogs/merrill_aldrich/archive/2009/08/09/hexagonal-close-packing-for-your-fact-data.aspx">even older post</a>. It frankly wasn’t all that great back then.)</p> </blockquote> <p>The script was developed on PowerShell 2 and SQL Server 2012 client tools against SQL Server 2005, but I believe the work that the script does (the process of generating the T-SQL and the results) is compatible with any SQL Server EE 2005 or later. It assumes you’ve loaded the SQL provider or SQLPS module into PowerShell.</p> <pre> &lt;#
PowerShell Script to generate CREATE INDEX statements from existing indexes
mapped to a new partition scheme.
14 Jan 2013 Merrill Aldrich
Free for your use, but without warranty. Use at your sole risk.
#&gt;
$ErrorActionPreference = 'Stop'
# Create a connection to the SQL Server instance
Set-Location SQLSERVER:\SQL\&lt;yourserver&gt;\DEFAULT\
# Names of existing and new partition schemes. New will replace existing in script output:
$oldPartitionScheme = 'nameOfOldScheme'
$newPartitionScheme = 'nameOfNewScheme'
# Compose a query that will list out the tables that use a specific partition scheme
$partitionedTables = @( Get-ChildItem .\Databases\&lt;yourdatabase&gt;\tables |
Where-Object -filter { $_.PartitionScheme -eq $oldPartitionScheme } )
# For each partitioned table, script out create statements for partitioned indexes
$scripter = New-Object ('Microsoft.SqlServer.Management.Smo.Scripter') ( get-item ( get-location ) )
# Set scripting options here as needed
# $scripter.Options.&lt;someoption&gt; = &lt;somevalue&gt;
$scripter.Options.ScriptDrops = $False
$partitionedTables | foreach {
# Note current table in output:
&quot;/*** Table $($_.Schema).$($_.Name) ***/&quot;
$partitionedIndexes = ( $_.indexes |
Where-Object -Filter { $_.IsPartitioned -eq $True -and $_.PartitionScheme -eq $oldPartitionScheme }
)
# Script create statements
$partitionedIndexes | foreach {
$indexCreateScr = $scripter.Script( $_ )
# Change the partition scheme for every statement to the new partition scheme
$indexCreateScr = $IndexCreateScr -replace $oldPartitionScheme, $newPartitionScheme
# Change ALTER TABLE ADD CONSTRAINT to CREATE INDEX statements for PKs
$indexCreateScr = $indexCreateScr -replace `
'ALTER TABLE (\[[^\]]*\])\.(\[[^\]]*\]) ADD CONSTRAINT (\[[^\]]*\]) PRIMARY KEY CLUSTERED', `
('/* $&amp; */' + &quot;`n&quot; + 'CREATE UNIQUE CLUSTERED INDEX $3 ON $1.$2')
# For ALTER TABLEs the DROP_EXISTING index option has to be added to the script
# Find any &quot;WITH (&quot; clause not containing the DROP_EXISTING option, and add it
$indexCreateScr = $IndexCreateScr -replace 'WITH \((?!.*DROP_EXISTING)', 'WITH ( DROP_EXISTING = ON, '
# Change index create options, if necessary. Edit to suit:
$indexCreateScr = $IndexCreateScr -replace 'PAD_INDEX = OFF, ', ''
$indexCreateScr = $IndexCreateScr -replace 'STATISTICS_NORECOMPUTE = OFF, ', ''
$indexCreateScr = $IndexCreateScr -replace 'IGNORE_DUP_KEY = OFF, ', ''
$indexCreateScr = $IndexCreateScr -replace 'SORT_IN_TEMPDB = OFF, ', ''
$indexCreateScr = $IndexCreateScr -replace 'ONLINE = OFF', 'ONLINE = ON'
$indexCreateScr = $IndexCreateScr -replace 'DROP_EXISTING = OFF', 'DROP_EXISTING = ON'
# Insert some line terminators for legibility
$indexCreateScr = $IndexCreateScr -replace &quot;ON \[$($newPartitionScheme)\]&quot;, &quot;`nON [$($newPartitionScheme)]&quot;
$indexCreateScr = $IndexCreateScr -replace ', ', &quot;, `n`t&quot;
# Output the revised script
$indexCreateScr
&quot;GO`n&quot;
}
}</pre>
<p>Most of this is pretty basic – put the tables into a collection, loop over them, and for each table go through each partitioned index, scripting them out. The only parts that were tricky to develop involve the indexes that support primary keys.</p>
<p>The scripter from SMO wants to script out a PK as “ALTER TABLE … ADD CONSTRAINT,” and the problem with that is you can’t use it to <em>recreate</em> the existing index using the DROP_EXISTING option. But, in fact, in SQL Server it is perfectly valid to do a CREATE INDEX … WITH ( DROP_EXISTING = ON ) against the index that supports a primary key. It’s just that the scripter isn’t designed to deal with that idea (as far as I know).</p>
<p>I searched around for some solution to this issue to no avail, but instead fell back on good old hacky find and replace. There are a few semi-hairy regex expressions in the script that locate instances of ALTER TABLE … ADD CONSTRAINT and substitute the equivalent CREATE INDEX statement, and also locate the WITH clauses that don’t have the DROP_EXISTING option, and add that. The gibberish parts of those expressions are mostly looking for object names and escaping square brackets. </p>
<p>If it’s not clear what this sort of thing means <font face="Courier New">(\[[^\]]*\])\.(\[[^\]]*\])</font> drop me a comment and I’ll try to clarify. Conversely, if you are better at regex than I am, which is likely, any advice you might have to simplify this would be welcome!</p>
<p>A side note about regex and PowerShell:</p>
<p>If you are used to using regex in the find and replace dialog in SSMS / Visual Studio, it’s not the same flavor of regex that PowerShell uses, and there are some important differences:</p>
<blockquote>
<p>Tagging expressions/ Grouping is accomplished with parens (), not curlies {}</p>
<p>Substitution is done with dollar signs $1 and not backslashes \1</p>
<p>AND, epically confusing, the dollar signs in regex are not the dollar signs in PowerShell. So </p>
<pre>&quot;$1&quot; and '$1'</pre>
in a –replace expression are different in important and confusing ways. One will look for a PowerShell variable $1 and try to expand it, while the other is the regex matching group #1 and does regex replacement. </blockquote>
<p>What makes this sad is that the regex one might use in editing T-SQL in SSMS can’t be moved over to PowerShell without a lot of changes. </p>