Blog Archives
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, 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 Nexus, Data Collection
Monday we started our SQL Nexus journey together in order for me to refresh my knowledge of how to use this awesome tool. On Tuesday, we installed some essential pieces in order to get the SQL Nexus tool installed and ready to use. We detoured on Wednesday to attend the SQL Server 2012 Technology Days at Microsoft in Tampa. Today we return to the fun, so let’s jump right back in!
I realized this morning that I left out another download, the SQL Server 2005/2008 Performance Statistics collection scripts. This has the XML configurations needed to run SQLDiag in order to load performance data into SQL Nexus. Well let me qualify that, these XML files are pre-configured and they can be modified if necessary. These configurations will capture DMVs and also blocking information that can help you troubleshoot performance problems. It will also collect machine information from Perfmon, msinfo32, various event logs, and the profiler trace (unless you run the NoTrace command script). There is more detailed information on customization available on the CodePlex download page (make sure you read the fine print about the download location as it is not the big download button on the right).
Once you run one of the scripts, such as StartSQLDiagDetailed_Trace2008R2.cmd, you will see several lines of preparatory messages followed by “SQLDIAG Collection started. Press Ctrl+C to stop.” At this point you can reproduce your issue, if possible. If not possible, then collect data for a few minutes and then press Ctrl+C to finish the collection and generate the output necessary for SQL Nexus. Be vigilant of running this as the output can grow very large very quickly especially if you running this against a production server. Be careful! Once the dialog closes, then the output is ready to load into SQL Nexus.
Now open SQL Nexus and we can import the data that is output from SQLDiag from the output folder that is generated underneath the folder where you ran the script from, by default. From there we can run some of the reports located on the upper left corner of the interface. Not all of them will apply depending upon what data you collected and which script ran. Below is a sample of the Blocking and Wait Statistics report. This was just a sample ran for this blog, so unfortunately I did not having any blocking issues to show you. I hope this helps your understanding of SQL Nexus. Enjoy!
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!
What is a Blocked Process, and Why Do I Care?
This is a question I received from a new SQL Server DBA so I thought it would make a great blog topic. Blocking occurs in SQL Server when one process has one (or more) of the records locked and another process tries to acquire a lock on the record. This will subsequently cause the process to wait until the original process is done so that it can acquire the record. This can cause a domino effect if subsequent processes are waiting for the new process to be done with the records it is holding. Blocking is somewhat different from the fatal deadlock which SQL Server handles and stops automatically (and a block can become a deadlock but this a different case).
The blocked process essentially must wait and that can be a problem for your database! I have previously blogged about best practices for separating our your log files to another drive as well as system databases and tempdb. If you are not following these best practices, then they may play into your blocking situation as well as inadequate hardware for your SQL Server.
Ok, how do I see those blocked processes?
One way is some code I found at CodeProject: List blocking processes in SQL server – CodeProject®.
Or, two days ago I wrote a blog talking about the newest release of Adam Machanic’s Who is Active stored procedure.
Below I have listed a couple of links for more information on how to use this procedure.
Adam Machanic : Why Am I Blocked? (A Month of Activity Monitoring, Part 19 of 30)
Adam Machanic : Leader of the Block (A Month of Activity Monitoring, Part 23 of 30)
Thanks, Adam! Enjoy!
An Amazing Utility: Adam Machanic’s Who is Active
In case you haven’t heard, Adam Machanic (Blog | Twitter) released Who is Active v11.11 last week. If you are using this utility, then you will want to grab this version as it will be the last version to support SQL Server 2005. If you are not using this utility, then you own it to yourself to download it and check it out. Thinking about going into detail about the various uses of this utility, I decided it would be better to link to Adam’s blog where he did a series called a Month of Activity Monitoring where he presented thirty blog posts detailing activity monitoring and the script. Why reinvent the wheel, especially when it rolls so smoothly?
Enjoy, and thanks Adam for a great utility!
SQL Swampland 