2010 4feb sqlserver2008toolfordevelopers - nicole

8/11/2019 2010 4Feb SQLServer2008ToolForDevelopers - Nicole

1/21

Microsoft SQL Server 2008Tools for Developers

Nicole Loh

Principle Consultant


2/21

Key Takeaway

Checking efficiency of your TSQL statements

Tools to monitor resources consume

Tools that you should use during development

Out-of-box reports you can use


3/21

Session Objectives and Agenda

WHAT are the different tools available - DEMOHOW to use the tools

WHEN to use the tools


4/21

List of Tools

Tools shipped with SQL Server 2005/2008Management Studio

SQL Server Profiler

Database Engine Tuning Advisor

Reporting

Performance Dashboard Reports


5/21

Sample Output


6/21

Performance Monitor

SQL Server Profiler

Database Tuning Advisor



7/21

Performance Monitor

Counters to enable

CPU - % processor

Memory - Cache hit ratio

I/O Disk queue length

Etc. (depending what you are troubleshooting)


8/21

Demo Performance

Monitor


9/21

Performance Monitor

CPU

Memory

I/O

Perfmon Recommendations:

Use logs collected to identify when an external

process is heavily using the computer running SQLServer and negatively impacting SQL Server

Run prior to production to determine efficiencyand to understand system/database activities


10/21

Performance Monitor

SQL Server Profiler




11/21

SQL Server Profiler

Low overhead detailed tracing

Track engine process events

Monitor database activity

Locking, blocking or deadlocking


12/21

Demo SQL Server

Profiler


13/21

SQL Server Profiler

Low overhead detailed tracing

Track engine process events

Monitor database activity

Locking, blocking or deadlocking

Profiler Recommendations:

Run to scan for deadlocks during the later part ofdevelopment cycle and early part of production

Run on purchased applications prior to production

to understand database activity


14/21

Performance Monitor

SQL Server Profiler




15/21


Powerful tuning tool

Analyze trace activity captured via SQL Profiler

Include creating and dropping indexes

Tuning recommendation


16/21

Demo Database

Tuning Advisor


17/21


Powerful tuning toolAnalyze trace activity captured via SQL Profiler

Include creating and dropping indexes

Tuning recommendation

Profiler Recommendations:

Run during test phase of an application. Ensure to

include online activity and batch in the same trace

Use proactively to verify current index structure isstill valid

Should be used for verification purposes


18/21

Performance Monitor

SQL Server Profiler




19/21

Performance Dashboard Report

Reduce time spent in initial performanceproblem investigation

Reporting Services is not required to be

installedHelp you identify common performance issuesincluding CPU bottlenecks, I/O bottlenecks,

Blocking, etc.


20/21

Demo Performance

Dashboard Reports


21/21

Conclusion

Do performance benchmarking beforedeploying application to production

Write efficient procedures and TSQL

statementsWork with your DBA to plan the databasedesign

Use the tools. They are free!!!Good luck

2010 4feb sqlserver2008toolfordevelopers - nicole

Documents