September 14, 2016 at 1:44 am
Kindly give the queries for below,
1.Query for List of tables of top and bottom accessed in last 30 days
2. Query for List of stored procedures 10% of top and bottom used in last 30 days
3.Query for List of functions not used of top and bottom in last 30 days List of triggers not used 10% of top and bottom in last 30 days
4. Query for List of tables with more than 10,000 updates per day
please forward if anyone knows
September 14, 2016 at 3:52 am
Not sure what "of top and bottom" means in the context of your question.
For questions #2 and #3, the answer is that this is not possible "out of the box". SQL Server does not track usage of code units. You can add code at the start of each stored procedure, user-defined function, and trigger that records when it is invoked.
If you do not want to modify code, then you can set up an extended events session to track and persist this. Or you can perhaps extract this infomation by regularly querying the plan cache and persisting the results. But none of these approaches are for the faint-hearted.
Also note that for one type of user-defined function, the "inline table-valued user-defined function", it is extrra hard to track usage because it is not executed as an independed code block; it's definition is inlined in the calling query which is then executed; at execution time the processor does not even know that you referenced a user-defined function in the query. The only approach I could think of is to capture statements being executed (probably with extended events) and analyze the SQL text to find references to the function. And even that would not be 100% complete because the reference to the function could be nested in a view or in another function. (Oh, and just for fun - this type of function is actually the only one I ever rercommend using, because it is the only type of user-defined function that does not have terrible performance)
For questions #1 and #4: you can do this by querying the dynamic management views (DMVs). The view sys.dm_db_index_usage_stats has counters of how often each index was used, per usage type. If you use memory-optimized tables, then you'll need to query sys.dm_db_xtp_index_stats as well.
Note that the contents of these DMVs are reset when the SQL Server service restarts. If you want to be able to query this even after the service restarts, you'll have to create a job that regularly queries the DMVs and persists the information in a normal table.
The purpose of this forum is to help people solve issues and help them get better. That means that I will not write the full queries for you. I have given you some suggestions on how to approach the problem, now you start typing key phrases from my answer in Google to find additional information (such as the exact usage of the DMVs I mention), then start working on the queries. If at one point you get stuck (which, given the complexity of working with DMVs, is perfectly undersrtandable), then you can post the code you have so far and ask for help getting over the hump that you are stuck at.
I hope this helps!
September 14, 2016 at 4:10 am
Top means mostly accessed tables or stored procedure by user and bottom means least accessed tables or stored procedure by user on SQL server , I have to make query for such type,
if any solution plz forward ,n thanks for your valuable time
September 14, 2016 at 4:51 am
Bharat21 (9/14/2016)
Top means mostly accessed tables or stored procedure by user and bottom means least accessed tables or stored procedure by user on SQL server , I have to make query for such type,if any solution plz forward ,n thanks for your valuable time
Hugo has answered your questions.
I'm curious - it's a strange set of requirements. Where did they come from and what will you do with the information?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
September 14, 2016 at 4:58 am
Hi Bharat,
I think Hugo has explained you very well all the routes you can take. As he mentioned you can use DMV (they are good only since last restart) for the tables. It would be a good idea to create a table and collect it using a job.
Since there is no built in feature/ reports to get the same, further details would depend on the route which you take.
Start something and post the query you have written so far, so that anyone can help you.
September 14, 2016 at 6:23 am
check below query and suggest how make query for take count of accessed this stored procedure and triggers on SQL server in last 30 days
-- 2 List of function
SELECT @@Servername AS ServerName ,
DB_NAME() AS DB_Name ,
o.name AS 'Functions' ,
o.[Type] ,
o.create_date
FROM sys.objects o
WHERE o.Type = 'FN' -- Function
ORDER BY o.NAME;
--OR
-- Function details
SELECT @@Servername AS ServerName ,
DB_NAME() AS DB_Name ,
o.name AS 'FunctionName' ,
o.[type] ,
o.create_date ,
sm.[DEFINITION] AS 'Function script'
FROM sys.objects o
INNER JOIN sys.sql_modules sm ON o.object_id = sm.OBJECT_ID
WHERE o.[Type] = 'FN' -- Function
ORDER BY o.NAME;
GO
------ 2 List of triggers
SELECT @@Servername AS ServerName ,
DB_NAME() AS DBName ,
parent.name AS TableName ,
o.name AS TriggerName ,
o.[Type] ,
o.create_date
FROM sys.objects o
INNER JOIN sys.objects parent ON o.parent_object_id = parent.object_id
WHERE o.Type = 'TR' -- Triggers
ORDER BY parent.name ,
o.NAME
--OR
SELECT @@Servername AS ServerName ,
DB_NAME() AS DB_Name ,
Parent_id ,
name AS TriggerName ,
create_date
FROM sys.triggers
WHERE parent_class = 1
ORDER BY name;
--OR
-- Trigger Details
SELECT @@Servername AS ServerName ,
DB_NAME() AS DB_Name ,
OBJECT_NAME(Parent_object_id) AS TableName ,
o.name AS 'TriggerName' ,
o.Type ,
o.create_date ,
sm.[DEFINITION] AS 'Trigger script'
FROM sys.objects o
INNER JOIN sys.sql_modules sm ON o.object_id = sm.OBJECT_ID
WHERE o.Type = 'TR' -- Triggers
ORDER BY o.NAME;
GO
September 14, 2016 at 6:32 am
Bharat21 (9/14/2016)
check below query and suggest how make query for take count of accessed this stored procedure and triggers on SQL server in last 30 days
Hugo already told you how to get that information, though only for back to the last time SQL was started, not 30 days.
Oh, and you probably want to read a bit more into whereever those queries came from. You ask for stored procedures and triggers, but the queries are for functions and triggers, and functions aren't stored procedures.
What are you actually trying to do?
If it's a performance problem, your best bet is to get a consultant in. Hugo does that kind of work, so do I, so do several other people here.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 14, 2016 at 6:42 am
Bharat21 (9/14/2016)
Kindly give the queries for below,1.Query for List of tables of top and bottom accessed in last 30 days
2. Query for List of stored procedures 10% of top and bottom used in last 30 days
3.Query for List of functions not used of top and bottom in last 30 days List of triggers not used 10% of top and bottom in last 30 days
4. Query for List of tables with more than 10,000 updates per day
please forward if anyone knows
If you're on SQL Server 2016, you can use Query Store to answer the questions 2 & 3, but you have to turn it on. Once it's on, it captures information about the queries being processed on the system and persists that information to disk. You can very easily identify the stored procedure and functions (triggers is going to be harder, it's not stored there) that have been used or not used just by combining the information with the system views listing stored procedures. The object_id values are stored in Query Store. This is set per database though, so you'll need to enable it on each database you wish to gather the information on. For more on query store, go to this link.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 14, 2016 at 7:03 am
Bharat21 (9/14/2016)
check below query and suggest how make query for take count of accessed this stored procedure and triggers on SQL server in last 30 days
Hmmm.
So you spent probably all of five minutes googling for "list of functions" or something similar, then did a quick copy/paste of someone else's code (apparently without even making an attempt to understand or test it, or you would have realized that the code produces incomplete results, and will not even work at all on case-sensitive servers) and now expect us to do your job.
I *can* actually do your job for you, but then it's called consulting and I get to send you an invoice when done. That's not what this forum is for.
I (and many others on this forum) are more than willing to spend a lot of effort on helping, completely free of charge, people who show that they put in an efforr themselves.
Your post shows very limited effort, and also very limited willingness to read and believe answers given. I alrerady told you that SQL Server does not track usage of user-defined functions, and yet you seem to think that someone can magically produce this for you. Short answer: No. Longer answer: Still no.
This all being said - the article that you copied the posted code from does contain a lot of queries that appear to be useful starting points for your requests - that is, for the two requests that CAN be (sort of) satisfied). Just make sure that you really check the code, analyze it until you understand what it's doing, and test it. Based on the errors I see in the query for function, it appears that not all queries have been tested well enough, so you should definitely not blindly copy, paste, run, and trust the results. As long as you keep that in mind, then you can definitely use some of these queries as a starting point.
September 15, 2016 at 4:24 pm
Just wanted to bring up a point of inquiry : the view sys.dm_db_index_usage_stats would not satisfy if any table has no indeces I imagine.
I am not sure as well what the reason for the question is. Though one thing that came to mind is turning on tracing. Though for thirty days it seems impractical and you would have to do it after the fact (of the last thirty day) if it was not turned on prior.
I thus have to ask... what is the issue that you really want to solve?
----------------------------------------------------
September 16, 2016 at 12:40 am
MMartin1 (9/15/2016)
Just wanted to bring up a point of inquiry : the view sys.dm_db_index_usage_stats would not satisfy if any table has no indeces I imagine.
A logical conclusion based on the name of the DV, but wrong nevertheless. Tables without clustered index arer stored as a heap, and data about heaps is tracked in sys.dm_db_index_usage_stats (using index_id = 0).
However, tables and indexes that have seen no usage at all since the last server restart are not included in this view.
September 16, 2016 at 11:09 am
Hugo Kornelis (9/16/2016)
MMartin1 (9/15/2016)
Just wanted to bring up a point of inquiry : the view sys.dm_db_index_usage_stats would not satisfy if any table has no indeces I imagine.A logical conclusion based on the name of the DV, but wrong nevertheless. Tables without clustered index arer stored as a heap, and data about heaps is tracked in sys.dm_db_index_usage_stats (using index_id = 0).
However, tables and indexes that have seen no usage at all since the last server restart are not included in this view.
Thank you for clarifying that Hugo. I did a quick test and verified. Though I have to say that there was some latency. I selected from another table and created a third to test out. It took a few selects from sys.dm_db_index_usage_stats to see the new rows in there. I guess its just something you gotta live with. Is it just me ?
----------------------------------------------------
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply