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…
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…
Stored Procedure Error Logging Create table to capture Errors Create Stored Procedure to Log Error Invoke Stored Procedure in TRY/CATCH Block
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 |
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; |
Monitor Memory Optimized Table Space Usage :
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 |
; 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
1 2 3 4 5 6 7 |
-- 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
1 2 3 4 |
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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
-- 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 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? 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 Remove all elements from the plan cache for the entire sql server instance
1 |
DBCC FREEPROCCACHE; |
Flush the cache and suppress the regular completion message
1 |
DBCC FREEPROCCACHE WITH NO_INFOMSGS; |
Remove all elements from the plan cache for a…
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…
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): 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…
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…
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,…