Questions tagged [performance-tuning]

Improving the performance characteristics of a database application or system.

Filter by
Sorted by
Tagged with
1
vote
1answer
31 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
18 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
7 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
22 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
29 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
43 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
36 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 ...
2
votes
0answers
31 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
435 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
35 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
22 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
30 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
494 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
105 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
131 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
113 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
125 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
46 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
226 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
169 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
141 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
113 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
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
239 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
106 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
55 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
140 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
54 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
93 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
160 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
118 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
773 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
43 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
88 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
265 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
165 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
75 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
41 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
2 3 4 5
10