Questions tagged [performance-tuning]

Improving the performance characteristics of a database application or system.

Filter by
Sorted by
Tagged with
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 ...
Wayne Cochran's user avatar
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 ...
Basil Titus's user avatar
-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 ...
Mohammad Safyar's user avatar
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 - ...
Sicilian-Najdorf's user avatar
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]( ...
Mohd Waseem's user avatar
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 ...
SQL_NoExpert's user avatar
  • 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
aee's user avatar
  • 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) ...
y.y's user avatar
  • 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 ...
Vipin's user avatar
  • 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 ...
Farhan's user avatar
  • 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 ...
Skary's user avatar
  • 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 ...
Nick Molyneux's user avatar
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 ...
Hhry's user avatar
  • 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 ...
user570286's user avatar
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 ...
Alejandro's user avatar
  • 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 ...
Cintya 's user avatar
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 ...
Skary's user avatar
  • 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 ...
Viktor Vsk's user avatar
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 ...
Sri S's user avatar
  • 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 [...
KHP's user avatar
  • 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, ...
viblo's user avatar
  • 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 ...
James Scott's user avatar
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 ...
Geezer's user avatar
  • 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 ...
KHP's user avatar
  • 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 ...
Fajela Tajkiya's user avatar
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 ...
nam's user avatar
  • 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 ...
nam's user avatar
  • 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 ...
Rajesh Ranjan's user avatar
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 ...
adrianTNT's user avatar
  • 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 ...
Ryflex's user avatar
  • 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 ...
user2966697's user avatar
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 ...
Kokonen's user avatar
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 ...
AW_DBA's user avatar
  • 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 ...
xMRi's user avatar
  • 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 | ...
Phantom's user avatar
  • 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 ...
Paradox's user avatar
  • 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, ...
Avinash Pawar's user avatar
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 ...
Deefo22's user avatar
  • 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 ...
Leon's user avatar
  • 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 ...
Leon's user avatar
  • 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; ...
abhishek's user avatar
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....
KodaCode's user avatar
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',...
Potter's user avatar
  • 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 ...
webnoob's user avatar
  • 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 ...
Jenny's user avatar
  • 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 ...
SqlDBA's user avatar
  • 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, ...
Nuno's user avatar
  • 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 (...
Jaejatae's user avatar
  • 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 ...
lifeisajourney's user avatar
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 ...
trustno1's user avatar

1
2 3 4 5
14