SQL Server Performance Tuning

Analyze and Fix Index Fragmentation in SQL Server 2008

It is very common that over time SQL Server tables and indexes tend to become fragmented. The fragmentation generally happens when data within the underlying tables on which an index exists is modified. The data modification basically can be an insert, update or a delete operation. The indexes over time become ineffective because they get […]

Entity Framework Tutorial


Monitoring Index Fragmentation

A few years ago I started a new job as a DBA with a new group.  This application served the needs of hourly employees across the globe as they clocked in and out of work.  Reports were generated and payroll issued based on the data gathered and collected in this database.  It was a 24/7 […]

SQL Server High-Availability with Virtualisation

SQL Server associated applications and data are often mission-critical to the operation of a business. While protecting SQL Server and its databases is a top priority for IT departments, it’s often not enough. If the applications residing on SQL are not also protected, there is a risk that business operations will be disrupted. The complexity […]

Are Your Indexes Being Used Effectively?

In this fast moving world, data is the heart and soul of any enterprise. As the data is growing very rapidly day by day, the biggest challenge which enterprises face today is to store the data in such a way that it can be retrieved quickly whenever required. The most common thought which comes in […]

Speeding up the Performance of Table Counts in SQL Server 2005

Introduction When we want to get the row count of a table, we use the transact-SQL function count (*).  In the case of large table which may contain millions of rows, it can take a long time to return the row count of the whole table, and that leads to slow performance for the query. Count () function Every DBA know […]

Characterizing I/O Workload

In order to determine an application’s ideal storage system and disk quantity, it’s important to understand the type and volume of I/O the application will generate. This article will focus on the different types of I/O, the metrics used to classify workload, and methods used in measuring and estimating values for the I/O metrics. OLTP […]

Resource Governor in SQL Server 2008

SQL Server 2008 introduces a new feature called Resource Governor which enables Database Administrators to manage SQL Server workload and critical system resource consumption. Resource Governor enables DBA to specify limits on the amount of CPU and memory which the incoming sessions to the SQL Server can use. In a production environment DBA’s will come across scenarios […]

SQL Server Performance Coding Standards

1. Avoid using “*” in SELECT queries Always specify the required list of columns in the select list. This will ensure that only the columns required by the query are returned to avoid unnecessary I/O and processing.   2. Always use variables of the appropriate data type and size Use appropiate data types to avoid implicit data type conversion being perfromed […]

System Data Collection Reports

In my last article (http://sql-server-performance.com/articles/per/Management_Data_Warehouse_p1.aspx) I introduced one of the new SQL Server 2008 features known as the Management Data Warehouse (MDW).  In that article I described how to install the components that make up the MDW.  Now I’m going to expand on the subject and write about the rich reporting capabilities that Microsoft has […]