SQL server very slow

[Jomercat]

Hi all,

We have a SQL server running a VB application developed by a company for us. For some odd reason when more than one or two computers connect to the system, the server performs slow.

I checked the CPU Usage and SQLservr.exe is using an average of 50 percent most of the time.

Does anybody have an idea why this is happening?

Desperatelly needing help, please.

Thanks in advance.

Jose.

 

[SQLDenis]

Run a trace with profiler also look into running perfmon


“I sense many useless updates in you... Useless updates lead to fragmentation... Fragmentation leads to downtime...Downtime leads to suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" --

http://sqlservercode.blogspot.com/

 

[Jomercat]

SQLDenis,

Thanks for the reply!!!

What should I look for when running the profiler and perfmon?

Thanks.

Jose.

 

[SQLDenis]

profiler:The queries that take the longest to complete

perfmon:
Memory usage Insufficient memory allocated or available to SQL Server will degrade performance. Data must be read from the disk continually rather than residing in the data cache. Windows NT 4.0 and Microsoft Windows® 2000 perform excessive paging by swapping data to and from the disk as the pages are needed.

CPU processor utilization A constantly high CPU rate may indicate the need for a CPU upgrade or the addition of multiple processors.

Disk I/O performance A slow disk I/O (disk reads and writes) will cause transaction throughput to degrade.


User connections An improperly configured number of users can cause the system to run slowly or restrict the amount of memory otherwise available to SQL Server.

Blocking locks A process may be forcing another process to wait, thereby slowing down or stopping the blocking process.


“I sense many useless updates in you... Useless updates lead to fragmentation... Fragmentation leads to downtime...Downtime leads to suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" --

http://sqlservercode.blogspot.com/

 

[Jomercat]

Is it normal to see many, many locks in the Locks/Process ID?

How about the Locks/Object? I see a lock for the master.dbo and the tempdb.dbo with a "##lockinfo 338" under the index column of the tempdb.dbo.

Is it normal that this lock remains there?

Thanks.

Jose.

 

[timscronin]

Yes, locks are normal, blocking would be an issue (will be highlighted in red)

 

[mrdenny]

What kinds of commands are people running that's making the CPU run at 50%? That's very high for only having a couple of people running queries. Run profiler and look for queries that have a very high CPU load.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com