From: Gerhard on
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
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
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
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
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
>
> .
>