Coding Powers

Hosting Vista File Previews in Managed Code

As part of my work on the Coding4Fun Developer Kit 2008, I created a WinForm control encapsulating the flow to display file previews.  The host is dependent on an improved version of Stephen Toub's framework, originally published here.  My enhancements were basically to make the COM wrapper classes public so that they can be used by the Managed PreviewHandlers and also by my Preview Handler Host.  The source for this project including the updated framework, Stephen's sample preview handlers, my host control and a sample application using the host control are available here.  Also, they can be downloaded from the codeplex project for the Coding4Fun Developer Kit 2008.

A couple of quick notes before I describe how the managed preview handler host works. 

1. For any of this to work, the C4F.DevKit.PreviewHandlerFramework.dll needs to be installed in GAC.
2. Also, this host will work with all non-managed preview handlers and any managed preview handlers that were built against the PreviewHandlerFramework mentioned above.  Any Managed Preview Handlers that were compiled using Stephen's original framework or another method will not work with this host. 

To host the results generated by any registered file preview handler in managed code, the PreviewHandlerFramework’s COM Wrappers are leveraged. In addition, part of this host needs to look up the preview handler that is currently registered with a particular file extension. To accomplish this, the PreviewHandlerHost leveraged the registry code from Stephen Toub’s Preview Handler Association Editor (http://blogs.msdn.com/toub/archive/2006/12/14/preview-handler-association-editor.aspx). With the code to find the registered handler in hand, we constructed a control to implement the preview handler Data Flow as shown below (all of this code is part of PreviewHandlerHost project, PreviewHandlerHostControl.cs):

• Declare an object that is the PreviewHandler (this will be cast to different COM Interfaces as needed)
• Unload any existing previews before generating a new preview
  • Call Unload of IPreviewHandler Interface (dDefined in PreviewHandlerFramework as a wrapper to existing COM interface)

Visual C#

private object _comInstance = null; 
private void GeneratePreview() 
{ 
if (_comInstance != null) 
{ 
((IPreviewHandler)_comInstance).Unload(); 
}

Visual Basic

Private _filePath As String 
Private Sub GeneratePreview() 
If Not _comInstance Is Nothing Then 
CType(_comInstance, IPreviewHandler).Unload() 
End If

• Build a RECT struct using the bounds of the visible area where the preview handler can draw. In this case, that is the full bounds of the PreviewHandlerHostControl

Visual C#

RECT r; 
r.top = 0; 
r.bottom = this.Height; 
r.left = 0; 
r.right = this.Width;

Visual Basic

Dim r As RECT 
r.top = 0 
r.bottom = Me.Height 
r.left = 0 
r.right = Me.Width

• Find the CLSID of the preview handler registered for the file extension of the file using registry lookup. This is typically done by looking in the registry under HKEY_CLASSES_ROOT\%file_extension%\shellex\{8895b1c6-b41f-4c1c-a562-0d564250836f}. This Registry key will have a value pointed to the Class ID (CLSID) of the COM registration of the registered preview handler. For more information about how preview handlers can be found in the registry, see this article.
• Create an instance of the preview handler using Reflection and the CLSID found in the registry.

Visual C#

Type comType = Type.GetTypeFromCLSID(new Guid(handler.ID)); 
_comInstance = Activator.CreateInstance(comType);

Visual Basic

Dim comType As Type = Type.GetTypeFromCLSID(New Guid(handler.ID)) 
_comInstance = Activator.CreateInstance(comType)

• Call the appropriate initialize for the create preview handler (either passing in a stream or filepath)
  • If the preview handler is a stream previewhandler, using a COM IStream Wrapper to handle Stream marshalling to COM

Visual C#

if (_comInstance is IInitializeWithFile) 
{ 
((IInitializeWithFile)_comInstance).Initialize(_filePath, 0); 
} 
else if (_comInstance is IInitializeWithStream) 
{ 
if (File.Exists(_filePath)) 
{ 
StreamWrapper stream = new StreamWrapper(File.Open(_filePath, FileMode.Open)); 
((VSExpressDevPack.PreviewHandlerFramework.IInitializeWithStream)_comInstance).Initialize(stream, 0); 
} 
else 
{ 
throw new Exception("File not found"); 
} 
}

Visual Basic

If TypeOf _comInstance Is IInitializeWithFile Then 
CType(_comInstance, IInitializeWithFile).Initialize(_filePath, 0) 
ElseIf TypeOf _comInstance Is IInitializeWithStream Then 
If File.Exists(_filePath) Then 
Dim stream As StreamWrapper = New StreamWrapper(File.Open(_filePath, FileMode.Open)) 
CType(_comInstance, VSExpressDevPack.PreviewHandlerFramework.IInitializeWithStream).Initialize(stream, 0) 
Else 
Throw New Exception("File not found") 
End If 
End If

• Call SetWindow on the PreviewHandler, passing in a handle to the control and the bounds (RECT created earlier)

Visual C#

((IPreviewHandler)_comInstance).SetWindow(this.Handle, ref r);

Visual Basic

CType(_comInstance, IPreviewHandler).SetWindow(Me.Handle, r)

• Call DoPreview on the PreviewHandler

Visual C#

((IPreviewHandler)_comInstance).DoPreview();

Visual Basic

CType(_comInstance, IPreviewHandler).DoPreview()

If you are interested in more information, the zip file included with this project has all of the source and a document describing a walkthrough of using this control.  Additionally, the Coding4Fun Developer Pack 2008 Vol.1 Codeplex project has all the info and will also have any fixes/enhancements.

Coding4Fun Developer Kit 2008 Vol.1 Beta Released on Codeplex

Recently, I have been spending most of my time helping put together the Coding4Fun Developer Kit that was released today on Codeplex.  This is a Kit put together by Microsoft to open up many of the new and powerful APIs avaiable in Vista and Visual Studio 2008 to enable simple programming interfaces that can be used from managed code.

This developer kit contains .net wrappers and sample projects for the following Vista APIs

• Bluetooth
• Contacts
• Digital Sound Recording
• Messaging/Email
• Picture Sharing
• Picture Acquisition
• Power Management
• Preview Handlers
• Feeds
• Desktop Search

I made two big contributions to this developer kit.  The first was building the Preview Handler Wrapper.  This Wrapper included enhancing the managed preview handler framework built by Stephen Toub and published in the January Edition of MSDN Magazine.  In addition, I wrote a managed preview handler host, to enable displaying any preview handler within a .net winform application.  Watch for my next post describing, Hosting File Previews In Managed Code.  My other contribution to the project was pulling the various projects together and building the installer.  I used WIX.  It was a tedious process at times, but ultimately pretty effective.  I promise to write another post detailing my experience with WIX and what I did to put this installer together.

Check out the codeplex site for the Coding4Fun Developer Kit 2008 vol.1.  Definitely some cool and interesting stuff.

SQL 2005 Performance Tuning using DMVs - Part 4 of 7 - I/O Health

In my previous 3 posts in this series, I went through an introduction to Dynamic Management Views and an in depth look at how DMVs can be used to analyze and pinpoint CPU bottlenecks.  In today's post we will take a look at using DMVs for analyzing I/O Health and pressure.

You can use the following DMV query to find currently pending I/O requests. You can execute this query periodically to check the health of I/O subsystem and to isolate physical disk(s) that are involved in the I/O bottlenecks.

SELECT 
    database_id, 
    file_id, 
    io_stall,
    io_pending_ms_ticks,
    scheduler_address 
FROM    
    sys.dm_io_virtual_file_stats(NULL, NULL)t1
INNER JOIN
       sys.dm_io_pending_io_requests as t2
ON    t1.file_handle = t2.io_handle

If you find, that you regularly have a high number of pending I/O requests, you can use the following query to determine which SQL batches use the most I/O.

SELECT TOP 5 
    (total_logical_reads/execution_count) as avg_logical_reads,
    (total_logical_writes/execution_count) as avg_logical_writes,
    (total_physical_reads/execution_count) as avg_phys_reads,
     Execution_count, 
    statement_start_offset as stmt_start_offset, 
    st.[text], 
    qp.query_plan
FROM sys.dm_exec_query_stats  qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY 
 (total_logical_reads + total_logical_writes) Desc

If you are troubleshooting blocked I/O realtime, you can use dm_tran_locks and dm_waiting_tasks to determine what sql is blocking and what is waiting.

SELECT 
    t1.resource_type,
    'database'=db_name(resource_database_id),
    'blk object' = t1.resource_associated_entity_id,
    t1.request_mode,
    t1.request_session_id,
    t2.blocking_session_id     
FROM 
    sys.dm_tran_locks as t1
INNER JOIN 
    sys.dm_os_waiting_tasks as t2
ON 
    t1.lock_owner_address = t2.resource_address and
    t1.request_session_id = t1.request_session_id

The last thing to check when analyzing I/O is the utlization of tempDB.  You can use dm_db_file_space_usage to determine the amount of space that is being used in tempdb by user_objects, internal_objects and the version_store.

SELECT
    SUM (user_object_reserved_page_count)*8 as user_objects_kb,
    SUM (internal_object_reserved_page_count)*8 as internal_objects_kb,
    SUM (version_store_reserved_page_count)*8  as version_store_kb,
    SUM (unallocated_extent_page_count)*8 as freespace_kb
FROM sys.dm_db_file_space_usage
WHERE database_id = 2

That's it for this edition, in the next installment we will look at finding Problem Queries.

The code from this post is available here.

Facebook Developer Toolkit Version 1.2 Released

I have been spending most of time working on the Facebook Developer Toolkit and monitoring our codeplex site.  Things have been going pretty, the response has been pretty positive and the help from the other site contributors has been great.  We even just became the 10th most popular project on CodePlex.  Which is a pretty cool accomplishment and pretty impressive company.

Today, we released Version 1.2 of the Toolkit.

Included in this release are:

The major change is the Canvas base pages, these should help new developers solve the riddle of building Facebook Canvas applications.

Thanks to everyone who helped with code and feedback.  I think we continue to make good progress on this project.  You can download the release or documentation here.

SQL 2005 Performance Tuning using DMVs - Part 3 of 7 - Alleviating CPU Pressure

This is the third installment on my series on using Dynamic Views to help with SQL 2005 performance tuning.  The previous post on Identifying CPU Pressure can be found here.

After identifying that a database is cpu constrained, the next step is to find out what is using all the cpu.  The first step is to identify the SQL batches that are using the most CPU.  To find these batches, you use dm_exec_query_stats and join to dm_exec_sql_text to get the actual SQL statement. The following query will yield the top 10 (currently cached) sql statements for cpu utilization.

SELECT 
    derived_table.total_cpu_time,
    derived_table.total_execution_count,
    derived_table.total_cpu_time/derived_table.total_execution_count as cpu_per_execution,
    derived_table.number_of_statements,
    Plans.query_plan
FROM    
(
SELECT TOP 10 
    SUM(qs.total_worker_time) as total_cpu_time, 
    SUM(qs.execution_count) as total_execution_count,
    COUNT(*) as  number_of_statements, 
    qs.plan_handle
FROM 
    sys.dm_exec_query_stats qs
GROUP BY qs.plan_handle
ORDER BY SUM(qs.total_worker_time) desc) derived_table
CROSS APPLY sys.dm_exec_query_plan(plan_handle) as Plans
order by derived_table.total_cpu_time/derived_table.total_execution_count desc

Another common cause of CPU over-utilization on a database server is query optimization time.  If you take periodic snapshots of the dm_exec_query_optimizer_info DMV, you can get a good idea of the amount of time spent optimizing.  The other way to look at optimization time is to examine how often queries are recompiled.  The following SQL is useful for this examination:

--To examine queries that recompile often,
--plan_generation_num indicates the number of times 
--the query has recompiled. 
--The following sample query gives you the top 10 stored procedures 
--that have been recompiled.

SELECT top 10
    sql_text.[text],
    sql_handle,
    plan_generation_num,
    execution_count,
    dbid,
    objectid 
FROM 
    sys.dm_exec_query_stats a
    CROSS APPLY sys.dm_exec_sql_text(sql_handle) as sql_text
WHERE
    plan_generation_num >1
ORDER BY plan_generation_num DESC

Another common cause of high CPU database server is queries that require parallel processing.  You can use a combination of dm_exec_requests, dm_os_tasks and dm_exec_sessions to find all active requests utilizing parallel processing.

SELECT 
    r.session_id,
    r.request_id,
    MAX(ISNULL(exec_context_id, 0)) as number_of_workers,
    r.sql_handle,
    r.statement_start_offset,
    r.statement_end_offset,
    r.plan_handle
FROM 
    sys.dm_exec_requests r
INNER JOIN sys.dm_os_tasks t on r.session_id = t.session_id
INNER JOIN sys.dm_exec_sessions s on r.session_id = s.session_id
WHERE 
    s.is_user_process = 0x1
GROUP BY 
    r.session_id, r.request_id, 
    r.sql_handle, r.plan_handle, 
    r.statement_start_offset, r.statement_end_offset
HAVING MAX(ISNULL(exec_context_id, 0)) > 0

Alternatively, you can just identify all cached plans that allow parallel execution. 

SELECT 
    p.*, 
    q.*,
    cp.plan_handle
FROM 
    sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) p
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) as q
WHERE 
    cp.cacheobjtype = 'Compiled Plan'
    AND p.query_plan.value('declare namespace p="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
        max(//p:RelOp/@Parallel)', 'float') > 0

This should help you identify and address the causes of high CPU usage.  Next installment will take a look at identifying I/O bottlenecks.  You can download the source for these examples here.