Use profiler and Database Engine Tuning Advisor to optimize SQL Server

Some time ago, I had a performance problem with my website, and sometimes the page was very slow to open. It would take more than 10 seconds. I tried a lot of methods, including cache, disable viewstate, and Disable debug mode. The improvements were not obvious.

 

Later, I ran into SQL Server Profiler to check the cause: a slow query dragged down the entire page. Later, the query was optimized, but not many improvements were made. Finally, an index is created for this table, and the CPU time consumed by the query will immediately change from 1000 + to 0! Haha.

 

Http://www.mssqltips.com/tip.asp? Tip = 1872 this article describes how to use Database Engine Tuning Advisor (DTA) to optimize database performance.

 

You can retain the SQL statement for the query and enable it with DTA. You can also use profiler to capture some queries and use DTA to enable the query. However, when I used DTA to open the file captured by Profiler, the speed was very slow. It showed that the file was being analyzed. After several hours of analysis, it finally timed out. Open a single query.

 

Sometimes, the analysis advice of DTA is self-contradictory. when optimizing this query, we say that we should create this index. When we optimize it, we say that we have deleted the index We just created. However, it is not a problem to create or not delete only.

 

I checked a lot of things about Asp.net performance optimization and it seems useless. But you still need to learn! Come on!

 

Http://ubbs.xunlei.com/read.php? Tid = 10618 common optimization methods

 

Http://msdn.microsoft.com/en-us/library/ff647787.aspx, Microsoft's official optimization guide, shows a global perspective.