SQL Saturday #223 – SMO Internals for High Performance Powershell with Ben Miller

SMO – Shared Management Objects

Managment Studio uses SMO

SMO emits TSQL

Powershell is a scripting language

Objects – live in memory; have properties and methods.

How does SMO work? memory to engine and engine to memory; once the request is made there are no other requests – the data from that point in time is stored.

Server object contains the connection information

Default happenings –

  • get database engine type
  • compatability
  • database name


Default fields – name

Interesting Fields –

  • ID (group)
  • Size (retrieved)
  • Owner (group)
  • PageVerify (group)
  • SpaceAvailable (retrieved)
  • CompatibilityLevel (retrieved)
  • CreateDate (group)
  • Many more…


Default fields – schema, name, ID

Interesting Fields –

  • IndexSpaceUsed (retrieved)
  • IsSystemObject (group)
  • Rowcount (group)
  • Createdate (group)
  • LastModified (group)
  • Many more…


Default fields – Name, ID

Interesting Fields –

  • DataType (group)
  • Identity (group)
  • Nullable (group)
  • InPrimaryKey (group)
  • Others…

What now?

  • Iteration with ForEach
  • GetDefaultInitFields
  • SetDefaultInitFields
  • Use Profieler to continue to fish
  • Be specific if performance is key (one byte at a time)
  • Be aware of the pipline

Load assemblies

Old way –


New way (sort of) –

Add-Type -AssemblyName "Microsoft.SqlServer.Smo"

Other new way –

Add-Type -AssemblyName "Microsoft.SqlServer.Smo, Version=, Culture=Neutral,...."

Add-Type -AssemblyName "Microsoft.SqlServer.SmoExtended, Version=, Culture=Neutral,...."

Powershell memory considerations – depending on what you are pulling back, and whether or not you have already retrieved the data, the time it takes for the command to be processed will vary.

Different ramifications with foreach.

Leave a Reply

Your email address will not be published. Required fields are marked *