Script to Get PK and FK

set nocount on
create table #PK(constraint_schema sysname not null, constraint_name sysname not null, sql varchar(4000) not null, constraint PK_#PK1 primary key clustered(constraint_schema, constraint_name))

create table #cols(constraint_schema sysname not null, constraint_name sysname not null, column_name sysname not null, ordinal_position int not null, constraint PK_#PKcol primary key clustered(constraint_schema, constraint_name, ordinal_position))

create table #FK(constraint_schema sysname not null, constraint_name sysname not null,
unique_constraint_schema sysname not null, unique_constraint_name sysname not null,
sql varchar(4000) not null, constraint PK_#FK primary key clustered(constraint_schema, constraint_name))

insert into #PK
select constraint_schema, constraint_name, 'ALTER TABLE ' + quotename(table_schema) + '.' + quotename(TABLE_NAME) +
' ADD CONSTRAINT ' + quotename(CONSTRAINT_NAME) +
' PRIMARY KEY ' + CASE WHEN si.indid<>1 THEN 'NON' ELSE '' END +
'CLUSTERED (>cols<) WITH FILLFACTOR=' + cast(si.OrigFillFactor as varchar) + ' ON ' + quotename(fg.groupname) AS SQL from INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC INNER JOIN sysindexes si on TC.CONSTRAINT_NAME=si.name inner join sysfilegroups fg on si.groupid=fg.groupid WHERE CONSTRAINT_TYPE IN('PRIMARY KEY','UNIQUE') and TC.CONSTRAINT_NAME not in(Select CONSTRAINT_NAME from kds.INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE where CONSTRAINT_NAME like 'p%') insert into #fk select c.constraint_schema, c.constraint_name, c.unique_constraint_schema, c.unique_constraint_name, 'ALTER TABLE ' + quotename(F.table_schema) + '.' + quotename(F.table_name) + ' ADD CONSTRAINT ' + quotename(F.constraint_name) + ' FOREIGN KEY(>cols<) REFERENCES ' + quotename(r.table_schema) + '.' + quotename(r.table_name) + '(>rcols<)' AS sql FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS F INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C ON F.constraint_schema=C.constraint_schema AND f.constraint_name=c.constraint_name AND F.constraint_type='FOREIGN KEY' INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS R ON R.constraint_schema=C.unique_constraint_schema AND r.constraint_name=c.unique_constraint_name AND r.constraint_type in ('PRIMARY KEY','UNIQUE') ORDER BY F.table_name, r.table_name insert into #cols select constraint_schema, constraint_name, COLUMN_NAME, ORDINAL_POSITION from INFORMATION_SCHEMA.KEY_COLUMN_USAGE declare @ctr int, @max int, @delim varchar(1) select @ctr=1, @max=max(ordinal_position), @delim='' from #cols set nocount on while @ctr<=@max BEGIN update P SET SQL=Replace(SQL, '>cols<', @delim + quotename(c.column_name) + '>cols<') FROM #PK P INNER JOIN #cols C ON P.constraint_schema=C.constraint_schema AND P.constraint_name=C.constraint_name WHERE C.ORDINAL_POSITION=@ctr UPDATE F SET SQL=Replace(Replace(SQL, '>cols<', @delim + quotename(c.column_name) + '>cols<'), '>rcols<', @delim + quotename(r.column_name) + '>rcols<') FROM #FK F INNER JOIN #cols C ON F.constraint_schema=C.constraint_schema AND F.constraint_name=C.constraint_name AND C.ordinal_position=@ctr INNER JOIN #cols R ON F.unique_constraint_schema=R.constraint_schema AND F.unique_constraint_name=R.constraint_name AND C.ordinal_position=R.ordinal_position select @ctr=@ctr+1, @delim=',' END set nocount on update #PK SET SQL=Replace(SQL, '>cols<', '') update #FK SET SQL=Replace(Replace(SQL, '>cols<', ''), '>rcols<', '')

select sql from #PK
select sql from #FK

--drop table #pk
--drop table #fk
--drop table #cols

Ports Used By SQL Server

Ports Used By the Database Engine

The following table lists the ports that are frequently used by the Database Engine.

Scenario	Port	Comments
SQL Server default instance running over TCP	TCP port 1433	This is the most common port allowed through the firewall. It applies to routine connections to the default installation of the Database Engine, or a named instance that is the only instance running on the computer. (Named instances have special considerations. See Dynamic Ports later in this topic.)
SQL Server named instances in the default configuration	The TCP port is a dynamic port determined at the time the Database Engine starts.Â	See the discussion below in the section Dynamic Ports. UDP port 1434 might be required for the SQL Server Browser Service when you are using named instances.
SQL Server named instances when they are configured to use a fixed port	The port number configured by the administrator.	See the discussion below in the section Dynamic Ports.
Dedicated Admin Connection	TCP port 1434 for the default instance. Other ports are used for named instances. Check the error log for the port number.	By default, remote connections to the Dedicated Administrator Connection (DAC) are not enabled. To enable remote DAC, use the Surface Area Configuration facet. For more information, see Understanding Surface Area Configuration.
SQL Server Browser service	UDP port 1434	The SQL Server Browser service listens for incoming connections to a named instance and provides the client the TCP port number that corresponds to that named instance. Normally the SQL Server Browser service is started whenever named instances of the Database Engine are used. The SQL Server Browser service does not have to be started if the client is configured to connect to the specific port of the named instance.
SQL Server instance running over an HTTP endpoint.	Can be specified when an HTTP endpoint is created. The default is TCP port 80 for CLEAR_PORT traffic and 443 for SSL_PORT traffic.	Used for an HTTP connection through a URL.
SQL Server default instance running over an HTTPS endpoint.	TCP port 443	Used for an HTTPS connection through a URL. HTTPS is an HTTP connection that uses secure sockets layer (SSL).
Service Broker	TCP port 4022. To verify the port used, execute the following query: SELECT name, protocol_desc, port, state_desc FROM sys.tcp_endpoints WHERE type_desc = 'SERVICE_BROKER'	There is no default port for SQL Server Service Broker, but this is the conventional configuration used in Books Online examples.
Database Mirroring	Administrator chosen port. To determine the port, execute the following query: SELECT name, protocol_desc, port, state_desc FROM sys.tcp_endpoints WHERE type_desc = 'DATABASE_MIRRORING'	There is no default port for Database mirroring however Books online examples use TCP port 7022. It is very important to avoid interrupting an in-use mirroring endpoint, especially in high-safety mode with automatic failover. Your firewall configuration must avoid breaking quorum. For more information, see Specifying a Server Network Address (Database Mirroring).
Replication	Replication connections to SQL Server use the typical regular Database Engine ports (TCP port 1433 for the default instance, etc.) Web synchronization and FTP/UNC access for replication snapshot require additional ports to be opened on the firewall. To transfer initial data and schema from one location to another, replication can use FTP (TCP port 21), or sync over HTTP (TCP port 80) or File and Print Sharing (TCP port 137,138, or 139).	For sync over HTTP, replication uses the IIS endpoint (ports for which are configurable but is port 80 by default), but the IIS process connects to the backend SQL Server through the standard ports (1433 for the default instance. During Web synchronization using FTP, the FTP transfer is between IIS and the SQL Server publisher, not between subscriber and IIS. For more information, see Configuring Microsoft Internet Security and Acceleration Server for Microsoft SQL Server 2000 Replication over the Internet.
Transact-SQL debugger	TCP port 135 See Special Considerations for Port 135 The IPsec exception might also be required.	If using Visual Studio, on the Visual Studio host computer, you must also add Devenv.exe to the Exceptions list and open TCP port 135. If using Management Studio, on the Management Studio host computer, you must also add ssms.exe to the Exceptions list and open TCP port 135. For more information, see Configuring the Transact-SQL Debugger.

For step by step instructions to configure the Windows Firewall for the Database Engine, see How to: Configure a Windows Firewall for Database Engine Access.

Dynamic Ports

By default, named instances (including SQL Server Express) use dynamic ports. That means that every time that the Database Engine starts, it identifies an available port and uses that port number. If the named instance is the only instance of the Database Engine installed, it will probably use TCP port 1433. If other instances of the Database Engine are installed, it will probably use a different TCP port. Because the port selected might change every time that the Database Engine is started, it is difficult to configure the firewall to enable access to the correct port number. Therefore, if a firewall is used, we recommend reconfiguring the Database Engine to use the same port number every time. This is called a fixed port or a static port. For more information, see Configuring a Fixed Port.

An alternative to configuring a named instance to listen on a fixed port is to create an exception in the firewall for a SQL Server program such as sqlservr.exe (for the Database Engine). This can be convenient, but the port number will not appear in the Local Port column of the Inbound Rules page when you are using the Windows Firewall with Advanced Security MMC snap-in. This can make it more difficult to audit which ports are open. Another consideration is that a service pack or cumulative update can change the path to the SQL Server executable which will invalidate the firewall rule.

Undocumented DBCC commands

Undocumented DBCC commands

Here you can find some useful undocumented DBCC commands.

1. DBCC BUFFER

This command can be used to display buffer headers and pages from the buffer cache.

Syntax:

dbcc buffer ([dbid|dbname] [,objid|objname] [,nbufs], [printopt])

where dbid|dbname - database id|database name objid|objname - object id|object name nbufs - number of buffers to examine printopt - print option 0 - print out only the buffer header and page header (default) 1 - print out each row separately and the offset table 2 - print out each row as a whole and the offset table

This is the example:

DBCC TRACEON (3604) dbcc buffer(master,'sysobjects')

2. DBCC BYTES

This command can be used to dump out bytes from a specific address.

Syntax:

dbcc bytes ( startaddress, length )

where startaddress - starting address to dump length - number of bytes to dump

This is the example:

DBCC TRACEON (3604) dbcc bytes (10000000, 100)

3. DBCC DBINFO

Displays DBINFO structure for the specified database.

Syntax:

DBCC DBINFO [( dbname )]

where dbname - is the database name.

This is the example:

DBCC TRACEON (3604) DBCC DBINFO (master)

4. DBCC DBTABLE

This command displays the contents of the DBTABLE structure.

Syntax:

DBCC DBTABLE ({dbid|dbname})

where dbid|dbname - database name or database ID

This is the example:

DBCC TRACEON (3604) DBCC DBTABLE (master)

The DBTABLE structure has an output parameter called dbt_open. This parameter keeps track of how many users are in the database.

Look at here for more details:
FIX: Database Usage Count Does Not Return to Zero

5. DBCC DES

Prints the contents of the specified DES (descriptor).

Syntax:

dbcc des [( [dbid|dbname] [,objid|objname] )]

where dbid|dbname - database id|database name. objid|objname - object id|object name

This is the example:

DBCC TRACEON (3604) DBCC DES

6. DBCC HELP

DBCC HELP returns syntax information for the specified DBCC statement. In comparison with DBCC HELP command in version 6.5, it returns syntax information only for the documented DBCC commands.

Syntax:

DBCC HELP ('dbcc_statement' | @dbcc_statement_var | '?')

This is the example:

DBCC TRACEON (3604) DECLARE @dbcc_stmt sysname SELECT @dbcc_stmt = 'CHECKTABLE' DBCC HELP (@dbcc_stmt)

7. DBCC IND

Shows all pages in use by indexes of the specified table.

Syntax:

dbcc ind( dbid|dbname, objid|objname, printopt = {-2|-1|0|1|2|3} )

where dbid|dbname - database id|database name. objid|objname - object id|object name printopt - print option

There is change in this command in how it is used in SQL Server 7.0, in that the printopt parameter is now no longer optional.

This is the example:

DBCC TRACEON (3604) DBCC IND (master, sysobjects, 0)

8. DBCC log

This command is used to view the transaction log for the specified database.

Syntax:

DBCC log ( {dbid|dbname}, [, type={-1|0|1|2|3|4}] )

PARAMETERS: Dbid or dbname - Enter either the dbid or the name of the database in question. type - is the type of output: 0 - minimum information (operation, context, transaction id) 1 - more information (plus flags, tags, row length, description) 2 - very detailed information (plus object name, index name, page id, slot id) 3 - full information about each operation 4 - full information about each operation plus hexadecimal dump of the current transaction log's row. -1 - full information about each operation plus hexadecimal dump of the current transaction log's row, plus Checkpoint Begin, DB Version, Max XDESID by default type = 0

To view the transaction log for the master database, run the following command:

DBCC log (master)

9. DBCC PAGE

You can use this command to view the data page structure.

Syntax:

DBCC PAGE ({dbid|dbname}, pagenum [,print option] [,cache] [,logical])

PARAMETERS: Dbid or dbname - Enter either the dbid or the name of the database in question. Pagenum - Enter the page number of the SQL Server page that is to be examined. Print option - (Optional) Print option can be either 0, 1, or 2. 0 - (Default) This option causes DBCC PAGE to print out only the page header information. 1 - This option causes DBCC PAGE to print out the page header information, each row of information from the page, and the page's offset table. Each of the rows printed out will be separated from each other. 2 - This option is the same as option 1, except it prints the page rows as a single block of information rather than separating the individual rows. The offset and header will also be displayed. Cache - (Optional) This parameter allows either a 1 or a 0 to be entered. 0 - This option causes DBCC PAGE to retrieve the page number from disk rather than checking to see if it is in cache. 1 - (Default) This option takes the page from cache if it is in cache rather than getting it from disk only. Logical - (Optional) This parameter is for use if the page number that is to be retrieved is a virtual page rather then a logical page. It can be either 0 or 1. 0 - If the page is to be a virtual page number. 1 - (Default) If the page is the logical page number.

This is the example:

DBCC TRACEON (3604) DBCC PAGE (master, 1, 1)

Look at here for more details:
Data page structure in MS SQL 6.5

10. DBCC procbuf

This command displays procedure buffer headers and stored procedure headers from the procedure cache.

Syntax:

DBCC procbuf( [dbid|dbname], [objid|objname], [nbufs], [printopt = {0|1}] )

where dbid|dbname - database id|database name. objid|objname - object id|object name nbufs - number of buffers to print printopt - print option (0 print out only the proc buff and proc header (default) 1 print out proc buff, proc header and contents of buffer)

This is the example:

DBCC TRACEON (3604) DBCC procbuf(master,'sp_help',1,0)

11. DBCC prtipage

This command prints the page number pointed to by each row on the specified index page.

Syntax:

DBCC prtipage( dbid, objid, indexid, indexpage )

where dbid - database ID objid - object ID indexid - index ID indexpage - the logical page number of the index page to dump

This is the example:

DBCC TRACEON (3604) DECLARE @dbid int, @objectid int SELECT @dbid = DB_ID('master') SELECT @objectid = object_id('sysobjects') DBCC prtipage(@dbid,@objectid,1,0)

12. DBCC pss

This command shows info about processes currently connected to the server.

Syntax:

DBCC pss( suid, spid, printopt = { 1 | 0 } )

where suid - server user ID spid - server process ID printopt - print option (0 standard output, 1 all open DES's and current sequence tree)

This is the example:

DBCC TRACEON (3604) dbcc pss

13. DBCC resource

This command shows the server's level RESOURCE, PERFMON and DS_CONFIG information. RESOURCE shows addresses of various data structures used by the server. PERFMON structure contains master..spt_monitor field info. DS_CONFIG structure contains master..syscurconfigs field information.

Syntax:

DBCC resource

This is the example:

DBCC TRACEON (3604) DBCC resource

14. DBCC TAB

You can use the following undocumented command to view the data pages structure (in comparison with DBCC PAGE, this command will return information about all data pages for viewed table, not only for particular number).

Syntax:

DBCC tab (dbid, objid)

where dbid - is the database id objid - is the table id

This is the example:

DBCC TRACEON (3604) DECLARE @dbid int, @objectid int SELECT @dbid = DB_ID('master') SELECT @objectid = object_id('sysdatabases') DBCC TAB (@dbid,@objectid)

Maximum Capacity Specifications for SQL Server

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

SQL Server Database Engine object	Maximum sizes/numbers SQL Server (32-bit)	Maximum sizes/numbers SQL Server (64-bit)
Batch size1	65,536 * Network Packet Size	65,536 * Network Packet Size
Bytes per short string column	8,000	8,000
Bytes per GROUP BY, ORDER BY	8,060	8,060
Bytes per index key2	900	900
Bytes per foreign key	900	900
Bytes per primary key	900	900
Bytes per row8	8,060	8,060
Bytes in source text of a stored procedure	Lesser of batch size or 250 MB	Lesser of batch size or 250 MB
Bytes per varchar(max), varbinary(max), xml, text, or image column	2^31-1	2^31-1
Characters per ntext or nvarchar(max) column	2^30-1	2^30-1
Clustered indexes per table	1	1
Columns in GROUP BY, ORDER BY	Limited only by number of bytes	Limited only by number of bytes
Columns or expressions in a GROUP BY WITH CUBE or WITH ROLLUP statement	10	10
Columns per index key7	16	16
Columns per foreign key	16	16
Columns per primary key	16	16
Columns per nonwide table	1,024	1,024
Columns per wide table	30,000	30,000
Columns per SELECT statement	4,096	4,096
Columns per INSERT statement	4096	4096
Connections per client	Maximum value of configured connections	Maximum value of configured connections
Database size	524,272 terabytes	524,272 terabytes
Databases per instance of SQL Server	32,767	32,767
Filegroups per database	32,767	32,767
Files per database	32,767	32,767
File size (data)	16 terabytes	16 terabytes
File size (log)	2 terabytes	2 terabytes
Foreign key table references per table4	253	253
Identifier length (in characters)	128	128
Instances per computer	50 instances on a stand-alone server for all SQL Server editions. SQL Server supports 25 instances on a failover cluster.	50 instances on a stand-alone server. 25 instances on a failover cluster.
Length of a string containing SQL statements (batch size)1	65,536 * Network packet size	65,536 * Network packet size
Locks per connection	Maximum locks per server	Maximum locks per server
Locks per instance of SQL Server5	Up to 2,147,483,647	Limited only by memory
Nested stored procedure levels6	32	32
Nested subqueries	32	32
Nested trigger levels	32	32
Nonclustered indexes per table	999	999
Number of distinct expressions in the GROUP BY clause when any of the following are present: CUBE, ROLLUP, GROUPING SETS, WITH CUBE, WITH ROLLUP	32	32
Number of grouping sets generated by operators in the GROUP BY clause	4,096	4,096
Parameters per stored procedure	2,100	2,100
Parameters per user-defined function	2,100	2,100
REFERENCES per table	253	253
Rows per table	Limited by available storage	Limited by available storage
Tables per database3	Limited by number of objects in a database	Limited by number of objects in a database
Partitions per partitioned table or index	1,000	1,000
Statistics on non-indexed columns	30,000	30,000
Tables per SELECT statement	Limited only by available resources	Limited only by available resources
Triggers per table3	Limited by number of objects in a database	Limited by number of objects in a database
Columns per UPDATE statement (Wide Tables)	4096	4096
User connections	32,767	32,767
XML indexes	249	249

1Network Packet Size is the size of the tabular data stream (TDS) packets used to communicate between applications and the relational Database Engine. The default packet size is 4 KB, and is controlled by the network packet size configuration option.

2The maximum number of bytes in any index key cannot exceed 900 in SQL Server. You can define a key using variable-length columns whose maximum sizes add up to more than 900, provided no row is ever inserted with more than 900 bytes of data in those columns. In SQL Server, you can include nonkey columns in a nonclustered index to avoid the maximum index key size of 900 bytes.

3Database objects include objects such as tables, views, stored procedures, user-defined functions, triggers, rules, defaults, and constraints. The sum of the number of all objects in a database cannot exceed 2,147,483,647.

4Although a table can contain an unlimited number of FOREIGN KEY constraints, the recommended maximum is 253. Depending on the hardware configuration hosting SQL Server, specifying additional FOREIGN KEY constraints may be expensive for the query optimizer to process.

5This value is for static lock allocation. Dynamic locks are limited only by memory.

6If a stored procedure accesses more than 8 databases, or more than 2 databases in interleaving, you will receive an error.

7If the table contains one or more XML indexes, the clustering key of the user table is limited to 15 columns because the XML column is added to the clustering key of the primary XML index. In SQL Server, you can include nonkey columns in a nonclustered index to avoid the limitation of a maximum of 16 key columns. For more information, see Index with Included Columns.

8SQL Server supports row-overflow storage which enables variable length columns to be pushed off-row. Only a 24-byte root is stored in the main record for variable length columns pushed out of row; because of this, the effective row limit is higher than in previous releases of SQL Server. For more information, see the "Row-Overflow Data Exceeding 8 KB" topic in SQL Server Books Online.

SQL Server Utility Objects

---

The following table specifies the maximum sizes and numbers of various objects that were tested in the SQL Server Utility.

SQL Server Utility object	Maximum sizes/numbers SQL Server (32-bit)	Maximum sizes/numbers SQL Server (64-bit)
Computers (physical computers or virtual machines) per SQL Server Utility	100	100
Instances of SQL Server per computer	5	5
Total number of instances of SQL Server per SQL Server Utility	2001	2001
User databases per instance of SQL Server, including data-tier applications	50	50
Total number of user databases per SQL Server Utility	1,000	1,000
File groups per database	1	1
Data files per file group	1	1
Log files per database	1	1
Volumes per computer	3	3

1 SQL Server 2008 R2 Datacenter supports a SQL Server Utility with a maximum of 200 managed instances of SQL Server. SQL Server 2008 R2 Enterprise supports a SQL Server Utility with a maximum of 25 managed instances of SQL Server. For getting started information, see Overview of SQL Server Utility.

SQL Server Data-tier Application Objects

---

The following table specifies the maximum sizes and numbers of various objects that were tested in the SQL Server data-tier applications (DAC).

SQL Server DAC object	Maximum sizes/numbers SQL Server (32-bit)	Maximum sizes/numbers SQL Server (64-bit)
Databases per DAC	1	1
Objects per DAC1	Limited by the number of objects in a database, or available memory.	Limited by the number of objects in a database, or available memory.

1The types of objects included in the limit are users, tables, views, stored procedures, user-defined functions, user-defined data type, database roles, schemas, and user-defined table types.

Replication Objects

---

The following table specifies the maximum sizes and numbers of various objects defined in SQL Server Replication.

SQL Server Replication object	Maximum sizes/numbers SQL Server (32-bit)	Maximum sizes/numbers SQL Server (64-bit)
Articles (merge publication)	256	256
Articles (snapshot or transactional publication)	32,767	32,767
Columns in a table1 (merge publication)	246	246
Columns in a table2 (SQL Server snapshot or transactional publication)	1,000	1,000
Columns in a table2 (Oracle snapshot or transactional publication)	995	995
Bytes for a column used in a row filter (merge publication)	1,024	1,024
Bytes for a column used in a row filter (snapshot or transactional publication)	8,000	8,000

1If row tracking is used for conflict detection (the default), the base table can include a maximum of 1,024 columns, but columns must be filtered from the article so that a maximum of 246 columns is published. If column tracking is used, the base table can include a maximum of 246 columns. For more information on the tracking level, see the "Tracking Level" section of How Merge Replication Detects and Resolves Conflicts.

2The base table can include the maximum number of columns allowable in the publication database (1,024 for SQL Server), but columns must be filtered from the article if they exceed the maximum specified for the publication type.

Pages and Extents

Pages and Extents

SQL Server stores data in blocks of 8 Kb called Pages and eight contiguous extents are the basic unit of storage for tables and indexes, called Extents. There are two types of extents:

Uniform extents - the entire extent contains data from one table.

Mixed extents - the extent contains data from two to eight different tables. This is the case of either tables with less than eight pages or the last pages from a table with a total number of pages multiple of eight.

Data Pages are pages that contain data from tables, there are two ways to organize such pages:

Clustered tables - tables with one clustered index, the pages are linked in a doubly-linked list using the index as a key and the index is stored as a B-tree structure. Indexed views have an identical structure as Clustered tables.

Heaps - tables with no clustered index, the data pages are stored in no particular order and not linked.

Non-clustered indexes have a B-tree index structure but the data pages are stored in no particular order.

A significant part of sql server process memory has been paged out. This may result in a performance degradation.

The number in working set and duration will vary. What happens here is SQL Server is forced to release memory to operating system because some other application or OS itself needs to allocate RAM.

We went through many support articles like:

• 918483: How to reduce paging of buffer pool memory in the 64-bit version of SQL Server 2005
• 905865: The sizes of the working sets of all the processes in a console session may be trimmed when you use Terminal Services to log on to or log off from a computer that is running Windows Server 2003
• 920739: You may experience a decrease in overall system performance when you are copying files that are larger than approximately 500 MB in Windows Server 2003 Service Pack 1

But nothing solved the problem. We still have the page out problem happening every day.

The server has 16 GB RAM where 12 GB is maximum limit allocated to SQL Server. 4 GB is left to OS and and other application. We have also turned off antivirus and any large backup job. 12 GB RAM should be plenty because there’s no other app running on the dedicated SQL Server box. But the page out still happens. When this happens, SQL Server becomes very slow. Queries timeout, website throws error, transactions abort. Sometimes this problems goes on for 30 to 40 minutes and website becomes slow/unresponsive during that time.

I have found what causes SQL Server to page out. File System cache somehow gets really high and forces SQL Server to trim down.

You see the System cache resident bytes are very high. During this time SQL Server gets much less RAM than it needs. Queries timeout at very high rate like 15 per sec. Moreover, there’s high SQL Lock Timeout/sec (around 15/sec not captured in screen shot).

SQL Server max memory is configured 12 GB. But here it shows it’s getting less than 8 GB.

While the file system cache is really high, there’s no process that’s taking significant RAM.

After I used SysInternal’s CacheSet to reset file system cache and set around 500 MB as max limit, memory started to free up.

SQL Server started to see more RAM free:

Then I hit the “Clear” button to clear file system cache and it came down dramatically.

Paging stopped. System cache was around 175 MB only. SQL Server lock timeout came back to zero. Everything went back to normal.

So, I believe there’s either some faulty driver or the OS itself is leaking file system cache in 64bit environment.

What we have done is, we have a dedicated person who goes to production database servers every hour, runs the CacheSet program and clicks “Clear” button. This clears the file system cache and prevents it from growing too high.

There are lots of articles written about this problem. However, the most informative one I have found is from the SQL Server PSS team:

http://blogs.msdn.com/psssql/archive/2007/05/31/the-sql-server-working-set-message.aspx

UPDATE – THE FINAL SOLUTION!

The final solution is to run this program on Windows Startup:

SetSystemFileCacheSize 128 256

This sets the lower and higher limit for the System Cache. You need to run this on every windows startup because a restart will undo the cache setting to unlimited.

You can run the program without any parameter to see what is the current setting.

Download the program from this page:

http://www.uwe-sieber.de/ntcacheset_e.html

Go to the end and you will get the link to the SetSystemFileCacheSize.zip

Reposting Cluster - 2008 - Part4

Installing SQL Server 2008 on a Windows Server 2008 Cluster Part 4

By: Edwin Sarmiento -- 4/2/2009

Rating: | Comments (13) | Print |

Share

We're giving away another Kindle loaded with SQL Server eBooks

Problem
In a previous tip on SQL Server 2008 Installation Process, we have seen how different SQL Server 2008 installation is from its previous versions. Now, we have another challenge to face: installing SQL Server 2008 on a Windows Server 2008 Cluster. Windows Server 2008 has a lot of differences from its previous versions and one of them is the clustering feature. How do I go about building a clustered SQL Server 2008 running on Windows Server 2008?

Solution
To continue this series on Installing SQL Server 2008 on a Windows Server 2008 Cluster, we will look at adding a node in a SQL Server 2008 failover cluster.

• Part 1 we completed the installation of the Application Server role in both of the servers that we will be using as part of our cluster.
• Part 2 walked you through the installation of the Failover Cluster Feature, validating the servers that will be a part of the cluster, and creating the cluster.
• Part 3 completed with a working SQL Server 2008 failover cluster running on a single node.
• In this tip, we will proceed to add a node in a SQL Server 2008 failover cluster and apply the latest cumulative updates.

Adding a node on a SQL Server 2008 Failover Cluster

Now that you have a working failover cluster, we will make it highly available by adding nodes. The number of nodes you can add in a failover cluster depends on the editions of SQL Server that you will use. A Standard Edition of SQL Server 2008 can support up to two (2) nodes in a failover cluster while the Enterprise Edition supports up to sixteen (16) nodes, which is practically the limit for the Enterprise Edition for Windows Server 2008. As most of the steps in this process are similar to the one when you were installing the failover cluster, I've skipped most of the screenshots.

To add a node on a SQL Server 2008 failover cluster:

1. Run setup.exe from the installation media to launch SQL Server Installation Center
2. Click on the Installation link on the left-hand side. Click the Add node to a SQL Server failover cluster link. This will run the SQL Server 2008 Setup wizard.
   There are a couple of glitches when you get to this point. One of them is a popup error with an error message "failed to retrieve data for this request" while in this step. I've seen a Microsoft Connect item on this but refers to CTP6 so I was thinking it has already been resolved. After a few searches and questions asked, SQL Server MVP Geoff Hiten advised that prior to adding another node in the cluster, any cumulative update should be pre-applied to the node before the main installation as the cluster install of the RTM version has some bugs. This creates a patched install script for the RTM installer to use. The fix started with cumulative update 1 so, technically, you can apply any cumulative update. Sounds weird, but it works. You still have to apply the patch after the installation.

   

3. In the Setup Support Rules dialog box, validate that the checks return successful results and click OK.
4. In the Product Key dialog box, enter the product key that came with your installation media and click Next.
   Again, a few glitches on this step. This might seem unusual as you are only being asked about the Product Key. There is also a Microsoft Connect item for this which basically asks you to run the setup.exe in command prompt. There is a popup error with an error message "The current SKU is invalid" while in this step. This usually happens when you use a media with a supplied product key, like the one that comes with an MSDN subscription. What worked for me was to copy the installation media on a local disk, locate the file DefaultSetup.ini file from the installation files and delete it or move it to different location. If you opt to delete the file, make sure you note down the product key written on this file as you will need to manually key this in during the installation process. This forum post will give you quite a few options to solve this issue
5. In the License Terms dialog box, click the I accept the license terms check box and click Next.
6. In the Setup Support Rules dialog box, click Install. Validate that the checks return successful results. Again, make sure to fix any errors returned by this check before proceeding with the installation.
7. In the Cluster Node Configuration dialog box, validate that the information for the existing SQL Server 2008 cluster is correct.

   

8. In the Service Accounts dialog box, verify that the information is the same as what you have used to configure the first node.

   

9. In the Error and Usage Reporting dialog box, click Next
10. In the Add Node Rules dialog box, verify that all checks are successful and click Next
11. In the Ready to Add Node dialog box, verify that all configurations are correct and click Install
12. In the Complete dialog box, click Close. This concludes adding a node to a SQL Server 2008 Failover Cluster

You can validate your cluster installation by expanding the Services and Applications node and check the cluster name of your SQL Server instance. You can now see an option to move the service to another node, in this case, the node you've just added in your failover cluster

Applying patches on a SQL Server 2008 cluster

Part of the tasks of a DBA is to apply patches on the database engine and a SQL Server 2008 failover cluster is no exception. In fact, it is not as straight-forward as applying patches and service packs on a stand-alone server. It is important to note that when applying patches or service packs to a SQL Server failover cluster, you should apply them first on the passive node. After completing the installation on the passive node, failover the SQL Server 2008 cluster resource to this node making it the active node. Once the SQL Server service and all other dependencies are up, you can, then, apply the patches on the new passive node. The latest available patch for SQL Server 2008 is cumulative update 4 and is available for request from Microsoft. For more information, check out this Microsoft KB article. You will have to request for the patch from Microsoft as it is not available from the Microsoft Download Center. The screenshots below show cumulative update 3 (version 10.0.1600.22) but the process is basically the same. Also, note that even though you may have already applied the cumulative update due to the bug mentioned above for adding a node in a failover cluster, you still have to apply the patch on both nodes

To apply patches on a SQL Server 2008 failover cluster node:

1. Run SQLServer2008-KB960484-x86.exe (this would depend on the cumulative update that you want to apply) from the hotfix package you have requested from Microsoft
2. In the Welcome dialog box, validate that the checks return successful results.

   

3. In the License Terms dialog box, click the I accept the license terms check box and click Next

   

4. In the Select Features dialog box, validate the SQL Server 2008 components by clicking on the check box. The Upgrade Status field will tell you whether or not the patch has already been applied. Click Next

   

5. In the Ready to Update dialog box, verify that all configurations are correct and click Patch

   

6. In the Update Progress dialog box, validate that the installation was successful.

   

7. In the Complete dialog box, click Close. This concludes patching the passive node of a SQL Server 2008 Failover Cluster

   

After successfully installing the patch on the passive node, move the SQL Server 2008 cluster resource to this node so it will become the new active node. Make sure that all the SQL Server 2008 cluster dependencies are online prior to applying the patch on the other node. Repeat the process outlined above to the new passive node. A more comprehensive approach for applying a SQL Server 2008 patch to a failover cluster instance is defined in this Microsoft KB article

Congratulations! You now have a working two-node SQL Server 2008 failover cluster running on Windows Server 2008.

Next Steps

• Download and install an Evaluation copy of Windows Server 2008 and SQL Server 2008 for this tip
• Review Part 1, Part 2 and Part 3 of this series
• Start working on building your test environment in preparation for building a SQL Server 2008 cluster on Windows Server 2008