sys.dm_db_index_operational_stats
in [Server Configuration]
|
From: Gerhard on 4 May 2010 12:40 I am a .net programmer, but sometimes get SQL Server DBA tasks as we don't have a DBA. There is a situation where the database seems to lock up occasionally. Below is a row from sys.dm_db_index_operational_stats, where the row_lock_wait_in_ms is very high. Can you please give me some clues as to what would cause this? Also, can you please give me a straightforward reference that would help someone like myself who is not a DBA by trade as to what best practices are to maintain a database? Thanks. database_id 15 object_id 1709353254 index_id 1 partition_number 1 leaf_insert_count 267 leaf_delete_count 0 leaf_update_count 11002 leaf_ghost_count 0 nonleaf_insert_count 6 nonleaf_delete_count 0 nonleaf_update_count 0 leaf_allocation_count 6 nonleaf_allocation_count 0 leaf_page_merge_count 0 nonleaf_page_merge_count 0 range_scan_count 42549 singleton_lookup_count 1927482989 forwareded_fetch_count 0 lob_fetch_in_pages 0 lob_fetch_in_bytes 0 lob_orphan_create_count 0 lob_orphan_insert_count 0 row_overflow_fetch_in_pages 0 row_overflow_fetch_in_bytes 0 column_value_push_off_row_count 0 column_value_pull_in_row_count 0 row_lock_count 36485380 row_lock_wait_count 27 row_lock_wait_in_ms 259362 page_lock_count 653129249 page_lock_wait_count 17 page_lock_wait_in_ms 262845 index_lock_promotion_attempt_count 1439219 index_lock_promotion_count 65531 page_latch_wait_count 842 page_latch_wait_in_ms 267 page_io_latch_wait_count 17 page_io_latch_wait_in_ms 531
From: TheSQLGuru on 4 May 2010 13:06 IIRC these are cumulative stats, and given the numbers I would say your server has been up for a very long time. 260 seconds of total wait for kajillions of hits really isn't that bad in the grand scheme of things. But I believe the counter you ask about could be the amount it is simply because other actions (insert/update/delete) have the row/page/table locked when someone tryes to take the specific row lock out. -- Kevin G. Boles Indicium Resources, Inc. SQL Server MVP kgboles a earthlink dt net "Gerhard" <msnews08(a)nospam.nospam> wrote in message news:5E5FAEDA-EDE2-4C21-B24A-44A9C0553668(a)microsoft.com... >I am a .net programmer, but sometimes get SQL Server DBA tasks as we don't > have a DBA. There is a situation where the database seems to lock up > occasionally. Below is a row from sys.dm_db_index_operational_stats, > where > the row_lock_wait_in_ms is very high. Can you please give me some clues > as > to what would cause this? Also, can you please give me a straightforward > reference that would help someone like myself who is not a DBA by trade as > to > what best practices are to maintain a database? Thanks. > > database_id 15 > object_id 1709353254 > index_id 1 > partition_number 1 > leaf_insert_count 267 > leaf_delete_count 0 > leaf_update_count 11002 > leaf_ghost_count 0 > nonleaf_insert_count 6 > nonleaf_delete_count 0 > nonleaf_update_count 0 > leaf_allocation_count 6 > nonleaf_allocation_count 0 > leaf_page_merge_count 0 > nonleaf_page_merge_count 0 > range_scan_count 42549 > singleton_lookup_count 1927482989 > forwareded_fetch_count 0 > lob_fetch_in_pages 0 > lob_fetch_in_bytes 0 > lob_orphan_create_count 0 > lob_orphan_insert_count 0 > row_overflow_fetch_in_pages 0 > row_overflow_fetch_in_bytes 0 > column_value_push_off_row_count 0 > column_value_pull_in_row_count 0 > row_lock_count 36485380 > row_lock_wait_count 27 > row_lock_wait_in_ms 259362 > page_lock_count 653129249 > page_lock_wait_count 17 > page_lock_wait_in_ms 262845 > index_lock_promotion_attempt_count 1439219 > index_lock_promotion_count 65531 > page_latch_wait_count 842 > page_latch_wait_in_ms 267 > page_io_latch_wait_count 17 > page_io_latch_wait_in_ms 531 > > >
From: Gerhard on 4 May 2010 17:56 Thanks for your feedback, it was helpful. Can you recommend a site with the basic best practices for maintaining a server? It is being backed up nightly, then checked for database integrity, updates statistics and maintenance cleanup task. Weekly I am doing a shrink database and reorganize index. I would appreciate being pointed in the right direction on other best practices. Like how often should the server be restarted, how to reduce huge log files (especially from Sharepoint), and any other regular tasks. Thanks again for your assistance. "TheSQLGuru" wrote: > IIRC these are cumulative stats, and given the numbers I would say your > server has been up for a very long time. 260 seconds of total wait for > kajillions of hits really isn't that bad in the grand scheme of things. > > But I believe the counter you ask about could be the amount it is simply > because other actions (insert/update/delete) have the row/page/table locked > when someone tryes to take the specific row lock out. > > -- > Kevin G. Boles > Indicium Resources, Inc. > SQL Server MVP > kgboles a earthlink dt net > > > "Gerhard" <msnews08(a)nospam.nospam> wrote in message > news:5E5FAEDA-EDE2-4C21-B24A-44A9C0553668(a)microsoft.com... > >I am a .net programmer, but sometimes get SQL Server DBA tasks as we don't > > have a DBA. There is a situation where the database seems to lock up > > occasionally. Below is a row from sys.dm_db_index_operational_stats, > > where > > the row_lock_wait_in_ms is very high. Can you please give me some clues > > as > > to what would cause this? Also, can you please give me a straightforward > > reference that would help someone like myself who is not a DBA by trade as > > to > > what best practices are to maintain a database? Thanks. > > > > database_id 15 > > object_id 1709353254 > > index_id 1 > > partition_number 1 > > leaf_insert_count 267 > > leaf_delete_count 0 > > leaf_update_count 11002 > > leaf_ghost_count 0 > > nonleaf_insert_count 6 > > nonleaf_delete_count 0 > > nonleaf_update_count 0 > > leaf_allocation_count 6 > > nonleaf_allocation_count 0 > > leaf_page_merge_count 0 > > nonleaf_page_merge_count 0 > > range_scan_count 42549 > > singleton_lookup_count 1927482989 > > forwareded_fetch_count 0 > > lob_fetch_in_pages 0 > > lob_fetch_in_bytes 0 > > lob_orphan_create_count 0 > > lob_orphan_insert_count 0 > > row_overflow_fetch_in_pages 0 > > row_overflow_fetch_in_bytes 0 > > column_value_push_off_row_count 0 > > column_value_pull_in_row_count 0 > > row_lock_count 36485380 > > row_lock_wait_count 27 > > row_lock_wait_in_ms 259362 > > page_lock_count 653129249 > > page_lock_wait_count 17 > > page_lock_wait_in_ms 262845 > > index_lock_promotion_attempt_count 1439219 > > index_lock_promotion_count 65531 > > page_latch_wait_count 842 > > page_latch_wait_in_ms 267 > > page_io_latch_wait_count 17 > > page_io_latch_wait_in_ms 531 > > > > > > > > > . >
From: Erland Sommarskog on 4 May 2010 18:08 Gerhard (msnews08(a)nospam.nospam) writes: > Can you recommend a site with the basic best practices for maintaining a > server? It is being backed up nightly, then checked for database > integrity, updates statistics and maintenance cleanup task. I'm afraid that I cannot recommend any specific site, but there are a couple of books out there. One is by Ron Coolidge which is specifically by administration. No, I have not read it myself. But you'll get some tips right here and now: > Weekly I am doing a shrink database and reorganize index. Index reorganization/rebuild is a good thing - even better if done selectively. Shrinking on a regular basis on the other hand is utterly bad. Shrinking a database is a very exceptional operation. > Like how often should the server be restarted, Only when needed to apply patches from Windows etc. > how to reduce huge log files (especially from Sharepoint), Backup you transaction logs regularly. -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
From: Gerhard on 4 May 2010 19:12 Thanks for the tips. "Erland Sommarskog" wrote: > Gerhard (msnews08(a)nospam.nospam) writes: > > Can you recommend a site with the basic best practices for maintaining a > > server? It is being backed up nightly, then checked for database > > integrity, updates statistics and maintenance cleanup task. > > I'm afraid that I cannot recommend any specific site, but there are a > couple of books out there. One is by Ron Coolidge which is specifically > by administration. No, I have not read it myself. > > But you'll get some tips right here and now: > > > Weekly I am doing a shrink database and reorganize index. > > Index reorganization/rebuild is a good thing - even better if done > selectively. Shrinking on a regular basis on the other hand is utterly > bad. Shrinking a database is a very exceptional operation. > > > Like how often should the server be restarted, > > Only when needed to apply patches from Windows etc. > > > how to reduce huge log files (especially from Sharepoint), > > Backup you transaction logs regularly. > > > > -- > Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se > > Links for SQL Server Books Online: > SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx > SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx > SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx > > . >
|
Next
|
Last
Pages: 1 2 Prev: creating a connection in VisualStudio Next: sp_delete_backuphistory, how does it work |