Currently, we’re dealing with memory bumps. I’m new to troubleshoot memory pressure and I’m also trying to figure it out whether we need a new memory or not. I’ve a few questions to ask:

1. How to optimize memory usage in our environment?
2. how to identify the script/index which is consuming more memory?
3. What is the reason behind memory pressure?
4. Bufferpool
5. For 4TB db in enterprise SQL edition, how much memory needs to be added?
6. How to avoid resource semaphore?

I’ve done following troubleshooting but it seems like I don’t have a proper understanding to identify memory usage, memory optimization and memory pressure. Could you please help me with this.

We’re also noticing stack dumps in our environment: Our Server memory is 69 GB. SQL Server memory is 61GB.

What to check why we have stack dumps in our environment?

memory task627×661 130 KB
'm running following script to check is there any kind of pressure or not:

 SELECT AVG(current_tasks_count) AS [Avg Task Count], 
   AVG(work_queue_count) AS [Avg Work Queue Count],
   AVG(runnable_tasks_count) AS [Avg Runnable Task Count],
   AVG(pending_disk_io_count) AS [Avg Pending DiskIO Count]
   FROM sys.dm_os_schedulers WITH (NOLOCK)
   WHERE scheduler_id < 255 OPTION (RECOMPILE);
type or paste code here

Task count is 3 and other values are 0s. For the resource semaphore, I found 4 records. It keeps changing but resource seamaphore has records. Is it ok to request for following memory grant? Does this script need optimization?

resource_semaphore1243×218 7.56 KB
memory grants21063×217 7.82 KB

When I execute sp_BLitzCache u/sortOrder=‘memory grant’. I’m seeing requested memory grants in GB and used memory grants is in MB. Also, I’m seeing spills. Could you please help me what does spill mean? If requested memory grants in GB and used memory grants is in MB, does that mean I need to optimize those scripts? I’m referring too many documents and I’m not finding entire concept in one document that makes me confuse.

memory grant1052×237 7.72 KB
Memory primary troubleshooting:

 SELECT total_physical_memory_kb/1024 [Total Physical Memory in MB],
available_physical_memory_kb/1024 [Physical Memory Available in MB],
system_memory_state_desc
FROM sys.dm_os_sys_memory;

SELECT physical_memory_in_use_kb/1024 [Physical Memory Used in MB],
process_physical_memory_low [Physical Memory Low],
process_virtual_memory_low [Virtual Memory Low]
FROM sys.dm_os_process_memory;

SELECT committed_kb/1024 [SQL Server Committed Memory in MB],
committed_target_kb/1024 [SQL Server Target Committed Memory in MB]
FROM sys.dm_os_sys_info;

SELECT  OBJECT_NAME
,counter_name
,CONVERT(VARCHAR(10),cntr_value) AS cntr_value
FROM sys.dm_os_performance_counters
WHERE ((OBJECT_NAME LIKE '%Manager%')
AND(counter_name = 'Memory Grants Pending'
OR counter_name='Memory Grants Outstanding'
OR counter_name = 'Page life expectancy'))

troubleshooting722×151 5.05 KB

Also, some scripts are not executing only one time and requesting for 1 GB memory grant and using only MB of memory. Does this script requires any optimization? How to optimize memory intensive scripts?

memory grant3787×225 5.94 KB

o check memory pressure using following script:

  select * from sys.dm_Os_schedulers;

--check work_queque_count and pending_disk_io_count should be 0
--runnable_tasks_count should be 0 to check memory pressure

memory pressure1022×387 12.5 KB

Currently, we’re dealing with memory bumps. I’m new to troubleshoot memory pressure and I’m also trying to figure it out whether we need a new memory or not. I’ve a few questions to ask:

1. How to optimize memory usage in our environment?
2. how to identify the script/index which is consuming more memory?
3. What is the reason behind memory pressure?
4. Bufferpool
5. For 4TB db in enterprise SQL edition, how much memory needs to be added?
6. How to avoid resource semaphore?

I’ve done following troubleshooting but it seems like I don’t have a proper understanding to identify memory usage, memory optimization and memory pressure. Could you please help me with this.

We’re also noticing stack dumps in our environment: Our Server memory is 69 GB. SQL Server memory is 61GB.

What to check why we have stack dumps in our environment?

memory task627×661 130 KB

I have a project in which I am maintaining a table where I store translation of each line of the book. These translations can be anywhere between 1-50M.

I have a jobId mentioned in each row.

What can be the fastest way of searching all the rows with jobId?

As the table grows the time taken to fetch all those lines will grow as well. I want a way to fetch all the lines as quickly as possible.

If there can be any other option rather than using DB. I would use that. Just want to make the process faster.

Hi everyone,

I’m a total beginner working with MySQL 5.7.18, and I’m trying to get a thorough understanding of the EXPLAIN command to optimize my queries. I’ve looked at the official documentation, but honestly, it’s a bit overwhelming for me. I’d love some guidance or simpler resources to help me really grasp how EXPLAIN works.

I'm hoping to learn:

1. Understanding Each Column:

What do all the columns (id, select_type, table, type, possible_keys, key, rows, Extra, etc.) mean? How do I interpret these values and their importance in different types of queries?

2. Order of Execution:

How can I figure out the order in which MySQL is executing parts of my query from the EXPLAIN output?

3. Optimizing Queries:

What are the possible values for each column and how can I use that knowledge to optimize my queries and improve performance?

If anyone can break it down for me or point me toward beginner-friendly resources to learn thoroughly, I’d really appreciate it. Thanks for any help !

Obtain a Practice Database to experiment with different indexing strategies, query structures, and execution plans to find the most efficient way to retrieve data.

Practice databases can be used to experiment with automated query optimization tools and scripts, ensuring they work effectively before being implemented in a production environment.

Defining a primary key has always been a manual task and however we are rapidly moving towards automation, this task has been overlooked. I work in a company where ETL is my forte. So I've pitched to write a stored procedure that identifies the columns that optimally define a unique row in the table. So far I've put forward these points which will have some weightage while deciding such columns: • Cardinality • Column Data Type • Column Name What else would you add? Any suggestions on how to proceed with this?

This SQL query was timing out until I added a WHERE clause to reduce the amount of rows it has to process. Is there anything further I can do to either optimiza the MAX to reduce query time from a few minutes to less than a minute? Or is there any alternative to get the same result of a single Project ID per group by? TIA!

SELECT DISTINCT
ISNULL([Statement of Work ID],'') as "Statement of Work ID",
ISNULL([Primary Cost Center Code],'') as "Primary Cost Center Code",
ISNULL([Purchase Order Number],'') as "Purchase Order Number",
ISNULL([Invoice ID],'') as "Invoice ID",
MAX (CASE
WHEN [Project ID] LIKE '%[1-1][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%'
THEN SUBSTRING([Project ID],PATINDEX('%[1-1][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%', [Project ID]),10)
END) as "Project ID"

FROM [dbo]
WHERE [WorkWeek] LIKE '2024%'

GROUP BY
ISNULL([Statement of Work ID],''),
ISNULL([Primary Cost Center Code],''),
ISNULL([Purchase Order Number],''),
ISNULL([Invoice ID],'')

At my work, there is a debate regarding use of foreign keys.

One side of the debate is to remove foreign keys permanently to gain in performance and to simplify data archival.

The other side says that performance tradeoffs are in play, including gains for the query optimizer/planner, and that the data would become garbage because the system has almost no automated tests.

Do any of you have experience with such a debate, and what tradeoffs did you see when making such changes (either adding or removing foreign keys)?

I have table called events in postgres used for outbox pattern (read unpublished events and publish to kafka and mark them as published).

As table grows faster, I added partition for hourly on creation_time.

When enabling partition, it warned to use event_id & creation_time as primary key due to criteria that partition_key should be part of primary_key.

Now, when doing update query to mark event_id as processed = true with given event_id list, its scanning all partitions.

How to avoid this? or any approaches to make this more performant?

model table:

CREATE TABLE events 
(
    event_id SERIAL,
    event_timestamp TIMESTAMP NOT NULL,
    processed BOOLEAN DEFAULT FALSE,
    payload JSONB
    PRIMARY KEY ( event_id, event_timestamp)
) PARTITION BY RANGE (event_timestamp);

Today is actually my first day trying to understand and utilize SQL. I am using ssms to do this as its the software my upcoming internship will be using. Nevertheless, I have been trying to bulk insert this csv file and I cannot get it to work for the life of me, and yes I am positive that the file path is correct. I also did create a fmt file, which I tried to use in a previous query attempt, but was still given the same error message. Any feedback is appreciated!

https://preview.redd.it/qhofxyrxmg2d1.png?width=1487&format=png&auto=webp&s=98a95defa23754b780a5174aaf884d65601d0837

https://preview.redd.it/wcnhwycymg2d1.png?width=1139&format=png&auto=webp&s=6edc22d17b16281b28a560fd10db641da3e667fe

I read doc they said merge need to sort ,but sort quite cost therefore im not consider using it ? is that ok

This is probably a dumb question as I am new to SQL, but I am trying to pull sales data for 900 accounts. To make this faster I am using an IN function and all 900 accounts. What would be a better way of doing this?

We need to find the latest asset history record for each asset.

```

DECLARE u/__projectId_0 int = 23;

DECLARE u/__phaseId_1 int = 3;

SELECT *

FROM [asset_history] AS [a]

INNER JOIN (

SELECT [a0].[asset_id] AS [AssetId], MAX([a0].[created]) AS [MaxDate]

FROM [asset_history] AS [a0]

WHERE ([a0].[project_id] = u/__projectId_0) AND ([a0].[status] <> 3)

GROUP BY [a0].[asset_id]

HAVING (

SELECT TOP(1) [a1].[workflow_phase_id]

FROM [asset_history] AS [a1]

WHERE (([a1].[project_id] = u/__projectId_0) AND ([a1].[status] <> 3)) AND ([a0].[asset_id] = [a1].[asset_id])

ORDER BY [a1].[created] DESC) = u/__phaseId_1

) AS [t] ON ([a].[asset_id] = [t].[AssetId]) AND ([a].[created] = [t].[MaxDate])

WHERE ([a].[project_id] = u/__projectId_0) AND ([a].[status] <> 3)

```

I have been trying to build a poc which generates unit test to test my SQL Packages with multiple procedures by making my own custom LLM by training on base Llama2 70-b . I have build a model - A that explains what a specific procedure does, followed by another model - B which just prompt engineers the response from model - A to generate unit test cases to test the procedures present in the packages. So far this has been a good approach but i would like to make it more efficient. Any ideas on improving the overall process?

I can't get concurrent users to increase no matter the server's CPU power.

Hello, I'm working on a production web application that has a giant MySQL database at the backend. The database is constantly updated with new information from various sources at different timestamps every single day. The web application is report-generation-based, where the user 'generates reports' of data from a certain time range they specify, which is done by querying against the database. This querying of MySQL takes a lot of time and is CPU intensive (observed from htop). MySQL contains various types of data, especially large-string data. Now, to generate a complex report for a single user, it uses 1 CPU (thread or vCPU), not the whole number of CPUs available. Similarly, for 4 users, 4 CPUs, and the rest of the CPUs are idle. I simulate multiple concurrent users' report generation tests using the PostMan application. Now, no matter how powerful the CPU I use, it is not being efficient and caps at around 30-40 concurrent users (powerful CPU results in higher caps) and also takes a lot of time.

When multiple users are simultaneously querying the database, all logical cores of the server become preoccupied with handling MySQL queries, which in turn reduces the application's ability to manage concurrent users effectively. For example, a single user might generate a report for one month's worth of data in 5 minutes. However, if 20 to 30 users attempt to generate the same report simultaneously, the completion time can extend to as much as 30 minutes. Also, when the volume of concurrent requests grows further, some users may experience failures in receiving their report outputs successfully.

I am thinking of parallel computing and using all available CPUs for each report generation instead of using only 1 CPU, but it has its disadvantages. If a rogue user constantly keeps generating very complex reports, other users will not be able to get fruitful results. So I'm currently not considering this option.

Is there any other way I can improve this from a query perspective or any other perspective? Please can anyone help me find a solution to this problem? What type of architecture should be used to keep the same performance for all concurrent users and also increase the concurrent users cap (our requirement is about 100+ concurrent users)?

Additional Information:

Backend: Dotnet Core 6 Web API (MVC)

Database:

MySql Community Server (free version)
table 48, data length 3,368,960,000, indexes 81,920
But in my calculation, I mostly only need to query from 2 big tables:

1st table information:

Every 24 hours, 7,153 rows are inserted into our database, each identified by a timestamp range from start (timestamp) to finish (timestamp, which may be Null). When retrieving data from this table over a long date range—using both start and finish times—alongside an integer field representing a list of user IDs.
For example, a user might request data spanning from January 1, 2024, to February 29, 2024. This duration could vary significantly, ranging from 6 months to 1 year. Additionally, the query includes a large list of user IDs (e.g., 112, 23, 45, 78, 45, 56, etc.), with each userID associated with multiple rows in the database.

2nd table information:

The second table in our database experiences an insertion of 2,000 rows every 24 hours. Similar to the first, this table records data within specific time ranges, set by a start and finish timestamp. Additionally, it stores variable character data (VARCHAR) as well.
Queries on this table are executed over time ranges, similar to those for table one, with durations typically spanning 3 to 6 months. Along with time-based criteria like Table 1, these queries also filter for five extensive lists of string values, each list containing approximately 100 to 200 string values.

Test Results (Dedicated Bare Metal Servers):

SystemInfo: Intel Xeon E5-2696 v4 | 2 sockets x 22 cores/CPU x 2 thread/core = 88 threads | 448GB DDR4 RAM
Single User Report Generation time: 3mins (for 1 week's data)
20 Concurrent Users Report Generation time: 25 min (for 1 week's data) and 2 users report generation were unsuccessful.
Maximum concurrent users it can handle: 40

Hi , I have using two select statement in my stored procedure with different set of columns having common of two I'd but in MySQL of latest version the CTE is not supported. What is the alternative solution of this issue please help me to find it out..

Hello Guys anyone with experience optimizing Sql queries in Oracle Inmemory? Please PM me if you can assist for a fee . Thanks

Inputs

• multiple teams with various hours available per month.
• multiple projects with various hours to complete and months from the deadline.

Assumption

• project hours/month will not exceed team capacity.
• month 1 in solution is next month (June 2024)

In the data below team A has 3 projects. The projects require 1000 monthly hours each (3000 hours divided by 3 months). Team A has 2000 monthly capacity hours to dedicate to any number of projects. I want to write code that will define the start month and then smartly know when to start the next project with that team until all projects are done. In the example, team A can do projects 1 and 2 simultaneously because it is below their capacity and start on project 3 in month 4 as project 1 wraps up and their capacity increases to a point where they can start working on project 3.

Project Data

Team Capacity Dimension

Output

I’m thinking a loop and/ or an over (partition by, order) would be my best option. Thoughts?

Thanks in advance, jamkgrif

I have a stored procedure that creates two temp tables. Both temp tables have a primary key setup with a nvarchar(10) and a date field. Most of the other fields are numeric and not indexed. One table gets loaded with about 330k of rows and the other gets about 455k. Sql server 2019 will not use a merge join on the query that links these two tables together by only the two indexed fields. It displays and adaptive join but always picks a hash match. Sql server adds a "parallelism (repartition streams)" to the plan. Any suggestions on how I can make it perform the merge join with out the forcing it in the query?

Good morning,

​

This is my first post. Currently, I'm running a PowerShell script from my server that calls several stored procedures on a SQL Server. I have three stored procedures:

​

1. Delete

2. Update

3. Insert

​

The script first executes the delete, then the update, and finally, the insert. Do you think this is the best way to manage it, or would it be better to combine all the operations into a single stored procedure? Sometimes, I encounter errors from the SQL Server, such as timeouts. The timeout in the script is set to 300 seconds.

how do you guys manage that?

How do you contro

​

Check some new point of views about if we should use or not use Optimize for Ad Hoc Workloads

https://red-gate.com/simple-talk/blogs/sql-server-optimize-for-ad-hoc-workloads-use-or-not-use/

Customer Table

Customer product_key

1 5

2 6

3 5

3 6

1 6

​

​

Product Table

Product_key

5

6

​

Output

Customer_id

1

3

The problem asks for getting all customers who purchased all product
This is my query
SELECT customer_id

FROM customer c WHERE customer_id IN

( SELECT c.customer_id FROM customer c INNER JOIN product p ON c.product_key = p.product_key GROUP BY c.customer_id HAVING COUNT(c.product_key) > 1 );

how can i further optimize my query or is there a better way to right it

Weather table:

+----+------------+-------------+

| id | recordDate | temperature |

+----+------------+-------------+

| 1 | 2015-01-01 | 10 |

| 2 | 2015-01-02 | 25 |

| 3 | 2015-01-03 | 20 |

| 4 | 2015-01-04 | 30 |

+----+------------+-------------+

Output:

+----+

| id |

+----+

| 2 |

| 4 |

+----+

this is the query

select w1.id from weather w1

inner join weather w2

on w1.id = w2.id + 1

where w1.temperature>w2.temperature I am not Getting where 4 is coming from?

Hi All ,Newbie here.

Recently I attended an interview for data engineer role, where the following question was asked.

we have two tables stg and final both with same set of columns (id,name)

stg | id, name

final | id,name

and both of them has some data already present. And the ask is to write a query which insert the data from stg table whose ids are not present in the final table.

I gave the following answer.

insert into final
select id , name from stg
where id not in ( select distinct id from final )

And then the interviewer asked a follow up question. If the final table is huge (millions of records) , then this query will not be efficient as it has to scan the whole final table and asked me to give a better approach.

I couldn't answer it and I failed the interview.
Can you guys help me with this ? What can we do to improve this insert performance ?

​

Thanks in advance

select max(case when d.department = 'engineering' then e.salary else 0 end) as max_eng_sal

, max(case when d.department = 'marketing' then e.salary else 0 end ) as max_markt_sal

from db_employee as e

inner join db_dept as d

on e.department_id = d.id

group by d.department

order by max_eng_sal desc, max_markt_sal desc

limit 1;

​

max_eng_sal max_markt_sal

45787 0

​

this querry is showing max_markt_sal = 0 but it is incorect how can i correct it

Hi. Struggling with a problem.

I can't seem to find a solution to this. Table dbo.table Columns count, offer, offer_t, errand, copy Column offer and copy have both mix of same data strings but in different proportions. How do I find a value from column offer that doesn't exist in column copy by using exist or not exist?

I'm interviewing for a role as a business analyst. On which platform I can practice SQL questions related to joins, window functions etc