{"id":34,"date":"2013-08-24T20:40:41","date_gmt":"2013-08-24T20:40:41","guid":{"rendered":"http:\/\/www.sqlkitten.com\/?p=34"},"modified":"2013-10-05T05:56:50","modified_gmt":"2013-10-05T05:56:50","slug":"sql-saturday-223-smo-internals-for-high-performance-powershell-with-ben-miller","status":"publish","type":"post","link":"http:\/\/www.sqlkitten.com\/?p=34","title":{"rendered":"SQL Saturday #223 &#8211; SMO Internals for High Performance Powershell with Ben Miller"},"content":{"rendered":"<p>SMO &#8211; Shared Management Objects<\/p>\n<p>Managment Studio uses SMO<\/p>\n<p>SMO\u00a0emits TSQL<\/p>\n<p>Powershell is a scripting language<\/p>\n<p>Objects &#8211; live in memory;\u00a0have properties and methods.<\/p>\n<p>How does SMO work? memory to engine and engine to memory; once the request is made there are no other requests &#8211; the data from that point in time is stored.<\/p>\n<p>Server object contains the connection information<\/p>\n<p>Default happenings &#8211;<\/p>\n<ul>\n<li>get database engine type<\/li>\n<li>compatability<\/li>\n<li>database name<\/li>\n<\/ul>\n<p><strong>Database<\/strong><\/p>\n<p>Default fields &#8211; name<\/p>\n<p>Interesting Fields &#8211;<\/p>\n<ul>\n<li>ID (group)<\/li>\n<li>Size (retrieved)<\/li>\n<li>Owner\u00a0(group)<\/li>\n<li>PageVerify\u00a0(group)<\/li>\n<li>SpaceAvailable\u00a0(retrieved)<\/li>\n<li>CompatibilityLevel\u00a0(retrieved)<\/li>\n<li>CreateDate\u00a0(group)<\/li>\n<li>Many more&#8230;<\/li>\n<\/ul>\n<p><strong>Table<\/strong><\/p>\n<p>Default fields &#8211; schema, name, ID<\/p>\n<p>Interesting Fields &#8211;<\/p>\n<ul>\n<li>IndexSpaceUsed (retrieved)<\/li>\n<li>IsSystemObject (group)<\/li>\n<li>Rowcount\u00a0(group)<\/li>\n<li>Createdate\u00a0(group)<\/li>\n<li>LastModified\u00a0(group)<\/li>\n<li>Many more&#8230;<\/li>\n<\/ul>\n<p><strong>Columns<\/strong><\/p>\n<p>Default fields &#8211; Name, ID<\/p>\n<p>Interesting Fields &#8211;<\/p>\n<ul>\n<li>DataType (group)<\/li>\n<li>Identity\u00a0(group)<\/li>\n<li>Nullable\u00a0(group)<\/li>\n<li>InPrimaryKey\u00a0(group)<\/li>\n<li>Others&#8230;<\/li>\n<\/ul>\n<p>What now?<\/p>\n<ul>\n<li>Iteration with ForEach<\/li>\n<li>GetDefaultInitFields<\/li>\n<li>SetDefaultInitFields<\/li>\n<li>Use Profieler to continue to fish<\/li>\n<li>Be specific if performance is key (one byte at a time)<\/li>\n<li>Be aware of the pipline<\/li>\n<\/ul>\n<p><strong>Load assemblies<\/strong><\/p>\n<p>Old way &#8211;<\/p>\n<p><code language=\"powershell\">[System.Reflection.Assembly]::LoadWithPartialName(\"Microsoft.SqlServer.SMO\")<\/code><\/p>\n<p>New way (sort of) &#8211;<\/p>\n<p><code language=\"powershell\">Add-Type -AssemblyName \"Microsoft.SqlServer.Smo\"<\/code><\/p>\n<p>Other new way &#8211;<\/p>\n<p><code language=\"powershell\">Add-Type -AssemblyName \"Microsoft.SqlServer.Smo, Version=11.0.0.0, Culture=Neutral,....\"<\/p>\n<p>Add-Type -AssemblyName \"Microsoft.SqlServer.SmoExtended, Version=11.0.0.0, Culture=Neutral,....\"<\/code><\/p>\n<p>Powershell memory considerations &#8211; 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.<\/p>\n<p>Different ramifications with foreach.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>SMO &#8211; Shared Management Objects Managment Studio uses SMO SMO\u00a0emits TSQL Powershell is a scripting language Objects &#8211; live in memory;\u00a0have properties and methods. How does SMO work? memory to engine and engine to memory; once the request is made &hellip; <a href=\"http:\/\/www.sqlkitten.com\/?p=34\">Continue reading <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[5,3],"tags":[],"_links":{"self":[{"href":"http:\/\/www.sqlkitten.com\/index.php?rest_route=\/wp\/v2\/posts\/34"}],"collection":[{"href":"http:\/\/www.sqlkitten.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.sqlkitten.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.sqlkitten.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.sqlkitten.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=34"}],"version-history":[{"count":5,"href":"http:\/\/www.sqlkitten.com\/index.php?rest_route=\/wp\/v2\/posts\/34\/revisions"}],"predecessor-version":[{"id":46,"href":"http:\/\/www.sqlkitten.com\/index.php?rest_route=\/wp\/v2\/posts\/34\/revisions\/46"}],"wp:attachment":[{"href":"http:\/\/www.sqlkitten.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=34"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.sqlkitten.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=34"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.sqlkitten.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=34"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}