VIEW SERVER STATE permission required for the user to execute DMO objects with SQL Server 2005, 2008, and 2008 R2.
To identify the permission already exists / not:
IF EXISTS(SELECT 1 FROM fn_my_permissions(NULL,'SERVER')
WHERE permission_name = 'VIEW SERVER STATE')
SELECT 'Permission Exists' 'Permission'
ELSE
SELECT 'Permission not Exists' 'Permission'
Go
To grand permission to the login:
USE master
Go
GRANT VIEW SERVER STATE TO loginID
Go
Who is accessing your Database ?
We can identify that who are all accessing your Database with the various information as given below..
- When they connected
- From which machine name
- From which machine IP
- What program they using
- Login Name
- From which session
- Current request mode
- Active status
- What query thay finally applied
Here, I have used four DMOs - sys.dm_tran_locks, sys.dm_exec_sessions, sys.dm_exec_connections, sys.dm_exec_sql_text.
I try to identify that who are all accessing my SQLServerBuddy database.
SELECT DB_NAME(l.resource_database_id) 'Database Name',
l.request_mode 'Request Mode',
l.request_status 'Status',
l.request_session_id 'SessionID',
s.login_time 'Connected at',
c.client_net_address 'Client IP',
s.[host_name] 'Client Machine',
s.[program_name] 'Program Name',
s.login_name 'Login',
q.[text] 'Query Used'
FROM sys.dm_tran_locks l JOIN sys.dm_exec_sessions s
ON(L.request_session_id = S.session_id)
JOIN sys.dm_exec_connections c CROSS APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) q
ON(C.session_id = S.session_id)
WHERE l.resource_type = 'DATABASE'
AND l.resource_database_id = DB_ID('SQLServerBuddy')
AND l.request_session_id <> @@SPID
- When they connected
- From which machine name
- From which machine IP
- What program they using
- Login Name
- From which session
- Current request mode
- Active status
- What query thay finally applied
Here, I have used four DMOs - sys.dm_tran_locks, sys.dm_exec_sessions, sys.dm_exec_connections, sys.dm_exec_sql_text.
I try to identify that who are all accessing my SQLServerBuddy database.
SELECT DB_NAME(l.resource_database_id) 'Database Name',
l.request_mode 'Request Mode',
l.request_status 'Status',
l.request_session_id 'SessionID',
s.login_time 'Connected at',
c.client_net_address 'Client IP',
s.[host_name] 'Client Machine',
s.[program_name] 'Program Name',
s.login_name 'Login',
q.[text] 'Query Used'
FROM sys.dm_tran_locks l JOIN sys.dm_exec_sessions s
ON(L.request_session_id = S.session_id)
JOIN sys.dm_exec_connections c CROSS APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) q
ON(C.session_id = S.session_id)
WHERE l.resource_type = 'DATABASE'
AND l.resource_database_id = DB_ID('SQLServerBuddy')
AND l.request_session_id <> @@SPID
Tips: Generate Sequence Number 1 - 100 (No Physical Table, No Loop)
How to generate sequence number 1 through 100 without any physical(System, User defined) table, Loop.
We can do that, In SQL Server 2005, 2008, 2008 R2 we have new feature called Recursive CTE - Common Table Expression.
CTE Syntax:
;WITH CTEName
AS
(
SELECT Statement1
UNION ALL
SELECT Statement2
)
SELECT * FROM CTEName[Query Hint]
Sample:
WITH CTEs
AS
(
SELECT 1 AS Sequence
UNION ALL
SELECT Sequence + 1 FROM CTEs WHERE Sequence < 100
)
SELECT * FROM CTEs
Result:
We can do that, In SQL Server 2005, 2008, 2008 R2 we have new feature called Recursive CTE - Common Table Expression.
CTE Syntax:
;WITH CTEName
AS
(
SELECT Statement1
UNION ALL
SELECT Statement2
)
SELECT * FROM CTEName
Sample:
WITH CTEs
AS
(
SELECT 1 AS Sequence
UNION ALL
SELECT Sequence + 1 FROM CTEs WHERE Sequence < 100
)
SELECT * FROM CTEs
Result:
Internally, Optimizer uses Nested Loop, But we are not using any loop in the script.
Varchar and NVarchar
Most of us freequently used NVARCHAR data type for storing normal alpha numeric characters, But we can simply use VARCHAR data type instead.
We can use VARCHAR data type insteadof NVARCHAR. Correct ?
A) When we go for NVarchar data type ?
B) Is there any internal differences between Varchar and NVarchar data types ?
According to BOL
varchar [ ( n max ) ]
1. Variable-length, non-Unicode character data. n can be a value from 1 through 8,000.
2. MAX indicates that the maximum storage size is 2^31-1 bytes.
3. The storage size is the actual length of data entered + 2 bytes.
4. The ISO synonyms for varchar are char varying or character varying.
5. When n is not specified in a data definition or variable declaration statement, the default length is 1.
6. When n is not specified when using the CAST and CONVERT functions, the default length is 30.
nvarchar [ ( n max ) ]
1. Variable-length Unicode character data. n can be a value from 1 through 4,000.
2. MAX indicates that the maximum storage size is 2^31-1 bytes.
3. The storage size, in bytes, is two times the number of characters entered + 2 bytes.
4. The ISO synonyms for nvarchar are national char varying and national character varying.
5. When n is not specified in a data definition or variable declaration statement, the default length is 1. When n is not specified with the CAST function, the default length is 30.
A) When we go for NVarchar data type ?
If we have sites that support multiple languages (Multi-lingual), consider using the Unicode nchar or nvarchar data types to minimize character conversion issues. Except that, we have to use Varchar data type.
B) Is there any internal differences between Varchar and NVarchar data types ?
YES.
Actually NVARCHAR data type uses Bytes in two times the number of characters used. But, In VARCHAR uses only the number of characters used.
NVARCHAR data type uses more memory spaces than VARCHAR data types. How ?
NVARCHAR data type requires more Subtree Cost and I/O than the VARCHAR data types. How ?
Creating Sample Tables and Records :
IF OBJECT_ID('Tb_SQLServer_VAR','U') IS NOT NULL
DROP TABLE Tb_SQLServer_VAR
GO
CREATE TABLE Tb_SQLServer_VAR
(
Column1 VARCHAR(100)
)
GO
IF OBJECT_ID('Tb_SQLServer_NVAR','U') IS NOT NULL
DROP TABLE Tb_SQLServer_NVAR
GO
CREATE TABLE Tb_SQLServer_NVAR
(
Column1 NVARCHAR(100)
)
GO
We have created two tables. Each table has only one column.
INSERT Tb_SQLServer_VAR VALUES('SQL Server Buddy')
GO 1000
Go
INSERT Tb_SQLServer_NVAR VALUES('SQL Server Buddy')
GO 1000
We have Inserted 1000 records in two tables (Tb_SQLServer_VAR, Tb_SQLServer_NVAR)
Identifying Space allocated on each Tables for VARCHAR, NVARCHAR data types:
SELECT OBJECT_NAME(p.[object_id]) 'Table Name',
p.[rows] 'Record(s)',
a.total_pages*8 'Total Pages(KB)',
a.used_pages*8 'Used Pages(KB)',
((a.total_pages*8)- (a.used_pages*8)) 'Unused Pages(KB)',
a.type_desc
FROM sys.allocation_units A JOIN sys.partitions P
ON (A.container_id = P.hobt_id)
where Object_Name(p.[object_id]) in('Tb_SQLServer_VAR','Tb_SQLServer_NVAR')
Result:
Tb_SQLServer_VAR table has one VARCHAR data type column.
Tb_SQLServer_NVAR has one NVARCHAR data type column.
Both table have 1000 Records. But the Size allocated for table differs.
40 KB for VARCHAR data type table - Tb_SQLServer_VAR
56 KB for NVARCHAR data type table - Tb_SQLServer_NVAR
So, NVARCHAR data type occupies more spaces than the VARCHAR data types. Correct.
Identifying Plan/Optimizer activity on each Tables:
SET STATISTICS PROFILE,TIME,IO ON
SELECT Column1 FROM Tb_SQLServer_VAR
GO
SELECT Column1 FROM Tb_SQLServer_NVAR
SET STATISTICS PROFILE,TIME,IO OFF
Result:
Logical reads from both table differs.
6 Logical reads performed on NVARCHAR data type table.
4 Logical reads performed on VARCHAR data type table.
Conclusion:
NVARCHAR data types can be used only when really needed. Not for normal storage.
Grouping Concatenated data - XML PATH()
We trying to concatenate data based on Group using - XML PATH()
Creating Table(s) and Sample records:
USE SQLServerBuddy
Go
IF OBJECT_ID('Tb_DataTypeCategory','U') IS NOT NULL
DROP TABLE Tb_DataTypeCategory
GO
CREATE TABLE Tb_DataTypeCategory
(
CategoryId INT IDENTITY(1,1),
Category VARCHAR(50)
)
GO
INSERT Tb_DataTypeCategory(Category) VALUES('Approximate numerics')
INSERT Tb_DataTypeCategory(Category) VALUES('Binary strings')
INSERT Tb_DataTypeCategory(Category) VALUES('Character strings')
INSERT Tb_DataTypeCategory(Category) VALUES('Date and time')
INSERT Tb_DataTypeCategory(Category) VALUES('Exact numerics')
INSERT Tb_DataTypeCategory(Category) VALUES('Other data types')
INSERT Tb_DataTypeCategory(Category) VALUES('Unicode character strings')
GO
IF OBJECT_ID('Tb_DataType','U') IS NOT NULL
DROP TABLE Tb_DataType
GO
CREATE TABLE Tb_DataType
(
Id INT IDENTITY(1,1),
DataType VARCHAR(50),
CategoryId INT
)
GO
INSERT Tb_DataType(DataType,CategoryId) VALUES('FLOAT',1)
INSERT Tb_DataType(DataType,CategoryId) VALUES('REAL',1)
INSERT Tb_DataType(DataType,CategoryId) VALUES('BINARY',2)
INSERT Tb_DataType(DataType,CategoryId) VALUES('IMAGE',2)
INSERT Tb_DataType(DataType,CategoryId) VALUES('VARBINARY',2)
INSERT Tb_DataType(DataType,CategoryId) VALUES('CHAR',3)
INSERT Tb_DataType(DataType,CategoryId) VALUES('VARCHAR',3)
INSERT Tb_DataType(DataType,CategoryId) VALUES('TEXT',3)
INSERT Tb_DataType(DataType,CategoryId) VALUES('DATE',4)
INSERT Tb_DataType(DataType,CategoryId) VALUES('DATETIME',4)
INSERT Tb_DataType(DataType,CategoryId) VALUES('SMALLDATETIME',4)
INSERT Tb_DataType(DataType,CategoryId) VALUES('DATETIME2',4)
INSERT Tb_DataType(DataType,CategoryId) VALUES('DATETIMEOFFSET',4)
INSERT Tb_DataType(DataType,CategoryId) VALUES('TIME',4)
INSERT Tb_DataType(DataType,CategoryId) VALUES('BIGINT',5)
INSERT Tb_DataType(DataType,CategoryId) VALUES('BIT',5)
INSERT Tb_DataType(DataType,CategoryId) VALUES('DECIMAL',5)
INSERT Tb_DataType(DataType,CategoryId) VALUES('INT',5)
INSERT Tb_DataType(DataType,CategoryId) VALUES('MONEY',5)
INSERT Tb_DataType(DataType,CategoryId) VALUES('NUMERIC',5)
INSERT Tb_DataType(DataType,CategoryId) VALUES('SMALLINT',5)
INSERT Tb_DataType(DataType,CategoryId) VALUES('SMALLMONEY',5)
INSERT Tb_DataType(DataType,CategoryId) VALUES('TINYINT',5)
INSERT Tb_DataType(DataType,CategoryId) VALUES('CURSOR',6)
INSERT Tb_DataType(DataType,CategoryId) VALUES('TIMESTAMP',6)
INSERT Tb_DataType(DataType,CategoryId) VALUES('HIERARCHYID',6)
INSERT Tb_DataType(DataType,CategoryId) VALUES('SQL_VARIENT',6)
INSERT Tb_DataType(DataType,CategoryId) VALUES('TABLE',6)
INSERT Tb_DataType(DataType,CategoryId) VALUES('UNIQUEIDENTIFIER',6)
INSERT Tb_DataType(DataType,CategoryId) VALUES('XML',6)
INSERT Tb_DataType(DataType,CategoryId) VALUES('NCHAR',7)
INSERT Tb_DataType(DataType,CategoryId) VALUES('NVARCHAR',7)
INSERT Tb_DataType(DataType,CategoryId) VALUES('NTEXT',7)
Go
Actual Result:
Data Type should be concatenated and grouped based on Category
USE SQLServerBuddy
Go
SELECT Category,LEFT([Data Type(s)],LEN([Data Type(s)])-1) + ' (' + [Types] + ')' [Data Type(s)] FROM
(
SELECT dtc.Category,
(SELECT dt.DataType + ', ' FROM Tb_DataType dt(NOLOCK) WHERE dt.CategoryId = dtc.CategoryId FOR XML PATH('')) 'Data Type(s)',
(SELECT CAST(COUNT(1) AS VARCHAR) FROM Tb_DataType dt(NOLOCK) WHERE dt.CategoryId = dtc.CategoryId) 'Types'
FROM Tb_DataTypeCategory dtc(NOLOCK)
) AS Result
Go
Result:
Using PIVOT with Static and Dynamic records
We can use the PIVOT relational operators to change a table-valued expression into another table like ROW(s) to COLUMN(s).
UNPIVOT performs the opposite operation to PIVOT by rotating COLUMN(s) to ROW(s).
Creating Table(s) and Sample records:
IF OBJECT_ID('Tb_DataTypeCategory','U') IS NOT NULL
DROP TABLE Tb_DataTypeCategory
GO
CREATE TABLE Tb_DataTypeCategory
(
CategoryId INT IDENTITY(1,1),
Category VARCHAR(50)
)
GO
INSERT Tb_DataTypeCategory(Category) VALUES('Approximate numerics')
INSERT Tb_DataTypeCategory(Category) VALUES('Binary strings')
INSERT Tb_DataTypeCategory(Category) VALUES('Character strings')
INSERT Tb_DataTypeCategory(Category) VALUES('Date and time')
INSERT Tb_DataTypeCategory(Category) VALUES('Exact numerics')
INSERT Tb_DataTypeCategory(Category) VALUES('Other data types')
INSERT Tb_DataTypeCategory(Category) VALUES('Unicode character strings')
GO
IF OBJECT_ID('Tb_DataType','U') IS NOT NULL
DROP TABLE Tb_DataType
GO
CREATE TABLE Tb_DataType
(
Id INT IDENTITY(1,1),
DataType VARCHAR(50),
CategoryId INT
)
GO
INSERT Tb_DataType(DataType,CategoryId) VALUES('FLOAT',1)
INSERT Tb_DataType(DataType,CategoryId) VALUES('REAL',1)
INSERT Tb_DataType(DataType,CategoryId) VALUES('BINARY',2)
INSERT Tb_DataType(DataType,CategoryId) VALUES('IMAGE',2)
INSERT Tb_DataType(DataType,CategoryId) VALUES('VARBINARY',2)
INSERT Tb_DataType(DataType,CategoryId) VALUES('CHAR',3)
INSERT Tb_DataType(DataType,CategoryId) VALUES('VARCHAR',3)
INSERT Tb_DataType(DataType,CategoryId) VALUES('TEXT',3)
INSERT Tb_DataType(DataType,CategoryId) VALUES('DATE',4)
INSERT Tb_DataType(DataType,CategoryId) VALUES('DATETIME',4)
INSERT Tb_DataType(DataType,CategoryId) VALUES('SMALLDATETIME',4)
INSERT Tb_DataType(DataType,CategoryId) VALUES('DATETIME2',4)
INSERT Tb_DataType(DataType,CategoryId) VALUES('DATETIMEOFFSET',4)
INSERT Tb_DataType(DataType,CategoryId) VALUES('TIME',4)
INSERT Tb_DataType(DataType,CategoryId) VALUES('BIGINT',5)
INSERT Tb_DataType(DataType,CategoryId) VALUES('BIT',5)
INSERT Tb_DataType(DataType,CategoryId) VALUES('DECIMAL',5)
INSERT Tb_DataType(DataType,CategoryId) VALUES('INT',5)
INSERT Tb_DataType(DataType,CategoryId) VALUES('MONEY',5)
INSERT Tb_DataType(DataType,CategoryId) VALUES('NUMERIC',5)
INSERT Tb_DataType(DataType,CategoryId) VALUES('SMALLINT',5)
INSERT Tb_DataType(DataType,CategoryId) VALUES('SMALLMONEY',5)
INSERT Tb_DataType(DataType,CategoryId) VALUES('TINYINT',5)
INSERT Tb_DataType(DataType,CategoryId) VALUES('CURSOR',6)
INSERT Tb_DataType(DataType,CategoryId) VALUES('TIMESTAMP',6)
INSERT Tb_DataType(DataType,CategoryId) VALUES('HIERARCHYID',6)
INSERT Tb_DataType(DataType,CategoryId) VALUES('SQL_VARIENT',6)
INSERT Tb_DataType(DataType,CategoryId) VALUES('TABLE',6)
INSERT Tb_DataType(DataType,CategoryId) VALUES('UNIQUEIDENTIFIER',6)
INSERT Tb_DataType(DataType,CategoryId) VALUES('XML',6)
INSERT Tb_DataType(DataType,CategoryId) VALUES('NCHAR',7)
INSERT Tb_DataType(DataType,CategoryId) VALUES('NVARCHAR',7)
INSERT Tb_DataType(DataType,CategoryId) VALUES('NTEXT',7)
Go
Actual Result:
SELECT dtc.Category,dt.DataType FROM Tb_DataType dt(NOLOCK) JOIN Tb_DataTypeCategory dtc(NOLOCK) ON(dt.CategoryId = dtc.CategoryId) ORDER BY dtc.Category,dt.DataType
Go
Using PIVOT with Static Record(s)
SELECT
[Approximate numerics]
,[Binary strings]
,[Character strings]
,[Date and time]
,[Exact numerics]
,[Other data types]
,[Unicode character strings]
FROM
(
SELECT dtc.Category,dt.DataType FROM Tb_DataType dt(NOLOCK) JOIN Tb_DataTypeCategory dtc(NOLOCK)
ON(dt.CategoryId = dtc.CategoryId)
)AS ActualData
PIVOT
(
COUNT(DataType)
FOR Category IN([Approximate numerics],[Binary strings],[Character strings],[Date and time],[Exact numerics],[Other data types],[Unicode character strings])
)AS PIVOTData
Go
Result:
But, The result will be the same. Because we have hardcoded the Categories in this static query. Then How to overcome this loss of records.
We can move to Dynamic query.
Using PIVOT with Dynamic Record(s):
1. Concatenating the Categories into a Variable: @ChvCategories
DECLARE @ChvCategories VARCHAR(MAX) , @ChvnSql NVARCHAR(MAX)
SELECT @ChvCategories = '', @ChvnSql = N' '
SELECT @ChvCategories = COALESCE(@ChvCategories ,'') + '[' + Category + '],' FROM Tb_DataTypeCategory
SELECT @ChvCategories = LEFT(@ChvCategories,LEN(@ChvCategories)-1)
2. Using the Concatenated variable in Dynamic query
SELECT @ChvnSql = N'
SELECT ' + @ChvCategories + ' FROM
(
SELECT dtc.Category,dt.DataType FROM Tb_DataType dt(NOLOCK) JOIN Tb_DataTypeCategory dtc(NOLOCK)
ON(dt.CategoryId = dtc.CategoryId)
)AS ActualData
PIVOT
(
COUNT(DataType)
FOR Category IN(' + @ChvCategories + ')
)AS PIVOTData'
EXEC SP_EXECUTESQL @ChvnSql
Go
Result:
Now, The PIVOT results with added records. Because we have used Dynamic query with PIVOT.
Monitoring Tempdb Database Usage
Analyzing Tempdb usability by the various SQL server resources/activities. We can analyze these kind of monitoring activities using the following various ways
1. Performance Monitor
2. System Stored Procedures
3. DMVs
In this post, We going to proceed with Performance Monitor
Creating a Table:
USE SQLServerBuddy
Go
IF OBJECT_ID('Tb_Table1','U') IS NOT NULL
DROP TABLE Tb_Table1
Go
CREATE TABLE Tb_Table1
(
ID INT IDENTITY(1,1),
Column1 VARCHAR(100),
Column2 VARCHAR(100)
)
Go
Inserting Sample Records:
USE SQLServerBuddy
Go
INSERT Tb_Table1(Column1,Column2)
SELECT 'SQL Server Buddy','Analysing Tempdb Database File Growth'
GO 10000
The table Tb_Table1 has 10000 records.
Creating a Stored Procedure:
USE SQLServerBuddy
Go
IF OBJECT_ID('USP_FileGrowth','P') IS NOT NULL
DROP PROC USP_FileGrowth
Go
CREATE PROC USP_FileGrowth
AS
BEGIN
SET NOCOUNT ON
DECLARE @valTable1 TABLE(Id INT, Col1 VARCHAR(100), Col2 VARCHAR(100))
INSERT @valTable1
SELECT a.Id,a.column1,a.column2 FROM Tb_Table1 a, Tb_Table1 b
END
Go
Inside the procedure, We trying to insert N x M records into a Table variable. Now, we can identify that Table variables used Tempdb database resources.
Configuring Performance Monitor:
1. Start --> Run --> Enter PerfMon
2. Clear the all existing Counters as we going to proceed with Tempdb database usage counter.
3. Click (+) on Tool bar or Press Ctrl+D as given below.
4.Choose the Database Server name at drop down list.
5.Select Databases in Performance Object Drop down list.
6.Select Log File(s) Size(KB) in Select counters from list area.
7.Select Tempdb in Select Instance from list area.
8.Click Add button and Cancel Button.
9. Execute the Stored Procedure which we have created now.
USE SQLServerBuddy
Go
EXEC USP_FileGrowth
Go
10.Check the Performance Monitor, How is behaving for the Tempdb database usage.
Conclusion:
We can avoid the usage of Tempdb database for these kind of activities by avoiding Table variable(s).
SQL Server Coding Standards(Do's and Don'ts)
1.Variable should have combination of Datatype prefix and content.
DECLARE @Intage INT
2.Use User Defined Datatypes if a particular column repeats in multiple tables so that the datatype of that column is consistent across all our tables(UDT).
UDT_Intage
3.Use VARCHAR(Variying Length Type) instead of CHAR(Fixed Length Type)
4.Do not use column numbers in the ORDER BY clause.
Use
SELECT Column1, Column2, Column3 FROM Tb_Table1 ORDER BY Column2
Do not Use
SELECT Column1, Column2, Column3 FROM Tb_Table1 ORDER BY 2
5.SQL 92 Join should be used instead of SQL 89 Join.
Use - SQL 92
SELECT a.Column1,
a.Column2, b.Column3 FROM Tb_Table1 a JOIN Tb_Table2 B ON(a.Column1=b.Column1)
Do not Use - SQL 89
SELECT a.Column1, a.Column2, b.Column3 FROM Tb_Table1 a, Tb_Table2 B WHERE a.Column1=b.Column1
6.To make SQL statements more readable, start each CLAUSE on a new line and indent when needed.
SELECT Column1, Column2, Column3
FROM Tb_Table1
WHERE Column1=100
ORDER BY Column2
7.Unicode data type should not be used, only when they are absolutely needed(NCHAR,NVARCHAR)
8.Do not call functions repeatedly within your stored procedures, triggers, functions and batches.
9.Do not use 'SELECT *' in your queries. Always write the required column names in (Stored Procedures/Views/Normal Select)
Use
SELECT Column1, Column2, Column3 FROM Tb_Table1
Do not Use
SELECT * FROM Tb_Table1
10.LIKE Operator should not be used. Only when they are absolutely needed
Use
SELECT Column1, Column2, Column3 FROM Tb_Table1 WHERE Column3 = 'SQLBuddy'
Do not Use
SELECT Column1, Column2, Column3 FROM Tb_Table1 WHERE Column3 LIKE 'SQLBuddy'
11.Avoid searching using negative operators (<> and NOT) as they result in table and index scans.
Do not Use
SELECT Column1, Column2, Column3 FROM Tb_Table1 WHERE Column3 <> 'SQLBuddy'
12.Perform all your referential integrity checks and data validations using constraints (foreign key and check constraints) instead of TRIGGERS.
13.Try to avoid cursors as much as possible. Use WHILE LOOP instead
14. Always access tables in the same order in all your stored procedures and triggers consistently. This helps in avoiding deadlocks.
15.Use UPPER case for table, view & Keywords.
Use
SELECT Column1, Column2, Column3 FROM Tb_Table1 WHERE Column3 = 'SQLBuddy'
Do not Use
select Column1, Column2, Column3 from Tb_Table1 where Column3 = 'SQLBuddy'
16.A foreign key name should identify both tables participating in a foreign key, the column(s) involved in the relationship, and the direction.
17.Do not use alter procedure. Scripts should be structured so that procedures are dropped and recreated.
Use
IF OBJECT_ID('PROCEDURENAME','P') IS NOT NULL
DROP PROC PROCEDURENAME
GO
CREATE PROC PROCEDURENAME
...
...
Do not Use
ALTER PROC PROCEDURENAME
...
...
18.Use parenthesis to increase readability, especially when working with branch conditions or complicated expressions.
19.Use BEGIN…END blocks only when multiple statements are present within a conditional code segment.
Use
DECLARE @Variable INT
SELECT @Variable = 1
IF (@Variable = 1)
BEGIN
SELECT @Variable = 2
SELECT @Variable = @Variable + 2
SELECT @Variable = @Variable + 2
END
ELSE
SELECT @Variable = 10
20.When a result set is not needed, use the syntax.. IF EXISTS(......) Insteadof IF (SELECT COUNT())
Use
IF EXISTS(SELECT 1 FROM TABLE1)
PRINT 'Record Exists'
ELSE
PRINT 'Record Not Exists'
Do not Use
IF (SELECT COUNT(1) FROM TABLE1) >0
Do not Use
SELECT Col1, Col2, Col3 FROM Tb_Table1
UNION
SELECT Col1, Col2, Col3 FROM Tb_Table2
37.When you have a choice of using the IN or the BETWEEN clauses in your Transact-SQL, you will generally want to use the BETWEEN clause, as it is more efficient.
Use
SELECT Col1, Col2, Col3 FROM TABLE1WHERE BETWEEN 1 AND 5
Do not Use
SELECT Col1, Col2, Col3 FROM TABLE1WHERE ID IN(1,2,3,4,5)
38.Don’t turn ON the CLR unless you will be using it
39.Standard data access, such as SELECT, INSERTs, UPDATEs, and DELETEs are best done via Transact-SQL code, not the CLR
40.Use the CLR for error handling, as it is more robust than what Transact-SQL offers
41.Use the CLR for string manipulation, as it is generally faster than using Transact-SQL
42.Use the CLR when you want to access external resources, such as the file system, Event Log, a web service, or the registry
43.When possible, use typed XML data types over untyped XML data to boost performance
44.Add primary and secondary indexes to XML columns to speed retrieval of xml data
45.Schedule large data imports, exports, or transformation during less busy periods of the day to reduce the impact on users
46.Table names should accurately reflect the table's content and function.
47.View names follow the same conventions as table names, but should be prefixed with the literal 'VW' or 'VW_'
CREATE VIEW VW_VIEW1 AS SELECT a.Col1, a.Col2, a.Col3 FROM TABLE1 a(NOLOCK) JOIN TABLE1 b(NOLOCK) ON (a.ID = b.ID)
48.Each table should have PRIMARY KEY
49.Table name should be Prefixed with 'TB_' (or) 'TB'
50.Alias name should be used for Tables when using JOINs
Use
SELECT a.Col1, b.Col22, b.Col33 FROM Tb_Table1 a(NOLOCK) JOIN Tb_Table2
b(NOLOCK) ON (a.ID = b.IDs)
Do not Use
SELECT Col1, Col22, Col33 FROM Tb_Table1(NOLOCK) JOIN
Tb_Table2(NOLOCK) ON (ID = IDs)
51.Primary key have a Prefix of 'PK_'
52.Foreign keys have a Prefix of 'FK_'
53.Clustered indexes have a Prefix of 'IDX_'
54.All other indexes have a Prefix of 'NDX_'
55.All application level and user defined stored procedures are prefixed with the constant 'USP' (or) 'USP_' with a description of what the stored procedure does.
CREATE PROC USP_Proc1
(
@TableID INT
)AS
BEGIN
SET NOCOUNT ON
SELECT a.Col1, b.Col22, b.Col33 FROM Tb_Table1 a(NOLOCK) JOIN Tb_Table2 b(NOLOCK)ON (a.ID = b.IDs) WHERE a.ID = @TableID
END
Go
56.Stored procedures should not be started as 'SP_' as the system stored procedures used in Master Database.
57.Use SET NOCOUNT ON at the beginning of your SQL batches, stored procedures and triggers
58.Do not use alter procedure. Scripts should be structured so that procedures are dropped and recreated.
59.Do not use temporary stored procedures (Local / Global)
CREATE PROC #USP_Proc1
AS
BEGIN
SET NOCOUNT ON
…
END
Go
CREATE PROC ##USP_Proc1
AS
BEGIN
SET NOCOUNT ON
…
END
Go
60.Do not create stored procedures that return multiple result sets (if realy required)
CREATE PROC USP_Proc1
AS
BEGIN
SET NOCOUNT ON
SELECT Col1, Col2, Col3 FROM TABLE1
SELECT Col1, Col2, Col3 FROM TABLE2
END
61.Never use the recompile option(Procedure / Statement level) as the procedure recompile every time.CREATE PROC USP_Proc1
WITH RECOMPILE
AS
BEGIN
SET NOCOUNT ON
SELECT Col1 FROM TABLE1
END
CREATE PROC USP_Proc1
AS
BEGIN
SET NOCOUNT ON
SELECT Col1 FROM TABLE1 OPTION(RECOMPILE)
END
62.Fully qualify all stored procedure and table references in all stored procedures
USE DatabaseName
Go
IF OBJECT_ID('.','P') IS NOT NULL
DROP PROC...
GO
CREATE PROC
....
(
Input Parameter,
Output Parameter,
@ErrorLog BIT = 0
)AS
BEGIN
/********************Modification History**********************/
/* Created By : Pandian S */
/* Created On : 23-July-2010, Fri */
DECLARE @Intage INT
2.Use User Defined Datatypes if a particular column repeats in multiple tables so that the datatype of that column is consistent across all our tables(UDT).
UDT_Intage
3.Use VARCHAR(Variying Length Type) instead of CHAR(Fixed Length Type)
4.Do not use column numbers in the ORDER BY clause.
Use
SELECT Column1, Column2, Column3 FROM Tb_Table1 ORDER BY Column2
Do not Use
SELECT Column1, Column2, Column3 FROM Tb_Table1 ORDER BY 2
5.SQL 92 Join should be used instead of SQL 89 Join.
Use - SQL 92
SELECT a.Column1,
a.Column2, b.Column3 FROM Tb_Table1 a JOIN Tb_Table2 B ON(a.Column1=b.Column1)
Do not Use - SQL 89
SELECT a.Column1, a.Column2, b.Column3 FROM Tb_Table1 a, Tb_Table2 B WHERE a.Column1=b.Column1
6.To make SQL statements more readable, start each CLAUSE on a new line and indent when needed.
SELECT Column1, Column2, Column3
FROM Tb_Table1
WHERE Column1=100
ORDER BY Column2
7.Unicode data type should not be used, only when they are absolutely needed(NCHAR,NVARCHAR)
8.Do not call functions repeatedly within your stored procedures, triggers, functions and batches.
9.Do not use 'SELECT *' in your queries. Always write the required column names in (Stored Procedures/Views/Normal Select)
Use
SELECT Column1, Column2, Column3 FROM Tb_Table1
Do not Use
SELECT * FROM Tb_Table1
10.LIKE Operator should not be used. Only when they are absolutely needed
Use
SELECT Column1, Column2, Column3 FROM Tb_Table1 WHERE Column3 = 'SQLBuddy'
Do not Use
SELECT Column1, Column2, Column3 FROM Tb_Table1 WHERE Column3 LIKE 'SQLBuddy'
11.Avoid searching using negative operators (<> and NOT) as they result in table and index scans.
Do not Use
SELECT Column1, Column2, Column3 FROM Tb_Table1 WHERE Column3 <> 'SQLBuddy'
12.Perform all your referential integrity checks and data validations using constraints (foreign key and check constraints) instead of TRIGGERS.
13.Try to avoid cursors as much as possible. Use WHILE LOOP instead
14. Always access tables in the same order in all your stored procedures and triggers consistently. This helps in avoiding deadlocks.
15.Use UPPER case for table, view & Keywords.
Use
SELECT Column1, Column2, Column3 FROM Tb_Table1 WHERE Column3 = 'SQLBuddy'
Do not Use
select Column1, Column2, Column3 from Tb_Table1 where Column3 = 'SQLBuddy'
16.A foreign key name should identify both tables participating in a foreign key, the column(s) involved in the relationship, and the direction.
17.Do not use alter procedure. Scripts should be structured so that procedures are dropped and recreated.
Use
IF OBJECT_ID('PROCEDURENAME','P') IS NOT NULL
DROP PROC PROCEDURENAME
GO
CREATE PROC PROCEDURENAME
...
...
Do not Use
ALTER PROC PROCEDURENAME
...
...
18.Use parenthesis to increase readability, especially when working with branch conditions or complicated expressions.
19.Use BEGIN…END blocks only when multiple statements are present within a conditional code segment.
Use
DECLARE @Variable INT
SELECT @Variable = 1
IF (@Variable = 1)
BEGIN
SELECT @Variable = 2
SELECT @Variable = @Variable + 2
SELECT @Variable = @Variable + 2
END
ELSE
SELECT @Variable = 10
20.When a result set is not needed, use the syntax.. IF EXISTS(......) Insteadof IF (SELECT COUNT())
Use
IF EXISTS(SELECT 1 FROM TABLE1)
PRINT 'Record Exists'
ELSE
PRINT 'Record Not Exists'
Do not Use
IF (SELECT COUNT(1) FROM TABLE1) >0
PRINT 'Record Exists'
ELSE
PRINT 'Record Not Exists'
21.Always list column names within an insert statement. Never perform inserts based on column position alone.
Use
INSERT TABLE1(Col1, Col2, Col3) VALUES ('A','B','C')
Do not Use
INSERT TABLE1 VALUES ('A','B','C')
22.Do not call a stored procedure during an insert.(Inserting record(s) based on the resultset of a stored procedure)
Do not Use
CREATE PROC USP_Proc1
(
@ID INT
)AS
BEGIN
SET NOCOUNT ON
IF (@ID <0)
SELECT 'Negative'
ELSE
SELECT 'Possitive'
END
Go
INSERT TABLE1(Col1)
EXEC USP_Proc1 -1
23.Avoid using Temp / derived tables, Use only CTE (Based on the statement Scope) (or) use table variables instead for single SELECT operations.
24.Avoid using table variables when JOIN with physical tables. Use Temp tables instead.
25.PRIMARY KEY column should be in WHERE clause of UPDATE/DELETE operations.
26.Avoid using SPACE or Special Characters except '_' in Database, Objects and Column names.
27.Do not use RESERVED words on Objects/columns.
28.Frequently required / complicated JOINs should be used inside the VIEWs.
29.Use NOLOCK table optimizer hint.
SELECT a.Column1, a.Column2, b.Column3 FROM Tb_Table1 a(NOLOCK) JOIN Tb_Table2 B(NOLOCK) ON(a.Column1=b.Column1)
30.Do not depend on undocumented functionality (May be Obsolated in next release)
31.Try not to use system tables directly. System table structures may change in a future release or Obsolete.
32.Try to avoid wildcard characters at the beginning of a word while searching using the LIKE keyword, as that results in an index scan, which is defeating the purpose of having an index.
Use
ColumnName LIKE 'p%n'
Do not Use
ColumnName LIKE 'p%'
33.Be aware that the old style *= and =* left and right outer join syntax should be avoided even(It will make compatibility issue)
34.Always store 4 digit years in dates (especially, when using char or int datatype columns)
Use
DECLARE @IntDate INT
SELECT @IntDate = 2010
Do not Use
DECLARE @IntDate INT
SELECT @IntDate = 23
35.In queries and other SQL statements, always represent date in yyyy/mm/dd format. This format will always be interpreted correctly.
Use
DECLARE @Date DATETIME
SELECT @Date = '2010-07-23'
36.Do not use GO or use it Carefully
Do not Use
DECLARE @Date DATETIME
SELECT @Date = '2010-07-23'
GO
SELECT @Date = @Date + 1
36.Use UNION ALL insteadof UNION if possible(we can avoide DISTINCT and Worktables)
Use
SELECT Col1, Col2, Col3 FROM Tb_Table1
UNION ALL
SELECT Col1, Col2, Col3 FROM Tb_Table2
ELSE
PRINT 'Record Not Exists'
21.Always list column names within an insert statement. Never perform inserts based on column position alone.
Use
INSERT TABLE1(Col1, Col2, Col3) VALUES ('A','B','C')
Do not Use
INSERT TABLE1 VALUES ('A','B','C')
22.Do not call a stored procedure during an insert.(Inserting record(s) based on the resultset of a stored procedure)
Do not Use
CREATE PROC USP_Proc1
(
@ID INT
)AS
BEGIN
SET NOCOUNT ON
IF (@ID <0)
SELECT 'Negative'
ELSE
SELECT 'Possitive'
END
Go
INSERT TABLE1(Col1)
EXEC USP_Proc1 -1
23.Avoid using Temp / derived tables, Use only CTE (Based on the statement Scope) (or) use table variables instead for single SELECT operations.
24.Avoid using table variables when JOIN with physical tables. Use Temp tables instead.
25.PRIMARY KEY column should be in WHERE clause of UPDATE/DELETE operations.
26.Avoid using SPACE or Special Characters except '_' in Database, Objects and Column names.
27.Do not use RESERVED words on Objects/columns.
28.Frequently required / complicated JOINs should be used inside the VIEWs.
29.Use NOLOCK table optimizer hint.
SELECT a.Column1, a.Column2, b.Column3 FROM Tb_Table1 a(NOLOCK) JOIN Tb_Table2 B(NOLOCK) ON(a.Column1=b.Column1)
30.Do not depend on undocumented functionality (May be Obsolated in next release)
31.Try not to use system tables directly. System table structures may change in a future release or Obsolete.
32.Try to avoid wildcard characters at the beginning of a word while searching using the LIKE keyword, as that results in an index scan, which is defeating the purpose of having an index.
Use
ColumnName LIKE 'p%n'
Do not Use
ColumnName LIKE 'p%'
33.Be aware that the old style *= and =* left and right outer join syntax should be avoided even(It will make compatibility issue)
34.Always store 4 digit years in dates (especially, when using char or int datatype columns)
Use
DECLARE @IntDate INT
SELECT @IntDate = 2010
Do not Use
DECLARE @IntDate INT
SELECT @IntDate = 23
35.In queries and other SQL statements, always represent date in yyyy/mm/dd format. This format will always be interpreted correctly.
Use
DECLARE @Date DATETIME
SELECT @Date = '2010-07-23'
36.Do not use GO or use it Carefully
Do not Use
DECLARE @Date DATETIME
SELECT @Date = '2010-07-23'
GO
SELECT @Date = @Date + 1
36.Use UNION ALL insteadof UNION if possible(we can avoide DISTINCT and Worktables)
Use
SELECT Col1, Col2, Col3 FROM Tb_Table1
UNION ALL
SELECT Col1, Col2, Col3 FROM Tb_Table2
Do not Use
SELECT Col1, Col2, Col3 FROM Tb_Table1
UNION
SELECT Col1, Col2, Col3 FROM Tb_Table2
37.When you have a choice of using the IN or the BETWEEN clauses in your Transact-SQL, you will generally want to use the BETWEEN clause, as it is more efficient.
Use
SELECT Col1, Col2, Col3 FROM TABLE1WHERE BETWEEN 1 AND 5
Do not Use
SELECT Col1, Col2, Col3 FROM TABLE1WHERE ID IN(1,2,3,4,5)
38.Don’t turn ON the CLR unless you will be using it
39.Standard data access, such as SELECT, INSERTs, UPDATEs, and DELETEs are best done via Transact-SQL code, not the CLR
40.Use the CLR for error handling, as it is more robust than what Transact-SQL offers
41.Use the CLR for string manipulation, as it is generally faster than using Transact-SQL
42.Use the CLR when you want to access external resources, such as the file system, Event Log, a web service, or the registry
43.When possible, use typed XML data types over untyped XML data to boost performance
44.Add primary and secondary indexes to XML columns to speed retrieval of xml data
45.Schedule large data imports, exports, or transformation during less busy periods of the day to reduce the impact on users
46.Table names should accurately reflect the table's content and function.
47.View names follow the same conventions as table names, but should be prefixed with the literal 'VW' or 'VW_'
CREATE VIEW VW_VIEW1 AS SELECT a.Col1, a.Col2, a.Col3 FROM TABLE1 a(NOLOCK) JOIN TABLE1 b(NOLOCK) ON (a.ID = b.ID)
48.Each table should have PRIMARY KEY
49.Table name should be Prefixed with 'TB_' (or) 'TB'
50.Alias name should be used for Tables when using JOINs
Use
SELECT a.Col1, b.Col22, b.Col33 FROM Tb_Table1 a(NOLOCK) JOIN Tb_Table2
b(NOLOCK) ON (a.ID = b.IDs)
Do not Use
SELECT Col1, Col22, Col33 FROM Tb_Table1(NOLOCK) JOIN
Tb_Table2(NOLOCK) ON (ID = IDs)
51.Primary key have a Prefix of 'PK_'
52.Foreign keys have a Prefix of 'FK_'
53.Clustered indexes have a Prefix of 'IDX_'
54.All other indexes have a Prefix of 'NDX_'
55.All application level and user defined stored procedures are prefixed with the constant 'USP' (or) 'USP_' with a description of what the stored procedure does.
CREATE PROC USP_Proc1
(
@TableID INT
)AS
BEGIN
SET NOCOUNT ON
SELECT a.Col1, b.Col22, b.Col33 FROM Tb_Table1 a(NOLOCK) JOIN Tb_Table2 b(NOLOCK)ON (a.ID = b.IDs) WHERE a.ID = @TableID
END
Go
56.Stored procedures should not be started as 'SP_' as the system stored procedures used in Master Database.
57.Use SET NOCOUNT ON at the beginning of your SQL batches, stored procedures and triggers
58.Do not use alter procedure. Scripts should be structured so that procedures are dropped and recreated.
59.Do not use temporary stored procedures (Local / Global)
CREATE PROC #USP_Proc1
AS
BEGIN
SET NOCOUNT ON
…
END
Go
CREATE PROC ##USP_Proc1
AS
BEGIN
SET NOCOUNT ON
…
END
Go
60.Do not create stored procedures that return multiple result sets (if realy required)
CREATE PROC USP_Proc1
AS
BEGIN
SET NOCOUNT ON
SELECT Col1, Col2, Col3 FROM TABLE1
SELECT Col1, Col2, Col3 FROM TABLE2
END
61.Never use the recompile option(Procedure / Statement level) as the procedure recompile every time.
WITH RECOMPILE
AS
BEGIN
SET NOCOUNT ON
SELECT Col1 FROM TABLE1
END
CREATE PROC USP_Proc1
AS
BEGIN
SET NOCOUNT ON
SELECT Col1 FROM TABLE1 OPTION(RECOMPILE)
END
62.Fully qualify all stored procedure and table references in all stored procedures
USE DatabaseName
Go
IF OBJECT_ID('
DROP PROC
GO
CREATE PROC
(
Input Parameter,
Output Parameter,
@ErrorLog BIT = 0
)AS
BEGIN
/********************Modification History**********************/
/* Created By : Pandian S */
/* Created On : 23-July-2010, Fri */
/* Tables Used : TB_Table1, TB_Table2,... */
/* DB Used : DB01, DB2,... */
/* Description : Template of Stored Procedure */
/*********************************************************/
SET NOCOUNT ON
BEGIN TRY
BEGIN TRAN
COMMIT
END TRY
BEGIN CATCH
IF (@ErrorLog=1)
BEGIN
DECLARE @ErrorMessage NVARCHAR(100)
SELECT @ErrorMessage = 'Error Line # :' + CAST(ERROR_LINE() AS NVARCHAR) + ', ' + ERROR_MESSAGE()
EXEC XP_LOGEVENT 60001,@ErrorMessage,'Error'
END
ROLLBACK TRAN
END CATCH
END
Go
63.Place all declare statements before any other code in the procedure to give the query optimizer the best shot at reusing query plans.
64.Place SELECT statements before any executing code in the procedure. (variable Assigning operations)
65.Always create stored procedures in same Database.
66.Write comments in your stored procedures, triggers and SQL batches generously. Don't worry about the length of the comments, as it won't impact the performance.
67.SET statements should not be used (Except NOCOUNT, ROWCOUNT) inside the Procedures. Use SELECT instead.
i.e: SET is an environment setting(Session specific) statement, We can not use multiple assignments on single line(We have to assign each value in seperate line), We can not assign a value directly from a table(without SELECT clause)
DBA Recommendations :
68.Monitor disk space to ensure your SQL Servers won’t run out of disk space.
69.Throughout the day, periodically monitor performance using System Monitor, Profiler, Activity monitor, Client statistics and DMVs.
70.Keep a log of any changes you make to servers, including documentation of any performance issues you identify and correct.
71.Create SQL Server alerts to notify you of potential problems, and have them emailed to you. Take actions as needed
72.If you upgrade from a previous version of SQL Server, you should update all of the statistics in all your databases. (Because statistics are not automatically updated during the upgrade process)
73.When possible, use Windows Authentication logins instead of SQL Server logins.
74.Don’t grant permissions to the public database role.
75.When using distributed queries, use linked servers instead of remote servers.
76.At least every two weeks, Check all the databases and data integrity.
77.Avoid running most DBCC commands during busy times of the day. These commands are often I/O intensive and can reduce performance of the SQL Server, negatively affecting users
78.In almost all cases, leave the "auto create statistics" and "auto update statistics" options on for all user databases.
79.Databases need to be shrunk periodically in order to free up disk space as older data is deleted from the database. But don’t be tempted to use the "auto shrink" database option, as it can waste SQL Server resources unnecessarily. Instead, SHRINK databases manually.
80.Regularly monitor your SQL Servers for BLOCKED transactions
81.Regularly monitor system performance.
82.Periodically, run the DTA against current Load File to identify potentially missing indexes and Remove indexes that are never used
83.Don’t REINDEX your tables when your database is in active production, as it can lock resources and cause your users performance problems
84.REINDEXing should be scheduled during down times, or during light use of the databases
85.Don’t accidentally create redundant indexes.
86.If possible, avoid adding a clustered index to a GUID column (uniqueidentifier data type). GUIDs take up 16-bytes of storage, more than an Identify column, which makes the index larger, which increases I/O reads, which can hurt performance.
87.Indexes should be considered on all columns that are frequently accessed by the JOIN, WHERE, ORDER BY, GROUP BY, TOP, and DISTINCT clauses.
88.A high fill factor is good for rarely changed data, but highly modified data needs a lower fill factor to reduce page splitting. So we can avoid unwanted fragmentation on Tables/Indexes.
89.Use covering index, which is a form of a composite index, includes all of the columns referenced in SELECT, JOIN, and WHERE clauses of a query. It will reduce the Physical and Logical I/O
90.Periodically, Check the Fragmentation and take action accordingly.
/* DB Used : DB01, DB2,... */
/* Description : Template of Stored Procedure */
/*********************************************************/
SET NOCOUNT ON
BEGIN TRY
BEGIN TRAN
COMMIT
END TRY
BEGIN CATCH
IF (@ErrorLog=1)
BEGIN
DECLARE @ErrorMessage NVARCHAR(100)
SELECT @ErrorMessage = 'Error Line # :' + CAST(ERROR_LINE() AS NVARCHAR) + ', ' + ERROR_MESSAGE()
EXEC XP_LOGEVENT 60001,@ErrorMessage,'Error'
END
ROLLBACK TRAN
END CATCH
END
Go
63.Place all declare statements before any other code in the procedure to give the query optimizer the best shot at reusing query plans.
64.Place SELECT statements before any executing code in the procedure. (variable Assigning operations)
65.Always create stored procedures in same Database.
66.Write comments in your stored procedures, triggers and SQL batches generously. Don't worry about the length of the comments, as it won't impact the performance.
67.SET statements should not be used (Except NOCOUNT, ROWCOUNT) inside the Procedures. Use SELECT instead.
i.e: SET is an environment setting(Session specific) statement, We can not use multiple assignments on single line(We have to assign each value in seperate line), We can not assign a value directly from a table(without SELECT clause)
DBA Recommendations :
68.Monitor disk space to ensure your SQL Servers won’t run out of disk space.
69.Throughout the day, periodically monitor performance using System Monitor, Profiler, Activity monitor, Client statistics and DMVs.
70.Keep a log of any changes you make to servers, including documentation of any performance issues you identify and correct.
71.Create SQL Server alerts to notify you of potential problems, and have them emailed to you. Take actions as needed
72.If you upgrade from a previous version of SQL Server, you should update all of the statistics in all your databases. (Because statistics are not automatically updated during the upgrade process)
73.When possible, use Windows Authentication logins instead of SQL Server logins.
74.Don’t grant permissions to the public database role.
75.When using distributed queries, use linked servers instead of remote servers.
76.At least every two weeks, Check all the databases and data integrity.
77.Avoid running most DBCC commands during busy times of the day. These commands are often I/O intensive and can reduce performance of the SQL Server, negatively affecting users
78.In almost all cases, leave the "auto create statistics" and "auto update statistics" options on for all user databases.
79.Databases need to be shrunk periodically in order to free up disk space as older data is deleted from the database. But don’t be tempted to use the "auto shrink" database option, as it can waste SQL Server resources unnecessarily. Instead, SHRINK databases manually.
80.Regularly monitor your SQL Servers for BLOCKED transactions
81.Regularly monitor system performance.
82.Periodically, run the DTA against current Load File to identify potentially missing indexes and Remove indexes that are never used
83.Don’t REINDEX your tables when your database is in active production, as it can lock resources and cause your users performance problems
84.REINDEXing should be scheduled during down times, or during light use of the databases
85.Don’t accidentally create redundant indexes.
86.If possible, avoid adding a clustered index to a GUID column (uniqueidentifier data type). GUIDs take up 16-bytes of storage, more than an Identify column, which makes the index larger, which increases I/O reads, which can hurt performance.
87.Indexes should be considered on all columns that are frequently accessed by the JOIN, WHERE, ORDER BY, GROUP BY, TOP, and DISTINCT clauses.
88.A high fill factor is good for rarely changed data, but highly modified data needs a lower fill factor to reduce page splitting. So we can avoid unwanted fragmentation on Tables/Indexes.
89.Use covering index, which is a form of a composite index, includes all of the columns referenced in SELECT, JOIN, and WHERE clauses of a query. It will reduce the Physical and Logical I/O
90.Periodically, Check the Fragmentation and take action accordingly.
Validating Database Backup file
Validating the backup file whether accessible/restorable/actual space needed for restoring the backup.
Basically, The script reads and returns Data and Log information from the Backup file.
Use Master
Go
Restore FileListOnly From Disk='D:\Backup\SQLServerBuddy_20100722.Bak'
Whenever we take a database backup it should be validated either the backup file is Restorable/accessible/Not.
The statement returns two rows for data and log file with actual size without any Err, So, the backup file is valid, accessible and restorable.
If the statement returns any Err other than the Data and Log file information then, we should go for some other backup.
Note:
Backup file should be there in local system/server where we using the script given above.
Linked Server Limitations
The following are the Linked Server limitations:
1. Login mappings have to exist along all paths it's using (Servers)
2. Amount of data coming from the other server, resulting in some very bad query plans (Query plan may not be Re-Used effectively)
3. Context Switching inside the Stored Procedure will cause problem.
4. XML type parameter cannot be used with Linked Server.
5. Table-valued parameters cannot be used.
6. CLR User-defined data type cannot be used.
7. Cannot call table-valued functions with four-part notation.
8. BULK INSERT statement into linked server is not supported.
9. Save Transaction Cannot be applied during distributed transaction.
10. Case statement won't work properly.
11. There is a chance to exceed the “remote query timeout (s)”
Note:
The following are the two main system stored procedures to implement Linked Server
1. sp_addlinkedserver - Used to define the linked server name to access an OLEDB datasources.
2. sp_addlinkedsrvlogin - Local SQL Server logins are mapped to logins in the linked server.
1. Login mappings have to exist along all paths it's using (Servers)
2. Amount of data coming from the other server, resulting in some very bad query plans (Query plan may not be Re-Used effectively)
3. Context Switching inside the Stored Procedure will cause problem.
4. XML type parameter cannot be used with Linked Server.
5. Table-valued parameters cannot be used.
6. CLR User-defined data type cannot be used.
7. Cannot call table-valued functions with four-part notation.
8. BULK INSERT statement into linked server is not supported.
9. Save Transaction Cannot be applied during distributed transaction.
10. Case statement won't work properly.
11. There is a chance to exceed the “remote query timeout (s)”
Note:
The following are the two main system stored procedures to implement Linked Server
1. sp_addlinkedserver - Used to define the linked server name to access an OLEDB datasources.
2. sp_addlinkedsrvlogin - Local SQL Server logins are mapped to logins in the linked server.
Subscribe to:
Comments (Atom)