MS SQL Server interview question II

21. What is a Join in SQL Server? -
Join actually puts data from two or more tables into a single result set.
  22. Can you explain the types of Joins that we can have with Sql Server? -
There are three types of joins: Inner Join, Outer Join, Cross Join
  23. When do you use SQL Profiler? -
SQL Profiler utility allows us to basically track connections to the SQL Server and also determine activities such as which SQL Scripts are running, failed jobs etc..
  24. What is a Linked Server? -
Linked Servers is a concept in SQL Server by which we can add other SQL Server to a Group and query both the SQL Server dbs using T-SQL Statements.
  25. Can you link only other SQL Servers or any database servers such as Oracle? -
We can link any server provided we have the OLE-DB provider from Microsoft to allow a link. For Oracle we have a OLE-DB provider for oracle that microsoft provides to add it as a linked server to the sql server group.
  26. Which stored procedure will you be running to add a linked server? -
sp_addlinkedserver, sp_addlinkedsrvlogin
  27. What are the OS services that the SQL Server installation adds? -
MS SQL SERVER SERVICE, SQL AGENT SERVICE, DTC (Distribution transac co-ordinator)
  28. Can you explain the role of each service? -
SQL SERVER - is for running the databases SQL AGENT - is for automation such as Jobs, DB Maintanance, Backups DTC - Is for linking and connecting to other SQL Servers
  29. How do you troubleshoot SQL Server if its running very slow? -
First check the processor and memory usage to see that processor is not above 80% utilization and memory not above 40-45% utilization then check the disk utilization using Performance Monitor, Secondly, use SQL Profiler to check for the users and current SQL activities and jobs running which might be a problem. Third would be to run UPDATE_STATISTICS command to update the indexes
  30. Lets say due to N/W or Security issues client is not able to connect to server or vice versa. How do you troubleshoot? -
First I will look to ensure that port settings are proper on server and client Network utility for connections. ODBC is properly configured at client end for connection ——Makepipe & readpipe are utilities to check for connection. Makepipe is run on Server and readpipe on client to check for any connection issues.