SQL Server is slow

Question

Post reply

SQL Server is slow

Roust_m

SSCoach

Points: 17410
More actions
March 1, 2010 at 4:27 pm

#145534

Hi,
We had a call from an overnight processes support guys saying that the process was very slow. CPU is almost flat, no disk queues, no blocks, no nothing that would indicate poor performance. After the instance restart it all started to run fast.
I am just wondering in this kind of situation where should I look? Any DMVs that would show some sort of memory saturation or whatever else wrong on the server?
Thanks.

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

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

M&M SSC-Insane Points: 21700 More actions · Answer 1

Please check the process.

Is the process running slower than normal?

If so, please check more on this process.

Could you share that process with us.

M&M

Roust_m SSCoach Points: 17410 More actions · Answer 2

The process is a big IS package. I can't post it as it may contain some sensitive information.

However I am wondering why after the instance restart the process started to run fast.

Are there any indicators on SQL Server side, e.g.: DMVs, which would show the problem is coming?

SQLRNNR SSC Guru Points: 281344 More actions · Answer 3

SSIS Performance Counters – SSIS provides several performance counters that you can use to gain visibility into how resources are utilized during package execution. For example, you can view the number of rows read and the number of buffers in use as the package executes. One specific performance counter that is particularly useful is Buffers Spooled. If Microsoft Windows® runs out of physical memory during package execution or the process executing the package runs out of virtual memory, SSIS begins to spool buffers to files. Once this occurs, performance will degrade significantly, so it is a good idea to monitor this setting and make sure that you have enough memory for your operations.

Source: http://msdn.microsoft.com/en-us/library/cc966529.aspx

Based on that, I would recommend Buffers Spooled.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Carl Federl One Orange Chip Points: 25384 More actions · Answer 4

Did you check for blocking before restarting SQL Server ?

SQL = Scarcely Qualifies as a Language

Roust_m SSCoach Points: 17410 More actions · Answer 5

Carl Federl (3/1/2010)
Did you check for blocking before restarting SQL Server ?

Yes I did, no blocking whatsoever...