Webcast Material for Virtual Tech Days

In May, I had done a webcast on “Understanding Performance Bottlenecks using Performance Dashboard”. The presentation material is now available on the SQLServerFAQ MSDN blog and the webcast videos are available for download on MSDN. Refer the links below for the presentation deck and webcast video download link.

Managing and Optimizing Resources for SQL Server [PPT | Webcast Download] – Balmukund Lakhani [Blog | @Blakhani]
Optimizing and Tuning Full Text Search for SQL Server [PPT | Webcast Download] – Sudarshan Narasimhan [Blogs @ SQLServerFAQ]
Understanding Performance Bottlenecks using Performance Dashboard [PPT | Webcast Download | QnA]
Cool Tools to have for SQL Server DBA [Webcast Download] – Pradeep Adiga [Blog | @PradeepAdiga]
Learn Underappreciated Features of SQL Server to Improve Productivity [Webcast Download] – Nakul Vachhrajani [Blog]



Tags: ,,,


QnA: Performance Dashboard Webcast

I had an overflow of questions during the webcast today and wasn’t able to answer a few as we ran short of time. I shall attempt to address the most common questions asked during the webcast.

How do I configure SQL Server Performance Dashboards?
SQL Server Performance Dashboard was originally released for SQL Server 2005. Setup instructions are available with the download link. However, the same install can be tweaked to work for SQL Server 2008 and SQL Server 2008 R2 using the blog post by Sudarshan: Configuring Performance Dashboard for SQL Server 2008
Download link: SQL Server 2005 Performance Dashboard

The SQL Server instance being monitored must be running SQL Server 2005 SP2 or later. After completing the installation, you must:
1. Run the Setup.sql file on each instance of SQL Server 200x that you wish to monitor with the SQL Server 2005 Performance Dashboard Reports.
2. Open the performance_dashboard_main.rdl file with the Custom Reports functionality new to Management Studio in SQL Sever 2005 Service Pack 2.

Does Performance Dashboard work for SQL Server 2000?
No, Performance Dashboard uses DMVs to generate the data for the reports which are available from SQL Server 2005 and higher.

Where can I get the presentation and webcast recording?
The presentation and webcast recording for the entire webcast series will be made available on the SQLServerFAQ MSDN blog. I shall post an update once both are available.

What is the performance impact of running these reports?
If your server is unresponsive or experiencing high CPU usage on all the processors, then these reports will also face issues while retrieving diagnostic data from the DMVs. However, the overall impact of running this reports on a SQL Server instance is very minimal as compared to any other performance diagnostic tool.

Can these reports be used on SQL Azure or any other RDBMS?
No, they cannot be used to monitor SQL Azure databases or any other non-SQL Server RDMBS environment.

What permissions are required for using Performance Dashboard?
You need to have VIEW SERVER STATE permission to view all the information exposed by Performance Dashboard as most of the information is collected from DMVs.

Can these reports be deployed to a local shared repository?
You can use Visual Studio and create custom reports similar to the Performance Dashboard reports or modify the existing reports as per your needs and deploy them. Be advised, that this will be a customized solution and not supported by CSS w.r.t. performance issues while running these reports or configuration issues.

Lastly, thank you for attending the session! And for your patience.

In case there are more questions, please feel free to add a comment and I shall reply back accordingly.



Webcast: Understanding Performance Bottlenecks using Performance Dashboard

I will be delivering a webcast on “Understanding Performance Bottlenecks using Performance Dashboard”. The idea of this is webcast is demonstrate the use of tools offered by Microsoft to analyze performance bottlenecks when the issue is happening. This will be a good folow-up on the recent write-up that I did for SSWUG on tackling SQL Performance issues using tools provided by Microsoft.

Time: 2:30PM IST – 3:45PM IST
Date: 4th May, 2011

Here is the session abstract:

This session will demonstrate how to troubleshoot a SQL Server performance issue using out-of-the-box features without having to collect diagnostic data for post-mortem analysis.

Apart from this session, there are other sessions being delivering as part of this webcast series.

Managing and Optimizing Resources for SQL Server (Date: May 2, 2011 Time: 2:30 pm – 3:45 pm IST)

Optimizing and Tuning Full Text Search for SQL Server (Date: May 3, 2011 Time: 2:30 pm – 3:45 pm IST)

Cool Tools to have for SQL Server DBA (Date: May 5, 2011 Time: 2:30 pm – 3:45 pm IST)

Learn Underappreciated Features of SQL Server to Improve Productivity (Date: May 6, 2011 Time: 2:30 pm – 3:45 pm IST)

If you are interested, here is the registration link for signing up: http://virtualtechdays.com/SQLServer2008R2/


SQL MEME Monday <= 11 words

I just came across this post by Thomas LaRock [Blog | Twitter] regarding a blog post on SQL Server which has 11 words or less. So here is my less than 11 words of wisdom:

Document unique solutions so that others can re-use it!


Pinal Dave [Blog | Twitter]

Pradeep Adiga [Blog | Twitter]

Argenis Fernandez [Blog | Twitter]

Technorati Tags:

Wiki: Demystifying SQL Server Exception Dumps

Recently, I had replied to a MSDN post regarding an Access Violation reported by a SQL Server instance. In this post, I shall elucidate on troubleshooting memory dumps generated by SQL Server exceptions and what can be done without the availability of private symbols for debugging.

I had written a post earlier on when a memory dump is generated in SQL Server. If a single Errorlog has multiple stack dumps from multiple exceptions, then the first thing to do is to investigate the first access violation reported in the series of dumps reported. It is also possible that an exception (eg:access violation) could be followed by another exception/assertion or a non-yielding condition which may be a side effect of the earlier access violation.

When SQL encounters an access violation, there will be  SQLDumpXXXX.mdmp, SQLDumpXXXX.txt and SQLDumpXXXX.log files created in the LOG folder associated with the exception. The SQL Errorlog will report the occurrence of the exception as well.

  1. The .mdmp file is the memory dump which would be required by CSS to troubleshoot the issue.
  2. The .txt file is the symptom dump file which contains environment information along with other information depending on the nature of the exception.
  3. The .log file contains an extract from the SQL Errorlog when the exception was encountered. This helps in looking at what messages were reported in the Errorlog prior to the issue. Helps during post-mortem analysis if the current Errorlogs have already been recycled.

There can be two types of exceptions: one that is fatal enough to cause a SQL service termination or one that encounters the exception which terminates the thread which was encountered the issue.

Now what can be done in such a situation without having to look into memory dump file. The first step would be to check if the issue is a recurring behavior due to a particular operation to establish some pattern or if there is not identifiable pattern at all. If you look into the SQL Errorlog, you might be able to identify the input buffer which cause the exception (unless it’s an exception which terminated the SQL service, one such example is shown below).

Server * spid 5728
Server * ex_handle_except encountered exception C0000005 – Server terminating

If the above snippet contains the T-SQL query which raised the exception, then you would want to execute the same query from a Management Studio query window and check if the exception re-occurs. If yes, then you have a valid repro scenario.

Example snippet from an Errorlog where the Access Violation didn’t cause a service termination:

*   Exception Address = 0042B866
*   Exception Code    = c0000005 EXCEPTION_ACCESS_VIOLATION
*   Access Violation occurred writing address 67192000
* Input Buffer 38 bytes –
*  exec sp_updatestats

Next check if you can spot any external components loaded into SQL Server address space. I shall revert back to my MSDN post where I had spotted an anti-virus filter DLL loaded in SQL Server process address space.

Server * bcrypt 000007FEFC900000 000007FEFC921FFF 00022000
Server * PCTLsp64 000000000B1E0000 000000000B251FFF 00072000
Server * mswsock 000007FEFC840000 000007FEFC893FFF 00054000

Based on a few Bing searches, I was able to determine that the PCTLsp64 was part of a virus monitoring program from PC Tools. The next step would be to check if this external component can be moved out of SQL Server process address space. The fact that the DLL is loaded in SQL process address space doesn’t make the DLL a culprit. However, when troubleshooting such issues, it is important to eliminate all unknown or third party elements that could prevent us from narrowing down to the culprit. Other DLLs which can be commonly found in SQL Server process address space would be provider DLLs when linked servers are being used on the SQL Server instance. Additionally execution of Extended Stored Procedures (XSPs) could also load external DLLs into SQL Server process address space depending on the way the XSP DLL was coded. An easy way to identifying a XSP dll is searching for the word “using” in the SQL Server Errorlog. SQL Server reports the first instance of a XSP dll being loaded into SQL Server process address space.

Using ‘xpstar.dll’ version ‘2009.100.1600’ to execute extended stored procedure ‘xp_readerrorlog’

If you do find an external component loaded in SQL process address space, check the callstack reported in the Errorlog below the list of modules printed below to determine if the functions of the exception stack are associated with external component that you identified. For the issue reported in the MSDN post, I did find this:


Now this makes my story a bit more compelling of removing the external component out of SQL Server process address space. Once that is done and if you have a repro condition which causes the issue, then it would be good to re-run your repro and check if the issue is actually resolved. Not all the exceptions are the same even though they are reported in the same manner.

If all the above hasn’t helped in narrowing down the issue, then check if you are on the latest available cumulative update for the version of  SQL Server that you are using. If not, then it might be a good idea to update your instance to the latest available build to ensure that you are not hitting a known issue. If this is possible, then apply the latest cumulative update package and check if the issue re-occurs. I am cognizant of the fact that this might be an easy option due to change management restrictions on production environments. For such a dependency, my recommendation would be engage CSS to assist with a thorough investigation of the root cause of the issue.

As you can see from the steps outline above, you can look into a few things before you need to start poking into the exception memory dump using a Debugging tool.

Happy debugging!