Fill Factor impact on Sql Server performance

Interestingly, fill factor naming convention itself  saying its an argument, which has been to be fill up when performing certain task  none other than creating or rebuilding indexing.

prior to that other interesting concept is page split in Sql Server.In Sql Server data/rows will be stored in pages capacity range of 8kb. Page split operation happens when more number of rows are getting to be inserted in pages.

Fill Factor indicates the percentage that server has to be filled with data in the index pages creation/rebuild.
It has to be configured under level of Server and Database with the integer value limit to 100.
Fill Factor 0 = 100 in Sql server specifies the maximum amount of space filled by the server.
Fill Factor 70 Specifies only 70% of space has to be used for filling up the data into the pages rest is left for further respective insets and Updates.

Recommendation : Data Reading i.e SELECT always recommends 100/0 fill factor value.
More DML operations then default value should be altered based on the data range modification.
default value is '0'
using the syntax: Alter Index/Create Index for altering the value at Individual index level.
sp_Configure at server level fill factor value modification.

Index Usage Monitoring

Database consists of two types of pages Index pages and Data pages.By running up of some sample queries we can come to conclusion that what data in the table and what type of data is being populated on to the table on other hand it is little bit hard to find out which index is being mostly using for DML operation and which index is being used for Scans and Seeks.

Earlier Version's of sql server don't have an opportunity to gain the performance of Dynamic Management Views.One function and View can give a lot of in depth data about the indexes.

                                        Function:  sys.dm_db_index_operational_stats

                                                   lets see the usage of this function

Dynamic View: sys.dm_db_index_usage_stats

Now let see the usage of this view

Backup Using Transparent Data Encryption

Transparent Data encryption is one of the newly introduced feature in sql server 2008. Transferring the data between two networks/machines must be enriched with security limits to get rid of malicious  attacks.It ensures the best possibility of encryption for data and log files.Sql server engine perform the certain operation in order get transparent of data.Certainly encrypts the data before it written into the disk and Decryption the data before reads from the disk.

Usually backups perform on disk or tapes contains sensitive information that has to protect.Configuration of TDE is not as complex as clustering,Mirroring etc, just using the standard backup command after the encryption option is truned on. The backup files with TDE is highly protected,without the access with key is completely failure.

As we all know  that there are various algorithms have already been exposed in cryptography world..In our case AES algorithm is best for to make the backup file encrypt.lets bit concentrate more on encryption and decryption.

Normal definition as follows

Encryption--Plane text to cipher text
Decryption--cipher text to plane text.

basically there are two types of keys symmentric  key and asymmentric key,by using of these keys in order to storage of encrption data is effective.

USE master GO CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'mY_P@$$w0rd'

Then, we create the server-based certificate which will be used to encrypt the database

CREATE CERTIFICATE AdventureWorksCert WITH SUBJECT = 'My DEK Certificate for AdventureWorks database'

We will then set the encryption for the AdventureWorks database by creating a database encryption key and password using the certificate we just created.

USE Adventureworks GO CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_128 ENCRYPTION BY SERVER CERTIFICATE AdventureWorksCert GO

The encryption algorithm choice is yours, since a few different options exist.  For guidance on selecting an encryption algorithm on SQL Server, check out this Microsoft TechNet article.  After creating the database encryption key, we'll enable encryption on the database level.

ALTER DATABASE AdventrueWorks  SET ENCRYPTION ON

Let's backup the encrypted AdventureWorks database this time but using a different filename.

BACKUP DATABASE AdventureWorks  TO DISK = 'C:\Adventureworks_encrypted.bak' WITH INIT, STATS = 10

Thats all Done!

Redundant Array Of Independent Disks

Today i would like to present a basic idea regarding RAID.

WHAT IS RAID?

 It is a combination of multiple small and inexpensive disks in the form of an array to accomplish performance and redundancy goals which is not attained to gain by large disk drives.This type of drive appear like as a single storage unit  or drive.

In the RAID method information is spread across different drives using some of existing techniques called,disk stripping (RAID LEVEL 0), disk mirroring (RAID LEVEL 1),disk stripping with parity(RAID LEVEL 5) to achieve redundancy, lower latency and/or increase bandwidth for reading and writing to disks and maximize the ability to recover from hardware crashes .

RAID existence of different levels:

RAID 0:Non Redundant and data striping.

RAID 1: Redundant Mirroring.

RAID 3:Redundant parity disks.

RAID 5: Redundant  distributed parity

RAID 6:Redundant distributed parity(P+Q)

SQLSERVER CLUSTERING

lets discuss about Clustering

First and foremost question is WHAT IS CLUSTERING?


Microsoft Sql server clustering is collection of two or more physical servers with having an identical access to the shared disk that offers the disk resources to store the database files.

By naming convention these servers are referred as 'Nodes'.These nodes will maintain the communication between one another via private network, by sending heartbeat signal between each other.If one node fails to send the signal in the cluster then automatically the secondary server will take the ownership of all the responsibilities and application services depend on the primary node to maintain the high availability.This process refers as 'Fail over'.

There is an option to make this process manually. Manual fail over is very beneficial to make the upgrades and patches to the server or some other physical server maintenance is required.

Sql Server clusters are exclusively designed for high-availability databases to eliminate the down time in association of any hardware failure.A group of services are hosted on cluster node are referred as resource groups. Those groups are resides on shared disk in the form of SAN disk.Each resource group will be mapped to a logical drive that is physically hosted on the shared disk array and also have its own associated IP address and network name.

In windows server 2003 w.r.t Sql server 2005 enterprise edition is limited to 8 nodes in a single cluster.while considering the sql server 2008 the ability is doubled i.e 16 nodes.

Is there any thing new in SQL SERVER DENALI

Hello Frds,

hope you guys are doing great.we already had a close look of SQL SERVER former versions till R2.

2012 year brought an exited Gift for us called SQL SERVER DENALI(2012).Is it not guys? Every IT professional should speed up there actions to know about this Highly anticipated version.

Today i would like to give an simple and basic task of Denali called Its new features.

There are huge number of upgrades incense of features. I will present some of those.

1. Always Availability Groups:
This is one of interesting feature in Denali,it takes the Database mirroring to the next level.Instead of one Fail-over database,users can be able to perform multiples in groups.The secondary copies also readable  by users and also used for database backups.                 
2. Column Store Index:
Covered index,filtered index are one of the closest features in sql server2008R2 till we are aware.This index is special type of read only index for data warehousing queries.Data is grouped and stored in flat and compressed in column store index.well designed for reducing I/O and memory consumption of larger queries.
3. User Defined Server Roles: 
DBA s having the ability to create custom database role,but never server wide. For example, if the DBA wanted to give a development team read/write access to every database on a shared server, traditionally the only ways to do it were either manually, or using undocumented procedures. Neither of which were good solutions. Now, the DBA can create a role, which has read/write access on every DB on the server, or any other custom server wide role.

5. Enhanced Auditing Features : Audit is now available in all editions of SQL Server. Additionally, users can define custom audit specifications to write custom events into the audit log. New filtering features give greater flexibility in choosing which events to write to the log.

6. BI Semantic Model : This is replacing the Analysis Services Unified Dimensional Model (or cubes most people referred to them). It's a hybrid model that allows one data model will support all BI experiences in SQL Server. Additionally, this will allow for some really neat text infographics\

1. 
   
   7. Sequence Objects :For those folks who have worked with Oracle, this has been a long requested feature. A sequence is just an object that is a counter -- a good example of it's use would be to increment values in a table, based a trigger. SQL has always had similar functionality with identity columns, but now this is a discrete object.
   
   
   8. Enhanced PowerShell Support : Windows and SQL Server admins should definitely start brushing up on their PowerShell scripting skills. Microsoft is driving a lot of development effort into instrumenting all of their server-based products with PowerShell. SQL 2008 gave DBAs some exposure to it, but there are many more in cmdlets in SQL 2012.

DATA AUDITING IN SQL SERVER 2008

Hi guys,

DATA AUDITING is newly introduced concept in SQL SERVER 2008.

Auditing an instance of sql server or sql server database involves tracking and logging events that occurs on the system.There are several methods to audit the instance or database

---syntax for sql auditing.

create server audit example

to file (filepath = 'd:\example\')

alter server audit example

with (state = on)

---syntax for auditing the database in server audit file

create database audit specification example_db

for server audit example

add(insert ,select ,drop,delete on table_name by schema_name)

with (state = on)

---To check the data auditng by using the system function

select * from fn_get_audit_file('d:\xxx\',null,null)

Hope u gusy got the Basic idea about DATA AUDITING IN SQL SERVER