Technology | Kaizen - Continuous Improvement

Category: Technology

Import DACPAC file to SQL Server

Posted on January 26, 2019 by

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

Import DACPAC File https://docs.microsoft.com/en-us/azure/sql-database/scripts/sql-database-import-from-bacpac-powershell

# 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

# 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

Posted on January 25, 2019 by

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

SQL Server Performance Tuning

-- 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

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

Posted on January 15, 2019 by

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

-- 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

-- Read IO Stalls Statistics

SELECT @@VERSION

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

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 — 2 Comments ↓

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

Category: Technology

Import DACPAC file to SQL Server

SQL Server Performance Queries

SQL Server IO Stall Statistics

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

Recent Posts

Categories

Recent Posts

Archives