December, 2014 | Kaizen - Continuous Improvement

Month: December 2014

SQL Server or MySQL for our Business Needs?

Posted on December 29, 2014 by

Raju Venkataraman Posted in MySQL, SQL Server, Technology

Business Need: Currently we are in the cross-roads of growing from small-mid sized company to large scale K-12 online Test Delivery and reporting Solution provider. We primarily operate on K-12 Summative Market and its absolutely critical for us to provide 100 % accuracy…

SQL Server 2014 – Memory Optimized Tables

Posted on December 27, 2014 by

Raju Venkataraman Posted in SQL Server, Technology

Our Team Need : We have about 5-500 GB Databases and very few tables ( ~ About 10 tables ) are high volume high concurrency tables. At peak volume, we may write few thousands per second per table. We are…

XACT_ABORT Settings and Connection Timeout Nuances

Posted on December 6, 2014 by

Raju Venkataraman Posted in SQL Server

We have .NET Application connecting to SQL Server database. I have noticed the timeout from the Application while calling the stored Procedure. We didn’t configure any specific timeout limit , so we were using default 30 Sec Timeout. Its pretty…

SQL Handle to SQL Text

Posted on December 2, 2014 by

Raju Venkataraman Posted in SQL Server

SQL Handle to SQL Text with Statement Start and Statement End:

DECLARE @sql_handle VARBINARY(64) ,
    @stmtstart INT ,
    @stmtend INT 

SELECT  @stmtstart = 3268 ,
        @stmtend = 5102 ,
        @sql_handle = 0x03000800d8c3dd70e2f83f01e2a300000100000000000000

SELECT  SUBSTRING(qt.text, s.statement_start_offset / 2,
                  ( CASE WHEN s.statement_end_offset = -1
                         THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
                         ELSE s.statement_end_offset
                    END - s.statement_start_offset ) / 2) AS "SQL statement" ,
        s.statement_start_offset ,
        s.statement_end_offset ,
        batch = qt.text ,
        qt.dbid ,
        qt.objectid ,
        s.execution_count ,
        s.total_worker_time ,
        s.total_elapsed_time ,
        s.total_logical_reads ,
        s.total_physical_reads ,
        s.total_logical_writes
FROM    sys.dm_exec_query_stats s
        CROSS APPLY sys.dm_exec_sql_text(s.sql_handle) AS qt
WHERE   s.sql_handle = @sql_handle
AND s.statement_start_offset = @stmtstart AND s.statement_end_offset = @stmtend

DECLARE @sql_handle VARBINARY(64) ,

@stmtstart INT ,

@stmtend INT

SELECT @stmtstart = 3268 ,

@stmtend = 5102 ,

@sql_handle = 0x03000800d8c3dd70e2f83f01e2a300000100000000000000

SELECT SUBSTRING(qt.text, s.statement_start_offset / 2,

( CASE WHEN s.statement_end_offset = -1

THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2

ELSE s.statement_end_offset

END - s.statement_start_offset ) / 2) AS "SQL statement" ,

s.statement_start_offset ,

s.statement_end_offset ,

batch = qt.text ,

qt.dbid ,

qt.objectid ,

s.execution_count ,

s.total_worker_time ,

s.total_elapsed_time ,

s.total_logical_reads ,

s.total_physical_reads ,

s.total_logical_writes

FROM sys.dm_exec_query_stats s

CROSS APPLY sys.dm_exec_sql_text(s.sql_handle) AS qt

WHERE s.sql_handle = @sql_handle

AND s.statement_start_offset = @stmtstart AND s.statement_end_offset = @stmtend

Deadlocks : Count , Analysis and Event Notification

Posted on December 2, 2014 by

Raju Venkataraman Posted in SQL Server

SQL Server : Number of Deadlocks since Last SQL Server Restart

SELECT sqlserver_start_time AS ServerRestartTime
FROM sys.dm_os_sys_info

SELECT * 
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Number of Deadlocks/sec'
AND instance_name = '_Total'

-- Trace Flags to Capture Deadlock Events
DBCC TRACESTATUS(1222)
DBCC TRACEON(1222,-1)
DBCC TRACEOFF(1222,-1)

SELECT sqlserver_start_time AS ServerRestartTime

FROM sys.dm_os_sys_info

SELECT *

FROM sys.dm_os_performance_counters

WHERE counter_name = 'Number of Deadlocks/sec'

AND instance_name = '_Total'

-- Trace Flags to Capture Deadlock Events

DBCC TRACESTATUS(1222)

DBCC TRACEON(1222,-1)

DBCC TRACEOFF(1222,-1)

Extended Events Query:

WITH SystemHealth 
AS ( 
SELECT CAST(target_data as xml) AS TargetData 
FROM sys.dm_xe_session_targets st 
JOIN sys.dm_xe_sessions s 
ON s.address = st.event_session_address 
WHERE name = 'system_health' 
AND st.target_name = 'ring_buffer') 

SELECT XEventData.XEvent.query('(data/value/deadlock)[1]') AS DeadLockGraph 
FROM SystemHealth 
CROSS APPLY TargetData.nodes('//RingBufferTarget/event') AS XEventData (XEvent) 
WHERE XEventData.XEvent.value('@name','varchar(4000)') LIKE  '%deadlock%'

WITH SystemHealth

AS (

SELECT CAST(target_data as xml) AS TargetData

FROM sys.dm_xe_session_targets st

JOIN sys.dm_xe_sessions s

ON s.address = st.event_session_address

WHERE name = 'system_health'

AND st.target_name = 'ring_buffer')

SELECT XEventData.XEvent.query('(data/value/deadlock)[1]') AS DeadLockGraph

FROM SystemHealth

CROSS APPLY TargetData.nodes('//RingBufferTarget/event') AS XEventData (XEvent)

WHERE XEventData.XEvent.value('@name','varchar(4000)') LIKE '%deadlock%'

select XEventData.XEvent.value('(data/value)[1]', 'varchar(max)') as DeadlockGraph, 
XEventData.XEvent.value('(@timestamp)[1]', 'datetime2') AS TS1
FROM
(select CAST(target_data as xml) as TargetData
from sys.dm_xe_session_targets st
join sys.dm_xe_sessions s on s.address = st.event_session_address
where name = 'system_health') AS Data
CROSS APPLY TargetData.nodes ('//RingBufferTarget/event') AS XEventData (XEvent)
where XEventData.XEvent.value('@name', 'varchar(4000)') = 'xml_deadlock_report'

select XEventData.XEvent.value('(data/value)[1]', 'varchar(max)') as DeadlockGraph,

XEventData.XEvent.value('(@timestamp)[1]', 'datetime2') AS TS1

FROM

(select CAST(target_data as xml) as TargetData

from sys.dm_xe_session_targets st

join sys.dm_xe_sessions s on s.address = st.event_session_address

where name = 'system_health') AS Data

CROSS APPLY TargetData.nodes ('//RingBufferTarget/event') AS XEventData (XEvent)

where XEventData.XEvent.value('@name', 'varchar(4000)') = 'xml_deadlock_report'

DEADLOCK Query from Extended Events:

SELECT  TOP 10 XEventData.XEvent.value('(data/value)[1]', 'varchar(max)') AS DeadlockGraph ,
        XEventData.XEvent.value('(@timestamp)[1]', 'datetime2') AS TS1
FROM    ( SELECT    CAST(target_data AS XML) AS TargetData
          FROM      sys.dm_xe_session_targets st WITH ( NOLOCK )
                    JOIN sys.dm_xe_sessions s WITH ( NOLOCK ) ON s.address = st.event_session_address
          WHERE     name = 'system_health'
        ) AS Data
        CROSS APPLY TargetData.nodes('//RingBufferTarget/event') AS XEventData ( XEvent )
WHERE   XEventData.XEvent.value('@name', 'varchar(4000)') = 'xml_deadlock_report'
AND  XEventData.XEvent.value('(@timestamp)[1]', 'datetime2') > GETUTCDATE()-2
ORDER BY XEventData.XEvent.value('(@timestamp)[1]', 'datetime2') DESC

SELECT TOP 10 XEventData.XEvent.value('(data/value)[1]', 'varchar(max)') AS DeadlockGraph ,

XEventData.XEvent.value('(@timestamp)[1]', 'datetime2') AS TS1

FROM ( SELECT CAST(target_data AS XML) AS TargetData

FROM sys.dm_xe_session_targets st WITH ( NOLOCK )

JOIN sys.dm_xe_sessions s WITH ( NOLOCK ) ON s.address = st.event_session_address

WHERE name = 'system_health'

) AS Data

CROSS APPLY TargetData.nodes('//RingBufferTarget/event') AS XEventData ( XEvent )

WHERE XEventData.XEvent.value('@name', 'varchar(4000)') = 'xml_deadlock_report'

AND XEventData.XEvent.value('(@timestamp)[1]', 'datetime2') > GETUTCDATE()-2

ORDER BY XEventData.XEvent.value('(@timestamp)[1]', 'datetime2') DESC

Decipher waitresource= “KEY: 27:72057596131278848 (1413e0809b53)”

SELECT DB_NAME(27) AS DatabaseName

SELECT OBJECT_NAME(p.object_id) AS TableName ,
 i.name AS IndexName
FROM sys.partitions AS p
 INNER JOIN sys.indexes AS i ON p.object_id = i.object_id
 AND p.index_id = i.index_id
WHERE partition_id = 72057596131278848;

-- IF nothing is Changed the key actually exists

SELECT * FROM dbo.table1
WHERE %%LOCKRES%% = '{1413e0809b53}'

SELECT DB_NAME(27) AS DatabaseName

SELECT OBJECT_NAME(p.object_id) AS TableName ,

i.name AS IndexName

FROM sys.partitions AS p

INNER JOIN sys.indexes AS i ON p.object_id = i.object_id

AND p.index_id = i.index_id

WHERE partition_id = 72057596131278848;

-- IF nothing is Changed the key actually exists

SELECT * FROM dbo.table1

WHERE %%LOCKRES%% = '{1413e0809b53}'

DBCC Usuage:

DBCC PAGE ( 30 , 1 , 310497 , 2 ) WITH TABLERESULTS
DBCC TRACEON ( 3604 )
DBCC PAGE ( 8 , 1 , 591132 , 1 )
DBCC TRACEOFF ( 3604 )

-- PAGE: 8:1:591132

SELECT OBJECT_NAME(1371151930)
SELECT OBJECT_NAME(1371151930)

-- Proc [Database Id = 8 Object Id = 50099219]
-- Proc [Database Id = 8 Object Id = 2021582240]

SELECT OBJECT_NAME(50099219)
SELECT OBJECT_NAME(2021582240)

DBCC PAGE ( 30 , 1 , 310497 , 2 ) WITH TABLERESULTS

DBCC TRACEON ( 3604 )

DBCC PAGE ( 8 , 1 , 591132 , 1 )

DBCC TRACEOFF ( 3604 )

-- PAGE: 8:1:591132

SELECT OBJECT_NAME(1371151930)

-- Proc [Database Id = 8 Object Id = 50099219]

-- Proc [Database Id = 8 Object Id = 2021582240]

SELECT OBJECT_NAME(50099219)

SELECT OBJECT_NAME(2021582240)

SQL Handle to SQL Text :

DECLARE @pStartOffSet INT
DECLARE @pEndOffSet INT
DECLARE @pSQLHandle VARBINARY(64)

SELECT @pSQLHandle = 0x03003800150027147ddafc00dea500000100000000000000
,@pStartOffSet = 36772,@pEndOffSet = 37296

SELECT 
    SUBSTRING(st.text, (@pStartOffSet/2)+1, 
        ((CASE @pEndOffSet
          WHEN -1 THEN DATALENGTH(st.text)
         ELSE @pEndOffSet
         END - @pStartOffSet)/2) + 1) AS statement_text
FROM sys.dm_exec_sql_text(@pSQLHandle) AS st

SELECT @pSQLHandle = 0x03003800dcdb3213e1d3fc00dea500000100000000000000
,@pStartOffSet = 4646,@pEndOffSet = 4896

DECLARE @pStartOffSet INT

DECLARE @pEndOffSet INT

DECLARE @pSQLHandle VARBINARY(64)

SELECT @pSQLHandle = 0x03003800150027147ddafc00dea500000100000000000000

,@pStartOffSet = 36772,@pEndOffSet = 37296

SELECT

SUBSTRING(st.text, (@pStartOffSet/2)+1,

((CASE @pEndOffSet

WHEN -1 THEN DATALENGTH(st.text)

ELSE @pEndOffSet

END - @pStartOffSet)/2) + 1) AS statement_text

FROM sys.dm_exec_sql_text(@pSQLHandle) AS st

SELECT @pSQLHandle = 0x03003800dcdb3213e1d3fc00dea500000100000000000000

,@pStartOffSet = 4646,@pEndOffSet = 4896

Object…

SQL Server – Read Errorlog

Posted on December 2, 2014 by

Raju Venkataraman Posted in AWS RDS, SQL Server, Technology

Enable Audit log to capture successful and failed Logins — To Enable Failed and Successful Logins — Need to Restart the Server for this to effect

EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE',
 N'Software\Microsoft\MSSQLServer\MSSQLServer', N'AuditLevel', REG_DWORD, 3;
GO

EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE',

N'Software\Microsoft\MSSQLServer\MSSQLServer', N'AuditLevel', REG_DWORD, 3;

— Query for Successful Logins

 EXEC sys.sp_readerrorlog @p1 = 0, -- int -- 0 = current, 1 = Archive #1, 2 = Archive
 @p2 = 1, -- int -- 1 or NULL = error log, 2 = SQL Agent log
 @p3 = 'succe', -- varchar(255) -- String one we want to search for
 @p4 = 'login'
 -- varchar(255) -- String two we want to search to further refine the result

EXEC sys.sp_readerrorlog @p1 = 0, -- int -- 0 = current, 1 = Archive #1, 2 = Archive

Month: December 2014

SQL Server or MySQL for our Business Needs?

SQL Server 2014 – Memory Optimized Tables

XACT_ABORT Settings and Connection Timeout Nuances

SQL Handle to SQL Text

Deadlocks : Count , Analysis and Event Notification

SQL Server – Read Errorlog

Recent Posts

Categories

Recent Posts

Archives