Microsoft has posted the first cumulative update for SQL 2008 under article 956717.

Once installed, your patched instance will go to version 10.0.1763 from 10.0.1442.

I'm blowing my budget this year in going to the Microsoft BI conference in Seattle, but for anyone else out there who, a) will miss the Microsoft BI Conference, or b) is lucky enough to afford two trips, then be sure to go to the DevConnections event in Las Vegas this November.

Some of us wear blinders when it comes to all things SQL Server and BI related, however DevConnections is broken out into several categories: SQL Server, SharePoint, ASP.NET, Visual Studio .NET, Microsoft Exchange, Windows and Unified Communications.

The main categories for SQL Server will be:

• SQL Server Administration
• SQL Server Development
• Business Intelligence
• SSWUG
• And one day for Microsoft presenters

The event will run from November 10^th to the 13^th. Early registration has already passed, but you still will get that free copy of SQL 2008.

Earlier this year, I posted a short feature on auditing in SQL 2005 using DDL triggers to capture events and log them. In SQL 2008, one of the less sexy features added, though highly important and underscored by this current eras reporting and traceability debacles in public documents, is SQL 2008's new auditing features (less sexy from a populist point of view, I do note that there is probably an avid community out there with an audit fetish, so no intention of diminishing this feature's importance to you and yours).

Quite simply, it is implemented by the following sample syntax (full reference here):

One of the interesting switches you can use for auditing is the ON_FAILURE argument accepts parameters of CONTINUE or SHUTDOWN. If auditing is paramount for this system, then any failure in the auditing mechanism will turn the SQL Server service off. As auditing is SQL instance specific, this can be useful for many types of compliance scenarios.

The audit is set up at two levels; first, as shown in the script above, at the server level, which captures audit events (more on those events here).

The second level that can be implemented is at the database level. The script below is run within the context of the database I wanted the audit on, while the first script needs to be run within the context of the master database (full reference here):

This particular audit specification will capture all DDL executed against my particular database and make it part of the log.

For more, you can reference books line here for the Auditing home page for SQL 2008.

SQL 2008 has now been fully released for just over a month, and for those who have not been keeping track of additions throughout the CTPs and Release Candidates, here is a bit of useful information: SQL 2008 has 46 new DMVs(as of version 10.0.1442).

If you have a 2005 instance on your box along side your 2008 install, you can run this from your 2008 instance query to take a look at the output:

select name fromsys.all_objectswhere name like'dm_%'

except

select name from [localhost\sql2005instance].master.sys.all_objectswhere name like'dm_%'

Several of these DMVs correspond directly to feature additions such as Filestream, new cryptography features and resource governor, plus several that give more insight into the host OS.

Here is the complete list if you don't have a 2005 instance handy:

dm_audit_actions

dm_audit_class_type_map

dm_cdc_errors

dm_cdc_log_scan_sessions

dm_cryptographic_provider_algorithms

dm_cryptographic_provider_keys

dm_cryptographic_provider_properties

dm_cryptographic_provider_sessions

dm_database_encryption_keys

dm_db_mirroring_auto_page_repair

dm_db_mirroring_past_actions

dm_db_persisted_sku_features

dm_exec_procedure_stats

dm_exec_trigger_stats

dm_filestream_file_io_handles

dm_filestream_file_io_requests

dm_fts_fdhosts

dm_fts_index_keywords

dm_fts_index_keywords_by_document

dm_fts_outstanding_batches

dm_fts_parser

dm_os_dispatcher_pools

dm_os_dispatchers

dm_os_memory_brokers

dm_os_memory_node_access_stats

dm_os_memory_nodes

dm_os_nodes

dm_os_process_memory

dm_os_spinlock_stats

dm_os_sys_memory

dm_resource_governor_configuration

dm_resource_governor_resource_pools

dm_resource_governor_workload_groups

dm_server_audit_status

dm_sql_referenced_entities

dm_sql_referencing_entities

dm_tran_commit_table

dm_xe_map_values

dm_xe_object_columns

dm_xe_objects

dm_xe_packages

dm_xe_session_event_actions

dm_xe_session_events

dm_xe_session_object_columns

dm_xe_session_targets

dm_xe_sessions

More wall decorations! Actually, keep this as a PDF on your computer for reference instead of printing and save paper.

I came across this SQL 2005 system views poster in my travels across cyberspace. It is posted on a cube at the client site I am currently at, so I've been looking for a soft copy ever since I started here.

This poster breaks out the system views in nicely colored patterns in the following groups:

• Objects, Types and Indexes
• Linked Servers
• Common Language Runtime
• Database Mirroring
• Service Broker
• Security
• Endpoints
• Partitioning
• Databases and Storage
• Transaction Information
• Server Wide Configuration
• Server Wide Information
• Execution Environment
• Tracing and Eventing

After putting up a primer post about statistics last week, my mind has now jumped to another facet of performance: cached plans.

Cached plan objects, whether they are for procedures, ad-hoc statements, views, etc., can have both a negative and positive effect on system performance. There are some general guidelines for when you will want to consider freeing the cache in order to negate when the cache plan is having a negative effect on system performance:

• Whenever you make database schema changes to widely used objects
• Large loads of data or heavy transactions higher than average
• If the database servers up ad-hoc reports
• You are getting ready to do performance testing

The SQL statement to clean up the cache is: dbcc freeproccache [ ( { plan_handle | sql_handle | pool_name } ) ] [ WITH NO_INFOMSGS ].

On the three optional parameters you can use with this statement:

• Plan_handle is the varbinary(64) identifier for the in memory plan. This identifier is constant only while this plan remains in system cache. It can be found in the sys.dm_exec_sql_text, sys.dm_exec_query_plan or sys.dm_exec_plan_attributes DMVs

• SQL_handle is also a varbinary(64) identifier. It refers to the batch or stored procedure that a query is a part of in the system. It can be found in the sys.dm_exec_query_stats, sys.dm_exec_requests, sys.dm_exec_cursors, sys.dm_exec_xml_handles, sys.dm_exec_query_memory_grants or sys.dm_exec_connections DMVs  

     

• Pool_name is the name of a Resource Governor resource pool and is a sysname. You can find more detail in the sys.dm_resource_governor_resource_pools DMV.

     

For heavy performance testing, or for a server that is really bogged down, the dbcc DROPCLEANBUFFERS statement is a way to clean out the cold buffer cache without restarting the server. It is recommended though, to run a CHECKPOINT command; this command forces SQL to write all dirty pages to disk.

After this has completed, then run the DROPCLEANBUFFERS command against the system to clean out the system buffer. Note that for pure reporting systems with no writes back to the database, this is not a required step.