Blog Archives
Deadlock Graphs
Last night I learned about graphical deadlock graphs from Fargham Butt of Microsoft. I had simply used the traceflag and then read the error logs to find the information. Thus today I thought I would write a blog about the subject and decided to do some research first when I found an excellent blog post by Jonathan Kehayias (B|T) covering Handling Deadlocks in SQL Server. Jonathon is a Microsoft Certified Master and does a much better job than I can do explaining the whole subject, so I thought it more prudent to just refer you to his blog. Give it a whirl and enjoy!
Using the Right Tool
My week has been spent deep diving with the OpNet AppInternals Xpert and AppResponse console to learn some more valuable troubleshooting skills for our servers and applications that we administer including SQL Server and Oracle.
Using OpNet, I have been able to resolve three major headaches for my team this week working with their engineer. I am reminded of the simple fact that sometimes the ability to do your job well consists of having the proper tools to perform the job. With the cost of these tools sometimes you have to be able to justify the need but this one pays for itself many times over and that says a lot because it is pricey.
Master Maintenance Plan, Part One
For the last two days, in between other tasks, I have been kicking around ideas to organize or reorganize scheduled jobs and maintenance plans into a grander scheme solution that sends me ONE e-mail a day. Sounds like a minor operation, but I have 32 SQL servers including four clusters. Currently, many of the 300 databases send me nightly e-mails to let me know whether the individual operations succeeded or failed. There are still some maintenance plans that I have not replaced yet. I have also implemented Ola Hallengren’s backup solution on many of the servers which greatly reduced the number of e-mails that I receive. The plan is to install that solution on every server. This solution has saved me hours upon hours of work, check it out if you have not already.
Sometime ago, I wrote or found a script (no, I cannot remember) that would send me an e-mail letting me know the backup type and date/time of the last backup. So here we are, and I am trying to take this to another level by exploring solutions to greatly reduce e-mail traffic and reduce the amount of time it takes me to assess my server’s overnight maintenance thus allowing me to spend more time being proactive. With all that being said, I found the following article, Use PowerShell to Report SQL Server Backup Status, written on the Hey, Scripting Guy! Blog.
As this plan develops, I will post new blog posts. Enjoy or let me know how you handle it. Let’s share ideas!
SQL Nexus, Perf Stats Report
2005 Perf Stats Report for 2008 Data
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!
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!
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
Performance Monitoring Tools Compilation
Reading some blogs this morning, I found this article on SQL Server Performance Monitoring Tools. The only product I see that they are missing is Red Gate’s offering, which I personally use. But this is a great list as some of these I had never seen before.
Does anyone have an opinion on these products? I personally enjoy the Red Gate SQL Monitor, product, but it is the only product I have ever used outside of SQL Server itself. Give me your input. Enjoy!
SQL Swampland 