:::: MENU ::::

Posts Categorized / Dynamic Management Views DMV

  • Oct 01 / 2014
  • 0
dbDigger, Dynamic Management Views DMV, Monitoring and Analysis, SQL Server IO, T-SQL Scripts

Get number of reads and writes for each database on SQL Server

We can get the number of read and write operations for each database on our server. This information id being fetched by a DMV so analysis data depends upon up time of server.

 -- total I/O for each database  
 SELECT name AS 'Database Name'  
 ,SUM(num_of_reads) AS 'Number of Read'  
 ,SUM(num_of_writes) AS 'Number of Writes'   
 FROM sys.dm_io_virtual_file_stats(NULL, NULL) I  
 INNER JOIN sys.databases D   
 ON I.database_id = d.database_id  
 GROUP BY name ORDER BY 'Number of Read' DESC;  
  • May 07 / 2009
  • 0
dbDigger, Dynamic Management Views DMV, Monitoring and Analysis, T-SQL Scripts

Using sys.dm_exec_requests to monitor slow response of SQL Server

It is very important for a DBA to monitor the response time of SQL Server to connected users. Minute details like read, write and resource consumption may be analyzed later but first priority should be to point out slow response time. Several DMVs and other system data may be utilized to monitor the slow response of SQL Server. For this post I have

 
USE master   
 GO   
 SELECT start_Time,DB_NAME(database_id),st.TEXT,  
 [Status], command, wait_type ,wait_time,  
 wait_resource, last_wait_type,  
 CASE transaction_isolation_level  
 WHEN 0 THEN 'unspecified'   
 WHEN 1 THEN 'ReadUncomitted'  
 WHEN 2 THEN 'ReadCommitted'  
 WHEN 3 THEN 'Repeatable'  
 WHEN 4 THEN 'Serializable'  
 WHEN 5 THEN 'Snapshot'  
 END AS [TRANSACTION ISOLATION],executing_managed_code   
 FROM sys.dm_exec_requests CROSS APPLY sys.dm_exec_sql_text(sql_handle) st 
 ORDER BY wait_type   
 GO  

sys.dm_exec_requests result
The field [wait_type] is important if some wait type is given for any transaction. And it become critical if wait type is due to any lock. Such wait types will begin with LCK prefix. T-SQL statement being used is also given, so DBA may get a clue that which statement is experiencing slow response.
Above script just provides a clue. If you note any problem then further fields from sys.dm_exec_requests may be included and other ways to track down the problem may be used.
For details of fields in sys.dm_exec_requests please click here
As a DMV is being used in above script so it is obvious that above script will be used for SQL Server 2005 and later.

  • Apr 17 / 2009
  • 1
dbDigger, Dynamic Management Views DMV

Dynamic management views (DMVs) in SQL Server 2005 and later

Dynamic management views (DMVs) return information on server state or database state that can be used for monitoring the health of a server instance and databases, and diagnose performance problems. Dynamic management views (DMVs) can be identified by their name, which begins with ”dm_,” plus an abbreviation of what category the DMV is a part, then a description of what the view returns. For example, dm_db_file_space_usage is part of the database category of DMVs and returns information on file space usage.

  • Apr 16 / 2009
  • 1
dbDigger, Dynamic Management Views DMV, Indexes, Monitoring and Analysis, Performance Tunning and Optimization

Get information about missing indexes in SQL Server 2005 and onwards databases

Indexes are core of optimization and performance of DBMS. Keen attention is required while planning indexes. Fortunately SQL Server contains a suggestion mechanism for creating indexes where required. While optimizing the submitted queries, Query optimizer notes down the columns where indexes are missing and their creation may boost the performance. First let us view the script to retrieve the missing indexes information in SQL Server 2005 databases

SELECT DB_NAME(database_id) AS [DATABASE],
equality_columns, inequality_columns,
included_columns, STATEMENT
FROM sys.dm_db_missing_index_details
ORDER BY 1

Result of above script will be generated in five columns.

  • [Database] is name of database where missing index is detected
  • [equality_columns] is list of columns that are used in equality comparison and index is required on these.
  • [inequality_columns] is list of columns that are used for inequality comparison and index is required on these.
  • [included_columns] is list of columns that are used in query for other than comparison (e.g. Select List). And covering index on these is suggested.
  • [Statement] is name of table along with column where index is missing

It is important to mention that this information is geather since the time when SQL Server Service starts. Existing information about missing indexes will be vanished with service stop/restart. So DBA should backup missing indexes information time to time for later analysis.
Also note that all this information is based on queries submitted to SQL Server query engine either by user or application. So there may be other objects where indexes are required but these are not suggested because of not being used in scripts.
For deep and detailed monitoring/analysis i would suggest Database Engine Tuning Advisor.

  • Nov 05 / 2008
  • 1
dbDigger, Dynamic Management Views DMV, Monitoring and Analysis

System Views in SQL Server 2005 for metadata

System views are new for SQL Server 2005. System views in SQL Server 2005 are designed to expose instance and database related metadata in an organized and useful method. Some system tables from SQL Server 2000 are now implemented in SQL Server 2005 as system views for backward compatibility. Although the SQL Server 2000 system tables can still be queried by name, SQL Server 2005 features and related metadata will not be seen. Thus, the results may be different from those when querying the corresponding new system view.
There are many SQL Server 2005 system tables that do not have system views for accessing data from them, such as the backup and restore history tables. In those cases, the data must be accessed by querying the system table itself.
System base tables are the underlying tables that actually store metadata for a specific database. System base tables are used within SQL Server 2005 Database Engine and are not intended for customer user. Therefore, the system views are provided for accessing that metadata without accessing the base tables.
All of the system objects referenced by the system views are physically persisted in the system base tables stored within the read only system database called Resource. This database is not visible to users and does not appear in SQL Server Management Studio. Users cannot use or connect to it, unless in single-user mode.
All system views are contained in either the INFORMATION_SCHEMA or the sys schemas. Both schemas logically appear in every database.
There are six collections of system views:
  • catalog
  • compatibility
  • dynamic management
  • information schema
  • replication
  • notification services
There are numerous categories of system views within each collection. Following are descriptions of each of the collections:

Catalog views
Catalog views return information that is used by the Database Engine, such as information on objects, databases, files, security, and more. They do not contain information about backups, replication, database maintenance plans, or SQL Server Agent catalog data.

Compatibility views
Compatibility views are provided for backward compatibility only with SQL Server 2000 system tables. They do not expose any SQL Server 2005 new feature metadata, such as partitioning. Use the new catalog views instead.

Dynamic management views (DMVs)
Dynamic management views (DMVs) return information on server state or database state that can be used for monitoring the health of a server instance and databases, and diagnose performance problems. Dynamic management views (DMVs) can be identified by their name, which begins with ”dm_,” plus an abbreviation of what category the DMV is a part, then a description of what the view returns. For example, dm_db_file_space_usage is part of the database category of DMVs and returns information on file space usage.

Information schema views
Information schema views are system views that are part of a separate schema, called INFORMATION_SCHEMA. Returns metadata for database objects in a particular database. All other system views are part of the sys schema.

Replication views
Replication views return information about replication. They are created when a database is configured as a publisher or subscriber, and different views are created in the different databases: msdb, distribution, publisher database, and subscriber database. Otherwise, these views will not exist. Using replication stored procedures is still a good way to access replication metadata.

Notification services views
Notification services views return instance and application data specifically related to Notification Services; designed to help with debugging, tracking, or troubleshooting.

Consult us to explore the Databases. Contact us