SQL Server 2005 Best Practices Analyzer (BPA)

The SQL Server 2005 Best Practices Analyzer (BPA) gathers data from Microsoft Windows and SQL Server configuration settings. BPA uses a predefined list of SQL Server 2005 recommendations and best practices to determine if there are potential issues in the database environment.This download is the January 2008 release of SQL Server 2005 Best Practices Analyzer.

SQL Server 2005 Performance Dashboard Reports

The SQL Server 2005 Performance Dashboard Reports are Reporting Services report files designed to be used with the Custom Reports feature introduced in the SP2 release of SQL Server Management Studio. The reports allow a database administrator to quickly identify whether there is a current bottleneck on their system, and if a bottleneck is present, capture additional diagnostic data that may be necessary to resolve the problem. For example, if the system is experiencing waits for disk IO the dashboard allows the user to quickly see which sessions are performing the most IO, what query is running on each session and the query plan for each statement.Common performance problems that the dashboard reports may help to resolve include:
- CPU bottlenecks (and what queries are consuming the most CPU)
- IO bottlenecks (and what queries are performing the most IO).
- Index recommendations generated by the query optimizer (missing indexes)
- Blocking
- Latch contentionThe information captured in the reports is retrieved from SQL Server’s dynamic management views. There is no additional tracing or data capture required, which means the information is always available and this is a very inexpensive means of monitoring your server.Reporting Services is not required to be installed to use the Performance Dashboard Reports.

SQL Server Health and History Tool (SQLH2)

Appendix A – What SQLH2 Collects

 The SQLH2 Collector gathers a wealth of information from each target server. The specifics of the information that SQLH2 gathers are provided here, in the interest of full disclosure so that you can be aware of exactly what information is collected and what information will be uploaded to Microsoft, assuming you choose to share this information. As discussed earlier in this document, information uploaded to Microsoft by using the SQLH2 tool is completely anonymous. 

Hardware and Configuration (Windows Management Instrumentation)

• Computer system (DomainRole, Manufacturer, Model, NumberOfProcessors, SystemStartupOptions, TotalPhysicalMemory)
• Processors (prc_id, ExtClock, DeviceID, CpuStatus, CurrentClockSpeed)
• Disk drives (Idx, ConfigManagerErrorCode, ConfigManagerUserConfig, InterfaceTypeID, BytesPerSector, ModelID, Partitions, PowerManagementSupported, SCSIBus, SCSILogicalUnit, SCSIPort, SCSITargetId, Size, TotalCylinders, TotalHeads)
• NIC (card_id, AdapterTypeID, Availability, ConfigManagerErrorCode, ConfigManagerUserConfig, Idx, MACAddress, MaxSpeed, Speed, PowerManagementSupported, TimeOfLastReset)
• NIC configuration (Idx, DHCPEnabled, IPEnabled, IPFilterSecurityEnabled, IPXEnabled, TcpipNetbiosOptions)
• Operating System configuration (BootDevice, BuildNumber, BuildType, Caption, CodeSet, CountryCode, CSDVersion, CSName, CurrentTimeZone, Debug, Distrib, FreePhysicalMemory, FreeSpaceInPagingFiles, FreeVirtualMemory, InstallDate, LastBootUpTime, LocalDateTime, Locale, MaxProcessMemorySize, NumberOfProcesses, NumberOfUsers, OSLanguage, OSProductSuite, PAEEnabled, ServicePackMajorVersion, ServicePackMinorVersion, SizeStoredInPagingFiles, SystemDevice, SystemDirectory, TotalVirtualMemorySize, TotalVisibleMemorySize, Version, WindowsDirectory)
• Installed products (prod_id, InstallDate, InstallState)
• Installed services
• Running tasks (task_id, ProcessId, SessionId, CreationDate)
• Event Log configuration (type, FileSize, MaxFileSize, OverwriteOutDated, Name, OverwritePolicy, Status)
• Installed updates [using the registry] (qfe_id, InstalledDate, InstalledBy)

Outages and Events

• Operating system outages (start, finish, IsDirty, state, version, duration)
• SQL Server outages (start, finish, state, pid, version, duration)
• Event Log history (type, start, start_index, finish, finish_index, e_count, IsHistory, IsGap)
• Dr. Watson for SQL Server events (source, timegenerated, info[bucket])
• SQL Server access violations or assertions (time, message)
• Dumps [using the file system] (fname, fsize, ftime)

SQL Server (General)

• Instances of SQL Server (major, minor, build, version, edition, plevel)
• Syscurconfigs (value, config)
• Dr. Watson enabled (yes/no)
• SQL Errorlog (path, logsize, depth)
• Databases (dbid, name, CompatibilityLevel, CreateDate, DataSpaceUsage, IndexSpaceUsage, dbSize, SpaceAvailable, IsSystem, txSize, txSpaceAvailable, txLastBackup, oRecoveryModel, oPageVerify, oUserAccess, oAutoClose, oAutoShrink, oAutoCreateStat, oAutoUpdateStat, oOffline, oReadOnly, oSelectIntoBulkCopy, oTruncateLogOnCheckpoint, oPublished, oSubscribed, oMergePublished, oMergeSubscribed, oFullTextEnabled, oDbChaining)
• FileGroup (dbid, fgid, name, IsDefault, IsReadOnly, IsOffline, fgSize, type)
• DBfiles (dbid, fgid, fid, name, IsPrimary, IsReadOnly, physicalName, fSize, fSpaceUsed, MaxSize, GrowthType, Growth, IsLog)
• Running profiler traces (trace_id, property, value)
• Trace history (trace_id, start, finish)

SQL Server (SQL Server 2005 Only)

• Instances of SQL Server (Collation, EngineEdition, IsClustered, IsFullTextInstalled, IsIntegratedSecurityOnly, IsSingleUser, LicenseType, NumLicenses, IsAD)
• Databases (dbid, oBrokerEnabled, oReadCommittedSnapshotOn, SnapshotIsolationState, Collation, oDistributor, qIndexedViews, mirroringSafetyLevel, mirroringWitness, IsRosetta)
• Dataspaces (dbid, dsid, name, IsDefault, type)
• Partition schemes
• Partition functions (fnId, type, fanout, boundary_value_on_right)
• Partition parameters [aggregated]
• Database objects [aggregated]
• Columns [aggregated]
• Indexes [aggregated]
• Triggers [aggregated]
• Assemblies [aggregated]
• Assembly types [aggregated]
• Event notifications [aggregated]
• Notification subscriptions [aggregated]
• Cursors [aggregated]
• Endpoints [aggregated]
• Linked servers [aggregated]
• Internal tables [aggregated]
• Backups [aggregated]
• Text In Row property (OBJECTPROPERTY(‘TableTextInRowLimit’) ) [aggregated]
• Global Trace Flags (DBCC TRACESTATUS (-1))
• iMail settings (hostId, isProfile, isAccount)
• Service Broker settings [aggregated] (dbid, Routes, Queues, ActivatedQueues, MessageTypes, TypedMessageTypes)
• Reporting Services – number of reports run
• Full Text info [aggregated] (dbid, Catalogs, Tables, LastCrawl, BackgroundIndexes)
• Suspect Page table [aggregated]
• Certificates [aggregated]
• sys.dm_exec_query_optimizer_info

Best Practices Analyzer Tool for Microsoft SQL Server 2000 1.0

Microsoft SQL Server Best Practices Analyzer is a database management tool that lets you verify the implementation of common Best Practices. These best practices typically relate to the usage and administration aspects of SQL Server databases and ensure that your SQL Servers are managed and operated well.

January 2008 release of the SQL Server 2005 Best Practices Analyzer (BPA)

This free tool allows you to easily identify any issues such as Service Accounts running under the incorrect security context or the reuse of backup files.

The SQL Server 2005 Best Practices Analyzer (BPA) gathers data from Microsoft Windows and SQL Server configuration settings. BPA uses a predefined list of SQL Server 2005 recommendations and best practices to determine if there are potential issues in the database environment.