Move Datafiles in SQL Server Part 1

Move Datafiles in SQL Server Part 1
4 (80%) 1 vote

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

Overview of DMV Tool using DBFS FUSE with Docker Containers on Linux Machine – PART2

Overview of DMV Tool using DBFS FUSE with Docker Containers on Linux Machine – PART2
4.5 (90%) 2 votes

After reading this article, you will be able to the following:

  1. Create a Linux Docker container using the Docker commands.
  2. Perform required configuration change to setup Docker.
  3. Install DBFS FUSE and related dependent packages.
  4. Mount the file-system and data extraction procedures using the configuration file.
  5. Run DBFS program to run custom SQL queries.
  6. Push Docker container image to GitHub repository.
  7. And more…

Read More

Implementing Paging Using OFFSET FETCH NEXT in SQL Server

Implementing Paging Using OFFSET FETCH NEXT in SQL Server
5 (100%) 3 votes

A database can have hundreds of thousands of records. It is easy to insert and select these records via database management systems like SQL Server or MySQL etc. However, it is not easy to display thousands of records on a single webpage or in a desktop application. Space and memory constraints make it difficult to display a huge number of records all at once.

A common solution to such a problem is to implement paging. (Note, this is not memory paging implemented by operating systems) Paging in programming refers to displaying data via a series of pages. A random Google search may result in thousands of results. Google uses paging to display these results. If you scroll down the Google page with search results you would see the following:

Here you can see the number of pages that the search result is divided into. You can click the Next link to see more pages.

In this article, we will see how OFFSET FETCH NEXT operators can be used to implement paging on front-end applications. We will begin with a simple example using the OFFSET FETCH NEXT operator and will then see how it can be practically used with the help of a stored procedure.

Read More

Basics of sys.dm_exec_requests

Basics of sys.dm_exec_requests
5 (100%) 5 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

Main Usage of sys.dm_os_wait_stats
4.3 (85%) 4 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

Traditional and Natively Compiled Scalar-Valued User-Defined Functions

Traditional and Natively Compiled Scalar-Valued User-Defined Functions
4.6 (92%) 10 votes

In the software programming world, there are several approaches used by developers that help them with effortless software development. Design patterns, object-oriented programming, test driven development are some of them. If we particularly mention the code reuse; “Reuse of a typed code. It is written once and can be used in most places. It is a basic concept of software engineering. It is a structure necessary for modular programming. ”

For this reason, user-defined functions in SQL Server help us to avoid rewriting of T-SQL queries. At the same time, they improve code readability. In this post, we will discuss the pros and cons (advantages and disadvantages) of the traditional and natively compiled scalar user-defined functions and make a simple performance test.

Read More

Run Excel Macro using SSIS Script Task

Run Excel Macro using SSIS Script Task
4.8 (95%) 4 votes

When we export data from SQL server table to excel file by using SQL Server integration service package, data in excel file column exports into text format. Even though the column values are Integer or Decimal, it is stored in text format. For example, I am retrieving the values of “CreditLimit” column from the product table. Data format changes automatically and stores in text format in excel file.

Read More

Overview of DMV Tool using DBFS FUSE with Docker Containers on Linux Machine- PART1

Overview of DMV Tool using DBFS FUSE with Docker Containers on Linux Machine- PART1
3.3 (65%) 4 votes

This article is intended for database administrators and system administrators who plan to configure the DBFS FUSE file-system on a Linux machine using the Docker containers. I would recommend reading the previous articles “Introduction to Docker and Docker toolbox” and “Docker and SQL Server on Linux”. This article assumes that the reader has a rough knowledge of the Docker Containers and SQL Server. Read More

Similarities and Differences among RANK, DENSE_RANK and ROW_NUMBER Functions

Similarities and Differences among RANK, DENSE_RANK and ROW_NUMBER Functions
4.7 (93.33%) 3 votes

The RANK, DENSE_RANK and ROW_NUMBER functions are used to retrieve an increasing integer value. They start with a value based on the condition imposed by the ORDER BY clause. All of these functions require the ORDER BY clause to function properly. In case of partitioned data, the integer counter is reset to 1 for each partition.

In this article, we will study the RANK, DENSE_RANK and ROW_NUMBER functions in detail, but before that, let’s create dummy data that these functions can be used on unless your database is fully backed up. Read More

Conditional Split Transformation: Export Data from SQL Server into Oracle and MySQL Databases

Conditional Split Transformation: Export Data from SQL Server into Oracle and MySQL Databases
3.8 (75%) 4 votes

In this article, I have explained how we can divide and export data of SQL Server database table into Oracle and MySQL databases. That process is based on the condition defined in SSIS as “conditional split transformation”. Conditional split transformation is like case statement of any programming language. Using conditional, we can redirect the output of a component in multiple destinations that is based on a condition defined in the conditional split component. Read More