Windows policy Lock Pages in Memory option enabled check

SET NOCOUNT ON;

 

DECLARE @CMDShellFlag [bit] ,

        @CheckCommand [nvarchar](256);

         

 

DECLARE @xp_cmdshell_output TABLE

    (

      [output] [varchar](8000)

    );

 

IF NOT EXISTS ( SELECT  *

                FROM    [sys].[configurations]

                WHERE   [name] = N'xp_cmdshell'

                        AND [value_in_use] = 1 )

    BEGIN

         

        SET @CMDShellFlag = 1;

 

        EXEC [sp_configure] 'show advanced options', 1;

 

        RECONFIGURE;

 

        EXEC [sp_configure] 'xp_cmdshell', 1;

 

        RECONFIGURE;

 

        EXEC [sp_configure] 'show advanced options', 0;

 

        RECONFIGURE;

    END

 

SELECT  @CheckCommand = 'EXEC [master]..[xp_cmdshell]' + SPACE(1) + QUOTENAME('whoami /priv', '''');

 

INSERT INTO @xp_cmdshell_output

        ( [output] )

EXEC [sys].[sp_executesql] @CheckCommand;

 

IF EXISTS ( SELECT  *

            FROM    @xp_cmdshell_output

            WHERE   [output] LIKE '%SeLockMemoryPrivilege%enabled%' )

    SELECT  'Windows policy Lock Pages in Memory option is enabled' AS [Finding];

ELSE

    SELECT  'Windows policy Lock Pages in Memory option is disabled' AS [Finding];

 

IF @CMDShellFlag = 1

    BEGIN

 

        EXEC [sp_configure] 'show advanced options', 1;

 

        RECONFIGURE;

 

        EXEC [sp_configure] 'xp_cmdshell', 0;

 

        RECONFIGURE;

 

        EXEC [sp_configure] 'show advanced options', 0;

 

        RECONFIGURE;

    END

 

SET NOCOUNT OFF;

lock pages in memory

To enable the lock pages in memory option

1. On the Start menu, click Run. In the Open box, type gpedit.msc.
   The Group Policy dialog box opens.
2. On the Group Policy console, expand Computer Configuration, and then expand Windows Settings.
3. Expand Security Settings, and then expand Local Policies.
4. Select the User Rights Assignment folder.
   The policies will be displayed in the details pane.
5. In the pane, double-click Lock pages in memory.
6. In the Local Security Policy Setting dialog box, click Add.
7. In the Select Users or Groups dialog box, add an account with privileges to run sqlservr.exe.
8. Restart SQL Server for this to take effect

SQL Server rebuild all indexes

DECLARE @Database VARCHAR(255)   DECLARE @Table VARCHAR(255)  DECLARE @cmd NVARCHAR(500)  DECLARE @fillfactor INT

SET @fillfactor = 90
DECLARE DatabaseCursor CURSOR FOR 
SELECT name FROM master.dbo.sysdatabases   WHERE name NOT IN ('master','msdb','tempdb','model','distribution')   ORDER BY 1 
OPEN DatabaseCursor 
FETCH NEXT FROM DatabaseCursor INTO @Database  WHILE @@FETCH_STATUS = 0  BEGIN 

   SET @cmd = 'DECLARE TableCursor CURSOR FOR SELECT ''['' + table_catalog + ''].['' + table_schema + ''].['' +
  table_name + '']'' as tableName FROM [' + @Database + '].INFORMATION_SCHEMA.TABLES
  WHERE table_type = ''BASE TABLE'''  

   -- create table cursor 
   EXEC (@cmd) 
   OPEN TableCursor  

   FETCH NEXT FROM TableCursor INTO @Table  
   WHILE @@FETCH_STATUS = 0  
   BEGIN  

       IF (@@MICROSOFTVERSION / POWER(2, 24) >= 9)
       BEGIN
           -- SQL 2005 or higher command
           SET @cmd = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'
           EXEC (@cmd)
       END
       ELSE
       BEGIN
          -- SQL 2000 command
          DBCC DBREINDEX(@Table,' ',@fillfactor) 
       END

       FETCH NEXT FROM TableCursor INTO @Table  
   END  

   CLOSE TableCursor  
   DEALLOCATE TableCursor 

   FETCH NEXT FROM DatabaseCursor INTO @Database  END 
CLOSE DatabaseCursor   DEALLOCATE DatabaseCursor

SQL Query to check for deadlock

SELECT L.request_session_id AS SPID, DB_NAME(L.resource_database_id) AS DatabaseName, O.Name AS LockedObjectName, P.object_id AS LockedObjectId, L.resource_type AS LockedResource, L.request_mode AS LockType, ST.text AS SqlStatementText, ES.login_name AS LoginName, ES.host_name AS HostName, TST.is_user_transaction as IsUserTransaction, AT.name as TransactionName, CN.auth_scheme as AuthenticationMethod
FROM sys.dm_tran_locks L JOIN sys.partitions P ON P.hobt_id = L.resource_associated_entity_id JOIN sys.objects O ON O.object_id = P.object_id JOIN sys.dm_exec_sessions ES ON ES.session_id = L.request_session_id JOIN sys.dm_tran_session_transactions TST ON ES.session_id = TST.session_id JOIN sys.dm_tran_active_transactions AT ON TST.transaction_id = AT.transaction_id JOIN sys.dm_exec_connections CN ON CN.session_id = ES.session_id CROSS APPLY sys.dm_exec_sql_text(CN.most_recent_sql_handle) AS ST WHERE resource_database_id = db_id() ORDER BY L.request_session_id

setting maxdop

--  High CXPACKET waits found with the current max degree of parallelism setting

declare @value bigint;

declare @maxDOP bigint;

declare @showAdvOpts bit;

set @maxDOP = 8;

select @value = cast(value_in_use as bigint) from sys.configurations where configuration_id = 1539; -- maximum degree of parallelism

if @maxDOP <> @value

begin

 

       select @showAdvOpts = cast(value_in_use as bigint) from sys.configurations where configuration_id = 518; -- show advanced options

       if 0 = @showAdvOpts

       begin

              exec sp_configure 'show advanced options', 1;

              reconfigure with override;

       end;

       exec sp_configure 'max degree of parallelism', @maxDOP;

       reconfigure with override;

       if 0 = @showAdvOpts

       begin

              exec sp_configure 'show advanced options', 0;

              reconfigure with override;

       end;

end;

add tempdb files

USE [master];

GO

 

ALTER DATABASE [tempdb] ADD FILE (NAME = N'tempdev2', FILENAME = N'T:\MSSQL\DATA\tempdev2.ndf' , SIZE = 8GB , FILEGROWTH = 0);

 

ALTER DATABASE [tempdb] ADD FILE (NAME = N'tempdev3', FILENAME = N'T:\MSSQL\DATA\tempdev3.ndf' , SIZE = 8GB , FILEGROWTH = 0);

 

ALTER DATABASE [tempdb] ADD FILE (NAME = N'tempdev4', FILENAME = N'T:\MSSQL\DATA\tempdev4.ndf' , SIZE = 8GB , FILEGROWTH = 0);

GO

SQL to find relevant table.column that contains String value

DECLARE @SearchStrColumnName nvarchar(100), @SearchStrColumnValue nvarchar(100), @SearchStrInXML bit

SET @SearchStrColumnValue = '%FTP%' /* use LIKE syntax */

SET @SearchStrColumnName = NULL /* NULL for all, use LIKE syntax */

SET @SearchStrInXML = 0 /* Searching XML data may be slow */

IF OBJECT_ID('tempdb..#Results') IS NOT NULL DROP TABLE #Results

CREATE TABLE #Results (TableName nvarchar(128), ColumnName nvarchar(128), ColumnValue nvarchar(max),ColumnType nvarchar(20))

SET NOCOUNT ON

DECLARE @TableName nvarchar(256) = '',@ColumnName nvarchar(128),@ColumnType nvarchar(20), @QuotedSearchStrColumnValue nvarchar(110), @QuotedSearchStrColumnName nvarchar(110)

SET @QuotedSearchStrColumnValue = QUOTENAME(@SearchStrColumnValue,'''')

DECLARE @ColumnNameTable TABLE (COLUMN_NAME nvarchar(128),DATA_TYPE nvarchar(20))

WHILE @TableName IS NOT NULL




BEGIN


SET @TableName =




(
 

SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))

FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_TYPE = 'BASE TABLE'

AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName

AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0




)



IF @TableName IS NOT NULL




BEGIN


DECLARE @sql VARCHAR(MAX)

SET @sql = 'SELECT QUOTENAME(COLUMN_NAME),DATA_TYPE




FROM INFORMATION_SCHEMA.COLUMNS


WHERE TABLE_SCHEMA = PARSENAME(''' + @TableName + ''', 2)

AND TABLE_NAME = PARSENAME(''' + @TableName + ''', 1)

AND DATA_TYPE IN (' + CASE WHEN ISNUMERIC(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@SearchStrColumnValue,'%',''),'_',''),'[',''),']',''),'-','')) = 1 THEN '''tinyint'',''int'',''bigint'',' ELSE '' END + '''char'',''varchar'',''nchar'',''nvarchar''' + CASE @SearchStrInXML WHEN 1 THEN ',''xml''' ELSE '' END + ')

AND COLUMN_NAME LIKE COALESCE(' + CASE WHEN @SearchStrColumnName IS NULL THEN 'NULL' ELSE '''' + @SearchStrColumnName + '''' END + ',COLUMN_NAME)'

INSERT INTO @ColumnNameTable

EXEC (@sql)

WHILE EXISTS (SELECT TOP 1 COLUMN_NAME FROM @ColumnNameTable)




BEGIN


SELECT TOP 1 @ColumnName = COLUMN_NAME,@ColumnType = DATA_TYPE FROM @ColumnNameTable

SET @sql = 'SELECT ''' + @TableName + ''',''' + @ColumnName + ''',' + CASE @SearchStrInXML WHEN 1 THEN 'LEFT(CAST(' + @ColumnName + ' AS nvarchar(MAX)), 4096),''' ELSE 'LEFT(' + @ColumnName + ', 4096),''' END + @ColumnType + '''

FROM ' + @TableName + ' (NOLOCK) ' +

' WHERE ' + CASE @SearchStrInXML WHEN 1 THEN 'CAST(' + @ColumnName + ' AS nvarchar(MAX))' ELSE @ColumnName END + ' LIKE ' + @QuotedSearchStrColumnValue

INSERT INTO #Results

EXEC(@sql)

DELETE FROM @ColumnNameTable WHERE COLUMN_NAME = @ColumnName

END




END

END


SET NOCOUNT OFF

SELECT TableName, ColumnName, ColumnValue, ColumnType, COUNT(*) AS Count FROM #Results

GROUP BY TableName, ColumnName, ColumnValue, ColumnType