Improve SQL Server Performance with Compression

SQL Server data compression is now available in all editions of SQL Server, starting with 2016 SP1.

In this Article, you will not only know how data compression will save space, you’ll also find out how compression can sometimes improve performance as well.

Space Savings vs. Performance

When I first heard about compression back in 2008, my first thought is that it would have a big performance penalty. Compression would save disk space, but it would probably decrease performance as the data was compressed and decompressed. It turns out that compression can improve performance instead. Because compressed data fits in a smaller number of data pages, there are decreased I/O requirements. Since I/O is generally the bottleneck in SQL Server, this can improve performance. Compressed data also has a decreased memory requirement. When querying compressed data, a smaller number of pages will be copied to the buffer pool. The one area that is impacted is CPU. You do need to have some CPU headroom because compression will require some additional CPU resources.


The good thing is that if the workload is reasonably tuned, many SQL Server instances have more CPU resources than they need. One note of caution here. Don’t look to compression as the solution to solving major performance issues. You need to look at physical resources, configuration, indexing, and query tuning. The point I’m trying to make is that decreased I/O and better memory utilization will benefit the workload in many cases.

Continue reading “Improve SQL Server Performance with Compression”

Alwayson Availability Groups for Disaster Recovery Solutions


There are many users, who are not aware about disaster recovery planning with always-on availability groups. Even they do not know the terms that come in the utilization of Always-On Availability groups for disaster recovery planning. In the following section, we will discuss about the always-on availability groups for resolving the disaster Continue reading “Alwayson Availability Groups for Disaster Recovery Solutions”

Help! I have -2, -3, or -4 Session ID!

We can kill a session by using KILL command. However, KILL command requires a positive number; executing KILL with negative number returns an error:

Msg 6101, Level 16, State 1, Line 1
Session ID -4 is not valid.

In order to kill the session ID, you need to find the unit of work (UOW) guid.

SELECT DISTINCT(request_owner_guid) AS UOW
  FROM sys.dm_tran_locks
 WHERE request_session_id IN (-2,-3,-4)

Now you can kill this using UOW:

KILL 'D5499C66-E398-45CA-BF7E-DC9C194B48CF'

Like all normal transactions, killing a session causes any work performed by it to be rolled back to bring the database back into consistent state.

The negative session ID are orphaned or stuck sessions that SQL Server; they are rare occurrences. Most often the only one I have seen is -2; what do they mean?

Session ID Description
-2 The blocking resource is owned by an orphaned distributed transaction.
-3 The blocking resource is owned by a deferred recovery transaction.
-4 Session ID of the blocking latch owner could not be determined due to internal latch state transitions.

Reference: Books Online, sys.sysprocesses (Transact-SQL)

This post is cross posted on my SQLCAN Blog, MSDN Blog, and SQL server Consultation blog.

Awesome!! DMV and Stored procedure for any DBA

 Untitled Hello every body today no new post today but i will share some amazing and Awesome!!  DMV and stored i created it to help me in my daily work really it is very helpful for any Database Administrator
  • Create Clustered Index on all HEAP Tables by on Single Click.

this one of the best DMV for me because it fast the process for me and it is very helpful by this Stored procedure i sued the technique of   SQL SERVER DYNAMIC QUERY to loop on all databases exists on my SQL SERVER CLUSTER where this databases is not in system databases and it should be with status online to retrieve from it

  1. list by all heap tables.
  2. Loop in this list to retrieve the best column can be Clustered index Based on the criteria that have been developed from my side (Customized option )
  3. build the T-SQL statment of create clustered index

For more information and for download DMV check the (POST &  VIDEO)

  • Index Statistics for all Databases Exists on your Server

NOW any DBA can return all his index Statistics and save it to decide what he need to do shall i should drop some index (UNUSED INDEX) or shall i should check the index size to take A wise decision we should have the information about the index (last user seek data and percentage , user scan , index size , drop index statement if you need to delete index,..ETC)

For more information and for download DMV check the (POST)

Continue reading “Awesome!! DMV and Stored procedure for any DBA”

DMV Stacks#1(How to check Database Size , Log Size , Free Size)

Hi dears today i will write small DMV but the usage of it is very high needful and important today our DMV is How to check the Database size , Log Size , Free Size in SQL Server i will not Speak more let my DMV Complete what i need to Say :

at the first i will Use in my Demo Database AdventureWorkyou can download it from here SQL Server Database Product Simple

1- Check Database Size for one DB :

by this DMV you can find the Database Size by MB or GB for one DB on your Database Server and you can know the Physical File located for this DB

SELECT DB_NAME(database_id) AS DatabaseName,Name AS Logical_Name,Physical_Name, (size*8)/1024 SizeMB FROM sys.master_files WHERE DB_NAME(database_id) = 'AdventureWorks'



2- Database Size For All Databases on your DB Server :

here we can get all database size for all DB Server Each database separately

SELECT d.NAME,ROUND(SUM(mf.size) * 8 / 1024, 0) Size_MBs ,(SUM(mf.size) * 8 / 1024) / 1024 AS Size_GBs
FROM sys.master_files mf INNER JOIN sys.databases d
ON d.database_id = mf.database_id GROUP BY d.NAME
Order by (SUM(mf.size) * 8 / 1024) DESC

IF you need to know the SUM of the Database size it’s very simple the same previous DMV with CTE (Conman table Expression Technology )

Note : i used here in this DMV Concat Function  this new Function in SQL Server 2012 by it we do Concatenation between integer and String in the previous SQL Server version you should Convert the integer to String at first now it very easy

With CTE AS (
SELECT d.NAME,ROUND(SUM(mf.size) * 8 / 1024, 0) Size_MBs,
(SUM(mf.size) * 8 / 1024) / 1024 AS Size_GBs
FROM sys.master_files mf INNER JOIN sys.databases d
ON d.database_id = mf.database_id
Select Concat (SUM(Size_GBs),' GB') [DB Server SIZEfrom CTE

3- Check Free Size for one Database :

sometimes we need free size at this time we should know How many GB can we free it from our Database Server

SELECT DB_NAME() AS DbName, name AS FileName, size/128.0 AS CurrentSizeMB,
 size/128.0 - CAST(FILEPROPERTY(name'SpaceUsed'AS INT)/128.0 ASFreeSpaceMB
FROM sys.database_files;

For all Databases  and the SUM free Size :

one single DMV to know all Databases Free size and the SUM of this Frees size using Temp Table

Create Table #LogSize
Database_name Nvarchar(200),
[FILENAME] Nvarchar(200),
 CurrentSizeMB BIGINT,
insert into #LogSize
exec sp_msforeachdb
'use [?];
name AS FileName,
size/128.0 AS CurrentSizeMB,
size/128.0 - CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT)/128.0 AS FreeSpaceMB
FROM sys.database_files;
Select *,FreeSpaceMB/1024 AS FreeSpaceGB from #LogSize
ORDER BY FreeSpaceMB Desc
SELECT SUM(FreeSpaceMB/1024) TOTAL_Free_SIZE_GB from #LogSize
drop Table #LogSize

4- Check Log Size for all Database or one Database

here i am using temp table to save in it all my Database log size inforamtion and then i can Select what i need based on my requirement

Create Table #LogSize
Database_name Nvarchar(200),
Log_File_Size_MB Int ,
Log_File_Size_GB INT
insert into #LogSize
exec sp_msforeachdb
'use [?];
 select DB_NAME() AS DbName,
sum(size)/128.0 AS Log_File_Size_MB,
(sum(size)/128.0)/1024 AS Log_File_Size_GB
from sys.database_files where type=1
group by type
Select from #LogSize where Log_File_Size_MB > 100 order by Log_File_Size_MBdesc
drop Table #LogSize

Follow Me
Mostafa Elmasry