0

A. Is there the possibility to get the result of ps-script:

$ss=New-Object Microsoft.SqlServer.Management.Smo.Server 'MyServer';
$ss.Databases[$db].Tables |  Where-Object  {$_.IsIndexable -eq $true}

with the comparable performance to the select

SELECT * FROM sys.Tables WHERE OBJECTPROPERTY(object_id, 'IsIndexable')=1

Of course one way is to run select (from ps) and then instantiate the bunch of smo.table by names (or object_id), something like: 

$d = $ss.Databases[$db]
$r = $d.ExecuteWithResults('SELECT object_id FROM sys.tables WHERE OBJECTPROPERTY(object_id, ''IsIndexable'')=1');
$t = @()
foreach ($i in $r.Tables[0].Rows) {$t+=$d.Tables.ItemById($i["object_id"])}

but this is doesn't look "how it should be"..

B. How to improve performance of indexes filter?

$i=$d.Tables.Indexes | where {$_.HasFilter -eq $true};

Of course the same result is possible to achieve quick just but sql: 

$d = $ss.Databases[$db]
$r = $d.ExecuteWithResults('SELECT object_id, Name FROM sys.indexes WHERE has_filter=1 ORDER BY object_id, Name');
$t = @()
foreach ($i in $r.Tables[0].Rows) {$t+=$d.Tables.ItemById($i["object_id"]).Indexes[$i["Name"]]}

but I think there should be better solution..

Roman Pokrovskij
  • 9,449
  • 21
  • 87
  • 142

1 Answers1

2

One thing that will speed up your code is to tell the server object that you care about the IsIndexable property on tables, so when you fetch a table object it should grab that property. You can follow the process on this BOL article, but it boils down to:

$ss=New-Object Microsoft.SqlServer.Management.Smo.Server 'MyServer';
$tableType = (new-object Microsoft.SqlServer.Management.Smo.Table).getType();
$ss.SetDefaultInitFields($tableType, "IsIndexable");
$ss.Databases[$db].Tables |  Where-Object  {$_.IsIndexable -eq $true}

Note that in the pointed to BOL article, they save off the default set of properties and restore it when they're done. Whether you care to do that or not is, well, up to you.

Ben Thul
  • 31,080
  • 4
  • 45
  • 68
  • Hi, Ben. Do we have a possibility to speed up such search: $i=$d.Tables.Indexes | where {$_.HasFilter -eq $true}? That means how to "preload all indexes"?. $s.SetDefaultInitFields((new-object ('Microsoft.SqlServer.Management.Smo.Index')).getType(), 'HasFilter') works but do only part of job.. – Roman Pokrovskij Feb 04 '14 at 20:18
  • $s.SetDefaultInitFields((new-object 'Microsoft.SqlServer.Management.Smo.Index')).getType(), 'HasFilter') would have been my first attempt. In what way does this not work for you? – Ben Thul Feb 05 '14 at 00:19
  • Still too long (compare to select from sys.indexes). As I said it doesn't "preload" indexes therefore during iteration foreach table we produce new request "get all indexes". – Roman Pokrovskij Feb 05 '14 at 06:58
  • $d.Tables.Indexes produces no results as a TableCollection doesn't have a property called Indexes. Perhaps it's time to start a new thread detailing what you're actually trying to accomplish. – Ben Thul Feb 05 '14 at 19:34
  • I want to know is it possible to filter indexes quick? This is slow: $i=$d.Tables.Indexes | where {$_.HasFilter -eq $true}; SetDefaultInitFields with index type and "IsIndexable" doesn't improove situation till acceptable duration. – Roman Pokrovskij Feb 06 '14 at 10:08