In other words, zoom…Įxcluding the first 6 I/O's and re-creating the chart will result in a more detailed data view: It is important to look at the data ranges before plotting to ensure the graph shows the appropriate data.
If the data set was more varied, for instance starting at 0 and going all the way up to 361,380,839,424 the graph will appear to show only sequential data access. Note: I wanted to specifically point out that the offset range is localized. As the SQL engine detected sequential access it began to size up the I/O request to 128, 192 and 256K. In this example SQL server issued 30 64K I/O requests. In addition to the access pattern we can also look at the actual size of each I/O. This graph shows that the row count query created a nice sequential access pattern. This creates a visual representation of the query: I inserted a Scatter graph (Scatter with Markers only) and plotted all of the offsets (Insert tab – charts – scatter). I am left with an offset column and a length column:Īs a final step I clean up the data by removing the commas and converting the columns to number with no decimal places. I use a Space as the delimiter of choice (using comma does not work as commas are used as both a 1000's separator and a value Delimiter):įinally I use step 3 of the text to column wizard to clean up the import by skipping the text (as I don't want it for this analysis). I use the text to columns feature of Excel to separate the values into columns (Data tab then Text to Columns): Each detail records the offset, length and I/O flags: We first need to separate the detail column into usable parts. I saved all of the events that generated the query values as a. I set up Process Monitor to track only sqlservr.exe events (see Part 1 ). For quick setup I only created one data file and one log file.
mdf file with 64K and larger I/O (a total of 209 reads averaging 165K each). The first query simply counts the rows in the table:įROM ( select * FROM. I fired up two different queries and ran Process Monitor to track the I/O ( - Version 2.95 as of this writing). The data is generated by the TCPH utility called DBGEN.EXE (you can download the source files and documentation at ).įor the purposes of these examples I am using the Supplier table. The environment consists of a 300GB data base with clustered and Non-Unique Indexes (depending on the table and keys).
In my lab I have a TPC-H "Like" environment (If testing is not sanctioned by the TPC committee then you are legally obliged to call it TPCH Like - ). Part will cover interpreting Process Monitor results. Part one covered the Microsoft Process Monitor tool. Today's post is the second in a long series.
0 kommentar(er)
