SQL Server General DBA

Does Transactional Replication Include Structural Changes Made to a Database

We typically use Transactional Replication for Reporting purposes. In Transactional Replication, the idea is to replicate the changes done on the Publisher Database (OLTP) onto the Subscriber Database (Reporting).  The main challenge in Replication is whether it will replicate the structural changes made on the Publisher database successfully onto the Subscriber database. For Demonstration purposes, […]

Does LogShipping Include the Structural Changes Made to the Database?

Recently a client asked me “ Does LogShipping ship the Create Table, Alter Table, Drop Table commands onto the Secondary Server?”. Obviously the answer to his question was “Yes” but I wanted to be 101% sure before committing something to him. In this article we are going to prove that LogShipping not only propogates the […]

SQL Server Hardware Optimization

An important concern in optimizing the hardware platform is hardware components that restrict performance, known as bottlenecks. Quite often, the problem isn’t correcting performance bottlenecks as much as it is identifying them in the first place. Start with obtaining a performance baseline. You monitor the server over time so that you can determine Server average […]

SQL Server 2014 – Delayed Transaction Durability

Prior to SQL Server 2014, SQL Server used a Write Ahead Log (WAL) mechanism which means that changes are written to the transaction log before they are committed.  The major advantage with this mechanism is that there will be no data loss . However, since IO is the slowest component, there will be a high […]

SQL Server 2014 Performance Tuning – Columnstore Indexes

SQL Server 2014 updatable in-memory columnstore (abbreviated to xVelocity where appropriate) index is another compelling performance-related feature of SQL Server 2014. Columnstore indexes enable you to deliver predictable performance for large data volumes. Columnstore indexes were first introduced with SQL Server 2012, to significantly improve performance of data warehouse workloads. For certain data warehousing analytical […]

Four Key New Performance Features SQL Server 2014 – Part 1

The latest release of SQL Server 2014 has several new performance-related features as well as significant enhancements to the existing performance features. With these new performance-related enhancements of SQL Server 2014 we can significantly improve the performance of high transactional OLTP applications. In this first of the three part article series, we will take a […]

Improved Online Operations in SQL Server 2014

Online indexing rebuilding was a major breakthrough from SQL Server to support users re-indexing while the clustered index (or table) and other indexes are available during the index rebuild operation.   However, in real world there are multiple issues with these online operations as it does not have much flexibility. In SQL Server 2014, there are […]

TroubleShooting SQL Server Memory Consumption

Recently on one of our staging servers had a memory consumption issue and even 32 GB of RAM was looking insufficient and application performance was being impaired. Initially I thought the cause would be poorly designed queries, but after a thorough investigation this turned out not to be the case. I noticed that whenever the […]

Performance Tuning Re-indexing and Update Statistics – A Case Study

Recently we started experiencing a very strange issue in our production reporting environment where the Re-indexing and Update Statistics operation suddenly began taking more than 2 days to complete and was thus causing blockage in the database which in turn caused impairment in application performance. Reporting Server Configuration Details: SQL Server Version: SQL Server 2005 […]

Tuning your SQL Query – Generating a Proper Execution Plan.

Recently one of my developers came to and complained of slow performance on a particular query. During the investigation I noticed that the primary cause was the poor query execution plan. In this article, I will discuss the approach which I used to improve the execution plan. For demonstration purpose, I will be creating a […]
Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |