SQL Server Performance Forum – Threads Archive

Suggestions about performance?

I’m running SQL server 2005, and my perfmon stats look like: PhysicalDisk:
% Disk Read Time: 0.510
% Disk Write Time: 159.496
% Idle Time: 35.169
Avg. Disk Queue Length: 1.600 Process: sqlservr
% Processor Time: 18.750
Working Set: 1201704960 Processor:
% PRocessor Time: 5.471 SQLServer: Buffer Manager
Page life expectancy: 32460
Page reads/sec: 0.998
Page writes/sec: 15.969 SQLServer: Databases Total
Active Transactions: 2
Transactions/sec: 589.861 SQLServer General Statistics
Logins/sec: 296.428
Transactions: 2
User Connections: 211 SQLServer: Locks
Average Wait Time (ms): 17.273
Lock Requests/Sec: 28836.331 SQLServer: Memory Manager:
Connection Memory(Kb): 3526
SQL Cache Memory(kb): 12472
Total server Memory (kb): 1104896 SQLServer: Plan Cache
Cache Hit Radio: 78.988 SQLServer: SQL Statistics
Bach Requests/Sec: 588.863 System:
Context Switches/sec: 5508.366
Processor Queue Length: 0 Any suggestions on what I can do to optimize the setup?

I don’t see any issue in those counters. Luis Martin
Moderator
SQL-Server-Performance.com All in Love is Fair
Stevie Wonder
All postings are provided â€œAS ISâ€ with no warranties for accuracy.

Thanks for the help. Everything seems to be running smoothly on the server; but randomly the server locks jump up significantly and then the server starts rejecting logins and I have to restart the sql server; or reboot. Any clue on what can be causing that? Thanks,
Joe

Run Profiler to find long queries (duration, cpu) and try to optimize those queries.
Luis Martin
Moderator
SQL-Server-Performance.com All in Love is Fair
Stevie Wonder
All postings are provided â€œAS ISâ€ with no warranties for accuracy.

you are probably throttled on logins/sec,
start by enable connection pooling on you app server
afterward, you could probably benefit from consolidating calls to SQL, depending on what you are trying to accomplish with this app next, get the main database logs on to a dedicated disk see my site, www.qdpma.com/instructions for which perf counters are relevent, and which are not

]]>