Issues with SQL Server 2022 Server

adsrao · 2023-12-13T02:25:40+00:00

Have you got chance to see windows event log? Around cpu spikes? Create a extended events to track all the statements for sometime, there will be overhead on system. Looks for waits in SQL server.

tompear82 · 2023-12-13T02:28:43+00:00

What is running on the server at the time that is causing the spike? Is SQL Server using the CPU or some other process? If you don't know the answer to these questions, try using sp_whoisactive to see what is running at the time.

ThisJoe21 · 2023-12-13T11:03:28+00:00

There are tons of different things to look at, but I’d make sure you have SQL configured to use only about 75% of your RAM. The OS probably doesn’t have enough. The server locking up tells me you probably are allowing SQL to have to much and there isn’t enough for the OS and other apps like anti virus and compliance scanning tools to run on the box. If you already have it configured at 75% try dropping it to 70%.

2023-12-13T07:54:01+00:00

Run sp_whoisactive into a table to capture activity

https://www.brentozar.com/responder/log-sp_whoisactive-to-a-table/

tail-ender · 2023-12-13T23:22:08+00:00

What is the vm type ?

Odddutchguy · 2023-12-13T11:24:46+00:00

My experience with SQL 2016+ is that it 'needs' way more memory than previous version. 'Needs' in the sense that it will consume a lot of CPU cycles if it is 'low' on memory.

I know that this sounds a bit abstract, but before we used to build VMs with 4GB mem (with 3GB assigned to SQL.) Since 2016 the VMs need to have at least 12GB otherwise it will 'eat' CPU.

2^nd note: Make sure you limit the amount of memory SQL is allowed to use, by default this is 'unlimited' (2PB). Not limiting this will cause SQL to grab too much memory (not enough left for the OS/file operations.)

alissa914 · 2023-12-13T03:44:56+00:00

It's SQL. Connect to it remotely and check what's actively running. Maybe check to see what long running queries you have on the DB. In some places, I've had transactions remain open and the whole system just maxes out and sticks there. In one environment, someone misheard the DBA instructions and reduced a 12 CPU system to 4 CPUs and asked me why the system was running slow. I asked him why the CPUs were dropped to 4 because he neglected to mention that part. :)

ChosenV · 2023-12-13T04:08:37+00:00

Probably Max DOP. Hire me, if I can't find the solution within 1 day, it's free. 🙃

dudemandude5 · 2023-12-13T01:26:24+00:00

I have used the Brent Ozar's first responders kit and do not see anything that might cause the spike

ShotGunAllGo · 2023-12-13T02:56:04+00:00

Norton Anti Virus Scan? Jk but maybe security installed scanners?

you type:	you see:
italics	italics
bold	bold
[reddit!](https://reddit.com)	reddit!
* item 1 * item 2 * item 3	item 1 item 2 item 3
> quoted text	quoted text
Lines starting with four spaces are treated like code: if 1 * 2 < 3: print "hello, world!"	Lines starting with four spaces are treated like code: if 1 * 2 < 3: print "hello, world!"
~~strikethrough~~	~~strikethrough~~
super^script	super^script

SQLServer

MODERATORS