Upgrade SQL 2005 to SQL 2008

Here, I am going to upgrade SQL Server 2005 Standard Edition to SQL Server 2008 Standard Edition.

What are the Version and Edition supports when upgrade - Go to Bottom of this post.

SQL Server Upgrade Advisor helps us prepare for upgrades to SQL Server 2008. Upgrade Advisor analyzes installed components from earlier versions of SQL Server, and then generates a report that identifies issues to fix either before or after we upgrade.

1. Take a Backup of System Databases and User Defined Databases.

2. Generate Scripts for Replications, Right Click on Replication --> Generate Scripts

3. Make a list of Linked Servers on the Existing SQL Servers.

4. Start the SQL Server, SQL Server Agent, SQL Server Analysis Services, SQL Server Full Text Search, SQL Server Reporting Services which you going to Upgrade(SQL Server 2005)

5. Step Into SQL Server 2008 Setup --> Setup.exe -->

6. Now you will have SQL Server Installation Center screen as given below

 
 
 
 
 
 
 
 
 
 
 
 
 
 
7. Click the Install Upgrade Advisor, So we can easily identify what are all the prerequisites, Warnings and Errors.

8. Once you completed the Installation of Upgrade Advisor.





9. Click the SQL Server 2008 Upgrade Advisor as given above.

10. It will open a window named Microsoft SQL Server 2008 Upgrade Advisor and Click the link as given below
 


11. It will open a wizard and click Next as given below
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 


12. SQL Server components window shows that what the components to be upgraded. In this case we could not select Notification Services. Because, Notification Service is deprecated in SQL Server 2008. So Un-check the checkbox for Notification Services.

13. Just Click the Detect button to fetch the Server name. (So, we can select which Server want to Upgrade)


 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
14. Click the Next Button as given above.

15. It will show Connection Parameters wizard, In that wizard, Select the Instance Name which you want to Upgrade and Provide the Authentication and Credentials as given below.


 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
16. Select the button next. SQL Server Parameters wizard appears, in that wizard, select the all Databases which you want to analyze as given below.
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 

17. Click the Button Next.

18. It will prompt Reporting Service Instance and Analysis Service Instance; select the Instance name for each.

19. It will prompt DTS Parameters. Just select and Click Next button



20. Click the option for SSIS Parameters. Just select and Click Next button.
 


21. Click the Run button on Confirm Upgrade Advisor Settings wizard.

22. Once the analysis completed. It shows the Analysis status wizard as given below.


 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
23. Once completed the analysis. Click the Launch Report button.
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
24. In this wizard, Select which service you want to see the Errors / Warnings as given below.
 
 
 
 
 
 
 
 
 
 
 
 
 
 
25. Once the Warnings / Errors fixed, Click the Back button and Click the Run button until the all services success.

26. Once the Upgrade Advisor completed successfully.

27. Run the Setup.exe file.

28. Click the link Upgrade from SQL Server 2000 or SQL Server 2005 under Installation as given below


 
 
 
 
 
 
 
 


29. The Upgrade process started.
 
 
 
 
 
 
30. It passes the all Setup support rules.














31. Click the OK button

32. Click Install button.

33. Click the Next button as given below


 
 
 
 
 
 
 
 
 
 
 
 
34. Enter the Product Key and Click Next Button.

35. Select the I accept the license terms check box and Click Next button

36. It will browse the all available instances on the server, Select the Instance to upgrade as given below


 
 
 
 
 
 
 
 
 
 
 
 
37. Click the Next button on Select Instance wizard.

38. Click the Next button on Select Features wizard.

39. Click the Next button on Instance Configuration wizard.

40. Click the Next button on Disk Space Requirements wizard.

41. Click the Next button on Server Configuration wizard.

42. Click the Next button on Full-text Upgrade wizard.

43. Click the Next button on Reporting Services Authentication (Windows / Mixed Mode) wizard.

44. Click the Next button on Error and Usage Reporting wizard.

45. If the Up gradation Rule is not matching with the Edition and Version then, It will show an Error as given below
 
 
 



46. Otherwise, Click the Next button on Upgrade Rules wizard.
 
47. Ready to Upgrade wizard appears as given below
 
 
 
 
 
 
 
 
 
 
 
 


48. Click the Button Upgrade on Ready to upgrade wizard.

49. Once the Upgrade Process completed, Click the Next button as given below

 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
50. Once the Complete process completed the screen looks like as given below

 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
51. Complete upgrade activity report available as a link on local machine.

52. When you select the Link the text file looks like as below


 
 
 
 
 
 
 
 
 
53. Click the Close button.

54. I try to connect the Instance SQL2005 using SQL Server 2005 Standard Edition, But It is throwing an Error as given below

 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
55. Because, we have upgraded the SQL2005 instance to SQL Server 2008 Standard.

56. Now, I try to connect the Instance SQL2005 using SQL Server 2008 Standard Edition, I can able to connect it.

57. Finally, I examined the Upgraded server. All the Databases, Login Credentials, Linked Servers, Replications are accessible in SQL Server 2008 Standard Edition now as given below


 
 
 
 
 
 
 
 
 
 
 
 
 
 


58. The instance name is same as SQL2005, but the edition is SQL Server 2008 Standard Edition.
 
Version and Edition Upgrade Path
http://msdn.microsoft.com/en-us/library/ms143393(v=SQL.100).aspx

Tips: Server Permission Needed

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

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:
 
 
 
 
 
 
 
 
 
 
 
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.

NARCHAR data type table performed more I/O and SubTreeCost than the 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:
I am adding one more Category and Data Types in Tb_DataTypeCategory, Tb_DataType respectively.

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:
I am adding one more Category and Data Types in Tb_DataTypeCategory, Tb_DataType respectively.

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

   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


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 */
/* 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.