Chapter 14 - Implementation Details

  • Article

The tables in the following topics discuss the maximum capacities and memory usage of Microsoft SQL Server objects.

Maximum Capacity Specifications

This table specifies the maximum sizes and numbers of various objects defined in Microsoft SQL Server databases, or referenced in Transact-SQL statements.

Object

SQL Server 6.5

SQL Server 7.0

Batch size

128 KB

65,536* Network Packet Size

Bytes per short string column

255

8000

Bytes per text, ntext, or image column

2 GB-2

2 GB-2

Bytes per GROUP BY, ORDER BY

900

8060

Bytes per index

900

900

Bytes per foreign key

900

900

Bytes per primary key

900

900

Bytes per row

1962

8060

Bytes in source text of a stored procedure

65025

Lesser of batch size or 250 MB

Clustered indexes per table

1

1

Columns in GROUP BY, ORDER BY

16

Limited only by number of bytes

Columns or expressions in a GROUP BY WITH CUBE or WITH ROLLUP statement

10

10

Columns per index

16

16

Columns per foreign key

16

16

Columns per primary key

16

16

Columns per base table

250

1024

Columns per SELECT statement

4096

4096

Columns per INSERT statement

250

1024

Connections per client

Max. value of configured connections

Max. value of configured connections

Database size

1 TB

1,048,516 TB

Databases per server

32,767

32,767

Filegroups per database

N/A

256

Files per database

32

32,767

File size (data)

32 GB

32 TB

File size (log)

32 GB

4 TB

Foreign key table references per table

16

253

Identifier length (in characters)

30

128

Locks per connection

Max. locks per server

Max. locks per server

Locks per server

2,147,483,647

2,147,483,647 (static)40% of SQL Server memory (dynamic)

Nested stored procedure levels

16

32

Nested subqueries

16

32

Nested trigger levels

16

32

Nonclustered indexes per table

249

249

Objects concurrently open in a server*

2 billion

2,147,483,647

Objects in a database*

2 billion

2,147,483,647

Parameters per stored procedure

255

1024

REFERENCES per table

31

63

Rows per table

Limited by available storage

Limited by available storage

SQL string length (batch size)

128 KB

128* TDS packet size

Tables per database

2 billion

Limited by number of objects in a database

Tables per SELECT statement

16

256

Triggers per table

3

Limited by number of objects in a database

UNIQUE indexes or constraints per table

249

249 nonclustered and 1 clustered

* Database objects include all tables, views, stored procedures, extended stored procedures, triggers, rules, defaults, and constraints. The sum of the number of all these objects in a database cannot exceed 2,147,483,647.

Configuration Option Specifications

Microsoft SQL Server version 7.0 contains improved algorithms for controlling computer resources. Many of the options that had to be configured manually in earlier versions of SQL Server are managed dynamically in SQL Server 7.0. These configuration options are not applicable in SQL Server 7.0 and are marked N/A in this table.

Several configuration options are still specified in SQL Server 7.0; however, instead of specifying the size of a static allocation, the options now specify the upper limit for the number of objects that are allocated dynamically as needed. These options are marked with an asterisk (*) in this table.

Configuration values

Minimum

Maximum

Minimum

Maximum

affinity mask

0

2,147483,647

0

2,147,483,647

allow updates

0

1

0

1

backup buffer size

1

32

N/A

N/A

backup threads

0

32

N/A

N/A

cost threshold for parallelism

N/A

N/A

0

32,767

cursor threshold

-1

2,147483,647

-1

2,147,483,647

database size

2

10,000

N/A

N/A

default language

0

9,999

0

9,999

default sort order id

0

255

0

255

extended memory size (MB)

N/A

N/A

0

2,147,483,647

fill factor (%)

0

100

0

100

free buffers

20

524,288

N/A

N/A

hash buckets

4,999

265,003

N/A

N/A

index create memory (K)

N/A

N/A

704

1,600,000

language in cache

3

100

3

100

language neutral full-text indexing

N/A

N/A

0

1

LE threshold maximum

2

500000

N/A

N/A

LE threshold minimum

2

500000

N/A

N/A

LE threshold percent

1

100

N/A

N/A

lightweight pooling

N/A

N/A

0

1

locks

5000

2,147,483,647

5000*

2,147,483,647*

LogLRU buffers

0

2,147,483,647

N/A

N/A

logwrite sleep (ms)

-1

500

N/A

N/A

max async IO

1

1,024

1

255

max degree of parallelism

N/A

N/A

0

32

max lazywrite IO

1

1,024

N/A

N/A

max server memory (MB)

N/A

N/A

4 *

2,147,483,647*

max text repl size

0

2,147,483,647

0

2,147,483,647

max worker threads

10

1,024

10

1,024

media retention

0

365

0

365

memory

2800

1,048,576

N/A

N/A

min memory per query (K)

N/A

N/A

512

2,147,483,647

min server memory (MB)

N/A

N/A

0*

2,147,483,647*

nested triggers (bytes)

0

1

0

1

network packet size

512

32,767

4,096

65,535

open databases

5

32,767

N/A

N/A

open objects

100

2,147,483,647

0*

2,147,483,647*

priority boost

0

1

0

1

procedure cache

1

99

N/A

N/A

Protection cache size

1

8,192

N/A

N/A

query governor cost limit

N/A

N/A

0

2,147,483,647

query wait (s)

N/A

N/A

-1

2,147,483,647

RA cache hit limit

1

255

N/A

N/A

RA cache miss limit

1

255

N/A

N/A

RA delay

0

500

N/A

N/A

RA pre-fetches

1

1,000

N/A

N/A

RA slots-per-thread

1

255

N/A

N/A

RA worker threads

0

255

N/A

N/A

recovery flags

0

1

N/A

N/A

recovery interval (min)

1

32,767

0

32,767

remote access

0

1

0

1

remote conn timeout

-1

32,767

N/A

N/A

remote login timeout

0

2,147,483,647

0

2,147,483,647

remote proc trans

0

1

0

1

remote query timeout

0

2,147,483,647

0

2,147,483,647

remote sites

0

256

N/A

N/A

resource timeout

5

2,147,483,647

5

2,147,483,647

scan for startup procs

N/A

N/A

0

1

set working set size

0

1

0

1

show advanced options

0

1

0

1

SMP concurrency

-1

64

N/A

N/A

sort pages

64

511

N/A

N/A

spin counter

1

2,147,483,647

1

2,147,483,647

tempdb in RAM (MB)

0

2,044

N/A

N/A

time slice

50

1,000

50

1,000

Unicode comparison style

N/A

N/A

0

2,147,483,647

Unicode locale id

N/A

N/A

0

2,147,483,647

user connections

5

32,767

0*

32,767 (server)*

user options

0

4,095

0

4,095

* Lower or upper limit for objects allocated dynamically.

Memory Used by SQL Server Objects Specifications

This table lists the amount of memory used by different objects in Microsoft SQL Server.

Object

SQL Server 6.5

SQL Server 7.0

Lock

60 bytes

96 bytes

Open database

1,144 bytes per server plus 160 bytes per database

2880 bytes

Open object*

240 bytes

276 bytes

User connection

44K

24K

* Open objects include all tables, views, stored procedures, extended stored procedures, triggers, rules, defaults, and constraints.

SQL Server Editions

Microsoft SQL Server comes in three editions: Standard, Enterprise, and Small Business Server (SBS). In addition, users covered by a per-seat license from any of these three editions can install a Desktop SQL Server installation on their client computer.

This table compares the capabilities of the Standard, Enterprise, and SBS editions.

Feature

SBS

Standard

Enterprise

Runs on Microsoft Windows Small Business Server

Yes

Yes

No

Runs on Microsoft Windows NT Server

No

Yes

No

Runs on Windows NT Enterprise

No

Yes

Yes

Maximum database size

10GB

Unlimited

Unlimited

Number of SMP CPUs

4

4

32

Extended memory support

No

No

Yes

Failover clustering

No

No

Yes

Supports Microsoft Search Service, full-text catalogs, andfull-text indexes

Yes

Yes

Yes

Supports Microsoft SQL Server OLAP Services

No

Yes(No user-defined cube partitions)

Yes(Includes user-defined cube partitions)

The performance of the Small Business Server Edition is limited to the throughput typical of 50 concurrent users, although individual SBS installations may be licensed for fewer than 50 users. The performance of the Standard and Enterprise editions are limited only by the hardware and operating systems on which they run.

While the Desktop edition can accept remote connections, it is intended for SQL Server clients that:

  • Operate in a mobile environment where they are sometimes not connected to their SBS, Standard, or Enterprise edition of SQL Server and need a local database to store data while disconnected.

  • Store most of their SQL-based data in an SBS, Standard, or Enterprise edition of SQL Server, but occasionally run applications that need local database storage.

The Desktop Edition server component has these capabilities:

  • Runs on Windows 95/98, Windows NT Workstation, Windows NT Server, and Windows NT Enterprise.

  • Has no limit to the size of the databases it supports.

  • Scales effectively over 2 SMP CPUs.

  • Has optimizations to minimize memory usage. The working set is minimized during startup and while the system is running.

  • Supports full merge and snapshot replication. Supports only subscriptions to transaction replications, cannot publish transaction replications.

    The Desktop server does not support these features at any time:

    • Parallel queries

    • Fiber mode scheduling

    • Read-ahead scans

    • Hash and merge joins

    • Failover clusters

    • Extended memory addressing

    For more information about additional features that are not supported when the Desktop server is running on Windows 95/98, see "SQL Server 7.0 on Windows 95/98" in this volume.

  • The Microsoft Search service cannot be installed during a Desktop server installation. Desktop servers do not support full-text catalogs and indexes. Computers running a Desktop server can use SQL Server Enterprise Manager to manage full-text indexes on computers running SQL Server Standard or Enterprise editions. Applications running on Desktop servers can also execute full-text queries against SQL Server Standard or Enterprise edition databases that have full-text catalogs and indexes.

  • OLAP Services cannot be installed during a Desktop server installation. Applications running on Desktop servers can use the client PivotTable Service.

SQL Server and Year 2000

SQL Server 7.0 has been tested against and complies with the Microsoft Year 2000 Compliance Statement. This statement can be found on the Web at https://www.microsoft.com/Windows95/downloads/contents/WURecommended/S_WUFeatured/win95y2k/Default.asp. The Microsoft Year 2000 Product Guide can also be reached from this page.

The Microsoft Year 2000 Product Guide contains up-to-date information regarding the Year 2000 (Y2K, or Year2K) compliance of different versions of SQL Server.