DataType can matter in where condition - a worst case scenario by serverku

We should use proper filter in where conditions as per data type. Like if the data type of filtered column is varchar then use filtered variable or value varchar, if filtered column is an integer then filtered variable/value should be an integer.

Worst case,

 
-- #1. 
-- If OrderId column is VARCHR, this cause table scan
-- because here OrderId column all values convert from varchar to int 
-- even if index created on it 
SELECT * 
FROM OrderDetails 
WHERE OrderId = 123

Best case,

 
-- #1. 
-- If OrderId column is INT, this will work fine 
SELECT * 
FROM OrderDetails 
WHERE OrderId = 123

-- #2. 
-- If OrderId column is INT, this will also work fine 
-- because here value '123' convert from varchar to int 
SELECT * FROM OrderDetails 
WHERE OrderId = '123'

Create a table with sample records with different data types as mentioned in above queries and check the execution plan, you may see the difference for both of them.

Using Covering Index and Performance Review - a new Index type of SQL Server 2005 by serverku

We all know about the indexes and the concept of it. As well as the types of the indexes. During working one query optimization, I got suggested by the SQL Server Optimizer with index creation and that index was Covering index.

What is covering index?
It is an additional index with includes columns in definition which are exists in select list. Per msdn and book online, The term covering index does not mean a separate kind of index having a different internal structure. Rather, this term is used to describe a certain technique that is used to improve performance. It will better if we go through the demo and example. Let us start with objects creation,

USE DEMO
GO

-- Creating table which will be used for demo

IF (OBJECT_ID('TblCoveringIndex','U') > 0)
DROP TABLE TblCoveringIndex

CREATE TABLE TblCoveringIndex
(
ObjectId bigint,
ObjectName varchar(100),
CreateDate datetime,
ObjectType varchar(100)
)

GO

-- Inserting some sample records in table 

INSERT INTO TblCoveringIndex
SELECT TOP 80000
convert(bigint,CONVERT(VARCHAR(100),a.object_id) + CONVERT(VARCHAR(100),b.object_id) ),
CONVERT(VARCHAR(100),a.name) + CONVERT(VARCHAR(100),b.name) ,
B.create_date,
b.type_Desc
FROM SYS.OBJECTS A
CROSS JOIN SYS.OBJECTS B

GO

Now we will review the execution plan without any indexes created on the table.

--  Execution plan with any indexes
SELECT 
ObjectId,
ObjectName,
ObjectType
FROM TblCoveringIndex
WHERE CreateDate >= GETDATE() - 50

GO

Now We will create a one clustered index, Normal non-clustered index and an additional non cluster index.

-- Creating clustered index on ObjectId column.
CREATE CLUSTERED INDEX IX_ObjectId ON TblCoveringIndex (ObjectId)
GO

-- Creating normal nonclustered index on CreateDate column.
CREATE NONCLUSTERED INDEX IX_CreateDate on TblCoveringIndex (CreateDate)
GO

-- Creating covering nonclustered index on CreateDate column.
CREATE NONCLUSTERED INDEX IX_CreateDate_Covering ON TblCoveringIndex (CreateDate) INCLUDE (ObjectId,ObjectName,ObjectType)
GO

We have created an additional non-clustered index with an included column, which are going to be used in the select list. After index's creation, It is finally time to review for each index created and execution plans of the queries using each of them. Here we force the query to use the normal non-clustered index and addition non-clustered index created.

SELECT 
ObjectId,
ObjectName,
ObjectType
FROM TblCoveringIndex with (index (IX_CreateDate)) 
-- Forcing index hint of normal nonclustered index
WHERE CreateDate >= GETDATE() - 50 
-- Applied column filter on which the indexes created

SELECT 
ObjectId,
ObjectName,
ObjectType
FROM TblCoveringIndex with (index (IX_CreateDate_Covering)) 
-- Forcing index hint of additional nonclustered index 
WHERE CreateDate >= GETDATE() - 50 
-- Applied column filter on which the indexes created

GO

(Clink on image to enlarge)

I hope you like this post. Before apply this type of index please verify execution plans and performance review and decide which indexes are better for the query plan.

Restore Full Database from multiple backup files - SQL Server by serverku

As I know database restoration is a best practice on periodically to check and verify database backup copies and issues while restoration due to any stuffs. I have written for the database backups as how can we perform a full database backup completely and split into multiple files to reduce IO and time. As we have performed full database backups into split multiple files, Same way we will perform restoration from those complete and split multiple files. First we will see to restore full database complete backup and tried for from multiple files.

#1. Using TSQL : Let us run the below script first and get the logical names for database data and log files

RESTORE FILELISTONLY FROM 
DISK =  'D:\DBBackups\ReportServer\ReportServer.bak'
GO

After collection and putting logical file names in script below, it will restore the database from complete backup.

RESTORE DATABASE ReportServerComplteCopy FROM 
DISK = 'D:\DBBackups\ReportServer\ReportServer.bak'
WITH REPLACE ,
MOVE 'ReportServer' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER11\MSSQL\DATA\ReportServerComplteCopy.mdf',
MOVE 'ReportServer_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER11\MSSQL\DATA\ReportServerComplteCopy_log.ldf'
GO

As above, we can restore database from multiple files as following,

RESTORE DATABASE ReportServerSplitCopy FROM 
 DISK = 'D:\DBBackups\ReportServer\ReportServer_Split1.bak'
,DISK = 'D:\DBBackups\ReportServer\ReportServer_Split2.bak'
,DISK = 'D:\DBBackups\ReportServer\ReportServer_Split3.bak'
WITH REPLACE ,
MOVE 'ReportServer' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER11\MSSQL\DATA\ReportServerSplitCopy.mdf',
MOVE 'ReportServer_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER11\MSSQL\DATA\ReportServerSplitCopy_log.ldf'
GO

#2. From Management Studio : We can also perform restoration from SSMS by adding those multiple backups as per shot.

You can see over here after adding those files it will show as a single backup set.

Hope this help you.

Database Backup Compression, Amazing feature for DBA - SQL Server 2008 by serverku

Production Database servers may have databases which are heavily in size. For the maintenance of those database backups are very hard and lengthy as the backups of those heavily databases take more time to execute and very CPU, memory and IO consumptive. And the important thing is backups activity should be completed within down time or pick a time when more users are not connected with databases.

What is the solution?
We have alternative ways to use the some backup tool that can help to use in this matter. But SQL Server itself provides the best feature and supported SQL Server 2008 or newer version. That is "Backup Compression". You can read my earlier posts for Automated All Databases Backups, Database Backup files Verification and Details and Split Database Full Backup to Multiple files

Using this feature, we can take a database backup with compression option. And will really reduce the time required to backup it, reduce server IO and less CPU and memory consumption. It is very full features for the DBA. Let us look on below example, which will clear you the difference between the uncompressed and compressed backups. We will first perform non-compressed backups of the database which have 4 GB size.
Using Management Studio :

Using TSQL:

#1. Performing noncompressed backup.

SET STATISTICS IO ON
SET STATISTICS TIME ON

BACKUP DATABASE ReportServer TO  
DISK = N'D:\DBBackups\Compressed\ReportServer_NonCompressedBackup.bak' 
WITH NAME = N'ReportServer-Full NonCompressed Database Backup',
NO_COMPRESSION -- Specifying option here

SET STATISTICS IO OFF
SET STATISTICS TIME OFF
GO

#2. Performing compressed backup.

SET STATISTICS IO ON
SET STATISTICS TIME ON

BACKUP DATABASE ReportServer TO  
DISK = N'D:\DBBackups\Compressed\ReportServer_CompressedBackup.bak' 
WITH NAME = N'ReportServer-Full Compressed Database Backup',
COMPRESSION -- Specifying option here

SET STATISTICS IO OFF
SET STATISTICS TIME OFF
GO

From the result oputput , you can view the time for the backup execution, CPU usage. Here you have screen for the both of the backups size.

You can use below query to get the backup statistics,

SELECT 
 bs.database_name AS DatabaseName , -- Database name
 backup_size/compressed_backup_size as CompressionRatio, 
 CASE bs.type 
  WHEN 'D' THEN 'Full' 
  WHEN 'I' THEN 'Differential' 
  WHEN 'L' THEN 'Log' 
  WHEN 'F' THEN 'File or filegroup' 
  WHEN 'G' THEN 'Differential file' 
  WHEN 'P' THEN 'P' 
  WHEN 'Q' THEN 'Differential partial' 
 END AS BackupType, -- Type of database baclup
 bs.backup_start_date AS BackupstartDate, -- Backup start date
 bs.backup_finish_date AS BackupFinishDate,  -- Backup finish date
 bmf.physical_device_name AS PhysicalDevice, -- baclup Physical localtion
 bs.backup_size AS  [BackupSize(In bytes)],  -- Normal backup size (In bytes)
 compressed_backup_size AS [ConmpressedBackupSize(In bytes)] -- Compressed backup size (In bytes)
FROM msdb.dbo.backupset bs 
INNER JOIN msdb.dbo.backupmediafamily bmf
 ON (bs.media_set_id=bmf.media_set_id)
 AND database_name = 'ReportServer'
ORDER BY bs.backup_start_date DESC

You can set the default backup setting to Compressed as following,

By TSQL :

USE MASTER
GO

EXEC SP_CONFIGURE 'backup compression default', 1
GO
RECONFIGURE WITH OVERRIDE;
GO

From UI :

I hope you like this feature..

Grouping Sets vs Native method of Group By - Performance review in SQL Server 2008 by serverku

Previously I have posted for the overview and usage of the Grouping Sets as how can we get the aggregate data of different group sets with GROPING SETS vs Native method of group by.

If you have not read my earlier post for the same, then please read it before go ahead with this demonstration. In this demo i am going to show the performance of Grouping Sets and native method of group by. Let us start the demo here.

-- Creatind database and table 
CREATE DATABASE GroupingDB

GO

USE GroupingDB

GO

IF (OBJECT_ID('GroupingTable') > 0)
DROP TABLE GroupingTable

CREATE TABLE GroupingTable
  (
     MainCategoryName VARCHAR(100)
     ,SubCategoryId   VARCHAR(100)
     ,VALUE           BIGINT
  )

GO 

Now we are going to insert so many records in table for the presentation of demo.

-- Insert some demo records for different group sets
INSERT INTO GroupingTable
SELECT 'Main-1','Sub-1',100
GO 500 

INSERT INTO GroupingTable
SELECT 'Main-1','Sub-2',200
GO 500 

INSERT INTO GroupingTable
SELECT 'Main-2','Sub-1',300
GO 500 

INSERT INTO GroupingTable
SELECT 'Main-3','Sub-1',300
GO 500 

INSERT INTO GroupingTable
SELECT 'Main-3','Sub-2',400
GO 500 

As I said to check the performance of both of the script using them and check the execution plan.

-- Get aggregate data using native method of group by of different sets
SELECT NULL, NULL,
  SUM(VALUE) as Total
FROM   GroupingTable
Union all
SELECT  MainCategoryName
,NULL
,SUM(VALUE) as Total
FROM   GroupingTable
GROUP  BY MainCategoryName 
Union all
SELECT  NULL,
  SubCategoryId
  ,SUM(VALUE) as Total
FROM   GroupingTable
GROUP  BY SubCategoryId 
union all
SELECT
  MainCategoryName
  ,SubCategoryId
  ,SUM(VALUE) as Total
FROM   GroupingTable
GROUP  BY
  MainCategoryName
  ,SubCategoryId

-- Get aggregate data using Grouping Sets of different sets
SELECT
  MainCategoryName
  ,SubCategoryId
  ,SUM(VALUE) as Total
FROM   GroupingTable
GROUP  BY
GROUPING SETS
(
  (MainCategoryName ,SubCategoryId),
  (MainCategoryName),
  (SubCategoryId),
  ()

)
ORDER BY MainCategoryName,SubCategoryId

You can see the data rows of above both scripts are same and given same result set. Now look for execution plan.

(Click on image to enlarge)

This performance review is totally based on the data and depends on the your business requirement. Before implementing this new feature, please check the execution and decide you view.