Category Archives: Monitoring
This morning one of our VMWare clusters went down when our SAN guys inadvertently unplugged the wrong storage array. Oops! We had to reboot some of our development and test servers including my SQL Servers. No big deal, this happens from time to time.
Good manners dictate that I notify the effected application administrators so they don’t panic seeing their connections drop. This is a pretty normal procedure. In my previous positions I had at the most five servers and I knew every database and who it belonged to. However, in this environment, I have almost 300 databases and knowing what they go to and who to contact is handled by a spreadsheet that was created before I started. Seems like there should be a better way to track this and to notify me when new databases are created. How are you handling this task?
Preparing to blog, I saw a new blog post by Jorge Segarra (blog | twitter) that will identify and alert you when you have long-running alert jobs. This is something that I have had on my list to sit down and write when I found some extra time (translation: never going to happen any time soon). I was so excited, I thought I would pass it on to you before I even played with it myself. The life of a DBA sometimes does include doing the happy dance when you see something totally cool. Enjoy!
Continuing our SQL Nexus journey, I noticed another item with the Perf Stats reports. As I mentioned in a previous post, I imported SQL Server 2008 R2 data into SQL Nexus. However when I choose the “SQL Server 2008 Perf Stats” report I get the “The database doesn’t have necessary data to run this report” dialog box and when I choose the 2005 report, it opens.
Here is where it gets interesting, if I right-click on the report I can select the report parameters. The only parameter is version and the value is 2005. That’s understandable, so I manually tried to change it to 2008. Voila! The report refreshes and now the report title reflects the new value. This report gives you three choices: Blocking and Wait Statistics and Bottleneck Analysis, which both have their own reports on the menu, and Spin Lock Stats. It appears that changing the parameter only changes the title of the report. I think next week I will crack open these two reports to see why the 2008 data loads in the 2005 report and not in the proper one and what the parameter really does. Until then, enjoy your weekend!
Yesterday, I wrote about the “Realtime – Server Status” report quirkiness where essentially the report does not work when you import SQL Server 2008 data and essentially it does not contain any information as to why it does not work, only a blank screen. Craig Purnell (Blog | Twitter) shortly thereafter sent me an updated report library file that works with 2008. That is exciting! I ran a diff on the two files and found that Craig updated two lines with the same change in order for this to work. The filename is “Realtime – Server Status.rdl” located in the Reports subfolder of your SQL Nexus installation folder. Below is the line that needs to be changed (remember to do it in both places):
Original line: SELECT @ts_now = cpu_ticks / CONVERT (float, cpu_ticks_in_ms) FROM sys.dm_os_sys_info
Updated Line: SELECT @ts_now = ms_ticks FROM sys.dm_os_sys_info
Thanks Craig, now I have results that look like the following:
As we continue our SQL Nexus journey, we continue to point out some quirkiness in the amazingly useful SQL Nexus tool. One of the items in the Reports section that I find quirky is the “Realtime – Server Status” report which on the surface sounds like an essential tool that I would rely heavily upon. However, when I select that report, the child screen goes blank and nothing happens.
Some of the other reports, such as the “SQL Server 2000 Perf Stats” report at least prompt you with a “The database doesn’t have necessary data to run this report” dialog box. That makes sense in this case because I am running from a SQL 2008 R2 source import. As we discovered early on in this blog series, the “ReadTrace_Main” report only works if your trace file import is successful. But the “Realtime – Server Status” report gives us nothing. No error, no suggestion, no data, nothing whatsoever.
After some careful research, actually I just loaded a SQL Server 2005 import, I discovered that this only works for 2005. Sadly, it appears to be a deprecated feature. This is a neat report that shows a dashboard reflecting the health of the server. This is quirky and there should at least be a message telling you that it will not work with 2008. Enjoy!
As we continue our SQL Nexus journey, I would like to point out something interesting that I learned from reading the SQL Nexus chapter in the race car book (aka Professional SQL Server 2008 Internals and Troubleshooting). The author, Christian Bolton, noted that the default Trace events that are collected when you run StartSQLDiagTrace2008R2.cmd from the performance scripts that we mentioned earlier in this series provide a good baseline for your monitoring activity. However, he noted added the following events in order to enable additional functionality necessary to compare execution plans and drill down to the statement level:
- EventType “Performance”, Event name “Show Plan Statistics”
- EventType “Stored Procedures”, Event name “SP:StmtCompleted”
- EventType “Stored Procedures”, Event name “SP:StmtStarting”
- EventType “TSQL”, Event name “SQL:StmtCompleted”
- EventType “TSQL”, Event name “SQL:StmtStarting”
In order to enable these simply edit the XML file, SQLDiagPerfStats_Trace2008.xml, changing the above items from false to true. After you collect these new events by running the CMD file, then import the collection into SQL Nexus and check out your new results. Enjoy!
As we continue our SQL Nexus journey, there are a few light things I would like to point out before the long holiday weekend. We will rejoin this journey probably next week after Memorial Day.
The first thing I noticed, after our debacle with the trace files not loading, was that several of the reports do not work as they should. After those were resolved, I noticed that some of the reports are not exactly what they state they are. For example, I connected to a SQL 2008 R2 box and imported the results from a detailed 2008 performance script (see previous posts) and yet when I click on the “SQL Server 2008 Perf Stats” report I get the error below. That is strange, what is stranger is that when I select the “SQL Server 2005 Perf Stats” report then I get results. I have talked with other DBAs who have experienced similar problems. Enjoy your holiday weekend!
Continuing in our journey of SQL Nexus, today we will look at the RML Utilities that we installed prior to the installation of Nexus. While thinking about what to blog about this morning, I decided to search for some background information to integrate into our learning. Once a found an article from the SQL Customer Advisory Team (aka SQL CAT), I thought I would not reinvent the wheel and simply pass this information on to you. The article, Precision Performance for Microsoft SQL Server using RML Utilities 9.0, gives an excellent break down for the uses of the tool. Check it out and enjoy!
Last Monday we started our SQL Nexus journey together in order for me to refresh my knowledge of this awesome tool. Near the end of last week, the blog series was delayed due to an error with the ReadTrace and its corresponding report. Yesterday and again today, I am troubleshooting our attempt to resume our journey but with little hope for correcting the error. I have posted the error on CodePlex and am awaiting a response.
Yesterday, I referenced a “ReadTrace exit code: -2” error in the ReadTrace log file. I did find one page out there on SQL Server Central where Grant Fritchey (Twitter | Blog) had encountered the problem. After contacting, Grant he told me that the problem that he had was a simple one, he did not have the file and path name correct which corresponded with the file not found error higher up in the ReadTrace log. However, when cutting and pasting this file straight out of the log, the file opens. Strange indeed, but Grant got me thinking and thus reading through all of the files looking for a possible mistake in the scripts. I could not find any.
Keep in mind, this is with running the StartSQLDiagDetailed_Trace2008R2.cmd performance script that I outlined in one of the previous posts. This simply reference XML files that appear to only outline the performance indicators in which we want to monitor. It is in the MSDiagProcs.sql script that it identifies the trace files to load and run. In addition, none of the three scripts will load trace files into the import.
Here is a sample of what is called from the CMD scripts:
H:\PerfScripts\2008R2>”C:\Program Files\Microsoft SQL Server\100\Tools\Binn\SQLdiag.exe” /I “H:\PerfScripts\2008R2\SQLDiagPerfStats_Trace2008.XML” /O “H:\PerfScripts\2008R2\SQLDiagOutput” /P
However, attempts to run this manually through ReadTrace also failed with one of the trace files created by the performance script, such as:
H:\Program Files\Microsoft Corporation\RMLUtils>readtrace.exe -I”H:\PerfScripts\2008R2\SQLDiagOutput\SQLVMDEV10_SQLDIAG__sp_trace.trc” -o”C:\Users\crssqladmin\AppData\Local\Temp\2\RML” -SSQLVMDEV10 -dsqlnexus -E -T18 -f -H!PSSDIAG
However, playing with the file names yielded a different problem. When I renamed the file taking out the double underscore, ‘__’, thus resulting in SQLVMDEV10_SQLDIAGsp_trace.trc and SQLVMDEV10_SQLDIAGsp_trace_blk.trc, which seemed odd to me from the beginning, the ReadTrace load now indicated a permissions error. When I added my administrative account to these two trace files, the ReadTrace worked. Hmm, weird but interesting. I wonder now if the SQL Nexus import would load? See for yourself:
Enjoy and I hope this helps if you encountered the same issue!
Last Monday we started our SQL Nexus journey together in order for me to refresh my knowledge of this awesome tool. Near the end of last week, the blog series was delayed due to an error with the ReadTrace and its corresponding report. Today, I am still attempting to resume our journey but with little hope for correcting the error. I have posted the error on CodePlex and am awaiting a response.
The first error I received when loading the trace files in was error 26 which many people translated into a permissions problem on the trace file. I resolved that issue by granting full control to my admin login and SQL Server when that did not resolve the issue. Now I am receiving a “ReadTrace exit code: -2” error in the ReadTrace log file.
At this point, I can find no helpful information on the interwebs. I do find it interesting the that log file also denotes that it cannot find the trace file that it is trying to load, however when I cut and paste the file in question right from the log file it opens in notepad so I am not sure that the error is entirely accurate. I checked the permissions on the trace file and it is consistent with the other files. I’m stumped.
Has anyone else seen this?