SQL Server high-load I / O and I / O database

Question

Our application has been running very slowly lately. During debugging and tracking, it turned out that the process shows high processor cycles, and SQL Server shows high I / O activity. Can you talk about how you can optimize it?

Now the application is about a year, and the size of the database files is not very large or something else. The database is configured for automatic compression. Its work on win2003, SQL Server 2005 and the application is a web application encoded in C # ie vs2005

Answer 1

SQL Profiler , , "" . , .

, . , , , , . , . , , , .more.

. .

Answer 2

• (, , mdf/ldf).
• ldf , mdf,
• SQL 2005; , ; " ", ; , , ; , .

Answer 3

, .

DBCC showcontig, . , , , . , . , .

Answer 4

EXPLAIN. , .

Answer 5

, , , . , , , . , . , . , . , . .

Answer 6

, , , 2 . db sql server 2005, 80-100% , , , , , , , . ( , , ), Sql Server .

, SQL Server 2005 ; SQL Server , , , . 130 (, ). db cpu 30% 40% , , .

. , . , - , .

SELECT mid.database_id, 
       db.name,
       migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) AS improvement_measure, 
       'CREATE INDEX [missing_index_' + CONVERT (varchar, mig.index_group_handle) + '_' + CONVERT (varchar, mid.index_handle) + '_' + LEFT (PARSENAME(mid.statement, 1), 32) + ']' + ' ON ' + mid.statement + ' (' + ISNULL (mid.equality_columns,'') + CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END + ISNULL (mid.inequality_columns, '') + ')' + ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement, 
       migs.*, mid.database_id, mid.[object_id]
FROM sys.dm_db_missing_index_groups mig 
INNER JOIN sys.dm_db_missing_index_group_stats migs
    ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid
    ON mig.index_handle = mid.index_handle
INNER JOIN sys.databases db
    ON mid.database_id = db.database_id
WHERE migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) > 10
ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC