Introduction to SQL Server 2005 generating histograms

A histogram is a statistical report that displays the frequency at which values in the rank or range of values between a minimum and maximum value appear. Let's take the students ' test results as an example to illustrate. It is assumed that 30 students take the examination, the lowest is divided into 51, the highest is divided into 100. You want to generate a number of levels between the lowest and highest points, and calculate the number of results within each level to understand the distribution of the scores throughout the class. If you want to generate five consecutive levels of similar range sizes, you can get levels and ranges ranging from 50 to 60, 60 to 70, 70 to 80, 80 to 90, 90 to 100 (the lower limit is not included, but includes the upper limit). The histogram includes each level and the number of results within each level.

Similarly, using histograms, you can analyze the sampling of the values of performance counters (for example, CPU utilization, memory) that are set up on the server in your network. For example, suppose you record the CPU utilization of a network server every five minutes, and the value measured on one day changes between 21% and 100%. You can generate four levels of histograms-20 to 40, 40 to 60, 60 to 80, 80 to 100-to derive the number of samples that belong to each level. If your server is overworked on that day, most samples will fall into the fourth level.

Generate Performance counter histogram

Here's a question about histograms; You can see if you can fix it before you read my solution. A scheduled SQL Server Agent job will periodically record the sampling of performance counters for a network server in a table named Samples. Run the code shown in Listing 1 to create the Samples table and populate it with data. After sampling (DT), each row of the table contains a measure ID (MEASID), a measure (value), and a filtered column (100 bytes) representing the other columns in the table. (For example, the table typically includes a serverid column so that you can record a sample of metrics for multiple servers.) With this in mind, in this example, let the table contain only one server's data. ）

Suppose Measid 1 is a percentage of CPU utilization, and Measid 2 is the amount of memory used in megabytes. Your users need histograms to help them analyze performance data for a metric over a period of time. The user provides the following parameters: Number of levels (@numsteps), Measure ID (@measid), and date range (@fromdt-including start date, @todt-excluding end date). Your task is to generate histograms of these given parameters. Note that you do not have to include a level with a sample number of 0 in the result. For example, suppose the user provided the following parameters: