Questions tagged [performance-tuning]
Improving the performance characteristics of a database application or system.
489
questions
1
vote
0answers
19 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
0answers
15 views
Is it allowed to ask here help to tweak mysql 5.5 performance? [closed]
Can I post here my MySQL configuration and VPS data and simply ask suggestion for tuning?
I've inherited a VPS and a old webapp, but I have no knowledge of MySQL administration and we have a lot of ...
1
vote
1answer
39 views
Physical co-clustering of two or more logical relations/tables in modern RDBMS
One possible approach to optimize search queries is (a) to store the records that retain data that corresponds to different relations/tables in (b) the same file → on the same pages. That way, a join ...
0
votes
1answer
51 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
16 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
76 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
28 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
23 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
11 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
127 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
33 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 ...
5
votes
2answers
86 views
Best way to configure and maintain a table with many insertions and deletions that's empy on average?
There exists a "batch query" table that has two columns (BatchID, RecordID) which are both of TSQL type 'uniqueidentifier'. The unique clustering key is on (BatchID, RecordID), and there are no ...
0
votes
0answers
11 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 ...
1
vote
1answer
184 views
Postgres 10 Query waits on IO: BufFileWrite causing failure to get new database connections
I have a query (attached with query plan) that is run every 5-7 seconds from every device that is running our dashboard (typically, 500+ devices at peak time). This query, at the outset looks like it ...
13
votes
3answers
1k 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
0answers
25 views
SQL Server Resource Governor for Buffer Pool [duplicate]
We plan to have multiple databases on a Server Instance. Does Resource Governor allow buffer pool to be allocated for each database in SQL Server 2017?
0
votes
2answers
137 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
34 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
47 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
48 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
149 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
46 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
29 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
94 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
324 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
24 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
410 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 ...
0
votes
0answers
35 views
What is the best compaction strategy for overwritable/mutable/upsert-able data?
Suppose I have a Cassandra table like this:
CREATE TABLE keyspace.last_modified (
category text,
subcategory text,
id text,
created_at bigint,
PRIMARY KEY ((category, subcategory, ...
5
votes
2answers
375 views
Slow checkpoint and 15 second I/O warnings on flash storage
Last couple of weeks we've been working on getting to the root cause of what could likely be the cause of the occurrence of these I/O issues and slowdown of the checkpoints.
At first glance it looks ...
0
votes
1answer
78 views
Do Indexes in the T-SQL Data Warehouse automatically go over and optimize into the SSAS Cube?
Do Indexes in the T-SQL Data Warehouse automatically go over and optimize into the SSAS Cube?
I am reading that applying indexes, will transfer into SSAS. I thought SQL and SSAS are totally different ...
0
votes
1answer
148 views
Performance Metrics: Columnstore Indexes vs SSAS Cubes [closed]
What has faster performance speed: Columnstore Indexes or SSAS Cubes? Are Columnstore indexes proven to be faster than SSAS cubes (SQL Analysis Services)? Have their been any performance studies ...
0
votes
1answer
36 views
Why these Two Queries performace so different?
This is Query 1:
SELECT
A.innings_no AS Inn, bowler, OT.Out_Name AS OutType, Runs
FROM
(SELECT
a.innings_no, a.striker, SUM(b.runs_scored) AS Runs
FROM
ball_by_ball a
...
0
votes
1answer
46 views
Need Help in extended events SQL Server
I have created an extended event as below. I want to know how to query the historical data using T-SQL to see the issues at particular timestamps. The trace is running as of now
CREATE EVENT SESSION [...
0
votes
2answers
41 views
does sequence of columns in where condition matters the sequence of composite key? in Oracle Database
here, In the code where exists clause contains all primary key columns in where, I want to know that the sequence of this columns in it, matters in query performance.
update temp_fd_dtl_svj a
set ...
0
votes
1answer
29 views
Filegroup: [primary] dbcc shrinkfile() need balanced extent counts per file when done
I'm working with a ~1 terabyte db, server 40+cores, memory 500G+, db files on SAN (no control over lun segments), the bottleneck now is file contention. Currently running 2014 but by final "release" ...
0
votes
0answers
14 views
Performance considerations for a standalone server running Postgres 9.4 on 8 CPU core with 47GB RAM
I'm running:
Postgres 9.4
8 CPU cores
47GB RAM
shared_buffers = 13.8GB
work_mem = 32MB
The server gets its workload from 6 web application nodes which connect using JDBC connection pooling. Each web ...
-3
votes
1answer
76 views
Partitioning and Performance of DB Table [closed]
After partitioning a table the performance of select queries returning large datasets degrades as compared to performance earlier. For small datasets there is no degradation. No change in index is ...
0
votes
2answers
182 views
What kind of SSD to speed up querying a lot of data?
For research purposes I am using a big MariaDB database (~500 GB) with 20 Tables (engine: myISAM) and around 9 Billion rows. Due to missing free storage on my Laptop (16 GB RAM), I put the data on an ...
2
votes
1answer
331 views
Empty Table: Insert into Table WITH (TABLOCK)
We have a clustered index table which is empty. Table has around eg: 20 columns, 50 million rows, around (40GB).
Is it best practice to apply Insert With (TABLOCK) for empty tables such as this? The ...
0
votes
1answer
46 views
Are these stats reasonable? If not, where should I start?
The below is the output result of mysqladmin for our mysql server which has been having consistent problems for months since we switched from a dedicated server to the cloud. In the switch, we ...
2
votes
3answers
2k views
SQL Server: Performance Insert Into vs Select Into
I am hearing different things from colleagues/research. What are good guidelines in performance for, Select Into vs Insert into when creating a temp table? I know difference is minimal for small ...
0
votes
0answers
17 views
any knowledge or experience with dynamic ax [duplicate]
I support a system called "dynamics ax" and sometimes my optimizer picks a good plan, drops it and then picks a bad plan (where the same code is been ran). users who usually wait for 10 seconds start ...
1
vote
1answer
79 views
oracle slow query with diferent user
I have a question with an oracle query that takes almost 20 minutes when it is executed from a schema and yet when it runs as sys users it runs in 1 second maximum ... how is this possible? ... I do ...
1
vote
1answer
287 views
Debug Connection Pool Error
I have been getting the below error.
System.Data.Entity.Core.EntityException: The underlying provider failed on Open. ---> System.InvalidOperationException: Timeout expired. The timeout ...
4
votes
1answer
879 views
why is this left join faster than an inner join?
So I'm tuning this query, and I am pretty sure that in this instance, I can replace an inner join with a left join without affecting the data. However, I'm not entirely sure why this is faster. Here ...
1
vote
1answer
87 views
When to Disable and Recreate Indexes in Data Warehouse ETL?
We are moving data from Staging Database into Kimball Data Warehouse DB.
I am hearing different ideas regarding when to disable and rebuild indexes.
Stage tables are truncated every day for each new ...
2
votes
0answers
53 views
Recommendations around managing large amounts of data that needs to be aggregated (SUM)
I am looking for alternatives to redesign a part of our system that needs to perform cumulative SUMs over large data sets of rows based on user and dates.
How the system works
We are running a web (...
-1
votes
1answer
227 views
Index vs Statistics [duplicate]
In SQL Server, when to create statistics only on a table column instead of index and when create to index (which creates statistics as well automatically)? I mean both improve query performance then ...
2
votes
2answers
150 views
Slow query that includes a WHERE clause with SUBSTRING
I have the following query in SQL Server 2016, as there is no code for the "enter code here" column, I had to do a search with SUBSTRING. But as expected it is slow, the query ends in 00:03:37.
with ...
