Understanding the Importance of Memory Setting in SQL Server

Memory is one among the resources forming the performance triangle—CPU and storage being the other two. If one is hit, the other two take the load to try to bring the performance to acceptable levels, but there’s always the trade-off. Whatever transactions cannot be committed to the memory they would be forwarded to the disk subsystem by SQL Server. This causes a performance bottleneck. Therefore, the wait statistics can help identify performance issues on a SQL Server.

In this article, the following topics are discussed:

  1. Understanding internals of SQL Server memory setting and configuration
  2. The SQL Server memory and its impact on the database and application performance
  3. Discuss various SQL Server components that contribute to the memory usage
  4. Best practices and recommendation for memory sizing
  5. Multi-server memory report
  6. And more…

Read More

SQL Server Table Partitioning

Problem

In this article, we will focus on the demonstration of table partitioning. The simplest explanation of table partitioning can be called as dividing large tables into small ones. This topic provides scalability and manageability.

Why do we need table partitioning?

Assume that we have a table and it grows day by day. In this case, the table can cause some problems which need to be solved by the steps defined below:

  • Maintain this table. It will take a long time and consume more resources (CPU, IO etc.).
  • Back up.
  • Lock problems.

Read More

Microsoft SQL Operations Studio: Configuration and Querying

In my previous article, Microsoft SQL Operations Studio: Understanding and Installation, we discussed deeply the need for a new SQL Server development and administration graphical user interface tool that can replace the default SQL Server user interface tool, the SQL Server Management Studio. We discussed deeply the new features and the advantages of SQL Operations Studio over the SQL Server Management Studio tool and how to download and install it to the Windows machine.

In this article, we will see the different configuration of the SQL Operations Window and how we can perform our daily tasks with it.

Read More

Microsoft SQL Operations Studio: Understanding and Installation

SQL Server Management Studio is considered as the default integrated graphical user interface tool that has been used for many years to configure, manage, monitor and administrate the SQL Server instances hosted on the local machines, on remote servers or in the cloud by all SQL Server administrators and developers. It provides us with editing, debugging and deploying environment for many languages including T-SQL, XML, MDX and DMX languages. Due to the fact that Microsoft SQL Server can be installed now on the Linux platform, and that the SQL Server Management Studio tool is not compatible with any operating system outside Microsoft Windows, the need for a new cross-platform graphical user interface appears.

Read More

SQL Server – Dissect the Internals of sp_spaceused

This article is an effort to dissect the output of the sp_spaceused stored procedure.

Introduction

Understanding the database usage internals and the growth trends play a vital role in the defining the right sizing of the database. sp_spaceused is probably an administrator’s most widely-executed system stored procedure to find the disk space used by a database. This helps get a quick glimpse of the database usage. statistics. sp_spaceused is used to display the number of rows, the data size, index size, amount of used space, unused space by each object, and the unallocated size of the database. Although looking at the values given by sp_spaceused, one shouldn’t think of shrinking the database or data file or log file. Many a time, we are unaware of what we are doing. Many a time, we don’t know what would be the aftereffects of doing such resource intrinsic operations. The output of sp_spaceused tells us a lot about the current performance of the database. The unallocated column and the unused column tell us the free space left at the database and the table levels. Read More

Real-Time Operational Analytics and Non-Clustered Column Store Index

In this article, we will focus on real time operational analytics and how to apply this approach to an OLTP database. When we look at the traditional analytical model, we can see OLTP and analytic environments are separate structures. First of all, the traditional analytic model environments need to create ETL (Extract, Transform and Load) tasks. Because we need to transfer transactional data to the data warehouse. These types of architecture have some disadvantages. They are cost, complexity and data latency. In order to eliminate these disadvantages, we need a different approach.  Read More

SQL Server Triggers: Understanding and Alternatives

The SQL Server trigger is a special type of stored procedures that is automatically executed when an event occurs in a specific database server. SQL Server provides us with two main types of triggers: the DML Triggers and the DDL triggers. The DDL triggers will be fired in response to different Data Definition Language (DDL) events, such as executing CREATE, ALTER, DROP, GRANT, DENY, and REVOKE T-SQL statements. The DDL trigger can respond to the DDL actions by preventing these changes from affecting the database, perform another action in response to these DDL actions or recording these changes that are executed against the database. Read More