Interview Questions | Kaizen - Continuous Improvement

Category: Interview Questions

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;

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…

Remove Procedure Cache and Reset Wait Stats

Posted on March 16, 2016 by

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

Remove Procedure Cache and Reset Wait Stats Remove all elements from the plan cache for the entire sql server instance

DBCC FREEPROCCACHE;

1	DBCC FREEPROCCACHE;

Flush the cache and suppress the regular completion message

DBCC FREEPROCCACHE WITH NO_INFOMSGS;

1	DBCC FREEPROCCACHE WITH NO_INFOMSGS;

Remove all elements from the plan cache for a…

Configurations : Web Config or Database?

Posted on February 5, 2016 by

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

Configurations : Web Config or Database? Web Config: Database Connection Strings. OfCourse. You can’t connect without that details. Changes you want trigger Application Pool refresh. Things need to be available when the database is unavailable. (such as a list of…

SQL Server Locking

Posted on April 24, 2015 by

Raju Venkataraman Posted in Interview Questions, SQL Server

Microsoft Link : https://technet.microsoft.com/en-us/library/jj856598(v=sql.110).aspx Locks held by Current Processes: SQL Server 2008 R2 https://technet.microsoft.com/en-us/library/ms189497(v=sql.105).aspx SQL Team Primer on Locks : http://www.sqlteam.com/article/introduction-to-locking-in-sql-server Kalen Delany Crisp explanation on Update Locks: http://sqlblog.com/blogs/kalen_delaney/archive/2009/11/13/update-locks.aspx UPDATE locks are not really a separate kind of lock, but…

More CPU’s in SQL Server : Does it Help or Hurt? (MAXDOP)

Posted on April 23, 2015 by

Raju Venkataraman Posted in Interview Questions, SQL Server, Technology

More CPU’s in SQL Server : Does it Help or Hurt? (MAXDOP): Common sense tells us , having more CPU’s mean more processing power , that means things would be done faster. Hold on for a sec..Its SQL Server. Common…

Show Database Size and Last access time

Posted on November 19, 2014 by

Raju Venkataraman Posted in Interview Questions, SQL Server

How do you find the last time a database was accessed and size? We had about 108 databases on one of our Test Servers and it was being used by multiple teams and we don’t have comprehensive list of what’s…

SQL Server – Interview Questions

Posted on September 17, 2014 by

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

Database Professional – Interview Questions What factors do you usually consider for your datastore selection ? Data Volume , Variety and Velocity. Whats CAP Theorem?. Consistency , Availability and Partition Tolerance and you can’t all three together. In theoretical computer science,…

Category: Interview Questions

Stored Procedure Error Logging

Memory-Optimized Tables

Brute force attack on SQL Server

When Does SQL Server Trial Edition Expire?

Remove Procedure Cache and Reset Wait Stats

Configurations : Web Config or Database?

SQL Server Locking

More CPU’s in SQL Server : Does it Help or Hurt? (MAXDOP)

Show Database Size and Last access time

SQL Server – Interview Questions

Recent Posts

Categories

Recent Posts

Archives