SQL Server Articles

Categories : Performance | Audit | Business Intelligence | Clustering | Developer | Reporting | Windows Server | SQL Azure | Powershell | ASP.NET | Other .NET

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 […]

SQL Server 2014 Performance – Resource Governor Enhancements and Buffer Pool Extension

In this second of the three part article series, we will first take a look at Resource Governor Enhancements for Physical IO.  Finally, we will talk about buffer pool extension feature. Resource Governor Enhancements for Physical IO control Resource Governor allows us to manage our workloads. This feature was first introduced with SQL Server 2008. […]

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 […]

Viewing Execution Plans While Queries are Running

Analyzing execution plans is a key tool when analyzing query performance. As most of you are aware there are two ways of analyzing query plans, namely ‘Include Actual Execution Plan’ and ‘Display Estimated Query Plan’ The Include Actual Execution Plan option will provide the execution plan after the completion of the query. However, this option […]

Tips For Minimizing Deadlocks in SQL Server

A deadlock or fatal embrace is a situation which occurs when the a process is waiting for the resource that is locked by a second process and that second process is waiting for a resource that is locked by the first process. In this article, we will learn about ways to minimise deadlocks. Tips for […]

Manage and Monitor Identity Ranges in SQL Server Transactional Replication

Problem When using transactional replication to replicate data in a one way topology from a publisher to a read-only subscriber(s) there is no need to manage identity ranges. However, when using  transactional replication to replicate data in a two way replication topology – between two or more servers there is a need to manage identity […]

Practical Introduction to XML Manipulation Using XQuery in SQL Server

XQuery was developed by W3C as a query language designed to programmatically interact with and manipulate Extensible Markup Language (XML).  XQuery uses XPath methods and expressions to search and retrieve XML data.  In this article, I’d like to show a few simple, practical examples of these implementations. First, let’s create a table to hold sample […]

Using DML Triggers to Capture Multiple Events

AS DBAs we often need to develop DML triggers to capture multiple entries which are either INSERTED, UPDATED or DELETED from a table. A few months back, I came across a situation where the Application Development Team asked me to write a logic  to notify the DBA  if any records were either INSERTED,UPDATED or DELETED […]

Automate Audit Requests

User Review Periodically, an internal auditor would stop by my desk and ask me to show them all the users and permissions on a particular database.  This is easily gathered, and most of us know how to do this.  I produce this list, and send it on to the requestor.  The next question is ‘What […]

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 |