Baseline performance
There are four tools that we can use to check performance metrics for the operating system and SQL Server:
Dynamic management objects (DMOs): Including Dynamic management views (DMVs) and Dynamic management functions (DMFs).
Performance Monitor Operating.
SQL Trace.
Extended Events.
Dynamic management objects (DMOs)
Most DMOs provide information about the current state, such as currently blocked sessions in sys.dm_os_waiting_tasks, or information accumulated since SQL Server last restarted, such as sys.dm_os_wait_stats. SQL Server retains the information accessible through DMOs in memory only and does not persist it to disk. Therefore, the information is reset when SQL Server restarts.
You use the sys.dm_os_wait_stats, sys.dm_exec_session_wait_stats, or sys.dm_os_waiting_tasks DMVs to gather information about the amount of time that threads must wait on resources, determine whether the server is experiencing CPU, memory, or IO pressure, or find out which resources are causing excessive waits.
Example:
You have been monitoring wait statistics in the sys.dm_os_wait_stats DMV for several weeks and notice that the ratio of signal_wait_time_ms to wait_time_ms has been increased from 10% to 30%. What type of problem is this likely to indicate?
A. CPU pressure B. Memory pressure C. Network bandwidth issues D. IO subsystem failures
Answer:A. The signal wait time is the amount of time that a thread is able to run a task, but is waiting its turn on the CPU. To confirm this diagnosis, check for an increasing number of SOS_SCHEDULER_YIELD wait types in thesys.dm_os_wait_stats DMV and check the sys.dm_os_schedulers DMV for a high value in the runnable_tasks_count column. Answer B is incorrect because memorypressure is indicated by PAGEIOLATCH waits in combination with a Page Life Expectancy performance counter value dropping er time. Answer C is incorrect because you generally use network-related wait types and performance counters to confirm your diagnosis of network bandwidth issues. Answer D is incorrect because a failure in the IO subsystem will become evident when there are many waits containing IO in the name and average wait time begins to increase.
Performance Monitor
Performance Monitor, also known as PerfMon, is a tool provided with the Windows operating system that you can use to monitor operating system, application, and hardware performance in real time. You can even establish thresholds and receive alerts when thresholds are crossed. As an alternative, you can capture performance data in logs that you can review in the graphical interface or save to SQL Server tables for trend analysis.
Typically, you use performance counters to confirm suspicions of a problem that you uncover by using wait statistics rather than as a starting point. Like DMOs, for real-time analysis, open Performance Monitor, and click the Add button in the toolbar.
To identify issues in disk IO activity, start by reviewing the following performance counters (replacing PhysicalDisk with the LogicalDisk if you have multiple logical partitions on the same disk):
PhysicalDisk: % Disk Time
PhysicalDisk: Avg. Disk sec/Read
PhysicalDisk: Avg. Disk sec/Transfer
PhysicalDisk: Avg. Disk sec/Write
PhysicalDisk: Current Disk Queue Length
Memory: Page Faults/sec
To monitor is CPU usage, using the following performance counters:
Processor: % Privileged Time
Processor: % Processor Time
Processor: % User Time
System: Processor Queue Length
To monitor memory, use the following performance counters:
Memory: Available Bytes
Memory: Pages/sec
Process: Working Set
Example: As another method to confirm your diagnosis for the scenario in the previous question, which performance counters should you check?
A. Physical disk counters: % Disk Time, Avg. Disk sec/Read, Avg. Disksec/Transfer, Avg. Disk sec/Write, Current Disk Queue Length.
B. Processor counters: % Privileged Time, % Processor Time, % User Time,Processor Queue Length.
C. Memory counters: Available bytes, Pages/sec, Working set.
D. SQL Server counters: Page lookups/sec, Page reads/sec, Page writes/sec, FreeList Stalls/Sec, Lazy Writes/Sec, Memory Grants Outstanding, Memory GrantsPending.
Answer: B. You can monitor CPU usage by using the Processor performance counters. In particular, if % Processor Time is between 80 and 90 percent consistently, % User Time nears 100% consistently, and the Processor Queue Length value is increasing over time, the CPU is experiencing pressure. You should consider upgrading the CPU or adding more processors. The counters listed for Answers A, C, and D are useful performance counters. However, they do not provide any confirmation of whether the observations in Question 5 are related to CPU pressure.
SQL Trace
SQL Trace is useful for server-side or client-side tracing when you want to capture query plans. However, you can use tracing to monitor other aspects of SQL Server performance. Unlike DMOs that allow you to capture activity as it occurs, SQL Trace requires that a monitored event is completed before its data is added to the trace.
Besides using SQL Trace to get query plans, consider using it as a diagnostic tool when you need to monitor SQL Server’s behavior during query processing. For example, you might use SQL Trace in the following situations: Lock escalation, Deadlocks, Slow queries.
Extended Events
As the replacement for SQL Trace, which is slated for deprecation in a future version of SQL Server, Extended Events not only allow you to perform the same tasks with greater flexibility and better performance, but also allow you to monitor more events.
In SQL Server 2016, you can monitor 180 events by using SQL Trace, whereas you can monitor 1209 events by using Extended Events. Later in this chapter, we provide specific examples of how you might use Extended Events, we compare the performance impact between Extended Events and SQL Trace, and explain its architecture of this event-handling infrastructure.
you can use Extended Events and SQL Trace interchangeably to provide many similar diagnostic functions. However, it is important to note that there is considerable difference between them when considering their respective impact on the observed server.
Both tools by necessity add overhead to the server, which is measurable by observing performance counters for monitoring CPU processor time and batch requests per second.
The use of SQL Server Profiler for client-side tracing is the most intrusive option whereas using SQL Trace stored procedures for server-side tracing is less intrusive. With this in mind, you should try to limit the number of events and number of columns captured to minimize the overhead as much as possible. The least intrusive option is Extended Events, which was developed as a lightweight replacement for SQL Trace. Nonetheless, because it does incur overhead, you should take care to create events that collect the minimum amount of data necessary for troubleshooting.
In particular, be aware that the query_post_execution_showplan event is expensive and should be avoided on a production server. If you must use it to troubleshoot a specific issue, take care to restrict its use to a limited time only.
Example:
Which tool monitoring tools can you use to get information about SQL Server memory usage without writing any code?
A. DMVs or SQL Profiler B. Server-side SQL Trace or Extended Events C. Performance Monitor or Management Data Warehouse D. Client-side SQL Trace or Resource Governor
Answer is C. Both Performance Monitor and Management Data Warehouse provide graphical interfaces that you can use to monitor SQL Server’s memory usage. In Performance Monitor, you can set up the data collection of performance counters for the SQL Server Memory Manager, such as Memory Grants Outstanding or Memory Grants Pending, among others. In Management Data Warehouse, you can use the Server Activity History report to drill into Memory Usage details for the server. To access information from DMVs, you must write a T-SQL query; therefore, A is incorrect. Similarly, B is incorrect because a server-side SQL Trace requires you to write code. Answer D is incorrect because Resource Governor is not a performance monitoring tool.
A series articles on performance monitoring can be found here:
http://tutorial.wmlcloud.com/windows_server/SQL-Server-2008-R2---Performance-Monitoring-Tools-(part-7)---SQL-Server-Extended-Events.aspx
Data structure
By the way, here is a good article by Viswanath Subramani (https://medium.com/@vishwan/data-preparation-etl-in-business-performance-37de0e8ef632), who describes data-warehouse, I find this is intriguing.
There are some others if you search ETL and datawarehouse, and see image. For example, https://www.slideshare.net/RTTS/what-is-a-data-warehouse-and-how-do-i-test-it, https://panoply.io/data-warehouse-guide/data-warehouse-architecture-traditional-vs-cloud
