So SQL Diagnostic Manager for SQL Server collects a wide variety of Windows metrics, SQL Server metrics, and also virtualization metrics either from VMware or Hyper-V. Okay so if you wanted to collect additional information outside of what the SQL Diagnostic Manager for SQL Server already collects then you can create what is known as custom counters. So we can create a variety of custom counters. In our administration section, we navigate to custom counters. You can see that I have three already created. One for a virtualization metric. In this case, I am using VMware. I want to track my VMware overhead. I also have a Windows system counter that tracks the total number of reporting services reports created. And I also have a SQL system counter tracking the number of cache to cursor counts. Now if you for any of these you can custom counters. If you add additional SQL Servers to be monitored to within SQL Diagnostics SQL Server you can link them up either here or by right-clicking on your appropriate SQL Server instance and go to Properties. And there is a custom counters tab for you to add that. Add the appropriate custom counters to that SQL Server. Okay, now we are going to create a new custom counter based off of a SQL script. Okay, so that is a single numerical value from a query from your appropriate instance here now. Instead of typing in this in freehand what I like to do is actually create my query in Management Studio to make sure that it works. And in this case, I am tracking today is a number of Newsfeed posts here. So I am just going to copy this query into my SQL script dialog here. Look next now we can either use the collected value from that query or we can create a calculate a per second night collection between your most recent collection. And calculate the difference between the two. And then calculate a per second calculation. Since the last collection. In this case, we are just going to use the last collected value. Your scale factor may be different from one in the case that you are collecting. A metric that is very large maybe it is a number of bytes. And you want to convert it to megabytes or gigabytes. In the case of megabytes, you will want to [you know] scale that down to point zero one. We are going to leave this as scale factor one. Regardless of what you select here, you will always want to test this metric. My motto is to test and test again. So this metric actually only applies to one of my instances here. So I click Test. And indeed that looks correct. So I am going to click Next here you need to give it a name here. So I am going to give it a number of Newsfeed posts. Click Next. You can select whether higher values are worse than lower values or vice-versa. In this case, this value is [you know] if the number in […] posts is higher then that actually happens to be a worse value. In the case, it is like a metric similar to page life expectancy obviously lower values are worse than higher numbers. Okay, so I am going to give this [you know] thresholds here. So maybe if my informational threshold is fifty my warning threshold is a hundred and my critical threshold is two hundred and fifty. If you click on Advanced here. If SQL Diagnostic Manager fails to collect this metric for whatever reason. Maybe [you know] there is [you know] your database is down on your monitored instance for some reason that you are pulling this data from then we can generate an alert. Here this is really just something that is informational here more than anything. So I am just going to choose not to generate an alert. But you do have the option to generate an alert if you cannot create collect this custom counter information here. Now you can also enable this counter alert by default to alert on these thresholds here. Alright, so I am going to leave this checked. You can uncheck it to not. And enable these specials or this counter alert to fire off if you breach any of these thresholds. Okay click Next here and Finish. And really that easy now here you are offered the ability to link this new custom counter to particular instances. Right now you can link it to a group of instances. Here, in this case, this only applies to my SQL Server 2012 named instance. So I will just click Add Here and click OK. Alright so if we go to our servers area. And select our SQL Server 2012 instance. And then go up to details. We can actually view the data that is being collected for that particular custom counter. And I can filter this list. This is a list of all the metrics including custom counters that SQL Server for SQL Diagnostic Manager is currently collecting. So if I click on custom counters just to kind of filter that. Right, I can see the number of Newsfeed posts has gone over one of my thresholds. Here I can actually graph it if I click on this little checkbox here. Now we have just started collecting data here. So we only have two data points right now. But you can see that we are already collecting data here. All right. So I hope you enjoyed this video. And have a great day.
Topics : Database Diagnostics,Database Monitoring,Database Performance,SQL Query Performance,
Products : SQL Diagnostic Manager for SQL Server,