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