SQL Doctor 3.7

SQL Doctor 3.7 – Documentation

Table of Contents
1

Table of Contents................................................................................................... 22

SQL Doctor home ................................................................................................... 93

SQL Doctor Release Notes ................................................................................... 10

3.1

New features and fixed issues ................................................................................................... 10

3.1.1

3.7 New features........................................................................................................................................................10

3.1.1.1 General ......................................................................................................................................................................10
3.1.2

3.7 Fixed issues..........................................................................................................................................................10

3.1.2.1 Analysis......................................................................................................................................................................10
3.1.2.2 Optimization Scripts .................................................................................................................................................10

3.2

Previous features and fixed issues ............................................................................................ 11

3.2.1

3.6 New features........................................................................................................................................................11

3.2.1.1 Data Collection..........................................................................................................................................................11
3.2.1.2 Microsoft SQL Azure and Amazon RDS compatibility .............................................................................................11
3.2.2

3.6 Fixed issues..........................................................................................................................................................11

3.2.2.1 Licensing....................................................................................................................................................................11
3.2.2.2 Deadlock Report .......................................................................................................................................................11
3.2.2.3 Blocking Recommendation ......................................................................................................................................11
3.2.2.4 Analyzing a server .....................................................................................................................................................123.2.3

3.5 New features........................................................................................................................................................123.2.3.1 Reviewed and enhanced recommendations...........................................................................................................123.2.3.2 Full support for SQL Server 2014..............................................................................................................................123.2.3.3 SQL Server 2016 experimental support ...................................................................................................................123.2.3.4 Help documentation.................................................................................................................................................12
3.2.4
3.2.4.1
3.2.5

3.5 Fixed issues..........................................................................................................................................................12
General................................................................................................................................................................12
3.1 New features........................................................................................................................................................13

3.2.5.1 SQL Doctor no longer part of the SQL Toolbox .......................................................................................................13
3.2.5.2 Check for updates to SQL Doctor .............................................................................................................................13
3.2.5.3 SQL Server 2012 support ..........................................................................................................................................13

SQL Doctor 3.7 – Documentation

3.2.6

3.1 Fixed issues..........................................................................................................................................................13

3.2.7

3.0 New features........................................................................................................................................................14

3.2.7.1 Redesigned user interface ........................................................................................................................................14
3.2.7.2 New Server Overview ................................................................................................................................................14
3.2.7.3 New analysis for Query Plan Statistics.....................................................................................................................14
3.2.7.4 New analysis for Wait Stat Statistics........................................................................................................................14
3.2.7.5 New analysis for Session Statistics ..........................................................................................................................14
3.2.7.6 More crucial server information ...............................................................................................................................14
3.2.7.7 SQL Server 2012 compatible with experimental support.......................................................................................14
3.2.8

3.0 Fixed issues..........................................................................................................................................................15

3.2.9

2.0.1 New features.....................................................................................................................................................15

3.2.10 2.0 New features........................................................................................................................................................15
3.2.10.1 New backup recommendations ...............................................................................................................................15
3.2.10.2 New wait statistics recommendations ....................................................................................................................15
3.2.10.3 New index recommendations ..................................................................................................................................15
3.2.10.4 Undo scripts ..............................................................................................................................................................16
3.2.10.5 Selective category analysis ......................................................................................................................................16
3.2.10.6 Recommendation state persistence ........................................................................................................................16
3.2.10.7 Diagnose queries.......................................................................................................................................................16
3.2.11 1.1.1 New features.....................................................................................................................................................16
3.2.11.1 System Specific Resources .......................................................................................................................................16
3.2.12 1.1.1 Fixed issues.......................................................................................................................................................16

3.3

Known issues.............................................................................................................................. 17

3.3.1

3.7 Known Issues.......................................................................................................................................................17

3.3.2

Recommendations....................................................................................................................................................17

3.3.3

Known issues in previous releases...........................................................................................................................17

3.4

Recommended IDERA Solutions ............................................................................................... 17

4

Welcome to SQL Doctor....................................................................................... 19

4.1

What is SQL Doctor?................................................................................................................... 19

4.2

Find answers .............................................................................................................................. 20

4.2.1

Contact IDERA ...........................................................................................................................................................20

4.2.2

Using Help .................................................................................................................................................................21

SQL Doctor 3.7 – Documentation

4.2.3

Document conventions ............................................................................................................................................21

4.3

About IDERA ............................................................................................................................... 22

4.3.1

IDERA products .........................................................................................................................................................22

4.3.2

Legal notice ...............................................................................................................................................................23

4.3.2.1 Trademark .................................................................................................................................................................23
4.3.2.2 Copyright ...................................................................................................................................................................23
4.3.2.3 Use of the Software...................................................................................................................................................23
4.3.2.4 Use of web site information ? ...................................................................................................................................23
4.3.2.5 Warranties and Disclaimers; Liability Limitations...................................................................................................24
4.3.2.6 Submissions ..............................................................................................................................................................24
4.3.2.7 Governing Law and Jurisdiction ? ............................................................................................................................25
4.3.2.8 Export Control Laws..................................................................................................................................................25
4.3.2.9 United States Government Rights............................................................................................................................25

5

Get started............................................................................................................ 27

5.1

Product requirements ............................................................................................................... 27

5.2

How to install SQL Doctor.......................................................................................................... 29

5.2.1

SQL Doctor and OLE automation .............................................................................................................................34

5.2.2

Check for SQL Doctor updates .................................................................................................................................35

5.3

Configure your deployment ...................................................................................................... 35

5.3.1

Add Server .................................................................................................................................................................35

5.3.1.1 Add a SQL Server instance to analyze performance issues ....................................................................................35
5.3.2

Remove Server ..........................................................................................................................................................35

5.3.2.1 Remove server...........................................................................................................................................................35
5.3.3

Analyze Server...........................................................................................................................................................35

5.3.3.1 Schedule an analysis ................................................................................................................................................35
5.3.4

Diagnose Queries ......................................................................................................................................................36

5.3.4.1 Running Diagnose Queries .......................................................................................................................................36
5.3.5

Add a server to analyze.............................................................................................................................................36

5.3.5.1 Access the Add Server window.................................................................................................................................36
5.3.5.2 Add a server to SQL Doctor.......................................................................................................................................36
5.3.6

Change general settings ...........................................................................................................................................37

5.3.6.1 Access the General Settings tab of the Server Settings window ............................................................................37

SQL Doctor 3.7 – Documentation

5.3.6.2 Available settings on the General Settings tab .......................................................................................................37
5.3.6.3 Configure Advanced Connection Settings ...............................................................................................................38
5.3.7

Block recommendations ..........................................................................................................................................39

5.3.7.1 Access the Block Recommendations tab of the Server Settings window..............................................................40
5.3.7.2 Block and unblock your recommendations ............................................................................................................40
5.3.7.3 Block objects .............................................................................................................................................................40
5.3.8

Block databases ........................................................................................................................................................40

5.3.8.1 Access the Block Databases tab of the Server Settings window ............................................................................41
5.3.8.2 Block and unblock your databases..........................................................................................................................41
5.3.9

Remove a server from SQL Doctor ...........................................................................................................................41

5.3.9.1 Access the Remove Server window..........................................................................................................................41
5.3.9.2 Remove a server from SQL Doctor ...........................................................................................................................41
5.3.10 Schedule analysis......................................................................................................................................................425.3.10.1 Access the Schedule Analysis window.....................................................................................................................425.3.10.2 Schedule an analysis ................................................................................................................................................425.3.11 Set the Server Overview warning thresholds ..........................................................................................................43
5.3.11.1 Access the Server Overview Defaults window .........................................................................................................43
5.3.11.2 Use the Server Overview Defaults window settings................................................................................................44

5.4

Manage your SQL Doctor license............................................................................................... 46

5.4.1

Access the licensing console ....................................................................................................................................46

5.4.2

About the trial license...............................................................................................................................................46

5.4.3

Register your software..............................................................................................................................................46

5.4.3.1 Access the Register Your Software window.............................................................................................................47
5.4.3.2 Use the Register Your Software window..................................................................................................................47
5.4.4

Choose how to activate your license .......................................................................................................................48

5.4.4.1 Access the Activate Your Software window.............................................................................................................48
5.4.4.2 Use the Activate Your Software window..................................................................................................................48
5.4.5

Activate your license manually ................................................................................................................................49

5.4.5.1 Access the Activate Manually window .....................................................................................................................49
5.4.5.2 Manually activating your license..............................................................................................................................49
5.4.5.3 Use the Activate Manually window ..........................................................................................................................49
5.4.6

Deactivate your license.............................................................................................................................................50

5.4.6.1 Access the Deactivate License window....................................................................................................................51
5.4.6.2 Use the Deactivate License window ........................................................................................................................51

SQL Doctor 3.7 – Documentation

5.4.7

Extend your trial period ............................................................................................................................................51

5.4.7.1 Access the Extend License Use Period window.......................................................................................................51
5.4.7.2 Use the Extend License Use Period window............................................................................................................52

6

Analyze your SQL Server instance....................................................................... 53

6.1

Access the Analysis Wizard ........................................................................................................ 53

6.2

Analyze a SQL Server instance .................................................................................................. 53

6.3

View your analysis results.......................................................................................................... 56

6.4

Select performance categories ................................................................................................. 56

6.4.1

Access the Analysis Wizard .......................................................................................................................................57

6.4.2

All categories .............................................................................................................................................................57

6.4.2.1 Disaster Recovery......................................................................................................................................................57
6.4.2.2 Index Optimization ...................................................................................................................................................57
6.4.2.3 Query Optimization...................................................................................................................................................57
6.4.3

Resources ..................................................................................................................................................................57

6.4.3.1 Disk ............................................................................................................................................................................57
6.4.3.2 Memory......................................................................................................................................................................58
6.4.3.3 Network .....................................................................................................................................................................58
6.4.3.4 Processor...................................................................................................................................................................58
6.4.4

Configuration ............................................................................................................................................................58

6.4.4.1 Database Configuration............................................................................................................................................58
6.4.4.2 Server Configuration.................................................................................................................................................58
6.4.4.3 Security......................................................................................................................................................................58
6.4.5

Workload ...................................................................................................................................................................58

6.4.5.1 Deadlocks ..................................................................................................................................................................58
6.4.5.2 Blocking Processes ...................................................................................................................................................58
6.4.5.3 Long Running Jobs ...................................................................................................................................................59
6.4.5.4 Open Transactions....................................................................................................................................................59
6.4.5.5 Wait Stats...................................................................................................................................................................59

6.5

Choose database or application ............................................................................................... 59

6.5.1

Access Filters .............................................................................................................................................................59

6.6

Get the analysis status............................................................................................................... 59

SQL Doctor 3.7 – Documentation

6.6.1

Analysis type..............................................................................................................................................................60

6.6.2

Start time...................................................................................................................................................................60

6.6.3

Time elapsed .............................................................................................................................................................60

6.6.4

Current status............................................................................................................................................................61

7

Resolve performance issues................................................................................ 62

7.1

Check analysis history across servers ....................................................................................... 62

7.1.1

View recommendations............................................................................................................................................62

7.1.2

Delete an analysis .....................................................................................................................................................62

7.2

Check all analyses for server ..................................................................................................... 63

7.2.1

Access the All Analyses view.....................................................................................................................................63

7.2.2

View recommendations............................................................................................................................................63

7.2.3

Delete an analysis .....................................................................................................................................................63

7.3

Check the recommendations list .............................................................................................. 64

7.3.1

Highest priority recommendation ...........................................................................................................................64

7.3.2

Lowest priority recommendation ............................................................................................................................64

7.3.3

Flag recommendations to investigate later ............................................................................................................64

7.3.4

Block a recommendation from appearing in future analyses................................................................................65

7.3.5

Export analysis results ..............................................................................................................................................65

7.3.6

Use Show me the problem and Optimize script......................................................................................................65

7.3.6.1 Show me the problem ..............................................................................................................................................65
7.3.6.2 Optimize Script .........................................................................................................................................................65
7.3.6.3 Undo Script................................................................................................................................................................65
7.3.7

Schedule the next analysis .......................................................................................................................................65

7.3.8

Implicit conversion recommendation .....................................................................................................................66

7.3.8.1 Use SQL Doctor to identify implicit conversions .....................................................................................................68

7.4

Diagnose individual queries ...................................................................................................... 69

7.4.1

Access the Diagnose Queries window......................................................................................................................69

7.4.2

Diagnose your queries ..............................................................................................................................................70

7.5

Get a quick overview of enterprise health ................................................................................ 70

7.6

Get a quick overview of server health....................................................................................... 71

7.7

Get quick findings ...................................................................................................................... 72

7.7.1

View query details and findings ...............................................................................................................................72

SQL Doctor 3.7 – Documentation

7.7.1.1 Access the Query Details window ............................................................................................................................72
7.7.2

View session data and findings ................................................................................................................................73

7.7.2.1 Access the Sessions view ..........................................................................................................................................73
7.7.3

View wait stats and findings .....................................................................................................................................73

7.7.3.1 Access the Waits view ...............................................................................................................................................73
7.7.3.2 Parallelism and CXPACKET waits .............................................................................................................................73
7.7.3.3 CXPACKET waits in your SQL Servers.......................................................................................................................75
7.7.4

View query plan stats and findings ..........................................................................................................................77

7.7.4.1 Access the Query Plan Stats view.............................................................................................................................78

7.8

Show me the problem ............................................................................................................... 78

7.8.1

Access the Show me the problem ............................................................................................................................78

7.9

Show me how to optimize performance .................................................................................. 78

7.9.1

Access the Optimize Now window ...........................................................................................................................79

7.10

Undo previously applied optimization ..................................................................................... 79

7.10.1 Access the Undo Optimization window ...................................................................................................................79

7.11

View recommendation details .................................................................................................. 79

7.11.1 Flag ............................................................................................................................................................................79
7.11.2 Block ..........................................................................................................................................................................79
7.11.3 Copy ...........................................................................................................................................................................80
7.11.4 Email ..........................................................................................................................................................................80
7.11.5 Export ........................................................................................................................................................................80
7.11.6 Use Show me the problem and Optimize Script .....................................................................................................80
7.11.6.1 Show me the problem ..............................................................................................................................................80
7.11.6.2 Optimize Script .........................................................................................................................................................80
7.11.6.3 Undo Script................................................................................................................................................................80

8

SQL Doctor PDF .................................................................................................... 81

SQL Doctor 3.7 – Documentation

SQL Doctor home
IDERA SQL Doctor is your one-stop source for identifying and resolving SQL Server performance problems. SQL Doctor
includes a robust, yet light-weight analysis engine that not only checks your SQL Server configuration for potential
problems and the health of your databases. At the end of the analysis detailed, specific recommendations are provided
that will improve your SQL Server performance.

IDERA Website | Products | Buy | Support | Community | About Us | Resources | Legal

SQL Doctor 3.7 – Documentation

SQL Doctor Release Notes
IDERA SQL Doctor provides performance tuning recommendations and health checks for your SQL Server environment.
To get a quick glimpse into the newest features, fixed issues, and known issues in this release of SQL Doctor, review thefollowing sections of the Release Notes:• Learn about key new features in this release• Review issues fixed by this release• Review previous features and fixed issues• View a list of any known issues• View a list of recommended IDERA Solutions

New features and fixed issues
SQL Doctor provides the following new features and fixed issues.

3.7 New features

GeneralSQL Doctor is able to analyze and generate recommendations for SQL Server 2017 instances running on
Windows or Linux operating systems.

3.7 Fixed issues

Analysis
SQL Doctor is able to correctly limit analysis to a single database as specified in the Analysis wizard.
Users are no longer able to remove instances that have analysis in progress.

Optimization Scripts
Optimization scripts now include schema information to ensure proper execution of the script.
Optimization scripts are no longer generating the ·The embedded resource was not found..." error.

IDERA Website | Products | Buy | Support | Community | About Us | Resources | Legal

SQL Doctor 3.7 – Documentation

Previous features and fixed issues
This build includes many fixed issues, including the following updates from previous releases.

3.6 New features

Data Collection
To collect query-related data, SQL Doctor 3.6 now supports the use of Extended Events, allowing for optimaldata collection during analysis.

Microsoft SQL Azure and Amazon RDS compatibility
SQL Doctor installation is compatible with Microsoft Windows Azure and Amazon EC2 environments.
SQL Doctor is able to connect to Microsoft SQL Azure and Amazon RDS SQL Server databases.
SQL Doctor is able to analyze and provide recommendations for SQL instances hosted by Microsoft Windows
Azure and Amazon EC2 environment.

3.6 Fixed issues

LicensingSQL Doctor has no longer issues recognizing license after Windows 10 Anniversary update.

Deadlock ReportSQL Doctor is no longer showing error while running the deadlock report.

Blocking Recommendation
SQL Doctor is no longer displaying an error when viewing the details of the recommendation addressing a
blocking condition.

IDERA Website | Products | Buy | Support | Community | About Us | Resources | Legal

SQL Doctor 3.7 – Documentation

Analyzing a server
SQL Doctor is no longer showing an error after analyzing a server. “An unhandled exception occurred whilerunning Idera SQL doctor. Specified argument was out of the range of valid values".

3.5 New features

Reviewed and enhanced recommendations
SQL Doctor 3.5 comes with new and enhanced recommendations to improve your SQL Server performance.
In addition, links within recommendations associated to online content have been reviewed and updated forthis release.

Full support for SQL Server 2014
SQL Doctor 3.5 fully supports monitoring of SQL Server 2014 instances.

SQL Server 2016 experimental support
SQL Doctor 3.5 is SQL Server 2016 compatible. This version of SQL Doctor is not certified against newer
builds of SQL Server and should not be used with these builds in a production environment. IDERA provides
experimental support while you use your installation in a testing environment to ensure the features you rely
on most are working as or better than expected.

Help documentation
SQL Doctor Help (User Guide and Release Notes) is now wiki-hosted and available at wiki.idera.com > SQL
Doctor.

3.5 Fixed issues

GeneralAn issue preventing some users with system administration permissions from adding a SQL Server instancefor analysis in SQL Doctor no longer occurs.

IDERA Website | Products | Buy | Support | Community | About Us | Resources | Legal

SQL Doctor 3.7 – Documentation

3.1 New featuresSQL Doctor no longer part of the SQL Toolbox
With this release, SQL Doctor is independent of the SQL Toolbox. New installations of SQL Doctor no longer
require you to install SQL Toolbox.

Check for updates to SQL Doctor
SQL Doctor now allows you to quickly check for updates to the product from within the user interface. You
can check for new versions of SQL Doctor by clicking Help > Check for Updates.

SQL Server 2012 support
SQL Doctor 3.1 fully supports the use of SQL Server 2012.

3.1 Fixed issues• This release fixes two broken links provided by SQL Doctor when displaying recommendations.
• Two issues preventing some users from properly activating and verifying SQL Doctor licenses in theirenvironment is fixed.
• Column sizing and alignment is improved in this release.
• Users no longer receive an unhandled object reference when attempting to view details for a
recommendation to address a blocking condition.
• The SDR-Q6 "Inconsistent procedure execution times" recommendation now appears only when avery sizable difference exists in the number of reads between executions of a stored procedure. The
recommendation informs the user that they should test to see if different stored procedureparameters are causing different execution plans.
• This release improves the recommendation provided when SQL Doctor displays "No join predicate
detected" for a query that contains explicit joins. The recommendation now displays the estimatedcost of the missing join predicate, suggests that the user adjust the query, and provides theapproximate performance improvement percentage.
• An issue with a recommendation attempting to use invalid text columns in an index no longer occurs.
• SQL Doctor now displays the correct unit of measure for the Unit Allocation Size.
• SQL Doctor now supports Page Life Expectancy counters on NUMA systems.
• The SDR-D11 "Inconsistent procedure execution times" recommendation now appears only when avery sizable difference exists in the number of reads between executions of a stored procedure. The
recommendation informs the user that they should test to see if different stored procedureparameters are causing different execution plans.

IDERA Website | Products | Buy | Support | Community | About Us | Resources | Legal

SQL Doctor 3.7 – Documentation• SQL Doctor no longer generates recommendations for its own hypothetical indexes.

3.0 New features

Redesigned user interface
A new dashboard provides a summary of all previously executed server analyses, with instant access to thecorresponding recommendations.

New Server Overview
The Server Overview displays a summary of key SQL Server metrics, warns you of issues that need to beaddressed, and provides quick findings based on your current server health.

New analysis for Query Plan Statistics
Tune your query plans by viewing real-time query plan performance metrics and quick findings along side
the T-SQL code.

New analysis for Wait Stat Statistics
Identify and fix any bottlenecks by viewing real-time statistics and quick findings for waits on your server.

New analysis for Session Statistics
Track session performance by viewing the commands, wait times, elapsed times, affected databases, andmuch more.

More crucial server information
Keep tabs on your processes, network interface cards, processors, cache, memory, physical and logical disk
performance, disk partitions and system properties.

SQL Server 2012 compatible with experimental support
SQL Doctor 3.0 is SQL Server 2012 compatible. This version of SQL Doctor is not certified against newer
builds of SQL Server and should not be used with these builds in a production environment. IDERA provides
experimental support while you use your installation in a testing environment to ensure the features you rely
on most are working as, or better than, expected.

IDERA Website | Products | Buy | Support | Community | About Us | Resources | Legal

SQL Doctor 3.7 – Documentation

3.0 Fixed issues
There are no fixed customers issues in this release.

2.0.1 New features
Improved the user experience with enhanced recommendation content and layout.

2.0 New features

New backup recommendations
The server analysis now checks the age and location of your database backups and, if needed, provides
information about how you can ensure your backup data sets are both recent and easily accessible.

New wait statistics recommendations
The server analysis now checks the following wait statistics:• CXPACKET. This typically occurs when a query is parallelized and the parallel threads are notgiven equal amounts of work. One thread may require more time to complete, which results in"cxpacket" waits for the remaining threads which are "waiting" for the longest running thread
to complete.
• ASYNC_NETWORK_IO. This typically occurs when a client application that is not efficiently
processing results from SQL Server over the network. This results in an increase inasync_network_io waits because SQL Server is waiting for the client application to process theresult set.
• PAGEIOLATCH. This typically occurs when SQL Server waits for a data page to be read fromdisk into memory. This can be an indication of contention on the same pages by concurrent
selects, inserts, updates and deletes, which can be magnified by IO subsystem performanceissues, insufficient memory, missing indexes, out of date statistics or inefficient queries.

New index recommendations
The server analysis now checks the following index configurations:• Overlapping indexes. This typically occurs when one or more indexes contain the same data insome key columns and included columns. Although these indexes are not identical,

IDERA Website | Products | Buy | Support | Community | About Us | Resources | Legal

SQL Doctor 3.7 – Documentation

maintaining these separate indexes can incur unnecessary performance overhead for data
modifications on the database.
• Non-clustered index key matches clustered index key. This typically occurs when a nonclustered index contains the same key columns as the clustered index. Although these indexes
are not technically duplicates, the non-clustered index rarely provides a data access advantage
over the clustered index. SQL Server will maintain both indexes, creating performance
overhead for data modifications which and increasing the database size.

Undo scripts
Now, when you perform a DDL or DML optimization from within SQL Doctor, you can run an undo script thatreverts any changes that were made by the original optimization script.

Selective category analysis
Easily direct SQL Doctor to a specific area of concern for analysis, such as memory, disk, network, security,
processor, and deadlocks.

Recommendation state persistence
Remember where you left off by flagging recommendations and returning to them later for follow-up.

Diagnose queries
Evaluate any query to obtain recommendations that improve its performance without running a full server
analysis1.1.1 New features

System Specific Resources
Resources included with recommendations are now applicable to the operating system and SQL Server
version being analyzed.

1.1.1 Fixed issues• The server analysis is now able to analyze sysjobhistory tables that contain a very large number ofrows.

IDERA Website | Products | Buy | Support | Community | About Us | Resources | Legal

SQL Doctor 3.7 – Documentation

Known issues
IDERA strives to ensure our products provide quality solutions for your SQL Server needs. The following known issues
are described in this section. If you need further assistance with any issue, please contact Customer Support Portal.

3.7 Known Issues

Recommendations
SQL Doctor may incorrectly generate the "No join predicate has been detected". This recommendation is
generated when the No Join Predicate warning is detected in the query execution plan.

Known issues in previous releases• When upgrading SQL Doctor, you may receive a message that your license is expired. To resolve thisissue, re-register and reactivate your existing license key. For additional information, see Manage
your SQL Doctor license. If you are unable to re-register your key, please contact IDERA Support.
• SQL Doctor may fail to work after upgrading the operating system to Windows 10.
• Average Percent Disk Time data may not be collected on Windows Server 2008 and Vista computers.
You can resolve this known WMI issue by installing a Microsoft Windows Hotfix. To apply this hotfix, go
to http://support.microsoft.com/kb/961435.
• When you used Windows Task Manager to set the processor affinity for the target SQL Server instance,
SQL Doctor is unable to generate recommendations based on the CPU core affinity settings.
• When connecting to a local instance using WMI, the security context of the logged in user is used.
Trying to supply credentials under the WMI connection options will result in an error.

Recommended IDERA Solutions
IDERA strives to ensure our products provide quality solutions for your database needs. IDERA Solutions have been
recently added to the knowledge base at Customer Support Portal.

IDERA Website | Products | Buy | Support | Community | About Us | Resources | Legal

SQL Doctor 3.7 – Documentation

Solution
Number

Title

4177

SQL Doctor finishes collecting data for a server, but returns the error message, "A complete
analysis could not performed due to errors".

4178

How can I export the results of a SQL Doctor analysis?

4179

SQL Doctor install or upgrade fails with "Error: Arithmetic operation resulted in an overflow".

IDERA Website | Products | Buy | Support | Community | About Us | Resources | Legal

SQL Doctor 3.7 – Documentation

Welcome to SQL Doctor
SQL Doctor minimizes costly server downtime by proactively alerting administrators to problems and enabling rapid
diagnosis and resolution.
This wiki helps you understand SQL Doctor characteristics and provides step-by-step help on how to use all its features.
Need help using SQL Doctor? See the following sections:• View SQL Doctor requirements• Analyze your SQL Server instance• Check the recommendations list
• View the recommendation details

What is SQL Doctor?
SQL Doctor is a revolutionary technology that analyzes the performance of SQL Server and provides recommendations
for improving performance. Furthermore, SQL Doctor targets some of the most common areas of SQL Server
performance problems, such as queries, server configuration, security, database objects, memory, and more. It is a
must-have tool for quality assurance, database administrators, analysts, developers, and individuals who are tasked
with maintaining and improving SQL Server performance.

• Provides an unparalleled analysis of SQL Server performance.
• Generates prioritized performance optimization recommendations.
• Offers access to educational, performance-tuning resources.
• Performs both real-time and scheduled health checkups.
• Stores historical analysis recommendations.

IDERA Website | Products | Buy | Support | Community | About Us | Resources | Legal

SQL Doctor 3.7 – Documentation

Find answers
This documentation set includes a comprehensive online Help system as well as additional resources that support you
as you install and use the product. You can also search the IDERA Solutions knowledge base, available at the IDERA
Customer Support.

Contact IDERA
Please contact IDERA with your questions and comments. We look forward to hearing from you. For support around theworld, please contact us or your local partner.
For a complete list of our partners, please visit IDERA Website.

IDERA Website | Products | Buy | Support | Community | About Us | Resources | Legal

SQL Doctor 3.7 – Documentation

Who?

How?

Sales

713.523.4433
1.877.GO.IDERA (464.3372)(only in the United States and Canada)

Sales Emailsales@idera.com

Support713.523.4433
1.877.GO.IDERA (464.3372)(only in the United States and Canada)

Websitewww.idera.com

Using Help
The IDERA wiki includes a comprehensive online Help system as well as additional resources that support you as you
install and use IDERA products. You can also search multiple IDERA Support Solutions, available at the Customer
Support Portal.
Additionally, IDERA helps you by providing:

24/7 technical support for critical issues.
Availability to report cases and access a web-based customer portal for update status.
Access to the Customer Support Portal where you can find FAQs, How To’s, Best Practices, andWebcasts.
You can access the IDERA Help system through the Help icon on the top right section of your window or by pressing F1
on the section where you need more information.
You can print a help topic from the wiki using the Print function in your browser.

Document conventions
IDERA documentation uses consistent conventions to help you identify items throughout the library.

IDERA Website | Products | Buy | Support | Community | About Us | Resources | Legal

SQL Doctor 3.7 – Documentation

Convention

Specifying

Bold

Window items

Italics• Book and CD titles• Variable names• New terms

Fixed Font

• File and directory names• Commands and code examples• Text typed by you

Straight brackets, as in [value]

Optional command parameters

Curly braces, as in {value}

Required command parameters

Logical OR, as in value 1 | value 2

Exclusively command parameters where only one of the options can be
specified

About IDERAIDERA is a leading provider of application and server management solutions. We have a wide variety of performance
management products for Microsoft SQL Server, and award-winning server backup solutions for both managed service
providers and enterprise customers. IDERA products install in minutes and start solving server problems immediately,
giving administrators more time, reduced overhead and expenses, and increased server performance and reliability. We
are a Microsoft Gold Certified partner, headquartered in Houston, Texas, with offices in Asia Pacific, Australia, New
Zealand, Europe, Africa, and Latin America. So we're everywhere your IT needs are.

IDERA products
Our tools are engineered to scale from managing a single server to enterprise deployments with thousands of servers.
IDERA products combine ease of use with a design that installs in minutes, configures in hours, and deploys worldwide
in days. To learn more about IDERA products, visit the IDERA Website.

IDERA Website | Products | Buy | Support | Community | About Us | Resources | Legal

SQL Doctor 3.7 – Documentation

Legal notice
IDERA, Inc. (“IDERA”) makes information and products available on this web site, subject to the following terms andconditions. By accessing this web site, you agree to these terms and conditions. IDERA reserves the right to changethese terms and conditions, and the products, services, prices, and programs mentioned in this web site at any time, at
its sole discretion, without notice. IDERA reserves the right to seek all remedies available by law and in equity for any
violation of these terms and conditions. THIS WEB SITE MAY INCLUDE TECHNICAL OR OTHER INACCURACIES. CHANGESARE PERIODICALLY MADE TO THE INFORMATION HEREIN. HOWEVER, IDERA MAKES NO COMMITMENT TO UPDATEMATERIALS ON THIS WEB SITE.

Trademark3rdrail, Appmethod, Approve, Blackfish, C#Builder, C++Builder, Codegear, Coderage, Codewright, CopperEgg,
CopperEgg logo, Data Voyager, Datasnap, DBArtisan, Delphi, Delphi Prism, Describe, Do More Now, DT/Studio,
EMBARCADERO, EMBARCADERO logo, Embarcadero All-Access, Embarcadero Rapid SQL, Embarcadero ToolCloud, ER/
Studio, Extreme Test, Firemonkey, Interbase, J Optimizer, Jbuilder, JDataStore, Jgear, Kylix, Powerstudio, Precise,
Precise Software, RADPHP, Rapid SQL, RevealStorage, SQL Boost, SQL Compliance Manager, SQL Diagnostic
Manager, SQL Mobile Manager, SQL Safe, SQL Secure Thingbase, Thingconnect, Thingpoint, Thingware, Turbo, Turbo C,
Turbo Debugger, Turbo Pascal, Two-Way-Tools, Up.Time, IDERA, and the IDERA logo are trademarks or registeredtrademarks of Idera, Inc., or its subsidiaries in the United States and other jurisdictions. All other company and productnames may be trademarks or registered trademarks of their respective companies. Elements of this web site areprotected by trade dress or other laws and may not be imitated or reproduced in whole or in part.

Copyright
The information on this web site is protected by copyright. Except as specifically permitted, no portion of this web site
may be distributed or reproduced by any means, or in any form, without IDERA’s prior written consent.

Use of the Software
The software and accompanying documentation available to download from this web site are the copyrighted work of
IDERA. Use of the software is governed by the terms of the License Agreement, which accompanies such software. If nolicense accompanies the download, the terms of the license, which accompanied the original product being updated,
will govern. You will not be able to use, download, or install any software unless you agree to the terms of such License
Agreement.

Use of web site information ?
Except as otherwise indicated on this web site, you may view, print, copy, and distribute documents on this web site
subject to the following terms and conditions:

1. The document may be used solely for informational, personal, non-commercial purposes;

IDERA Website | Products | Buy | Support | Community | About Us | Resources | Legal

SQL Doctor 3.7 – Documentation

2. Any copy of the document or portion thereof must include all copyright and proprietary notices in thesame form and manner as on the original;
3. The document may not be modified in any way; and4. IDERA reserves the right to revoke such authorization at any time, and any such use shall be
discontinued immediately upon notice from IDERA.
Documents specified above do not include logos, graphics, sounds or images on this web site or layout or design of thisweb site, which may be reproduced or distributed only when expressly permitted by IDERA.

Warranties and Disclaimers; Liability Limitations
EXCEPT AS EXPRESSLY PROVIDED OTHERWISE IN A WRITTEN AGREEMENT BETWEEN YOU AND IDERA, ALL
INFORMATION AND SOFTWARE ON THIS WEB SITE ARE PROVIDED "AS IS" WITHOUT WARRANTY OR CONDITIONS OF ANY
KIND, EITHER EXPRESS OR IMPLIED, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OR CONDITIONS OF
MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE, OR NON-INFRINGEMENT.
IDERA ASSUMES NO RESPONSIBILITY FOR ERRORS OR OMISSIONS IN THE INFORMATION OR SOFTWARE OR OTHER
DOCUMENTS WHICH ARE REFERENCED BY OR LINKED TO THIS WEB SITE.
IN NO EVENT SHALL IDERA BE LIABLE FOR ANY SPECIAL, INCIDENTAL, INDIRECT OR CONSEQUENTIAL DAMAGES OF ANY
KIND, OR ANY DAMAGES WHATSOEVER (INCLUDING WITHOUT LIMITATION, THOSE RESULTING FROM: (1) RELIANCE ON
THE MATERIALS PRESENTED, (2) COSTS OF REPLACEMENT GOODS, (3) LOSS OF USE, DATA OR PROFITS, (4) DELAYS OR
BUSINESS INTERRUPTIONS, (5) AND ANY THEORY OF LIABILITY, ARISING OUT OF OR IN CONNECTION WITH THE USE ORPERFORMANCE OF INFORMATION) WHETHER OR NOT IDERA HAS BEEN ADVISED OF THE POSSIBILITY OF SUCHDAMAGES. SOME STATES DO NOT ALLOW THE LIMITATION OR EXCLUSION OF LIABILITY FOR INCIDENTAL OR
CONSEQUENTIAL DAMAGES SO THE ABOVE LIMITATIONS OR EXCLUSIONS MAY NOT APPLY TO YOU.

Submissions
With the exception of credit card numbers for the purchase of products and services, IDERA does not want to receive
confidential or proprietary information through its web site.
Any information sent to IDERA, with the exception of credit card numbers, will be deemed NOT CONFIDENTIAL. You
grant IDERA an unrestricted, irrevocable license to display, use, modify, perform, reproduce, transmit, and distribute
any information you send IDERA, for any and all commercial and non-commercial purposes.
You also agree that IDERA is free to use any ideas, concepts, or techniques that you send IDERA for any purpose,including, but not limited to, developing, manufacturing, and marketing products that incorporate such ideas,
concepts, or techniques.
IDERA may, but is not obligated to, review or monitor areas on its web site where users may transmit or postcommunications, including bulletin boards, chat rooms, and user forums. Idera is not responsible for the accuracy of
any information, data, opinions, advice, or statements transmitted or posted on bulletin boards, chat rooms, and user
forums.
You are prohibited from posting or transmitting to or from this web site any libelous, obscene, defamatory,
pornographic, or other materials that would violate any laws. However, if such communications do occur, IDERA will
have no liability related to the content of any such communications.

IDERA Website | Products | Buy | Support | Community | About Us | Resources | Legal

SQL Doctor 3.7 – Documentation

Some companies, names, and data in this document are used for illustration purposes and may not represent realcompanies, individuals, or data.

Governing Law and Jurisdiction ?
You agree that all matters relating to your access to, or use of, this web site and these terms and conditions shall begoverned by the laws of the state of Texas. You agree and hereby irrevocably submit to the exclusive personal
jurisdiction and venue of the state courts of Texas located in Harris County, Texas, and the United States District Court
for the Southern District of Texas, with respect to such matters.
IDERA makes no representation that information on this web site are appropriate or available for use in all countries,
and prohibits accessing materials from territories where contents are illegal. Those who access this site do so on theirown initiative and are responsible for compliance with all applicable laws.

Export Control Laws
Certain IDERA products, including software, documentation, services, and related technical data, available on
the IDERA and other web sites are subject to export controls administered by the United States (including, but notlimited to, the U.S. Department of Commerce Export Administration Regulations (“EAR”)) and other countries including,controls for re-export under European Union, the Singapore Strategic Goods Control Act, and the import regulations ofother countries. Diversion contrary to U.S. or other applicable law of any IDERA product or service is prohibited. Export,
re-export or import of products and services may require action on your behalf prior to purchase and it is yourresponsibility to comply with all applicable international, national, state, regional and local laws, and regulations,
including any import and use restrictions. IDERA products and services are currently prohibited for export or re-export
to Cuba, Iran, North Korea, Sudan, Syria, or to any country then subject to U.S. trade sanctions. IDERA products andservices are prohibited for export or re-export to any person or entity named on the U.S. Department of Commerce
Denied Persons, Entities and Unverified Lists, the U.S. Department of State's Debarred List, or the U.S. Department ofTreasury's lists of Specially Designated Nationals, Specially Designated Narcotics Traffickers, or Specially DesignatedTerrorists. IDERA products and services are prohibited from use with chemical or biological weapons, sensitive nuclear
end-users, or missiles, drones or space launch vehicles capable of delivering such weapons. By downloading or using
any product from this web site, or purchasing any service, you are acknowledging that you have read and understood
this notice and agree to comply with all applicable export control laws. You are also representing that you are notunder the control of, located in, or a resident or national of any prohibited country, and are not a prohibited person orentity. This notice is not intended to be a comprehensive summary of the export laws that govern the products andservices. It is your responsibility to consult with a legal adviser to ensure compliance with applicable laws.

United States Government Rights
All IDERA products and publications are commercial in nature. The software, publications, and software
documentation available on this web site are "Commercial Items", as that term is defined in 48 C.F.R.§2.101, consistingof "Commercial Computer Software" and "Commercial Computer Software Documentation", as such terms are definedin 48 C.F.R. §252.227-7014(a)(5) and 48 C.F.R. §252.227-7014(a)(1), and used in 48 C.F.R.?12.212 and 48 C.F.R. 227.7202,
as applicable. Pursuant to 48 C.F.R. §12.212, 48 C.F.R.§252.227-7015, 48 C.F.R. §227.7202 through 227.7202-4, 48 C.F.R.
§52.227-19, and other relevant sections of the Code of Federal Regulations, as applicable, IDERA's publications,commercial computer software, and commercial computer software documentation are distributed and licensed to

IDERA Website | Products | Buy | Support | Community | About Us | Resources | Legal

SQL Doctor 3.7 – Documentation

United States Government end users with only those rights as granted to all other end users, according to the terms
and conditions contained in the license agreements that accompany the products and software documentation, andthe terms and conditions herein.
© 2003-2018 IDERA, Inc., all rights reserved.

IDERA Website | Products | Buy | Support | Community | About Us | Resources | Legal

SQL Doctor 3.7 – Documentation

Get started
SQL Doctor is designed to help you identify, understand, and fix problems with your SQL Server performance. SQL
Server performance problems are presented as ranked recommendations that focus on helping you with real problems
you are experiencing.
The following tasks are associated with using SQL Doctor:• Add the SQL Server instances you want to analyze. You can use Windows or SQL Server authentication
to connect to your SQL Server instance.
• Start a full analysis to diagnose the problems your system is experiencing.
• View a list of the SQL Doctor recommendations with short descriptions, arranged by impact. This list
can be sorted to fit your needs and even printed or exported.
• Double-click a recommendation in the recommendations list to see a detailed explanation of therecommendation and, where applicable, view or even execute an optimization script to fix the issue.
• Get a real-time overview of your enterprise health or individual server health.
• View the results from past analysis and the recommendations that are associated.
• Schedule an analysis to occur on your SQL Server instance on a daily or weekly time period.

Product requirements
You can easily and quickly install SQL Doctor on any computer that meets or exceeds the following hardware, software,and permissions requirements.
Consider the following requirements when installing SQL Doctor in a typical environment:
Type

Requirement

CPU

2.0 GHz or higher

Memory

1 GB

IDERA Website | Products | Buy | Support | Community | About Us | Resources | Legal

SQL Doctor 3.7 – Documentation

Operating System (32-bit and 64bit)• Windows Server 2003 SP2+
• Windows Server 2008 SP2+
• Windows Server 2008 R2• Windows 7 SP1+
• Windows 8• Windows 10• Windows Server 2012
• Windows Server 2012 R2• Windows 2016
• Microsoft Windows Azure• Amazon EC2

Microsoft .NET Framework.NET 4.0

Client Browser

Internet Explorer 7.0 or later

SQL Server• SQL Server 2005 - All Editions• SQL Server 2008 - All Editions• SQL Server 2008 R2 - All Editions• SQL Server 2012 - All Editions• SQL Server 2014 - All Editions• SQL Server 2016 - All Editions• SQL Server 2017 - All Editions (Windows and Linux)• Microsoft SQL Azure• Amazon RDS SQL Sever

The SQL Doctor is be able toconnect and analyze SQL Server
instances running on the following
operating systems (Windows andLinux):

Windows:• Windows Server 2003 SP2+
• Windows Server 2008 SP2+
• Windows 7• Windows 8• Windows Server 2008 R2• Windows Server 2012
• Windows Server 2012 R2• Windows 10• Windows 2016
Linux:• Red hat Enterprise 7.3• SUSE Enterprise v12 SP2• Ubuntu 16.04LTS and 16.10

IDERA Website | Products | Buy | Support | Community | About Us | Resources | Legal

SQL Doctor 3.7 – Documentation

OLE Automation Enabled

By default, SQL Doctor uses WMI to collect data for the analysis. However,you can choose to use OLE automation instead. For more information, see
SQL Doctor and OLE automation.

Permissions

SQL Doctor requires that the Windows account or SQL login used to connect
to the analyzed SQL Server instance has sysadmin privileges on thisinstance.

 SQL Doctor 3.7 supports Microsoft SQL Azure and Amazon RDS.
SQL Doctor supports the following Amazon regions:• AP-NORTH-EAST-1• AP-NORTH-EAST-2• AP-SOUTH-EAST-1• AP-SOUTH-EAST-2• CN-NORTH-1• EU-CENTRAL-1• EU-WEST-1• SA-EAST-1• US-EAST-1• US-GOVCLOUD-WEST-1• US-WEST-1• US-WEST-2• US-EAST-2

How to install SQL Doctor
You can install SQL Doctor on any computer that meets or exceeds the product requirements.
Follow these steps, to install SQL Doctor:

1. Log in with an administrator account to the computer on which you want to install SQL Doctor.
2. Download and install the SQL Doctor installation kit
3. Run the IderaSQLdoctorInstallationKit.

IDERA Website | Products | Buy | Support | Community | About Us | Resources | Legal

SQL Doctor 3.7 – Documentation

4. On the SQL Doctor Installation window of the setup program, click Next.

IDERA Website | Products | Buy | Support | Community | About Us | Resources | Legal

SQL Doctor 3.7 – Documentation

5. Review the License Agreement. To accept this License Agreement, check the I accept the terms in the
License Agreement box, and then click Next.

6. Select the destination folder. You can use the default install location or specify the path for the newdestination (Change). For your first install, we recommend using the default location. Then click Next

IDERA Website | Products | Buy | Support | Community | About Us | Resources | Legal

SQL Doctor 3.7 – Documentation

to continue with the installation.

IDERA Website | Products | Buy | Support | Community | About Us | Resources | Legal

SQL Doctor 3.7 – Documentation

7. Click Install to begin the SQL Doctor installation.

8. Click Finish to exit the IDERA SQL Doctor Setup wizard.

IDERA Website | Products | Buy | Support | Community | About Us | Resources | Legal

SQL Doctor 3.7 – Documentation

SQL Doctor and OLE automation
OLE automation allows SQL Doctor to collect system counters that are not stored in SQL Server. These system counters
include many that are vital to the SQL Doctor analysis engine. SQL Doctor requires that you enable OLE automation forthe duration of an analysis.
OLE automation is turned off by default on all servers running SQL Server 2005 or later. However, when you choose to
use OLE automation for data collection, SQL Doctor enables OLE automation for the length of the analysis, and then
immediately disable it when done.
To configure SQL Doctor to use OLE automation, click Edit > General Settings and select Advanced Connection
Settings.
For more information about how SQL Server uses OLE automation, see the Microsoft document OLE Automation
Objects in Transact-SQL.

IDERA Website | Products | Buy | Support | Community | About Us | Resources | Legal

SQL Doctor 3.7 – Documentation

Check for SQL Doctor updates
IDERA periodically releases updates to SQL Doctor. You can check for updated versions from within SQL Doctor byclicking Help > Check for Updates. This link directs you to the version control page on the IDERA Website. You can get alist of updates for each version of SQL Doctor on the Version History.

Configure your deployment
You can configure the SQL Doctor settings for each registered server. In the main windows of SQL Doctor console you
can find the following configuration options:

Add Server

Add a SQL Server instance to analyze performance issues
The Add Server window lets you register a SQL Server instance with SQL Doctor and set up the basic options
you need to get started with an analysis.

 When adding a SQL Server instance you can choose a cloud provider for your monitored SQL Server instancesthat are hosted on the cloud. SQL Doctor supports Amazon RDS and Microsoft SQL Azure.

Remove Server

Remove server
The Remove Server window lets you unregister one or more SQL Server instances from SQL Doctor.

Analyze Server

Schedule an analysis
The Schedule analysis link lets you create or edit the analysis schedule for a SQL Server instance.

IDERA Website | Products | Buy | Support | Community | About Us | Resources | Legal

SQL Doctor 3.7 – Documentation

Diagnose Queries

Running Diagnose QueriesThis window helps you save time by analyzing the queries you care about without having to wait for a fullanalysis to complete. When SQL Doctor diagnoses a query, you receive performance recommendations
without actually executing it.

Add a server to analyze
Use the Add Server window to register a SQL Server instance with SQL Doctor. By adding a server to SQL Doctor, you
can schedule when analyses are performed, as well as run analyses on an as-needed basis.
To run an analysis on your SQL Server instance, you must specify either system administration level SQL Server
credentials or be currently logged in to a Windows account with system administrator level credentials on the
SQL Server instance you want to analyze.

Access the Add Server window
To access the Add Server window, click Add Server on the SQL Doctor toolbar.

Add a server to SQL Doctor
To add a server:

1. Click Add Server on the SQL Doctor toolbar.
2. In the Add Server window; in the Server name text box, select the SQL Server instance you want to add
by clicking <Browse for more...>. Once the Browse for SQL Server window opens, select a local SQL
Server instance or select a SQL Server instance on the network. Otherwise, you can enter the name of
the SQL Sever instance you want to use in the Server name text box.
3. If this instance is being used as a production server, check Production Server.
4. If this instance has OLTP enabled, check Online Transaction Processing (OLTP).
5. If this instance is hosted by a cloud instance, check Cloud Instance, and select which service theinstance is hosted on, either Amazon RDS SQL Server or Microsoft Azure SQL Database.
6. Choose the type of authentication this server requires, and then specify the appropriate Windows user
account or SQL login credentials. By default, SQL Doctor uses the credentials of your currently logged
on Windows account to access the target server. Ensure the specified account has the required
permissions.
7. Click Add.

IDERA Website | Products | Buy | Support | Community | About Us | Resources | Legal

SQL Doctor 3.7 – Documentation

 In case you choose a Cloud Instance hosted by Amazon RDS SQL Server, ensure to enter the Access Key andSecret Key.

 Click Advanced Connection Settings on the Add Server window to configure how SQL Doctor collects
performance counters from your instance.

Change general settings
The General Settings tab of the Server Settings window lets you configure basic settings for the selected SQL Server
instance. You can choose different settings for each server that you have registered with SQL Doctor.

Access the General Settings tab of the Server Settings window
You can access the General Settings tab of the Server Settings window by clicking Edit > General Settings.

Available settings on the General Settings tab
The General Settings tab allows you to manage the following settings:
Store your analysis results
SQL Doctor stores the overall analysis summary information along with all the individual recommendations.
Additionally, vital metric data is stored and compared against subsequent analysis data to see if changes
have occurred that are negatively affecting SQL Server performance. Browse for and then select the location
where you want to store your analysis results.
Enter your connection credentials
You can configure SQL Doctor to use either Windows or SQL Server Authentication to connect to the selected
instance.
Select your analysis options
When performing an analysis, SQL Doctor takes into account whether the instance is a production server, it
has online transaction processing (OLTP) enabled, or it is a Cloud Instance (In this option, select Amazon
RDS SQL Server or Microsoft Azure SQL Database). Specify whether you want SQL Doctor to use these
options when analyzing the selected server.

IDERA Website | Products | Buy | Support | Community | About Us | Resources | Legal

SQL Doctor 3.7 – Documentation

Select your performance collection options
You can configure SQL Doctor to use either OLE automation or WMI to collect performance metrics from theselected instance. To configure the connection settings you want SQL Doctor to use for this server, click
Advanced Connection Settings. Be sure to test your collection settings before you start the analysis.

Configure Advanced Connection Settings
The Advanced Connection Settings window of the SQL Doctor allows you to input options for how you want to collectperformance counters.
Access the Advanced Connection Settings window
You can access the Advanced Connection Settings window by clicking Advanced Connection Settings in the Add
Server window or by selecting Edit > General Settings and clicking the corresponding link.

 The options available in the Advanced Connection Setting windows are not available for cloud instances. The
Advanced Connection Setting is not available when SQL Doctor recognizes instances running on a Linux
operating system.

Select your advanced connection settings
You can configure SQL Doctor to use either SQL Server OLE Automation or Windows Management Instrumentation(WMI) for collecting performance metrics from the selected instance.
SQL Server Automation
To use SQL Server OLE automation, check the Use OLE Automation for collecting performance counters box
and then click OK.

IDERA Website | Products | Buy | Support | Community | About Us | Resources | Legal

SQL Doctor 3.7 – Documentation

Windows Management Instrumentation
To use WMI for collection, type the user name and password in the appropriate fields. The Authority field is
optional when setting your WMI namespace security. Then click OK to close the Advanced Connection Settings
window.

 Windows Management Instrumentation (WMI) is not available on a Linux operating system.

 For more information about WMI connection, see the following Microsoft tip at http://technet.microsoft.com/
en-us/library/ee692772.aspx#EEAA.

Block recommendations
The Block Recommendations tab of the Server Settings window lists all available SQL Doctor recommendations andindicates whether they are blocked. This list is server-specific, and can be customized per your unique diagnostic
needs.

IDERA Website | Products | Buy | Support | Community | About Us | Resources | Legal

SQL Doctor 3.7 – Documentation

 To reduce analysis "noise," consider blocking recommendations for changes that you do not plan to make inyour environment. Blocking a recommendation removes the recommendation from all future analyses results
for this registered instance.

Access the Block Recommendations tab of the Server Settings window
You can access the Block Recommendations tab of the Server Settings window by clicking Edit > Blocked
Recommendations.

Block and unblock your recommendations
To block recommendations from appearing in your analysis:

1. Access the Block Recommendations tab of the Server Settings window by clicking Edit > Blocked
Recommendations.
2. Check the Block box next to each of the recommendations you want to block from future analyses.
3. Click OK.
To unblock recommendations to include them in your analysis:

1. Access the Block Recommendations tab of the Server Settings window by clicking Edit > Blocked
Recommendations.
2. Scroll down the list of blocked recommendations and find the recommendation you want to unblock.
3. Clear the Block box next to the recommendation.
4. Click OK.

Block objects
The Block Objects window allows you to confirm that you want to block the selected recommendation or group ofrecommendations. Select the recommendation that you want to block form appearing in the recommendations list,
and then click Save Changes to confirm.

Block databases
The Block Databases tab of the Server Settings window lets you block SQL Server databases from being included inscheduled, ad-hoc, or real-time analyses. When a database is blocked, SQL Doctor does not return recommendations
for it, and the database is not listed under the Databases node in the navigation pane.

IDERA Website | Products | Buy | Support | Community | About Us | Resources | Legal

SQL Doctor 3.7 – Documentation

Access the Block Databases tab of the Server Settings window
You can access the Block Databases tab of the Server Settings window by clicking Edit > Blocked Databases.

Block and unblock your databases
To block databases from appearing in your analysis:

1. Access the Block Databases tab of the Server Settings window by clicking Edit > Blocked Databases.
2. Check the Block box next to each of the databases you want to block from future analyses.
3. Click OK.
To unblock databases to include them in your analysis:

1. Access the Block Databases tab of the Server Settings window by clicking Edit > Blocked Databases.
2. Scroll down the list of blocked databases and find the database you want to unblock.
3. Clear the Block box next to the database.
4. Click OK.

Remove a server from SQL Doctor
Use the Remove Server window to unregister the selected SQL Server instance from SQL Doctor. The unregisteredserver no longer appears in the server tree and is no longer available for future analyses. Any scheduled analyses are
stopped.

 Be aware that, when you remove a server, all previous analysis results are deleted from the Repository and are
no longer available for historical reference later.

Access the Remove Server window
You can access the Remove Server window by clicking Remove Server.

Remove a server from SQL Doctor
To remove a server:

1. Click Remove Server on the SQL Doctor toolbar.
2. On the Remove Server window, check the box for the SQL Server instance you want to remove.
3. Click Remove.

IDERA Website | Products | Buy | Support | Community | About Us | Resources | Legal

SQL Doctor 3.7 – Documentation

Schedule analysis
The Schedule Analysis window allows you to schedule a regular SQL Doctor analysis on your SQL Server instance.
Scheduled analyses are an important way to track the health of your SQL Server environment. To get the most out of
SQL Doctor, IDERA recommends that you schedule the analysis to run during busy periods or during times yourSQL Server users are experiencing problems.
By default, SQL Doctor performs a full analysis for five minutes, using the general settings defined for the target
SQL Server instance. A full analysis collects and examines performance metrics from all available analysis categories.

 SQL Doctor allows you to schedule a future analysis on a single SQL Server instance. You can perform manual
analyses on an unlimited number of SQL Server instances.

Access the Schedule Analysis window
You can access the Schedule Analysis window by clicking Edit > Schedule Analysis or by clicking Schedule Analysis on
the right side of the SQL Doctor toolbar.

Schedule an analysis

To schedule an analysis:

IDERA Website | Products | Buy | Support | Community | About Us | Resources | Legal

SQL Doctor 3.7 – Documentation

1. Access the Schedule Analysis window by clicking Schedule Analysis on the right side of the SQL
Doctor toolbar.
2. On the Schedule Analysis window, click Run a routine checkup of my SQL Server to enable
scheduling.
3. Select the target SQL Server instance using the Server Name drop-down option.
4. Choose to run the analysis either daily or weekly using the Recurrence drop-down option.
5. Select the time you want the analysis to start using the local time of the SQL Doctor computer. For
best results, select a time when the SQL Server instance is busy.
6. Specify the total Duration (in minutes) that you want SQL Doctor to take collecting and analyzing
data from this server.
7. Click OK.
To cancel a scheduled analysis:

1. Access the Schedule Analysis window by clicking Schedule Analysis on the right side of the SQL
Doctor toolbar.
2. On the Schedule Analysis window, select the target SQL Server instance using the Server Name dropdown option.
3. Clear the Run a routine check of my SQL Server check box.
4. Click OK. SQL Doctor displays a message alerting you that you are disabling the scheduled analysis
checkup for a server.
5. Click Yes.

Set the Server Overview warning thresholds
Use the Server Overview Defaults window to configure when you want SQL Doctor to warn you about the health statusof your servers.
You can set the maximum threshold values for several crucial server properties and statistics. SQL Doctor applies thesethresholds to all SQL Server instances in your environment, and assesses the real-time health of each server
accordingly.
To see the current health of a particular server, routinely check its Server Overview.

Access the Server Overview Defaults window
You can access the Server Overview Defaults window by clicking Edit > Server Overview Defaults.

IDERA Website | Products | Buy | Support | Community | About Us | Resources | Legal

SQL Doctor 3.7 – Documentation

Use the Server Overview Defaults window settings

Most Recent Analysis
Triggers a warning if there is no analysis performed in the entered number of days.

IDERA Website | Products | Buy | Support | Community | About Us | Resources | Legal

SQL Doctor 3.7 – Documentation

Recommendations
Triggers a warning if the number of recommendations in the most recent analysis exceeds the enterednumber of recommendations.
Priority
Triggers a warning if the recommendation priority meets or exceeds the selected level.
CPU Usage
Triggers a warning if the CPU usage exceeds the entered percentage.
Memory Available
Triggers a warning if the memory available dips below the entered amount in megabytes (MB).
Blocking Processes
Triggers a warning if the number of blocking processes (by SPID) exceeds the entered amount.
Network Retransmits
Triggers a warning if the network retransmits exceeds the entered percentage.
Disk Space
Triggers a warning if the free space available on the disk dips below the entered percentage.
Recent Full Backups
Triggers a warning if there is no full backup performed in the entered number of days.
Recent Log Backups
Triggers a warning if there is no log backup performed in the entered number of days.
Failed Jobs
Triggers a warning if any job fails in the entered number of past hours.

IDERA Website | Products | Buy | Support | Community | About Us | Resources | Legal

SQL Doctor 3.7 – Documentation

Manage your SQL Doctor license
By default, SQL Doctor installs with a limited-time trial license key that is valid for the computer on which you haveinstalled the product. When you decide to move from a trial implementation of SQL Doctor to a production version,contact and obtain a license key from IDERA.
Using the IDERA Licensing Console, you can:• Register your license for SQL Doctor• Choose how to activate your license• Manually activate your license• Deactivate your license• Extend your trial period
SQL Doctor checks for a valid license each time you start the product. A valid license is a trial or production key that isregistered for the computer on which you have installed SQL Doctor. If your license expires, you can no longer access
SQL Doctor.

Access the licensing console
You can access the licensing module from the Help menu by clicking Manage License. This option opens the Licensing
Console.

About the trial license
By default, SQL Doctor installs with a limited-time trial license key that is valid for the computer on which you haveinstalled the tool.
When you decide to move from a trial implementation of SQL Doctor to a production version, contact and obtain alicense key from IDERA.

Register your software
Use the Register Your Software window to start the license activation process and begin using this product. Once yoursoftware is registered, you have 14 days to complete the license activation. You can continue using all the productfeatures during that 2-week period.
After your product registration is expired, you can no longer use the product, but can still activate a valid production
license online or manually.

IDERA Website | Products | Buy | Support | Community | About Us | Resources | Legal

SQL Doctor 3.7 – Documentation

Access the Register Your Software window
The Licensing Console automatically opens when you start SQL Doctor. To access the Register Your Software window,
click Register. The Licensing Console displays the Register Your Software window.

Use the Register Your Software window
Use the following fields to register your software:
Name
Type your full name.
Organization

Optional. Type the name of your company, business unit, or department.
Serial Number
Type or copy and paste the product Serial Number.
Activate Automatically
Select this option to allow the licensing engine to automatically activate your registered license key the nexttime you are online. This option requires an active Internet connection.
Buy Now
Launches the IDERA e-commerce Web site, where you can purchase multiple licenses for this product.
Learn more about activation
Click Tell me more to learn more about how activation works.
Register
Click Register to submit your information and register this product with IDERA. This action starts the license
activation process by sending your license key and company information to IDERA.
Trial
Click Trial to run the product in trial mode, allowing you to continue evaluating this product.

IDERA Website | Products | Buy | Support | Community | About Us | Resources | Legal

SQL Doctor 3.7 – Documentation

Choose how to activate your license
Use the Activate Your Software window to choose how and when you want to complete the activation process. Activate
your license as soon as possible after registering your software.
Once your software is registered, you have 14 days to complete the license activation. You can continue using all the
product features during that 2-week period. After your product registration expires, you can no longer use the product.

 You can activate one license per computer at a time. Each installation of the tool requires a unique license key.

Access the Activate Your Software window
The Licensing Console automatically opens when you start SQL Doctor.
To access the Register Your Software window

1. Click Register.
2. On the Register Your Software window, type the required information.
3. Click Register. The Licensing Console displays the Activate Your Software window.

Use the Activate Your Software window
Use the following fields to activate your software:
Activate online now
Connects to the IDERA Web site and confirms that you have entered a valid license key. No additional action
is needed. This action requires an Internet connection.
Activate manually
Allows you to activate your license over the phone or through email. Use this option when you do not haveInternet access from the computer on which you installed the software.
Activate later
Allows you to activate your license at a later time. The product license remains registered with IDERA.

IDERA Website | Products | Buy | Support | Community | About Us | Resources | Legal

SQL Doctor 3.7 – Documentation

CancelStops the licensing process at this step. The next time you start the product, the Licensing Console prompts
you to activate your license.

Activate your license manually
Use the Activate Manually window to manually activate your license key when the target computer on which SQL
Doctor is installed does not have Internet access or is otherwise offline. Use the provided buttons to copy the necessary
information and then email or call IDERA Licensing to activate this license.

Access the Activate Manually window
The Licensing Console automatically opens when you start SQL Doctor.
To access the Activate Manually window

1. Click Register.
2. On the Register Your Software window, type the required information.
3. Click Register.
4. On the Activate Your Software window, click the option to activate your license manually.

Manually activating your license
1. Use the provided buttons to copy the license key and machine code.
2. Email or phone IDERA Licensing, and provide the Licensing Representative with the information you
copied in Step 1. IDERA Licensing provides you with an activation code.
3. Type the activation code in the corresponding field on this window.

Use the Activate Manually window
Use the following fields to manually activate your software:
Copy license key and machine code to email IDERA
Click Copy to Email to open a new email message, pre-populated with the license key and machine code,that you can immediately send to IDERA Licensing to start the manual activation process.
Copy license key and machine code for your records
Click Copy to Clipboard to copy your license information and then paste it into an application such as
Microsoft Word.

IDERA Website | Products | Buy | Support | Community | About Us | Resources | Legal

SQL Doctor 3.7 – Documentation

Return to the previous activation window
Click Go Back to return to the Activate Your License window and choose a different activation route.
Complete the license activation
Click Activate to complete the license activation process and begin using this product in production.
CancelStops the licensing process at this step. The next time you start the product, the Licensing Console prompts
you to activate your license.
Activation CodeAllows you to enter the activation code provided by IDERA Licensing.
Machine CodeDisplays the serial number of the computer on which you have installed the registered product.
Machine ID
Displays the corresponding ID number for this computer.
License Key
Displays the license key you entered when you registered the product.

Deactivate your license
Use the Deactivate License window to disable the currently active license key. You can deactivate the product license
when you want to:• Un-install the tool. You can re-install this tool on the same computer later.
• Migrate your installation from one computer to another. After migrating this tool to anothercomputer, the system prompts you to complete the license activation process for the installation on
the new computer.
To immediately deactivate the product license for this tool, click Deactivate.

IDERA Website | Products | Buy | Support | Community | About Us | Resources | Legal

SQL Doctor 3.7 – Documentation

 Deactivation requires Internet access. If the computer does not have Internet access or is otherwise
unavailable, contact IDERA Licensing to deactivate the license manually.

Access the Deactivate License window
The Licensing Console automatically opens when you start SQL Doctor.
To access the Deactivate License window

1. Navigate to the Help menu.
2. Click Deactivate License.

Use the Deactivate License window
Use the following fields to deactivate your software:
CancelStops deactivation. You can continue to use this license key for the product installation on this computer.
Deactivate this license
Deletes the license key and activation information for the product installation on this computer. If you do
not use this license key for another installation on a different computer, you can later re-install the producton this computer and re-activate the license key.
Learn more about activation
Click Tell me more to learn more about how activation works.

Extend your trial period
Use the Extend License Use Period window to add more time to your trial period for this specific tool. You can extend
your trial only after the original time period expires, and you can only extend your trial license once.
If your trial key has not yet expired, or if you previously extended your key, the Licensing Console returns an error.

Access the Extend License Use Period window
The Licensing Console automatically opens when you start SQL Doctor. Click Extend Trial to access the Extend License
Use Period window.

IDERA Website | Products | Buy | Support | Community | About Us | Resources | Legal

SQL Doctor 3.7 – Documentation

Use the Extend License Use Period window
Use the following fields to extend the trial license period for your software:
Copy license key and limit code to clipboard
Click Copy to Clipboard to copy your trial license information and then paste it into an application such as
Microsoft Word.
Return to the previous trial license window
Click Go Back to return to the main licensing window and choose a different action.
Extend your trial license online
Connects to the IDERA Web site, validates that your current trial license has expired, and then adds sevenmore days to the total trial time period. This action requires an Internet connection.
Extend your trial license manually
Email your trial license information to IDERA Licensing, asking for a trial extension code, and then type thatcode into the Extend Time field.
Extend Time
Allows you to enter the new trial license key provided by IDERA.
Limit CodeDisplays the trial identification code.
License Key
Displays the trial license key.

IDERA Website | Products | Buy | Support | Community | About Us | Resources | Legal

SQL Doctor 3.7 – Documentation

Analyze your SQL Server instance
The Analysis Wizard allows you to fine tune the type of analysis you want to perform on the target server. Note thatanalysis of the collected data may take several additional minutes and does not begin until the collection process iscomplete.

Access the Analysis Wizard
You can access the Analysis Wizard by clicking Analyze Server.

Analyze a SQL Server instanceTo use the Analysis Wizard to analyze a SQL Server instance:

1. Click Analyze Server. SQL Doctor displays the Analysis Wizard.
2. On the Select SQL instance tab, select the SQL Server instance you want to analyze, if the instancedoes not appear in the list, verify that the instance is registered. Select between Collect query-related
data using Extended Events (SQL 2012+) or Collect query-related data using SQL Trace (if applies), andthen click Next.

IDERA Website | Products | Buy | Support | Community | About Us | Resources | Legal

SQL Doctor 3.7 – Documentation

 The Extended Events options is disabled when analyzing SQL Server 2005, SQL Server 2008 or
SQL Server 2008 R2, and Amazon RDS SQL Server. The SQL Trace option is selected by default.
3. On the Select categories tab, check the box for each category you want to include in the analysis. If
you want more information about a category, select the category and SQL Doctor displays

IDERA Website | Products | Buy | Support | Community | About Us | Resources | Legal

SQL Doctor 3.7 – Documentation

additional detail in the Category Description area.

4. After selecting the appropriate analysis categories, click Next.
5. On the Filters tab, complete the following fields:• If you want to analyze the workload from a specific location, type the name of theapplication using wild card characters (%) if necessary. If you want to limit your analysis to
only one database, select the appropriate database from the list.

IDERA Website | Products | Buy | Support | Community | About Us | Resources | Legal

SQL Doctor 3.7 – Documentation• Type the number of minutes you want SQL Doctor to spend collecting data. By default, SQL
Doctor spends five minutes on this task.

6. Click Finish. SQL Doctor analyzes the selected instance.

View your analysis results
Once your analysis is complete, you can review your results by:• Viewing the highest priority performance recommendations• Viewing individual recommendation details for answers on how to fix the problem

Select performance categories
When configuring your analysis, you can select any combination of performance categories to focus the analysis. Eachcategory contains a set of performance metrics and their related recommendations. By picking a category, you are
identifying which SQL Server areas concern you most.

IDERA Website | Products | Buy | Support | Community | About Us | Resources | Legal

SQL Doctor 3.7 – Documentation

Based on the chosen categories, SQL Doctor analyses your target SQL Server instance to gather the appropriate metrics
and determine which recommendations match the server's current performance issues. The more categories you pick,the longer the analysis needs to run to ensure all relevant data has been collected and thoroughly analyzed.

Access the Analysis Wizard
You can access the Select categories tab of the Analysis Wizard by clicking Analyze Server, selecting the appropriate
SQL Server instance, select Extended events or SQL Trace, and clicking Next.

 By default, the Extended Event option is enabled for SQL instances 2012+ and Microsoft SQL Azure (Note thatfor these options SQL Trace is an alternative option).

On the Select categories tab, you can select one or more of the following performance categories for use in the detailed
analysis:

All categories

Disaster Recovery
Detects any database and transaction log backup issues such as failures or skips, overdue integrity
checks, and corruption issues.

Index Optimization
Determines whether index settings or problems with the indexes themselves are negatively affecting
SQL Server performance.

Query Optimization
Detects query problems that negatively affect your SQL Server performance.

Resources

Disk
Detects whether disk settings and associated problems are affecting SQL Server performance.

IDERA Website | Products | Buy | Support | Community | About Us | Resources | Legal

SQL Doctor 3.7 – Documentation

MemoryExamines the server memory settings and determines whether they affect performance.

Network
Detects network problems or configuration settings that affect SQL Server performance.

Processor
Detects processor problems and associated configuration settings.

Configuration

Database Configuration
Identifies which database configuration settings improve SQL Server performance.

Server Configuration
Detects server configuration settings that can decrease the performance of your SQL Server instance.

Security
Identifies non-optimal security settings on the SQL Server instance.

Workload

Deadlocks
Detects any deadlocks on the SQL Server instance.

Blocking Processes
Detects any processes that are blocking other processes.

IDERA Website | Products | Buy | Support | Community | About Us | Resources | Legal

SQL Doctor 3.7 – Documentation

Long Running Jobs
Identifies any long-running jobs.

Open Transactions
Identifies any long-running open transactions and determines whether the associated T-SQL codeneeds corrections to improve performance.

Wait Stats
Identifies any waits that are affecting SQL Server performance.

Choose database or application
When configuring your analysis, you can choose which database or application workload you want to include in theanalysis. Picking a database or application allows you to further fine-tune the analysis results.
You can also set the amount of time (duration, in minutes) that you want SQL Doctor to spend collecting data for youranalysis. When setting the analysis duration, select a number of minutes that is proportionate to the criticality of theperformance issue you are experiencing. For example, if you are experiencing a serious performance issue and you
select more than one performance category to analyze, you may want to run the analysis for at least 15 minutes toensure that you collect enough data. The more data SQL Doctor can collect for a given analysis, the greater likelihoodthat the resultant recommendations accurately identify and resolve the issue.

Access Filters
You can access the Filters tab of the Analysis Wizard by clicking Analyze Server, completing the Select SQL instanceand Select categories tabs, and then clicking Next.
Enter the name (%wildcard) of the Application you want to analyze in the name text box, you can limit the analysis to asingle database or to all databases, specify how long you want that SQL Doctor spend collecting diagnostic data
(Between 1-500 minutes), and then click Finish.

Get the analysis status
The Analysis Status window displays the progress of the SQL Doctor analysis of your server. The analysis process cantake as little as three minutes and in very busy, large environments it can take 30 minutes or more.

IDERA Website | Products | Buy | Support | Community | About Us | Resources | Legal

SQL Doctor 3.7 – Documentation

In general, a longer analysis provides a better indication of the health of your SQL Server environment.

Analysis type
The type of analysis which is based on the answers you provided on the user interview.

Start time
The time on the local computer when the analysis began.

Time elapsed
The length of time the analysis required.

IDERA Website | Products | Buy | Support | Community | About Us | Resources | Legal

SQL Doctor 3.7 – Documentation

Current status
Description of what the analysis is working on. In general, the message displayed here lists the database or
object currently being analyzed.

IDERA Website | Products | Buy | Support | Community | About Us | Resources | Legal

SQL Doctor 3.7 – Documentation

Resolve performance issues
When an analysis is complete, SQL Doctor provides a priority-ranked list of recommendations that help you resolve theissues that are most affecting the performance of your SQL Server instance.
A quick finding provides a real-time analysis of your current SQL Server performance, allowing you to address issues
right now as they happen.
You can get quick findings on a server-by-server basis. Use the left pane navigation tree to select the server you want toanalyze now, and then review the data on the Server Overview view.

 To see the latest analysis results across all registered servers, navigate to the All Servers tab on the Dashboard
view.

Check analysis history across servers
The Analysis History tab on the SQL Doctor dashboard allows you to access the results of every analysis stored by SQL
Doctor for your registered SQL Server instances. Analyses are ordered by start time per the local computer running theSQL Doctor console, and include the following information:• The name of the analyzed server• Timestamp when the analysis started• Total time the analysis ran (duration in minutes)• Type of analysis performed• Total number of recommendations found• Highest priority recommendation associated with the analysis

View recommendations
You can view the Recommendations List for a specific analysis, double-clicking the analysis you want toinvestigate.

Delete an analysis
You can delete an analysis by selecting one or more analyses from the list, and then clicking Delete. Thisaction permanently removes the corresponding analysis data from the SQL Doctor Repository.
You can specify how long you want to keep your analyses before SQL Doctor deletes them. By default, SQL Doctor
stores each analysis in the Repository database for 30 days. To change this setting, click the Keep history for 30 days
link. Keep in mind that this setting is a global setting that applies to all analyses performed on all registered SQL Server
instances.

IDERA Website | Products | Buy | Support | Community | About Us | Resources | Legal

SQL Doctor 3.7 – Documentation

SQL Doctor also allows you to schedule an analysis to run daily or weekly at the specified time. To automate youranalyses, click Schedule analysis on the SQL Doctor toolbar.

Check all analyses for server
The All Analyses view lists every available analysis performed on the selected SQL Server instance. Analyses areordered by start time (per the local computer running the SQL Doctor Console) and include:• The time stamp when the analysis started.
• The total time the analysis ran (duration in minutes).
• The type of analysis performed.
• The total number of recommendations found.
• The highest priority recommendation associated with the analysis.

Access the All Analyses view
You can access the All Analyses view for a SQL Server instance by expanding the directory tree for the
instance, and then selecting All Analyses.

View recommendations
You can view the list of recommendations for a specific analysis by double-clicking the analysis you want toinvestigate.

Delete an analysis
You can delete an analysis by selecting one or more analyses from the list, and then clicking Delete. Thisaction permanently removes the corresponding analysis data from the SQL Doctor Repository.
You can specify how long you want to keep your analyses before SQL Doctor deletes them. By default, SQL Doctor
stores each analysis in the Repository database for 30 days. To change this setting, click the Keep history for 30 days
link. Keep in mind that this setting is a global setting that applies to all analyses performed on all registered SQL Server
instances.
SQL Doctor also allows you to schedule an analysis to run daily or weekly at the specified time. To automate youranalyses, click Schedule analysis on the SQL Doctor toolbar.

IDERA Website | Products | Buy | Support | Community | About Us | Resources | Legal

SQL Doctor 3.7 – Documentation

Check the recommendations list
The Recommendations view provides a list of performance recommendations based on the analysis SQL Doctor ran
using your specific criteria. These recommendations result from a full analysis. You can also view quick findings for
currently-occurring issues.



SQL Doctor uses a recommendation engine from SQL Diagnostic Manager which helps you ease the updates
between these two products.

By default, SQL Doctor lists recommendations by priority. The priority of a recommendation is determined by theperformance gain you receive from making the suggested change, how relevant it is the parameters you specified when
configuring the analysis, and the confidence SQL Doctor has in the recommendation.

Highest priority recommendation

Lowest priority recommendation
Use the left pane navigation tree to narrow the recommendations by:• Applications targeted in your analysis• Performance categories you selected• Databases on the server• Logins at the server or database level• Frequency that specific recommendations were found• Prioritized recommendations• Workstations associated with this server

Flag recommendations to investigate later
Flagging a recommendation allows you to mark it for follow-up action. Use flags to identify recommendations that
require further investigation or that you want to optimize at a later time. When you flag a recommendation, this flaggedstate is saved to the analysis file, allowing you easily find the recommendation later.
To flag recommendations, click the Flag icon next to the recommendation you want to flag.

IDERA Website | Products | Buy | Support | Community | About Us | Resources | Legal

SQL Doctor 3.7 – Documentation

Block a recommendation from appearing in future analyses
Select each recommendation you want to block, and then click Block on the Recommendations toolbar menu. Once a
recommendation is blocked, it does not appear in any future analysis you run. For more information, see Block
Recommendations.

Export analysis results
You can either export the results to Excel or email the results to specific people.

1. Select the recommendations you want to include in your export.
2. Select either Export or Email on the Recommendations toolbar menu.

Use Show me the problem and Optimize script
SQL Doctor allows you to perform the following actions, when applicable:

Show me the problem
Opens a SQL Viewer window displaying the problematic part of the SQL script that generated therecommendation.

Optimize Script
Opens the Optimize Now window, displaying the SQL script used to fix the detected problem. Click Run to fix
the problem.

Undo Script
Opens the Undo Optimization window, displaying the SQL script used to undo the previously executed
optimization. Click Run to undo the previous optimization.
Click a recommendation in the list to open the Recommendation Details window.

Schedule the next analysis
You can schedule an analysis to run automatically on a daily or weekly timeframe. To schedule the next analysis, click
Schedule analysis in the SQL Doctor toolbar.

IDERA Website | Products | Buy | Support | Community | About Us | Resources | Legal

SQL Doctor 3.7 – Documentation

Implicit conversion recommendation
It is common for implicit data type conversions to be performed throughout TSQL. Implicit conversion occurs whendata from one object is moved to, compared with, or combined with data from another object of a different data type.
Generally, implicit conversions cause an unnoticeable performance impact on query performance so most TSQL
developers do not worry about the data type mismatches that lead to the implicit conversion.
The lack of attention to data type mismatches can have a significant negative impact on query performance. One case,in particular, is when the data type mismatch leads to an implicit conversion being performed on a column used in asearch predicate. This implicit conversion prevents the use of an index seek and ensures that an index scan or tablescan is performed to satisfy the query predicate. In this case, the implicit conversion is caused by the query optimizer by
seeing the column with the implicit conversion as an expression rather than a column.
For example, see the following implicit conversion occurring in a search predicate and the result:

You would expect an index seek to occur on the ‘AK_Employee_NationalIDNumber’ index; however, the execution plan
clearly shows that an index scan is performed instead of an index seek.

IDERA Website | Products | Buy | Support | Community | About Us | Resources | Legal

SQL Doctor 3.7 – Documentation

To determine the cause of having an index scan, mouse over the index scan operation of the execution plan to display
the tooltip containing additional details:

IDERA Website | Products | Buy | Support | Community | About Us | Resources | Legal

SQL Doctor 3.7 – Documentation

Under the ‘Predicate’ section of the tooltip see that the ‘CONVERT_IMPLICIT(int,[AdventureWorks].[HumanResources].
[Employee].[NationalIDNumber],0)=(253022876)’ implicit conversion is being performed on the ‘NationalIDNumber’column. This column value is being converted because the data type of the column mismatches the data type of theliteral value being used in the compare. To resolve the data mismatch, change the data type of the literal value tomatch the data type of the column:

The new execution plan for the updated TSQL is the following:

Note that the index scan is replaced by an index seek operation, optimizing successfully the query.

Use SQL Doctor to identify implicit conversions
IDERA SQL Doctor collects and analyzes your heaviest TSQL to help you identify implicit conversions along with otherproblems that impact query performance. The following is the resulting recommendation generated by IDERA SQL
Doctor for the example presented above (SDR-Q36 – Implicit conversion on a column may be causing index
suppression):

IDERA Website | Products | Buy | Support | Community | About Us | Resources | Legal

SQL Doctor 3.7 – Documentation

You can click Show me the problem on the Recommendations window to display the TSQL.

Diagnose individual queries
The Diagnose Queries window helps you save time by analyzing the queries you care about without having to wait fora full analysis to complete. When SQL Doctor diagnoses a query, you receive performance recommendations without
actually executing it.
When you click the diagnose button, SQL Doctor parses the T-SQL and bases the analysis and resultant
recommendations on the estimated execution plans of the database selected.

 The analyzed query is never executed on the target server or database.

Access the Diagnose Queries window
To open the Diagnose Queries window, click the Diagnose Queries button on the SQL Doctor toolbar.

IDERA Website | Products | Buy | Support | Community | About Us | Resources | Legal

SQL Doctor 3.7 – Documentation

Diagnose your queries
To diagnose your queries:

1. Click Diagnose Queries.
2. Use the Server and Database drop-down lists to select the SQL Server instance and database on
which this query either currently runs or runs in the future.
3. Type a query in the space provided or copy and paste a query from your T-SQL files.

 To validate the syntax of your query, use a tool like SQL Server Management Studio. SQLDoctor may be unable to generate estimated execution plans for queries that contain bad
syntax.
4. Click Diagnose.

Get a quick overview of enterprise health
Use the All Servers tab on the Dashboard view to quickly see the overall health of each registered server, based on thelast analysis run. Also, it shows you a recommendation summary from the latest analysis and from this tab, you haveaccess to quick links to different actions or settings for your monitored SQL Server.
Quick Links

Tasks
Analysis Results

Sessions

Waits

Query plan stats

Analyze Server

Diagnose Queries

IDERA Website | Products | Buy | Support | Community | About Us | Resources | Legal

SQL Doctor 3.7 – Documentation

Settings
SQL Doctor displays the registered server status in its own pane with links to the corresponding tasks.

Get a quick overview of server health
Use the console tree to navigate to the Server Overview view and quickly see the overall health of this specificregistered server, as of the last refresh.
This overview provides:• A server health checklist that highlights where performance issues may be happening right now• Real-time, key performance metrics for each major area of the server• Quick findings based on the values of the key metrics so you can immediately start troubleshooting
performance problems

IDERA Website | Products | Buy | Support | Community | About Us | Resources | Legal

SQL Doctor 3.7 – Documentation

Each health checklist provides the current value of specific server properties and statistics, such as how much memory
is available and how old the last database backup is, and warns you when a property or statistic exceeds the defined
threshold. You can customize any of these thresholds to match the performance requirements for your servers.

 For optimal performance, this view requires OLE automation to be enabled on the SQL Server instance.

Get quick findings
Like recommendations, quick findings can provide expert advice on how to fix an issue and tune your overall server
performance.
Unlike recommendations generated by a full analysis, quick findings are real-time. Each finding is based on aquick (and, therefore, limited) look at key performance metrics. Quick findings point you in the correct direction andgive you crucial troubleshooting information when you need it most. However, to get a thorough review of your server
performance, routinely perform a full analysis.
You can see quick findings for the following database and performance metrics:• Queries• Sessions• Wait stats• Query plans

View query details and findings
Use the Query Details window to identify which T-SQL code in your query is possibly causing performance issues andneeds optimization.
This displays the full query, highlighting the suspect areas of your code. SQL Doctor then displays the performance
metrics and quick findings associated with this specific query. This detailed information helps you narrow down yourtroubleshooting steps and obtain an immediate increase in server performance.

Access the Query Details window
To access the Query Details window, double-click a query while viewing your Query Plan Stats.

IDERA Website | Products | Buy | Support | Community | About Us | Resources | Legal

SQL Doctor 3.7 – Documentation

View session data and findings
The Sessions view identifies all sessions that are currently active on the selected SQL Server instance, as of the lastrefresh, as well as their key performance metrics and any quick findings that would help improve your session
performance.
These session performance metrics are collected from the Dynamic Management View (DMV) sys.dm_exec_requests.
For more information about individual metrics, see the Microsoft article sys.dm_exec_requests (Transact-SQL)

Access the Sessions view
You can access the Sessions view for a SQL Server instance by expanding the directory tree for the instance, and thenselecting Sessions.

View wait stats and findings
The Waits view identifies all waits that occurred on the selected SQL Server instance, as of the last refresh, as well as
their key performance metrics and any quick findings that would help decrease the number of waits and improve youroverall server performance.
These statistics and findings represent the state of waits on this server during the specified time range. The time-range
spans from the reset time to the last refresh time. To change the reset time to your current time, click Reset.
Although the metric descriptions and categories are provided by SQL Doctor, the performance metrics are collectedfrom the Dynamic Management View (DMV) sys.dm_os_wait_stats. For more information about individual metrics, see
the Microsoft article sys.dm_os_wait_stats (Transact-SQL).

Access the Waits view
You can access the Waits view for a SQL Server instance by expanding the directory tree for the instance, and thenselecting Waits.

Parallelism and CXPACKET waits
CXPACKET waits occur when a parallel query is executed and one or more threads have to wait on one of the otherthreads to complete. SQL Server parallelizes a query by dividing each query operation into equal sized sets and by
splitting up these sets into multiple threads targeting specific processors for parallel processing. Because not all thethreads complete at exactly the same time, the server experiences CXPACKET waits.

IDERA Website | Products | Buy | Support | Community | About Us | Resources | Legal

SQL Doctor 3.7 – Documentation

 Parallel queries require extra overhead to coordinate the parallel processing along with the initialization,synchronization, and termination of the parallel plan. If you have a parallel query execution, you will have
CXPACKET waits.

High CXPACKET waits
CXPACKET waits are of concern only if they are in excess. High CXPACKET waits indicate that some performance tuningis necessary on a specific query or the whole SQL Server.
The causes for experiencing high CXPACKET in your SQL Server can be any of the following:• Out of data statistics causing SQL Server to incorrectly divide the query into equal sized sets.
• Fragmented indexes causing slower I/O speeds that impact one thread over the others.
• Client applications processing result sets inefficiently.
• Hyper-threading that causes SQL Server to process threads on hyper-threaded cores instead of only
physical cores.
• CPU pressure.
• Memory pressure.
• Incorrect configuration of the Max degree of parallelism server option.
• Incorrect configuration of the Cost threshold for parallelism server option.
If you experience high CXPACKET waits in your OLTP server, it is generally due to one or more of the following:• Missing indexes• Out of data statistics• Missing search predicate• Function used on a column in a search predicate
Keep CXPACKET waits under control
Use the following process to keep regular maintenance of your index and statistics and to control CXPACKET waits:

1. Enable Auto Create Statistics and Auto Update Statistics database properties options in all databases.
2. Rebuild all indexes.
3. Perform an UPDATE STATISTICS on all stats objects with the FULLSCAN.
4. Disable Hyper-threading.
5. Set the Max Degree of Parallelism server configuration option to a number smaller than the number ofphysical processor cores.
6. Set the Cost Threshold for Parallelism server configuration option not too low.
Identify the cause for experiencing CXPACKET waits
You can review the query plans from the plan cache to identify queries that can be parallelized. Use the following links
to find scripts for viewing your query plans:

IDERA Website | Products | Buy | Support | Community | About Us | Resources | Legal

SQL Doctor 3.7 – Documentation• Can you dig it? - Parallelism in the plan cache• Find query plans that may utilize parallelism

CXPACKET waits in your SQL Servers

CXPACKET waits for OLTP servers
OLTP servers have fast running transactions that should be never parallelized and therefore never cause CXPACKET
waits. If you tune your queries properly, verify that the index usage is correct, and perform index and statistics
maintenance when necessary, CXPACKET waits should not be of concern. If you experience high CXPACKET waits, try
the following:• Increase the Cost threshold for parallelism server configuration option until the waits are undercontrol.
• Use the ‘OPTION (MAXDOP 1)’ query hint to prevent parallelism when you identify specific queries thatcause the CXPACKET waits.
• Set the Max degree of parallelism server configuration option to half the number of physicalprocessors on the server up to a max of eight. This configuration prevents a query from monopolizing
all of the processors at the detriment of other activity on the server. As a last resort, set the Max
degree of parallelism server configuration option to 1 to prevent any query parallelism.
CXPACKET waits for non-OLTP servers
CXPACKET waits for non-OLTP servers (Reporting, OLAP, Hybrid-OLTP, etc.) are indicators that parallelism takes place.
CXPACKET waits indicate that:• Queries may need to be tuned• Indexes may need to be added and/or correctly tuned for the workload• Index reorganization/rebuilding may be needed• Statistics may be out of date
Configure your server options for parallelism

Max degree of parallelism
Leave the Max degree of parallelism server configuration option at zero. Note that this configuration may be
acceptable for non-OTPL Servers. You can also consider setting the Max degree of parallelism server
configuration option at half the number of physical processors on the server up to a max of eight.

IDERA Website | Products | Buy | Support | Community | About Us | Resources | Legal

SQL Doctor 3.7 – Documentation

Cost threshold for parallelism
Set the Cost threshold for parallelism server configuration option at the default setting of 5.
Identify what is causing CXPACKET waits
To identify the responsible for CXPACKET waits in your SQL Server, run the following query:selectt.wait_type,t.wait_duration_ms,
t.session_id,
t.resource_description,s.program_name,b.sql_handle,
b.statement_start_offset,b.statement_end_offset,[statement_text]=SUBSTRING(st.text, (b.statement_start_offset/2)+1,((CASE b.statement_end_offset
WHEN-1 THEN DATALENGTH(st.text)
ELSE b.statement_end_offset
END - b.statement_start_offset)/2) + 1),
st.text
from sys.dm_os_waiting_tasks tleft join sys.dm_exec_requests b
left join sys.dm_exec_sessions s on b.session_id = s.session_id
on t.waiting_task_address = b.task_addressouter apply sys.dm_exec_sql_text (b.sql_handle) st
where b.sql_handle is not null and t.wait_type = 'CXPACKET';
To identify the responsible for CXPACKET waits and also display the execution plan, run the following modified
script:IDERA Website | Products | Buy | Support | Community | About Us | Resources | Legal

SQL Doctor 3.7 – Documentation

selectt.wait_type,t.wait_duration_ms,
t.session_id,
t.resource_description,s.program_name,qp.query_plan,b.statement_start_offset,b.statement_end_offset,[statement_text]=SUBSTRING(st.text, (b.statement_start_offset/2)+1,((CASE b.statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE b.statement_end_offset
END - b.statement_start_offset)/2) + 1),
st.text
from sys.dm_os_waiting_tasks tleft join sys.dm_exec_requests b
left join sys.dm_exec_sessions s on b.session_id = s.session_id
on t.waiting_task_address = b.task_addressouter apply sys.dm_exec_sql_text (b.sql_handle) st
cross apply sys.dm_exec_query_plan(b.plan_handle) qp
where b.sql_handle is not null and t.wait_type = 'CXPACKET'
After running the query, click the ShowPlan link in the query_plan column to display the execution plan. Review
the plan to see if the query in question needs additional tuning.

View query plan stats and findings
The Query Plan Stats view identifies all queries that are either scheduled or currently running on the selected
SQL Server instance, as of the last refresh. This view also displays key performance metrics for these queries and any
quick findings that would help improve your query performance.

IDERA Website | Products | Buy | Support | Community | About Us | Resources | Legal

SQL Doctor 3.7 – Documentation

These query performance metrics are collected from the Dynamic Management View (DMV) sys.dm_exec_query_stats.
For more information about individual metrics, see Microsoft article sys.dm_exec_query_stats (Transact-SQL).
However, the Query column displays the T-SQL code executed by the query, and the Count column reports how manytimes the query was run.

Access the Query Plan Stats view
You can access the Query Plan Stats view for a SQL Server instance by expanding the directory tree for the instance, andthen selecting Query Plan Stats.

Show me the problem
The Show me the problem button launches the SQL Viewer window with the SQL text associated with therecommendation highlighted. Click Copy to copy the SQL text to the clipboard so you can view the text in a text editor
or in SQL Server Management Services.

Access the Show me the problem
To access the Show me the problem window, view the list of recommendations for a SQL Server instance. Select theappropriate row, and then click Show me the problem.

Show me how to optimize performance
The Optimize Now window displays the script that used to enact the recommended change.
Review the script, and then either click the Run button to initiate the optimization or click Cancel to return to therecommendation you were viewing.

 When you run the optimize script, adisplays next to the optimized recommendation.

You can also copy the script to Notepad or another text editor to examine the code and research other options further
before making the change.

If you need to cancel out of the optimization, keep in mind it may take a few minutes for the process to stop.

IDERA Website | Products | Buy | Support | Community | About Us | Resources | Legal

SQL Doctor 3.7 – Documentation

Access the Optimize Now window
To access the Optimize Now window, view the list of recommendations for a SQL Server instance. Select theappropriate row, and then click Optimize Script.

Undo previously applied optimization
The Undo Optimization window displays the script that is used to enact the recommended change. Review the scriptand either click the Run button to initiate the undo or click Cancel to return to the recommendation you were viewing.
The undo script can be copied to clipboard and used at a later time.
Executing the undo script on a recommendation that is not already optimized can harm your SQL Server environment.
Before executing the undo script, be sure that the corresponding optimization is performed.

Access the Undo Optimization window
To access the Undo Optimization window, select the row of the recommendation that you previously optimized, andthen click Undo Script.

View recommendation details
The Recommendation Details window provides detailed information about your recommendation; in many cases, thisincludes the actual metrics involved, databases, tables, or applications involved.
The following actions are available on every recommendation details window:

Flag
Allows you to flag the recommendation. Use this flag to identify or group recommendations that require
further troubleshooting. The flag state is preserved in the recommendation history, so you can return to thislist of recommendations later and see which ones still need attention.

Block
Disallow this recommendation from appearing in subsequent analysis results.

IDERA Website | Products | Buy | Support | Community | About Us | Resources | Legal

SQL Doctor 3.7 – Documentation

Copy
Copy the recommendation text to the clipboard to use in other applications.

Email
Send the contents of this recommendation in an email message.

ExportSaves the selected recommendations to a Microsoft Excel spreadsheet.

Use Show me the problem and Optimize Script
SQL Doctor allows you the opportunity to perform the following actions, when applicable:

Show me the problem
Opens a SQL Viewer window displaying the problematic part of the SQL script that generated therecommendation.

Optimize Script
Opens the Optimize Now window, displaying the SQL script used to fix the detected problem. Click Run to fix
the problem.

Undo Script
Opens the Undo Optimization window, displaying the SQL script used to undo the previously executed
optimization. Click Run to undo the previous optimization.
Click a recommendation in the list to open the Recommendation Details window.

IDERA Website | Products | Buy | Support | Community | About Us | Resources | Legal

SQL Doctor 3.7 – Documentation

SQL Doctor PDF
This page contains a direct link to the SQL Doctor help in PDF format. This format is suitable for printing and for saving
on your local PC for further reference. The PDF includes all pages from the relevant help published on IDERA wiki.
SQL Doctor.pdf

IDERA Website | Products | Buy | Support | Community | About Us | Resources | Legal