T-SQL performance Tuning-information collection

Original: T-SQL performance Tuning-information collection

IO information (starting from server startup)

--database IO Analysiswith iofordatabase as (SELECT db_name (vfs.database_id) as DatabaseName, case if Smf.type = 1 Then ' log_file ' ELSE ' data_file ' END As Databasefile_type, sum (vfs.num_of_bytes_written) as Io_write, sum (VFS.N                        Um_of_bytes_read) as Io_read, SUM (Vfs.num_of_bytes_read + vfs.num_of_bytes_written) as Total_io, SUM (Vfs.io_stall) as Io_stall from sys.dm_io_virtual_file_stats (null, NULL) as VF                                                        S JOIN sys.master_files as SMF on vfs.database_id = smf.database_id                        and vfs.file_id = smf.file_id GROUP by db_name (vfs.database_id),  Smf.type) SELECT row_number () over (ORDER by Io_stall DESC) as RowNumber, Databasename , DatabasefiLe_type, CAST (1.0 * Io_read/(1024x768) as DECIMAL (2)) as IO_READ_MB, CAST (1.0 * Io_writ E/(1024x768 * 1024x768) as decimal (2)) as IO_WRITE_MB, CAST (1.0 * Total_io/(1024x768) as Decimal (12, 2) As IO_TOTAL_MB, cast (io_stall/1000 as DECIMAL (2)) as Io_stall_seconds, cast (. * Io_stal  L/sum (Io_stall) over () as DECIMAL (ten, 2)) as io_stall_pct from Iofordatabase ORDER by Io_stall_seconds DESC

Statistical information:

1. Use the DBCC SHOW_STATISTICS () command to check. Histogram and density information can be viewed.

2. Use the Sys.stats view to see how many statistics are in the table.

3. Use the Sys.stats_columns view to see which columns are part of the statistical information.

T-SQL performance Tuning-information collection