Logon Triggers in SQL Server

Logon Triggers in SQL Server
5 (100%) 1 vote

A logon trigger, as the name suggests, is a trigger that fires in response to a LOGON event in SQL Server.

In simple terms, a logon trigger fires whenever someone tries to establish a new connection to a database server. The trigger fires after the user authentication and the login phase completes but before the user session is actually initiated. Read More

Planning of Disk Space for Databases

Planning of Disk Space for Databases
Rate this post

Do you think about something when you create a new database? I guess that most of you would say no, since we all use default parameters, though they are far from being optimal. However, there is a bunch of disc settings, and they really help to increase system reliability and performance. Read More

Monitoring TempDB Using Key Dynamic Management Views

Monitoring TempDB Using Key Dynamic Management Views
4.5 (90%) 2 votes

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

Read-only Routing for an Always On

Read-only Routing for an Always On
4.5 (90%) 2 votes

As DBAs, we generally come across our clients complaining that the current Production Server is not able to hold the load on the server and whether the load may be balanced with the Secondary Server. This is possible with a database in DR Server with Read-only database in Log Shipping and Secondary SQL Server replicas in Always On Availability Group. The biggest advantage of Always On Groups is that it allows us to set up group level HA for any number of databases and we can create up to four secondary replicas and this is a combination of Clustering, Log Shipping and Database Mirroring where the data transmission is more flexible and functional.

Read More

Identifying and Fixing Forwarded Records Performance Issue

Identifying and Fixing Forwarded Records Performance Issue
3.7 (73.33%) 3 votes

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

How to Use Default and Custom Widgets in SQL Server Operations Studio

How to Use Default and Custom Widgets in SQL Server Operations Studio
2.7 (53.33%) 3 votes

There are already a number of articles and blog posts that reveal the benefits of the SQL Operations Studio tool. The demand created across the software and the related tools are relatively unimaginable than ever before. The trend and growth will continue to increase in the upcoming days.

The graphical representation is in the vogue today. Visualizing data helps us to better understand this and to make decisions. It’s no wonder that data visualization continues to attract a growing number of users. The development of any toolset could provide opportunities to speed up the software development lifecycle process.

This article demonstrates the advantages of using custom SQL queries or complex T-SQL to provide a great insight into the database and explains how one can use this insight to build custom widgets. In this case, the SQL Operations Studio Widget is a customized piece of the code to personalize the SQL Server Dashboard for effective management of SQL instances.

Read More

The Art of Aggregating Data in SQL from Simple to Sliding Aggregations

The Art of Aggregating Data in SQL from Simple to Sliding Aggregations
Rate this post

Let us start our SQL journey to understand aggregating data in SQL and types of aggregations including simple and sliding aggregations.

Before we jump to the aggregations, it is worth considering interesting facts often missed by some developers when it comes to SQL in general and the aggregation in particular.

In this article, SQL refers to T-SQL which is the Microsoft version of SQL and has more features than the standard SQL. Read More

Calculating Running Total with OVER Clause and PARTITION BY Clause in SQL Server

Calculating Running Total with OVER Clause and PARTITION BY Clause in SQL Server
4.4 (88.33%) 12 votes

You often come across scenarios where you have to calculate a running total of a quantity.

A running total refers to the sum of values in all cells of a column that precedes the next cell in that particular column.

Let’s take a look at an example to make this clearer.

Read More