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