Questions tagged [performance-tuning]

Improving the performance characteristics of a database application or system.

Filter by
Sorted by
Tagged with
2
votes
0answers
37 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
53 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
70 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
40 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
71 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
36 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 ...
0
votes
0answers
26 views

MYSQL optimization for Magento 2

Im running my magento 2 project on 3 core 8gb 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
208 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
68 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
51 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
85 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
23 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
21 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
122 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
97 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
123 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
101 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
41 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
275 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
37 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
8 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
25 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
41 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
46 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
44 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
91 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
443 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
38 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
23 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
37 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
1k 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
211 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
61 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,...
0
votes
1answer
29 views

Which query has better performance? [closed]

I have 3 tables as follow: class id:bigint(PK) name:varchar (unique) principal: id: bigint(PK) pid:bigint role:boolean uniqueConstraint(pid, role) entry: cid: (FK, reference class) ...
2
votes
2answers
277 views

Increasing WAL Segment Size?

Is it possible to increase the WAL segment size in an existing PostgreSQL 11 database? What are the disadvantages of having larger WAL segment sizes? The reason for wanting to increase it from 16 MB ...
2
votes
1answer
250 views

ZFS Tunings for PostgreSQL

What are the recommended ZFS tunings for PostgreSQL 11 (with TimescaleDB 1.6 extension) running on striped mirrored vdevs of SATA3 SSDs? I am considering: ashift=12 atime=off relatime=on (or off?) ...
0
votes
3answers
135 views

When is it ok to use an Index Hint on a query in production code?

I've recently been working with decently sized tables (between 100s of million of rows to 10s of billions), and have ran into a few cases where I was forced into a corner to use query hints, which I ...
1
vote
1answer
74 views

Batch - Update vs Insert then Delete [closed]

There is a plan to create a new ETL job to do a particular task. It will retrieve data from database1, and from that data, it would update equivalent records in database2. Both database1 and database2 ...
6
votes
2answers
239 views

Disable top (n) Sort Optimization for a specific view

I have a a view with a complex logic and three levels of deepness (nested views). Because of the complexity I can't paste the execution plan. As the purpose of the view is to provide some business ...
1
vote
2answers
291 views

Mariadb Delete with Join query stuck sending data

I am using Mariadb (10.4.10-MariaDB-log) with Galera Cluster (3 Nodes). I am running a large data queries with joins , but No idea why delete with join is not working. DROP TEMPORARY TABLE IF ...
-2
votes
2answers
77 views

T-SQL Query performance

We are using SSIS package to execute the following statement. SQL statement delete from ABC OUTPUT DELETED.[ABC_Id], 5 into XYZ (OId, OCode) where ([ABC_Id] = ?...
1
vote
1answer
157 views

Funky cardinality estimate skews, high memory grants and missing statistics

I have a select statement within a procedure that is causing me some grief at the moment. The query estimates about 100 million rows and that in turn grants large chunks of memory (about 8 gigs per ...
0
votes
1answer
154 views

in what all scenarios SQL Server update statistics

I know SQL Server update stats when 20% of data is changed in a table. It keeps tracks of it by using modification counter which we can see in [modification_counter] column of sys.stats table. But I ...
0
votes
0answers
28 views

SQL Access Advisor with big Select query

I need to get recommendations from SQL Access Advisor for big Select query (> 10000 simbols). I use DBMS_Advisor, but one procedure i need support only 4000 symbols. How can I use this package with ...
3
votes
0answers
77 views

SQL Server, what is the optimal Processor & Core configuration for VMs? [duplicate]

In my day job I deal with alot of SQL Servers...and lately I've had a rash of clients running 16 cores but configured as 1 core per processor and their SQL Standard edition runs like a dog, as ...

1
2 3 4 5
10