Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
DBADave
Constraint Violating Yak Guru
366 Posts |
 Posted - 2003-10-09 : 14:46:29
|
| We've been receiving a lot of complaints about the performance of a database server. The server is running SQL 2000 on Windows 2000 and has 2GB of memory. I reviewed some perfmon counters and found the following:Counter AveragePages/sec .030Working Set 1.65 - 1.7GBLock Blocks 3065According to SQL Server 2000 Unleashed "Pages/sec" should be 0 and "Lock Blocks" should be 0. I'm not familiar with lock blocks, but doesn't SQL Server automatically allocate 2500 Lock Blocks? If so, how could the counter ever be 0? I suspect we have a memory issue, but I'm no expert with these counters. Please advise.Thanks, Dave |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-10-09 : 15:53:04
|
| I can check what we have on several different server, but I forget which object that I can find working set in. I found it in Process. But I think that you can do it on the whole server too. Which one are you using (let me know which object)?Tara |
 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-10-09 : 16:09:22
|
| lock blocks is the number of blocks allocated to support locks. The more locks you need the more will be allocated up to the max set. 0 sounds like an unlikely value.Try using the profiler to monitor queries. It will tell you what is taking a long time (although it will impact performance) then you can look atthose queries.Looking at general server performance won't tell you anything about the underlying problems and you could end up buying more hardware when all that's needed is an index somewhere.If you find the probelm is not with the database structure or queries then look at the memory and i/o subsystems and suchlike.You will probably find that either a single query is very heavy and stops everything else or that updates are blocking other updates and reads - depends what sort of system you have.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
DBADave
Constraint Violating Yak Guru
366 Posts |
Posted - 2003-10-14 : 10:41:58
|
| Tara, I'm using SQL Server:Memory Manager: Lock Blocksnr, are there any specific events I should trace to help isolate the problem? I suspect our problem is the result of some poorly designed views.Thanks, Dave |
|
|
|
|
|
|
Help with Lock Blocks