Gapminder visualization of demographic info

Hat-tip to Catallarchy for this awesome visualizer of demographic info.

The ability to move through time takes a snapshot in time and turns it into a good indication of behaviour.

Try a movie (playing the data) of children per women (fertility rate) against life expectancy.

or

Child Mortality against Fertility rate.

People are rational.

Have Fun

Paul

Trackback

RSS and the subscribe model

There has been a idea (a meme) building for a while around the use of RSS (or atom) as a possible way of sharing and propagating information both internally and externally in a business.

Adam Bosworth has a great podcast on this subject when he was talking about Google and his version of a distributed DNS-like database.

As I was reviewing by blog aggregator (sage) for firefox I found another link to a page dedicated about RSS in the enterprise.

RSS builds on the acceptance of XML as a basis for sharing information between companies, there are various formats out there for your standard B2B solution. With Oracle getting into the vertical integration game and almost every major software vendor pushing Service Oriented Architecture (SOA) I am sure it won't be long before RSS in some form appears as a solution for retrieving data from diverse data sources.

This is the database replication model where RSS tells you what has changed so you get everything incrementally which is faster. You also could only get what you want to receive, pulling that data you want, or even what you are allowed to see, which provides security and privacy.

The possibilities are big... what about a way where you take one or more feeds add some value and pass that new feed onto as a new RSS. This is what people call mashups or remixing.
For all the database people this is like a view on one or more tables, your VIEW of the data which is feed to you.

From a monitoring point of view, wouldn't it be nice to be able to pull the data you want and forget the rest.

The last interesting idea is based on a metaphor from this article if you pass around XML, you could pass S-expressions, pass around s-expressions and you can pass not only data but code. I had an idea or thought, after reading that article, that maybe the data in a database is really code after all, the tables being just functions (or relations in the original sense), the data model actually reflective the function of the business (the business model) in abstraction.

So you could in fact use RSS not only to share data or a view of data but also share code or functions.

Have Fun

SQL Server 2005: Creating V$ACTIVE_SESSION_HISTORY

Are you are DBA like me who works on both Oracle and SQL server (and MySQL)?

Do you miss Oracle performance views like v$session, v$sql and in Oracle 10G v$active_session_history?

I have created a series of tables and procedures which provide roughly the same functionality in SQL server 2005, basically mirroring the same view that is in Oracle 10G. I will backport the stuff to SQL server 2000 at some stage.

These scripts use the new dynamic management views (DMV) available to SQL Server 2005.
Specifically,

1. sys.dm_exec_requests
2. sys.dm_exec_sessions
   

I have tried to map the columns in v$active_session_history to columns from the DMVs, whilst keeping the overhead of selecting the data under control, which means limiting the number of joins. So unfortunately there is no rowwait data included in this iteration.


The steps to install this on your SQL Server 2005 instance

1. Copy and paste these scripts to a sql file.
2. Open up SQL Server Management Studio.
   
3. Select the instance and database where you want to create the tables. I suggest MSDB as the database or a dba specific database if you already have one.
   
4. Open the file as a query.
5. Execute the query. This will create the view, table and two stored procedures.
6. Create a job which executes the stored procedure usp_ins_sql2005_ASH every n secs. It would be prudent to start with 3-5 secs and see how much overhead that causes.
7. Create a job which executes the stored procedure usp_purge_sql2005_ASH. This stored procedure takes a parameter for the number of hours to keep.
   

Note: Check the growth of your table to determine how much data you want to keep. As busy systems are likely to have many rows per sample.

E.g. Sampling every 2 secs will create 30 samples per minute, 1800 samples per hour. If you have on average 5 sessions running or waiting per sample that will be 9000 rows per hour.

I will provide some useful reports from the data you have collected in the next article.

The scripts are attached at the end of this article. Due to html parsing < as a tag you will have to check the purge script before running.

/* Recreating V$ACTIVE_SESSION_HISTORY in SQL Server 2005
Version: 0.1
Created: Paul Moen 2006
*/


drop view uv_active_session_history
go
create view uv_active_session_history
as
select
getdate() as sample_time,
req.session_id,
req.sql_handle as sql_id,
req.plan_handle as sql_plan_hashvalue,
req.database_id,
req.user_id,
req.command as sql_opcode,
req.status as session_state,
req.blocking_session_id as blocking_session,
req.wait_type as event,
req.wait_time,
sess.program_name as program,
sess.client_interface_name as module
from sys.dm_exec_requests req join sys.dm_exec_sessions sess
on req.session_id = sess.session_id
where req.user_id <> 1
go

drop table active_session_history
go
select top 0 * into active_session_history
from uv_active_session_history
go

drop procedure usp_ins_sql2005_ASH
go
create  procedure usp_ins_sql2005_ASH
as
insert into active_session_history
select * from uv_active_session_history
go

drop procedure usp_purge_sql2005_ASH
go

create procedure usp_purge_sql2005_ASH
@hour int
as
delete from active_session_history
where sample_time < dateadd(hh,-1*@hour,getdate())
go