Sql Dynamic Managment views

dynamic_management_views_HOW to

 This is  very elemental only and is provided as a lamen view of troublshooting with this tool.  This is a very powerful feature that I would love to see a very good break down on on  how to make this feature easy. I suppose the answer is just to use it every day. Ah how nice it would be to do one thing every day!

Driving down the dirt road with dynamic management views:

 After looking at DMV. I think you have to know exactly what you are looking for before you use them. You may find something in the event log that gives you something specific. In this case you may need a “how to” to do this on the fly.

 A list of the DMV’s:

http://sqlblog.com/blogs/denis_gobo/archive/2008/02/21/5188.aspx

  1. Then you need to start basic by pulling up the desired view from the list. Right click the database and click new query inside SQL Management Studio; then type the following:

Select * from sys.dm_ Place_your_title_from_the_list (Putting SYS.DM is essential!)

With tab completion you just need to know to type sys.dm_ .  Do you get it/ we are just using tab completion to bring up a query for the right value.

You will get a list of the possible items you can pull up. Here are the major categories:

sys.dm_exec_* – provide information about execution of .NET CLR Modules and connections. All contained here are a number of views available to help you drill into issues related to execution of queries.

  1. sys.dm_os_* – report on memory, locks, and execution scheduling.
  2. sys.dm_trans_* – provide insight into transactions and isolation.
  3. sys.dm_io_*. – monitoring disk I/O
  4. Sys.dm_db_* – provide database-level data.

 Here is an example of things you might pull up:

http://www.pythian.com/news/1305/browsing-sql-server-2008s-new-dmvs/

 To make a free form query you may start with a format for a specific database. I am writing the query based on the column names in the results below:

select sys query 

I want to see the lock request /second for all instances – example (line 133)

The query would look like

Select instance_name,

Cntr_value, ‘lock request/sec’

From sys.dm_os_performance_counters

Where counter_name = ‘lock request/sec’

The cleaned up query looks like:

Select instance_name,

Cntr_value, ‘lock request/sec’

From sys.dm_os_performance_counters

Where counter_name = ‘lock request/sec’

Of course retuns no results.

Trying another one: I just searched until I found one that turned a lot of results:

select * from sys.dm_os_memory_cache_counters

 

select * from sys.dm_os_memory_cache_counters

 arbitrartily decided I wanted to see single pages over 100k- see below:

select name,

single_pages_kb ‘Memory over 100k’

 from sys.dm_os_memory_cache_counters

where single_pages_kb > ‘100’

 4

 You can see the result in the window. Here is a good article about the subject. This person is much more knowledgable on the subject, but she did give me the courage to delve in and try to understand. Tab completion does make this easier and you do get used to it. Just learn to memoruze the sys. to begin the tabbed search feature.

http://www.simple-talk.com/sql/t-sql-programming/troubleshooting-with-dynamic-management-views/

THE END

BONUS SP_configure – What is it!!

http://www.sp-configure.com/sp_configure-command/

USE master

go

EXEC sp_configure ‘show advanced option’,’1′

RECONFIGURE

http://www.tek-tips.com/faqs.cfm?fid=6042

Some examples in the link above – the commands for SP_configure below

http://www.sp-configure.com/sp_configure-command/

http://msdn.microsoft.com/en-us/library/ms189631.aspx

For a list of settings :

SELECT * FROM sys.configurations

ORDER BY name ;

GO

Running stored procedures:

exec sp_

type this in the query window for a list

Good list of stored procedures

List of 2008 DMV

use master

go

SELECT * FROM sysobjects

WHERE name LIKE ‘dm_%’

AND name NOT IN(‘dm_os_hosts’,

‘dm_exec_cached_plan_dependent_objects’,

‘dm_os_memory_allocations’,

‘dm_os_loaded_modules’,

‘dm_db_task_space_usage’,

‘dm_os_memory_objects’,

‘dm_os_schedulers’,

‘dm_os_threads’,

‘dm_exec_requests’,

‘dm_db_missing_index_columns’,

‘dm_repl_tranhash’,

‘dm_qn_subscriptions’,

‘dm_db_session_space_usage’,

‘dm_exec_query_optimizer_info’,

‘dm_tran_top_version_generators’,

‘dm_os_waiting_tasks’,

‘dm_exec_background_job_queue’,

‘dm_exec_cursors’,

‘dm_db_missing_index_details’,

‘dm_clr_properties’,

‘dm_os_sublatches’,

‘dm_exec_query_memory_grants’,

‘dm_tran_current_snapshot’,

‘dm_io_virtual_file_stats’,

‘dm_exec_xml_handles’,

‘dm_os_wait_stats’,

‘dm_exec_text_query_plan’,

‘dm_broker_connections’,

‘dm_os_stacks’,

‘dm_os_ring_buffers’,

‘dm_db_missing_index_groups’,

‘dm_exec_cached_plans’,

‘dm_exec_sessions’,

‘dm_broker_forwarded_messages’,

‘dm_os_memory_clerks’,

‘dm_repl_articles’,

‘dm_fts_memory_buffers’,

‘dm_fts_index_population’,

‘dm_tran_current_transaction’,

‘dm_os_child_instances’,

‘dm_exec_connections’,

‘dm_exec_background_job_queue_stats’,

‘dm_fts_active_catalogs’,

‘dm_tran_database_transactions’,

‘dm_os_memory_cache_clock_hands’,

‘dm_repl_schemas’,

‘dm_db_mirroring_connections’,

‘dm_db_index_operational_stats’,

‘dm_db_partition_stats’,

‘dm_io_pending_io_requests’,

‘dm_os_memory_cache_entries’,

‘dm_os_virtual_address_dump’,

‘dm_tran_transactions_snapshot’,

‘dm_exec_query_plan’,

‘dm_os_memory_cache_hash_tables’,

‘dm_exec_query_stats’,

‘dm_clr_tasks’,

‘dm_os_worker_local_storage’,

‘dm_db_index_usage_stats’,

‘dm_db_index_physical_stats’,

‘dm_os_buffer_descriptors’,

‘dm_tran_active_snapshot_database_transactions’,

‘dm_tran_active_transactions’,

‘dm_db_file_space_usage’,

‘dm_broker_activated_tasks’,

‘dm_broker_queue_monitors’,

‘dm_os_memory_cache_counters’,

‘dm_tran_session_transactions’,

‘dm_clr_appdomains’,

‘dm_exec_sql_text’,

‘dm_os_memory_pools’,

‘dm_os_latch_stats’,

‘dm_io_backup_tapes’,

‘dm_fts_memory_pools’,

‘dm_os_sys_info’,

‘dm_tran_locks’,

‘dm_exec_query_transformation_stats’,

‘dm_exec_query_resource_semaphores’,

‘dm_repl_traninfo’,

‘dm_db_missing_index_group_stats’,

‘dm_fts_population_ranges’,

‘dm_os_performance_counters’,

‘dm_os_workers’,

‘dm_io_cluster_shared_drives’,

‘dm_os_tasks’,

‘dm_exec_plan_attributes’,

‘dm_tran_version_store’,

‘dm_os_cluster_nodes’,

‘dm_clr_loaded_assemblies’)

About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s