Elastic Block Storage (EBS) --AWS

Amazon Elastic Block Store (EBS) provides block level storage volumes for use with EC2 instances. 

EBS volumes are highly available and reliable storage volumes that can be attached to any running instance that is in the same Availability Zone. 

With Amazon EBS, you pay only for what you use. 

You can create EBS provisioned and throughput optimized  volumes up to 16 TiB.

You can mount these volumes as devices on your Amazon EC2 instances. 

Amazon Elastic Block Store provides block level storage volumes for use with EC2 instances.

EBS can be attached to any running instance that is in the same Availability Zone. 

EBS volumes that are attached to an EC2 instance are exposed as storage volumes that persist independently from the life of the instance. 

With Amazon EBS, you pay only for what you use.

You can mount multiple volumes on the same instance, but each volume can be attached to only one instance at a time.

You can dynamically change the configuration of a volume attached to an instance.

EBS volumes behave like raw, unformatted block devices. We can create a file system on top of these volumes.

Blocking on sqlserver

To check blocking


select * from sys.sysprocesses where blocked <> 0


head blocker

++++++++++

declare @handle binary(20), @spid int

select top 1 @spid = blocked

from master..sysprocesses a

where a.blocked != 0 

and a.blocked not in (select spid from master..sysprocesses b where blocked != 0)

if @spid is not null

begin

  select @handle = sql_handle from master..sysprocesses where spid = @spid

  exec sp_who2 @spid

  dbcc inputbuffer (@spid)

select lastwaittype from sysprocesses where spid=@spid

  select * from ::fn_get_sql(@handle)

end  

Last successful backup in sqlserver script

SET quoted_identifier OFF
DECLARE @dbname AS VARCHAR(80)
DECLARE @msgdb AS VARCHAR(100)
DECLARE @dbbkpname AS VARCHAR(80)
DECLARE @dypart1 AS VARCHAR(2)
DECLARE @dypart2 AS VARCHAR(3)
DECLARE @dypart3 AS VARCHAR(4)
DECLARE @currentdate AS VARCHAR(10)
DECLARE @server_name AS VARCHAR(30)
SELECT @server_name = @@servername
SELECT @dypart1 = DATEPART(dd,GETDATE())
SELECT @dypart2 = DATENAME(mm,GETDATE())
SELECT @dypart3 = DATEPART(yy,GETDATE())
SELECT @currentdate= @dypart1 + @dypart2 + @dypart3

SELECT SUBSTRING(s.name,1,50) AS 'DATABASE Name',
b.backup_start_date AS 'Full DB Backup Status',
c.backup_start_date AS 'Differential DB Backup Status',
d.backup_start_date AS 'Transaction Log Backup Status'
FROM MASTER..sysdatabases s
LEFT OUTER JOIN msdb..backupset b
ON s.name = b.database_name
AND b.backup_start_date =
(SELECT MAX(backup_start_date)AS 'Full DB Backup Status'
FROM msdb..backupset
WHERE database_name = b.database_name
AND TYPE = 'D') -- full database backups only, not log backups
LEFT OUTER JOIN msdb..backupset c
ON s.name = c.database_name
AND c.backup_start_date =
(SELECT MAX(backup_start_date)'Differential DB Backup Status'
FROM msdb..backupset

Procedure to attend SQL server memory issues

Check SQL installed on Server

Confirm SQL server is installed on the server through services.msc or SQL services configuration manger.

Start à Run àservices.msc verify SQL Services are visible like SQL server, SQL Server Agent. Etc.,

SQL services configuration

Start àProgramsàMicrosoft SQL server 20xxàConfiguration Tools àSQL Server configuration  Manager

Verify SQL maintenance jobs status

Verify SQL maintenance jobs like Reindex , Update Stats or CheckDB  is running during business hours. These jobs are created and scheduled to run on week end or off business hours.  If any maintenance jobs in running state.

Check blocking

select *from sysprocesseswhere blocked >0

If many blockings are found, head blocker can be determined by using the query below.

declare @handle binary(20), @spid int

select top1 @spid = blocked

from master..sysprocesses a

where a.blocked != 0

and a.blocked not in (select spid from master..sysprocesses b whereblocked != 0)

if @spid isnot null

begin

  select@handle = sql_handlefrom master..sysprocesses where spid = @spid

  execsp_who2 @spid

  dbccinputbuffer (@spid)

--exec ('kill ' + @SPID)

select lastwaittype from sysprocesses where spid=@spid

  select* from ::fn_get_sql(@handle)

end

Verify SQL Server Max. Memory settings

 The minimum and maximum memory values are important, especially since we gave the SQL Server account the permission to lock its pages in memory. If other applications are running on this server, we need to specify how much memory want SQL Server to take.

SQL Server application is Memory consuming application. We need to restrict SQL instance with minimum and maximum memory setting by leaving some amount of memory to Operating system.

Go into SQL Server Management Studio, right-click on the server name and click Properties, go into Memory, and just verify SQL max. memory set for the SQL server. 

Check Memory Pressure

This query will provide information about how many queries are requesting for memory , how many are granted and how many are pending to be granted.

--Query 3: SELECT * FROM sys.dm_exec_query_resource_semaphores --Query 4: SELECT TEXT,* FROM sys.dm_exec_query_memory_grants cross apply sys.dm_exec_sql_text (sql_handle) order by requested_memory_kb desc

Checking resource semaphore wait type during memory pressure.

SELECT * FROM SYSPROCESSES  ORDER BY lastwaittype

SELECT * FROM sys.dm_exec_query_resource_semaphore

File movement in sql server

USE master;
GO

ALTER  database <Database name>
MODIFY FILE (NAME = <database>_Data,
 FILENAME = ' J:\sqldb03\MSSQL12.MSSQLSERVER\data\<database>.mdf');

go

ALTER DATABASE  <Database name>
SET SINGLE_USER WITH ROLLBACK IMMEDIATE

go

ALTER DATABASE  <Database name>
SET OFFLINE
GO

 Move files from
U:\SQL01\MSSQL11.SQLE\MSSQL\Data to U:\SQL07\MSSQL11.SQLE\MSSQL\Data

ALTER DATABASE  <Database name> SET ONLINE
go

ALTER DATABASE  <Database name> SET MULTI_USER

Go

AWS

Amazon Elastic Compute Cloud (Amazon EC2)

Amazon Elastic Compute Cloud (Amazon EC2) is a web service that provides resizable
compute capacity in the cloud. It allows organizations to obtain and configure virtual servers
in Amazon’s data centers and to harness those resources to build and host software systems.
Organizations can select from a variety of operating systems and resource configurations
(memory, CPU, storage, and so on) that are optimal for the application profile of each
workload. Amazon EC2 presents a true virtual computing environment, allowing
organizations to launch compute resources with a variety of operating systems, load them
with custom applications, and manage network access permissions while maintaining
complete control.

AWS Lambda

AWS Lambda is a zero-administration compute platform for back-end web developers that
runs your code for you on the AWS Cloud and provides you with a fine-grained pricing
structure. AWS Lambda runs your back-end code on its own AWS compute fleet of Amazon
EC2 instances across multiple Availability Zones in a region, which provides the high
availability, security, performance, and scalability of the AWS infrastructure.

Auto Scaling

Auto Scaling allows organizations to scale Amazon EC2 capacity up or down automatically
according to conditions defined for the particular workload (see Figure 1.3). Not only can it be
used to help maintain application availability and ensure that the desired number of Amazon
EC2 instances are running, but it also allows resources to scale in and out to match the
demands of dynamic workloads. Instead of provisioning for peak load, organizations can
optimize costs and use only the capacity that is actually needed.

AWS account create.

ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT

ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT


Some time we face this error when we run batch jobs with very high degree of parallelism.  Oracle is  unable to use more PGA and it kills the session and the batch job fails. This can happen even though you assign very high  PGA_AGGREGATE_LIMIT.

Batch job sessions failed with below errors.

ORA-00028: your session has been killed
ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT
PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT of 2048 MB

To Avoid this error: Select correct degree of parallelism

Selecting degree of parallelism is very much important, number of parallel sessions depends on server CPU core.

Degree of parallelism can be calculated with below equation.

For a single instance, DOP = PARALLEL_THREADS_PER_CPU x CPU_COUNT.
For an Oracle RAC configuration, DOP = PARALLEL_THREADS_PER_CPU x CPU_COUNT x INSTANCE_COUNT

Running more that 16 parallel session in 8 core CPU server is not a good idea.

You may have a look at this oracle document for more details.

https://docs.oracle.com/cd/E11882_01/server.112/e25523/parallel002.htm

Check the degree of parallelism is in recommended value. If yes check the  parameter PGA_AGGREGATE_LIMIT is set for small value. You may increase it according to degree of parallelism.

Still facing the issue and if your database is 12c, you may revert this parameter to pre12c by setting pga_aggregate_limit to 0.

alter system set pga_aggregate_limit=0 scope=both;

Setting the higher value for pga_aggregate_limit

alter system set pga_aggregate_limit=<Value> scope=both;

This parameter change not required instance restart.