Tag: performance

How to Test Azure Web Application Performance through Microsoft Azure and Visual Studio

Amarendra Babu
How to Test Azure Web Application Performance through Microsoft Azure and Visual Studio
4.1 (82.22%) 9 votes

Before deploying your application into production, doing a performance load test is imperative for measuring future performance and ensure that your application is production-ready.

Testing is essential for every application to make sure that any application works and performs according to the desired requirements. During the application testing process, we can attempt and find out if any imperfections are remaining in the application. There are numerous sorts of testing like functional testing, unit testing, acceptance testing, and integration testing. We compose Functional and UI tests to see whether the application is working as per the requirements. Read More

Move Datafiles in SQL Server – Part 1

Kenneth Igiri
Move Datafiles in SQL Server – Part 1
3.6 (72.5%) 8 votes

Introduction

There are a number of situations which would warrant the movement of database files or transaction log files from one volume to another on the same server. These may include:

  1. The need to format the volume assuming it was not formatted properly when SQL Server was installed. Recall that when installing SQL Server, it is recommended that 64K allocation unit size is used to format the volumes. If this is not done at the point of installation and needs to be done later, it will obviously require preserving a backup of the database fist or creating a new, properly formatted volume and moving the database to this new volume.
  2. The need to use a new volume assuming the limits have been reached for the underlying storage. A good example would be the 2TB limit of a VMware Data Store. This is the case as of VSphere 5.0. Higher versions of VSphere have much higher limits.
  3. The need to improve performance by managing IO. One more reason you may want to move datafiles is performance. There are cases where a database is created with multiple datafiles all sitting on one disk until it becomes obvious, as the database grows, that the you have created a “hot region” in the storage layer. One solution would be creating new data files and rebuilding clustered indexes, another would be moving data files.

Read More

Basics of sys.dm_exec_requests

Esat Erkeç
Basics of sys.dm_exec_requests
4.4 (87.27%) 11 votes

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

Main Usage of sys.dm_os_wait_stats

Esat Erkeç
Main Usage of sys.dm_os_wait_stats
4.5 (90%) 6 votes

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.

Read More

How SQL Server 2016 Took Azure SQL Data Warehouse to Next Level

Savaram Ravindra
How SQL Server 2016 Took Azure SQL Data Warehouse to Next Level
Rate this post

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

Kenneth Igiri
Consolidating SQL Server Instance by Clustering and Stacking
3.7 (73.33%) 3 votes

Stacked SQL Cluster Instances

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

Read More

Main concept of SQL Server locking

Esat Erkeç
Main concept of SQL Server locking
3 (60%) 2 votes

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.

Read More

Choosing Azure SQL Purchase Model and Service Tier

Esat Erkeç
Choosing Azure SQL Purchase Model and Service Tier
4.3 (85%) 4 votes

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?

Read More

Monitoring TempDB Using Key Dynamic Management Views

Kenneth Igiri
Monitoring TempDB Using Key Dynamic Management Views
5 (100%) 1 vote

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.

Read More

Identifying and Fixing Forwarded Records Performance Issue

Ahmad Yaseen
Identifying and Fixing Forwarded Records Performance Issue
Rate this post

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.

Read More