Introducing DoxieData.com

DoxieData.com is a new data exchange where you can purchase curated data sets.  The initial offering is end of day stock market data.  Doxie Data

ColtonVending

We have recently started a vending machine company.  If you are in the market for a vending machine please let us know: https://coltonvending.godaddysites.com/

New Project Announcement

I've been working on a new project that I'd like to announce.  In an attempt to help bring more transparency to health care costs I've started work on a new project, www.medpricemonkey.com.  This website will provide detailed information on medical procedure cost including what people are paying for them at different providers.

I hope that this information will help consumers make more intelligent decisions about what they pay for their medical bills. I also hope that the transparency of that this data provides will help drive down prices in the medical industry as a whole.  Please let me know what you think in the comments.

Identify Foreign Keys in a DB2 database

When trying to identify what foreign keys existing in a DB2 database the sysibm.sysrels table is the best place to look.  The query below will give you all the information you need.

select *

from sysibm.sysrels

where tbname = 'CHILD TABLE'

and reftbname = 'PARENT TABLE'

Persist Stored Procedure Execution Counts

/*

SELECT * FROM sys.procedures AS p left JOIN sys.dm_exec_procedure_stats AS s ON s.[object_id] = p.[object_id]

drop table stored_procedure_monitor

use mydatabase

CREATE TABLE dbo.stored_procedure_monitor(

    stored_procedure_monitor_id int identity(1,1),

    db_id         int             NOT NULL,

    obj_id        int             NOT NULL,

    schema_id     int             NOT NULL,

    db_nme        varchar(256)    NOT NULL,

    obj_nme       varchar(256)    NOT NULL,

       exp_dte       smalldatetime   NULL,

    create_dte    datetime        DEFAULT (getdate()) NOT NULL,

    create_uid    char(10)        DEFAULT (substring(suser_sname(),charindex('\',suser_sname())+(1),(10))) NOT NULL,

    update_dte    datetime        DEFAULT (getdate()) NOT NULL,

    update_uid    char(10)        DEFAULT (substring(suser_sname(),charindex('\',suser_sname())+(1),(10))) NOT NULL,

    CONSTRAINT PK__stored_procedure_monitor PRIMARY KEY CLUSTERED (stored_procedure_monitor_id)

)

drop table stored_procedure_monitor_executions

CREATE TABLE dbo.stored_procedure_monitor_executions(

    stored_procedure_monitor_id int NOT NULL,

    cached_time   datetime        NOT NULL,

    sql_handle varbinary(64) NOT NULL,

    plan_handle varbinary(64) NOT NULL,

    last_execution_time datetime  NOT NULL,

    execution_count bigint NOT NULL,

    total_worker_time bigint NOT NULL,

       min_worker_time bigint NOT NULL,

       max_worker_time bigint NOT NULL,

       total_physical_reads bigint NOT NULL,

       min_physical_reads bigint NOT NULL,

       max_physical_reads bigint NOT NULL,

       total_logical_reads bigint NOT NULL,

       min_logical_reads bigint NOT NULL,

       max_logical_reads bigint NOT NULL,

       total_logical_writes bigint NOT NULL,

       min_logical_writes bigint NOT NULL,

       max_logical_writes bigint NOT NULL,     

       total_elapsed_time bigint NOT NULL,     

       min_elapsed_time bigint NOT NULL,

       max_elapsed_time bigint NOT NULL,

       exp_dte smalldatetime null,

    create_dte    datetime        DEFAULT (getdate()) NOT NULL,

    create_uid    char(10)        DEFAULT (substring(suser_sname(),charindex('\',suser_sname())+(1),(10))) NOT NULL,

    update_dte    datetime        DEFAULT (getdate()) NOT NULL,

    update_uid    char(10)        DEFAULT (substring(suser_sname(),charindex('\',suser_sname())+(1),(10))) NOT NULL,

    CONSTRAINT PK__stored_procedure_monitor_executions PRIMARY KEY CLUSTERED (stored_procedure_monitor_id, cached_time, sql_handle, plan_handle)

)

*/

use mydatabase

go

create table #procedure (

db_id int NOT NULL,

db_nme varchar(256) NOT NULL,

obj_id int NOT NULL,

obj_nme varchar(256) NOT NULL,

schema_id int NOT NULL)

declare @db_id int

declare @db_nme as varchar(256)

declare @sql nvarchar(4000)

declare curs cursor for

select distinct database_id, name

from master.sys.databases

open curs

fetch next from curs into @db_id, @db_nme

while @@FETCH_STATUS = 0

begin

       set @sql = 'select ' + cast(@db_id as varchar) + ' as db_id, ''' + @db_nme + ''' as db_nme, object_id as obj_id, name as obj_nme, schema_id from ' + @db_nme + '.sys.procedures'

      

       insert into #procedure (db_id, db_nme, obj_id, obj_nme, schema_id)

       exec sp_executesql @sql

       fetch next from curs into @db_id, @db_nme

end

close curs

deallocate curs

--insert any new stored procedures that were identified

insert into mydatabase.dbo.stored_procedure_monitor(db_id, db_nme, obj_id, obj_nme, schema_id)

select db_id, db_nme, obj_id, obj_nme, schema_id

from #procedure p

where not exists

       (select 1 from mydatabase.dbo.stored_procedure_monitor p2

       where p.db_id = p2.db_id

       and p.obj_id = p2.obj_id

       and p.schema_id = p2.schema_id)

--expire any stored procedures which no longer exist

update mydatabase.dbo.stored_procedure_monitor

set exp_dte = getdate()

from mydatabase.dbo.stored_procedure_monitor p2

where not exists (select 1 from #procedure p

       where p.db_id = p2.db_id

       and p.obj_id = p2.obj_id

       and p.schema_id = p2.schema_id)

and exp_dte is null

--unexpire anything that exists again

update mydatabase.dbo.stored_procedure_monitor

set exp_dte = null

from mydatabase.dbo.stored_procedure_monitor p2

where exists (select 1 from #procedure p

       where p.db_id = p2.db_id

       and p.obj_id = p2.obj_id

       and p.schema_id = p2.schema_id

       and exp_dte is not null)

--------------------------------------------------------------------------------------

---------------begin stored proc executions

CREATE TABLE #executions(

       db_id int not null,

       obj_id int not null,

    cached_time   datetime        NOT NULL,

    sql_handle varbinary(64) NOT NULL,

    plan_handle varbinary(64) NOT NULL,

    last_execution_time datetime  NOT NULL,

    execution_count bigint NOT NULL,

    total_worker_time bigint NOT NULL,

       min_worker_time bigint NOT NULL,

       max_worker_time bigint NOT NULL,

       total_physical_reads bigint NOT NULL,

       min_physical_reads bigint NOT NULL,

       max_physical_reads bigint NOT NULL,

       total_logical_reads bigint NOT NULL,

       min_logical_reads bigint NOT NULL,

       max_logical_reads bigint NOT NULL,

       total_logical_writes bigint NOT NULL,

       min_logical_writes bigint NOT NULL,

       max_logical_writes bigint NOT NULL,     

       total_elapsed_time bigint NOT NULL,     

       min_elapsed_time bigint NOT NULL,

       max_elapsed_time bigint NOT NULL)

             

declare curs cursor for

select distinct db_id, db_nme

from mydatabase.dbo.stored_procedure_monitor

where exp_dte is null

open curs

fetch next from curs into @db_id, @db_nme

while @@FETCH_STATUS = 0

begin

       set @sql = 'SELECT ' + CAST(@db_id as varchar) + ' as db_id, s.object_id, s.cached_time, s.sql_handle, s.plan_handle, s.last_execution_time, s.execution_count, s.total_worker_time,

       s.min_worker_time, s.max_worker_time, s.total_physical_reads, s.min_physical_reads, s.max_physical_reads, s.total_logical_reads, s.min_logical_reads,

       s.max_logical_reads, s.total_logical_writes, s.min_logical_writes, s.max_logical_writes, s.total_elapsed_time, s.min_elapsed_time, s.max_elapsed_time

       FROM ' + @db_nme + '.sys.procedures AS p

       INNER JOIN sys.dm_exec_procedure_stats AS s ON s.object_id = p.object_id and s.database_id = ' + CAST(@db_id as varchar)

       --select * from sys.dm_exec_procedure_stats

       --insert into #procedure (db_id, db_nme, obj_id, obj_nme, schema_id)

      

       insert into #executions (db_id, obj_id, cached_time, sql_handle, plan_handle, last_execution_time, execution_count, total_worker_time,

       min_worker_time, max_worker_time, total_physical_reads, min_physical_reads, max_physical_reads, total_logical_reads, min_logical_reads,

       max_logical_reads, total_logical_writes, min_logical_writes, max_logical_writes, total_elapsed_time, min_elapsed_time, max_elapsed_time)

       exec sp_executesql @sql

       fetch next from curs into @db_id, @db_nme

end

close curs

deallocate curs

--insert new stored procedure executions

insert into mydatabase.dbo.stored_procedure_monitor_executions (stored_procedure_monitor_id, cached_time, sql_handle, plan_handle, last_execution_time, execution_count, total_worker_time,

       min_worker_time, max_worker_time, total_physical_reads, min_physical_reads, max_physical_reads, total_logical_reads, min_logical_reads,

       max_logical_reads, total_logical_writes, min_logical_writes, max_logical_writes, total_elapsed_time, min_elapsed_time, max_elapsed_time)

select stored_procedure_monitor_id, cached_time, sql_handle, plan_handle, last_execution_time, execution_count, total_worker_time,

       min_worker_time, max_worker_time, total_physical_reads, min_physical_reads, max_physical_reads, total_logical_reads, min_logical_reads,

       max_logical_reads, total_logical_writes, min_logical_writes, max_logical_writes, total_elapsed_time, min_elapsed_time, max_elapsed_time

from #executions e

inner join dbo.stored_procedure_monitor spm on e.obj_id = spm.obj_id and e.db_id = spm.db_id

where not exists (select 1

       from mydatabase.dbo.stored_procedure_monitor_executions spme

       where spme.stored_procedure_monitor_id = spm.stored_procedure_monitor_id

       and spme.cached_time = e.cached_time

       and spme.sql_handle = e.sql_handle

       and spme.plan_handle = e.plan_handle)

      

--update stored procedure executions stats

update mydatabase.dbo.stored_procedure_monitor_executions

set last_execution_time = e.last_execution_time,

execution_count = e.execution_count,

total_worker_time = e.total_worker_time,

min_worker_time = e.min_worker_time,

max_worker_time = e.max_worker_time,

total_physical_reads = e.total_physical_reads,

min_physical_reads = e.min_physical_reads,

max_physical_reads = e.max_physical_reads,

total_logical_reads = e.total_logical_reads,

min_logical_reads = e.min_logical_reads,

max_logical_reads = e.max_logical_reads,

total_logical_writes = e.total_logical_writes,

min_logical_writes = e.min_logical_writes,

max_logical_writes = e.max_logical_writes,

total_elapsed_time = e.total_elapsed_time,

min_elapsed_time = e.min_elapsed_time,

max_elapsed_time = e.max_elapsed_time,

update_dte = GETDATE()

from #executions e

inner join dbo.stored_procedure_monitor spm on e.obj_id = spm.obj_id and e.db_id = spm.db_id

where spm.stored_procedure_monitor_id = mydatabase.dbo.stored_procedure_monitor_executions.stored_procedure_monitor_id and

mydatabase.dbo.stored_procedure_monitor_executions.cached_time = e.cached_time and

mydatabase.dbo.stored_procedure_monitor_executions.sql_handle = e.sql_handle and

mydatabase.dbo.stored_procedure_monitor_executions.plan_handle = e.plan_handle and

mydatabase.dbo.stored_procedure_monitor_executions.last_execution_time <> e.last_execution_time

--record the time we noted the plan was out of cache

update mydatabase.dbo.stored_procedure_monitor_executions

set exp_dte = GETDATE(),

update_dte = GETDATE()

where not exists (select 1

       from #executions e

       inner join dbo.stored_procedure_monitor spm on e.obj_id = spm.obj_id and e.db_id = spm.db_id

       where spm.stored_procedure_monitor_id = mydatabase.dbo.stored_procedure_monitor_executions.stored_procedure_monitor_id and

       mydatabase.dbo.stored_procedure_monitor_executions.cached_time = e.cached_time and

       mydatabase.dbo.stored_procedure_monitor_executions.sql_handle = e.sql_handle and

       mydatabase.dbo.stored_procedure_monitor_executions.plan_handle = e.plan_handle and

       mydatabase.dbo.stored_procedure_monitor_executions.last_execution_time = e.last_execution_time)

and exp_dte is null

      

/*

select spm.db_nme,

spm.obj_nme,

SUM(execution_count) as execution_count,

SUM(total_worker_time)/SUM(execution_count) as avg_cpu_time,

MIN(min_worker_time) as min_worker_time,

MAX(max_worker_time) as max_worker_time,

sum(total_elapsed_time) total_elapsed_time,

sum(total_elapsed_time)/SUM(execution_count) as avg_execution_time,

min(min_elapsed_time) as min_elapsed_time,

max(max_elapsed_time) as max_elapsed_time

from mydatabase.dbo.stored_procedure_monitor spm

left outer join mydatabase.dbo.stored_procedure_monitor_executions spme on spm.stored_procedure_monitor_id = spme.stored_procedure_monitor_id

group by spm.db_nme, spm.obj_nme

order by sum(total_elapsed_time)/SUM(execution_count) desc

*/

Identify unused indexes and tables

The below query will persist data from sys.dm_db_index_usage_stats so that a history of access to indexes can be maintained following SQL Server restarts.  This script should be scheduled to run periodically (nightly is ideal).

use mydatabase

go

if not exists (select 1 from sysobjects where name = 'index_usage_stats')

       CREATE TABLE dbo.index_usage_stats(

              index_usage_stats_id int identity (1,1) not null,

              database_name sysname NOT NULL,

              database_id smallint NOT NULL,

              object_name sysname NOT NULL,

              object_id int NOT NULL,

              index_name sysname NULL,

              index_id int NOT NULL,

              user_seeks bigint NOT NULL,

              user_scans bigint NOT NULL,

              user_lookups bigint NOT NULL,

              user_updates bigint NOT NULL,

              last_user_seek datetime NULL,

              last_user_scan datetime NULL,

              last_user_lookup datetime NULL,

              last_user_update datetime NULL,

              system_seeks bigint NOT NULL,

              system_scans bigint NOT NULL,

              system_lookups bigint NOT NULL,

              system_updates bigint NOT NULL,

              last_system_seek datetime NULL,

              last_system_scan datetime NULL,

              last_system_lookup datetime NULL,

              last_system_update datetime NULL,

              dpages bigint null,

              create_dte smalldatetime not null default getdate(),

              instance_restart_id int

       )

if not exists (select 1 from sysobjects where name = 'instance_restart')

       create table dbo.instance_restart (

              instance_restart_id int identity(1,1) not null,

              instance_restart_dte smalldatetime not null)

declare @instance_restart_id as int

declare @instance_restart_dte as smalldatetime

select @instance_restart_dte = create_date FROM master.sys.databases WHERE database_id = 2 --sql was last restarted at the time tempdb was created

IF NOT EXISTS (select 1 from mydatabase.dbo.instance_restart where instance_restart_dte = @instance_restart_dte)

       insert into mydatabase.dbo.instance_restart (instance_restart_dte) select (@instance_restart_dte)

select @instance_restart_id = max(instance_restart_id) from mydatabase.dbo.instance_restart

declare @dbname as sysname

declare @dbid int

declare @sql as nvarchar(4000)

declare curs cursor for

select name, database_id

from master.sys.databases

where database_id > 4 --do not monitor system databases, we don't necessarily have access

open curs

fetch next from curs into @dbname, @dbid

while @@fetch_status = 0

begin

       print @dbname

       set @sql = '

       insert into mydatabase.dbo.index_usage_stats (database_name, database_id, object_name, object_id, index_name, index_id, user_seeks, user_scans, user_lookups, user_updates,

              last_user_seek, last_user_scan, last_user_lookup, last_user_update, system_seeks, system_scans, system_lookups,system_updates, last_system_seek, last_system_scan,

              last_system_lookup, last_system_update, dpages, instance_restart_id)

       select ''' + @dbname + ''' as database_name, ' + cast(@dbid as varchar) + ' as database_id, o.name as object_name, o.object_id, i.name as index_name,

       i.index_id, isnull(s.user_seeks, 0), isnull(s.user_scans, 0), isnull(s.user_lookups, 0), isnull(s.user_updates, 0), s.last_user_seek, s.last_user_scan,

       s.last_user_lookup, s.last_user_update, isnull(s.system_seeks, 0), isnull(s.system_scans, 0),

       isnull(s.system_lookups, 0), isnull(s.system_updates, 0), s.last_system_seek, s.last_system_scan, s.last_system_lookup,

       s.last_system_update, i2.dpages, ' + cast(@instance_restart_id as varchar) + '

       from ' + @dbname + '.sys.indexes i

       inner join ' + @dbname + '.sys.objects o on o.object_id = i.object_id

       inner join ' + @dbname + '.dbo.sysindexes i2 on i.object_id = i2.id and i.index_id = i2.indid

       left outer join sys.dm_db_index_usage_stats s on i.object_id = s.object_id and i.index_id = s.index_id and s.database_id = ' + cast(@dbid as varchar)

       print @sql

       exec sp_executesql @sql

       fetch next from curs into @dbname, @dbid

end

close curs

deallocate curs