sqlCrunch.com :: SQL Server Performance Tuning Links

This excellent blog post from the SQL CAT Team provides performance objectives for an OLTP application. They list counters, target values and the DMV or Performance counter to measure the objective ... Source : Tom Davidson - SQL Server Customer Advisory Team - Blog Post

Performance Monitoring Tools

Kalen provides a nice summary of links to free performance troubleshooting tools including SQL Nexus and PSSDiag ... Source : Kalen Delaney - SQLblog.com - Blog Post

Performance Checklist

Grumpy lists 18 external factors that may reduce SQL Server performance. Items include Raid levels, AntiVirus Software, NIC Teaming, Drivers, Page Files, Windows Services and lots more ... Source : Grumpy Old DBA - sqlBlogCasts.com - Blog Post

Disabling Windows Services not Required

Grumpy takes a look at improving system performance by shutting down windows services not required ... Source : Grumpy Old DBA - sqlBlogCasts.com - Blog Post

Top 10 Performance Tips for Data Warehousing

Top 10 performance bottlenecks to avoid for Data Warehouse or Reporting applications ... Source : Microsoft.com - Technet Article

sys.dm_exec_requests

The sys.dm_exec_requests DMV comes in handy for troubleshooting a variety of performance problems, but as Aaron points out, the BOL entries on this DMV are limited. In this post, Aaron shows us a variety of ways it can be used in troubleshooting various issues ... Source : Aaron Bertrand - SQLBlog.com

Load 1TB in less than 1 hour - Best Practices and Lessons Learned

This project was done using SQL Server 2005 Enterprise Edition (with SP1 beta) using the BULK INSERT command with 60 parallel input files on an Itanium2 HP Superdome with 64 x 1.6GHZ 9M (Madison) CPUs. For the insert tests the system was equipped with 256GB of RAM ... Source : Kevin Cox - SQL Server Customer Advisory Team - Blog Post

Server Sizing for SQL Server 2000 and 2005

This post from Joe Chang covers the process of sizing and selecting components for a database server. He pays special attention to processors. A great comment exchange between Joe and Linchi Shea at the end of the post ... Source : Joe Chang - SQLblog.com - Blog Post

Performance Impact of Procedure Calls without Owner Qualification

Linchi tests the best practice recomendation that all procedures calls should include the owner prefix e.g.; exec dbo.procname instead of exec procname ... Source : Linchi Shea - SQLblog.com - Blog Post

Perfmon Counters thresholds

Grumpy blogs about a great post from the UK SQL Server Premier Support Field Engineers. They provide suggested performance monitor counters for SQL Server, Reporting Services, Analysis Services and Windows Server. As well as listing the actual counters to monitor are explanations of likely values and causes ... Source : Grumpy Old DBA

Disk I/O

Storage Best Practices

Some of the most common best practices that the SQL Server team recommends with respect to storage configuration for SQL Server ... Source : Microsoft.com - Technet Article

Storage Performance for SQL Server

Another great post from Joe covering various aspects of storage configuration for optimal Disk I/O Performance ... Source : Joe Chang - SQLblog.com - Blog Post

Physical Database Storage Design

This article provides a guide for physical storage design and gives recommendations and trade-offs for physical hardware design and file architecture. Update; Linchi Shea comments on this paper in his recent blog post ... Source : Microsoft.com - Technet Article

Predeployment I/O Best Practices

When configuring a new server for SQL Server or when adding or modifying the disk configuration of an existing system, it is good practice to determine the capacity of the I/O subsystem prior to deploying SQL Server. This white paper discusses validating and determining the capacity of an I/O subsystem. A number of tools are available for performing this type of testing. This white paper focuses on the SQLIO.exe tool, but also compares all available tools. It also covers basic I/O configuration best practices for SQL Server 2005 ... Source : Microsoft.com - Technet Article

Partition Alignment

Track aligning disk partitions before formatting is crucial in delivering the best possible disk IO performance. This post contains a forum exchange on configuring a Clarion SAN for partition alignment ... Source : Grumpy Old DBA - SQLBlogCasts.com - Blog Post

SQLIOSim

SQLIOSim is a correctness and stress tool (not a performance measurement tool). Use this to verify your IO subsystem is functioning correctly under heavy loads. In this SQL Server Storage Engine Blog post, links are provided for download, and the usage of the tool is discussed ... Source : Jerome Halmans - SQL Server Storage Engine - Blog Post

SQLIOSim II

SQLIOSim is used to validate the integrity of the IO Subsystem. In this post, the PSS Engineers provide an overview of how this tools works ... Source : PSS SQL Server Engineers - Blog Post

SQLIO Disk Subsystem Benchmark Tool

SQLIO is a tool provided by Microsoft which can also be used to determine the I/O capacity of a given configuration ... Source : Microsoft.com - Download

Disk Queue Length: Some Data Points may Help!

An analysis of the usefulness of the "Disk Queue Length" performance counter ... Source : Linchi Shea - SQLBlog.com - Blog Post

SQL Server Urban Legends Discussed

True or False ? SQL Server Uses One Thread Per Data File -- A Disk Queue Length greater than 2 indicates an I/O bottleneck ... Source : Bob Dorr - Microsoft PSS SQL Support - Blog Post

Deploying SQL Server on a SAN

This series of blog posts from the SQL CAT team covers a wide ranging set of topics such as virtualization of physical disks, RAID groups, creation and sizing of LUNs, alignment of sectors, NTFS format size, base lining of SAN throughput, dynamic and basic disks, sizing and allocation of Windows files, isolation of data, index and log objects and performance monitoring. The provided link is part 1. Part 2 is here and Part 3 is here ... Source : Prem Mehra and Mike Ruthruff - SQL Server Customer Advisory Team - Blog Post

Tuning SQL Server 2005 Performance for Hitachi SAN's

This article is specific to Hitachi (HDS) SAN's, but gives an excellent insight into SAN performance tuning concepts for SQL Server regardless of the SAN vendor ... Source : Hds.com

Fibre Channel / iSCSI Performance Comparison

iSCSI shows promise as it leverages the features of Ethernet topologies and TCP/IP - encapsulating the SCSI command set over IP. Read this report by NetApp which provides insight into the topic of FCP versus iSCSI for DSS workloads using SQL Server 2005 ... Source : Microsoft.com - Whitepaper

Deployments and Tests in an iSCSI SAN

iSCSI SANs offer an alternative for building Storage Area Networks. Consolidating storage in a SAN offers storage management and scaling benefits for datacenters. iSCSI support in Windows Server 2003 makes connecting servers to an iSCSI SAN easy and affordable. This paper describes the deployment and testing results of SQL Server 2005 using the Microsoft iSCSI Initiator with an EqualLogic iSCSI SAN. It helps you understand best practices and the benefits of using an iSCSI SAN with SQL Server 2005 ... Source : Microsoft.com - MSDN Article

I/O Basics

This white paper introduces new terms, discusses maintenance and configuration issues, new and improved error messages, and I/O enhancements. After reading this document you will better understand SQL Server I/O needs and capabilities in SQL Server 2000 Service Pack 4 and SQL Server 2005 ... Source : Microsoft.com - Technet Article

I/O Subsystem Performance Tuning

This paper considers performance from both the hardware and software perspectives. In addition, it discusses tools for storage subsystem analysis and design and provides rules of thumb and guidelines for system design and to solve the performance bottlenecks in specific configurations ... Source : Microsoft.com - Article

How many files should a database have?

In part 1 of this posting, the SQLCat team answer this question from an OLAP workload perspective ... Source : SQLCat.com

Solid State Disk IO

Chad compares SQLIO figures from 4 different platforms, one of which uses SSD (Solid State Disk). Interesting results … ... Source : Chad - sqlstuff.info - blog post

SQL Server and SANs: The QueueDepth Setting of a Host Bus Adapter (HBA)

Great blog post from Linchi Shea on I/O throughput variability when altering the Queue Depth settings on the HBA ... Source : Linchi Shea - SQLBlog.com - Blog Post

SAN vs Direct Attach Storage - The winner is...

SANs are blisteringly fast right? Way faster than local attached storage! Not always, as this arcticle points out ... Source : sqlteam.com - article

Google Disk Failure Paper

An interesting analysis of disk performance and failure rates by Google ... Source : Google.com

CPU

SQL Server Consolidation on the 64-Bit Platform

This white paper discusses lessons learned during test scenarios in which Microsoft deployed and operated a 64-bit server to consolidate Microsoft SQL Server instances that previously resided on separate physical computers ... Source : Microsoft.com - Whitepaper

64 Bit SQL Server

Adam Machanic discusses the advantages of a 64 bit SQL Server environment ... Source : searchsqlserver.techtarget.com - Article

Advantages of a 64-bit Environment

Review this white paper to learn about the two key factors motivating the rapid adoption and deployment of SQL Server 2005 (64-bit): Clear industry dynamics supporting the longer-term prospects of 64-bit computing and Specific benefits that SQL Server 2005 (64-Bit) bring to customers ... Source : Microsoft.com - Whitepaper

Will 64-bit increase the performance of my SQL Server application?

With 64-bit servers becoming more mainstream and SQL Server customers running more deployments on native 64-bit Windows there is a common misperception that running on 64-bit will always provide an increase in performance over 32-bit ... Source : Microsoft ISV Program Management Team Blog – Blog Post

64 Bit Solutions on SQL Server 2005

Stuart Ozer's (SQL CAT Team) Powerpoint presentation from SEAS 2006 on 64 Bit SQL Server platform benefits and best practices ... Source : Microsoft.com - Download

Determining Page File Size for 64 Bit Systems

Discusses page file usage and management and covers the special case for 64 bit systems ... Source : Microsoft.com - Knowledge Base Article

Is my system CPU bound ?

In this log post, Slava uses DMV's to answer questions such as 'Do I need more CPUs ?', 'Should I configure SQL Server to use more threads ?', and 'What is affinity of my schedulers to CPUs ?' ... Source : Slava Osk - Blog Post

Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005

This paper explains how batches are cached and reused in SQL Server 2005, and suggests best practices on maximizing reuse of cached plans. It also explains scenarios in which batches are recompiled, and gives best practices for reducing or eliminating unnecessary recompilations. ... Source : Microsoft.com - Technet Article

Ad Hoc vs. Parameterized

Linchi blogs about SQL Server's Forced Parameterization setting and its effects on transactions/sec ... Source : Linchi Shea - SQLblog.com - Blog Post

Signal Waits, CPU and SQLOS Scheduler

High signal waits = CPU pressure right? Maybe not. In this post, Linchi refers to a blog from Mario Broodbakker who argues that it perhaps points to pressure on the SQLOS Scheduler ... Source : Linchi Shea - SQLblog.com - Blog Post

Multiple files for multiple CPUs

A common practice for multi core boxes is to create 1 file per cpu core. Paul addresses the merits of this in this blog post ... Source : Paul S. Randal - sqlskills.com - blog post

Issues with Hyperthreading CPU's

Our customers observed very interesting behavior on high end Hyperthreading (HT) enabled hardware {another blog on hyperthreading issues from Kevin Kline} ... Source : Slava Osk - Blog Post

Load Testing

SQL Server query performance testing tool

A great (free) tool from Adam Machanic that allows you to perform simple load testing of a stored procedure or T-SQL command. It allows randomization of parameters and includes basic reporting of resource use. This is a great tool for testing contention issues and validating performance increases/decreases from code changes ... Source : DataManipulation.net

Trace Replay Tools

This post discusses the pros and cons of two trace replay tools used to simulate production load in test environments; SQL Profiler and Ostress, now included in RML Utilities. As well as providing a description of each tool, the post provides recommendations on which tool is appropriate for different scenarios ... Update; This codeplex tool can also be used to generate a load test using a Profiler trace ... Source : Microsoft SQL ISV Program Management Team - Blog Post

Create fake statistics for testing query plans using undocumented UPDATE STATISTICS commands

This technique allows you to test T-SQL performance and see the expected results against varying table sizes, without actually need to load/remove data ... Source : SQL Server Query Optimization Team - Blog Post

Did You Know? Run a batch multiple times

Kalen discusses a feature not generally know about the batch separator command 'GO' - that it takes an integer parameter and will repeat the batch that many times ... Source : Kalen Delaney - SQLBlog.com - Blog Post

Performance Baselines - Include Application Stats!

Linchi blogs about a common absentee in SQL Server performance baselines - Application Stats. He argues that as well as including SQL related performance data, application stats should be included ... Source : Linchi Shea - SQLblog.com - Blog Post

Performance Tuning Methods

SQL Nexus

SQL Nexus is a tool that helps you identify the root cause of SQL Server performance issues. It loads and analyzes performance data collected by SQLDiag and PSSDiag. Further information this tool is discussed in this blog post from Ken Henderson ... Source : Codeplex.com

A SQL Profiler trace Swiss Army Knife

Have you ever needed to find your most expensive queries and quickly grew weary of writing T-SQL against trace tables to try to ferret them out? Have you ever had to wade through gigabytes of trace data just to find one ill-behaving query? Have you ever struggled to decide what performance metrics really matter when analyzing Profiler traces: duration, reads, writes, etc? Ken Henderson recently wrote a tool to address precisely these issues ... Source : Ken Henderson - Blog Post

RML Utilities for Microsoft SQL Server Released

The Microsoft SQL Server support team uses several internally-written utilities to make it easier to work on a typical customer support case. These utilities may also be useful to database developers and system administrators. With the RML Utilities you can answer questions such as the following; 1) Which application, database or login is consuming the most resources, and which queries are responsible for that. 2) Whether there were any plan changes for a batch during the time when the trace was captured and how each of those plans performed. 3) What queries are running slower in today's data as compared to a previous set of data ... Source : Microsoft.com - PSS Server Engineers - Blog Post

Performance Troubleshooting Flowchart

The SQLCat team keep delivering great content. This one's no exception; a flowchart to address the most common performance and scalability issues that crop up. Read left to right and top down, it addresses the big issues first such as table scans and out of date stats and includes links to recommended reading. Great stuff ... Source : Sharon Bjeletich - SQLCat.com

Simulating Network Latency

Part of any good testing strategy is simulating the network conditions that exist in the production environment. One of the network attributes that needs to be taken into account is the latency, and is a particular issue with sites connected with high latency links such as satellite connections. In this post, John Paul Cook blogs about a tool that can be used to simulate higher network latency ... Source : John Paul Cook - SQLBlog.com - Blog Post

Performance Tuning with Waits and Queues

Helps identify the areas of slow performance by looking at the problem from two directions called Waits and Queues. An analysis of Waits indicates where SQL Server is spending lots of time waiting. In addition, the biggest waits point out the most important or relevant queues ... Source : Microsoft.com - Technet Article

Improving Performance with SQL Server 2005 Indexed Views

This document describes the improved indexed views capability of SQL Server 2005 Enterprise Edition. Indexed views are explained and specific scenarios in which they may provide performance improvements are discussed ... Source : Microsoft.com - Technet Article

Troubleshooting Performance Problems in 2005

This paper provides step-by-step guidelines for diagnosing and troubleshooting common performance problems by using publicly available tools such as SQL Server Profiler, System Monitor, and the new Dynamic Management Views in SQL Server 2005 ... Source : Microsoft.com - Technet Article

eBook :: Making SQL Server Perform

eBook from Kalen Delaney. This three-chapter eBook offers insight into SQL Server 2005 performance and provides tips for improving user management, disk (physical) management and application management (query plans). Covers new 2005 features including DMV's ... Source : WindowsITPro.com

Database Engine Tuning Advisor (DTA) in SQL Server 2005

Given a workload of SQL queries and updates, DTA recommends an appropriate physical design, and generates a script to implement the recommended physical design ... Source : Microsoft.com - Technet Article

sys.dm_exec_query_stats

The sys.dm_exec_query_stats DMV can be used to identify long running queries. In this article, Greg Larsen covers this DMV and a few other techniques for identifying slow running queries ... Source : sql-server-performance.com - Article

Transaction Concurrency

Row Level Versioning & Database Concurrency

This white paper focuses on concurrency enhancements in SQL Server 2005. On the server side, it covers all the features of SQL Server that leverage RLV technology. These include the new features: Snapshot Isolation, Multiple Active Result Sets (MARS) and Online Index Rebuild. RLV is also used in SQL Server 2005 to support database triggers, so the differences in trigger behaviour between SQL Server 2000 and 2005 are also discussed. On the client side, concurrency enhancements covered include concurrency in CLR objects, transaction control from the new SQL Native Client, Windows Enterprise Services and queued components, and concurrency using Service Broker enabled applications ... Source : Microsoft.com - Technet Article

Previously committed rows might be missed if NOLOCK hint is used

This blog post from Lubor Kollar explains some of the lesser known issues around the NOLOCK hint ... Source : Lubor Kollar - SQL Server Customer Advisory Team - Blog Post

Version Based Transaction Isolation

Microsoft SQL Server 2005 provides nonlocking, nonblocking read consistency to your users through snapshot isolation and read committed isolation using row versioning. Find out when you can use these features to improve performance and reduce latency in your applications ... Source : Microsoft.com - MSDN Article

Scalability / Design

SQL Server Scalability FAQ ... Source : Microsoft.com - Article

Planning, Implementing, and Administering Scaleout Solutions with SQL Server 2005

This white paper describes planning guidelines and administration tasks to consider when implementing a scaleout solution with SQL Server 2005. Different scaleout features are compared to determine when to use a particular feature ... Source : Microsoft.com - Technet Article

Scalable Shared Databases

Shared Scalable Databases are a feature of SQL Server Enterprise edition to access the same database hosted on a SAN from several server instances. That means that read-only workloads can be scaled out by adding additional servers accessing the same database, and each server operates with it’s own CPU’s, RAM and TempDB ... Source : Microsoft.com - Knowledge Base Article

Physical Database Storage Design

This article provides a guide for physical storage design and gives recommendations and trade-offs for physical hardware design and file architecture ... Source : Microsoft.com - Technet Article

Internals, Troubleshooting, and Best Practices for use of Scaleout Technologies in SQL Server 2005

This white paper describes the internal details of SQL Server 2005 scaleout technologies, and suggests troubleshooting techniques for technical issues involved in scaleout. In addition, best practices are suggested for each technology ... Source : Microsoft.com - Technet Article

A Rose By Any Other Name

Does having duplicate copies of the same stored procedure increase transactions/sec throughput? In this blog post, Linchi explains that the answer is most definately YES, depending on the load. He also mentions that this is a technique used in some of the TPC-C benchmark tests ... Source : Linchi Shea - SQLblog.com - Blog Post

Solutions for Highly Scalable Database Applications: An analysis of architectures and technologies

Understanding the nuanced differences between two Relational Database Management Systems (RDBMS) products like Oracle 10g and SQL Server 2005 can be difficult for even the most seasoned database professional. In this paper, Performance Tuning Corporation compares and provides an analysis of the differences of these two RDBMS products in their ability to "scale-out" and "scale-up". From the paper, you will see how SQL Server 2005 supports both "scale-out" and "scale-up" while Oracle 10g offers "scale-out" only technologies ... Source : Microsoft.com - Whitepaper

Performance Enthusiasts

Jim Gray's Home Page

Jim Gray (RIP ?) is a researcher and manager of Microsoft Research's eScience Group. He continues a long-standing interest on building super computers with commodity components, thereby reducing the cost of storage, processing, and networking by factors of 10x to 1000x over low-volume solutions ... Source : Research.Microsoft.com

Maximum Performance, Minimum Cost

Great article from sqlmag.com that discusses the building of a prototype high performance server from commodity parts. The final product is a system that claims to compete with mega servers costing 20 times the price ... Source : SQLMag.com

(More) Maximum Performance, Minimum Cost

Great thread discussing how to achieve the best disk performance for a data warehouse system using locally attached storage instead of SAN disk, using low end servers from Dell and HP ... Source : SQL-Server-Performance.com

NUMA - Hard and Soft

What is NUMA? What about Hard and Soft NUMA? PSS answers these questions and more ... Source : PSS SQL Server Engineers - Blog Post

NUMA Systems

Understanding Non Uniform Memory Access (NUMA)

Microsoft SQL Server 2005 is non-uniform memory access (NUMA) aware, and performs well on NUMA hardware without special configuration. As clock speed and the number of processors increase, it becomes increasingly difficult to reduce the memory latency required to use this additional processing power. To circumvent this, hardware vendors provide large L3 caches, but this is only a limited solution. NUMA architecture provides a scalable solution to this problem. SQL Server 2005 has been designed to take advantage of NUMA-based computers without requiring any application changes ... Source : Microsoft.com - MSDN Article

RAM

Procedure Cache usage in 64Bit Systems

One of the great things about 64 bit systems is that the procedure cache has access to the full amount of memory, unlike 32 bit systems where AWE only provides access to memory above 4GB to the data cache. Whilst this is a big improvement, certain (bad) applications that do not parameterize their queries can overwhelm the cache with query plans that will never be reused limiting the effectiveness of the cache for data access. In this post, Lara looks at some of the ways we can tame such applications, including Forced Parameterization, Plan Guides and DBCC free proc cache ... Source : Lara Rubbelke - SQLBlog.com - Blog Post

Optimize for ad hoc workloads

In this post, Adam covers a new option in SQL Server 2008 called "Optimize for Ad Hoc Workloads" that enables the procedure cache to be used more effectively for applications with lots of ad hoc sql and little parameterization ... Source : Adam Machanic - SQLBlog.com - Blog Post

Parameterized queries and procedure cache

Perhaps the best blog post I've seen on the effect of non parameterized sql on the procedure cache. Tony uses an example that exhausts the procedure cache, and rewrites it using parameterization. He also shows how the procedure cache can be inspected to see the level of usage. Great stuff ... Source : Tony Rogerson - SQLBlogcasts.com - Blog Post

Plan Cache Sizing Q & A

In this post, Kalen answers questions originating from her SQL Server Magazine article on Plan Cache Sizing. How to calculate target memory and determining plan cache pressure limits ... Source : Kalen Delaney - SQLblog.com - Blog Post

Does SQL Server always respond to memory pressure?

In this blog post, Slava talks about using the Max Server Memory option in SQL 2005, and have some suggested values based on total server memory. He also touches on the use of the "max worked threads" option ... Source : Slava Osk - Blog Post

Paging Issue in x64 versions of SQL 2005

Whilst this KB Article is focused on a particular paging issue in SQL 2005, it does provide a good overview of how paging works in 64 bit Windows/SQL ... Source : Microsoft.com - KB Article

SP2 does NOT limit the amount of plan cache you can have

Great blog post from Kalen covering the plan cache hard limit (or lack of) depending on the version of SQL Server you are running, and the differences in possible plan cache size between 32 and 64 bit versions ... Source : Kalen Delaney - SQLBlog.com - Blog Post

Flash Memory and Databases

Flash memory is coming. How will it impact on DBMS's like SQL Server? Lichi blogs about Goetz Graefe's update on Gray and Putzolo's original five-minute rule which was written in 1985 and subtitled Trading Memory for Disc Accesses ... Source : Linchi Shea - SQLblog.com - Blog Post

Configuring Windows 2003 ( x64 ) for SQL Server

A great blog from Grumpy Old DBA! In it he discusses configuring Windows x64 for SQL Server and covers memory allocation, CPU allocation and page file sizing ... Source : Grumpy Old DBA

Where does Time Go?

Linchi blogs about a whitepaper from 1999. He writes .... "The key conclusion of the paper is that when data is memory resident, the main bottleneck to the DBMS performance is L2 data cache misses, and to some extent L1 instruction cache misses. This conclusion appears to be valid even with today’s commercial DBMSs, given that the conclusion is still being quoted in recently published studies. If you are interested in the DBMS performance with respect to processors and memory hierarchies, I highly recommend this paper" Update : Linchi blogs about an update to this paper using more recent CPU technology... Source : Linchi Shea - SQLblog.com - Blog Post