Questions tagged [performance-tuning]

Improving the performance characteristics of a database application or system.

Filter by
Sorted by
Tagged with
0
votes
2answers
111 views

An error occurred while updating the entries. See the inner exception for details.; [INNER] Execution Timeout Expired [closed]

One of our production SQL Servers database is performing slow. On the application side, they are experiencing this error: An error occurred while updating the entries. See the inner exception for ...
0
votes
2answers
16 views

How to check the source of analysis task in Oracle Performance Analyzer?

We have provided sql tuning set as input to the analysis task of performance analyzer variable l_task_id VARCHAR2(64); exec :l_task_id:=dbms_sqlpa.create_analysis_task( sqlset_name => '<...
-1
votes
1answer
47 views

Oracle Execution Plan - Outline Question

I'm spending the last few days trying to learn some tips/tricks on tuning an Oracle query being run in Crystal Report. Obviously it has some performance issues. I suspect there are some deficiencies ...
0
votes
2answers
50 views

In PostgreSQL, how can I make Bitmap Index Scan parallelized?

I want to improve the performance of an SQL statement. I am on version 13. Here are the sample codes and the query I am interested in. drop table ords; CREATE TABLE ords ( ORD_ID INT NOT NULL, CUST_ID ...
1
vote
0answers
46 views

Query performance in describing a race's relative finishing positions

Is there a faster, and more parsimonious way to do this? For each race (i.e. 1, 2, and 3), there are many race entries X (horses), some details including it's finishing position, and in the same row ...
0
votes
1answer
31 views

Sql Server consuming lot of memory Creating Bottleneck for my web application Hosted in the same server

HI I have a server with 64 GB Ram and I have assigned around 52GB to the SQl Server itself. However Even this has effected the performance of the server and the web application hosted in the same ...
1
vote
3answers
138 views

Should the T and L phase of a well tuned ETL report process take the same amount of time regardless of the time bucket width and data size?

For an ETL reporting system, is it normal for the total execution time for a pull of 15 minutes with no data to be similar to a 24 hour pull with data? I had expected that the total time for ETL when ...
0
votes
0answers
11 views

SSRS High IOPS and Slowdowns

Please let me preface by saying that I understand poorly formed reports can cause poor SSRS performance and I have people looking at that issue separately. I have an SSRS server that is seeing ...
2
votes
2answers
127 views

MySQL 8.0 Tuning Advice Needed - Slow Locking & Various Issues

I recently upgraded my database to MySQL 8.0 and I'm using phpmyadmin to get status information. I also upgraded my virtual server to 4GB RAM and 2 vCPUs, intended to be a MySQL-only server for my ...
-2
votes
1answer
35 views

Database scoped configuration applicable to Secondary

I saw some new database scoped configurations like MAXDOP and legacy cardinality for secondary options and have few questions or doubts i am not able to understand MAXDOP- How do i setup MAXDOP, say ...
1
vote
0answers
46 views

Why performance of this query deteriorates over time?

I have a 1 million row table in oracle 19c. I am running this query. Table has an index on ID column, stats are up to date. select <columns> from (select <columns> from <tableA> ...
6
votes
5answers
2k 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, is not a fan of the idea of OPTION (RECOMPILE) within the meat of ...
2
votes
1answer
50 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
32 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
56 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
39 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
56 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
55 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
248 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
103 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
120 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
46 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
60 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
152 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
84 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
179 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
43 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
51 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
224 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
72 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
125 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
23 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
174 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
29 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
23 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
133 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
476 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
160 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
45 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
1k 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
102 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
20 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
13 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
42 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 ...

1
2 3 4 5
11