Collect and Analyse Troubleshooting Data

Question 1

What is SQL Server Profiler?

Answer 1

It can be used to find problems with slow queries (find cause)

capturing series of t-sql statements leading up to a problem

Monitor performance of SQL to fine tune workloads

Correlating performance counters to diagnose problems.

Question 2

How do you get to SQL Server Profiler?

Answer 2

Tools -> SQL Server Profiler

Right click in query and click [Trace Query in SQL Server Profiler]

Question 3

What is going to replace Sql Server Profiler?

Answer 3

Extended Events (XEvents)

Question 4

Where is Extended Events found?

Answer 4

SSMS -> Management -> Extended Events

Question 5

What’s a benefit to using XEvents over SQL Server Profiler?

Answer 5

XEvents is more lightweight than Profiler (Less resources)

Question 6

What is System Monitor?

Answer 6

It is able to monitor SQL Server as well as things outside of SQL. (Device usage, memory, threads, processes)

Question 7

Why are disk performance counters disabled on System Monitor?

Answer 7

Because there is a noted significant performance impact in collecting disk metrics.

Question 8

How do you enable disk performance for System Monitor?

Answer 8

in a command prompt type:

diskperf -Y

Question 9

Within System Monitor, what is the standard expectation for % Idle Time?

Answer 9

Less than 60%

Question 10

Within System Monitor, what is the standard expectation for average disk sec/Read (Write)?

Answer 10

less than 20 Milliseconds

Question 11

Within System Monitor, what is the standard expectation for current disk Queue Length?

Answer 11

Not higher than 2

Question 12

Within System Monitor, what is the standard expectation for Memory Available MBytes?

Answer 12

Minimum of 10% available.

Question 13

Within System Monitor, what is the standard expectation for Memory Pages/Second?

Answer 13

No higher than 1000

Question 14

Within System Monitor, what is the standard expectation for Network Bytes Total / Sec?

Answer 14

Preferably under 40%

Question 15

Within System Monitor, what is the standard expectation for Paging File % Usage?

Answer 15

No higher than 10%

Question 16

How do you save System Monitor data?

Answer 16

Add a new Data Collector Set under User Defined

Question 17

What do you use to identify Memory Bottlenecks?

Answer 17

Look at Memory:

• Available Bytes
• Pages/Sec
• Process: Working Set (How much memory is being used by a process)

SQL Server:

• Buffer Manager: Buffer Cache hit ratio
• Buffer Manager: Database Pages
• Memory Manager: Total Server Memory (KB)