I've been playing with the new CTP for a day and I really like what I'm seeing from this feature complete CTP.

The additional features around partitioning and data compression really caught my attention as they relate well to activities on my current client.  The storage team has an interesting post about a wizard driven partitioning feature in 2008.  I think this will help drive some customers to test the waters of partitioning.

The improved intellisense caught my eye as well.  I am using the client tools for day to day work on an existing SQL 2005 database without the first issue.  My experience was so good in fact, I had to send the guys in the manageability team some props yesterday.  The intellisense in SSMS is starting to feel more like Visual Studio, to which most of us (fairly or unfairly) will be comparing it.

I think the product team did a better job this time adding features that solve specific problems existing today.  While the CLR was an interesting add in 2005 I would have easily given it up for data compression or the MERGE statement.

So big props to the SQL Server product team as a whole.  They have made this geek very excited about the future.

Get your download started here. 

I had another DMV post in mind but it will have to wait while I play. :)

I'm staying on the same path in regards to dynamic management views (DMVs from here on) but turning my attention to the SQL Server 2008 product.  There are roughly 30 new DMVs in 2008 and I am going to highlight two today.

I seem to be constantly looking for referenced objects in T-SQL code and there are a multitude of paths to choose to get there.  All of them have slight issues that leave me wondering if I truly found all the objects I was after.  SQL Server 2008 introduces two dynamic management functions that make this search easier.

sys.dm_referenced_entities

This function takes a qualified name (schema + object) as well as a type and returns all objects that are used by the given object.  The types that can be passed to the function are OBJECT, TYPE, XML_SCHEMA_COLLECTION, and PARTITION_FUNCTION.  For today's code examples I'm using the freshly downloaded AdventureWorks sample database for SQL Server 2008.  The following code will return all objects referenced in the stored procedure uspGetBillOfMaterials:

SELECT
    referenced_schema_name
    ,referenced_entity_name
    ,referenced_minor_name
    ,referenced_class_desc
FROM sys.dm_sql_referenced_entities('dbo.uspGetBillOfMaterials', 'OBJECT')
WHERE referenced_minor_id > 0    --return columns only;

sys.dm_referencing_entities

This function works in the opposite direction of sys.dm_referenced_entities and returns the objects dependent on the given object.  Again, we pass a qualified name and a type.  The following code will return all objects dependent on the table Production.BillOfMaterials:

SELECT
    referencing_schema_name
    ,referencing_entity_name
    ,referencing_class_desc
FROM sys.dm_sql_referencing_entities('Production.BillOfMaterials', 'OBJECT');

I think these functions will make your next search for a table or procedure reference much easier.

Enjoy!

One of my favorite features in SQL Server 2005 are the dynamic management views.  I've been playing with them for quite a while now and there is a wealth of information available to you.

I've been doing work with a client looking over the performance of their database application.  Two DMV queries in particular have been helpful and I thought I would share them.  The first has to do with index fragmentation and the other with stored procedure execution.

We start with the management function (not a view I know, but bear with me) sys.dm_db_index_physical_stats.  This function returns size and fragmentation information for the data and indexes of the specified object.  The function takes inputs of database_id, object_id, index_id, partition_number, and mode.  Here's an example that will return index fragmentation at an index level for your database:

SELECT
        OBJECT_NAME(s.[object_id]) AS [tableName]
        ,i.[name] AS [indexName]
        ,s.index_type_desc AS [indexType]
        ,s.partition_number
        ,s.page_count AS [numberOfPages]
        ,s.fragment_count AS [fragmentCount]
        ,ROUND(avg_fragmentation_in_percent,2) AS [fragmentationPercent]
        ,ROUND(s.avg_fragment_size_in_pages, 2) AS [averageFragmentationInPages]
FROM 
    sys.dm_db_index_physical_stats(db_id(),null, null, null, null) s
    INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id 
WHERE 
    s.database_id = DB_ID() 
    AND i.name IS NOT NULL    --Filter HEAPs
    AND OBJECTPROPERTY(s.[object_id], 'IsMsShipped') = 0
ORDER BY 
    fragmentationPercent DESC;

Next we move to stored procedure workload.  The query makes use of the sys.dm_exec_query_stats view which returns a row for each query statement within the cached plan.  We also use the management function sys.dm_exec_sql_text to return the T-SQL text of the query.  If you do not use stored procedures in your database application simply remove the join to sys.procedures as well as the [objectName] reference in the query output.

DECLARE @CurrentTime DATETIME ; 

SET @CurrentTime = GETDATE() ;

-- Get executed SP's ordered by calls/sec
SELECT
    SCHEMA_NAME([schema_id]) AS [schemaName]
   ,p.[name] AS [objectName]
   ,qs.execution_count AS [executionCount]
   ,qs.total_worker_time / qs.execution_count AS [avgWorkerTime]
   ,qs.total_worker_time AS [totalWorkerTime]
   ,qs.total_elapsed_time / qs.execution_count AS [avgElapsedTime]
   ,qs.max_logical_reads
   ,qs.max_logical_writes
   ,qs.total_physical_reads
   ,qs.total_physical_reads/(qs.execution_count * 1.0) AS [avgPhysicalReads]
   ,qs.creation_time
   ,DATEDIFF(MI,qs.creation_time,@CurrentTime) AS [ageInCache]
   ,qs.execution_count / DATEDIFF(SS,qs.creation_time,@CurrentTime) AS [callsPerSecond]
   ,qs.last_execution_time
   ,SUBSTRING(qt.text,qs.statement_start_offset / 2,( 
        CASE 
            WHEN qs.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX),qt.text)) * 2
            ELSE qs.statement_end_offset
        END - qs.statement_start_offset ) / 2
        ) AS [individualQueryText]
FROM
    sys.dm_exec_query_stats AS qs
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
    INNER JOIN sys.procedures AS p ON qt.objectid = p.[object_id]
WHERE
    qt.dbid = DB_ID()
ORDER BY
    [callsPerSecond] DESC ;