SQL Server Performance Forum – Threads Archive

Help Please – SQL Performance Issues

Hello All – I have taken over the aspect of finding out why are current SQL 2000 Server is causing headaches for the users. Meaning – both internal and external users state that the apps pulling data from SQL is extremely slow. The only thing I can tell is that when this happens – SQL is usually peggin the system at around 60 – 80% – No backups are taking place
It has dual nics – which are team.
It is an IBM x345 series – 2 Xeon 4GB memory
SQL 2000
Only about 150 – 200 users
Our current DBA states that she has ran a trace and sees nothing on the SQL side. Is there any app that I can run on the box to detect problems? I am trying to find out where the bottleneck is – Thanks for your help – Chris

KBAhttp://support.microsoft.com/?id=224587,http://support.microsoft.com/kb/835864 andhttp://sqljunkies.com/Article/7F8518F9-FDAA-4FF3-8FC5-25E8946C8D0C.scuk – take your time to go thru the links and collect the required data for further assessment. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided Ã¯Â¿Â½AS ISÃ¯Â¿Â½ with no rights for the sake of knowledge sharing.

Thank you for your reply – I have taken some screenshots – any ideas – this is mainly a gui for perfmon – Sql Performance Screen Shot
Thanks, Chris

with properly configured hardware, including storage and networks,
proper execution plans
pulling data from SQL Server 2000 is not a problem
700MB/sec+ in a table scan is possible almost always, people have serious storage performance deficiencies, more so with expensive SANs very frequently, there are serious deficiencies in the execution plans

Thank you for your reply – I hate to ask this – but could you explain a little more of what you mean – I am drawing a blank – Thanks, Chris

if looking over your chart
i would say the limitation is more likely to be on your client side but,
you have done no analysis as to what your problem is
it does not matter that SQL is 80-100% of the cpu being used,
the key is cpu is not maxed out, so what is the deal?
do you want your query to run faster or what

Thanks again for your reply – No what is happening is – during certain parts of the day and not every day – the SQL box gets hammered – users start complaining about slowness etc etc. Out current DBA – which I am not – states that is nothing on her side – that she has ran "traces" and is not coming up with anything. So of course, the hardware gets pointed at. So I am just trying to get the best logical way for plan of attack… Chris

what analysis does your dba have to support the fact that the heavy load delays are not the db server
as opposed to the app servers i suggest having a clean app server, not used by users
do one of the problem ops when the db is quiet
when the db is slow, do it again,
this will tell you whether it is the db or app server

]]>