Database Reference
In-Depth Information
F
Hard disk space:
Minimum 120 GB or higher
Specific to database size maximum
Data striping—RAID1 or Mirrored drive—RAID0
Quick recovery and reliability—RAID 1+0
Redundancy and high performance—RAID5
How to do it...
The following steps are required in implementing parallel query processing methods. By
default, SQL Server controls provides flexibility to control parallelism by using the maximum
degree of parallelism (MAXDOP) hint per query.
1.
Use the default value of
MAXDOP
is
0
(zero). To supress parallel plan generation, the
MAXDOP value can be set to
1
.
2.
Similarly, the max degree of parallelism option can be set as a server-side
option using:
--Use 8 processors in a 12 CPU server
sp_configure 'show advanced options', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
sp_configure 'max degree of parallelism', 8;
GO
RECONFIGURE WITH OVERRIDE;
GO
3.
Let us see how best the MAXDOP query hint can help in a frequently running or adhoc
execution of queries. This can be achieved opening a New Query window on SSMS:
--Capture the query execution statistics and generate a showplan
for the query
SET STATISTICS IO ON
GO
SET SHOWPLAN_ALL ON
GO
--To generate PARALLELISM Physical Operation
Use Adventureworks2008R2
GO
SELECT * FROM Production.TransactionHistoryArchive ORDER BY
ProductID,Quantity
Search WWH ::
Custom Search