PtiTom

SQL Performance Baseline for SQL Server 2012

Oct 16th, 2015
217
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. -- http://www.sqlservercentral.com/scripts/baselines/96797/
  2. USE [master]
  3. GO
  4. if exists(select 1 from sys.sysobjects where name=N'InstanceAnalysis_PerformanceBaseLine' and type=N'P')
  5. begin
  6. Drop procedure [dbo].[InstanceAnalysis_PerformanceBaseLine]
  7. end
  8.  
  9. /****** Object:  StoredProcedure [dbo].[InstanceAnalysis_PerformanceBaseLine]    
  10. Script Date: 1/17/2013 10:28:04 PM
  11. Created By Nirav Joshi
  12. Copy Right By Nirav Joshi
  13. Subject:This script will collect the performancebase line data from the diffrent DMV and performacen counter of the SQL Server.
  14. Please let me know your feedback about the script any suggestion comment are most welcome
  15. Please drop me line at nirav.j05@gmail.com
  16.  
  17. ******/
  18. SET ANSI_NULLS ON
  19. GO
  20.  
  21. SET QUOTED_IDENTIFIER ON
  22. GO
  23.  
  24.  
  25. CREATE procedure [dbo].[InstanceAnalysis_PerformanceBaseLine]
  26. as
  27.  
  28. declare @ts_now bigint,
  29.         @start_time varchar(20),
  30.         @Server_Name varchar(100),
  31.         @Server_ver varchar(500),
  32.         @SQLSer_OSInfo varchar(500),
  33.         @SQL_inst_date varchar(100),
  34.         @MachineName varchar(100),
  35.         @ServerName varchar(100),
  36.         @SrvName_prop varchar(100),
  37.         @Srv_Machine varchar(100),
  38.         @InstName varchar(100),
  39.         @IsCluster varchar(10),
  40.         @CompNetbios varchar(200),
  41.         @SqlEdition varchar(100),
  42.         @SqlProductLevel varchar(10),
  43.         @SqlProdVer varchar(10),
  44.         @SqlProid varchar(10),
  45.         @Sql_Ins_collation varchar(100),
  46.         @IsfullText varchar(10),
  47.         @IsInterSec varchar(10),
  48.         @LogicalCPUCount varchar(10),
  49.         @HTRatio varchar(10),
  50.         @PhyCPUCount varchar(10),
  51.         @PhyRAM_MB varchar(10),
  52.         @Proc_Value varchar(200),
  53.         @Proc_date Varchar(500),
  54.         @Sp_config_Name varchar(500),
  55.         @Sp_config_value varchar(10),
  56.         @Sp_config_inusevalue varchar(10),
  57.         @Sp_config_des varchar(1000),
  58.         @db_det_name varchar(400),
  59.         @db_det_fileid varchar(200),
  60.         @db_det_filename varchar(200),
  61.         @db_det_phyfilename varchar(4000),
  62.         @db_det_filedesc varchar(100),
  63.         @db_det_statedesc varchar(200),
  64.         @db_det_filesizeMB varchar(20),
  65.         @db_log_info_dbname varchar(500),
  66.         @db_log_info_rmodle varchar(500),
  67.         @db_log_info_logreusewait varchar(500),
  68.         @db_log_info_logsizekb varchar(200),
  69.         @db_log_info_logusedkb varchar(200),
  70.         @db_log_info_logusedper varchar(200),
  71.         @db_log_info_dbcmptlevel varchar(200),
  72.         @db_log_info_pageverify varchar(200),
  73.         @db_log_info_autstats varchar(10),
  74.         @db_log_info_autoupdstats varchar(10),
  75.         @db_log_info_autstatsasyncon varchar(10),
  76.         @db_log_info_parameterrizatio varchar(10),
  77.         @db_log_info_snapshotisolation varchar(50),
  78.         @db_log_info_readcommitedsnapshot varchar(50),
  79.         @db_log_info_autoclose varchar(10),
  80.         @db_log_info_autoshrink varchar(10),
  81.         @IO_DBName varchar(100),
  82.         @IO_PhyName varchar(5000),
  83.         @io_stall_read_ms real,
  84.         @io_num_of_reads bigint,
  85.         @io_avg_read_stall_ms real,
  86.         @io_stall_write_ms real,
  87.         @io_num_of_writes bigint,
  88.         @io_avg_write_stall_ms real,
  89.         @io_stalls bigint,
  90.         @io_total bigint,
  91.         @avg_io_stall_ms real,
  92.         @row_cnt int,
  93.         @Db_name varchar(500),
  94.         @Db_cpu_time_ms bigint,
  95.         @db_cpu_per real,
  96.         @dbcache_Dbname varchar(500),
  97.         @dbcache_dbcachesizeMB real,
  98.         @waitType_WaitTypeName varchar(500),
  99.         @WaitType_waittime_s real,
  100.         @WaitType_resource_s real,
  101.         @WaitType_Signal_s real,
  102.         @WaitType_counts bigint,
  103.         @WaitType_WaitingPct real,
  104.         @WaitType_RunningPct real,
  105.         @cpuwait_signal_cpu_waits real,
  106.         @cpuwait_resource_wait real,
  107.         @logindet_LoginName varchar(500),
  108.         @logindet_session_count bigint,
  109.         @avg_task_count varchar(200),
  110.         @avg_runnable_task_count varchar(200),
  111.         @avg_diskpendingio_count varchar(200),
  112.         @sqlproc_cpu_Sql_proc int,
  113.         @sqlproc_cpu_sysidle int,
  114.         @sqlproc_cpu_otheros_proc int,
  115.         @sqlproc_cpu_event_time datetime,
  116.         @sqlmem_svr_name varchar(200),
  117.         @sqlmem_obj_name varchar(200),
  118.         @sqlmem_ins_name int,
  119.         @sqlmem_Page_life_expe int,
  120.         @sqlmem_svrm_name varchar(200),
  121.         @sqlmem_sql_obj_name varchar(200),
  122.         @sqlmem_sql_mem_grant_pend int,
  123.         @sqlmemclerk_obj_name varchar(500),
  124.         @sqlmemclerk_mem_kb bigint,
  125.         @adhocQue_QueryText varchar(4000),
  126.         @adhocQue_Qplan_size_byte bigint,
  127.         @tokempermcachesizekb varchar(200),
  128.         @clocktokenname varchar(200),
  129.         @clocktyoe varchar(200),
  130.         @clockhand varchar(200),
  131.         @clock_status varchar(200),
  132.         @clockroundcounts varchar(200),
  133.         @clockremovedallroundcount varchar(200),
  134.         @clockremovedlastroundcount varchar(200),
  135.         @clockupdatedlastroundcount varchar(200),
  136.         @clocklastroundstarttime varchar(200),
  137.         @flagname varchar(20),
  138.         @flagstatus varchar(20),
  139.         @flagglobal varchar(20),
  140.         @flagsesion varchar(20),
  141.         @topspbycpu_spname varchar(4000),
  142.         @topspbycpu_totalworkertimeinmicros varchar(200),
  143.         @topspbycpu_Avgworkertimeinmicros varchar(200),
  144.         @topspbycpu_Executioncount varchar(100),
  145.         @topspbycpu_callsecond varchar(200),
  146.         @topspbycpu_averageelapsedtimeinmicros varchar(200),
  147.         @topspbycpu_maxlogicalread varchar(200),
  148.         @topspbycpu_maxlogicalwrites varchar(200),
  149.         @topspbycpu_ageincache varchar(200),
  150.         @sqlschedule_parenenodeid varchar(10),
  151.         @sqlschedule_schdulerid varchar(10),
  152.         @sqlschedule_cpuid varchar(10),
  153.         @sqlschedule_status varchar(30),
  154.         @sqlschedule_isonline varchar(10),
  155.         @sqlschedule_isidle varchar(10),
  156.         @sqlschedule_preemptiveswtichescounts varchar(50),
  157.         @sqlschedule_contextswtichescounts varchar(50),
  158.         @sqlschedule_idleswtichescounts varchar(50),
  159.         @sqlschedule_currenttaskcounts varchar(50),
  160.         @sqlschedule_runnabletaskcounts varchar(50),
  161.         @sqlschedule_currentworkercounts varchar(50),
  162.         @sqlschedule_activeworkercounts varchar(50),
  163.         @sqlschedule_pendingiocounts varchar(20),
  164.         @sqlschedule_failedtocreate varchar(20),
  165.         -- Listing 10 Locating physical read I/O pressure
  166.         -- Get Top 20 executed SP's ordered by physical reads (read I/O pressure)
  167.         @topsp_iopressure_spname varchar(1000),
  168.         @topsp_iopressure_physicalread varchar(40),
  169.         @topsp_iopressure_spname_avgphysicalread varchar(40),
  170.         @topsp_iopressure_spname_Executioncount varchar(40),
  171.         @topsp_iopressure_spname_callsecond varchar(40),
  172.         @topsp_iopressure_spname_Avgworkertime varchar(40),
  173.         @topsp_iopressure_spname_Totalworkertime varchar(40),
  174.         @topsp_iopressure_spname_Avgelapsedtime varchar(40),
  175.         @topsp_iopressure_spname_maxlogicalreads varchar(40),
  176.         @topsp_iopressure_spname_maxlogicalwrite varchar(40),
  177.         @topsp_iopressure_spname_ageincache varchar(40),
  178.  
  179.         -- Listing 14 Finding indexes and tables that use the most buffer space
  180.         -- Breaks down buffers by object (table, index) in the buffer cache
  181.         @object_spaceinmem_objname varchar(1000),
  182.         @object_spaceinmem_objid varchar(10),
  183.         @object_spaceinmem_indexid varchar(10),
  184.         @object_spaceinmem_buffersizeinmb varchar(10),
  185.         @object_spaceinmem_Buffcount varchar(100),
  186.         -- Listing 16 Finding your 25 most expensive queries for memory
  187.         -- Get Top 25 executed SP's ordered by logical reads (memory pressure)
  188.         @topsp_mempressure_spname varchar(1000),
  189.         @topsp_mempressure_totallogicalread varchar(30),
  190.         @topsp_mempressure_executioncount varchar(30),
  191.         @topsp_mempressure_Avglogicalreads varchar(30),
  192.         @topsp_mempressure_callspersecond varchar(30),
  193.         @topsp_mempressure_avgworkertime varchar(30),
  194.         @topsp_mempressure_totalworkertime varchar(30),
  195.         @topsp_mempressure_Avgelapsedtime varchar(30),
  196.         @topsp_mempressure_totallogicalwrite varchar(30),
  197.         @topsp_mempressure_maxlogicalread varchar(30),
  198.         @topsp_mempressure_maxlogicalwrite varchar(30),
  199.         @topsp_mempressure_totalphysicalread varchar(30),
  200.         @topsp_mempressure_ageincache varchar(30),
  201.        
  202.         -- Missing Indexes by Index Advantage
  203.         @msngidx_idxadv varchar(400),
  204.         @msngidx_lastuser_seek varchar(140),
  205.         @msngidx_dbschematable varchar(1000),
  206.         @msngidx_equalitycols varchar(1000),
  207.         @msngidx_inequalitycols varchar(1000),
  208.         @msngidx_includedcols varchar(1000),
  209.         @msngidx_uniquecompiles varchar(100),
  210.         @msngidx_userseeks varchar(100),
  211.         @msngidx_avgtotalusercost varchar(100),
  212.         @msngidx_avguserimpact varchar(100),
  213.         --Missing Indexes by Script
  214.         @msgindx_idxgroup_handle varchar(200),
  215.         @msgindx_idx_handle varchar(200),
  216.         @msgindx_improvement_measures varchar(200),
  217.         @msgindx_createidxstat varchar(5000),
  218.         @msgindx_grphandle varchar(200),
  219.         @msgindx_uniqcompiles varchar(200),
  220.         @msgindx_userseeks varchar(200),
  221.         @msgindx_usescans varchar(200),
  222.         @msgindx_lastuserseek varchar(200),
  223.         @msgindx_lastuserscan varchar(200),
  224.         @msgindx_avgtotalusercost varchar(200),
  225.         @msgindx_avguserimpact varchar(200),
  226.         @msgindx_systemseek varchar(200),
  227.         @msgindx_systemscan varchar(200),
  228.         @msgindx_lastsysseek varchar(200),
  229.         @msgindx_avgtotalsyscost varchar(200),
  230.         @msgindx_avgsysimpact varchar(200),
  231.         @msgindx_databaseid varchar(200),
  232.         @msgindx_objid varchar(200),
  233.  
  234.         --MSDB Suspect pages
  235.         @mscorrupt_dbid varchar(10),
  236.         @mscorrupt_fileid varchar(20),
  237.         @mscorrupt_pageid varchar(500),
  238.         @mscorrupt_eventtype varchar(2000),
  239.         @mscorrupt_errorcount varchar(5000),
  240.         @mscorrupt_lastupdate varchar(2000),
  241.  
  242.         -- Listing 26 Detecting blocking (a more accurate and complete version)
  243.         @blocking_lcktype varchar(200),
  244.         @blocking_dbname varchar(500),
  245.         @blocking_blockerobj varchar(500),
  246.         @blocking_lckreque varchar(200),
  247.         @blocking_waitersid varchar(10),
  248.         @blocking_waitime varchar(10),
  249.         @blocking_waitbatch varchar(20),
  250.         @blocking_waiterstmt varchar(1000),
  251.         @blocking_blockersid varchar(200),
  252.         @blocking_blocker_stmt varchar(1000),
  253.  
  254.         -- Listing 27 Looking at locks that are causing problems
  255.         @lockquery_restype varchar(100),
  256.         @lockquery_resdbid varchar(10),
  257.         @lockquery_resentryid varchar(100),
  258.         @lockquery_reqmode varchar(100),
  259.         @lockquery_reqsessid varchar(10),
  260.         @lockquery_blocksid varchar(10),
  261.  
  262.         -- Database Growth Query
  263.         @endDate datetime,
  264.         @months smallint,
  265.         @DBG_Dbname varchar(200),
  266.         @DBG_YearMon varchar(50),
  267.         @DBG_MinSizeMB varchar(200),
  268.         @DBG_MaxSizeMB varchar(200),
  269.         @DBG_AVGSizeMB varchar(200),
  270.         @DBG_GrowthMB varchar(200),
  271.  
  272.         --- Memory Configuration
  273. @pg_size int,
  274. @Instancename varchar(50),
  275. --Physical Memory Details on Server along with VAS.
  276. @phymem_onsrvinmb varchar(200),
  277. @phymem_onsrvingb varchar(200),
  278. @phymem_onsrvVAS varchar(200),
  279. --Buffer Pool Usage at the Moment
  280. @bpoolusg_commitedinmb varchar(20),
  281. @bpoolusg_commitedintargetmb varchar(20),
  282. @bpoolusg_visibleinMB varchar(20),
  283. --Total Memory used by SQL Server instance from Perf Mon
  284. @totalmemsql_usageinkb varchar(20),
  285. @totalmemsql_usageinMB varchar(20),
  286. @totalmemsql_usageinGB varchar(20),
  287. --Memory needed as per current Workload for SQL Server instance
  288. @memneed_curwl_meminkb varchar(20),
  289. @memneed_curwl_meminmb varchar(20),
  290. @memneed_curwl_meminGB varchar(20),
  291. --Total amount of dynamic memory the server is using for maintaining connections
  292. @memcon_usageinkb varchar(50),
  293. @memcon_usageinmb varchar(50),
  294. @memcon_usageingb varchar(50),
  295. --'Total amount of dynamic memory the server is using for locks
  296. @memlock_useinkb varchar(50),
  297. @memlock_useinMb varchar(50),
  298. @memlock_useinGb varchar(50),
  299. --Total amount of dynamic memory the server is using for the dynamic SQL cache
  300. @dynsqlcache_useinkb varchar(50),
  301. @dynsqlcache_useinMb varchar(50),
  302. @dynsqlcache_useinGb varchar(50),
  303. --Total amount of dynamic memory the server is using for query optimization
  304. @qryopt_useinkb varchar(50),
  305. @qryopt_useinMb varchar(50),
  306. @qryopt_useinGb varchar(50),
  307. --Total amount of dynamic memory used for hash, sort and create index operations.
  308. @idexsort_userinkb varchar(50),
  309. @idexsort_userinMb varchar(50),
  310. @idexsort_userinGb varchar(50),
  311. --Total Amount of memory consumed by cursors.
  312. @curmem_useinkb varchar(50),
  313. @curmem_useinMb varchar(50),
  314. @curmem_useinGb varchar(50),
  315. --Number of pages in the buffer pool (includes database, free, and stolen)
  316. @bpool_page_8kbno varchar(50),
  317. @bpool_pages_inkb varchar(50),
  318. @bpool_pages_inmb varchar(50),
  319.  
  320. --Number of Data pages in the buffer pool
  321. @dbpagebpool_page_8kbno varchar(50),
  322. @dbpagebpool_page_inkb varchar(50),
  323. @dbpagebpool_page_inmb varchar(50),
  324.  
  325. --Number of Free pages in the buffer pool
  326. @freepagebpool_page_8kbno varchar(50),
  327. @freepagebpool_page_inkb varchar(50),
  328. @freepagebpool_page_inmb varchar(50),
  329.  
  330. --Number of Reserved pages in the buffer pool
  331. @respagebpool_page_8kbno varchar(50),
  332. @respagebpool_page_inkb varchar(50),
  333. @respagebpool_page_inmb varchar(50),
  334.  
  335. --Number of Stolen pages in the buffer pool
  336. @stolenpbpool_page_8kbno varchar(50),
  337. @stolenpbpool_page_inkb varchar(50),
  338. @stolenpbpool_page_inmb varchar(50),
  339.  
  340. --Number of Plan Cache pages in the buffer pool
  341. @plancachebpool_page_8kbno varchar(50),
  342. @plancachebpool_page_inkb varchar(50),
  343. @plancachebpool_page_inmb varchar(50),
  344. --SQL Server Binary Module Information
  345. @DllFilePath varchar(2000),
  346. @FileVer varchar(500),
  347. @Productver varchar(200),
  348. @Bin_Descrip varchar(5000),
  349. @Modulesize_inkb varchar(200),
  350.  
  351. -- Version Stored Application
  352. @verstorepage_used varchar(20),
  353. @verstorepage_spaceinMB Varchar(20),
  354.  
  355. --Script to total tempdb usage by type across all files
  356. @tempdb_user_obj_pages_inMB varchar(20),
  357. @tempdb_internal_obj_pages_inMB varchar(20),
  358. @tempdb_versionstore_obj_pages_inMB varchar(20),
  359. @tempdb_total_pages_use_inMB varchar(20),
  360. @tempdb_total_pages_free_inMB varchar(20),
  361.  
  362. --Script to find the top five sessions running tasks that use tempdb
  363. @tempdbsession_sid varchar(20),
  364. @tempdbsession_requ_sid varchar(20),
  365. @tempdbsession_execontext_sid varchar(20),
  366. @tempdbsession_dbid varchar(20),
  367. @tempdbsession_usrobjallocpage_count varchar(20),
  368. @tempdbsession_usrobjdeallocpage_count varchar(20),
  369. @tempdbsession_internalallocpage_count varchar(20),
  370. @tempdbsession_internaldeallocpage_count varchar(20),
  371. --Script to find the top five sessions running tasks that use tempdb
  372. @sessionact_sid varchar(10),
  373. @sessionact_logintime varchar(100),
  374. @sessionact_hostname varchar(100),
  375. @sessionact_programname varchar(520),
  376. @sessionact_cputime varchar(10),
  377. @sessionact_memusginkb varchar(10),
  378. @sessionact_totalschetime varchar(10),
  379. @sessionact_totalelsapsedtime varchar(10),
  380. @sessionact_lastrequestendtime varchar(50),
  381. @sessionact_reads varchar(10),
  382. @sessionact_write varchar(10),
  383. @sessionact_conncount varchar(10),
  384. --script for IO Result for file in min
  385. @fileio_dbname varchar(200),
  386. @fileio_filename varchar(4000),
  387. @fileio_filetype varchar(200),
  388. @fileio_filesizegb varchar(200),
  389. @fileio_mbread varchar(200),
  390. @fileio_mbwrite varchar(200),
  391. @fileio_noofread varchar(200),
  392. @fileio_noofwrite varchar(200),
  393. @fileio_miniowritestall varchar(200),
  394. @fileio_minioreadstall varchar(200),
  395. --script to look for open transaction actual activity
  396. @otran_spid varchar(10),
  397. @otran_lasworkertime varchar(200),
  398. @otran_lastphysicalread varchar(200),
  399. @otran_totalphysicalread varchar(200),
  400. @otran_totallogicalwrites varchar(200),
  401. @otran_lastlogicalreads varchar(200),
  402. @otran_currentwait varchar(200),
  403. @otran_lastwaittype varchar(1000),
  404. @otran_watiresource varchar(1000),
  405. @otran_waittime varchar(100),
  406. @otran_opentrancount varchar(100),
  407. @otran_rowcount varchar(10),
  408. @otran_granterqmem varchar(20),
  409. @otran_sqltect varchar(4000)
  410.  
  411.  
  412.  
  413.         print'<HTML><head><Title>SQL Server Instance Detail Report.</Title>'+
  414.             '<style type="text/css">'+
  415.                 'table {
  416.                 border-collapse:collapse;
  417.                 background:#EFF4FB url(http://www.roscripts.com/images/teaser.gif) repeat-x;
  418.                 border-left:1px solid #686868;
  419.                 border-right:1px solid #686868;
  420.                 font:0.8em/145% Trebuchet MS,helvetica,arial,verdana;
  421.                 color: #333;
  422.                 }'+
  423.  
  424. 'td, th {
  425.         padding:5px;
  426. }'+
  427.  
  428. 'caption {
  429.         padding: 0 0 .5em 0;
  430.         text-align: left;
  431.         font-size: 1.4em;
  432.         font-weight: bold;
  433.         text-transform: uppercase;
  434.         color: #333;
  435.         background: transparent;
  436. }'+
  437.  
  438. 'table a {
  439.         color:#950000;
  440.         text-decoration:none;
  441. }'+
  442.  
  443. 'table a:link {}'+
  444.  
  445. 'table a:visited {
  446.         font-weight:normal;
  447.         color:#666;
  448.         text-decoration: line-through;
  449. }'+
  450.  
  451. 'table a:hover {
  452.         border-bottom: 1px dashed #bbb;
  453. }'+
  454.  
  455.  
  456. 'thead th, tfoot th, tfoot td {
  457.         background:#333 url(http://www.roscripts.com/images/llsh.gif) repeat-x;
  458.         color:#fff
  459. }'+
  460.  
  461. 'tfoot td {
  462.         text-align:right
  463. }'+
  464.  
  465. 'tbody th, tbody td {
  466.         border-bottom: dotted 1px #333;
  467. }'+
  468.  
  469. 'tbody th {
  470.         white-space: nowrap;
  471. }'+
  472.  
  473. 'tbody th a {
  474.         color:#333;
  475. }'+
  476.  
  477. '.odd {}'+
  478.  
  479. 'tbody tr:hover {
  480.         background:#fafafa
  481. }'+
  482.  
  483.  
  484. '</style></head>'
  485.  
  486. /*
  487. SQL Server Startup Time    
  488.            
  489. */
  490.  
  491.  
  492. print N'<h1>SQL Server Up Time</h1>'
  493. print N'<table cellspacing="1" cellpadding="1" border="1">'+
  494.  
  495. N'<tr><th><strong>Time</strong></th>'+'</tr>'
  496.  
  497. declare cur_uptime_sql cursor for
  498. select CONVERT(VARCHAR(20), create_date, 100)
  499.   from sys.databases where database_id=2
  500. open cur_uptime_sql
  501. fetch from cur_uptime_sql into
  502. @start_time
  503. while @@fetch_status>=0
  504. begin
  505. print '<tr><td>'+@start_time+'</td>'+'</tr>'
  506. fetch from cur_uptime_sql into
  507. @start_time
  508. end
  509. close cur_uptime_sql
  510. deallocate cur_uptime_sql
  511. print'</table><br/>'
  512. /*
  513. Instance Detail Information fetching Query
  514. */
  515.  
  516. print N'<h1>SQL Server Instance Detail</h1>'
  517. print N'<H3>SQL Server Name and Version Detail</H3>'
  518. print N'<table cellspacing="1" cellpadding="1" border="1">'+
  519.  
  520. N'<tr><th><strong>Server Name</strong></th>'+
  521. N'<th><strong>Instance Version</strong></th></tr>'
  522.  
  523.  
  524.  
  525. declare cur_sql_info  cursor for SELECT @@SERVERNAME AS [Server Name], @@VERSION AS [SQL Server and OS Version Info]
  526. open cur_sql_info
  527. fetch next from cur_sql_info into @Server_Name,@Server_ver
  528. while @@fetch_status>=0
  529. begin
  530. print '<tr><td>'+@Server_Name+'</td><td>'+@Server_ver+'</td>'+'</tr>'
  531. fetch next from cur_sql_info into @Server_Name,@Server_ver
  532. end
  533. close cur_sql_info
  534. deallocate cur_sql_info
  535. print'</table><br/>'
  536.  
  537. print '<table style="width: 100%">
  538.     <tr>
  539.         <td><span class="auto-style1"><strong>RECOMMENDATION:</strong></span><br>
  540.         SQL Server 2005 fell out of Mainsteam Support on April 12, 2011 -- This
  541.         means no more Service Packs or Cumulative Updates.<br>-- The SQL Server
  542.         2005 builds that were released after SQL Server 2005 Service Pack 2 was
  543.         released<br>
  544.         <a href="http://support.microsoft.com/kb/937137" target="_blank">
  545.         http://support.microsoft.com/kb/937137</a><br>-- The SQL Server 2005
  546.         builds that were released after SQL Server 2005 Service Pack 3 was
  547.         released<br>
  548.         <a href="http://support.microsoft.com/kb/960598" target="_blank">
  549.         http://support.microsoft.com/kb/960598</a><br>-- The SQL Server 2005
  550.         builds that were released after SQL Server 2005 Service Pack 4 was
  551.         released <br>
  552.         <a href="http://support.microsoft.com/kb/2485757" target="_blank">
  553.         http://support.microsoft.com/kb/2485757</a></td>
  554.     </tr>
  555. </table>
  556. '
  557.  
  558. /*
  559. When was SQL Server last Installed date
  560. */
  561.  
  562. print N'<H3>SQL Server Name and Installation Detail</H3>'
  563. print N'<table cellspacing="1" cellpadding="1" border="1">'+
  564.  
  565. N'<tr><th><strong>Server Name</strong></th>'+
  566. N'<th><strong>SQL Installation Date</strong></th></tr>'
  567.  
  568.  
  569. declare cur_sql_sqlinstall cursor for SELECT @@SERVERNAME AS [Server Name], createdate AS [SQL Server Install Date]
  570. FROM sys.syslogins WITH (NOLOCK)
  571. WHERE [sid] = 0x010100000000000512000000;
  572. open cur_sql_sqlinstall
  573. fetch next from cur_sql_sqlinstall into @SQLSer_OSInfo,@SQL_inst_date
  574. while @@fetch_status>=0
  575. begin
  576. print '<tr><td>'+@SQLSer_OSInfo+'</td><td>'+@SQL_inst_date+'</td>'+'</tr>'
  577. fetch next from cur_sql_sqlinstall into @SQLSer_OSInfo,@SQL_inst_date
  578. end
  579. close cur_sql_sqlinstall
  580. deallocate cur_sql_sqlinstall
  581. print'</table><br/>'
  582.  
  583. /*
  584. Get selected server properties (SQL Server 2005)
  585. -- This gives you a lot of useful information about your instance of SQL Server
  586.  
  587. */
  588.  
  589. print N'<H3>SQL Server Server properties</H3>'
  590. print N'<table cellspacing="1" cellpadding="1" border="1">'+
  591.  
  592.  
  593. N'<tr><th><strong>Machine Name</strong></th>'+
  594. N'<th><strong>Server Name</strong></th>'+
  595. N'<th><strong>Instance Name</strong></th>'+
  596. N'<th><strong>Is Clustered</strong></th>'+
  597. N'<th><strong>Computer Netbios Name</strong></th>'+
  598. N'<th><strong>SQL Edition</strong></th>'+
  599. N'<th><strong>SQL Product Patch Level</strong></th>'+
  600. N'<th><strong>SQL Product Product Version</strong></th>'+
  601. N'<th><strong>SQL Process ID</strong></th>'+
  602. N'<th><strong>SQL Instance Collation</strong></th>'+
  603. N'<th><strong>SQL FullText Installed</strong></th>'+
  604. N'<th><strong>SQL IsIntegratedSecurityOnly</strong></th></tr>'
  605.  
  606. declare cur_sql_sqlpropties cursor for
  607. SELECT
  608. cast(SERVERPROPERTY('MachineName') as varchar(200)) AS [MachineName],
  609. cast(SERVERPROPERTY('ServerName') as varchar(200)) AS [ServerName],  
  610. cast(SERVERPROPERTY('InstanceName') as varchar(200)) AS [Instance],
  611. cast(SERVERPROPERTY('IsClustered') as varchar(200)) AS [IsClustered],
  612. CAST(SERVERPROPERTY('ComputerNamePhysicalNetBIOS') as varchar(200)) AS [ComputerNamePhysicalNetBIOS],
  613. cast(SERVERPROPERTY('Edition') as varchar(200)) AS [Edition],
  614. cast(SERVERPROPERTY('ProductLevel') as varchar(200)) AS [ProductLevel],
  615. cast(SERVERPROPERTY('ProductVersion') as varchar(200)) AS [ProductVersion],
  616. cast(SERVERPROPERTY('ProcessID') as varchar(200)) AS [ProcessID],
  617. cast(SERVERPROPERTY('Collation') as varchar(200)) AS [Collation],
  618. cast(SERVERPROPERTY('IsFullTextInstalled') as varchar(200)) AS [IsFullTextInstalled],
  619. cast(SERVERPROPERTY('IsIntegratedSecurityOnly') as varchar(200)) AS [IsIntegratedSecurityOnly]
  620.  
  621. open cur_sql_sqlpropties
  622. fetch next from cur_sql_sqlpropties into
  623. @Srv_Machine,
  624. @SrvName_prop,
  625. @InstName,
  626. @IsCluster,
  627. @CompNetbios,
  628. @SqlEdition,
  629. @SqlProductLevel,
  630. @SqlProdVer,
  631. @SqlProid,
  632. @Sql_Ins_collation,
  633. @IsfullText,
  634. @IsInterSec
  635. while @@fetch_status>=0
  636. begin
  637.  
  638. if(@InstName IS NULL)
  639. begin
  640. set @InstName = 'Default'
  641. end
  642. print '<tr><td>'+@Srv_Machine+'</td><td>'+@SrvName_prop+'</td><td>'+@InstName+'</td><td>'+@IsCluster+'</td><td>'+@CompNetbios+'</td><td>'+@SqlEdition+'</td><td>'+@SqlProductLevel+'</td><td>'+@SqlProdVer+'</td><td>'+@SqlProid+'</td><td>'+@Sql_Ins_collation+'</td><td>'+@IsfullText+'</td><td>'+@IsInterSec+'</td>'+'</tr>'
  643. --print 'I am in the cursor'
  644. fetch next from cur_sql_sqlpropties into
  645. @Srv_Machine,
  646. @SrvName_prop,
  647. @InstName,
  648. @IsCluster,
  649. @CompNetbios,
  650. @SqlEdition,
  651. @SqlProductLevel,
  652. @SqlProdVer,
  653. @SqlProid,
  654. @Sql_Ins_collation,
  655. @IsfullText,
  656. @IsInterSec
  657. end
  658. close cur_sql_sqlpropties
  659. deallocate cur_sql_sqlpropties
  660. print'</table><br/>'
  661. print '<table style="width: 100%">
  662.     <tr>
  663.         <td>--In the configuration detail where 0 is disable and 1 is enable.</td>
  664.     </tr>
  665. </table>
  666. <br/>'
  667. /*
  668.  
  669. CPU Hardware Information for SQL Server 2005
  670.  
  671.  */
  672. print N'<H3>SQL Server Server CPU Information</H3>'
  673. print N'<table cellspacing="1" cellpadding="1" border="1">'+
  674. N'<tr><th><strong>Logical CPU Count</strong></th>'+
  675. N'<th><strong>Hyperthreading Ratio</strong></th>'+
  676. N'<th><strong>Physical CPU Count</strong></th>'+
  677. N'<th><strong>Physical RAM</strong></th></tr>'
  678.  
  679. declare sql_cpu_prop cursor for
  680. SELECT cast(cpu_count as varchar(10)) AS [Logical CPU Count], cast(hyperthread_ratio as varchar(10)) AS [Hyperthread Ratio],
  681. cast(cpu_count/hyperthread_ratio as varchar(10)) AS [Physical CPU Count],
  682. cast(physical_memory_kb/1024 as varchar(10)) AS [Physical Memory (MB)]
  683. FROM sys.dm_os_sys_info WITH (NOLOCK) OPTION (RECOMPILE)
  684.  
  685.  
  686. open sql_cpu_prop
  687.  
  688. fetch from sql_cpu_prop into
  689. @LogicalCPUCount,
  690. @HTRatio,
  691. @PhyCPUCount,
  692. @PhyRAM_MB
  693. while @@fetch_status>=0
  694. begin
  695. print '<tr><td>'+@LogicalCPUCount+'</td><td>'+@HTRatio+'</td><td>'+@PhyCPUCount+'</td><td>'+@PhyRAM_MB+'</td>'+'</tr>'
  696. fetch from sql_cpu_prop into
  697. @LogicalCPUCount,
  698. @HTRatio,
  699. @PhyCPUCount,
  700. @PhyRAM_MB
  701. end
  702. close sql_cpu_prop
  703. deallocate sql_cpu_prop
  704. print'</table><br/>'
  705. print '<br>
  706. <table style="width: 100%">
  707.     <tr>
  708.         <td>-- In this above Table we have mention table Server CPU
  709.         configuration along with  total physical RAM available on the
  710.         server.<br>-- It is good to to check Hyperthreading Ratio for CPU some
  711.         time CPU pressure can be contribute by it.<br>-- This does not
  712.         distinguish between multicore and hyperthreading.</td>
  713.     </tr>
  714. </table>'
  715.  
  716. /*
  717. Server Model and Manufacturer and processor model
  718. */
  719. set nocount on
  720. print N'<H3>Server Processor Information</H3>'
  721. print N'<table cellspacing="1" cellpadding="1" border="1">'+
  722. N'<tr><th><strong>Processor Value</strong></th>'+
  723. N'<th><strong>Processor Name</strong></th></tr>'
  724. --declare @ProcName Table
  725. --( Value varchar(200),
  726. --  Name varchar(400)
  727. -- )
  728. set nocount on
  729. IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'#ProcName') AND type in (N'U'))
  730. DROP TABLE #ProcName
  731. create table #ProcName( Value varchar(200),Name varchar(400))
  732.  insert into #ProcName exec xp_instance_regread
  733. 'HKEY_LOCAL_MACHINE', 'HARDWARE\DESCRIPTION\System\CentralProcessor\0',
  734. 'ProcessorNameString';
  735.  
  736. --select * from @ProcName
  737.  
  738. declare cur_proc_name cursor for select value,Name from #ProcName
  739.  
  740. open cur_proc_name
  741.  
  742. fetch from cur_proc_name into
  743. @Proc_Value,
  744. @Proc_date
  745.  
  746. while @@fetch_status>=0
  747. begin
  748. print '<tr><td>'+@Proc_Value+'</td><td>'+@Proc_date+'</td>'+'</tr>'
  749.  
  750. fetch from cur_proc_name into
  751. @Proc_Value,
  752. @Proc_date
  753. end
  754.  
  755. close cur_proc_name
  756. deallocate cur_proc_name
  757. set nocount off
  758. print'</table><br/>'
  759. print '<br>
  760. <table style="width: 100%">
  761.     <tr>
  762.         <td>--Above Table will give you information about the CPU make and moel
  763.         and clock speed information.</td>
  764.     </tr>
  765. </table>'
  766.  
  767.  
  768.  
  769. /*
  770. SQL Server configuration setting Information.
  771. */
  772.  
  773. print N'<H3>SQL Server SP_CONFIGURE Information For Instance</H3>'
  774. print N'<table cellspacing="1" cellpadding="1" border="1">'+
  775. N'<tr><th><strong>Parameter Name</strong></th>'+
  776. '<th><strong>Parameter Value</strong></th>'+
  777. '<th><strong>Parameter Running Vlaue</strong></th>'+
  778. N'<th><strong>Parameter Description</strong></th></tr>'
  779.  
  780.  
  781. declare cur_sql_spconfig cursor  for SELECT name, cast(value as varchar(10)) as value,CAST(value_in_use as varchar(10)) as valueinuse, [description]
  782. FROM sys.configurations WITH (NOLOCK)
  783. ORDER BY name  OPTION (RECOMPILE);
  784.  
  785. open cur_sql_spconfig
  786.  
  787. fetch from cur_sql_spconfig into
  788. @Sp_config_Name,
  789. @Sp_config_value,
  790. @Sp_config_inusevalue,
  791. @Sp_config_des
  792.  
  793.  
  794. while @@fetch_status>=0
  795. begin
  796. print '<tr><td>'+@Sp_config_Name+'</td><td>'+@Sp_config_value+'</td><td>'+@Sp_config_inusevalue+'<td>'+@Sp_config_des+'</td>'+'</tr>'
  797.  
  798. fetch from cur_sql_spconfig into
  799. @Sp_config_Name,
  800. @Sp_config_value,
  801. @Sp_config_inusevalue,
  802. @Sp_config_des
  803. end
  804.  
  805. close cur_sql_spconfig
  806. deallocate cur_sql_spconfig
  807. print'</table><br/>'
  808.  
  809. print'<table style="width: 100%">
  810.     <tr>
  811.         <td>--Above table will show you SQL Server Instance Level configuration
  812.         settings. Whic is very important to know and set it to proper according
  813.         value in the first will save you from lot of performance related issues
  814.         in the future.<br><strong><span class="auto-style1">-- Focus on the
  815.         following parameter.</span><br class="auto-style1">1.Max Degree of
  816.         Parallelism:-<br>--</strong>Set this option based on the your instance
  817.         database configuration whether you have OLTP databases or DSS(Reporting)
  818.         databases.For OLTP databases we dont need much processing power since
  819.         ammount of transaction would very small.<br>--While in DSS or Reporting
  820.         system we definetly need more CPU since many of queries doing select
  821.         with conditional logic and that would be always fast if it would get
  822.         benifited from parallel processing.<br>--Set this value  to 0
  823.         indicate SQL can use all available CPU on the server for processing
  824.         while setting to 1 indicate SQL can only use single CPU for processing.<br>
  825.         --You can set this value based on the number of processsor you have and
  826.         type of your workload(OLTP,DSS).<br><strong>2.Max Server Memory:-<br>--</strong>This
  827.         option is also very important for setting working set size for the SQL
  828.         Server instance and also used to limit memory utilization on the server
  829.         by instance.<br>-- This option has to be set for your instnace in order
  830.         to avoid memory throtlling and memory bottleneck problem on the system.
  831.         This option set memory dynamic so no need to restart SQL Server in order
  832.         to take in to effect.<br>-- Hypothetical example of memory distribution
  833.         System with having 32 GB RAM with 64 bit OS Single Production SQL Server
  834.         instnace running on it then we can divide memory for OS to 6 GB rest 26
  835.         GB to SQL and if you have any other application on the same box other
  836.         than SQL then you have to further reduce SQL Server Max Server Memory.<br>
  837.         -- For Better tunning of Max Server Memory use Performance Monitor to
  838.         examine the SQLServer:Buffer Manager performance object while under a
  839.         load, and note the current values of the Stolen pages and Reserved pages
  840.         counters. These counters report memory as the number of 8K pages. max
  841.         server memory should be set above the sum of these two values to avoid
  842.         out-of-memory errors.<br><strong>3. CLR Enabled:-</strong><br>--This
  843.         should be set to 0 if you don''t use any .Net related commond language
  844.         run time.If you need it then enable it.<br><strong>4.lightweight
  845.         pooling:-<br>--</strong>Setting lightweight pooling to 1 causes SQL
  846.         Server to switch to fiber mode scheduling. The default value for this
  847.         option is 0.<br>--Use the lightweight pooling option to provide a means
  848.         of reducing the system overhead associated with the excessive context
  849.         switching sometimes seen in symmetric multiprocessing (SMP)
  850.         environments. When excessive context switching is present, lightweight
  851.         pooling can provide better throughput by performing the context
  852.         switching inline, thus helping to reduce user/kernel ring transitions.<br>
  853.         <em>--We do not recommend that you use fiber mode scheduling for routine
  854.         operation. This is because it can decrease performance by inhibiting the
  855.         regular benefits of context switching, and because some components of
  856.         SQL Server that use Thread Local Storage (TLS) or thread-owned objects,
  857.         such as mutexes (a type of Win32 kernel object), cannot function
  858.         correctly in fiber mode.<br></em>5.Priority Boost:-<br>--By setting this
  859.         option to 1 allows SQL Server to run on Windows Server with highest
  860.         priority on Windows Scheduler. <br>-- If this option is enable then SQL
  861.         Server will run on Windows Scheduler with priority base of 13 and in
  862.         normal mode it will be running with priority base of 7<br>--We have seen
  863.         failover issues in the past on Failover Cluster system when you ran SQL
  864.         Server with High Priority boost.<br>-- So try to avoid configuring SQL
  865.         Server for this option.<br><strong>5.optimize for ad hoc workloads:-</strong><br>
  866.         --The optimize for ad hoc workloads option is used to improve the
  867.         efficiency of the plan cache for workloads that contain many single use
  868.         ad hoc batches.<br>--When this option is set to 1, the Database Engine
  869.         stores a small compiled plan stub in the plan cache when a batch is
  870.         compiled for the first time, instead of the full compiled plan. <br>
  871.         --This helps to relieve memory pressure by not allowing the plan cache
  872.         to become filled with compiled plans that are not reused.<br>--The
  873.         compiled plan stub allows the Database Engine to recognize that this ad
  874.         hoc batch has been compiled before but has only stored a compiled plan
  875.         stub, so when this batch is invoked (compiled or executed) again, the
  876.         Database Engine compiles the batch, removes the compiled plan stub from
  877.         the plan cache, and adds the full compiled plan to the plan cache.<br>
  878.         --Setting the optimize for ad hoc workloads to 1 affects only new plans;
  879.         plans that are already in the plan cache are unaffected.</td>
  880.     </tr>
  881. </table>
  882. <br/>'
  883.  
  884. /*
  885. Database Data FIles Detail
  886. */
  887.  
  888.  
  889.  
  890. print N'<H3>SQL Server Databases Datafiles location size and status</H3>'
  891. print N'<table cellspacing="1" cellpadding="1" border="1">'+
  892. N'<tr><th><strong>Database Name</strong></th>'+
  893. '<th><strong>DB File Id</strong></th>'+
  894. '<th><strong>DB File Name</strong></th>'+
  895. '<th><strong>DB Physical File Name</strong></th>'+
  896. '<th><strong>DB file Type</strong></th>'+
  897. '<th><strong>DB File Status</strong></th>'+
  898. N'<th><strong>DB File Size in (MB)</strong></th></tr>'
  899.  
  900.  
  901. declare cur_db_datafiles cursor for
  902. SELECT cast(DB_NAME([database_id]) as varchar(400))AS [Database Name],
  903.        cast([file_id] as varchar(10)) as File_id,
  904.        name,
  905.        physical_name,
  906.        type_desc,
  907.        state_desc,
  908.        cast(CONVERT( bigint, size/128.0) as varchar(200)) AS [Total Size in MB]
  909. FROM sys.master_files WITH (NOLOCK)
  910. WHERE [database_id] > 4
  911. AND [database_id] <> 32767
  912. OR [database_id] = 2
  913. ORDER BY DB_NAME([database_id]) OPTION (RECOMPILE);
  914.  
  915. open cur_db_datafiles
  916.  
  917. fetch from cur_db_datafiles into
  918. @db_det_name,
  919. @db_det_fileid,
  920. @db_det_filename,
  921. @db_det_phyfilename,
  922. @db_det_filedesc,
  923. @db_det_statedesc,
  924. @db_det_filesizeMB
  925.  
  926. while @@fetch_status>=0
  927. begin
  928. print '<tr><td>'+@db_det_name+'</td><td>'+@db_det_fileid+'</td><td>'+@db_det_filename+'</td><td>'+@db_det_phyfilename+'</td><td>'+@db_det_filedesc+'</td><td>'+@db_det_statedesc+'</td><td>'+@db_det_filesizeMB+'</td>'+'</tr>'
  929. fetch from cur_db_datafiles into
  930. @db_det_name,
  931. @db_det_fileid,
  932. @db_det_filename,
  933. @db_det_phyfilename,
  934. @db_det_filedesc,
  935. @db_det_statedesc,
  936. @db_det_filesizeMB
  937. end
  938.  
  939. close cur_db_datafiles
  940. deallocate cur_db_datafiles
  941.  
  942. print'</table><br/>'
  943.  
  944. print'<table style="width: 100%">
  945.     <tr>
  946.         <td><span class="auto-style1"><strong>SQL Server Databases Datafiles
  947.         location size and status</strong></span><br>--The above table provides
  948.         you inforation about your databases Files and their respective location
  949.         with status of the file and along with FileSize.<br>--Things to look at
  950.         also Files for all Databases are on the same drive.<br>-- Files like
  951.         data file and log file are on diffrent drive.<br>-- How many files we
  952.         have for tempdb and are they at same size.<br>-- Is tempdb is on
  953.         dedicated drive.<br>-- Idle condition log file should be put on the very
  954.         fast drive so we will not have IO latency bottelneck while performing
  955.         transactions.</td>
  956.     </tr>
  957. </table>
  958. <br/>'
  959.  
  960.  
  961. /*
  962. Database Congiuration Properties QUery.
  963. */
  964.  
  965. print '<H3>SQL Server Databases Configuration Properties</H3>'
  966. print '<table cellspacing="1" cellpadding="1" border="1">'+
  967. '<tr><th><strong>Database Name</strong></th>'+
  968. '<th><strong>DB Recovery Model</strong></th>'+
  969. '<th><strong>DB Log Reuse Wait Description</strong></th>'+
  970. '<th><strong>DB Log File Size(KB)</strong></th>'+
  971. '<th><strong>DB Log File Used Size(KB)</strong></th>'+
  972. '<th><strong>DB Log File Used(%)</strong></th>'+
  973. '<th><strong>DB Compatibility Level</strong></th>'+
  974. '<th><strong>DB Page Verify Option</strong></th>'+
  975. '<th><strong>DB is_auto_create_stats_on</strong></th>'+
  976. '<th><strong>DB is_auto_update_stats_on</strong></th>'+
  977. '<th><strong>DB is_auto_update_stats_async_on</strong></th>'+
  978. '<th><strong>DB Force Parameterization</strong></th>'+
  979. '<th><strong>DB Snapshot Isolation State</strong></th>'+
  980. '<th><strong>DB Read Commited Snapshot On</strong></th>'+
  981. '<th><strong>DB AutoClose On</strong></th>'+
  982. '<th><strong>DB AutoShrink On</strong></th></tr>'
  983.  
  984.  
  985. declare cur_db_log_info cursor for
  986. SELECT db.[name] AS [Database Name], db.recovery_model_desc AS [Recovery Model],
  987. db.log_reuse_wait_desc AS [Log Reuse Wait Description],
  988. ls.cntr_value AS [Log Size (KB)], lu.cntr_value AS [Log Used (KB)],
  989. CAST(CAST(lu.cntr_value AS FLOAT) / CAST(ls.cntr_value AS FLOAT)AS DECIMAL(18,2)) * 100 AS [Log Used %],
  990. db.[compatibility_level] AS [DB Compatibility Level],
  991. db.page_verify_option_desc AS [Page Verify Option], db.is_auto_create_stats_on, db.is_auto_update_stats_on,
  992. db.is_auto_update_stats_async_on, db.is_parameterization_forced,
  993. db.snapshot_isolation_state_desc, db.is_read_committed_snapshot_on,
  994. db.is_auto_close_on, db.is_auto_shrink_on
  995. FROM sys.databases AS db WITH (NOLOCK)
  996. INNER JOIN sys.dm_os_performance_counters AS lu WITH (NOLOCK)
  997. ON db.name = lu.instance_name
  998. INNER JOIN sys.dm_os_performance_counters AS ls WITH (NOLOCK)
  999. ON db.name = ls.instance_name
  1000. WHERE lu.counter_name LIKE N'Log File(s) Used Size (KB)%'
  1001. AND ls.counter_name LIKE N'Log File(s) Size (KB)%'
  1002. AND ls.cntr_value > 0 OPTION (RECOMPILE);
  1003.  
  1004. open cur_db_log_info
  1005. fetch from cur_db_log_info into
  1006.         @db_log_info_dbname,
  1007.         @db_log_info_rmodle,
  1008.         @db_log_info_logreusewait,
  1009.         @db_log_info_logsizekb,
  1010.         @db_log_info_logusedkb,
  1011.         @db_log_info_logusedper,
  1012.         @db_log_info_dbcmptlevel,
  1013.         @db_log_info_pageverify,
  1014.         @db_log_info_autstats,
  1015.         @db_log_info_autoupdstats,
  1016.         @db_log_info_autstatsasyncon,
  1017.         @db_log_info_parameterrizatio,
  1018.         @db_log_info_snapshotisolation,
  1019.         @db_log_info_readcommitedsnapshot,
  1020.         @db_log_info_autoclose,
  1021.         @db_log_info_autoshrink    
  1022.  
  1023. while @@fetch_status>=0
  1024. begin
  1025. print '<tr><td>'+cast(@db_log_info_dbname as varchar(500))+'</td><td>'+cast(@db_log_info_rmodle as varchar(500))+'</td><td>'+cast(@db_log_info_logreusewait as varchar(500))+'</td><td>'+cast(@db_log_info_logsizekb as varchar(500))+'</td><td>'+cast(@db_log_info_logusedkb as varchar(500))+'</td><td>'+cast(@db_log_info_logusedper as varchar(500))+'</td><td>'+cast(@db_log_info_dbcmptlevel as varchar(500))+'</td><td>'+cast(@db_log_info_pageverify as varchar(500))+'</td><td>'+cast(@db_log_info_autstats as varchar(500))+'</td><td>'+cast(@db_log_info_autoupdstats as varchar(500))+'</td><td>'+cast(@db_log_info_autstatsasyncon as varchar(500))+'</td><td>'+cast(@db_log_info_parameterrizatio as varchar(500))+'</td><td>'+cast(@db_log_info_snapshotisolation as varchar(500))+'</td><td>'+cast(@db_log_info_readcommitedsnapshot as varchar(500))+'</td><td>'+cast(@db_log_info_autoclose as varchar(500))+'</td><td>'+cast(@db_log_info_autoshrink as varchar(500))+'</td>'+'</tr>'
  1026. fetch from cur_db_log_info into
  1027.         @db_log_info_dbname,
  1028.         @db_log_info_rmodle,
  1029.         @db_log_info_logreusewait,
  1030.         @db_log_info_logsizekb,
  1031.         @db_log_info_logusedkb,
  1032.         @db_log_info_logusedper,
  1033.         @db_log_info_dbcmptlevel,
  1034.         @db_log_info_pageverify,
  1035.         @db_log_info_autstats,
  1036.         @db_log_info_autoupdstats,
  1037.         @db_log_info_autstatsasyncon,
  1038.         @db_log_info_parameterrizatio,
  1039.         @db_log_info_snapshotisolation,
  1040.         @db_log_info_readcommitedsnapshot,
  1041.         @db_log_info_autoclose,
  1042.         @db_log_info_autoshrink    
  1043. end
  1044. close cur_db_log_info
  1045. deallocate cur_db_log_info
  1046.  
  1047. print'</table><br/>'
  1048. print'<table style="width: 100%">
  1049.     <tr>
  1050.         <td><span class="auto-style1"><strong>SQL Server Databases Configuration
  1051.         Properties:-</strong></span><br>--In the above table will show you each
  1052.         database properties configuration information like.<br>1.Recovery Model<br>
  1053.         2.Transaction Log Reuse Wait Description.<br>3. DB log file size in KB<br>
  1054.         4. DB log file used size in KB<br>5. DB log file percentage usage.<br>--
  1055.         Another parameter is also very important is DB Compatibility level this
  1056.         parameter shows values like (80,90,100,110). Where if you have restored
  1057.         any of SQL Server database from older version to new version you will
  1058.         have to change this option.<br>--Consequnces of this option not set
  1059.         could be SQL databases which having SQL 2000(80) compatibility version
  1060.         can use old query optimization techniques on advance version of SQL
  1061.         Server which intern may degraded performance of the SQL Server.<br>
  1062.         --Database Parameterization option is set to simple SQL Server query
  1063.         optimizer may choose to parameterize the queries. This means that any
  1064.         literal values that are contained in a query are substituted with
  1065.         parameters.<br>--When SIMPLE parameterization is in effect, you cannot
  1066.         control which queries are parameterized and which queries are not.
  1067.         However, you can specify that all queries in a database be parameterized
  1068.         by setting the PARAMETERIZATION database option to FORCED. This process
  1069.         is referred to as forced parameterization.<br>--you can specify that
  1070.         forced parameterization is attempted on a certain class of queries. You
  1071.         do this by creating a TEMPLATE plan guide on the parameterized form of
  1072.         the query, and specifying the PARAMETERIZATION FORCED query hint in the
  1073.         sp_create_plan_guide stored procedure. You can consider this kind of
  1074.         plan guide as a way to enable forced parameterization only on a certain
  1075.         class of queries, instead of all queries.<br>--When the PARAMETERIZATION
  1076.         database option is set to FORCED, you can specify that for a certain
  1077.         class of queries, only simple parameterization is attempted, not forced
  1078.         parameterization. You do this by creating a TEMPLATE plan guide on the
  1079.         force-parameterized form of the query, and specifying the
  1080.         PARAMETERIZATION SIMPLE query hint in <b>sp_create_plan_guide</b>.</td>
  1081.     </tr>
  1082. </table>
  1083. <br><br/>'
  1084.  
  1085.  
  1086.  
  1087. /*
  1088. SQL Server datafile read write stats in the min
  1089. */
  1090.  
  1091.  
  1092. /*
  1093. SQL Server Databfiles Read/Write Stall and Average Read/Write Information
  1094. */
  1095. print N'<H3>SQL Server Databases Datafiles Writes/Reads</H3>'
  1096. print N'<table cellspacing="1" cellpadding="1" border="1">'+
  1097. N'<tr><th><strong>Database Name</strong></th>'+
  1098. '<th><strong>Physical File Name</strong></th>'+
  1099. '<th><strong>File Types</strong></th>'+
  1100. '<th><strong>File Size in MB</strong></th>'+
  1101. '<th><strong>Total Reads in MB</strong></th>'+
  1102. '<th><strong>Total Writes in MB</strong></th>'+
  1103. '<th><strong>Number of Reads</strong></th>'+
  1104. '<th><strong>Number of Writes</strong></th>'+
  1105. '<th><strong>IO Stall Write in Minute</strong></th>'+
  1106. N'<th><strong>IO Stall Read in Minute</strong></th></tr>'
  1107. declare cur_iom_info cursor for
  1108. SELECT sys.master_files.name as DatabaseName,
  1109. sys.master_files.physical_name,
  1110. CASE WHEN sys.master_files.type_desc = 'ROWS' THEN 'Data Files'
  1111. WHEN sys.master_files.type_desc = 'LOG' THEN 'Log Files'
  1112. END as 'File Type',
  1113. ((FileStats.size_on_disk_bytes/1024)/1024)/ 1024.0 as FileSize_GB,
  1114. (FileStats.num_of_bytes_read /1024)/1024.0 as MB_Read,
  1115. (FileStats.num_of_bytes_written /1024)/1024.0 as MB_Written,
  1116. FileStats.Num_of_reads, FileStats.Num_of_writes,
  1117. ((FileStats.io_stall_write_ms /1000.0)/60) as
  1118. Minutes_of_IO_Write_Stalls,
  1119. ((FileStats.io_stall_read_ms /1000.0)/60) as
  1120. Minutes_of_IO_Read_Stalls
  1121. FROM sys.dm_io_virtual_file_stats(null,null) as FileStats
  1122. JOIN sys.master_files ON
  1123. FileStats.database_id = sys.master_files.database_id
  1124. AND FileStats.file_id = sys.master_files.file_id
  1125.  
  1126. open cur_iom_info
  1127. fetch from cur_iom_info into
  1128. @fileio_dbname,
  1129. @fileio_filename,
  1130. @fileio_filetype,
  1131. @fileio_filesizegb,
  1132. @fileio_mbread ,
  1133. @fileio_mbwrite,
  1134. @fileio_noofread,
  1135. @fileio_noofwrite,
  1136. @fileio_miniowritestall ,
  1137. @fileio_minioreadstall
  1138.  
  1139. while @@fetch_status>=0
  1140. begin
  1141.  
  1142. print '<tr><td>'+cast(@fileio_dbname as varchar(500))+
  1143.       '</td><td>'+cast(@fileio_filename as varchar(5000))+
  1144.       '</td><td>'+cast(@fileio_filetype as varchar(500))+
  1145.       '</td><td>'+cast(@fileio_filesizegb as varchar(500))+
  1146.       '</td><td>'+cast(@fileio_mbread as varchar(150))+
  1147.       '</td><td>'+cast(@fileio_mbwrite as varchar(150))+
  1148.       '</td><td>'+cast(@fileio_noofread as varchar(150))+
  1149.       '</td><td>'+cast(@fileio_noofwrite as varchar(150))+
  1150.       '</td><td>'+cast(@fileio_miniowritestall as varchar(150))+
  1151.       '</td><td>'+cast(@fileio_minioreadstall as varchar(150))+'</td>'+'</tr>'
  1152. fetch from cur_iom_info into
  1153. @fileio_dbname,
  1154. @fileio_filename,
  1155. @fileio_filetype,
  1156. @fileio_filesizegb,
  1157. @fileio_mbread ,
  1158. @fileio_mbwrite,
  1159. @fileio_noofread,
  1160. @fileio_noofwrite,
  1161. @fileio_miniowritestall ,
  1162. @fileio_minioreadstall
  1163. end
  1164.  
  1165. close cur_iom_info
  1166. deallocate cur_iom_info
  1167. print'</table><br/>'
  1168.  
  1169. print N'<table cellspacing="1" cellpadding="1" border="1">'+
  1170. N'<tr><th><strong>Database Name</strong></th>'+
  1171. '<th><strong>Physical File Name</strong></th>'+
  1172. '<th><strong>IO stall READ in MS</strong></th>'+
  1173. '<th><strong>IO Num of READ</strong></th>'+
  1174. '<th><strong>IO Avg READ Stall in MS </strong></th>'+
  1175. '<th><strong>IO stall WRITE in MS</strong></th>'+
  1176. '<th><strong>IO Num of WRITE</strong></th>'+
  1177. '<th><strong>IO Avg WRITE Stall in MS</strong></th>'+
  1178. '<th><strong>IO Stalls in MS(Io stall read_MS+Io stall write_MS)</strong></th>'+
  1179. '<th><strong>Total IO(Total Read+Total Write)</strong></th>'+
  1180. N'<th><strong>IO Avg IO Stall</strong></th></tr>'
  1181.  
  1182.  
  1183. declare cur_db_io_readwrite cursor for SELECT DB_NAME(fs.database_id) AS [Database Name], mf.physical_name, io_stall_read_ms, num_of_reads,
  1184. CAST(io_stall_read_ms/(1.0 + num_of_reads) AS NUMERIC(10,1)) AS [avg_read_stall_ms],io_stall_write_ms,
  1185. num_of_writes,CAST(io_stall_write_ms/(1.0+num_of_writes) AS NUMERIC(10,1)) AS [avg_write_stall_ms],
  1186. io_stall_read_ms + io_stall_write_ms AS [io_stalls], num_of_reads + num_of_writes AS [total_io],
  1187. CAST((io_stall_read_ms + io_stall_write_ms)/(1.0 + num_of_reads + num_of_writes) AS NUMERIC(10,1))
  1188. AS [avg_io_stall_ms]
  1189. FROM sys.dm_io_virtual_file_stats(null,null) AS fs
  1190. INNER JOIN sys.master_files AS mf WITH (NOLOCK)
  1191. ON fs.database_id = mf.database_id
  1192. AND fs.[file_id] = mf.[file_id]
  1193. ORDER BY avg_io_stall_ms DESC OPTION (RECOMPILE);
  1194.  
  1195. open cur_db_io_readwrite
  1196. fetch from cur_db_io_readwrite into
  1197. @IO_DBName,
  1198. @IO_PhyName,
  1199. @io_stall_read_ms,
  1200. @io_num_of_reads,
  1201. @io_avg_read_stall_ms,
  1202. @io_stall_write_ms,
  1203. @io_num_of_writes,
  1204. @io_avg_write_stall_ms,
  1205. @io_stalls,
  1206. @io_total,
  1207. @avg_io_stall_ms
  1208. while @@fetch_status>=0
  1209. begin
  1210.  
  1211. print '<tr><td>'+cast(@IO_DBName as varchar(500))+
  1212.       '</td><td>'+cast(@IO_PhyName as varchar(5000))+
  1213.       '</td><td>'+cast(@io_stall_read_ms as varchar(50))+
  1214.       '</td><td>'+cast(@io_num_of_reads as varchar(50))+
  1215.       '</td><td>'+cast(@io_avg_read_stall_ms as varchar(50))+
  1216.       '</td><td>'+cast(@io_stall_write_ms as varchar(50))+
  1217.       '</td><td>'+cast(@io_num_of_writes as varchar(50))+
  1218.       '</td><td>'+cast(@io_avg_write_stall_ms as varchar(50))+
  1219.       '</td><td>'+cast(@io_stalls as varchar(50))+
  1220.       '</td><td>'+cast(@io_total as varchar(50))+
  1221.       '</td><td>'+cast(@avg_io_stall_ms as varchar(50))+'</td>'+'</tr>'
  1222.  
  1223. fetch from cur_db_io_readwrite into
  1224. @IO_DBName,
  1225. @IO_PhyName,
  1226. @io_stall_read_ms,
  1227. @io_num_of_reads,
  1228. @io_avg_read_stall_ms,
  1229. @io_stall_write_ms,
  1230. @io_num_of_writes,
  1231. @io_avg_write_stall_ms,
  1232. @io_stalls,
  1233. @io_total,
  1234. @avg_io_stall_ms
  1235.  
  1236. end
  1237.  
  1238. close cur_db_io_readwrite
  1239. deallocate cur_db_io_readwrite
  1240. print'</table><br/>'
  1241. print'<table style="width: 100%">
  1242.     <tr>
  1243.         <td><span class="auto-style1"><strong>SQL Server Databases Datafiles
  1244.         Writes/Reads:-</strong></span><br>-- This above table will give you
  1245.         detail about the Database DataFiles read/write operation information
  1246.         along with Read Stall and Write Stall.<br>--  Helps you determine
  1247.         which database files on the entire instance have the most I/O
  1248.         bottlenecks.<br>-- This can help you decide whether certain LUNs are
  1249.         overloaded and whether you might.<br>-- With help of this you can plan
  1250.         to move some of very busy files to some another less busy locations.</td>
  1251.     </tr>
  1252. </table>'
  1253.  
  1254. /*
  1255. SQL Server database wise CPU Utilization Query
  1256. */
  1257.  
  1258. print N'<H3>SQL Server Databases Wise CPU Utilization</H3>'
  1259. print N'<table cellspacing="1" cellpadding="1" border="1">'+
  1260. N'<tr><th><strong>Row Count</strong></th>'+
  1261. '<th><strong>Database Name</strong></th>'+
  1262. '<th><strong>CPU Time in MS</strong></th>'+
  1263. N'<th><strong>CPU Usage in(%)</strong></th></tr>'
  1264.  
  1265.  
  1266. declare cur_db_cpuusage cursor for
  1267. WITH DB_CPU_Stats
  1268. AS
  1269. (SELECT DatabaseID, DB_Name(DatabaseID) AS [DatabaseName], SUM(total_worker_time) AS [CPU_Time_Ms]
  1270.  FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)
  1271.  CROSS APPLY (SELECT CONVERT(int, value) AS [DatabaseID]
  1272.               FROM sys.dm_exec_plan_attributes(qs.plan_handle)
  1273.               WHERE attribute = N'dbid') AS F_DB
  1274.  GROUP BY DatabaseID)
  1275. SELECT ROW_NUMBER() OVER(ORDER BY [CPU_Time_Ms] DESC) AS [row_num],
  1276.        DatabaseName, [CPU_Time_Ms],
  1277.        CAST([CPU_Time_Ms] * 1.0 / SUM([CPU_Time_Ms]) OVER() * 100.0 AS DECIMAL(5, 2)) AS [CPUPercent]
  1278. FROM DB_CPU_Stats
  1279. WHERE DatabaseID > 4 -- system databases
  1280. AND DatabaseID <> 32767 -- ResourceDB
  1281. ORDER BY row_num OPTION (RECOMPILE);
  1282.  
  1283. open cur_db_cpuusage
  1284.  
  1285. fetch cur_db_cpuusage into
  1286. @row_cnt,
  1287. @Db_name,
  1288. @Db_cpu_time_ms,
  1289. @db_cpu_per
  1290.  
  1291. while @@fetch_status>=0
  1292. begin
  1293. print '<tr><td>'+cast(@row_cnt as varchar(50))+'</td><td>'+cast(@Db_name as varchar(500))+'</td><td>'+cast(@Db_cpu_time_ms as varchar(500))+'</td><td>'+cast(@db_cpu_per as varchar(500))+'</td>'+'</tr>'
  1294. fetch cur_db_cpuusage into
  1295. @row_cnt,
  1296. @Db_name,
  1297. @Db_cpu_time_ms,
  1298. @db_cpu_per
  1299.  
  1300. end
  1301. close cur_db_cpuusage
  1302. deallocate cur_db_cpuusage
  1303. print'</table><br/>'
  1304.  
  1305. print '<table style="width: 100%">
  1306.     <tr>
  1307.         <td><span class="auto-style1"><strong>SQL Server Databases Wise CPU
  1308.         Utilization:-</strong></span><br>-- This above table helps you to
  1309.         determine which database is using most of CPU.<br>-- With the help of
  1310.         above table we can tune the database to reduce consumption of CPU(
  1311.         Statistics Update,Weekly Indxe Rebuild)<br>-- If fesible tune most
  1312.         expensive query by CPU utilization.</td>
  1313.     </tr>
  1314. </table>'
  1315.  
  1316. /*
  1317. SQL Server databases Cache Size Information in the bpool Query.
  1318. */
  1319.  
  1320. print N'<H3>SQL Server Databases Cache Size Information in Buffer Pool</H3>'
  1321. print N'<table cellspacing="1" cellpadding="1" border="1">'+
  1322. N'<tr><th><strong>Database Name</strong></th>'+
  1323. N'<th><strong>Cache Size in (MB)</strong></th></tr>'
  1324.  
  1325. declare cur_db_cacheinfo cursor for
  1326. SELECT DB_NAME(database_id) AS [Database Name],
  1327. COUNT(*) * 8/1024.0 AS [Cached Size (MB)]
  1328. FROM sys.dm_os_buffer_descriptors WITH (NOLOCK)
  1329. WHERE database_id > 4 -- system databases
  1330. AND database_id <> 32767 -- ResourceDB
  1331. GROUP BY DB_NAME(database_id)
  1332. ORDER BY [Cached Size (MB)] DESC OPTION (RECOMPILE);
  1333.  
  1334. open cur_db_cacheinfo
  1335.  fetch from cur_db_cacheinfo into
  1336.  @dbcache_Dbname,
  1337.  @dbcache_dbcachesizeMB
  1338.  while @@FETCH_STATUS>=0
  1339.  begin
  1340. print '<tr><td>'+cast(@dbcache_Dbname as varchar(500))+'</td><td>'+cast(@dbcache_dbcachesizeMB as varchar(500))+'</td>'+'</tr>'
  1341. fetch from cur_db_cacheinfo into
  1342.  @dbcache_Dbname,
  1343.  @dbcache_dbcachesizeMB
  1344.  end
  1345.  
  1346.  close cur_db_cacheinfo
  1347.  deallocate cur_db_cacheinfo
  1348.  
  1349. print'</table><br/>'
  1350. print '<table style="width: 100%">
  1351.     <tr>
  1352.         <td><strong><span class="auto-style1">SQL Server Databases Cache Size
  1353.         Information in Buffer Pool:-</span><br class="auto-style1"></strong>
  1354.         --This above table tells you total buffer usage by the databases.<br>
  1355.         --It also tells you how much memory in the buffer pool is being used by
  1356.         each database on the instance.</td>
  1357.     </tr>
  1358. </table>'
  1359.  
  1360.  
  1361. /*
  1362. SQL Server Instance Over all Wait Type information Query
  1363. */
  1364.  
  1365. print N'<H3>SQL Server Instance Wait Type Information</H3>'
  1366. print N'<table cellspacing="1" cellpadding="1" border="1">'+
  1367. N'<tr><th><strong>WAIT Type Names</strong></th>'+
  1368. '<th><strong>WAIT Time in (S)</strong></th>'+
  1369. '<th><strong>Resource Time in (S)</strong></th>'+
  1370. '<th><strong>Signal Time (S)</strong></th>'+
  1371. '<th><strong>Wait Counts</strong></th>'+
  1372. '<th><strong>WAIT Perc(%)</strong></th>'+
  1373. N'<th><strong>Running in (%)</strong></th></tr>'
  1374.  
  1375. declare cur_inst_waitinfo cursor for
  1376. WITH Waits AS
  1377. (SELECT
  1378. wait_type,
  1379. wait_time_ms / 1000 AS waits,
  1380. (wait_time_ms-signal_wait_time_ms)/1000 as Resoruce_Wait_Time_S,
  1381. signal_wait_time_ms /1000.0 as signals_wait_time_s,
  1382. waiting_tasks_count as WaitCount,
  1383. 100. * wait_time_ms / SUM(wait_time_ms) OVER() AS Percentage,
  1384. ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS RowNumber
  1385. FROM sys.dm_os_wait_stats
  1386. WHERE wait_type NOT IN ('CLR_SEMAPHORE','LAZYWRITER_SLEEP','RESOURCE_QUEUE','SLEEP_TASK'
  1387. ,'SLEEP_SYSTEMTASK','SQLTRACE_BUFFER_FLUSH','WAITFOR', 'LOGMGR_QUEUE','CHECKPOINT_QUEUE'
  1388. ,'REQUEST_FOR_DEADLOCK_SEARCH','XE_TIMER_EVENT','BROKER_TO_FLUSH','BROKER_TASK_STOP','CLR_MANUAL_EVENT'
  1389. ,'CLR_AUTO_EVENT','DISPATCHER_QUEUE_SEMAPHORE', 'FT_IFTS_SCHEDULER_IDLE_WAIT'
  1390. ,'XE_DISPATCHER_WAIT', 'XE_DISPATCHER_JOIN', 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP'))
  1391. SELECT
  1392. W1.wait_type as WaitType,
  1393. CAST(W1.waits AS DECIMAL(12, 2)) AS wait_S,
  1394. CAST(W1.Resoruce_Wait_Time_S as decimal(12,2)) as Resource_S,
  1395. CAST(W1.signals_wait_time_s as decimal(12,2)) as Signal_S,
  1396. CAST(W1.WaitCount as varchar(20)) as WaitCounts,
  1397. CAST(W1.Percentage AS DECIMAL(12, 2)) AS Percentage_wait,
  1398. CAST(SUM(W2.Percentage) AS DECIMAL(12, 2)) AS running_Percentage
  1399. FROM Waits AS W1
  1400. INNER JOIN Waits AS W2
  1401. ON W2.RowNumber <= W1.RowNumber
  1402. GROUP BY
  1403. W1.RowNumber,
  1404. W1.wait_type,
  1405. W1.waits,
  1406. W1.Percentage,
  1407. W1.Resoruce_Wait_Time_S,
  1408. W1.signals_wait_time_s,
  1409. W1.WaitCount
  1410. HAVING SUM(W2.Percentage) - W1.Percentage < 99;
  1411.  
  1412. open cur_inst_waitinfo
  1413. fetch cur_inst_waitinfo into
  1414.         @waitType_WaitTypeName,
  1415.         @WaitType_waittime_s,
  1416.         @WaitType_resource_s,
  1417.         @WaitType_Signal_s,
  1418.         @WaitType_counts,
  1419.         @WaitType_WaitingPct,
  1420.         @WaitType_RunningPct
  1421.  
  1422. while @@FETCH_STATUS>=0
  1423.  begin
  1424. print '<tr><td>'+cast(@waitType_WaitTypeName as varchar(500))+
  1425.      '</td><td>'+cast(@WaitType_waittime_s as varchar(500))+
  1426.      '</td><td>'+cast(@WaitType_resource_s as varchar(500))+
  1427.      '</td><td>'+cast(@WaitType_Signal_s as varchar(500))+
  1428.      '</td><td>'+cast(@WaitType_counts as varchar(500))+
  1429.      '</td><td>'+cast(@WaitType_WaitingPct as varchar(500))+
  1430.      '</td><td>'+cast(@WaitType_RunningPct as varchar(500))+'</td>'+'</tr>'
  1431. fetch cur_inst_waitinfo into
  1432.         @waitType_WaitTypeName,
  1433.         @WaitType_waittime_s,
  1434.         @WaitType_resource_s,
  1435.         @WaitType_Signal_s,
  1436.         @WaitType_counts,
  1437.         @WaitType_WaitingPct,
  1438.         @WaitType_RunningPct
  1439. end
  1440.  
  1441. close cur_inst_waitinfo
  1442. deallocate cur_inst_waitinfo
  1443.  
  1444. print'</table><br/>'
  1445. print '<table style="width: 100%">
  1446.     <tr>
  1447.         <td><strong><span class="auto-style1">SQL Server Instance Wait Type
  1448.         Information:-</span><br class="auto-style1"></strong>-- Common
  1449.         Significant Wait types with BOL explanations<br><br>-- *** Network
  1450.         Related Waits ***<br>-- ASYNC_NETWORK_IO Occurs on network writes when
  1451.         the task is blocked behind the network<br><br>-- *** Locking Waits ***<br>
  1452.         -- LCK_M_IX Occurs when a task is waiting to acquire an Intent Exclusive
  1453.         (IX) lock<br>-- LCK_M_IU Occurs when a task is waiting to acquire an
  1454.         Intent Update (IU) lock<br>-- LCK_M_S Occurs when a task is waiting to
  1455.         acquire a Shared lock<br><br>-- *** I/O Related Waits ***<br>--
  1456.         ASYNC_IO_COMPLETION Occurs when a task is waiting for I/Os to finish<br>
  1457.         -- IO_COMPLETION Occurs while waiting for I/O operations to complete.
  1458.         <br>-- This wait type generally represents non-data page I/Os. Data page
  1459.         I/O completion waits appear <br>-- as PAGEIOLATCH_* waits<br>--
  1460.         PAGEIOLATCH_SH Occurs when a task is waiting on a latch for a buffer
  1461.         that is in an I/O request. <br>-- The latch request is in Shared mode.
  1462.         Long waits may indicate problems with the disk subsystem.<br>--
  1463.         PAGEIOLATCH_EX Occurs when a task is waiting on a latch for a buffer
  1464.         that is in an I/O request. <br>-- The latch request is in Exclusive
  1465.         mode. Long waits may indicate problems with the disk subsystem.<br>--
  1466.         WRITELOG Occurs while waiting for a log flush to complete. <br>-- Common
  1467.         operations that cause log flushes are checkpoints and transaction
  1468.         commits.<br>-- PAGELATCH_EX Occurs when a task is waiting on a latch for
  1469.         a buffer that is not in an I/O request. <br>-- The latch request is in
  1470.         Exclusive mode.<br>-- BACKUPIO Occurs when a backup task is waiting for
  1471.         data, or is waiting for a buffer in which to store data<br><br>-- ***
  1472.         CPU Related Waits ***<br>-- SOS_SCHEDULER_YIELD Occurs when a task
  1473.         voluntarily yields the scheduler for other tasks to execute. <br>--
  1474.         During this wait the task is waiting for its quantum to be renewed.<br>
  1475.         <br>-- THREADPOOL Occurs when a task is waiting for a worker to run on.
  1476.         <br>-- This can indicate that the maximum worker setting is too low, or
  1477.         that batch executions are taking <br>-- unusually long, thus reducing
  1478.         the number of workers available to satisfy other batches.<br>--
  1479.         CX_PACKET Occurs when trying to synchronize the query processor exchange
  1480.         iterator <br>-- You may consider lowering the degree of parallelism if
  1481.         contention on this wait type becomes a problem<br></td>
  1482.     </tr>
  1483. </table>
  1484. <br/>'
  1485.  
  1486.  
  1487. /*
  1488. SQL Server Signal Wait Type Query
  1489. */
  1490.  
  1491. print N'<H3>SQL Server Signal Wait in Percentage</H3>'
  1492. print N'<table cellspacing="1" cellpadding="1" border="1">'+
  1493. N'<tr><th><strong>%signal (cpu) waits</strong></th>'+
  1494. N'<th><strong>%resource waits</strong></th></tr>'
  1495.  
  1496. declare cur_sql_cpuwaitinfo cursor for
  1497. SELECT CAST(100.0 * SUM(signal_wait_time_ms) / SUM (wait_time_ms) AS NUMERIC(20,2)) AS [%signal (cpu) waits],
  1498.        CAST(100.0 * SUM(wait_time_ms - signal_wait_time_ms) / SUM (wait_time_ms) AS NUMERIC(20,2)) AS [%resource waits]
  1499. FROM sys.dm_os_wait_stats OPTION (RECOMPILE);
  1500.  
  1501. open cur_sql_cpuwaitinfo
  1502.  
  1503. fetch from cur_sql_cpuwaitinfo into
  1504. @cpuwait_signal_cpu_waits,
  1505. @cpuwait_resource_wait
  1506.  
  1507. while @@FETCH_STATUS>=0
  1508.  begin
  1509. print '<tr><td>'+cast(@cpuwait_signal_cpu_waits as varchar(500))+'</td><td>'+cast(@cpuwait_resource_wait as varchar(500))+'</td>'+'</tr>'
  1510.  
  1511. fetch from cur_sql_cpuwaitinfo into
  1512. @cpuwait_signal_cpu_waits,
  1513. @cpuwait_resource_wait
  1514.  
  1515. end
  1516. close cur_sql_cpuwaitinfo
  1517. deallocate cur_sql_cpuwaitinfo
  1518.  
  1519. print'</table><br/>'
  1520. print '<table style="width: 100%">
  1521.     <tr>
  1522.         <td><strong><span class="auto-style1">SQL Server Signal Wait in (%):-</span><br class="auto-style1">
  1523.         </strong><br>-- Signal Waits above 10-15% is usually a sign of CPU
  1524.         pressure</td>
  1525.     </tr>
  1526. </table>
  1527. <br/>'
  1528.  
  1529. /*
  1530.  
  1531. SQL Server Login Count and Session Detail.
  1532. */
  1533.  
  1534. print N'<H3>SQL Server Login and session count detail</H3>'
  1535. print N'<table cellspacing="1" cellpadding="1" border="1">'+
  1536. N'<tr><th><strong>SQL Login Name</strong></th>'+
  1537. N'<th><strong>SQL Session Counts</strong></th></tr>'
  1538.  
  1539. declare cur_session_countinfo cursor for
  1540. SELECT login_name, COUNT(session_id) AS [session_count]
  1541. FROM sys.dm_exec_sessions WITH (NOLOCK)
  1542. GROUP BY login_name
  1543. ORDER BY COUNT(session_id) DESC OPTION (RECOMPILE);
  1544.  
  1545. open cur_session_countinfo
  1546. fetch from cur_session_countinfo into
  1547. @logindet_LoginName,
  1548. @logindet_session_count
  1549. while @@FETCH_STATUS>=0
  1550.  begin
  1551. print '<tr><td>'+cast(@logindet_LoginName as varchar(500))+'</td><td>'+cast(@logindet_session_count as varchar(500))+'</td>'+'</tr>'
  1552. fetch from cur_session_countinfo into
  1553. @logindet_LoginName,
  1554. @logindet_session_count
  1555. end
  1556. close cur_session_countinfo
  1557. deallocate cur_session_countinfo
  1558. print'</table><br/>'
  1559. print '<table style="width: 100%">
  1560.     <tr>
  1561.         <td><strong><span class="auto-style1">SQL Server Login and Session
  1562.         Detail:-</span><br class="auto-style1"></strong>-- Get logins that are
  1563.         connected and how many sessions they have <br>-- This can help
  1564.         characterize your workload and determine whether you are seeing a normal
  1565.         level of activity.</td>
  1566.     </tr>
  1567. </table>'
  1568.  
  1569.  
  1570. /*
  1571.  
  1572. SQL Server Average Task COunt
  1573. */
  1574.  
  1575. print N'<H3>SQL Average Tasks count</H3>'
  1576. print N'<table cellspacing="1" cellpadding="1" border="1">'+
  1577. N'<tr><th><strong>Avg Task Count</strong></th>'+
  1578. '<th><strong>Avg Runnable Task Count</strong></th>'+
  1579. N'<th><strong>Avg Pending IO Disk Count</strong></th></tr>'
  1580.  
  1581. declare cur_avgtask_count cursor for
  1582. SELECT AVG(current_tasks_count) AS [Avg Task Count],
  1583. AVG(runnable_tasks_count) AS [Avg Runnable Task Count],
  1584. AVG(pending_disk_io_count) AS [AvgPendingDiskIOCount]
  1585. FROM sys.dm_os_schedulers WITH (NOLOCK)
  1586. WHERE scheduler_id < 255 OPTION (RECOMPILE);
  1587.  
  1588. open cur_avgtask_count
  1589. fetch from cur_avgtask_count into
  1590.         @avg_task_count,
  1591.         @avg_runnable_task_count,
  1592.         @avg_diskpendingio_count
  1593. while @@FETCH_STATUS>=0
  1594.  begin
  1595. print '<tr><td>'+cast(@avg_task_count as varchar(500))+'</td><td>'+cast(@avg_runnable_task_count as varchar(500))+'</td><td>'+cast(@avg_runnable_task_count as varchar(500))+'</td>'+'</tr>'
  1596. fetch from cur_avgtask_count into
  1597.         @avg_task_count,
  1598.         @avg_runnable_task_count,
  1599.         @avg_diskpendingio_count
  1600. end
  1601. close cur_avgtask_count
  1602. deallocate cur_avgtask_count
  1603. print'</table><br/>'
  1604. print ' <table style="width: 100%">
  1605.     <tr>
  1606.         <td>SQL Average Tasks Count:<br>-- Sustained values above 10 suggest
  1607.         further investigation in that area.<br>-- High current_tasks_count is
  1608.         often an indication of locking/blocking problems.<br>-- High
  1609.         runnable_tasks_count is an indication of CPU pressure.<br>-- High
  1610.         pending_disk_io_count is an indication of I/O pressure.</td>
  1611.     </tr>
  1612. </table>
  1613. <br>'
  1614.  
  1615.  
  1616. /*
  1617.  
  1618. SQL Server and OS Cpu utilization for last 4 hours
  1619.  
  1620. */
  1621. print N'<H3>SQL and OS CPU Utilization from SQL Ring Buffer</H3>'
  1622. print N'<table cellspacing="1" cellpadding="1" border="1">'+
  1623. N'<tr><th><strong>SQL Server Process CPU Util</strong></th>'+
  1624. '<th><strong>System IDLE Process CPU Util</strong></th>'+
  1625. '<th><strong>Other Process CPU Util</strong></th>'+
  1626. N'<th><strong>CPU Time Stamp</strong></th></tr>'
  1627.  
  1628. select  @ts_now= (SELECT cpu_ticks/(cpu_ticks/ms_ticks)FROM sys.dm_os_sys_info)
  1629. declare cur_sqlos_cpu_usage cursor for
  1630.  
  1631.  
  1632.  
  1633. SELECT TOP(256) SQLProcessUtilization AS [SQL Server Process CPU Utilization],
  1634.                SystemIdle AS [System Idle Process],
  1635.                100 - SystemIdle - SQLProcessUtilization AS [Other Process CPU Utilization],
  1636.                DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) AS [Event Time]
  1637. FROM (
  1638.       SELECT record.value('(./Record/@id)[1]', 'int') AS record_id,
  1639.             record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int')
  1640.             AS [SystemIdle],
  1641.             record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]',
  1642.             'int')
  1643.             AS [SQLProcessUtilization], [timestamp]
  1644.       FROM (
  1645.             SELECT [timestamp], CONVERT(xml, record) AS [record]
  1646.             FROM sys.dm_os_ring_buffers WITH (NOLOCK)
  1647.             WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
  1648.             AND record LIKE N'%<SystemHealth>%') AS x
  1649.       ) AS y
  1650. ORDER BY record_id DESC OPTION (RECOMPILE);
  1651.  
  1652. open cur_sqlos_cpu_usage
  1653.  
  1654. fetch  from cur_sqlos_cpu_usage into
  1655. @sqlproc_cpu_Sql_proc,
  1656. @sqlproc_cpu_sysidle,
  1657. @sqlproc_cpu_otheros_proc,
  1658. @sqlproc_cpu_event_time
  1659.  
  1660. while @@FETCH_STATUS>=0
  1661.  begin
  1662. print '<tr><td>'+cast(@sqlproc_cpu_Sql_proc as varchar(500))+'</td><td>'+cast(@sqlproc_cpu_sysidle as varchar(500))+'</td><td>'+cast(@sqlproc_cpu_otheros_proc as varchar(500))+'</td><td>'+cast(@sqlproc_cpu_event_time as varchar(500))+'</td>'+'</tr>'
  1663. fetch  from cur_sqlos_cpu_usage into
  1664. @sqlproc_cpu_Sql_proc,
  1665. @sqlproc_cpu_sysidle,
  1666. @sqlproc_cpu_otheros_proc,
  1667. @sqlproc_cpu_event_time
  1668. end
  1669. close cur_sqlos_cpu_usage
  1670. deallocate cur_sqlos_cpu_usage
  1671. print'</table><br/>'
  1672. print '<table style="width: 100%">
  1673.     <tr>
  1674.         <td><strong><span class="auto-style1">SQL and OS CPU Utilization from
  1675.         SQL Ring Buffer:-</span><br class="auto-style1"><br></strong>-- Look at
  1676.         the trend over the entire period. <br>-- Also look at high sustained
  1677.         Other Process CPU Utilization values</td>
  1678.     </tr>
  1679. </table>
  1680. <br/>'
  1681.  
  1682. /*
  1683. SQL Server memory utilization History via PLE
  1684. */
  1685. print N'<H3>SQL Memory Utilization History</H3>'
  1686. print N'<table cellspacing="1" cellpadding="1" border="1">'+
  1687. N'<tr><th><strong>Server Name</strong></th>'+
  1688. '<th><strong>Object Name</strong></th>'+
  1689. '<th><strong>Instance Name</strong></th>'+
  1690. N'<th><strong>Page Life Expectancy</strong></th></tr>'
  1691.  
  1692. declare cur_sql_mem_info cursor for
  1693. SELECT @@SERVERNAME AS [Server Name], [object_name], instance_name, cntr_value AS [Page Life Expectancy]
  1694. FROM sys.dm_os_performance_counters WITH (NOLOCK)
  1695. WHERE [object_name] LIKE N'%Buffer Node%' -- Handles named instances
  1696. AND counter_name = N'Page life expectancy' OPTION (RECOMPILE);
  1697.  
  1698. open cur_sql_mem_info
  1699. fetch from cur_sql_mem_info into
  1700. @sqlmem_svr_name,
  1701. @sqlmem_obj_name,
  1702. @sqlmem_ins_name,
  1703. @sqlmem_Page_life_expe
  1704. while @@FETCH_STATUS>=0
  1705.  begin
  1706. print '<tr><td>'+cast(@sqlmem_svr_name as varchar(500))+'</td><td>'+cast(@sqlmem_obj_name as varchar(500))+'</td><td>'+cast(@sqlmem_ins_name as varchar(500))+'</td><td>'+cast(@sqlmem_Page_life_expe as varchar(500))+'</td>'+'</tr>'
  1707. fetch from cur_sql_mem_info into
  1708. @sqlmem_svr_name,
  1709. @sqlmem_obj_name,
  1710. @sqlmem_ins_name,
  1711. @sqlmem_Page_life_expe
  1712. end
  1713. close cur_sql_mem_info
  1714. deallocate cur_sql_mem_info
  1715. print'</table><br/>'
  1716.  
  1717. print '<table style="width: 100%">
  1718.     <tr>
  1719.         <td><strong><span class="auto-style1">SQL Server Memory Utilization
  1720.         History:-</span><br class="auto-style1"></strong><br>-- Page Life
  1721.         Expectancy (PLE) value for each NUMA node in current instance<br>-- PLE
  1722.         is a good measurement of memory pressure.<br>-- Higher PLE is better.
  1723.         Watch the trend, not the absolute value.<br>-- This will only return one
  1724.         row for non-NUMA systems.</td>
  1725.     </tr>
  1726. </table>
  1727. <br/>'
  1728.  
  1729. /*
  1730.  
  1731. SQL Server memory grant pending
  1732. */
  1733.  
  1734. print N'<H3>SQL Memory Grant Pending History</H3>'
  1735. print N'<table cellspacing="1" cellpadding="1" border="1">'+
  1736. N'<tr><th><strong>Server Name</strong></th>'+
  1737. '<th><strong>Object Name</strong></th>'+
  1738. N'<th><strong>Memory Grants Pending</strong></th></tr>'
  1739.  
  1740. declare cur_sqlmem_grantinfo cursor for
  1741. SELECT @@SERVERNAME AS [Server Name], [object_name], cntr_value AS [Memory Grants Pending]                                                                                                      
  1742. FROM sys.dm_os_performance_counters WITH (NOLOCK)
  1743. WHERE [object_name] LIKE N'%Memory Manager%' -- Handles named instances
  1744. AND counter_name = N'Memory Grants Pending' OPTION (RECOMPILE);
  1745.  
  1746. open cur_sqlmem_grantinfo
  1747. fetch from cur_sqlmem_grantinfo into
  1748. @sqlmem_svrm_name,
  1749. @sqlmem_sql_obj_name,
  1750. @sqlmem_sql_mem_grant_pend
  1751. while @@FETCH_STATUS>=0
  1752.  begin
  1753. print '<tr><td>'+cast(@sqlmem_svrm_name as varchar(500))+'</td><td>'+cast(@sqlmem_sql_obj_name as varchar(500))+'</td><td>'+cast(@sqlmem_sql_mem_grant_pend as varchar(500))+'</td>'+'</tr>'
  1754.  
  1755. fetch from cur_sqlmem_grantinfo into
  1756. @sqlmem_svrm_name,
  1757. @sqlmem_sql_obj_name,
  1758. @sqlmem_sql_mem_grant_pend
  1759. end
  1760. close cur_sqlmem_grantinfo
  1761. deallocate cur_sqlmem_grantinfo
  1762. print'</table><br/>'
  1763.  
  1764. print '<table style="width: 100%">
  1765.     <tr>
  1766.         <td><strong><span class="auto-style1">SQL Server Memory Grant Pending
  1767.         History:-</span><br class="auto-style1"><br></strong>-- Memory Grants
  1768.         Pending above zero for a sustained period is a very strong indicator of
  1769.         memory pressure.</td>
  1770.     </tr>
  1771. </table>
  1772. <br/>'
  1773.  
  1774. /*
  1775. SQL Server memory clerk utilization
  1776. */
  1777.  
  1778. print N'<H3>SQL Memory Clerks Memory Utilization</H3>'
  1779. print N'<table cellspacing="1" cellpadding="1" border="1">'+
  1780. N'<tr><th><strong>Memory Clerk Name</strong></th>'+
  1781. N'<th><strong>Single Page Memory Allocation in (KB)</strong></th></tr>'
  1782.  
  1783. declare cur_sqlmem_clerkinfo cursor for
  1784. SELECT TOP(10) [type] AS [Memory Clerk Type], SUM(pages_kb) AS [SPA Mem, Kb]
  1785. FROM sys.dm_os_memory_clerks WITH (NOLOCK)
  1786. GROUP BY [type]  
  1787. ORDER BY SUM(pages_kb) DESC OPTION (RECOMPILE);
  1788.  
  1789. open cur_sqlmem_clerkinfo
  1790. fetch from cur_sqlmem_clerkinfo into
  1791. @sqlmemclerk_obj_name,
  1792. @sqlmemclerk_mem_kb
  1793. while @@FETCH_STATUS>=0
  1794.  begin
  1795. print '<tr><td>'+cast(@sqlmemclerk_obj_name as varchar(500))+'</td><td>'+cast(@sqlmemclerk_mem_kb as varchar(500))+'</td>'+'</tr>'
  1796. fetch from cur_sqlmem_clerkinfo into
  1797. @sqlmemclerk_obj_name,
  1798. @sqlmemclerk_mem_kb
  1799. end
  1800. close cur_sqlmem_clerkinfo
  1801. deallocate cur_sqlmem_clerkinfo
  1802. print'</table><br/>'
  1803.  
  1804. print'<table style="width: 100%">
  1805.     <tr>
  1806.         <td><strong><span class="auto-style1">SQL Server Memory Clerk
  1807.         Information:-</span><br><br></strong>-- Look for high value for
  1808.         CACHESTORE_SQLCP (Ad-hoc query plans)<br>-- CACHESTORE_SQLCP SQL Plans
  1809.         <br>-- These are cached SQL statements or batches that <br>-- aren''t in
  1810.         stored procedures, functions and triggers<br>-- CACHESTORE_OBJCP Object
  1811.         Plans <br>-- These are compiled plans for <br>-- stored procedures,
  1812.         functions and triggers<br>-- CACHESTORE_PHDR Algebrizer Trees <br>-- An
  1813.         algebrizer tree is the parsed SQL text <br>-- that resolves the table
  1814.         and column names</td>
  1815.     </tr>
  1816. </table>
  1817. <br/>'
  1818. /*
  1819. SQL Server QUery which tells you who is bloating plan cache
  1820. */
  1821.  
  1822. print N'<H3>SQL Ad Hoc Query Plan cache  Utilization by Top 10</H3>'
  1823. print N'<table cellspacing="1" cellpadding="1" border="1">'+
  1824. N'<tr><th><strong>SQL Query Text</strong></th>'+
  1825. N'<th><strong>Size in Bytes(B)</strong></th></tr>'
  1826.  
  1827.  
  1828. declare cur_plancache_bloatqry cursor for
  1829. SELECT TOP(10) [text] AS [QueryText], cp.size_in_bytes
  1830. FROM sys.dm_exec_cached_plans AS cp WITH (NOLOCK)
  1831. CROSS APPLY sys.dm_exec_sql_text(plan_handle)
  1832. WHERE cp.cacheobjtype = N'Compiled Plan'
  1833. AND cp.objtype = N'Adhoc'
  1834. AND cp.usecounts = 1
  1835. ORDER BY cp.size_in_bytes DESC OPTION (RECOMPILE);
  1836.  
  1837.  
  1838. open cur_plancache_bloatqry
  1839. fetch from cur_plancache_bloatqry into
  1840. @adhocQue_QueryText,
  1841. @adhocQue_Qplan_size_byte
  1842.  
  1843. while @@FETCH_STATUS>=0
  1844.  begin
  1845. print '<tr><td>'+cast(@adhocQue_QueryText as varchar(4000))+'</td><td>'+cast(@adhocQue_Qplan_size_byte as varchar(500))+'</td>'+'</tr>'
  1846. fetch from cur_plancache_bloatqry into
  1847. @adhocQue_QueryText,
  1848. @adhocQue_Qplan_size_byte
  1849. end
  1850. close cur_plancache_bloatqry
  1851. deallocate cur_plancache_bloatqry
  1852. print'</table><br/>'
  1853.  
  1854. print '<table style="width: 100%">
  1855.     <tr>
  1856.         <td><strong>SQL Ad-Hoc Query Plan cache Utilization by TOP 50:-<br>
  1857.         </strong>-- Gives you the text and size of single-use ad-hoc queries
  1858.         that waste space in plan cache<br>-- SQL Server Agent creates lots of
  1859.         ad-hoc, single use query plans in SQL Server 2005<br>-- Enabling forced
  1860.         parameterization for the database can help<br></td>
  1861.     </tr>
  1862. </table>
  1863. <p> </p>'
  1864.  
  1865. /*
  1866. SQL Server 2005 TokenAndPermUserStore cache information query
  1867. */
  1868.  
  1869.  
  1870.  
  1871. print N'<H3>SQL Server 2005 TokenAndPermUserStore cache information</H3>'
  1872. print N'<table cellspacing="1" cellpadding="1" border="1">'+
  1873. N'<tr><th><strong>SecurityTokenCacheSize(kb)</strong></th></tr>'
  1874.  
  1875. declare cur_tkenpermcache_info cursor for
  1876. SELECT SUM(pages_kb) AS "SecurityTokenCacheSize(kb)"
  1877. FROM sys.dm_os_memory_clerks
  1878. WHERE name = 'TokenAndPermUserStore'
  1879.  
  1880. open cur_tkenpermcache_info
  1881.  
  1882. fetch from cur_tkenpermcache_info into
  1883. @tokempermcachesizekb
  1884. while @@FETCH_STATUS>=0
  1885.  begin
  1886. print '<tr><td>'+cast(@tokempermcachesizekb as varchar(200))+'</td>'+'</tr>'
  1887. fetch from cur_tkenpermcache_info into
  1888. @tokempermcachesizekb
  1889. end
  1890. close cur_tkenpermcache_info
  1891. deallocate cur_tkenpermcache_info
  1892.  
  1893. print'</table><br/>'
  1894.  
  1895. print N'<H3>Monitor the number of entries that are removed in the cache store during the clock hand movement</H3>'
  1896. print N'<table cellspacing="1" cellpadding="1" border="1">'+
  1897. N'<tr><th><strong>Name</strong></th>'+
  1898. '<th><strong>Type</strong></th>'+
  1899. '<th><strong>clock_hand</strong></th>'+
  1900. '<th><strong>clock_status</strong></th>'+
  1901. '<th><strong>rounds_count</strong></th>'+
  1902. '<th><strong>removed_all_rounds_count</strong></th>'+
  1903. '<th><strong>removed_last_round_count</strong></th>'+
  1904. '<th><strong>updated_last_round_count</strong></th>'+
  1905. N'<th><strong>last_round_start_time</strong></th></tr>'
  1906. declare cur_clockcount_tkenperm cursor for
  1907. select name,type,clock_hand,clock_status,rounds_count,removed_all_rounds_count
  1908. ,removed_last_round_count,updated_last_round_count,last_round_start_time from sys.dm_os_memory_cache_clock_hands where name='TokenAndPermUserStore'
  1909.  
  1910. open cur_clockcount_tkenperm
  1911. fetch from cur_clockcount_tkenperm into
  1912.         @clocktokenname ,
  1913.         @clocktyoe ,
  1914.         @clockhand ,
  1915.         @clock_status ,
  1916.         @clockroundcounts,
  1917.         @clockremovedallroundcount,
  1918.         @clockremovedlastroundcount,
  1919.         @clockupdatedlastroundcount,
  1920.         @clocklastroundstarttime
  1921.  
  1922. while @@FETCH_STATUS>=0
  1923.  begin
  1924. print '<tr><td>'+cast(@clocktokenname as varchar(200))+'</td><td>'+cast(@clocktyoe as varchar(200))+'</td><td>'+cast(@clockhand as varchar(200))+'</td><td>'+cast(@clock_status as varchar(200))+'</td><td>'+cast(@clockroundcounts as varchar(200))+'</td><td>'+cast(@clockremovedallroundcount as varchar(200))+'</td><td>'+cast(@clockremovedlastroundcount as varchar(200))+'</td><td>'+cast(@clockupdatedlastroundcount as varchar(200))+'<td>'+cast(@clocklastroundstarttime as varchar(200))+'</td>'+'</tr>'
  1925. fetch from cur_clockcount_tkenperm into
  1926. @clocktokenname ,
  1927.         @clocktyoe ,
  1928.         @clockhand ,
  1929.         @clock_status ,
  1930.         @clockroundcounts,
  1931.         @clockremovedallroundcount,
  1932.         @clockremovedlastroundcount,
  1933.         @clockupdatedlastroundcount,
  1934.         @clocklastroundstarttime
  1935. end
  1936.  
  1937.  close cur_clockcount_tkenperm
  1938.  deallocate cur_clockcount_tkenperm
  1939.  print'</table><br/>'
  1940. print'<table style="width: 100%">
  1941.     <tr>
  1942.         <td><strong><span class="auto-style1">SQL Server 2005
  1943.         TokenAndPermUserStore cache information:-</span><br class="auto-style1">
  1944.         </strong>--TokenAndPermUserStore is one of the many caches present in
  1945.         the SQL Server 2005 memory architecture. As the name implies, this cache
  1946.         stores various security related information used by the SQL Server
  1947.         Engine.<br>--These tokens represent information about cumulative
  1948.         permission checks for queries.<br>--There are several indicators you can
  1949.         monitor to determine if you are running into this class of problems.<br>
  1950.         1. The amount of memory used by this security token cache<br>2. The
  1951.         number of entries present in this security token cache<br>3. The extent
  1952.         of contention on this security token cache<br>--There is no specific
  1953.         threshold for this size beyond which the problem starts to happen. The
  1954.         characteristic you need to monitor is the rate at which this cache size
  1955.         is growing.<br>--If you are encountering problems with this cache, then
  1956.         you will notice that as the size of the cache grows, the nature of the
  1957.         problems you experience becomes worse. On a sample server that
  1958.         experienced this problem, the cache grew at a rate approximately 1MB per
  1959.         min to reach close to 1.2 GB. We have seen the problem starting to show
  1960.         up even when the size of this cache reaches several hundred MB.<br>--The
  1961.         symptoms that you want to correlate with the above data points include a
  1962.         combination of the following:<br>1. Queries which normally finish faster
  1963.         take a long time<br>2. CPU usage of SQL Server process is relatively
  1964.         higher. CPU usage could come down after remaining high for a period of
  1965.         time.<br>3. Connections from your applications keep increasing
  1966.         (specifically in connection pool environments)<br>4. You encounter
  1967.         connection or query timeouts<br>--In Microsoft SQL Server 2005,
  1968.         performance issues may occur and CPU usage may increase when the size of
  1969.         the TokenAndPermUserStore cache store increases to several hundred
  1970.         megabytes. To address these issues, SQL Server 2005 Service Pack 3
  1971.         enables you to customize the quota for the TokenAndPermUserStore cache
  1972.         store.<br>--Quota defines the threshold for the number of entries in the
  1973.         cache store. As soon as a new entry is added that exceeds the quota, an
  1974.         internal clock hand movement is made that decrements the cost of each
  1975.         entry in the store, and those entries whose cost reaches zero are
  1976.         released. <br>--You can monitor the number of entries that are removed
  1977.         in the cache store during the clock hand movement. To do this, query the
  1978.         sys.dm_os_memory_cache_clock_hands Dynamic Management View.<br>
  1979.         <a href="http://support.microsoft.com/default.aspx?scid=kb;EN-US;959823" target="_blank">
  1980.         http://support.microsoft.com/default.aspx?scid=kb;EN-US;959823</a></td>
  1981.     </tr>
  1982. </table><br/>'
  1983.  
  1984.  
  1985. /*
  1986. Trace Information about this SQL Server Instance.
  1987. */
  1988.  
  1989.  
  1990. print N'<H3>SQL Server enable trace information</H3>'
  1991. print N'<table cellspacing="1" cellpadding="1" border="1">'+
  1992. N'<tr><th><strong>TraceFlag Name</strong></th>'+
  1993. '<th><strong>Status</strong></th>'+
  1994. '<th><strong>Global</strong></th>'+
  1995. N'<th><strong>Session</strong></th></tr>'
  1996. set nocount on
  1997. create table #traceinfo(flag varchar(20),Status varchar(10),Global varchar(10),Session varchar(10))
  1998. INSERT INTO #traceinfo EXECUTE ('DBCC TRACESTATUS(-1)')
  1999.  
  2000. declare cur_trace_info cursor for select flag,Status,Global,Session from #traceinfo
  2001. open cur_trace_info
  2002. fetch from cur_trace_info
  2003. into
  2004. @flagname,
  2005. @flagstatus,
  2006. @flagglobal,
  2007. @flagsesion
  2008.  
  2009. while @@FETCH_STATUS>=0
  2010.  begin
  2011. print '<tr><td>'+cast(@flagname as varchar(20))+'</td><td>'+cast(@flagstatus as varchar(20))+'</td><td>'+cast(@flagglobal as varchar(20))+'</td><td>'+cast(@flagsesion as varchar(20))+'</td>'+'</tr>'
  2012. fetch from cur_trace_info
  2013. into
  2014. @flagname,
  2015. @flagstatus,
  2016. @flagglobal,
  2017. @flagsesion
  2018. end
  2019. close cur_trace_info
  2020. deallocate cur_trace_info
  2021. drop table #traceinfo
  2022.  print'</table><br/>'
  2023. print'<table>
  2024.     <tr>
  2025.     <td>--For More information about the traceflag please visit following link
  2026.     <a href="http://msdn.microsoft.com/en-us/library/ms188396.aspx" target="_blank">
  2027.         Trace Flag Information</a></td>
  2028.     </tr>
  2029. </table><br/>'
  2030.  
  2031.  
  2032.  
  2033. /*
  2034. Script for getting Top 20 SP ordered bu total worker time to find out most expensive sp by total worker time
  2035. indication could be CPU pressure.
  2036. The following example returns information about the top five queries ranked by average CPU time. This example aggregates the queries according to their query hash so that logically equivalent queries are grouped by their cumulative resource consumption.
  2037.  
  2038.  
  2039. */
  2040.  
  2041.  
  2042. print N'<H3>SQL Server Top 10 SP ordered by Total Worker time:-</H3>'
  2043. print N'<table cellspacing="1" cellpadding="1" border="1">'+
  2044. N'<tr><th><strong>SP Name/Text</strong></th>'+
  2045. '<th><strong>Total Worker Time in Microsecond</strong></th>'+
  2046. '<th><strong>Average Worker Time in Microsecond</strong></th>'+
  2047. '<th><strong>Execution Count</strong></th>'+
  2048. '<th><strong>Calls /Second</strong></th>'+
  2049. '<th><strong>Average Elapsed Time in Microsecond</strong></th>'+
  2050. '<th><strong>Max Logical Reads</strong></th>'+
  2051. '<th><strong>Max Logical Writes</strong></th>'+
  2052. N'<th><strong>Age in Cache(Min)</strong></th></tr>'
  2053.  
  2054. declare cur_topspcpu_info cursor for
  2055. SELECT TOP(10) qt.[text] AS [SP Name],
  2056. qs.total_worker_time AS [TotalWorkerTimeinmicroseconds],
  2057. qs.total_worker_time/qs.execution_count AS [AvgWorkerTimeinmicroseconds],
  2058. qs.execution_count AS [Execution Count],
  2059. NULLIF(qs.execution_count/DATEDIFF(Second, qs.creation_time,
  2060. GETDATE()), 1) AS [Calls/Second],
  2061. ISNULL(qs.total_elapsed_time/qs.execution_count, 0)
  2062. AS [AvgElapsedTimemicroseconds],
  2063. qs.max_logical_reads, qs.max_logical_writes,
  2064. DATEDIFF(Minute, qs.creation_time, GETDATE()) AS [Age in Cache]
  2065. FROM sys.dm_exec_query_stats AS qs
  2066. CROSS APPLY sys.dm_exec_sql_text(qs.[sql_handle]) AS qt
  2067. --WHERE qt.[dbid] = DB_ID() -- Filter by current database
  2068. ORDER BY qs.total_worker_time DESC;
  2069.  
  2070. open cur_topspcpu_info
  2071. fetch from cur_topspcpu_info into
  2072.         @topspbycpu_spname,
  2073.         @topspbycpu_totalworkertimeinmicros ,
  2074.         @topspbycpu_Avgworkertimeinmicros ,
  2075.         @topspbycpu_Executioncount ,
  2076.         @topspbycpu_callsecond ,
  2077.         @topspbycpu_averageelapsedtimeinmicros ,
  2078.         @topspbycpu_maxlogicalread ,
  2079.         @topspbycpu_maxlogicalwrites ,
  2080.         @topspbycpu_ageincache
  2081.  
  2082.  
  2083. while @@FETCH_STATUS>=0
  2084.  begin
  2085. print '<tr><td>'+cast(@topspbycpu_spname as varchar(1000))+
  2086.      '</td><td>'+cast(@topspbycpu_totalworkertimeinmicros as varchar(200))+
  2087.      '</td><td>'+cast(@topspbycpu_Avgworkertimeinmicros as varchar(200))+
  2088.      '</td><td>'+cast(@topspbycpu_Executioncount as varchar(20))+
  2089.      '</td><td>'+ISNULL(cast(@topspbycpu_callsecond as varchar(20)),0)+
  2090.      '</td><td>'+cast(@topspbycpu_averageelapsedtimeinmicros as varchar(20))+
  2091.      '</td><td>'+cast(@topspbycpu_maxlogicalread as varchar(20))+
  2092.      '</td><td>'+cast(@topspbycpu_maxlogicalwrites as varchar(20))+
  2093.      '</td><td>'+cast(@topspbycpu_ageincache as varchar(20))+'</td>'+'</tr>'
  2094. fetch from cur_topspcpu_info into
  2095.         @topspbycpu_spname,
  2096.         @topspbycpu_totalworkertimeinmicros ,
  2097.         @topspbycpu_Avgworkertimeinmicros ,
  2098.         @topspbycpu_Executioncount ,
  2099.         @topspbycpu_callsecond ,
  2100.         @topspbycpu_averageelapsedtimeinmicros ,
  2101.         @topspbycpu_maxlogicalread ,
  2102.         @topspbycpu_maxlogicalwrites ,
  2103.         @topspbycpu_ageincache
  2104.  
  2105. end
  2106. close cur_topspcpu_info
  2107. deallocate cur_topspcpu_info
  2108.  print'</table><br/>'
  2109.  print N'<table style="width: 100%">
  2110.     <tr>
  2111.         <td><strong>SQL Server Top 10 SP ordered by Total Worker time:-</strong><br>
  2112.         --Above table shows the top 10 stored procedures sorted by total worker
  2113.         time (which equates to CPU pressure). This will tell you the most
  2114.         expensive stored procedures from a CPU perspective</td>
  2115.     </tr>
  2116. </table>'
  2117.  
  2118.  
  2119.  
  2120.  /*
  2121.  
  2122. SQL Server Scheduler Information and NUMA related Information if parent node has more than one vlaue other than 0 and 32 and 64
  2123. then it indicate that you have NUMA architecture available with your server
  2124.  
  2125.  */
  2126.  
  2127.  
  2128.  
  2129.  
  2130. print N'<H3>SQL Server Scheduler stats and NUMA Stats :-</H3>'
  2131. print N'<table cellspacing="1" cellpadding="1" border="1">'+
  2132. N'<tr><th><strong>Parent Node ID/Text</strong></th>'+
  2133. '<th><strong>Scheduler ID</strong></th>'+
  2134. '<th><strong>CPU ID</strong></th>'+
  2135. '<th><strong>Schedler Status</strong></th>'+
  2136. '<th><strong>Is Online</strong></th>'+
  2137. '<th><strong>Is Idle</strong></th>'+
  2138. '<th><strong>Preemptive Switches Count:-</strong></th>'+
  2139. '<th><strong>Context Switches Count:-</strong></th>'+
  2140. '<th><strong>Idle Switches Count</strong></th>'+
  2141. '<th><strong>Current Tasks Count</strong></th>'+
  2142. '<th><strong>Runnable Tasks Count</strong></th>'+
  2143. '<th><strong>Current Workers Count</strong></th>'+
  2144. '<th><strong>Pending Disk IO Count</strong></th>'+
  2145. '<th><strong>Failed to Create Workerthread Count</strong></th>'+
  2146. N'<th><strong>Active Workers Count</strong></th></tr>'
  2147.  
  2148. declare cur_sqlschedule_info cursor for
  2149. select parent_node_id,scheduler_id,cpu_id,status,is_online,is_idle,
  2150. preemptive_switches_count,
  2151. context_switches_count,
  2152. idle_switches_count,
  2153. current_tasks_count,
  2154. runnable_tasks_count,
  2155. current_workers_count,
  2156. active_workers_count,
  2157. pending_disk_io_count,
  2158. failed_to_create_worker
  2159.  
  2160.  from sys.dm_os_schedulers
  2161.  
  2162.  open cur_sqlschedule_info
  2163.  fetch from cur_sqlschedule_info into
  2164.         @sqlschedule_parenenodeid,
  2165.         @sqlschedule_schdulerid,
  2166.         @sqlschedule_cpuid,
  2167.         @sqlschedule_status,
  2168.         @sqlschedule_isonline ,
  2169.         @sqlschedule_isidle ,
  2170.         @sqlschedule_preemptiveswtichescounts ,
  2171.         @sqlschedule_contextswtichescounts ,
  2172.         @sqlschedule_idleswtichescounts ,
  2173.         @sqlschedule_currenttaskcounts ,
  2174.         @sqlschedule_runnabletaskcounts ,
  2175.         @sqlschedule_currentworkercounts ,
  2176.         @sqlschedule_activeworkercounts,
  2177.         @sqlschedule_pendingiocounts,
  2178.         @sqlschedule_failedtocreate    
  2179.  
  2180.  
  2181. while @@FETCH_STATUS>=0
  2182.  begin
  2183. print '<tr><td>'+cast(@sqlschedule_parenenodeid as varchar(20))+'</td><td>'+cast(@sqlschedule_schdulerid as varchar(20))+'</td><td>'+cast(@sqlschedule_cpuid as varchar(20))+'</td><td>'+cast(@sqlschedule_status as varchar(20))+'</td><td>'+cast(@sqlschedule_isonline as varchar(20))+'</td><td>'+cast(@sqlschedule_isidle as varchar(200))+'</td><td>'+cast(@sqlschedule_preemptiveswtichescounts as varchar(20))+'</td><td>'+cast(@sqlschedule_contextswtichescounts as varchar(20))+'</td><td>'+cast(@sqlschedule_idleswtichescounts as varchar(20))+'</td><td>'+cast(@sqlschedule_currenttaskcounts as varchar(20))+'</td><td>'+cast(@sqlschedule_runnabletaskcounts as varchar(20))+'</td><td>'+cast(@sqlschedule_currentworkercounts as varchar(20))+'</td><td>'+cast(@sqlschedule_failedtocreate as varchar(20))+'</td><td>'+cast(@sqlschedule_pendingiocounts as varchar(20))+'</td><td>'+cast(@sqlschedule_activeworkercounts as varchar(20))+'</td>'+'</tr>'
  2184.  
  2185.  fetch from cur_sqlschedule_info into
  2186.         @sqlschedule_parenenodeid,
  2187.         @sqlschedule_schdulerid,
  2188.         @sqlschedule_cpuid,
  2189.         @sqlschedule_status,
  2190.         @sqlschedule_isonline ,
  2191.         @sqlschedule_isidle ,
  2192.         @sqlschedule_preemptiveswtichescounts ,
  2193.         @sqlschedule_contextswtichescounts ,
  2194.         @sqlschedule_idleswtichescounts ,
  2195.         @sqlschedule_currenttaskcounts ,
  2196.         @sqlschedule_runnabletaskcounts ,
  2197.         @sqlschedule_currentworkercounts ,
  2198.         @sqlschedule_activeworkercounts,
  2199.         @sqlschedule_pendingiocounts,
  2200.         @sqlschedule_failedtocreate    
  2201.  
  2202. end
  2203. close cur_sqlschedule_info
  2204. deallocate cur_sqlschedule_info
  2205.  print'</table><br/>'
  2206.  print '<table style="width: 100%">
  2207.     <tr>
  2208.         <td><strong>SQL Server Scheduler and NUMA Related Information:-<br>
  2209.         </strong>--Non-uniform memory access (NUMA) is enabled on your SQL
  2210.         Server instance.<br>--For more information about NUMA please refer to
  2211.         the following links<br>
  2212.         <a href="http://msdn.microsoft.com/en-in/library/ms178144(v=sql.105).aspx">
  2213.         http://msdn.microsoft.com/en-in/library/ms178144(v=sql.105).aspx</a><br>
  2214.         <a href="http://msdn.microsoft.com/en-us/library/ms345357.aspx">
  2215.         http://msdn.microsoft.com/en-us/library/ms345357.aspx</a></td>
  2216.     </tr>
  2217. </table>'
  2218.  
  2219.  
  2220. /*
  2221. Looking for Physical IO read Pressure
  2222. Top 20 Executed SP ordered by physical reads.
  2223. */
  2224.  
  2225. print N'<H3>SQL Server Top 10 SP Executed by Physical Read:-</H3>'
  2226. print N'<table cellspacing="1" cellpadding="1" border="1">'+
  2227. N'<tr><th><strong>SP Name</strong></th>'+
  2228. '<th><strong>Total Physical Reads</strong></th>'+
  2229. '<th><strong>Avg Physical Reads</strong></th>'+
  2230. '<th><strong>Execution Count</strong></th>'+
  2231. '<th><strong>Calls/Second</strong></th>'+
  2232. '<th><strong>AvgWorker Time(in Microsecond)</strong></th>'+
  2233. '<th><strong>TotalWorker Time(in Microsecond)</strong></th>'+
  2234. '<th><strong>Avg Elapsed Time(in Microsecond)</strong></th>'+
  2235. '<th><strong>Max Logical Reads</strong></th>'+
  2236. '<th><strong>Max Logical Writes</strong></th>'+
  2237. N'<th><strong>Age In Cache</strong></th></tr>'
  2238.  
  2239.  
  2240. declare cur_topspiopre_info cursor for
  2241. SELECT TOP (20) qt.[text] AS [SP Name], qs.total_physical_reads,
  2242. qs.total_physical_reads/qs.execution_count AS [Avg Physical Reads],
  2243. qs.execution_count AS [Execution Count],
  2244. qs.execution_count/DATEDIFF(Second, qs.creation_time, GetDate()) AS [Calls/Second],
  2245. qs.total_worker_time/qs.execution_count AS [AvgWorkerTime],
  2246. qs.total_worker_time AS [TotalWorkerTime],
  2247. qs.total_elapsed_time/qs.execution_count AS [AvgElapsedTime],
  2248. qs.max_logical_reads, qs.max_logical_writes,
  2249. DATEDIFF(Minute, qs.creation_time, GetDate()) AS [Age in Cache]
  2250. FROM sys.dm_exec_query_stats AS qs
  2251. CROSS APPLY sys.dm_exec_sql_text(qs.[sql_handle]) AS qt
  2252. --WHERE qt.[dbid] = db_id() -- Filter by current database
  2253. ORDER BY qs.total_physical_reads DESC;
  2254.  
  2255. open cur_topspiopre_info
  2256. fetch from cur_topspiopre_info into
  2257. @topsp_iopressure_spname,
  2258. @topsp_iopressure_physicalread,
  2259. @topsp_iopressure_spname_avgphysicalread,
  2260. @topsp_iopressure_spname_Executioncount,
  2261. @topsp_iopressure_spname_callsecond,
  2262. @topsp_iopressure_spname_Avgworkertime,
  2263. @topsp_iopressure_spname_Totalworkertime,
  2264. @topsp_iopressure_spname_Avgelapsedtime,
  2265. @topsp_iopressure_spname_maxlogicalreads,
  2266. @topsp_iopressure_spname_maxlogicalwrite,
  2267. @topsp_iopressure_spname_ageincache
  2268.  
  2269. while @@FETCH_STATUS>=0
  2270.  begin
  2271. print '<tr><td>'+cast(@topsp_iopressure_spname as varchar(1000))+
  2272.       '</td><td>'+cast(@topsp_iopressure_physicalread as varchar(40))+
  2273.       '</td><td>'+cast(@topsp_iopressure_spname_avgphysicalread as varchar(40))+
  2274.       '</td><td>'+cast(@topsp_iopressure_spname_Executioncount as varchar(40))+
  2275.       '</td><td>'+ISNULL(cast(@topsp_iopressure_spname_callsecond as varchar(40)),0)+
  2276.       '</td><td>'+cast(@topsp_iopressure_spname_Avgworkertime as varchar(40))+
  2277.       '</td><td>'+cast(@topsp_iopressure_spname_Totalworkertime as varchar(40))+
  2278.       '</td><td>'+cast(@topsp_iopressure_spname_Avgelapsedtime as varchar(40))+
  2279.       '</td><td>'+cast(@topsp_iopressure_spname_maxlogicalreads as varchar(40))+
  2280.       '</td><td>'+cast(@topsp_iopressure_spname_maxlogicalwrite as varchar(40))+
  2281.       '</td><td>'+cast(@topsp_iopressure_spname_ageincache as varchar(40))+'</td>'+'</tr>'
  2282.  
  2283. fetch from cur_topspiopre_info into
  2284. @topsp_iopressure_spname,
  2285. @topsp_iopressure_physicalread,
  2286. @topsp_iopressure_spname_avgphysicalread,
  2287. @topsp_iopressure_spname_Executioncount,
  2288. @topsp_iopressure_spname_callsecond,
  2289. @topsp_iopressure_spname_Avgworkertime,
  2290. @topsp_iopressure_spname_Totalworkertime,
  2291. @topsp_iopressure_spname_Avgelapsedtime,
  2292. @topsp_iopressure_spname_maxlogicalreads,
  2293. @topsp_iopressure_spname_maxlogicalwrite,
  2294. @topsp_iopressure_spname_ageincache
  2295. end
  2296.  
  2297. close cur_topspiopre_info
  2298. deallocate cur_topspiopre_info
  2299.  print'</table><br/>'
  2300.  print'<br>
  2301. <table style="width: 100%">
  2302.     <tr>
  2303.         <td><strong><span class="auto-style1">SQL Server Top 10 SP Executed by
  2304.         Physical Read(IO Pressure):-</span><br class="auto-style1"></strong>--Above table shows the top 10 stored procedures sorted by total
  2305.         physical reads(which equates to read I/O pressure). This will tell you
  2306.         the most expensive stored procedures from a read I/O perspective.<br>--
  2307.         If it is high Physical Read means SQL has to go to the disk in order to
  2308.         write the data this inturns very expensive operation.</td>
  2309.     </tr>
  2310. </table>
  2311. <br/>'
  2312.  
  2313. /*
  2314. -- Get Top 25 executed SP's ordered by logical reads (memory pressure)
  2315. */
  2316.  
  2317.  
  2318.  
  2319. print N'<H3>SQL Server Top 10 SP Executed by Logical Read(Memory Pressure):-</H3>'
  2320. print N'<table cellspacing="1" cellpadding="1" border="1">'+
  2321. N'<tr><th><strong>SP Name</strong></th>'+
  2322. '<th><strong>Total Logical Reads</strong></th>'+
  2323. '<th><strong>Execution Count</strong></th>'+
  2324. '<th><strong>Average Logical Reads</strong></th>'+
  2325. '<th><strong>Calls/Second</strong></th>'+
  2326. '<th><strong>AvgWorker Time(in Microsecond)</strong></th>'+
  2327. '<th><strong>TotalWorker Time(in Microsecond)</strong></th>'+
  2328. '<th><strong>Avg Elapsed Time(in Microsecond)</strong></th>'+
  2329. '<th><strong>Total Logical Writes</strong></th>'+
  2330. '<th><strong>Max Logical Reads</strong></th>'+
  2331. '<th><strong>Max Logical Writes</strong></th>'+
  2332. '<th><strong>Total Physical Reads</strong></th>'+
  2333. N'<th><strong>Age In Cache</strong></th></tr>'
  2334.  
  2335.  
  2336. declare cur_sp_top20logical cursor for
  2337. SELECT TOP(10) qt.[text] AS 'SP Name', total_logical_reads,
  2338. qs.execution_count AS 'Execution Count',
  2339. total_logical_reads/qs.execution_count AS 'AvgLogicalReads',
  2340. qs.execution_count/ISNULL(DATEDIFF(Second, qs.creation_time, GetDate()),1) AS 'Calls/Second',
  2341. qs.total_worker_time/qs.execution_count AS 'AvgWorkerTime',
  2342. qs.total_worker_time AS 'TotalWorkerTime',
  2343. qs.total_elapsed_time/qs.execution_count AS 'AvgElapsedTime',
  2344. qs.total_logical_writes,
  2345. qs.max_logical_reads, qs.max_logical_writes, qs.total_physical_reads,
  2346. DATEDIFF(Minute, qs.creation_time, GetDate()) AS 'Age in Cache'
  2347. FROM sys.dm_exec_query_stats AS qs
  2348. CROSS APPLY sys.dm_exec_sql_text(qs.[sql_handle]) AS qt
  2349. --WHERE qt.[dbid] = db_id() -- Filter by current database
  2350. ORDER BY total_logical_reads DESC;
  2351.  
  2352. open cur_sp_top20logical
  2353. fetch from cur_sp_top20logical into
  2354. @topsp_mempressure_spname ,
  2355. @topsp_mempressure_totallogicalread ,
  2356. @topsp_mempressure_executioncount,
  2357. @topsp_mempressure_Avglogicalreads,
  2358. @topsp_mempressure_callspersecond ,
  2359. @topsp_mempressure_avgworkertime ,
  2360. @topsp_mempressure_totalworkertime ,
  2361. @topsp_mempressure_Avgelapsedtime ,
  2362. @topsp_mempressure_totallogicalwrite ,
  2363. @topsp_mempressure_maxlogicalread ,
  2364. @topsp_mempressure_maxlogicalwrite ,
  2365. @topsp_mempressure_totalphysicalread ,
  2366. @topsp_mempressure_ageincache
  2367.  
  2368.  
  2369. while @@FETCH_STATUS>=0
  2370.  begin
  2371. print '<tr><td>'+cast(@topsp_mempressure_spname as varchar(1000))+
  2372.       '</td><td>'+cast(@topsp_mempressure_totallogicalread as varchar(40))+
  2373.       '</td><td>'+cast(@topsp_mempressure_executioncount as varchar(40))+
  2374.       '</td><td>'+cast(@topsp_mempressure_Avglogicalreads as varchar(40))+
  2375.       '</td><td>'+ISNULL(cast(@topsp_mempressure_callspersecond as varchar(40)),0)+
  2376.       '</td><td>'+cast(@topsp_mempressure_avgworkertime as varchar(40))+
  2377.       '</td><td>'+cast(@topsp_mempressure_totalworkertime as varchar(40))+
  2378.       '</td><td>'+cast(@topsp_mempressure_Avgelapsedtime as varchar(40))+
  2379.       '</td><td>'+cast(@topsp_mempressure_totallogicalwrite as varchar(40))+
  2380.       '</td><td>'+cast(@topsp_mempressure_maxlogicalread as varchar(40))+
  2381.       '</td><td>'+cast(@topsp_mempressure_maxlogicalwrite as varchar(40))+
  2382.       '</td><td>'+cast(@topsp_mempressure_totalphysicalread as varchar(40))+
  2383.       '</td><td>'+cast(@topsp_mempressure_ageincache as varchar(40))+'</td>'+'</tr>'
  2384.  
  2385. fetch from cur_sp_top20logical into
  2386. @topsp_mempressure_spname ,
  2387. @topsp_mempressure_totallogicalread ,
  2388. @topsp_mempressure_executioncount,
  2389. @topsp_mempressure_Avglogicalreads,
  2390. @topsp_mempressure_callspersecond ,
  2391. @topsp_mempressure_avgworkertime ,
  2392. @topsp_mempressure_totalworkertime ,
  2393. @topsp_mempressure_Avgelapsedtime ,
  2394. @topsp_mempressure_totallogicalwrite ,
  2395. @topsp_mempressure_maxlogicalread ,
  2396. @topsp_mempressure_maxlogicalwrite ,
  2397. @topsp_mempressure_totalphysicalread ,
  2398. @topsp_mempressure_ageincache
  2399. end
  2400.  
  2401.  
  2402. close cur_sp_top20logical
  2403. deallocate cur_sp_top20logical
  2404.  print'</table><br/>'
  2405.  
  2406.  print'<br>
  2407. <table style="width: 100%">
  2408.     <tr>
  2409.         <td><strong>SQL Server Top 10 SP by Logical Reads(Memory Pressure):-</strong><br>
  2410.         --Above table shows the top 10 stored procedures sorted by total logical
  2411.         reads(which equates to memory pressure). This will tell you the most
  2412.         expensive stored procedures from a memory perspective, and indirectly
  2413.         from a read I/O perspective.</td>
  2414.     </tr>
  2415. </table>
  2416. <br/>'
  2417.  /*
  2418.    Looking at Index Advantage to find missing indexes
  2419. -- Missing Indexes by Index Advantage (make sure to also look at last user seek time)
  2420.  */
  2421.  
  2422.  print N'<H3>SQL Server Missing Indexes by Index Advantage:-</H3>'
  2423. print N'<table cellspacing="1" cellpadding="1" border="1">'+
  2424. N'<tr><th><strong>Index Advantage</strong></th>'+
  2425. '<th><strong>Last User Seek</strong></th>'+
  2426. '<th><strong>Datbase Schema Table</strong></th>'+
  2427. '<th><strong>Equality Columns</strong></th>'+
  2428. '<th><strong>Inequality Columns</strong></th>'+
  2429. '<th><strong>Included Columns</strong></th>'+
  2430. '<th><strong>Unique Compiles</strong></th>'+
  2431. '<th><strong>User Seeks</strong></th>'+
  2432. '<th><strong>Average Total user cost</strong></th>'+
  2433. N'<th><strong>Average User Impact</strong></th></tr>'
  2434.  
  2435.  
  2436.  
  2437.  declare cur_msng_idx_cost_cur cursor for
  2438.  SELECT user_seeks * avg_total_user_cost * (avg_user_impact * 0.01) AS
  2439. index_advantage, migs.last_user_seek,
  2440. mid.statement AS 'Database.Schema.Table',
  2441. mid.equality_columns, mid.inequality_columns, mid.included_columns,
  2442. migs.unique_compiles, migs.user_seeks, migs.avg_total_user_cost, migs.avg_user_impact
  2443. FROM sys.dm_db_missing_index_group_stats AS migs WITH (NOLOCK)
  2444. INNER JOIN sys.dm_db_missing_index_groups AS mig WITH (NOLOCK)
  2445. ON migs.group_handle = mig.index_group_handle
  2446. INNER JOIN sys.dm_db_missing_index_details AS mid WITH (NOLOCK)
  2447. ON mig.index_handle = mid.index_handle
  2448. ORDER BY index_advantage DESC;
  2449.  
  2450. open cur_msng_idx_cost_cur
  2451. fetch from cur_msng_idx_cost_cur into
  2452. @msngidx_idxadv,
  2453. @msngidx_lastuser_seek,
  2454. @msngidx_dbschematable,
  2455. @msngidx_equalitycols,
  2456. @msngidx_inequalitycols,
  2457. @msngidx_includedcols,
  2458. @msngidx_uniquecompiles,
  2459. @msngidx_userseeks,
  2460. @msngidx_avgtotalusercost,
  2461. @msngidx_avguserimpact
  2462.  
  2463.  
  2464. while @@FETCH_STATUS>=0
  2465.  begin
  2466. print '<tr><td>'+cast(@msngidx_idxadv as varchar(100))+
  2467.       '</td><td>'+cast(@msngidx_lastuser_seek as varchar(40))+
  2468.       '</td><td>'+cast(@msngidx_dbschematable as varchar(1000))+
  2469.       '</td><td>'+cast(@msngidx_equalitycols as varchar(1000))+
  2470.       '</td><td>'+cast(@msngidx_inequalitycols as varchar(1000))+
  2471.       '</td><td>'+cast(@msngidx_includedcols as varchar(1000))+
  2472.       '</td><td>'+cast(@msngidx_uniquecompiles as varchar(40))+
  2473.       '</td><td>'+cast(@msngidx_userseeks as varchar(40))+
  2474.       '</td><td>'+cast(@msngidx_avgtotalusercost as varchar(40))+
  2475.       '</td><td>'+cast(@msngidx_avguserimpact as varchar(40))+'</td>'+'</tr>'
  2476.  
  2477. fetch from cur_msng_idx_cost_cur into
  2478. @msngidx_idxadv,
  2479. @msngidx_lastuser_seek,
  2480. @msngidx_dbschematable,
  2481. @msngidx_equalitycols,
  2482. @msngidx_inequalitycols,
  2483. @msngidx_includedcols,
  2484. @msngidx_uniquecompiles,
  2485. @msngidx_userseeks,
  2486. @msngidx_avgtotalusercost,
  2487. @msngidx_avguserimpact
  2488.  
  2489. end
  2490.  
  2491.  
  2492. close cur_msng_idx_cost_cur
  2493. deallocate cur_msng_idx_cost_cur
  2494.  print'</table><br/>'
  2495.  
  2496.  
  2497.  print N'<H3>SQL Server Missing Indexes With Index Creating:-</H3>'
  2498. print N'<table cellspacing="1" cellpadding="1" border="1">'+
  2499. N'<tr><th><strong>Index Group Handle</strong></th>'+
  2500. '<th><strong>Index Handle</strong></th>'+
  2501. '<th><strong>Index Improvments Measures</strong></th>'+
  2502. '<th><strong>Index Create Statement</strong></th>'+
  2503. '<th><strong>Index Group Handle</strong></th>'+
  2504. '<th><strong>Index Unique Compiles</strong></th>'+
  2505. '<th><strong>Index User Seeks</strong></th>'+
  2506. '<th><strong>Index  User Scans</strong></th>'+
  2507. '<th><strong>Index Last User Seek</strong></th>'+
  2508. '<th><strong>Index Last User Scan</strong></th>'+
  2509. '<th><strong>Index Avg Total User Cost</strong></th>'+
  2510. '<th><strong>Index Avg User Impact</strong></th>'+
  2511. '<th><strong>Index System Seek</strong></th>'+
  2512. '<th><strong>Index System Scan</strong></th>'+
  2513. '<th><strong>Index Last Sytem Seek</strong></th>'+
  2514. '<th><strong>Index Avg total System Cost</strong></th>'+
  2515. '<th><strong>Index Avg System Impact</strong></th>'+
  2516. '<th><strong>Database ID</strong></th>'+
  2517. N'<th><strong>Object ID</strong></th></tr>'
  2518.  
  2519. declare cu_msgdet cursor for
  2520. SELECT  
  2521.  mig.index_group_handle
  2522.  , mid.index_handle
  2523.  ,CONVERT (decimal (28,1), migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)) AS improvement_measure
  2524.  ,'CREATE INDEX missing_index_' + CONVERT (varchar, mig.index_group_handle) + '_' + CONVERT (varchar, mid.index_handle)
  2525.   + ' ON ' + mid.statement
  2526.   + ' (' + ISNULL (mid.equality_columns,'')
  2527.     + CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END + ISNULL (mid.inequality_columns, '')
  2528.   + ')'
  2529.   + ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement,
  2530.   migs.group_handle,
  2531.   migs.unique_compiles,
  2532.   migs.user_seeks,
  2533.   migs.user_scans,
  2534.   migs.last_user_seek,
  2535.   ISNULL(migs.last_user_scan,0) as last_user_scan,
  2536.   migs.avg_total_user_cost,
  2537.   migs.avg_user_impact,
  2538.   migs.system_seeks,
  2539.   migs.system_scans,
  2540.   ISNULL(migs.last_system_seek,0) as last_system_seek,
  2541.   migs.avg_total_system_cost,
  2542.   migs.avg_system_impact,
  2543.    mid.database_id, mid.[object_id]
  2544. FROM sys.dm_db_missing_index_groups mig
  2545. INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
  2546. INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
  2547. WHERE CONVERT (decimal (28,1), migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)) > 10
  2548. ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC
  2549.  
  2550. open cu_msgdet
  2551. fetch from cu_msgdet into
  2552.         @msgindx_idxgroup_handle ,
  2553.         @msgindx_idx_handle ,
  2554.         @msgindx_improvement_measures,
  2555.         @msgindx_createidxstat,
  2556.         @msgindx_grphandle ,
  2557.         @msgindx_uniqcompiles,
  2558.         @msgindx_userseeks,
  2559.         @msgindx_usescans ,
  2560.         @msgindx_lastuserseek ,
  2561.         @msgindx_lastuserscan ,
  2562.         @msgindx_avgtotalusercost,
  2563.         @msgindx_avguserimpact ,
  2564.         @msgindx_systemseek ,
  2565.         @msgindx_systemscan ,
  2566.         @msgindx_lastsysseek ,
  2567.         @msgindx_avgtotalsyscost ,
  2568.         @msgindx_avgsysimpact ,
  2569.         @msgindx_databaseid ,
  2570.         @msgindx_objid
  2571.  
  2572.  
  2573.  
  2574. while @@FETCH_STATUS>=0
  2575.  begin
  2576. print '<tr><td>'+cast(@msgindx_idxgroup_handle as varchar(100))+
  2577.       '</td><td>'+cast(@msgindx_idx_handle as varchar(100))+
  2578.       '</td><td>'+cast(@msgindx_improvement_measures as varchar(100))+
  2579.       '</td><td>'+cast(@msgindx_createidxstat as varchar(5000))+
  2580.       '</td><td>'+cast(@msgindx_grphandle as varchar(1000))+
  2581.       '</td><td>'+cast(@msgindx_uniqcompiles as varchar(1000))+
  2582.       '</td><td>'+cast(@msgindx_userseeks as varchar(40))+
  2583.       '</td><td>'+cast(@msgindx_usescans as varchar(40))+
  2584.        '</td><td>'+cast(@msgindx_lastuserseek as varchar(40))+
  2585.         '</td><td>'+cast(@msgindx_lastuserscan as varchar(40))+
  2586.         '</td><td>'+cast(@msgindx_avgtotalusercost as varchar(40))+
  2587.         '</td><td>'+cast(@msgindx_avguserimpact as varchar(40))+
  2588.         '</td><td>'+cast(@msgindx_systemseek as varchar(40))+
  2589.         '</td><td>'+cast(@msgindx_systemscan as varchar(40))+
  2590.         '</td><td>'+cast(@msgindx_lastsysseek as varchar(40))+
  2591.         '</td><td>'+cast(@msgindx_avgtotalsyscost as varchar(40))+
  2592.         '</td><td>'+cast(@msgindx_avgsysimpact as varchar(40))+
  2593.       '</td><td>'+cast(@msgindx_databaseid as varchar(40))+
  2594.       '</td><td>'+cast(@msgindx_objid as varchar(40))+'</td>'+'</tr>'
  2595. fetch from cu_msgdet into
  2596.         @msgindx_idxgroup_handle ,
  2597.         @msgindx_idx_handle ,
  2598.         @msgindx_improvement_measures,
  2599.         @msgindx_createidxstat,
  2600.         @msgindx_grphandle ,
  2601.         @msgindx_uniqcompiles,
  2602.         @msgindx_userseeks,
  2603.         @msgindx_usescans ,
  2604.         @msgindx_lastuserseek ,
  2605.         @msgindx_lastuserscan ,
  2606.         @msgindx_avgtotalusercost,
  2607.         @msgindx_avguserimpact ,
  2608.         @msgindx_systemseek ,
  2609.         @msgindx_systemscan ,
  2610.         @msgindx_lastsysseek ,
  2611.         @msgindx_avgtotalsyscost ,
  2612.         @msgindx_avgsysimpact ,
  2613.         @msgindx_databaseid ,
  2614.         @msgindx_objid
  2615.  
  2616. end
  2617.  
  2618. close cu_msgdet
  2619. deallocate cu_msgdet
  2620.  print'</table><br/>'
  2621.  
  2622.  
  2623.  
  2624.  print'<br>
  2625. <table style="width: 100%">
  2626.     <tr>
  2627.         <td><strong><span class="auto-style1">SQL Server Missing Indexes by
  2628.         Index Advantage:-</span><br class="auto-style1"></strong>--Above table
  2629.         will give you a list of indexes that the query optimizer would have
  2630.         liked to have had, based on the workload.We can see if there are any
  2631.         tables that jump out with multiple missing indexes.<br>--You may also
  2632.         want to look at the last_user_seek column to see when was the last time
  2633.         the optimizer wanted an index. If it is several hours or days ago, it
  2634.         may have been from an ad-hoc query of maintenance job rather than your
  2635.         normal workload.</td>
  2636.     </tr>
  2637. </table>
  2638. <br/>'
  2639.  
  2640.  /*
  2641.  --Detecting blocking (a more accurate and complete version)
  2642.  */
  2643.  
  2644.  
  2645.  print N'<H3>SQL Server Detected Blocking on Instance:-</H3>'
  2646. print N'<table cellspacing="1" cellpadding="1" border="1">'+
  2647. N'<tr><th><strong>Lock Type</strong></th>'+
  2648. '<th><strong>Database Name</strong></th>'+
  2649. '<th><strong>Blocked Object</strong></th>'+
  2650. '<th><strong>Lock Requested</strong></th>'+
  2651. '<th><strong>Waiter Spid</strong></th>'+
  2652. '<th><strong>Wait Time(in Microsecond)</strong></th>'+
  2653. '<th><strong>Waiter Batch</strong></th>'+
  2654. '<th><strong>Waiter Statement</strong></th>'+
  2655. '<th><strong>Blocker Sid</strong></th>'+
  2656. N'<th><strong>Blocker Statement</strong></th></tr>'
  2657.  
  2658.  
  2659.  
  2660. declare cur_sqlblcoking_detail_cur cursor for
  2661. SELECT t1.resource_type AS 'lock type',db_name(resource_database_id) AS 'database',
  2662. t1.resource_associated_entity_id AS 'blk object',t1.request_mode AS 'lock req', --- lock requested
  2663. t1.request_session_id AS 'waiter sid', t2.wait_duration_ms AS 'wait time',
  2664. (SELECT [text] FROM sys.dm_exec_requests AS r
  2665. CROSS APPLY sys.dm_exec_sql_text(r.sql_handle)
  2666. WHERE r.session_id = t1.request_session_id) AS 'waiter_batch',
  2667. (SELECT substring(qt.text,r.statement_start_offset/2,
  2668. (CASE WHEN r.statement_end_offset = -1
  2669. THEN LEN(CONVERT(nvarchar(max), qt.text)) * 2
  2670. ELSE r.statement_end_offset END - r.statement_start_offset)/2)
  2671. FROM sys.dm_exec_requests AS r
  2672. CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS qt
  2673. WHERE r.session_id = t1.request_session_id) AS 'waiter_stmt',
  2674. t2.blocking_session_id AS 'blocker sid',
  2675. (SELECT [text] FROM sys.sysprocesses AS p
  2676. CROSS APPLY sys.dm_exec_sql_text(p.sql_handle)
  2677. WHERE p.spid = t2.blocking_session_id) AS 'blocker_stmt'
  2678. FROM sys.dm_tran_locks AS t1
  2679. INNER JOIN sys.dm_os_waiting_tasks AS t2
  2680. ON t1.lock_owner_address = t2.resource_address;
  2681.  
  2682.  
  2683. open cur_sqlblcoking_detail_cur
  2684. fetch from cur_sqlblcoking_detail_cur into
  2685. @blocking_lcktype ,
  2686. @blocking_dbname ,
  2687. @blocking_blockerobj ,
  2688. @blocking_lckreque ,
  2689. @blocking_waitersid ,
  2690. @blocking_waitime ,
  2691. @blocking_waitbatch ,
  2692. @blocking_waiterstmt ,
  2693. @blocking_blockersid ,
  2694. @blocking_blocker_stmt
  2695.  
  2696.  
  2697.  
  2698. while @@FETCH_STATUS>=0
  2699.  begin
  2700. print '<tr><td>'+cast(@blocking_lcktype as varchar(100))+
  2701.       '</td><td>'+cast(@blocking_dbname as varchar(40))+
  2702.       '</td><td>'+cast(@blocking_blockerobj as varchar(100))+
  2703.       '</td><td>'+cast(@blocking_lckreque as varchar(100))+
  2704.       '</td><td>'+cast(@blocking_waitersid as varchar(10))+
  2705.       '</td><td>'+cast(@blocking_waitime as varchar(100))+
  2706.       '</td><td>'+cast(@blocking_waitbatch as varchar(200))+
  2707.       '</td><td>'+cast(@blocking_waiterstmt as varchar(1000))+
  2708.       '</td><td>'+cast(@blocking_blockersid as varchar(40))+
  2709.       '</td><td>'+cast(@blocking_blocker_stmt as varchar(1000))+'</td>'+'</tr>'
  2710. fetch from cur_sqlblcoking_detail_cur into
  2711. @blocking_lcktype ,
  2712. @blocking_dbname ,
  2713. @blocking_blockerobj ,
  2714. @blocking_lckreque ,
  2715. @blocking_waitersid ,
  2716. @blocking_waitime ,
  2717. @blocking_waitbatch ,
  2718. @blocking_waiterstmt ,
  2719. @blocking_blockersid ,
  2720. @blocking_blocker_stmt
  2721. end
  2722.  
  2723. close cur_sqlblcoking_detail_cur
  2724. deallocate cur_sqlblcoking_detail_cur
  2725.  
  2726. print'</table><br/>'
  2727.  
  2728.  
  2729.  
  2730. /*
  2731. Analyse the database size growth using backup history.
  2732. */
  2733.  
  2734.  
  2735.  
  2736.  print N'<H3>SQL Server Database Growth in Last Six Month:-</H3>'
  2737. print N'<table cellspacing="1" cellpadding="1" border="1">'+
  2738. N'<tr><th><strong>Database Name</strong></th>'+
  2739. '<th><strong>Year-Month</strong></th>'+
  2740. '<th><strong>MinSize in MB</strong></th>'+
  2741. '<th><strong>MaxSize in MB</strong></th>'+
  2742. '<th><strong>Average Size in MB</strong></th>'+
  2743. N'<th><strong>Growth in MB</strong></th></tr>'
  2744.  
  2745.  
  2746. set nocount on
  2747.  
  2748.  
  2749. SET @endDate = GetDate();  -- Include in the statistic all backups from today
  2750. SET @months = 6;           -- back to the last 6 months.
  2751. WITH HIST AS
  2752.    (SELECT BS.database_name AS DatabaseName
  2753.           ,YEAR(BS.backup_start_date) * 100
  2754.            + MONTH(BS.backup_start_date) AS YearMonth
  2755.           ,CONVERT(numeric(10, 1), MIN(BF.file_size / 1048576.0)) AS MinSizeMB
  2756.           ,CONVERT(numeric(10, 1), MAX(BF.file_size / 1048576.0)) AS MaxSizeMB
  2757.           ,CONVERT(numeric(10, 1), AVG(BF.file_size / 1048576.0)) AS AvgSizeMB
  2758.     FROM msdb.dbo.backupset as BS
  2759.          INNER JOIN
  2760.          msdb.dbo.backupfile AS BF
  2761.              ON BS.backup_set_id = BF.backup_set_id
  2762.     WHERE NOT BS.database_name IN
  2763.               ('master', 'msdb', 'model', 'tempdb')
  2764.           AND BF.file_type = 'D'
  2765.           AND BS.backup_start_date BETWEEN DATEADD(mm, - @months, @endDate) AND @endDate
  2766.     GROUP BY BS.database_name
  2767.             ,YEAR(BS.backup_start_date)
  2768.             ,MONTH(BS.backup_start_date))
  2769. SELECT MAIN.DatabaseName
  2770.       ,MAIN.YearMonth
  2771.       ,MAIN.MinSizeMB
  2772.       ,MAIN.MaxSizeMB
  2773.       ,MAIN.AvgSizeMB
  2774.       ,MAIN.AvgSizeMB
  2775.        - (SELECT TOP 1 SUB.AvgSizeMB
  2776.           FROM HIST AS SUB
  2777.           WHERE SUB.DatabaseName = MAIN.DatabaseName
  2778.                 AND SUB.YearMonth < MAIN.YearMonth
  2779.           ORDER BY SUB.YearMonth DESC) AS GrowthMB into #DBgrwothdata
  2780. FROM HIST AS MAIN
  2781. ORDER BY MAIN.DatabaseName
  2782.         ,MAIN.YearMonth
  2783.  
  2784. --select * from #DBgrwothdata
  2785.  
  2786. declare cur_dbgrowth_info cursor for
  2787. select
  2788. DatabaseName,
  2789. YearMonth,
  2790. MinSizeMB,
  2791. MaxSizeMB,
  2792. AvgSizeMB,
  2793. GrowthMB from #DBgrwothdata
  2794.  
  2795. open cur_dbgrowth_info
  2796.  
  2797. fetch from cur_dbgrowth_info into
  2798. @DBG_Dbname ,
  2799. @DBG_YearMon ,
  2800. @DBG_MinSizeMB ,
  2801. @DBG_MaxSizeMB ,
  2802. @DBG_AVGSizeMB ,
  2803. @DBG_GrowthMB
  2804.  
  2805. while @@FETCH_STATUS>=0
  2806.  begin
  2807. print '<tr><td>'+cast(@DBG_Dbname as varchar(100))+
  2808.       '</td><td>'+cast(@DBG_YearMon as varchar(40))+
  2809.       '</td><td>'+cast(@DBG_MinSizeMB as varchar(100))+
  2810.       '</td><td>'+cast(@DBG_MaxSizeMB as varchar(100))+
  2811.       '</td><td>'+cast(@DBG_AVGSizeMB as varchar(10))+
  2812.       '</td><td>'+IsNull(cast(@DBG_GrowthMB as varchar(100)),'')+'</td>'+'</tr>'
  2813. fetch from cur_dbgrowth_info into
  2814. @DBG_Dbname ,
  2815. @DBG_YearMon ,
  2816. @DBG_MinSizeMB ,
  2817. @DBG_MaxSizeMB ,
  2818. @DBG_AVGSizeMB ,
  2819. @DBG_GrowthMB
  2820. end
  2821. close cur_dbgrowth_info
  2822. deallocate cur_dbgrowth_info
  2823. set nocount on
  2824. IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'#DBgrwothdata') AND type in (N'U'))
  2825. DROP TABLE #DBgrwothdata
  2826. print'</table><br/>'
  2827. print'
  2828. <br>
  2829. <table style="width: 100%">
  2830.     <tr>
  2831.         <td><span class="auto-style1"><strong>SQL Server Database Growth
  2832.         Matrix:-</strong></span><br class="auto-style1">--Above table shows you
  2833.         your user database growth based on hte backup of the database.<br>--This information is very handy when you planing for
  2834.         capacity management.</td>
  2835.     </tr>
  2836. </table>
  2837.  
  2838. <br/>'
  2839.  
  2840.  
  2841.  
  2842. /*
  2843. Memory Configuration
  2844. */
  2845. SELECT @pg_size = low from master..spt_values where number = 1 and type = 'E'
  2846. SELECT @Instancename = LEFT([object_name], (CHARINDEX(':',[object_name]))) FROM sys.dm_os_performance_counters WHERE counter_name = 'Buffer cache hit ratio'
  2847.  
  2848. /*
  2849. --Physical Memory Details on Server along with VAS.
  2850.  
  2851. */
  2852.  
  2853.  
  2854.  print N'<H3>SQL Server Instance Memory Configuration:-</H3>'
  2855. print N'<table cellspacing="1" cellpadding="1" border="1">'+
  2856. N'<tr><th><strong>Physical Mem in MB</strong></th>'+
  2857. '<th><strong>Physical Mem in GB</strong></th>'+
  2858. N'<th><strong>Virtual Mem MB</strong></th></tr>'
  2859.  
  2860. declare cur_phyvasmem_det cursor for
  2861. SELECT physical_memory_kb/1024.0 as [Physical Memory_MB], physical_memory_kb/(1024*1024.0) as [Physical Memory_GB], virtual_memory_kb/1024.0 as [Virtual Memory MB] FROM sys.dm_os_sys_info
  2862. open cur_phyvasmem_det
  2863. fetch from cur_phyvasmem_det into
  2864. @phymem_onsrvinmb ,
  2865. @phymem_onsrvingb ,
  2866. @phymem_onsrvVAS
  2867.  
  2868.  
  2869. while @@FETCH_STATUS>=0
  2870.  begin
  2871. print '<tr><td>'+cast(@phymem_onsrvinmb as varchar(100))+
  2872.       '</td><td>'+cast(@phymem_onsrvingb as varchar(40))+
  2873.       '</td><td>'+cast(@phymem_onsrvVAS as varchar(100))+'</td>'+'</tr>'
  2874. fetch from cur_phyvasmem_det into
  2875. @phymem_onsrvinmb ,
  2876. @phymem_onsrvingb ,
  2877. @phymem_onsrvVAS
  2878. end
  2879. close cur_phyvasmem_det
  2880. deallocate cur_phyvasmem_det
  2881. print'</table><br/>'
  2882.  
  2883. print'<br>
  2884. <table style="width: 100%">
  2885.     <tr>
  2886.         <td><span class="auto-style1"><strong>SQL Server Instance Memory
  2887.         Configuration:-</strong></span><br>--Above table will show you available
  2888.         physical memory in MB on the server and virtual memory available on the
  2889.         server.<br>--It is always good to have overview
  2890.         of how much physical RAM your server have and virtual memory will be
  2891.         always depend upon the 32-bit and 64-bit system.<br>-- For 32-bit system
  2892.         Virtual address space (Virtual Memory) is limited to 2 GB (User Mode
  2893.         Address space and 2 GB( Kernel Mode Address Space).<br>-- While with
  2894.         64-bit system this limitation has been removed. you have almost 8TB
  2895.         virtual address space in 64bit system.</td>
  2896.     </tr>
  2897. </table>
  2898. <br/>'
  2899. /*
  2900. ----Buffer Pool Usage at the Moment
  2901.  
  2902. */
  2903.  
  2904. print N'<H3>SQL Server Instance Buffer Pool Usage:-</H3>'
  2905. print N'<table cellspacing="1" cellpadding="1" border="1">'+
  2906. N'<tr><th><strong>Buffer Pool Commited in MB</strong></th>'+
  2907. '<th><strong>Buffer Pool Target Commited GB</strong></th>'+
  2908. N'<th><strong>Buffer Pool Visible Memory MB</strong></th></tr>'
  2909.  
  2910. declare cur_bpoolmeminfo cursor for
  2911. SELECT (committed_kb*8)/1024.0 as BPool_Committed_MB, (committed_target_kb*8)/1024.0 as BPool_Commit_Tgt_MB,(visible_target_kb*8)/1024.0 as BPool_Visible_MB  FROM sys.dm_os_sys_info
  2912.  
  2913. open cur_bpoolmeminfo
  2914. fetch from cur_bpoolmeminfo into
  2915. @bpoolusg_commitedinmb,
  2916. @bpoolusg_commitedintargetmb ,
  2917. @bpoolusg_visibleinMB
  2918.  
  2919.  
  2920. while @@FETCH_STATUS>=0
  2921.  begin
  2922. print '<tr><td>'+cast(@bpoolusg_commitedinmb as varchar(100))+
  2923.       '</td><td>'+cast(@bpoolusg_commitedintargetmb as varchar(40))+
  2924.       '</td><td>'+cast(@bpoolusg_visibleinMB as varchar(100))+'</td>'+'</tr>'
  2925.  
  2926. fetch from cur_bpoolmeminfo into
  2927. @bpoolusg_commitedinmb,
  2928. @bpoolusg_commitedintargetmb ,
  2929. @bpoolusg_visibleinMB
  2930. end
  2931. close cur_bpoolmeminfo
  2932. deallocate cur_bpoolmeminfo
  2933. print'</table><br/>'
  2934. print'<br>
  2935. <table style="width: 100%">
  2936.     <tr>
  2937.         <td><span class="auto-style1"><strong>SQL Server Instace Buffer Pool
  2938.         Usage:-</strong></span><br><strong>1.Bpool Commited Memory:-</strong>Number
  2939.         of 8-KB buffers in the buffer pool. This amount represents committed
  2940.         physical memory in the buffer pool. Does not include reserved memory in
  2941.         the buffer pool<br><strong>2.Bpool Target Commited:-</strong>Number of
  2942.         8-KB buffers needed by the buffer pool. The target amount is calculated
  2943.         using a variety of inputs such as the current state of the system,
  2944.         including its load, the memory requested by current processes, the
  2945.         amount of memory installed on the computer, and configuration
  2946.         parameters. If the bpool_commit_target is larger than the
  2947.         bpool_committed value, the buffer pool will try to obtain additional
  2948.         memory. If the bpool_commit_target is smaller than the bpool_committed
  2949.         value, the buffer pool will shrink.<br><strong>3.Bpool Visible Memory:-</strong>Number
  2950.         of 8-KB buffers in the buffer pool that are directly accessible in the
  2951.         process virtual address space. When not using the Address Windowing
  2952.         Extensions (AWE), when the buffer pool has obtained its memory target
  2953.         (bpool_committed = bpool_commit_target), the value of bpool_visible
  2954.         equals the value of bpool_committed.<br><br>When using AWE on a 32-bit
  2955.         version of SQL Server, bpool_visible represents the size of the AWE
  2956.         mapping window used to access physical memory allocated by the buffer
  2957.         pool. The size of this mapping window is bound by the process address
  2958.         space and, therefore, the visible amount will be smaller than the
  2959.         committed amount, and can be further reduced by internal components
  2960.         consuming memory for purposes other than database pages. If the value of
  2961.         bpool_visible is too low, you might receive out of memory errors.</td>
  2962.     </tr>
  2963. </table>
  2964. <br/>'
  2965. /*
  2966. Total Memory Consumption by SQL Server from perfmon
  2967. */
  2968. print N'<H3>SQL Server Total Memory Consumption:-</H3>'
  2969. print N'<table cellspacing="1" cellpadding="1" border="1">'+
  2970. N'<tr><th><strong>Memory in KB</strong></th>'+
  2971. '<th><strong>Memory in MB</strong></th>'+
  2972. N'<th><strong>Memory in GB</strong></th></tr>'
  2973.  
  2974. declare cur_sqlmeminfoperf cursor for
  2975. SELECT cntr_value as Mem_KB, cntr_value/1024.0 as Mem_MB, (cntr_value/1048576.0) as Mem_GB FROM sys.dm_os_performance_counters WHERE counter_name = 'Total Server Memory (KB)'
  2976. open cur_sqlmeminfoperf
  2977. fetch from cur_sqlmeminfoperf into
  2978. @totalmemsql_usageinkb,
  2979. @totalmemsql_usageinMB,
  2980. @totalmemsql_usageinGB
  2981.  
  2982. while @@FETCH_STATUS>=0
  2983.  begin
  2984. print '<tr><td>'+cast(@totalmemsql_usageinkb as varchar(100))+
  2985.       '</td><td>'+cast(@totalmemsql_usageinMB as varchar(40))+
  2986.       '</td><td>'+cast(@totalmemsql_usageinGB as varchar(100))+'</td>'+'</tr>'
  2987. fetch from cur_sqlmeminfoperf into
  2988. @totalmemsql_usageinkb,
  2989. @totalmemsql_usageinMB,
  2990. @totalmemsql_usageinGB
  2991. end
  2992. close cur_sqlmeminfoperf
  2993. deallocate cur_sqlmeminfoperf
  2994. print'</table><br/>'
  2995. print '<br>
  2996. <table style="width: 100%">
  2997.     <tr>
  2998.         <td><strong><span class="auto-style1">SQL Server Total Memory
  2999.         Consumption:-</span><br class="auto-style1"></strong>Specifies the
  3000.         amount of memory the server has committed using the memory manage how
  3001.         much memory the cache (buffer cache) is using, which is what you control
  3002.         when you specify max server memory.<br>Note:- This value is only mention
  3003.         for the Buffer cache component of SQL Server memory no other components
  3004.         has been mentioned here so far since this you can control it through Max
  3005.         Server memory setting</td>
  3006.     </tr>
  3007. </table>
  3008. <br/>'
  3009. /*
  3010. Memory Needed for current workload for SQL Server instance
  3011. */
  3012. print N'<H3>Memory Needed by SQL Server Instance:-</H3>'
  3013. print N'<table cellspacing="1" cellpadding="1" border="1">'+
  3014. N'<tr><th><strong>Memory in KB</strong></th>'+
  3015. '<th><strong>Memory in MB</strong></th>'+
  3016. N'<th><strong>Memory in GB</strong></th></tr>'
  3017.  
  3018. declare cur_memneed_sql cursor for
  3019. SELECT cntr_value as Mem_KB, cntr_value/1024.0 as Mem_MB, (cntr_value/1048576.0) as Mem_GB FROM sys.dm_os_performance_counters WHERE counter_name = 'Target Server Memory (KB)'
  3020.  
  3021. open cur_memneed_sql
  3022. fetch from cur_memneed_sql into
  3023. @memneed_curwl_meminkb,
  3024. @memneed_curwl_meminmb,
  3025. @memneed_curwl_meminGB
  3026. while @@FETCH_STATUS>=0
  3027.  begin
  3028. print '<tr><td>'+cast(@memneed_curwl_meminkb as varchar(100))+
  3029.       '</td><td>'+cast(@memneed_curwl_meminmb as varchar(40))+
  3030.       '</td><td>'+cast(@memneed_curwl_meminGB as varchar(100))+'</td>'+'</tr>'
  3031. fetch from cur_memneed_sql into
  3032. @memneed_curwl_meminkb,
  3033. @memneed_curwl_meminmb,
  3034. @memneed_curwl_meminGB
  3035. end
  3036. close cur_memneed_sql
  3037. deallocate cur_memneed_sql
  3038. print'</table><br/>'
  3039. print '<br>
  3040. <table style="width: 100%">
  3041.     <tr>
  3042.         <td>Memory Needed By SQL Server Instance:-<br>--Above table shows value
  3043.         for Max Server memory Setting we have put for SQL Server and in use.</td>
  3044.     </tr>
  3045. </table>
  3046. <br/>'
  3047.  
  3048. /*
  3049. Dynamic Memory usage by SQL Server Connections
  3050. */
  3051. print N'<H3>Dynamic Memory Usage for SQL Server Connections:-</H3>'
  3052. print N'<table cellspacing="1" cellpadding="1" border="1">'+
  3053. N'<tr><th><strong>Memory in KB</strong></th>'+
  3054. '<th><strong>Memory in MB</strong></th>'+
  3055. N'<th><strong>Memory in GB</strong></th></tr>'
  3056. SELECT @pg_size = low from master..spt_values where number = 1 and type = 'E'
  3057. SELECT @Instancename = LEFT([object_name], (CHARINDEX(':',[object_name]))) FROM sys.dm_os_performance_counters WHERE counter_name = 'Buffer cache hit ratio'
  3058.  
  3059. declare cur_sqlconn_memusg cursor for
  3060.     SELECT cntr_value as Mem_KB,
  3061.     cntr_value/1024.0 as Mem_MB,
  3062.      (cntr_value/1048576.0) as Mem_GB
  3063.      FROM sys.dm_os_performance_counters WHERE counter_name = 'Connection Memory (KB)'
  3064. open cur_sqlconn_memusg
  3065. fetch from cur_sqlconn_memusg into
  3066. @memcon_usageinkb ,
  3067. @memcon_usageinmb ,
  3068. @memcon_usageingb
  3069. while @@FETCH_STATUS>=0
  3070.  begin
  3071. print '<tr><td>'+cast(@memcon_usageinkb as varchar(100))+
  3072.       '</td><td>'+cast(@memcon_usageinmb as varchar(100))+
  3073.       '</td><td>'+cast(@memcon_usageingb as varchar(100))+'</td>'+'</tr>'
  3074. fetch from cur_sqlconn_memusg into
  3075. @memcon_usageinkb ,
  3076. @memcon_usageinmb ,
  3077. @memcon_usageingb
  3078. end
  3079. close cur_sqlconn_memusg
  3080. deallocate cur_sqlconn_memusg
  3081. print'</table><br/>'
  3082. print '<br>
  3083. <table style="width: 100%">
  3084.     <tr>
  3085.         <td><strong><span class="auto-style1">Dynamic Memory Usage for SQL
  3086.         Server Connection:-</span><br class="auto-style1"></strong>--Specifies
  3087.         the total amount of dynamic memory the server is using for maintaining
  3088.         connections.</td>
  3089.     </tr>
  3090. </table>'
  3091. /*
  3092. Total Amount of Memory Usage for SQL Server Locks
  3093. */
  3094. print N'<H3>Dynamic Memory Usage for SQL Server Locks:-</H3>'
  3095. print N'<table cellspacing="1" cellpadding="1" border="1">'+
  3096. N'<tr><th><strong>Memory in KB</strong></th>'+
  3097. '<th><strong>Memory in MB</strong></th>'+
  3098. N'<th><strong>Memory in GB</strong></th></tr>'
  3099. SELECT @pg_size = low from master..spt_values where number = 1 and type = 'E'
  3100. SELECT @Instancename = LEFT([object_name], (CHARINDEX(':',[object_name]))) FROM sys.dm_os_performance_counters WHERE counter_name = 'Buffer cache hit ratio'
  3101.  
  3102. declare cur_locksmem_usg cursor for
  3103. SELECT cntr_value as Mem_KB, cntr_value/1024.0 as Mem_MB, (cntr_value/1048576.0) as Mem_GB FROM sys.dm_os_performance_counters WHERE counter_name = 'Lock Memory (KB)'
  3104.  
  3105. open cur_locksmem_usg
  3106. fetch from cur_locksmem_usg into
  3107. @memlock_useinkb ,
  3108. @memlock_useinMb ,
  3109. @memlock_useinGb
  3110.  
  3111. while @@FETCH_STATUS>=0
  3112.  begin
  3113. print '<tr><td>'+cast(@memlock_useinkb as varchar(100))+
  3114.       '</td><td>'+cast(@memlock_useinMb as varchar(40))+
  3115.       '</td><td>'+cast(@memlock_useinGb as varchar(100))+'</td>'+'</tr>'
  3116. fetch from cur_locksmem_usg into
  3117. @memlock_useinkb ,
  3118. @memlock_useinMb ,
  3119. @memlock_useinGb
  3120.  end
  3121.  close cur_locksmem_usg
  3122.  deallocate cur_locksmem_usg
  3123.  print'</table><br/>'
  3124.  print '<br>
  3125. <table style="width: 100%">
  3126.     <tr>
  3127.         <td><span class="auto-style1"><strong>Dynamic Memory Usage for SQL
  3128.         Server Locks:-</strong></span><br>--Specifies the total amount of
  3129.         dynamic memory the server is using for locks.</td>
  3130.     </tr>
  3131. </table>
  3132. <br/>'
  3133.  
  3134.  
  3135. /*
  3136. Total Amount of Memory Usage for Dynamic SQL Server Cache
  3137. */
  3138. print N'<H3>Dynamic Memory Usage for SQL Server Cache:-</H3>'
  3139. print N'<table cellspacing="1" cellpadding="1" border="1">'+
  3140. N'<tr><th><strong>Memory in KB</strong></th>'+
  3141. '<th><strong>Memory in MB</strong></th>'+
  3142. N'<th><strong>Memory in GB</strong></th></tr>'
  3143. SELECT @pg_size = low from master..spt_values where number = 1 and type = 'E'
  3144. SELECT @Instancename = LEFT([object_name], (CHARINDEX(':',[object_name]))) FROM sys.dm_os_performance_counters WHERE counter_name = 'Buffer cache hit ratio'
  3145.  
  3146. declare cur_sqlmemcache_info cursor for
  3147. SELECT cntr_value as Mem_KB, cntr_value/1024.0 as Mem_MB, (cntr_value/1048576.0) as Mem_GB FROM sys.dm_os_performance_counters WHERE counter_name = 'SQL Cache Memory (KB)'
  3148.  
  3149. open cur_sqlmemcache_info
  3150. fetch from cur_sqlmemcache_info into
  3151. @dynsqlcache_useinkb ,
  3152. @dynsqlcache_useinMb ,
  3153. @dynsqlcache_useinGb
  3154.  
  3155. while @@FETCH_STATUS>=0
  3156.  begin
  3157. print '<tr><td>'+cast(@dynsqlcache_useinkb as varchar(100))+
  3158.       '</td><td>'+cast(@dynsqlcache_useinMb as varchar(40))+
  3159.       '</td><td>'+cast(@dynsqlcache_useinGb as varchar(100))+'</td>'+'</tr>'
  3160. fetch from cur_sqlmemcache_info into
  3161. @dynsqlcache_useinkb ,
  3162. @dynsqlcache_useinMb ,
  3163. @dynsqlcache_useinGb
  3164. end
  3165. close cur_sqlmemcache_info
  3166. deallocate cur_sqlmemcache_info
  3167. print'</table><br/>'
  3168. print '<br>
  3169. <table style="width: 100%">
  3170.     <tr>
  3171.         <td>Dynamic Memory SQL Server Cache:-<br>--Specifies the total amount of
  3172.         dynamic memory the server is using for the dynamic SQL cache.</td>
  3173.     </tr>
  3174. </table>
  3175. <br/>'
  3176.  
  3177. /*
  3178. Dynamic Memory Utilization by Query Optimization
  3179. */
  3180. print N'<H3>Dynamic Memory Usage for SQL Server Query Optimization:-</H3>'
  3181. print N'<table cellspacing="1" cellpadding="1" border="1">'+
  3182. N'<tr><th><strong>Memory in KB</strong></th>'+
  3183. '<th><strong>Memory in MB</strong></th>'+
  3184. N'<th><strong>Memory in GB</strong></th></tr>'
  3185.  
  3186. SELECT @pg_size = low from master..spt_values where number = 1 and type = 'E'
  3187. SELECT @Instancename = LEFT([object_name], (CHARINDEX(':',[object_name]))) FROM sys.dm_os_performance_counters WHERE counter_name = 'Buffer cache hit ratio'
  3188.  
  3189. declare cur_quryopti_info cursor for
  3190. SELECT cntr_value as Mem_KB, cntr_value/1024.0 as Mem_MB, (cntr_value/1048576.0) as Mem_GB FROM sys.dm_os_performance_counters WHERE counter_name = 'Optimizer Memory (KB) '
  3191. open cur_quryopti_info
  3192. fetch from cur_quryopti_info into
  3193. @qryopt_useinkb,
  3194. @qryopt_useinMb ,
  3195. @qryopt_useinGb
  3196. while @@FETCH_STATUS>=0
  3197.  begin
  3198. print '<tr><td>'+cast(@qryopt_useinkb as varchar(100))+
  3199.       '</td><td>'+cast(@qryopt_useinMb as varchar(40))+
  3200.       '</td><td>'+cast(@qryopt_useinGb as varchar(100))+'</td>'+'</tr>'
  3201. fetch from cur_quryopti_info into
  3202. @qryopt_useinkb,
  3203. @qryopt_useinMb ,
  3204. @qryopt_useinGb
  3205. end
  3206. close cur_quryopti_info
  3207. deallocate cur_quryopti_info
  3208. print'</table><br/>'
  3209. print '<br>
  3210. <table style="width: 100%">
  3211.     <tr>
  3212.         <td><strong>Memory Usage for SQL Server Query Optimization:-<br>--Specifies
  3213.         the total amount of dynamic memory the server is using for query
  3214.         optimization.</td>
  3215.     </tr>
  3216. </table>
  3217. <br/>'
  3218. /*
  3219. Memory Usage by Hash Sort Index Creation Operation
  3220. */
  3221. print N'<H3>Dynamic Memory Usage for Hash sort Index Creation:-</H3>'
  3222. print N'<table cellspacing="1" cellpadding="1" border="1">'+
  3223. N'<tr><th><strong>Memory in KB</strong></th>'+
  3224. '<th><strong>Memory in MB</strong></th>'+
  3225. N'<th><strong>Memory in GB</strong></th></tr>'
  3226.  
  3227. SELECT @pg_size = low from master..spt_values where number = 1 and type = 'E'
  3228. SELECT @Instancename = LEFT([object_name], (CHARINDEX(':',[object_name]))) FROM sys.dm_os_performance_counters WHERE counter_name = 'Buffer cache hit ratio'
  3229.  
  3230. declare cur_idexsort_memusg cursor for
  3231. SELECT cntr_value as Mem_KB, cntr_value/1024.0 as Mem_MB, (cntr_value/1048576.0) as Mem_GB FROM sys.dm_os_performance_counters WHERE counter_name = 'Granted Workspace Memory (KB) '
  3232.  
  3233. open cur_idexsort_memusg
  3234. fetch from cur_idexsort_memusg into
  3235. @idexsort_userinkb,
  3236. @idexsort_userinMb,
  3237. @idexsort_userinGb
  3238. while @@FETCH_STATUS>=0
  3239.  begin
  3240. print '<tr><td>'+cast(@idexsort_userinkb as varchar(100))+
  3241.       '</td><td>'+cast(@idexsort_userinMb as varchar(40))+
  3242.       '</td><td>'+cast(@idexsort_userinGb as varchar(100))+'</td>'+'</tr>'
  3243. fetch from cur_idexsort_memusg into
  3244. @idexsort_userinkb,
  3245. @idexsort_userinMb,
  3246. @idexsort_userinGb
  3247. end
  3248. close cur_idexsort_memusg
  3249. deallocate cur_idexsort_memusg
  3250. print'</table><br/>'
  3251. print'<br>
  3252. <table style="width: 100%">
  3253.     <tr>
  3254.         <td><span class="auto-style1"><strong>SQL Server memory usage for Hash
  3255.         Sort and Index Creation:-</strong></span><br>--Specifies the total
  3256.         amount of memory currently granted to executing processes, such as hash,
  3257.         sort, bulk copy, and index creation operations.ons.</td>
  3258.     </tr>
  3259. </table>
  3260. <br/>'
  3261. /*
  3262. Dynamic memory consumed by Cursor
  3263. */
  3264. print N'<H3>Dynamic Memory Usage by SQL Cursors:-</H3>'
  3265. print N'<table cellspacing="1" cellpadding="1" border="1">'+
  3266. N'<tr><th><strong>Memory in KB</strong></th>'+
  3267. '<th><strong>Memory in MB</strong></th>'+
  3268. N'<th><strong>Memory in GB</strong></th></tr>'
  3269.  
  3270. SELECT @pg_size = low from master..spt_values where number = 1 and type = 'E'
  3271. SELECT @Instancename = LEFT([object_name], (CHARINDEX(':',[object_name]))) FROM sys.dm_os_performance_counters WHERE counter_name = 'Buffer cache hit ratio'
  3272.  
  3273. declare cur_curmemusginfo cursor for
  3274. SELECT cntr_value as Mem_KB, cntr_value/1024.0 as Mem_MB, (cntr_value/1048576.0) as Mem_GB FROM sys.dm_os_performance_counters WHERE counter_name = 'Cursor memory usage' and instance_name = '_Total'
  3275.  
  3276. open cur_curmemusginfo
  3277. fetch from cur_curmemusginfo into
  3278. @curmem_useinkb ,
  3279. @curmem_useinMb ,
  3280. @curmem_useinGb
  3281. while @@FETCH_STATUS>=0
  3282.  begin
  3283. print '<tr><td>'+cast(@curmem_useinkb as varchar(100))+
  3284.       '</td><td>'+cast(@curmem_useinMb as varchar(40))+
  3285.       '</td><td>'+cast(@curmem_useinGb as varchar(100))+'</td>'+'</tr>'
  3286. fetch from cur_curmemusginfo into
  3287. @curmem_useinkb ,
  3288. @curmem_useinMb ,
  3289. @curmem_useinGb
  3290. end
  3291. close cur_curmemusginfo
  3292. deallocate cur_curmemusginfo
  3293. print'</table><br/>'
  3294. print '<br>
  3295. <table style="width: 100%">
  3296.     <tr>
  3297.         <td>SQL Server Memory Usage by SQL Cursors:-<br>--Memory utilize by SQL
  3298.         Server cursor.</td>
  3299.     </tr>
  3300. </table>
  3301. <br/>'
  3302. /*
  3303. Number of Pages Consumed in buffer pool includes(free,database,stolen)
  3304. */
  3305. print N'<H3>Bufferpool Pages(Includes Free,Datapage,Stolen):-</H3>'
  3306. print N'<table cellspacing="1" cellpadding="1" border="1">'+
  3307. N'<tr><th><strong>No of 8KB Pages</strong></th>'+
  3308. '<th><strong>Pages in KB</strong></th>'+
  3309. N'<th><strong>Pages in MB</strong></th></tr>'
  3310.  
  3311. SELECT @pg_size = low from master..spt_values where number = 1 and type = 'E'
  3312. SELECT @Instancename = LEFT([object_name], (CHARINDEX(':',[object_name]))) FROM sys.dm_os_performance_counters WHERE counter_name = 'Buffer cache hit ratio'
  3313.  
  3314. declare cur_buffpoolpage_info cursor for
  3315. SELECT cntr_value as [8KB_Pages], (cntr_value*@pg_size)/1024.0 as Pages_in_KB, (cntr_value*@pg_size)/1048576.0 as Pages_in_MB FROM sys.dm_os_performance_counters WHERE object_name= @Instancename+'Buffer Manager' and counter_name = 'Total pages'
  3316.  
  3317. open cur_buffpoolpage_info
  3318. fetch from cur_buffpoolpage_info into
  3319. @bpool_page_8kbno,
  3320. @bpool_pages_inkb,
  3321. @bpool_pages_inmb
  3322. while @@FETCH_STATUS>=0
  3323.  begin
  3324. print '<tr><td>'+cast(@bpool_page_8kbno as varchar(100))+
  3325.       '</td><td>'+cast(@bpool_pages_inkb as varchar(40))+
  3326.       '</td><td>'+cast(@bpool_pages_inmb as varchar(100))+'</td>'+'</tr>'
  3327. fetch from cur_buffpoolpage_info into
  3328. @bpool_page_8kbno,
  3329. @bpool_pages_inkb,
  3330. @bpool_pages_inmb
  3331. end
  3332. close cur_buffpoolpage_info
  3333. deallocate cur_buffpoolpage_info
  3334. print'</table><br/>'
  3335. print '<br>
  3336. <table style="width: 100%">
  3337.     <tr>
  3338.         <td><strong><span class="auto-style1">Buffer Pool Usage:-</span><br class="auto-style1">
  3339.         </strong>--Total Number of pages that are included in the buffer pool it
  3340.         includes Data pages Free pages and Stolen pages.</td>
  3341.     </tr>
  3342. </table>
  3343. <br/>'
  3344. /*
  3345. Total Number of Data Pages in Buffer Pool
  3346. */
  3347. print N'<H3>Bufferpool Pages Total Number of DataPages:-</H3>'
  3348. print N'<table cellspacing="1" cellpadding="1" border="1">'+
  3349. N'<tr><th><strong>No of 8KB Pages</strong></th>'+
  3350. '<th><strong>Pages in KB</strong></th>'+
  3351. N'<th><strong>Pages in MB</strong></th></tr>'
  3352.  
  3353. SELECT @pg_size = low from master..spt_values where number = 1 and type = 'E'
  3354. SELECT @Instancename = LEFT([object_name], (CHARINDEX(':',[object_name]))) FROM sys.dm_os_performance_counters WHERE counter_name = 'Buffer cache hit ratio'
  3355.  
  3356. declare cur_bpooldbpage_info cursor for
  3357. SELECT cntr_value as [8KB_Pages], (cntr_value*@pg_size)/1024.0 as Pages_in_KB, (cntr_value*@pg_size)/1048576.0 as Pages_in_MB FROM sys.dm_os_performance_counters WHERE object_name=@Instancename+'Buffer Manager' and counter_name = 'Database pages'
  3358.  
  3359. open cur_bpooldbpage_info
  3360. fetch from cur_bpooldbpage_info into
  3361. @dbpagebpool_page_8kbno,
  3362. @dbpagebpool_page_inkb ,
  3363. @dbpagebpool_page_inmb
  3364. while @@FETCH_STATUS>=0
  3365.  begin
  3366. print '<tr><td>'+cast(@dbpagebpool_page_8kbno as varchar(100))+
  3367.       '</td><td>'+cast(@dbpagebpool_page_inkb as varchar(40))+
  3368.       '</td><td>'+cast(@dbpagebpool_page_inmb as varchar(100))+'</td>'+'</tr>'
  3369. fetch from cur_bpooldbpage_info into
  3370. @dbpagebpool_page_8kbno,
  3371. @dbpagebpool_page_inkb ,
  3372. @dbpagebpool_page_inmb
  3373. end
  3374. close cur_bpooldbpage_info
  3375. deallocate cur_bpooldbpage_info
  3376. print'</table><br/>'
  3377. print'<br>
  3378. <table style="width: 100%">
  3379.     <tr>
  3380.         <td><strong>Bpool Number of Data Pages:-<br></strong>--Number of pages
  3381.         in the buffer pool with database content.</td>
  3382.     </tr>
  3383. </table>
  3384. <br/>'
  3385.  
  3386. /*
  3387. Total Number of Free Pages in Buffer Pool
  3388. */
  3389. print N'<H3>Bufferpool Pages Total Number of FreePages:-</H3>'
  3390. print N'<table cellspacing="1" cellpadding="1" border="1">'+
  3391. N'<tr><th><strong>No of 8KB Pages</strong></th>'+
  3392. '<th><strong>Pages in KB</strong></th>'+
  3393. N'<th><strong>Pages in MB</strong></th></tr>'
  3394.  
  3395. SELECT @pg_size = low from master..spt_values where number = 1 and type = 'E'
  3396. SELECT @Instancename = LEFT([object_name], (CHARINDEX(':',[object_name]))) FROM sys.dm_os_performance_counters WHERE counter_name = 'Buffer cache hit ratio'
  3397.  
  3398. declare cur_bpoolfreepage_info cursor for
  3399. SELECT cntr_value as [8KB_Pages], (cntr_value*@pg_size)/1024.0 as Pages_in_KB, (cntr_value*@pg_size)/1048576.0 as Pages_in_MB FROM sys.dm_os_performance_counters WHERE object_name=@Instancename+'Buffer Manager' and counter_name = 'Free pages'
  3400.  
  3401. open cur_bpoolfreepage_info
  3402. fetch from cur_bpoolfreepage_info into
  3403. @freepagebpool_page_8kbno,
  3404. @freepagebpool_page_inkb,
  3405. @freepagebpool_page_inmb
  3406. while @@FETCH_STATUS>=0
  3407.  begin
  3408. print '<tr><td>'+cast(@freepagebpool_page_8kbno as varchar(100))+
  3409.       '</td><td>'+cast(@freepagebpool_page_inkb as varchar(40))+
  3410.       '</td><td>'+cast(@freepagebpool_page_inmb as varchar(100))+'</td>'+'</tr>'
  3411. fetch from cur_bpoolfreepage_info into
  3412. @freepagebpool_page_8kbno,
  3413. @freepagebpool_page_inkb,
  3414. @freepagebpool_page_inmb
  3415. end
  3416. close cur_bpoolfreepage_info
  3417. deallocate cur_bpoolfreepage_info
  3418. print'</table><br/>'
  3419. print '<br>
  3420. <table style="width: 100%">
  3421.     <tr>
  3422.         <td><span class="auto-style1"><strong>Bpool Total Number of Free Pages:-</strong></span><br>
  3423.         --Number of requests per second that had to wait for a free page.Total
  3424.         number of pages on all free lists.</td>
  3425.     </tr>
  3426. </table>
  3427. <br/>'
  3428. /*
  3429. --Number of reserved pages in the buffer pool
  3430. */
  3431. print N'<H3>Bufferpool Pages Total Number of Reserved Pages:-</H3>'
  3432. print N'<table cellspacing="1" cellpadding="1" border="1">'+
  3433. N'<tr><th><strong>No of 8KB Pages</strong></th>'+
  3434. '<th><strong>Pages in KB</strong></th>'+
  3435. N'<th><strong>Pages in MB</strong></th></tr>'
  3436.  
  3437. SELECT @pg_size = low from master..spt_values where number = 1 and type = 'E'
  3438. SELECT @Instancename = LEFT([object_name], (CHARINDEX(':',[object_name]))) FROM sys.dm_os_performance_counters WHERE counter_name = 'Buffer cache hit ratio'
  3439.  
  3440. declare cur_bpoolresvpage_info cursor for
  3441. SELECT cntr_value as [8KB_Pages], (cntr_value*@pg_size)/1024.0 as Pages_in_KB, (cntr_value*@pg_size)/1048576.0 as Pages_in_MB FROM sys.dm_os_performance_counters WHERE object_name=@Instancename+'Buffer Manager' and counter_name = 'Reserved pages'
  3442. open cur_bpoolresvpage_info
  3443. fetch from cur_bpoolresvpage_info into
  3444. @respagebpool_page_8kbno ,
  3445. @respagebpool_page_inkb ,
  3446. @respagebpool_page_inmb
  3447. while @@FETCH_STATUS>=0
  3448.  begin
  3449. print '<tr><td>'+cast(@respagebpool_page_8kbno as varchar(100))+
  3450.       '</td><td>'+cast(@respagebpool_page_inkb as varchar(40))+
  3451.       '</td><td>'+cast(@respagebpool_page_inmb as varchar(100))+'</td>'+'</tr>'
  3452. fetch from cur_bpoolresvpage_info into
  3453. @respagebpool_page_8kbno ,
  3454. @respagebpool_page_inkb ,
  3455. @respagebpool_page_inmb
  3456. end
  3457. close cur_bpoolresvpage_info
  3458. deallocate cur_bpoolresvpage_info
  3459. print'</table><br/>'
  3460. print '<br>
  3461. <table style="width: 100%">
  3462.     <tr>
  3463.         <td>Bpool Total Number of Reserved Pages:-<br>--Number of buffer pool
  3464.         reserved pages.</td>
  3465.     </tr>
  3466. </table>
  3467. <br/>
  3468. '
  3469. /*
  3470. Number of stolen pages in Bpool
  3471. */
  3472. print N'<H3>Bufferpool Pages Total Number of Stolen Pages:-</H3>'
  3473. print N'<table cellspacing="1" cellpadding="1" border="1">'+
  3474. N'<tr><th><strong>No of 8KB Pages</strong></th>'+
  3475. '<th><strong>Pages in KB</strong></th>'+
  3476. N'<th><strong>Pages in MB</strong></th></tr>'
  3477.  
  3478. SELECT @pg_size = low from master..spt_values where number = 1 and type = 'E'
  3479. SELECT @Instancename = LEFT([object_name], (CHARINDEX(':',[object_name]))) FROM sys.dm_os_performance_counters WHERE counter_name = 'Buffer cache hit ratio'
  3480.  
  3481. declare cur_bpoolstolenpage_info cursor for
  3482. SELECT cntr_value as [8KB_Pages], (cntr_value*@pg_size)/1024.0 as Pages_in_KB, (cntr_value*@pg_size)/1048576.0 as Pages_in_MB FROM sys.dm_os_performance_counters WHERE object_name=@Instancename+'Buffer Manager' and counter_name = 'Stolen pages'
  3483.  
  3484. open cur_bpoolstolenpage_info
  3485. fetch  from cur_bpoolstolenpage_info into
  3486. @stolenpbpool_page_8kbno ,
  3487. @stolenpbpool_page_inkb ,
  3488. @stolenpbpool_page_inmb
  3489. while @@FETCH_STATUS>=0
  3490.  begin
  3491. print '<tr><td>'+cast(@stolenpbpool_page_8kbno as varchar(100))+
  3492.       '</td><td>'+cast(@stolenpbpool_page_inkb as varchar(40))+
  3493.       '</td><td>'+cast(@stolenpbpool_page_inmb as varchar(100))+'</td>'+'</tr>'
  3494. fetch  from cur_bpoolstolenpage_info into
  3495. @stolenpbpool_page_8kbno ,
  3496. @stolenpbpool_page_inkb ,
  3497. @stolenpbpool_page_inmb
  3498.  
  3499. end
  3500. close cur_bpoolstolenpage_info
  3501. deallocate cur_bpoolstolenpage_info
  3502. print'</table><br/>'
  3503. print'<br>
  3504. <table style="width: 100%">
  3505.     <tr>
  3506.         <td><strong><span class="auto-style1">Bpool Total number of Stolen
  3507.         Pages:-</span><br class="auto-style1"></strong>The size of SQL Server
  3508.         database page is 8KB. Buffer Pool is a cache of data pages. Consequently
  3509.         Buffer Pool operates on pages of 8KB in size. It commits and decommits
  3510.         memory blocks of 8KB granularity only. If external components decide to
  3511.         borrow memory out of Buffer Pool they can only get blocks of 8KB in
  3512.         size. These blocks are not continues in memeory. Interesting, right? It
  3513.         means that Buffer Pool can be used as underneath memory manager forSQL
  3514.         Server components as long as they allocate buffers of 8KB. (Sometimes
  3515.         pages allocated from BP are referred as stolen)<br></td>
  3516.     </tr>
  3517. </table>
  3518. <br/>'
  3519. /*
  3520. Number plan cache pages in Buffer pool
  3521. */
  3522. print N'<H3>Bufferpool Pages Total Number of Plan Cache Pages:-</H3>'
  3523. print N'<table cellspacing="1" cellpadding="1" border="1">'+
  3524. N'<tr><th><strong>No of 8KB Pages</strong></th>'+
  3525. '<th><strong>Pages in KB</strong></th>'+
  3526. N'<th><strong>Pages in MB</strong></th></tr>'
  3527.  
  3528. SELECT @pg_size = low from master..spt_values where number = 1 and type = 'E'
  3529. SELECT @Instancename = LEFT([object_name], (CHARINDEX(':',[object_name]))) FROM sys.dm_os_performance_counters WHERE counter_name = 'Buffer cache hit ratio'
  3530.  
  3531. declare cur_bpoolplancache_info cursor for
  3532. SELECT cntr_value as [8KB_Pages], (cntr_value*@pg_size)/1024.0 as Pages_in_KB, (cntr_value*@pg_size)/1048576.0 as Pages_in_MB FROM sys.dm_os_performance_counters WHERE object_name=@Instancename+'Plan Cache' and counter_name = 'Cache Pages'  and instance_name = '_Total'
  3533.  
  3534. open cur_bpoolplancache_info
  3535. fetch from cur_bpoolplancache_info into
  3536. @plancachebpool_page_8kbno ,
  3537. @plancachebpool_page_inkb ,
  3538. @plancachebpool_page_inmb
  3539. while @@FETCH_STATUS>=0
  3540.  begin
  3541. print '<tr><td>'+cast(@plancachebpool_page_8kbno as varchar(100))+
  3542.       '</td><td>'+cast(@plancachebpool_page_inkb as varchar(40))+
  3543.       '</td><td>'+cast(@plancachebpool_page_inmb as varchar(100))+'</td>'+'</tr>'
  3544. fetch from cur_bpoolplancache_info into
  3545. @plancachebpool_page_8kbno ,
  3546. @plancachebpool_page_inkb ,
  3547. @plancachebpool_page_inmb
  3548. end
  3549. close cur_bpoolplancache_info
  3550. deallocate cur_bpoolplancache_info
  3551. print'</table><br/>'
  3552. print'<br>
  3553. <table style="width: 100%">
  3554.     <tr>
  3555.         <td><span class="auto-style1"><strong>Bpool plan cache pages:-</strong></span><br>
  3556.         --This metric counts the number of 8-kilobyte (KB) pages used by plan
  3557.         cache objects, which indicates the plan cache size of an instance. This
  3558.         counter is very similar to the SQL Server: memory manager: SQL cache
  3559.         memory, but instead of providing the number of 8-kilobyte pages that
  3560.         make up the plan cache, it provides the total amount of memory, in
  3561.         kilobytes, used by the plan cache.</td>
  3562.     </tr>
  3563. </table>
  3564. <br/>'
  3565. /*
  3566. --SQL Server Binary Module Information
  3567.  
  3568. */
  3569.  
  3570. print N'<H3>SQL Server Binary Module Informatio:-</H3>'
  3571. print N'<table cellspacing="1" cellpadding="1" border="1">'+
  3572. N'<tr><th><strong>Name and Path of File</strong></th>'+
  3573. '<th><strong>File Version</strong></th>'+
  3574. '<th><strong>Product Version</strong></th>'+
  3575. '<th><strong>Description of Module</strong></th>'+
  3576. N'<th><strong>Module Size KB</strong></th></tr>'
  3577.  
  3578. declare cur_sqlbinmodule_info cursor for
  3579. SELECT olm.[name], olm.[file_version], olm.[product_version], olm.[description], SUM(ova.[region_size_in_bytes])/1024 [Module Size in KB]
  3580. FROM sys.dm_os_virtual_address_dump ova
  3581. INNER JOIN sys.dm_os_loaded_modules olm ON olm.base_address = ova.region_allocation_base_address
  3582. GROUP BY olm.[name],olm.[file_version], olm.[product_version], olm.[description],olm.[base_address]
  3583. ORDER BY [Module Size in KB] DESC
  3584.  
  3585. open cur_sqlbinmodule_info
  3586. fetch from cur_sqlbinmodule_info into
  3587. @DllFilePath,
  3588. @FileVer,
  3589. @Productver,
  3590. @Bin_Descrip,
  3591. @Modulesize_inkb
  3592. while @@FETCH_STATUS>=0
  3593.  begin
  3594. print '<tr><td>'+cast(@DllFilePath as varchar(2000))+
  3595.       '</td><td>'+cast(@FileVer as varchar(400))+
  3596.       '</td><td>'+cast(@Productver as varchar(400))+
  3597.       '</td><td>'+cast(@Bin_Descrip as varchar(5000))+
  3598.       '</td><td>'+cast(@Modulesize_inkb as varchar(100))+'</td>'+'</tr>'
  3599. fetch from cur_sqlbinmodule_info into
  3600. @DllFilePath,
  3601. @FileVer,
  3602. @Productver,
  3603. @Bin_Descrip,
  3604. @Modulesize_inkb
  3605. end
  3606. close cur_sqlbinmodule_info
  3607. deallocate cur_sqlbinmodule_info
  3608. print'</table><br/>'
  3609. print'<br>
  3610. <table style="width: 100%">
  3611.     <tr>
  3612.         <td><strong><span class="auto-style1">SQL Server Binary Information:-</span><br class="auto-style1">
  3613.         --</strong>The above table contains information about SQL Server binary
  3614.         information loaded inside in SQL Server OS.</td>
  3615.     </tr>
  3616. </table>
  3617. <br/>'
  3618.  
  3619. /*
  3620. Version Store Information
  3621. */
  3622.  
  3623. print N'<H3>SQL Server Version Store Informatio:-</H3>'
  3624. print N'<table cellspacing="1" cellpadding="1" border="1">'+
  3625. N'<tr><th><strong>Version Store Pages Used</strong></th>'+
  3626. N'<th><strong>Version stored space in MB</strong></th></tr>'
  3627.  
  3628. declare cur_versionstoreinfo cursor for
  3629. SELECT SUM(version_store_reserved_page_count) AS [version store pages used],
  3630. (SUM(version_store_reserved_page_count)*1.0/128) AS [version store space in MB]FROM sys.dm_db_file_space_usage
  3631.  
  3632. open  cur_versionstoreinfo
  3633. fetch from cur_versionstoreinfo into
  3634. @verstorepage_used,
  3635. @verstorepage_spaceinMB
  3636. while @@FETCH_STATUS>=0
  3637.  begin
  3638. print '<tr><td>'+cast(@verstorepage_used as varchar(200))+
  3639.       '</td><td>'+cast(@verstorepage_spaceinMB as varchar(100))+'</td>'+'</tr>'
  3640. fetch from cur_versionstoreinfo into
  3641. @verstorepage_used,
  3642. @verstorepage_spaceinMB
  3643. end
  3644. close cur_versionstoreinfo
  3645. deallocate cur_versionstoreinfo
  3646. print'</table><br/>'
  3647. print'<br>
  3648. <table style="width: 100%">
  3649.     <tr>
  3650.         <td>SQL Server Version Store Information:-<br>-- Version store is
  3651.         feature available in SQL Server with Snap shot isolation level.But it
  3652.         has contention on the TEMPDB.<br>-- We have to check if any database is
  3653.         having snapshot isolation level on.</td>
  3654.     </tr>
  3655. </table>
  3656. <br/>'
  3657. /*
  3658. TempDB pages information for the storaage
  3659. */
  3660.  
  3661. print N'<H3>SQL Server Version Store Informatio:-</H3>'
  3662. print N'<table cellspacing="1" cellpadding="1" border="1">'+
  3663. N'<tr><th><strong>User Object Pages MB</strong></th>'+
  3664. N'<th><strong>Internal Object Pages MB</strong></th>'+
  3665. N'<th><strong>Version Store Pages MB</strong></th>'+
  3666. N'<th><strong>Total in Use Pages MB</strong></th>'+
  3667. N'<th><strong>Total Free Pages MB</strong></th></tr>'
  3668.  
  3669. Declare cur_tempdbfileusg_info cursor for
  3670. SELECT (SUM(user_object_reserved_page_count)*8)/1024 AS user_object_pages_mb,
  3671. (SUM(internal_object_reserved_page_count)*8)/1024 AS internal_object_pages_mb,
  3672. (SUM(version_store_reserved_page_count)*8)/1024 AS version_store_pages_mb,
  3673. total_in_use_pages_mb = (SUM(user_object_reserved_page_count)+ SUM(internal_object_reserved_page_count)+ SUM(version_store_reserved_page_count)*8)/1024,
  3674. (SUM(unallocated_extent_page_count)*8)/1024 AS total_free_pages_mb
  3675. FROM sys.dm_db_file_space_usage ;
  3676.  
  3677. open cur_tempdbfileusg_info
  3678. fetch from cur_tempdbfileusg_info into
  3679. @tempdb_user_obj_pages_inMB,
  3680. @tempdb_internal_obj_pages_inMB,
  3681. @tempdb_versionstore_obj_pages_inMB,
  3682. @tempdb_total_pages_use_inMB ,
  3683. @tempdb_total_pages_free_inMB
  3684. while @@FETCH_STATUS>=0
  3685.  begin
  3686. print '<tr><td>'+cast(@tempdb_user_obj_pages_inMB as varchar(20))+
  3687.       '</td><td>'+cast(@tempdb_internal_obj_pages_inMB as varchar(20))+
  3688.       '</td><td>'+cast(@tempdb_versionstore_obj_pages_inMB as varchar(50))+
  3689.      '</td><td>'+cast(@tempdb_total_pages_use_inMB as varchar(50))+
  3690.       '</td><td>'+cast(@tempdb_total_pages_free_inMB as varchar(50))+'</td>'+'</tr>'
  3691. fetch from cur_tempdbfileusg_info into
  3692. @tempdb_user_obj_pages_inMB,
  3693. @tempdb_internal_obj_pages_inMB,
  3694. @tempdb_versionstore_obj_pages_inMB,
  3695. @tempdb_total_pages_use_inMB ,
  3696. @tempdb_total_pages_free_inMB
  3697. end
  3698.  
  3699. close cur_tempdbfileusg_info
  3700. deallocate cur_tempdbfileusg_info
  3701. print'</table><br/>'
  3702.  
  3703.  
  3704. /*
  3705. TempDb usage by session
  3706. */
  3707. print N'<H3>SQL Server Tempdb Usaage by Session:-</H3>'
  3708. print N'<table cellspacing="1" cellpadding="1" border="1">'+
  3709. N'<tr><th><strong>Session ID</strong></th>'+
  3710. N'<th><strong>Request ID</strong></th>'+
  3711. N'<th><strong>Execution Context ID</strong></th>'+
  3712. N'<th><strong>Database ID</strong></th>'+
  3713. N'<th><strong>User Object Page Allocation Count</strong></th>'+
  3714. N'<th><strong>User Object Page Deallocation Count</strong></th>'+
  3715. N'<th><strong>Internal Object Page Allocation Count</strong></th>'+
  3716. N'<th><strong>Internal Object Page Deallocation Count</strong></th></tr>'
  3717.  
  3718. declare cur_tempdbsessinfo_usg cursor for
  3719. SELECT TOP 10
  3720. *
  3721. FROM sys.dm_db_task_space_usage
  3722. WHERE session_id > 50
  3723. ORDER BY user_objects_alloc_page_count + internal_objects_alloc_page_count DESC ;
  3724.  
  3725. open cur_tempdbsessinfo_usg
  3726. fetch from cur_tempdbsessinfo_usg into
  3727. @tempdbsession_sid,
  3728. @tempdbsession_requ_sid,
  3729. @tempdbsession_execontext_sid,
  3730. @tempdbsession_dbid ,
  3731. @tempdbsession_usrobjallocpage_count,
  3732. @tempdbsession_usrobjdeallocpage_count,
  3733. @tempdbsession_internalallocpage_count,
  3734. @tempdbsession_internaldeallocpage_count
  3735.  
  3736. while @@FETCH_STATUS>=0
  3737.  begin
  3738. print '<tr><td>'+cast(@tempdbsession_sid as varchar(20))+
  3739.       '</td><td>'+cast(@tempdbsession_requ_sid as varchar(20))+
  3740.       '</td><td>'+cast(@tempdbsession_execontext_sid as varchar(20))+
  3741.     '</td><td>'+cast(@tempdbsession_dbid as varchar(20))+
  3742.     '</td><td>'+cast(@tempdbsession_usrobjallocpage_count as varchar(20))+
  3743.      '</td><td>'+cast(@tempdbsession_usrobjdeallocpage_count as varchar(50))+
  3744.      '</td><td>'+cast(@tempdbsession_internalallocpage_count as varchar(50))+
  3745.       '</td><td>'+cast(@tempdbsession_internaldeallocpage_count as varchar(50))+'</td>'+'</tr>'
  3746. fetch from cur_tempdbsessinfo_usg into
  3747. @tempdbsession_sid,
  3748. @tempdbsession_requ_sid,
  3749. @tempdbsession_execontext_sid,
  3750. @tempdbsession_dbid ,
  3751. @tempdbsession_usrobjallocpage_count,
  3752. @tempdbsession_usrobjdeallocpage_count,
  3753. @tempdbsession_internalallocpage_count,
  3754. @tempdbsession_internaldeallocpage_count
  3755. end
  3756.  
  3757. close cur_tempdbsessinfo_usg
  3758. deallocate cur_tempdbsessinfo_usg
  3759. print'</table><br/>'
  3760.  
  3761. /*
  3762. Top 10 Session in SQL by what they are doing
  3763.  
  3764. */
  3765.  
  3766. print N'<H3>SQL Server Top Sessions:-</H3>'
  3767. print N'<table cellspacing="1" cellpadding="1" border="1">'+
  3768. N'<tr><th><strong>Session ID</strong></th>'+
  3769. N'<th><strong>Login Time</strong></th>'+
  3770. N'<th><strong>Host Name</strong></th>'+
  3771. N'<th><strong>Program Name</strong></th>'+
  3772. N'<th><strong>CPU Time</strong></th>'+
  3773. N'<th><strong>Memory Usage in KB</strong></th>'+
  3774. N'<th><strong>Total Scheduled Time in MS</strong></th>'+
  3775. N'<th><strong>Total Elapsed Time in MS</strong></th>'+
  3776. N'<th><strong>Last Request End Time</strong></th>'+
  3777. N'<th><strong>Number of Reads</strong></th>'+
  3778. N'<th><strong>Number of Writes</strong></th>'+
  3779. N'<th><strong>Number of Connection Count</strong></th></tr>'
  3780.  
  3781. declare cur_topsess_activity cursor for
  3782.  
  3783. select top 10 s.session_id
  3784. , s.login_time
  3785. , s.host_name
  3786. , s.program_name
  3787. , s.cpu_time as cpu_time
  3788. , s.memory_usage * 8 as memory_usage_in_KB
  3789. , s.total_scheduled_time as total_scheduled_time
  3790. , s.total_elapsed_time as total_elapsed_time
  3791. , s.last_request_end_time
  3792. , s.reads
  3793. , s.writes
  3794. , count(c.connection_id) as conn_count
  3795. from sys.dm_exec_sessions s
  3796. left outer join sys.dm_exec_connections c on ( s.session_id = c.session_id )
  3797. left outer join sys.dm_exec_requests r on ( r.session_id = c.session_id )
  3798. where (s.is_user_process= 1)
  3799. group by s.session_id, s.login_time, s.host_name, s.cpu_time, s.memory_usage,
  3800. s.total_scheduled_time, s.total_elapsed_time, s.last_request_end_time, s.reads,
  3801. s.writes, s.program_name
  3802. order by s.memory_usage desc
  3803.  
  3804. open cur_topsess_activity
  3805. fetch from cur_topsess_activity into
  3806. @sessionact_sid ,
  3807. @sessionact_logintime ,
  3808. @sessionact_hostname,
  3809. @sessionact_programname,
  3810. @sessionact_cputime ,
  3811. @sessionact_memusginkb ,
  3812. @sessionact_totalschetime ,
  3813. @sessionact_totalelsapsedtime ,
  3814. @sessionact_lastrequestendtime ,
  3815. @sessionact_reads,
  3816. @sessionact_write ,
  3817. @sessionact_conncount
  3818.  
  3819. while @@FETCH_STATUS>=0
  3820.  begin
  3821. print       '<tr><td>'+cast(@sessionact_sid as varchar(20))+
  3822.             '</td><td>'+cast(@sessionact_logintime as varchar(1000))+
  3823.             '</td><td>'+cast(@sessionact_hostname as varchar(50))+
  3824.             '</td><td>'+cast(@sessionact_programname as varchar(520))+
  3825.             '</td><td>'+cast(@sessionact_cputime as varchar(20))+
  3826.              '</td><td>'+cast(@sessionact_memusginkb as varchar(50))+
  3827.              '</td><td>'+cast(@sessionact_totalschetime as varchar(50))+
  3828.              '</td><td>'+cast(@sessionact_totalelsapsedtime as varchar(50))+
  3829.              '</td><td>'+cast(@sessionact_lastrequestendtime as varchar(50))+
  3830.              '</td><td>'+cast(@sessionact_reads as varchar(50))+
  3831.              '</td><td>'+cast(@sessionact_write as varchar(50))+
  3832.              '</td><td>'+cast(@sessionact_conncount as varchar(50))+'</td>'+'</tr>'
  3833.  
  3834.  
  3835. fetch from cur_topsess_activity into
  3836. @sessionact_sid ,
  3837. @sessionact_logintime ,
  3838. @sessionact_hostname,
  3839. @sessionact_programname,
  3840. @sessionact_cputime ,
  3841. @sessionact_memusginkb ,
  3842. @sessionact_totalschetime ,
  3843. @sessionact_totalelsapsedtime ,
  3844. @sessionact_lastrequestendtime ,
  3845. @sessionact_reads,
  3846. @sessionact_write ,
  3847. @sessionact_conncount
  3848. end
  3849.  
  3850. close cur_topsess_activity
  3851. deallocate cur_topsess_activity
  3852. print'</table><br/>'
  3853.  
  3854. print N'<H3>SQL Server Top Activity:-</H3>'
  3855. print N'<table cellspacing="1" cellpadding="1" border="1">'+
  3856. N'<tr><th><strong>Session ID</strong></th>'+
  3857. N'<th><strong>Last Worker Time</strong></th>'+
  3858. N'<th><strong>Last Physical Read</strong></th>'+
  3859. N'<th><strong>Total Physical Read</strong></th>'+
  3860. N'<th><strong>Total Logical Read</strong></th>'+
  3861. N'<th><strong>Last Logical Read</strong></th>'+
  3862. N'<th><strong>Current Wait Type</strong></th>'+
  3863. N'<th><strong>Last Wait Type</strong></th>'+
  3864. N'<th><strong>Wait Resource Type</strong></th>'+
  3865. N'<th><strong>Wait Time</strong></th>'+
  3866. N'<th><strong>Open Transaction Count</strong></th>'+
  3867. N'<th><strong>Row Count</strong></th>'+
  3868. N'<th><strong>Grant Memory in kB</strong></th>'+
  3869. N'<th><strong>SQL Text</strong></th>'+'</tr>'
  3870.  
  3871. declare cur_sqlact_info cursor
  3872. for
  3873. SELECT
  3874. Rqst.session_id as SPID,
  3875. Qstat.last_worker_time,
  3876. Qstat.last_physical_reads,
  3877. Qstat.total_physical_reads,
  3878. Qstat.total_logical_writes,
  3879. Qstat.last_logical_reads,
  3880. Rqst.wait_type as CurrentWait,
  3881. Rqst.last_wait_type,
  3882. Rqst.wait_resource,
  3883. Rqst.wait_time,
  3884. Rqst.open_transaction_count,
  3885. Rqst.row_count,
  3886. Rqst.granted_query_memory,
  3887. tSQLCall.text as SqlText
  3888. FROM sys.dm_exec_query_stats Qstat
  3889. JOIN sys.dm_exec_requests Rqst ON
  3890. Qstat.plan_handle = Rqst.plan_handle AND Qstat.sql_handle = Rqst.sql_handle
  3891. CROSS APPLY sys.dm_exec_sql_text (Rqst.sql_handle) tSQLCall
  3892.  
  3893. open cur_sqlact_info
  3894. fetch from cur_sqlact_info into
  3895. @otran_spid,
  3896. @otran_lasworkertime ,
  3897. @otran_lastphysicalread ,
  3898. @otran_totalphysicalread ,
  3899. @otran_totallogicalwrites,
  3900. @otran_lastlogicalreads ,
  3901. @otran_currentwait ,
  3902. @otran_lastwaittype,
  3903. @otran_watiresource,
  3904. @otran_waittime ,
  3905. @otran_opentrancount ,
  3906. @otran_rowcount ,
  3907. @otran_granterqmem ,
  3908. @otran_sqltect
  3909.  
  3910. while @@FETCH_STATUS>=0
  3911.  begin
  3912. print       '<tr><td>'+cast(@otran_spid as varchar(50))+
  3913.             '</td><td>'+cast(@otran_lasworkertime as varchar(200))+
  3914.             '</td><td>'+cast(@otran_lastphysicalread as varchar(50))+
  3915.             '</td><td>'+cast(@otran_totalphysicalread as varchar(100))+
  3916.             '</td><td>'+cast(@otran_totallogicalwrites as varchar(50))+
  3917.              '</td><td>'+cast(@otran_lastlogicalreads as varchar(50))+
  3918.              '</td><td>'+cast(@otran_currentwait as varchar(200))+
  3919.              '</td><td>'+cast(@otran_lastwaittype as varchar(200))+
  3920.              '</td><td>'+cast(@otran_watiresource as varchar(100))+
  3921.              '</td><td>'+cast(@otran_waittime as varchar(50))+
  3922.              '</td><td>'+cast(@otran_opentrancount as varchar(50))+
  3923.              '</td><td>'+cast(@otran_waittime as varchar(50))+
  3924.              '</td><td>'+cast(@otran_rowcount as varchar(50))+
  3925.              '</td><td>'+cast(@otran_sqltect as varchar(1000))+'</td>'+'</tr>'
  3926.  
  3927.  
  3928. fetch from cur_sqlact_info into
  3929. @otran_spid,
  3930. @otran_lasworkertime ,
  3931. @otran_lastphysicalread ,
  3932. @otran_totalphysicalread ,
  3933. @otran_totallogicalwrites,
  3934. @otran_lastlogicalreads ,
  3935. @otran_currentwait ,
  3936. @otran_lastwaittype,
  3937. @otran_watiresource,
  3938. @otran_waittime ,
  3939. @otran_opentrancount ,
  3940. @otran_rowcount ,
  3941. @otran_granterqmem ,
  3942. @otran_sqltect
  3943.  
  3944. end
  3945.  
  3946. close cur_sqlact_info
  3947. deallocate cur_sqlact_info
  3948. print'</table><br/>'
  3949.  
  3950.  
  3951. /*
  3952. Message From Nirav Joshi
  3953. */
  3954. print'<table style="width: 100%">
  3955.     <tr>
  3956.         <td><strong>Thanks for using this reporting solution for SQL Server Base
  3957.         Line Performance Report.<br>This tool is developed by Nirav Joshi we
  3958.         have taken queries from Glenn Berry''s SQL Server Performance.<br>Thanks
  3959.         Glenn for sharing those queries.<br>Looking forward to use this queries
  3960.         in your day to day SQL Server performance trouble shooting.<br>We will
  3961.         be keep updating this script with new Queries and more automation
  3962.         detail.<br>We always look forward for your feedback and your suggestion.<br>
  3963.         You can download this script from <br></strong>
  3964.         <a href="Download%20Scripts%20for%20SQL%20Server%20Performance%20BaseLine%20Report">
  3965.         <strong>
  3966.         http://niravjoshi05.wordpress.com/2012/12/31/sql-server-200520082008r22012-instance-performance-data-capture-scripts</strong></a><strong><br>
  3967.         </strong><a href="http://www.SkyNicIndia.com"><strong>SkyNicIndia.com</strong></a><br>
  3968.         </td>
  3969.     </tr>
  3970. </table>'
  3971. print '</HTML>'
  3972.  
  3973.  
  3974.  
  3975. GO
  3976.  
  3977.  
  3978.  
  3979. USE [master]
  3980. GO
  3981. if exists(select 1 from sys.sysobjects where name=N'SP_InstanceBaselinePerfReport' and type=N'P')
  3982. begin
  3983. Drop procedure [dbo].[SP_InstanceBaselinePerfReport]
  3984. end
  3985.  
  3986. /****** Object:  StoredProcedure [dbo].[SP_InstanceBaselinePerfReport]
  3987. Script Date: 1/17/2013 10:28:51 PM
  3988. Created By Nirav Joshi
  3989. Subject:This script will create folder under the master database location as PerformanceBaseline and then it will also create HTML file in this folder
  3990. using the server name and created datetime stamp.
  3991.  
  3992. Please let me know your feedback about the script any suggestion comment are most welcome
  3993. Please drop me line at nirav.j05@gmail.com
  3994.  
  3995.  
  3996. ******/
  3997. SET ANSI_NULLS ON
  3998. GO
  3999.  
  4000. SET QUOTED_IDENTIFIER ON
  4001. GO
  4002. CREATE proc [dbo].[SP_InstanceBaselinePerfReport]
  4003.  
  4004. as
  4005. declare @sql varchar(8000)
  4006. declare @sql2 varchar(8000)
  4007. declare @path varchar(4000)
  4008. declare @foldername varchar(200)
  4009. declare @command varchar(4000)
  4010. declare @datefile varchar(200)
  4011. declare @srvname varchar(200)
  4012. declare @ftype varchar(10)
  4013. declare @finalfile varchar(2000)
  4014. declare @fret int
  4015. declare @repret int
  4016. declare @value int
  4017. DECLARE @fileEx int
  4018.  
  4019. declare cur_spvalue cursor for
  4020. SELECT cast(value_in_use as int)
  4021. FROM sys.configurations WITH (NOLOCK) where name='xp_cmdshell'
  4022. ORDER BY name OPTION (RECOMPILE);
  4023.  
  4024. open cur_spvalue
  4025. fetch from cur_spvalue into
  4026. @value
  4027. while @@fetch_status>=0
  4028. begin
  4029. if @value<>1
  4030. begin
  4031. exec sp_configure 'xp_cmdshell',1
  4032. reconfigure with override
  4033. end
  4034. fetch from cur_spvalue into
  4035. @value
  4036. end
  4037.  
  4038. close cur_spvalue
  4039. deallocate cur_spvalue
  4040.  
  4041. set @foldername ='PerformanceBaseLine'
  4042. set @path=(SELECT SUBSTRING(physical_name, 1, CHARINDEX(N'\master.mdf', LOWER(physical_name)) - 1)
  4043.                   FROM master.sys.master_files
  4044.                   WHERE database_id = 1 AND file_id = 1)
  4045. set @path=@path+@foldername
  4046. --select @path
  4047.  
  4048. create table #tempdir
  4049. (
  4050. File_Exists int,
  4051. File_is_a_Directory int,
  4052. Parent_Directory_Exists int
  4053. )
  4054. insert into #tempdir(File_Exists,File_is_a_Directory,Parent_Directory_Exists)
  4055. EXEC master..xp_fileexist @path
  4056. select @fileEx = (select File_is_a_Directory from #tempdir)
  4057.  
  4058.  
  4059. Drop table #tempdir
  4060.  
  4061. if @fileEx = 0
  4062. begin
  4063. set @command='mkdir "'+@path+'"'
  4064. --select @command
  4065. set nocount on
  4066.  
  4067. exec @fret= master.dbo.xp_cmdshell @command,no_output
  4068. print @fret
  4069. if @fret <> 0
  4070.     begin
  4071.         print'#############################################################################################################################'
  4072.         print @path
  4073.         print'#############################################################################################################################'
  4074.         print'#############################################################################################################################'
  4075.         print 'Folder is not able to create on the ' +@path+ ' please validate security settings for this folder for SQL Server service account or folder is already exisit.'
  4076.         print'#############################################################################################################################'
  4077.         return 1
  4078.     end
  4079.    
  4080. else
  4081.     begin
  4082.         print'#############################################################################################################################'
  4083.         print 'Folder is created now generating report'
  4084.         print'#############################################################################################################################'
  4085.        
  4086.     end
  4087. end
  4088.  
  4089. set @datefile = GETDATE()
  4090. set @datefile=(select convert(datetime,@datefile,126))
  4091. set @datefile=Replace(@datefile, ' ', '')
  4092. set @datefile=REPLACE(@datefile,':','')
  4093. --print @datefile
  4094.  
  4095. set @srvname=(select @@SERVERNAME)
  4096. set @srvname=REPLACE(@srvname,'\','')
  4097. set @path=@path+'\'
  4098. set @ftype='.html'
  4099. set @finalfile=(@path+@srvname+@datefile+@ftype)
  4100. --print @finalfile
  4101.  
  4102. select @sql='sqlcmd -E -Q "exec master.[dbo].[InstanceAnalysis_PerformanceBaseLine]" -o "'+@path+@srvname+@datefile+@ftype+'" -S'+ @@SERVERNAME
  4103. --print @sql
  4104. exec @repret=master..xp_cmdshell @sql,no_output
  4105. if @repret <>0
  4106.     begin
  4107.         print'#############################################################################################################################'
  4108.         print 'Report creating has failed there is something wrong with report.'
  4109.         print'#############################################################################################################################'
  4110.         return 1
  4111.     end
  4112. else
  4113.     begin
  4114.         print'#############################################################################################################################'
  4115.         print 'Report is Created fine please check report at this location ' +@finalfile+ '  please validate it'
  4116.         print'#############################################################################################################################'
  4117.         return 0
  4118.     end
  4119.  
  4120. GO
  4121.  
  4122.  
  4123. USE [msdb]
  4124. GO
  4125.  
  4126. if exists(select 1 from sysjobs where name='DBA_PerfBaseline_Report_Job')
  4127. begin
  4128. declare @jid uniqueidentifier
  4129. select @jid=(select job_id from sysjobs where name='DBA_PerfBaseline_Report_Job')
  4130. EXEC msdb.dbo.sp_delete_job @job_id=@jid,@delete_unused_schedule=1
  4131. end
  4132.  
  4133. /****** Object:  Job [DBA_PerfBaseline_Report_Job]    Script Date: 1/17/2013 10:38:09 PM ******/
  4134. BEGIN TRANSACTION
  4135. DECLARE @ReturnCode INT
  4136. SELECT @ReturnCode = 0
  4137. /****** Object:  JobCategory [[Uncategorized (Local)]]]    Script Date: 1/17/2013 10:38:09 PM ******/
  4138. IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
  4139. BEGIN
  4140. EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
  4141. IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
  4142.  
  4143. END
  4144.  
  4145. DECLARE @jobId BINARY(16)
  4146. EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'DBA_PerfBaseline_Report_Job',
  4147.         @enabled=1,
  4148.         @notify_level_eventlog=0,
  4149.         @notify_level_email=0,
  4150.         @notify_level_netsend=0,
  4151.         @notify_level_page=0,
  4152.         @delete_level=0,
  4153.         @description=N'This job is creating performance baseline report.For this instance report is locating under the location where master data files are reside.--This job is owned by Physical DBA.',
  4154.         @category_name=N'[Uncategorized (Local)]',
  4155.         @owner_login_name=N'sa', @job_id = @jobId OUTPUT
  4156. IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
  4157. /****** Object:  Step [Execute SP For Creating HTML Report]    Script Date: 1/17/2013 10:38:09 PM ******/
  4158. EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Execute SP For Creating HTML Report',
  4159.         @step_id=1,
  4160.         @cmdexec_success_code=0,
  4161.         @on_success_action=1,
  4162.         @on_success_step_id=0,
  4163.         @on_fail_action=2,
  4164.         @on_fail_step_id=0,
  4165.         @retry_attempts=0,
  4166.         @retry_interval=0,
  4167.         @os_run_priority=0, @subsystem=N'TSQL',
  4168.         @command=N'
  4169. declare @retval int
  4170.  
  4171. exec @retval=SP_InstanceBaselinePerfReport
  4172.  
  4173. select  @retval
  4174. if @retval <> 0
  4175. begin
  4176. RAISERROR (50005, -- Message id.
  4177.           10, -- Severity,
  4178.           1, -- State,
  4179.           N''PerformanceBaseline Job is failing Please check folder  and FIles or anything wrong with script'')
  4180. end',
  4181.         @database_name=N'master',
  4182.         @flags=0
  4183. IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
  4184. EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
  4185. IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
  4186. EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
  4187. IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
  4188. COMMIT TRANSACTION
  4189. GOTO EndSave
  4190. QuitWithRollback:
  4191.     IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
  4192. EndSave:
  4193.  
  4194. GO
RAW Paste Data

Adblocker detected! Please consider disabling it...

We've detected AdBlock Plus or some other adblocking software preventing Pastebin.com from fully loading.

We don't have any obnoxious sound, or popup ads, we actively block these annoying types of ads!

Please add Pastebin.com to your ad blocker whitelist or disable your adblocking software.

×