Questions tagged [performance-tuning]
The performance-tuning tag has no usage guidance.
447
questions
2
votes
0answers
68 views
SQL Server, what is the optimal Processor & Core configuration for VMs?
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
23 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 ...
-1
votes
0answers
47 views
Huge difference in logical reads for the same query and same data in different executions [closed]
When capturing long running queries in SQL Server (with XEvents), I have realized that some of them perform a huge number logical reads (millions). However, when I run the exact same query a few ...
0
votes
1answer
39 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
52 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
43 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
67 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
49 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
66 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
45 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
69 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
51 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
114 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
687 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
16 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
72 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
67 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
1answer
76 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
66 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
52 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
25 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
100 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
50 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
44 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
136 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] ...
0
votes
0answers
104 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
98 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
52 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
34 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
41 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
14 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
261 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 (...
-2
votes
1answer
35 views
How does 'Top SQL - By Application' values from SQL sentry correlates
I have run a performance simulation on my application server and pull the sentry report for my analysis. And While going through the top sql -by application, I found difficult to correlates the values ...
0
votes
0answers
65 views
MariaDB Inner join is slow
Here is my SQL (with explain):
EXPLAIN
SELECT * FROM
GBI gbi
INNER JOIN VF_KRED kred ON
gbi.vendor = kred.vendor;
Giving below the outcome:
Now, the select SQL takes ~10 seconds to produce the ...
16
votes
4answers
3k views
SQL Server has encountered occurences of I/O requests taking longer than 15 seconds
On Production SQL Server, we have following config:
3 Dell PowerEdge R630 servers, combined into Availability Group
All 3 are connected to single Dell SAN storage unit which is a RAID array
From ...
0
votes
2answers
143 views
same query gets slower after other queries get called (using a lot of prepared statements inside stored procedures)
I have a query that, when i run it first after i restart mysql, takes 2 seconds, but when I run a sequence of other queries before it (this query belongs to a procedure between other procedures) ...
0
votes
0answers
40 views
SQL Server inconsistently reporting missing indexes
I have a stored procedure that is not performing well and I am trying to tune it. I am confused by what I see in SSMS and the Database Tuning Engine Advisor.
In SSMS I have 'Include Actual ...
1
vote
1answer
55 views
What is Page Life Expectancy precisely?
What is the value returned by Page Life Expectancy, precisely?
The use of the word "will" in this documentation
Indicates the number of seconds a page will stay in the buffer pool without ...
1
vote
1answer
84 views
Is it possible to move mongodb indexes to another file system?
Is it possible to separate MongoDB files location so the indexes are on another file system?
In the example below, I have database with 1.5TB of data + indexes of 330GB.
I have a very fast storage ...
1
vote
2answers
979 views
How to determine if it is necessary to increase the sga and the pga in Oracle database
How to determine if it is necessary to increase the sga and the pga in a database oracle 12c, is there any procedure, any specific view where can I be guided that indicates to me that it is necessary ...
1
vote
0answers
33 views
Different query plans for same In line Function [duplicate]
I am facing a potential parameter sniffing issue with an In Line function in sql server 2008.
Basically Given the below three queries (same function):
First Query:
DECLARE @SQL NVARCHAR(1000)
...
1
vote
2answers
51 views
Inline function Plan Properties in SQL Server 2008
Currently I use the below two queries to get some properties from the execution plan in stored procedures, the problem arises when I try to run it in a SQL Server 2008 version. Do you know if there is ...
0
votes
0answers
57 views
MYSQL 5.7 Online DDL (Alter) Statement causing Lock_Wait_Timeout : AWS RDS
We are not able to do updated code publish from application on MySQL 5.7 as it contains ALTER statement on the table which has more than 50 million records and application team escalated that most of ...
0
votes
0answers
202 views
Does keyset/cursor pagination scale query performance better than offset pagination?
I am seeing behavior where the query that powers my infinite scrolling list seems to be slowing down as the number of rows grow.
Naturally, I used offset pagination because the library I am using ...
10
votes
3answers
358 views
Converted a Scalar function to a TVF function for parallel execution-Still running in Serial mode
One of my query in was running in serial execution mode after a release and i noticed that two new functions were used in a view which is referenced in the LINQ to SQL Query generated from the ...
0
votes
1answer
25 views
mysql query with 1 derived table and 1 inner join takes forever to load
I tried removing some chunks of code little by little and found out that the condition inside the inner join is the devil. Can someone enlighten me on how to achieve this when I have like 10,000+ rows ...
0
votes
3answers
541 views
Make SQL query “latency proof”
I have developed an application which connects to a database over a WAN.
As long as I'm using the application on my local network, everything works fine.
When I connect over the WAN, the database ...
