Ahmad Yaseen

Author
Recent Posts

Ahmad Yaseen is a Microsoft Big Data engineer with deep knowledge and experience in SQL BI, SQL Server Database Administration and Development fields.

He is a Microsoft Certified Solution Expert in Data Management and Analytics, Microsoft Certified Solution Associate in SQL Database Administration and Development, Azure Developer Associate and Microsoft Certified Trainer.

Also, he is contributing with his SQL tips in many blogs.

View all posts by Ahmad Yaseen

SQL Server Data Type Conversion Methods and performance comparison

October 3, 2017 by Ahmad Yaseen

When you define SQL Server database tables, local variables, expressions or parameters, you should specify what kind of data will be stored in those objects, such as text data, numbers, money or dates. This attribute is called the SQL Server Data Type. SQL Server provides us with a big library of system data types that define all types of data that can be used with SQL Server, from which we can choose the SQL Server data type that is suitable for the data we will store in that object. You can also define your own customized user defined data type using T-SQL script. SQL Server data types can be categorized into seven main categories:

T-SQL commands performance comparison – NOT IN vs SQL NOT EXISTS vs SQL LEFT JOIN vs SQL EXCEPT

September 22, 2017 by Ahmad Yaseen

This articles gives you a performance comparison for NOT IN, SQL Not Exists, SQL LEFT JOIN and SQL EXCEPT.

SQL Server 2016 Maintenance Plan Enhancements

September 18, 2017 by Ahmad Yaseen

SQL Server Maintenance Plans is a SQL Server Management Studio built-in feature that helps in creating a workflow of variant database administration tasks, which can be run automatically using a predefined schedule or manually triggered by the user.

SQL Server Maintenance Plans allow you to use typical database maintenance tasks or customize your own task using a T-SQL script that runs on the local server or group of SQL Servers, providing more flexibility to the database administration tasks.

How to Index Foreign Key Columns in SQL Server

September 5, 2017 by Ahmad Yaseen

Before going through the main concern of this article, indexing the foreign key columns, let’s take a small trip back to review the SQL Server Indexes and Foreign Key concepts.

What’s new in SQL Server Management Studio 17.2; Authentication methods, scripting options and more

August 31, 2017 by Ahmad Yaseen

SQL Server Management Studio is an integrated graphical interface that is used to configure, manage, monitor and administrate the SQL Server instances hosted on the local machine, on a remote server or in the cloud. It provides us with editing, debugging and deploying environment for the T-SQL, XML, MDX and DMX languages.

In-Memory OLTP Enhancements in SQL Server 2016

August 22, 2017 by Ahmad Yaseen

SQL Server In-Memory OLTP, also known as Hekaton when it was introduced in SQL Server 2014, provides us with the ability to move specific database tables and suitable stored procedures into memory and compile the stored procedures into native x86 code. As a result, you can easily query these database objects directly from memory with the best performance and the least possible data access latency. In addition to that, the SQL Server Engine will no longer use the old latching and locking mechanism to control the data access concurrency. Instead, a high performance row versioning mechanism will be used to control the concurrency. This optimistic concurrency mechanism is 5 times to 20 times faster than the normal disk-based processing, due to reading the data from the memory directly.

How to simplify SQL Server Database Object usage with Synonyms

July 20, 2017 by Ahmad Yaseen

The concept of SQL Server Synonyms was introduced the first time in SQL Server 2005 as an alias name that references an existing database object, replacing its fully qualified name. In this way, it makes the database object more portable and provides more flexibility for the clients to reach and maintain it. You can imagine Synonyms as a layer of abstraction that provides us with an easy way to connect to and manage the database objects without the need to identify the real name and location for these objects.

How to control online Index Rebuild Locking using SQL Server 2014 Managed Lock Priority

July 18, 2017 by Ahmad Yaseen

When you perform a SQL Server Online Index Rebuild operation, introduced for the first time in SQL Server 2005, the index will not be taken down. But at a specific point, in which the new index new is built and switched from the old structure of the index, a special kind of lock, Schema Modification (SCH-M), will be granted. This lock may cause blocking if your database server is busy.

SQL Server SET Options that Affect the Query Result – SET CONCAT_NULL_YIELDS_NULL, SET NUMERIC_ROUNDABORT, SET QUOTED_IDENTIFIER, SET NOCOUNT, SET XACT_ABORT

July 4, 2017 by Ahmad Yaseen

In the previous article of this two-part series SQL Server SET Options that Affect the Query Result – SET ANSI_NULLS, SET ANSI_PADDING, SET ANSI_WARNINGS and SET ARITHABORT, we described the first four SQL Server SET options and showed practically how setting these options ON and OFF affects the SQL Server Database Engine behavior and the query result. To recall, SQL Server SET options are a group of session-level options that control how the SQL Server behaves on the database session level, and the option value can be changed using the SET T-SQL command for the current session that you execute the SET command on.

In this article, we will describe another five SET options and see how turning it ON and OFF will change the SQL Server behavior and the query result.

SQL Server SET Options that Affect the Query Result – SET ANSI_NULLS, SET ANSI_PADDING, SET ANSI_WARNINGS and SET ARITHABORT

June 30, 2017 by Ahmad Yaseen

SQL Server provides us with a number of options to control SQL Server behavior on the connection level. These session-level options are configured using the SET T-SQL command that change the option value for the session on which the SET command is executed. Changing the default value of these session-level configuration affects how the session queries will be executed affecting the query result. The performed change on a session-level option will be applied to the current session until its value is reset or until the current user’s session is terminated.

SQL Server 2016 Memory-Optimized Tables – The Checkpoint operation

June 21, 2017 by Ahmad Yaseen

The SQL Server Database Engine stores data changes in the buffer pool, in memory, before applying it to the database files, for I/O performance reasons. After that, a special kind of background process, called Checkpoint, will write all of these not reflected pages, also known as Dirty Pages, to the database data and log files periodically.

Understanding different SQL Server database states

June 16, 2017 by Ahmad Yaseen

Identifying the SQL Server database state and how a database can be moved between these different states is considered an important aspect of SQL Server database administration . A good understanding of this will help us in troubleshooting and fixing many database problems and issues.

Compression and decompression functions in SQL Server 2016

June 2, 2017 by Ahmad Yaseen

The concept of data compression is not a new on for SQL Server Database Administrators , as it is was introduced the first time in SQL Server 2008. In that SQL Server version, you were able to configure the compression at the row and page levels on the table, index, indexed view or the partition. The row and page level compression is not the best choice in all cases, as it does not work well on the Binary Large Objects (BLOB) datatypes, such as the videos, images and text documents.

How to Create a Clustered Columnstore Index on a Memory-Optimized Table

May 24, 2017 by Ahmad Yaseen

With Microsoft SQL Server, like all Microsoft products, you will enjoy experiencing new features and enhancements to the existing ones, when a new SQL Server version is launched. These SQL Server enhancements concentrate heavily in simplifying and speeding up the data retrieval without consuming excessive resources.

How to Spoof Production Environment Resources for Performance Tuning

May 16, 2017 by Ahmad Yaseen

Performance tuning is one of the most important and critical tasks that the SQL Server Database Administrator performs on a daily basis to keep a SQL Server running in a healthy state, by identifying the performance bottlenecks and fixing the main cause of these problems.

How to add a TDE encrypted user database to an Always On Availability Group

April 27, 2017 by Ahmad Yaseen

SQL Server Transparent Data Encryption, also known as TDE, is a “data at rest” encryption mechanism that is introduced in SQL Server 2008 as an Enterprise Edition feature. TDE is used to perform a real-time I/O encryption for the SQL Server database data, log, backup and snapshot physical files, rather than encrypting the data itself, using either Advanced Encryption Standard (AES) or Triple DES (3DES) encryption.

How to Configure Read-Only Routing for an Availability Group in SQL Server 2016

March 23, 2017 by Ahmad Yaseen

The SQL Server Always On Availability Groups concept was introduced the first time in SQL Server 2012 as an enterprise-level high availability and disaster recovery solution that will replace the database mirroring feature. Always On Availability Group provides a high availability solution on the group level, where each group can contain any number of databases that can be replicated to multiple secondary servers known as Replicas.

New date objects in SQL Server 2016 – DATEDIFF_BIG and AT TIME ZONE

March 10, 2017 by Ahmad Yaseen

SQL Server 2016 obtains the values of the date and time using the GetSystemTimeAsFileTime() Windows API, with precision fixed at 100 nanoseconds. The accuracy of these date and time values depends on the hardware specs and the version of the Windows of the server on which the SQL Server instance installed.

How to create indexes on SQL Server computed columns

March 8, 2017 by Ahmad Yaseen

A SQL Server Computed Column is a virtual column that is not stored physically on the table, unless it is previously specified as PERSISTED. A computed Column value is calculated using a specific expression that can be constant, function, data from other columns on the same table or a combination of these types connected together by one or more operators.

Saving the Plan Cache storage using the Optimize for Ad hoc Workloads option

February 23, 2017 by Ahmad Yaseen

When you run a query in SQL Server, the SQL Server Query Optimizer will draw the road map for that query, specifying the optimal way to execute it, which is called the query execution plan.

Generating the execution plan will take few milliseconds from the CPU cycles, which is negligible for one query or small load, but it will be considerable for a very heavy transactional workload. Because of this, SQL Server caches these generated plans in a special type of memory called the Plan Cache to eliminate the overhead generated by the query plan if the same query is executed again. When you submit your query to the SQL Server Engine, it will search in the plan cache if there is any existing execution plan that can be reused, if an available execution plan is found in the plan cache, the plan will be used to execute that query, otherwise, the SQL Server Query Optimizer will create a new plan and keep it in the plan cache for future use.

Indexing SQL Server temp tables

January 31, 2017 by Ahmad Yaseen

SQL Server temp tables are a special type of tables that are written to the TempDB database and act like regular tables, providing a suitable workplace for intermediate data processing before saving the result to a regular table, as it can live only for the age of the database connection.

Using Memory-Optimized Tables to Replace SQL Temp Tables and Table Variables

January 24, 2017 by Ahmad Yaseen

TempDB usage can be considered as a performance bottleneck for workloads that use SQL temp tables and table variables intensively resulted in heavy IO usage. A valuable alternatives for the SQL temp table and table variable are SCHEMA_ONLY Memory-Optimized tables and the Memory-optimized Table Variable, where the data will be completely stored in the memory without the need to touch the TempDB database, providing the best data access performance. SCHEMA_ONLY Memory-Optimized table and the Memory-optimized Table Variable are stored only in the memory with no related component in the disk. It involves no IO activity or TempDB utilization. It can also participate in the transactions without the need to log the transactions.

SQL Server 2016 Always On Availability Group with Direct Seeding

January 12, 2017 by Ahmad Yaseen

SQL Server Always On Availability Groups are an enterprise-level high-availability and disaster-recovery feature introduced the first time in SQL Server 2012as an alternative to database mirroring. A set of user databases that fail over together forms the availability group. These availability databases are hosted by the availability replicas and can be readable- writable at the primary replica and up to eight sets of secondary replica databases that can be configured to be read-only databases. The availability groups fail over due to the availability replica’s level issues and not the ones caused due to database level issues such as data loss or database corruption.

Different Ways to set the Max Degree of Parallelism in SQL Server

December 29, 2016 by Ahmad Yaseen

In this article, we will go through the different ways to set the Max Degree of Parallelism option. Before doing that, it is helpful to understand the concept of parallelism, how it works and what the MSXDOP is.

SQL Server Estimated Vs Actual Execution Plans

December 29, 2016 by Ahmad Yaseen

A SQL Server execution plan is the most efficient and least cost road map that is generated by the Query Optimizer’s algorithms calculations to execute the submitted T-SQL query. Execution plans are used by the database administrators to troubleshoot the performance of poorly performing queries to isolate the part of the query that is at the root of the performance issue.