Wednesday, 20 February 2013

Recompilation Of Execution Plan after plan cache flushing


In SQL Server 2005, certain database maintenance operations or certain dbcc commands such as “dbcc freeproccache” or “dbcc freesystemcache” will clear the plan cache for the instance of SQL Server. Clearing the plan cache causes a recompilation of all subsequent execution plans and cause a sudden, temporary excessive CPU usage and decrease in query performance.

The following operations cause the plan cache flushing



  • When the database is set to AUTOCLOSE = ON, an operation that initiates an automatic database shutdown clears the plan cache for the instance of SQL Server.
  • ALTER DATABASE [dbName] SET ONLINE
  • ALTER DATABASE [dbName] SET OFFLINE
  • ALTER DATABASE [dbName] SET READ_ONLY
  • ALTER DATABASE [dbName] SET READ_WRITE
  • ALTER DATABASE [dbName] MODIFY NAME = [SomeDB_Name]
  • ALTER DATABASE [dbName] MODIFY FILEGROUP Test1FG1 DEFAULT
  • ALTER DATABASE [dbName] MODIFY FILEGROUP Test1FG1 READ_WRITE
  • ALTER DATABASE [dbName] MODIFY FILEGROUP Test1FG1 READ_ONLY
  • ALTER DATABASE [dbName] COLLATE Collation_Name
  • DROP DATABASE [db_Snapshot_Name]
  • Restore database operation
  • Detach database operation
  • DBCC FREEPROCCACHE
  • DBCC FREESYSTEMCACHE

Script To fix all Orphaned users


USE [master]
GO
CREATE PROCEDURE [dbo].[SP_AutoFixOrphanUsers]

AS
SET NOCOUNT ON
---Declare Variables

DECLARE @iDBSUCCEED           INT,
            @iDBFAIL          INT,
            @iAutoID        INT,
            @iMaxAutoID     INT,
            @iRetryAttempts TINYINT,
        @cUserName      NVARCHAR(128) --Variable to hold current UserName to process
     
DECLARE @OrphanUsers TABLE
        (AutoID     INT IDENTITY(1,1),
         UserName    NVARCHAR(128))

-- Initialize Variables
--
SET   @iDBSUCCEED = 0
SET   @iDBFAIL    = -100
SET @iAutoID    = 1
SET @iRetryAttempts = 0

BEGIN TRY

    -- Add Orphan Users into the Temp Table
 -----
    INSERT INTO @OrphanUsers(UserName)
        SELECT  name
        FROM    sysusers
        WHERE   (issqluser = 1)
            AND (sid is not null and sid <> 0*0)
            AND (suser_sname(sid) is null)
        ORDER BY name

    -- Get the Max AutoID
  -----
    SELECT  @iMaxAutoID = MAX(AutoID)
    FROM    @OrphanUsers

    -- Loop through orphan users and fix those
    ---------
    WHILE @iAutoID <= @iMaxAutoID
    BEGIN

        -- Get the UserName
        -----------
        SELECT  @cUserName = UserName
        FROM    @OrphanUsers
        WHERE   AutoID = @iAutoID

            -- Check whether if that login exists here
            -- If it exists then proceed fixing the user
            ------
            IF (SELECT  COUNT(*)
                  FROM    master..syslogins
                  WHERE name = @cUserName) = 1
            BEGIN
                  -- Re-sync the currently selected orphan user
                  -----------
                  EXEC sp_change_users_login 'auto_fix', @cUserName  
            END

        -- Get Next Orphan User
        ----
        SELECT @iAutoID = @iAutoID + 1
    END
 
    -- Return Success
  ---------
    RETURN @iDBSUCCEED

END TRY

-- Error Handling
BEGIN CATCH

    -- Return Failure
      ---------
      RETURN @iDBFAIL

END CATCH


EXEC SP_AutoFixOrphanUsers

Script to get the most expensive SQL statement in addition to their Query plan


DECLARE @MinExecutions int;
SET @MinExecutions = 5

SELECT A.total_worker_time AS TotalWorkerTime
      ,A.total_logical_reads + A.total_logical_writes AS TotalLogicalIO
      ,A.execution_count As ExeCnt
      ,A.last_execution_time AS LastUsage
      ,A.total_worker_time / A.execution_count as AvgCPUTimeMiS
      ,(A.total_logical_reads + A.total_logical_writes) / A.execution_count
       AS AvgLogicalIO
      ,DB.name AS DatabaseName
      ,SUBSTRING(B.text
                ,1 + A.statement_start_offset / 2
                ,(CASE WHEN A.statement_end_offset = -1
                       THEN LEN(convert(nvarchar(max), B.text)) * 2
                       ELSE A.statement_end_offset END
                 - A.statement_start_offset) / 2
                ) AS SqlStatement
        ,C.[query_plan] AS [QueryPlan]
FROM sys.dm_exec_query_stats AS A
     CROSS APPLY sys.dm_exec_sql_text(A.sql_handle) AS B
     CROSS APPLY sys.dm_exec_query_plan(A.plan_handle) AS C
     LEFT JOIN sys.databases AS DB
         ON B.dbid = DB.database_id  
WHERE  A.last_execution_time > DATEDIFF(MONTH, -1, GETDATE())
ORDER BY AvgLogicalIo DESC
        ,AvgCPUTimeMiS DESC

Starting SQL server In single Mode using Command Net start



  • Launch the command line as administrator
  • Run the following command net start MSSQL$INSTanceName /m
  • Stop the SQL service InstanceName using Net Stop

Create a SQL Server event alert


In this Example we will configure SQL Server Agent to transmit an alert if an error of severity level 17 occurs and alert the operator Operator_admin when it happens

·         In Object Explorer, expand SQL Server Agent, right-click Alerts, and then click New Alert.
·         In the New Alert dialog box, add a new alert with the properties in the following table.

·         Configure the alert as below :

Name: Sql Server Resource alert
Type: Sql server event alert
Database Name: MyuserDB
Severity: 017-Insufficient Resources
Raise alert when Message Contains: Cleared

·         In The Response Panel select the Notify operators check box, and then select the E-mail check box for the Operator_Admin operator
·         On the Options page, select the E-mail check box, in the Additional notification message to send box, type Warning: An error occurred in MyuserDB database, and then click OK.

To test that the alert is triggered when the condition is met
·         Connect to The instance using SSMS

·         On the File menu, point to New, and then click Query with Current Connection.

·         In the query pane, type the following Transact-SQL code.

USE MyUserDB
GO
RAISERROR (‘Test Error’, 17, 1) WITH LOG

·         On the Query menu, click Execute.

·         Switch to Outlook and verify the new mail exists.

See WHO is blocking OFF


set nocount on
declare @spid varchar(10)
declare @blkby varchar(10)
declare @stmt varchar(1000)

   create table #temp ( spid integer, status_1 varchar(100), login_1 varchar(50), hostname varchar(25), blkby varchar(10),
                        dbname varchar(100), command varchar(100), cputime integer, diskio integer, lastbatch varchar(25),
                        programname varchar(255), spid2 integer, Requestid varchar(10) )


insert into #temp
exec sp_who2

declare curs cursor for
select convert(varchar(10),spid), blkby from #temp where blkby not like '%.%'

open curs

fetch next from curs into @spid, @blkby
while @@fetch_status = 0
begin
   set @stmt = 'dbcc inputbuffer(' + @blkby + ')'
   raiserror('SPID:%s is Blocking with the following statement',0,1,@blkby) with nowait
   exec (@stmt)
   raiserror('SPID that is Blocked:%s',0,1,@spid) with nowait
   set @stmt = 'dbcc inputbuffer(' + convert(varchar(10), @spid) + ')'
   exec (@stmt)
   fetch next from curs into @spid, @blkby
end

close curs

deallocate curs

select * from #temp

drop table #temp

List Datafiles names , Locations and Size of all instance Databases (best script ever)


create table #temp (Databasename varchar(100),name varchar(1000),fileid int , filename varchar(500),filegroup varchar(100) ,
                    size varchar(200),maxsize varchar(300),growth varchar(100),usage varchar(100))

declare @database varchar(100)
Declare c cursor for select name  from master..sysdatabases where dbid not in (13,14)
open c
fetch next from c into @database
while @@fetch_status=0
begin
exec('use '+@database +'
insert into #temp(name ,fileid , filename,filegroup ,
                    size ,maxsize ,growth ,usage )
 exec sp_helpfile')
update #temp set Databasename=@database where Databasename is null
fetch next from c into @database
end
close c
deallocate c

update #temp set size=replace(size,'KB','')
select * from #temp
select 'Sql Server',rtrim(convert(varchar(100),a.Filename))+'&'+left(replace(replace(b.filename,'d:\Program Files\Microsoft SQL Server\MSSQL\',''),'data\',''),26)
,a.DatabaseName,CONVERT(char(20), SERVERPROPERTY('servername'))
  from #temp a inner join (select * from #temp where usage<>'data only') b
on a.databasename=b.databasename
where a.usage='data only' order by 1



drop table #temp

Create a SQL Server Performance Condition alert


we will configure SQL Server Agent to transmit an alert if the number of connections is above 50 and alert the operator Operator_admin when it happens

  •  In Object Explorer, expand SQL Server Agent, right-click Alerts, and then click New Alert.
  • In the New Alert dialog box, add a new alert with the properties in the following table.
  • Configure the alert as below :


Name: Sql Server Performance alert
Type: Sql server Performance Condition alert
Object: Sql server General Statistics
Counter: User connections
Alert if counter rises above: 50


  • In The Response Panel select the Notify operators check box, and then select the E-mail check box for the Operator_Admin operator
  • On the Options page, select the E-mail check box, in the Additional notification message to send box, type Warning: The number of user connections has exceeded 50 on the instance NamedInstance, and then click OK.

Create a SQL Server WMI alert (CPU Workload)



         
In this example, we will create an alert in sql server to be notified when a CPU average workload is 90% within a minute

  • In Object Explorer, expand SQL Server Agent, right-click Alerts, and then click New Alert.
  • In the New Alert dialog box, add a new alert with the properties in the following table.
  • Configure the alert as below :



Name: SQL Performance CPU alert
Type: WMI event Alert
Namespace: \\.\ROOT\CIMV2
Query: SELECT * FROM __InstanceModificationEvent WITHIN 60 WHERE TargetInstance ISA “Win32_Processor” AND TargetInstance.LoadPercentage > 90





  • In The Response Panel select the Notify operators check box, and then select the E-mail check box for the Operator_Admin operator
  • On the Options page, select the E-mail check box, in the Additional notification message to send box, type Warning: The Average CPU workload has exceeded 90% on the instance NamedInstance, and then click OK

List SQL Server Tables without clustered Indexes


SELECT DISTINCT [TABLE] = OBJECT_NAME(OBJECT_ID) FROM SYS.INDEXES WHERE INDEX_ID = 0 AND OBJECTPROPERTY(OBJECT_ID,'IsUserTable') = 1 ORDER BY [TABLE]