SQL Server Blocking and Deadlocks

The more blocking happens on SQL Server the less concurrency the system. A certain amount of short blocks are unavoidable but too many blocks for longer periods of time can degrade performance and increase the chances of SQL Server deadlocks. SQL Server blocking SQL Server blocking occurs when one connection (user process or application process) […]

SQL Server Performance Counters

The most important performance counters to watch when tuning SQL Server performance are CPU usage, memory usage, cache hit ratios, and disk I/O. Even though CPU capacity and available memory are crucial, disk I/O is frequently the cause of bottlenecks in most systems. Paying extra attention to disk I/O and cached disk access is worthwhile. […]

SQL Server Performance Tips

SQL Server performance tuning can consume a considerable amount of time and effort. The following list is a quick guideline that you should keep in mind when designing and developing SQL Server database applications: User Defined Functions (UDF) Refrain from using user defined functions (UDF) in a select statement that may potentially return many records. UDFs […]

Advantages of SQL Server 2005 64-bit

64-bit processors are expected to dominate the markets by the end of 2007. It is clear that the industry is moving towards the 64-bit platform fast, especially for server hardware. The move towards the 64-bit platform has been driven by lower hardware prices, especially after the introduction of x64 architecture by both Intel (EM64T) and […]

Understanding SQL Server Memory Internals & Cache

Like all database servers, Microsoft SQL Server thrives on memory. Each instance of Microsoft SQL Server has its own memory address space. The SQL Server address space (Virtual memory allocation by Windows) consists of two main components: executable code and memory pool. Let’s examine each of these components in more details. SQL Server executable code […]

The 4 Pillars of SQL Server Performance: CPU, Memory, Disk I/O, & Cache Hit Ratios

The most important performance counters to watch when tuning SQL Server performance are CPU usage, memory usage, cache hit ratios, and disk I/O. Even though CPU capacity and available memory are crucial, disk I/O is frequently the cause of bottlenecks in most systems. Paying extra attention to disk I/O and cached disk access is worthwhile. […]

Tuning tempdb For Better SQL Server Performance

One of the most important areas of SQL Server performance is the tempdb database. It maintains temporary user tables and intermediate query results used to prepare and optimize queries. The tempdb in essence is very similar to a user database but it is not intended to persist data. The tempdb is replaced with a copy […]