SQL Server DBA Tutorial 145-How to Check CPU % Usage by SQL Server

welcome to tech brothers in this video we're going to learn how to check CPU usage in percentage by sequel server in this demo we'll be learning how to check CPU utilization using task manager and that will tell us in percentage number two we'll be looking at a system dynamic view that'll tell us a little bit information about CPU utilization number three CPU utilization by process so first up here using task manager you have to remote into sequel server into the server aware sequence every instance is so let's say that this is the server that I'm in right now and I have a sequel service server instance running on this server so what I will do right click on the task bar and go to the task manager the first thing you will see that if you don't see the processes you need to add that column processes and you will see the sequel server process up here saying sequel server and right here is the instance if you have multiple instances of sequel server on a particular server that will show up right here under sequel server Windows NT and you can look at CPU utilization by just looking at the task manager as you can see right here is CPU utilization this is the total operating system utilization of the CPU and percentage which is 20% right here but we are more interested basically in finding out that how much is sequel server using so let me do it one more time so we're going to go ahead and remote into a server where sequel server instance is installed and go in task manager and you will see right here all the application you can sort by application and you can see that each application is using how much if you go all the way you will see the sequel server right here this is equal server agent and if you wanted to collectively look at sequel server and agent then you can add these two up but this is your sequel server instance right here and this is the column that CPU utilization is right currently appear on this system is 21 22 % CPU but the seeker isn't using much of CPU because we're not using sequence ever a whole lot but if sequence ever we'll be using let's go ahead and run just a report on sequel server and see that if it would bring our CPU up for sequel server as you can see that as soon as we ran the report it went 2.9 percent or 1 percent of CPU utilization so this is how you check CPU utilization in percentage used by a sequel server if you have multiple sequencer for instance as I said it'll appear right here all the instances right here in sequel server like in my case I have just only one sequel server instance which is sequel test and this is how you can basically use CPU utilization memory in percentage and disk i/o and network as well so we're going to go ahead and close this and we're going to go ahead and look assist CPU utilization using system dynamic view and I'll be just showing you that these are really important if you that many of us usually don't look at the dynamic views but I would encourage you to start using dynamic views they have a really nice information they have really great information basically sometimes but by just going in that view and exploring that view you will get the information that you're looking for so here are these are the system views we're going to go ahead and go in dynamic views right here it says DM so if you go in OS views right here this will give us a lot more detail as you can see that if you explore really these these are just phenomenal these are this contains really a great information so what we're interested right now is DMOS system info so we're going to go ahead and select everything from this dynamic view so let's see what we get right here we get CPU ticks and millisecond CPU ticks right here so if you I wanted to get the info in percentage you have to do some aggregation in order to get in percentage basically I just wanted to in in this video I just wanted to show you that if you explore in this dynamic view you will get information a lot more information about CPU than other any other up here if you wanted to look at the memory you can look at the memory up here you can also go ahead and join and create your own query call it a health query system health query and basically join these dynamic views and go ahead and look at the utilization the physical resource utilization on any server or any application residing on that particular server so we're going to go ahead and look at by the process it's really easy you can look at in the system dynamic view as well so we're going to go ahead and right click on the instance go to the reports and go to the standard reports as you can see right here we have a server dashboard server dashboard has a lot great information if you get a chance take a look on that and now we're looking for the CPU as you can see the top queries by total CPU time up here is top ways by average CPU time and up here is top queries by total IO so we're going to go ahead and first we're going to look at it comes under the performance so top queries by average CPU so it's going to give us the top queries and if you scroll down right here these are the queries that who ran and this is the CPU time in milliseconds right here so you can find from here that if this query ran this this is the query that who ate my CPU and we ran into performance issue so let's go back and look at another report so we're going to go ahead top ways by total CPU time right here and it gives about the same view as you can see right here it's a total CPU time instead of average CPU time you can look the average as well as as I showed so this is how you basically check by the processes a easy way very easy way to go in standard reports and you can also look at the scheduler health right here and you can go and it will tell you the scheduler ID up here and you can go in much more detail if you expand one of these you can expand the work threads underneath it and it will tell you what each thread how much CPU it consumed up here all the workers so basically the whole idea in this video is to make you realize that you can explore a lot more information but in the beginning that our main purpose of this video is to show you - how to find CPU utilization by sequel server in percentage if you wanted to look at that you can also go in sequence in the server and set up CPU counters and you can basically get the report from there if you go in system views I'll show you real quick dynamic OS counters so let's go ahead and pull this guy up so once we ran it run it right here the counter names all the counter names and you can go ahead and say we're comp counter name and you can basically get it just the CPU so we can get the counters just related to CPU as you can see right here so these are the great tips and tricks as well to look at the CPU utilization and basically physical resources of the server where sequel server instances running and consumption of those physical resources related to sequel server you can explore more so we went ahead and looked at utilization using task manager percent and we went ahead and look at a system dynamic view which is the OS system dynamic view and also CPU utilization by process by looking at the different reports on sequel server instance and I hope this video helped

Channel: TechBrothersIT

Views: 60,630

Rating: 4.7547894 out of 5

Keywords: How to Check CPU % Usage by SQL Server, SQL, Server, Developer, TSQL, DBA, DBA Tips, SQL Developer, SQL Server Interview Questions, TSQL Interview Questions and Answer, SQL Server 2014, SQL Server Advance Tutorial, SQL Server Tutorial for beginners, Tech Brothers, SQL Server 2014 DBA, SQL Server DBA tutorial for Beginners, SQL Data Administration Tutorial Step by Step, Database Administrator (Job Title), Microsoft SQL Server (Software), TechBrothersIt

Id: 8rGJsUj4Br4

Length: 9min 3sec (543 seconds)

Published: Wed Apr 08 2015