Exceptional SQL DBA

Joe님이 만드신 ExecStats툴을 처음 사용하게 되었는데, 좋은 툴을 공유하고자 합니다.

About
요약 : 실행 계획 분석 툴

ExecStats started out with a focus on execution plan analysis with cross-referencing index usage to the SQL. Formerly separate programs for performance monitoring and server system architecture discovery via WMI have now been integrated into ExecStats (2012-Oct).

The Distributed Management Views and functions (DMV and DMF) have greatly simplified the process of collecting performance data sufficient for basic assessment and SQL query and index tuning. Still, even the modest effort in collecting a set of DMV/DMFs is more than the push-button effort that people will be inclined to do on a regular basis. Query tuning also requires the additional effort to examine the execution plans.

Index tuning involves both indexes that should be added or expanded, and indexes that should be removed or consolidated. The DMVs provide some recommendations on missing indexes, but this is not always reliable, so a live expert is still essential. Identifying indexes not used is simple. The other part is identifying infrequently used indexes that could also be dropped or consolidated. This would require finding the execution plans that reference the infrequently used indexes to assess the implications or impact.

Much of this can be automated with the SQL Exec Stats so that regular performance data collection is nearly a zero effort routine, including correlation of the execution statistics in the DMV with information from the execution plans, and a cross reference of execution plans that reference each index. Finally, if performance expertise is not available onsite, the set of information is compact enough to be sent by email or ftp for full assessment

원본 위치 <http://www.qdpma.com/ExecStats/SqlExecStats.html>

Download

http://www.qdpma.com/ExecStats/SqlExecStats.html

ExecStats를 압축해제 합니다. 확장자가 SQLExecStats.exe.config를 Notepad로 수정을 합니다.

XML 구조인데, SqlServer의 속성 Value 정보에 접속 정보를 적용하며, 만약, 하나의 DB만 분석하겠다 하면 Database에 DB명을 적용합니다. 여러 DB일 경우 Databases Value에 여러 DB를 콤마로 구분하여 적용합니다. 19 line의 User 항목에 로그인 계정명 적용하고 SQLExecStats.exe을 실행합니다.

Use

패스워드를 config파일에 적용하지 않았기 때문에 SSPI를 체크를 disable를 해줍니다. 그리고 connect 버튼으로 분석을 진행 합니다.

List에는 서버 정보, 클러스터링 되어 있는지 여부, xp_msver, dm_os_memory_cache_counters, dm_os_sys_memory, dm_os_process_memory, dm_os_wait_stats, sys.configurations, dm_os_loaded_modules 등 결과 정보를 보여줍니다.

이제, 상세는 각 탭 별 정보를 확인할 수 있습니다.