SQL Server Performance Forum – Threads Archive
Slow Performance
Hello I have sql server 2005 x64 SP1 on a server with win 2003 x64, 2 dual core opterons, and 4 gb of memory. There are no other services running on this server except for SQL 2005. The four processors are constantly above 70% and the free memory is 100 mb. SQL 2005 is configure to use a minimum of 1 gb of memory and a max of 3 gb. I stop all sql jobs and even took sql 2005 offline and the performance remains the same. Please help!!! My users are hunting me down and I am cowering in the server room. -DBA_Rookie TheeWhat kind of queries you’re running and how about the index defragmentation methods in this case? A poorly designed database or a system that is improperly configured for the workload are but several of many possible causes of this type of performance problem. SQL Server 2000 exposes some of this information though system tables such as sysprocesses, but sometimes you need to generate a physical dump of the SQL Server process memory to extract relevant information from internal structures.
Check the resource bottlenecks: CPU, memory, and I/O bottleneck, Tempdb bottlenecks and a slow running user query. The poor choice of a query plan can tax system resources and cause an overall slowdown of the workload. So, if a large table is missing a useful index, or the query optimizer decides not to use it, this not only causes the query to slow down but it also puts heavy pressure on the I/O subsystem to read the unnecessary data pages and on the memory (buffer pool) to store these pages in the cache. Similarly, excessive recompilation of a frequently running query can put pressure on the CPU. 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.
learn to use the PSSDIAG and Read80Trace (from RML, maybe Read90Trace for 2005) search the terms PSSDIAG or RML on the MS site
or get Quest Coefficient or some other third party product if you do not collect the right information,
then you are just guessing blindly
which is more likely to cause harm than help
See if paging is occuring on server. Look for below counters: Pages / second
Access Methods -> Full Scans / seconds
Memory Available MBytes
Working set size for processes Processor is it more on user time or privileged time. If SQL is high on CPU
-> Compilations / sec or recompilations /sec.
-> if compilcation / sec is high then check for batches / sec if both follow same trend then it should be adhoc queries are more.
Set database property to forced parameterization on
-> If recompilation is more see subclass event to check reason for recompilation -> If priv time is more then it should IO or paging in server
Cap max server memory to 3 Gigs and let OS have breating space and see performance. Use tools like
PSSDiag
ReadTrace (RML)
IOSim
http://www.microsoft.com/downloads/…6A-28C2-4483-8293-76FFF67B9EB3&displaylang=en
http://support.microsoft.com/kb/231619
]]>