Number Of Changes After Last Update Statistics

set nocount on

Create table #tbl(ID int, tblname varchar(60), rowcnt int)

Declare @sql varchar(100)
Select @sql='Select ID=object_id(''?''),tblname=''?'',rowcnt=count(*) From ?'

Insert #tbl
Exec sp_msforeachtable @sql

Select o.name, abs(t.rowcnt - i.rowcnt)
From
(Select ID, rowcnt=max(rowcnt)
From sysindexes Group By id) i
Join sysobjects o
on i.id=o.id
Join #tbl t
On o.id=t.id
where o.type='u'
and o.name <> 'dtproperties'
and abs(t.rowcnt - i.rowcnt) > 0
Order By abs(t.rowcnt - i.rowcnt) desc

drop table #tbl

Informix – Database uptime

select
  case
        when ((sh_curtime-sh_boottime)/86400) > 1 then
        ((sh_curtime-sh_boottime)/86400)::int ||' day(s) and ' ||
        ((mod(sh_curtime-sh_boottime,86400))/3600)::int ||':' ||
        (mod(mod(sh_curtime-sh_boottime,86400),3600)/60)::int ||':' ||
        (mod(mod(mod(sh_curtime-sh_boottime,86400),3600),60))::int
        else
        '' || '' ||
        ((mod(sh_curtime-sh_boottime,86400))/3600)::int ||':' ||
        (mod(mod(sh_curtime-sh_boottime,86400),3600)/60)::int ||':' ||
        (mod(mod(mod(sh_curtime-sh_boottime,86400),3600),60))::int
  end
from   sysmaster:sysshmvals ;

SQL Server – Database uptime

SELECT
case
When (DATEDIFF(ss,crdate,GETDATE())/86400 ) > 1  then
convert(varchar(3),(DATEDIFF(ss,crdate,GETDATE())/86400 )) + ' day(s) and ' +
convert(varchar(2),((DATEDIFF(ss,crdate,GETDATE()) % 86400)/3600)) + ':' +
convert(varchar(2),(((DATEDIFF(ss,crdate,GETDATE()) % 86400)%3600)/60 )) + ':' +
convert(varchar(2),(((DATEDIFF(ss,crdate,GETDATE()) % 86400)%3600)%60))
Else
convert(varchar(2),((DATEDIFF(ss,crdate,GETDATE()) % 86400)/3600)) + ':' +
convert(varchar(3),(((DATEDIFF(ss,crdate,GETDATE()) % 86400)%3600)/60 )) + ':' +
convert(varchar(3),(((DATEDIFF(ss,crdate,GETDATE()) % 86400)%3600)%60) )
End</div>
FROM sysdatabases WHERE NAME='tempdb'

SQL Server – List of indexes

DECLARE @IndexInfo
TABLE (index_name         varchar(250)
      ,index_description  varchar(250)
      ,index_keys         varchar(250)
      )
INSERT INTO @IndexInfo
exec sp_msforeachtable 'sp_helpindex ''?'''
select * from @IndexInfo

SQL Server – Useful Hardware Information

DECLARE @ProcessorNameString VARCHAR(60);
DECLARE @OSName VARCHAR(60);
DECLARE @OSSP VARCHAR(60);
CREATE TABLE #tblStats
(
[Index] INT,
[Name] VARCHAR(200),
Internal_Value VARCHAR(50),
Character_Value VARCHAR(200)
)
INSERT INTO 
EXEC master.dbo.xp_msver
EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',
N'HARDWARE\DESCRIPTION\System\CentralProcessor',
N'ProcessorNameString',
@ProcessorNameString OUTPUT,
N'no_output'
Update #tblStats SET Character_Value = @ProcessorNameString Where [Index] = 18
EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',
N'SOFTWARE\Microsoft\Windows NT\CurrentVersion',
N'ProductName',
@OSName OUTPUT,
N'no_output'
EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',
N'SOFTWARE\Microsoft\Windows NT\CurrentVersion',
N'CSDVersion',
@OSSP OUTPUT,
N'no_output'
Update #tblStats SET Character_Value = @OSNAME + ' ' + @OSSP Where [Index] = 15
select Name,Character_Value from #tblstats where [Index] in ( 8,4,7,15,16,18,19 )
DROP TABLE #tblStats

SQL Server – Server side profiling

/***********************************************/

/*    Start Server Side Trace                  */

/***********************************************/

– Declare variables

DECLARE @rc INT

DECLARE @TraceID INT

DECLARE @maxFileSize bigint

DECLARE @fileName NVARCHAR(128)

DECLARE @on bit

– Set values

SET @maxFileSize = 5  – MB

SET @fileName = N’C:\TestTrace’

SET @on = 1

SET @EventId = 45 – SQL:StmtCompleted, More option

– Create trace

EXEC @rc = sp_trace_create @TraceID output, 0, @fileName, @maxFileSize, NULL

– If error end process

IF (@rc != 0) GOTO error

– Set the events and data to collect, More options

EXEC sp_trace_setevent @TraceID, @EventId,  1, @on

EXEC sp_trace_setevent @TraceID, @EventId, 12, @on

EXEC sp_trace_setevent @TraceID, @EventId, 13, @on

EXEC sp_trace_setevent @TraceID, @EventId, 14, @on

EXEC sp_trace_setevent @TraceID, @EventId, 15, @on

EXEC sp_trace_setevent @TraceID, @EventId, 16, @on

EXEC sp_trace_setevent @TraceID, @EventId, 17, @on

– Set Filters

– filter1 include databaseId = 6

EXEC sp_trace_setfilter @TraceID, 3, 1, 0, 6

– filter2 exclude application SQL Profiler

EXEC sp_trace_setfilter @TraceID, 10, 0, 7, N’SQL Profiler’

– Start the trace

EXEC sp_trace_setstatus @TraceID, 1

– display trace id for future references

SELECT TraceID=@TraceID

GOTO finish

– error trap

error:

SELECT ErrorCode=@rc

– exit

finish:

GO

/***********************************************/

/*  Stop and close Server Side Trace           */

/***********************************************/

– Stop Trace

EXEC sp_trace_setstatus @traceid=2, @status=0

– Close Trace

EXEC sp_trace_setstatus @traceid=2, @status=2

– Check the trace file

SELECT * FROM ::fn_trace_gettable(‘C:\TestTrace.trc’, 999) –where 999 is the number of rollover trace files to read

Windows batch – Parse a delimited file and use columns

@echo off

:Loop

set TEXT_T=< a copy of Delimited file with comma “,” >

FOR /F “usebackq delims=, tokens=1*” %%i in (%TEXT_T%) do (

If “%%i”==”ECHO is off.” goto :End

::

echo %%i

::

echo %%j> %TEXT_T%

)

goto Loop

:End

SQL Server – Column name of a table

select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME=<Table Name>;

SQL Server – Changing compatibility mode

Changing compatiblity mode in 3 steps :

• ALTER DATABASE database_name SET SINGLE_USER;
• One of these :

ALTER DATABASE database_name SET COMPATIBILITY_LEVEL = { 80 | 90 | 100 }

EXEC sp_dbcmptlevel database_name, compatibility_level value;

• ALTER DATABASE database_name SET MULTI_USER;

Compatibility modes :

60 = SQL Server 6.0
65 = SQL Server 6.5
70 = SQL Server 7.0
80 = SQL Server 2000
90 = SQL Server 2005
100 = SQL Server 2008

Informix – DBSpace Alert

– Putting information in ph_threshold table ( We can exclude some dbspaces )

– Delete from ph_threshold where task_name = “Dbspace full alarm”;

insert into ph_threshold values (0, “DBSPACE_PCT_FULL_RED”, “Dbspace full alarm”, 90, “NUMERIC”, “Dbspace percentage full alarm – critical”);

insert into ph_threshold values (0, “DBSPACE_PCT_FULL_YELLOW”, “Dbspace full alarm”, 70, “NUMERIC”, “Dbspace percentage full alarm – warning”);

insert into ph_threshold values (0, “DBSPACE_PCT_FULL_EXCLUDE”, “Dbspace full alarm”, “””, “STRING”, “Comma separated list of dbspaces (each in single quites) to exclude from the check”);

– Checking space and put information in ph_alert

– DROP FUNCTION _sysadmin_dbspace_full;

CREATE FUNCTION _sysadmin_dbspace_full(task_id INT, task_seq INT) RETURNING INTEGER

DEFINE dbspace_name CHAR(129);

DEFINE exclude_list LVARCHAR(1024);

DEFINE dbspace_query LVARCHAR(2048);

DEFINE dbspace_pct_free DECIMAL(4,2);

DEFINE dbspace_pct_free_red DECIMAL(4,2);

DEFINE dbspace_pct_free_yellow DECIMAL(4,2);

ON EXCEPTION IN (-201, -217)

INSERT INTO ph_alert

(ID, alert_task_id, alert_task_seq, alert_type, alert_color, alert_object_type, alert_object_name, alert_message, alert_action)

VALUES

(0,task_id, task_seq, “ERROR”, “RED”, “SQL”, exclude_list, “Dbspace full alarm: Exclude list [" || exclude_list || "] wrong format!”, NULL);

RETURN 0;

END EXCEPTION;

LET exclude_list = (select value from ph_threshold where name = “DBSPACE_PCT_FULL_EXCLUDE” and task_name = “Dbspace full alarm”);

IF exclude_list IS NULL OR exclude_list = ” THEN

LET exclude_list = “””;

END IF

LET dbspace_query = “select trim(name) dbspace, round ((sum(nfree))/(sum(chksize))*100,2) percent_free from sysmaster:sysdbspaces d, sysmaster:syschunks c where d.dbsnum = c.dbsnum and trim(name) not in (” || exclude_list || “) and is_sbspace != 1 group by 1 union select trim(name)||’ (UD)’, round(100/sum(udsize)*sum(udfree),2) from sysmaster:sysdbspaces d, sysmaster:syschunks c where d.dbsnum=c.dbsnum and trim(name) not in (” || exclude_list || “) and is_sbspace=1 group by 1 union select trim(name)||’ (MD)’, round(100/sum(mdsize)*sum(nfree),2) from sysmaster:sysdbspaces d, sysmaster:syschunks c where d.dbsnum=c.dbsnum and trim(name) not in (” || exclude_list || “) and is_sbspace=1 group by 1 INTO TEMP __t_dbspace_full”;

EXECUTE IMMEDIATE dbspace_query;

let dbspace_pct_free_red = (select value from ph_threshold where name = “DBSPACE_PCT_FULL_RED” and task_name = “Dbspace full alarm”);

IF dbspace_pct_free_red IS NULL THEN

INSERT INTO ph_alert

(ID, alert_task_id, alert_task_seq, alert_type, alert_color, alert_object_type, alert_object_name, alert_message, alert_action)

VALUES

(0,task_id, task_seq, “ERROR”, “YELLOW”, “SQL”,

dbspace_name,

“Task parameter [dbspace_pct_free_red] for task [Dbspace full alarm] is not defined. Task not run.”,

NULL

);

RETURN 0;

END IF

let dbspace_pct_free_yellow = (select value from ph_threshold where name = “DBSPACE_PCT_FULL_YELLOW” and task_name = “Dbspace full alarm”);

IF dbspace_pct_free_yellow IS NULL THEN

INSERT INTO ph_alert

(ID, alert_task_id, alert_task_seq, alert_type, alert_color, alert_object_type, alert_object_name, alert_message, alert_action)

VALUES

(0,task_id, task_seq, “ERROR”, “YELLOW”, “SQL”,

dbspace_name,

“Task parameter [dbspace_pct_free_yellow] for task [Dbspace full alarm] is not defined. Task not run.”,

NULL

);

RETURN 0;

END IF

FOREACH

SELECT dbspace,

percent_free

INTO dbspace_name, dbspace_pct_free

FROM __t_dbspace_full

WHERE percent_free <= (100.00 – dbspace_pct_free_yellow)

AND percent_free  > (100.00 – dbspace_pct_free_red)

ORDER BY percent_free DESC

INSERT INTO ph_alert

(ID, alert_task_id, alert_task_seq, alert_type, alert_color, alert_object_type, alert_object_name, alert_message, alert_action)

VALUES

(0,task_id, task_seq, “WARNING”, “YELLOW”, “DBSPACE”,

dbspace_name,

“Dbspace [" || trim(dbspace_name) || "] has less than ” || (100.00-dbspace_pct_free_yellow) || ” percent free “,

NULL

);

END FOREACH

FOREACH

SELECT dbspace,

percent_free

INTO dbspace_name, dbspace_pct_free

FROM __t_dbspace_full

WHERE percent_free  <= (100.00 – dbspace_pct_free_red)

ORDER BY percent_free DESC

INSERT INTO ph_alert

(ID, alert_task_id, alert_task_seq, alert_type, alert_color, alert_object_type, alert_object_name, alert_message, alert_action)

VALUES

(0,task_id, task_seq, “WARNING”, “RED”, “DBSPACE”,

dbspace_name,

“Dbspace [" || trim(dbspace_name) || "] has less than ” || (100.00-dbspace_pct_free_red) || ” percent free “,

NULL

);

END FOREACH

DROP TABLE __t_dbspace_full;

RETURN 0;

END FUNCTION;

– Put above function in ph_task for running automatically

– DELETE FROM ph_task WHERE tk_name = “Dbspace full alarm”;

INSERT INTO ph_task

(

tk_name,

tk_type,

tk_group,

tk_description,

tk_execute,

tk_start_time,

tk_stop_time,

tk_frequency

)

VALUES

(

“Dbspace full alarm”,

“TASK”,

“DISK”,

“Checks if dbspace free space has dropped below configured thresholds”,

“_sysadmin_dbspace_full”,

DATETIME(00:00:00) HOUR TO SECOND,

NULL,

INTERVAL ( 1 ) HOUR TO HOUR

);

– Send Email Procedure

– DROP PROCEDURE SendEmail

CREATE PROCEDURE SendEmail( subject char(8), body char(200) )

SYSTEM ‘echo “‘ || body || ‘” > /tmp/spacealert’ ;

SYSTEM ‘mail -s “‘ || subject || ‘Alert For SPACE” informix@localhost </tmp/spacealert’ ;

SYSTEM ‘rm -f /tmp/spacealert’ ;

END PROCEDURE ;

– Trigger on insertion in ph_alert

– DROP TRIGGER SpaceAlert

CREATE TRIGGER SpaceAlert

INSERT ON ph_alert

REFERENCING NEW AS post

FOR EACH ROW ( EXECUTE PROCEDURE SendEmail( post.alert_color,post.alert_message ) ) ;