Fractional CTO

David Favor

Developer and Analyst

Eduardo Fuerte

<div>
As Aneesh said, normally the problem is explicit or implicit SELECT statements which generate data sets which exceed RAM, then roll over to Swap.<br />
<br />
Once all your RAM is consumed, all database i/o will grind to a crawl causing more swapping.<br />
<br />
If you're lucky, the offending query(s) will end before your machine crashes.<br />
<br />
With MariaDB/MySQL, you can enable long query logging + then while instance is running, set any value (without restarting the database) for the query time which is considered long.<br />
<br />
Likely MSSQL provides a similar facility.<br />
<br />
Start with a slow query logging time of maybe any query taking &gt;100ms.<br />
<br />
You may have to enable general logging to log every SQL statement.<br />
<br />
What you should see in your general log is statements whizzing by... then... in your slow query log eventually you'll likely see a few long queries, which are the same query... they just get longer + longer...<br />
<br />
Many times what you'll see is the general log showing one SQL statement, then the log will go quiet.<br />
<br />
Also the slow query log will go quiet.<br />
<br />
Then you can look at the slow queries that were captured as culprits + run them with EXPLAIN + hopefully reorder your statements to get around problems like missing indexes or queries that are slow purely because of how the SQL statement is crafted.<br />
<br />
Once you get the hang of debugging this type of problem, likely you'll be able fix these easily in the future.<br />
<br />
Tip: If you have a dev machine, you can write a simple script to produce a synthetic database load to ensure you've actually fixed the problem.
</div>


<div>
Hi<br />
<br />
Thank you for the replies.<br />
Sql server activity monitor didn't present expensive queries by now.<br />
I had been warned several tempdb(s) are consuming almost all disk space, so expensive queries had been run before, that swaped to the disk, as David mentioned.<br />
Unfortunatelly a DBA is not easily accessible by now, could you suggest how to correctly reobtain the disk space used by these tempdb(s) - how to correctly eliminate them?
</div>


<div>
It's normal for SQL to gobble RAM. &nbsp;You hadn't mentioned but is performance being affected or just free drive/memory?<br />
<br />
You can use performance monitor to check a few key performance metrics including Lazy Writes/sec (less than 2), Pending Memory Grants (0), and Page Life Expectancy (at least 300 sec, but depending on total memory should be higher).<br />
<br />
If you look at the drive where the SQL files are, is the the .mdfs or the .ldfs (or potentially .ndf) that are taking up the space? &nbsp;What is the logging level set to on the server?
</div>


<div>
I couldn't directly see the files &nbsp;.mdfs or the .ldfs (or potentially .ndf) , I'm only beenn warned, but I'm going try to ask for someone that has the information.<br />
<br />
One doubt I have, if the session that performs the onerous query is killed - the tempdb space that was demanded by this session is imediatelly returned to be available?
</div>


<div>
You can use<br />

<pre><code class="code-1 nolinks" id="code-20-42832430-1"><span>SELECT file_id, name, type_desc, physical_name, size, max_size  </span>
<span>FROM sys.database_files ;  </span>
<span>GO  </span>
</code></pre>
<br />
You'd need to shrink the tempdb if some rogue adhoc was indeed the culprit and not some excess of transaction logs, which <a href="https://www.sqlskills.com/blogs/paul/shrinking-tempdb-longer-prohibited/" rel="ugc">will no longer potentially cause corruption</a> if done.<br />
<a href="https://support.microsoft.com/en-us/help/307487/how-to-shrink-the-tempdb-database-in-sql-server" rel="ugc">https://support.microsoft.com/en-us/help/307487/how-to-shrink-the-tempdb-database-in-sql-server</a><br />
<br />
Again, the memory usage isn't a suprise but that doesn't neccesarily mean performance is going to be impacted unless you had some other services running on SQL.
</div>


<div>
After reading what you posted I obtained:<br />
&nbsp;<a href="https://filedb.experts-exchange.com/incoming/2019/04_w14/1416730/img_EE_001_02042019.png" target="_blank" title="img001 (33 KB)"><img alt="img001" class="file-block lazyload" style="max-width: 800px;" data-src="https://filedb.experts-exchange.com/incoming/2019/04_w14/800_1416730/img_EE_001_02042019.png" /></a><br />
We had been warned again the disk space is always ended;<br />
<br />
By restarting the MSSQLServer the tempdb(s) will reassume the original dimension, isn't it?<br />
<br />
What do you think about redimensionate it? <br />
<br />

<pre><code class="code-1 nolinks" id="code-20-42834616-1"><span>ALTER DATABASE tempdb MODIFY FILE</span>
<span>   (NAME = 'tempdev', SIZE = target_size_in_MB) </span>
<span>   --Desired target size for the data file</span>
<span></span>
<span>   ALTER DATABASE tempdb MODIFY FILE</span>
<span>   (NAME = 'templog', SIZE = target_size_in_MB)</span>
<span>   --Desired target size for the log file</span>
</code></pre>
<br />
<a href="https://support.microsoft.com/en-us/help/307487/how-to-shrink-the-tempdb-database-in-sql-server" rel="ugc">Accordingly to.</a><br />
<br />
Must &nbsp;it to be cleaned before?
</div>


img_EE_001_02042019.png

<div>
Thank you for your replies.<br />
<br />
The matter is a data migration from a local VM to AWS is in course. This fact wasn't sufficient informed to the company.<br />
<br />
During the replication logs for syncronizations were consuming all the disk space..<br />
<br />
Until next weekend an AWS DB snapshot will be available for all our tests.
</div>


<div>
<div class="content wysiwyg-content">
Hi Experts<br />
<br />
Could you point out how to check what is progressively consuming all the features of MSSQLServer?<br />
<br />
From one moment to the next, the resources - disk space and RAM are alarmingly &nbsp;going low- &nbsp;we do not know the causes.<br />
<br />
Could you suggest a script to check what is causing this?<br />
<br />
Thanks in advance.
</div>
</div>


Hi Experts

Could you point out how to check what is progressively consuming all the features of MSSQLServer?

From one moment to the next, the resources - disk space and RAM are alarmingly  going low-  we do not know the causes.

Could you suggest a script to check what is causing this?

Thanks in advance.

Could you point out how to check what is progressively consuming all the features of MSSQLServer?

Databases are organized collections of data, most commonly accessed through management systems including schemas, tables, queries and processes that allow users to enter and manipulate the information or utilize it in other fashions, such as with web applications or for reporting purposes.

Databases

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.