On the piste, France.
UK Consulting Blogs
PerformancePoint Server 2007 CTP1 is now available via the Connect website https://connect.microsoft.com/site/sitehome.aspx?SiteID=181. The bits (x86 and x64) are available to download including documentation and sample data etc. I haven't had a change to look at it yet but will do over the next few days.
On a slightly different note, my brother has decided to run in the London Marathon (26 miles!). He is raising money for Concern Worldwide - this is an international charity focused on education, health and emergencies. They work all over the world but concentrate much of their work around Africa. If you'd like to sponsor him for a good cause, please do so via: http://www.justgiving.com/mattjones. Thank you.
This is quite an interesting KB article detailing I/O requirements for tempdb, and amongst other things, the use of RAM disks and solid state media. There are some important points to consider e.g. as the media is not durable, only tempdb can be located on it; it cannot be used for other database files. It is also worth remembering that the performance of temp tables will not be great due to the double RAM caching mechanism. I recommend reading the KB article for the full details http://support.microsoft.com/kb/917047 Microsoft SQL Server I/O subsystem requirements for the tempdb database
Tony Rogerson (SQL Server MVP who runs the UK site sqlserverfaq.com) has two copies (invitations) of MSDN Team Subscription to give away! Go here to read the full competition details: http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/11/11/1285.aspx
What do you need to do? Simple really, just contribute technical information, hints and tips or advice to the SQL Server community via the UK SQL Server User Group sister site www.sqlblogcasts.com. The winners will be announced at the end of this month (31st November).
The sysinternal tools are now on TechNet. I know this is not specifically related to SQL Server however, but they are very useful tools which can help diagnose performance problems. I particularly like process explorer and process monitor (and they work on x64 & Vista too!). Get them here: http://www.microsoft.com/technet/sysinternals/processesandthreadsutilities.mspx
As you probably know, Service Pack 2 for SQL Server 2005 introduces the vardecimal storage format. Below is a step-by-step example using the AdventureWorks database. There are a few incorrections in BOL SP2 CTP2 although these are not major problems and can be easily resolved; I imagine discussions are ongoing about naming conventions for the vardecimal options.
USE master ;GO
-- Enable vardecimal on databaseEXEC sp_db_vardecimal_storage_format 'AdventureWorks', 'ON' ;GO
-- Check the vardecimal storage format state for all databases in the instanceEXEC sp_db_vardecimal_storage_formatGO
-- Enable vardecimal compression at the table levelUSE AdventureWorksGO-- Note: The BOL example incorrectly references 'decimal data compression'EXEC sp_tableoption 'Sales.SalesOrderDetail', 'vardecimal storage format', 1GO
-- Does not show vardecimal propertiesEXEC sp_help 'Sales.SalesOrderDetail'
-- So, use the TableHasVarDecimalStorageFormat objectpropertyUSE AdventureWorks ;GOSELECT name, object_id, type_descFROM sys.objects WHERE OBJECTPROPERTY(object_id, N'TableHasVarDecimalStorageFormat') = 1 ;GO
-- Under the covers, this uses sys.dm_db_index_physical_stats to calculate the stats-- Documented in BOL CTP2 as sp_estimatedecimalcompressionEXEC sp_estimated_rowsize_reduction_for_vardecimal 'Sales.SalesOrderDetail' ;
-- Clean-up / disable vardecimal storage formatUSE AdventureWorksGO
-- Disable table-level storage formatEXEC sp_tableoption 'Sales.SalesOrderDetail', 'vardecimal storage format', 0GOUSE master;GO
-- Disable database propertyEXEC sp_db_vardecimal_storage_format 'AdventureWorks', 'OFF' ;GO
The Service Pack 2 CTP 2 download will be available very soon from here: http://www.microsoft.com/sql/ctp.mspx. In the meantime, please take a look at the What's New readme document which is available here: http://go.microsoft.com/fwlink/?LinkId=71711. This provides a comprehensive review of the new and improved features. I particularly like the fact that SSIS is no longer required for maintenance plans. The vardecimal storage format is also a welcome addition.
We have just released a new whitepaper on MSDN which is worth a read: Architecting Service Broker Applications http://msdn2.microsoft.com/en-us/library/aa964144.aspx