SQL Server 2000 Optimization for Network Administrators and System Engineers

^{As a network administrator or system engineer, you may find yourself in a situation in which you have to buy, configure, monitor, and manage a server with a database. Ideally, the organization that you are working for should have a specialist—a database administrator (DBA)—to handle such events. Unfortunately, because of various constraints (usually financial in nature), such is not always the case. You should be able to perform basic tasks and know when to ask for specific help when issues require specific knowledge and experience that you do not have in your arsenal.
To help you make the appropriate decisions with regard to these matters, we will define the issues surrounding the following activities:
• Selecting a server to run SQL Server 2000 and satisfy the requirements of your database
• Configuring SQL Server 2000, monitoring, identifying bottlenecks, and improving performance
• Designing databases and applications
You may not be able to perform all of these activities effectively yourself, but you need to know the difference between what you can and cannot do, and when to hire the appropriate expert to achieve optimal results.}
Download

Table of Content

Introduction.................................................................. 4

System Selection and Configuration............................................ 4

	Operating System...................................................... 5

	Processors............................................................ 6

	Memory................................................................ 7

	Disk Subsystem........................................................ 9

		RAID.......................................................... 9

		Using SQL Server with Caching Disk Controllers............... 10

		Don’t Use File Groups to Improve Performance................. 10

		FAT versus NTFS.............................................. 10

	Backup Storage....................................................... 11

	Network.............................................................. 11

	Other Services....................................................... 12

Server and Database Configuration, Monitoring, and Optimization.............. 13

	Tools of the Trade................................................... 13

		Service Manager.............................................. 13

		Query Analyzer............................................... 14

		Enterprise Manager........................................... 18

		SQL Profiler................................................. 22

		Windows System Monitor....................................... 27

	Using Server Configuration Options................................... 28

		Affinity Mask................................................ 33

		Locks........................................................ 34

		Max Worker Threads........................................... 34

		Memory Options............................................... 34

		Priority Boost............................................... 35

		Query Governor Cost Limit.................................... 35

		Open Objects................................................. 35

	Setting Database Parameters.......................................... 36

		Auto Create Statistics....................................... 36

		Auto Update Statistics....................................... 37

		AutoClose.................................................... 37

		AutoShrink................................................... 37

		Torn Page Detection.......................................... 37

	Optimizing the tempdb Database....................................... 38

	Optimizing the Transaction Log....................................... 39

	Using Network Libraries.............................................. 39

	Optimizing the Backup Process........................................ 43

	Optimizing the Import Process........................................ 44

Application and Database Design.............................................. 44

	Query Optimization................................................... 45

	Using Indexes........................................................ 46

		Index Tuning Wizard.......................................... 48

	File-Database and Fat-Client Scenario................................ 54

	Using Database Access Libraries...................................... 55

	Using Cursors and Result Sets........................................ 56

	OLTP versus DSS...................................................... 57

	Normalization and Denormalization.................................... 58

	Using OLAP........................................................... 61

	Using Table Partitioning............................................. 61

	Using Federated Servers.............................................. 63