SQL Server Performance

Batch Requests

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by melvinlusk, Aug 26, 2009.

  1. melvinlusk Member

    I have a SQL Server 2005 Enterprise server running SP3. The server has about 35 databases on it, and is a publisher in a transactional replication setup. Two seperate boxes act as the distributor and subscriber. The setup is completely 32-bit with Windows Server 2003 Enterprise. The publisher, distributor, and subscriber all have their own active/passive cluster.
    The publisher is a 2.8 Ghz dual-processor quad-core server with 8 GB of RAM. The server almost never exceeds more than 75 users; these users connect through 6 webservers that host an ASP.NET application.
    The CPU utilization on this server is incredibly high; it hovers around 50% utilization during the day, sometimes spiking up to 85%. Profiler doesn't show very many high-duration transactions over 2 seconds. Perfmon shows an average number of 1150 Batch Requests/sec. I think this is the cause of the extreme CPU usage, since this seems incredibly high. My question is, how can I identify where all of these batch requests are coming from? I have a hard time grasping 75 users doing that much work, and I dont' think replication is the cause. Surely a server with this kind of horsepower should be able to handle this number of users.
    Ideas?
  2. MohammedU New Member

    Some times low duration CPU with in high number of execution may cuase the CPU spikes and also proc/query recompilations...
    First run the sql trace with cpu filter >100 and see the what are the procedures/queries cuasing it and tune them...and also check if you can cache some of the result set on web server if the results are static...
    And also check through the task manager, how much % CPU consumed by SQL, it could be something else...
  3. melvinlusk Member

    You're right in that the CPU usage is probably due to a high number of executes and not duration.

    The SQL Server service is what's using the majority of the CPU, not another process.
    If I run a trace with a CPU filter > 100, all I'm doing is seeing what is using more than 100 milliseconds of CPU cycles, not the number of executes, right? This shouldn't give me much more information than looking for durations....

Share This Page