Questions tagged [performance-tuning]
Improving the performance characteristics of a database application or system.
496
questions
3
votes
5answers
534 views
Is OPTION (RECOMPILE) used in production environments?
Is OPTION (RECOMPILE) used in production?
This option seems to get a lot of bad press. Is it deserved?
I have a DBA who, so far, refuses to entertain the idea of OPTION (RECOMPILE) within the meat of ...
2
votes
1answer
48 views
Do SQL OS Background Tasks Yield to Normal Tasks?
I have a SQL Server 2019 server that is running at about 22% CPU. I look at what's running and I see that about half of that 22% is coming from background tasks (i.e. the status is "background&...
0
votes
1answer
19 views
Why turning off condition_fanout_filter speed up query
bin/mysqld_safe --user=mysql --optimizer-switch=condition_fanout_filter=off --innodb-flush-method=O_DIRECT &
Query (from TPC-H query-19):
SELECT Sum(l_extendedprice * ( 1 - l_discount )) AS ...
1
vote
1answer
50 views
Stale Statistics and View performance on SQL Server 2016
I have a report query which behaves abnormally, but rebuilding the indexes/refreshing statistics seems to sort it momentarily.
So my doubt essentially is -
What could be the reason that even with ...
0
votes
1answer
34 views
Tuning query - reduce temporary and/or filesort use with join
My query runs in 60-90 seconds, I would like to see how can I improve it.
One table contain partitions and have tens of million rows each partition.
The other table contain 5-8 million rows between ...
0
votes
0answers
85 views
Improving the run time of a DW query
I have a user running query which is sometimes under 10 seconds while other time going over 30 minutes.
This is not a store proc , he is doing it manually from his machine to get data and later after ...
1
vote
0answers
36 views
Help me tune Mysql instance to support more users
I'm currently supporting, A Moodle 3.10 instance, running MySQL 8.0.20. This is running through an Azure Provider as a Standard DS11(2 vcpus, 14 GiB memory). Running as a LAMP stack server.
Currently, ...
0
votes
1answer
54 views
Execution plan push sort operator to avoid too many PK lookups
For a query like this:
select top 40 ColumnNotInIdexCausingPKLookup from table
where ColumnIncludedInIndex=A
order by ColumnIncludedInIndex,ColumnIncludedInIndex,ColumnIncludedInIndex
Any way to ...
7
votes
2answers
1k views
Do I understand indexes correctly?
I'm trying to wrap my head around the difference between various kinds of database indexes. I have created a little example for myself and I don't know whether I'm understanding everything correctly.
...
0
votes
0answers
82 views
Query performance drop after upgrade to MariaDB 10.5 (group_concat + join)
We have upgrade our database servers and are experiencing some performance issue.
Some complex queries, with lots of join and group_concat are 30% to 60% slower than on our old servers.
(Same query ...
0
votes
3answers
96 views
How to fine tune a huge problematic Heap Table? [closed]
Problem:
A huge table is causing to a critical performance issue on a very critically important production database (MS SQL Server 2016 SP1 Standard Edition).
Facts about the problematic Table :
Heap ...
1
vote
2answers
118 views
Help with tuning this UPDATE Query or possible re-write
I have below SQL query which runs on avg. 40 secs and need some guidance where can i improve it or possibly re-write:
Declare @status_D INT,
Declare @StatusIP INT,
Declare @Status_F INT,...
2
votes
0answers
41 views
SQL Server sys.query_store_runtime_stats max_dop field reporting wrong values?
I have been trying to use the Query Store in SQL Server as an easy way to identify queries running in parallel and the degree of parallelism. However, when I look at the max_dop field in sys....
-1
votes
2answers
55 views
Index usage across databases [closed]
I would like counseling as to what is the best way to get performance in the following scenario. This is on a SQL2016 server.
I have a newly created central database where data is replicated to from ...
0
votes
2answers
96 views
Performance benefits of multiple .ndf files
Is there a benefit to having multiple mdfs with SSDs? I assume that with multiple cores there is, since there is less contention?
1
vote
1answer
58 views
Postgresql Query estimated time vs actual time difference
We have a hosted Postgres 11 DB server to fetch medium sized dataset and ingest into another application.
"States" table is the largest one in the following query, which will fetch ~1.5 ...
1
vote
2answers
111 views
Best Way to Optimize MySQL / MariaDB for 250M Row Table on a 64GB 16 core Machine [closed]
I have an extremely large dataset (250M Rows in one table, 170G on disk) on a very powerful desktop computer that I'm utilizing to clean-up the data and do analysis on it.
As part of this analysis:
I ...
0
votes
2answers
42 views
In what range can you expect a cloud based Sql Server data reading in mb/s? [closed]
Say everything is optimized, queries are simple, indexes are good, etc.
Should a SQL (Azure) database where you spend about 150 to 300 euros per month easily handle data reading with about 250-500 mb ...
-1
votes
1answer
47 views
MYSQL optimization for Magento 2
Im running my magento 2 project on 5 core 10gb ram VM.
I did some benchmarks and i see that my DB queries take quite some time to execute. Traffic on this site is almost zero. Mysql is running on 5.7....
8
votes
2answers
220 views
Sqlite: Finding the next or previous element in a table consisting of integer tuples
I have a sqlite table called tuples defined like
create table tuples
(
a INTEGER not null,
b INTEGER not null,
c INTEGER not null,
d INTEGER not null,
primary key (a, b, c, d)
) ...
1
vote
1answer
70 views
Optimisation select with large IN and millions of records in result
I work with a table which consists of 1.5B records.
DB: RDS, PostgreSQL 12.4, 16GB RAM, 4vCPU
Schema:
CREATE TABLE public.trip (
id bigint NOT NULL,
cell_to character varying NOT NULL,
...
1
vote
2answers
78 views
Configure MySQL to minimize file locks
*** EDIT ***
8GB RAM
SHOW VARIABLES: https://pastebin.com/ANAiq1E2
SHOW GLOBAL STATUS: https://pastebin.com/pwWKRiie
*** END EDIT ***
To start off, I'm not a DBA, so I may have overlooked something ...
0
votes
1answer
50 views
Query data via username - pair - best performance
RDBMS: MariaDB 10.3
Let's say you have a table which stores specific information (info_1, info_2, etc.) according to a given pair of usernames; like so:
+----------+----------+----------+----------+---...
0
votes
0answers
22 views
Semantics and significance of a one time query execution, delete with Like vs =
I was required to delete the values of a certain table XX_choice_value, but this will be a delete run only once in two different stages. Raw query execution and nothing more, and then in my Pull-...
0
votes
2answers
121 views
Investigating RDS MySQL 8.0.16 stalls on write-heavy workload
I'm trying to track down some sporadic and difficult-to-reproduce issues with a MySQL 8.0.16 RDS instance connected to a web application with a write-heavy workload on a couple tables with large JSON ...
0
votes
1answer
27 views
set autotrace on is just estimate plan?
I've made a test:
SQL> set autotrace on; << autotrace on
SQL> select * from danilo.testepart where nome = 'danilo3';
ID NOME
---------- -------------------------------------------...
0
votes
1answer
22 views
How to set up indexes for dynamic filtering queries in Oracle
Given I have SOURCE_EVENT table in Oracle:
create table SOURCE_EVENT
(
ID NUMBER(19) not null
constraint PK_SOURCE_EVENT
primary ...
0
votes
2answers
56 views
How can i tune below proc either by changing query or adding index
I am having issues with one of the proc running very slow approx an hour:
Please pardon as I have to write entire code out of system to paste here. I will be editng the question with more details on ...
3
votes
4answers
128 views
SQL Server memory
It's regarding the SQL Server memory. When the server is running out of memory, we recommend adding additional memory to the hardware to overcome CPU utilization issues.
But before proceeding to the ...
0
votes
1answer
272 views
InfluxDB with Zabbix or Telefraf?
I'm considering the use of InfluxDB to store thousands of measurements per second from routers in a network. Each measurement is lightweight (between 10-20 bytes). Now, I'm wondering what is the ...
1
vote
0answers
125 views
Anomalous performance of mass INSERT in Oracle
Can you please help me diagnose, explain, and possibly solve the
problem of unstable peformance of mass insertions in Oracle?
I need periodically to load large amounts of data into an Oracle
database. ...
0
votes
2answers
134 views
PAGEIOLATCH wait and Page Life Expectancy very low
Training for SQL Server, has following scenario:
SQL Server on Azure VM, DS-series machine, it has 32 GB of RAM
Server has below performance conditions:
- very high PAGELATCH_IO waits
- average Page ...
0
votes
2answers
43 views
Are there better alternatives rathet than joining a table twice for having one record for a specific column in the output?
I have two tables with below structures:
create table TABLE_LC
( lc_num NUMBER,
brn_code NUMBER,
brn_role NUMBER)
create table BRN
( brn_code NUMBER,
brn_name VARCHAR2(50))
Some example data ...
0
votes
1answer
775 views
PostgreSQL LWLock: lock_manager issue
In our application we need to insert a lot of historical data that will be consumed later by some mobile devices.
These data is organized by a numeric incremental attribute that the device will use to ...
1
vote
1answer
57 views
how to speed up / fix this Ubuntu Apache MySQL Server Configuration?
I got new to the project, my predecessor is far gone and I'm left on my own.
Currently the MySQL-database seems super slow and after checking some variables and values I cannot identify the problem on ...
0
votes
0answers
33 views
Slow on searching duplicate records in millions of records
I've below three tables
CREATE TABLE [dbo].[RegisteredUser] (
[ID] [int] IDENTITY(1,1) NOT NULL,
[UserID] [int] NOT NULL,
[UserCode] char(9) NOT NULL,
[Email] [varbinary](5000) NULL,
...
0
votes
0answers
19 views
Sampled rows is very less for auto update stats in SQL Server 2016 [duplicate]
I have SQL Server 2016. One of the table is having 9.24 million records. One of the stats (no filter on stats) on it is getting auto updated by SQL Server with sampling rate of 1 % (120023 rows).
As ...
0
votes
0answers
9 views
Postgres tracking count of recent items in count table or by selecting count of recent records from history table
Which would generally be more efficient, or does it not matter?
Option 1 is incrementing a count in a table that stores counts by the unique id, and resetting the count to zero when enough time has ...
-2
votes
1answer
30 views
What are some good SQL Server Performance Metrics from System Views?
I am working on a project where I need to collect as many SQL performance metrics as possible, all from system views.
I currently have collected, and in parenthesis the system view from which I have ...
0
votes
1answer
47 views
Mysql 8 - Count(*) - performance issue
I have this table with 2 million records
CREATE TABLE `my_table` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`ID_USER` int(11) DEFAULT NULL,
`BODY` mediumtext CHARACTER SET utf8 COLLATE ...
0
votes
0answers
53 views
Index usage for database: where are the sys tables located
I have seen a couple of scripts that query some sys.someName tables to find usage of indexes in a database. My guess is that these tables are tied to a database e.g. there is a sys.indexes table in ...
-2
votes
1answer
53 views
Is Buffer Pool Extension (BPE) needed if my database is already on SSD?
Here is my simplified scenario, let's say I have one (and only one) database of 500GB on SQL Server 2016 EE, this database is on SSD drives already (both data and log files), and my server has 64GB ...
1
vote
0answers
175 views
Sql server partitioned table: what happens when an update of a record changes the destination partition?
I my company we are facing performance issues on a big Sql server DB (running on Azure, total size > 150GB).
We have one big table (1,5GB, 3 millions records) that has several quite slow "...
3
votes
3answers
457 views
Need help to understand tuning up a slow SQL server query [closed]
For one of our databases the query like below is extremely slow.
Due to security reasons I am not able to share the actual query or plan but just to give an idea how query is written is below
SELECT [...
0
votes
1answer
47 views
MySQL Clustered Index or Non Clustered index? (Database Design)
I know this might be a basic thing for you guys in this corner of SE but I'm having some trouble figuring out the best option for designing my DB.
I am creating a custom app where the user will be ...
1
vote
2answers
24 views
MySQL index size doesn't include all values
I have a table with ~1.4M rows and an id field as primary key and not all primary keys are included in index:
mysql> SELECT DISTINCT(COUNT(id)) FROM my_table;
+-------------+
| (COUNT(id)) |
+------...
0
votes
0answers
49 views
Slow query using SQL Server difference (soundex) function
We've got a nasty query that is always doing a clustered index scan even if I add in a non-clustered index with the Forename and Surname columns.
Query takes 2-3 seconds on a 2 million row table and ...
0
votes
1answer
2k views
MongoDB - Replication Oplog Window has gone below 1 hours
I'm working with MongoDB Atlas and I have a cluster of 3 with M30 and 100gb of storage.
My current usecase is the following:
- An user dispatches one search in my platform
- The platform dispatches ...
-3
votes
2answers
307 views
Why does IO Statistics show a lot of LOB Logical Reads on the Worktable when none of the tables in my query use any LOB datatype fields?
I have a procedure that runs a query dynamically across a few hundred databases that all have the same exact schema and aggregates the results to a temp table.
The query only involves 3 tables (each ...
1
vote
2answers
62 views
Very large table makes the application performance too slow
We have an old application that has problems with performance. I narrow it down to few spots in the system that definitely require attention.
I found one table referenced in this app with 1,332,730,...
