Technology | Kaizen - Continuous Improvement

Category: Technology

Azure – Troubleshooting

Posted on December 14, 2018 by

Raju Venkataraman Posted in Technology — No Comments ↓

Azure – Troubleshooting AKS Recommended KeepAlive Settings

sudo bash -c "echo 120 > /proc/sys/net/ipv4/tcp_keepalive_time"

sudo bash -c "echo 30 > /proc/sys/net/ipv4/tcp_keepalive_intvl"

sudo bash -c "echo 8 > /proc/sys/net/ipv4/tcp_keepalive_probes"

sudo bash -c "echo 120 > /proc/sys/net/ipv4/tcp_keepalive_time"

sudo bash -c "echo 30 > /proc/sys/net/ipv4/tcp_keepalive_intvl"

sudo bash -c "echo 8 > /proc/sys/net/ipv4/tcp_keepalive_probes"

Change Database Recover Model

Posted on December 12, 2018 by

Raju Venkataraman Posted in SQL Server, Technology — No Comments ↓

Change Database Recover Model

USE master
DECLARE @isql VARCHAR(2000),
        @dbname VARCHAR(64),
        @logfile VARCHAR(128)

DECLARE c1 CURSOR FOR
SELECT d.name,
       mf.name AS logfile --, physical_name AS current_file_location, size
FROM sys.master_files mf
    INNER JOIN sys.databases d
        ON mf.database_id = d.database_id
WHERE recovery_model_desc <> 'SIMPLE'
      AND d.name NOT IN ( 'master', 'model', 'msdb', 'tempdb' )
      AND mf.type_desc = 'LOG'
OPEN c1
FETCH NEXT FROM c1
INTO @dbname,
     @logfile
WHILE @@fetch_status <> -1
BEGIN
    SELECT @isql = 'ALTER DATABASE ' + @dbname + ' SET RECOVERY SIMPLE'
    PRINT @isql
    --exec(@isql)
    SELECT @isql = 'USE ' + @dbname + ' checkpoint'
    PRINT @isql
    --exec(@isql)
    SELECT @isql = 'USE ' + @dbname + ' DBCC SHRINKFILE (' + @logfile + ', 1)'
    PRINT @isql
    --exec(@isql)

    FETCH NEXT FROM c1
    INTO @dbname,
         @logfile
END
CLOSE c1
DEALLOCATE c1

USE master

DECLARE @isql VARCHAR(2000),

@dbname VARCHAR(64),

@logfile VARCHAR(128)

DECLARE c1 CURSOR FOR

SELECT d.name,

mf.name AS logfile --, physical_name AS current_file_location, size

FROM sys.master_files mf

INNER JOIN sys.databases d

ON mf.database_id = d.database_id

WHERE recovery_model_desc <> 'SIMPLE'

AND d.name NOT IN ( 'master', 'model', 'msdb', 'tempdb' )

AND mf.type_desc = 'LOG'

OPEN c1

FETCH NEXT FROM c1

INTO @dbname,

@logfile

WHILE @@fetch_status <> -1

BEGIN

SELECT @isql = 'ALTER DATABASE ' + @dbname + ' SET RECOVERY SIMPLE'

PRINT @isql

--exec(@isql)

SELECT @isql = 'USE ' + @dbname + ' checkpoint'

PRINT @isql

--exec(@isql)

SELECT @isql = 'USE ' + @dbname + ' DBCC SHRINKFILE (' + @logfile + ', 1)'

PRINT @isql

--exec(@isql)

FETCH NEXT FROM c1

INTO @dbname,

@logfile

END

CLOSE c1

DEALLOCATE c1

Deadlocks in Azure SQL Database

Posted on December 8, 2018 by

Raju Venkataraman Posted in Interview Questions, Scaling SQL Server, SQL Server, Technology — No Comments ↓

Deadlocks in Azure SQL Database : Recently we were working with Azure Logic Apps to invoke Azure Functions. By Default, Logic App runs parallel threads and we didn’t explicitly control the concurrency and left the default values. So Logic App…

Azure SQL Database Connectivity Architecture

Posted on December 5, 2018 by

Raju Venkataraman Posted in SQL Server, Technology — No Comments ↓

Azure SQL Database Connectivity Architecture Connection policy Azure SQL Database supports the following three options for the connection policy setting of a SQL Database server: Redirect (recommended): Clients establish connections directly to the node hosting the database. To enable connectivity, the…

Azure Database Firewall Queries

Posted on November 30, 2018 by

Raju Venkataraman Posted in SQL Server, Technology — No Comments ↓

-- Server Level Firewall Rules
SELECT * FROM sys.firewall_rules

-- Database Level Firewall Rules
SELECT * FROM sys.database_firewall_rules

-- -- Remove server level firewall setting  
EXECUTE sp_delete_firewall_rule N'AllowAllWindowsAzureIps';

-- Remove database-level firewall setting  
EXECUTE sp_delete_database_firewall_rule N'Allow Azure';

-- Add database-level firewall setting  
EXECUTE sp_set_database_firewall_rule N'Allow Azure', '40.79.152.30', '40.79.152.30';

-- Add server-level firewall setting  
EXECUTE sp_set_firewall_rule N'Allow_Azure_Elastic_Query', '40.79.152.30', '40.79.152.30';

-- Server Level Firewall Rules

SELECT * FROM sys.firewall_rules

-- Database Level Firewall Rules

SELECT * FROM sys.database_firewall_rules

-- -- Remove server level firewall setting

EXECUTE sp_delete_firewall_rule N'AllowAllWindowsAzureIps';

-- Remove database-level firewall setting

EXECUTE sp_delete_database_firewall_rule N'Allow Azure';

-- Add database-level firewall setting

EXECUTE sp_set_database_firewall_rule N'Allow Azure', '40.79.152.30', '40.79.152.30';

-- Add server-level firewall setting

EXECUTE sp_set_firewall_rule N'Allow_Azure_Elastic_Query', '40.79.152.30', '40.79.152.30';

Logic Apps – US East Region

/********************************************************************************************************
-- https://docs.microsoft.com/en-us/azure/logic-apps/logic-apps-limits-and-config#configuration
East US	
13.92.98.111, 23.100.29.190, 23.101.132.208, 23.101.136.201, 23.101.139.153, 40.114.82.191, 40.121.91.41, 104.45.153.81

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

declare @pIPAddresslist nvarchar(4000)
select  @pIPAddresslist = '13.92.98.111, 23.100.29.190, 23.101.132.208, 23.101.136.201, 23.101.139.153, 40.114.82.191, 40.121.91.41, 104.45.153.81'
select value from string_split(@pIPAddresslist,',')

declare @IPAddress varchar(50)
select  @IPAddress = '13.92.98.111'
-- Add database-level firewall setting  
EXECUTE sp_set_database_firewall_rule N'LogicApps_USEast_IP_1', @IPAddress, @IPAddress;


select  @IPAddress = '23.100.29.190'
-- Add database-level firewall setting  
EXECUTE sp_set_database_firewall_rule N'LogicApps_USEast_IP_2', @IPAddress, @IPAddress;


select  @IPAddress = '23.101.132.208'
-- Add database-level firewall setting  
EXECUTE sp_set_database_firewall_rule N'LogicApps_USEast_IP_3', @IPAddress, @IPAddress;

select  @IPAddress = '23.101.136.201'
-- Add database-level firewall setting  
EXECUTE sp_set_database_firewall_rule N'LogicApps_USEast_IP_4', @IPAddress, @IPAddress;

select  @IPAddress = '23.101.139.153'
-- Add database-level firewall setting  
EXECUTE sp_set_database_firewall_rule N'LogicApps_USEast_IP_5', @IPAddress, @IPAddress;

select  @IPAddress = '40.114.82.191'
-- Add database-level firewall setting  
EXECUTE sp_set_database_firewall_rule N'LogicApps_USEast_IP_6', @IPAddress, @IPAddress;

select  @IPAddress = '40.121.91.41'
-- Add database-level firewall setting  
EXECUTE sp_set_database_firewall_rule N'LogicApps_USEast_IP_7', @IPAddress, @IPAddress;

select  @IPAddress = '104.45.153.81'
-- Add database-level firewall setting  
EXECUTE sp_set_database_firewall_rule N'LogicApps_USEast_IP_8', @IPAddress, @IPAddress;

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

-- https://docs.microsoft.com/en-us/azure/logic-apps/logic-apps-limits-and-config#configuration

East US

13.92.98.111, 23.100.29.190, 23.101.132.208, 23.101.136.201, 23.101.139.153, 40.114.82.191, 40.121.91.41, 104.45.153.81

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

declare @pIPAddresslist nvarchar(4000)

select @pIPAddresslist = '13.92.98.111, 23.100.29.190, 23.101.132.208, 23.101.136.201, 23.101.139.153, 40.114.82.191, 40.121.91.41, 104.45.153.81'

select value from string_split(@pIPAddresslist,',')

declare @IPAddress varchar(50)

select @IPAddress = '13.92.98.111'

-- Add database-level firewall setting

EXECUTE sp_set_database_firewall_rule N'LogicApps_USEast_IP_1', @IPAddress, @IPAddress;

select @IPAddress = '23.100.29.190'

-- Add database-level firewall setting

EXECUTE sp_set_database_firewall_rule N'LogicApps_USEast_IP_2', @IPAddress, @IPAddress;

select @IPAddress = '23.101.132.208'

-- Add database-level firewall setting

EXECUTE sp_set_database_firewall_rule N'LogicApps_USEast_IP_3', @IPAddress, @IPAddress;

select @IPAddress = '23.101.136.201'

-- Add database-level firewall setting

EXECUTE sp_set_database_firewall_rule N'LogicApps_USEast_IP_4', @IPAddress, @IPAddress;

select @IPAddress = '23.101.139.153'

-- Add database-level firewall setting

EXECUTE sp_set_database_firewall_rule N'LogicApps_USEast_IP_5', @IPAddress, @IPAddress;

select @IPAddress = '40.114.82.191'

-- Add database-level firewall setting

EXECUTE sp_set_database_firewall_rule N'LogicApps_USEast_IP_6', @IPAddress, @IPAddress;

select @IPAddress = '40.121.91.41'

-- Add database-level firewall setting

EXECUTE sp_set_database_firewall_rule N'LogicApps_USEast_IP_7', @IPAddress, @IPAddress;

select @IPAddress = '104.45.153.81'

-- Add database-level firewall setting

EXECUTE sp_set_database_firewall_rule N'LogicApps_USEast_IP_8', @IPAddress, @IPAddress;

Videos Watched

Posted on October 29, 2018 by

Raju Venkataraman Posted in Technology — No Comments ↓

Videos Watched October 201 October 20 Meet My Next Guest, Richard M Stallman https://www.youtube.com/watch?v=VMM6D9vuHkY October 21 Monitor your infrastructure and analyze operational logs at scale with Azure Monitor – BRK3354 https://www.youtube.com/watch?v=_0ccWoScTyE Uncle” Bob Martin – “The Future of Programming…

Stored Procedure Error Logging

Posted on October 28, 2017 by

Raju Venkataraman Posted in AWS RDS, Interview Questions, SQL Server — No Comments ↓

Stored Procedure Error Logging Create table to capture Errors Create Stored Procedure to Log Error Invoke Stored Procedure in TRY/CATCH Block

USE TEMPDB;
GO
IF OBJECT_ID('APM.ErrorLog') IS NOT NULL
    DROP TABLE APM.ErrorLog;
GO


CREATE TABLE APM.ErrorLog
(
    ErrorLogID INT IDENTITY(1, 1) PRIMARY KEY,
    DatabaseID INT,
    DatabaseName sysname,
    SPID INT,
    ErrorNumber INT,
    ErrorSeverity INT,
    ErrorState INT,
    ErrorProcedure VARCHAR(400),
    ErrorMessage NVARCHAR(MAX),
    AdditionalInfo NVARCHAR(MAX),
    CreatedBY VARCHAR(50)
        DEFAULT SYSTEM_USER,
    CreatedDateTime DATETIME
        DEFAULT GETUTCDATE(),
    Status INT
        DEFAULT 1
);
GO

CREATE OR ALTER PROC APM.LogErrorInfo
(
    @pAdditionalIndfo NVARCHAR(550) = NULL,
    @pCreatedBy VARCHAR(50) = NULL
)
AS
BEGIN
    INSERT INTO APM.ErrorLog
    (
        DatabaseID,
        DatabaseName,
        SPID,
        ErrorNumber,
        ErrorSeverity,
        ErrorState,
        ErrorProcedure,
        ErrorMessage,
        AdditionalInfo,
        CreatedBY
    )
    SELECT DB_ID(),                       -- DatabaseID - int
           DB_NAME(),                     -- DatabaseName - sysname
           @@SPID,                        -- SPID - int
           ERROR_LINE(),                  -- ErrorNumber - int
           ERROR_SEVERITY(),              -- ErrorSeverity - int
           ERROR_STATE(),                 -- ErrorState - int
           ERROR_PROCEDURE(),             -- ErrorProcedure - varchar(400)
           ERROR_MESSAGE(),               -- ErrorMessage - nvarchar(max)
           ISNULL(@pAdditionalIndfo, ''), -- AdditionalInfo - nvarchar(max)
           ISNULL(@pCreatedBy, SYSTEM_USER);

    SELECT DB_ID(),                       -- DatabaseID - int
           DB_NAME(),                     -- DatabaseName - sysname
           @@SPID,                        -- SPID - int
           ERROR_LINE(),                  -- ErrorNumber - int
           ERROR_SEVERITY(),              -- ErrorSeverity - int
           ERROR_STATE(),                 -- ErrorState - int
           ERROR_PROCEDURE(),             -- ErrorProcedure - varchar(400)
           ERROR_MESSAGE(),               -- ErrorMessage - nvarchar(max)
           ISNULL(@pAdditionalIndfo, ''), -- AdditionalInfo - nvarchar(max)
           ISNULL(@pCreatedBy, SYSTEM_USER);

/*
 -- UNIT TEST
 BEGIN TRY
SELECT 1 /0
END TRY
BEGIN CATCH
EXEC APM.LogErrorInfo @pAdditionalIndfo ='Unit Test'
END CATCH

SELECT * FROM APM.ErrorLog
*/

END;

USE TEMPDB;

IF OBJECT_ID('APM.ErrorLog') IS NOT NULL

DROP TABLE APM.ErrorLog;

CREATE TABLE APM.ErrorLog

(

ErrorLogID INT IDENTITY(1, 1) PRIMARY KEY,

DatabaseID INT,

DatabaseName sysname,

SPID INT,

ErrorNumber INT,

ErrorSeverity INT,

ErrorState INT,

ErrorProcedure VARCHAR(400),

ErrorMessage NVARCHAR(MAX),

AdditionalInfo NVARCHAR(MAX),

CreatedBY VARCHAR(50)

DEFAULT SYSTEM_USER,

CreatedDateTime DATETIME

DEFAULT GETUTCDATE(),

Status INT

DEFAULT 1

);

CREATE OR ALTER PROC APM.LogErrorInfo

(

@pAdditionalIndfo NVARCHAR(550) = NULL,

@pCreatedBy VARCHAR(50) = NULL

)

BEGIN

INSERT INTO APM.ErrorLog

(

DatabaseID,

DatabaseName,

SPID,

ErrorNumber,

ErrorSeverity,

ErrorState,

ErrorProcedure,

ErrorMessage,

AdditionalInfo,

CreatedBY

)

SELECT DB_ID(), -- DatabaseID - int

DB_NAME(), -- DatabaseName - sysname

@@SPID, -- SPID - int

ERROR_LINE(), -- ErrorNumber - int

ERROR_SEVERITY(), -- ErrorSeverity - int

ERROR_STATE(), -- ErrorState - int

ERROR_PROCEDURE(), -- ErrorProcedure - varchar(400)

ERROR_MESSAGE(), -- ErrorMessage - nvarchar(max)

ISNULL(@pAdditionalIndfo, ''), -- AdditionalInfo - nvarchar(max)

ISNULL(@pCreatedBy, SYSTEM_USER);

SELECT DB_ID(), -- DatabaseID - int

DB_NAME(), -- DatabaseName - sysname

@@SPID, -- SPID - int

ERROR_LINE(), -- ErrorNumber - int

ERROR_SEVERITY(), -- ErrorSeverity - int

ERROR_STATE(), -- ErrorState - int

ERROR_PROCEDURE(), -- ErrorProcedure - varchar(400)

ERROR_MESSAGE(), -- ErrorMessage - nvarchar(max)

ISNULL(@pAdditionalIndfo, ''), -- AdditionalInfo - nvarchar(max)

ISNULL(@pCreatedBy, SYSTEM_USER);

-- UNIT TEST

BEGIN TRY

SELECT 1 /0

END TRY

BEGIN CATCH

EXEC APM.LogErrorInfo @pAdditionalIndfo ='Unit Test'

END CATCH

SELECT * FROM APM.ErrorLog

END;

Install and Run SQL Server Docker Container on Mac

Posted on October 8, 2017 by

Raju Venkataraman Posted in AWS, EC2, Git — No Comments ↓

Install and Run SQL Server Docker Container on Mac Like most people, I use Mac , Windows as well Linux OS for development and testing purposes. Primarily I use Mac for Development purpose. I have few projects which uses SQL Server as…

Bulk Load Data Files in S3 Bucket into Aurora RDS

Posted on July 15, 2017 by

Raju Venkataraman Posted in AWS, AWS RDS, Technology — 1 Comment ↓

Bulk Load Data Files in S3 Bucket into Aurora RDS We typically get data feeds from our clients ( usually about ~ 5 – 20 GB) worth of data. We download these data files to our lab environment and use shell…

MYSQL / AURORA Database Troubleshooting

Posted on June 28, 2017 by

Raju Venkataraman Posted in AWS RDS, MySQL, Technology — No Comments ↓

Number of Connections by Host

SELECT SUBSTRING(HOST, 1, 10) , DB,USER , COUNT(*) AS Count
FROM information_schema.processlist 
group by SUBSTRING(HOST, 0, 10) , DB,USER 
ORDER BY Count desc ;

-- '10.10.50.22', 'Portal', 'webguest-dev', '46'

SELECT SUBSTRING(HOST, 1, 10) , DB,USER , COUNT(*) AS Count

FROM information_schema.processlist

group by SUBSTRING(HOST, 0, 10) , DB,USER

ORDER BY Count desc ;

-- '10.10.50.22', 'Portal', 'webguest-dev', '46'

Aurora Max Connections

select AURORA_VERSION();

select * from mysql.slow_log 
where sql_text not like '%LOAD DATA%'
order by query_time desc
limit 1000 ;

select count(*) from mysql.general_log 
where user_host not like 'rdsadmin%'
and user_host not like '[rdsadmin]%'
and event_time > '2017-06-15 18:51:14';

select current_timestamp();

desc mysql.general_log  ;

select @@MAX_CONNECTIONS
-- '4000'

select *  from mysql.general_log 
where command_type like '%Connect%';

select *  from mysql.general_log_backup 
where command_type like '%Connect%' ;

SHOW GLOBAL STATUS LIKE '%Connection_errors%';

SHOW STATUS WHERE `variable_name` = 'Threads_connected';

select AURORA_VERSION();

select * from mysql.slow_log

where sql_text not like '%LOAD DATA%'

order by query_time desc

limit 1000 ;

select count(*) from mysql.general_log

where user_host not like 'rdsadmin%'

and user_host not like '[rdsadmin]%'

and event_time > '2017-06-15 18:51:14';

select current_timestamp();

desc mysql.general_log ;

select @@MAX_CONNECTIONS

-- '4000'

select * from mysql.general_log

where command_type like '%Connect%';

select * from mysql.general_log_backup

where command_type like '%Connect%' ;

SHOW GLOBAL STATUS LIKE '%Connection_errors%';

SHOW STATUS WHERE `variable_name` = 'Threads_connected';

Memory-Optimized Tables

Posted on June 23, 2017 by

Raju Venkataraman Posted in AWS, AWS RDS, Interview Questions, Scaling SQL Server, SQL Server — No Comments ↓

Monitor Memory Optimized Table Space Usage :

;
WITH    system_allocated_memory ( system_allocated_memory_in_mb )
          AS ( SELECT   ISNULL(( SELECT CONVERT(DECIMAL(18, 2), ( SUM(TMS.memory_allocated_for_table_kb)
                                                              + SUM(TMS.memory_allocated_for_indexes_kb) )
                                        / 1024.00)
                                 FROM   [sys].[dm_db_xtp_table_memory_stats] TMS
                                 WHERE  TMS.object_id <= 0
                               ), 0.00)
             ),
        table_index_memory ( table_used_memory_in_mb, table_unused_memory_in_mb, index_used_memory_in_mb, index_unused_memory_in_mb )
          AS ( SELECT   ISNULL(( SELECT CONVERT(DECIMAL(18, 2), ( SUM(TMS.memory_used_by_table_kb)
                                                              / 1024.00 ))
                               ), 0.00) AS table_used_memory_in_mb ,
                        ISNULL(( SELECT CONVERT(DECIMAL(18, 2), ( SUM(TMS.memory_allocated_for_table_kb)
                                                              - SUM(TMS.memory_used_by_table_kb) )
                                        / 1024.00)
                               ), 0.00) AS table_unused_memory_in_mb ,
                        ISNULL(( SELECT CONVERT(DECIMAL(18, 2), ( SUM(TMS.memory_used_by_indexes_kb)
                                                              / 1024.00 ))
                               ), 0.00) AS index_used_memory_in_mb ,
                        ISNULL(( SELECT CONVERT(DECIMAL(18, 2), ( SUM(TMS.memory_allocated_for_indexes_kb)
                                                              - SUM(TMS.memory_used_by_indexes_kb) )
                                        / 1024.00)
                               ), 0.00) AS index_unused_memory_in_mb
               FROM     [sys].[dm_db_xtp_table_memory_stats] TMS
               WHERE    TMS.object_id > 0
             )
    SELECT  s.system_allocated_memory_in_mb ,
            t.table_used_memory_in_mb ,
            t.table_unused_memory_in_mb ,
            t.index_used_memory_in_mb ,
            t.index_unused_memory_in_mb ,
            ISNULL(( SELECT DATABASEPROPERTYEX(DB_NAME(DB_ID()),
                                               'IsXTPSupported')
                   ), 0) AS has_memory_optimized_filegroup
    FROM    system_allocated_memory s ,
            table_index_memory t

SELECT  t.object_id ,
        t.name ,
        ISNULL(( SELECT CONVERT(DECIMAL(18, 2), ( TMS.memory_used_by_table_kb )
                        / 1024.00)
               ), 0.00) AS table_used_memory_in_mb ,
        ISNULL(( SELECT CONVERT(DECIMAL(18, 2), ( TMS.memory_allocated_for_table_kb
                                                  - TMS.memory_used_by_table_kb )
                        / 1024.00)
               ), 0.00) AS table_unused_memory_in_mb ,
        ISNULL(( SELECT CONVERT(DECIMAL(18, 2), ( TMS.memory_used_by_indexes_kb )
                        / 1024.00)
               ), 0.00) AS index_used_memory_in_mb ,
        ISNULL(( SELECT CONVERT(DECIMAL(18, 2), ( TMS.memory_allocated_for_indexes_kb
                                                  - TMS.memory_used_by_indexes_kb )
                        / 1024.00)
               ), 0.00) AS index_unused_memory_in_mb
FROM    sys.tables t
        JOIN sys.dm_db_xtp_table_memory_stats TMS ON ( t.object_id = TMS.object_id )

;

WITH system_allocated_memory ( system_allocated_memory_in_mb )

AS ( SELECT ISNULL(( SELECT CONVERT(DECIMAL(18, 2), ( SUM(TMS.memory_allocated_for_table_kb)

+ SUM(TMS.memory_allocated_for_indexes_kb) )

/ 1024.00)

FROM [sys].[dm_db_xtp_table_memory_stats] TMS

WHERE TMS.object_id <= 0

), 0.00)

table_index_memory ( table_used_memory_in_mb, table_unused_memory_in_mb, index_used_memory_in_mb, index_unused_memory_in_mb )

AS ( SELECT ISNULL(( SELECT CONVERT(DECIMAL(18, 2), ( SUM(TMS.memory_used_by_table_kb)

/ 1024.00 ))

), 0.00) AS table_used_memory_in_mb ,

ISNULL(( SELECT CONVERT(DECIMAL(18, 2), ( SUM(TMS.memory_allocated_for_table_kb)

- SUM(TMS.memory_used_by_table_kb) )

/ 1024.00)

), 0.00) AS table_unused_memory_in_mb ,

ISNULL(( SELECT CONVERT(DECIMAL(18, 2), ( SUM(TMS.memory_used_by_indexes_kb)

/ 1024.00 ))

), 0.00) AS index_used_memory_in_mb ,

ISNULL(( SELECT CONVERT(DECIMAL(18, 2), ( SUM(TMS.memory_allocated_for_indexes_kb)

- SUM(TMS.memory_used_by_indexes_kb) )

/ 1024.00)

), 0.00) AS index_unused_memory_in_mb

FROM [sys].[dm_db_xtp_table_memory_stats] TMS

WHERE TMS.object_id > 0

)

SELECT s.system_allocated_memory_in_mb ,

t.table_used_memory_in_mb ,

t.table_unused_memory_in_mb ,

t.index_used_memory_in_mb ,

t.index_unused_memory_in_mb ,

ISNULL(( SELECT DATABASEPROPERTYEX(DB_NAME(DB_ID()),

'IsXTPSupported')

), 0) AS has_memory_optimized_filegroup

FROM system_allocated_memory s ,

table_index_memory t

SELECT t.object_id ,

t.name ,

ISNULL(( SELECT CONVERT(DECIMAL(18, 2), ( TMS.memory_used_by_table_kb )

/ 1024.00)

), 0.00) AS table_used_memory_in_mb ,

ISNULL(( SELECT CONVERT(DECIMAL(18, 2), ( TMS.memory_allocated_for_table_kb

- TMS.memory_used_by_table_kb )

/ 1024.00)

), 0.00) AS table_unused_memory_in_mb ,

ISNULL(( SELECT CONVERT(DECIMAL(18, 2), ( TMS.memory_used_by_indexes_kb )

/ 1024.00)

), 0.00) AS index_used_memory_in_mb ,

ISNULL(( SELECT CONVERT(DECIMAL(18, 2), ( TMS.memory_allocated_for_indexes_kb

- TMS.memory_used_by_indexes_kb )

/ 1024.00)

), 0.00) AS index_unused_memory_in_mb

FROM sys.tables t

JOIN sys.dm_db_xtp_table_memory_stats TMS ON ( t.object_id = TMS.object_id )

All Memory Used by Memory Optimized Table across Database Engine

-- this DMV accounts for all memory used by the hek_2 engine 
SELECT type ,
name ,
memory_node_id ,
pages_kb / 1024 AS pages_MB
FROM sys.dm_os_memory_clerks
WHERE type LIKE '%xtp%'

-- this DMV accounts for all memory used by the hek_2 engine

SELECT type ,

name ,

memory_node_id ,

pages_kb / 1024 AS pages_MB

FROM sys.dm_os_memory_clerks

WHERE type LIKE '%xtp%'

Enable Natively Compiled Stored Procedure Stats Collection

EXEC [sys].[sp_xtp_control_proc_exec_stats] @new_collection_value = 1  
DECLARE @c BIT  
EXEC sp_xtp_control_proc_exec_stats @old_collection_value = @c OUTPUT  
SELECT  @c AS 'collection status'

EXEC [sys].[sp_xtp_control_proc_exec_stats] @new_collection_value = 1

DECLARE @c BIT

EXEC sp_xtp_control_proc_exec_stats @old_collection_value = @c OUTPUT

SELECT @c AS 'collection status'

DBCC FREEPROCCACHE does not remove natively compiled stored procedures from Plan Cache

-- https://connect.microsoft.com/SQLServer/Feedback/Details/3126441

DECLARE @sql NVARCHAR(MAX) = N''

SELECT  @sql += N'EXECUTE sp_recompile N'''
        + QUOTENAME(SCHEMA_NAME(o.schema_id)) + N'.' + QUOTENAME(o.name) + '''
'
FROM    sys.sql_modules sm
        JOIN sys.objects o ON sm.object_id = o.object_id
WHERE   uses_native_compilation = 1

EXECUTE sp_executesql @sql

	
-- Reset wait and latch statistics.
DBCC SQLPERF('sys.dm_os_latch_stats' , CLEAR)
DBCC SQLPERF('sys.dm_os_wait_stats' , CLEAR)

-- https://connect.microsoft.com/SQLServer/Feedback/Details/3126441

DECLARE @sql NVARCHAR(MAX) = N''

SELECT @sql += N'EXECUTE sp_recompile N'''

+ QUOTENAME(SCHEMA_NAME(o.schema_id)) + N'.' + QUOTENAME(o.name) + '''

FROM sys.sql_modules sm

JOIN sys.objects o ON sm.object_id = o.object_id

WHERE uses_native_compilation = 1

EXECUTE sp_executesql @sql

-- Reset wait and latch statistics.

DBCC SQLPERF('sys.dm_os_latch_stats' , CLEAR)

DBCC SQLPERF('sys.dm_os_wait_stats' , CLEAR)

…

Brute force attack on SQL Server

Posted on June 22, 2017 by

Raju Venkataraman Posted in AWS, AWS RDS, Interview Questions, SQL Server — No Comments ↓

Brute force attack on SQL Server If your business needs the SQL Server to be accessible on public network, you may be very vulnerable for brute force attacks. Following query will help you identify the failed login attempts and you…

When Does SQL Server Trial Edition Expire?

Posted on June 11, 2017 by

Raju Venkataraman Posted in Interview Questions, SQL Server, Technology — No Comments ↓

When Does SQL Server Trial Edition Expire? I installed SQL Server Docker Container on Linux Machine running on EC2. I took the AMI Image and launched the existing container and wanted to know when will the SQL Server licence expires…

SQL Injection attack on Website hosted on EC2 Machine

Posted on June 10, 2017 by

Raju Venkataraman Posted in AWS, EC2, SQL Server, Technology — No Comments ↓

SQL Injection attack on Website hosted on EC2 Machine: I setup a website http://h1bsalary.online with publicly available dataset. As soon as I launched website, numerous trolls and automated bots sending traffic to identify the vulnerabilities. Safe-Guards I have taken so far :…

Install SQL Server Docker Container on Linux

Posted on May 25, 2017 by

Raju Venkataraman Posted in AWS, Scaling SQL Server, SQL Server — No Comments ↓

Install SQL Server Docker Container on Linux and Connect through Client Tools I was surprised by ability to run setup and run SQL Server Docker container. It only takes about 2 commands and 2 Minutes.

$docker pull microsoft/mssql-server-linux
$docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=Password' -p 1433:1433 -d microsoft/mssql-server-linux
$docker exec -it 40dd "bash"
$/opt/mssql-tools/bin/sqlcmd -S localhost -U SA
Password:

$docker pull microsoft/mssql-server-linux

$docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=Password' -p 1433:1433 -d microsoft/mssql-server-linux

$docker exec -it 40dd "bash"

$/opt/mssql-tools/bin/sqlcmd -S localhost -U SA

Password:

[root@ip-10-0-0-110 ec2-user]# docker pull microsoft/mssql-server-linux
[root@ip-10-0-0-110 ec2-user]# docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=Password' -p 1433:1433 -d microsoft/mssql-server-linux
[root@ip-10-0-0-120 ec2-user]# docker exec -it 40dd "bash"
root@40dde973f4a0:/# /opt/mssql-tools/bin/sqlcmd -S localhost -U SA
Password:

Command History
[root@ip-10-0-0-110 ec2-user]# docker pull microsoft/mssql-server-linux
Using default tag: latest
latest: Pulling from microsoft/mssql-server-linux
4c0c60131530: Pull complete
Digest: sha256:604d27fe5d3d9b4434fb1657e9bf4f2c2bf55ea9bd29dc0cb3660d84bc6f56a8
Status: Downloaded newer image for microsoft/mssql-server-linux:latest
[root@ip-10-0-0-110 ec2-user]# docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=Password' -p 1433:1433 -d microsoft/mssql-server-linux
40dde973f4a0cc2af469f9d1c2182403d1e22e28c2a8821e29ce832529965513
[root@ip-10-0-0-120 ec2-user]# docker -it 40dd "bash"
flag provided but not defined: -it
See 'docker --help'.
[root@ip-10-0-0-120 ec2-user]# docker exec -it 40dd "bash"
root@40dde973f4a0:/# /opt/mssql-tools/bin/sqlcmd -S localhost -U SA
Password:
1> select @@servername;
2> go

--------------------------------------------------------------------------------------------------------------------------------
40dde973f4a0

(1 rows affected)
1> select db_name();
2> go

--------------------------------------------------------------------------------------------------------------------------------
master

(1 rows affected)

[root@ip-10-0-0-110 ec2-user]# docker pull microsoft/mssql-server-linux

[root@ip-10-0-0-110 ec2-user]# docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=Password' -p 1433:1433 -d microsoft/mssql-server-linux

[root@ip-10-0-0-120 ec2-user]# docker exec -it 40dd "bash"

root@40dde973f4a0:/# /opt/mssql-tools/bin/sqlcmd -S localhost -U SA

Password:

Command History

[root@ip-10-0-0-110 ec2-user]# docker pull microsoft/mssql-server-linux

Using default tag: latest

latest: Pulling from microsoft/mssql-server-linux

4c0c60131530: Pull complete

Digest: sha256:604d27fe5d3d9b4434fb1657e9bf4f2c2bf55ea9bd29dc0cb3660d84bc6f56a8

Status: Downloaded newer image for microsoft/mssql-server-linux:latest

[root@ip-10-0-0-110 ec2-user]# docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=Password' -p 1433:1433 -d microsoft/mssql-server-linux

40dde973f4a0cc2af469f9d1c2182403d1e22e28c2a8821e29ce832529965513

[root@ip-10-0-0-120 ec2-user]# docker -it 40dd "bash"

flag provided but not defined: -it

See 'docker --help'.

[root@ip-10-0-0-120 ec2-user]# docker exec -it 40dd "bash"

root@40dde973f4a0:/# /opt/mssql-tools/bin/sqlcmd -S localhost -U SA

Password:

1> select @@servername;

2> go

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

40dde973f4a0

(1 rows affected)

1> select db_name();

2> go

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

master

(1 rows affected)

Version Info:

select @@version

/*
Microsoft SQL Server 2017 (CTP2.1) - 14.0.600.250 (X64) 
May 10 2017 12:21:23 
Copyright (C) 2017 Microsoft Corporation. All rights reserved.
Developer Edition (64-bit) on Linux (Ubuntu 16.04.2 LTS)
*/

select @@version

Microsoft SQL Server 2017 (CTP2.1) - 14.0.600.250 (X64)

May 10 2017 12:21:23

Developer Edition (64-bit) on Linux (Ubuntu 16.04.2 LTS)

…

AWS Data Pipeline & Load S3 File Into MySQL

Posted on May 5, 2017 by

Raju Venkataraman Posted in AWS, AWS RDS, EC2, Technology — No Comments ↓

Getting started with AWS Data Pipeline AWS Data Pipeline is a web service that you can use to automate the movement and transformation of data. With AWS Data Pipeline, you can define data-driven workflows, so that tasks can be dependent on…

AWS Regions and Availability Zones

Posted on May 5, 2017 by

Raju Venkataraman Posted in AWS, AWS RDS, Technology — No Comments ↓

AWS Regions and Availability Zones

C:\Users\Raju>AWS ec2 describe-regions
C:\Users\Raju> aws ec2 describe-availability-zones

1 2	C:\Users\Raju>AWS ec2 describe-regions C:\Users\Raju> aws ec2 describe-availability-zones

EC2 Regions and US-WEST-2 Availability Zones References: http://docs.aws.amazon.com/general/latest/gr/rande.html http://docs.aws.amazon.com/AWSEC2/latest/UserGuide/using-regions-availability-zones.html#using-regions-availability-zones-describe

Tagged with: AWS

SQL Server Optimizations for High Concurrency

Posted on April 9, 2017 by

Raju Venkataraman Posted in Scaling SQL Server, SQL Server, Technology, TempDB — No Comments ↓

SQL Server Optimizations for High Concurrency Our business needs very robust, low latency, highly available and durable online transactional system which supports high concurrency for about four weeks in a year. It’s almost like Thanksgiving sale where you mark down very popular item…

Looking Back at 2016 !!

Posted on December 27, 2016 by

Raju Venkataraman Posted in Personal, Technology — 1 Comment ↓

Gratitude Reflecting on year 2016, I feel so fortunate and lucky for my experiences. In professional side, there are gazillion things where we don’t have direct or complete control but that directly impacts our company performance as well as employees individual lives…

Restrict S3 bucket Access to VPC

Posted on October 16, 2016 by

Raju Venkataraman Posted in AWS, Technology — No Comments ↓

Allow access to S3 bucket only from VPC Currently I am evaluating options to lockdown permissions to my S3 Buckets as part of Security Enhancements. These are the steps I followed to lock down S3 Bucket access only to my…

Category: Technology

Azure – Troubleshooting

Change Database Recover Model

Deadlocks in Azure SQL Database

Azure SQL Database Connectivity Architecture

Azure Database Firewall Queries

Videos Watched

Stored Procedure Error Logging

Install and Run SQL Server Docker Container on Mac

Bulk Load Data Files in S3 Bucket into Aurora RDS

MYSQL / AURORA Database Troubleshooting

Memory-Optimized Tables

Brute force attack on SQL Server

When Does SQL Server Trial Edition Expire?

SQL Injection attack on Website hosted on EC2 Machine

Install SQL Server Docker Container on Linux

AWS Data Pipeline & Load S3 File Into MySQL

AWS Regions and Availability Zones

SQL Server Optimizations for High Concurrency

Looking Back at 2016 !!

Restrict S3 bucket Access to VPC

Recent Posts

Categories

Recent Posts

Archives