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 and availability during Student Test windows. …
SQL Server 2014 – Memory Optimized Tables
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 in the business of providing …
XACT_ABORT Settings and Connection Timeout Nuances
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 big red flag, that the …
SQL Handle to SQL Text
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), …
Deadlocks : Count , Analysis and Event Notification
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) Extended …
SQL Server – Read Errorlog
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 — Query for Successful Logins EXEC sys.sp_readerrorlog @p1 = 0, — …