How to analyze SQL Server database performance using T-SQL

The performance of a database is the most challenging and critical part of database optimization. The challenge every DBA faces is to identify the most resource-intensive databases. This article talks about the natively available features that can accomplish the task of getting the required details of the entire database at a granular level.

Using the techniques and methods baked right into SQL, one can collect the aggregate information at the instance level. The instance level data sometimes might be helpful when you want to get an insight at a higher level. When we work with mission-critical systems, the breakdown at a granular level and detailed information on CPU, Memory, and I/O is always helpful. There are few metrics that are important, and I’m going to discuss those shortly.

This article describes on how to measure the various performance characteristics of databases. Learn about how:

  1. In-lines T-SQL’s module for each performance metrics
  2. Details the use of SQL 2017 STRING_AGG string function
  3. Includes the use of DMF sys.dm_db_log_info
  4. Display consolidated data using T-SQL
  5. and more …

What database metrics do I monitor?

SQL Server Performance Monitoring revolves around many key areas

  • CPU
  • Memory
  • Disk I/O
  • Also, the factors such as user connections, database transaction rate, and data and log file settings

These factors give an overview of its impact on the performance of the application. This article is an effort to understand the general characteristics of databases; it gives an overview of the key factors used to classify the databases as critical, medium and low usage databases.

There are many tools available to track the database usage details, which are listed below:

  • DMV’s
  • SQL Profiler
  • Counters
  • Activity Monitor
  • Windows Perfmon
  • Third party tools

SQL Server bundles various monitoring options but there will be cases when you would want to turn to third party tools. The article outlines the details the using native SQL techniques to identify the most resource-intensive databases of an SQL instance. The following SQL’s are tested on SQL 2012-2017

Further reading…

https://www.sqlshack.com/analyze-sql-server-database-performance-using-t-sql/

Happy Learning!!

 

Posted in SQL Server 2017, sysfiles, T-SQL | Leave a comment

SQL Server 2017

SQL 2017

 

Happy Learning!!

Posted in SQL Server 2017 | Leave a comment

Overview of Resumable Indexes in SQL Server 2017

Managing indexes is a critical component of database maintenance but we often don’t think about the indicators behind the index maintenance operations. SQL Server 2017 (CTP 2.0) introduces a very useful index feature, to mitigate the administration overhead of index maintenance which we’ll review and discuss in this article.

This article outlines

  • Overview of the indicators of disk space issues during heavy index rebuilding maintenance
  • Index maintenance options available in SQL 2017
  • How to use index_resumable_operations
  • Application of these settings on SQL partitions
  • A demonstration
  • And more…

Background

Being a database administrator, understanding the implication of fragmentation takes precedence over fixing it. Often we don’t think about fragmentation details, and the proceeds to rebuild (or defrag) every index in the database. This operation eats up a significant amount of transaction log space and has a substantial impact on the system resources, backup size, restoration time, etc. Finding the Fill Factor parameter and setting it to a right value works great to deal with fragmentation, but in many cases, finding this value is a tedious job.

Rebuilding indexes require additional disk space, and an inadequate disk space can negatively impact the I/O, and degrade system performance; sometimes the entire operation may fail.

The index rebuild operation typically has an option to sort the results in a system database called “TempDB”. The option, SORT_IN_TEMPDB, is set to OFF by default for each index. When using this option, one must ensure that there is enough room to expand TempDB. If not, we must at least make sure that there is enough room on the disk on which the user database resides.

If the index rebuild maintenance operation fails due insufficient disk space, the operation has to be started from the beginning. In many instances, administrators struggle to manage the load on the system resources and wish to pause and resume the operation at a later point.

Further reading….

Overview-resumable-indexes-sql-server-2017

Happy Learning!!

 

Posted in SQL Indexes, SQL Server 2017 | Tagged , | Leave a comment

How to use Python in SQL Server 2017 to obtain advanced data analytics

On the 19th of April 2017, Microsoft held an online conference called Microsoft Data Amp to showcase how Microsoft’s latest innovations put data, analytics and artificial intelligence at the heart of business transformation. Microsoft has, over the last few years, made great strides in accelerating the pace of innovation to enable businesses to meet the demands of a dynamic marketplace and harness the incredible power of data—more securely and faster than ever before.

After the conference, there were a few questions some of us had, though. Is Microsoft SQL Server 2017 emerging as an enterprise solution for data science? Does it provide the required capabilities—is the engine capable of handling huge data? It seems the answer is “Yes”, as starting with the CTP 2.0 release of SQL Server 2017, Microsoft has brought Python-based intelligence to data in SQL Server.

Python has gathered a lot of interest recently as a language of choice for data analysis. This language has the right set of libraries for data analysis and predictive modeling, not to mention a simpler learning curve.

The growing trends of data science and modeling predict a massive growth in data in the upcoming years. The propulsion towards innovation and adaptation to leading trends in the data technology might intrigue us enough to make us take a look at the current release of SQL Server 2017.

Data science is a combination of Data Mining, Machine Learning, Analytics and Big Data. The integration of SQL 2016 with data science language, R, into the database the engine provides an interface that can efficiently run models and generate predictions using SQL R services. Python builds on the foundation laid for R Services in SQL Server 2016, and extends that mechanism to include Python support for in-database analytics and machine learning.

further reading…….

https://www.sqlshack.com/how-to-use-python-in-sql-server-2017-to-obtain-advanced-data-analytics/

Happy Learning!!

 

 

Posted in Python, SQL Server 2017 | Leave a comment

Top string functions in SQL Server 2017

SQL Server 2017 has been in the talk for its many features that simplify a developer’s life. With the previous versions, developers had to write T-SQL, or user-defined functions using temporary tables, and build complex logic, just for string manipulation. In almost every database I work with, I see many user-defined functions for string manipulation and string aggregation.

This article outlines the basic concepts of how to use the new string function in SQL Server 2017 on a Linux machine.

The SQL Server 2017 CTP 1.1 contains various string function out of the box, a few of them being:

  • CONCAT_WS, TRANSLATE, and TRIM
  • Support for the WITHIN GROUP clause for the STRING_AGG function.

A collection of new string function in SQL Server 2017, such as TRANSLATE, TRIM, CONCAT_WS, STRING_AGG are almost similar to the string functions of other relational databases engines.

As developers, we try to achieve results in simple and smart ways. The string functions available in SQL Server 2017 make the life of a developer much simpler.

So, let us get started, and see the usage of the available string functions!

TRIM

Removes the space character char(32) or other specified characters from the start or end of a string.

As a SQL developer, you must have often come across a scenario where you had to remove the empty spaces at the beginning and the end of strings. To achieve that, you may have used the string functions, RTRIM and
LTRIM—you had to use both because SQL Server does not have a function which can trim leading or trailing spaces of a given string with a single function, even though TRIM() is a very popular function in many languages. The release of SQL Server 2017 comes with this new string function “TRIM”, which you can use to get rid of the leading and trailing spaces around any given string.

SQL Server 2017 SQL Server
Syntax TRIM ([characters FROM] string)
Characters could be a literal, variable, or a function call of any non-LOB character type (nvarcharvarcharnchar, or char) containing characters to be removed. nvarchar(max) and varchar(max) types are not allowed.
A string is an expression of any character type (nvarcharvarcharnchar, or char) from which characters should be removed.
CREATE FUNCTION dbo.TRIM(@str VAR-CHAR(MAX))
RETURNS VAR-CHAR(MAX)
BEGIN
RETURN LTRIM(RTRIM(@str))
END
or CLR

Let us consider an example string, from which we have to remove the leading and trailing spaces:

further reading

https://www.sqlshack.com/top-string-functions-in-sql-server-2017/

Happy Learning!!

 

 

Posted in SQL Server 2017, Uncategorized | Tagged , , , | Leave a comment

Migrate an SQL database from Windows to Linux using backup-and-restore and SMB Protocol

In an attempt decouple systems, we may modularize them. In most cases, we would need to move databases from one server to another—sometimes, even move them from Windows to Linux.

In the case of moving a database to Linux, SQL Server’s Backup and Restore feature is the recommended mode of migration; we would migrate from SQL Server to Windows to SQL Server vNext CTP 1.4 on Linux.

This topic provides step-by-step instructions for this procedure. In this tutorial, you will:

  • Create a database, SQLShack
  • Backup SQLShack on a Windows machine
  • Transfer the backup to your Linux machine using a File Sharing Protocol, Samba. Use Samba* to setup an SMB network share between the Windows and the Linux machines.
  • Restore the database using the SSMS console (sqlcmd) or GUI

Demonstration

Let’s create a database, SQLShack. I’m going to switch to the SQLShack database to create a sample table to hold dummy data which is needed to demonstrate the restoration process.

Database Backup on Windows Machine

Backup the database, SQLShack, on a Windows Machine.

The following simple command creates a backup of SQLShack to a given file location

Transfer backup file to the Linux machine

You can use Samba to create a shared folder on the Linux system and transfer files from the Windows computer to the Linux (CentOS) computer—or vice versa. This allows the files to be shared on the network through the SMB protocol. This will also allow Windows hosts to get remote access to the files using the file explorer.

Let us go through a step-by-step installation and configuration process of Samba so that we can easily share the files between computers running different operating systems.

further reading…

https://www.sqlshack.com/migrate-an-sql-database-from-windows-to-linux-using-backup-and-restore-and-smb-protocol/

Happy Learning!!!

 

Posted in SQL, SQL 2016, SQL Server vNext | Tagged , , , | Leave a comment

Installation of SQL Server vNext CTP on Linux Distribution CentOS 7

It was a paradigm shift in December 2016, when Microsoft made their SQL Server database available for Linux; it was the first time in history that Microsoft ever designed SQL Server to run on a non-Windows operating system. SQL Server vNext was released for public preview so the user community could test and deploy SQL Server on a Linux operating system. Microsoft took a major step in diversifying the database technology into the non-windows platform for the first time.

Introduction

The close integration of various data sources under one umbrella called Hadoop – Big Data enabled DBAs to handle data and its data structures on the available platforms. It’s a great opportunity for the SQL community members to try MS SQL on the non-windows platform.

SQL Server on Linux can potentially provide customers with even more flexibility in their data solution. SQL Server’s proven enterprise experience and capabilities is a valuable asset to Enterprise Linux customers around the world. It’s expanding the database market to meet the needs and be on par with the changing trends in technology.

This article is an effort to detail the instructions for the installation of SQL Server vNext on Linux. The article also includes several basic Linux commands, thereby being helpful in understanding the process of installation and configuration.

This article is a comprehensive guide for the installation and configuration of MSSQL; it includes:

  • Introduction and understanding of how SQL Server run on Linux
  • Challenges and complications in building and managing SQL Server on Linux
  • Curating the MSSQL Server installation using Linux commands
  • Detailed installation procedure

Pre-requisites

  • Basic understanding of Linux
  • Access to CentOS/RHEL Software Libraries
  • At least 4 GB RAM
  • At least 8 GB of hard disk space

How Does SQL Server Run on Linux?

Microsoft has provided great insight on how they were able to port SQL Server to run natively on Linux introducing what is known as Platform Abstraction Layer (“PAL”). The Microsoft Research Team set out to bring full functionality, performance, and scale value of the SQL Server RDBMS to Linux. The Microsoft Research Drawbridge acts as an abstraction layer between the OS and the application layers. Drawbridge explored a new approach to process virtualization and isolation. It’s a form of virtualization, specifically for application sandboxing. In fact, it’s very hard to provide a capable SQL version outside of windows within the stipulated time, hence MSR (Microsoft Research) team decides to integrate SQL Server’s existing platform layer SOS (SQL Server Operating System) with Drawbridge to create what we call as the SQLPAL. The SOS provides robust memory management, thread scheduling, and IO services. Creating SQLPAL enabled the full functional SQL version to run on Linux. Please read the reference section for more information.

Further reading….

https://www.sqlshack.com/installation-of-sql-server-vnext-ctp-on-linux-distribution-centos-7/

Thanks for reading my space. Happpy Learning!!

 

Posted in SQL Server vNext | Tagged , , , | Leave a comment

Understanding Database Backup Encryption in SQL Server

Terabytes of data, millions of rows; the entire business depends on this — a database administrator’s responsibility is as massive as it sounds. Apart from keeping the data secure, a database administrator also has to keep the system up and running, and restore the data as needed, in case of a failure, with minimal impact to the business.

While this is less of a challenge in an all-on-premises environment, database backups stored off-site or on the cloud require some more precaution. The data literally resides on someone else’s infrastructure. Data security has to be thought of from a different perspective now. If someone gets unauthorized access to the site, they could simply restore a copy of your database from a backup, onto their own hardware. What good, then, is it to lock the doors of your own equipment, when the soul has already departed?

Security best-practices are in place in order to secure the metaphorical soul. They need to be implemented not just in the production environment, but within our backup solution as well.

Earlier versions of SQL Server had a limitation on this security feature; we had to use third-party solutions to encrypt and compress the backup files. Microsoft, with SQL Server 2014, has begun introducing database backup encryption within the native backup capability—now, SQL Server has the ability to encrypt the data while creating a backup using various encryption algorithms!

Continue reading…..

www.sqlshack.com/understanding-database-backup-encryption-sql-server/

Happy Learning!!

 

Posted in SQL, SQL 2012, SQL 2016 | Tagged , , , , , , , | Leave a comment

SQL Server 2016 enhancements – Truncate Table and Table Partitioning

The idea behind this article is to discuss the importance and the implication of SQL Partition and understand the truncate command partitioning enhancements in SQL 2016

One of the biggest challenges for a DBA is to identify the right candidate for table partitioning, as it requires expertise in design and implementation.

The following are the various truncating options available in SQL 2016

  • Truncate individual partitions
  • Truncate multiple individual partitions
  • Truncate a Range of partitions
  • Truncate a Range with multiple individual partitions

Article Highlights

  • Define the importance and the implication of SQL table partitioning
  • Identify the right candidate for table partitioning
  • Provide inline comparison of features available in the different editions of SQL 2016
  • Discuss the truncate partition enhancements in SQL 2016
  • Demonstrate the truncate table partition use cases

SQL Partition

Let’s understand the objective of SQL partitioning, why we need partitioning and the factors that are vital to deciding on a Table Partitioning Strategy.

Partitions are a logical mapping of the physical data. A well-designed partition gives us an option to scale out the data. It optimizes the performance and simplifies the management of data by partitioning each table into multiple separate partitions. Although, not all tables are good candidates for partitioning. If the answer is ‘yes’ to all or most of the following questions, table partitioning may be a viable database design strategy; if the answer is ‘no’ to most of the following questions, table partitioning may not be the right solution for that table.

Continue reading……..

https://www.sqlshack.com/sql-server-2016-enhancements-truncate-table-table-partitioning/

 

Happy Learning!!!

 

Posted in SQL 2016, T-SQL | Tagged , , , , | Leave a comment

SQL Server Database Recovery Process Internals – database STARTUP Command

A database recovery process is an essential requirement for database systems, It can be a tedious job and the process of recovery varies on a lot of scenarios. The desire to improve recovery results has resulted in various procedures but understood by few and prone to errors. In this article, I’ll illustrate the impact of stopping the database instance in the middle of a large transaction that was running and discuss several techniques and tools that are available for faster and successful recovery.

Introduction

Let’s discuss a scenario where we have a SAN disk issue and latency is really high and think many jobs on the data warehouse systems started running indefinitely without a success.

The owner of the server felt this might be a server issue and decided to restart the server but from that point, he’s invited trouble. Everything comes back but he’s now not able to query the database because of a block generated by the system process on the user sessions during the recovery process. This leads to an involvement of senior DBA to troubleshoot the problem.

Further reading…

https://www.sqlshack.com/sql-server-database-recovery-process-internals-database-startup-command/

 

Happy Learning!!

 

Posted in SQL | Tagged , , | Leave a comment