Page Life Expectancy, Buffer Cache Hit Ratio

 Page Life Expectancy (PLE) in SQL Server:

Page Life Expectancy is the number of seconds a page will stay in the buffer pool without references.

If your page stays for more time in the buffer pool your PLE is high which leads to high performance. Every time request comes there are more chances that it may find its data in the cache itself, instead of going to the hard drive to read the data.

PLE measures in seconds.

PLE is one of the performance counters of SQL Server.

Higher the PLE, performance of SQL Server is good.

As per recommendation, the value of PLE counter is around 300 seconds.

You can check this value for your SQL server using below DMV.

SELECT object_name, counter_name, cntr_value from sys.dm_os_performance_counters WHERE [object_name] LIKE '%Buffer Manger%' AND [counter_name] = 'page life expectancy'

Buffer Cache Hit Ratio

Buffer Cache Hit Ratio indicates the percentage of pages found in the buffer cache without having to read from the disk.

The ration is the total number of cache hits divided by the total number of cache lookups over the last few thousand page accesses.

Buffer Cache Hit Ratio is one of the performance counters of SQL server.

You can increase the buffer cache hit ratio by increasing the amount of memory available to SQL Server or by using the buffer pool extension feature.

You can check this value for your SQL server using below DMV.

SELECT object_name, counter_name, cntr_value from sys.dm_os_performance_counters WHERE [object_name] LIKE '%Buffer Manger%' AND [counter_name] = 'Buffer cache hit ratio'


Who is a DBA?

An IT Professional, who is responsible for Installation, Configuration, Upgrading, Administration, Monitoring, Maintenance and Security of Databases in any organization.

Phases of a DBA Career path (not limited to these only)

  • Junior DBAs
  • Mid-level DBAs
  • Senior DBAs
  • DBA consultants
  • Manager or director of database administration/information technology
  • Data Architects
  • Release Managers
  • Change Managers

Main Responsibilities

  • Installing and upgrading the database server tools
  • Planning future storage requirements for the database systems
  • Modifying the database structure based on information given by application developers
  • Enrolling users and maintaining system security
  • Ensuring compliance with database vendor license agreement
  • Controlling and monitoring user access to the databases
  • Monitoring and optimizing the performance of the databases
  • Planning for backup and recovery of database information
  • Data Archiving
  • Backing up and restoring databases
  • Contacting database vendor for technical support
  • Generating various reports by querying from database as per need
  • Participating in Disaster Recovery Tests
  • Working closely with other teams like Network Operation Teams, Monitoring Teams

High Level Skills that a DBA must have

  • Top Communication skills
  • Good knowledge of database theory
  • Good knowledge of database design, logical or physical
  • Good knowledge of RDBMS
  • Knowledge of structured query language (SQL), e.g. Transact-SQL
  • Understanding of distributed computing architectures
  • Understanding of the underlying operating system
  • Understanding of storage technologies, memory management, disk arrays, NAS/SAN, networking
  • Understanding of routine maintenance, recovery, and handling failover of a Database

As I mentioned, today’s jobs are more demanding. DBAs are now a days performing many roles and when they specialized in those roles, they grow like

Production Support DBAs: These DBAs are focused on the physical aspects of database administration such as DBMS installation, configuration, patching, upgrades, backups, restores, refreshes, performance optimization, maintenance and disaster recovery.

Development DBAs: These DBAs are focused on the logical and development aspects of database administration such as data model design and maintenance, DDL (data definition language) generation, SQL writing and tuning, coding stored procedures, collaborating with developers to help choose the most appropriate DBMS feature/functionality and other pre-production activities.

Application DBAs: These DBAs are usually found in organizations that have purchased 3rd party application software such as ERP (enterprise resource planning) and CRM (customer relationship management) systems.

Hybrid DBAs: As name suggests, these DBAs are usually those who performs almost all the tasks which are mentioned. These DBAs might not be specialized however they are the one who grows like Architects at enterprise level.

New security server roles in SQL SERVER 2022

Now the DBA can have peace of mind by giving access to these roles if any user requested for performance or monitoring tools access . 

Now no need of giving SA access to such requests.

Below is 2019 SQL SERVER roles. 


SQL SERVER 2022 SERVER ROLES: 




Fixed server-level roleDescription
##MS_DatabaseConnector##Members of the ##MS_DatabaseConnector## fixed server role can connect to any database without requiring a User-account in the database to connect to.

To deny the CONNECT permission to a specific database, users can create a matching user account for this login in the database and then DENY the CONNECT permission to the database-user. This DENY permission will overrule the GRANT CONNECT permission coming from this role.
##MS_DatabaseManager##Members of the ##MS_DatabaseManager## fixed server role can create and delete databases. A member of the ##MS_DatabaseManager## role that creates a database, becomes the owner of that database, which allows that user to connect to that database as the dbo user. The dbo user has all database permissions in the database. Members of the ##MS_DatabaseManager## role don't necessarily have permission to access databases that they don't own.
##MS_PerformanceDefinitionReader##Members of the ##MS_PerformanceDefinitionReader## fixed server role can read all catalog views that are covered by VIEW ANY PERFORMANCE DEFINITION, and respectively has VIEW PERFORMANCE DEFINITION permission on any database on which the member of this role has a user account. This is a subset of what the ##MS_DefinitionReader## server role has access to.
##MS_SecurityDefinitionReader##Members of the ##MS_SecurityDefinitionReader## fixed server role can read all catalog views that are covered by VIEW ANY SECURITY DEFINITION, and respectively has VIEW SECURITY DEFINITION permission on any database on which the member of this role has a user account. This is a small subset of what the ##MS_DefinitionReader## server role has access to.
##MS_DefinitionReader##Members of the ##MS_DefinitionReader## fixed server role can read all catalog views that are covered by VIEW ANY DEFINITION, and respectively has VIEW DEFINITION permission on any database on which the member of this role has a user account.
##MS_LoginManager##Members of the ##MS_LoginManager## fixed server role can create and delete logins.
##MS_ServerPerformanceStateReader##Members of the ##MS_ServerPerformanceStateReader## fixed server role can read all dynamic management views (DMVs) and functions that are covered by VIEW SERVER PERFORMANCE STATE, and respectively has VIEW DATABASE PERFORMANCE STATE permission on any database on which the member of this role has a user account. This is a subset of what the ##MS_ServerStateReader## server role has access to.
##MS_ServerSecurityStateReader##Members of the ##MS_ServerSecurityStateReader## fixed server role can read all dynamic management views (DMVs) and functions that are covered by VIEW SERVER SECURITY STATE, and respectively has VIEW DATABASE SECURITY STATE permission on any database on which the member of this role has a user account. This is a small subset of what the ##MS_ServerStateReader## server role has access to.
##MS_ServerStateReader##Members of the ##MS_ServerStateReader## fixed server role can read all dynamic management views (DMVs) and functions that are covered by VIEW SERVER STATE, and respectively has VIEW DATABASE STATE permission on any database on which the member of this role has a user account.
##MS_ServerStateManager##Members of the ##MS_ServerStateManager## fixed server role have the same permissions as the ##MS_ServerStateReader## role. Also, it holds the ALTER SERVER STATE permission, which allows access to several management operations, such as: DBCC FREEPROCCACHE, DBCC FREESYSTEMCACHE ('ALL'), DBCC SQLPERF()

Reference Link



SQL DBA Checklist/Activities

 SQL DBA Checklist/Activities

Daily Checklist

1) Backups

2) SQL Server Error Logs

3) SQL Server Agent Jobs

4) HA or DR Logs

5) Shift Handover (New/Pending Tickets)

6) Implementing planned Change Tickets

Weekly Checklist

1) Integrity Checks (DBCC CHECKDB)

2) Index Maintenance

3) Updation of Statistics

4) Cycle SQL Server Error Logs

5) Reporting of Tickets Handled

6) Planning of Change Tickets

7) If any Weekly Meetings, then Prepare MOM (Minutes of Meeting).

8) Attending CAB Meetings and taking approvals for Changes.

Monthly Checklist

1) Backup Validation Test.

2) Capacity Planning: Disk, CPU, and Memory.

3) Plan if any Security Patches or Critical Hotfixes are released.

4) Report on overall Uptime/Downtime

Quarter Checklist

1) DR Test

2) Check who all have SYSADMIN permission and remove unwanted logins having this permission.

3) SOX Audit Standard

Yearly Checklist

1) Licensing Validation (if it is Volume Based Licensing)

2) Service Pack validation and applying newly released SPs.




Getting Database Backup History In SQL Server

--Getting Database Backup History In SQL Server 

SELECT

    bs.database_name,
    bs.backup_start_date,
    bs.backup_finish_date,
    bs.server_name,
    bs.user_name,
    bs.type,
    bm.physical_device_name
FROM msdb.dbo.backupset AS bs
INNER JOIN msdb.dbo.backupmediafamily AS bm on bs.media_set_id = bm.media_set_id