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