Thursday, December 23, 2010

SQL Scripts Manager a free tool from Red Gate



Red Gate's new "SQL Scripts Manager" is available for free download. 


This tool contains over 28 scripts written by expert DBAs, SQL Server MVPs, and Red Gate's SQL developers., to help you streamline monotonous administration and scripting tasks. 


You can easily customize this tool for using your own script.


You can view the description about scripts used, script source and author information.


Download SQL Scripts Manager now and start benefitting from the collective wisdom of these experts – and sharing your own scripts with the SQL Server community – today.


This will be really a very useful and handy tool for DBAs.










The following are the scripts available:

  1. SQL FileGroup SpaceUsed  
  2. Monitoring Plan Cache    
  3. CPU Utilization    
  4. IO Stalls    
  5. Top Resource Waits    
  6. Buffer Cache Hit Ratio    
  7. Object Read Write Ratio    
  8. Top Cpu Intensive Queries    
  9. Database Backup    
  10. Database Integrity Check    
  11. Deploy Maintenance Scripts    
  12. Index Maintenance    
  13. CallProc Template    
  14. InsertInto Template    
  15. Select From Table    
  16. Table Valued Function Template    
  17. Update Templates    
  18. Ping    
  19. Full And Log Backups In Past X Days    
  20. Last Good Database Backup    
  21. Read Error Logs The DBA Way    
  22. Blocking Transactions    
  23. Investigating Fragmented Indexes    
  24. Who Is Running What    
  25. Get Hit Ratio  
  26. Get Partitioned DBs  
  27. Get Tables With No Clustered Index
  28. Get Version And Uptime  

Tuesday, December 21, 2010

SQL Server 2005 Top 10 Features for Database Administration

Feature
Description
Database Mirroring
Extend log shipping capabilities with the database mirroring solution. You will be able to use database mirroring to enhance availability of your SQL Server systems by setting up automatic failover to a standby server.
Online Restore
With SQL Server 2005, database administrators are able to perform a restore operation while an instance of SQL Server is running. Online restore improves the availability of SQL Server because only the data being restored is unavailable; the rest of the database remains online and available.
Online Indexing Operations
The online index option allows concurrent modifications (updates, deletes, and inserts) to the underlying table or clustered index data and any associated indexes during index data definition language (DDL) execution. For example, while a clustered index is being rebuilt, you can continue to make updates to the underlying data and perform queries against the data.
Fast Recovery
A new faster recovery option improves availability of SQL Server databases. Administrators can reconnect to a recovering database after the transaction log has been rolled forward.
Standards-based Information Access
Any object, data source, or business intelligence component can be exposed using standards-based protocols such as SOAP and HTTP—eliminating the need for a middle-tier listener, such as IIS, to access a Web services interface that is exposed by SQL Server 2005.
SQL Server Management Studio
SQL Server 2005 includes SQL Server Management Studio, a new integrated suite of management tools with the functionality to develop, deploy, and troubleshoot SQL Server databases, as well as enhancements to previous functionality.
Dedicated Administrator Connection
SQL Server 2005 provides a dedicated administrator connection that administrators can use to access a running server even if the server is locked or otherwise unavailable. This capability enables administrators to troubleshoot problems on a server by executing diagnostic functions or Transact-SQL statements.
Snapshot Isolation
Snapshot Isolation (SI) level is provided at the database level. With SI, users can access the last committed row using a transitionally consistent view of the database. This capability provides greater scalability.
Data Partitioning
Data partitioning is enhanced with native table and index partitioning that enables efficient manageability of large tables and indexes.
Replication Enhancements
For distributed databases, SQL Server 2005 provides comprehensive schema change (DDL) replication, next-generation monitoring capabilities, built in replication from Oracle to SQL Server, merge replication over https, and significant merge replication scalability and performance improvements. Additionally, the peer-to-peer transactional replication feature improves support for data scale out using replication.

SQL Server 2005 Top 10 Features for Development

Feature
Description
Hosted CommonLanguage Runtime
With SQL Server 2005 developers can create database objects using familiar languages such as Microsoft Visual C# .NET and Microsoft Visual Basic .NET. Developers can also create two new objects—user-defined types and aggregates.
Native XML Support
Native XML data can be stored, queried, and indexed in a SQL Server database—allowing developers to build new classes of connected applications around Web services and across any platform or device.
ADO.NET version 2.0
From new support for SQL Types to Multiple Active Result Sets (MARS), ADO.NET in SQL Server 2005 evolves dataset access and manipulation to achieve greater scalability and flexibility.
Security Enhancements
The security model in SQL Server 2005 separate users from objects, provides fine-grain access, and enables greater control of data access. Additionally, all system tables are implemented as views, providing more control over database system objects.
Transact-SQL Enhancements
SQL Server 2005 provides new language capabilities for developing scalable database applications. These enhancements include error handling, recursive query capabilities, relational operator PIVOT, APPLY, ROW_NUMBER and other row ranking functions, and more.
Reliable Messaging for Asynchronous Applications
Service Broker is a robust messaging infrastructure that provides reliable transactional delivery of critical messages between servers—with the scalable high-performance that is expected with asynchronous queuing.
Visual Studio Integration
Tight integration with Microsoft Visual Studio and the .NET Framework streamlines development and debugging of data-driven applications. Developers can build database objects, such as stored procedures, using any .NET language and can seamlessly debug across .NET and Transact-SQL (TSQL) languages.
Web Services
With SQL Server 2005 developers can develop Web services in the database tier, making SQL Server a hypertext transfer protocol (HTTP) listener and providing a new type of data access capability for Web services-centric applications.
Embedded Reports
Use client-side reporting controls to embed real-time reports into an application at design time.
Full-Text Search Enhancements
SQL Server 2005 supports rich, full-text search applications. Cataloging capabilities provide greater flexibility over what is cataloged. Query performance and scalability have been improved dramatically, and new management tools provide greater insight into the full-text implementation.

Monday, December 13, 2010

Questions on SQL JOINS

A join will allow us to view data from related tables in a single result set.

INNER JOIN:
  • In an inner join, Only rows with values satisfying the join condition in the common column are displayed.
  • It is default join. So 'INNER' keyword can be ignored.

OUTER JOIN:
  • In an outer join, rows are returned even when there are no matches through the JOIN criteria on the second table.
  • Outer join is of three types: Left, Right, Full outer joins

LEFT OUTER JOIN:
  • A left outer join or a left join returns results from the table mentioned on the left of the JOIN keyword irrespective of whether it finds matches or not.
  • If the ON clause matches 0 records from table on the right, it will still return a row in the result—but with NULL in each column.

RIGHT OUTER JOIN:
  • A right outer join or a right join returns results from the table mentioned on the right of the JOIN keyword irrespective of whether it finds matches or not.
  • If the ON clause matches 0 records from table on the left, it will still return a row in the result—but with NULL in each column.

FULL OUTER JOIN:
  • A full outer join will combine results of both left and right outer join. Hence the records from both tables will be displayed with a NULL for missing matches from either of the tables.

CROSS JOIN:
  • A cross join also known as cartesian product between two tables joins each row from one table with each row of the other table.
  • A cross join does not include 'on' clause.
Two tables are created and values are inserted using following SQL statements.

CREATE TABLE A (ID numeric)

INSERT INTO A VALUES(1)
INSERT INTO A VALUES(2)
INSERT INTO A VALUES(3)

CREATE TABLE B (ID numeric)

INSERT INTO B VALUES(4)
INSERT INTO B VALUES(5)
INSERT INTO B VALUES(6)

Using the above tables, try to answer the following questions.

Questions:

1. What will be output of following query?


SELECT A.* from A inner join B on A.ID=B.ID

(A) 1, 2, 3, NULL, NULL, NULL
(B) 1, 2, 3
(C) NULL, NULL, NULL
(D) 0 Rows


2. What will be output of following query?

SELECT A.* from A left outer join B on A.ID=B.ID
(A) 1, 2, 3, NULL, NULL, NULL
(B) 1, 2, 3
(C) NULL, NULL, NULL
(D) 4, 5, 6


3. What will be output of following query?


SELECT A.* from A right outer join B on A.ID=B.ID

(A) 1, 2, 3, NULL, NULL, NULL
(B) 1, 2, 3
(C) NULL, NULL, NULL
(D) 4, 5, 6


4. What will be output of following query?


SELECT A.* from A full outer join B on A.ID=B.ID

(A) 1, 2, 3, NULL, NULL, NULL
(B) 1, 2, 3
(C) NULL, NULL, NULL
(D) 4, 5, 6


5. What will be output of following query?


SELECT A.* from A cross join B

(A) 1, 2, 3, NULL, NULL, NULL
(B) 1, 2, 3
(C) NULL, NULL, NULL
(D) 1, 2, 3, 1, 2, 3, 1, 2, 3

Show Answers:

Tuesday, December 7, 2010

Questions on Choosing an Authentication Mode

When you install SQL Server 2005, the database engine can be set to any of two types of Authentication modes, Windows Authentication mode and Mixed mode.

Windows Authentication Mode:

  • It is default authentication mode.
  • It provides high level of security.
  • It mandates that only users who are previously authenticated to windows OS can connect to SQL Server.

Mixed Authentication Mode:

  • Client applications submits user name and password to connect to SQL Server.
  • When using 'Mixed mode' you can use either 'Windows authentication' or 'SQL Server authentication' to connect to SQL Server.
  • Non-windows users (unix, macintosh) use this mode.


If Windows Authentication mode is selected during installation, the sa login is disabled. If you later change authentication mode to Mixed mode, the sa login remains disabled. To enable the sa login:
Execute the following statements to enable the sa password and assign a password.

ALTER LOGIN sa ENABLE;
GO
ALTER LOGIN sa WITH PASSWORD = '';
GO


Adhering to Password Policies:
To ensure SQL login security, SQL Server 2005 lets you to use AD's password policy and password complexity. You can do the following.

  • You can force password policies.
  • You can use password complexity.
  • You can force password expiration.
  • You can force users to change their password at next logon.

Passwords entered on the Authentication page must meet strong password policy requirements. Strong passwords are not readily guessed by any one, and are not easily hacked by using a computer program.

Follow these guidelines when you set passwords:

1. Strong passwords cannot use prohibited conditions or terms, including:

  • A blank or NULL condition
  • "Password"
  • "Admin"
  • "Administrator"
  • "sa"
  • "sysadmin"

2. A strong password cannot use the following terms associated with the installation computer:

  • The name of the user currently logged onto the computer.
  • The computer name.

3. A strong password must be at least six characters in length and satisfy at least three of the following four criteria:

  • It must contain uppercase letters.
  • It must contain lowercase letters.
  • It must contain numbers.
  • It must contain non-alphanumeric characters – e.g., #, %, or ^.



Questions:

1. You are tasked to implement a password policy for users connecting to your database. You must also provide access to non-windows clients. Which authentication modes you will use?
(a) Windows Authentication mode.
(b) Mixed Authentication mode.

2. Which of the following are true when talking about SQL Server 2005 password policies?
(a) You can force password policies.
(b) You can use password complexity.
(c) You can force password expiration.
(d) You can force users to change their password at next logon.

Show Answers:

Questions on Default, Named and Multiple instances

SQL Server 2005 supports to have multiple instances on a single server. Each instance within a computer runs independently of other instances. You can have only one default instance and others are named instances.

When can I use multiple instance?

  • When testing multiple versions of SQL Server on same computer.
  • When testing service packs and development databases and applications.
What is meant by a Default Instance?

During SQL Server installation, the setup acquires the hostname of the server. So you can have only one default instance on a computer. All clients can connect to the instance using the hostname of the computer.

What are Named Instances?
Named instances are the names that we provide during installation. You can have many number of named instances on a single computer.

For example, if your computer hostname is MCITP and you are providing a name "certification" during installation, then all clients will connect to this instance with MCITP\certification as server name.

Following points are to be considered when creating a named instance.
  • Instance names are not case sensitive.
  • Instance names are limited to 16 characters.
  • Instance names cannot contain the terms "Default," "MSSQLServer," or other reserved keywords.
  • The first character in the instance name must be a letter or an underscore (_).
  • Subsequent characters can be letters defined by the Unicode Standard 2.0, decimal numbers from Basic Latin or other national scripts, the dollar sign ($), or an underscore (_).
  • Embedded spaces or other special characters are not allowed in instance names; nor are the backslash (\), comma (,), colon (:), semi-colon (;), single quote ('), ampersand (&), or at sign (@).

By default, the first time you install SQL Server 2005 on a computer, Setup specifies the installation as the default instance; however, you can choose to install SQL Server as a named instance without installing the default instance first.


The following table shows the number of instances supported for each instance in the different editions of SQL Server 2005.

SQL Server 2005 edition
Database Engine instances
Analysis Services instances
Reporting Services instances
Enterprise Edition
Developer Edition
50
50
50
Standard Edition
Workgroup Edition
Express Edition
16
16
16

It is important point to consider that all components (SSAS/SSIS) of SQL Server that share the same instance name must have same version, edition, platform (16 bit/32 bit) and language.




Questions:


1. How many default instances you can have on a single SQL Server?

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

Show Answers:


Sunday, December 5, 2010

Simple Questions on SQL Server Editions




SQL Server 2005 installation requirements may differ depending on your application needs.
The different editions of SQL Server 2005 have the unique performance, price requirements and will suit for different scenarios. It is your responsibility to decide among editions of SQL Server 2005 before you install.
This image can help you understand how to make the best choice among the editions and components available in SQL Server 2005.

Questions:


1. Which of the following editions of SQL Server 2005 can support Indexed Views? Choose all that apply.
(A) Enterprise Edition
(B) Developer Edition
(C) Standard Edition
(D) Workgroup Edition
(E) Express Edition

2. You have given a Server with Windows 2003 operating system, 4 processors and 3 GB of RAM. What editions of SQL Server 2005 will you install on the server? Choose all that apply.
(A) Enterprise Edition
(B) Developer Edition
(C) Standard Edition
(D) Workgroup Edition
(E) Express Edition

3. Your customer requests you to develop a small web application to record walk-in interview details. Your will develop the application along with a small database and to be shipped to customer free of cost. Which edition of SQL Server 2005 will you choose?
(A) Enterprise Edition
(B) Developer Edition
(C) Standard Edition
(D) Workgroup Edition
(E) Express Edition

4. Which of the following editions of SQL Server 2005 NOT supported for use in a production environment?
(A) Enterprise Edition
(B) Developer Edition
(C) Standard Edition
(D) Workgroup Edition
(E) Express Edition

5. You have given a Server with Windows 2003 operating system with 8 processors. What editions of SQL Server 2005 will you install on the server that can utilize all the 8 processors?
(A) Enterprise Edition
(B) Developer Edition
(C) Standard Edition
(D) Workgroup Edition
(E) Express Edition

6. You suspect that a particular table can grow large and historical data may be archived. To do this you may implement table partitioning. Which of the following editions of SQL Server 2005 can support table partitioning?
(A) Enterprise Edition
(B) Developer Edition
(C) Standard Edition
(D) Workgroup Edition
(E) Express Edition

Show Answers:


Wednesday, December 1, 2010

Sequel QB Mag Volume 1 - E-Book Download
























I have created e-book version of all the articles published till 30-Nov-2010. You can download the e-book and use for your offline reading.

Download E-Book:

Topics Covered:



  1. Triggers 1
  2. Stored Procedures 3
  3. Recompiling SP 4
  4. SELECT Statement 6
  5. Transactions 7
  6. XML 8
  7. System Databases 11
  8. Collation settings 13
  9. SQL injection 14
  10. RAID systems 17
  11. Processor Affinity19
  12. Patch Management 21

Tuesday, November 30, 2010

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:

Monday, November 29, 2010

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:

Friday, November 26, 2010

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:



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

Wednesday, November 24, 2010

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: