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
- · ROWLOCK
- ·
PAGLOCK
- ·
TABLOCK
- · DBLOCK
- · UPDLOCK
- · XLOCK
- ·
HOLDLOCK
- ·
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.
