MSSQL: SQL Server Commands

quick link

sql agent subsystem

msdb..sp_enum_sqlagent_subsystems
go

-- merge replication
sp_addmergepublication 
sp_addpublication_snapshot 
sp_addmergearticle 
sp_mergearticlecolum 
sp_addmergefilter

--  distriubtion 
select * from master..MSreplication_options;

select * from distribution..MSpublications;

select * from distribution..MSmerge_sessions;

select * from [distribution].[dbo].[MSmerge_subscriptions];

find objects

use Master;
go
declare @myobjectname sysname

set @myobjectname = 'msforeach'

SELECT top 100
db_name() db_name,s.name as schema_name,
-- ISNULL(po.name, ps.name) AS object_owner,
-- ISNULL(po.type_desc, ps.type_desc) as owner_type,
o.name,
-- o.type,
o.type_desc,
o.is_ms_shipped 
FROM sys.all_objects o
INNER JOIN sys.schemas s on o.schema_id = s.schema_id
    LEFT OUTER JOIN sys.database_principals po ON o.principal_id = po.principal_id
    LEFT OUTER JOIN sys.database_principals ps ON s.principal_id = ps.principal_ID
where 
-- o.name like '%sp_database%'
-- o.name = @myobjectname
o.name like '%' + @myobjectname + '%' 
order by o.name, s.name;
go

find objects in all dbs

eg: EXECUTE master.sys.sp_MSforeachdb 'USE [?]; EXEC sp_spaceused'
-- For All DBs
EXECUTE master.sys.sp_MSforeachdb '
use [?];
declare @myobjectname sysname

set @myobjectname = ''sp_help_operator''

SELECT top 100
db_name() db_name,s.name as schema_name,
-- ISNULL(po.name, ps.name) AS object_owner,
-- ISNULL(po.type_desc, ps.type_desc) as owner_type,
o.name,
-- o.type,
o.type_desc,
o.is_ms_shipped 
FROM sys.all_objects o
INNER JOIN sys.schemas s on o.schema_id = s.schema_id
    LEFT OUTER JOIN sys.database_principals po ON o.principal_id = po.principal_id
    LEFT OUTER JOIN sys.database_principals ps ON s.principal_id = ps.principal_ID
where 
-- o.name like ''%sp_database%''
-- o.name = @myobjectname
o.name like ''%'' + @myobjectname + ''%'' 
order by o.name, s.name;
'

use Master;
go
-- use msdb;
-- go
select db_name() + '.' + 
schema_name(schema_id) + '.' +  
o.name, o.type,  o.type_desc from sys.objects o 
where 
-- name like 'sp_whoIsActive' 
type  not in ( 'PK', 'AAA' ) 
and is_ms_shipped = 0
order by schema_id,type,name;
go

SELECT *
FROM   [sys].[objects]
WHERE  SCHEMA_NAME([schema_id]) != 'sys'
   AND [is_ms_shipped] = 0
   AND [parent_object_id] NOT IN (SELECT [object_id]
                                   FROM   [sys].[objects]
                                    WHERE  SCHEMA_NAME([schema_id]) = 'sys'
                                           OR [is_ms_shipped] = 1);

set ansi_nulls off --> column_name = null will return rows
set ansi_nulls on  --> column_name = null will return no rows

Find SQL Text

-- find sql text per ( active ) session
SELECT es.session_id, ib.event_info   
FROM sys.dm_exec_sessions AS es  
CROSS APPLY sys.dm_exec_input_buffer(es.session_id, NULL) AS ib  
WHERE es.session_id > 50;
GO

-- flush sql text buffer
DBCC FREEPROCCACHE;

-- find sql text in the buffer
select distinct 
'-- BEGIN1 -------------------------------------------------------------------------- END1 ' sqltext1,
char(10) + char(13) + ' ' + char(10) + char(13) xxx1,
st.text,
char(10) + char(13) + ' ' + char(10) + char(13) xxx2,
'-- BEGIN2 -------------------------------------------------------------------------- END2 ' sqltext2,
char(10) + char(13) + ' ' + char(10) + char(13) xxx3
FROM sys.dm_exec_query_stats AS qs  
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st;

-- find sql text in the buffer
select qs.*, st.*
FROM sys.dm_exec_query_stats AS qs  
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
where st.text like  '%ag%stat%' 
and st.text not like '%PROCEDURE%' 
and st.text not like '%INVOICE%' 
and st.text not like '%AGENT%'
and st.text not like '%cruise%'
and st.text  not like '%contract%';

or
select qs.*, st.*
FROM sys.dm_exec_query_stats AS qs  
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st;

-- 
sp_who
sp_who myusername
sp_who mydbname
sp_who active
sp_who2

SELECT t.*
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE session_id = 802;

sp_who N'MYDOMAIN\MYUSERNAME'
SELECT t.*
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE session_id in ( 1119,1280,2347,2379,2388,802,865,980 );

-- -------------------------------------------

-- DBCC INPUTBUFFER ( session_id [ , request_id ])  [WITH NO_INFOMSGS ] 
-- The fn_get_sql function returns information that is similar to the DBCC INPUTBUFFER command. 

DECLARE @Handle varbinary(64);  
SELECT @Handle = sql_handle   
FROM sys.dm_exec_requests   
WHERE session_id = 52 and request_id = 0;  
SELECT * FROM sys.fn_get_sql(@Handle);  
GO  

-- -------------------------------------------

Find SQL Statement In DB Cache

SELECT total_worker_time/execution_count AS [Avg CPU Time],  
    SUBSTRING(st.text, (qs.statement_start_offset/2)+1,   
        ((CASE qs.statement_end_offset  
          WHEN -1 THEN DATALENGTH(st.text)  
         ELSE qs.statement_end_offset  
         END - qs.statement_start_offset)/2) + 1) AS statement_text  
FROM sys.dm_exec_query_stats AS qs  
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st 
where st.text like 'SELECT%CREATE_STATEMENT%'  
ORDER BY total_worker_time/execution_count DESC;  

Server Start Time And DB Instance Start Time

-- server start time
SELECT sqlserver_start_time FROM sys.dm_os_sys_info;
GO

SELECT DATEADD(SECOND, (ms_ticks/1000)*(-1), GETDATE()) 
FROM sys.dm_os_sys_info;
GO

SELECT DATEADD(ms,-sample_ms,GETDATE())AS StartTime 
FROM sys.dm_io_virtual_file_stats(1,1);
GO

SELECT
  DATEADD(MILLISECOND, (sample_ms * -1), GETDATE()) AS BOOT_TIME_MACHINE
FROM sys.dm_io_virtual_file_stats(DB_ID(N'tempdb'), 2);
GO
 
-- database instance start time
sp_readerrorlog 0,1,'Copyright (c)'
GO

SELECT create_date FROM sys.databases WHERE name = 'tempdb';
GO

SELECT create_date AS START_TIME_INSTANCE FROM sys.databases WHERE name = 'tempdb';
GO

SELECT getdate() curr_time, 
login_time db_start_time, 
DATEDIFF(minute, login_time,getdate()) mydiff_minute 
from sysprocesses WHERE spid = 1;
GO

system functions

system function: 

APP_NAME  NONE  Returns the program name for the current connection if one has been set by the program before logging on. 
COALESCE  (expression1, expression2, ... expressionN)  A specialized form of the CASE statement. Returns the first non-null expression.  
COL_LENGTH  ('table_name', 'column_name')  The defined (maximum) storage length of a column. 
COL_NAME  (table_id, column_id)  The name of the column. 
DATALENGTH  ('expression')  The length of an expression of any datatype. 
DB_ID  (['database_name'])  The database identification number.  
DB_NAME  ([database_id])  The database name. 
GETANSINULL  (['database_name'])  The default nullability for the database. Returns 1 when the nullability is the ANSI NULL default.  
HOST_ID  NONE  The workstation identification number. 
HOST_NAME  NONE  The workstation name. 
IDENT_INCR  ('table_or_view')  The increment value specified during creation of an identity column of a table or view that includes an identity column. 
IDENT_SEED ('table_or_view') The seed value specified during creation of an identity column of a table or view that includes an identity column. 
INDEX_COL  ('table_name', index_id, key_id) The indexed column name(s). 
ISDATE  (expression_of_possible_date)  Checks whether an expression is a datetime datatype or a string in a recognizable datetime format. Returns 1 when the expression is compatible with the datetime type; otherwise, returns 0. 
ISNUMERIC  (expression_of_possible_number)  Checks whether an expression is a numeric datatype or a string in a recognizable number format. Returns 1 when the expression is compatible with arithmetic operations; otherwise, returns 0. 
ISNULL  (expression, value)  Replaces NULL entries with the specified value. 
NULLIF  (expression1, expression2)  A specialized form of CASE. The resulting expression is NULL when expression1 is equivalent to expression2. 
OBJECT_ID  ('object_name')  The database object identification number. 
OBJECT_NAME  (object_id)  The database object name. 
STATS_DATE  (table_id,index_id)  The date when the statistics for the specified index (index_id) were last updated. 
SUSER_ID  (['login_name'])  The user's login ID number. 
SUSER_NAME  ([server_user_id])  The user's login name. 
SUSER_SID  (['login_name'])  The security identification number (SID) for the user's login name. 
SUSER_SNAME  ([server_user_sid])  The login identification name from a user's security identification number (SID). 
USER_ID  (['user_name'])  The user's database ID number. 
USER_NAME  ([user_id])  The user's database username. 

-- --------
use @@SERVERNAME, or that query the server name from sys.servers.

-- SELECT DEFAULT_DOMAIN()
-- SELECT DEFAULT_DOMAIN()[DomainName] --> DomainName is the alias

--  return the login name for the current security context.
SELECT SUSER_SNAME();  
GO 

-- return the sa username
SELECT SUSER_SNAME(0x01);  
GO  

--  Windows user security ID
SELECT SUSER_SNAME(0x0105......);  
GO

-- test 
SELECT SUSER_SNAME();
GO
EXECUTE AS LOGIN = N'sa';
SELECT SUSER_SNAME();
REVERT;
GO
SELECT SUSER_SNAME();
GO

sp_helpdb

exec sp_helpdb 
go
Use tempdb
exec sp_helpfile
go
EXEC sp_helpindex customer
go
EXEC sp_helpconstraint customer_table_name
GO
select @@ROWCOUNT

sp_configure 
sp_who 
sp_who2
sp_lock 
xp_msver 
sp_helpextendedproc 

sp_executesql 'insert mytable values(@p)','@p float',1.0 

table, index size

use mydb;
go

SELECT  sc.name + '.' + t.NAME AS TableName,
        p.[Rows],
        ( SUM(a.total_pages) * 8 ) / 1024 AS TotalReservedSpaceMB, -- Number of total pages * 8KB size of each page in SQL Server
        ( SUM(a.used_pages) * 8 ) / 1024 AS UsedDataSpaceMB,
        ( SUM(a.data_pages) * 8 ) / 1024 AS FreeUnusedSpaceMB
FROM    sys.tables t
        INNER JOIN sys.schemas sc ON sc.schema_id = t.schema_id
        INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id
        INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID
                                            AND i.index_id = p.index_id
        INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
WHERE   t.type_desc = 'USER_TABLE'
        AND i.index_id <= 1  --- Heap\ CLUSTERED
GROUP BY sc.name + '.' + t.NAME,
        i.[object_id],i.index_id, i.name, p.[Rows]
ORDER BY ( SUM(a.total_pages) * 8 ) / 1024 DESC;

use mydb;
go
SELECT [Size in MB] = SUM(IDX.reserved)/128,
[Object Name] = OBJ.name
FROM dbo.sysindexes IDX
JOIN dbo.sysobjects OBJ
ON OBJ.id = IDX.id
WHERE IDX.indid IN (0, 1, 255)
GROUP BY IDX.id, OBJ.name
ORDER BY 1 DESC

IF OBJECT_ID('tempdb..#t', 'U') IS NOT NULL
DROP TABLE #t
 
CREATE TABLE #t (
[name] NVARCHAR(128),
[rows] CHAR(11),
reserved VARCHAR(18),
data VARCHAR(18),
index_size VARCHAR(18),
unused VARCHAR(18))
 
-- get the space used for each table in this database
INSERT #t EXEC sp_msForEachTable 'EXEC sp_spaceused ''?'''
SELECT * FROM (
SELECT [name] as "Table",
CONVERT(int, [rows]) as "Rows",
CONVERT(int, LEFT([reserved],LEN([reserved])-3)) / 1024 as "Reserved MB",
CONVERT(int, LEFT([data],LEN([data])-3)) / 1024 as "Data MB",
CONVERT(int, LEFT([index_size],LEN([index_size])-3)) / 1024 as "Index MB",
CONVERT(int, LEFT([unused],LEN([unused])-3)) / 1024 as "Unused MB"
FROM #t WHERE [name] like 'CONSPT%') t
ORDER BY [Reserved MB] DESC
 
DROP TABLE #t

msdb purging

msdb purging
use msdb
go
exec sp_delete_backuphistory '2009-04-02' 
go

sp_delete_backuphistory @oldest_date='4/28/2009'
sp_delete_backuphistory @oldest_date = '08-19-2008'

sp_purge_jobhistory
sysmail_delete_mailitems
sysmail_delete_log_sp

DECLARE @cutoff DATE;
SET @cutoff = DATEADD(MONTH, -6, GETDATE());
EXEC msdb..sysmail_delete_mailitems_sp @sent_before = @cutoff;
EXEC msdb..sysmail_delete_log_sp @logged_before = @cutoff;


DECLARE @MinBackupDate DATETIME;
SET @MinBackupDate = DATEADD(month,-6, GETDATE());
DECLARE @SQL VARCHAR(MAX);
 
SET @SQL = 'EXEC msdb..sysmail_delete_mailitems_sp @sent_before= ''' + CONVERT(VARCHAR(20),@MinBackupDate,101) + ''' ;' ;
--PRINT @SQL;
EXEC( @SQL );
 
SET @SQL = 'EXEC msdb..sysmail_delete_log_sp @logged_before= ''' + CONVERT(VARCHAR(20),@MinBackupDate,101) + ''' ;' ;
--PRINT @SQL;
EXEC( @SQL );

-- delete all backup history for a specific database 
use msdb
go
exec sp_delete_database_backuphistory 'test1' 
go
USE MSDB
GO

select count(start_time) from sysmaintplan_logdetail with (nolock) where start_time < '4/28/2009.';

sp_maintplan_delete_log @oldest_time='4/28/2009'

1 

Exec SP_SpaceUsed log_shipping_monitor_history_detail
Exec SP_SpaceUsed log_shipping_monitor_error_detail
Exec SP_SpaceUsed sysjobhistory
Exec SP_SpaceUsed msdb.dbo.sysmaintplan_logdetail


sp_cleanup_log_shipping_history '7CED0C18-6B90-44D2-AAB6-B18A123B7B29',0
sp_cleanup_log_shipping_history
which requires the agent_id parameter which i found using : 
select top 10 * from log_shipping_monitor_history_detail with (nolock)

the sp_cleanup_log_shipping_history deleted a huge amount of data in
the [sysmaintplan_log] and [sysmaintplan_logdetail] tables

DBCC SHRINKFILE(MSDBLog, 100)
GO

. SHRINK THE MSDB Data File
USE MSDB
GO

DBCC SHRINKFILE(MSDBData, 100)
GO

SQL Server Version

ssms --> help --$gt; about
Object Explorer -> serever instance --> the version information 
C:\Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\LOG\ERRORLOG
Select @@version
SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

SELECT
SERVERPROPERTY('ServerName') 'ServerName'
,@@VERSION '@@version'
,SERVERPROPERTY('ComputerNamePhysicalNetBIOS') 'ComputerNamePhysicalNetBIOS'
,SERVERPROPERTY('MachineName') 'MachineName'
,SERVERPROPERTY('InstanceName') 'InstanceName'
,SERVERPROPERTY('IsClustered') 'IsClustered'
,SERVERPROPERTY('BuildClrVersion') 'BuildClrVersion'
,SERVERPROPERTY('Collation') 'Collation'
,SERVERPROPERTY('CollationID') 'CollationID'
,SERVERPROPERTY('ComparisonStyle') 'ComparisonStyle'
,SERVERPROPERTY('ComputerNamePhysicalNetBIOS') 'ComputerNamePhysicalNetBIOS'
,SERVERPROPERTY('Edition') 'Edition'
,SERVERPROPERTY('EditionID') 'EditionID'
,SERVERPROPERTY('EngineEdition') 'EngineEdition'
,SERVERPROPERTY('IsFullTextInstalled') 'IsFullTextInstalled'
,SERVERPROPERTY('IsIntegratedSecurityOnly') 'IsIntegratedSecurityOnly'
,SERVERPROPERTY('IsSingleUser') 'IsSingleUser'
,SERVERPROPERTY('LCID') 'LCID'
,SERVERPROPERTY('LicenseType') 'LicenseType'
,SERVERPROPERTY('NumLicenses') 'NumLicenses'
,SERVERPROPERTY('ProcessID') 'ProcessID'
,SERVERPROPERTY('ProductVersion') 'ProductVersion'
,SERVERPROPERTY('ProductLevel') 'ProductLevel'
,SERVERPROPERTY('ResourceLastUpdateDateTime') 'ResourceLastUpdateDateTime'
,SERVERPROPERTY('ResourceVersion') 'ResourceVersion'
,SERVERPROPERTY('ServerName') 'ServerName'
,SERVERPROPERTY('SqlCharSet') 'SqlCharSet'
,SERVERPROPERTY('SqlCharSetName') 'SqlCharSetName'
,SERVERPROPERTY('SqlSortOrder') 'SqlSortOrder'
,SERVERPROPERTY('SqlSortOrderName') 'SqlSortOrderName'
,SERVERPROPERTY('FilestreamShareName') 'FilestreamShareName'
,SERVERPROPERTY('FilestreamConfiguredLevel') 'FilestreamConfiguredLevel'
,SERVERPROPERTY('FilestreamEffectiveLevel') 'FilestreamEffectiveLevel'

cf:sql server discovery 
SQL Server Installation Center --> Tools --> Installed SQL Server features discovery report
%ProgramFiles%\Microsoft SQL Server\100\Setup Bootstrap\Log\

Run "Setup.exe /Action=RunDiscovery" from a command prompt
If you add "/q" to the command line above no UI will be shown, 
but the report will still be created in 
%ProgramFiles%\Microsoft SQL Server\100\Setup Bootstrap\Log\20091112_082147. 

fix orphaned user at database level

sp_change_users_login 


-- change sid in user database
EXECUTE master.sys.sp_MSforeachdb 'USE [?]; select db_name(); EXEC sp_change_users_login ''Report''';
GO

-- report
USE my_db
GO
EXEC sp_change_users_login 'Report' --> db username, login name, password must not presended
GO
select UserName = name, UserSID = sid from sys.sysusers
where issqluser = 1 
and   (sid is not null and sid <> 0x0)
and   (len(sid) <= 16) /* all sid has length of 16 */
and   suser_sname(sid) is null /* key part */
order by name;
GO

select su.name server_username , du.name db_username, su.sid
from sys.sysusers du, master.sys.server_principals su 
where (du.name like 'xxx%' or  su.name like 'xxx%')
and du.sid=su.sid;

-- just use alter user to fix it
USE my_db
GO
ALTER USER [my_db_username] with LOGIN=[my_login_name]
GO
ALTER USER UserName WITH LOGIN = UserName;
GO

-- Fixing Multiple Orphan Users
USE my_db
GO
EXEC sp_change_users_login 'Report'
GO
USE my_db
GO
DECLARE @loop INT;
DECLARE @USER sysname;
DECLARE @sqlcmd NVARCHAR(500); 
SET @sqlcmd = '';
 
IF OBJECT_ID('tempdb..#Orphaned') IS NOT NULL
    BEGIN
        DROP TABLE #orphaned;
    END;
 
CREATE TABLE #Orphaned
    (
      UserName sysname ,
      IDENT INT IDENTITY(1, 1)
    );
 
INSERT  INTO #Orphaned
        ( UserName
        )
        SELECT  [name]
        FROM    sys.database_principals
        WHERE   [type] IN ( 'U', 'S' )
                AND is_fixed_role = 0
                AND [name] NOT IN ( 'dbo', 'guest', 'sys',
                                    'INFORMATION_SCHEMA' );
 
IF ( SELECT COUNT(*)
     FROM   #Orphaned
   ) > 0
    BEGIN
        SET @loop = 1;
        WHILE @loop <= ( SELECT MAX(IDENT)
                         FROM   #Orphaned
                       )
            BEGIN
                SET @USER = ( SELECT    UserName
                              FROM      #Orphaned
                              WHERE     IDENT = @loop
                            );
                IF ( SELECT COUNT(*)
                     FROM   sys.server_principals
                     WHERE  [name] = @USER
                   ) <= 0
                    BEGIN
                        IF EXISTS ( SELECT  1
                                    FROM    sys.database_principals
                                    WHERE   [name] = @USER
                                            AND type_desc = 'WINDOWS_USER' )
                            BEGIN
                                SET @sqlcmd = 'CREATE LOGIN [' + @USER
                                    + '] FROM WINDOWS';
                                -- EXEC(@sqlcmd);
                                PRINT @sqlcmd;
                            END;
                        IF EXISTS ( SELECT  1
                                    FROM    sys.database_principals
                                    WHERE   [name] = @USER
                                            AND type_desc = 'SQL_USER' )
                            BEGIN
                                SET @sqlcmd = 'CREATE LOGIN [' + @USER
                                    + '] WITH PASSWORD = N''p@ssw0rd1''';
                                -- EXEC(@sqlcmd);
                                PRINT @sqlcmd;
                            END;
                    END;
     
                SET @sqlcmd = 'ALTER USER [' + @USER + '] WITH LOGIN = ['
                    + @USER + ']';
                -- EXEC(@sqlcmd);
                PRINT @sqlcmd;
                PRINT '-- Orphaned User ' + @USER + ' link to DB reset';
                SET @loop = @loop + 1;
            END;
    END;
SET NOCOUNT OFF;
GO

-- fix the sid in application db
USE my_db
GO
EXEC sp_change_users_login 'Update_One', 'my_db_username', 'my_login_name'  --> password must not presented
GO
EXEC sp_change_users_login 'Auto_Fix', 'my_db_username'
GO
EXEC sp_change_users_login 'Auto_Fix', 'my_db_username', NULL, 'my_password'
GO

-- fix the sid in master db
USE my_db 
GO 
SELECT name, sid FROM sys.sysusers WHERE name = 'MyUserName' 
GO 

USE MASTER 
GO 
SELECT name, sid FROM sys.sql_logins WHERE name = 'MyUserName' 
GO

-- CREATE Login MyUserName WITH password = 'xxxx' hashed, SID = 0x61B657112A43D24585BFE2BF80D9BE19

password hash

loginproperty 
_ 



-- PWDENCRYPT
-- PWDCOMPARE 

-- get password hash
SELECT LOGINPROPERTY('MyAppUser','PASSWORDHASH');
SELECT LOGINPROPERTY('sa','PASSWORDHASH');
-- ALTER LOGIN [sa] with password = 0x999999999999 hashed;

SELECT LOGINPROPERTY('sa','PasswordLastSetTime');

SELECT ltrim(rtrim(@@servername)) + space(30-len(@@servername)) servername,
substring(convert(varchar(20),LOGINPROPERTY(N'sa',N'PasswordLastSetTime'),120),1,20) sa_last_change_time;
GO

SELECT LOGINPROPERTY ( 'sa' , 'DaysUntilExpiration' );  
SELECT LOGINPROPERTY ( 'sa' , 'PasswordLastSetTime' ) ; 
SELECT LOGINPROPERTY ( 'sa' , 'PasswordHash' );  
SELECT LOGINPROPERTY ( 'sa' , 'IsExpired' );
SELECT LOGINPROPERTY ( 'sa' , 'IsLocked' ) ; 
SELECT LOGINPROPERTY ( 'sa' , 'IsMustChange');  
SELECT LOGINPROPERTY ( 'sa' , 'PasswordLastSetTime');  
SELECT LOGINPROPERTY ( 'sa' , 'PasswordHashAlgorithm');  
GO 

-- update a password change time
-- A HASHED password cannot be set for a login that has CHECK_POLICY turned on
SELECT LOGINPROPERTY('sa', 'PasswordHash');  
GO 
SELECT LOGINPROPERTY('sa', 'PasswordLastSetTime');  
GO 
USE [master]
GO
ALTER LOGIN [sa] WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
-- ALTER LOGIN [sa] with password = 0x099999999999999999999 hashed;
SELECT 'ALTER LOGIN sa WITH PASSWORD = 0x' + 
CONVERT (VARCHAR(514), (SELECT CAST (LOGINPROPERTY('sa', 'PasswordHash') AS varbinary(256))), 2) + ' HASHED;'
GO
USE [master]
GO
ALTER LOGIN [sa] WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON
GO
SELECT LOGINPROPERTY('sa', 'PasswordLastSetTime');
GO


-- alter login
SELECT 'ALTER LOGIN ' + QUOTENAME(sp.name) + ' WITH PASSWORD = 0x' +
CONVERT (VARCHAR(514), (SELECT CAST (LOGINPROPERTY(sp.name, 'PasswordHash') 
                                   AS varbinary(256))), 2) + ' HASHED;' cmd 
FROM sys.server_principals sp
WHERE sp.type_desc = 'SQL_LOGIN'
ORDER BY sp.name;

-- create server login
CREATE LOGIN [MyAppUser] WITH 
PASSWORD = 0x0100FEFCB7E9DF1FD04B3DB6E048A92F07DE060C4B75C97DC267 HASHED, 
SID = 0x8AE08F0D255CE34C9164AD21DF960A16, 
DEFAULT_DATABASE = [master], 
CHECK_POLICY = ON, CHECK_EXPIRATION = OFF;

Get User Sid

-- get sid
USE AppDB 
GO 
SELECT name, sid FROM sys.sysusers WHERE name = 'MyAppUser' 
GO 
USE MASTER 
GO 
SELECT name, sid FROM sys.sql_logins WHERE name = 'MyAppUser' 
GO
select * from sys.server_principals where name like 'MyAppUser%'
GO

Unlock User Account

alter login myuserloginname with check_policy=off;
alter login myuserloginname with check_pocicy_on;

Alter User Password

USE [master]
GO
ALTER LOGIN yoursqllogin WITH PASSWORD = N'newpassword' OLD_PASSWORD = N'oldpassword'
GO

USE [master]
GO
ALTER LOGIN [LOGIN_NAME] WITH DEFAULT_DATABASE=[master]
GO

USE [master]
GO
ALTER LOGIN [LOGIN_NAME] WITH PASSWORD=N'NewPassword'
GO

USE [master]
GO
ALTER LOGIN [LOGIN_NAME] WITH PASSWORD=N'NewPassword' MUST_CHANGE
GO

USE [master]
GO
ALTER LOGIN [sa] WITH DEFAULT_DATABASE=[master]
GO

USE [master]
GO
ALTER LOGIN [sa] WITH PASSWORD=N'NewPassword' MUST_CHANGE
GO

Create User With SYSADMIN Permission

USE [master]
GO
CREATE LOGIN [MYDOMAINNAME\MYUSERNAME] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]
GO
ALTER SERVER ROLE [sysadmin] ADD MEMBER [MYDOMAINNAME\MYUSERNAME]
GO
EXEC sp_addsrvrolemember 'MYDOMAINNAME\MYUSERNAME', 'sysadmin';
GO

user permission

-- server level permissions (Securables)
alter any database
alter any linked server
alter any login
alter any server role
aler server state
Connect Any Database
Connect SQL
Create any database
Select All User Securables
Shutdown
View any database
View any definition
View server state

-- ----------------------
-- database level roles:
db_accessadmin
db_backupoperator
db_datareader
db_datawriter
db_ddladmin
db_denydatareader
db_denydatawriter
db_owner
db_securityadmin

-- ---------------------

-- database level permissions (Securables)
alter any database ddl trigger
alter any schema
alter any user
backup database
backup log
create schema
create table
create view
show plan
view database state
view definition
-- ----------------------

-- server level roles:
select name, principal_id from sys.server_principals where type='R'  order by principal_id;

public -- connect ( at db level ),  connect sql ( at server level )
bulkadmin
dbcreator
diskadmin
processadmin
securityadmin
serveradmin
setupadmin
sysadmin

-- list all server sys permissions
SELECT SP.[name] AS 'Login' , SPerm.state_desc + ' ' + SPerm.permission_name AS 'ServerPermission'
FROM sys.server_principals SP
  JOIN sys.server_permissions SPerm
    ON SP.principal_id = SPerm.grantee_principal_id
ORDER BY [Login], [ServerPermission];

-- list all server member role pairs
select c.name, p.name from sys.server_role_members rm, sys.server_principals p, sys.server_principals c
where rm.role_principal_id=p.principal_id and rm.member_principal_id=c.principal_id order by 1,2;

-- list all db member role pairs
EXECUTE master.sys.sp_MSforeachdb '
use [?];
select @@servername servername, db_name() db_name, c.name,p.name from sys.database_role_members rm, sys.database_principals p,sys.database_principals c
 where rm.member_principal_id in ( select principal_id from sys.database_principals )
 and rm.role_principal_id = p.principal_id
 and rm.member_principal_id = c.principal_id
 ;
'

-- db sys permission and db obj permission
SELECT 
    USER_NAME(grantee_principal_id) AS 'User'
  , state_desc AS 'Permission'
  , permission_name AS 'Action'
  , CASE class
      WHEN 0 THEN 'Database::' + DB_NAME()
      WHEN 1 THEN OBJECT_NAME(major_id)
      WHEN 3 THEN 'Schema::' + SCHEMA_NAME(major_id) END AS 'Securable'
FROM sys.database_permissions dp
WHERE class IN (0, 1, 3)
AND minor_id = 0
AND user_name(grantee_principal_id) not in ( 'public' )
AND permission_name not in ( 'CONNECT' ) 
order by 1,2,3,4
; 

-- db sys permission and db obj permission per db
USE MyDB;
GO
SELECT 
    USER_NAME(grantee_principal_id) AS 'User'
  , state_desc AS 'Permission'
  , permission_name AS 'Action'
  , CASE class
      WHEN 0 THEN 'Database::' + DB_NAME()
      WHEN 1 THEN OBJECT_NAME(major_id)
      WHEN 3 THEN 'Schema::' + SCHEMA_NAME(major_id) END AS 'Securable'
FROM sys.database_permissions dp
WHERE class IN (0, 1, 3)
AND minor_id = 0
AND user_name(grantee_principal_id) not in ( 'public' )
AND permission_name not in ( 'CONNECT' ) 
order by 1
; 

-- db sys permission and db obj permission for all db
EXECUTE master.sys.sp_MSforeachdb '
use [?];
SELECT
    @@ServerName ServerName
  , db_name() db_name
  , USER_NAME(grantee_principal_id) AS ''User''
  , state_desc AS ''Permission''
  , permission_name AS ''Action''
  , CASE class
      WHEN 0 THEN ''Database::'' + DB_NAME()
      WHEN 1 THEN OBJECT_NAME(major_id)
      WHEN 3 THEN ''Schema::'' + SCHEMA_NAME(major_id) END AS ''Securable''
FROM sys.database_permissions dp
WHERE class IN (0, 1, 3)
AND minor_id = 0
AND user_name(grantee_principal_id) not in ( ''public'' )
AND permission_name not in ( ''CONNECT'' ) 
order by 1,2,3,4,5
;
'

-- list single user server and db permission

-- username, pull server info

-- declare @myusername sysname
declare @myusername nvarchar 

set @myusername = 'Ignite'
select @@servername servername,
name username, type_desc,is_disabled,default_database_name 
from sys.server_principals where name = @myusername;

SELECT @@servername servername,
SP.[name] AS 'Login' , SPerm.state_desc + ' ' + SPerm.permission_name AS 'ServerPermission'
FROM sys.server_principals SP
  JOIN sys.server_permissions SPerm
    ON SP.principal_id = SPerm.grantee_principal_id
where SP.[name]=@myusername;

SELECT 
@@servername servername,
sp2.name
FROM sys.server_principals sp2
left outer JOIN sys.server_role_members srm2 ON srm2.role_principal_id = sp2.principal_id
where srm2.member_principal_id in (select sid from sys.server_principals where name = @myusername );

-- username, pull database info
EXECUTE master.sys.sp_MSforeachdb '
declare @myusername sysname
set @myusername = ''Ignite''
use [?];
SELECT
    @@ServerName ServerName
  , db_name() db_name
  , USER_NAME(grantee_principal_id) AS ''User''
  , state_desc AS ''Permission''
  , permission_name AS ''Action''
  , CASE class
      WHEN 0 THEN ''Database::'' + DB_NAME()
      WHEN 1 THEN OBJECT_NAME(major_id)
      WHEN 3 THEN ''Schema::'' + SCHEMA_NAME(major_id) END AS ''Securable''
FROM sys.database_permissions dp
WHERE class IN (0, 1, 3)
AND minor_id = 0
AND user_name(grantee_principal_id) not in ( ''public'' )
AND permission_name not in ( ''CONNECT'' ) 
AND USER_NAME(grantee_principal_id) COLLATE DATABASE_DEFAULT =@myusername COLLATE DATABASE_DEFAULT
order by 1,2,3,4,5
;
'

EXECUTE master.sys.sp_MSforeachdb '
use [?];
declare @myusername sysname
set @myusername = ''Ignite''
select @@servername servername, db_name() db_name, * from sys.database_principals where name=@myusername;
select @@servername servername, db_name() db_name, * from sys.sql_logins where name=@myusername;
select @@servername servername, db_name() db_name, * from sys.sysusers where name=@myusername;
select @@servername servername, db_name() db_name, c.name,p.name 
from sys.database_role_members rm, sys.database_principals p,sys.database_principals c
 where rm.member_principal_id in 
( select principal_id from sys.database_principals 
  where name COLLATE DATABASE_DEFAULT = @myusername COLLATE DATABASE_DEFAULT 
)
 and rm.role_principal_id = p.principal_id
 and rm.member_principal_id = c.principal_id
 ;
'

start sql server with -m, then add sysadmin

EXEC sp_addsrvrolemember 'MYDOMAINNAME\MYUSERNAME', 'sysadmin';  
GO  

sp_helprotect [ [ @name = ] 'object_statement' ] 
     [ , [ @username = ] 'security_account' ] 
     [ , [ @grantorname = ] 'grantor' ] 
     [ , [ @permissionarea = ] 'type' ]

EXEC sp_helprotect 'titles'; --> lists the permissions for the titles table
EXEC sp_helprotect NULL, 'Judy';  --> lists all permissions that user Judy has in the current database.

EXEC sp_helprotect NULL, NULL, 'Judy';
EXEC sp_helprotect NULL, NULL, dbo;
EXEC sp_helprotect @grantorname = 'dbo';

EXEC sp_helprotect NULL, NULL, NULL, 's'; 
EXEC sp_helprotect @name = 'CREATE TABLE';

-- at server instance level
EXEC xp_logininfo 'mywindowdomain\mywindowuser'
select * from sys.sql_logins;
select * from sys.server_principals;

declare @myusername sysname
set @myusername= N'mywindowsdomain\shpnt_DB_RO'
select 'CREATE LOGIN [' + name + '] ' + 
case when [TYPE] = 'G' then 'FROM WINDOWS ' 
     when [TYPE] = 'U' then 'FROM WINDOWS ' 
     when [TYPE] = 'S' then 'password ' END +
'WITH DEFAULT_DATABASE=[' + default_database_name + '],' +
'DEFAULT_LANGUAGE=[' + default_language_name + ']' cmd 
 from sys.server_principals pp where name = @myusername  
union all 
select 
convert(varchar(255),pm.state_desc) + ' ' + 
convert( varchar(255),pm.permission_name) + ' TO ' + 
pp.name collate DATABASE_DEFAULT + ';' cmd  
from 
sys.server_permissions pm, 
sys.server_principals pp 
where 
    pp.name = @myusername 
and ( not (pm.state = 'G' and pm.type = 'COSQ' ))
and pm.grantee_principal_id = pp.principal_id
union all 
select 'grant ' + ppr.name + ' to ' + pp.name + ';' cmd 
from sys.server_role_members rm, sys.server_principals pp,
sys.server_principals ppr
where 
    pp.name = @myusername
and rm.member_principal_id = pp.principal_id
and rm.role_principal_id = ppr.principal_id;



-- database level
sp_helpuser N'mywindowsdomain\shpnt_DB_RO'

declare @myusername sysname
set @myusername= N'mywindowsdomain\shpnt_DB_RO'
SELECT	'use ' + db_name() + CHAR(13) + CHAR(10) + 'GO' + + CHAR(13) + CHAR(10) 
        + 'exec sp_addrolemember ' + SPACE(1) + QUOTENAME(USER_NAME(rm.role_principal_id), '''') 
	+ ',' + SPACE(1) +  QUOTENAME(USER_NAME(rm.member_principal_id), '''') 
        + CHAR(13) + CHAR(10) + 'GO' + + CHAR(13) + CHAR(10)  cmd 
FROM	sys.database_role_members AS rm
WHERE	USER_NAME(rm.member_principal_id) = @myusername
ORDER BY 1 ASC;

EXEC sp_MSforeachdb '\
USE ? \
declare @myusername sysname \
set @myusername= N''mywindowsdomain\shpnt_DB_RO'' \
SELECT	''use '' + db_name() + CHAR(13) + CHAR(10) + ''GO'' + + CHAR(13) + CHAR(10) + ''exec sp_addrolemember '' + SPACE(1) + QUOTENAME(USER_NAME(rm.role_principal_id), '''''''') \
	+ '','' + SPACE(1) +  QUOTENAME(USER_NAME(rm.member_principal_id), '''''''') \
	+ CHAR(13) + CHAR(10) + ''GO'' + + CHAR(13) + CHAR(10)  cmd \
FROM	sys.database_role_members AS rm \
WHERE	USER_NAME(rm.member_principal_id) = @myusername \
ORDER BY 1 ASC'

Get Server Role

-- server role
SELECT 'grant ' + SP2.[name] + ' TO [' + SP1.[name] + '];' AS 'CMD'
FROM sys.server_principals SP1
  JOIN sys.server_role_members SRM
    ON SP1.principal_id = SRM.member_principal_id
  JOIN sys.server_principals SP2
    ON SRM.role_principal_id = SP2.principal_id
ORDER BY SP1.[name], SP2.[name]; 

-- server permission
SELECT convert(varchar(255),  
       SPerm.state_desc + ' ' 
	   + SPerm.permission_name ) collate DATABASE_DEFAULT + ' TO [' 
	   +  convert(varchar(255),SP.[name]) collate DATABASE_DEFAULT + '];' AS CMD
FROM sys.server_principals SP
  JOIN sys.server_permissions SPerm
    ON SP.principal_id = SPerm.grantee_principal_id
where 
not (SPerm.state = 'G' and SPerm.type = 'COSQ') 
ORDER BY 1
;

transaction log

DBCC SQLPERF

DBCC SQLPERF(LOGSPACE);
GO

DBCC SQLPERF("sys.dm_os_wait_stats",CLEAR);
DBCC SQLPERF("sys.dm_os_latch_stats",CLEAR);

DBCC LOGINFO
DBCC LOGINFO(N'logfilename');
GO
-- -------------------
# Without Log Truncation
BACKUP WITH COPY_ONLY
BACKUP LOG database_name TO  WITH COPY_ONLY
BACKUP DATABASE database_name TO  WITH COPY_ONLY 

# log truncation
BACKUP LOG WITH NO_LOG
BACKUP LOG WITH TRUNCATE_ONLY
BACKUP LOG DBUtil WITH NO_LOG

Shrink Log File (BACKUP LOG WITH TRUNCATE_ONLY; DBCC SHRINKFILE (db_name ,0,TRUNCATEONLY)

Backup Log (BACKUP WITH NO_LOG)
-- -----------------
DBCC SHRINKFILE (nologtest_log, 2);
GO

SELECT log_reuse_wait_desc FROM sys.databases WHERE NAME = 'my_db_name';
GO

Transaction

DBCC OPENTRAN

Find Duplicated Indexes

-- find-duplicate-indexes
-- find Redundant and Duplicate Indexes 

SELECT
	s.Name + '.' + t.Name AS TableName	
	,i.name AS IndexName1	
	,DupliIDX.name AS IndexName2
	,c.name AS ColumnNae
FROM sys.tables AS t 
JOIN sys.indexes AS i
	ON t.object_id = i.object_id
JOIN sys.index_columns ic 
	ON ic.object_id = i.object_id 
		AND ic.index_id = i.index_id 
		AND ic.index_column_id = 1  
JOIN sys.columns AS c 
	ON c.object_id = ic.object_id 
		AND c.column_id = ic.column_id      
JOIN sys.schemas AS s 
	ON t.schema_id = s.schema_id
CROSS APPLY
(
	SELECT 
	   ind.index_id
	   ,ind.name
	FROM sys.indexes AS ind 
	JOIN sys.index_columns AS ico 
	   ON ico.object_id = ind.object_id
	   AND ico.index_id = ind.index_id
	   AND ico.index_column_id = 1  
	WHERE ind.object_id = i.object_id 
	   AND ind.index_id > i.index_id
	   AND ico.column_id = ic.column_id
) DupliIDX     
ORDER BY
    s.name,t.name,i.index_id
GO

Index and Table Rebuild And Reorganization

-- 30 percentage framentation, rebuild, between 5 percent and 30 percent reorganiztion
select ' print ''begin'''
union all
select ' print ''end'''
union all
SELECT 
-- '-- ' xxx
-- --                     ps.database_id AS 'databaseID'
-- --                   , QUOTENAME(DB_NAME(ps.database_id)) AS 'databaseName'
-- --                   , ps.[object_id] AS 'objectID'
                     -- ,object_name(ps.[object_id]) AS 'objectName'--
-- --                  , ps.index_id AS 'indexID'
--                    , i.name AS 'indexName'
-- --                    , ps.partition_number AS 'partitionNumber'
                    --, SUM(ps.avg_fragmentation_in_percent) AS 'fragmentation'
                    -- , SUM(ps.page_count) AS 'page_count'
                    -- , os.range_scan_count
                    -- , GETDATE() AS 'scanDate',
'alter index ' + i.name + ' on ' + object_schema_name(ps.object_id) + '.' + object_name(ps.[object_id]) + ' rebuild;' cmd 
                FROM sys.dm_db_index_physical_stats(DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT) AS ps
                JOIN sys.dm_db_index_operational_stats(DEFAULT,DEFAULT,DEFAULT,DEFAULT) AS os
                    ON ps.database_id = os.database_id
                    AND ps.[object_id] = os.[object_id]
                    AND ps.index_id = os.index_id
                    AND ps.partition_number = os.partition_number
                    LEFT OUTER JOIN sys.indexes AS i ON i.object_id = ps.object_id
                                            AND i.index_id = ps.index_id
                WHERE avg_fragmentation_in_percent >= 30 
                    AND ps.index_id > 0 -- ignore heaps
                    AND ps.page_count > 0
                    AND ps.index_level = 0 -- leaf-level nodes only, supports @scanMode
					AND DB_NAME(ps.database_id)=db_name() 
                GROUP BY ps.database_id 
                    , QUOTENAME(DB_NAME(ps.database_id)) 
                    , ps.[object_id]
                    , ps.index_id 
					, i.name
                    , ps.partition_number 
                    , os.range_scan_count
                OPTION (MAXDOP 2);
select 'print ''end''';

index reorganization

-- reorganization
select ' print ''begin'''
union all
select ' print ''end'''
union all
SELECT 
-- '-- ' xxx
-- --                     ps.database_id AS 'databaseID'
-- --                   , QUOTENAME(DB_NAME(ps.database_id)) AS 'databaseName'
-- --                   , ps.[object_id] AS 'objectID'
                     -- ,object_name(ps.[object_id]) AS 'objectName'--
-- --                  , ps.index_id AS 'indexID'
--                    , i.name AS 'indexName'
-- --                    , ps.partition_number AS 'partitionNumber'
                    --, SUM(ps.avg_fragmentation_in_percent) AS 'fragmentation'
                    -- , SUM(ps.page_count) AS 'page_count'
                    -- , os.range_scan_count
                    -- , GETDATE() AS 'scanDate',
'alter index ' + i.name + ' on ' + object_schema_name(ps.object_id) + '.' + object_name(ps.[object_id]) + ' REORGANIZE;' cmd 
                FROM sys.dm_db_index_physical_stats(DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT) AS ps
                JOIN sys.dm_db_index_operational_stats(DEFAULT,DEFAULT,DEFAULT,DEFAULT) AS os
                    ON ps.database_id = os.database_id
                    AND ps.[object_id] = os.[object_id]
                    AND ps.index_id = os.index_id
                    AND ps.partition_number = os.partition_number
                    LEFT OUTER JOIN sys.indexes AS i ON i.object_id = ps.object_id
                                            AND i.index_id = ps.index_id
                WHERE avg_fragmentation_in_percent < 30 
                    AND avg_fragmentation_in_percent >= 5
                    AND ps.index_id > 0 -- ignore heaps
                    AND ps.page_count > 0
                    AND ps.index_level = 0 -- leaf-level nodes only, supports @scanMode
					AND DB_NAME(ps.database_id)='napa_log'
                GROUP BY ps.database_id 
                    , QUOTENAME(DB_NAME(ps.database_id)) 
                    , ps.[object_id]
                    , ps.index_id 
					, i.name
                    , ps.partition_number 
                    , os.range_scan_count
                OPTION (MAXDOP 2);
select 'print ''end''';

sys.dm_db_index_physical_stats;

ALTER TABLE table1 rebuild;
ALTER TABLE [dbo].[table1] REBUILD;

ALTER INDEX index_name ON table_name REORGANIZE;
ALTER INDEX ALL ON table_name REORGANIZE;

ALTER INDEX [dbo].[index_name] ON [dbo].[table_name] REORGANIZE;
ALTER INDEX ALL ON [dbo].[table_name] REORGANIZE;



Reorganize Index
ALTER INDEX index_name ON table_name REORGANIZE WITH ( LOB_COMPACTION = ON );
ALTER INDEX index_name ON table_name REORGANIZE WITH ( LOB_COMPACTION = OFF );

USE [AdventureWorks]
GO
ALTER INDEX [PK_AWBuildVersion_SystemInformationID] ON [dbo].[AWBuildVersion] REORGANIZE WITH ( LOB_COMPACTION = ON )
GO

ALTER INDEX index_name ON table_name REORGANIZE WITH ( LOB_COMPACTION = ON )

ALTER INDEX index_name ON table_name REORGANIZE WITH ( LOB_COMPACTION = OFF )

ALTER  INDEX  ALL ON table_name  REBUILD ;

ALTER  INDEX  index_name  ON  table_name  REBUILD  PARTITION  =  ALL  
WITH ( PAD_INDEX              =  OFF,  
       STATISTICS_NORECOMPUTE =  OFF,  
       ALLOW_ROW_ LOCKS       =  ON,  
       ALLOW_PAGE_LOCKS       =  ON,  
       ONLINE                 =  OFF,  
       SORT_IN_TEMPDB         =  OFF  )

ALTER INDEX [index_name]  ON [dbo].[table1] rebuild;  
ALTER INDEX index_name1   ON [dbo].[table1]   REBUILD PARTITION = 12; 

ALTER INDEX index_name ON table_name REBUILD PARTITION = ALL 
WITH ( 
PAD_INDEX = OFF, 
STATISTICS_NORECOMPUTE = OFF, 
ALLOW_ROW_LOCKS = ON, 
ALLOW_PAGE_LOCKS = ON, 
ONLINE = OFF, 
SORT_IN_TEMPDB= OFF );
go

ALTER INDEX ALL ON myschema.mytable  
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = ON);  

ALTER INDEX ALL ON myschema. mytable 
REBUILD WITH   
(  
    FILLFACTOR = 80,   
    SORT_IN_TEMPDB = ON,  
    STATISTICS_NORECOMPUTE = ON,  
    ONLINE = ON ( WAIT_AT_LOW_PRIORITY ( MAX_DURATION = 4 MINUTES, ABORT_AFTER_WAIT = BLOCKERS ) ),   
    DATA_COMPRESSION = ROW  
);  

ALTER INDEX [MyIdxName] ON [dbo].[MyTabNme] 
REBUILD 
PARTITION=ALL 
WITH 
(
PAD_INDEX              =OFF,
STATISTICS_NORECOMPUTE =OFF,
ALLOW_ROW_LOCKS        =ON,
ALLOW_PAGE_LOCKS       =ON,
ONLINE                 =OFF,
SORT_IN_TEMPDB         =OFF
)
GO


top





top   prev   next   




USE [AdventureWorks2012]
ALTER INDEX [PK_Product_ProductID] ON [Production].[Product] REBUILD PARTITION = ALL
WITH 
(
PAD_INDEX = OFF, 
STATISTICS_NORECOMPUTE = OFF, 
SORT_IN_TEMPDB = OFF, 
ONLINE = OFF, 
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON, 
DATA_COMPRESSION = ROW)

Index and Table compression

table and index compression

EXEC sp_estimate_data_compression_savings 'dbo','table1',null,null,'row'
EXEC sp_estimate_data_compression_savings 'dbo','table1',null,null,'page'

EXEC sp_estimate_data_compression_savings 'Production','Product',1,null,'ROW'
ALTER TABLE table1 REBUILD WITH (data_compression = row)
ALTER TABLE table1 REBUILD WITH (data_compression = page)
ALTER TABLE TestCompression REBUILD WITH (DATA_COMPRESSION = NONE);

SQL Trace Server Side and SQL Profiler (Client Side)

1 

2 

3 

4 

5 

top





top   prev   next   




Running a Server-Side Trace on a SQL Server DB Instance

Writing scripts to create a server-side trace can be complex and is beyond the scope of this document. This section contains sample scripts that you can use as examples. As with a client-side trace, the goal is to create a workload file or trace table that you can open using the Database Engine Tuning Advisor.

The following is an abridged example script that starts a server-side trace and captures details to a workload file. The trace initially saves to the file RDSTrace.trc in the D:\RDSDBDATA\Log directory and rolls-over every 100 MB so subsequent trace files are named RDSTrace_1.trc, RDSTrace_2.trc, etc.

DECLARE @file_name NVARCHAR(245) = 'D:\RDSDBDATA\Log\RDSTrace';
DECLARE @max_file_size BIGINT = 100;
DECLARE @on BIT = 1
DECLARE @rc INT
DECLARE @traceid INT

EXEC @rc = sp_trace_create @traceid OUTPUT, 2, @file_name, @max_file_size
IF (@rc != 0) BEGIN
   EXEC sp_trace_setevent @traceid, 10, 1, @on
   EXEC sp_trace_setevent @traceid, 10, 2, @on
   EXEC sp_trace_setevent @traceid, 10, 3, @on
  .
   EXEC sp_trace_setfilter @traceid, 10, 0, 7, N'SQL Profiler'
   EXEC sp_trace_setstatus @traceid, 1
   END

The following example is a script that stops a trace. 
Note that a trace created by the previous script continues 
to run until you explicitly stop the trace or the process runs out of disk space.

DECLARE @traceid INT
SELECT @traceid = traceid FROM ::fn_trace_getinfo(default) 
WHERE property = 5 AND value = 1 AND traceid <> 1 

IF @traceid IS NOT NULL BEGIN
   EXEC sp_trace_setstatus @traceid, 0
   EXEC sp_trace_setstatus @traceid, 2
END
            

You can save server-side trace results to a database table and 
use the database table as the workload for the Tuning Advisor 
by using the fn_trace_gettable function. 
The following commands load the results of all files named RDSTrace.trc 
in the D:\rdsdbdata\Log directory, 
including all rollover files like RDSTrace_1.trc, 
into a table named RDSTrace in the current database:

SELECT * INTO RDSTrace
FROM fn_trace_gettable('D:\rdsdbdata\Log\RDSTrace.trc', default);                    
                

To save a specific rollover file to a table, for example the RDSTrace_1.trc file, 
specify the name of the rollover file and substitute 1 instead of default as the 
last parameter to fn_trace_gettable.

SELECT * INTO RDSTrace_1
FROM fn_trace_gettable('D:\rdsdbdata\Log\RDSTrace_1.trc', 1);                    
                    

Running Tuning Advisor with a Trace

Once you create a trace, either as a local file or as a database table, 
you can then run Tuning Advisor against your RDS instance. 
Microsoft includes documentation on using the Database Engine 
Tuning Advisor in MSDN. Using Tuning Advisor with RDS is 
the same process as when working with a standalone, 
remote SQL Server instance. 
You can either use the Tuning Advisor UI on your client machine 
or use the dta.exe utility from the command line. 
In both cases, you must connect to the RDS DB Instance using 
the endpoint for the DB Instance and provide your master user 
name and master user password when using Tuning Advisor.

The following code example demonstrates using the dta.exe command line utility 
against an RDS DB Instance with an endpoint of 
dta.cnazcmklsdei.us-east-1.rds.amazonaws.com. 
The example includes the master user name admin and the master user password test, 
the example database to tune is named RDSDTA and the input workload is a trace file 
on the local machine named C:\RDSTrace.trc. The example command line code also 
specifies a trace session named RDSTrace1 and specifies output files to the 
local machine named RDSTrace.sql for the SQL output script, RDSTrace.txt for a result file, 
and RDSTrace.xml for an XML file of the analysis. There is also 
an error table specified on the RDSDTA database named RDSTraceErrors.

dta -S dta.cnazcmklsdei.us-east-1.rds.amazonaws.com -U admin -P test -D RDSDTA -if C:\RDSTrace.trc -s RDSTrace1 -of C:\ RDSTrace.sql -or C:\ RDSTrace.txt -ox C:\ RDSTrace.xml -e RDSDTA.dbo.RDSTraceErrors





top





top   prev   next   




To run a client-side trace on a SQL Server DB instance

    Start SQL Server Profiler. It is installed in the Performance Tools folder of your SQL Server instance folder. You must load or define a trace definition template to start a client-side trace.

    In the SQL Server Profiler File menu, click New Trace. In the Connect to Server dialog box, enter the DB Instance endpoint, port, master user name, and password of the database you would like to run a trace on.

    In the Trace Properties dialog box, enter a trace name and choose a trace definition template. A default template, TSQL_Replay, ships with the application. You can edit this template to define your trace. Edit events and event information under the Events Selection tab of the Trace Properties dialog box. For more information about trace definition templates and using the SQL Server Profiler to specify a client-side trace see the documentation in MSDN.

    Start the client-side trace and watch SQL queries in real-time as they execute against your DB Instance.

    Select Stop Trace from the File menu when you have completed the trace. Save the results as a file or as a trace table on you DB Instance.

read a text file or windows command result in sql server

CREATE TABLE #cmd_result (output varchar(8000))
INSERT #cmd_result
EXEC master.dbo.xp_cmdshell 'TYPE C:\BOOT.INI'
SELECT * FROM #cmd_result
DROP TABLE #cmd_result

sp_spaceused at database level

USE Northwind
EXEC dbo.sp_spaceused


# exec sp_spaceused all databases
EXEC sp_msforeachdb @command1="use [?]  exec sp_spaceused"
EXEC sp_msforeachdb @command1="use [?]; exec sp_spaceused"


USE pubs
EXEC Northwind..sp_spaceused

sp_spaceused at table/object level

EXEC sys.sp_spaceused N'dbo.mytable_name', @updateusage = N'TRUE';
GO

create table #t
(
  name nvarchar(128),
  rows varchar(50),
  reserved varchar(50),
  data varchar(50),
  index_size varchar(50),
  unused varchar(50)
);

EXEC sp_MSforeachtable @command1="insert into #t EXEC sp_spaceused '?'"

select * from #t order by convert(int, substring(data, 1, len(data)-3)) desc;

select name, rows, reserved,DATA,index_size,unused 
from #t a 
order by 
convert(int, substring(a.data, 1, len(a.data)-3)) + 
convert(int, substring(a.index_size, 1, len(a.index_size)-3)) desc;

drop table #t

Statistics

stats, rebuild, reorg

DBCC SHOW_STATISTICS("my_tablename", my_indexname);
GO
DBCC SHOW_STATISTICS ("my_schemaname.my_tablename", my_indexname) WITH HISTOGRAM;
GO
1 

2 

sp_updatestats

USE mydb;
GO
EXEC sp_updatestats; 
GO

sp_autostats
USE AdventureWorks2012;
GO
EXEC sp_autostats 'Production.Product';  # show autostats setting
GO
USE AdventureWorks2012;
GO
EXEC sp_autostats 'Production.Product', 'ON'; # set stats
GO

USE AdventureWorks2012;
GO
EXEC sp_autostats 'Production.Product', 'OFF', AK_Product_Name; #disable autostats for an index
GO

1 

DROP STATISTICS 
-- Create the statistics groups.
USE AdventureWorks2012;
GO
CREATE STATISTICS VendorCredit
    ON Purchasing.Vendor (Name, CreditRating)
    WITH SAMPLE 50 PERCENT
CREATE STATISTICS CustomerTotal
    ON Sales.SalesOrderHeader (CustomerID, TotalDue)
    WITH FULLSCAN;
GO
DROP STATISTICS Purchasing.Vendor.VendorCredit, Sales.SalesOrderHeader.CustomerTotal;

SQL Server Transaction Programming Technique

USE tempdb
GO
drop proc testp
GO
CREATE PROC testp @var int
AS
CREATE TABLE #temp (k1 int identity)
IF @var=1
 ALTER TABLE #temp ADD c1 int
ELSE
 ALTER TABLE #temp ADD c1 varchar(2)
INSERT #temp DEFAULT VALUES
EXEC dbo.sp_executesql N'SELECT c1 FROM #temp'
GO
exec testp 2

USE master
IF OBJECT_ID('dbo.sp_created') IS NOT NULL
 DROP PROC dbo.sp_created
GO
CREATE PROC dbo.sp_created @objname sysname=NULL
/*
Object: sp_created
Description: Lists the creation date(s) for the specified object(s)
Usage: sp_created @objname="Object name or mask you want to display"
Returns: (None)
$Author: Khen $. Email: khen@khen.com
$Revision: 2 $
Example: sp_created @objname="myprocs%"
Created: 1999-08-01. $Modtime: 1/04/01 12:16a $.
*/
AS
IF (@objname IS NULL) or (@objname='/?') GOTO Help
SELECT name, crdate FROM sysobjects
WHERE name like @objname
RETURN 0
Help:
EXEC dbo.sp_usage @objectname='sp_created',
    @desc='Lists the creation date(s) for the specified object(s)',
    @parameters='@objname="Object name or mask you want to display"',
    @example='sp_created @objname="myprocs%"',
    @author='Ken Henderson',
    @email='khen@khen.com',
    @version='1', @revision='0',
    @datecreated='19990801', @datelastchanged='19990815'
RETURN -1
GO
USE Northwind
EXEC dbo.sp_created 'Order%'

Marked As system objects

USE master
GO
IF OBJECT_ID('dbo.sp_test') IS NOT NULL
 DROP PROC dbo.sp_test
GO
CREATE PROC dbo.sp_test AS
select 1
GO
SELECT OBJECTPROPERTY(OBJECT_ID('dbo.sp_test'),'IsMSShipped') AS 'System Object?', status, status & 0xC0000000
FROM sysobjects WHERE NAME = 'sp_test'
GO
EXEC sp_MS_marksystemobject 'sp_test'
GO
SELECT OBJECTPROPERTY(OBJECT_ID('dbo.sp_test'),'IsMSShipped') AS 'System Object?', status, status & 0xC0000000
FROM sysobjects WHERE NAME = 'sp_test'

John Kauffman
1 
2 

John.Kauffman@microsoft.com

1 
1 

1 

1 

1 

1 

1 

Change DB Owner

sp_changedbowner
1 
1 

EXEC sp_changedbowner 'Albert';
ALTER AUTHORIZATION ON DATABASE::my_db_name to sa;

ALTER AUTHORIZATION ON DATABASE::[xxx] TO sa;
GO

Change dbowner with ssms: 
object explorer --> database --> properties --> Files --> Owner --> sa

table, view, schema, 
1 

Change DB to single User Mode

1 
1 
1 
1 

select d.name, d.dbid, spid, login_time, nt_domain, nt_username, loginame
from sysprocesses p inner join sysdatabases d on p.dbid = d.dbid
where d.name = 'mydb';

Set a Database to Single-user Mode
database/properties/Options/Restrict Access/single/(Kill)Open Connections/Yes
USE master;
GO
ALTER DATABASE AdventureWorks2012 SET SINGLE_USER WITH ROLLBACK After 30;
GO
ALTER DATABASE AdventureWorks2012 SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
ALTER DATABASE AdventureWorks2012 SET SINGLE_USER WITH NO_WAIT;
GO
ALTER DATABASE AdventureWorks2012 SET READ_ONLY;
GO
ALTER DATABASE AdventureWorks2012 SET MULTI_USER;
GO
ALTER DATABASE 'DB NAME' SET MULTI_USER WITH ROLLBACK IMMEDIAT


-- Rename a database
1 
ALTER DATABASE AdventureWorks2012
Modify Name = Northwind ;
GO

Database Features And Task:
1 

sqlservercentral 

SELECT @@VERSION
go
SELECT @@version;
go

sys.dm_os_waiting_tasks;
sys.dm_os_wait_stats;

set statistics io on;



sqlcmd

-E: windows authentication

-- --------------------------------------
create endpoint

-- --------------------------------------
set showplan_all on
go

set showplan_all off
go

set statistics profile on
go

set statistics profile off
go
-- --------------------------------------

select top 10 ... ...

-- ---------------------------------------

dtswizard --> Import and Export Data

-- ---------------------------------------
sqlcmd -s  -q "exec sp_databases"
sqlcmd -s localhost -q "exec sp_databases"
sqlcmd -s dbserver -u loginname -p password -q "exec sp_databases"
sqlcmd -s ./MSSQLSERVER -u loginname -p password -q "exec sp_databases" 



SELECT name FROM sys.databases;

exec sp_databases;
go

exec xp_logininfo;
go

EXEC xp_logininfo 'BUILTIN\Administrators'
go

SELECT * FROM sysfiles;
go

sp_who3

;WITH  
 XMLNAMESPACES 
     (DEFAULT N'http://schemas.microsoft.com/sqlserver/2004/07/showplan'   
             ,N'http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS ShowPlan) 
SELECT
    SPID                = er.session_id
    ,Status             = ses.status
    ,[Login]            = ses.login_name
    ,Host               = ses.host_name
    ,BlkBy              = er.blocking_session_id
    ,DBName             = DB_Name(er.database_id)
    ,CommandType        = er.command
    ,SQLStatement       =
        SUBSTRING
        (
            qt.text,
            er.statement_start_offset/2,
            (CASE WHEN er.statement_end_offset = -1
                THEN LEN(CONVERT(nvarchar(MAX), qt.text)) * 2
                ELSE er.statement_end_offset
                END - er.statement_start_offset)/2
        )
    ,QueryPlan          = qp.query_plan 
    ,ObjectName         = OBJECT_SCHEMA_NAME(qt.objectid,qt.dbid) + '.' + OBJECT_NAME(qt.objectid, qt.dbid)
    ,ElapsedMS          = er.total_elapsed_time
    ,CPUTime            = er.cpu_time
    ,IOReads            = er.logical_reads + er.reads
    ,IOWrites           = er.writes
    ,LastWaitType       = er.last_wait_type
    ,StartTime          = er.start_time
    ,Protocol           = con.net_transport
    ,transaction_isolation =
        CASE ses.transaction_isolation_level
            WHEN 0 THEN 'Unspecified'
            WHEN 1 THEN 'Read Uncommitted'
            WHEN 2 THEN 'Read Committed'
            WHEN 3 THEN 'Repeatable'
            WHEN 4 THEN 'Serializable'
            WHEN 5 THEN 'Snapshot'
        END
    ,ConnectionWrites   = con.num_writes
    ,ConnectionReads    = con.num_reads
    ,ClientAddress      = con.client_net_address
    ,Authentication     = con.auth_scheme
FROM sys.dm_exec_requests er
LEFT JOIN sys.dm_exec_sessions ses
ON ses.session_id = er.session_id
LEFT JOIN sys.dm_exec_connections con
ON con.session_id = ses.session_id
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) as qt
CROSS APPLY sys.dm_exec_query_plan(er.plan_handle) as qp
WHERE  er.session_id > 50
ORDER BY
    er.blocking_session_id DESC
    ,er.session_id

current running sql

;WITH  
 XMLNAMESPACES 
     (DEFAULT N'http://schemas.microsoft.com/sqlserver/2004/07/showplan'   
             ,N'http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS ShowPlan)
SELECT s.loginame, db_name(s.dbid) name, s.hostname, s.program_name, 
t.text Statement, p.query_plan QueryPlan, s.stmt_start, s.stmt_end, s.spid, 
CONVERT(smallint, s.waittype) waittype, s.lastwaittype, s.ecid, s.waittime, 
CONVERT(varchar(64), s.context_info) context_info, RTRIM(r.wait_resource) waitresource, 
s.blocked, r.statement_start_offset, r.statement_end_offset, 
r.start_time, q.plan_generation_num  
FROM 
master..sysprocesses AS s WITH(NOLOCK) LEFT OUTER JOIN 
sys.dm_exec_requests r ON r.session_id = s.spid LEFT OUTER JOIN 
sys.dm_exec_query_stats q ON q.plan_handle = r.plan_handle AND 
q.statement_start_offset = r.statement_start_offset AND 
q.statement_end_offset = r.statement_end_offset 
CROSS APPLY sys.dm_exec_sql_text(r.[plan_handle]) AS t 
CROSS APPLY sys.dm_exec_query_plan(r.[plan_handle]) AS p
WHERE (s.dbid<>0 AND s.cmd<>'AWAITING COMMAND' 
AND s.cmd NOT LIKE '%BACKUP%' 
AND s.cmd NOT LIKE '%RESTORE%' 
AND s.cmd NOT LIKE 'FG MONITOR%' 
AND s.hostprocess > '' 
AND s.spid>50 AND s.spid<>@@SPID)  
AND lastwaittype NOT IN ('SLEEP_TASK')

Missing Index

;WITH  
 XMLNAMESPACES 
     (DEFAULT N'http://schemas.microsoft.com/sqlserver/2004/07/showplan'   
             ,N'http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS ShowPlan)       
SELECT top 5 ECP.[usecounts]    AS [UsageCounts] 
      ,ECP.[refcounts]    AS [RefencedCounts] 
      ,ECP.[objtype]      AS [ObjectType] 
      ,ECP.[cacheobjtype] AS [CacheObjectType] 
      ,EST.[dbid]         AS [DatabaseID] 
      ,EST.[objectid]     AS [ObjectID] 
      ,EST.[text]         AS [Statement]       
      ,EQP.[query_plan]   AS [QueryPlan] 
FROM sys.dm_exec_cached_plans AS ECP 
     CROSS APPLY sys.dm_exec_sql_text(ECP.[plan_handle]) AS EST 
     CROSS APPLY sys.dm_exec_query_plan(ECP.[plan_handle]) AS EQP 
WHERE ECP.[usecounts] > 1  -- Plan should be used more then one time (= no AdHoc queries) 
      AND EQP.[query_plan].exist(N'/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/QueryPlan/MissingIndexes/MissingIndexGroup') <> 0 
ORDER BY ECP.[usecounts] DESC

DMV:
Clearing the wait statistics on a server.
DBCC SQLPERF('sys.dm_os_wait_stats', clear);
DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR);

sys.dm_io_virtual_file_stats
sys.dm_exec_query_stats
sys.dm_os_wait_stats
sys.dm_os_waiting_tasks
sys.dm_exec_requests

sys.dm_exec_query_stats 
sys.dm_exec_sql_text

SELECT DISTINCT
wt.wait_type
FROM sys.dm_os_waiting_tasks AS wt
JOIN sys.dm_exec_sessions AS s ON wt.session_id = s.session_id
WHERE s.is_user_process = 0;

SELECT TOP 10
wait_type ,
max_wait_time_ms wait_time_ms ,
signal_wait_time_ms ,
wait_time_ms - signal_wait_time_ms AS resource_wait_time_ms ,
100.0 * wait_time_ms / SUM(wait_time_ms) OVER ( )
AS percent_total_waits ,
100.0 * signal_wait_time_ms / SUM(signal_wait_time_ms) OVER ( )
AS percent_total_signal_waits ,
100.0 * ( wait_time_ms - signal_wait_time_ms )
/ SUM(wait_time_ms) OVER ( ) AS percent_total_resource_waits
FROM sys.dm_os_wait_stats
WHERE wait_time_ms > 0 -- remove zero wait_time
AND wait_type NOT IN -- filter out additional irrelevant waits
( 'SLEEP_TASK', 'BROKER_TASK_STOP', 'BROKER_TO_FLUSH',
'SQLTRACE_BUFFER_FLUSH','CLR_AUTO_EVENT', 'CLR_MANUAL_EVENT',
'LAZYWRITER_SLEEP', 'SLEEP_SYSTEMTASK', 'SLEEP_BPOOL_FLUSH',
'BROKER_EVENTHANDLER', 'XE_DISPATCHER_WAIT', 'FT_IFTSHC_MUTEX',
'CHECKPOINT_QUEUE', 'FT_IFTS_SCHEDULER_IDLE_WAIT',
'BROKER_TRANSMITTER', 'FT_IFTSHC_MUTEX', 'KSOURCE_WAKEUP',
'LAZYWRITER_SLEEP', 'LOGMGR_QUEUE', 'ONDEMAND_TASK_QUEUE',
'REQUEST_FOR_DEADLOCK_SEARCH', 'XE_TIMER_EVENT', 'BAD_PAGE_PROCESS',
'DBMIRROR_EVENTS_QUEUE', 'BROKER_RECEIVE_WAITFOR',
'PREEMPTIVE_OS_GETPROCADDRESS', 'PREEMPTIVE_OS_AUTHENTICATIONOPS',
'WAITFOR', 'DISPATCHER_QUEUE_SEMAPHORE', 'XE_DISPATCHER_JOIN',
'RESOURCE_QUEUE' )
ORDER BY wait_time_ms DESC;

SELECT DB_NAME(vfs.database_id) AS database_name ,
vfs.database_id ,
vfs.FILE_ID ,
io_stall_read_ms / NULLIF(num_of_reads, 0) AS avg_read_latency ,
io_stall_write_ms / NULLIF(num_of_writes, 0)
AS avg_write_latency ,
io_stall / NULLIF(num_of_reads + num_of_writes, 0)
AS avg_total_latency ,
num_of_bytes_read / NULLIF(num_of_reads, 0)
AS avg_bytes_per_read ,
num_of_bytes_written / NULLIF(num_of_writes, 0)
AS avg_bytes_per_write ,
vfs.io_stall ,
vfs.num_of_reads ,
vfs.num_of_bytes_read ,
vfs.io_stall_read_ms ,
vfs.num_of_writes ,
vfs.num_of_bytes_written ,
vfs.io_stall_write_ms ,
size_on_disk_bytes / 1024 / 1024. AS size_on_disk_mbytes ,
physical_name
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS vfs
JOIN sys.master_files AS mf ON vfs.database_id = mf.database_id
AND vfs.FILE_ID = mf.FILE_ID
ORDER BY avg_total_latency DESC
;

SELECT TOP 10
execution_count ,
statement_start_offset AS stmt_start_offset ,
sql_handle ,
plan_handle ,
total_logical_reads / execution_count AS avg_logical_reads ,
total_logical_writes / execution_count AS avg_logical_writes ,
total_physical_reads / execution_count AS avg_physical_reads ,
t.text
FROM sys.dm_exec_query_stats AS s
CROSS APPLY sys.dm_exec_sql_text(s.sql_handle) AS t
ORDER BY avg_physical_reads DESC
;

SELECT SUM(signal_wait_time_ms) AS TotalSignalWaitTime ,
( SUM(CAST(signal_wait_time_ms AS NUMERIC(20, 2)))
/ SUM(CAST(wait_time_ms AS NUMERIC(20, 2))) * 100 )
AS PercentageSignalWaitsOfTotalTime
FROM sys.dm_os_wait_stats;

SELECT TOP ( 10 )
wait_type ,
waiting_tasks_count ,
( wait_time_ms - signal_wait_time_ms ) AS resource_wait_time ,
max_wait_time_ms ,
CASE waiting_tasks_count
WHEN 0 THEN 0
ELSE wait_time_ms / waiting_tasks_count
END AS avg_wait_time
FROM sys.dm_os_wait_stats
WHERE wait_type NOT LIKE '%SLEEP%' -- remove eg. SLEEP_TASK and
-- LAZYWRITER_SLEEP waits
AND wait_type NOT LIKE 'XE%'
AND wait_type NOT IN -- remove system waits
( 'KSOURCE_WAKEUP', 'BROKER_TASK_STOP', 'FT_IFTS_SCHEDULER_IDLE_WAIT',
'SQLTRACE_BUFFER_FLUSH', 'CLR_AUTO_EVENT', 'BROKER_EVENTHANDLER',
'BAD_PAGE_PROCESS', 'BROKER_TRANSMITTER', 'CHECKPOINT_QUEUE',
'DBMIRROR_EVENTS_QUEUE', 'SQLTRACE_BUFFER_FLUSH', 'CLR_MANUAL_EVENT',
'ONDEMAND_TASK_QUEUE', 'REQUEST_FOR_DEADLOCK_SEARCH', 'LOGMGR_QUEUE',
'BROKER_RECEIVE_WAITFOR', 'PREEMPTIVE_OS_GETPROCADDRESS',
'PREEMPTIVE_OS_AUTHENTICATIONOPS', 'BROKER_TO_FLUSH' )
ORDER BY wait_time_ms DESC;

SELECT scheduler_id ,
current_tasks_count ,
runnable_tasks_count
FROM sys.dm_os_schedulers
WHERE scheduler_id < 255;

SP_CONFIGURE 'show advanced options', 1 ;
GO
RECONFIGURE ;
GO
-- Blocked Process Report event will fire repeatedly, every 10 seconds
SP_CONFIGURE 'blocked process threshold', 10 ;
GO
RECONFIGURE ;
GO
SP_CONFIGURE 'blocked process threshold', 0 ;
GO
RECONFIGURE ;
GO

The AUTO_UPDATE_STATISTICS database option
AUTO_UPDATE_STATISTICS_ASYNCHRONOUSLY database option to TRUE.

sp_configure 'default trace enabled', 0

SELECT name FROM sys.objects WHERE object_id = 1836025772;

SELECT CAST(name AS char(20)) AS Name, is_disabled
FROM sys.foreign_keys WHERE name = 'FK_Items_Boxes' ;

SELECT OBJECT_NAME(t.parent_id), te.type_desc
FROM sys.triggers AS t INNER JOIN sys.trigger_events AS te 
ON t.OBJECT_ID = te.OBJECT_ID
GROUP BY OBJECT_NAME(t.parent_id),te.type_descHAVING COUNT(*) > 1 ;

SELECT * FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_NAME = 'Codes' AND TABLE_SCHEMA = 'dbo' );

SET NOCOUNT ON;

SET STATISTICS TIME ON ;

SET LANGUAGE 'us_english' ;

SET XACT_ABORT ON;
SET XACT_ABORT OFF ;

SET DEADLOCK_PRIORITY LOW ;

BEGIN CATCH ; 
PRINT 'Entering CATCH block' ; 
IF XACT_STATE () = 1 
    BEGIN ; 
       PRINT 'Transaction is committable' ; 
       COMMIT ; 
    END ; 
IF XACT_STATE () = -1 
     BEGIN ; 
      PRINT 'Transaction is not committable' ; 
      ROLLBACK ; 
    END ;
END CATCH ;
PRINT 'Ending batch';

SET ROWCOUNT 1;
SET ROWCOUNT 0;

CREATE DATABASE DW
WITH (
AUTOGROW = ON,
REPLICATED_SIZE = 50,
DISTRIBUTED_SIZE = 10000,
LOG_SIZE = 25
);

ALTER DATABASE Test SET READ_COMMITTED_SNAPSHOT ON;
GO
ALTER DATABASE Test SET READ_COMMITTED_SNAPSHOT OFF ;
GO
ALTER DATABASE Test SET ALLOW_SNAPSHOT_ISOLATION ON;
GO

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ ;
Go
SET TRANSACTION ISOLATION LEVEL SNAPSHOT ;
Go
SET TRANSACTION ISOLATION LEVEL READ COMMITTED ;
Go

SET DEADLOCK_PRIORITY HIGH ;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

EXEC msdb.dbo.sp_start_job @job_id=N'cb73ea96-9a96-49fe-ada9-a70a941f9fb9';
go
SELECT * FROM msdb.dbo.sysjobs_view;
go

declare 
@dt datetime 
select @dt = cast(N'2009-07-22T14:19:13' asdatetime)
EXEC msdb.dbo.sp_delete_backuphistory @dt
GO
EXEC msdb.dbo.sp_purge_jobhistory @oldest_date='2009-07-22T14:19:13'
GO
EXECUTE msdb..sp_maintplan_delete_log null,null,'2009-07-22T14:19:13'

EXECUTE master.dbo.xp_delete_file 1,N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log',N'txt',N'2009-07-23T12:55:05'

sp_delete_backuphistory
sp_purge_jobhistory
sp_maintplan_delete_log

-- remove old data from msdb:
sp_delete_backuphistory
sp_purge_jobhistory
sp_maintplan_delete_log

BACKUP DATABASE [AdventureWorks] 
TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup\AdventureWorks_backup_2009_08_19_145336_3160000.bak'
WITH NOFORMAT, NOINIT, NAME = N'AdventureWorks_backup_2009_08_19_145336_3150000', 
SKIP, REWIND, NOUNLOAD, STATS = 10;
go
BACKUP LOG [AdventureWorks] TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup\AdventureWorks_backup_2009_08_20_111623_3462370.trn' 
WITH NOFORMAT, NOINIT, NAME = N'AdventureWorks_backup_2009_08_20_111623_3462370', 
SKIP, REWIND, NOUNLOAD, STATS = 10
go

-- Check Database Integrity
-- check the physical integrity of their databases
DBCC CHECKDB

DBCC CHECKDB('database_name')WITH NO_INFOMSGS
DBCC CHECKDB ('DATABASE_NAME') WITH NO_INFOMSGS, ALL_ERRORMSGS

DBCC CHECKDB('database_name', NOINDEX)

DBCC CHECKDB('database_name') WITH NO_INFOMSGS;
go
DBCC CHECKDB ('DATABASE_NAME') WITH NO_INFOMSGS, ALL_ERRORMSGS;
go
DBCC CHECKDB('database_name', NOINDEX)
go

USE [AdventureWorks]
GO
DBCC CHECKDB(N''AdventureWorks'') WITH NO_INFOMSGS
GO
-- USE [AdventureWorks]
-- GO
-- DBCC CHECKDB(N''AdventureWorks'') WITH NO_INFOMSGS
-- GO

-- Shrink Database

-- Check Database Integrity
1 

1 

AUTO_CREATE_STATISTICS
AUTO_UPDATE_STATISTICS

UPDATE STATISTICS 
UPDATE STATISTICS table_name WITH FULLSCAN;

UPDATE STATISTICS 
or sp_updatestats commands

UPDATE STATISTICS table_name WITH FULLSCAN;

use [AdventureWorks]
GO
UPDATE STATISTICS [dbo].[AWBuildVersion]
WITH FULLSCAN
GO
use [AdventureWorks]|
GO
UPDATE STATISTICS [dbo].[DatabaseLog]
WITH FULLSCAN
GO

sp_updatestats