Friday, 11 April 2014

Configure alerts for CPU utilization higher than 80% for more than 5mins

SET NOCOUNT ON
DECLARE @TimeNow bigint
SELECT @TimeNow = cpu_ticks / convert(float, ms_ticks) from sys.dm_os_sys_info
-- Collect Data from DMV
Select record_id, dateadd(ms, -1 * (@TimeNow - [timestamp]),
GetDate())EventTime, SQLSvcUtilization, SystemIdle,
(100 - SystemIdle - SQLSvcUtilization) AS OtherOSProcessUtilization into #tempCPURecords
from ( select record.value('(./Record/@id)[1]', 'int')record_id,
record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]','int')SystemIdle,
record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]','int')SQLSvcUtilization,
timestamp
from ( select timestamp, convert(xml, record)record
from sys.dm_os_ring_buffers
where ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
and record like '%<SystemHealth>%')x )y  order by record_id desc
To send detailed sql server session reports consuming high cpu
 For a dedicated SQL Server you can monitor 'SQLProcessUtilization'
 if (select avg(SQLSvcUtilization) from #temp where EventTime>dateadd(mm,-5,getdate()))>=80
For a Shared SQL Server you can monitor 'SQLProcessUtilization'+'OtherOSProcessUtilization'
if (select avg(SQLSvcUtilization+OtherOSProcessUtilization)
from #tempCPURecords where EventTime>dateadd(mm,-5,getdate()))>=50
begin
print 'CPU Alert Condition Ture, Sending Email..'
DECLARE @tableHTML  NVARCHAR(MAX) ;
SET @tableHTML =
    N'<H1>High CPU Utilization Reported</H1>' +
    N'<H2>SQL Server Session Details</H2>' +
    N'<table border="1">' +
    N'<tr><th>SPID</th><th>Status</th><th>Login</th><th>Host</th><th>BlkBy</th>'+
N'<th>DatabaseID</th><th>CommandType</th><th>SQLStatement</th><th>ElapsedMS</th>'+
N'<th>CPUTime</th><th>IOReads</th><th>IOWrites</th><th>LastWaitType</th>'+
N'<th>StartTime</th><th>Protocol</th><th>ConnectionWrites</th>'+
N'<th>ConnectionReads</th><th>ClientAddress</th><th>Authentication</th>'+
CAST ( ( SELECT  TOP 50 -- or all by using *
td= er.session_id,'',
td= ses.status,'',
td= ses.login_name,'',
td= ses.host_name,'',  
td= er.blocking_session_id,'',
td= er.database_id,'',
td= er.command,'',
td= st.text,'',
td= er.total_elapsed_time,'',
td= er.cpu_time,'',
td= er.reads,'',
td= er.writes,'',
td= er.last_wait_type,'',
td= er.start_time,'',
td= con.net_transport,'',
td= con.num_writes,'',
td= con.num_reads,'',
td= con.client_net_address,'',
td= con.auth_scheme,''
FROM sys.dm_exec_requests er
OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) st
LEFT JOIN sys.dm_exec_sessions ses
ON ses.session_id = er.session_id  LEFT JOIN sys.dm_exec_connections con
ON con.session_id = ses.session_id
WHERE er.session_id > 50
ORDER BY er.cpu_time DESC ,er.blocking_session_id
FOR XML PATH('tr'), TYPE
)AS NVARCHAR(MAX))+
N'</table>'
-- Change SQL Server Email notification code here


EXEC msdb.dbo.sp_send_dbmail
@recipients='hhhhhhhh.dddd@xxxx.com',
@profile_name = 'xxx',  
@subject = 'ServerName:Last 5 Minutes Avg CPU Utilization Over 80%',
@body = @tableHTML,
@body_format = 'HTML';
END
-- Drop the Temporary Table
DROP Table #tempCPURecords

Tuesday, 4 March 2014

convert comma seperated values into distinct values

CREATE FUNCTION [dbo].Split1(@input AS Varchar(4000) )

RETURNS

      @Result TABLE(Value BIGINT)

AS

BEGIN

      DECLARE @str VARCHAR(20)

      DECLARE @ind Int

      IF(@input is not null)

      BEGIN

            SET @ind = CharIndex(',',@input)

            WHILE @ind > 0

            BEGIN

                  SET @str = SUBSTRING(@input,1,@ind-1)

                  SET @input = SUBSTRING(@input,@ind+1,LEN(@input)-@ind)

                  INSERT INTO @Result values (@str)

                  SET @ind = CharIndex(',',@input)

            END

            SET @str = @input

            INSERT INTO @Result values (@str)

      END

      RETURN

END



DECLARE @str VARCHAR(4000) = '6,7,7,8,10,12,13,14,16,44,46,47,394,396,417,488,714,717,718,719,722,725,811,818,832,833,836,837,846,913,914,919,922,923,924,925,926,927,927,928,929,929,930,931,932,934,935,1029,1072,1187,1188,1192,1196,1197,1199,1199,1199,1199,1200,1201,1202,1203,1204,1205,1206,1207,1208,1209,1366,1367,1387,1388,1666,1759,1870,2042,2045,2163,2261,2374,2445,2550,2676,2879,2880,2881,2892,2893,2894'

SELECT * FROM dbo.split1 ( @str )

--==================================================

DECLARE @input VARCHAR(4000) = '6,7,7,8,10,12,13,14,16,44,46,47,394,396,417,488,714,717,718,719,722,725,811,818,832,833,836,837,846,913,914,919,922,923,924,925,926,927,927,928,929,929,930,931,932,934,935,1029,1072,1187,1188,1192,1196,1197,1199,1199,1199,1199,1200,1201,1202,1203,1204,1205,1206,1207,1208,1209,1366,1367,1387,1388,1666,1759,1870,2042,2045,2163,2261,2374,2445,2550,2676,2879,2880,2881,2892,2893,2894'

drop table #result
create table #result
(
    value bigint
)

      DECLARE @str VARCHAR(20)

      DECLARE @ind Int

      IF(@input is not null)

      BEGIN

            SET @ind = CharIndex(',',@input)

            WHILE @ind > 0

            BEGIN

                  SET @str = SUBSTRING(@input,1,@ind-1)

                  SET @input = SUBSTRING(@input,@ind+1,LEN(@input)-@ind)

                  INSERT INTO #result values (@str)

                  SET @ind = CharIndex(',',@input)

            END

            SET @str = @input

            INSERT INTO #result values (@str)

      END

select * from #result 

--====================================================================
on special demand from a good friend, 
i've modifed the function to work for character comma seperated values too.
Here you go-

create FUNCTION [dbo].Split_charval(@input AS Varchar(4000) )
RETURNS
      @Result TABLE(Value VARCHAR(200))
AS
BEGIN
      DECLARE @str VARCHAR(200)
      DECLARE @ind VARCHAR(200)
      IF(@input is not null)
      BEGIN
            SET @ind = CharIndex(',',@input)
            WHILE @ind > 0
            BEGIN
                  SET @str = SUBSTRING(@input,1,@ind-1)
                  SET @input = SUBSTRING(@input,@ind+1,LEN(@input)-@ind)
                  INSERT INTO @Result values (@str)
                  SET @ind = CharIndex(',',@input)
            END
            SET @str = @input
            INSERT INTO @Result values (@str)
      END
      RETURN
END


DECLARE @str VARCHAR(4000) = 'a,gf,sd,safd,hyd,w,r,t,y,c,c,h,gh,as,h,j,h'

SELECT * FROM dbo.split_charval ( @str )

Thursday, 27 February 2014

trimming characters

declare @T table
  (
  Col varchar(20)
  )



  insert into @T
  Select 'WO-012345' --'images/test1.jpg'
  --union all
  --Select 'images/test2.png'
  --union all
  --Select 'images/test3.jpg'
  --union all
  --Select 'images/test4.jpeg'
  --union all
  --Select 'images/test5.jpeg'

 Select substring( col,charindex('-',Col)+1,6 )
from @T

find a column name in all databases

SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE '%gradient%'
ORDER BY schema_name, table_name;

Thursday, 23 January 2014

Find a VALUE in all the tables

CREATE PROC SearchAllTables
(
@SearchStr nvarchar(100)
)
AS
BEGIN


/*

exec SearchAllTables @SearchStr = 'transparent'

*/

CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

SET NOCOUNT ON

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
SET  @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

WHILE @TableName IS NOT NULL
BEGIN
    SET @ColumnName = ''
    SET @TableName =
    (
        SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
        FROM    INFORMATION_SCHEMA.TABLES
        WHERE       TABLE_TYPE = 'BASE TABLE'
            AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
            AND OBJECTPROPERTY(
                    OBJECT_ID(
                        QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
                         ), 'IsMSShipped'
                           ) = 0
    )

    WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
    BEGIN
        SET @ColumnName =
        (
            SELECT MIN(QUOTENAME(COLUMN_NAME))
            FROM    INFORMATION_SCHEMA.COLUMNS
            WHERE       TABLE_SCHEMA    = PARSENAME(@TableName, 2)
                AND TABLE_NAME  = PARSENAME(@TableName, 1)
                AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
                AND QUOTENAME(COLUMN_NAME) > @ColumnName
        )

        IF @ColumnName IS NOT NULL
        BEGIN
            INSERT INTO #Results
            EXEC
            (
                'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)
                FROM ' + @TableName + ' (NOLOCK) ' +
                ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
            )
        END
    END
END

SELECT ColumnName, ColumnValue FROM #Results
 END

Tuesday, 21 January 2014

find complete information about dependency of a Stored Procedure

;WITH stored_procedures AS (
SELECT
o.name AS proc_name, oo.name AS table_name,
ROW_NUMBER() OVER(partition by o.name,oo.name ORDER BY o.name,oo.name) AS row
FROM sysdepends d
INNER JOIN sysobjects o ON o.id=d.id
INNER JOIN sysobjects oo ON oo.id=d.depid
WHERE o.xtype = 'P')
SELECT proc_name, table_name FROM stored_procedures
--WHERE row = 1
where proc_name like 'usp_insTextSize'
ORDER BY proc_name,table_name

Wednesday, 30 October 2013

Issue with BLOB rows



Issue with BLOB rows-
Method 1 Set the SubscriptionStreams parameter of the Distribution Agent to a value of 1. Method 2 You can set the OleDbStreamThreshold parameter of the Distribution Agent to a value that is greater than the largest data size for binary large object (BLOB) columns that have to be replicated. Then, the Distribution Agent will not bind binary large object (BLOB) data as a stream. Starting from SQL Server 2008, binary large object (BLOB) data is bound as a stream when the data size of the binary large object (BLOB) data is less than the value of the OleDbStreamThreshold parameter. This behavior is true by default.

USE TestDB;
GO
EXEC sp_configure 'show advanced options', 1 ;
RECONFIGURE ;
GO
EXEC sp_configure 'max text repl size';
GO

-- OUTPUT:
-- name                    minimum  maximum     config_value run_value
-- ----------------------- -------- ----------- ------------ -----------
-- max text repl size (B)  -1       2147483647  65536        65536
Output clearly shows maximum value of 65536 bytes. Now, Execute the below set of statements to resolve the issue.
-- For SQL Server 2005
USE TestDB;
GO
EXEC sp_configure 'show advanced options', 1 ;
RECONFIGURE ;
GO
EXEC sp_configure 'max text repl size', 2147483647 ; 
GO
RECONFIGURE;
GO

-- For SQL Server 2008 (and 2008 R2)
USE TestDB;
GO
EXEC sp_configure 'show advanced options', 1 ;
RECONFIGURE ;
GO
EXEC sp_configure 'max text repl size', -1 ;
GO
RECONFIGURE;
GO