Maximum Capacity Details Related to Indexes in SQL Server

table.hovertable {
font-family: verdana,arial,sans-serif;
font-size:11px;
color:#333333;
border-width: 1px;
border-color: #999999;
border-collapse: collapse;
}
table.hovertable th {
background-color:#c3dde0;
border-width: 1px;
padding: 8px;
border-style: solid;
border-color: #a9c6c9;
}
table.hovertable tr {
background-color:#d4e3e5;
}
table.hovertable td {
border-width: 1px;
padding: 8px;
border-style: solid;
border-color: #a9c6c9;
}

Below are the Maximum capacity details related to Indexes Supported By SQL Server:

Max Capacity For SQL Server 2012 SQL Server 2008 R2 SQL Server 2008 SQL Server 2005
Maximum number of Clustered Indexes on a table 1 Clustered Index 1 Clustered Index 1 Clustered Index 1 Clustered Index
Maximum number of Non Clustered Indexes on a table 999 Non Clustered Indexes 999 Non Clustered Indexes 999 Non Clustered Indexes 249 Non Clustered Indexes
Maximum number of Bytes per Index Key 900 Bytes 900 Bytes 900 Bytes 900 Bytes
Maximum number of Columns per index key 16 Columns 16 Columns 16 Columns 16 Columns
Maximum number of Partitions per partitioned table or index 15,000 Partitions 1,000 Partitions 1,000 Partitions 1,000 Partitions
Maximum number of Statistics on non-indexed columns 30,000 Statistics 30,000 Statistics 30,000 Statistics 2,000 Statistics
Maximum number of XML indexes 249 XML indexes 249 XML indexes 249 XML indexes 249 XML indexes

In Detail:
SQL Server supports:

  • 1 clustered index per table.
  • 999 Non Clustered Indexes per table for SQL Server 2012, SQL Server 2008 R2, SQL Server 2008
  • 249 Non Clustered Indexes per table for SQL Server 2005
  • 900 Bytes per Index Key
  • 16 Columns per index key
  • 15,000 Partitions per partitioned table or index in SQL Server 2012
  • 1,000 Partitions per partitioned table or index in SQL Server 2008 R2, SQL Server 2008,SQL Server 2005
  • 30,000 Statistics on non-indexed columns
  • 249 XML indexes

References:
http://msdn.microsoft.com/en-us/library/ms143432.aspx
http://msdn.microsoft.com/en-us/library/ms143432(v=sql.105).aspx
http://msdn.microsoft.com/en-us/library/ms143432(v=sql.100).aspx
http://msdn.microsoft.com/en-us/library/ms143432(v=sql.90).aspx

How to connect to database in sql server management studio

Steps to connect to a database in SQL Server Management Studio (ssms)

In SSMS click on file – Connect to object explorer.

Connect Object Explorer

In the connect to server give the details of server name , login ID and password.

Connect to Server

Click on connect.

Note: Select the correct Authentication while connecting to the server. Contact your database administrator for details of the authentication that you have to use while connecting.

Causes For Network Related Errors in SQL Server

Following are the main reasons for network related errors in SQL Server.

1) Server/Instance name is wrong
Fix: Check for the correct name of the Server/Instance and try to connect again

2) SQL Server Service is not running on the Server Machine.
Fix: Start the SQL Server service on the Server Machine.
Steps to start the SQL Server Service:
– Run -> Services.msc
– RightClick on SQL Server (MSSQLSERVER)
– Click on Start

Start SQL Server Service
Start SQL Server Service

3) SQL Server instance Service is not running on the Server Machine.
Fix: Start the SQL Server instance service on the Server Machine.
Steps to start the SQL Server instance Service:
– Run -> Services.msc
– RightClick on SQL Server (instancename)
– Click on Start

4) SQL Server Browser Service is not running on the Server Machine.
Fix: Start the SQL Server Browser service on the Server Machine.
Steps to start the SQL Server Browser Service:
– Run -> Services.msc
– RightClick on SQL Server Browser
– Click on Start

5) TCP/IP is disabled on the Server Machine.
Fix: Enable TCP/IP
Steps to enable TCP IP On SQL Server
Open SQL Server Configuration Manager
– Allprograms ->( Microsoft SQL Server 2008 R2 (or) Microsoft SQL Server 2008 (or) Microsoft SQL Server 2005 ) -> SQL Server Configuration Manager

Open SQL Server Configuration Manager
Open SQL Server Configuration Manager

– Expand SQL Server Network configuration
– Click on protocols for MSSQLSERVER (If its an instance click on the respective instance name)
– RightClick on TCP/IP and click on enable

Enable TCP IP
Enable TCP IP

6) Firewall does not allow connections to SQL Server Service.
Fix: Add exception to Firewall to allow connections to SQL Server Service.

7) Firewall does not allow connections to SQL Browser Service.
Fix: Add exception to Firewall to allow connections to SQL Browser Service.

8 ) Remote connections are disabled on SQL Server
Fix: Allow remote connections on sql server
Steps:
– Connect to SQL Server on SSMS
– RightClick on Server
– Click On Properties
– In the Server Properties Popup Select Connections
– Check the Allow remote connections to Server and click on OK

Allow Remote Connections to Server
Allow Remote Connections to Server

9) Time out errors due to network latency
Fix: Increase Remote Query Time out
Steps:
– Connect to SQL Server on SSMS
– RightClick on Server
– Click On Properties
– In the Server Properties Popup Select Connections
– Increase the Remote query timeout value and click on OK

Remote Query Time Out
Remote Query Time Out

10) Invalid Credentials
Fix: Fetch the latest creadentials to login into the server or ask the administrator to give your credentials necessary oermissions on the server.

SHRINK Database

Following are the Steps to SHRINK Database in SQL Server.

1) Connect to the SQL Server in SSMS

2) Expand Databases

3) RightClick on the Database to be shrinked, Click on Tasks then on Shrink.

4) Here you will have two options:

Shrink Database
Shrink Files

Using Database option you can shrink the database as a whole
Files option lets you shrink each file associated with the database seperately.

Shrink Database
Shrink Database

5) Select Database

6) Now in the Shrink Database Popup Click OK

Shrink Database Popup
Shrink Database Popup

Why is SQL Server consuming Lot of RAM

Why is SQL Server consuming Lot of RAM

There could be multiple reasons why SQL Server is consuming lot of physical memory (RAM).

Following are the few that I would like to list out:

1) Performance of the server could be bad as the queries that are hitting the sever are not optimal.
You can further fine tune such queries.

2) Maximum and Minimum server memory configuration is not set up properly.
You can set up maximum server memory configuration so that it doesnt eat up whole of the RAM.

Following are the Steps to configure sql server memory allocation:

  • Connect to the SQL Server in SQL Server management studio
  • Right click on the server and click on properties
  • Now the Server Properties window pops up.
    In the Server Properties window Select Memory.
    Now check the values of
    Minimum Server Memory(in MB) and Maximum Server Memory(in MB)

    Configure Maximum And Minimum SQL Server Memory
    Configure Maximum And Minimum SQL Server Memory
  • Adjust these values to opimal memory values as per your SQL Server’s memory requirement.

Note: Be aware that changing the memory configuration of SQL Server might give you very bad results. Hence be cautious before making such changes.
You might also get There is insufficient system memory in resource pool ‘default’ to run this query error if memory configuration is bad.

Read SQL Server Error Log Using TSQL Query

Read SQL Server Error Log Using TSQL Query

xp_readerrorlog procedure can be used to read error logs on SQL Server.

To read error logs you can use the following query:

[sql]
EXEC sys.xp_readerrorlog
[/sql]

By default this procedure reads the Current SQL Server error log.

In order to read the archive logs give the Archive log number as a parameter.

[sql]
EXEC sys.xp_readerrorlog 1
[/sql]

The above TSQL Query reads the first archive log.

To read the SQL Server agent error log, pass the second parameter value as 2

[sql]
EXEC sys.xp_readerrorlog 0,2
[/sql]

The above TSQL Statement reads the current error log for sql server agent.

The xp_readerrorlog procedure also has the ability to search for a string in error logs.
Searching error logs for a string can be achieved using the third parameter.

[sql]
EXEC sys.xp_readerrorlog 0,1,’error’
[/sql]

This TSQL query searches the current sql server error log for the string ‘error’ and returns all the rows that coontain the string error.

It is also possible to search for two string in the error log. The second string is the fourth parameter for the procedure xp_readerrorlog.

[sql]
EXEC sys.xp_readerrorlog 0,1,’error’,’17836′
[/sql]

The above TSQL Query searches for rows that contain both the srtings ‘error’ and ‘17836’ in the current sql server error log.

NOTE:
If the first parameter value exceeds the number of archive error logs present then the procedure gives the error.

[sql]
EXEC sys.xp_readerrorlog 50
[/sql]

Whey you execute the above query and if the 50th archive error log is not present in the system then you will get the below error:

Msg 22004, Level 16, State 1, Line 0
xp_readerrorlog() returned error 2, ‘The system cannot find the file specified.’