In-Memory OLTP – Part 2

Posted on Wednesday, December 5th, 2018
In last article “In-Memory OLTP Introduction” we discussed what is in-memory OLTP and how it works. We also got to know memory-optimized tables and natively compiled stored procedures. now we are going to be more familiar with creating in-memory objects. Create Memory-Optimized Tables To create memory-optimized tables, your database must contain a filegroup for in-memory […]
Read More

In-Memory OLTP Introduction – Part 1

Posted on Monday, December 3rd, 2018
In-Memory OLTP (also known as Hekaton) is introduced by SQL Server 2014 to improve the performance of OLTP workloads. In-Memory OLTP is optimized for Online Transaction Processing (OLTP) and integrated into SQL Server Database Engine. It lets You move tables from your traditional, disk based, storage, which suffer I/O latency, into the memory directly. It […]
Read More

How to implement database mirroring

Posted on Friday, October 26th, 2018
Database mirroring is a solution for increasing the availability of a SQL Server database. Mirroring is implemented on a per-database basis and works only with databases that use the full recovery model. Database mirroring maintains two copies of a single database that must reside on different server instances of SQL Server Database Engine. Typically, these […]
Read More

Relational Databases vs. Hadoop

Posted on Saturday, September 1st, 2018
Big Data is a common buzzword in the world of IT nowadays, and it is important to understand what the term means: Big Data describes the realization of greater business intelligence by storing, processing, and analyzing data that was previously ignored or siloed due to the limitations of traditional data management technologies. Notice from this definition […]
Read More

Designing and Implementing User-Defined Functions

Posted on Sunday, July 15th, 2018
Functions are routines that consist of one or more Transact-SQL statements that you can use to encapsulate code for reuse. In this blog, you will learn to design and implement user-defined functions (UDFs) that enforce business rules or data consistency, and to modify and maintain existing functions that other developers have written. A function takes […]
Read More

Security in SQL Server – Part 3

Posted on Sunday, June 10th, 2018
In the security terms, Authentication is the operation to verify the user and Authorization is the rights that have been assigned to the user. Securing SQL Server is not only involving to these two terms, instead is also about providing right privileges to every single user and manage them properly without having any security conflict. […]
Read More

Security in SQL Server – Part 2

Posted on Sunday, May 27th, 2018
Securing SQL Server is not completely done in the instance, there must be secure network environment to reduce or avoid attacks to the server. To make secure the SQL Server there are some factors such as service account, communication protocol and firewall configuration. SQL Server is a windows service process and windows platform runs it […]
Read More

Security in SQL Server – Part 1

Posted on Sunday, May 27th, 2018
Microsoft SQL Server is a Relational Database Management System (RDBMS) that contains few components and subcomponent. Each component in SQL Server has its own security mechanism which is almost separated from each other. Database Engine security includes Service Account, Instance and databases security. Most of developers and IT professionals only think encryption as only mechanism […]
Read More

DML Triggers

Posted on Monday, May 21st, 2018
Data manipulation language (DML) triggers are a powerful tool that enables you to enforce domain, entity, and referential data integrity and business logic. The enforcement of integrity helps you to build reliable applications. DML triggers need to be able to work with both the previous state of the database and its changed state. DML triggers […]
Read More

Stored Procedures in SQL Server

Posted on Thursday, May 17th, 2018
A stored procedure is a named collection of Transact-SQL statements that is stored on the server within the database itself. A stored procedure could be created at the server level to encapsulate all of the Transact-SQL statements that are required. Stored procedures are given names and are called by name. The application can then simply […]
Read More

Clustered Indexes in SQL Server

Posted on Tuesday, April 24th, 2018
An index is a collection of pages associated with a table. Indexes are used to improve the performance of queries or enforce uniqueness. You can use indexes to point to the location of required data and to minimize the need for scanning entire tables. At this point, it is useful to consider an analogy that […]
Read More

Introduction to Views

Posted on Tuesday, April 24th, 2018
A view is a named SELECT query. Unlike ordinary tables, a view is not part of the physical schema; it is a dynamic, virtual table that collect data from base tables (or views). Effective use of views in database design helps improve performance and manageability. The data that is accessible through a view is not […]
Read More

Create Table By T-SQL

Posted on Tuesday, April 17th, 2018
In relational database management systems (RDBMSs), user and system data is stored in tables. Each table consists of a set of rows that describe entities and a set of columns that hold the attributes of an entity. For example, a Customer table would have columns such as CustomerName and CreditLimit and a row for each […]
Read More

Advanced Indexing and Performance Monitoring Fundamentals

Posted on Wednesday, April 4th, 2018
In earlier post about Execution Plans, you have seen that one of the most important decisions that Microsoft SQL Server takes when executing a query, is how to access the data in any of the tables involved in the query and how each step of a query was executed. In this post, you will see […]
Read More

SQL Server Execution Plan Fundamentals

Posted on Monday, March 26th, 2018
One of the most important steps in working with Microsoft SQL Server data management is to understand what execution plans are, why execution plans are so important and the phases that SQL Server passes through when it executes a query. Execution plans enable you to answer precise that how a query is to be performed. […]
Read More

Does Non-Clustered Index (B-Tree) Gets Update Asynchronously?

Posted on Friday, March 23rd, 2018
The idea of writing this blog post came to my mind, once one of our clients was insist and determine that Non-Clustered index gets update asynchronously and it does not impact INSERT, DELETE and UPDATE transaction performance. Are you shocked? I understand. Prior demonstration of how SQL Server handles Non-Clustered indexes, I would like to […]
Read More

SQL Server Hybrid Data Synchronization (Azure & On-Premise)

Posted on Monday, March 19th, 2018
As you know, the On-premise & Azure data synchronization feature is just released by Microsoft Azure team as preview version; therefore I want to have a try on this feature to check the limitations and advantages over other traditional techniques and technologies regarding data synchronization. This blog will go through Azure SQL Database synchronization setup […]
Read More

Data Integrity

Posted on Monday, March 19th, 2018
Data integrity refers to maintaining and assuring the accuracy and consistency of data over its entire life-cycle, and is a critical aspect to the design, implementation and usage of any system which stores, processes, or retrieves data. SQL Server enforces data integrity to guarantees the quality of data in the database. It is a critical […]
Read More

How to Deal with Deadlock in SQL Server?

Posted on Thursday, March 15th, 2018
A deadlock is a special blocking scenario in which two sessions get blocked by each other. Each session, while holding its own resources, attempts to access a resource that is locked by the other session. This will lead to a circular blocking scenario, also known as a deadly embrace, as illustrated below. Deadlocks also frequently […]
Read More

SQL Server Database File Deduplication

Posted on Saturday, February 17th, 2018
Data Deduplication feature is added on Windows Server 2012 to reduce the storage cost by replacing duplicate data chunks from files with some reparse pointers. It reduces the impact of redundant data on storage costs. When enabled, Data Deduplication optimizes free space on a volume by examining the data on the volume by looking for […]
Read More

How to Prevent SQLInjection

Posted on Saturday, February 17th, 2018
SQL injection is an attack in which malicious code is inserted into strings that are later passed to an instance of SQL Server for parsing and execution. Any procedure that constructs SQL statements should be reviewed for injection vulnerabilities because SQL Server will execute all syntactically valid queries that it receives. Even parameterized data can […]
Read More

Interaction Between Sets

Posted on Tuesday, February 13th, 2018
SQL Server provides several operators that act on sets with different effect on the input sets. Common features that need to know before using set operators: The input sets must have the same number of columns and compatible data type – implicit conversion or explicit conversion. Input sets are the result of a SELECT statements […]
Read More

Grouping and Aggregating Data

Posted on Monday, February 12th, 2018
Except of row-by-row queries, you may need to summarize data to analyze it. Microsoft SQL Server provides built-in functions that can aggregate, and summarize information based on multiple rows. In this post, we will discuss about aggregate functions, GROUP BY, and HAVING clauses. Using Aggregate Functions Microsoft SQL Server provide aggregate functions such as SUM, […]
Read More

SQL Server TSQL Subquery

Posted on Tuesday, January 23rd, 2018
A subquery is a SELECT statement nested, or embedded, within another query. The nested query (subquery), is the inner query. The query containing the nested query is the outer query. A subquery can be used anywhere an expression is allowed, as a column expression, in a WHERE clause, in a HAVING clause or even in […]
Read More

SQL Server Administration by Powershell (Part 2)

Posted on Sunday, January 21st, 2018
PowerShell can help you to automate security tasks. Whether you need to monitor repeated failed login attempts by parsing out event logs, or manage roles and permissions, especially if the number of users in the system is very large, PowerShell can help you deliver. In this blog, I am going to share some of SQL Server administrative […]
Read More

SQL Server Administration by Powershell (Part 1)

Posted on Sunday, January 21st, 2018
Powershell is a new modular command-line tool from Microsoft, by using this tool you are able to perform whatever you can do by GUI. most of Microsoft products have Powershell module which you may need to import it manually. In this blog, I am going to share some of SQL Server administrative tasks that can be accomplished  […]
Read More

SQL Server Health Check and Scoring

Posted on Saturday, January 20th, 2018
SQL Server Health Check is a very challenging subject that requires expertise and in-depth knowledge about SQL Server internals, administration and development. Fard Solutions provides specialized health check service where our internal software called ‘SQL Server Analyzer’ helps us to deep dive into your SQL Server environment current and potential issues and recommend solutions to […]
Read More

SQL Server Temporary Tables and Performance Impact

Posted on Wednesday, January 17th, 2018
It’s been a while, that I did not write up any blog post. So as the title mentions, I would like to write about Temporary Tables in SQL Server and how those objects can reduce the overall performance. You might seen or used Temporary Tables in stored procedures to implement the business logic with keeping […]
Read More

How to Update SQL Server Statistics in Heavy Workload Database

Posted on Friday, January 12th, 2018
Sometimes we do need to disable AUTO_STATS_UPDATE or AUTO_STATS_UPDATE_ASYNC due to many reasons such as database performance impact in heavy workload environment. Updating statistics based on regular schedule also takes long time and impact the database performance. Updating statistics are necessary due to SQL Server engine behavior, updated statistics object can lead the database and […]
Read More

Using DML to Modify Data

Posted on Thursday, January 11th, 2018
Hi dear all, hope you enjoy the last post about Sorting and Filtering Data. As you see below, transact-SQL (T-SQL) consists of several commands. One of the most important commands of T-SQL is DML. In this post we will learn about DML commands and how to use. Transact-SQL (T-SQL) Data Manipulation Language (DML) is the subset […]
Read More

Ranking Functions

Posted on Tuesday, January 9th, 2018
SQL server provides many built-in functions, ranging from those that perform data type conversion, to those that aggregate and analyze groups of rows. Functions built-into SQL server can be categorized as below: In this blog I want to write about ranking functions. As it mentioned in above table, ranking functions return a ranking value for […]
Read More

Mystery of MSSQLSERVER~ Windows Users in SQL Server 2016

Posted on Saturday, January 6th, 2018
I came across to install and configure SQL Server 2016 and found there are many MSSQLSERVER windows users are created in Windows Local Users. Therefore I start to do some Bing search but unfortunately found nothing related to this. I will explain what are those local users and what is the use of them. SQL […]
Read More

SQL Server AlwaysOn Availability Group Performance Internals

Posted on Wednesday, January 3rd, 2018
One of our customer requested us to showcase the SQL Server 2016/2017 AlwaysOn Availability Group performance increments in Synchronous replica scenarios, then I thought it is a good topic to write a blog post about. I have prepared a SQL Server AlwaysOn Availability Group on two different Virtual Machines (VM) as the following figure with […]
Read More

Sorting and Filtering Data

Posted on Wednesday, December 27th, 2017
hi all, hope enjoy the last post about ‘SELECT Queries’, you can also refer to ‘Saeed’s Post’ for further information about Querying Multiple Tables by JOINs. In this post we will discuss how to limit the number of rows in SELECT queries and control the order of results. Because of relational theory, sets of data […]
Read More

Data Types in SQL Server

Posted on Tuesday, December 26th, 2017
To write effective queries in T-SQL, you should understand how SQL server stores different types of data. This is especially important if your queries not only retrieve data from tables, but also perform comparisons, manipulate data, and implement other operations. In this blog I want to show you built-in systems data types specifications, similarities and […]
Read More

Process/Thread Context Switch Impact on SQL Server Performance

Posted on Thursday, December 7th, 2017
SQL Server like any other application is using a specific process and threads, therefore the processor resource needs to be shared with fair amount of time with other processes within operating system (Windows). To accomplish this goal, there is a concept of Context Switch which is referring to the process of saving and loading the […]
Read More

Writing SELECT Queries

Posted on Tuesday, November 28th, 2017
In this post, we will discuss about the structure and elements of the SELECT statement. Elements of the SELECT Statement In the following table you see the order of operations in the SELECT statement: The SELECT clause specifies the columns to retrieve. You can also add calculated expressions in the SELECT clause. The FROM clause […]
Read More

Querying Multiple Tables By Using JOINS

Posted on Tuesday, November 28th, 2017
In SQL Server environments, when user wants to retrieve data from multiple tables needs to make join between those tables based on date relationship between tables. In SELECT statement order of writing tables name is important because the table after FROM clause will be considered as LEFT table and the table after JOIN will be […]
Read More

SQL Server StretchDB

Posted on Saturday, November 25th, 2017
StretchDB is a new feature of SQL Server 2016 to archive/migrate cold data from on-premise to Azure SQL Database. We as consultant put the following values on the StretchDB feature:- The cost of keeping cold data is low on Azure compare to On-Premise environment. Using this feature does not require any changes on TSQL queries […]
Read More

SQL Server Compute On-Premise, Storage On-Cloud

Posted on Monday, November 20th, 2017
Microsoft Azure offers fully managed file shares in the cloud that are accessible via the industry standard Server Message Block (SMB) protocol. Azure File shares can be mounted concurrently by cloud or on-premises deployments of Windows, macOS, and Linux. Azure Files enable sharing files between applications running in your virtual machines using familiar Windows APIs […]
Read More

Introduction to Microsoft SQL Server

Posted on Friday, November 10th, 2017
This post is an overview of Microsoft SQL Server. After reading this, you will know about basic architecture of SQL Server, SQL Server Management Studio and how to execute a query against SQL Server database. Basic Architecture of SQL Server SQL Server is a relational database management system to store and manage data. Relational databases […]
Read More

Introduction to Transact-SQL

Posted on Friday, November 10th, 2017
Transact structured query language (T-SQL) is a querying language which user can use to ask Microsoft SQL server to process request . You also can use many tools in SQL Server Management Studio (SSMS) instead of writing query in T-SQL but it’s much better to use T-SQL for professional Database Administrator (DBA) because sometimes you […]
Read More

SQL Server Discovery, Inventory and Resource Planning

Posted on Tuesday, September 19th, 2017
Having almost 100% accurate of SQL Server inventory (license and installed instances) is very difficult to get, but not impossible. every IT team in organizations need to keep track of such information, SQL Server licenses are not inexpensive that you can effort to forget them or misuse the licenses. Problem comes into the picture, once management […]
Read More

SQL Server Smart Database Backup Plan

Posted on Monday, August 7th, 2017
Preparing a proper database backup plan strategy is very critical for every DBA to make sure the plan meets the business requirements such as Recovery Point Objective (RPO) and Recovery Time Objective (RTO). I have consulted many organizations regarding SQL Server issues and one of the most common mistake that client makes, is performing database […]
Read More

What is SQL Server Latch and Buffer Latch Contention?

Posted on Wednesday, May 17th, 2017
SQL Server Latches are lightweight synchronization technique used by the storage engine to make sure the in-memory structures such as index, data pages and internal structures like non-leaf pages in a B-Tree are consistent. SQL Server uses buffer latches to protect the buffer pool and I/O latches to protect pages not yet loaded into the […]
Read More

SQL Server TLog Flush Size and Allocation Unit Size

Posted on Saturday, April 15th, 2017
It is been a while from my previous blog post, so in this blog post I would like to write about SQL Server transaction log flush size and what is the best allocation unit for transaction log disk drive. The idea came from a discussion with one my friend – Special Thanks to Siavash Golchobian […]
Read More

TempDB Recreation Myth!

Posted on Tuesday, February 21st, 2017
Most SQL Server DBAs believes that TempDB always recreated once SQL Server service is restarted and makes a clean copy of TempDB database. therefore, it came to my mind if we configure TempDB data file size to 120GB and log file to 45GB, we might have some performance issue in case of instance restart or […]
Read More

Database Backup Verification Policy Automation

Posted on Thursday, January 12th, 2017
Taking regular database backup is important and most of DBAs are assigned to perform this task, but making sure the backup is restorable and entire database is accessible once restored is very important. Basically every database should have its own ‘Backup & Restore’ policy which is designed based on the RTO, RPO and backup strategy. […]
Read More

SQL Server Write Ahead Logging Protocols

Posted on Monday, November 28th, 2016
Write Ahead Logging To understand how the write-ahead log works, it is important for you to know how modified data is written to disk. SQL Server maintains a buffer cache into which it reads data pages when data must be retrieved. Data modifications are not made directly to disk, but are made to the copy […]
Read More

SQL Server on Linux Findings

Posted on Tuesday, November 22nd, 2016
I tried to explore more regarding SQL Server on Linux operating system, and I found few mysterious stuff which I hope they are interesting to you as well. Finding 1: That is Microsoft SQL Server Developer Edition Finding2: Runs on top of Microsoft Windows NT 6.2 (9200 Build) Finding3: Root directory is C:\ Finding4: Supports NUMA […]
Read More

Install SQL Server on Ubuntu (Step-By-Step)

Posted on Friday, November 18th, 2016
Installing SQL Server on Linux was a great experience for me, that I would like to share with you the challenges and some of the scripts. Hope you enjoy reading this blog post and it is informative for you. Before we start, we do need to install the Ubuntu Server 16.04 version on either physical […]
Read More

Boost-Up SQL Server Performance with Minimal Investment!

Posted on Thursday, November 17th, 2016
DBAs are always under pressure to boost-up the database performance by tuning queries and re-fining the database design and data file placement, but all those efforts only boost-up the performance in to some extend, and beyond that requires faster hardware storage. In this blog post, I will share with you how to increase Create,Update and […]
Read More

Why CMEMTHREAD Wait Type Occurs?

Posted on Wednesday, November 9th, 2016
It is very rare to get CMEMTHREAD wait type as top 10 SQL Server wait-stats ordered by wait_time_ms column as descending. CMEMTHREAD indicates there are lots of memory object synchronization to make sure that object accessed by different thread (worker) would not get corrupted. this technique is mostly talked about when Windows Synchronization Object, such […]
Read More

SQL Server Strech Cluster with Windows Server Storage Replica

Posted on Saturday, November 5th, 2016
Windows Server 2016 Storage Replica technology enables synchronous replication of volumes between servers or clusters for disaster recovery. It also enables you to use asynchronous replication to create failover clusters that span two sites, with all nodes staying in sync. Storage Replica supports synchronous and asynchronous replication: Synchronous replication mirrors data within a low-latency network […]
Read More

SQL Server Reporting Services Installation Considerations

Posted on Tuesday, October 18th, 2016
Sometimes happens that we may install both the Reporting Services Windows service and the Report Catalog on the same server (Not Best Practice). However, some organizations may choose to place these items on separate servers. This can be done through either a distributed installation or a scale-out installation. Distributed Installation In a distributed installation, the […]
Read More

SQL Server Reporting Services Internal Architecture

Posted on Tuesday, October 18th, 2016
If Reporting Services were simply a report authoring environment, it would be a good tool. However, Reporting Services has much more to offer beyond just report creation. In fact, Reporting Services is a complete, enterprise-wide report management and distribution service. Reporting Services enables us to securely distribute reports throughout an organization using an existing intranet […]
Read More

OLAP Storage vs. Performance

Posted on Monday, October 17th, 2016
This blog post covers the details of physical cube processing and storage, including an explanation of the what, when, and why to use any of the three cube storage methods: MOLAP, HOLAP, or ROLAP. These storage-type acronyms stand for Multidimensional, Hybrid, or Relational OLAP.
Read More

SQL Server Advance Monitoring

Posted on Sunday, October 2nd, 2016
SQL Server and Windows platforms provide some features for administrative alerts and notifications. SQL Server DBAs should take this advantage to perform pro-active monitoring on SQL Server, Databases, Windows Server and/or Failover Cluster setup. In this post, I will explain some of critical SQL Server and Windows Server errors. SQL Server Alerts and Notifications SQL […]
Read More

SQL Server Backup Performance Tuning

Posted on Saturday, September 17th, 2016
Taking SQL Server database backup takes very long time to accomplished on database in production servers. Most DBAs complain about the backup performance, therefore I would like to highlight few tips to improve the backup performance in this blog post. Remember once the BACKUP DATABASE command is executed, SQL Server automatically does few ‘Checkpoint’ to […]
Read More

SQL Server Master Data Services – Model Implementation

Posted on Monday, August 22nd, 2016
In the previous blog post, I already explained the advantage of using SQL Server Master Data Services feature in the end-to-end organizational BI environment. At the center of every Master Data Services solution is a Master Data Services model. To create a master data management solution with Master Data Services, you must know how to […]
Read More

Does our solution worth the money?

Posted on Monday, August 15th, 2016
As a service provider, it happens all the time that some companies request our services or in need to have immediate solution for their current problem regarding SQL Server, Once we provide them our proposal and associate cost, they usually put a side our proposal and mention that ‘We are charging beyond market and other competitors […]
Read More

SQL Server Master Data Services

Posted on Sunday, July 10th, 2016
The pain that organizations are experiencing around consistent reporting, regulatory compliance, strong interest in Service-Oriented Architecture (SOA), and Software as a Service (SaaS) has prompted a great deal of interest in Master Data Management (MDM). This blog post explains what MDM is and why it is important, while identifying some of the key MDM management patterns […]
Read More

SQL Server Memory Capacity Planning

Posted on Saturday, July 9th, 2016
It is quiet challenging to determine how much memory will be used by SQL Server, therefore infrastructure team needs to provide sufficient physical memory to the server. most of the times insufficient memory capacity can cause CPU and I/O bottlenecks, if the databases are not tuned well. You may refer to SQL Server Performance Bottlenecks […]
Read More

Pig Basics for SQL Server DBA

Posted on Friday, July 8th, 2016
You might think why Pig is related to SQL Server, at first glance it might not be related but as we know that SQL Server is already integrated with Hadoop and Big Data, is going to be used by corporations, then SQL Server DBAs should learn some basics of Pig to perform some data extraction […]
Read More

What is Big Data and Hadoop?

Posted on Tuesday, July 5th, 2016
Every company ranging from enterprise level to small-scale startups has money for Big Data. The storage and hardware costs have dramatically reduced over the past few years enabling the businesses to store and analyze data, which were earlier discarded due to storage and processing challenges.  We’re seeing an explosion of data where there is an […]
Read More

SQL Server Business Intelligence

Posted on Saturday, July 2nd, 2016
I already explained about Data Warehouse Project, A data warehouse is the foundation for a business intelligence (BI) solution, enabling business users, information workers, and data analysts to make faster, better decisions. This blog post introduces BI, describing the components of Microsoft SQL Server that you can use to create a BI solution, and the […]
Read More

SQL Server WCF Web Service

Posted on Friday, July 1st, 2016
Since Microsoft SQL Server 2012, SOAP endpoints are deprecated features and developers should not use this feature due to separation of concerns and also security purposes. I believe that feature was reducing a lot of development effort. In this blog post I would like to introduce SQL Server Web Service which is developed to generate […]
Read More

SQL Server 2016 Internal Enhancements

Posted on Monday, June 27th, 2016
Everyone excited to see the new features and enhancements of SQL Server 2016, beside all those improvements, there are some improvements in the architectural design of the database engine to provide better performance, scalability and use of hardware resources. I would like to pin point to some of the major internal improvements that have been […]
Read More

Data Visualization in Power BI using Power View

Posted on Saturday, June 25th, 2016
In the ‘previous blog post called ‘All About Power BI’, I came across to describe ‘what is PowerBI’, and ‘what is Power BI components’. Therefore in this blog post, I would like to focus more on how to visualize data in Power View and Power Map components. When business users have obtained the data that […]
Read More

All About Power BI

Posted on Saturday, June 25th, 2016
Business users often depend on reports to make business decisions in a timely manner. However, as the volume and variety of business data that is used to make those decisions grows, relying on the IT department or business intelligence (BI) specialists to create reports introduces a bottleneck into ongoing business processes that can frustrate business […]
Read More

SQL Server Hardware Sizing

Posted on Tuesday, June 21st, 2016
Planning the hardware infrastructure for a OLAP or OLTP solution that is based on Microsoft SQL Server requires an understanding of how the various SQL Server components work together, and how their typical workloads use hardware resources. The core-balanced system architecture is based on the fact that most OLAP or OLTP workloads need to transfer small to large amounts […]
Read More

SQL Server Data Warehouse Project

Posted on Saturday, June 18th, 2016
Data warehousing is a technique and concept for gathering and centralizing data for reporting and analysis purposes. Although the data warehouse project is subjective case by case, there are some common elements in most data warehousing implementations. Knowing these components will make you more clear to have better plan and build an effective data warehousing solution. […]
Read More

SQL Server Backup Plan Strategy

Posted on Sunday, June 12th, 2016
Backup plan! The operation that is getting fade and fade among SQL Server DBAs due to other backup technologies such as SAN replication, VM replication and VM HADR. Each SQL Server database needs its own backup and restore plan strategy depending on the business value. Most of DBAs and Infra-Administrators are looking at database backup plan as […]
Read More

SQL Server In-Memory Multi-Version Concurrency Control

Posted on Wednesday, May 25th, 2016
In the previous blog post regarding SQL Server In-Memory Internals, I wrote an overview of how In-Memory OLTP engine controls the concurrency without acquiring Lock and Latches, and how it handles the transactions. In this blog post I would like to write more in depth regarding concurrency control in this new SQL Server engine. Basically […]
Read More

SQL Server CLR Monitoring and Optimization

Posted on Sunday, May 15th, 2016
The common language runtime (CLR) is the heart of the Microsoft .NET Framework and provides the execution environment for all .NET Framework code. Code that runs within the CLR is referred to as managed code. The CLR provides various functions and services required for program execution, including just-in-time (JIT) compilation, allocating and managing memory, enforcing […]
Read More

SQL Server I/O Subsystem Simulation

Posted on Monday, May 2nd, 2016
It is been long time that I do hardware I/O subsystem pressure test prior to SQL Server installation, based on my experience and observation, not many DBAs or Consultants are doing this. Basically before installing SQL Server in any hardware server, we are in need to do some I/O tests base on SQL Server I/O […]
Read More

SQL Server Partitioning

Posted on Sunday, May 1st, 2016
SQL Server provides partitioning feature to partition the data and distribute them within few data files to provide better manageability and also performance increment. SQL Server only provides ‘Range Partitioning’ which there are other few partitioning techniques such as:- List Partitioning Hash Partitioning Composite Partitioning Partitioning Methods Horizontal partitioning involves putting different rows into different […]
Read More

SQL Server Memory Calculator Tool

Posted on Wednesday, April 20th, 2016
Providing enough memory to SQL Server is very tricky and most DBAs do not know how much memory is needed, then the gradually add on the memory into the VM or physical server once they face the performance issue from lack of memory space. SQL Server memory usage calculation is different on every single hardware/server, […]
Read More

SQL Server Database Backup Internals

Posted on Sunday, April 17th, 2016
How SQL Server Backup Works Once the ‘Backup Database’ command executed, SQL Server automatically does few ‘Checkpoint’ to reduce the recovery time and also it makes sure that at point of command execution there is no dirty pages in the buffer pool. After that SQL Server creates at least three workers as ‘Controller’, ‘Stream Reader’ […]
Read More

SQL Server Farm Management Techniques

Posted on Sunday, April 10th, 2016
Lots of DBAs are using third party software to control and manage their SQL Server farm within their company data center, in this post I would like to highlight few SQL Server features regarding this matter. SQL Server Management Studio is not just an application to run your queries, instead it is a powerful application […]
Read More

SQL Server In-Memory Table Size Estimation Script

Posted on Sunday, March 27th, 2016
Microsoft introduced In-Memory OLTP technology for SQL Server in version 2014, I would not explain about this technology, you may visit our other blog post at SQL Server In-Memory Internals to read more about it. It is quiet tricky to estimate and provide enough memory for In-Memory tables. Many of our clients implemented this technology […]
Read More

SQL Server Lock Internals

Posted on Saturday, March 26th, 2016
SQL Server uses lock mechanism to synchronize data access to prevent corruption for in-memory data (such as buffer pool) and to provides higher level of concurrency. Most of DBAs get confuse when it comes to lock mechanism. What is SQL Server Lock? Microsoft SQL Server has multi-granular locking that allows different types of resources to […]
Read More

SQL Server and UDF’s Performance Issues

Posted on Saturday, March 19th, 2016
Recently this issue happened once we upgraded a database from SQL Server 2005 to SQL Server 2014 to meet the compliance. Once the upgrading process is done, users tried to do the User Acceptance Test (UAT) to make sure every single functionality in the application and also the database performance still remain intact. A part […]
Read More

Oracle RAC vs. SQL Server AlwaysOn

Posted on Tuesday, March 15th, 2016
Oracle RAC and its predecessor Oracle Parallel Server (OPS) have been around for about 20 years since the first release of OPS in 1992. Oracle RAC is a complex and archaic architecture that might not be suitable for customers, especially in the cloud computing era. Newer cloud computing applications require much larger scale (more nodes) […]
Read More

Why SQL Server Optimization Matters?

Posted on Saturday, March 12th, 2016
Database optimization is the act of design and developing database systems to take maximum advantage of the resources available. For example, databases can be optimized to benefit from the large quantity of memory available on a particular server, or the speed of the specific I/O hardware available, or particular features of the processor being used. Even […]
Read More

What is SQL Server MCM, MCSM and MVP?

Posted on Friday, March 11th, 2016
The good point of Microsoft is providing multi level certifications, I regularly speak at local SQL Server user group in Malaysia and few times happened that some of participants ask me whether I am MVP holder or not! I straight away mention ‘No, I am not. But I am MCM holder’, then they ask me what […]
Read More

Why SQL Server Health Check Matters?

Posted on Tuesday, March 8th, 2016
Deep down I know just how important it is to schedule the time to do SQL Server health check-up, and I know you are not the only one making up excuses. It’s something DBA needs to think about and prioritize. From experience it’s better to know SQL Server results and be able to take real, actionable steps towards better […]
Read More

SQL Server High Availability Solutions

Posted on Thursday, March 3rd, 2016
It is quiet tough to decide what kind of high availability solution is available on the table and which one can fit all your production server requirements in terms of licenses, hardware cost, maintenance and professional services. In this blog post, I would like to explain every single SQL Server high availability solution that is […]
Read More

Rows Gap Analysis

Posted on Monday, February 29th, 2016
It happens sometimes that we are in need to find out the row number’s gap in a table to analyze the deleted/update/missing records range after an incident. There are different ways to find out the rows gap, I will explain 3 different ways in this post. Problem: There is a table with few rows which […]
Read More

Why SQL Server Consolidation Matters?

Posted on Friday, February 26th, 2016
The Evolution of the Microsoft Data Platform Before we get into SQL Server 2014 I want to start off by showing you how far the Microsoft Data Platform has come in the last decade. Many of you may be still be using SQL Server 2005 or SQL Server 2008 running on Windows Server 2003 or […]
Read More

SQL Server Memory Pressure

Posted on Sunday, February 21st, 2016
As the following figure illustrates, below functions are executed in SQL Server (Overview): 1- TDS goes to the SNI component in SQL Server to be decoded into ‘Language Event’; 2- Command Parser parses the TSQL Query to validate the syntax, and uses Algebrizer component to generate ‘Query Tree’. 3- Query Tree been sent to ‘Optimizer’ […]
Read More

SQL Server Backup/Restore and Completion Time Estimation

Posted on Sunday, February 14th, 2016
Taking database backup is a compulsory task if your data is critical. SQL Server backup and restore operations can take from few seconds to few hours or some cases few days which most of DBAs or system administrators assume that SQL Server is crashed or hanged during backup or restore operation. Therefor they restart the […]
Read More

SQL Server In-Memory Internals

Posted on Saturday, February 13th, 2016
Applicable on: SQL Server 2014, SQL Server 2016 What is In-Memory OLTP? New in SQL Server 2014, Microsoft added new feature called In-Memory OLTP which can significantly improve OLTP database application performance by placing the whole table records into the memory. There are some limitations to use this features which will be rectified on next version. In-Memory Optimized tables can […]
Read More

SQL Server DBA Responsibilities

Posted on Tuesday, February 9th, 2016
The ideal SQL Server DBA candidate should have at least 3-5 years of experience with SQL Server 2012, SQL Server 2008 R2, SQL Server 2008, SQL Server 2005 and a fair knowledge about SQL Server 2014. This position is targeted for both dedicated support roles and short term reactive support and proactive engagements where overnight […]
Read More

SQL Server Secondary Data File Access Failure

Posted on Sunday, January 24th, 2016
  SQL Server Query Execution Overview The database component of Microsoft SQL Server is a Structured Query Language (SQL)–based, scalable, relational database with integrated Extensible Markup Language (XML) support for extendable applications. The following describes a fundamental part of the query execution of the SQL Server database engine: Read Request As above figure illustrates, below […]
Read More

SQL Server Automatic Page Repair Script

Posted on Saturday, January 9th, 2016
Database page level corruption in SQL Server is the worst trouble that any SQL Server professional can face. In any environment, either small or enterprise, the integrity and availability of the data cannot be compromised. This is especially the case in those organizations that totally rely on their OLTP systems, for example a high-volume website. […]
Read More

SQL Server Index Design Recommendations

Posted on Tuesday, January 5th, 2016
Applicable: SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012 and SQL Server 2014— In this blog post, I would like to explain overview of SQL Server indexes and most common SQL Server index design recommendations. What is an Index? Index is an object in SQL Server Database that affects physical and […]
Read More

SQL Server Hardening

Posted on Friday, December 25th, 2015
SQL Server hardening can be implemented on instance, database and network levels. SQL Server applications rely on data, and protecting that data has never been more important than it is today. SQL Server is a popular target for hackers, so your data is at risk of being intentionally compromised. In addition, your data is at […]
Read More

SQL Server Performance Bottlenecks

Posted on Friday, December 4th, 2015
SQL Server performance is all depends on the resource bottlenecks, if there is a bottleneck, and then the SQL Server performance will be affected. SQL Server bottleneck can be on memory, disk and processor. To analyze each of these bottlenecks you need to measure it based on some counters. The most critical bottleneck which can reduce SQL Server performance […]
Read More

Backup and Restore Plan Strategy Matters!

Posted on Saturday, November 28th, 2015
Backup and restore plan! The operation that is getting fade and fade among SQL Server DBAs due to other backup technologies such as SAN replication, VM replication and VM HADR. Each SQL Server database needs its own backup and restore plan strategy depending on the business value. Most of DBAs and Infra-Administrators are looking at database backup […]
Read More