Friday, March 19, 2010

A few maximum limitations for SQL Server 2005


 

Every now and then i see a question pop up that asks what is the max this or that in sql server?

Well here are some maximum values:

Bytes per short string column

8,000

  

Bytes per GROUP BY, ORDER BY

8,060

  

Columns in GROUP BY, ORDER BY

Limited only by number of bytes

  

Bytes per index key

900

  

Bytes per foreign key

900

  

Bytes per primary key

900

  

Bytes per row

8,060

  

Bytes per varchar(max), varbinary(max), xml, text, or image column

2^31-1

  

Characters per ntext or nvarchar(max) column

2^30-1

  

Clustered indexes per table

1

  

Columns per index key

16

  

Columns per foreign key

16

  

Columns per primary key

16

  

Columns per base table

1,024

  

Columns per SELECT statement

4,096

  

Columns per INSERT statement

1,024

  

Connections per client

32,767

  

Database size

1,048,516 terabytes

  

Databases per instance of SQL Server

32,767

  

Filegroups per database

32,767

  

Files per database

32,767

  

File size (data)

16 terabytes

  

File size (log)

2 terabytes

  

Foreign key table references per table

253

  

Identifier length (in characters)

128

  

Instances per computer

50 (Workgroup Edition only 16)

  

Locks per connection

Maximum locks per server

  

Locks per instance of SQL Server

Up to 2,147,483,647

  

Nested stored procedure levels

32

  

Nested subqueries

32

  

Nested trigger levels

32

  

Nonclustered indexes per table

249

  

Parameters per stored procedure

2,100

  

Parameters per user-defined function

2,100

  

REFERENCES per table

253

  

Rows per table

Limited by available storage

  

Tables per database

Limited by number of objects in a database

  

Partitions per partitioned table or index

1,000

  

Statistics on non-indexed columns

2,000

  

Tables per SELECT statement

256

  

Triggers per table

Limited by number of objects in a database

  

UNIQUE indexes or constraints per table

249 nonclustered and 1 clustered

  

User connections

32,767

  

XML indexes

249

  


 

Wednesday, March 17, 2010

How to move database physical files?

If you want to
move
database physical files to a new location, you can use the "ALTER DATABASE" statements to bring the database
offline,
MOVE the files TO the new location and map it .

Here is an example


 

EXEC
sp_helpDB
myDB


 

Note the filenames AND the physical paths


 

ALTER
DATABASE MyDB SET
ONLINE

GO

ALTER
DATABASE MyDB SET
OFFLINE

GO


 

Now it is
safe
to
move the database physical files to a new location:


 

1. Run Windows Explorer

2. MOVE the files TO new location


 


run:


 

ALTER
DATABASE MyDB


MODIFY
FILE (NAME = MyDB,


FILENAME
=
'C:\newpath\data\MyDB.mdf')

GO

The file "MyDB" has been modified in the system
catalog. The new path will be used the next
time the database
is
started.


 

ALTER
DATABASE MyDB


MODIFY
FILE (NAME = FyiCenterLog,


FILENAME
=
'C:\newpath\data\FyiCenterLog.ldf')

GO

The file "MydbLog" has been modified in the system
catalog. The new path will be used the next
time the database
is
started.


 

ALTER
DATABASE MyDB SET
ONLINE

GO


 

Now verify the new path


 

EXEC
sp_helpDB
myDB


 

Tuesday, March 2, 2010

Stress Testing tools for SQL Server


 

http://www.microsoft.com/downloads/details.aspx?familyid=9a8b005b-84e4-4f24-8d65-cb53442d9e19&displaylang=en


 

http://blogs.msdn.com/psssql/archive/2008/12/19/using-sqliosim-to-diagnose-sql-server-reported-checksum-error-824-823-failures.aspx


 

http://www.windowsitpro.com/article/john-savills-windows-faqs/what-tools-are-available-to-stress-benchmark-sql-server-.aspx


 

http://www.quest.com/benchmark-factory/