SQL Server General DBA

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

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

Implementing Database Settings Using Policy Based Management

Introduction Database Administrators have always had a tough time to ensuring that all the SQL Servers administered by them are configured according to the policies and standards of organization. Using SQL Server’s  Policy Based Management feature DBAs can now manage one or more instances of SQL Server 2008 and check for policy compliance issues. In […]

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

Notifications for SQL Server Agent Restarts

Introduction In this article we will set up an email notification at the SQL Server level whenever SQL Server and the SQL Server Agent Restarts. The article will   useful for DBA’s who support a large number of database servers which they cannot manually connect to   daily to check the SQL Server status, SQL job status […]

Report SQL Server Percentage Backup Completion and Time Completion

When executing a backup from a t-sql command, it is difficult to know what percentage has completed and when the backup will be complete. When you are taking a backup, you can specified STATS = 10 like following. BACKUP DATABASE [AdventureWorks2012] TO DISK = N’D:\MYBackup\AdventureWorks2012.bak’ WITH NOFORMAT, NOINIT, NAME = N’AdventureWorks2012-Full Database Backup’, SKIP, NOREWIND, […]
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 |