Monthly Archives: May 2012
SQL Nexus, Quirkiness Workaround
SQL Nexus Report Options
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:
Realtime Server Status
SQL Nexus, Quirkiness Continued
SQL Nexus Report Options
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!
SQL Nexus, Adding Additional Collection Events
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!
Memorial Day
Today we give thanks to all those veterans and soldiers who have made the ultimate sacrifice to ensure our freedom. Soldiers put their lives on the line day after day to ensure our way of life and today we should remember that sacrifice. Both of my granddads in WWII, my uncle in Vietnam, my brother-in-law in Iraq, my cousins in the Army including Patrick who died in service, and my stepdad spent a career in the Navy. I have many other relatives as do most of you who also served. Thank you can never be enough but it’s a start.
Who’s You’re Daddy?
Sitting around my Dad’s house for the Memorial Day weekend which always coincides with my birthday celebration weekend, the subject of ancestry always comes up. My sister and I have been tracking our ancestry especially with my parents are getting older this becomes more important for the two of us to pass this along to our children.j
My wife enjoys listening to the numerous stories of my childhood and all the stories of our family and our crazy relatives; when you’re from the South you always have crazy relatives. The repetition is helpful so that these stories are remembered, now my wife is recording all of this into ancestry.com to coincide with her history. My sister has recorded part of this history, and thankfully we have a Civil War heritage that does not include any Yankees, which would’ve been disgraceful for any self-respecting Southerner. We are also part Native American, as well as Irish and Scottish so you can guess that we have a rich history with alcohol and fighting.
With all of this being said, I am reflecting on family and the greatest lesson I have learned in life is that no matter what happens in life all you have is your family, treasure it! Spend this wonderful weekend with your family. Enjoy.
SQL Schoolhouse, SQL Nexus Version
With a big holiday weekend, I thought I would continue our series on the SQL Nexus tool and pass on some excellent resources for our continued journey to learn. The following links are all great resources that will help us better understand how to use the SQL Nexus tool and related utilities. Enjoy and have a safe weekend!
- Trace Reporting with RML Utilities on SQL Server Pro magazine
- RML Utilities on Troubleshooting SQL
- Stress Testing your database engine using RML utilities on SQL Server Geeks
- Free SQL Server Tools, Software and Utilities
- Use SQL Server replay tools to reproduce and resolve customer issues by SQL CAT
- SQL Nexus articles on Troubleshooting SQL
- A blog showing SQL Nexus in action by Elisabeth Redei
SQL Nexus, More Quirkiness
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!
Weird SQL Nexus Errors
SQL Nexus, Using RML Utilities
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!
SQL Nexus, Read Trace Errors Resolved!
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:
SQL Nexus Import Successful
Enjoy and I hope this helps if you encountered the same issue!
SQL Nexus, Read Trace Errors
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?
SQL Swampland 