Stored Procedure – Clean
Cache and Clean Buffer
DBCC FREEPROCCACHE (
ALTER DATABASE SCOPED
CONFIGURATION CLEAR PROCEDURE_CACHE)
DBCC DROPCLEANBUFFERS
sys.dm_os_buffer_descriptors returns cached pages for all
user and system databases
DBCC Commands can be ran to free
memory:
- DBCC FREESYSTEMCACHE
- DBCC FREESESSIONCACHE
- DBCC FREEPROCCACHE
DBCC FREESYSTEMCACHE:
Releases all unused cache
entries from all caches.
DBCC FREESESSIONCACHE
Flushes the distributed query connection cache used by distributed queries against an instance of Microsoft SQL Server.
Flushes the distributed query connection cache used by distributed queries against an instance of Microsoft SQL Server.
USE database_name
CHECKPOINT;
DBCC shrinkfile (logfilename, 1)
DBCC shrinkfile (logfilename, 1)
USE AdventureWorks
GO
BACKUP LOG AdventureWorks TO DISK='d:\adtlog.bak'
GO
-- Get Logical file name of the log file
sp_helpfile
GO
DBCC SHRINKFILE(AdventureWorks_Log,TRUNCATEONLY)
GO
ALTER DATABASE AdventureWorks
MODIFY FILE
(NAME = AdventureWorks_Log,SIZE = 1GB)
GO
DBCC LOGINFO
GO
GO
BACKUP LOG AdventureWorks TO DISK='d:\adtlog.bak'
GO
-- Get Logical file name of the log file
sp_helpfile
GO
DBCC SHRINKFILE(AdventureWorks_Log,TRUNCATEONLY)
GO
ALTER DATABASE AdventureWorks
MODIFY FILE
(NAME = AdventureWorks_Log,SIZE = 1GB)
GO
DBCC LOGINFO
GO
DBCC
CHECKIDENT can reseed (reset) the identity value of the table. For example,
YourTable has 25 rows with 25 as last identity. If we want next record to have
identity as 35 we need to run following T SQL script in Query Analyzer.
DBCC CHECKIDENT (yourtable, reseed, 34)
Call your Stored Procedure prefixed with
dbo.SPName – fully qualified name.
When SP are called prefixed with dbo. or database.dbo. it will prevent SQL Server from placing a COMPILE lock on the procedure. While SP executes it determines if all objects referenced in the code have the same owners as the objects in the current cached procedure plan.
When SP are called prefixed with dbo. or database.dbo. it will prevent SQL Server from placing a COMPILE lock on the procedure. While SP executes it determines if all objects referenced in the code have the same owners as the objects in the current cached procedure plan.
PATINDEX is CHARINDEX + WildCard Search. Use
either of them depending your need.\\\
·
Set a
reasonable size of your database and transaction log (25% of database size).
·
Leave the Autogrow feature ON for the data files and for the log files
with reasonable size of autogrow increment.
·
Don’t set the autoshrink feature run the task during off-peak hours.
·
Place the log files on other physical disk arrays than those with the
data files to improve I/O Performance.
ALTER DATABASE [mydb] SET OFFLINE WITH ROLLBACK AFTER 30 SECONDS
OR
ALTER DATABASE [mydb] SET OFFLINE WITH ROLLBACK IMMEDIATE
OR
ALTER DATABASE [mydb] SET OFFLINE WITH ROLLBACK IMMEDIATE
RAID (redundant array
of independent disks) levels 0, 1, and 5 are typically implemented with SQL
Server.
- Use SORT_IN_TEMPDB option when the table is
created if tempdb is on different disks. This will increase the
performance to create the Index.
- Rebuild Index frequently using ALTER INDEX and
De-fragment Index to keep performance optimal for Indexes.
ALTER DATABASE AdventureWorksSET SINGLE_USER WITHROLLBACK IMMEDIATERESTORE DATABASE AdventureWorksFROM DISK = 'C\:BackupAdventureworks.bak'WITH MOVE 'AdventureWorks_Data' TO 'C:\Data\datafile.mdf',MOVE 'AdventureWorks_Log' TO 'C:\Data\logfile.ldf',REPLACE
USE pubs
GO
SELECT a.au_id
FROM authors a
JOIN titleauthor b ON a.au_id = b.au_id
OPTION (MERGE JOIN)
GO
GO
SELECT a.au_id
FROM authors a
JOIN titleauthor b ON a.au_id = b.au_id
OPTION (MERGE JOIN)
GO
DBAs are creating all the tables with
no Clustered Index.
USE AdventureWorks ----Replace
AdventureWorks with your DBName
GO
SELECT DISTINCT [TABLE] = OBJECT_NAME(OBJECT_ID)
FROM SYS.INDEXES
WHERE INDEX_ID = 0
AND OBJECTPROPERTY(OBJECT_ID,'IsUserTable') = 1
ORDER BY [TABLE] GO
GO
SELECT DISTINCT [TABLE] = OBJECT_NAME(OBJECT_ID)
FROM SYS.INDEXES
WHERE INDEX_ID = 0
AND OBJECTPROPERTY(OBJECT_ID,'IsUserTable') = 1
ORDER BY [TABLE] GO
sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
sp_configure 'fill factor', 90
GO
RECONFIGURE
GO
EXEC sys.sp_configure 'show advanced options', '1'
RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure 'fill factor (%)', '90'
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure 'show advanced options', '0'
RECONFIGURE WITH OVERRIDE
GO
Disable
Nesting/Recursing Triggers
Following script will stop executing all the nested triggers.
sp_CONFIGURE 'nested_triggers',0
GO
RECONFIGURE
GO
Following script will stop executing all the nested triggers.
sp_CONFIGURE 'nested_triggers',0
GO
RECONFIGURE
GO
There is also alternate way to stop
Trigger Recursion
ALTER DATABASE databasename
SET RECURSIVE_TRIGGERS ON | OFF
ALTER DATABASE databasename
SET RECURSIVE_TRIGGERS ON | OFF
Restrict Trigger
Nesting to certain level
Put following script in trigger code. This will stop the trigger recursion after certain levels. In following case it will stop after 5 recursion.
IF ((
SELECT TRIGGER_NESTLEVEL()) > 5 )
RETURN
Put following script in trigger code. This will stop the trigger recursion after certain levels. In following case it will stop after 5 recursion.
IF ((
SELECT TRIGGER_NESTLEVEL()) > 5 )
RETURN
USE AdventureWorks
GO
----Diable Index
ALTER INDEX [IX_StoreContact_ContactTypeID] ON Sales.StoreContact DISABLE
GO
----Enable Index
ALTER INDEX [IX_StoreContact_ContactTypeID] ON Sales.StoreContact REBUILD
GO
GO
----Diable Index
ALTER INDEX [IX_StoreContact_ContactTypeID] ON Sales.StoreContact DISABLE
GO
----Enable Index
ALTER INDEX [IX_StoreContact_ContactTypeID] ON Sales.StoreContact REBUILD
GO
SQL SERVER
2005 Syntax
SET DEADLOCK_PRIORITY { LOW | NORMAL | HIGH | <numeric-priority> | @deadlock_var | @deadlock_intvar }
<numeric-priority> ::= { -10 | -9 | -8 | … | 0 | … | 8 | 9 | 10 }
Example:
The following example sets the deadlock priority to NORMAL.
SET DEADLOCK_PRIORITY NORMAL;
GO
SET DEADLOCK_PRIORITY { LOW | NORMAL | HIGH | <numeric-priority> | @deadlock_var | @deadlock_intvar }
<numeric-priority> ::= { -10 | -9 | -8 | … | 0 | … | 8 | 9 | 10 }
Example:
The following example sets the deadlock priority to NORMAL.
SET DEADLOCK_PRIORITY NORMAL;
GO
Best
Practices and Recommendations for TempDB in SQL Server.
Analyze the existing workload and adjust the space for projected concurrent activities in SQL TempDB.
SQL Server does not cache the temp table if it is created as part of dynamic SQL.
Perform index maintenance and update statistics on TempDB as well, even thought objects in TempDB are not permanent.
Set Auto Grow to ON for TempDB.
Instant file initialization improves the performance of auto grow operations in TempDB.
Create TempDB database on other disk drive than other database. This will improve performance for database as different disk controller will improve the performance for disk input/output.
Analyze the existing workload and adjust the space for projected concurrent activities in SQL TempDB.
SQL Server does not cache the temp table if it is created as part of dynamic SQL.
Perform index maintenance and update statistics on TempDB as well, even thought objects in TempDB are not permanent.
Set Auto Grow to ON for TempDB.
Instant file initialization improves the performance of auto grow operations in TempDB.
Create TempDB database on other disk drive than other database. This will improve performance for database as different disk controller will improve the performance for disk input/output.
SQL SERVER 2000:
DBCC SHOWCONTIG was used to find index fragmentation. In SQL SERVER 2005 it is deprecated and replaced by query to sys.DM_DB_INDEX_PHYSICAL_STATS.
DBCC SHOWCONTIG was used to find index fragmentation. In SQL SERVER 2005 it is deprecated and replaced by query to sys.DM_DB_INDEX_PHYSICAL_STATS.
SQL SERVER 2005:
SELECT query to sys.DM_DB_INDEX_PHYSICAL_STATS displays all the Index Fragmentation related information.
SELECT query to sys.DM_DB_INDEX_PHYSICAL_STATS displays all the Index Fragmentation related information.
To return
the Index Information for only Sales.SalesOrderDetail Table:
To return all the information for all the Indexes in Database:
USE AdventureWorks;SELECT INDEX_ID, AVG_FRAGMENTATION_IN_PERCENTFROM sys.dm_db_index_physical_stats(DB_ID(),OBJECT_ID(N'Sales.SalesOrderDetail'), NULL, NULL, 'DETAILED')To return all the information for all the Indexes in Database:
SELECT *FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, NULL);
SQL SERVER
2000:
The DBCC INDEXDEFRAG and DBCC DBREINDEX statements are used to reduce table fragmentation.
The DBCC INDEXDEFRAG and DBCC DBREINDEX statements are used to reduce table fragmentation.
To Rebuild
only one Index:
TO Rebuild all the Indexes on Table with Specifying options:
USE AdventureWorksGOALTER INDEX PK_ProductPhoto_ProductPhotoIDON Production.ProductPhotoREORGANIZEGOTO Rebuild all the Indexes on Table with Specifying options:
USE AdventureWorksGOALTER INDEX ALL ON Production.ProductREBUILD WITH (FILLFACTOR = 90, SORT_IN_TEMPDB = ON,STATISTICS_NORECOMPUTE = ON)GO
T-SQL to rebuild all the indexes on table.
USE AdventureWorks;GOALTER INDEX ALL ON Production.Product REBUILDGO
-- Change database Parameterization to
Forced
ALTER DATABASE AdventureWorks
SET PARAMETERIZATION FORCED
GO
-- Change database Parameterization to Simple
ALTER DATABASE AdventureWorks
SET PARAMETERIZATION Simple
GO
ALTER DATABASE AdventureWorks
SET PARAMETERIZATION FORCED
GO
-- Change database Parameterization to Simple
ALTER DATABASE AdventureWorks
SET PARAMETERIZATION Simple
GO
Find Unused Indexes of Current Database
USE AdventureWorks
GO
DECLARE @dbid INT
SELECT @dbid = DB_ID(DB_NAME())
SELECT OBJECTNAME = OBJECT_NAME(I.OBJECT_ID),
INDEXNAME = I.NAME,
I.INDEX_ID
FROM SYS.INDEXES I
JOIN SYS.OBJECTS O
ON I.OBJECT_ID = O.OBJECT_ID
WHERE OBJECTPROPERTY(O.OBJECT_ID,'IsUserTable') = 1
AND I.INDEX_ID NOT IN (
SELECT S.INDEX_ID
FROM SYS.DM_DB_INDEX_USAGE_STATS S
WHERE S.OBJECT_ID = I.OBJECT_ID
AND I.INDEX_ID = S.INDEX_ID
AND DATABASE_ID = @dbid)
ORDER BY OBJECTNAME,
I.INDEX_ID,
INDEXNAME ASC
GO
GO
DECLARE @dbid INT
SELECT @dbid = DB_ID(DB_NAME())
SELECT OBJECTNAME = OBJECT_NAME(I.OBJECT_ID),
INDEXNAME = I.NAME,
I.INDEX_ID
FROM SYS.INDEXES I
JOIN SYS.OBJECTS O
ON I.OBJECT_ID = O.OBJECT_ID
WHERE OBJECTPROPERTY(O.OBJECT_ID,'IsUserTable') = 1
AND I.INDEX_ID NOT IN (
SELECT S.INDEX_ID
FROM SYS.DM_DB_INDEX_USAGE_STATS S
WHERE S.OBJECT_ID = I.OBJECT_ID
AND I.INDEX_ID = S.INDEX_ID
AND DATABASE_ID = @dbid)
ORDER BY OBJECTNAME,
I.INDEX_ID,
INDEXNAME ASC
GO
Display Fragmentation Information of Data and
Indexes of Database Table
SELECT OBJECT_NAME(i.OBJECT_ID) AS TableName,
i.name AS IndexName,
indexstats.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') indexstats
INNER JOIN sys.indexes i ON i.OBJECT_ID = indexstats.OBJECT_ID
AND i.index_id = indexstats.index_id
WHERE indexstats.avg_fragmentation_in_percent > 20