You snooze you lose... I lost....

I was working on a post about the new columns sql_memory_model, sql_memory_model_desc columns in DMV sys.dm_os_sys_info. These columns allow DBAs to programmatically identify if Lock Pages in Memory (LPIM) privilege is in effect at the service startup time.

I was setting it up, had one instance running with pages locked in memory and the other one without. Took some screenshots, then I noticed the MSSQL Tiger Team published the post
Developers choice: Programmatically identify LPIM and IFI privileges in SQL Server

So really there was no need for my post anymore. But what do I do with my beautiful screenshots?

Well not someone who likes to waste stuff, I decided to write just a couple of sentences and put the screenshots in this post and then link to the post by the MSSQL Tiger Team

Here is the query I ran to see instant file initialization is enabled, I ran this against two instances, one a SQL Server 2016 Service Pack instance, the other a SQL Server vNext instance

SELECT instant_file_initialization_enabled,* 
FROM sys.dm_server_services

You can see the output in the pic below, the vNext instance does not have instant file initialization enabled, the 2016 SP1 instance does have instant file initialization enabled

To see if Lock Pages in Memory is in use, you can run the following query

SELECT sql_memory_model,sql_memory_model_desc
FROM sys.dm_os_sys_info

You can see the output in the pic below, the vNext instance does not use Lock Pages in Memory, the 2016 SP1 instance does use Lock Pages in Memory

The sys.dm_os_sys_info DMV also has a new column that SQL Server 2016 SP1 does not have.. process_physical_affinity


To get all the details about this stuff in the pictures go read the post
Developers choice: Programmatically identify LPIM and IFI privileges in SQL Server published by the MSSQL Tiger Team

46 New Dynamic Management Views In SQL Server 2008 CTP6

Remember the 33 new DMVs in SQL Server 2008 post? I was just poking around in SQL Server 2008  CTP6 and wanted to know if there were any new Dynamic Management Views since CTP5. There are 13 new DMVs in CTP6 and there are a total of 45 new Dynamic Management Views compared to SQL server 2005. The list of them is below, I think their names make clear what the dmv is supposed to do (or at least I think I know)

dm_audit_actions
dm_audit_class_type_map
dm_cdc_errors
dm_cdc_log_scan_sessions
dm_cryptographic_provider_algorithms
dm_cryptographic_provider_keys
dm_cryptographic_provider_properties
dm_cryptographic_provider_sessions
dm_database_encryption_keys
dm_db_mirroring_auto_page_repair
dm_db_mirroring_past_actions
dm_db_persisted_sku_features
dm_exec_procedure_stats
dm_exec_trigger_stats
dm_filestream_oob_handles
dm_filestream_oob_requests
dm_fts_fdhosts
dm_fts_index_keywords
dm_fts_index_keywords_by_document
dm_fts_outstanding_batches
dm_fts_parser
dm_os_dispatcher_pools
dm_os_dispatchers
dm_os_memory_brokers
dm_os_memory_node_access_stats
dm_os_memory_nodes
dm_os_nodes
dm_os_process_memory
dm_os_spinlock_stats
dm_os_sys_memory
dm_resource_governor_configuration
dm_resource_governor_resource_pools
dm_resource_governor_workload_groups
dm_server_audit_status
dm_sql_referenced_entities
dm_sql_referencing_entities
dm_tran_commit_table
dm_xe_map_values
dm_xe_object_columns
dm_xe_objects
dm_xe_packages
dm_xe_session_event_actions
dm_xe_session_events
dm_xe_session_object_columns
dm_xe_session_targets
dm_xe_sessions



The 13 DMVs listed below are new in CTP6

dm_audit_actions
dm_audit_class_type_map
dm_db_persisted_sku_features
dm_exec_procedure_stats
dm_exec_trigger_stats
dm_fts_fdhosts
dm_fts_index_keywords
dm_fts_index_keywords_by_document
dm_fts_outstanding_batches
dm_fts_parser
dm_os_dispatchers
dm_os_memory_node_access_stats
dm_server_audit_status

If you want to find out what the names of all the new DMVs are by running a query on your SQL Server 2008 box and you don't have a linked server to a SQL Server 2005 box then don't worry I have prepared the query below for you.

 
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')

Enjoy exploring these DMVs

 

 

DMVStats (A SQL Server 2005 Dynamic Management View Performance Data Warehouse ) Released

Over the last year, Tom Davidson has been working on a tool called DMVstats with some of his CAT colleagues. DMVstats collects performance oriented DMVs into a data warehouse, and provides a methodology called 'Waits' and 'Queues' to identify and track down performance issues. Drill-through analysis is provided by reporting services reports.

DMVStats 1.01
A SQL Server 2005 Dynamic Management View Performance Data Warehouse

Introduction
Microsoft SQL Server 2005 provides Dynamic Management Views (DMVs) to expose valuable information that you can use for performance analysis. DMVstats 1.0 is an application that can collect, analyze and report on SQL Server 2005 DMV performance data. DMVstats does not support Microsoft SQL Server 2000 and earlier versions.

Main Components
The three main components of DMVstats are:
• DMV data collection
• DMV data warehouse repository
• Analysis and reporting.
Data collection is managed by SQL Agent jobs. The DMVstats data warehouse is called DMVstatsDB. Analysis and reporting is provided by means of Reporting Services reports.

Download it here: http://www.codeplex.com/sqldmvstats/