I bet you've configured the virtual CPUs in a way that some of the CPU nodes and/or memory nodes are offline.
Download sp_Blitz (disclaimer: I'm one of the authors of that free open source script) and run it:
sp_Blitz @CheckServerInfo = 1;
Look for warnings about CPU and/or memory nodes being offline. SQL Server Standard Edition only sees the first 4 CPU ...
Yes, you can use the @SkipChecks parameters for this. Create a table with columns for:
Then populate it with the list of databases & checks you want skipped. For example, if you want check 52 skipped for all databases, add a row with CheckID 52, and databasename null. If you want check 52 ...
What you see makes perfect sense given the information available.
SQL Server 2012 Standard Edition is limited to the lesser of 4 sockets or 16 physical cores.
Since you have 40 cores (likely 2 socket 10 core + HT) 16 physical cores is going to be 32 logical cores (because of HT). If you have 40 total, I would EXPECT 8 to be VISIBLE OFFLINE due to licensing....
Microsoft says that DMV (ring buffers) doesn't work on SQL Server 2017, only 2019:
Sys.dm_os_ring_buffers DMV has been a key DMV used for monitoring SQL
Server by built-in tools as well as third party monitoring utilities.
When SQL Server 2017 was released on Linux, unfortunately this DMV did
not return correct CPU usage information by SQL Server ...
I would suggest you follow what the sp_blitz recommended. You can change the option to CHEKSUM. Checksum might be able to detect more problem that torn_page.
Torn page allows you to detect whether page was successfully written to disk or not. It would not check what is inconsistency inside the page. While checksum performs more thorough checks. Having said ...
Yes, it requires sa permissions. But since it's a stored procedure, you can get permissions via a certificate
See the section How to Grant Permissions to Non-DBAs
CREATE CERTIFICATE sp_BlitzFirst_cert
ENCRYPTION BY PASSWORD = '5OClockSomewhere'
WITH SUBJECT = 'Certificate for sp_BlitzFirst',
START_DATE = '20130711', EXPIRY_DATE = '21000101';
These are likely left over from Microsoft's Database Tuning Advisor. You should be able to get information about the sessions with this stored procedure:
After identifying the sessions that you do not need you can remove them with this stored procedure supplying the session id using one as an example:
EXEC sp_DTA_delete_session 1
As an addendum to Brent Ozar's plan of action, I wanted to share the results. As Brent noted, within VMware we had configured the Virtual Machine improperly with 12 single-core CPUs. This resulted in the remaining 8 cores being inaccessible by SQL Server, and as a result, led to the memory issue described in my original question. We placed our services in ...
When someone does a BEGIN TRAN, inserts a row, locks their computer, and goes home for the weekend, your log file will grow and grow and grow.
That would be a Bad Thing™.
Rather than facing an outage, you're better off letting the log file grow, and using your monitoring tool to get alerts that there's a problem.
Question 1: "What's with CREATE PROCEDURE?!?" When you execute a stored procedure, SQL Server stores the entire text of the stored procedure as the thing you called.
You weren't CREATING the stored procedure - you were only executing it - but this can be a little confusing for folks who are just getting started analyzing the plan cache.
So hey, you're now ...
There was an issue with the latest release, shown in ISSUE 526. It was updated, by changing this code on line 472.
SELECT @MsSinceWaitsCleared =
DATEDIFF(MINUTE, create_date, CURRENT_TIMESTAMP) * 60000
SELECT @MsSinceWaitsCleared =
DATEDIFF(MINUTE, create_date, CURRENT_TIMESTAMP) * 60000.0
If you do a new Pull request through github it has ...
From the documentation on GitHub - (highlighting mine)
The First Responder Kit runs on:
SQL Server 2008, 2008R2, 2012, 2014, 2016, 2017 on Windows - yes,
SQL Server 2017 on Linux - yes, fully supported except sp_AllNightLog
and sp_DatabaseRestore, which require xp_cmdshell, which Microsoft
doesn't provide on Linux
SQL Server 2000, 2005 - ...
As Denis Rubashkin said in a comment:
If you had a look at the script text you would see that the script just creates a procedure sp_Blitz. You have to execute that proc (sample execution call with the most common parameters is in the bottom of the sp_Blitz script) to get an output you are waiting for.
You just created the procedure, but you still have ...
Brent Ozar folks already have got you 50% - Inserting sp_Blitz® Output Into a Table
My steps would be :
Log the output to a local dbastats or some dba database.
Have a list of all sql servers in your central server.
use sqlcmd and opendatasource to query remote servers and dump all the info into centralized server .. make sure you include something like ...
For sp_Blitz, there's an enhancement request filed at Github to add outputs for version, and if you'd like to influence how the work is done (or contribute code), you're welcome to leave comments over there.
For sp_WhoIsActive, the author Adam Machanic is considering adding an output to the Messages tab each time it runs.
Until then, Oreo's suggestion ...
This is not an over-provisioning issue or even a memory misconfiguration.
RESOURCE_SEMAPHORE: "Occurs when a query memory request cannot be granted immediately due to other concurrent queries. High waits and wait times may indicate excessive number of concurrent queries, or excessive memory request amounts."
Depending on which version and build you are ...
Can be anywhere from 5 seconds to 15 minutes depending on your server, workloads, databases, etc. If you have thousands of databases, for example, you can check out the documentation and use switches like @CheckUserDatabaseObjects = 0 to go faster by skipping stuff.
So let's do a quick rundown of your top two waits:
IO_COMPLETION - waiting to write to storage
PAGEIOLATCH* - waiting to read from storage
In 30 seconds, your queries spent 1,655 seconds waiting on storage.
Your storage is probably slow - if you skip down to the next section in sp_BlitzFirst's output, it will show which data and log files SQL Server was ...
You need to divide io_stall_read_ms by num_of_reads, and io_stall_write_ms by num_of_writes. This will give you something more sensible as what you are seeing for read and write stalls and sum totals since your instance last stated up.
However, using this DMV in this manner can average out any spikes and hide patters, therefore you need to take samples from ...
If you look closely at SpBlitz memory recommendations it says it has two possible meanings
Memory Dangerously Low or Max Memory Too High
In your case looking at perfmon counters output you have shared their does not seems to be a memory pressure. So we are left with the fact that max server memory is configured incorrectly. And this seems true, on a ...
You did not mention your workload, but I run log backups every minute in our OLTP environment without issue (granted, we don't process thousands of transactions per second). If a log backup happens to take over 1 minute, there is no failure. The job will merely run again on the minute mark after the current job completes.
As Brent says in What happens to ...
Adding the resolution from the comments: the stored procedure was created in a case-sensitive user database, and it was trying to copy things into a case-sensitive TempDB.
After removing sp_Blitz from the case-sensitive user database, and installing it in master (which would then reflect the system database collations), it worked fine.
I managed to get quite a usable list by sniffing out the versions within the SProcs' source code, as stored within the sys.comments tables.
A healthy amount of assumptions was made, so test this code on a solitary test instance first, before going wild on your multitude of Production servers...
Edit: added Adam Machanic's sp_WhoIsActive as an example of ...
In your screenshot, you're running sp_WhoIsActive. You can click on the query text in blue:
And that'll tell you which query is running slow, and might give you some insight into why. You can also read the documentation, which mentions to try @CheckUserDatabaseObjects = 0 as a parameter to go faster.
You have a couple of different questions in here.
Can sp_BlitzWho get blocked?
Yes, any query can. There are DMVs that are known to ignore isolation level requests and incur blocking.
Should I use sp_BlitzWho to log wait stats by session?
I wouldn't recommend doing that on a scheduled basis, no. If you need wait stats by session, you're better off with a ...
In simple recovery model, the database still uses the log file. The difference is that this log file doesn't need a log backup to get truncated (to be empty).
A long running transaction may fill that log file and if SQL can't write to it, the database will stop beeing usable.
As Brent recommended, you should let the file growth until it fills the drive (...
There's a weird bug that only pops up on some instances of SQL Server when the Trace File path has problems. I can't diagnose those, but we took some steps to avoid that bug which are in the latest version of sp_Blitz.
Please head over to firstresponderkit.org to grab the newest version, and file an issue at the GitHub repo if you run into anything else.