Monday, April 30, 2012

Capture Deprecated SQL Server code with SQL Profiler

While migrating your application from one version of SQL Server to another have you ever wondered how to identify the deprecated features in the new version? Manually going through hundreds of scripts is going to be tedious and time consuming. I am a lazy coder myself and would be interested only in an automated solution :)

Until SQL Server 2005 we had only one way to identify it that is by making use of SQL Server Profiler. From SQL Server 2008 onwards we can make use of Extended Events as well.

In this post lets see how to make use of SQL Server Profiler to identify the deprecated SQL Server code.

Step 1: Open a SQL Server Instance and find out the session ID by executing the following script. This would come handy in SQL Profiler to filter out only information coming from this session.

SELECT @@SPID



Step 2: Open your SQL Server Profiler.


Click on "Event Selections" tab and choose "Deprecation" event.


Deprecation Announcement: Occurs when you use a feature that will be removed from future version of SQL Server, but will NOT be removed from the next major release of SQL Server.

Deprecation Final Support: Occurs when you use a feature that will be removed from the next major release of SQL Server.


Next click on "Column Filters" and set SPID filter to 61 (or what ever number you got in Step 1)


Step 3: Now lets go back to the window where we ran SELECT @@SPID and create some sample scripts which contains deprecated commands in it.

/*
Some random scripts to demonstrate how deprecated code can be captured using SQL Server Profiler
*/

SET NOCOUNT ON;

--Here TEXT, IMAGE are deprecated
CREATE TABLE tblDeprecationLocator
(
Sno INT,
Remarks TEXT,
Profilepic IMAGE
)
GO

--This is deprecated. Better alternative is to use ALTER INDEX
DBCC DBREINDEX('tblDeprecationLocator')
GO

--Using hints without WITH keyword is deprecated. It should be WITH (NOLOCK)
SELECT * FROM tblDeprecationLocator (NOLOCK)
GO

USE Master
GO
--here Truncate_only option is deprecated
BACKUP TRAN TestBed WITH TRUNCATE_ONLY
GO

Now when we execute these scripts SQL Profiler would capture all the deprecated code and provide appropriate message as shown below.

1. The TEXT, NTEXT, and IMAGE data types will be removed in a future version of SQL Server. Avoid using them in new development work, and plan to modify applications that currently use them. Use the varchar(max), nvarchar(max), and varbinary(max) data types instead.


2. DBCC DBREINDEX will be removed in a future version of SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use it. Use ALTER INDEX instead.


3. Specifying table hints without using a WITH keyword is a deprecated feature and will be removed in a future version.


4. BACKUP LOG WITH TRUNCATE_ONLY or WITH NO_LOG is deprecated. The simple recovery model should be used to automatically truncate the transaction log.


In the next post we would see how to make use of Extended Events in SQL Server 2008.

Conclusion: 

For greatest longevity of your applications, avoid using features that cause the deprecation announcement event class (or) the deprecation final support event class.

Monday, April 09, 2012

Clustered Index on an UniqueIdentifier column is costly

Generally having a clustered index on a UniqueIdentifier column is going to be costly. Even if we add some 5000 or 10000 records in the table the fragmentation level would be surely around 95+ % which means we have to very frequently REORGANIZE or REBUILD that index. So always i would suggest lets double check and be very sure that we need a GUID column and not an INT or BIGINT column.

Fragmentation Level would be too heavy

Lets create a sample to see how data is getting fragmented for different datatypes.


-----------------------------------------------------
--Demo Table Creation Scripts
-----------------------------------------------------

--Table with UniqueIdentifier as Clusetered Index
CREATE TABLE dbo.TblUID
(
Sno Uniqueidentifier NOT NULL DEFAULT NEWID(),
FirstName VARCHAR(100) NOT NULL,
DOB DATETIME NOT NULL,
CONSTRAINT pk_tblUid PRIMARY KEY CLUSTERED(sno asc)
);

--Table with UniqueIdentifier as Clusetered Index
CREATE TABLE dbo.TblSEQUID
(
Sno Uniqueidentifier NOT NULL DEFAULT NEWSEQUENTIALID(),
FirstName VARCHAR(100) NOT NULL,
DOB DATETIME NOT NULL,
CONSTRAINT pk_tblSEQUid PRIMARY KEY CLUSTERED(sno asc)
);

--Table with INTeger column as Clusetered Index
CREATE TABLE dbo.TblInt
(
Sno INT IDENTITY(1,1) NOT NULL,
FirstName VARCHAR(100) NOT NULL,
DOB DATETIME NOT NULL,
CONSTRAINT pk_tblInt PRIMARY KEY CLUSTERED(sno asc)
);

--Table with BIGINTeger as Clusetered Index
CREATE TABLE dbo.TblBigInt
(
Sno BIGINT IDENTITY(1,1) NOT NULL,
FirstName VARCHAR(100) NOT NULL,
DOB DATETIME NOT NULL,
CONSTRAINT pk_tblBigInt PRIMARY KEY CLUSTERED(sno asc)
);


-----------------------------------------------------
--Create NON Clustered Index on DOB Column
-----------------------------------------------------
CREATE NONCLUSTERED INDEX nx_tbluid_dob on dbo.tbluid(DOB);
CREATE NONCLUSTERED INDEX nx_tblsequid_dob on dbo.tblSEQUid(DOB);
CREATE NONCLUSTERED INDEX nx_tblInt_dob on dbo.tblInt(DOB);
CREATE NONCLUSTERED INDEX nx_tblBigInt_dob on dbo.tblBigInt(DOB);


/*
Insert dummy records in each of the table. 
Change the number near GO for capturing data for more records
*/

SET NOCOUNT ON;

INSERT INTO dbo.TblUID (FirstName, DOB)
SELECT REPLICATE('x',100),Getdate();
GO 10000

INSERT INTO dbo.TblSEQUID (FirstName, DOB)
SELECT REPLICATE('x',100),Getdate();
GO 10000

INSERT INTO dbo.TblInt(FirstName, DOB)
SELECT REPLICATE('x',100),Getdate();
GO 10000

INSERT INTO dbo.TblBigInt(FirstName, DOB)
SELECT REPLICATE('x',100),Getdate();
GO 10000


---------------------------------------------------
--Check the Space Used and Fragmentation Level
---------------------------------------------------
EXEC sp_spaceused tblUID, True;
SELECT index_type_desc, avg_fragmentation_in_percent, page_count, record_count
FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(N'tblUID'), NULL, NULL, NULL)
OPTION (MAXDOP 1);

EXEC sp_spaceused tblSEQUID, True;
SELECT index_type_desc, avg_fragmentation_in_percent, page_count, record_count
FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(N'tblSEQUID'), NULL, NULL, NULL)          
OPTION (MAXDOP 1);
  
EXEC sp_spaceused tblINT, True;
SELECT index_type_desc, avg_fragmentation_in_percent, page_count, record_count
FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(N'tblINT'), NULL, NULL, NULL)
OPTION (MAXDOP 1);

EXEC sp_spaceused tblBIGINT, True;
SELECT index_type_desc, avg_fragmentation_in_percent, page_count, record_count
FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(N'tblBIGINT'), NULL, NULL, NULL)
OPTION (MAXDOP 1);

Inspired by this blog post of Kimberlyhttp://www.sqlskills.com/BLOGS/KIMBERLY/post/GUIDs-as-PRIMARY-KEYs-andor-the-clustering-key.aspx