Microsoft offers lots of free E-Learning Courses

March 27, 2007 / Comments Off on Microsoft offers lots of free E-Learning Courses

Besides the fact that you can test trive MS technology on their virtual machines >>LINK<< MS offers llots of training material for their technology as well. Follow this link >>LINK<< to find out more.

They also have loads of interesting video’s available about ASP.NET >>LINK<<

Is there going to be a next version of BizTalk.

March 26, 2007 / Comments Off on Is there going to be a next version of BizTalk.

Well sometimes you hear things you don’t like, I got an email from someody stating that BizTalk would cease to exist within the next couple of years, since functionality of the .Net 3.0 framwork takes over part of what BizTalk does.

Well this is only partly true. And for sure BT will use WCF and depricate some of its old adapters, and probably BizTalk will act as a host for WorkFlow besides WAS and IIS.

But for people wanting to know more have a look at slide 53 of the presenation in this post. It’s a very good presentation about SOA / WCF /WF / BizTalk. It has some sheets when to use what (slide 23) and also there is some usefull info about SOA itself and when to use the technologies mentioned.

Click >>here<< to see the blog entry that has the presentation in it.

Test drive almost any MS technology on their virtual machines

March 25, 2007 / Comments Off on Test drive almost any MS technology on their virtual machines

Sometimes fellow consultants want to learn more about for example MS BizTalk. It’s a lot of work to set up a BizTalk machine and sometimes people don’t have that time. You can get access to a complete already set up BizTalk Machine via MS. They have special built VM set up and you can run lab’s on them so you can test drive the technology you are interested in. They have Labs set up for:

  • BizTalk

  • Team System

  • SQL

  • ASP (1.0/2.0)

  • And lots more……..

If you are a few hours idle or want to learn for example something about BizTalk it’s extremely usefull to walk through some of the labs.

Go to the Virtual Labs Site now by clicking this link >>VirtualLabs<<


Awesome SQL power

March 20, 2007 / Comments Off on Awesome SQL power

A couple of days ago I went to a SQL 2005 SQL Deep Dive. This was a powerpoint only presentation of two days with a few demo’s in it. It was a very interesting deep dive and I learned a couple of things I thought I needed to share.

SQL Server keeps some statistics on tables and indexes. Once SQL server is started the statistics are kept and the statistics are lost after a reset. The SQL Guru’s called them DMV’s. Below are a few usefull DMV queries

List Indexes not used :

select object_name(i.object_id),
from sys.indexes i, sys.objects o
where  i.index_id NOT IN (select s.index_id
       from sys.dm_db_index_usage_stats s
    where s.object_id=i.object_id and
    i.index_id=s.index_id and
   database_id = db_id(‘##YOUR DATABASE##’) )
and o.type = ‘U’
and o.object_id = i.object_id
order by object_name(i.object_id) asc

Average Stalls per Read, Write and Total IO

select database_id, file_id
 ,cast(io_stall_read_ms/(1.0+num_of_reads) as numeric(10,1)) as ‘avg_read_stall_ms’
 ,cast(io_stall_write_ms/(1.0+num_of_writes) as numeric(10,1)) as ‘avg_write_stall_ms’
 ,io_stall_read_ms + io_stall_write_ms as io_stalls
 ,num_of_reads + num_of_writes as total_io
 ,cast((io_stall_read_ms+io_stall_write_ms)/(1.0+num_of_reads + num_of_writes) as numeric(10,1)) as ‘avg_io_stall_ms’
from sys.dm_io_virtual_file_stats(null,null)
order by avg_io_stall_ms desc

Top 50 IO per statement

        (qs.total_logical_reads + qs.total_logical_writes) /qs.execution_count as [Avg IO],
   (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 query_text,
  qt.dbid, dbname=db_name(qt.dbid),
FROM sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(qs.sql_handle) as qt
       [Avg IO] DESC

Rarely used indexes

declare @dbid int
select @dbid = db_id()
select objectname=object_name(s.object_id), s.object_id
 ,, i.index_id
 , user_seeks, user_scans, user_lookups, user_updates
from sys.dm_db_index_usage_stats s,
 sys.indexes i
where database_id = @dbid
and objectproperty(s.object_id,’IsUserTable’) = 1
and i.object_id = s.object_id
and i.index_id = s.index_id
order by (user_seeks + user_scans + user_lookups + user_updates) asc

Temp DB usage

SELECT t1.session_id,
(t1.internal_objects_alloc_page_count + task_alloc) as allocated,
(t1.internal_objects_dealloc_page_count + task_dealloc) as deallocated
 , t3.sql_handle, t3.statement_start_offset
 , t3.statement_end_offset, t3.plan_handle
from sys.dm_db_session_space_usage as t1,
  sys.dm_exec_requests t3,
(select session_id,
   sum(internal_objects_alloc_page_count) as task_alloc,
   sum (internal_objects_dealloc_page_count) as task_dealloc
      from sys.dm_db_task_space_usage group by session_id) as t2
where t1.session_id = t2.session_id and t1.session_id >50
and t1.database_id = 2   — tempdb is database_id=2
and t1.session_id = t3.session_id
order by allocated DESC

For the query above you will need to get the query and the plan handles (wich will have the actual SQL statement beeing executed) here is a query to get those :

(select text from sys.dm_exec_sql_text(put_sql_handle_here)) as sql_text
,(select query_plan from sys.dm_exec_query_plan(put_plan_handle_here)) as query_plan

List out indexes with the most contention e.g. blocking

declare @dbid int
select @dbid = db_id()
Select dbid=database_id, objectname=object_name(s.object_id)
 ,, i.index_id –, partition_number
 , row_lock_count, row_lock_wait_count
 , [block %]=cast (100.0 * row_lock_wait_count / (1 + row_lock_count) as numeric(15,2))
 , row_lock_wait_in_ms
 , [avg row lock waits in ms]=cast (1.0 * row_lock_wait_in_ms / (1 + row_lock_wait_count) as numeric(15,2))
from sys.dm_db_index_operational_stats (@dbid, NULL, NULL, NULL) s
 ,sys.indexes i
where objectproperty(s.object_id,’IsUserTable’) = 1
and i.object_id = s.object_id
and i.index_id = s.index_id
order by row_lock_wait_count desc

There were many more interesting queries, but it came clear that a lot of these queries were incorparated into the new Sql Performance DashBoard. (You will need SQL 2005 SP2 for this to work) Go and get the SQL Performance Dashboard >>here<< 

Below is a screenshot of this amazing tool. Everything is click-able and you can drill down into the details.

SQL Perf DashBoard.JPG


  • Recent Posts
  • Recent Comments
  • Archives
  • Categories
  • Meta