Collecting Performance Data into a SQL Server Table

Occasionally, when tracking down a performance problem, you have to have information over time on the values of particular sets of performance counters. To track down the more insidious or sporadic problem, it is best to have the data in a SQL Server table, so you can query it. Feodor Georgiev explains the command-line way of doing this.

It has been about 4 years since Brent Ozar posted his famous blog post on collecting performance counters. This information turned out to be extremely useful for both accidental and professional DBAs.  About 4 years later I ran into  Jonathan Allen’s article (Getting baseline and performance stats – the easy way.), which is an upgrade to Brent’s blog. Jonathan offers a slightly more sophisticated way of running the Perfmon process from a command line. By appending the proper parameters, this method does speed things up.

In this article, I would like to build on what Brent and Jonathan have written to  propose an even more flexible method for SQL Server performance data collection.

 typeperf.exe: Command-line performance-data collection

As Jonathan Allen mentions in his blog, typeperf.exe is a powerful command. Here is a screenshot of all parameters the command accepts and their short description (as the output of ‘typeperf.exe /?‘ would show them):

1223-clip_image002.jpg

As we can see, there are several options which allow us to save the output of the typeperf in different formats: CSV, TSV, BIN, SQL. (CSV = Comma Separated file, TSV = Tab Separated file, BIN = Binary file, SQL = SQL Server table)

And here is the moment when I start thinking about my preferred choice of format.

As a DBA, I do not like CSV much, unless I really need to export some trivial  data and email it to someone. With CSV there is also a security risk, since it is nothing but a text file saved on the file system; same goes for the TSV and the BIN formats.

Furthermore, the processing times are significant, since it is a two-step process: first we would have to wait for the counters to collect into the file, and then we would have to open them and manipulate the data so that we extract what interests us.

Now, wouldn’t it be great if we could have the performance data collected directly into our already secured SQL Server? (I talk about security because I can personally think of at least a few scenarios where even performance data in the wrong hands can cause a lot of trouble.)

Furthermore, if we could import our performance counters to SQL Server database, that would mean that we can query the data any time and we can write reusable code for the queries which will help us easily analyze data over and over again. It will also help us detect events, patterns, send notifications, if we wanted.

So, to get back on track: my choice for the performance data collection output is SQL.

How to collect performance data directly to SQL Server:

First, of course we need to set up a database which will contain our performance data.

For this exercise we will create a new database called ‘PerfmonCollector‘ by using the following script:

Second we would need to connect Typeperf to SQL Server. Let’s run the ODBC Data Source Administrator (we can access it by clicking Run… and then ‘odbcad32.exe’).

The following screen will be presented:

1223-clip_image003.jpg

In the tabs of the administrator we see ‘User DSN’ and ‘System DSN’. The difference is that the User DSN is visible only to the current user, and the System DSN is visible to all users of the machine, including the NT services.

So, let’s choose a ‘User DSN’, since we do not want anyone but us to access our database. Let’s create it:

1.       Click Add… button and select SQL Server driver type

1223-clip_image004.jpg

2.       Click ‘Finish’. A Data Source wizard screen will show up:

1223-clip_image005.jpg

3.       Fill in the name of the Data Source and the SQL Server instance name. In my case, I would go for ‘SQLServerDS’ and (local).

4.       In the next screen we would have to provide the login details:

1223-clip_image006.jpg

I will use Windows authentication. Click next.

5.       In this screen it is important to select our ‘PerfmonCollector‘ database:

1223-clip_image007.jpg

Click Next.

6.       In this screen we would leave the settings as default:

1223-clip_image008.jpg

Click Finish.

7.       In this screen you will be presented with an overview of the settings and with a chance to test our connection.

1223-clip_image009.jpg

Click the ‘Test Data Source…’ and make sure that the test is successful.

1223-clip_image010.jpg

Now that we have a database and a connection, the next step is to gather some counters and to save the results into our database.

Collecting the counters:

Let’s say that we want to collect the values from the counters mentioned in Jonathan Allen’s  blog post:

  • Memory – Available MBytes
  • Paging File – % Usage
  • Physical Disk – % Disk Time
  • Physical Disk – Avg. Disk Queue Length
  • Physical Disk – Avg. Disk sec/Read
  • Physical Disk – Avg. Disk sec/Write
  • Physical Disk – Disk Reads/sec
  • Physical Disk – Disk Writes/sec
  • Processor – % Processor Time
  • SQLServer:Buffer Manager – Buffer cache hit ratio
  • SQLServer:Buffer Manager – Page life expectancy
  • SQLServer:General Statistics – User Connections
  • SQLServer:Memory Manager – Memory Grants Pending
  • System – Processor Queue Length

What we need to do is create a text file on our file system, which contains the counters we need to collect. Keep in mind that there are 2 kinds of counters – machine counters and SQL Server specific counters. So if we have only one default instance of SQL Server on a machine and we would like to collect the performance counters, our text file will look like this:

\Memory\Available MBytes
\Paging File(_Total)\% Usage
\PhysicalDisk(* *)\% Disk Time
\PhysicalDisk(* *)\Avg. Disk Queue Length
\PhysicalDisk(* *)\Avg. Disk sec/Read
\PhysicalDisk(* *)\Avg. Disk sec/Write
\PhysicalDisk(* *)\Disk Reads/sec
\PhysicalDisk(* *)\Disk Writes/sec
\Processor(*)\% Processor Time
\SQLServer:Buffer Manager\Buffer cache hit ratio
\SQLServer:Buffer Manager\Page life expectancy
\SQLServer:General Statistics\User Connections
\SQLServer:Memory Manager\Memory Grants Pending
\System\Processor Queue Length

It is a bit more complicated with the named instances of SQL Server. The text file containing the counters for a named instance would look like this:

\Memory\Available MBytes
\Paging File(_Total)\% Usage
\PhysicalDisk(* *)\% Disk Time
\PhysicalDisk(* *)\Avg. Disk Queue Length
\PhysicalDisk(* *)\Avg. Disk sec/Read
\PhysicalDisk(* *)\Avg. Disk sec/Write
\PhysicalDisk(* *)\Disk Reads/sec
\PhysicalDisk(* *)\Disk Writes/sec
\Processor(*)\% Processor Time
\MSSQL$InstanceName:Buffer Manager\Buffer cache hit ratio
\MSSQL$ InstanceName:Buffer Manager\Page life expectancy
\MSSQL$ InstanceName:General Statistics\User Connections
\MSSQL$ InstanceName:Memory Manager\Memory Grants Pending
\System\Processor Queue Length

As you can see, in the case of a named instance, we would have to manually edit the text file and input the name of the instance for which we need to collect counters.

Depending on how many servers we have and how many instances of SQL Server reside on one physical machine, we would group our text files accordingly.

Let’s say that we have one physical server and 4 SQL Server instances; in this case I would create one text file containing the counters for the physical server (including the counters for the default instance) and then create 3 more files containing only the named instances’ counters.

For this article, however, I would collect performance data only from my named instance (the name of my instance is ‘SQL2005’) and my server.

So, I will create a folder ‘CounterCollect’ in my C: drive, and in the folder I will place my ‘counters.txt’ file containing my list of counters as follows:

\Memory\Available MBytes
\Paging File(_Total)\% Usage
\PhysicalDisk(* *)\% Disk Time
\PhysicalDisk(* *)\Avg. Disk Queue Length
\PhysicalDisk(* *)\Avg. Disk sec/Read
\PhysicalDisk(* *)\Avg. Disk sec/Write
\PhysicalDisk(* *)\Disk Reads/sec
\PhysicalDisk(* *)\Disk Writes/sec
\Processor(*)\% Processor Time
\MSSQL$SQL2005:Buffer Manager\Buffer cache hit ratio
\MSSQL$ SQL2005:Buffer Manager\Page life expectancy
\MSSQL$ SQL2005:General Statistics\User Connections
\MSSQL$SQL2005:Memory Manager\Memory Grants Pending
\System\Processor Queue Length

And now comes the most interesting part: running the cmd command which will start our data collection:

Here is a short explanation of the parameters:

  • ‘f’ is the output file format
  • s‘ is the server from which we would like to collect counters
  • cf’ is the path to the text file which contains the counters
  • si‘ is a sampling interval, in this case every 15 seconds
  • ‘o’ is the path to the output file, or in this case it is specifying the DSN we created earlier
  • ‘sc’ is how many samples to collect, in this case 4, which means that the process typeperf will run for 1 minute and will collect 4 samples.

As you notice, there is a '!log1' after the DSN name. This is a way to give a name to our performance data collection set. For example, instead of 'log1‘ we could put ‘beforeCodeRelease'.

Note: do not be surprised if your first sample is sometimes 0. This is how typeperf works. This is because typeperf is getting the delta (the value difference) between the sampled intervals.

The results:

Let’s look at our ‘PerfmonCollector’ database.

We can notice that there are 3 new tables in our database, which were created by the typeperf:

Here is how part of the CounterData table looks:

1223-clip_image012.jpg

 

What do they contain? Here is some information from MSDN:

The CounterData table contains a row for each counter that is collected at a particular time. There will be a large number of these rows.

The CounterData table defines the following fields:

  • GUID: GUID for this data set. Use this key to join with the DisplayToID table.
  • CounterID: Identifies the counter. Use this key to join with the CounterDetails  table.
  • RecordIndex: The sample index for a specific counter identifier and collection GUID. The value increases for each successive sample in this log file.
  • CounterDateTime: The time the collection was started, in UTC time.
  • CounterValue: The formatted value of the counter. This value may be zero for the first record if the counter requires two sample to compute a displayable value.
  • FirstValueA: Combine this 32-bit value with the value of FirstValueB to create the FirstValue member of PDH_RAW_COUNTER. FirstValueA contains the low order bits.
  • FirstValueB: Combine this 32-bit value with the value of FirstValueA to create the FirstValue member of PDH_RAW_COUNTER. FirstValueB contains the high order bits.
  • SecondValueA: Combine this 32-bit value with the value of SecondValueB to create the SecondValue member of PDH_RAW_COUNTER. SecondValueA contains the low order bits.
  • SecondValueB: Combine this 32-bit value with the value of SecondValueA to create the SecondValue member of PDH_RAW_COUNTER. SecondValueB contains the high order bits.

Information about the rest of the tables can be obtained from MSDN as well: DisplayToID ) ( and CounterDetails )

So, we have the data, let’s use it!

As I mentioned earlier, this method of collecting performance data is not only more secure than CSV+Excell, but also is more flexible. Remember, as we defined earlier our Perfmon collector command, we gave a name to our collector set. In this case we named it simply log1. For a real hands-on performance tuning sessions, though, we would like to name every set with its own meaningful name. (For example, let’s say that we would like to measure the server’s performance between 10am and 11am every day, when we are running a specific batch job.)

The name of the collector set is found in the DisplayToID table, in the DisplayString column. There we also see the LogStartTime and LogStopTime. The DisplayToID table is joined to the CounterData table by the GUID.

 For my test case in this article I am using two data collector sets called log1 and log2. Both sets are using the same counters as mentioned above.

 The first thing we would like to do is to verify how many different servers we have collected the data from. By running this query we can check:

In my case I would get only one server: \\ALF.

 Now let’s check what data collection sets we have and what their start and end times are:

Here is the result:

 1223-clip_image013.jpg

 Now let’s check the values we have collected for a specific counter for a specific server:

This query will return the Processor Total % utilization time as well as the counter collection time and the collector set name. Feel free to use this query as a template for exploring other counters as well.

 1223-clip_image014.jpg

And here is one more query which will give some aggregations:

Here is the result and as you can see it is quite easy to compare the two data collector sets.

 1223-clip_image016.jpg

From this point on, I am sure that any DBA would be able to easily write queries and find out performance events, patterns and tendencies.

Summary:

In this article I describe a flexible and secure method for collecting data from the collection of performance counters from servers and SQL Server instances. This method avoids the limitations of Excel spreadsheets, and brings great possibilities to the DBA to query the data directly, so as to home in on the cause of  performance problems (or the lack of them, hopefully!) in the monitored systems.

Tags: , ,

  • 72362 views

  • Rate
    [Total: 117    Average: 4.6/5]
  • Abel

    A collecting performance data batch file for more than 100 computers
    Great job! Thanks!

    Following is my feedback! ^_^

    1. Edit server.txt

    SERVER01 user01 password01
    SERVER02 user02 password02

    more than 100 computers ….

    or

    dsquery computer -limit 10000 > computers.txt

    to fetch domain computers name and do some modify to fit your need …

    2. command tools
    grep.exe
    sed.exe

    3. Edit DSN (ODBC)

    Create a database name: PViewer
    Create a DSN name to mapping your PViewer Database : PViewer

    3. FetchPerfData.bat

    @echo off
    REM —————————————-
    REM Set environment variable
    REM —————————————-
    setlocal enableextensions
    rem setlocal enabledelayedexpansion
    set title=%~n0
    REM —————————————-

    REM —————————————-
    REM Set console title
    REM —————————————-
    title Generate Performance Counters and System Information
    REM —————————————-

    REM —————————————-
    REM Set path variable
    REM —————————————-
    path=%PATH%;C:Program FilesMicrosoft OfficeOffice12
    REM —————————————-

    REM —————————————-
    REM Set system date variable
    REM —————————————-
    for /f “tokens=1-5 delims=/ ” %%s in (“%date%”) do (set CurrDate=%%s%%t%%u)
    for /f “tokens=1-5 delims=:.” %%d in (“%time%”) do (set CurrTime=%%d%%e%%f)
    set sysdate=%CurrDate%_%CurrTime%
    REM —————————————-

    REM —————————————-
    REM Clear DNS record
    REM —————————————-
    ipconfig /flushdns
    REM —————————————-

    REM —————————————-
    REM Create work directory
    REM —————————————-
    if not exist “.Work” (mkdir .Work) else (del .Work* /F/Q)
    if not exist “.Counter” (mkdir .Counter) else (del .Counter* /F/Q)
    if not exist “.Output” (mkdir .Output) else (del .Output* /F/Q)
    REM —————————————-

    REM —————————————-
    REM Generating system information header
    REM —————————————-
    systeminfo /fo csv > .WorkHTemp.csv
    sed -e “1d” -e “3d” .WorkHTemp.csv > .WorkSTemp.csv
    REM —————————————-

    REM —————————————-
    REM Generate Performance Counters
    REM —————————————-
    for /f “tokens=1-3 delims= ” %%S in (Servers.txt) do (

    net use T: \%%Sc$ /user:%%T %%U

    start /wait typeperf -q Memory -s %%S -o .Counter%%S_Memory.cf
    start /wait typeperf -cf .Counter%%S_Memory.cf -si 1 -sc 5 -y -f SQL -o SQL:PViewer!%%S

    start /wait typeperf -q PhysicalDisk -s %%S -o .Counter%%S_PhysicalDisk.cf
    start /wait typeperf -cf .Counter%%S_PhysicalDisk.cf -si 1 -sc 5 -y -f SQL -o SQL:PViewer!%%S

    start /wait typeperf -q Processor -s %%S -o .Counter%%S_Processor.cf
    start /wait typeperf -cf .Counter%%S_Processor.cf -si 1 -sc 5 -y -f SQL -o SQL:PViewer!%%S

    start /wait typeperf -q “Network Interface” -s %%S -o .Counter%%S_Network.cf
    start /wait typeperf -cf .Counter%%S_Network.cf -si 1 -sc 5 -y -f SQL -o SQL:PViewer!%%S

    start /wait typeperf -cf .Counter%%S_Server.cf -si 1 -sc 5 -y -f SQL -o SQL:PViewer!%%S
    start /wait typeperf -q Server -s %%S -o .Counter%%S_Server.cf

    if “%%S”==”yourSQLServer” (
    start /wait typeperf -qx “sqlserver:databases” -s %%S -o .Counter%%S_Databases.cf
    start /wait typeperf -cf .Counter%%S_Databases.cf -si 1 -sc 5 -y -f SQL -o SQL:PViewer!%%S

    start /wait typeperf -qx “sqlserver:buffer manager” -s %%S -o .Counter%%S_Buffer.cf
    start /wait typeperf -cf .Counter%%S_Buffer.cf -si 1 -sc 5 -y -f SQL -o SQL:PViewer!%%S
    )

    systeminfo -s \%%S -U %%T -P %%U /fo csv /nh >> .WorkSTemp.csv
    net use T: /delete
    )
    REM —————————————-

    REM —————————————-
    REM Generate System information
    REM —————————————-
    grep -v “^$” .WorkSTemp.csv > .Outputsysteminfo.csv
    REM —————————————-

    REM —————————————-
    REM Delete Work files
    REM —————————————-
    del .WorkHTemp.csv /Q/F
    del .WorkSTemp.csv /Q/F
    REM —————————————-

  • Anonymous

    Performance metrics – monitor and capture
    Looks great article. Need to implment and see how it is useful before grading the article as to its validity.

  • Matt

    problem
    I really like the look of this as a performance monitoring tool, however after a brief play I couldn’t get the typeperf command to run. The error I got was Error: no valid counters.

    What should I check?

  • sibir1us

    problem
    Hello Matt, I believe this is a problem with the characters when copy-pasted from the site. Please type in manually the command:
    TYPEPERF -f SQL -s ALF -cf “C:CounterCollectCounters.txt” -si 15 -o SQL:SQLServerDS!log1 -sc 4

    Feodor

  • sql-troubles

    Data Collector
    Starting with SQL Server 2008 you can also use the Data Collector (refer to Warehouse Data Management).

  • Anonymous

    Data Collector
    The data collector, aside from being tied to SQL 2008 and up, is pretty clumsy. Yes, you do have some reports, however, it takes a serious setup, especially if you have a set of servers, and you want to collect the data to a cetral location. Again, as said earlier, if you have SQL 2000 and 2005 – the Management Data Warehouse is a partial failure.

  • peturgretars

    Statistics reports?
    Great job and thanks for sharing this. Now I have already implemented this for one of our databases.

    You don’t happen to have some additional scripts to share where you e.g. summarize into a report the total result of a collect set(DisplayString) or where you compare two collect set against each other?

    Petur

  • chandan_jha18

    long term collection
    Thanks for the nice article.I find it better than using data collection feature in sql 2008.

    I have a question.I wished to collect the data for as long as i want,so i did not use -sc parameters which will restrict the number of samples.Does it mean it will keep collecting the data as long as it can?

    Additionally,how to pause it for a couple of days and resume again

  • chandan_jha18

    sorry
    Sorry for duplicate posts! somehow it happened and i have no clue how to delete it 🙁

  • chandan_jha18

    solution to my problem
    I could not play with parameters.So i scheduled an SQL job to run as long as i want and it executes the commands above through command line interface.It gives me flexibility to capture it as long as i want.

  • sibir1us

    long term collection
    Actually, if you run the collection once, you will notice that there is a typeperf.exe process started in the Windows task manager. Depending on your collection settings, this process will be running on your system. What this process does is it resides on the host machine and ‘pulls’ data from the machine which you have specified in your data collection settings. I would recommend terminating the process before starting again the same collection. Read this article on how to terminate the process: http://feodorgeorgiev.com/blog/2010/05/how-to-terminate-processes-in-windows/

  • joeller

    Cannot connect to OBDC Datasource Name
    Seems to be an issue connecting to the ODBC data source. used “!” after name like you did on post. Then thinking “!” might be mistype did it without the “!”. Finally tried it without giving a name to the collection data set, using only Data Source Name. All failed. Then realized that there were no spaces between “!” and words on either side and ran successfully. How come the command would not recognize Data Source name without collection data set name?

  • sibir1us

    Cannot connect to OBDC Datasource Name
    Edward,

    as I mention in the article:

    “As you notice, there is a ‘!log1’ after the DSN name. This is a way to give a name to our performance data collection set. For example, instead of ‘log1’ we could put ‘beforeCodeRelease’.”

    Now I realize, that maybe I should have explained a bit more in detail, so here it is:

    If you look at the [dbo].[DisplayToID] table in your data collection you will notice, that the string you entered after the ‘!’ sign is saved in the [DisplayString] column, together with other properties of the performance collection session.
    This is done in order to give you flexibility to collect different sessions and give them proper human readable names and use this information later on in your analysis and session tracking.

    In short, this functionality is needed so you can easily track what sessions you have run and so you can distinguish them by the name you have given them, not only GUID or timestamps.

    I hope this answers your question. If you have other specific questions on the topic do not hesitate to send me an email.

    Feodor

    http://sqlconcept.com/2011/05/18/dba-using-perfmon-for-sql-performance-tuning/

  • SQL Linda

    Collecting hourly data for a week.
    Great article and great instructions! It worked the first time for me! I am confused on how to collect my statistics. If I want to monitor % Processor Time hourly for 1 week I am not sure how to set up my job.

    Before code release:
    typeperf ….-si 3600 -o SQL:SQLServerDS!BeforeCodeRelease -sc 168
    ??? how does this run for a week?
    ??? can i close the command prompt window?
    ??? can i stop the job?

    After code release:
    typeperf ….-si 3600 -o SQL:SQLServerDS!AfterCodeRelease -sc 168

    Thanks!

  • Berhan

    Great Performance data collection tool!!
    Awesome.

  • logicinside22

    Handy Tool
    I am working on this article and i am getting error says Error: No Valid counters.
    Any idea?
    Thans

  • sqlactions

    Extend the ‘reporting’ using powerpivot
    Nice article. To further extend the reporting by creating pivot charts, graphs etc, check out this blog:

    http://www.simple-talk.com/community/ConfirmEmailAddress.aspx?e=prashant@sqlactions.com&h=803761C46CD5DD92FA1DF1D5114F62B69456DC14

  • sqlactions

    Extend the ‘reporting’ using powerpivot
    Nice article. To further extend the reporting by creating pivot charts, graphs etc, check out this blog:

    http://sqlactions.com/2012/05/15/collection-and-reporting-of-perfmon-data-for-sql-server-capacity-planning-and-trend-analysis/

  • coolsub82

    Error: No valid counters
    I followed all the steps mentioned above. But when I tried to run typeperf command, it gave me invalid counter error message.

    Please suggest.

  • skan2dan

    Unable to collect performance data for SQLServer counters
    Configuration Windows Server 2008 with SQL Server 2008 Default instance.

    When I try to add the following counters, it throwing error message as : "Error: No Valid Counters"

    SQLServer:Buffer ManagerBuffer Cache Hit Ratio
    SQLServer:Buffer ManagerCheckpoint Pages/Sec
    SQLServer:Buffer ManagerPage Life Expectancy
    SQLServer:Buffer ManagerLazy Writes/Sec
    SQLServer:Memory ManagerMemory Grants Pending
    SQLServer:Memory ManagerTarget Server Memory
    SQLServer:Memory ManagerTotal Server Memory

    But, I am able to collect the data for other counters like
    Process(sqlservr)Private Bytes
    MemoryAvailable MBytes
    MemoryPage Faults/Sec
    MemoryPages Input/Sec
    MemoryPages/Sec
    MemoryPage Reads/Sec

    Could you please suggest to overcome this issue.

  • mkoetse

    no valid counters
    I was receiving the no valid counters error message. I changed the -s flag to use the ip address of my server instead of the server name. also double check what you have for the -o flag.

  • don_wrong

    Need Help
    I am getting the "no valid Counter" while running the code.

    Please help

  • don_wrong

    resolved
    hi all for "no valid counter" error please check the path correctly.

    Gaurav Kaushik

  • Amar009

    Change names of 3 Perfmon tables.
    Hi,
    Is there any way to change the names of the 3 tables created by Perfmon because I already have CounterData and CounterID tables with different schema being used by other processes.
    Thanks.

  • Amar009

    Change names of 3 Typeperf tables.
    Correction for the above. It’s "Typeperf" not "Perfmon".
    Hi,
    Is there any way to change the names of the 3 tables created by Typeperf because I already have CounterData pand CounterID tables with different schema being used by other processes.
    Thanks.

  • AlbertZOZ

    SQL way doesn’t work sometimes
    I have more than 20 servers running the typeperf per minute to collect performance counters to a central sql server database.

    The error like:
    "Call to SQLExecDirect failed with [Microsoft][ODBC SQL Server Driver]Communication link failure."
    or
    "Cannot alter CounterDetail table layout in SQL database."

    Restarting scheduled task sometimes work sometimes not.

    I am using "SQL Server" driver not the native one, the native one doesn’t work, found out all people using the classic driver.

    Anyone knows a stable solution?

  • smtzac

    getting error:No valid counters
    TYPEPERF -f SQL -s ALF -cf “C:CounterCollectCounters.txt” -si 15 -o SQL:SQLServerDS!log1 -sc 4

    What and where should I replace above?
    my server name: "ABCDsql_2012" and it’s window authentication

  • smtzac

    continue from above question…
    I created text file as:
    MemoryAvailable MBytes
    Paging File(_Total)% Usage
    PhysicalDisk(* *)% Disk Time
    PhysicalDisk(* *)Avg. Disk Queue Length
    PhysicalDisk(* *)Avg. Disk sec/Read
    PhysicalDisk(* *)Avg. Disk sec/Write
    PhysicalDisk(* *)Disk Reads/sec
    PhysicalDisk(* *)Disk Writes/sec
    Processor(*)% Processor Time
    MSSQL$SQL_2012:Buffer ManagerBuffer cache hit ratio
    MSSQL$SQL_2012:Buffer ManagerPage life expectancy
    MSSQL$SQL_2012:General StatisticsUser Connections
    MSSQL$SQL_2012:Memory ManagerMemory Grants Pending
    SystemProcessor Queue Length

  • SQL

    Very well written article. I am using TypePerf for baselining production servers about 100 servers. I am trying to run TypePerf remotely using PowerShell commands with bat files.When I run the command directly from a server, it’s works. But when I run it from PowerShell on a remote server its kicks me out with a login failure. Although, as a DBA I have full rights on the servers.

    $servercon = New-Object System.Data.SqlClient.SqlConnection
    $servercon.ConnectionString = “Data Source=”Utility server”;Initial Catalog=master;Integrated Security=true;”

    $servercmd = New-Object System.Data.SqlClient.SqlCommand
    $servercmd.Connection = $servercon

    $servercmd.CommandText = “SELECT ServerName FROM “database” where Environment =’PROD’ AND State = ‘Running’
    and servername = ‘P_DB’ ”
    $servercmd.CommandTimeout = 0

    Write-Warning $servercmd.CommandText
    try
    {
    $servercon.Open()
    $read = $servercmd.ExecuteReader()

    while ($read.Read())
    {
    $dbserverName = $read[“ServerName”]

    Write-Host $dbserverName

    ## Create a directory that will hold the config files in C drive on each prodcution server
    New-Item \$dbserverNamec$baseline -ItemType “Dir”

    ## Copy Setup files to C drive
    Copy-Item ‘\SWBC.LOCALadmins$SQLAdminsBaselineCounters.txt’ \$dbserverNamec$baseline #Counters info
    Copy-Item ‘\SWBC.LOCALadmins$SQLAdminsBaselineODBCdsn.bat’ \$dbserverNamec$baseline #Creates User DSN
    Copy-Item ‘\SWBC.LOCALadmins$SQLAdminsBaselineRunTypePerf.bat’ \$dbserverNamec$baseline #Calls TypePerf

    #Create User DSN
    invoke-command -ComputerName $dbserverName -ScriptBlock { &’c:baselineODBCdsn.bat’} -ArgumentList localhost
    #Run TyepPerf
    invoke-command -ComputerName $dbserverName -ScriptBlock { &’c:baselineRunTypePerf.bat’} -ArgumentList $dbserverName

    }
    }
    catch
    {
    Write-Warning “Read-Servers”
    Write-Warning $_
    }
    finally
    {

    $servercmd.Dispose()
    $servercon.Dispose()
    }

    Contents of the bat file are below:

    %WINDIR%System32odbcconf.exe CONFIGDSN “SQL Server” “DSN=TypePerf|Description=TypePerf|SERVER=D-DBUTIL3|Trusted_Connection=Yes|Database=TypePerf” – ODBCdsn.bat

    TYPEPERF -f SQL -cf “C:baselineCounters.txt” -si 15 -o SQL:TypePerf!log1 -sc 4 — RunTypePerf.bat

    This powershell script creates ODBC connection on each server but exits when running the TypePerf command with the error attached.

    https://uploads.disquscdn.com/images/2d8199198b92fb4d79ce1a432fd6db159b47c723bb31ccddcbbaf1de262f53f8.png

    On the error logs of D-DBUTIL3 server, I see login failures as below:
    Login failed for user ‘NT AUTHORITYANONYMOUS LOGON’. Reason: Could not find a login matching the name provided.

    Has someone had the same experience or is there something I am not doing right.