Questions tagged [performance-tuning]

Improving the performance characteristics of a database application or system.

Filter by
Sorted by
Tagged with
1
vote
2answers
36 views

DBCC shrink file history

I ran shrink file(.mdf) through GUI by selecting Task -> Shrink -> Files and selected second option i.e Reorganize pages before releasing unused space. Now, I would like to know the history of ...
1
vote
0answers
109 views

How to determine the Tipping Point of which Physical Join Operator gets used to process a query?

I've been noticing some strange occurrences lately on my development SQL Server instance where the Nested Loops Physical Join Operator is used when a Hash Join would be much more efficient, within a ...
3
votes
1answer
90 views

sp_HumanEvents @event_type = N'blocking' and logging job help needed

Those who use excellent sp_HumanEvents, maybe the author himself, please help me understand something I am missing (cause of my stupidity). #1 When monitoring blocking, blocked process threshold must ...
0
votes
1answer
44 views

Could hardware RAM disks (with battery) have any benefit in database servers?

For database servers with many writes, I am wondering why can't these ramdisk/ramdrive be a solution to avoid wearing out SSDs and to avoid losing data if too much is kept in normal ram. Can these ...
0
votes
0answers
25 views

Sql id and plan creation pattern

Oracle database. I've got this customer who is complaining about some ODI application taking longer than before (meh!). Currently, it is not clear what the app does (I'm asking..) but he told me that ...
1
vote
2answers
87 views

I am burning trough NVME SSDs, 3x 60GB MySQL databases (web crawler)

I made a web crawler that is split on 3 computers, each of them makes many db queries, I think around 200 3000-4000 queries per second (each server) non-stop, with short spikes to 12000-14000. I am ...
1
vote
1answer
38 views

Identify the line of a procedure of a spid/session

In SQL Server 2017, how can I identify or pinpoint at which line of a stored procedure a spid (session) is currently at and what the execution plan of the current statement looks like?
0
votes
1answer
25 views

What dataset(s) is Marcus Winand using in his book "SQL Performance Explained"?

I am currently reading SQL Perfrmance Explained by Marcus Winand. I would like to follow along with the queries and examples he used to explain database tuning. It would greatly allow me to understand ...
0
votes
2answers
76 views

MySQL 8.0 Hanging and Disconnecting At Random Times - Slow Downs - Need Help

Yesterday, I noticed in my web logs that most traffic disappeared for 2 hours. I decided to do a routine Ubuntu 20.04 upgrade yesterday that I haven't done in 1-2 months, and it seems like a lot of ...
0
votes
1answer
40 views

Oracle. Old Partitions having Stale Statistics

Our production database tables are range partitioned based on date. Some of the tables and indexes have hundreds of partitions. And some of partitions which are old ( based upon partition key ) have ...
0
votes
2answers
191 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
19 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
48 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
127 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
48 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
33 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
150 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
12 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
368 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
37 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
68 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
65 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
45 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
87 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
78 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
56 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
370 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
124 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
51 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
74 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
197 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
104 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
292 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
45 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
79 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
230 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
75 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
171 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
51 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
220 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
27 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
138 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 ...

1
2 3 4 5
11