Sunday, May 28, 2023

What are Dynamic Management Views in SQL Server? Part - 2

6. DMVs for system information

It may be recognized by the prefix "sys.dm_os_"

Examples are sys.dm_os_wait_stats and sys.dm_os_memory_clerks.


7. DMVs with High Availability

Using the prefix "sys.dm_hadr_"

Examples are sys.dm_hadr_database_replica_states and availability_replica_states.


8. DMVs for Extended Events:

Using the prefix "sys.dm_xe_"

Examples are sys.dm_xe_sessions and sys.dm_xe_database_session_events.


9. DMVs for CLR Integration:

It may be recognized by the prefix "sys.dm_clr_"

Examples are sys.dm_clr_appdomains and sys.dm_clr_properties.


10. DMVs for filestreams:

The prefix "sys.dm_filestream_" serves as an identifier.

Examples include sys.dm_filestream_non_transacted_handles and sys.dm_filestream_file_io_handles.






What are Dynamic Management Views in SQL Server? Part - 1

What are Dynamic Management Views in SQL Server? Part - 3

What are Dynamic Management Views in SQL Server? Part - 4

Saturday, May 27, 2023

What are Dynamic Management Views in SQL Server? Part - 1

Introduction:

SQL Server's Dynamic Management Views (DMVs) are potent diagnostic tools that offer helpful details on the operation and behaviour of your SQL Server instance. DMVs give database managers and programmers the ability to acquire data in real-time on numerous facets of the SQL Server environment. This article will examine several DMV categories, their uses, applications, samples, explanations, frequent queries with purposes, and the advantages they provide.


Understanding Dynamic Management Views: 

Dynamic Management Views are specialized system views that reveal specific details about SQL Server's internal operations. They provide a plethora of diagnostic and monitoring data that may be accessed using conventional SQL commands, and they are kept as virtual tables within the master database. Performance monitoring, resource utilization, query execution, index optimization, and many other topics are all covered by DMVs.


Benefits of DMVs:

1. Performance Monitoring and Troubleshooting: DMVs give administrators thorough insights into the operation of SQL Server instances, allowing them to quickly spot and resolve problems. Administrators may identify bottlenecks and improve system performance by looking at key performance indicators including CPU consumption, memory utilization, I/O statistics, and query execution plans.

2. Query Analysis and Optimisation: Developers may discover wasteful queries and improve their performance by using DMVs to obtain visibility into the execution plans of queries. DMVs like sys.dm_exec_query_stats and sys.dm_exec_query_plan may be analyzed by developers to evaluate indexing tactics, evaluate query performance, and optimize queries.

3. Index tuning: DMVs offer useful data on index utilization, index fragmentation, and index gaps. In order to improve database performance, administrators can find underutilized indexes, spot fragmentation problems, and optimize index structures by running queries against the sys.dm_db_index_usage_stats and sys.dm_db_missing_index_details tables.

4. Resource Utilization Monitoring: Monitoring of resource consumption, including CPU, memory, disc I/O, and network utilization, is provided by DMVs in real-time. Administrators can efficiently distribute system resources by monitoring resource-intensive processes, locating performance bottlenecks, and querying sys.dm_os_ring_buffers and sys.dm_os_wait_stats.

Based on their features and purposes, Dynamic Management Views (DMVs) in SQL Server may be divided into a number of categories. Here are the main categories of DMVs and the symbols used to identify them:


1. DMVs for performance:

Prefixed with "sys.dm_os_" or "sys.dm_exec_"

Examples are given below:-

a) sys.dm_exec_query_stats:

Provide data on the overall performance of SQL Server's cached query plans. Within the cached plan, the view comprises one row per query line, and the rows' lifespan is dependent upon the plan. The rows for that plan are deleted from this view when it is taken out of the cache.


Advantages of sys.dm_exec_query_stats:

i. Identifies Queries with High Resource Consumption: By examining data from sys.dm_exec_query_stats, we may locate queries with high resource consumption, such as those that consume a lot of CPU time or carry out several logical or physical reads. This makes it possible to concentrate optimization efforts on the questions that will have the greatest overall system effect.

ii. Enhances the execution of plans: The statistics in sys.dm_exec_query Developers can access and examine the query execution plans related to certain queries by using the execution plan handles provided by DMV. Developers can find ineffective join methods, missing indexes, and wasteful index utilization by evaluating execution plans. With this information at hand, they may optimize query execution strategies for increased performance.

iii. Assessing Query Modifications: Developers can assess the effects of their optimizations by comparing statistics from sys.dm_exec_query_stats before and after making modifications to queries. This gives them the ability to evaluate the success of their changes and, if required, make more.

SELECT TOP 5 @@ServerName AS [ServerName], @@Version AS [Version],ServerProperty('Edition') AS [Edition],

SUBSTRING(t.text, (s.statement_start_offset/2) + 1, ((CASE s.statement_end_offset WHEN -1 THEN DATALENGTH(t.text) 

ELSE s.statement_end_offset END - s.statement_start_offset)/2) + 1) AS [Query],

s.execution_count AS [No. Of Execution],s.total_worker_time AS [TotalWorkerTime],

s.total_logical_reads AS [TotalLogicalReads],s.total_physical_reads  AS [TotalPhysicalReads],

s.creation_time AS [CreationTime]

FROM sys.dm_exec_query_stats AS s

CROSS APPLY sys.dm_exec_sql_text(s.sql_handle) AS t

ORDER BY s.total_worker_time DESC;


According to the overall worker time, we extract the top 5 inquiries in this case. The query retrieves the text of the query, the number of executions, the number of workers, the number of logical reads, the number of physical reads, and the creation time. This data offers an insightful understanding of the query performance traits.


b) sys.dm_os_sys_info:-

The dynamic management view sys.dm_os_sys_info in Microsoft SQL Server offers details about the server's hardware setup and operating system. It includes various system-level data that may be used for monitoring and troubleshooting.

The following are some significant specifics that the sys.dm_os_sys_info view can offer:

Version, edition, and build information for SQL Server are all included in the display.

Information about the operating system: It gives specifics about the OS installed on the server, including the OS version, architecture (32- or 64-bit), and the number of CPUs.

It provides details about the actual hardware.


Advantages of sys.dm_os_sys_info:

i. Accurate system information: We may get accurate and current information about our system using sys.dm_os_sys_info like operating system version, CPU architecture, and the amount of physical memory accessible. These are just a few of the attributes that this DMV shows. We may learn a lot about the infrastructure that underlies our SQL Server setup by using this view.

ii. Get System Details: System information may be retrieved quickly, but doing so frequently requires deep dive into numerous levels of administrative tools and parameters. Sys.dm_os_sys_info, on the other hand, streamlines this procedure by offering a centralized and complete view of vital system information. Database administrators and system engineers may use this DMV effectively during troubleshooting and to improve system performance.

iii. Performance monitoring and troubleshooting: This plays an important part at the time of performance monitoring and troubleshooting. We can get details of important system parameters like the number of logical processors, system clock tick rate, and maximum level of parallelism by querying this DMV. With this knowledge, we can spot possible bottlenecks, adjust system settings, and maximize resource use.


Examples of how to use sys.dm_os_sys_info

1. Obtaining operating system information

We can use the following command to get information about the OS:

SELECT * FROM sys.dm_os_sys_info;

The results of this query will include details on the system architecture, service pack level, and version of Windows.


2. Analyzing memory configuration

To analyze memory configuration, we can use the following query:

SELECT @@ServerName AS [ServerName],GetDate() AS [CurrentDate],

physical_memory_in_bytes/1024/1024 AS [Total Physical Memory (MB)],

virtual_memory_in_bytes/ 1024 / 1024 AS [Total Virtual Memory (MB)],

max_server_memory / 1024 AS [Maximum Server Memory (MB)]

FROM sys.dm_os_sys_info;

We will get the total amount of physical memory, total amount of virtual memory, and maximum server memory configuration, all in megabytes (MB), from this query.

3 Checking system CPU details:

Use the following command to get the details of the CPU on the server:

SELECT @@ServerName AS [ServerName],GetDate() AS [CurrentDate],

CPU_count AS [Logical CPU], hyperthread_ratio AS [Hyperthread Ratio],

CPU_count / hyperthread_ratio AS [Physical CPU]

FROM sys.dm_os_sys_info;

Details of the CPU


In addition to calculating the number of physical CPUs, this query obtains the hyper thread ratio and the number of logical CPUs.


2. DMVs specific to databases:

It may be recognized by the prefix "sys.dm_db_"

Examples are sys.dm_db_file_space_usage and sys.dm_db_index_usage_stats.


3. Query and Execution Plan DMVs:

Has the prefix "sys.dm_exec_" in its name.

Examples are sys.dm_exec_cached_plans and sys.dm_exec_sessions.


4. DMV locks and blocks:

Assigned a prefix of "sys.dm_tran_"

Examples are sys.dm_tran_locks and sys.dm_tran_database_transactions.


5. Store DMV queries:

The prefix "sys.query_store_" serves as an identifier.

Examples are sys.query_store_plan and sys.query_store_runtime_stats





What are Dynamic Management Views in SQL Server? Part - 2

What are Dynamic Management Views in SQL Server? Part - 3

What are Dynamic Management Views in SQL Server? Part - 4

Friday, May 26, 2023

What are differences between VirtualBox, VMWare Workstation, Hyper-V, Virtual PC, Parallels Desktop, and Boot Camp

Introduction:

Many virtualization options are available in the market for running different operating systems on a single computer. The main distinctions between VirtualBox, VMWare Workstation, Hyper-V, Virtual PC, Parallels Desktop, and Boot Camp are discussed in this article. we can choose wisely based on our individual needs and requirements by comprehending their distinctive characteristics and functionalities.

VirtualBox: 

VirtualBox is an open-source virtualization tool with cross-platform interoperability that was created by Oracle. On a variety of operating systems, including Windows, macOS, Linux, and Solaris, it enables users to build and execute virtual machines. A variety of capabilities are available with VirtualBox, including support for USB devices, virtual networking, and snapshot management. It is suitable for individual users and businesses because of its user-friendly interface and wide range of customization options.

Software and Hardware Prerequisites:

depends on the demands of the guest OS, sufficient RAM, and CPU resources.
enough capacity to accommodate virtual machine files.

Primary Qualities:

1. Compatibility across platforms.
2. Management of snapshots for preserving and regaining machine states.
3. Using virtual networking, host and guest computers can communicate without interruption.
4. Support for USB devices for peripheral connection to virtual computers.

Advantages:

1. Both open-source and free.
2. Wide support for operating systems.
3. Numerous opportunities for customization.
4. Community activity and frequent updates.

Disadvantages:

1. More difficult for beginners to use.
2 Guest additions must be manually installed for improved operation.

Benefits:

1. A virtualization solution that is economical.
2. Suitable for both private and professional use.
3. Versatile for sandboxing, running several OSs, and testing applications.

Performance:

1. Provides adequate performance for the majority of virtualized activities.
2. With applications that need a lot of resources, performance may suffer.

Featured Post

Use DBCC SQLPerf (logspace)

 Use DBCC SQLPerf (logspace) to monitor and optimize database performance in SQL Server. Let's Explore: Let's Explore: https://mades...

Popular Posts