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

Poor Performance Querys

/*-------------------------------------------------------------------------------------------------------------------------------Description : This stored procedure will send out alert email if there is a blocking which lasted more than specified duration) -- Copyright 2011 - DBATAG -- Author : DBATAG -- Created on : 09/01/2011 -- Modified on : 09/01/2011 -- Version : 1.0 -- Dependencies : -- Table Procedure Permissions -- No Dependencies No Dependencies View Server State Permissions Required ----------------------------------------------------------------------------------------------------------------------------*/ -- List expensive queries DECLARE @MinExecutions int; SET @MinExecutions = 5 SELECT EQS.total_worker_time AS TotalWorkerTime ,EQS.total_logical_reads + EQS.total_logical_writes AS TotalLogicalIO ,EQS.execution_count As ExeCnt ,EQS.last_execution_time AS LastUsage ,EQS.total_worker_time / EQS.execution_count as AvgCPUTimeMiS ,(EQS.total_logical_reads + EQS.total_logical_writes) / EQS.execution_count AS AvgLogicalIO ,DB.name AS DatabaseName ,SUBSTRING(EST.text ,1 + EQS.statement_start_offset / 2 ,(CASE WHEN EQS.statement_end_offset = -1 THEN LEN(convert(nvarchar(max), EST.text)) * 2 ELSE EQS.statement_end_offset END - EQS.statement_start_offset) / 2 ) AS SqlStatement -- Optional with Query plan; remove comment to show, but then the query takes !!much longer time!! --,EQP.[query_plan] AS [QueryPlan] FROM sys.dm_exec_query_stats AS EQS CROSS APPLY sys.dm_exec_sql_text(EQS.sql_handle) AS EST CROSS APPLY sys.dm_exec_query_plan(EQS.plan_handle) AS EQP LEFT JOIN sys.databases AS DB ON EST.dbid = DB.database_id WHERE EQS.execution_count > @MinExecutions AND EQS.last_execution_time > DATEDIFF(MONTH, -1, GETDATE()) ORDER BY AvgLogicalIo DESC ,AvgCPUTimeMiS DESC ================================================================================= Note : The above mentioned query will list down queries which at least have been executed once in the past month or the last SQL Server restart, which ever is earlier View Server State permissions are required to execute this query. The result sets is based on total average CPU and IO used per execution. This Script might take 1-5 minutes in execution , depends on data in DMV.

Script to Check SQL COnnections

/*-------------------------------------------------------------------------------------------------------------------------------Description : This stored procedure will send out alert email if there is a blocking which lasted more than specified duration) -- Copyright 2011 - DBATAG Author : DBATAG Created on : 09/01/2011 Modified on : 09/01/2011 Version : 1.0 Dependencies : Table Procedure Permissions No Dependencies No Dependencies View Server State Permissions Required ----------------------------------------------------------------------------------------------------------------------------*/ -- Connectivity informations ;WITH con AS (SELECT SES.host_name AS HostName ,CON.client_net_address AS ClientAddress ,SES.login_name AS LoginName ,SES.program_name AS ProgramName ,EP.name AS ConnectionTyp ,CON.net_transport AS NetTransport ,CON.protocol_type AS ProtocolType ,CONVERT(VARBINARY(9), CON.protocol_version) AS TDSVersionHex ,SES.client_interface_name AS ClientInterface ,CON.encrypt_option AS IsEncryted ,CON.auth_scheme AS Auth FROM sys.dm_exec_connections AS CON LEFT JOIN sys.endpoints AS EP ON CON.endpoint_id = EP.endpoint_id INNER JOIN sys.dm_exec_sessions as SES ON CON.session_id = SES.session_id) -- Detailed list SELECT * FROM con ORDER by con.TDSVersionHex,con.HostName ,con.LoginName ,con.ProgramName; /* -- Count of different connectivity parameters SELECT COUNT(*) AS [Connections #] ,COUNT(DISTINCT con.HostName) AS [Hosts #] ,COUNT(DISTINCT con.LoginName) AS [Logins #] ,COUNT(DISTINCT con.ProgramName) AS [Programs #] ,COUNT(DISTINCT con.NetTransport) AS [NetTransport #] ,COUNT(DISTINCT con.TDSVersionHex) AS [TdsVersions #] ,COUNT(DISTINCT con.ClientInterface) AS [ClientInterfaces #] FROM con */

Script to check DB Size on FileSystem

SET NOCOUNT ON DECLARE @counter SMALLINT DECLARE @counter1 SMALLINT DECLARE @dbname VARCHAR(100) DECLARE @size INT DECLARE @size1 DECIMAL(15,2) SET @size1=0.0 SELECT @counter=MAX(dbid) FROM master..sysdatabases IF EXISTS(SELECT name FROM sysobjects WHERE name='sizeinfo') DROP TABLE sizeinfo CREATE TABLE sizeinfo(fileid SMALLINT, filesize DECIMAL(15,2), filename VARCHAR(1000)) WHILE @counter > 0 BEGIN SELECT @dbname=name FROM master..sysdatabases WHERE dbid=@counter TRUNCATE TABLE sizeinfo EXEC ('INSERT INTO sizeinfo SELECT fileid,size,filename FROM '+ @dbname +'..SYSFILES') SELECT @counter1=MAX(fileid) FROM sizeinfo WHILE @counter1>0 BEGIN SELECT @size=filesize FROM sizeinfo WHERE fileid=@counter1 SET @size1=@size1+@size SET @counter1=@counter1-1 END SET @counter=@counter-1 SELECT @dbname AS DBNAME,CAST(((@size1)*0.0078125) AS DECIMAL(15,2)) AS [DBSIZE(MB)] SET @size1=0.0 END SET NOCOUNT OFF