Saturday, June 30, 2012

SQL Server- Database Engine Tuning Advisor

Hi.....

                 Before entering to this article concept I am sharing one thing…A Few days ago I have given the Interview in one of the IT leading company and there interviewer asked to me many questions. There was one question was ”What is Database Engine Tuning Advisor (DTA) and its utility?” Ok. Now,

                    This is the Performance Tuning Tools. The Microsoft Database Engine Tuning Advisor (DTA) optimizes and analyzes databases and builds recommendations that you can use to Tune and optimize query performance. Basically we understand the meaning of database tuning Advisor we can use this Tool to find, select and create an best set of indexes, Non Cluster Index, indexed views, or table partitions. There is no need to Database expert because this tool gives the optimal solution.
               
                     This DTA understand the structure of database and it’s also understand the internal functional and logical of the server. This tool automatic makes the valid and needful structure which includes Indexing, Indexed View and table partitioning and more(tuning on large query in one or more then one database).

In SQL Server you can perform the following tasks with DTA. (Very Important)

1.        As we understand we can troubleshoot the performance of a specific problem query
2.        Using DTA Tool we can perform the tuning operation on large set of queries across one or more databases
3.        It’s used for optimal physical design changes on the database
4.        Minimize and utilize the storage space of the database.
5.        Using this we can save the Disk space.
6.        Using it we can remove unwanted existing Index, View and merge the table partition and database partition.
7.        DTA is not only gives the suggestion even its gives the method and script to perform the operation in the recommended object.
8.        When we execute the analysis it return the Latest progress, recommendation and finally its return the Report. In the report we can find the following things-
Maximum tuning time          50 Minutes
Time taken for tuning          1 Minute
Estimated percentage improvement 0.00
Maximum space for recommendation (MB) 251
Space used currently (MB) 161
Space used by recommendation (MB)        115
Number of events in workload        30
Number of events tuned      30
Number of indexes recommended to be dropped    36
Number of indexes on views recommended to be dropped  2

There are many tasks that can do in the DTA. 

Thanks For reading.......








Like and Share to SQL Integrity Blog

SQL Server- create new and Multiple FileGroups in Single Database





Hi .... Friends Well Come ....

NOTE:- Frequently Asked Question In Interview.

Today I am sharing very interesting Topic with you. I always get problem in add new FileGroup in SQL Server. But today I got the solution that is How to create new and Multiple FileGroups in Single Database.

There are two way to do this thing.

1) Using T-SQL 

2) Using SSMS (Sql Server Management Studio) Wizard

Solution: Now  we will  know  How to  Updating Existing Database and Adding Filegroup

1) Creating New Database And Adding Filegroup
   

Using T-SQL

CREATE DATABASE [DBFGTest] ON  PRIMARY
( NAME = N'DBFGTest',

FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\DBFGTest.mdf' ,

SIZE = 3072KB , FILEGROWTH = 1024KB ),

FILEGROUP [Secondary]

( NAME = N'DBFGTest_2',

FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\DBFGTest_2.ndf' ,

SIZE = 3072KB , FILEGROWTH = 1024KB )

LOG ON

( NAME = N'DBFGTest_log',

FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\DBFGTest_log.ldf' ,
SIZE = 1024KB , FILEGROWTH = 10%)
GO


 2) Using SSMS (Sql Server Management Studio) Wizard (Please Try This way also..)

a) Rigth click on New Database and Wirte New database Name

b) Select File option in properties (Showing on Left side in Dialog box)

c) Then 

Use this Operation
Do this operation In Showing Grid in Database Property Windows
File Name
Write the file name for the database file.
Location
Write the location of the database file, or click the ellipsis to navigate to the location.
Initial size (MB)
Write the initial size of the database file in megabytes (MB).
Filegroup
Write the name of the filegroup to which the database file will belong.







 D) Click OK Button .

This is very simple way to create Multiple filegroup in singale database ..

If you like this comment then please Comment it ...Thanks







Question: What is Locking?

Answer: Locking is a method which is used by the Microsoft SQL Server Database Engine to. It’s provide the synchronize access of Data by one or more than one users to the same piece of data at the same time.

Question: Why we use locking?
Answer: Generally user writing the query without Lock. But in the huge amount of database we should not write query or SQL script without Locking. Because When transaction running, Locking prevent the Dirty Read, it’s avoid the deadlock. We can utilize the resource of database

Question: Types of Locking?
 These are the types of locking which we can use in the database
  1. ·        ROWLOCK
  2. ·        PAGLOCK
  3. ·        TABLOCK
  4. ·        DBLOCK
  5. ·        UPDLOCK
  6. ·        XLOCK
  7. ·        HOLDLOCK
  8. ·        NOLOCK
Here I am Sharing Some Locking Examples are as follows

Examples:
SELECT OrderID
FROM Orders WITH (ROWLOCK)
WHERE OrderID BETWEEN 300
AND 5000

UPDATE Products WITH (NOLOCK)
SET ProductCat = 'Books'
WHERE ProductSubCat = 'Story'


SQL Server- Database Engine Tuning Advisor


Before entering to this article concept I am sharing one thing…A Few days ago I have given the Interview in one of the IT leading company and there interviewer asked to me many questions. There was one question was ”What is Database Engine Tuning Advisor (DTA) and its utility?” Ok. Now,

This is the Performance Tuning Tools. The Microsoft Database Engine Tuning Advisor (DTA) optimizes and analyzes databases and builds recommendations that you can use to Tune and optimize query performance. Basically we understand the meaning of database tuning Advisor we can use this Tool to find, select and create an best set of indexes, Non Cluster Index, indexed views, or table partitions. There is no need to Database expert because this tool gives the optimal solution. This DTA understand the structure of database and it’s also understand the internal functional and logical of the server. This tool automatic makes the valid and needful structure which includes Indexing, Indexed View and table partitioning and more(tuning on large query in one or more then one database).
In SQL Server you can do following tasks with DTA.
1.        As we understand we can troubleshoot the performance of a specific problem query
2.        Using DTA Tool we can perform the tuning operation on large set of queries across one or more databases
3.        It’s used for optimal physical design changes on the database
4.        Minimize and utilize the storage space of the database.
5.        Using this we can save the Disk space.
6.        Using it we can remove unwanted existing Index, View and merge the table partition and database partition.
7.        DTA is not only gives the suggestion even its gives the method and script to perform the operation in the recommended object.
8.        When we execute the analysis it return the Latest progress, recommendation and finally its return the Report. In the report we can find the following things-
Maximum tuning time          50 Minutes
Time taken for tuning          1 Minute
Estimated percentage improvement 0.00
Maximum space for recommendation (MB) 251
Space used currently (MB) 161
Space used by recommendation (MB)        115
Number of events in workload        30
Number of events tuned      30
Number of indexes recommended to be dropped    36
Number of indexes on views recommended to be dropped  2

There are many tasks that can do in the DTA.








Like and Share to SQL Integrity Blog

SQL SERVER: What is Locking, Types and Examples


Question: What is Locking?

Answer: Locking is a method which is used by the Microsoft SQL Server Database Engine to. It’s provide the synchronize access of Data by one or more than one users to the same piece of data at the same time.

Question: Why we use locking?
Answer: Generally user writing the query without Lock. But in the huge amount of database we should not write query or SQL script without Locking. Because When transaction running, Locking prevent the Dirty Read, it’s avoid the deadlock. We can utilize the resource of database

Question: Types of Locking?
 These are the types of locking which we can use in the database
  1. ·        ROWLOCK
  2. ·        PAGLOCK
  3. ·        TABLOCK
  4. ·        DBLOCK
  5. ·        UPDLOCK
  6. ·        XLOCK
  7. ·        HOLDLOCK
  8. ·        NOLOCK
Here I am Sharing Some Locking Examples are as follows

Examples:
SELECT OrderID
FROM Orders WITH (ROWLOCK)
WHERE OrderID BETWEEN 300
AND 5000

UPDATE Products WITH (NOLOCK)
SET ProductCat = 'Books'
WHERE ProductSubCat = 'Story'

Like and Share to SQL Integrity Blog

SQL SERVER – How To Create Multiple Filegroup For Single Database

Hi .... Friends Well Come ....

NOTE:- Frequently Asked Question In Interview.

Today I am sharing very interesting Topic with you. I always get problem in add new FileGroup in SQL Server. But today I got the solution that is How to create new and Multiple FileGroups in Single Database.

There are two way to do this thing.

1) Using T-SQL 

2) Using SSMS (Sql Server Management Studio) Wizard

Solution: Now  we will  know  How to  Updating Existing Database and Adding Filegroup

1) Creating New Database And Adding Filegroup
   
Using T-SQL

CREATE DATABASE [DBFGTest] ON  PRIMARY
( NAME = N'DBFGTest',

FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\DBFGTest.mdf' ,

SIZE = 3072KB , FILEGROWTH = 1024KB ),

FILEGROUP [Secondary]

( NAME = N'DBFGTest_2',

FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\DBFGTest_2.ndf' ,

SIZE = 3072KB , FILEGROWTH = 1024KB )

LOG ON

( NAME = N'DBFGTest_log',

FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\DBFGTest_log.ldf' ,
SIZE = 1024KB , FILEGROWTH = 10%)
GO
 

 2) Using SSMS (Sql Server Management Studio) Wizard (Please Try This way also..)

a) Rigth click on New Database and Wirte New database Name

b) Select File option in properties (Showing on Left side in Dialog box)

c) Then 

Use this Operation Do this operation In Showing Grid in Database Property Windows
File NameWrite the file name for the database file.
LocationWrite the location of the database file, or click the ellipsis to navigate to the location.
Initial size (MB)Write the initial size of the database file in megabytes (MB).
FilegroupWrite the name of the filegroup to which the database file will belong.



 D) Click OK Button .

This is very simple way to create Multiple filegroup in singale database ..

If you like this comment then please Comment it ...Thanks


Like and Share to SQL Integrity Blog

SQL SERVER : We cannot Perform Operation in MASTER database

Good Afternoon .........All...........

I Friends this very important thing which I am sharing with you. Just read about Master Database Restriction.

The operations cannot be performed on the master database in SQL Server 2005/2008/2012.
  • Adding files or filegroups SQL Server 2005/2008
  •  
  •  Changing collation. The default collation is the server collation SQL Server 2005/2008

  • Changing the database owner. master is owned by dbo SQL Server 2005/2008

  • Creating a full-text catalog or full-text index SQL Server 2005/2008

  • Removing the primary filegroup, primary data file, or log file.

  • Renaming the database or primary filegroup.
  • We cannot Setting the database to OFFLINE.

  •  We cannot Setting the database or primary filegroup to READ_ONLY.

  • Enabling change data capture. 
  •  
  • Participating in database mirroring.
  •  
  • Creating triggers on system tables in the database.
  •  
  • Dropping the database SQL Server 2005/2008
  •  
  •  Dropping the guest user from the database.

I hope this ll help to you .................

Like and Share to SQL Integrity Blog

Friday, June 29, 2012

How to check disk space in Sql Server 2000/05/08


Que : How to check disk space in Sql Server 
 
Run this Script and get the disk space of computer drives 
 
For 2005 /2008:
 
EXEC master.dbo.xp_fixeddrives
 
For 2000:
 
EXEC master.dbo.xp_fixeddrives

Like and Share to SQL Integrity Blog

Wednesday, June 27, 2012

SQL Server - Set Nocount On

SQL Server - Set Nocount On      
   
when you use the "set nocount on" sql server will not count how many row are effecting. that way it's imporve the performance of query of stored procedure.
Example 1:
set nocount on
 
select * from User_Details

Result: Command(s) completed successfully.

Example 2:

set nocount Off

 select * from User_Details 

Result : (12 row(s) affected)





I hope it will help you
 

Like and Share to SQL Integrity Blog