Questions tagged [performance-tuning]
Improving the performance characteristics of a database application or system.
660
questions
0
votes
1
answer
67
views
Windows Power Plans for SQL Server
Is the High Performance power plan in Windows Server still relevant in SQL Server setups on modern hardware/virtualization? Searching for articles from my usual trusted sources brought up nothing more ...
0
votes
1
answer
74
views
PostgreSQL : is max_worker_processes cpu bound?
I have seen a lot of PostgreSQL performance tuning blogs and companies like EDB suggesting putting max_worker_processes setting for PostgreSQL to the number of CPUs in the machine.
We are using a ...
- 13
-1
votes
0
answers
35
views
Page Life Expectancy Drops in SQL Server
I have a server with 512GB of RAM and a dual-CPU NUMA architecture. It's configured for "Always On" distributed availability, but I've been experiencing intermittent issues with a low Page ...
0
votes
1
answer
101
views
Is this Parameter Sniffing?
I have a particular stored procedure that takes 1 parameter.
This proc is executed once a day with the same parameter value everyday. The row count is also the same with each run of the proc.
Day 1 - ...
- 319
0
votes
3
answers
136
views
Partitioning an existing table yearly in SQL Server with huge data
I have a logs table for my application which is almost 3TB data. I want to partition this table yearly.
Below is the scripts for Table, View, and indexes script:
GO
CREATE TABLE [dbo].[AuditLogs](
...
1
vote
2
answers
138
views
SQL Server database with 100 terabyte of data
We have been assigned a new application where the vendor is expecting the database to grow to around 100 TB. They have mentioned SQL Server Standard Edition is compatible.
My question is whether SQL ...
- 1,039
-4
votes
1
answer
52
views
Please I received this error on a scrub process “scrub failed, no running scrub jobs”
Please help it is urgent . The database is also in replication enter code here
- 1
1
vote
1
answer
60
views
Almost identical MySQL server totally different performance
I have two servers which differ in mysql version
Server 1 runs with mysql 5.7 2 cores Intel(R) Xeon(R) Gold 6248R CPU @ 3.00GHz and 6GB RAM
Server 2 runs with mysql 8.0 4cores CPU Intel(R) Xeon(R) ...
- 111
1
vote
3
answers
100
views
SQL Server Query Performance: Index Seek in SSMS but Index Scan in Java Application
I'm facing an issue with SQL Server query performance and I'm hoping to get some insights from you all.
Problem Statement:
I have a SQL query that performs well (~6 ms) when executed directly in SQL ...
- 113
0
votes
2
answers
74
views
any way to keep track of all queries and their number per hour or per day in SQL server
in SQL server, i would like to keep track of all queries and their count , based on specific time frame and keep thsir historiacl record.
like every hour , which query ran for how many times.
We have ...
- 121
0
votes
2
answers
67
views
Performance tuning of SQL Server query, with LEFT OUTER JOIN vs INNER JOIN
In my scenario i need to optimize a stored procedure, that i used to import data into DB from an exchange DB.
I am relly puzzled because, the solution with INNER JOIN is lower than the LEFT OUTER JOIN ...
- 358
0
votes
1
answer
58
views
PostgreSQL Processor Queue Length
Is there a similar metric to MSSQL's Processor Queue Length in PostgreSQL? I had a situation recently where a system ran several concurrent long-running queries that essentially blocked all other ...
- 105
0
votes
0
answers
19
views
MongoDB: Complicate aggregation operation speed optimization
We want to calculate the average weight reduced and total weight loss users according to scales collection. The following steps describe our aggregation:
Firstly, we calculate the average weight for ...
- 101
0
votes
2
answers
36
views
Can many table names having the same prefix slow down a database?
I understand (maybe wrongly) that SQL databases often use associative arrays based on B-trees and that looking up entries in these arrays entails binary search that lexicographically compares a key ...
- 101
1
vote
1
answer
102
views
Slower than expected query performance on Postgres
I just moved a FrontEnd web from querying a small Redshift Cluster to Postgres, looking for, primarily getting rid of Redshift but mostly to get better performance and user experience from a reporting ...
- 113
0
votes
1
answer
526
views
Tuning MySQL 8.0.33 - increasing innodb_buffer_pool_size not working?
I'm trying to tune a MySQL 8.0.33 on Ubuntu 22.04 (32G RAM, 8 cores). (The same machine is also a web server, with 4 apache/php-fpm virtual hosts running).
I'm not sure if this is the right thing to ...
- 1
0
votes
1
answer
59
views
Tune SQL Server 2019 for receive many small queries
I am using SQL Server SE 2019, on a Windows Server 2019 (with SSD drive) that run IIS 10. On top of IIS 10 run an ASP.Net application, that has to be considered AS IS (I can't modify it).
Lately tourn ...
- 358
2
votes
1
answer
351
views
Postgres-15 response time jumps up from 0.5 ms to 100-200 ms once in a while (~ 0.5% of requests are affected)
I have a simple database with one table of ~5 millions rows, another table with ~10 millions rows, couple of other tables have 10th of thousands of rows.
There is a constant load without any spikes at ...
- 213
0
votes
0
answers
54
views
Utilizing GIN Indexes Efficiently for Queries Without Type Casting in Postgres
We have an use case where we need to perform cascading deletions across multiple tables in our database. For this purpose, I have found that using GIN indexes as suggested in this answer is quite ...
- 11
3
votes
2
answers
291
views
Merge join asks for seemingly unnecessary sort before joining
I use one table to update another table. Both the tables have a clustered index where the first 4 columns are the same type of data.
CREATE UNIQUE CLUSTERED INDEX [...
- 65
0
votes
0
answers
375
views
Sometimes simple queries are slow, high buffer IO wait times the cause?
I have a Azure SQL database, Premium P2: 250 DTUs, using 250GB of storage.
Sometimes even simple queries runs slow, for example by getting a single row from a simple 300mb table with with 500k rows, ...
- 479
0
votes
1
answer
40
views
Help with SQL Schedulers Offline
We are in the middle of a migration and moving from SQL 2012 Enterprise to SQL 2019 Standard (due to licensing costs) and I have recently encountered this issue using sp_blitz.
With the results ...
1
vote
0
answers
52
views
Getting a SORT operator when number of rows being updated is above a threshold [duplicate]
I'm working a table that has VARCHAR(18) as the PK. I'm putting this here in case it's significant.
We have a SP that runs as part of an ETL that updates X rows each time.
I've noticed that when the ...
- 441
3
votes
1
answer
141
views
SQL server chooses hash match over merge join but the input fields to join should be sorted
I have a simple query that joins two tables on the field PRODID. For some reason, SQL Server opts to use a hash match to join this, but I believe it should choose a merge join, as that field is part ...
- 65
1
vote
1
answer
102
views
Rewriting a DELETE statement to reduce pagelatch_ex waits
I have the following TSQL statement, which is often executed in multiple sessions in parallel and leads to serious pagelatch_ex waits:
DELETE dbo.huge_table1
WHERE c1 = 0
AND c3 IN (SELECT v ...
- 1,159
1
vote
2
answers
74
views
How to interpret the stats of index usage? [closed]
Question: How do we interpret the stats shown in the diagram below? The stats seem to indicate bad index designing, how can it be improved. I realize this a sort of broad question since it depends on ...
- 289
3
votes
2
answers
366
views
How to gather Index usage information
Following query correctly outputs number of seeks, scans, etc. I have question on understanding some rows in the output. In some output rows of the query, the index name is shown as NULL and the ...
- 289
1
vote
1
answer
208
views
Database performance, high IOPs and latency (SQL Sevrer)
I know that if we separate databases on separate drive configured of separate disk group (SAN LUN), it helps to improve disk IOPs.
Does separation of databases to different drives help to improve IO ...
- 1,931
1
vote
1
answer
33
views
Could cloning / imaging SSD cause MySQL data to be more fragmented?
On a MySQL server (InnoDB tables) with /var/lib/mysql around 350 GB, I cloned the nvme SSD (using Macrium Reflect) in order to compare query speeds with a faster SSD. But queries are around 4 times ...
- 195
0
votes
0
answers
325
views
MySQLTuner vs cPanel SQL recommendations are different / contradictory
I've used WHM's (cPanel) to optimize the mysql configuration on my server and it suggested making the following changes which I made
innodb_buffer_pool_size=3328M
key_buffer_size=55M
...
- 51
0
votes
1
answer
313
views
Upgrade Aurora from 5.6 to 5.7.mysql_aurora.2.11.1 getting tons of deadlocks and lock wait timeouts, maybe 20-30x what they were before the upgrade
Our application logs are showing an excessive amount of lock wait times, and deadlocks for queries that have not had issues previously after upgrading from 5.6 to 5.7 MySQL in AWS Aurora. Are there ...
0
votes
0
answers
27
views
Cache plan removal
I'm having an application which generates SQL queries from EF library. When looking into the query plan cache on the test server I see that plans are getting frequently removed from there. Plan ...
- 1
3
votes
1
answer
202
views
Stored Procedure Tuning Help
Actual plan: https://www.brentozar.com/pastetheplan/?id=SJviG_y1h
I'm looking for advice on where to start with tuning the above stored procedure. We need it to complete in as near to 1 second as ...
- 31
0
votes
0
answers
209
views
When to use sp_updatestats and when to use sp_updatestats with the resample option?
I am confused about the usage of RESAMPLE option and when to use it.
We had problems with a customer DB. One/some queries took longer as expected and the CPU usage on the machine was higher than in ...
- 230
-1
votes
2
answers
37
views
Is a performance improvement for this query with subselect possible?
I have the following table (simplified non-sense example):
table fruits:
+-----+---------+-------------+
| id | fruit | customer_id |
+-----+---------+-------------+
| 1 | apple | 1 |
...
- 111
0
votes
1
answer
907
views
Does a select within a where clause affect performance?
I know that we should avoid using functions in the where clause of a query wherever possible since the function will run for every row to return the proper results. However, does the same principle ...
- 155
0
votes
1
answer
68
views
Help me in optimising the query which takes almost 50 seconds
Please help me optimise the following query running on MySQL 8.0.28 Community Edition.
explain select id,
created_at,
data,
deleted_at,
entity_id,
...
1
vote
1
answer
522
views
Why is MariaDB innodb_flush_method = O_DIRECT so much slower than fsync for SELECT count(*) as count? [closed]
I have a MariaDB 10.5.18 server with 64 GB of RAM running on an enterprise grade SSD.
I'm using a fairly generic my.cnf to try to figure out why innodb_flush_method O_DIRECT is so much slower than ...
- 11
0
votes
2
answers
85
views
About "wal_buffers" of Postgresql-11 with "synchronous_commit=off"
Last days I tuned my PG11 for a lots of huge writing transactions, and got a amazing result.
The only warry in my mind is that whether there is more risk of lossing data when I turned off ...
- 315
0
votes
1
answer
158
views
Does it make sense to enlarge "shared_buffers" for writing Postgresql?
I'm using Postgresql-11, with a traditional HDD as the physical storing device.
I need to optimize the performance for writing.
I have changed the wal_buffers from -1 to 512MB, that means that ...
- 315
0
votes
0
answers
77
views
Which is faster between regex and string length function, in searching string of different length?
I have a table named table1 having 2 columns named as user_id and col1. The table contains 5 million rows. This column col1 contains 3 types of values: first ending with char 'x' and string length 13; ...
- 21
0
votes
0
answers
47
views
Optimize SQL Query
Im having trouble with the performance of this query, especificallly with the case statements. How would yo optimize it?
SELECT a.t,
a.c,
a.id,
a.id_r,
a.s,
a.n,
a.n_s,
a.c,
a.co,
a....
- 1
0
votes
0
answers
108
views
Does Oracle forces its own "estimate percent" even when using "estimate_percent" parameter in dbms_stats?
For example, why even using estimate_percent=>2 (2%) parameter, it still show sample size = num_rows (100%)?
SQL> exec dbms_stats.gather_table_stats(ownname=>'TESTE',TABNAME=>'T1',...
- 630
1
vote
1
answer
562
views
Calculating MySQL server requirements
I almost didn't post on here as I know this is a somewhat open ended question and I'm in danger of being berated but I've been doing a lot of reading about various things and still don't really have a ...
- 605
2
votes
0
answers
67
views
Mysql high Created_tmp_tables
I am seeing some high Created_tmp_tables and I just can't seem to figure out where they are coming from. Those rates apparently cause high Opened_tables aswell. PhpMyAdmin is complaining that ...
- 21
-2
votes
2
answers
111
views
index scan instead of index seek on columns with OR operator [closed]
I am trying to tune following query
select
-
-
-
FROM
Table1.Column1 = 'I'
and
(
Table1.column2 in (1234)
OR
Table2.column2 in (1234)
)
I am trying to improve query execution time but it takes ...
- 117
2
votes
1
answer
256
views
Why would INSERT, REPLACE or UPDATE statements sometimes take over 1 second to execute?
I have a very good server (great CPU, huge RAM, NVMe) with a huge InnoDB buffer pool that's not full yet.
The website is very write-intensive, but it runs fast enough (SELECT queries well optimized, ...
- 809
0
votes
1
answer
519
views
How should I configure my postgresql to create an Index for a big table (655GB) as fast as possible?
I'm importing a big dataset of medication usage (14 years of public health system data from Brazil) for a data science project.
When I first started, I created one of the tables with all the indexes (...
- 103
0
votes
0
answers
22
views
Local variables and their impact on execution plans in SQL Server Stored Procedure [duplicate]
If a parameter that is passed to stored procedure is assigned to a local variable and the local variable is used inside the stored procedure logic, will that affect the execution plan in any way? Is ...
- 437
0
votes
0
answers
22
views
P&T: which approach to take with app constantly creating new tables
A customer is complaining about low performance.
No cpu or memory bottlenecks observed on the system.
The problem is that their app is constantly creating new tables that are used for a while, then ...
- 9