70-432 : Fixed server and database roles

For the 70-432 exam you must understand the difference between the server and the database within the server.

You will need to under the fixed server-level and fixed database-level plus the associated stored procedures.

I found the following very useful summary:

Fixed server roles: These are server-wide roles. Logins can be added to these roles to gain the associated administrative permissions of the role. Fixed server roles cannot be altered and new server roles cannot be created. Here are the fixed server roles and their associated permissions in SQL Server 2000:

Fixed server role	Description
sysadmin	Can perform any activity in SQL Server
serveradmin	Can set server-wide configuration options, shut down the server
setupadmin	Can manage linked servers and startup procedures
securityadmin	Can manage logins and CREATE DATABASE permissions, also read error logs and change passwords
processadmin	Can manage processes running in SQL Server
dbcreator	Can create, alter, and drop databases
diskadmin	Can manage disk files
bulkadmin	Can execute BULK INSERT statements

Here is a list of stored procedures that are helpful in managing fixed server roles:

sp_addsrvrolemember	Adds a login as a member of a fixed server role
sp_dropsrvrolemember	Removes an SQL Server login, Windows user or group from a fixed server role
sp_helpsrvrole	Returns a list of the fixed server roles
sp_helpsrvrolemember	Returns information about the members of fixed server roles
sp_srvrolepermission	Returns the permissions applied to a fixed server role

Fixed database roles: Each database has a set of fixed database roles, to which database users can be added. These fixed database roles are unique within the database. While the permissions of fixed database roles cannot be altered, new database roles can be created. Here are the fixed database roles and their associated permissions in SQL Server 2000:

Fixed database role	Description
db_owner	Has all permissions in the database
db_accessadmin	Can add or remove user IDs
db_securityadmin	Can manage all permissions, object ownerships, roles and role memberships
db_ddladmin	Can issue ALL DDL, but cannot issue GRANT, REVOKE, or DENY statements
db_backupoperator	Can issue DBCC, CHECKPOINT, and BACKUP statements
db_datareader	Can select all data from any user table in the database
db_datawriter	Can modify any data in any user table in the database
db_denydatareader	Cannot select any data from any user table in the database
db_denydatawriter	Cannot modify any data in any user table in the database

Here is a list of stored procedures that are helpful in managing fixed database roles:

sp_addrole	Creates a new database role in the current database
sp_addrolemember	Adds a user to an existing database role in the current database
sp_dbfixedrolepermission	Displays permissions for each fixed database role
sp_droprole	Removes a database role from the current database
sp_helpdbfixedrole	Returns a list of fixed database roles
sp_helprole	Returns information about the roles in the current database
sp_helprolemember	Returns information about the members of a role in the current database
sp_droprolemember	Removes users from the specified role in the current database

http://vyaskn.tripod.com/sql_server_security_best_practices.htm

Another blog which also covers the above is http://articles.techrepublic.com.com/5100-10878_11-1061781.html. This article had the advantage of several screenshots show how to administer security via SQL Server Management Studio (SSMS) but I just don't like an example where you allocate db_accessadmin to guest - this is just to counter intuitive!

70-432 : Locks and Latchs (sys.dm_db_index_operational_stats)

This blog article is aimed at people preparing for 70-432 exam.

The MCTS 70-432 exams introduces some of the dynamic management views (DMVs). I like the ideas of DMVs, as a seasoned Oracle DBA I know many of the key Oracle Instance and Database views well. Once of the advantages of having a SQL view (as opposed to a GUI tool) is that he DMV query can be scheduled to run periodically and log key columns over time (very important for a DBA).

Starting with the msdn documentation for dm_db_index_operational_stats we can that it can capture leaf DML operations plus lock and latch waits:

You can use sys.dm_db_index_operational_stats to track the length of time that users must wait to read or write to a table, index, or partition, and identify the tables or indexes that are encountering significant I/O activity or hot spots.

Use the following columns to identify areas of contention.

To analyze a common access pattern to the table or index partition, use these columns:

• leaf_insert_count
• leaf_delete_count
• leaf_update_count
• leaf_ghost_count
• range_scan_count
• singleton_lookup_count

To identify latching and locking contention, use these columns:

• page_latch_wait_count and page_latch_wait_in_ms
  These columns indicate whether there is latch contention on the index or heap, and the significance of the contention.
• row_lock_count and page_lock_count
  These columns indicate how many times the Database Engine tried to acquire row and page locks.
• row_lock_wait_in_ms and page_lock_wait_in_ms
  These columns indicate whether there is lock contention on the index or heap, and the significance of the contention.

To analyze statistics of physical I/Os on an index or heap partition

• page_io_latch_wait_count and page_io_latch_wait_in_ms
  These columns indicate whether physical I/Os were issued to bring the index or heap pages into memory and how many I/Os were issued.

Column Remarks

The values in lob_orphan_create_count and lob_orphan_insert_count should always be equal.

The value in the columns lob_fetch_in_pages and lob_fetch_in_bytes can be greater than zero for nonclustered indexes that contain one or more LOB columns as included columns. For more information, see Index with Included Columns. Similarly, the value in the columns row_overflow_fetch_in_pages and row_overflow_fetch_in_bytes can be greater than 0 for nonclustered indexes if the index contains columns that can be pushed off-row. For more information, see Row-Overflow Data Exceeding 8 KB.

How the Counters Are Reset

The data returned by sys.dm_db_index_operational_stats exists only as long as the metadata cache object that represents the heap or index is available. This data is neither persistent nor transactionally consistent. This means you cannot use these counters to determine whether an index has been used or not, or when the index was last used. For information about this, see sys.dm_db_index_usage_stats.

The values for each column are set to zero whenever the metadata for the heap or index is brought into the metadata cache and statistics are accumulated until the cache object is removed from the metadata cache. Therefore, an active heap or index will likely always have its metadata in the cache, and the cumulative counts may reflect activity since the instance of SQL Server was last started. The metadata for a less active heap or index will move in and out of the cache as it is used. As a result, it may or may not have values available. Dropping an index will cause the corresponding statistics to be removed from memory and no longer be reported by the function. Other DDL operations against the index may cause the value of the statistics to be reset to zero.

http://msdn.microsoft.com/en-us/library/ms174281(SQL.90).aspx

Next what is the difference between a lock and a latch? A lock (aka "enqueue") is a request for exclusive or shared owner of some data - a row,page or extent (a set of eight contiguous pages makes up an extent). Now for Oracle a latch is lock on an internal data structure in the SGA:

What is the difference between locks, latches, enqueues and semaphores?

A latch is an internal Oracle mechanism used to protect data structures in the SGA from simultaneous access. Atomic hardware instructions like TEST-AND-SET are used to implement latches. Latches are more restrictive than locks in that they are always exclusive. Latches are never queued, but will spin or sleep until they obtain a resource, or time out.

Enqueues and locks are different names for the same thing. Both support queuing and concurrency. They are queued and serviced in a first-in-first-out (FIFO) order.

Semaphores are an operating system facility used to control waiting. Semaphores are controlled by the following Unix parameters: semmni, semmns and semmsl. Typical settings are:

• semmns = sum of the "processes" parameter for each instance (see init.ora for each instance)
• semmni = number of instances running simultaneously;
• semmsl = semmns

http://www.orafaq.com/wiki/Oracle_database_Internals_FAQ#What_is_the_difference_between_locks.2C_latches.2C_enqueues_and_semaphores.3F

while for SQLServer a latch is often described as a "lightweight lock" and again is about protect internal database engine structures like buffers:

Tips for Using SQL Server Performance Monitor Counters

By : Brad McGehee Aug 24, 2005

A latch is in essence a "lightweight lock". From a technical perspective, a latch is a lightweight, short-term synchronization object (for those who like technical jargon). A latch acts like a lock, in that its purpose is to prevent data from changing unexpectedly. For example, when a row of data is being moved from the buffer to the SQL Server storage engine, a latch is used by SQL Server during this move (which is very quick indeed) to prevent the data in the row from being changed during this very short time period. This not only applies to rows of data, but to index information as well, as it is retrieved by SQL Server.

Just like a lock, a latch can prevent SQL Server from accessing rows in a database, which can hurt performance. Because of this, you want to minimize latch time.

SQL Server provides three different ways to measure latch activity. They include:

• Average Latch Wait Time (ms): The wait time (in milliseconds) for latch requests that have to wait. Note here that this is a measurement for only those latches whose requests had to wait. In many cases, there is no wait. So keep in mind that this figure only applies for those latches that had to wait, not all latches.
• Latch Waits/sec: This is the number of latch requests that could not be granted immediately. In other words, these are the amount of latches, in a one second period, that had to wait. So these are the latches measured by Average Latch Wait Time (ms).
• Total Latch Wait Time (ms): This is the total latch wait time (in milliseconds) for latch requests in the last second. In essence, this is the two above numbers multiplied appropriately for the most recent second.

When reading these figures, be sure you have read the scale on Performance Monitor correctly. The scale can change from counter to counter, and this is can be confusing if you don't compare apples to apples.

Based on my experience, the Average Latch Wait Time (ms) counter will remain fairly constant over time, while you may see huge fluctuations in the other two counters, depending on what SQL Server is doing.

http://www.sql-server-performance.com/tips/sql_server_performance_monitor_coutners_p3.aspx

To finish the following query looks useful, a good example of how to use the sys.dm_db_index_operational_stats view:

Tables where the most latch contention is occurring

select object_schema_name(ddios.object_id) + '.' + object_name(ddios.object_id) as objectName,
indexes.name, case when is_unique = 1 then 'UNIQUE ' else '' end + indexes.type_desc as index_type,
page_latch_wait_count , page_io_latch_wait_count
from sys.dm_db_index_operational_stats(db_id(),null,null,null) as ddios
join sys.indexes
on indexes.object_id = ddios.object_id
and indexes.index_id = ddios.index_id
order by page_latch_wait_count + page_io_latch_wait_count desc

http://sqlblog.com/blogs/louis_davidson/archive/2007/08/26/sys-dm-db-index-operational-stats.aspx

70-432 : Lock Events, SQL Server Profiler & Deadlock examples

This blog article is aimed at people preparing for 70-432 exam and is an overview of lock event category with sample examples of how and when to use it

The 70-432 exams requires you are comfortable with the terminology around "lock events", a good place to start is simply referring to msdn documentation:

Locks Event Category

Use the event classes in the Locks event category to monitor locking activity in an instance of the Microsoft SQL Server Database Engine. These event classes can help you investigate locking problems caused by multiple users reading and modifying data concurrently.

Because the Database Engine often processes many locks, capturing the Locks event classes during a trace can incur significant overhead and result in large trace files or tables.

In This Section

Topic	Description
Deadlock Graph Event Class	Provides an XML description of a deadlock.
Lock:Acquired Event Class	Indicates that a lock has been acquired on a resource, such as a row in a table.
Lock:Cancel Event Class	Tracks requests for locks that were canceled before the lock was acquired (for example, to prevent a deadlock).
Lock:Deadlock Chain Event Class	Monitors when deadlock conditions occur and which objects are involved.
Lock:Deadlock Event Class	Tracks when a transaction has requested a lock on a resource already locked by another transaction, resulting in a deadlock.
Lock:Escalation Event Class	Indicates that a finer-grained lock has been converted to a coarser-grained lock.
Lock:Released Event Class	Tracks when a lock is released.
Lock:Timeout (timeout > 0) Event Class	Tracks when lock requests cannot be completed because another transaction has a blocking lock on the requested resource. This event occurs only in situations where the lock time-out value is greater than zero.
Lock:Timeout Event Class

http://msdn.microsoft.com/en-us/library/ms177493.aspx

Next I want to mention a very interesting presentation I went to last year, I did blog about this last year:

tech: Deadlocks due collisions in "SQL Server's hashing algorithm" (June 2009 SQL Server UserGroup)

At the SQLServer usergroup there was a brief overview of a very technical problem - there appears to be a bug in SQLServer's "hashing algorithm" which under certain circumstances is triggering "deadlocks" to occur.

James' detailed blog:

http://blogs.conchango.com/jamesrowlandjones/archive/2009/05/28/the-curious-case-of-the-dubious-deadlock-and-the-not-so-logical-lock.aspx?CommentPosted=true#commentmessage

gives plenty of technical details behind this intriguing problem, and also the extensive collaborative investigation it took to unearth and document this bug.

I have gone through this great blog piece, highlighting what I see as the key concepts and steps. I have add some further references to key background concepts. This shows the process I went through to understand this issue, hopefully you find my comments a helpful introduction into understand this complex topics.

http://davetravelogue.blogspot.com/2009/06/tech-deadlocks-due-collisions-in-sql.html

This is a very interesting bug (complex and very rare) in SQL Server, the presentation at the London SQL Server UserGroup (www.sqlserverfaq.com) was very good and afterwards, I went though the details of the Conchango / EMC blog post go through the article. In my blog post ()

Don't want worry if you are struggling to follow this - this is very advanced/senior DBA material. However I would draw you're attention to the deadlock graph event, which can represented graphically or in XML format (see graphic at top of this page). This sort of information is similar to the data held within the Oracle dba_blockers view.

For a simpler / clean example of deadlocks Peter Wardy has written a good blog article which is easy to understand and recreate:

Creating a Deadlock

Below are the steps to generate a deadlock so that the behaviour of a deadlock can be illustrated:

-- 1) Create Objects for Deadlock Example
USE TEMPDB

CREATE TABLE dbo.foo (col1 INT)
INSERT dbo.foo SELECT 1

CREATE TABLE dbo.bar (col1 INT)
INSERT dbo.bar SELECT 1

-- 2) Run in first connection
BEGIN TRAN
UPDATE tempdb.dbo.foo SET col1 = 1

-- 3) Run in second connection
BEGIN TRAN
UPDATE tempdb.dbo.bar SET col1 = 1
UPDATE tempdb.dbo.foo SET col1 = 1

-- 4) Run in first connection
UPDATE tempdb.dbo.bar SET col1 = 1

Connection two will be chosen as the deadlock victim

ie.

Server: Msg 1205, Level 13, State 50, Line 1
Transaction (Process ID 56) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Published Monday, December 12, 2005 11:00 AM by peter@wardyit.com

http://wardyit.com/blog//blog/archive/2005/12/12/65.aspx

Lastly I also found a very well written blog post by Brad McGehee

Brad is an Industry speaker, writer, and consultant on Microsoft SQL Server, specializing in SQL Server performance tuning, clustering, and high availability. He is the founder of www.SQL-Server-Performance.Com, and oversaw its growth to 350,000 visits each month. He is now Director of DBA Education at Red Gate Software. Brad is a frequent speaker at SQL PASS, SQL Connections, SQL Server user groups, and other industry seminars, and he is the author or co-author of more than 12 technical books and over 100 published articles. He spends what time he has left with his family in Hawaii. He is a Microsoft SQL Server MVP, MCSE+I, MCSD, MCT See also ..

The whole article is well worth reading but the following section is probably particularly relevant for the 70-432 exam:

To capture a SQL Server trace using SQL Server Profiler, you need to create a trace, which includes several basic steps:

1) You first need to select the events you want to collect. Events are an occurrence of some activity inside SQL Server that Profiler can track, such as a deadlock or the execution of a Transact-SQL statement.

2) Once you have selected the events you want to capture, the next step is to select which data columns you want to return. Each event has multiple data columns that can return data about the event. To minimize the impact of running Profiler against a production server, it is always a good idea to minimize the number of data columns returned.

3) Because most SQL Servers have many different users running many different applications hitting many different databases on the same SQL Server instance, filters can be added to a trace to reduce the amount of trace data returned. For example, if you are only interested in finding deadlocks in one particular database, you can set a filter so that only deadlock events from that database are returned.

4) If you like, you can choose to order the data columns you are returning, and you can even group or aggregate events to make it easier to analyze your trace results. While I do this for many of my traces, I usually don’t bother with this step when tracking down deadlock events.

5) Once you have created the trace using the above steps, you are ready to run it. If you are using the SQL Server Profiler GUI, trace results are displayed in the GUI as they are captured. In addition, you can save the events you collect for later analysis.

Now that you know the basics, let’s begin creating a trace that will enable us to collect and analyze deadlocks.

Selecting Events

While there is only one event required to diagnose most deadlock problems, I like to include additional context events in my trace so that I have a better understanding of what is happening with the code. Context events are events that help put other events into perspective. The events I suggest you collect include:

· Deadlock graph

· Lock: Deadlock

· Lock: Deadlock Chain

· RPC:Completed

· SP:StmtCompleted

· SQL:BatchCompleted

· SQL:BatchStarting

http://www.simple-talk.com/sql/learn-sql-server/how-to-track-down-deadlocks-using-sql-server-2005-profiler/

70-433 SQL Server - Common Table Expressions

Firstly looking on wikipedia:

CTE can be thought of as an alternatives to derived tables (subquery), views, and inline user-defined functions

CTEs are define by the WITH statement and enable you to seperately define a complex sub-select statement separately making your queries more readable/logical.

Some of the most frequent uses of a Common Table Expression include creating tables on the fly inside a nested select, and doing recursive queries. Common Table Expressions can be used for both selects and DML statements. The natural question is, if we have been using TSQL for this long without Common Table Expressions, why start using them now? There are several benefits to learning CTEs. Although new to SQL Server, Common Table Expressions are part of ANSI SQL 99, or SQL3. Therefore, if ANSI is important to you, this is a step closer. Best of all, Common Table Expressions provide a powerful way of doing recursive and nested queries in a syntax that is usually easier to code and review than other methods.

http://www.databasejournal.com/features/mssql/article.php/3502676/Common-Table-Expressions-CTE-on-SQL-2005.htm

The following simple example demonstrates the syntax:

USE AdventureWorks GO  

WITH MyCTE( ListPrice, SellPrice) AS (   

SELECT ListPrice, ListPrice * .95   

FROM Production.Product ) 

SELECT * FROM MyCTE  GO

The above myCTE can then be in place of a table in the immediate next SQL statement.

Above the MAXRECURSION statement limits the number of times the sub-query will run

70-433 SQL Server Powershell

It would be nice to see some good examples of SQL Server power shell, Martin Bell's blog went through some of Microsoft Book On Line (BOL) examples; but I didn't immediately see where and why powershell scripting would help (the idea sounds great as I am big fan of Unix shell scripting).

I suspect somewhat like JavaScript which was not used in a particularly flexible or subtle way at first, that powershell has enormous potential and it just needs people to understand and use it to it's full capacity (in a possibly similar way to jquery libraries "on top of basic JavaScript")?

The 70-433 requires basic familiarity:

"Get-Item . | Get-ChildItem" cmdlet

returns "index names for table employees" when applied to the "indexes location"

(i.e. path SQLSERVER:SQL\Srv1\DEFAULT\Databases\Tables\Employees\Indexes)

returns "column names for table employees" when applied to the "columns location"

(i.e. path SQLSERVER:SQL\Srv1\DEFAULT\Databases\Tables\Employees\Columns)

"Get-Item . | Get-Member -type Properties " cmdlet

returns "all properties for table employees" when applied to the "tables location"

(i.e. path SQLSERVER:SQL\Srv1\DEFAULT\Databases\Tables\Employees)

70-433 SQL Server - SERVICEs & QUEUEs

CREATE SERVICE service_name [AUTHORIZATION owner_name] ON QUEUE [schema_name.]queue_name 

  [(contract_name | [DEFAULT]) [,...n];

A SERVICE (aka "Service Broker") require a QUEUE to be created, for example

CREATE QUEUE myQueue WITH STATUS=ON, ACTIVATION (

  STATUS=OFF, PROCEDURE_NAME=myProcForMessageHandling,

  MAX_QUEUE_READERS=3, EXECUTE AS SELF);

The above queue is ON and will receive messages, however as it's ACTIVATION STATUS=OFF messages will be held.

To change the "message type" handled use the ALTER SERVICE statement... 

70-433 SQL Server - Database Mail (based on SMTP)

Configured by the "Database Mail Configuration Wizard": SMTP accounts, security settings, system parameters ...

 

sysmail_delete_mailitems_sp -- houeskeeping typically based on sent_before (i.e. date sent) and sent_status (e.g. has the mail been 'sent')

sp_send_dbmail -- stored procedure to send email

NB The older SQL Mail was based on MAPI profiles and is harder to configure and administrate - Database Mail was introduced in SQL2005

70-433 SQL Server - changetable

Setting up a CHANGETABLE

Standard change tracking for developers ...

alter table employee enable change_tracking with (track_columns_updated = ON);

select e.empID, e2.salary FROM (CHANGETABLE(CHANGES employee,NULL))  as e

JOIN dbo.classes e2 on e.empID = e2.empID;

you can also restrict your changetable results, for example, to check the salary changes for empID=42:

CHANGETABLE(VERSION employee, (empID), (42)) as e

70-433 SQL Server - change_tracking

Configuring your DB for CHANGE_TRACKING

Basic configuration / housekeeping to be agreed with your DBA ...

ALTER DATABASE HR SET CHANGE_TRACKING = ON 

(CHANGE_RETENTION = 40 DAYS, AUTO_CLEANUP = ON);

and to disable change tracking:

ALTER DATABASE HR SET CHANGE_TRACKING = OFF;

(NB the DBA or Developer should disable change tracking on your select tables as well)

70-433 SQL Server - full-text search and stoplists

Create basic full-text search:

  CREATE FULLTEXT INDEX on Courses (Summary) KEY INDEX courseID;

Now if you want to perform a full-text search

  select courseID, summary from courses where contains (*, ' "SQL" AND "2008" ');

If you want to ignore a certain word, say 'CBT', you need to create a STOPLIST

CREATE FULLTEXT STOPLIST myStopList FROM SYSTEM STOPLIST;

ALTER FULLTEXT STOPLIST myStopList ADD 'CBT';

now when you create a full-text search, you can include the STOPLIST:

CREATE FULLTEXT INDEX on Courses (Summary) KEY INDEX CustID

  WITH STOPLIST = myStopList;

70-433 SQL Server - Query() method on XML elements

Setup example XML element:

declare @myXML xml

SET myXML = '

   

    eve

    matthey

 

 

    stef

    amar

 

 

    miriam

    tricky

 

' 

Query example XML element:

Select @myXML.query('{/Root/employee[@employee=2]/surname}};

Expected results

amar

Reusing myXML, we could use sp_xml_preparedocument and OPENXML

DECLARE @docHandle int

EXEC sp_xml_preparedocument @docHandle, @myXML

70-433 SQL Server XML Indexes - basic usage examples

The emp table has two columns "xml data type" columns: emp_xml & dept_xml

each column have it's own "PRIMARY XML INDEX" and then also multiple (secondary) "XML INDEX" (FOR PATH, FOR VALUE or FOR PROPERTY)

CREATE PRIMARY XML INDEX emp_xml_pri ON emp(emp_xml);

CREATE XML INDEX emp_xml_value ON emp(emp_xml) USING XML INDEX emp_xml_pri FOR VALUE;

CREATE XML INDEX emp_xml_path ON emp(emp_xml)  USING XML INDEX emp_xml_pri FOR PATH;

CREATE XML INDEX emp_xml_prop ON emp(emp_xml)  USING XML INDEX emp_xml_pri FOR PROPERTY;

70-433 SQL Server XML Indexes

XML Indexes are a new concept for me, this Microsoft support page gives some good background.

Let start with the following slightly controversial (over-simplification):

Although not everyone would agree, one of the main reasons for the success of the relational database has been the inclusion of the SQL language. SQL is a set-based declarative language. As opposed to COBOL (or most .NET-based languages for that matter), when you use SQL, you tell the database what data you are looking for, rather than how to obtain that data. The SQL query processor determines the best plan to get the data you want and then retrieves the data for you. As query-processing engines mature, your SQL code will run faster and better (less I/O and CPU) without the developer making changes to the code. What development manager wouldn't be pleased to hear that programs will run faster and more efficiently with no changes to source code because the query engine gets better over time?

So how can we access data in XML format efficiently:

In the early days of XML, imperative programming (navigation through the XML DOM) was all the rage. The XQuery language in general and XQuery inside the database in particular make it possible for the query engine writers to approach the task of optimizing queries against XML. The chances of success are good because these folks have 20 years or so of practical experience optimizing SQL queries against the relational data model. The SQL Server 2005 implementation of XQuery over the built-in XML data type holds the same promise of a declarative language, with optimization through a query engine

The 70-433 exam appears to require basic familarity with the following XML functions:

Table 1. XML Data Type Functions

Name	Signature	Usage
exist	bit = X.exist(string xquery)	Checks for existence of nodes, returns 1 if any output returned from query, otherwise 0
value	scalar = X.value( string xquery, string SQL type)	Returns a SQL scalar value from a query cast to specified SQL data type
query	XML = X.query(string xquery)	Returns an XML data type instance from query
nodes	X.nodes(string xquery)	Table-value function used for XML to relational decomposition. Returns one row for each node that matches the query.
modify	X.modify(string xml-dml)	A mutator method that changes the XML value in place

The article goes on to detail how this mapped and represented in internal SQL Server database objects.. interesting stuff.