SQL Server Performance Tuning

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

How to Interact with SQL Server’s Data and Procedure Cache

As you have worked with SQL Server, you probably have run across the terms data cache and procedure cache, and may have wondered what exactly a cache was. SQL Server is configured to use a physical pool of memory on the server, and it will allocate the majority of this memory pool to hold data […]

Server System Architecture, 2002

Intended Audience This article is intended to provide software developers and IT administrators a basis for comparing server systems. The focus is on platforms for Intel processors and Windows NT, 2000 Server operating systems. Server system architectures can change substantially in 2-4 years, so it is important to understand the new capabilities introduced in each […]

Troubleshooting SQL Server Full-Text Search Problems

Should you have problems with the SQL Server full-text search, review this troubleshooting checklist to help find potential solutions. 1. Check the hardware requirementsFor SQL Server 7.0, you should have: Alpha AXP, Intel or compatible platform Pentium 166 MHz or higher 64MB RAM or more (recommended) 180MB hard disk space For SQL Server 2000, you […]

SQL Server 7.0 and 2000 Trace Flags

Trace flags are used to temporarily enable or disable specific SQL Server database functions, allowing you to change default database behavior or to observe hidden database behavior, and will remain in effect until they are either manually turned off or SQL Server is restarted. Keep in mind that most trace flags are undocumented and Microsoft […]

SQL Server Hardware Performance Checklist

Return to Previous Article in the Series Performance Audit Checklist  Counter Name Average Minimum Maximum  Memory: Pages/sec  Memory: Available Bytes  Physical Disk: % Disk time  Physical Disk: Avg. Disk Queue Length  Processor: % Processor Time  System: Processor Queue Length  SQL Server Buffer: Buffer Cache Hit Ratio  SQL Server General: User Connections Enter your results in […]
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 |