How many number of processors SQL server has?

The simple way to count the number of processors SQL Server has

exec xp_msver 'processorcount', 'processortype'

Identifying the protocols being used by current connections

There are four different Net-Library options are available for SQL Server.

Shared Memory
TCP/IP
Named Pipes
VIA (Virtual Interface Adaptor)

Below script provides you details about existing connections when connection was establish and which protocol used by particular connection.

SELECT session_id,connection_id, connect_time, net_transport, net_packet_size, client_net_address
FROM sys.dm_exec_connections

To learn more about protocol please click here

MS SQL Server 2005 - Reporting Services Fix for slow loading on first time report load

Configuring the new instance of SSRS (SQL Server Reporting Services) , i have observe that when you first time click on the SSRS reporting site it takes longer time to load sometime forever. Once it is load one time than it would be good and no issue. If you close the session and reload everything first time it takes again longer time.
I have load balancing in Reporting server so i thought it was taking longer time but after reading online material and applying following change on IIS - App Pool setting , it resolved the issue.

Select the second tab - Performance and there is setting of the App Pool, Shutdown worker processes after being idle for (time in minutes) : 20 mins. This is the default setting.Unchecked that, and you should see the performance improvement. On a site you which runs slowly you can see the direct improvement right away. It may require to restart IIS service.

How many number of TempDB data files?

TempDB system database plays as important role on SQL Server performance.

When you install SQL Server by default SQL Server will create one small TempDB data file and one log file in the default location for TempDB on your SQL Server instance. You should change the default file locations of TempDB, otherwise default file locations, the TempDB files will be in a sub-directory on the same drive where your SQL Server binary files are located. This is most likely your C: drive on your database server, which is not a good place. If this the case and your TempDB is in C: drive, good practice is to move it to fast, dedicated logical drive.

You also need to create some additional TempDB data files, which should all be the same size. If all of the TempDB data files are the same size, SQL Server will use all of them equally. The reason to have multiple data files is to reduce possible allocation contention, as objects are created and destroyed in TempDB.

Microsoft suggest to create one TempDB data file per physical process core. But now a days there is four,eight and more than that core processors. So I think this is unnecessary. In general consent you should start with four or more TempDB data files and be sure to make all of the same size and later look for allocation contention and make appropriate decision for additional TempDB data files.

Find owner of database thru T-SQL

For all the database

select suser_sname(owner_sid) from sys.databases

For specific database

select suser_sname(owner_sid) from sys.databases where name = 'Northwind'

Update Multiple Column

MS SQL Update Multiple Column in single shot

*****************

UPDATE MyTable
SET (Col1, Col2, Col3, Col4) = (SELECT a, b, c, d FROM Mytable2 WHERE e =Mytable.Col5)
WHERE Col5 IN (SELECT d FROM Table2 )

*****************

How to change SQL Server Instance Name?

Query current Instance Configuration

1. sp_helpserver
2. select @@servername

Execute the below query to change the instance name

1. sp_dropserver 'OldName'
2. go
3. sp_addserver 'NewName' , 'local'
4. go

Vefiry SQL Server Instance Config. by running following queries.

1. sp_helpserver
2. select @@servername