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

Maximum number of supported nodes in a cluster

http://support.microsoft.com/kb/288778

OR

Differene between affinity mask and max degree of parallelism

Affinity mask is instance level configuration
Degree of Parallelism is Query level configuration
----------------------------------------------------
If you have Multiple physical processors or logical Cores in your machine .It is very easy to understand the benefit of Afinitymask.For example u have 4 cores and 2 instances in your machine then ,u can share processors for each instance like 2 cores per instance.(for better resource balance).


Maxdop is for query execution purpose. By default it will be on for all processors.(All processors will work on one query execution).You can change this behavior for particular query like alter index .for ex if u give maxdop =2 while executing query ,2 processors will dedicated for this query execution where as other processors can handle server work load.

LOG TRUNCATION

Log truncation occurs at these points:
======================================
>> At the completion of any BACKUP LOG statement.

>> Every time a checkpoint is processed, provided the database is using the simple recovery model. This includes both explicit checkpoints resulting from a CHECKPOINT statement and implicit checkpoints generated by the system.

>> The exception is that the log is not truncated if the checkpoint occurs when a BACKUP statement is still active. For more information about the interval between automatic checkpoints, see Checkpoints and the Active Portion of the Log..

>> Transaction logs are divided internally into sections called virtual log files. Virtual log files are the unit of truncation. When a transaction log is truncated, all log records before the start of the virtual log file containing the MinLSN are deleted.

CHECKPOINT

Writes all dirty pages for the current database to disk. Dirty pages are data pages that have been entered into the buffer cache and modified, but not yet written to disk.

Events That Cause Checkpoints
=============================
>> Before a database backup, the Database Engine automatically performs a checkpoint so that all changes to the database pages are contained in the backup.

>> The active portion of the log exceeds the size that the server could recover in the amount of time specified in the recovery interval server configuration option.

>> The log becomes 70 percent full, and the database is in log-truncate mode.

>> A database is in log truncate mode when both these conditions are TRUE: the database is using the Simple recovery model, and, after execution of the last BACKUP DATABASE statement that referenced the database, one of the following events occurs:

> A minimally logged operation is performed in the database, such as a minimally logged bulk copy operation or a minimally logged WRITETEXT statement is executed.

> An ALTER DATABASE statement is executed that adds or deletes a file in the database.

>> Also, stopping a server issues a checkpoint in each database on the server. The following methods of stopping SQL Server perform checkpoints for each database:

Using SQL Server Configuration Manager.

Using SQL Server Management Studio.

Using the SHUTDOWN statement.

Using the net stop mssqlserver command in a command-prompt window.

Using Services in Control Panel, selecting mssqlserver, and clicking Stop.

Bringing an instance offline in a cluster..

> A BACKUP LOG statement referencing the database is executed with either the NO_LOG or TRUNCATE_ONLY clauses.

> A nonlogged operation is performed in the database, such as a nonlogged bulk copy operation or a nonlogged WRITETEXT statement is executed.

> An ALTER DATABASE statement that adds or deletes a file in the database is executed.

Note

The SHUTDOWN WITH NOWAIT statement shuts down SQL Server without executing a checkpoint in each database. This may cause the subsequent restart to take a longer time than usual to recover the databases on the server.