I began setting up for Profiler and Performance Monitor by first launching Profiler and then launching Performance Monitor within it (there’s an icon for PerfMon second from the Right or you can select Performance Monitor from the Tools Menu). I had already created a Performance Monitor Counter Log so all I needed to do was start it (more details on PerfMon Counter Logs later).
Tips:
· If you want to correlate Perf Mon events with Profiler you will need to do this from SAVED Profiler Traces and PerfMon logs. Real-time analysis is not supported.
· The correlation is done based on the time of the events so it is important that the times are correct. You should make sure to either:
· Run these on the same machine
· Make sure that the machines are in sync in terms of time.
· The correlation is a bit tough if there are TONs of events as Perf Mon’s granularity for sampling is 1 sec and Profiler can return a large number of events in a single 1 sec range… SO – filtering (and you can do this later) will be a very good idea. Regardless, this is a GREAT feature to see how certain counters (memory, disk, CPU, etc.) look over the course of long running activities.
If you’re not familiar with creating a Performance Monitor Counter Log click here.
Ok, so that was mainly setup. For my first real demo I wanted to show Profiler and a few of my favorite new features. Here’s a quick list:
When creating a trace there are ONLY two dialogs: General and Events Selection. The General dialog is similar to the old General dialog where you choose the template, the save to file and/or table options, etc. The Events Selection dialog however, is ALL NEW. The Events Selection Dialog has a ton of new and incredibly useful changes:
- Only data columns that produce values for each of the specific events are shown and only those data columns can be selected (check boxes). See this dialog here: EventSelectionDialog.jpg (56.51 KB). Notice that there are blank spots where an event doesn’t produce a data column.. yeah!
- Filters apply at the column level. See this dialog here: EditFilterDialog.jpg (16.74 KB). And you can state whether or not events that do NOT generate a value for the filter are sent (yeah!!!). However, I do remember having a bit of trouble with this one…
- You can pause an active profiler trace, change the events and/or data columns and restart it… all of the additional events go to the same trace file and you do not lose the previously started trace, etc.
- Deadlock graphing – this is an Event – and it produces a graphic display of the spids that were involved in the deadlock. You can “extract” these events (right click on the event “Deadlock Graph“ and choose “Extract Event Data“) and then open this xdl file within SQL Management Studio so you can spend more time analyzing it.
So, once setup I created a deadlock scenario, showed the profiler output for deadlock graphing, paused the trace, removed the deadlock related events (really only because I could J) and then opened another window to generate some simple activity. So – speaking of the simple activity… I created a “demotable” that had defaults for all columns and then I created an insert statement using the DEFAULT VALUES clause. To get this to execute repetitively I could have written a complex (not!) loop such as WHILE 1=1 but instead I chose to use go N (mostly because people don’t tend to know about this one). Here’s the full extent of my “code”
INSERT DEMOTABLE DEFAULT VALUES
GO 100000
This will cause that statement/batch to execute 100000 times.
Once that was running, I wanted to generate more activity to profiler so I decided to go back to show another favorite from Management Studio. In Management Studio, I had two registered servers waiting: one is a SQL Server 2000 server and the other SQL Server 2005. I connected to both. One of the databases on my SQL Server 2000 system is a schema from a Microsoft.com data warehouse. The reason why this is interesting is that this database holds 300+ tables and 1300+ views (not to mention many other objects). While this isn’t the largest database out there (at 450GB) it certainly has issues in terms of finding objects and managing your “view” of what you’re trying to manage. So – in the SQL Server 2005 Management studio you’re able to create “Filtered” Views. By right clicking on Views, you can create a name filter. For example, all of their user objects have a certain naming convention which includes “WMU”, all of their rollup counts have the word “count” and the counts refer to a dimension of time (weekly, monthly, etc.)… So, if I want to see all of their user related views with counts by week, I can create this filter WMU%Count%Week. Using this filter, the object list drops from 1300 views down to only 12. And – this demo was using the SQL Server 2005 Management Studio against a SQL 2000 server. Nice.
So – now with a bunch of activity generated I can go back over and get the data to correlate. I stop the trace. I stop the Profiler Log…
I re-open the profiler trace (only because this would likely to be done later… I’m not sure if I’ve ever just stopped it and then integrated perfmon… that should work?). Anyway, only AFTER the entire trace file is loaded with the menu option to integrate perf mon counters become available. Choose “Import Performance Data“ from the File dropdown and wham – IF they have corresponding times you will see how the times match (remember, if this is on two machines and the times aren’t in sync then the correlation could be virtually meaningless). Also, if they don’t intersect you’ll get an error: Correlation is not possible because there is no intersection between trace and performance data time ranges. Not bad!
If the two DO correlate in terms of time, then you’ll get a new dialog which will show all of the performance monitor counters that are IN the perfmon log. The idea here is to “limit“ the number of counters to correlate. In fact, the dialog is called “Performance Counters Limit Dialog.“ OK, so my log has only a few counters so I’ll just select them all. Click Ok. Once clicked your performance monitor dialog will show both – profiler data and perfmon data. Here you can select points in time and see what performance events occurred or choose profiler events and see what the perfmon counters look like. Here’s what it looks like:ProfilerPerfmon.jpg (167.51 KB). You can also highlight chunks of the graph and expand them to show only a specific time range. You do this by clicking in the graph and then drag the mouse to create a rectangle.
OK, so in summary – I REALLY like this BUT I think you’ll need to play with this a bit. I think it’s a great feature but the range of values for correlation for Performance Monitor only being 1 second makes it a bit tough to see exactly what event caused what spike (if you have thousands of events per second in Profiler) BUT – it’s good to get you close!!! And much closer than anything we’ve had before!
Have fun.
kt