June 13, 2020 at 3:49 pm
Is it reasonable to roll-up ALL metrics from Query Store to measure 'everything'?
and then compare 1 sp (input param, SP name) metrics with 'Everything else' on server (or should it be by db?)
one stored proc watch is easy via Query Store. But aggregating ALL THE REST ('Everything') for even just one day is a huge aggregation, taking long time to run and log.. How else can i do it? a CLR SP is not an option. No outside languages either. SO I'll have to stick to T-SQL.
Project EVERYTHING. How can I measure Everything? Is Query Store data ENOUGH?... to identify %/weight of a particular stored proc relative to the total Database/server load.
(I also have a bunch of other things running/collecting performance metrics/data for later historical/offline analysis.
Such as sp_whoisactive every minute dump, blocking processes, OS perf counters collector, XE /deadlock info saving every 5 min into a repository called MyDBAutilityDB). inluding query store data. I have it all saved historically as well, every 15 minutes polling/saving QS data into an offline table. I would prefer to use QS only. if it is enough. To measure Everything.
* Probably even Grant will be challenged by this question.
Steven Wright, a great comedian, asked once: 'If you had everything , where would you put it or keep it?'. That's a similar question to what I am asking about :).
Likes to play Chess
June 13, 2020 at 4:46 pm
Learn how to find and measure the high spots. If you fix the top 10 worst things (and "worst" must never be interpreted to mean only the longest running code and it's almost always going to be a code problem you need to fix), you'll be amazed at how much better everything performs. Don't just stop there, though... fix the next 10.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 13, 2020 at 6:38 pm
Thank you, Jeff, for your
very valuable comment. And I am doing that , too (top 10 worst , etc.).
but aside along from that, there is a specific question asked me to answer:
what % or weight this and that particular huge and heavy SP is taking compared to the entire workload of everything during 24 hour period of time and fur the entire week (7x24) . as well as a set of 10 sps (as a batch, defined as 1 ETL process) relative to the entire load per 24 hours, later rolled-up to 1 full weak.
WIth the business purpose to either completely deprecate those few SPs and the ETL process and solve business task via other means or keep using them in current design/definition.. and there are other business goals , too, in calculating such % (e.g., comparing if same SP or ETL Process takes the same resources/% off total load or less on another prod server with same exact databases and workloads, and other reasons.)...
I have no problem capturing/measuring particular SPs or the entire ETL processes comprising 10 huge SPs and a dozen of FNs.
but then what do i compare it/ calc % of those measurements to?...
SO i DO need to capture EVERYTHING ! 🙂
Likes to play Chess
June 13, 2020 at 8:52 pm
I hate to say it, but this looks like a set up for failure. The old joke about how do you eat an elephant comes to mine, one bite at a time. Sounds to me like you are being asked to eat the whole elephant in one bite.
June 13, 2020 at 10:51 pm
you don't need to measure everything - if someone asked you to do that they don't know what they are talking about.
and comparing a proc over all others on a 24 period may be futile - who would care about a proc that takes 20 mins to run during the dead hours where no other activity is taking place - if that proc is tuned correctly there is no point in looking at it - but having it as a "top 10" time/resource consuming means nothing.
On the other hand a proc taking 100 ms but called millions of times a day may be a good target for improving (fyi on one of my systems I have procs/functions that take <1 ms and get called over 1.000.000.000 times a day. Do I care about it? not really)
what you need to focus is on those that take up resources that are not needed - I saw a sql a few years ago that for each execution it would ask for 20GB to be allocated - and not much that could be done to it unfortunately due to the way the application worked but mainly due to the volume of data that db had (3TB).
and in many cases looking at individual procs/functions is not enough - you need to compare to what else was running at that point in time to see if they are influencing or being influenced by the others - you may have a rouge proc that runs every 20 mins for 1 min - not a big thing on its own - but if it trashes the buffer pool it can cause others to run a lot slower than what would be expected and some of these may show up as "bad" because of it.
I strongly advise you to look at some of the commercial tools on the market as they will by their own design already have this type of comparison, will enable you to look at all that was running at a particular point in time and what resources it used. Money you pay for those is more than worth it compared to the time you are going to spend to try and build something that is not going to be anywhere near what the tools do unless you spend a considerable amount of time build that something.
June 14, 2020 at 3:01 am
Thank you, Jeff, for your
very valuable comment. And I am doing that , too (top 10 worst , etc.).
but aside along from that, there is a specific question asked me to answer:
what % or weight this and that particular huge and heavy SP is taking compared to the entire workload of everything during 24 hour period of time and fur the entire week (7x24) . as well as a set of 10 sps (as a batch, defined as 1 ETL process) relative to the entire load per 24 hours, later rolled-up to 1 full weak.
WIth the business purpose to either completely deprecate those few SPs and the ETL process and solve business task via other means or keep using them in current design/definition.. and there are other business goals , too, in calculating such % (e.g., comparing if same SP or ETL Process takes the same resources/% off total load or less on another prod server with same exact databases and workloads, and other reasons.)...
I have no problem capturing/measuring particular SPs or the entire ETL processes comprising 10 huge SPs and a dozen of FNs.
but then what do i compare it/ calc % of those measurements to?...
SO i DO need to capture EVERYTHING ! 🙂
Ok... no problem. Lets capture EVERYTHING! It's wicked easy to do...
Just setup either a Server Side SQLProfiler trace or equivalent set of Extended Events to capture the CPU Usage, Logical Reads, Physical Reads, Writes, Compile Time, Number of Connections used, Memory used, Number of Page Splits, Start Time, End Time, 30 different Wait times, which database it occurred on along with the host name and user,and, of course, Duration, for every single query that runs, no matter how big or small, and you'll have EVERYTHING!
And there's a bonus here... just think of the analysis experience you'll get over the month of Sundays it'll take to analyze billions of rows of data and the wonderful additional experience you'll get trying to figure out the best indexes to use to support the aggregations you'll need to make to come up with the answers to the questions about how to achieve the task you cited only to find out that they gave you a totally useless task.
😀
Oh... another bonus... you'll need to buy new hardware because, after that, your CPU will look like the elephant's foot at Chernobal. Yeah... this is the gift that will keep on giving.
In case you couldn't tell... I strongly disagree with the task that people have asked you to do. They have no clue what to ask for but are looking for some stupid justification to move a process somewhere else and they aren't even thinking of looking for the real problem because they either have an agenda or they know the need to do something, even if it's wrong.
Shifting gears a bit, you said you're working on the top 10 problems, as well but then you say ...
I have no problem capturing/measuring particular SPs or the entire ETL processes comprising 10 huge SPs and a dozen of FNs.
but then what do i compare it/ calc % of those measurements to?...
... which means to me that you're probably looking at the wrong 10 problems precisely for the same reasons that Frederico posted about. He stole a lot of my thunder on this subject right down to the bit about the 100ms queries but, I don't mind because he's absolutely correct. The only thing I disagree with is paying money for tools to find the real problems because they're so bloody simple to come by for free or, better yet, build your own so you know what you're actually talking about when someone asks you "Really? Can you prove it"?
I'm not barking at you so much as I'm barking at the folks that assigned this task to you. I have to bark at you a little bit because you didn't tell them that it's a ridiculous task. Because of what they asked you for, you can be pretty well assured they'd not actually know what the data is actually telling them if they had it. Well, unless they have a hidden agenda.
Oh... by the way... even though it sounds sarcastic (and it was certainly that), the SQLProfiler run or Extended Events thing I posed at the beginning of this post are the only ways to get a truly accurate measurement of EVERYTHING except that it will be inherently correct because it'll actually take more time and resources than your entire current workload.
Now... if you're having some performance issues with the box, we can help you find what they are and fixing them.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 14, 2020 at 3:21 am
Frederico mentioned the most important thing in his very first sentence, which is...
you don't need to measure everything - if someone asked you to do that they don't know what they are talking about.
Now... with absolutely no sarcasm... if you're looking to identify your real problems, look for compile times ( https://www.sqlskills.com/blogs/jonathan/identifying-high-compile-time-statements-from-the-plan-cache/ ) and a little ditty ( see code below for sp_ShowWorst) I wrote for myself quite a while ago before I even knew sp_WhoIsactive existed. DO read the instructions in the flower box of the code below. They're important.
USE [master]
GO
/****** Object: StoredProcedure [dbo].[sp_ShowWorst] Script Date: 6/13/2020 11:13:10 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_ShowWorst]
/**********************************************************************************************************************
Purpose:
This store procedure returns the "worst" performing queries in descending order by "CPU" time (worker time), logical
"Reads", total "Duration", or most used (execution "Count") FOR WHATEVER IS CURRENTLY CACHED.
Note that cache can be flushed silently and more times than you might think. Certainly, is is flushed
if the server is rebooted or the SQL Server Service is bounced.
Programmer's Notes:
1. This stored procedure should live in the Master database.
2. After building the stored procedure, you need to run the following code to convert it into a "system" stored
procedure that can be executed from database.
USE MASTER;
EXEC sp_ms_marksystemobject 'sp_ShowWorst';
SELECT name, is_ms_shipped
FROM sys.objects
WHERE name = 'sp_ShowWorst'
;
-----------------------------------------------------------------------------------------------------------------------
Usage:
sp_ShowWorst --(from any database, defaults to "Reads" an "10")
... OR ...
EXEC sp_ShowWorst --(from any database, defaults to "Reads" an "10")
sp_ShowWorst [@pSortType][,@pRows][,@pDebug] --Both parameters are option. Logical "Reads" and "30" will be presumed.
Valid values for @pSortType:
Reads - Logical Reads (current default) since last compile date
CPU - Total worker time in micro seconds since last compile date
Duration - Total elapsed time in micro seconds since last compile date
Count - Total number of executions since last compile date
Physical - Total number of physical reads since last compile date
Recent - Adding the word "Recent" to any of the above limits the output to only those things that have executed in
the last 60 minutes.
Valid values for @pRows:
Any positive integer (current default is 10)
Valid values for @pDebug
0 - Debug is off (default) and the code will be executed
1 - Debug if on and the code will be displayed instead of being executed.
-----------------------------------------------------------------------------------------------------------------------
Example Usage:
--===== Example usage for things that have run in the last hour
-- regardless of when they were compiled.
EXEC sp_ShowWorst 'Recent CPU' , 10;
EXEC sp_ShowWorst 'Recent Reads' , 10;
EXEC sp_ShowWorst 'Recent Duration' , 10;
EXEC sp_ShowWorst 'Recent Writes' , 10;
EXEC sp_ShowWorst 'Recent Physical' , 10;
EXEC sp_ShowWorst 'Recent Count' , 10;
--===== Example usage for things since they were last compiled.
EXEC sp_ShowWorst 'CPU' , 10;
EXEC sp_ShowWorst 'Reads' , 10;
EXEC sp_ShowWorst 'Duration', 10;
EXEC sp_ShowWorst 'Writes' , 10;
EXEC sp_ShowWorst 'Physical', 10;
EXEC sp_ShowWorst 'Count' , 10;
-----------------------------------------------------------------------------------------------------------------------
Notes:
1. Remember that values returned are as of the creation date and time (creation_time column) of the execution plan
whether the RECENT option is used or not. It IS possible for a plan to never be flushed from cache and the
cumulative usage may skew the results. This frequently occurs over weekends.
2. Note to self... (TODO) Find a way to overcome the problem stated in the note above.
3. Note to self... (TODO) Find out why SQL Server sometimes doesn't return an execution plan. It's understood that
"Optimize for Ad Hoc Queries" can cause this for singleton queries, but not for queries
that are executed multiple times with the same plan handle.
p.s Turns out that it's usually due to the use of Temp Tables.
-----------------------------------------------------------------------------------------------------------------------
Revision History:
Rev 00 - 29 Feb 2013 - Jeff Moden
- Formalize a script for regular usage.
Rev 01 - 25 Feb 2015 - Jeff Moden
- Add object name and ID information and make data collection for different types a lot easier by adding
"Sample*" information columns.
Rev 02 - 04 Jul 2016 - Jeff Moden
- 2.1 Added code and columns to determine the currently active database when ad hoc queries are executed.
- 2.2 Added the "Exec" prefix to columns that identify the object that was actual executed. Note that these
columns are normmally NULL for ad hoc queries via ORMs, applications, and SSMS.
Rev 03 - 06 Feb 2020 - Jeff Moden
- Add code to replace CHAR(31) with an empty string.
**********************************************************************************************************************/
--===== Define the I/O for this proc
@pSortType VARCHAR(30) = 'RECENT Reads',
@pRows INT = '10',
@pDebug BIT = 0
AS
--===== Environmental Presets
SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
--===== Declare some obviously named variable(s)
DECLARE @SQL VARCHAR(MAX)
;
--===== Create the SQL to get the data in the order requested
SELECT @SQL = REPLACE(REPLACE(REPLACE(REPLACE('
SELECT TOP (<<@pRows>>) --sp_ShowWorst
SampleDT = GETDATE() --Rev 01
,SampleType = ''<<@pSortType>>'' --Rev 01
,SampleValue = [stats].<<SortColumn>> --Rev 01
,CurrentDBName = db.DBName --Rev 02.1
,CurrentDBID = DB_ID(db.DBName) --Rev 02.1
,ExecDBID = [txt].DBID --Rev 01 - Rev02.2
,ExecObjectID = [txt].ObjectID --Rev 01 - Rev02.2
,ExecDBName = DB_NAME([txt].DBID) --Rev 01 - Rev02.2
,ExecSchemaName = OBJECT_SCHEMA_NAME([txt].ObjectID, [txt].DBID) --Rev 01 - Rev02.2
,ExecObjectName = OBJECT_NAME([txt].ObjectID, [txt].DBID) --Rev 01 - Rev02.2
,StatementText = (SELECT REPLACE(REPLACE(CAST(''--'' + CHAR(10)
+ SUBSTRING(txt.Text
,[stats].Statement_Start_Offset/2+1
,(CASE [stats].Statement_End_Offset
WHEN -1
THEN DATALENGTH(txt.Text)
ELSE [stats].Statement_End_Offset
END)/2 - [stats].Statement_Start_Offset/2+1
) AS VARCHAR(MAX)),CHAR(0),''''),CHAR(31),'''')
AS [processing-instruction(StatementTextSQL)] FOR XML PATH(''''), TYPE)
,FullText = (SELECT REPLACE(REPLACE(CAST(''--''+CHAR(10)+[txt].Text AS VARCHAR(MAX)),CHAR(0),''''),CHAR(31),'''')
AS [processing-instruction(FullTextSQL)] FOR XML PATH(''''), TYPE)
,[plan].query_plan
,[stats].*
FROM sys.dm_exec_query_stats [stats]
OUTER APPLY sys.dm_exec_sql_text ([stats].sql_handle) [txt]
OUTER APPLY sys.dm_exec_query_plan([stats].plan_handle) [plan]
OUTER APPLY (SELECT DBName = DB_NAME(CONVERT(INT,value)) -- Rev 02.1
FROM sys.dm_exec_plan_attributes([stats].plan_handle)
WHERE attribute = ''dbid'') [db]
WHERE [Stats].Last_Execution_Time >= ''<<LookBackDate>>''
ORDER BY [stats].<<SortColumn>> DESC
;'
------- This is the other end of the REPLACE's
,'<<@pSortType>>',@pSortType)
,'<<@pRows>>',CAST(@pRows AS VARCHAR(10)))
,'<<LookBackDate>>', CAST(CASE WHEN @pSortType LIKE '%Recent%' THEN DATEADD(hh,-1,GETDATE()) ELSE '1753' END AS VARCHAR(20)))
,'<<SortColumn>>', CASE
WHEN @pSortType LIKE '%Count%' THEN 'Execution_Count'
WHEN @pSortType LIKE '%CPU%' THEN 'Total_Worker_Time'
WHEN @pSortType LIKE '%Duration%' THEN 'Total_Elapsed_Time'
WHEN @pSortType LIKE '%Reads%' THEN 'Total_Logical_Reads'
WHEN @pSortType LIKE '%Physical%' THEN 'Total_Physical_Reads'
WHEN @pSortType LIKE '%Writes%' THEN 'Total_Logical_Writes'
END)
;
--===== Produce the output according to what the @pDebug variable is set to
IF @pDebug = 0 EXEC (@SQL);
ELSE PRINT @SQL;
Setup a job to run sp_ShowWorst and dump the output to a table every ten minutes or so. Change the number "10" in the call to something like 50. It won't provide you with EVERYTHING, but it will provide you with what you actually need for many reasons. Keep in mind that each line in the output is actually an aggregate and it will tell you the last time it was compiled as well as the last time it was executed so that you could do some "normalization" for the type of analysis that you really need to do for the folks asking you to do that other silly thing.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 14, 2020 at 5:20 pm
Thank you, Jeff !
Studying your sp_ShowWorst today. Pretty amazing so far.
I will definitely share a couple of SPs in same 'LOB' as your that I have written recently.
Starting to test sp_ShowWorst right now in a big server..
Do you mind if I include it here next week? After sp_Who3 in my list? This is my meetup. I do it once a month.
https://www.meetup.com/Learning-SQL-Server-and-T-SQL-with-an-expert/events/271241820/
THANK YOU!
Likes to play Chess
June 14, 2020 at 5:47 pm
June 14, 2020 at 7:28 pm
Also, please enjoy sp_Who3
https://www.sql.ru/forum/96127/sp-who3%5B/quote%5D
Perhaps the author should spend a bit more time on it first.
Msg 8152, Level 16, State 13, Line 1
String or binary data would be truncated.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 15, 2020 at 3:00 pm
Thank you, Jeff !
Studying your sp_ShowWorst today. Pretty amazing so far.
I will definitely share a couple of SPs in same 'LOB' as your that I have written recently.
Starting to test sp_ShowWorst right now in a big server..
Do you mind if I include it here next week? After sp_Who3 in my list? This is my meetup. I do it once a month.
https://www.meetup.com/Learning-SQL-Server-and-T-SQL-with-an-expert/events/271241820/
THANK YOU!
I don't mind you including sp_ShowWorst so long as you leave all comments in place. Let me know when you're going to do this and I'll be happy to join your meeting.
Shifting gears a bit, is the meeting for a PASS Chapter Group? I ask because it doesn't appear to be so. I'm willing to do some remote presentations for PASS Chapter Groups and I have a fair number that a Group Leader could chose from.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 17, 2020 at 4:07 pm
Definitely, Jeff, I will be honored if you attend my 12 noon EST meetup meeting. If you could say a few comments on your SP it would be delightful, too. And your critique after the meeting will certainly be invaluable.
Quick question: Why/for what benefit(S) should I do this?
2. After building the stored procedure, you need to run the following code to convert it into a "system" stored
procedure that can be executed from database.
USE MASTER;
EXEC sp_ms_marksystemobject 'sp_ShowWorst';
SELECT name, is_ms_shipped
FROM sys.objects
WHERE name = 'sp_ShowWorst'
Likes to play Chess
June 17, 2020 at 5:47 pm
Quick question: Why/for what benefit(S) should I do this?
2. After building the stored procedure, you need to run the following code to convert it into a "system" stored
procedure that can be executed from database.
USE MASTER;
EXEC sp_ms_marksystemobject 'sp_ShowWorst';
SELECT name, is_ms_shipped
FROM sys.objects
WHERE name = 'sp_ShowWorst'
A bit of background...
1. I normally don't use "Hungarian Notation", which is the non-insulting and proper name for when someone includes the object type as a prefix in the name. For example, you'll almost never see me use the format of "tbl_SomeTableName" or "tblSometableName".
2. Referring to #1 above, you'll never see me us "sp_" in a generic fashion. The reason why is because it has a SPecial meaning in SQL Server. "sp_" does actually mean (depending on who you ask) "special" or "special procedure" or "system procedure". What makes it "special" is that such system stored procedures in the master database can be executed from ANY database provided that they are actually a system stored procedure.
And that brings us to your question about the code you cited...
So, if you want to be able to use the stored procedure from whatever the current database is, the installation of sp_ShowWorst has to be done as follows...
If someone is totally against putting anything in the master database, then deploy to whatever database you want with the understanding that that database needs to be the current database in SSMS (or whatever) when you try to us sp_ShowWorst.
I'll follow your lead during the meeting. Just let me know what date it will be on and I'll be sure to join sign up for it once you post it on MeetUp.
And thank you very much for both your interest in helping other and for the invitation.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 18, 2020 at 4:41 pm
Thank you, Jeff, for your explanation, very helpful.
Question:
If we compile your SP (and a couple of other SPs in which i access either system objects in MASTER db or Query Store in a DB where we do not have permissions and its not possible to grant us either View Database State or Server State ) . and do not compile them in Master, how can the EXEC permissions be given to me (or other user without access to Master or DB from where sql in SP pulls and saves data from) without me having to have permissions for Master system objects or QS in other DBs and the like?
THANK YOU!
Likes to play Chess
June 18, 2020 at 5:51 pm
With the idea of teaching a man to fish, lookup each of the DMVs that sp_ShowWorst calls upon in the MS documention and look for the "Permissions" section.
You might already have such permissions. Try installing and executing sp_ShowWorst in a database you have good privs in and see if it works.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply