Questions tagged [performance-tuning]
Improving the performance characteristics of a database application or system.
0
votes
0answers
28 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 [...
-2
votes
0answers
78 views
How to investigate locks impact on MSSQL performance? [closed]
I have performance issues with 3rd-party application on MSSQL database.
By query sys.dm_os_wait_stats (from sqlskills) I have this results:
WaitType Wait_S Resource_S Signal_S ...
0
votes
1answer
25 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
22 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
4 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
70 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
41 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 ...
3
votes
1answer
80 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
38 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 ...
-1
votes
0answers
52 views
Slowly Changing Dimension table Index Optimization
What is the optimal way to created index on slowly changing dimension tables which have Begin and EndDates? If I want to select when Customer owned house or enrolled in school.
Should EndDate be in ...
2
votes
3answers
145 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
15 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
0answers
41 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
30 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
107 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
52 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
52 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
85 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
88 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 ...
4
votes
2answers
69 views
CXPACKET wait on SQL Server
Recently I am experiencing performance issue on a SQL Server database and I have two queries that show me the wait as shown below, one of the databases on this server is a member of Always On replica, ...
1
vote
0answers
60 views
pgbench - 20-30% variation in benchmark results (non-repeatable benchmarks)
Problem
I'm trying to use pgbench to help me tune Postgres for my hardware, but I'm seeing a very strange behaviour. I cannot seem to get a stable number for TPS in consecutive runs of pgbench. Since ...
0
votes
0answers
33 views
RAC DB Slowness - AWR Report
While going through the AWR report found that the row lock waits on indexes and buffer busy waits are more (rest of the stats look normal).
Could someone tell me if we can interpret by the below if ...
0
votes
1answer
86 views
sp_WhoIsActive waiting resource
I have a huge query in SQL Server 2016 – when I run the query for a subset of records, it completes in 15 minutes. When I extrapolate it to total number of records, it is supposed to be complete in 1 ...
0
votes
1answer
109 views
TSQL or SSIS for Direct Insert Speed Performance
I am moving data from Table A into Table B. Tables have the exact same DDL, table structure, and are straight inserts. We are Not transforming or modifying data.
If TSQL Server and SSIS have the ...
3
votes
1answer
91 views
Identify Query Waiting on resources
I have some query with CXCONSUMER wait time when i ran sp_whoisactive as shown in the screenshot below.
When i checked the status it is suspended.That means it is waiting for some process to release ...
2
votes
1answer
119 views
Microsoft Optimal method for columns into 1 word: XML Raw or Concat
What is fastest, performance method to make columns into one word? Does Microsoft have recommendation, or is there performance Big-O studies on FOR XML RAW vs Concat?
Company has multiple tables with ...
1
vote
2answers
135 views
Is NEWSEQUENTIALID unique within a database for Many tables?
Is NEWSEQUENTIALID unique within a database? We plan many tables in a database with NEWSEQUENTIALID, inserting simultaneously and want to ensure every guid will be unique, regardless if they are in ...
0
votes
1answer
61 views
powerful VPS but slow MS SQL- why? [closed]
VPS (Contabo) : 10 cores (Xeon E5 2.2GHz), 60 GB RAM, SSD, MS SQL Web Edition (configured to use 10 CPUs), Windows Server 2016
When running SQL takes ~32GB of RAM,
When I execute SQL a stored ...
0
votes
1answer
75 views
Update sys.job$ set this_date=:1 where job=:2 as one of the most time-consuming queries - oracle
I am currently helping some organization with their Oracle database performance. They are using Oracle 11gR2 Standard Edition in Windows 2008. As there are no enterprise performance packages ...
0
votes
1answer
38 views
Oracle 9i historical long queries since instance startup
please I would like to know any query that shows the queries or the top 10 queries that take more time to finish since the beginning of the instance with user and the sql sentence, I have seen several ...
0
votes
3answers
225 views
Create Slowly Changing Dimension table from Repeated Data
I am receiving transaction snapshot data from a file, it contains a history of repeated data. Currently trying to find Slowly Changing Dimensions on table with [ProductId] Business Key. Many ...
0
votes
1answer
61 views
Loading 400 Million Records into MySQL Hangs on “Sending Data”
I have a large dataset of approximately 420 million records and I was able to load them into a temporary table in a timely manner of about 15 minutes using a LOAD DATA INFILE statement. Super awesome. ...
0
votes
2answers
23 views
MariaDB “EXECUTE AS” or User Impersonation
With MariaDB, is there a way to impersonate a user execution context when connected with a higher privileged account?
For instance, when connected with a "MasterUser" execute a SELECT using the more ...
7
votes
2answers
274 views
SQL Staging Tables: Primary Key Clustered or Heap
We are taking legacy flat txt files and inserting them into stage tables with SSIS.The question arose whether table should have primary clustered key index or not. This is direct flat file import with ...
1
vote
1answer
92 views
SSIS Data Source: Embedded Queries, Stored Procedures, or Functions
We are moving data from OLTP server into Data Warehouse server, utilizing SSIS to conduct this.
When writing queries from the OLTP Data Source, it it better to utilize?
1) Inline Embedded queries
2)...
0
votes
2answers
34 views
Index columns on pre-production?
If I understand correctly, indexing a column will increase SELECT speed but decrease INSERT speed.
Say your app is new and you have identified columns which are good candidates for indexing (based on ...
0
votes
1answer
36 views
Query Locks Table And Takes Long Time to Run
I have a query that can take a long time to run. I tried running the same query in a test server and it runs very quick.
However, in production it seems to hang and causes blocking of other queries.
...
1
vote
1answer
38 views
SQL Except Statement and Add in One more column
How do I conduct a Except statement and Add in one more column?
Three tables with the exact same structure. (CustomerTransaction1,CustomerTransaction2, CustomerTransactionDiff)
I want the LoadDate ...
3
votes
1answer
224 views
Memory Pressure in SQL Server - Enable LPIM or not?
I have slow query execution in my SQL Server 2016.
Server configurations:
Total Physical Memory : 128 GB
SQL server Max Memory : 102 GB
Transaction replication is enabled.
Database Size:1.6 TB
...
0
votes
1answer
73 views
SP_WhoIsActive - RESOURCE_SEMAPHORE_QUERY_COMPILE
Had an issue with one of our production servers earlier today. Many transactions were suspended with RESOURCE_SEMAPHORE_QUERY_COMPILE wait.
Have sp_whoisactive data logged to table every minute. Below ...
8
votes
1answer
188 views
What is the algorithm behind the EXCEPT operator?
What is the internal algorithm of how the Except operator works under the covers in SQL Server? Does it internally take a hash of each row and compare?
David Lozinksi ran a study, SQL: Fastest way to ...
0
votes
1answer
24 views
DB2 Workfile scan
We just migrate from LUW to zos and we're finding that in z/os the accesspath explains show new node named WFSCAN (Workfile scan) and this rises when there is a join, group by or any kind of sort.
...
0
votes
2answers
1k views
SSIS vs T-SQL Join Performance, Difference Databases on Same server
We are researching the performance difference between SSIS Lookup and T-SQL Join. We want to join two tables; tables are in the same SQL server instance, different databases.
I suspect for small ...
2
votes
1answer
328 views
Oracle 12c - Slow query to tables ALL_CONS_COLUMNS and ALL_CONSTRAINTS
We have successfully upgraded Oracle 11g to Oracle 12c. So far it's been going well. But We noticed one problem, queries made for Oracle's constraint tables (ALL_CONS_COLUMNS and ALL_CONSTRAINTS) are ...
1
vote
1answer
170 views
How Large Table before Using Clustered Columnstore Index
At what size should we consider applying Columnstore Index on a table? Is there a SQL Server storage or design guidance?
Our Data Warehouse is 5TB total. Additionally, we have tables which are 100GB ...
1
vote
2answers
324 views
Can a table have Both a Clustered Columnstore Index and Nonclustered Columnstore Index?
Can I a table have both Clustered Columnstore Index and Nonclustered Columnstore Index?
I am seeing example strategies to use Clustered Columnstore Indexes with only Nonclustered Binary row indexes.
...
3
votes
1answer
149 views
MySQL creates tables very slow
On my home machine running Ubuntu 18.04 and MySQL version 8.0.12 with better hardware, table creation, database creation and so on are very slow. On my other computer with older hardware (slower SSD), ...
1
vote
1answer
113 views
what are memory fractions and how to get rid of them (or the sort operator) in the example below?
This execution plan has the following memory fractions:
Memory fraction input:1, Memory Fraction Output:1.
it is a very simple query:
SELECT [a].[activityId],
...
2
votes
2answers
572 views
Performance differences between RID Lookup vs Key Lookup?
Are there any performance differences between when a non-clustered index uses the clustered index's key to locate the row vs when that table doesn't have a clustered index and the non-clustered index ...
-2
votes
1answer
123 views
What is the Optimized Way to Dynamic SQL Select all Columns from Table?
I just generated this script.
It will select all columns from a table dynamically.
Is there an alternative or more clean, efficient way of conducting this?
Additionally, I want to place line breaks ...
