November 9, 2009

sys.dm_exec_query_stats


The “sys.dm_exec_query_stats” DMV is useful for analyzing performance stats of cached query plans in order to identify long running queries and/or procedures that might be missing indexes.
I use the following script a lot, enjoy!
SELECT TOP 5 creation_time, last_execution_time, total_clr_time, total_clr_time/execution_count
AS [Avg CLR Time], last_clr_time, execution_count,
SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1
THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2) + 1) as statement_text
FROM sys.dm_exec_query_stats as qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as st
ORDER BY total_clr_time/execution_count DESC;
GO

June 15, 2009

Going Meta Redux


Continuing with my thoughts about "Logical Models" from yesterday...
The object's attributes encapsulate state
The object has relationships with other objects
The object's properties consist of both: its attributes & its relationships
The object may inherit or assimilate attributes from other objects
The object is created / evolves at "runtime"
The object manipulates state through behaviors / process / flow/movement
The object consists of parameterized primitives

June 14, 2009

Logical Models


The Domain Model
The domain model is a high level logical view of the object.
In the domain model, models are called entities.
An entity is comprised of attributes representing structures containing  data encapsulating state.
References to other models are called relationships.
Together, attributes and relationships are known as properties.
The Class Model
The class model describes the objects and their relationships.
class is a specification: a design pattern from which the objects are created at runtime.
Classes may be inherited from other classes.
A class encapsulates state as attributes. It manipulates state through behaviors.
An object is an instance of a class.

January 2, 2009

Render query results as XML

This simple script renders the query results as XML:
-- Declare a variable with the XML datatype
DECLARE @myXml xml 
-- Use FOR XML AUTO syntax
SET @myXml = (SELECT [states] FROM [pharmacy] Order By [states] FOR XML AUTO)
-- Retrieve the results
select @myXml
<pharmacy states="Alabama" />
<pharmacy states="Arizona" />
<pharmacy states="Arkansas" />
. . .