SELECT*

The Activity Monitor gives you a view of current Connections on instance.The monitor can be used to determine whether you have any processes blocking other processes.To open Activity Monitor in Management Studio,right click on server in the object explorer,Then select Activity Monitor. Session ID: The unique number assigned to a process connected to SQL Server. This is also called a SPID. An icon next to the number represents what is happening in the connection. If you see an hourglass, you can quickly tell that the process is waiting on or is being blocked by another connection. User Process Flag: Indicates whether processes that are internal SQL Server processes are connected.These processes are filtered out by default. You can change the value to see the SQL Server internal processes by clicking the drop down and selecting the appropriate value. Login: The login to which the process is tied. Database: The current database context for the connection. Task State: Indic...

In the sql server configuration Manager once we find the services. Right click on the service and go to properties and Advanced tab. Check the start up parameters. Below are the description for the parameters. -d switch specifies the database file -l switch specifies the log file -T which enables you to start given trace flags for all the connections for sql server instance. To monitor trace flags –T1204 and to turn on trace flag to monitor deadlocks in the instance. -f switch places sql server in minimal mode and only allow single connection. –f option correct the memory setting. -g switch is used to reserve the additional memory outside SQL Server main memory pool for use by extended stored procedures. If the –g switch is not used the default of 256MB of memory is allocated to this area. -m the “-m” switch puts sql server in single-user mode (sometimes called master recovery model) and suspends the CHECKPOINT process which writes data from disk to...

The below explanation about when we install sql services , we need to tell which service need to run on which. So the below are differences between the account. Domain Account: This is an active directory domain account that you create and is the preferred account type for SQL Server Services needing network access. Local System Account: This is highly privileged account you should not use for services. Local Service Account: This is special preconfigured account that has the same permission as members of the User Group.Network access is done as a null session with no credentials. Network Service Account: This account is the same as the Local Service Account,except that network access is allowed,credentialed as the computer account.Do not use this account for SQL Server or SQL Agent Service accounts. Local Server Account : This is a local Windows account that you create. This is the most secure method you can use for services that do not need network acces...

Please find the below image how CLUSTERED INDEX  works. And Its Architecture in the image.

I created .fmt file by running above command in the command prompt. Go to run click cmd and write the code above and click enter .FMT fie would be generate in the respective path.