In the previous part of this series, we described why tracking most expensive queries is important and how to use SQL Server Management Studio native solutions. In this part, we will focus on dynamic management views and functions.
Monitoring and diagnosing SQL Server performance requires monitoring performance metric values, but also understanding these metrics and their relation to other metrics, knowing metric normal values, monitoring resource intensive processes and queries, etc.
Efficient SQL Server performance monitoring includes monitoring operating system, SQL Server, and database performance metrics. In How to monitor your SQL Server instances and databases, we presented tools for monitoring the latter two performance metrics, In this article, we will present tools that provide operating system performance metrics monitoring.
When it comes to monitoring SQL Server performance, there are a few native SQL Server solutions that provide out of the box performance monitoring. We have written about some of them here: A DBA guide to SQL Server performance troubleshooting – Part 2 – Monitoring utilities.
Monitoring SQL Server instances and databases provides information necessary to diagnose and troubleshoot SQL Server performance issues, as well as to fine tune SQL Server performance. Optimal performance is not easy to define and set, as there is usually a trade-off between multiple software and hardware factors. It also depends on your environment, business requirements, and company policy.
In this article we will describe how to create a SQL database across multiple SQL Servers from multiple scripts by using PowerShell, the SQLCMD utility, SQL Server Management Studio, and ApexSQL Build.
Sometimes, it is necessary to export or import large amounts of data into or out of a database. Those operations are called bulk export and import operations.
The reasons for auditing events on SQL Server instances and their objects are various. Some of the most common are to provide accurate records, ensure a protected and safe environment, provide quality control, or to meet certain compliance regulation (e.g. Basel II, HIPAA, PCI, FERPA, GLBA, and SOX) requirements. The auditing process should provide information on specific event types that contains the time, user, object that’s affected by the event, and other relevant data that can help in reconstructing the event.
SQL Server logins are the credentials that enable users to connect to the Database Engine instance. SQL logins are distinguished based on the type of authentication method: Windows-authenticated, SQL Server–authenticated, Certificate, and Asymmetric key. One SQL login can be mapped to only one user in each database.
SQL Database refactoring becomes more complicated according to the degree of coupling in the database architecture; the more coupled things are coupled the greater the potential impact of any change. The simplest way to describe the concept of coupling is that if changing one module in one module of code or a program requires changes in another part of the same or external application, then coupling exists