Contents |
11 CPU Threads
64-bit computer | |
<= 4 processors | 512 |
8 processors | 576 |
16 processors | 704 |
32 processors | 960 |
64 processors | 1472 |
128 processors | 4480 |
256 processors | 8576 |
Monday, October 30, 2023
Tools - DTA
10.2 Database Engine Tuning Advisor (Dta) Tool
Database Engine Tuning advisor (DTA) tool is used analyse / understand .sql queries and .trc (profiler generate trace .trc file) audit reports. This tool gives us recommendations on possible database objects to be created for improving the performance of the queries.
10.2.1 Recommendations - index /statistics /partitions
The recommendations include:
i. index creation / index alter
ii. statistics creation / statistics update
iii. partitions creation / partitions alter
from ssms tool > go to top : tools >> database engine tuning advisor >> specify server name >
input = trace file from profiler tool abvoe. select input database and workload database
start analysis.. this gives "recommendations".
select each recommendation manually >> generate script (last column in recommendation table)
run the script on the database.
or
right click query in ssms >> analyse query in db enine tuning advisor tool. this gives "recommendations" > run scripts
Ref - TechBrothersIT,
Execution Plan
12 Execution Plan
"Execution Plan" Is Used By "Query Optimizer" Component To Ensure The Best Usage Of Resources (Memory & Cpu)
the above execution plans also give recommendations about missing indexes for a specific query. also, query execution stats.
but for complete set of recommendations we use dta tool [database engine tuning advisor tool].
12.1 Types of Execution Plans Statistics (query statistics) :
12.1.1 Estimated Execution Plan
plan for query execution; generated before the query execution
12.1.2 Live Execution Plan
plan for query execution; generated during the query execution >=2016
12.1.3 Actual Execution Ption;
12.1.4 generated after the query execution
12.2 Possible Plan Types:
12.2.1 Table Scan
Applicable for heap (table without clustered index). the entire table is scanned from 1st row to last row.
12.2.2 Index scan :
index pages content is scanned for data retrieval. all index pages are accessed for reads
12.2.3 index seek :
index pages content is searched for data retrieval. only required index pages are involved
12.2.4 spooling :
required data is loaded to tempdb for faster calculations, comparisons, loops, audits, etc..
12.3 Query Cost Types:
12.3.1 IO cost
refers to the disk io for reads & writes on the database files. for higher io cost, track for missing indexes & implement dta tool.
12.3.2 CPU cost
Refers to the thread management factor based on "numa" nodes on your processor. Each processor has two cores in a socket.
ex: if you have a core 2 duo processor means you have four processor nodes. Ensure lesser cpu cost. For higher cpu cost values, boost sql server priority and set proper thread count.
ssms > connect to server > right click server > properties
12.3.3 sub tree cost
refers to the cost involved in analysing the parse tree and compile tree.
reduce the number of sub queries and self joins, if possible.
also ensure to check the health of the database.
recompile the long running stored procedures.
12.3.4 operator cost
this refers to query predicates, keywords and operations within sql queries.
ex: merge join option is faster compared to hash join option.
for higher operator cost, it is advisable to ensure stats updates and sp recompilations.
** avoid nested loops in the execution plans. for this, we "merge join" option.
PERMON
13 PERMON - Performance Monitor
13.1 SQL 2018 School
THIS IS A WINDOWS LEVEL TOOL USED TO AUDIT / MONITOR THE SQL SERVER PERFORMANCE METRICS.
EXAMPLE PERFMON COUNTERS:
DATABASES
ACTIVE TRANSACTIONS
PERCENT LOG USED
MEMORY
FREE MEMORY
TOTAL SERVER MEMORY
TARGET SERVER MEMORY
TRANSACTIONS
FREE SPACE IN TEMPDB
LONGEST RUNNING TRANSACTION
TRANSACTIONS
13.2 HOW TO RUN PERFMON TOOL?
WINDOWS >> RUN >> PERFMON.EXE >> THIS LAUNCHES PERFORMANCE MONITOR TOOL >> CLICK @ "PERFORMANCE MONOTOR" PAGE TO THE LEFT SIDE
OF THE SCREEN > THEN FROM RIGHT SIDE : CLICK @ GREEN COLORED "+" SYMBOL TO ADD NEW PERFORMANCE COUNTER.
THEN SELECT REQUIRED COUNTERS FROM THE LEFT SIDE SCREEN > ADD.
THEN SELECT EACH COUNTER FROM THE RIGHT SIDE MONITORING SCREEN > CLICK @ HIGHLIGHT. THEN MEASURE THE USAGE METRICS:
LAST VALUE
AVERAGE VALUE
MINIMUM VALUE
MAXIMUM VALUE
DURATION
MORE USAGE OF DISK SPACE IN TEMPDB MEANS MORE SPOOLING ACTIVITY.
SPOOLING IS A MECHANISM TO LOAD DATA FROM DATABASES LOG FILES OR DATA FILES OR MEMORY INTO TEMPDB. FOR SNAPSHOTS, QUERY EXECUTIONS
AND TEMPORARY TABLES, CALCULATIONS. WE NEED TO MINIMIZE THE USAGE OF TEMPDB (AVOID USING PROFILER TOOL, DBCC, INDEX OPTIONS FOR
SORTING IN TEMPDB, SNAPSHOT ISOLATION, TEMPORARY TABLES, TABLE VARIABLES).
IMPORTANT COUNTERS perfmon.exe
SQL Server Access Methods object: Page Splits/sec
To monitor number of page splits w.r.t SQL Server. If the number of page splits is high, we need to increase the fill factor of our indexes there by providing more room in data pages and subsequently reducing the chances for Page Splits.
SQL Server Buffer Manager Object: Cache Size (pages)
To monitor the amount of physical RAM used by SQL Server's data cache. This number is presented in pages. We should see that no more than 60 %
SQLServer: SQL Statistics: Batch Requests/Sec counter
This counter measures the number of batch requests that SQL Server receives per second. Value greater than 1000 batch requests per second indicate a busy Server, and could lead to CPU bottleneck.
SQLServer: SQL Statistics: SQL Compilations/Sec counters
To monitor compilations on SQL Server requests per second. This counter should not exceed 150.
SQLServer: Databases: Log Flushes/sec
To monitor the number of log flushes per second. This can be measured on a per database level, or for all databases on a SQL Server. If the counter is more, we need to increase RAM allocated for SQL Server.
SQL Server General Statistics Object: User Connections
To monitor the number of user connections, not the number of users, those are currently connected to SQL Server.
Server Locks Object: Number of Deadlocks/sec
To minotor the number of deadlocks per second. Since the monitoring is done on per second basis, we may always see some number for this counter. For average, we need to average this value to seconds.
SQL Server Locks Object: Average Wait Time (ms)
To monitor the average wait time for locks. This counter is very useful if the users experience waits for their transactions to complete.
SQL Server Access Methods Object: Full Scans/sec
To monitor the number of table scans SQL Server instance is performing. This counter is for an entire server, not pertaining to a specific database.
SQL Server Buffer Manager Object: Buffer Cache Hit Ratio
To monitor how often SQL Server access buffer and not the hard disk, to retreive data. The higher this ratio, the less often SQL Server has to go to the hard disk to fetch data, and performance overall is boosted.
SQLServer:Memory Manager: Total Server Memory (KB)
To monitor how much total RAM the instance is using.
SQLServer:Memory Manager: Target Server Memory (KB)
Specifies how much memory SQL Server would like to have in order to operate efficiently. This is actually based on the number of buffers reserved by SQL Server when it is first started up.
IO Monitoring:
PhysicalDisk Object: Avg. Disk Queue Length - The average number of physical read and write requests that were queued on the selected physical disk during the sampling period. If the I/O system is overloaded, more read/write operations will be waiting. If the disk queue length exceeds a specified value too frequently during peak usage of SQL Server, there might be an I/O bottleneck.
Avg. Disk Sec/Read: The average time, in seconds, of a read of data from the disk. Use the following to analyze numbers in the output.
Less than 10 milliseconds (ms) = very good
Between 10-20 ms = okay
Between 20-50 ms = slow, needs attention
Greater than 50 ms = serious IO bottleneck
Avg. Disk Sec/Write: The average time, in seconds, of a write of data to the disk. See the guidelines for the previous item, Avg. Disk Sec/Read.
Physical Disk: %Disk Time: The percentage of elapsed time that the selected disk drive was busy servicing read or write requests. A general guideline is that if this value > 50%, there is an I/O bottleneck.
Avg. Disk Reads/Sec: The rate of read operations on the disk. Make sure that this number is less than 85% of disk capacity. Disk access time increases exponentially beyond 85% capacity.
Avg. Disk Writes/Sec: The rate of write operations on the disk. Make sure that this number is less than 85% of the disk capacity. Disk access time increases exponentially beyond 85% capacity.
Database: Log Bytes Flushed/sec: The total number of log bytes flushed. A large value indicates heavy log activity in tempdb.
Database:Log Flush Waits/sec: The number of commits that are waiting on log flush. Although transactions do not wait for the log to be flushed in tempdb, a high number in this performance counter indicates and I/O bottleneck in the disk(s) associated with the log.
ADVANCED COUNTERS:
There are some SQL Server counters that you can watch to help determine if your SQL Server is experiencing any problems.
* SQL Server Buffer Mgr: Page Life Expectancy: To specify how long data pages are staying in the buffer. If this counter gets below 300 seconds, this is an indication that SQL Server could use more memory in order to boost performance.
* SQL Server Buffer Mgr: Lazy Writes/Sec: This counter tells us how many times a second that the Lazy Writer process is moving dirty pages from the buffer to disk in order to free up buffer space. Ideally, it should be close to zero which indicates that your SQL Server's buffer cache is plenty big and SQL Server doesn't have to free up dirty pages, instead waiting for this to occur during regular checkpoints. If this value is high, then a need for more memory is indicated.
* SQL Server Buffer Mgr: Checkpoint Pages/Sec: Higher Value of this counter indicates that the checkpoint process is running more often than it should, which more server resources. In those cases, we need to increase RAM to reduce how often the checkpoint occurs or decrease the "recovery interval" SQL Server.
* SQL Server Buffer Mgr: Page Life Expectancy: To monitor how long data pages are staying in the buffer. If the value is less than 300 seconds means, an indication that the SQL Server could use more memory to boost overall performance.
13.3 SQL 2018 School End
13.4 Overview
Useful to identify the Hardware resources, CPU Usage, Memory Usage, I/O Usage.
Run Command >> perfmon.msc
We can add counter by clicking + Button
13.5 CPU Related Counters
13.5.1 % Processor Time
13.5.2 % Privileged Time
13.5.3 Avg Queue Length
13.6 Memory Related Counters
13.6.1 System Memory
13.6.1.1 Available bytes
13.6.1.2 Pages/Sec
13.6.2 SQL Server Memory
We can check the amount of RAM allocated to sql server is sufficient or not.
13.6.2.1 Page Life Expectancy
13.6.2.2 Buffer Cache Hit ratio
13.6.2.3 Total server memory
13.6.2.4 Target Server Memory
13.6.2.5 Lazy Writes/Sec
13.6.2.6 Pages Reads / Sec & Pages writes/sec
13.7 Disk I/O Flow
Default size is 4KB. Page transfer is splitting into 2 parts.
run >> WMIC (Windows Management Instrumentation CommandLine)
Run >> diskmgmt.msc
13.7.1 Disk Time
13.7.2 Average Disk Queue Length
13.8 Load Related Counters
13.8.1 Batch Requests / Sec
13.8.2 Compilations /Sec
Subscribe to:
Comments (Atom)