In this article, we will describe SQL Server Profiler and how it can be used. In addition, we will mention the tools that are integrated with it- plans about its deprecation, and what are the alternatives?Read more »
There is a multitude of database metrics that we can collect and use to help us understand database and server resource consumption, as well as overall usage.
This data can include hardware statistics, such as measures of CPU or memory consumed over time. We can also examine database metadata, including row counts, waits, and deadlocks.Read more »
Database metrics can be collected, maintained, and used to help predict when processes go awry so problems can be resolved before they become severe. Understanding when an application or process misbehaves is not always easy. We are often left waiting until a server, application, or service breaks or enters an undesirable state before we know something is wrong. At that point, we are forced to rush and resolve a production problem quickly, before its impact becomes severe.Read more »
This article gives an overview of the KILL SPID command and how to monitor the rollback progress.Read more »
SQL Server Query Store is a performance monitoring tool that helps us evaluate the performance of a SQL query in terms of several different performance metrics such as CPU and Memory Consumption, execution time and the I/O cycles consumed by the query. Query store is similar to the windows “Task Manager”. A task manager provides information about the CPU, Memory, Network and Disc consumption of a process. Similarly, the Query Store provides insight to similar information.Read more »
A graph database is a type of NoSQL database that is based on graph theory. Graph databases are ideal for storing data that has complex many to many relationships. In this article, we will study the very basics of graph databases with the help of a simple example.Read more »
This article explores the usage of TempDB and different ways to shrink the TempDB database in SQL ServerRead more »
One shared characteristic among popular data visualization tools such as Power BI is the ease at which data can be extracted from a variety of disparate data sources, usually at a click of a button, as shown in Figure 1. Such convenience, though, tends to come at a cost as you often have little control over how background scripts used to extract data are generated. Yet, this should be of utmost concern for data architects and BI developers alike as rarely do you find auto-generated scripts that are efficient and optimal. In this article, join me as I put on my DBA hat and trace, monitor and review SQL batch statements that are auto-generated by the Get Data feature in Power BI using SQL Server Extended Events.Read more »
It is essential for the DBA to need to ensure the SQL Server database performance. Performance tuning is an open-ended task, and you need to ensure the monitoring of various database parameters.Read more »
CPU pressure slowing down the server
This article is the sequel in a series about SQL Server monitoring tools and common performance issues. Before reading this piece, it advisable to read the previous two articles about monitoring tools for disk I/O and memory performance:Read more »
In this article, we will review how to monitor AWS RDS SQL Server database instances and setup email notifications using event subscriptions and CloudWatch alarms.
In the previous articles of this series on the SQL Server Transaction Log, we discussed the importance of the SQL Server Transaction Log and the role that it plays in maintaining the database consistency, by ensuring that the committing transactions data will be preserved and the failed transaction will be rolled back. It also helps to recover the database to a specific point in time in case of system or hardware failure. This is achieved by writing a log record to the SQL transaction log file before writing the data pages to the physical data file, using Write-ahead Logging process.Read more »
Memory pressure slowing down queries
This article is the sequel in a series about SQL Server monitoring tools and common performance issues. The first article SQL Server monitoring tools for disk I/O performance is about how to detect and solve high input/output on hard disk subsystems when doing too much work during peak or maintenance times.
SQL Server trace flags are configuration handles that can be used to enable or disable a specific SQL Server characteristic or to change a specific SQL Server behavior. It is an advanced SQL Server mechanism that allows drilling down into a hidden and advanced SQL Server features to ensure more effective troubleshooting and debugging, advanced monitoring of SQL Server behavior and diagnosing of performance issues, or turning on and off various SQL Server features
The goal of this article is to get familiar with SQL Server monitoring tools and identify what some of the most common SQL Server performance problems are.Read more »
This is the continuation of the previous article SQL Server In-Memory database internal memory structure monitoring. In this article we are going to dissect the details of other components that are available to validate the SQL Server In-Memory objects.Read more »
We can use a variety of available options to keep systems performing at their very best. The SQL Server In-Memory OLTP technological advancement is a great innovation to relieve tempdb contentions. This feature is available with an additional advantage to monitor systems without incurring stress on the database servers.Read more »
We’re facing a challenge with several of our data flows that use more time than they have in the past and we’re not sure when this trend started. We know in the past month, our reports have been delayed by over a day from the start to the finish. For some of our data flows we use SQL Server Agent that calls SSIS packages or procedures, while some of them use a custom data import and reporting application we’ve created. How can we track the length of time for these data flows, since we’re using a combination of tools for importing data?
In the article, we will learn how to use the ‘iotop’ and ‘iostat’ commands with various configuration options.
SQL Server 2017 and SQL Server 2019 supports both the Linux operating system. As part of performance troubleshooting or monitoring resources in Windows system, we need to check certain things like disk space, CPU or Memory consumption, check running processes, uptime etc. We can get the information, on a windows based SQL server, from the task manager.
In this article, we’re going to through many of the tools we can use for monitoring SQL Server. SQL Server is chock-full of lots of good reports that allows a DBA to quickly spot whether there is any current performance bottleneck on the SQL Server. Many of these sit on top of DMVs but they give us a visually interactive way to look and work with the data. We’re going to start with SQL Server Performance Dashboard Reports.
Let’s jump into SQL Server Management Studio (SSMS) and the first thing we’re going to take you through out-of-the-box dashboard reports of all levels. They can be found by right-clicking the SQL Server instance in Object Explorer, and from the context menu, you’ll find Reports > Standard Reports:Read more »
Transparent Data Encryption (TDE) was originally introduced in SQL Server 2008 (Enterprise Edition) with a goal to protect SQL Server data at rest. In other words, the physical data and log files along with the database backup sitting on file system are protected (encrypted).Read more »
When maintaining SQL Server, it is essential to get an accurate perception of how busy it is. Two metrics that are often considered as indicators of how busy SQL Server is are Batch Requests/sec and Transaction/sec. When those metrics trend higher, they often affect all other metrics and make them go higher as well. While they could look similar, they are using a different type of starting point for measurement; the batches and transactions. So, to correctly understand those important metrics, lets first try to understand what the batches and transactions in SQL Server are and what are the differences between the twoRead more »
SQL Server indexes are essentially copies of the data that already exist in the table, ordered and filtered in different ways to improve the performance of executed queries. Seeks, scans and lookups operators are used to access SQL Server indexes.Read more »
Dynamic management views (DMVs) and dynamic management functions (DMFs) are system views and system functions that return metadata of the system state. On querying the related system objects, database administrators can understand the internals of SQL Server. It allows us to monitor the performance of the SQL Server instance, and diagnose issues with it.
SQL Server 2017 ships with a number of new and enhanced dynamic management views and dynamic management functions that will help DBAs monitor the health and performance of SQL Server instances. A few existing DMV’s such as sys.dm_os_sys_info and sys.dm_db_file_space_usage have been enhanced. Some have also been newly built and available only for SQL Server 2017.Read more »