SQL Server Performance Tuning

SQL Server Hardware Resource Planning

There is a growing demand for high performing, fast response applications in today’s industry. In many organizations, a SQL Server 2000 server may be dedicated for the use of one particular application. From the ground up, analyzing and determining hardware requirements best suited for individual specific applications may help these application provide the timely response […]

How to Select Indexes for Your SQL Server Tables

Indexing is one of the most crucial elements in increasing the performance of SQL Server. A well-written query will not show its effectiveness unless powered by an appropriate index or indexes on the table(s) used in a query, especially if the tables are large. Indexes exist to make data selection faster, so the focus of […]

An Interview with the Authors of SQL Performance Tuning

Peter Gulutzan and Trudy Pelzer are the co-authors of SQL Performance Tuning, a new book on how to performance tune SQL code. In this interview, the authors tell us a little about their book and offer some advice for DBAs and SQL developers. Please tell us about yourselves. PETER GULUTZAN: I worked for a few […]

SQL Server Quantitative Performance Analysis

NOTE: See bottom of page to access all of the available articles. Abstract Quantitative models for the SQL Server internal query cost formulas and the true measured query costs for basic SQL operations are presented. The internal cost formulas are used by SQL Server’s Query Optimizer to determine the execution plan based on existing indexes […]

Processor Performance, 2002

Processor performance is a complex subject and cannot be characterized in a meaningful manner with a single number or benchmark score. Some insight into the factors that influence performance can be gained by examining performance across a range of processor characteristics, including frequency, cache size, bus bandwidth, and processor architecture. The best known database benchmarks […]

SQL Server Database Index Performance Checklist

Return to Previous Article in the Series Index Performance Audit Checklist Indexing Checklist Your Response Have you run the Index Tuning Wizard recently? Does every table in each database have a clustered index? Are any of the columns in any table indexed more than once? Are there any indexes that are not being used in […]

Gigabit Ethernet Direct Connect Networking

A useful device in Ethernet networking is the cross-over cable. There are circumstances where it is necessary to network two computers together and nothing else. Examples include test environments and clustered systems. In such cases, it is desired to avoid the need for a dedicated network switch. On Ethernet and Fast Ethernet networks, this is […]

SQL Server Database Settings Performance Checklist

Return to Previous Article in the Series Performance Audit Checklist Database Configuration Settings Default Value Current Value auto_close off auto_create_statistics on auto_update_statistics on auto_shrink off read_only off torn_page_detection on in 2000off in 7.0 compatibility level 80 for 200070 for 7.0 database auto grow on transaction log auto grow on Enter your results in the table […]

SQL Server Configuration Performance Checklist

Return to Previous Article in the Series  Performance Audit Checklist SQL ServerConfiguration Settings AdvancedSetting? RequiresRestart? Default Value Current Value affinity mask  Yes Yes 0 awe enabled  Yes Yes 0 cost threshold for parallelism  Yes No 5 cursor threshold  Yes No -1 fill factor (%)  Yes Yes 0 index create memory (KB)  Yes No 0 lightweight pooling […]

Operating System Performance Checklist

Return to Previous Article in the Series Performance Audit Checklist  SQL Server Hardware Characteristics Describe Here  Number of CPUs  CPU MHz  CPU L2 Cache Size  Physical RAM Amount  Total Amount of Available Drive Space on Server  Total Number of Physical Drives in Each Array  RAID Level of Array Used for SQL Server Databases  Hardware vs. […]
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 |