SQL Server Articles


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

SQL Server T-SQL Tuning -TVF and Scalar Functions

In the first of this series we looked at TSQL tuning for temp tables, table variables and the Union command , we now turn our attention to User Defined Functions – either Table-Valued Functions or Scalar Functions. A UDF is very convenient for centralising business logic as we can specify a set of business logic […]

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

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 |