TEMPDB

The following operations cannot be performed on the tempdb database:

Adding filegroups.

Backing up or restoring the database.

Changing collation. The default collation is the server collation.

Changing the database owner. tempdb is owned by dbo.

Creating a database snapshot.

Dropping the database.

Dropping the guest user from the database.

Enabling change data capture.

Participating in database mirroring.

Removing the primary filegroup, primary data file, or log file.

Renaming the database or primary filegroup.

Running DBCC CHECKALLOC.

Running DBCC CHECKCATALOG.

Setting the database to OFFLINE.

Setting the database or primary filegroup to READ_ONLY.

Enabling TDE

When you enable TDE, the data in a database is encrypted, however two other things occur. All subsequent transactions in the transaction log for that database are encrypted, and tempdb is encrypted.

If you have a 4 CPU socket server that has two NUMA nodes with CPUs (0,1) and (2,3), can you create a soft-NUMA node with CPUs 1 and 2?

No
Explanation:
The soft NUMA nodes that SQL Server can create cannot cross hardware NUMA boundaries. So you cannot create a soft NUMA set with CPUs 1 and 2.

Maximum Instances - SQL Server

50 instances on a stand-alone server for all SQL Server editions.

SQL Server supports 25 instances on a failover cluster.

SP_Update_stats

Many of you might have lot of tables or have large databases where you only want to update statistics using ‘UPDATE STATISTICS’ or ‘sp_updatestats’, but only for those tables that have large row modifications. Here is a script that you can use to get the output of each index that has significant row modifications. You can pipe this to a temp table and choose to update statistics on only these tables. You can do this by looking at the “ModifiedPercent” column.

select
schemas.name as table_schema,
tbls.name as table_name,
i.name as index_name,
i.id as table_id,
i.indid as index_id,
i.rowmodctr as modifiedRows,
(select max(rowcnt) from sysindexes i2 where i.id = i2.id and i2.indid < 2) as rowcnt,
convert(DECIMAL(18,8), convert(DECIMAL(18,8),i.rowmodctr) / convert(DECIMAL(18,8),(select max(rowcnt) from sysindexes i2 where i.id = i2.id and i2.indid < 2))) as ModifiedPercent,
stats_date( i.id, i.indid ) as lastStatsUpdate
from sysindexes i
inner join sysobjects tbls on i.id = tbls.id
inner join sysusers schemas on tbls.uid = schemas.uid
inner join information_schema.tables tl
on tbls.name = tl.table_name
and schemas.name = tl.table_schema
and tl.table_type='BASE TABLE'
where 0 < i.indid and i.indid < 255
and table_schema <> 'sys'
and i.rowmodctr <> 0
and i.status not in (8388704,8388672)
and (select max(rowcnt) from sysindexes i2 where i.id = i2.id and i2.indid < 2) > 0

Blocking OR Lock - Dead, Live

DEADLOCK

When two processes are waiting for each other to release locks the other needs. Each process would wait indefinitely for the other to release the lock. SQL Server detects deadlocks and choose one process as a deadlock victim, rolls back the transaction and return error 1205.

How would I face deadlock:

First I will try to find cause of deadlock. I need to know which processes led to the deadlock scenario and what resource and types of locks are involved. In SQL Server 2008 and higher, I will look output of system_health Extended Event session. It's started by default in SQL Server 2008, so if administrator didn't turn it off, we have information about processes, resources and types of locks involved in deadlock scenario. It's excellent way for retroactively finding deadlock information. And in this situation we can immediately start to work on resolving the deadlock problems.

If system_health Extended Event session is stopped I will start it to capture all detected deadlocks in the system. But I need to wait to deadlock happened again to know exactly what is going on. To capture deadlock information in older version I will:

If it's SQL Server 2005 I will turn on trace flag 1222 or will start server side trace to capture Deadlock graph. For SQL Server 2000 trace flag is 1204.

When I find information about processes, statements, resourdes and types of locks that are involved in deadlock I will run queries through DTA (Database tuning advisor). DTA will tell me if some indexes are missing. Usually deadlock can be resolved by adding missing index (adding covered nonclustered index resolved almost all my deadlocks).

If one of the involving statements is SELECT operation, I can add NOLOCK hint or set isolation level to READ UNCOMMITTED (if it’s OK that SELECT operation read uncommited data). If that’s not option, in SQL Server 2005 or higher I will set READ COMMITTED SNAPSHOT or SNAPSHOT isolation level (it will remove shared lock caused by reads). It’s always good that the query is using the minimum necessary transaction isolation level.

Also it’s good to check that transactions involved in deadlock are as short as possible and if they change the same tables, change them in the same order.

BLOCKING

Blocking occurs when one process holds a lock on a specific resource and a second process attempts to acquire a conflicting lock type on the same resource. This is normal behavior and may happen many times with no noticeable effect on system performance.

If blocking increase to the point where it impact on system performance I will first find what cause blocking (need information about queries that are involved in blocking and types of locks). I can use SQL Trace Blocked Process Report or can use DMVs (sys.dmoswaitingtasks, sys.dmtranlocks, sys.dmexecrequest, sysdmexecsql_text) to get that informations.

Solutions of blocking problems are the same as for deadlocking problems:

Identify and add missing indexes, ensure that all transactions are as short as possible, use hints to modify locking behavior, use the lowest isolation level acceptable, rewrite problematic code, ensure that indexes aren’t fragmented and that statistics are up to date.

Cluster Logs - Windows Cluster 2008

=============================================================
1. Go to CMD
2. Type as below,
cluster /cluster:nameofcluster log / gen /copy:"d:\clusterlog"
3. Read the logs from the location d:\clusterlog

=============================================================

Other Windows Servers, we can find the logs in

%systemroot%\Logs\Cluster