SQL Server DBA face question

1.sqlserver R2 on Windows Server R2 cluster, there are nodes A, B, now need to stop new add a node C come in and replace the existing node B, please list the necessary steps.


2.sqlserver R2 on Windows Server R2 cluster, there is a node A, b,a node memory is one times larger than B node memory, for the current primary node, in order to business needs, the need to release a node memory is to restart SQL Server, In the shortest possible time, how to keep the Cluster service from switching to the a node during the operation, please list the operation steps.


3. What does virtual logging do, and how can I see if the virtual log is impacting performance? How to control the number of virtual logs to avoid
The number of virtual logs is too large for the database performance impact?


4.sqlserver R2 on Windows Server R2, how can I avoid logging of job production to errorlog in a database backup to avoid errorlog being blown up?


5. How do I see the actual length of the text Type field in a table?


6. What is a database histogram? How do I design histogram statistics to provide database performance?


What is the understanding of the 7.sqlserver partitioned table and its impact on performance?


8.sqlserver R2 on the Windows Server R2 environment, the system log always prompts for "DNS failure to publish the Key Management Service (KMS) to the ' XXXXX ' domain", which causes the problem? How to resolve, please list the necessary commands?


9. How do I use char and varchar when I design a table? Please write down your understanding of varchar (max).


10.sqlserver R2 on Windows Server R2, to improve the speed of backup in case of CPU-saving as much as possible?


11. A table has 120 fields, 85 million data, how to delete duplicate data?

12.ASP. NET call SQL background stored procedures, sometimes suddenly become very slow, using SQL statements directly execute stored procedures is not a problem, but in the foreground call stored procedures is very slow, and after the foreground call succeeds, the call is still the same slow, but update the stored procedure again call quickly, please analyze the reason?

SQL Server R2 on Windows Server R2 environment, the database error: "

18056

The client cannot reuse a session with SPID%d, which has been reset for connection pooling. The failure ID is%d.

This error may be caused by a previous operation failure.

Please review the error log to find out the failed action that occurred just before this error message was displayed, and analyze the cause and workaround?

14. Please describe the use of the following statement? What is the use of?

DECLARE @objtype varchar (20)
DECLARE @usecounts int
DECLARE @size_in_bytes int
Set @objtype = ' adhoc '-can only be adhoc, proc, Prepared, default is Adhoc
Set @usecounts = 1--Number, default is 1
Set @size_in_bytes = 1--size, default is 1
print ' input object type is @objtype ';
print ' input cache count is @usecounts ';
print ' input cache size is @size_in_bytes ';


If @objtype in (' Adhoc ', ' proc ', ' Prepared ')
Begin
Select top Usecounts as [cache Count],
size_in_bytes/1024 as [occupies MB],
Cacheobjtype as [cache type],
ObjType as [object type],
B.text as [statement]
From Sys.dm_exec_cached_plans A,
(Select Plan_handle,
Qt.text from
Sys.dm_exec_query_stats QS
Cross APPLY sys.dm_exec_sql_text (qs.[ Sql_handle]) as QT b
where a.plan_handle=b.plan_handle and [email protected] and
[email protected] and size_in_bytes> @size_in_bytes
ORDER BY size_in_bytes Desc
End
Else
Begin
print ';
print ' system went wrong! ';
print '--------------------------';
print ' input type is incorrect, please verify ';
print '--------------------------';
End

15.sqlserver R2 on Windows Server R2 when building a SQL Server cluster, when the cluster validation check prompts "Verify SCSI-3 permanent retention failed", analyze what causes this?

How to solve?

16. In a lot of business logic, you need to count the last month's trading data on any day of the month, write a variable that automatically gets to the first and last day of the month (as today is 2011-10-9, then get to the beginning of the last month for the end of 2011-09-01 for 2011-19-90 ).

. SQL Server R2 on Windows Server R2, when transactional replication typically uses the Distribution Agent profile for the Distribution profile for OLE DB streaming, list at least three parameters that we can optimize.

18. In SQL Server R2 replication performance monitoring, we need to see the amount of data waiting to be replicated in the publication database at the publisher, what is the table name we usually look at?

19. In SQL Server R2 replication, we often use the following statement, please explain the purpose of the following command?

EXEC sp_repldone @xactid = null, @xact_segno = null, @numtrans = 0, @time = 0, @reset = 1

20. Please describe the understanding of SQL Server's always on?

SQL Server DBA face question