SQL Server performance Tuning common methods

1. Check the database space usage to see which tables are consuming larger disk space

Execute the following statement:

SelectO.name, SUM (p.reserved_page_count) asReserved_page_count, SUM (p.used_page_count) asUsed_page_count, SUM ( CaseWhen (p.index_id<2) Then (p.in_row_data_page_count+P.lob_used_page_count+p.row_overflow_used_page_count)Elsep.lob_used_page_count+P.row_overflow_used_page_count End) asDataPages, SUM ( CaseWhen (p.index_id<2) then Row_countElse 0end) asrowcounts fromsys.dm_db_partition_stats p INNER join sys.objects o on p.object_id=o.object_id GROUP BY o.name ORDER BY rowcounts Desc

View Code

2. Viewing performance consumption through Activity monitor

Determine which statements consume more performance by the time the disk io,cpu takes, the number of logical reads and writes

3. View SQL statements by execution time, disk IO and execution plan, including compile execution time, index scan lookup, read and write, etc.

View the statement of the Execution time and table scan, logical read and write conditions of the SQL statement:

SET STATISTICS time on
SET STATISTICS IO on

SQL Server performance Tuning common methods