SQL Server Performance Tuning

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

SQL Server T-SQL Performance Tuning – Views

In this installament of the TSQL Performance Tuning series we turn our attention to Views Views Used Within Joins or Where Conditions As a generic concept, an inner join with an object which has insufficient indexing will result in index scanning or perhaps a table scan which also applies for Views Impacts: • Much CPU […]

Natural vs. Surrogate Keys in SQL Server : Getting the Proper Perspective

I once walked into a bar, and saw two construction workers pounding each other to a pulp.  The argument was over what was the better tool—a  hammer or screwdriver.  I feel a similar sensation when I see SQL developers arguing over whether to use natural or surrogate keys.    Few other arguments in database design can […]

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

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

SQL Server T-SQL Tuning – Temp Tables, Table Variables and Union

In this article series we will focus on the primary points of T-SQL performance tuning. In this first installment I will lopok at tuning temp tables, table variables and the Union command. Temp Tables and Table Variables Most database developers are used to breaking down code into small chunks using  temp tables or table variables  […]

Using The System Monitor Tool For Measuring SQL Server Performance

System Monitor, a program to measure the performance of SQL Server allows you to view the objects of SQL Server and performance counters in addition to the behavior of other objects, including threads, processors, memory, and cache. These objects individually associate set of computers for measuring delays, device usage, and other counters indicating overall performance […]

Multiple Table Queries in SQL Server – UNION, EXCEPT, INTERSECT, Subqueries, and Joins

There are several for creating queries that return data from multiple tables. The one you choose depends on your data retrieval requirements and the underlying structure of the database. In this three part article series, I will demonstrate how to combine data from multiple tables by using UNION, INTERSECT, and EXCEPT. You’ll perform advanced queries […]

Techniques to Monitor SQL Server memory usage

Memory has a dramatic impact on SQL Server Performance. Fortunately, in SQL Server you can either use DMVs, , Extended Events, sp_server_diagnostics system procedure or SQL Profiler to server memory usage and track down root cause of SQL Server memory bottlenecks. In this article, I will provide you high-level overview of these memory-related tools. Dynamic […]

Improving 2D Range Query Performance in SQL Server

When using the BETWEEN operator on multiple columns, you are likely using a 2D range query.  Such queries perform very poorly in SQL Server.   This article will tell you how you can often use additional information to rewrite such queries for much better performance.  We’ll conclude by rewriting an actual BETWEEN query in a manner […]

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 |