Random slow performance on huge machine | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Random slow performance on huge machine

Hi,
I have an IBM Server with 6GB of RAM and 4-way XEON Processors of 1.5Ghz.
There is an optical array of drives and the data file is on a seperate logical
drive from the data file of the dbs.
The system described has 2 ethernet gigabit lan cards that clients connect to from
different locations. The problem is that a batch job that is run every day takes about 1.5-2h but if I
reset the server it takes 30-45minutes. Does anyone have a suggestion to make? I once saw an article on the web that says
that when a server has two lan cards the affinity mask of the sql server should
be changed from the default setting so that it uses less cpus or sth. Any help ould be appreciated. Thx,
Bill.
Do you use temp tables a lot in the batch process? What kind of an array is the tempdb on? I would be surprised if the affinity mask was causing this. Also, do you have anything running on the server that doesn’t need to be, such as a service that would consume resources? What is your available memory like after 2 days of running the server? Have you ran perfmon to discover any of this? Derrick Leggett
Also you can monitor the activity using PROFILER and see what other queries are overlapping. 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.
Just to be clear, are you saying that if you run the batch job, and it takes 1.2-2 hours, then immediately restart SQL Server, then run the batch job immediately, that it now runs in 30-45 minutes. If this is not exactly what you mean, please let us know exactly what steps you are taking to come up with your results. Also, how often do yo update statistics or rebuild your indexes in this database? —————————–
Brad M. McGehee, MVP
Webmaster
SQL-Server-Performance.Com
.. to add on, how about memory settings on SQL and any other applications sharing the resources on the server? 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.
I would use SQL Profiler to track the execution plans that are being created when your batch process is running slow. For such a difference in performance it almost sounds like SQL Server is choosing a non-optimal query plan when executing. Perhaps creating a parallel query plan where it would be better that it doesn’t. Since it is faster after a reboot, the limited data in CACHE right after a restart might be what allows SQL to choose a different plan. Alternatively you may have some other process on that server that is stealing resources from SQL Server. After a reboot this other process might not have allocated all of the resources it intends to take, only building them up after a period of time. The two ethernet cards should not play any role in raw performance of the server unless they are fighting with each other in routing of the network packets.
]]>