Prev: creating a connection in VisualStudio
Next: sp_delete_backuphistory, how does it work
From: Uri Dimant on 5 May 2010 13:32
Gerhard
sys.dm_db_index_operational_stats view is giving you information about ther
indexes even they are not used at all. If you experience with locking I
thibk you need another DMV to look at.
--By applying the filter in the where clause you get the answers to
questions like:

---- What SQL Statement is causing the lock?

--- Which user has executed the SQL statement that's holding the locks?

--- What objects/tables are being locked?

--- What kinds of locks are being held and on which pages, keys, RID's?





SELECT L.request_session_id AS SPID,

DB_NAME(L.resource_database_id) AS DatabaseName,

O.Name AS LockedObjectName,

P.object_id AS LockedObjectId,

L.resource_type AS LockedResource,

L.request_mode AS LockType,

ST.text AS SqlStatementText,

ES.login_name AS LoginName,

ES.host_name AS HostName,

TST.is_user_transaction as IsUserTransaction,

AT.name as TransactionName,

CN.auth_scheme as AuthenticationMethod

FROM sys.dm_tran_locks L

JOIN sys.partitions P ON P.hobt_id = L.resource_associated_entity_id

JOIN sys.objects O ON O.object_id = P.object_id

JOIN sys.dm_exec_sessions ES ON ES.session_id = L.request_session_id

JOIN sys.dm_tran_session_transactions TST ON ES.session_id = TST.session_id

JOIN sys.dm_tran_active_transactions AT ON TST.transaction_id =
AT.transaction_id

JOIN sys.dm_exec_connections CN ON CN.session_id = ES.session_id

CROSS APPLY sys.dm_exec_sql_text(CN.most_recent_sql_handle) AS ST

WHERE resource_database_id = db_id()

ORDER BY L.request_session_id





"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: TheSQLGuru on 5 May 2010 17:14
The best thing I can recommend by far is to have a professional come in and
give your system a review and help you get things set up correctly. You
should get some good mentoring out of this too so you will be better able to
do your job.

If you want to roll your own, I highly recommend Ola Hallengren's
maintenance stuff: ola.hallengren.com

--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net


"Gerhard" <msnews08(a)nospam.nospam> wrote in message
news:658DF909-6847-4196-B4E6-2B7015937AC6(a)microsoft.com...
> 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
>> >
>> >
>> >
>>
>>
>> .
>>


First  |  Prev  | 
Pages: 1 2
Prev: creating a connection in VisualStudio
Next: sp_delete_backuphistory, how does it work