This T-SQL Tuesday is about PowerShell and how I use it with SQL Server. There are many more posts to come about PowerShell and SQL Server but I thought I would cut my teeth on this T-SQL Tuesday since it was on PowerShell.
If you read Rob Farley’s (b | t) post on the SMO topic, I am going to extend it a little and illustrate how you can get a little better TSQL out of SMO by using some things built into the SMO Server object.
The Server object contains a lot of properties and methods, but this post will focus on one called SetDefaultInitFields. This method takes an object and a list of properties that should be retrieved initially when the object properties are accessed. This allows much more efficiency when dealing with collections of items, such as Databases, Tables and Columns.
The code will retrieve me a list of databases with their PageVerify setting and the screenshots will show you what the Profiler output looks like with and without using the Server.SetDefaultInitFields method. Nothing rocket-science, but it will help illustrate that you can help SMO out in the efficiency department.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
# This will get the Server object from the SQL Provider # either by using Import-Module SQLPS with SQL 2012 installed # or by using Add-PSSnapin SqlServerProviderSnapin100 with 2008/R2 installed # or by using SQLPS.exe with 2008/R2 installed # If you load the Module SQLPS with 2012, you don't have to load the # SMO Assembly for Microsoft.SqlServer.Smo # Execute each set in a new PowerShell window to see the same information # This set will get the list of databases, but will execute the query # to get the PageVerify for each database (plus much more information) $server= Get-Item SQLSERVER:\SQL\localhost\default $server.Databases | Select Name, PageVerify # This set will get the list of databases and when it does, it will get them with 1 query # along with the PageVerify at the same time $server= Get-Item SQLSERVER:SQLlocalhostdefault $server.SetDefaultInitFields([Microsoft.SqlServer.Management.Smo.Database], "PageVerify") $server.Databases | Select Name, PageVerify |
Here are the screenshots that show the output in Profiler for each query. Notice that you will see the first set of PowerShell actually gets the data for each database one at a time. With the second set, you see one query. The reason is because of SetDefaultInitFields on the Server Object.
First screenshot, shows the retrieval of the Name of the database for one database and you get the CompatibilityLevel for free as SMO will interrogate that automatically for it’s own internal purposes. But you will see that the second screenshot will have a lot more information that it retrieves, just because you asked for one of the properties in a group that SMO has defined. It retrieves it all. Notice on each of these, that it is specifically for the database named ‘Bob1’ and not for any other. Each database will have these 2 queries run for it, and without any help it could be a long process to get 1000 database attributes.
Now in the third screenshot you will see that using the second piece of PowerShell in the code, that it issues 1 query and gets all the information in one round trip to the server. Look Mom, no WHERE clause. Pretty efficient.
Hopefully this gave you a taste of what you can do to help SMO be a little more efficient and return with less round trips to the server. Do not underestimate the power of SetDefaultInitiFields.
Have a great T-SQL Tuesday.
Ben Miller
Very cool. Thanks for that. 🙂