When SQL Server creates a CLR appdomain to run code in (a runtime appdomain as noted in the previous blog entry), the appdomain normally stays in place for the lifetime of SQL Server. This is done to save appdomain create/teardown and assembly load time. Note that DDL appdomains, as opposed to runtime appdomains, are torn down immediately after they are used. A friend of mine recently wanted to shutdown an appdomain on purpose to troubleshoot a problem that he thought might have been SQLCLR-related. So how do you shutdown a runtime appdomain on purpose?

You could write a .NET proc to call AppDomain.Unload. But I shied away from this for a few reasons. You'd need to catalog the appdomain as unsafe for the proc to work, which means marking database as trustworthy or doing the 'signed assembly with key in master' dance. And I'd really prefer a way to have SQL Server gracefully shutdown the appdomain itself.

SQL Server will shutdown an appdomain for different reasons. It can shut them down under extremely low memory conditions or when there is a serious enough unhandled exceptional condition (e.g. unhandled exceptional condition that could leave .NET locks in place). We really don't want to cause either of these on purpose just to shut down an appdomain. Another reason the SQL Server will shut down an appdomain is when a loaded assembly is altered. You can use the ALTER ASSEMBLY DDL statement to replace code in place, subject to limitations. When you alter an assembly in place SQL Server recycles the appdomain to be able to use your new code. Currently executing code will continue to use the appdomain until the call completes; new requests are routed to the new appdomain (with the updated code). When all current requests against the old appdomain complete, the appdomain shuts down. Hmmm, probably not a good idea to muck with recompiling the producting code either. So...

Compile a simple do-nothing assembly with a simple do-nothing function (say, add two numbers together). We'll call the assembly 'fred' and the function 'addtwo'. The assembly must be owned by the same owner as the appdomain you want to recycle (remember runtime appdomains are on a per database and assembly owner basis). So if the appdomain we want to recycle is the 'pubs.dbo[runtime]' appdomain...

use pubs
go

create assembly fred authorization dbo ...
create function dbo.addtwo ...
use the function dbo.addtwo (this causes the assembly to be loaded)
recompile the assembly fred
alter assembly fred ... (this cause the eventual appdomain unload)

Note that you don't have to change the 'fred' assembly, only recompile it. SQL Server decides that an assembly is changed if it has a different MVID (.NET assembly module version identifier). New MVIDs are assigned each time an assembly is recompiled (note that an MVID is not the same as a four-part assembly version number). In Visual Studio, you'd recompile the assembly by using the Recompile menu entry, not the Build menu entry. If you try and run alter assembly without a rebuild you'll get the error message 'ALTER ASSEMBLY failed because the source assembly is, according to MVID, identical to an assembly that is already registered under the name "fred"'. You could conceivably use Visual Studio autodeploy for these steps as well, but Visual Studio autodeploy drops the functions and assembly and recreates them rather than using ALTER ASSEMBLY.

The next request against any of the .NET database objects will cause a new appdomain to be created.

.NET (and therefore SQLCLR) divides up running its code (even within the same process like the sqlserver.exe process) into appdomains. The appdomain is like a lightweight process used to enforce isolation between running .NET code within the same Windows process. SQLCLR (.NET code running in SQL Server) uses appdomains to isolate execution of .NET code on a per database and per assembly owner basis.

SQLCLR uses appdomains for two reasons: for running .NET user code like functions and procedures, and for running DDL to create and alter assemblies. You can see appdomains being created and destroyed in the SQL Server log, as well as query the current appdomains by using the sys.dm_clr_appdomains dynamic management view.

In SQL Server 2005, the SQL Server log only showed user code-running appdomains, the messages look like this:

AppDomain 4 (testdb.dbo[runtime].3) created.
AppDomain 4 (testdb.dbo[runtime].3) is marked for unload due to common language runtime (CLR) or security data definition language (DDL) operations.
AppDomain 4 (testdb.dbo[runtime].3) unloaded.

Is these messages there's a appdomain being created for running code in the testdb database for assemblies owned by dbo. It's a runtime appdomain. The last two messages show the appdomain (some time later) being unloaded.

In SQL Server 2005 it was difficult to "see" DDL appdomains, because their presence was not logged in the SQL Server log. You could deduce they existed by noting that the AppDomain numbers (e.g. AppDomain 4 above) seemed to contain skips in the number range. Or if you were extremely lucky, by querying sys.dm_clr_appdomains at *exactly* the right time.  In SQL Server 2008, it is. You don't see them being created, but I just executed some DDL to create a SQLCLR assembly and SQL Server log reports:

AppDomain 5 (testdb.dbo[ddl].4) unloaded.

This should make it a little easier to diagnose appdomain-related problems or see when assemblies have been created or altered.

I've been able to coordinate being home and the Portland (Oregon) SQL Server user group's meeting schedule, so next week I'll be speaking "at home" for a change. I'm giving a talk on one of my favorite features for developers in SQL Server 2008, Spatial Data support. Because it's relatively common these days to see GPS not only in cars but also in "carry along" devices, cell phones, cameras, and other hi-tech toys I don't personally possess, I called this talk "SQL Server spatial data for the masses". If you're at all interested in SQL Server or storing, processing, and visualizing spatial data and databases, I'll see you there.

WHEN:
Thursday, July 24th - 6:30 PM

WHERE:
SQLSoft+
1500 NW Bethany Blvd.
Suite 285
Beaverton, Oregon OR 97007

You may want to RVSP to Ken Starnes at kstarnes@kdsa.com so he can save you a seat.

I've noticed that some folks have written PowerShell scripts that execute against a list of servers. In the scripts, they read the names of the servers from XML files. But the SQL Server PowerShell provider in RC0 has a useful "component" called SQLRegistration; its "path" is SQLSERVER:\SQLRegistration that they can use instead.

This path permits enumeration and manipulation of the (SQL) servers and server groups that are defined using SQL Server Management Studio. The SQLRegistration path is not specific to server or instance, but to the SSMS user that's signed on. For example, on the same physical machine, SQLRegistration for the Windows user bobb reflects bobb's settings in SSMS. SQLRegistration for user mary (on the same machine) would reflect mary's SSMS settings. There is no SQLSERVER:\SQLRegistration\{machinename}\{instancename} path, just SQLSERVER:\SQLRegistration.

SQLRegistration contains two "subdirectories", called "Central Management Server Group" and "Database Engine Server Group". These are equivalent to SSMS' "Central Management Servers" and "Local Server Groups" folders, respectively.

So rather than write custom XML files that hold information about groups of servers you can use the built-in Registration store. You can import and export registration information to keep team members' view of SQL Servers and groups consistant using SSMS.

Because I've been writing PowerShell scripts against the provider's Policy and DataCollection stores, I thought it would be fun to program SQLRegistration. I found the Microsoft.SqlServer.Management.Registration namespace in BOL (which is RC0 is really called Microsoft.SqlServer.Management.Smo.Registration, BOL says this will change before RTM) and was about to code against the RegisteredServer and ServerGroup classes, when I realized that Registration was a simple hierarchies of servers and groups. Just like directories and files. And the built-in groups each have a "mode" property that's value is "d". Registered Servers have a blank mode property. Hmmm...

So, at the PowerShell prompt
> cd 'SQLSERVER:\sqlregistration\Database Engine Server Group'
> new-item MyNewGroup

creates a new directory (ServerGroup) named MyNewGroup. Well then, a registered server should be a file.

> new-item MyNewServer -itemtype file

New-Item : SQL Server PowerShell provider error: This provider only supports the
creation of new Server Groups or Server Registrations. Please specify "directory"
to create a new Server Group or "registration" to create a new Server Registration.

So this should work?

> new-item MyNewServer -itemtype registration

New-Item : SQL Server PowerShell provider error: Please specify a connection string using the -Value parameter.

> new-item MyNewServer -itemtype registration -Value "server=mynewserver;integrated security=true"
>

Yep. This creates a new server registration using the connection parameters that you specify. How cool is that? No other part of the SQL SERVER PowerShell provider that I'm aware of supports the new-item cmdlet/operation. Because the SQLRegistration represent fairly simple items and hierarchies new-item is supported here. No custom programming needed (although you can use the SMO classes if you'd rather), just treat it as you would file system.

A couple of caveats. In RC0 SSMS and PowerShell SQLRegisgtration sync up when you open SSMS but if you're using both at once, the sync to SSMS isn't immediate. Sometimes refresh doesn't appear to show the changes you made in PowerShell. Eventually it will show them if you refresh multiple times or restart SSMS. And after deleting a Server Registration in SSMS, I still had to run "rm MyNewServer" in PowerShell to get rid of it there. Also, I use "integrated security=sspi" rather than "integrated security=true" in my ADO.NET connection strings. This seems to bother SSMS which reports an error in configuration. Use "true" rather than "sspi" when creating registrations through PowerShell.

I received a question today about whether I'd converted my Policy-Based Management examples using SMO (see the multi-part "Programming Policy-Based Management with SMO" series, starting here) from C# to PowerShell yet. I did do this a while ago; they're available as a script download on the SQLskills website (look on the "Past Conferences" page under TechEd 2008). But...

Since then RC0 has changed PBM a bit. The multipart name policy can't use ExecutionMode.Enforce any more, so I changed it to ExecutionMode.None which equates to "On Demand" in the SSMS dialog. There was a change to one of the enumerated constant names too. So the updated scripts for RC0 are posted as part of this blog entry.

As long as I was working on parts of the PowerShell provider for SQL Server and SMO, I decided to put together an example of using the DataCollection APIs as well. The script creates a custom DataCollection Collection Set. That's pretty straightforward. Using the CollectionStore instance just use the correct location in the provider hierarchy and get a CollectionStore instance.

# set a collectionstore object for the default instance on local machine
$colpath = ('SQLSERVER:\DataCollection\' + (get-item env:\computername).Value + '\default')
$col = get-item $colpath

Then create a CollectionSet instance with (your CollectionStore $col is specifed as a parameter to new-object) and one or more CollectionItems in the CollectionSet. Then set the CollectionSet and CollectionItem proprties and call Create. This script enclosed as well, named CreateCustomCollectionSet.ps1.

As an aside, I've enclosed a script called start_smo_sql.ps1. This is a script that I call at PowerShell startup if I know the SQL Server provider is already installed. It sets convenience variables for long SMO namespace names, paths to the default Server, CollectionStore, etc. It's not invoked from my main PowerShell profile %UserProfile%\My Documents\WindowsPowerShell\profile.ps1 which is executed by all PowerShell shells/this user. Instead it's invoked from %UserProfile%\My Documents\WindowsPowerShell\Microsoft.SqlServer.Management.PowerShell.sqlps_profile.ps1. This profile is only executed when I'm using SQLPS.exe, the SQL Server-specific custom shell. It's quite cool that PowerShell accomodates separate profiles for custom shells.

The toughest part in creating a custom collection set is that the XML schema for collection set properties has been updated in one of the CTPs, but not updated in Books Online RC0. The updated XML schema can be obtained by executing:

use msdb
select * from syscollector_collector_types

and using the parameter_schema column to get the schema you need to figure out how to create a validatable parameter. My T-SQL custom collection set used a parameter with an XML namespace on the root element (only) that looked like this:

<ns:TSQLQueryCollector xmlns:ns="DataCollectorType">
        <Query>
          <Value>select * from sys.dm_exec_query_stats</Value>
          <OutputTable>dm_exec_query_stats</OutputTable>
        </Query>
</ns:TSQLQueryCollector>

Last week at TechEd Developers, I gave a talk on PowerShell and SQL Server. I mentioned some upcoming changes in RC0, and have just had a chance to check them out.

The PowerShell provider for SQL Server has been expanded to handle not only a "SQL" subdirectory (which enumerates database objects) and "SQLPolicy" (which enumerates the policy-based management objects) but also two new "directories": SQLRegistration and DataCollection.

SQLRegistration covers the groups and members of "Registered Servers" and before you ask, yes this is the same Registered Servers that show up in SQL Server Management Studio. So if you're managing hundreds of servers, you can easier script against any server or group of "Registered Servers". You don't have to navigate to a different machine and instance by referencing the name, as I described in an earlier blog entry. Be aware that the registration service takes a while to refresh registrations. If you define a new registered server in SSMS, it won't show up in the PowerShell list immediately. Just wait a minute or so...

The DataCollection "subdirectory" allows you to enumerate the database objects and properties for the data collection feature that is used to populate and control the performance data warehouse in SQL Server 2008. You navigate through these collections/values the same way you'd navigate SQL and SQLPolicy. This one isn't doc'd as being part of the PowerShell provider in  BOL yet.

A couple of other miscellanous changes I noticed:
1. There are no longer separate custom drives for SQL and SQLPolicy as in previous versions. If you liked these custom drives its easy to add them:
New-PSDrive -Name SQL -Root SQLSERVER:\SQL
New-PSDrive -Name SQLPolicy -Root SQLSERVER:\SQLPolicy
..etc

2. Collection Names are no longer case-sensitive. So:

dir SQLServer:\SQL\{Computer}\{Instance}\databases

(where you replace {Computer} and {Instance} with real names) works now with "databases" being case insensitive.

SQL Server 2008 RC0 comes with a Readme file and a Release Notes file. It's always good to read both. In this RC, the readme file contains information on installation and upgrade, and the Release Notes file contains information about things that have changed from previous releases.

In RC0, they've changed the syntax/format of a few relevent new features. The changes affect T-SQL MERGE, the HierarchyID data type's methods, and the Geometry data type's Latitude/Longitude order. These won't be doc'd in BOL under What's New, because the What's New section lists new features, not features that have changed since the last CTP. Reading up on features that have changed will (hopefully) reduce the number of "why doesn't this code example that I downloaded from a reputable source work?" questions.

In addition to the two new performance features I just blogged about, RC0 also contains the new "Spatial Builder" API. Isaac has an excellent blog post about this API, with a (now-)working example. Check out his blog, I didn't find this feature mentioned in either "What's New" or "Release Notes".

There's a newer version of SQL Server 2008 (Release Candidate 0) available on MSDN and Technet.

Another performance-related feature (actually its additional information) allows you to have better visibility into performance-affecting queries. This information is available as columns query_hash and query_plan_hash in the sys.dm_exec_query_stats and sys.dm_exec_requests DMV. There's a really nice illustrative example in Books Online, so I won't repeat it here. You can use the queries in my last blog post to experiment with this as well.

The queries:

SELECT title_id, sum(qty) from sales
group by title_id
having sum(qty) = 30

and

SELECT title_id, sum(qty) from sales
group by title_id
having sum(qty) = 40

are similar enough that they could be parameterized (although they aren't autoparameterized). Parameterization would save query plan entry storage, rather than allocating a separate plan for each incantation of the similar query. You can also use this feature, along with the additional information in sys.dm_exec_query_stats to produce cumulative statistics (such as avg IOs, elasped time, etc. BOL has a nice query to accumulate similar plans, the relavent part is simply "GROUP BY query_hash".

But because you have similar queries, should you always have a parameterized query? What if "...having  sum(qty) = 30" has a completely different plan than "...having sum(qty) = 130"? You can find this information by looking at query_plan_hash. If two plans have similar structure but a different query plan, the query_hash value will be identical but query_plan_hash will be different. That's an indication that parameterization might not be the way to go; the first query to executed will cache it's plan and the other query will use the same plan. This is because of a SQL Server behavior known as parameter sniffing.

Because parameterizing queries is usually one of the first things programmers can do to affect performance, it helps to know when to parameterize. In addition, many folks like to start query tuning by tuning the queries (including similar queries with different parameter values) that are executed most frequently. Query_plan_hash and query_hash gives you visibility into this important information.

There's a newer version of SQL Server 2008 (Release Candidate 0) available on MSDN and Technet.

There's a couple of features that show up in RC0 that are performance related. One is a server setting 'optimize for ad hoc workloads'. This one tells SQL Server to save a "compiled plan stub" in the query cache for adhoc queries, rather than the query plan that's usually saved.

You can see these stubs as cacheobjtype 'Compiled Plan Stub' in sys.dm_exec_cached_plans. And you can see them because trying to resolve a plan_handle to a query plan (using sys.dm_exec_cached_plans or sys.dm_exec_query_stats) doesn't work for stubs, like it does for "real" plan handles. Saving a stub rather than the entire plan saves precious plan cache memory.

Here's a little script to try it out.

sp_configure 'show advanced options',1
reconfigure
go
sp_configure 'optimize for ad hoc workloads',1
reconfigure
go
-- don't use on a production system, clears whole query cache
DBCC FREEPROCCACHE
go
use pubs
go
-- background plans to produce this DMV, note there is a stub for one of these plans
select * from sys.dm_exec_cached_plans
go
-- not autoparameterized, stub produced
SELECT title_id, sum(qty) from sales
group by title_id
having sum(qty) = 30
go
-- you can see the stub
select * from sys.dm_exec_cached_plans
go
-- autoparameterized, whole query plan for parameterized version
SELECT *
FROM titles
WHERE price = 19.99
go

-- stub for the non-parameterized version, plan for the parameterized version
SELECT usecounts, cacheobjtype, objtype, [text]
FROM sys.dm_exec_cached_plans P
   CROSS APPLY sys.dm_exec_sql_text(plan_handle)
   WHERE [text] NOT LIKE '%dm_exec%'
ORDER BY p.usecounts DESC
-- query plan for stub query handle is not saved returns NULL
SELECT sql.text, p.query_plan
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) sql
CROSS APPLY sys.dm_exec_query_plan(plan_handle) p
WHERE text NOT LIKE '%sys_dm_exec%' AND text NOT LIKE '%msparam_0%'
ORDER BY qs.EXECUTION_COUNT DESC
go

-- set it back, same experiment produces plans not stubs
sp_configure 'show advanced options',1
reconfigure
go
sp_configure 'optimize for ad hoc workloads',0
reconfigure
go

In a blog posting about a few weeks ago, I'd written about noticing a DataSet being created in an Entity Framework program by using the ADO.NET client trace facility. Entity Framework programs do (indirectly) cause a DataSet to be created, but its only for resolving the ProviderInvariantName of the underlying data provider. NOT for anything related to the functioning of the EntityClient or ObjectContext in any way. I've revised the original blog posting to remove the DataSet reference, but just in case you already read it....

The connection string for the EntityClient data provider contains a parameter that refers to the ProviderInvariantName of the underlying data provider (e.g. System.Data.SqlClient). Entity Framework validates the underlying data provider name by using the ADO.NET provider enumerator class DbProviderFactories. DbProviderFactories creates the DataSet to store the provider information obtained from the machine.config file.

I might have guessed this...as I'd written a whitepaper on the functionality of the ADO.NET provider model, "Generic Coding with the ADO.NET 2.0 Base Classes and Factories" not more than a few years ago. Using the DbProviderFactories class is the only way to enumerate the ADO.NET data providers installed on your machine.

Come to my "End-To-End Tracing with SQL Server 2008 and ADO.NET" talk at TechEd next week and I'll show you the trace.