In 200-400 words, evaluate whether decreasing/increasing the Memory provided detriments/benefits in query performance, why it made that impact/did not make that impact, and determine which of the two readings you’d recommend to users to base their tuning on.
This assignment is aimed at building an understanding of how to improve performance within databases by taking advantage the computers hardware by modifying MS SQL settings
Prerequisites:
Completion of Assignment 1B, reading of Module Readings and Slides.
Run the following query to understand how much Memory is currently in use in the
SELECT
physical_memory_in_use_kb/1024 AS sql_physical_memory_in_use_MB,
large_page_allocations_kb/1024 AS sql_large_page_allocations_MB,
locked_page_allocations_kb/1024 AS sql_locked_page_allocations_MB,
virtual_address_space_reserved_kb/1024 AS sql_VAS_reserved_MB,
virtual_address_space_committed_kb/1024 AS sql_VAS_committed_MB,
virtual_address_space_available_kb/1024 AS sql_VAS_available_MB,
page_fault_count AS sql_page_fault_count,
memory_utilization_percentage AS sql_memory_utilization_percentage,
process_physical_memory_low AS sql_process_physical_memory_low,
process_virtual_memory_low AS sql_process_virtual_memory_low
FROM sys.dm_os_process_memory;
Instructions:
Part A: Query Baseline
Execute the following query, and record the runtime. It can be seen on the bottom of the screen next to the number of rows.
- Select * From Student Join Enrollment on Student.ID = Enrollment.Student_ID Order by Name_Last desc
Total execution time:00:00:14.924
Part B: Upping the RAM
- Run the follow query to increase the set the RAM available to the database to something higher than what you saw in the sql_physical_memory_in_use_MB column on the query above. You’ll need to see how much Memory your particular device has to set this correctly, but the below example will show an increase to 8GB
- sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'max server memory', 8192;
GO
RECONFIGURE;
GO
;
- Note the above query is 8GB. If you want to go to 4 GB, divide that number by 2, or 16 GB multiply by 2, but it is dependent on your PCs available memory
- You must Restart your PC for this to take effect
Part C: Re-Run Query Baseline in Part A
- Re-Run the Query from Part A and evaluate if the speed to execute it increased or decreased
Submission:
In 200-400 words, evaluate whether decreasing/increasing the Memory provided detriments/benefits in query performance, why it made that impact/did not make that impact, and determine which of the two readings you’d recommend to users to base their tuning on.
reading:https://www.sqlservertutorial.net/sql-server-basics/sql-server-primary-key/
https://www.sqlshack.com/dont-fear-sql-server-performance-tuning/
The question at hand is the essential topic of optimizing MS SQL Server for maximum database performance. It investigates the effects of altering SQL Server memory settings and attempts to establish if decreasing or increasing memory allocation enhances or degrades query performance. The analysis seeks to offer insight on why these changes have the effects that they do and, finally, proposes which strategy users should prioritize for their tuning efforts. This is an important concern for SQL Server database administrators and developers, as performance optimization can have a substantial impact on the efficiency and responsiveness of database-driven applications.
Step by step
Solved in 4 steps
