Configuring Database Files with RAID systems

RAID stands for Redundant Array of Independent Disks. Even though RAID systems does not included with SQL Server configuration, it greatly affects the SQL Server's performance. RAID can be configured using
hardware or software. But our scope is to identify the best configuration for our needs.

The following are different type of RAID configurations.

	RAID 0: (Stripping) In Raid 0 system, data gets split up in blocks that get written across all the drives in the array. RAID 0 has Superior I/O performance. RAID 0 is not fault tolerant. If one disk fails, all the data in RAID 0 arrays are lost. RAID 0 should not be used in mission critical systems.
	RAID 1: (Disk Mirroring) RAID 1 provides a redundant copy of the selected disk. RAID 1 improves read performance but degrade write performance.
	RAID 5: (Stripping and Parity) RAID 5 stripes the data across the discs and also adds parity information to provide fault tolerance. Parity information is distributed among all disks. If any one of the disk fails, read performance decreases. Requires at least 3 disks to implement.
	RAID 10 or RAID 1+0: RAID 10 includes both stripping without parity and mirroring. RAID 10 offers better availability and performance than RAID 5, especially for write-intensive applications. It is best practice to have data files and log files placed in separate disks so that you can improve performance and reduce disk contention.

Questions:

1. You administer a SQL Server 2008 instance. You plan to deploy a new database that has the following capacity requirements:
275 GB for the database data file
50 GB for the transaction log file
The storage array has six 100-GB disk drives available for the database. The disks are attached to a redundant array of independent disks (RAID) controller that supports RAID levels 0, 1, 5, and 10. The
write performance of the transaction log needs to be maximized. The database and transaction log files must be protected in the event of a drive failure. You need to design the storage system. Which storage
configuration should you use?

A. A single RAID 5 volume
B. A single RAID 10 volume
C. A RAID 0 volume and a RAID 5 volume
D. A RAID 1 volume and a RAID 5 volume


2. Your database named DB01 has seven 120 GB hard disk drives. Two of the hard disks are configured as a mirrored volume and contains the operating system and SQL Server 2005 is installation. Another two disks
are configured as a RAID-1 volume and the rest are configured as a RAID-5 volume.

You to need to plan the placement of the database and the transaction logs. You want to implement fault tolerance and best performance for the database.  What should you do?

A. Place the database on the RAID-5 array and the transaction log on the RAID-1 array.
B. Place the database and the transaction log on the RAID-5 array.
C. Place the database on the mirrored volume and the transaction log on the RAID-5 array.
D. Place the database on the RAID-1 array and the transaction log on the RAID-5 array.


3. Which of the following types of RAID should you NOT use as the basis for a volume to host database files if you want to ensure that the volume survives the failure of a hard disk drive?

A. RAID 0
B. RAID 1
C. RAID 5
D. RAID 10


Show Answers:

1. (D)
2. (A)
3. (A)


Hide Answers (X).

How To Configure Processor Affinity Mask in SQL Server 2005

Under heavy system loads, configuring which processor should run a specific thread can improve performance by reducing the number of times the processor cache is reloaded. The association between a processor and a thread is called processor affinity.

 Provision to change the processor affinity mask was introduced in SQL Server 2005. The affinity mask setting controls the number of CPUs used by the SQL Server for query execution.

The default value of affinity mask setting is '0' which indicates parallel execution of all available logical CPUs.

How to set processor affinity:


To configure the affinity mask using GUI, following steps are to be carried out.

1. Open Microsoft SQL Server Management Studio.
2. Right click the server and then click properties.
3. Server properties dialog box opens.
4. In 'Select a Page' section, click the processor tab.
5. Uncheck the 'Automatically set processor affinity mask for all processors'.
6. Now, check boxes under "Processor Affinity" will be enabled.
7. Uncheck the processor you wanted to exclude.

As shown in the figure, CPU1 is selected. The script to enable CPU1 alone will be as follows.

EXEC sys.sp_configure 'show advanced options', '1'; 

RECONFIGURE WITH OVERRIDE

GO

EXEC sys.sp_configure 'affinity mask', '2';

RECONFIGURE WITH OVERRIDE

GO

The following query will return the value of the affinity mask setting in your SQL Server.

SELECT * FROM sys.configurations

WHERE NAME = 'AFFINITY MASK'

Determining Affinity Value:

Consider for example, if your server have 8 CPUs. If processors 1,2,5 are selected as available then bits 1,2,5 is set to1 and bits 0,3,4,6,7 are set to 0 as shown in the following table.

Processors	Bits	Binary
CPU7	Bit7	0
CPU6	Bit6	0
CPU5	Bit5	1
CPU4	Bit4	0
CPU3	Bit3	0
CPU2	Bit2	1
CPU1	Bit1	1
CPU0	Bit0	0

Therefore the binary would be 0010 0110 and its decimal value can be calculated as follows:

0010 0110
= 0*(2^7) + 0*(2^6) + 1*(2^5) + 0*(2^4) + 0*(2^3) + 1*(2^2) + 1*(2^1) + 0*(2^0)
= 0*(128) + 0*(64) + 1*(32) + 0*(16) + 0*(8) + 1*(4) + 1*(2) + 0*(1)
=0 + 0 + 32 + 0 + 0 + 4 + 2 + 0
=38

Therefore, decimal value '38' will be used with affinity mask command as follows:

EXEC sys.sp_configure 'show advanced options', '1'; 

RECONFIGURE WITH OVERRIDE

GO

EXEC sys.sp_configure 'affinity mask', '38';

RECONFIGURE WITH OVERRIDE

GO

In actual practice, changing the affinity mask setting only rarely helps performance gains. It may frequently degrade the performance. Hence it is better to leave with the default setting '0'.


Question:

1. You are administering SQL Server 2005 with 8 processors. You are supposed to optimize the server so that longer running queries can run parallel plans. What will you do?

A. Set the max degree of parallelism to 1.
B. Set the cost threshold for parallelism to 0.
C. Set the affinity mask to 0.
D. Set the AWE enabled setting to 0.

Show Answers:

1. (C).

Hide Answers (X).

Implementing WSUS for SQL Server Patch Management

Windows Server Update Services (WSUS) is the solution provided by Microsoft for enterprise patch management.

Using WSUS, network administrators updates specific computers and groups and determine automatically whether updates are appropriate for each targeted computer prior to installation.

It is a best practice to deploy an update in a test group before distributing it throughout your production network, as you can determine its impact before it can impair your production computers. Thereby you can avoid unscheduled downtime and productivity loss.

The following steps will be carried out to implement WSUS:

1. Configure Updates and Set up Synchronization with Microsoft’s Windows Update site.
2. Create a Computer Group for Updates.
3. Approve the updates in WSUS and Deploy in your test server.
4. Check the updates installed in test server.
5. Approve the updates in WSUS and Deploy in SQL Server group.
6. Check the updates installed in SQL Server group.

Question:

1. You came to know that a new update is available in Microsoft’s Windows Update site. The update will fix some SQL server issues. How will you deploy the update to all SQL servers in your company?

(a) Deploy the update in all SQL server boxes manually one by one.

(b) Deploy the update in all production SQL server boxes directly using WSUS.

(c) Deploy the update first in test server, after ensuring no issues, deploy in production using WSUS.
(d) Do not deploy the update. Instead wait for the next release of SQL Server application.

Show Answers:

1. (c).

Hide Answers (X).

For additional resources on how to implement WSUS server :

• The WSUS homepage at http://technet.microsoft.com/en-us/wsus/bb466202.aspx.
• TechNet article, "Patch Management Using Microsoft Software Update Services" at http://technet.microsoft.com/en-us/solutionaccelerators/default.aspx.
• Software Update Services Deployment white paper at http://technet.microsoft.com/en-us/wsus/bb466200.aspx

Recompiling Stored Procedures

If you want your query to run faster, you need to understand the optimizer. SQL server database engine uses query optimizer to generate an optimal execution path. The optimal execution path is nothing but the path that offers best performance.

Before the query executes, the optimizer assigns a cost based on CPU and disk I/O usage for different execution paths. The optimizer then uses the least expensive path and executes the query.

When we execute a stored procedure for the first time, its query plan is stored in cache. It will reside in the memory as long as memory is available or until it is recompiled.

After generating the query plan, if you add an index to a table that is used by the stored procedure, it may not be used by the stored procedure.

Hence whenever you do modifications for a table that is used by the stored procedure, it is best practice to use SP_RECOMPILE or you can drop and recreate the stored procedure.

A stored procedure automatically recompiles its query plan whenever you drop an index used by a table within a stored procedure.

To check if a stored procedure plan is recompiled the SP:Recompile SQL Server Profiler event can be monitored.

Automatic recompilation occurs whenever the following changes happen:

• Executing an ALTER TABLE statement.
• Creating or dropping indexes.
• Executing UPDATE STATISTICS.
• Table data modifications.

Let us consider the following example: 

CREATE PROCEDURE sp_Test @LName char(30) AS

SELECT LastName

FROM Users

WHERE LastName like @LName + '%'

CREATE INDEX LastName_idx ON Users(LastName)

Consider what happens when a user executes the query with 'B' as a parameter:


EXEC sp_Test 'B'


If the table contains numerous records that have a last name beginning with the letter B, the optimizer is likely to perform a table scan. Now consider what happens when a user executes the query with 'BALA' as a parameter:

EXEC sp_Test 'BALA'


If the table contains only a few records that have a last name equal to BALA, the optimizer may use an index.
As you can see, the optimizer chooses different query plans based on the value of the parameter passed into the stored procedure. The problem arises when the first query plan is stored in memory. 


By default, different users receive the same query plan based on the first execution of the stored procedure. If the first user of the stored procedure passed 'B' as a parameter, the query plan would be very inefficient for other users who specify 'BALA' as a parameter.


To avoid this problem, use the WITH RECOMPILE option when you execute the procedure. This forces the procedure to regenerate its query plan, as in the following:


EXEC  ... WITH RECOMPILE


The revised query plan is available only for the current execution of the procedure. Subsequent executions of the procedure without the recompile option revert back to the old query plan.
Additionally, you can use the WITH RECOMPILE option when you create the procedure, as in the following:


CREATE PROCEDURE ... WITH RECOMPILE


This option forces the optimizer to recompile the query plan each time the stored procedure is executed.

Questions: 

1. Which of the following option lets your stored procedure recompile each time when it is executed? Choose all that apply.

A. Add the RECOMPILE query hint to one of the stored procedure statements.
B. Include the WITH RECOMPILE option in stored procedure’s definition.
C. Include WITH RECOMPILE option when you run the stored procedure.
D. Use the sp_recompile system stored procedure.


2. Automatic recompilation occurs whenever the following changes happen. Choose all that apply.

A. Executing an ALTER TABLE statement.
B. Creating or dropping indexes.
C. Executing UPDATE STATISTICS.
D. Table data modifications.


Show Answers:

1. B, C.
2. A, B, C, D

Hide Answers (X).

Inviting Speakers for Microsoft Products & Technologies

Dear IT Professional, In past you might have attended Virtual Tech Days, Community Tech Days, Webcasts, User group meets & many other community events where you get to learn a good deal on Microsoft products and technologies like Windows, Office, SQL Server, SharePoint, BizTalk .NET and many other fascinating technologies. Peopleware India has organized many such events in association with SQLServerGeeks & regional user groups like DelhiITPro, MumbaiITPro, BITPro, etc. We at Peopleware India are now in the process of organizing the next level of similar community events across India and you as a budding speaker are invited to deliver technical sessions at these events. As a community speaker with Peopleware India, you will get an opportunity to showcase your technical expertise to a wide variety of audience. You will get an opportunity to speak at events and lead online webcasts. You will also have the leverage of  promoting yourself, your blogs and your articles. As a speaker with Peopleware India, you will be noticed by Microsoft India executives and you may possibly become a recipient of many community awards like MVP, Community contributor, etc. Peopleware India will provide you good support in form of speaker resources and other assistance that you may require. In an nutshell, this is an opportunity to rise to the next level and be known to your peers & IT audience pan-India. Above all, this is your opportunity to give back something to the society in form of education. We are looking for promising speakers from all parts of India including Mumbai, Pune, Bangalore, Chennai, Kolkata, Noida, Gurgaon, Delhi, Ahmadabad, Hyderabad and many other cities. If you specialize in any Microsoft product or technology this is the place to be. As a speaker, you will be required to deliver sessions on your area of expertise. If you are interested in exploring further, reply to this email with the following details: Name: City: Technical expertise: Cell number: Email id: Have you been a speaker before? (If ye, give details): How much do you rate yourself out of 10 in presentation skills?: How much do you rate yourself out of 10 in your area of expertise?: Company (optional): We look forward to hear from you. Thank you for your patience in reading this. Please forward this mail to your colleagues & friends, in case you think they might be interested. Best Regards Peopleware India Community Team www.peoplewareindia.com (Please note that this is a community initiative by Peopleware India. This is not a job opening. There is no commercial aspect to any part of this communication. This is all about serving the society. You have received this communication since you might have attended any of our technical events in the past. Please also note that you will not receive any more “Inviting Speakers” related emails thus unsubscribing is not necessary)

Microsoft Virtual TechDays - 22nd - 24th Nov

Unable to view the update? click here 80 Deep Technical Sessions across 3 days Dedicated tracks for Architects, Software Developers / Project Managers, Infrastructure Managers / Professionals andEnterprise Developers.   Block your calendar and tune in for the 10th edition of Virtual TechDays between 22nd and 24th November, 2010. Get trained on some of the key technology domains that you work on as a part of your day-to-day work. Key topics to be covered include:   Client Development Mobile Application Development Web Application Development Cloud Computing Database Development & Management Collaboration & Productivity Security & Identity  Virtualization   Remember, apart from the insightful sessions that you can attend, it’s also a great opportunity for you to interact and have your questions answered by the people who build and work on these technologies. The event is absolutely FREE. So, why wait?

Download SQL Server ebooks for free

You can download the following ebooks and enhance your SQL knowledge.

SQL Server Team-Based Development

By Phil Factor, Grant Fritchey, Alex Kuznetsov, and Mladen Prajdić

This book shows how to use of a mixture of home-grown scripts, native SQL Server tools, and tools from the Red Gate SQL Toolbelt, to successfully develop database applications in a team environment, and make database development as similar as possible to "normal" development.

Following are the topics covered in this book:

Chapter 1: Writing Readable SQL

Chapter 2: Documenting your Database

Chapter 3: Change Management and Source Control

Chapter 4: Managing Deployments

Chapter 5: Testing Databases

Chapter 6: Reusing T-SQL Code

Chapter 7: Maintaining a Code Library

Chapter 8: Exploring your Database Schema

Chapter 9: Searching DDL and Build Scripts

Chapter 10: Automating CRUD

Chapter 11: SQL Refactoring

http://www.red-gate.com/specials/Ebooks/teamdevelopment.htm

SQL Server Tacklebox

Essential Tools and Scripts for the day-to-day DBA

By Rodney Landrum

The book focuses on these topics:

1.      Installing SQL Server

2.      SQL Server Documentation

3.      How to Move Data Into and Out of SQL Server

4.      How to Manage Data Growth

5.      How to Use System Tables and DMVs to Investigate What SQL Server is Doing

6.      Monitoring and Notification

7.      Security Monitoring

8.      Finding and Fixing Data Corruption

http://www.red-gate.com/products/sql_Monitor/offers/monitor_tacklebox.htm

"Brad's Sure Guide to SQL Server Maintenance Plans"

Brad’ Sure Guide to SQL Server Maintenance Plans is now available as a free, 269 page PDF eBook. The book is designed for part-time or novice DBAs who want to learn how to properly create Maintenance Plans using the tools that come with SQL Server Management Studio (SSMS) in SQL Server 2005/2008.

These two tools include:

• Maintenance Plan Wizard – a Wizard that steps the user through the process of setting up basic Maintenance Plans, with limited options.
• Maintenance Plan Designer – a drag-and-drop GUI interface in SSMS that facilitates the design and creation of more flexible, customizable maintenance plans.

http://www.red-gate.com/products/SQL_Backup/offers/backup_maintenance_plans.htm?utm_source=sqlaloha&utm_medium=blogpost&utm_content=maintenance201001&utm_campaign=sqlbackup

Defensive Database Programming

By Alex Kuznetsov

The goal of Defensive Programming is to produce resilient code that responds gracefully to the unexpected.

To the SQL Server programmer, this means T-SQL code that behaves consistently and predictably in cases of unexpected usage, doesn't break under concurrent loads, and survives predictable changes to database schemas and settings.

Inside this book, you will find dozens of practical, defensive programming techniques that will improve the quality of your T-SQL code and increase its resilience and robustness.

http://www.red-gate.com/specials/Ebooks/defensive_database_programming.htm

Introducing Microsoft SQL Server 2008 R2

By Ross Mistry and Stacia Misner!

The book contains 10 chapters and 216 pages, like so:

PART I   Database Administration

CHAPTER 1   SQL Server 2008 R2 Editions and Enhancements 3
CHAPTER 2   Multi-Server Administration 21
CHAPTER 3   Data-Tier Applications 41
CHAPTER 4   High Availability and Virtualization Enhancements 63
CHAPTER 5   Consolidation and Monitoring 85

PART II   Business Intelligence Development

CHAPTER 6   Scalable Data Warehousing 109
CHAPTER 7   Master Data Services 125
CHAPTER 8   Complex Event Processing with StreamInsight 145
CHAPTER 9   Reporting Services Enhancements 165
CHAPTER 10   Self-Service Analysis with PowerPivot 189

http://blogs.msdn.com/b/microsoft_press/archive/2010/04/14/free-ebook-introducing-microsoft-sql-server-2008-r2.aspx

Enjoy Reading!!!


 

Simple questions on DML and DDL Triggers

Triggers contain code that automatically runs in response to events within the database. Triggers cannot be called or executed manually. DDL trigger is a new feature in SQL Server 2005. You expect questions on this for your certification exams.

Questions:

1.       Which of the following statements are valid for using inside a trigger?

A.      INSERT

B.      ALTER DATABASE

C.      RESTORE DATABASE

D.      CREATE INDEX 

2.       You are supposed to validate data entry and return error message for invalid data. How will you achieve this with minimum effort?

A.      Implement a DML trigger

B.      Include check constraints

C.      Implement DDL trigger

D.      Create a CLR function

3.       You need to import data to a table. If some rows are failed to import due to data validation.  Then you decide to create new table to collect the failed rows. How will you achieve this?

A.      Find the failed rows using a T-SQL query and insert into new table.

B.      Create a DML INSTEAD OF trigger to insert the failed rows to new table.

C.      Create a DML AFTER trigger to insert the failed rows to new table.

D.      Create a DDL AFTER trigger to insert the failed rows to new table.

4.       You are supposed to track who drops the table, procedures, views, databases. What will you do for that?
             A.      Check the application event log.
             B.      Create a DDL trigger.
             C.      Create a DML trigger.
             D.      Use System Stored Procedures.

 

5.       What type of trigger is the following code represents?

CREATE TRIGGER trgTest

ON DATABASE

FOR DROP_TABLE, ALTER_TABLE

AS

PRINT 'Tables cannot be dropped'

ROLLBACK;

A.      DML AFTER trigger.

B.      DML INSTEAD OF trigger.

C.      DDL trigger – Server scoped.

D.      DDL trigger-Database Scoped.

Show Answers:

1. A
2. A
3. B
4. B
5. D


Hide Answers (X).