How to store performance counters directly on SQL server using TypePerf Command
IntroductionEvery DBA should know that the baseline is one of the most important pieces to make a proactive troubleshoot to discover potential problems in your SQL Server environment. The baseline is a key point to measure the instance performance by capturing the data in regular intervals over time to establish a trendline and a pattern of your SQL Server instances. Without a proper baseline, it’s hard to say when and why the problem occurred. You also lose the ability to look over a period and analyze if a problem is really a problem, or if it is a normal environment behavior.
Baseline ValueHere is one of the best examples to describe this – A customer opens a ticket informing that the SQL Server is running slow, and he needs to find the root cause of the problem because it’s impacting his business continuity. Before we dig into the investigation, there are some questions that should come to your mind, such as:
- Have these issues been reported before?
- Do I have any useful information to guide me into this?
- Do I have a baseline established to verify the performance over a period and find why this came out now?
- Ad-Hoc query that is using all the available resources of the SQL Server machine.
- The developer team that changed a procedure code and now the query plan is causing more CXPACKET usage.
- The SQL Server that is sitting on a virtual machine that is using dynamic memory allocation and because of this is draining the memory.
- The primary site went down, and the DR site doesn’t have the same machine resources than the primary node has.
- The Database Administrator changed the compatibility mode of SQL Server from 2012 to 2014 and the new cost estimator for a particular plan is not behaving accordingly.
- One of the systems started to receive more incoming requests than normal. (i.e. Christmas and Black Friday)
- PAL – Performance Analysis of Log - https://straightpathsql.com/archives/2016/07/create-sql-server-baseline/
- Custom Scripts – DMV’s, DMF’s and Procedures - https://www.sqlskills.com/blogs/erin/sql-server-baselines-series-on-sqlservercentral-com/
TypePerf CommandThat’s when the TypePerf command come into play, to help us Database Administrators to store and maintain performance counter information directly to SQL Server. Now there is no more need to save this information on a .xlx file and mark to import using BCP or SSIS or even using RELOG to put together the .blg and then import to SQL Server. Since Windows Server 2008 we have had this command available for use, and to be quite honest I was using an old school solution - too bad for me. A comment about scalability - I tested this solution in some clients, and at this moment I did not see any considerable bottleneck or latency on the capture process. Of course, this depends on your environment and the interval of your collection.
Building SolutionFirst and foremost let’s create the database that will be used to store the performance counter data.
- Creating Database Named – PerfMonBaseline
- Creating Views – vw_PerfMonAnalysisByServerperDay and vw_PerfMonAnalysisByserverperMinute
- SI = Sample Interval
- SC = Samples