Performance monitoring and troubleshooting in SQL Server is a vast topic. In SQL Server 2005, dynamic management views, also known as DMV’s, have been introduced and became an essential helping tool for diagnosing SQL Server performance problems. At the same time, we can use dynamic management views for Azure SQL Database. Some of them can differ from SQL Server on-premise database but the logic of work is still the same. Microsoft has very good documentation about dynamic management views. The only thing, you need to be careful about the version and product validation of dynamic management views. Read More
Tag: performance
Main Usage of sys.dm_os_wait_stats
As you know, the main responsibility of the database administrator lies in the monitoring of the SQL Server performance and intervening in determined time. You can find several SQL Server performance monitoring tools in the market but sometimes we need additional information about SQL Server performance to diagnosis and troubleshoot the performance issues. So we must have enough information about SQL Server Dynamic Management Views to handle issues about SQL Server.
Dynamic Management View (DMV) is a concept that helps us to discover SQL Server Engine performance metrics. DMV was first announced in SQL Server 2005 version and it continued in all versions of SQL Server afterward. In this post, we will talk about particular DMV whose database administrator must have enough information. This is sys.dm_os_wait_stats.
How SQL Server 2016 Took Azure SQL Data Warehouse to Next Level
Azure SQL DW (SQL Data Warehouse) is a massively parallel, petabyte-scale, cloud solution for data warehousing based on SQL. It is highly elastic and fully managed, allowing you to scale capacity in seconds and set up in minutes. You can scale computing and storage independently by yourself. It will enable you to burst computing of analytical workloads that are complex, or scale down your warehouse for archival scenarios and pay depending on what you are utilizing rather than lock yourself into cluster configurations that are predefined – and obtain better cost efficiency when compared with traditional data warehouse solutions. Read More
Consolidating SQL Server Instance by Clustering and Stacking
NOTES:
- Windows Failover Clustering comprising two nodes.
- Two SQL Server Failover Cluster Instances. This configuration optimizes the hardware. IN01 is preferred on Node1 and IN02 is preferred on Node2.
- Port Numbers: IN01 listens on port 1435 and IN02 listens on port 1436.
- High Availability. Both nodes back up each other. Failover is automatic in case of failure.
- Quorum Mode is Node and Disk majority.
- Backup LAN in place and routine backup configured using Veritas
Main concept of SQL Server locking
In this post, we will discuss the SQL Server lock mechanism and how to monitor SQL Server locking with SQL Server standard dynamic management views. Before we start to explain SQL Server lock architecture, let’s take a moment to describe what the ACID (Atomicity, Consistency, Isolation, and Durability) database is. The ACID database can be explained as database theory. If a database is called relational database, it has to meet Atomicity, Consistency, Isolation, and Durability requirements. Now, we will explain these requirements briefly.
Choosing Azure SQL Purchase Model and Service Tier
When we are beginning to think of migrating our on-premises databases to Azure SQL, we have to decide on a proper purchase model, a service tier, and a performance level. Before starting the Azure SQL migration process, we have to find logical and provable answers to the following questions:
- Which purchase model is suitable for my apps and business requirements?
- How much budget do I need?
- Which performance level meets my requirements?
- Can I achieve the acceptable performance of my apps?
Monitoring TempDB Using Key Dynamic Management Views
What is TempDB?
TempDB is a system database in Microsoft SQL Server used as a store of internal objects, row versions, work tables, temporary tables, and indexes. TempDB is available for use to all participants connected to a SQL Server instance (it is a global resource). For those familiar with other database flavors, the tempDB database is similar to the TEMP tablespace in Oracle. To put it mildly, anything that cannot fit your instance memory spills over to the tempdb data files.
Identifying and Fixing Forwarded Records Performance Issue
Before going through the Forwarded Records performance issue and resolving it, we need to review the structure of the SQL Server tables.
Table Structure Overview
In SQL Server, the fundamental unit of the data storage is the 8-KB Pages. Each page starts with a 96-byte header that stores the system information about that page. Then, the table rows will be stored on the data pages serially after the header. At the end of the page, the row offset table, that contains one entry for each row, will be stored opposite to the sequence of the rows in the page. This row offset entry shows how far the first byte of that row is located from the start of the page.
Monitoring Azure SQL Database with Azure SQL Analytics
The most important and challenging responsibility of a database administrator is monitoring performance metrics. Because monitoring performance and troubleshooting performance issues are considered to be difficult. For this reason, we need diagnostic and monitoring tools to measure performance counters and metrics. For Azure SQL there is a tool which is named SQL Analytics. With this tool, we can measure and monitor Azure SQL databases and elastic pools. At the same time, we can create alerts for notifications. SQL Analytics offers performance metrics in graphical form. In this article, we will learn how to enable Azure SQL Analytics. Read More
Azure SQL Database Automatic Tuning
Microsoft has recently announced an incredible new feature – automatic tuning in Azure SQL Database. To be honest, I am thoroughly impressed with this feature because Microsoft engineers have sophisticatedly used artificial intelligence in SQL Azure performance tuning. The aim is to monitor Azure SQL database and send these observations to the built-in intelligence service that generates some recommendations. They can be applied at offpeak times. This feature has also simplified the work of database administrators; they don’t have to worry about SQL Azure database performance now.