Computer Associates SQLSTQ99000600 Microsoft SQL Guide - Page 111

Performance Improvement Tips, Read-only FileGroup. With SQL 2005 and later, use the Partial Database

Get Computer Associates SQLSTQ99000600 - Unicenter SQL-Station 6.0.0 PDF manuals and user guides
UPC - 757943257441
View all Computer Associates SQLSTQ99000600 manuals
Add to My Manuals
Save this manual to your list of manuals

Page 111 highlights

Microsoft SQL Server Database Basics Performance Improvement Tips Consider the physical layout of your database to take advantage of the way files and FileGroups and partial backups/restores can optimize your database performance. ■ If you separate tables with high traffic estimates from tables with less expected traffic by placing them into different FileGroups, you can store the high-traffic table on high-performance disks and store the other files in another FileGroup on different disks. Queries against the a table and FileGroup backup jobs can then access your fastest devices. ■ Create Files and FileGroups on as many separate devices as possible so that queries against their tables can be processed by multiple read/write devices. ■ Put different tables used in the same queries in different FileGroups to enable parallel data searches. ■ Put the transaction log file on a disk that does not also contain data files. ■ If you are using Microsoft SQL Server 2005 or later, you can partition tables across multiple FileGroups to speed query access times (queries scan part of the data instead of all of it) and simplify tasks like rebuilding indexes. Consider horizontal or vertical partitions. See the Microsoft SQL Server documentation for more details. ■ Consider data stability to help you allocate files and FileGroups. For example, data that is static but needed for historical purposes can be assigned to a Read-only FileGroup. With SQL 2005 and later, use the Partial Database subset option to exclude Read-only FileGroups from your backup plan, improving backup time. ■ You can individually restore the files in a database in which multiple FileGroups are used. If a database is allocated on several devices and a disk fails, only the file on the failed disk must be restored. ■ Put the files expected to grow quickly in different FileGroups on separate drives. ■ As files become full, you can add files and disks to existing FileGroups, allowing SQL Server to pass data to the new file. Appendix C: Backup and Recovery Best Practices 111

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49
  • 50
  • 51
  • 52
  • 53
  • 54
  • 55
  • 56
  • 57
  • 58
  • 59
  • 60
  • 61
  • 62
  • 63
  • 64
  • 65
  • 66
  • 67
  • 68
  • 69
  • 70
  • 71
  • 72
  • 73
  • 74
  • 75
  • 76
  • 77
  • 78
  • 79
  • 80
  • 81
  • 82
  • 83
  • 84
  • 85
  • 86
  • 87
  • 88
  • 89
  • 90
  • 91
  • 92
  • 93
  • 94
  • 95
  • 96
  • 97
  • 98
  • 99
  • 100
  • 101
  • 102
  • 103
  • 104
  • 105
  • 106
  • 107
  • 108
  • 109
  • 110
  • 111
  • 112
  • 113
  • 114
  • 115
  • 116
  • 117
  • 118
  • 119
  • 120
  • 121
  • 122
  • 123
  • 124
  • 125
  • 126
  • 127
  • 128
  • 129
  • 130
  • 131
  • 132
  • 133
  • 134
  • 135
  • 136
  • 137
Microsoft SQL Server Database Basics
Appendix C: Backup and Recovery Best Practices
111
Performance Improvement Tips
Consider the physical layout of your database to take advantage of the way files
and FileGroups and partial backups/restores can optimize your database
performance.
■
If you separate tables with high traffic estimates from tables with less
expected traffic by placing them into different FileGroups, you can store the
high-traffic table on high-performance disks and store the other files in
another FileGroup on different disks. Queries against the a table and
FileGroup backup jobs can then access your fastest devices.
■
Create Files and FileGroups on as many separate devices as possible so that
queries against their tables can be processed by multiple read/write devices.
■
Put different tables used in the same queries in different FileGroups to enable
parallel data searches.
■
Put the transaction log file on a disk that does not also contain data files.
■
If you are using Microsoft SQL Server 2005 or later, you can partition tables
across multiple FileGroups to speed query access times (queries scan part of
the data instead of all of it) and simplify tasks like rebuilding indexes.
Consider horizontal or vertical partitions. See the Microsoft SQL Server
documentation for more details.
■
Consider data stability to help you allocate files and FileGroups. For example,
data that is static but needed for historical purposes can be assigned to a
Read-only FileGroup. With SQL 2005 and later, use the Partial Database
subset option to exclude Read-only FileGroups from your backup plan,
improving backup time.
■
You can individually restore the files in a database in which multiple
FileGroups are used. If a database is allocated on several devices and a disk
fails, only the file on the failed disk must be restored.
■
Put the files expected to grow quickly in different FileGroups on separate
drives.
■
As files become full, you can add files and disks to existing FileGroups,
allowing SQL Server to pass data to the new file.