Category: Technology
Import DACPAC file to SQL Server
Import DACPAC File https://docs.microsoft.com/en-us/azure/sql-database/scripts/sql-database-import-from-bacpac-powershell
|
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 84 85 86 87 88 89 |
# Login-AzureRmAccount # Set the resource group name and location for your server $resourcegroupname = "myResourceGroup-$(Get-Random)" $location = "westeurope" # Set an admin login and password for your server $adminlogin = "ServerAdmin" $password = "ChangeYourAdminPassword1" # Set server name - the logical server name has to be unique in the system $servername = "server-$(Get-Random)" # The sample database name $databasename = "myImportedDatabase" # The storage account name and storage container name $storageaccountname = "sqlimport$(Get-Random)" $storagecontainername = "importcontainer$(Get-Random)" # BACPAC file name $bacpacfilename = "sample.bacpac" # The ip address range that you want to allow to access your server $startip = "0.0.0.0" $endip = "0.0.0.0" # Create a resource group $resourcegroup = New-AzureRmResourceGroup -Name $resourcegroupname -Location $location # Create a storage account $storageaccount = New-AzureRmStorageAccount -ResourceGroupName $resourcegroupname ` -AccountName $storageaccountname ` -Location $location ` -Type "Standard_LRS" # Create a storage container $storagecontainer = New-AzureStorageContainer -Name $storagecontainername ` -Context $(New-AzureStorageContext -StorageAccountName $storageaccountname ` -StorageAccountKey $(Get-AzureRmStorageAccountKey -ResourceGroupName $resourcegroupname -StorageAccountName $storageaccountname).Value[0]) # Download sample database from Github [Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12 #required by Github Invoke-WebRequest -Uri "https://github.com/Microsoft/sql-server-samples/releases/download/wide-world-importers-v1.0/WideWorldImporters-Standard.bacpac" -OutFile $bacpacfilename # Upload sample database into storage container Set-AzureStorageBlobContent -Container $storagecontainername ` -File $bacpacfilename ` -Context $(New-AzureStorageContext -StorageAccountName $storageaccountname ` -StorageAccountKey $(Get-AzureRmStorageAccountKey -ResourceGroupName $resourcegroupname -StorageAccountName $storageaccountname).Value[0]) # Create a new server with a system wide unique server name $server = New-AzureRmSqlServer -ResourceGroupName $resourcegroupname ` -ServerName $servername ` -Location $location ` -SqlAdministratorCredentials $(New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $adminlogin, $(ConvertTo-SecureString -String $password -AsPlainText -Force)) # Create a server firewall rule that allows access from the specified IP range $serverfirewallrule = New-AzureRmSqlServerFirewallRule -ResourceGroupName $resourcegroupname ` -ServerName $servername ` -FirewallRuleName "AllowedIPs" -StartIpAddress $startip -EndIpAddress $endip # Import bacpac to database with an S3 performance level $importRequest = New-AzureRmSqlDatabaseImport -ResourceGroupName $resourcegroupname ` -ServerName $servername ` -DatabaseName $databasename ` -DatabaseMaxSizeBytes "262144000" ` -StorageKeyType "StorageAccessKey" ` -StorageKey $(Get-AzureRmStorageAccountKey -ResourceGroupName $resourcegroupname -StorageAccountName $storageaccountname).Value[0] ` -StorageUri "http://$storageaccountname.blob.core.windows.net/$storagecontainername/$bacpacfilename" ` -Edition "Standard" ` -ServiceObjectiveName "S3" ` -AdministratorLogin "$adminlogin" ` -AdministratorLoginPassword $(ConvertTo-SecureString -String $password -AsPlainText -Force) # Check import status and wait for the import to complete $importStatus = Get-AzureRmSqlDatabaseImportExportStatus -OperationStatusLink $importRequest.OperationStatusLink [Console]::Write("Importing") while ($importStatus.Status -eq "InProgress") { $importStatus = Get-AzureRmSqlDatabaseImportExportStatus -OperationStatusLink $importRequest.OperationStatusLink [Console]::Write(".") Start-Sleep -s 10 } [Console]::WriteLine("") $importStatus # Scale down to S0 after import is complete Set-AzureRmSqlDatabase -ResourceGroupName $resourcegroupname ` -ServerName $servername ` -DatabaseName $databasename ` -Edition "Standard" ` -RequestedServiceObjectiveName "S0" # Clean up deployment # Remove-AzureRmResourceGroup -ResourceGroupName $resourcegroupname |
SQL Server Performance Queries
SQL Server Performance Tuning
|
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 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 |
-- Top 10 sessions caused blocking SELECT TOP 10 r.session_id, r.plan_handle, r.sql_handle, r.request_id, r.start_time, r.status, r.command, r.database_id, r.user_id, r.wait_type, r.wait_time, r.last_wait_type, r.wait_resource, r.total_elapsed_time, r.cpu_time, r.transaction_isolation_level, r.row_count, st.text FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st WHERE r.blocking_session_id = 0 AND r.session_id IN ( SELECT DISTINCT (blocking_session_id) FROM sys.dm_exec_requests ) GROUP BY r.session_id, r.plan_handle, r.sql_handle, r.request_id, r.start_time, r.status, r.command, r.database_id, r.user_id, r.wait_type, r.wait_time, r.last_wait_type, r.wait_resource, r.total_elapsed_time, r.cpu_time, r.transaction_isolation_level, r.row_count, st.text ORDER BY r.total_elapsed_time DESC -- To identify blockers and victims ;WITH Blockers AS (SELECT DISTINCT blocking_session_id AS session_id FROM sys.dm_exec_requests WHERE blocking_session_id > 0) SELECT 'Blocker' AS type_desc, sys.dm_exec_sessions.session_id, sys.dm_exec_requests.start_time, sys.dm_exec_requests.status, sys.dm_exec_requests.command, sys.dm_exec_requests.wait_type, sys.dm_exec_requests.wait_time, sys.dm_exec_requests.blocking_session_id, '' AS stmt_text FROM sys.dm_exec_sessions LEFT JOIN sys.dm_exec_requests ON sys.dm_exec_requests.session_id = sys.dm_exec_sessions.session_id INNER JOIN Blockers ON Blockers.session_id = sys.dm_exec_sessions.session_id UNION SELECT 'Victim' AS type_desc, sys.dm_exec_sessions.session_id, sys.dm_exec_requests.start_time, sys.dm_exec_requests.status, sys.dm_exec_requests.command, sys.dm_exec_requests.wait_type, sys.dm_exec_requests.wait_time, sys.dm_exec_requests.blocking_session_id, ST.text AS stmt_text FROM sys.dm_exec_sessions INNER JOIN sys.dm_exec_requests ON sys.dm_exec_requests.session_id = sys.dm_exec_sessions.session_id CROSS APPLY sys.dm_exec_sql_text(sys.dm_exec_requests.sql_handle) AS ST WHERE blocking_session_id > 0 -- Top Blocker (> 1 min blocking) SELECT w.session_id [Spid], DB_NAME(r.database_id) [DBName], s.status, w.blocking_session_id, w.wait_duration_ms, s.[program_name], s.client_interface_name, s.[host_name], s.cpu_time [cpu_time_ms], s.memory_usage * 8 / 1024 [Memory_usage], w.wait_type, r.wait_resource, s.deadlock_priority, CASE s.transaction_isolation_level WHEN 0 THEN 'Unspecified' WHEN 1 THEN 'ReadUncomitted' WHEN 2 THEN 'Readcomitted' WHEN 3 THEN 'Repeatable' WHEN 4 THEN 'Serializable' WHEN 5 THEN 'Snapshot' END AS TRANSACTION_ISOLATION_LEVEL, s.original_login_name, s.login_time, s.last_request_start_time, s.last_request_end_time, r.percent_complete, s.row_count, t.text, q.query_plan, aa.text [BlockingText] FROM sys.dm_os_waiting_tasks w INNER JOIN sys.dm_exec_sessions s ON w.session_id = s.session_id INNER JOIN sys.dm_exec_requests r ON s.session_id = r.session_id OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) t CROSS APPLY sys.dm_exec_query_plan(r.plan_handle) AS q OUTER APPLY ( SELECT S2.text FROM sys.sysprocesses sp CROSS APPLY sys.dm_exec_sql_text(sp.sql_handle) AS S2 WHERE sp.spid = w.blocking_session_id ) aa WHERE s.is_user_process = 1 AND w.blocking_session_id > 0 ORDER BY w.wait_duration_ms DESC -- Real Time Blocking information SELECT DB_NAME(Blocking.dbid) AS DBName, Blocking.spid AS Blocking_SPID, CASE Blocking.blocked WHEN 0 THEN 'Head Blocker' ELSE CAST(Blocking.blocked AS NVARCHAR(20)) END AS Blocking_BlockedBy, Blocking.status AS Blocking_Status, Blocking.last_batch AS Blocking_LastBatch, Blocking.cmd AS Blocking_Command, Blocking.nt_username AS Blocking_UserName, Blocking.login_time AS Blocking_LoginTime, Blocking.program_name AS Blocking_ProgramName, Blocking.hostname AS Blocking_HostName, Blocking.hostprocess AS Blocking_HostProcess, Blocking.waittime AS Blocking_WaitTime, Blocking.waittype AS Blocking_WaitType, Blocking.lastwaittype AS Blocking_LastWaitType, Blocking.waitresource AS Blocking_WaitResource, Blocking.open_tran AS Blocking_OpenTrans, Blocking.cpu AS Blocking_CPU, Blocking.memusage AS Blocking_MemoryUsage, Blocking.physical_io AS Blocking_PhysicalIO, Blocked.spid AS Blocked_SPID, Blocked.status AS Blocked_Status, Blocked.last_batch AS Blocked_LastBatch, Blocked.cmd AS Blocked_Command, Blocked.nt_username AS Blocked_UserName, Blocked.login_time AS Blocked_LoginTime, Blocked.program_name AS Blocked_ProgramName, Blocked.hostname AS Blocked_HostName, Blocked.hostprocess AS Blocked_HostProcess, Blocked.waittime AS Blocked_WaitTime, Blocked.waittype AS Blocked_WaitType, Blocked.lastwaittype AS Blocked_LastWaitType, Blocked.waitresource AS Blocked_WaitResource, Blocked.open_tran AS Blocked_OpenTrans, Blocked.cpu AS Blocked_CPU, Blocked.memusage AS Blocked_MemoryUsage, Blocked.physical_io AS Blocked_PhysicalIO FROM sys.sysprocesses AS Blocking WITH (NOLOCK) INNER JOIN sys.sysprocesses AS Blocked WITH (NOLOCK) ON Blocking.spid = Blocked.blocked ORDER BY Blocking.blocked ASC, Blocked.blocked ASC -- Blocking Details (millisecond detection) SELECT sp.spid, sp.status, sp.hostprocess, sp.hostname, sp.loginame, sp.cpu, sp.physical_io, sp.blocked, DB_NAME(sp.dbid) AS [Database], sp.cmd, sp.program_name, sp.uid, sp.memusage, sp.waitresource, sp.waittype, sp.lastwaittype, sp.login_time, sp.last_batch, sp.open_tran, sp.hostname, sp.[sql_handle], S2.text FROM sys.sysprocesses sp CROSS APPLY sys.dm_exec_sql_text(sp.sql_handle) AS S2 WHERE spid > 50 AND sp.blocked > 0 ORDER BY cpu DESC -- select * from Sys.Dm_Exec_Sql_Text(0x0300080028881470db6f25019a9b00000100000000000000) -- Locks established in current database SELECT (CASE sys.dm_tran_locks.request_session_id WHEN -2 THEN 'ORPHANED DISTRIBUTED TRANSACTION' WHEN -3 THEN 'DEFERRED RECOVERY TRANSACTION' ELSE sys.dm_tran_locks.request_session_id END ) AS session_id, DB_NAME(sys.dm_tran_locks.resource_database_id) AS database_name, sys.objects.name AS locked_obj_name, sys.dm_tran_locks.resource_type AS locked_resource, sys.dm_tran_locks.request_mode AS lock_type, st.text AS stmt_text, sys.dm_exec_sessions.login_name AS login_name, sys.dm_exec_sessions.host_name AS host_name, sys.dm_tran_locks.request_status AS request_status FROM sys.dm_tran_locks JOIN sys.objects ON sys.objects.object_id = sys.dm_tran_locks.resource_associated_entity_id JOIN sys.dm_exec_sessions ON sys.dm_exec_sessions.session_id = sys.dm_tran_locks.request_session_id JOIN sys.dm_exec_connections ON sys.dm_exec_connections.session_id = sys.dm_exec_sessions.session_id CROSS APPLY sys.dm_exec_sql_text(sys.dm_exec_connections.most_recent_sql_handle) AS st WHERE sys.dm_tran_locks.resource_database_id = DB_ID() ORDER BY sys.dm_tran_locks.request_session_id |
SQL Server IO Stall Statistics
|
1 2 3 4 5 6 7 8 9 10 11 |
-- Read IO Stalls Statistics SELECT @@VERSION GO SELECT type, SUM(io_stall_read_ms) AS io_stall_read, SUM(io_stall_write_ms) AS io_stall_write, SUM(num_of_reads) AS num_of_reads, SUM(num_of_writes) AS num_of_writes FROM sys.dm_io_virtual_file_stats(NULL, NULL) fs JOIN sys.database_files df ON fs.file_id = df.file_id WHERE database_id = DB_ID() GROUP BY df.type |
Azure – Troubleshooting
Azure – Troubleshooting AKS Recommended KeepAlive Settings
|
1 2 3 4 5 |
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
Change Database Recover Model
|
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 |
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
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
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
-- 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
|
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 |
/******************************************************************************************************** -- 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
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
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; |
Install and Run SQL Server Docker Container on Mac
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
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
Number of Connections by Host
|
1 2 3 4 5 6 |
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
|
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 |
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
Raju Venkataraman
Posted in AWS, AWS RDS, Interview Questions, Scaling SQL Server, SQL Server
—
No Comments ↓
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
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?
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
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
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.
|
1 2 3 4 5 |
$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: |
|
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 |
[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:
|
1 2 3 4 5 6 7 8 |
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) */ |
…
AWS Data Pipeline & Load S3 File Into MySQL
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…
(No Ratings Yet)
Loading...
Top