SQLTech PostsSQL Server Diagnostics Best Practices March 11, 20162369 views0Share Performance Tuning By IG ShareTable of ContentsNote: If you buy something from our links, we might earn a commission. See our disclosure statement. SQL Server Diagnostics Best PracticesPerfmonEvent LogsError logsCluster LogsSystem Health SessionRing buffers Extended EventsSQL Server Diagnostics Best PracticesI 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.PssdiagPSSDIAG 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 logsSQL Profiler tracesSQL Server blocking script outputWindows Event LogsSQLDIAG outputYou 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.SqldumperSqldumper.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 ServernumberSharedYou can find more information on the Microsoft site.XperfPerformance Analyser provides many different graphical views of trace data including:CPU SamplingCPU and Disk utilization by process and threadInterrupt service routine and deferred procedure callHard faultsDisk I/O DetailDownload Location: Windows Performance Toolkit – XperfPerfmonIn-built tool in Windows Server. See my Performance Tuning Post for detailed description.Event LogsSee Microsoft site for detailed information on Event Logs.Error logsThe 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 LogsYou 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 SessionThe 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 buffersIt 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 EventsIntroduced 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. Affiliate Disclosure: Faceofit.com is a participant in the Amazon Services LLC Associates Program. As an Amazon Associate we earn from qualifying purchases. Share What's your reaction? Excited 0 Happy 0 In Love 0 Not Sure 0 Silly 0
SQLSQL Server Disk Configuration SAN SSD and Partitioning Best PracticesSQL Server Disk configuration settings are one of the most important aspects of SQL Server ...
SQLSystem Center Deployment Requirements and Restrictions for SQL ServerSystem Center contains a suite of products and enterprises who have investments in them deploy ...
SQLHow To License Your SQL Server on Google CloudIn my previous post, we deployed SQL Server 2016 on a Windows Server 2012 R2 Operating ...
Enterprise TechHow to Run Microsoft SQL Server 2016 on Google Cloud Step by StepGoogle Cloud Services recently added Microsoft SQL Server to its offerings. Google’s move is primarily ...