What could be slowing machine | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

What could be slowing machine

Hi A Client has a machine :
4 x 3ghz Zeon
4 gb Memory
Windows Enterprise Server 2003
SQL Server Enterprise Sp3
(it also has some form of Raid which I am waiting for more details on)
The db is about 1gb on a 20gb drive with 16gb free. which takes 191 seconds to run a particular query. This machine is doing nothing else apart from running SQL Server, not even Anti Virus software. My local machine :
1 x 3ghx P4
1 gb Memory
Windows XP Pro Sp2
SQL Server Personnal edtion Sp3 takes 73 seconds to run exactly the same query. Both machines have the same database on them and the query was run through Query Analyser. On paper the client’s machine should be upto 4x faster rather than almost 3x slower. The client has had a 3rd party check the machine who say it is working fine. Does anybody have any ideas what could be causing this kind of difference?

i feel check in the profiler by running a trace.
Also check the task manager by any chance resources are being used.
check the performance monitor which is handy in this case.
For further asssesment capturing PERFMON counters will give more indepth idea to resolve.
KBAhttp://support.microsoft.com/default.aspx?scid=kb;en-us;243589 for more information. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
does the query in question show a parallel execution plan?
each parallel plan needs to be verified individually,
try running at different settings for OPTION (MAXDOP x)
some will show good gains, even better than linear, other will show degradation

Run performance monitor on both machine while query is executing.. also either restart both machine prior to executing or to clear the buffers, run below two DBCC commands.. DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE MemoryAvailable MBytesMemoryPages/sec
Paging File _total
SQL Data disk : Avg. Disk Queue Length
SQL Data disk : Disk Reads/sec
SQL Data disk : Disk Writes/sec
SQL Log disk : Avg. Disk Queue Length
SQL Log disk :Disk Reads/sec
SQL Log disk :Disk Writes/sec
Processor(_Total)% Processor Time
SQLServer:Buffer ManagerBuffer cache hit ratio
SQLServer:Buffer ManagerProcedure cache pages
SQLServer:General StatisticsUser Connections
SQLServer:Locks(_Total)Number of Deadlocks/sec
SystemContext Switches/sec
SystemProcessor Queue Length
Also in SQL profiler, select "Execution plan" from Performance event category.. this will help to understand better..how query is being optimized by SQL Engine. Check for Parallel Execution settings as given linkhttp://support.microsoft.com/default.aspx?scid=kb;en-us;243589 says : – Parallel Execution If you are using a multiprocessor computer, you can also investigate whether a parallel plan is in use. If parallelism is in use, you see a PARALLELISM (Gather Streams) event. If a particular query is slow when it is using a parallel plan, you can try forcing a non-parallel plan by using the OPTION (MAXDOP 1) hint. See the "SELECT (T-SQL)" topic in SQL Server 7.0 Books Online for more details.
Deepak Kumar –An eye for an eye and everyone shall be blind
Many thanks for all the responses. Embarassingly it turns out I didn’t have the same database , but one that was three weeks old. Once I had the uptodate database the timings were pretty much identical.
Ensure to maintian regular database consistency checks and update statistics to maintin optimum performance. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
]]>