The 1-2-3s of SQL Server and Your KPIs
Chris Shaw - Chris@sswug.org
I have been asked “how the SQL Server is performing” more times than I care to remember. What makes the answer to this question even that more difficult is when non-technical managers are asking the question. An answer of “good” really is not satisfactory as “good” is a perception. What may be good for me may not be good for other departments. When the SQL Servers have not been patched for a recent worm this may be serious cause for alarm with operations staff, but the finance department may be happy just because the servers are performing.
KPI’s or Key Performance Indicators allow operational staff to remove a lot of the perception by providing key statistics about the database. KPI’s may not be any different than counters in performance monitor, they are a statistic that is consistently used to determine if the database is running at acceptable performance benchmarks and, if not, how far it is off. KPI’s can even be specific to a database for a company. For example purposes we can look at the following example:
The Ski Shack is a company that rents skis all around the globe. They have multiple offices located near ski areas and a single server that resides in Colorado. The busiest time of the week for them is Friday afternoon before the weekend rush to the slopes. Monday morning is just as busy as Friday as everyone returns the rental skis.
If the database slows down, the company may lose a customer to nearby rental shops. It is very important that the database performs when called upon. To make ski rental easy for the customer they have a web site where customers can enter their information and then stop by the stores to pick up their rentals.
In this example there are a couple things that can be used as indicators of performance. One of these indicators could be how fast a few of the stored procedures are executing. If a few of the key stored procedures are executing in less than a second, then performance may be considered “good”. Another indicator that could be considered very important to the stock team is the number of skis of each size that are available. The last indicator we will consider for this article is the number of concurrent connections. KPI’s can be not only technical counters but business counters as well. The counters break down as follows:
· Performance – Speed of stored procedure execution
· Stock – Percentage of skis left in a size
· Reference – Number of connected users
Performance can be measured in a number of ways the, SQL Server profiler can provide information by running traces; an additional way is to monitor each stored procedure.
The first step would be to create a table where you can store the information long term. The table that I use to do this looks like:
Create Table ProcDurationTracker
(
ProcName varchar(100) not null,
CompleteDuration int not null
)
All that is left to do at this point is to modify the stored procedures in the database. This process may take awhile to complete if you have a large number of stored procedures. The only procedures for this example that need to be logged are the procedures that are related to the performance of the web page, or the stores. If you keep in mind that this is for a KPI and what is needed to be measured, the task becomes easier. A sample stored procedure could look like:
Create Procedure ProcTest2
AS
Declare @beginTime datetime
Declare @endtime datetime
Set @beginTime = GETDATE()
-- My Normal Procedure Here
Select [Movie Name], [Name Extended]
From Movies
-- End Procedure Here
Set @endtime = GETDATE()
Insert into ProcDurationTracker Values('ProcTest2',DATEDIFF(SS,@beginTime, @endtime))
GO
Stock measurements could be referred to just as any standard query in the database that would count the number of skis left in stock compared to the number of skis that are allocated to the inventory to the individual store. These stock numbers can be kept long term in a table that would store the size, percentage of stock left and the date and time of reading.
Reference numbers are statistics that give a baseline number to the other KPI’s that have been referred to. A non-technical way to look at a reference number could be comparing it to golf scores. In golf, each hole is given a predetermined number of stokes that each player should take as a score. This number is considered to be par. As a golfer plays a hole they could be above or below par. Golfers can tell how well they have played or how much practice they need by determining their score in comparison to par as par is the reference number.
One method to get the number of concurrent connections to the database would be to check the sys process DMV. This information could also be gathered by using the performance monitor using the connected user’s counters, but is not as easily recordable. By checking the number of active processes in the sys process DMV, there is an easy way to record and a consistent means of comparison. A count of this view could look close to this:
Select COUNT(*) from sys.sysprocesses
A well defined KPI will provide data that is measured consistently. I would argue that KPI’s should be recorded for long term analysis. History of KPI’s can provide awesome information for executives in a dashboard or other reporting paths.
The goal of the KPI is to be able to provide a number that indicates the health of the performance. If we consider the example; a few measurements could provide that the database is performing at 80%, the stock is down to 10% and the number of users is 150%. Here lies the purpose of the KPI’s.
The intention of this article was to give you the basics of why and how to refer to KPI’s. Please make sure that you rate this article and if there is a subject that you would like to see in the future, please let me know.
