[go: up one dir, main page]
Scribd
Upload
28 views3 pages

Practical 9.4

The document discusses various data recovery tools and techniques, performance monitoring and diagnostic tools in SQL Server like Query Store, best practices for using temporary tables and databases, methods to optimize queries and resolve memory issues.

Uploaded by

rasindugamlath9
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
28 views3 pages

Practical 9.4

The document discusses various data recovery tools and techniques, performance monitoring and diagnostic tools in SQL Server like Query Store, best practices for using temporary tables and databases, methods to optimize queries and resolve memory issues.

Uploaded by

rasindugamlath9
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 3

01.

Appropriate Recovery Tools and Mechanisms:

a. Reasons for Data Loss:

 Hardware Failures: Such as hard drive crashes or failure of other storage devices.
 Human Errors: Accidental deletion, modification, or overwriting of data.
 Software Errors: Corruption of database files or software glitches.
 Malware and Viruses: Malicious software can corrupt or delete data.
 Natural Disasters: Fire, flood, earthquakes, etc., can damage hardware.
 Theft: Physical theft of hardware where the data is stored.

b. How Data Recovery Tools Work:

 Data recovery tools work by scanning the storage media for traces of lost or deleted files. They
use algorithms to reconstruct data from these traces and restore it to a usable form.

c. Commercial Data Recovery Tools:

 Disk Drill
 EaseUS Data Recovery Wizard
 Wondershare Recoverit
 Recuva
 R-Studio
 Ontrack EasyRecovery

d. Data Recovery Tools vs. Backup Strategies:

 Data recovery tools are essential for retrieving lost data, but they are not foolproof. Compared to
well-defined backup strategies, data recovery tools have limitations:
 They may not recover all data, especially if it's severely corrupted.
 Recovery can be time-consuming and may not restore data to its original state.
 Proper backup strategies ensure data is recoverable, consistent, and minimizes downtime in case
of data loss, making them more effective and reliable.

02. Performance Problems and Diagnostic Tools:

a. Common Performance Issues and Causes:

 Slow queries: Inefficient SQL queries or lack of indexes.


 High CPU usage: Complex queries, insufficient indexing, or resource-intensive processes.
 Memory pressure: Inadequate RAM causing excessive disk usage (paging).
 Disk I/O bottlenecks: Slow storage or high read/write operations.
 Locking and blocking: Contentions due to concurrent transactions.
 Network issues: Slow network causing delays in data retrieval.
b. Query Store:

 Query Store is a feature in SQL Server that captures query execution plans and runtime statistics.
It helps identify performance issues, track query performance over time, and force specific
execution plans if needed.

c. Setting up Query Store:

 To set up Query Store, you can follow the instructions provided in the linked YouTube video.

03. Improper Use of Database and Temporary Table Spaces:

a. Temporary Tables and In-Memory OLTP:

 Temporary tables are used to store temporary data. They can be session-specific or global and
are useful for intermediate results.
 In-Memory OLTP is a feature in SQL Server for optimizing the performance of transactional
workloads. It stores and processes tables entirely in memory.

b. Purpose of Temporary Tables:

 Temporary tables are used to store intermediate results during complex query processing or to
store temporary data that needs to persist for the duration of a session.

c. Advantages and Disadvantages:

 Temporary Tables:
 Advantages: Improved query performance, reuse of intermediate results.
 Disadvantages: Temporary tables consume tempdb space, potential for increased disk I/O.
 In-Memory OLTP:
 Advantages: Faster data access due to in-memory storage, optimized for high-speed data
processing.
 Disadvantages: Limited support for certain data types, requires careful memory management.

04. Corrective Actions Based on Diagnostic Tests:

a. Rectifying Performance Issues Detected by SQL Query Store:

 Identify and analyze problematic queries.


 Review execution plans for inefficiencies.
 Optimize queries by adding appropriate indexes, rewriting SQL, or updating statistics.
 Monitor Query Store data to ensure improvements.
05. Rollback Segments and Database Snapshots:

a. Rollback Segments Reconfigured:


 To reconfigure rollback segments, follow the steps provided in the linked YouTube video
for the Adventure Works database.

b. Implement Database Snapshot and Restore:


 Follow the instructions in the provided YouTube videos to implement database
snapshots and perform restores for the Adventure Works database.

06. Maximizing Query Efficiency:

To improve query efficiency in SQL Server, you can explore various methods such as:

 Proper indexing of tables.


 Use of indexed views.
 Optimizing queries by avoiding unnecessary joins or subqueries.
 Regularly updating statistics.
 Partitioning large tables for faster data access.
 Considering columnstore indexes for analytical queries.

07. Identifying and Resolving Memory Contentions:

 Identify memory contents by monitoring SQL Server memory usage.


 Adjust SQL Server memory settings to allocate appropriate memory to SQL Server processes.
 Investigate and optimize queries that consume excessive memory.
 Monitor and adjust other applications/services running on the server to prevent memory
conflicts.

You might also like