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