SQL Server Articles


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

Using The AlwaysOn Feature of SQL Server 2012

This is the first in a four-part series on the new AlwaysOn feature in SQL Server 2012. In this article, AlwaysOn is introduced and contrasted with previous high-availability solutions in SQL Server. The second part of the series will commence with a detailed walkthrough on preparing the environment for AlwaysOn. Prior to SQL Server 2012, […]

Using Filtered Indexes to Improve Performance

A Filtered Index is a new index type introduced in SQL Server 2008. In simple terms, rather than adding a non-clustered index to the entire table, you can add an index to a filtered data set. The syntax for creating a filtered index is : CREATE NONCLUSTERED INDEX <Index_Name> ON <Table Name>(<Column(s)>) WHERE <Filter Condition> […]

Transactional Replication – Unable to Replicate Data onto the Subscriber – A Practical Example

Scenario: A couple of days back, I got a call from one of my users saying that the Data is not being replicated properly onto the Production Reporting Server from the Production OLTP server. We have Transactional Replication scheduled for every 15 minutes between our OLTP and the Reporting server. Approach: I started investigating this […]

Using Temp Tables in SSIS

Temporary tables are created in the TempDB database, which persists for a particular session. The objective is to maintain that session, until the temp table information is used and dump the data into a physical table. To maintain a session in SSIS, there is a ‘Retain Same Connection’ property of the Connection Manager. If we […]

Primer on Selecting Data Using Entity Framework

This question about selecting data using Entity Framework on StackOverflow got me thinking that a lot of LINQ to Entities code samples show very little consistency in how data is selected. Find(), Single(), SingleOrDefault(), First(), and FirstOrDefault() are often used interchangeably. The first issue to address is using Where() in the data selection statement. Where() […]

SQL Server Disaster Recovery Test

This article describes a simple procedure we implemented to ensure that the data on our the database on the Disaster Recovery server is consistent with the production server. Our DR Server is a separate server which is located in a separate data centre. The DR server receives data from the production database server via Transactional […]

Performance Impact of Using SELECT COUNT(*) In Queries

I am not sure how this statement came into common usage, however, it seems to be regularly used by developers. Count(*) will use a table scan or Clustered index scan and so will eventually impair performance. In the case of a large table, a table scan or clustered index scan will impair your query performance, […]

Understanding SQL Server Change Data Capture

DBA’s often encounter a requirement to audit DML activity such as INSERT/UPDATE/DELETE operations executed against a particular table or a group of tables in a particular database. Change Data Capture (first introduced in SQL Server 2008 R2) can greatly assist this function. Only members of sysadmin fixed server role can enable the Change Data Capture […]

SQL Server Performance Tuning – Index Tuning

In earlier installments of this series we looked at T-SQL Performance optimizations along with different T-SQL practices, we can now turn our attention to the second part of this series which is index Tuning In looking into Views we will break the article into two sections : Basic Index Tuning using DTA (Database Tuning Advisor) […]

Recovering a SQL Server Database from Suspect Mode

A couple of days back at I got a call from my support team informing me that one of our database located on the Production Server went into Suspect Mode. The version used was SQL Server 2005 Service Pack 3. Being a Production Database server, it was a Priority 1 incident and the expected time […]
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 |