SQLTech Posts SQL Server Diagnostics Best Practices March 11, 2016823 views0 Share SQL Server Diagnostics Best Practices I have already spoken about Free SQL Server tools for analysing SQL Server performance in a previous post. In this post, I am going to talk about a little advanced tools for diagnostics of SQL Server internals. Pssdiag PSSDIAG is primarily use by Microsoft SQL Server support team. It is a general purpose diagnostic collection utility that Microsoft Product Support Services uses to collect logs and data files. With PSSDIAG you can natively collect the following data: Performance Monitor logs SQL Profiler traces SQL Server blocking script output Windows Event Logs SQLDIAG output You can customize the data collection by enabling or disabling any of the log types, by changing the sample interval of the blocking script and the Performance Monitor logs, and by modifying the specific events and counters for SQL Profiler and Performance Monitor to capture. PSSDIAG can also run custom utilities or custom Transact-SQL scripts for support cases that require data outside the natively supported diagnostic types. Download Location: http://diagmanager.codeplex.com/ Use the Codeplex download for Microsoft SQL Server 2005 and later versions. Sqldumper Sqldumper.exe utility is already included with Microsoft SQL Server 2005, Microsoft SQL Server 2008, Microsoft SQL Server 2008 R2, and Microsoft SQL Server 2012. Use the following path to find the application. SQLServerInstallDrive:Program FilesMicrosoft SQL ServernumberShared You can find more information on the Microsoft site. Xperf Performance Analyser provides many different graphical views of trace data including: CPU Sampling CPU and Disk utilization by process and thread Interrupt service routine and deferred procedure call Hard faults Disk I/O Detail Download Location: Windows Performance Toolkit – Xperf Perfmon In-built tool in Windows Server. See my Performance Tuning Post for detailed description. Event Logs See Microsoft site for detailed information on Event Logs. Error logs The SQL Server error log can be found using the Management Studio GUI tool. It contains user-defined events and system events. Error logs can be used to troubleshoot problems related to SQL Server. More information on MSDN. Cluster Logs You need to use the command prompt in Windows to generate the log. Type cluster log /g and hit enter. Passing the parameter g allows you to generate the log. You can find the file named cluster.log which will be generated and stored in the %windir%ClusterReports directory on each node of the cluster. System Health Session The system_health session is a default Extended Events session with SQL Server 2008 onwards. This session starts automatically when the SQL Server Database Engine starts, and is a lightweight application which runs without any noticeable performance effects. More on MSDN. Ring buffers It is a dynamic management object (DMO) sys.dm_os_ring_buffers It is one of the best way to help you get quick and accurate determination about SQL Server memory allocation. If you have memory problems this will tell you whether they are coming from the Windows operating system or from SQL Server. Example: SELECT * FROM sys.dm_os_ring_buffers AS Memallocation; Extended Events Introduced with SQL Server 2008 without a GUI, and in SQL Server 2012 with a GUI. It is a lightweight application in-built into SQL Server Management studio. Find out more here. Disclaimer: The Questions and Answers provided on https://www.gigxp.com are for general information purposes only. We make no representations or warranties of any kind, express or implied, about the completeness, accuracy, reliability, suitability or availability with respect to the website or the information, products, services, or related graphics contained on the website for any purpose. Share What's your reaction? Excited 0 Happy 0 In Love 0 Not Sure 0 Silly 0 IG Website Twitter
Tech Posts How to Change Notification Settings on Google Drive on iOS Android & Windows By IGJune 7, 2021
Tech Posts How to Export MBOX File Format from Gmail and Import into Outlook 365 By IGSeptember 21, 2019
TSQL How to Effectively split a string using STRING_SPLIT function in SQL server Even though it’s flashy and glamorous being a Data Engineer these days, it comes with ...
Azure Azure SQL Managed Instance General Purpose vs Business Critical In this article, I will try to compare the Azure SQL Managed Instance General Purpose ...
Azure Azure SQL DTU to vCore Migration Steps for PAAS Databases on Cloud In this article, we are going to talk about Azure SQL DTU to vCore Migration. ...
Azure How To Change Azure SQL DB MAXDOP Settings Using T-SQL Command In this article, we are going to discuss how to change Azure SQL db MAXDOP ...