Home



Analyzed instance:

Summary tips:

[more]There are points of attention on your instance configuration.
[more] Warning: there are databases in FULL recovery model.
Action:
USE [master]
GO
ALTER DATABASE [__databaseName__] SET RECOVERY SIMPLE WITH NO_WAIT
GO
Databases involved: ( MDWT_2008R2_PT model Mondial ReportServer$SQL2008R2 rick SqlClrDemo sqlStart TDE TestCDC )
[more] Some databases have a percentage of free space less than 5%
Databases involved: ( AdventureWorks2008R2 AdventureWorksDW2008R2 AdventureWorksLT2008R2 AdventureWorks AdventureWorksDW AdventureWorksLT )
[more] There are databases with AUTO SHRINK option set to ON.
USE [master]
GO
ALTER DATABASE [__databaseName__] SET AUTO_SHRINK OFF
GO
Databases involved: ( AdventureWorks2008R2 )
[more] There are databases with AUTO CREATE STATISTICS OFF
USE [master]
GO
ALTER DATABASE [__databaseName__] SET AUTO_CREATE_STATISTICS ON
GO
Databases involved: ( AdventureWorks2008R2 AdventureWorks )
[more] There are databases with AUTO UPDATE STATISTICS option set to OFF.
USE [master]
GO
ALTER DATABASE [__databaseName__] SET AUTO_UPDATE_STATISTICS ON
GO
Databases involved: ( AdventureWorks2008R2 AdventureWorks )
[more] There are database with file on the C drive.
Putting system databases on the C drive expose to risk of crashing the server when it runs out of space.
[more] Some database have files on the C drive. Putting databases on the C drive expose to risk of crashing the server when the dick runs out of space.
[more] There are databases with Full or Bulk-Logged Recovery Model, with no transaction log backups since the last full backup
[more]The three main WAIT TYPE instance: PAGEIOLATCH_SH (39.03 %), OLEDB (32.93 %), IO_COMPLETION (6.92 %),
Please, check "WAIT ANALYSIS" paragraph on this document for more details
[more] Warning: To maximize the performance of the tempdb database it might be useful to have a number of files equal to the number of logical CPUs of the machine
[more] This may be wasted memory if we cache plans for queries that never get called again.
This may be a good use case for SQL Server 2008's Optimize for Ad Hoc Workloads or for Forced Parameterization
Example:
SP_CONFIGURE 'optimize for ad hoc workloads',1
RECONFIGURE
GO
[more]The 42.6 % of the cache is related to collections of objects less than an hour ago.
The higher this percentage, the greater the volatility'and'Execution Plans (careful compilations and recompilations)
[more] The analysis of waiting times shows a significant percentage (signal> 10) of "Signal Waits" compared to "Total Waits"
This is usually a symptom of CPU pressure
Possible solutions are:
- Add more CPUs or faster processors
- Remove unnecessary sort (attention to "order" and "group by"), joins, and compilations (and re-builds)
[more]The activity of reading and writing are slow, there might be a problem ([avg IO stall ms] < 50)
[more] Reads are averaging longer than 100ms for at least one database on this drive
Writes are averaging longer than 20ms for at least one database on this drive
Tips:
- Find tables without clustered indexes
- Find unused indexes
- Find missing indexes
- Find triggers
[more]Warning: there are NO maintenance activities for the following databases:
[AdventureWorks], [AdventureWorks2008R2], [AdventureWorksDW], [AdventureWorksDW2008R2], [AdventureWorksLT], [AdventureWorksLT2008R2], [MDWT_2008R2_PT], [Mondial], [rick], [SqlClrDemo], [sqlStart], [TDE], [TestCDC], [wpc2011cms]
[more]Warning: It is recommended to remove sample databases from a production system
[more]Warning: It is recommended to avoid granting unnecessary permissions to the PUBLIC role
[more]User Tables in the MASTER database may not be restored in the event of a disaster
[more]User tables in the MSDB database may not be restored in the event of a disaster
[more] Your instance is up to date. No service pack available for your instance.
[more] Some databases have a percentage of free space less than 10%
Databases involved: ( msdb AdventureWorks2008R2 AdventureWorksDW2008R2 AdventureWorksLT2008R2 AdventureWorks AdventureWorksDW AdventureWorksLT TDE )
[more] All databases are ONLINE.
[more] All databases are in READ/WRITE mode.
[more] No databases with AUTO CLOSE ON.
[more] The share of retrieved pages in memory directly, without accessing the disk, is very high.
No special reporting on.
[more] No issue on the available "Free Pages"
[more] No issue on the "Life Expectancy"
[more] No issue on the "Memory Grants Pending"
[more] No issues on database recovery model (SIMPLE, ok)
[more] No issues on fixed file size (AUTOGROWTH is correctly set)
[more]The db [AdventureWorks2008R2] is the database that is using more RAM
[more]The disk subsystem (I / O) does not appear to be under stress. Pending Disk IO Count = 0
[more]The CPU does not appear to be under stress. Runnable Tasks = 0
[more]No Errors in Event Log for the instance
[more]No significant points of attention on the user GUEST on user databases
[more]No significant issues on the Administrators group
[more]no stored procedures executed on startup of the instance.


ABALIEN\SQL2008R2


GET AVAILABLE AND FREE DISK SPACE    [top]

DriveCapacity (MB)Capacity (GB)Freespace (MB)Freespace (GB)
C:\695157679517360505


INSTANCE INFO    [top]

Instance Information
System Manufacturer: 'Alienware', System Model: 'M14xR1'.
Intel(R) Core(TM) i7-2630QM CPU @ 2.00GHz


PropertiesValue
SQL Server Authentication ModeMixed Authentication Mode
ServerABALIEN\SQL2008R2
Product Version10.50.2500.0 SP1
IsIntegratedSecurityOnlySQL + Integrated security
EngineEditionEnterprise Edition
ComputerNamePhysicalNetBIOSABALIEN
BuildClrVersionv2.0.50727
CollationLatin1_General_CI_AS
ProductEditionDeveloper Edition
IsClusteredNot Clustered
IsFullTextInstalledFull-text is installed
SqlSortOrderNamebin_ascii_8


INSTANCE OS    [top]

WindowsWin Service PackWin Language
Windows 7 / Windows Server 2008 R2Service Pack 11040


INSTANCE PROPERTIES    [top]

Physical memory MBVirtual memory MBbuffer pool committed MBbuffer pool commit targt MBbuffer pool visible MB
81398388607409640964096


Last restartminutes since restarthours since restartdays since restart
03/02/2012 10:15:26601,000,04


Logical CPU CountHyperthread RatioPhysical CPU Count (Sockets)Physical Memory (MB)
8818139


NameValueDescriptionnote
backup compression default0Enable compression of backups by default
clr enabled1CLR user code execution enabled in the server"SQLCLR is enabled". Verify if you are really using .Net Assemblies in databases.
lightweight pooling0User mode scheduler uses lightweight pooling
max degree of parallelism0maximum degree of parallelism
max server memory (MB)4096Maximum size of server memory (MB)
optimize for ad hoc workloads0When this option is set, plan cache size is further reduced for single-use adhoc OLTP workload. "optimize for ad hoc workloads" is off.
This option may improves the cache efficiency for execution plans for workloads that contains a lot of single use batch ad hoc.
When set to 1, at the first execution the database engine stores a small compiled plan stub in the plan cache, instead of the full compiled plan.
priority boost0Priority boost

There are points of attention on your instance configuration.


INSTANCE VERIFY SERVICE PACK    [top]

SQL Server Version in use
10.50.2500.0


Product VersionKB ArticleURLDescription
10.50.2769KB254479LinkCumulative update package 1 (CU1) for SQL Server 2008 R2 Service Pack 1
10.50.2772KB256771LinkCumulative update package 2 (CU2) for SQL Server 2008 R2 Service Pack 1

Your instance is up to date. No service pack available for your instance.


NON DEFAULT CONFIGURATION OPTIONS    [top]

Configuration OptionCurrent ValueSQL Server Default Value
affinity mask10
Agent XPs10
clr enabled10
max server memory (MB)40962147483647
min server memory (MB)160
show advanced options10
xp_cmdshell10


PORT NUMBER    [top]

ServerNamePort
ABALIEN\SQL2008R2


SETUP INSTANCE REGISTRY INFO    [top]

KeyNameData
HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQLServer\CurrentVersionCurrentVersion10.50.2500.0
HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQLServer\ParametersSQLArg0-dC:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\DATA\master.mdf
SQLArg1-eC:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\Log\ERRORLOG
SQLArg2-lC:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\DATA\mastlog.ldf
HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQLServer\SuperSocketNetLib\AdminConnection\TcpTcpDynamicPorts1198
DisplayNameTCP/IP
HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQLServer\SuperSocketNetLib\NpEnabled0
PipeName\\.\pipe\MSSQL$SQL2008R2\sql\query
DisplayNameNamed Pipes
HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQLServer\SuperSocketNetLib\SmEnabled1
DisplayNameShared Memory
HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQLServer\SuperSocketNetLib\TcpEnabled0
ListenOnAllIPs1
KeepAlive30000
DisplayNameTCP/IP
HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQLServer\SuperSocketNetLib\Tcp\IP1Enabled0
Active1
TcpPort
TcpDynamicPorts0
DisplayNameSpecific IP Address
IpAddressfe80::1ce0:466c:5bd5:c0c3%15
HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQLServer\SuperSocketNetLib\Tcp\IP10Enabled0
Active1
TcpPort
TcpDynamicPorts0
DisplayNameSpecific IP Address
IpAddress127.0.0.1
HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQLServer\SuperSocketNetLib\Tcp\IP11Enabled0
Active1
TcpPort
TcpDynamicPorts0
DisplayNameSpecific IP Address
IpAddressfe80::5efe:192.168.1.10%17
HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQLServer\SuperSocketNetLib\Tcp\IP12Enabled0
Active1
TcpPort
TcpDynamicPorts0
DisplayNameSpecific IP Address
IpAddress2001:0:5ef5:79fd:2c02:15b3:3f57:fef5
HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQLServer\SuperSocketNetLib\Tcp\IP13Enabled0
Active1
TcpPort
TcpDynamicPorts0
DisplayNameSpecific IP Address
IpAddressfe80::2c02:15b3:3f57:fef5%16
HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQLServer\SuperSocketNetLib\Tcp\IP2Enabled0
Active1
TcpPort
TcpDynamicPorts0
DisplayNameSpecific IP Address
IpAddress169.254.192.195
HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQLServer\SuperSocketNetLib\Tcp\IP3Enabled0
Active1
TcpPort
TcpDynamicPorts0
DisplayNameSpecific IP Address
IpAddressfe80::a945:c03f:fb67:df63%14
HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQLServer\SuperSocketNetLib\Tcp\IP4Enabled0
Active1
TcpPort
TcpDynamicPorts0
DisplayNameSpecific IP Address
IpAddress169.254.223.99
HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQLServer\SuperSocketNetLib\Tcp\IP5Enabled0
Active1
TcpPort
TcpDynamicPorts0
DisplayNameSpecific IP Address
IpAddressfe80::f8e8:3b10:6bb3:2da1%12
HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQLServer\SuperSocketNetLib\Tcp\IP6Enabled0
Active1
TcpPort
TcpDynamicPorts0
DisplayNameSpecific IP Address
IpAddress192.168.1.10
HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQLServer\SuperSocketNetLib\Tcp\IP7Enabled0
Active1
TcpPort
TcpDynamicPorts0
DisplayNameSpecific IP Address
IpAddressfe80::b863:d78e:4b48:82cc%11
HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQLServer\SuperSocketNetLib\Tcp\IP8Enabled0
Active1
TcpPort
TcpDynamicPorts0
DisplayNameSpecific IP Address
IpAddress169.254.130.204
HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQLServer\SuperSocketNetLib\Tcp\IP9Enabled0
Active1
TcpPort
TcpDynamicPorts0
DisplayNameSpecific IP Address
IpAddress::1
HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQLServer\SuperSocketNetLib\Tcp\IPAllTcpPort
TcpDynamicPorts0
DisplayNameAny IP Address
HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQLServer\SuperSocketNetLib\ViaEnabled0
DefaultServerPort0:1433
ListenInfo0:1433
DisplayNameVIA
HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL10_50.SQL2008R2\SQLServerAgentErrorLoggingLevel3
JobHistoryMaxRows1000
JobHistoryMaxRowsPerJob100
WorkingDirectoryC:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\JOBS
HKLM\SYSTEM\CurrentControlSet\Services\MSSQL$SQL2008R2ObjectName.\sqlUser
ImagePath"C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\Binn\sqlservr.exe" -sSQL2008R2
Start3
HKLM\SYSTEM\CurrentControlSet\Services\SQLAgent$SQL2008R2ObjectName.\sqlUser
ImagePath"C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\Binn\SQLAGENT.EXE" -i SQL2008R2
Start3
DependOnServiceMSSQL$SQL2008R2


SQL SERVER SERVICE CHECK UTILITY    [top]

Physical Server NameSQL Instance NameSQL Server ServicesCurrent Service Service StatusDate/Time Service Status Checked
ABALIENSQL2008R2MS SQL Server ServiceRunning.03/02/2012 11:15:34
SQL2008R2SQL Server Agent ServiceStopped.03/02/2012 11:15:34
SQL2008R2SQL Browser Service - Instance IndependentStopped.03/02/2012 11:15:34
SQL2008R2Intergration Service - Instance IndependentNOT INSTALLED03/02/2012 11:15:34
SQL2008R2Reporting ServiceStopped.03/02/2012 11:15:34
SQL2008R2Analysis ServicesStopped.03/02/2012 11:15:34
SQL2008R2Full Text Search ServiceNOT INSTALLED03/02/2012 11:15:34


LIST DATABASES    [top]

DB NameLogical NameStatusRecoveryDB CompabilityDB CollationDB CreatorCase Insensitive
AdventureWorksAdventureWorks_Data
21.57 Mb
ONLINE
READ_WRITE
MULTI_USER
SIMPLE100Latin1_General_CI_ASABAlien\Andrea Benedetti
29/10/2011 15:51:16
Yes
AdventureWorks_Log
0.25 Mb
ONLINE
READ_WRITE
MULTI_USER
SIMPLE100Latin1_General_CI_ASABAlien\Andrea Benedetti
29/10/2011 15:51:16
Yes
AdventureWorks2008R2AdventureWorks2008R2_Data
23.12 Mb
ONLINE
READ_WRITE
MULTI_USER
SIMPLE100Latin1_General_CI_ASABAlien\Andrea Benedetti
29/10/2011 15:50:24
Yes
AdventureWorks2008R2_Log
0.25 Mb
ONLINE
READ_WRITE
MULTI_USER
SIMPLE100Latin1_General_CI_ASABAlien\Andrea Benedetti
29/10/2011 15:50:24
Yes
DataFile
0.13 Mb
ONLINE
READ_WRITE
MULTI_USER
SIMPLE100Latin1_General_CI_ASABAlien\Andrea Benedetti
29/10/2011 15:50:24
Yes
DataFile2
255.63 Mb
ONLINE
READ_WRITE
MULTI_USER
SIMPLE100Latin1_General_CI_ASABAlien\Andrea Benedetti
29/10/2011 15:50:24
Yes
AdventureWorksDWAdventureWorksDW_Data
8.76 Mb
ONLINE
READ_WRITE
MULTI_USER
SIMPLE100Latin1_General_CI_ASABAlien\Andrea Benedetti
29/10/2011 15:51:48
Yes
AdventureWorksDW_Log
0.25 Mb
ONLINE
READ_WRITE
MULTI_USER
SIMPLE100Latin1_General_CI_ASABAlien\Andrea Benedetti
29/10/2011 15:51:48
Yes
AdventureWorksDW2008R2AdventureWorksDW2008R2_Data
9.70 Mb
ONLINE
READ_WRITE
MULTI_USER
SIMPLE100Latin1_General_CI_ASABAlien\Andrea Benedetti
29/10/2011 15:51:04
Yes
AdventureWorksDW2008R2_Log
0.25 Mb
ONLINE
READ_WRITE
MULTI_USER
SIMPLE100Latin1_General_CI_ASABAlien\Andrea Benedetti
29/10/2011 15:51:04
Yes
AdventureWorksLTAdventureWorksLT_Data
0.65 Mb
ONLINE
READ_WRITE
MULTI_USER
SIMPLE100Latin1_General_CI_ASABAlien\Andrea Benedetti
29/10/2011 15:52:01
Yes
AdventureWorksLT_Log
0.25 Mb
ONLINE
READ_WRITE
MULTI_USER
SIMPLE100Latin1_General_CI_ASABAlien\Andrea Benedetti
29/10/2011 15:52:01
Yes
AdventureWorksLT2008R2AdventureWorksLT2008R2_Data
0.65 Mb
ONLINE
READ_WRITE
MULTI_USER
SIMPLE100Latin1_General_CI_ASABAlien\Andrea Benedetti
29/10/2011 15:51:14
Yes
AdventureWorksLT2008R2_Log
0.25 Mb
ONLINE
READ_WRITE
MULTI_USER
SIMPLE100Latin1_General_CI_ASABAlien\Andrea Benedetti
29/10/2011 15:51:14
Yes
MDWT_2008R2_PTMDWT_2008R2_PT
0.28 Mb
ONLINE
READ_WRITE
MULTI_USER
FULL100Latin1_General_CI_ASABAlien\Andrea Benedetti
15/12/2011 10:50:44
Yes
MDWT_2008R2_PT_log
0.07 Mb
ONLINE
READ_WRITE
MULTI_USER
FULL100Latin1_General_CI_ASABAlien\Andrea Benedetti
15/12/2011 10:50:44
Yes
MDWT_2008R2_Data1
0.13 Mb
ONLINE
READ_WRITE
MULTI_USER
FULL100Latin1_General_CI_ASABAlien\Andrea Benedetti
15/12/2011 10:50:44
Yes
MDWT_2008R2_Data2
0.13 Mb
ONLINE
READ_WRITE
MULTI_USER
FULL100Latin1_General_CI_ASABAlien\Andrea Benedetti
15/12/2011 10:50:44
Yes
MDWT_2008R2_Data3
0.13 Mb
ONLINE
READ_WRITE
MULTI_USER
FULL100Latin1_General_CI_ASABAlien\Andrea Benedetti
15/12/2011 10:50:44
Yes
MDWT_2008R2_Data4
0.13 Mb
ONLINE
READ_WRITE
MULTI_USER
FULL100Latin1_General_CI_ASABAlien\Andrea Benedetti
15/12/2011 10:50:44
Yes
MDWT_2008R2_Data5
0.13 Mb
ONLINE
READ_WRITE
MULTI_USER
FULL100Latin1_General_CI_ASABAlien\Andrea Benedetti
15/12/2011 10:50:44
Yes
MDWT_2008R2_Data6
0.13 Mb
ONLINE
READ_WRITE
MULTI_USER
FULL100Latin1_General_CI_ASABAlien\Andrea Benedetti
15/12/2011 10:50:44
Yes
MondialMondial
0.41 Mb
ONLINE
READ_WRITE
MULTI_USER
FULL100Latin1_General_CI_ASABAlien\Andrea Benedetti
11/11/2011 17:01:12
Yes
Mondial_log
0.10 Mb
ONLINE
READ_WRITE
MULTI_USER
FULL100Latin1_General_CI_ASABAlien\Andrea Benedetti
11/11/2011 17:01:12
Yes
ReportServer$SQL2008R2ReportServer$SQL2008R2
0.53 Mb
ONLINE
READ_WRITE
MULTI_USER
FULL100Latin1_General_CI_AS_KS_WSABAlien\Andrea Benedetti
25/10/2011 23:29:08
Yes
ReportServer$SQL2008R2_log
0.78 Mb
ONLINE
READ_WRITE
MULTI_USER
FULL100Latin1_General_CI_AS_KS_WSABAlien\Andrea Benedetti
25/10/2011 23:29:08
Yes
ReportServer$SQL2008R2TempDBReportServer$SQL2008R2TempDB
0.28 Mb
ONLINE
READ_WRITE
MULTI_USER
SIMPLE100Latin1_General_CI_AS_KS_WSABAlien\Andrea Benedetti
25/10/2011 23:29:08
Yes
ReportServer$SQL2008R2TempDB_log
0.10 Mb
ONLINE
READ_WRITE
MULTI_USER
SIMPLE100Latin1_General_CI_AS_KS_WSABAlien\Andrea Benedetti
25/10/2011 23:29:08
Yes
rickrick
0.28 Mb
ONLINE
READ_WRITE
MULTI_USER
FULL100Latin1_General_CI_ASABAlien\Andrea Benedetti
01/12/2011 17:32:47
Yes
rick_log
0.10 Mb
ONLINE
READ_WRITE
MULTI_USER
FULL100Latin1_General_CI_ASABAlien\Andrea Benedetti
01/12/2011 17:32:47
Yes
SqlClrDemoSqlClrDemo
0.38 Mb
ONLINE
READ_WRITE
MULTI_USER
FULL100Latin1_General_CI_ASABAlien\Andrea Benedetti
08/01/2012 14:18:04
Yes
SqlClrDemo_log
0.13 Mb
ONLINE
READ_WRITE
MULTI_USER
FULL100Latin1_General_CI_ASABAlien\Andrea Benedetti
08/01/2012 14:18:04
Yes
sqlStartsqlStart
5.03 Mb
ONLINE
READ_WRITE
MULTI_USER
FULL100Latin1_General_CI_ASABAlien\Andrea Benedetti
11/11/2011 14:23:23
Yes
sqlStart_log
15.37 Mb
ONLINE
READ_WRITE
MULTI_USER
FULL100Latin1_General_CI_ASABAlien\Andrea Benedetti
11/11/2011 14:23:23
Yes
TDETDE
0.28 Mb
ONLINE
READ_WRITE
MULTI_USER
FULL100Latin1_General_CI_ASABAlien\Andrea Benedetti
16/12/2011 10:01:52
Yes
TDE_log
0.13 Mb
ONLINE
READ_WRITE
MULTI_USER
FULL100Latin1_General_CI_ASABAlien\Andrea Benedetti
16/12/2011 10:01:52
Yes
TestCDCTestCDC
0.41 Mb
ONLINE
READ_WRITE
MULTI_USER
FULL100Latin1_General_CI_ASABAlien\Andrea Benedetti
16/12/2011 10:58:01
Yes
TestCDC_log
0.29 Mb
ONLINE
READ_WRITE
MULTI_USER
FULL100Latin1_General_CI_ASABAlien\Andrea Benedetti
16/12/2011 10:58:01
Yes
wpc2011cmsWPC2011cms
6.25 Mb
ONLINE
READ_WRITE
MULTI_USER
SIMPLE100SQL_Latin1_General_CP1_CI_ASABAlien\Andrea Benedetti
02/11/2011 11:51:17
Yes
WPC2011cms_log
6.50 Mb
ONLINE
READ_WRITE
MULTI_USER
SIMPLE100SQL_Latin1_General_CP1_CI_ASABAlien\Andrea Benedetti
02/11/2011 11:51:17
Yes

Warning: there are databases in FULL recovery model.
Action:
USE [master]
GO
ALTER DATABASE [__databaseName__] SET RECOVERY SIMPLE WITH NO_WAIT
GO
Databases involved: ( MDWT_2008R2_PT model Mondial ReportServer$SQL2008R2 rick SqlClrDemo sqlStart TDE TestCDC )


Database NameFile NameFile TypeDB Size (Mb)DB Free (Mb)growthGrowth UnitsGrow Max Size (Mb)Increment Suggested (!)
AdventureWorksAdventureWorks_DataData1721204816Mb20 MB
AdventureWorks_LogLog22204816Mb10 MB
AdventureWorks2008R2AdventureWorks2008R2_DataData18437204816Mb20 MB
AdventureWorks2008R2_LogLog21204816Mb10 MB
DataFileData111281Mb10 MB
DataFile2Data204501281Mb10% (or higher)
FileStreamDocuments2008R2100Mb010 MB
AdventureWorksDWAdventureWorksDW_DataData701204816Mb10 MB
AdventureWorksDW_LogLog22204816Mb10 MB
AdventureWorksDW2008R2AdventureWorksDW2008R2_DataData772204816Mb10 MB
AdventureWorksDW2008R2_LogLog22204816Mb10 MB
AdventureWorksLTAdventureWorksLT_DataData50204816Mb10 MB
AdventureWorksLT_LogLog22204816Mb10 MB
AdventureWorksLT2008R2AdventureWorksLT2008R2_DataData50204816Mb10 MB
AdventureWorksLT2008R2_LogLog22204816Mb10 MB
mastermasterData421010%10 MB
mastlogLog111010%10 MB
MDWT_2008R2_PTMDWT_2008R2_Data1Data111281Mb1280010 MB
MDWT_2008R2_Data2Data111281Mb1280010 MB
MDWT_2008R2_Data3Data111281Mb1280010 MB
MDWT_2008R2_Data4Data111281Mb1280010 MB
MDWT_2008R2_Data5Data111281Mb1280010 MB
MDWT_2008R2_Data6Data111281Mb1280010 MB
MDWT_2008R2_PTData211281Mb10 MB
MDWT_2008R2_PT_logLog111010%10 MB
modelmodeldevData211281Mb10 MB
modellogLog111010%10 MB
MondialMondialData311281Mb10 MB
Mondial_logLog111010%10 MB
msdbMSDBDataData1611010%10 MB
MSDBLogLog761010%10 MB
ReportServer$SQL2008R2ReportServer$SQL2008R2Data411281Mb10 MB
ReportServer$SQL2008R2_logLog661010%10 MB
ReportServer$SQL2008R2TempDBReportServer$SQL2008R2TempDBData211281Mb10 MB
ReportServer$SQL2008R2TempDB_logLog111010%10 MB
rickrickData211281Mb10 MB
rick_logLog111010%10 MB
SqlClrDemoSqlClrDemoData321281Mb10 MB
SqlClrDemo_logLog111010%10 MB
sqlStartsqlStartData40101281Mb10 MB
sqlStart_logLog1221171010%20 MB
TDETDEData211281Mb10 MB
TDE_logLog111010%10 MB
tempdbtempdevData19171010%10 MB
templogLog111010%10 MB
TestCDCTestCDCData311281Mb10 MB
TestCDC_logLog221010%10 MB
wpc2011cmsWPC2011cmsData50471010%10 MB
WPC2011cms_logLog52451010%10 MB


DB NameFile Size MBLogical FileStatusUpdateabilityRecoveryFree Space MBFree Space %
AdventureWorks172AdventureWorks_DataONLINEREAD_WRITESIMPLE11.00
2AdventureWorks_LogONLINEREAD_WRITESIMPLE160.00
AdventureWorks2008R2184AdventureWorks2008R2_DataONLINEREAD_WRITESIMPLE3720.00
2AdventureWorks2008R2_LogONLINEREAD_WRITESIMPLE043.00
1DataFileONLINEREAD_WRITESIMPLE094.00
2045DataFile2ONLINEREAD_WRITESIMPLE00.00
AdventureWorksDW70AdventureWorksDW_DataONLINEREAD_WRITESIMPLE01.00
2AdventureWorksDW_LogONLINEREAD_WRITESIMPLE179.00
AdventureWorksDW2008R277AdventureWorksDW2008R2_DataONLINEREAD_WRITESIMPLE12.00
2AdventureWorksDW2008R2_LogONLINEREAD_WRITESIMPLE185.00
AdventureWorksLT5AdventureWorksLT_DataONLINEREAD_WRITESIMPLE02.00
2AdventureWorksLT_LogONLINEREAD_WRITESIMPLE168.00
AdventureWorksLT2008R25AdventureWorksLT2008R2_DataONLINEREAD_WRITESIMPLE02.00
2AdventureWorksLT2008R2_LogONLINEREAD_WRITESIMPLE168.00
master4masterONLINEREAD_WRITESIMPLE127.00
1mastlogONLINEREAD_WRITESIMPLE049.00
MDWT_2008R2_PT1MDWT_2008R2_Data1ONLINEREAD_WRITEFULL094.00
1MDWT_2008R2_Data2ONLINEREAD_WRITEFULL088.00
1MDWT_2008R2_Data3ONLINEREAD_WRITEFULL088.00
1MDWT_2008R2_Data4ONLINEREAD_WRITEFULL088.00
1MDWT_2008R2_Data5ONLINEREAD_WRITEFULL088.00
1MDWT_2008R2_Data6ONLINEREAD_WRITEFULL094.00
2MDWT_2008R2_PTONLINEREAD_WRITEFULL033.00
0MDWT_2008R2_PT_logONLINEREAD_WRITEFULL019.00
model2modeldevONLINEREAD_WRITEFULL036.00
0modellogONLINEREAD_WRITEFULL067.00
Mondial3MondialONLINEREAD_WRITEFULL010.00
0Mondial_logONLINEREAD_WRITEFULL059.00
msdb16MSDBDataONLINEREAD_WRITESIMPLE17.00
7MSDBLogONLINEREAD_WRITESIMPLE685.00
ReportServer$SQL2008R24ReportServer$SQL2008R2ONLINEREAD_WRITEFULL021.00
6ReportServer$SQL2008R2_logONLINEREAD_WRITEFULL585.00
ReportServer$SQL2008R2TempDB2ReportServer$SQL2008R2TempDBONLINEREAD_WRITESIMPLE039.00
0ReportServer$SQL2008R2TempDB_logONLINEREAD_WRITESIMPLE049.00
rick2rickONLINEREAD_WRITEFULL017.00
0rick_logONLINEREAD_WRITEFULL050.00
SqlClrDemo3SqlClrDemoONLINEREAD_WRITEFULL148.00
1SqlClrDemo_logONLINEREAD_WRITEFULL063.00
sqlStart40sqlStartONLINEREAD_WRITEFULL924.00
122sqlStart_logONLINEREAD_WRITEFULL11796.00
TDE2TDEONLINEREAD_WRITEFULL039.00
1TDE_logONLINEREAD_WRITEFULL07.00
tempdb19tempdevONLINEREAD_WRITESIMPLE1685.00
1templogONLINEREAD_WRITESIMPLE053.00
TestCDC3TestCDCONLINEREAD_WRITEFULL017.00
2TestCDC_logONLINEREAD_WRITEFULL173.00
wpc2011cms50WPC2011cmsONLINEREAD_WRITESIMPLE4694.00
52WPC2011cms_logONLINEREAD_WRITESIMPLE4486.00

Some databases have a percentage of free space less than 5%
Databases involved: ( AdventureWorks2008R2 AdventureWorksDW2008R2 AdventureWorksLT2008R2 AdventureWorks AdventureWorksDW AdventureWorksLT )
Some databases have a percentage of free space less than 10%
Databases involved: ( msdb AdventureWorks2008R2 AdventureWorksDW2008R2 AdventureWorksLT2008R2 AdventureWorks AdventureWorksDW AdventureWorksLT TDE )


Database NameLog Size (MB)Log Space Used (%)Status
master1,24218851,572330
tempdb1,24218847,484280
model0,742187534,210530
msdb7,42968814,82650
ReportServer$SQL2008R26,24218815,081350
ReportServer$SQL2008R2TempDB0,804687551,456310
AdventureWorks2008R21,99218857,450980
AdventureWorksDW2008R21,99218815,098040
AdventureWorksLT2008R21,99218831,960780
AdventureWorks1,99218840,588230
AdventureWorksDW1,99218820,980390
AdventureWorksLT1,99218831,960780
wpc2011cms51,9921914,252440
sqlStart122,92974,3152210
Mondial0,804687541,747570
rick0,804687550,485440
TestCDC2,30468826,779660
MDWT_2008R2_PT0,554687581,690140
TDE1,05468894,074070
SqlClrDemo0,992187538,188980


DB with Auto Shrink ON
AdventureWorks2008R2

There are databases with AUTO SHRINK option set to ON.
USE [master]
GO
ALTER DATABASE [__databaseName__] SET AUTO_SHRINK OFF
GO
Databases involved: ( AdventureWorks2008R2 )


DB with Auto Create Stats OFF
AdventureWorks
AdventureWorks2008R2

There are databases with AUTO CREATE STATISTICS OFF
USE [master]
GO
ALTER DATABASE [__databaseName__] SET AUTO_CREATE_STATISTICS ON
GO
Databases involved: ( AdventureWorks2008R2 AdventureWorks )


DB with Auto Update Stats OFF
AdventureWorks
AdventureWorks2008R2

There are databases with AUTO UPDATE STATISTICS option set to OFF.
USE [master]
GO
ALTER DATABASE [__databaseName__] SET AUTO_UPDATE_STATISTICS ON
GO
Databases involved: ( AdventureWorks2008R2 AdventureWorks )

All databases are ONLINE.
All databases are in READ/WRITE mode.
No databases with AUTO CLOSE ON.


LIST SYSTEM DATABASE ON C DRIVE    [top]

Database
master
model
msdb
tempdb

There are database with file on the C drive.
Putting system databases on the C drive expose to risk of crashing the server when it runs out of space.


LIST USER DATABASE ON C DRIVE    [top]

Database
AdventureWorks
AdventureWorks2008R2
AdventureWorksDW
AdventureWorksDW2008R2
AdventureWorksLT
AdventureWorksLT2008R2
MDWT_2008R2_PT
Mondial
ReportServer$SQL2008R2
ReportServer$SQL2008R2TempDB
rick
SqlClrDemo
sqlStart
TDE
TestCDC
wpc2011cms

Some database have files on the C drive. Putting databases on the C drive expose to risk of crashing the server when the dick runs out of space.


TRANSACTION LOG STATISTICS    [top]

Database NameRecovery ModelLog Reuse Wait DescriptionLog Size (MB)Log Used (MB)Log Used %DB Compatibility LevelPage Verify Option
AdventureWorksSIMPLENOTHING1,990,8141,00100CHECKSUM
AdventureWorks2008R2SIMPLENOTHING1,991,1457,00100CHECKSUM
AdventureWorksDWSIMPLENOTHING1,990,4221,00100CHECKSUM
AdventureWorksDW2008R2SIMPLENOTHING1,990,3015,00100CHECKSUM
AdventureWorksLTSIMPLENOTHING1,990,6432,00100CHECKSUM
AdventureWorksLT2008R2SIMPLENOTHING1,990,6432,00100CHECKSUM
MDWT_2008R2_PTFULLNOTHING0,550,4582,00100CHECKSUM
MondialFULLNOTHING0,800,3442,00100CHECKSUM
ReportServer$SQL2008R2FULLNOTHING6,240,9415,00100CHECKSUM
ReportServer$SQL2008R2TempDBSIMPLENOTHING0,800,4151,00100CHECKSUM
rickFULLNOTHING0,800,4150,00100CHECKSUM
SqlClrDemoFULLNOTHING0,990,3838,00100CHECKSUM
sqlStartFULLNOTHING122,935,304,00100CHECKSUM
TDEFULLLOG_BACKUP1,050,9994,00100CHECKSUM
TestCDCFULLNOTHING2,300,6227,00100CHECKSUM
wpc2011cmsSIMPLENOTHING51,997,4114,00100CHECKSUM


No information to report


BACKUP INFORMATION    [top]

DB NameAccess StateOnline/OfflineIn StandbyLast BackUp TakenBackup Size (MB)Days since BackupUser Name
AdventureWorksMULTI_USERONLINENo16/12/2011 10:44:04172,08249ABAlien\Andrea Benedetti
TDEMULTI_USERONLINENo16/12/2011 10:07:251,45507849ABAlien\Andrea Benedetti
wpc2011cmsMULTI_USERONLINENo02/11/2011 11:47:583,07519593VIRTUO\abenedetti


DB NameMost Recent DB Backup
AdventureWorks16/12/2011 10:44:04
tde16/12/2011 10:07:25
wpc2011cms02/11/2011 11:47:58


DB nameRecovery ModelLast Log BCKLast Data BCK
MDWT_2008R2_PTFULL
modelFULL
MondialFULL
ReportServer$SQL2008R2FULL
rickFULL
SqlClrDemoFULL
sqlStartFULL
TDEFULL16/12/2011 10:07:25
TestCDCFULL

There are databases with Full or Bulk-Logged Recovery Model, with no transaction log backups since the last full backup


WAIT ANALYSIS    [top]

Wait TypeWait SecNums di Wait%Avg Wait SecNote
PAGEIOLATCH_SH96,735931139,030,00Occurs when a task is waiting on a latch for a buffer that is in an I/O request. The latch request is in Shared mode. Long waits may indicate problems with the disk subsystem.
OLEDB81,61111895932,930,00Occurs when SQL Server calls the SQL Server Native Client OLE DB Provider. This wait type is not used for synchronization. Instead, it indicates the duration of calls to the OLE DB provider.
IO_COMPLETION17,1416246,920,01Occurs while waiting for I/O operations to complete. This wait type generally represents non-data page I/Os. Data page I/O completion waits appear as PAGEIOLATCH_* waits.
LCK_M_S16,2376,552,32Occurs when a task is waiting to acquire a Shared lock. For a lock compatibility matrix, see sys.dm_tran_locks (Transact-SQL).
PREEMPTIVE_OS_CREATEFILE11,281874,550,06
SLEEP_DBSTARTUP5,41512,180,11Occurs during database startup while waiting for all databases to recover.
PREEMPTIVE_OS_PIPEOPS4,5541,841,14
SLEEP_TEMPDBSTARTUP1,8150,730,36Occurs while a task waits for tempdb to complete startup.
WRITELOG1,8032050,730,00Occurs while waiting for a log flush to complete. Common operations that cause log flushes are checkpoints and transaction commits.

The three main WAIT TYPE instance: PAGEIOLATCH_SH (39.03 %), OLEDB (32.93 %), IO_COMPLETION (6.92 %),
Please, check "WAIT ANALYSIS" paragraph on this document for more details


PERFORMANCE COUNTERS    [top]

ObjectCounterValueNote
MSSQL$SQL2008R2:Buffer Manager Buffer cache hit ratio100,00 The value represents the percentage of pages retrieved in memory, no disk access.
This value should be as close as possible to 100
Free pages213175,00 Total number of data pages available in cache
A value less than (DataCacheSizeInGB/4GB * 300) could indicate a "memory pressure" problem
Page life expectancy1064,00 The value represents how long a data page resides in the buffer cache
An average time less than 300 seconds indicates that the pages are downloaded too frequently from disk
This represents a possible excess of disk activity
MSSQL$SQL2008R2:Memory Manager Memory Grants Pending0,00 The value represents the total number of processes waiting for a workspace
A value greater than 0 would indicate a memory problem

The share of retrieved pages in memory directly, without accessing the disk, is very high.
No special reporting on.
No issue on the available "Free Pages"
No issue on the "Life Expectancy"
No issue on the "Memory Grants Pending"


PERFORMANCE COUNTERS LOCKS    [top]

ObjectCounterValueNote
MSSQL$SQL2008R2:Locks Lock Wait Time (sec)16,55OK (value < 60)
Number of Deadlocks/sec0,00OK (value < 1)


TEMPDB OBJECTS    [top]

FileEntire file page count
C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\DATA\tempdb.mdf1024


Free Space (MB)Internal Objects (MB)User Objects (MB)Version Store (MB)
16100


TEMPDB TIPS    [top]

FileFile SizeMax SizeGrowthIncrementIncrement Suggested (!)
tempdev19.00 Mb Unlimited file size auto grouth 10%10 MB
templog1.25 Mb Unlimited file size auto grouth 10%10 MB

Warning: To maximize the performance of the tempdb database it might be useful to have a number of files equal to the number of logical CPUs of the machine
No issues on database recovery model (SIMPLE, ok)
No issues on fixed file size (AUTOGROWTH is correctly set)

Knowledge base:
Optimizing tempdb Performance
Working with tempdb in SQL Server 2005


CACHE SINGLE USE PLANS    [top]

Procedure Cache Info
349 query plans are taking up 42.367187 MB in the procedure cache

This may be wasted memory if we cache plans for queries that never get called again.
This may be a good use case for SQL Server 2008's Optimize for Ad Hoc Workloads or for Forced Parameterization
Example:
SP_CONFIGURE 'optimize for ad hoc workloads',1
RECONFIGURE
GO


CACHE SPACE    [top]

Plan cache in GB
0,860


NameTypeCache KbCache MbEntries count
SQL PlansCACHESTORE_SQLCP106648104581
Bound TreesCACHESTORE_PHDR4208841248
Object PlansCACHESTORE_OBJCP6064516
SystemRowsetStoreCACHESTORE_SYSTEMROWSET37123673
TokenAndPermUserStoreUSERSTORE_TOKENPERM864096
Broker dormant rowsetsCACHESTORE_BROKERTBLACS560035
SecCtxtACRUserStore-60-261USERSTORE_TOKENPERM1680951
Extended Stored ProceduresCACHESTORE_XPROC6406
Service broker mapping tableCACHESTORE_BROKERREADONLY5606
FTSTOPLIST_CACHESTORECACHESTORE_FULLTEXTSTOPLIST5601
ClrProcCacheCACHESTORE_CLRPROC4001
View Definition CacheCACHESTORE_VIEWDEFINITIONS2402
XMLDBCACHECACHESTORE_XMLDBTYPE1601
Temporary Tables & Table VariablesCACHESTORE_TEMPTABLES1605
sxcCacheStoreUSERSTORE_SXC1604
ACRUserStore-48-4-20USERSTORE_TOKENPERM801
SecCtxtACRUserStore-1-1USERSTORE_TOKENPERM803
ClrUdtUdaggCacheCACHESTORE_CLRUDTINFO802
SOS_StackFramesStoreCACHESTORE_STACKFRAMES801
SecCtxtACRUserStore-67-261USERSTORE_TOKENPERM801


Note
- CACHESTORE_OBJCP: compiled plans for stored procedures, functions and triggers - CACHESTORE_SQLCP: SQL statements or batches that do not reside in stored procedures, functions or triggers. For example, dynamic SQL SELECT statements, or sent directly to the server - CACHESTORE_PHDR: algebrizer trees for views, constraints and defaults. The algebrizer tree is the mechanism that resolves the parse table names and columns.


ObjectTypeDBsingle use countmulti use countsingle use size MBmulti use size MBtotal size MB
CLR Compiled FuncProc01000
Compiled PlanAdhoc349180424486
Prepared30227614
Procmaster01000
Procmsdb22000
ProcresourceDb011055
Extended ProcProcresourceDb06000
Parse TreeCheck01000
UsrTab21000
ViewAdventureWorks2008R20901818
Viewmsdb03000
ViewresourceDb023202222


TOP 30 USE COUNT STORED PROCEDURES    [top]

No information to report


AGE OF ITEMS IN PROCEDURE CACHE    [top]

Age in hoursCount at this agePercentage of totalLast execution time
049342,603/02/2012 11:15:37
166357,403/02/2012 11:15:37

The 42.6 % of the cache is related to collections of objects less than an hour ago.
The higher this percentage, the greater the volatility'and'Execution Plans (careful compilations and recompilations)


note
The information indicates the age of objects in the cache (and therefore how long the execution plans are drawn up), how many, their percentage of the total were performed and when the last time


SIGNAL WAITS    [top]

% signal (cpu) waits % resource waits
18,2981,71

The analysis of waiting times shows a significant percentage (signal> 10) of "Signal Waits" compared to "Total Waits"
This is usually a symptom of CPU pressure
Possible solutions are:
- Add more CPUs or faster processors
- Remove unnecessary sort (attention to "order" and "group by"), joins, and compilations (and re-builds)


TOTAL WORKER TIME    [top]

No information to report


Note
Total Worker time = sum of the CPU time used for the execution of implementation plans from their compilation.


CACHE AND MEMORY UTILIZATION    [top]

Database NameCached pages countCached Size (MB)
AdventureWorks2008R22800812188,132812
ReportServer$SQL2008R23072,398437
AdventureWorks2842,218750
AdventureWorksDW1961,531250
AdventureWorksDW2008R21901,484375
wpc2011cms1841,437500
MDWT_2008R2_PT1831,429687
TestCDC1751,367187
AdventureWorksLT1681,312500
AdventureWorksLT2008R21681,312500
Mondial1531,195312
SqlClrDemo1521,187500
sqlStart1521,187500
ReportServer$SQL2008R2TempDB1501,171875
rick1461,140625
TDE1461,140625

The db [AdventureWorks2008R2] is the database that is using more RAM


MEMORY CLEAN AND DIRTY BY DATABASE    [top]

Database NamePage StatePage count (data and index) in memory
AdventureWorksClean284
AdventureWorks2008R2Clean280021
Dirty60
AdventureWorksDWClean196
AdventureWorksDW2008R2Clean190
AdventureWorksLTClean168
AdventureWorksLT2008R2Clean168
MDWT_2008R2_PTClean183
MondialClean153
ReportServer$SQL2008R2Clean307
ReportServer$SQL2008R2TempDBClean150
rickClean146
SqlClrDemoClean152
sqlStartClean152
TDEClean146
TestCDCClean175
wpc2011cmsClean184


MEMORY STATE    [top]

Total Physical Memory (MB)Available Physical Memory (MB)State
8139,859999,289Available physical memory is high


MEMORY UTILIZATION BY DATABASE    [top]

Database NameBuffered Page Count%
AdventureWorks2008R228008196,93
ReportServer$SQL2008R23070,11
AdventureWorks2840,10
AdventureWorksDW1960,07
AdventureWorksDW2008R21900,07
AdventureWorksLT1680,06
AdventureWorksLT2008R21680,06
MDWT_2008R2_PT1830,06
TestCDC1750,06
wpc2011cms1840,06
Mondial1530,05
ReportServer$SQL2008R2TempDB1500,05
rick1460,05
SqlClrDemo1520,05
sqlStart1520,05
TDE1460,05


IO STALL    [top]

DBNum readsNum writesIO stallstotal IOavg IO stall ms
AdventureWorks2008R2406841881013284240872247,91
AdventureWorksDW2008R2271329528113,62
AdventureWorksDW281307829102,60
AdventureWorks39133954082,80
AdventureWorks2008R22646202229286284880,48
Mondial23116842467,36
rick22115132363,04
746761156,33
TDE21111122248,35
ReportServer$SQL2008R2TempDB22110352343,13
AdventureWorksLT2008R224110722541,23
SqlClrDemo2219762340,67
AdventureWorksLT855521339,43
MDWT_2008R2_PT2219212338,38
ReportServer$SQL2008R226511983137,44
wpc2011cms26110022735,79
MDWT_2008R2_PT20106235,33
ReportServer$SQL2008R243115634434,73
AdventureWorksLT2418872534,12
TestCDC2619162732,71
AdventureWorks854381331,29
MDWT_2008R2_PT2091230,33
wpc2011cms854071329,07
sqlStart1434428414728,95
AdventureWorksDW2008R2853191322,79
AdventureWorksDW853181322,71
AdventureWorksLT2008R2852821320,14
sqlStart2214692319,54
MDWT_2008R2_PT2049216,33
Mondial741921116,00
ReportServer$SQL2008R2TempDB741691114,08
SqlClrDemo851861313,29
MDWT_2008R2_PT2032210,67
641161010,55
TDE86144149,60
MDWT_2008R2_PT202829,33
TestCDC134153178,50
AdventureWorks2008R2201826,00
MDWT_2008R2_PT201725,67
AdventureWorks2008R29309368531020,22

The activity of reading and writing are slow, there might be a problem ([avg IO stall ms] < 50)

Knowledge base:
Troubleshooting Performance Problems in SQL Server 2005


IO BOTTLENECKS    [top]

Pending Disk IO Count
0

The disk subsystem (I / O) does not appear to be under stress. Pending Disk IO Count = 0


IO SLOW STORAGE    [top]

Info
Slow Storage Reads on Drive C

Reads are averaging longer than 100ms for at least one database on this drive
Writes are averaging longer than 20ms for at least one database on this drive
Tips:
- Find tables without clustered indexes
- Find unused indexes
- Find missing indexes
- Find triggers


IO STATISTICS    [top]

DB NameFile IDNum of readsNum of writesWrites + ReadsNum of bytes readNum of bytes written% Reads% Write
AdventureWorks1391402367488819297,50002,5000
285133932162457661,538538,4615
AdventureWorks2008R2126462022848184336384166297692,90737,0927
2930933102401408248299520,290199,7099
3202655360100,00000,0000
4406841884087226657669121260748899,54000,4600
AdventureWorksDW1281291662976819296,55173,4483
285133932162457661,538538,4615
AdventureWorksDW2008R21271281597440819296,42863,5714
285133932162457661,538538,4615
AdventureWorksLT1241251400832819296,00004,0000
285132908162457661,538538,4615
AdventureWorksLT2008R21241251400832819296,00004,0000
285132908162457661,538538,4615
MDWT_2008R2_PT1221231269760819295,65224,3478
264102703362048060,000040,0000
3202655360100,00000,0000
4202655360100,00000,0000
5202655360100,00000,0000
6202655360100,00000,0000
7202655360100,00000,0000
8202655360100,00000,0000
Mondial1231241335296819295,83334,1667
274113891202048063,636436,3636
rick1221231269760819295,65224,3478
274113891202048063,636436,3636
SqlClrDemo1221231269760819295,65224,3478
285133932162457661,538538,4615
sqlStart1221231269760819295,65224,3478
214341479461762048097,27892,7211
TDE1211221204224819295,45454,5455
28614573443276857,142942,8571
TestCDC1261271531904819296,29633,7037
2134172744322048076,470623,5294
wpc2011cms1261271531904819296,29633,7037
285133932162457661,538538,4615


Note
The analysis is also useful for making a correct sizing of your disk subsystem, focuses on the activities of reading / writing any file for each database.


IO STATISTICS MOST DB READS    [top]

DB Name% Reads% Write
AdventureWorks97,50002,5000
AdventureWorks2008R292,90737,0927
100,00000,0000
99,54000,4600
AdventureWorksDW96,55173,4483
AdventureWorksDW2008R296,42863,5714
AdventureWorksLT96,00004,0000
AdventureWorksLT2008R296,00004,0000
MDWT_2008R2_PT95,65224,3478
100,00000,0000
100,00000,0000
100,00000,0000
100,00000,0000
100,00000,0000
100,00000,0000
Mondial95,83334,1667
rick95,65224,3478
SqlClrDemo95,65224,3478
sqlStart95,65224,3478
97,27892,7211
TDE95,45454,5455
TestCDC96,29633,7037
wpc2011cms96,29633,7037


LOGICAL IO PERFORMED    [top]

DB NameTotal I/O
Adhoc Queries99 %
msdb0 %


CPU BOTTLENECKS    [top]

Scheduler idCurrent TasksRunnable Tasks
0250
120
210
310
410
510
610
720

The CPU does not appear to be under stress. Runnable Tasks = 0


CPU USAGE    [top]

DB NameCPU Usage
{Adhoc Queries}100 %
msdb0 %


CPU UTILIZATION    [top]

DB NameAvg CPU (sec)Total CPU (sec)Avg Duration (sec)Total Duration (sec)Avg Logical ReadsTotal Logical ReadsExecution Count
msdb0,000,000,000,004143


CPU UTILIZATION BY DATABASE    [top]

NDatabaseCPU Time (ms)CPU Time (min)CPU Time (h)CPU %
1AdventureWorks2008R212622877210,380,0099,75
2ReportServer$SQL2008R2239390,400,000,19
3ReportServer$SQL2008R2TempDB35510,060,000,03
4TestCDC16520,030,000,01
5AdventureWorks4210,010,000,00
6AdventureWorksDW2008R22390,000,000,00
7rick2110,000,000,00
8wpc2011cms2090,000,000,00
9Mondial1860,000,000,00
10SqlClrDemo1800,000,000,00
11AdventureWorksDW1800,000,000,00
12AdventureWorksLT2008R21800,000,000,00
13AdventureWorksLT1790,000,000,00
14sqlStart1770,000,000,00
15MDWT_2008R2_PT1680,000,000,00
16TDE1560,000,000,00


TOP 15 SLOW QUERIES    [top]

No information to report


TOP 15 MOST EXPENSIVE STORED PROCEDURES    [top]

No information to report


MAINTENANCE PLANS    [top]

No information to report

Warning: there are NO maintenance activities for the following databases:
[AdventureWorks], [AdventureWorks2008R2], [AdventureWorksDW], [AdventureWorksDW2008R2], [AdventureWorksLT], [AdventureWorksLT2008R2], [MDWT_2008R2_PT], [Mondial], [rick], [SqlClrDemo], [sqlStart], [TDE], [TestCDC], [wpc2011cms]


NUMBER OF ERRORS    [top]

No information to report

No Errors in Event Log for the instance


SYSADMIN LIST    [top]

Sysadmin NameCreateTypeDB DefaultLang Default
Customer14/12/2011 23:28:31 SQL_LOGINmasterus_english
Executive14/12/2011 23:28:17 SQL_LOGINmasterus_english


SECURITY DISABLE GUEST ACCOUNT FROM EACH DB USER    [top]

No information to report

No significant points of attention on the user GUEST on user databases


SECURITY SAMPLE DATABASES    [top]

name
AdventureWorks2008R2
AdventureWorksDW2008R2
AdventureWorksLT2008R2
AdventureWorks
AdventureWorksDW
AdventureWorksLT

Warning: It is recommended to remove sample databases from a production system


SECURITY SEARCH GRANT PERMISSIONS TO THE PUBLIC DATABASE ROLE    [top]

DatabasePermissionObject
TestCDCSELECTfn_cdc_get_all_changes_ ...
SELECTfn_cdc_get_net_changes_ ...
SELECTlsn_time_mapping

Warning: It is recommended to avoid granting unnecessary permissions to the PUBLIC role


SECURITY VERIFY EXISTS BUILTIN ADMINISTRATORS    [top]

No information to report

No significant issues on the Administrators group


TABLES IN MASTER DATABASE    [top]

User Tables
[tbltest1] table created by end users on: Nov 19 2011 10:22AM
[tbltest2] table created by end users on: Nov 19 2011 10:22AM
[tbltest3] table created by end users on: Nov 19 2011 10:22AM

User Tables in the MASTER database may not be restored in the event of a disaster


TABLES IN MSDB DATABASE    [top]

User Tables
[tbltest1] table was created by end users on: Nov 19 2011 10:22AM
[tbltest2] table was created by end users on: Nov 19 2011 10:22AM
[tbltest3] table was created by end users on: Nov 19 2011 10:22AM

User tables in the MSDB database may not be restored in the event of a disaster


TABLES IN TEMPDB DATABASE    [top]

No information to report


STORED PROCEDURE AT STARTUP    [top]

No information to report

no stored procedures executed on startup of the instance.


JOBS LIST    [top]

Job NameOwnerActive Start TimeActive Start DateActive End DateEnabled
syspolicy_purge_historysa2:0020080101999912311


JOBS WITH ERROR    [top]

No information to report