Data Compression–A unique feature PART–II TIP #92

 

In last post TIP #91  we talked about  What is Data compression ? What are the  features ?  Now in this tip we will take implement the data compression with basic steps.

So lets follow the steps

1) Right click on the table on which you need to implement compression. You will get following menu just select manage compression as shown in below figure

menu

2) Once you click on the above option you will get following screen.

wizard1

3) Now you can choose compression type from dropdown of the row either Page or Row . You can calculate estimated space saving by selecting the compression type. as shown in below figure

a) Page compression (In the last column you will find requested compressed space)

Page_Compression

b) Row Compression (in the last column you will find request compression space)

Row_compression

Now once you selected appropriate compression type you can click on Next button of the wizard you will get below screen

datacompressoionwizard

Now you can generate the script for this compression type. We can directly run the script on database itself if we are sure which with compression type.

row_data_compression_command

table_compression_command

Once we run the above command on database we are good to go and our table is compressed.

I hope this steps will help you to compress our database tables.

Enjoy compression !!

Rj !!

Data Compression– a unique feature of SQL Server TIP #91

A part from performance many times we faced challenges related to space  of our database. Sometimes our database is actually taking huge space and sometimes it our mistake due to which it took space.

It is worst condition when you are on a dedicated hosting or cloud hosting with limited space. In such case “Compression” is one of the best option.

This feature introduce in SQL SERVER 2008 onwards with Enterprise & Developer Edition.

By the name it is clear that it compress the data and provide more space on disk.

There are 2 options available in Data compression

Row level compression:-

 Row level compression primarily turns fixed-length data-types into variable data-types, thereby saving space. It also ignores zero and null values saving additional space. Because of this, more number of rows can be accommodated in a single data page.

Page level compression:-

Page level compression initially performs Row Level compression and adds two additional compression features – Prefix and Dictionary Compression. Page level compression offers better space saving than row level compression. Although the page level compression produce more space but obvious the CPU utilization is higher in page level compression. Overall we can say the page level compression is highly CPU consuming operation means it involves lot of  CPU efforts for compress & de compression. So choosing this option requires a high CPU & RAM configuration machine. Due to this feature it mostly used in data warehouse database where repeated values occurs. using this option with a table where lots of write operation occurs in day to day activity will be a bad idea.

Now we aware of data compression and its two options. Now lets discussed some of the major points before using this valuable feature

1) Data compression is SQL SERVER feature so we don’t need to do any tweak or rewrite our application code which is a good thing.

2) Compression is real time so again you don’t need to re run a maintenance job each time to compress the data.

3) As mention earlier you have to pay bit CPU cost for compression.

4) with Compression data files MDF files, ldf files and buffer cache size can be improve.

5) Compression not compress FILESTREAM data object which is obvious.

6) Although maintenance of compressed index is higher.

In next post I will show simple steps for implementing this best feature.

Enjoy !!!

Thanks

RJ!!!

Kill–use this weapon carefully in SQL SERVER . TIP #90

When we heard “Kill” then first impression of this word is very bad. We always scare with this word.

In real world we never want this action  from anyone but in SQL Server case it is very helpful and help us many times.

This feature we can use but very carefully. It is something like we have to kill a cruel giant not the common innocent person.

Lets understand this by an example.

Suppose we wrote a stored procedure or function and by mistake we wrote a condition which is never ending. We didn’t realize this and when we run the stored procedure/function the SQL Server hangs , system is slow and many other issues occur and our stored procedure which we run still running and laughing on us.

In such situation we have to kill that particular stored procedure execution statement which is cruel giant and eating the resources.

See below statement

WHILE 1=1
BEGIN
  SELECT ‘Infinite Loop’
END

It is never ending loop. and it is running on our SQL SERVER and will never stop.

Now first step is to Identify the process id for this statement for this we can use either sp_Who or sp_who2 which we discussed in TIP #82.

Now when we run it you will find on Database “IndiandotnetDB” we have a SPID 53 which is last statement on below image. This SPID 53 is reference of above while loop which we have to stop any ways.

sp_who2_Inadiandotnet

Now we know SPID so we can stop or KILL  as shown below the syntax is very simple of KILL

KILL SPID

Kill_Process_Indiandtnet

Now we are good to go.

Note: Please use this KILL statement very carefully.

I hope this tip will help you some where to KILL or stop SQL SERVER enemies.

Enjoy !!!

RJ!!!

Secure your password with HashBytes TIP #89

Dear Friends,

In my last article (TIP 87) I wrote about PWDENCRYPT.  I forgot to write few details but thanks to all my talented friends & blog readers who guided me by providing there feedback on post.

I respect their inputs and always interested to get more inputs. Thanks to all of you.

Now I would like to share information about “HASHBYTES”  function which is also available for secure your password using various Hash algorithms like MD2 , MD4 , MD5 , SHA ,SHA1 , SHA2_256 , SHA2_512.

The PWDENCRYPT  can be use but  HASHBYTES function provides you various options to make your content robust secure.

The Syntax is very easy as shown below

HASHBYTES (ALGORITHM, ‘INPUT WHICH YOU WANT TO SECURE’)

Now let see an example to understand it more

HASHBYTES

Just wanted to add here that SHA2_256, SHA2_512 available with 2012 version and above.

Someone said this line right “More option More confusion”

Now we have different algorithm then which one we have to use so the answer is (according to my knowledge) use the latest most secure one Like SHA1, SHA2 etc.

I hope this article may be useful to you.

Thanks !!!

RJ!!!

PWDCOMPARE–a hidden function of SQL SERVER TIP #88

 

I the last tip TIP#88 we saw how to encrypt a password. Now in this tip I would like to share how to check encrypted password ?

Means once you stored your encrypted password in database now next step is to compare that particular password with your input password and return results accordingly.

The Syntax of the PWDCOMPARE   is very simple

PWDCOMPARE(‘Password plain text’, ‘Password encrypted form’)

This function return 1 if plain text and hash value  are matched else return o.

For example

Lets suppose we have created a table with 3 columns like userId, username and password

as shown below

DECLARE @tblLogin AS TABLE (UserId INT IDENTITY,
                            Username  VARCHAR(100),
                            EncryptedPassword NVARCHAR(MAX))

Now suppose we have inserted 2 rows in to it wit encrypted password

INSERT INTO @tblLogin VALUES (‘Indiandotnet’,PWDENCRYPT(N’MyPassword’))
INSERT INTO @tblLogin VALUES (‘SQLRaaga’, PWDENCRYPT(N’Test’))

Now, Suppose we have want to write a query which return rows from @tbllogin whose password is Test then it should return SQL Raaga for this

I have to write following query

SELECT * FROM @tblLogin WHERE PWDCOMPARE(N’Test’,EncryptedPassword) = 1

For detail  take a look of below snap

PWDCompare_Indiandotnet

 

I hope you understand with above provided  example.

 

Enjoy !!!

RJ!!

How to Encrypt password in SQL Server ? TIP #87

Security is always a concern for every database developer. How to secure valuable information is one of the major and important aspect.

The first approach toward security to have a strong username & password and the next step is to have password in encrypted form.

Now this article will help you to encrypt your password in hash. Isn’t it interesting ?

So SQL Server provided a function by using that particular simple function we can encrypt a password from plain text to hash.

The valuable function is PWDENCRYPT.  By the name it is clear that it will crease the password.

The syntax is very simple PWDENCRYPT(N’String which you want to encrypt’)

see below snap for more detail.

PWDEncrypted_Indiandotnet

I hope this tip help you to secure your password.

Enjoy !!

Thanks

RJ

How easy to determine table dependencies ? TIP # 86

 

Determine the table dependencies is challenging sometime but we can easily resolve this by using a simple stored procedure which  SQL Server provides.

By using this stored procedure we can easily determine all the dependencies of particular table.

The stored procedure is sp_msdependencies

We can use this stored procedure as shown below

Execute sp_msdependencies ‘tableName’

For example I am using Adventureworks2012 and I want to know the dependencies of product table then I have to write following command

Use AdventureWorks2012
Go
EXEC sp_msdependencies ‘[Production].[Product]’
GO

When I run this command I get result as shown in below figure

sp_msdependecies_Indiandotnet

I hope this tip may help you somewhere.

Thanks for reading.

Enjoy !!!

Rj !!!

“sp_helptext“ – Useful command to view detail TIP #85

 

It might be already known to you but I thought for sharing because I frequently use this command and it is very useful command.

When someone wants to determine detail of a function or stored procedure he/she can use this useful command.

The syntax is very simple. Just write

sp_helptext Storedprocedure/ functionname

For example If I want to determine detail of a stored procedure “proc_FindStudentUsingCorrect” then I have to write following command

sp_helptext proc_FindStudentUsingCorrect

see below snap for detail

sp_helptext

 

You can now copy the result text and check what exactly written in the stored procedure or function.

I hope you will use it in your day to day practice.

Enjoy!!!

RJ

Easy way to diagnose SQL Server “sp_Who2” TIP #84

 

Why the SQL Server is running slow ?

What are the processes running currently on SQL SERVER instance ?

Many other like the above which might help us to understand our current SQL Server instance health can be answered by a simple command “sp_who2”.

“sp_Who2”  is an Undocumented command. You can utilize this command to check current status of your SQL SERVER.

We can run this stored procedure directly in Management studio.

See below snap for detail

sp_who2

if you see above snap you will find the sp_who2 providing login detail, database name, command action which is currently applied , CPU time, DiskIO etc.

You can easily find which spId consuming highest CPU,DISKIO etc.

I hope this stored procedure might help you.

Thanks

RJ

How to configure memory in SQL SERVER ? TIP#83

 

One of the good sentence I remember “When someone has teeth he/she is not having nuts and when someone has nuts he/she not having teeth”. Just joke a part. You understand what I mean to say here. If you have the resources then utilize it.

One of the most most important aspect in performance is Memory.

The main point here is if we have high configured machine which have more than 16 GB RAM but the pain point is our SQL Server is not using available Memory.

Now to configure memory for SQL SERVER is supper easy but on the same time you need to understand how much memory you can assign to SQL SERVER to use because you need some buffer memory to your operating system and other

Now just follow below steps to configure the memory for SQL SERVER

Step 1:- Right click the server and open SQL SERVER Properties

Step 2:- Now select the memory tab and you will find the below screen

Memory 

Step 3:- You can change the above boxed max memory value according to your available memory calculation.

OR you can run following command as well to set the Maximum memory that SQL SERVER can utilize

CommandMemoryset

sp_configure ‘show advanced options’, 1;
GO
RECONFIGURE;
GO
sp_configure ‘max server memory’, 4096; — 4GB
GO
RECONFIGURE;
GO

One more important point I would like to share if your machine is X86 machine in such case you have to use /3GB switch with boot file first.

You can find the instruction to set /3GB switch with following link  https://technet.microsoft.com/en-us/library/bb124810%28v=exchg.65%29.aspx

I hope this article might help you.

Enjoy !!!

Rj!!