Questions tagged [performance-tuning]

Improving the performance characteristics of a database application or system.

Filter by
Sorted by
Tagged with
0
votes
0answers
7 views

MariaDB game server config optimization

I have a FiveM game server that accesses a local database in MariaDB 10.3.16, this base is 400mb, 200mb being usable and the rest logs that are accessed only by external query when necessary. I have a ...
-1
votes
0answers
25 views

Different scan count and logical read, which one is best? [closed]

I have a SQL Server table, I created an exact copy of the table (including indexes, Pk, data, etc.) In one of the table I have created partition. I ran the same query in both the table and the IO ...
0
votes
1answer
33 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
66 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
60 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
26 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
49 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
60 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
116 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
44 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
212 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
82 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
128 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
78 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 ...
2
votes
0answers
76 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
vote
1answer
27 views

Optimizing a query that gets the opening balance

I have a query like this select org, `Balance` from oss_collection_history och2 where och2.`DateFrom` = (select min(`DateFrom`) from oss_collection_history och3 where och3.org = och2.org) It ...
0
votes
1answer
46 views

Best strategy to correctly index complex join query

This is the query running on MySQL 5.7 with INNODB: SELECT SQL_CALC_FOUND_ROWS calendar.id_calendar AS calendar_id, calendar.event_datetime AS calendar_event_datetime FROM users INNER ...
2
votes
1answer
55 views

Why should a SET TRANSACTION command show a long Duration when the CPU is low?

I got enlisted to troubleshoot a performance issue on a SQL Server 2016 Enterprise Edition at a customer site and have been handed a SQL Profiler trace captured during a period of "poor" performance. ...
0
votes
1answer
111 views

Same session ID(with same SQL text) but 3 different connection ids

I was listing out all sessions open in my sql server 2016 production server. I have issues with slow performance and i have been investigating it. I see that i have several same sessions(session_id) ...
2
votes
1answer
69 views

Breaking point for SQL server database based on connections from app to SQL server [closed]

We have been asked to confirm on some capacity test for one of our SQL server. Application team is building their new app servers with better infrastructure to connect to SQL server. Currently with ...
0
votes
0answers
93 views

SQL query tune to replace UNION - Oracle

SELECT FN_ENQDATA_STRING (col1) FROM tab1 WHERE tab1.col2 = tab2.col2 AND tab1.col3 = ? I also have tab2 which has same columns as tab1 and i need to call function for that too. What I can do is ( ...
0
votes
1answer
54 views

Can I drop an overlapping index if the larger index is unique

I have 2 nonclustered indexes: IX1 = [col1] IX2 = [col1],[col2] (unique). I want to drop the IX1 index as the optimizer can stop use IX2 for the first column and stop using when it has satisfied ...
2
votes
0answers
93 views

MySQL performance degradation with heavy inserts

I have an application that does heavy inserts into 5.7 MySQL database. The performance is really good for normal workloads, however, when there's an intense activity in the database the queries (...
0
votes
1answer
50 views

Performance issues on a MySQL database with 150 million rows

Current setup: A managed vps server InnoDB storage for big tables 10.0.36-MariaDB 18gb ram 8 x 2.40 GHz CPU. I have a pretty big MariaDB database table which is used for Business intelligence ...
1
vote
1answer
76 views

Alternative of JOIN with BETWEEN

I have two tables to JOIN based on BETWEEN condition. Table 1 is small is small around 1500 records and Table 2 is of 40 millions records. Table1 is having only one column with the datatype bigint ...
-1
votes
1answer
113 views

How to know needed UNDO_RETENTION for a specified query of long duration? [closed]

I'd like to know whether there is some way to know the undo_retention needed for a specified query with long duration that need keep the consistence. In other words, I'd like to know how long my ...
1
vote
0answers
38 views

updates in posgres taking a longer time

I have tables as follows: Source - 5 million records Target - 5 million + daily incremental records Both the source and target table have composite binary index on id and timestamp. Basically a ...
4
votes
2answers
117 views

Which Plan is Better? WHERE EXISTS Instead of INNER JOIN

Did a switch to a WHERE EXISTS in temp table (Object13 in the plans) from an INNER JOIN to said temp table in final select in procedure. Logical reads plummeted, but plan cost and memory ...
8
votes
2answers
750 views

Why is it not recommended to use RAID 5 for a log file? [duplicate]

While reading SQL Server Query Performance Tuning written by Grant Fritchey, I found it difficult to understand the following part: avoid RAID 5 for t-logs because, for every write request, RAID&...
0
votes
0answers
33 views

How do I troubleshoot FILESTREAM_WORKITEM_QUEUE waits in SQL Server 2014?

Env: SQL Server 2014 SP2 on Windows Server 2012 R2. I am seeing our top wait as FILESTREAM_WORKITEM_QUEUE, with average waits near 1s and over 5000 waits during a 2-hour period of time. I haven't ...
1
vote
1answer
83 views

Indexing Strategy for the query performance

I have a question on indexing strategy as i am trying to do indexing for a database. I understand the basic indexing strategy which are: Clustered index on PrimaryKey Add Non Clustered index for ...
1
vote
1answer
164 views

How To Fix High DTU Usage on Specific Query

I have a query that runs every few seconds on a Microsoft SQL Azure Database. It is from a web job that checks if there are actions that need to be done based on rows in a specific table (its a ...
0
votes
2answers
124 views

Worse query performance even after improving the query by making it to run in parallel mode

I have a SQL Query which gets generated from application using EFF. It uses a view and this view has joins to multiple tables in there. The query runs slow and when i looked at the execution plan,i ...
2
votes
1answer
73 views

Understanding when to remove Order By or Sort operator from Query/plan

While i was reading and per understanding one needs to avoid unwanted ORDER BY in SQL queries if matching index to support query has key columns sorted the same way. For below DB test schema-- ...
0
votes
0answers
56 views

Truncate causing poor performance on subsequent Select/Insert

Question Why is a Truncate-Table statement causing poor Insert/Select performance on subsequent calls, and is there any harm based on my use-case (described below) in leaving it as a heap? ...
0
votes
0answers
32 views

How to control dirty pages eviction threshold percentage in mongodb server settings?

I have a scenario in which I only insert documents to the MongoDB database. I'm aware that by default, once 5% of WiredTiger memory pages are dirty, MongoDB would start evicting the dirty pages. In ...
2
votes
0answers
104 views

How can i tune query without creating index

I am just starting to learn about tuning the queries and have found this very simple query which takes over 6 hours to run because of no supporting indexes. As its vendor product and they are not ...
-1
votes
1answer
88 views

Mysql keep high cpu usage and I cannot tune it down

I have an asp.net application which use Mysql as database server. In my application have some threading which will query mysql database. It always caused the Mysql database server CPU usage up to 99%. ...
0
votes
0answers
46 views

Comparing Performance impact between 2 Scalar UDF's

I have my developer team who uses the data from Change data capture from SQL Server to extract data and load to a different database system. To convert the LSN format within CDC they are using the ...
-1
votes
1answer
141 views

Query performance question

Using Demo from here to reproduce my issue with changes in table structure as below and modifying demo partition function to datetime CREATE TABLE [dbo].[DemoPartitionedTable]( [DemoID] [int] ...
1
vote
1answer
178 views

SQL Server : Memory pressure,Spiked user connections and CPU usage

I have the below sql server environment. SQL Server 2016 standard edition. 128 GB RAM 96 GB alllocated to SQL Server 8 tempdb's with 1 GB each. Have a major database with 2.5 TB size. Reporting also ...
0
votes
1answer
134 views

How to deal with different badges in Postgres Explain Visualizer?

I've been working with several DBMS during my career, but always Oracle/Sql Server/MySql. Now I have to deal with PostgreSQL and my first task is to tune several queries to speed them, as our DB is ...
0
votes
0answers
54 views

Partitions for table that hold only ids in PostgreSQL 9.6

I am working in query tuning and decided to go for partitioning my tables. There are 3 tables in the base query and only one table makes the query slower. I tried to create partitions but that table ...
-1
votes
2answers
77 views

Table with 80 foreign key

I have a table with 120 columns and most of them (around 80) are foreign key . We are refactor some of main table in our system . I was thinking of change this table to divide in small table because ...
0
votes
1answer
38 views

How to implement complex search functionality?

Below is my table structure: CREATE TABLE `post` ( `postId` bigint(20) NOT NULL AUTO_INCREMENT, `userId` mediumint(8) unsigned NOT NULL, `areaId` smallint(5) unsigned DEFAULT NULL, `postTitle`...
1
vote
0answers
53 views

high-water mark of number of sessions or connections

I was wondering if we can query/obtain the high-water mark of number of sessions or connections reached in a Postgres database. Is there a view or command that can provide this information. The ...
0
votes
0answers
15 views

IO scheduler for Oracle RAC on Linux

All the documentation I have seen says that Oracle recommends the deadline scheduler. This seems very counter-intuitive when using a mini-SAN (with persistent write caching) - in such a scenario, and ...
1
vote
1answer
376 views

Slow SQL query from app, fast from SQL Studio

I have a query that takes 30+ seconds when called from app, but takes <1sec when called directly from SQL SMS. Here is the query : SELECT T0.pk FROM products T0 WHERE (( ( T0.pk IN (...

1
2 3 4 5
10