Ahmad Yaseen

Ahmad Yaseen

Ahmad Yaseen

Ahmad Yaseen is Senior SQL Server DBA at Aramex International Company with a bachelor’s degree in computer engineering as well as .NET development experience.

He is a Microsoft Certified Professional with a good experience in SQL server development, administration, performance tuning, monitoring and high availability and disaster recovery technologies. Also he is contributing with his SQL tips in many blogs

View all posts by Ahmad Yaseen
Ahmad Yaseen

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

March 23, 2017 by

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 groups level, where each group can contain any number of databases that can be replicated to multiple secondary servers known as Replicas.

Read more »

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

February 23, 2017 by

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.

Read more »

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

January 24, 2017 by

TempDB usage can be considered as a performance bottleneck for workloads that use temp tables and table variables intensively resulted in heavy IO usage. A valuable alternatives for the 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.

Read more »

SQL Server 2016 Always On Availability Group with Direct Seeding

January 12, 2017 by

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.

Read more »

SQL Server Estimated Vs Actual Execution Plans

December 29, 2016 by

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.

Read more »

Understanding the SQL Server Proportional fill algorithm

November 22, 2016 by

When creating a database, SQL Server maps this database with minimum two operating system files; the database data file MDF and the database log file LDF. Logically, the database data files are created under a collection set of files that simplifies the database administration, this logical file container is called the Filegroup. Database data files can come in two types, the Primary data files that contains metadata information for the database and pointers to the other database files in addition to the data, where each database should have only one Primary data file and the optional Secondary files to store data only. The basic storage unit in SQL Server is the Page, with each page size equal to 8KB. And each 8 pages with 64KB size called Extent.

Read more »

SQL Server Partitioned Views

November 18, 2016 by

SQL Server table partitioning is a great feature that can be used to split large tables into multiple smaller tables, transparently. It allows you to store your data in many filegroups and keep the database files in different disk drives, with the ability to move the data in and out the partitioned tables easily. A common example for tables partitioning is archiving old data to slow disk drives and use the fast ones to store the frequently accessed data. Table partitioning improves query performance by excluding the partitions that are not needed in the result set. But table partitioning is available only in the Enterprise SQL Server Edition, which is not easy to upgrade to for most of small and medium companies due to its expensive license cost.

Read more »

What’s new in SQL Server 2014 Cardinality Estimator?

November 14, 2016 by

The Cardinality Estimator is a SQL Server Query Processor component that is responsible for predicting the number of rows that the query will return. This estimation of the number of rows in addition to the SQL Server statistics will be used by the SQL Server Query Optimizer to create the optimal and the most accurate execution plan for your query that has the lowest processing cost to execute.

Read more »

SQL Server 2016 Database Scoped Configuration

October 21, 2016 by

SQL Server 2016 introduces a new set of configurations that can be applied at the database level to control its performance and behavior. These configurations were previously applicable only at the SQL instance level before version SQL Server 2016, With the ability to configure settings at the database level, many databases that are hosted in the same instance can now be isolated from each other, with each database has its own customized configurations. The database level configurations allow us also to set different database configurations for the Always On Availability Groups primary and secondary databases to meet the different types of workloads.

Read more »

SQL Server 2016 Parameter Sniffing

October 19, 2016 by

SQL Server tries always to generate the most optimized execution plan for each stored procedure the first time that the stored procedure is executed. The SQL Server Engine looks at the stored procedure passed parameter values when compiling the stored procedure, the first execution, in order to create the optimal plan including the parameters and keep that plan for future use in the plan cache. This parameter analysis process is called the Parameter Sniffing.

Read more »

Using Dynamic Data Masking in SQL Server 2016 to protect sensitive data

October 17, 2016 by

Dynamic Data Masking is a new security feature introduced in SQL Server 2016 that limits the access of unauthorized users to sensitive data at the database layer.

As an example of the need for such a feature is allowing the applications developers to access production data for troubleshooting purposes and preventing them from accessing the sensitive data at the same time, without affecting their troubleshooting process. Another example is the call center employee who will access the customer’s information to help him in his request, but the critical financial data, such as the bank account number or the credit card full number, will be masked to that person.

Read more »

My journey to PASS Summit 2016 via SQLShack

October 3, 2016 by

My name is Ahmad Yaseen and I am a senior SQL Server database administrator at Aramex International Company for more than six years, where I started building my experience in the SQL Server administration field. Being a SQL Server Database administrator in such big company is not that easy, as the difficulties that you may face is not only at the beginning. What makes the DBA job special is that it is a very deep job, in which you will learn new things, have to solve new problems, fall in strange situations and be under pressure every day, but it is an interesting job for me.

Read more »

How to filter and block the data access using SQL Server 2016 Row-Level Security

September 28, 2016 by

SQL Server 2016 came with many new features and enhancements for existing ones, that concentrate on the aspect of SQL Server security. One of the new security features introduced in SQL Server 2016 is Row-Level Security. This feature allows us to control access deeply into the rows level in the database table, based on the user executing the query. This is done within the database layer, in complete transparency to the application process, without the need to manage it with complex coding at the application layer.

Read more »

How to track the history of data changes using SQL Server 2016 System-Versioned Temporal Tables

September 23, 2016 by

SQL Server 2016 introduces a new type of table that is designed to keep the full history of data changes, where row validity is managed by the system. This new table type is called a System-Versioned Temporal Table. In earlier SQL Server versions, user tables would enable you to hold only the most recent copy of the row, without being able to query the value before the UPDATE or DELETE operations. Using a Temporal Table, you will be able to query the recent state of the row as usual, in addition to the ability to query the full history of that row, which is fully managed by the SQL Server Engine, as you can’t define the rows validity period explicitly.

Read more »

Database checkpoints – Enhancements in SQL Server 2016

September 21, 2016 by

When a new row is inserted or an existing one is updated in your database, the SQL Server Database Engine will keep that change in the buffer pool in the memory first, without applying each change directly to the database files for IO performance reasons. These data pages located in the buffer pool and not reflected yet to the database files are called Dirty Pages. The SQL Server Database Engine uses a special kind of processes to write these dirty pages to the data and log database files periodically. These processes are called Checkpoints. The Checkpoint creates a mark that is used by the SQL Server Database Engine to redo any transaction that is committed, written to the database transaction log file without reflecting the data changes to the data files due to an unexpected shutdown or crash. Also, this recovery point that is created by the Checkpoint will be used to roll back any data changes associated with uncommitted transaction, by reversing the operation written in the transaction log file. In this way the SQL Server Engine will guarantee the database consistency. The time that is taken by the SQL Server Database Engine to redo and undo the transactions is called the Recovery Time. All information about the Checkpoints will be written to the database boot page to identify till which point the database files are synchronized with the buffer pool when the system wakes up after crash or shutdown.

Read more »
Page 1 of 212