SQL Server is slow

Question

Post reply

SQL Server is slow

Chitown

Hall of Fame

Points: 3239
More actions
July 29, 2022 at 6:09 am

#4069804

I have a user who is complaining about a job he is running from the application server is slow. The server has 110 GB of memory allocated to SQL Server out of 128 GB. The job runs in 2 minutes everyday but today it is taking hours. CPU is staying below 10%, not much activity on the server itself, no blocking so what else should I be looking at? When I open up SSMS, it takes time, expanding DBs is taking a little longer... Any help is highly appreciated .
September 7, 2022 at 12:48 am

This was removed by the editor as SPAM

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

deubel_m SSC-Addicted Points: 465 More actions · Answer 1

Hello Chitown,

1. look at the SQL Server Errorlog,

2. look at the Windows Eventlog,

3. look at the application server and the application itself.

4. Execute (if possible) the statement of the job manually

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 2

My first action would be to have someone check the CPU and I/O on the given application server. Then, check the code.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Meet George Jetson SSChampion Points: 11626 More actions · Answer 3

Is the action, coming from the application server, a query that can be ran manually ON the actual SQL server? This will let you see how quickly SQL is returning the result set, which will in turn eliminate one of the two servers.

Chris Powell

George: You're kidding.
Elroy: Nope.
George: Then lie to me and say you're kidding.

quickdraw SSC Enthusiast Points: 151 More actions · Answer 4

Check the waits that are occurring during the query when it is ran directly on the server (eliminate variable of the application).

If you get CXPACKET waits (waiting to recombine results from different parallel threads), then check the max degree of parallelism (MaxDOP) on the server and try setting that to 1 for the query and see if it improves.

Allocating only 18Gb of memory for Windows is not advised. Try giving it 30Gb+

MMartin1 One Orange Chip Points: 27795 More actions · Answer 5

I am a little risk averse when it comes to touching maxDop. After all, this query was running fine it was reported until this one time.

It is important to determine if this is a SQL Server issue or Windows server issue. Hareder when there are there other applications installed instead of a dedicated server

Though this is probably not a issue anymore but if expaning folders in SSMS was taking time I really would wonder about cpu was not a issue triggered by perhaps a regressed query, depeding on if the Job has a t-sql step. Here query store helps.

----------------------------------------------------