I need to figure out the last time that a function was used by SQL Server. How can I do this?
I work with databases that contain thousands of stored procedures and hundreds of functions. I have found on-line methods to track the usage of stored procedures; however, I haven't seen the same for functions. Any ideas?
sql-server-2012
asked Jul 21 '15 at 16:48
user71064user71064
61
bumped to the homepage by Community♦ 11 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
add a comment |
I work with databases that contain thousands of stored procedures and hundreds of functions. I have found on-line methods to track the usage of stored procedures; however, I haven't seen the same for functions. Any ideas?
sql-server-2012
asked Jul 21 '15 at 16:48
user71064user71064
61
bumped to the homepage by Community♦ 11 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
add a comment |
I work with databases that contain thousands of stored procedures and hundreds of functions. I have found on-line methods to track the usage of stored procedures; however, I haven't seen the same for functions. Any ideas?
sql-server-2012
asked Jul 21 '15 at 16:48
user71064user71064
61
I work with databases that contain thousands of stored procedures and hundreds of functions. I have found on-line methods to track the usage of stored procedures; however, I haven't seen the same for functions. Any ideas?
sql-server-2012
sql-server-2012
asked Jul 21 '15 at 16:48
user71064user71064
61
asked Jul 21 '15 at 16:48
user71064user71064
61
asked Jul 21 '15 at 16:48
user71064user71064
61
asked Jul 21 '15 at 16:48
user71064user71064
61
asked Jul 21 '15 at 16:48
user71064user71064
61
61
bumped to the homepage by Community♦ 11 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
bumped to the homepage by Community♦ 11 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
You can use Change Data Capture or Change tracking to analyse the required changes here
You might need to drill down more with Sql server audit to capture the required use for the function in question SQL Server Audit Action Groups and Actions
Also,
You can get a possible idea as mentioned here:using below query
SELECT usecounts
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE sys.dm_exec_sql_text.objectid=object_id('YourFunction')
So basically you have to find a proper way to analyse the usage.
edited May 23 '17 at 12:40
Community♦
1
answered Jul 21 '15 at 17:16
KASQLDBAKASQLDBA
5,32461943
Thanks KASQLDBA. I was hoping to find something with a small footprint like the following for stored procedure usage: SELECT OB.name, OB.object_id AS [ObjectID], MAX(last_execution_time) AS [LastRun] FROM sys.objects AS [OB] INNER JOIN sys.dm_exec_procedure_stats AS [DMEPS] ON OB.object_id = DMEPS.object_id WHERE OB.type = 'P' AND DMEPS.database_id = DB_ID() GROUP BY OB.name, OB.object_id; Thanks for the ideas!
– user71064
Jul 21 '15 at 19:50
Your usecounts query gets me part of the way there.
– user71064
Jul 21 '15 at 19:59
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f107650%2fi-need-to-figure-out-the-last-time-that-a-function-was-used-by-sql-server-how-c%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
You can use Change Data Capture or Change tracking to analyse the required changes here
You might need to drill down more with Sql server audit to capture the required use for the function in question SQL Server Audit Action Groups and Actions
Also,
You can get a possible idea as mentioned here:using below query
SELECT usecounts
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE sys.dm_exec_sql_text.objectid=object_id('YourFunction')
So basically you have to find a proper way to analyse the usage.
edited May 23 '17 at 12:40
Community♦
1
answered Jul 21 '15 at 17:16
KASQLDBAKASQLDBA
5,32461943
Thanks KASQLDBA. I was hoping to find something with a small footprint like the following for stored procedure usage: SELECT OB.name, OB.object_id AS [ObjectID], MAX(last_execution_time) AS [LastRun] FROM sys.objects AS [OB] INNER JOIN sys.dm_exec_procedure_stats AS [DMEPS] ON OB.object_id = DMEPS.object_id WHERE OB.type = 'P' AND DMEPS.database_id = DB_ID() GROUP BY OB.name, OB.object_id; Thanks for the ideas!
– user71064
Jul 21 '15 at 19:50
Your usecounts query gets me part of the way there.
– user71064
Jul 21 '15 at 19:59
add a comment |
You can use Change Data Capture or Change tracking to analyse the required changes here
You might need to drill down more with Sql server audit to capture the required use for the function in question SQL Server Audit Action Groups and Actions
Also,
You can get a possible idea as mentioned here:using below query
SELECT usecounts
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE sys.dm_exec_sql_text.objectid=object_id('YourFunction')
So basically you have to find a proper way to analyse the usage.
edited May 23 '17 at 12:40
Community♦
1
answered Jul 21 '15 at 17:16
KASQLDBAKASQLDBA
5,32461943
Thanks KASQLDBA. I was hoping to find something with a small footprint like the following for stored procedure usage: SELECT OB.name, OB.object_id AS [ObjectID], MAX(last_execution_time) AS [LastRun] FROM sys.objects AS [OB] INNER JOIN sys.dm_exec_procedure_stats AS [DMEPS] ON OB.object_id = DMEPS.object_id WHERE OB.type = 'P' AND DMEPS.database_id = DB_ID() GROUP BY OB.name, OB.object_id; Thanks for the ideas!
– user71064
Jul 21 '15 at 19:50
Your usecounts query gets me part of the way there.
– user71064
Jul 21 '15 at 19:59
add a comment |
You can use Change Data Capture or Change tracking to analyse the required changes here
You might need to drill down more with Sql server audit to capture the required use for the function in question SQL Server Audit Action Groups and Actions
Also,
You can get a possible idea as mentioned here:using below query
SELECT usecounts
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE sys.dm_exec_sql_text.objectid=object_id('YourFunction')
So basically you have to find a proper way to analyse the usage.
edited May 23 '17 at 12:40
Community♦
1
answered Jul 21 '15 at 17:16
KASQLDBAKASQLDBA
5,32461943
You can use Change Data Capture or Change tracking to analyse the required changes here
You might need to drill down more with Sql server audit to capture the required use for the function in question SQL Server Audit Action Groups and Actions
Also,
You can get a possible idea as mentioned here:using below query
SELECT usecounts
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE sys.dm_exec_sql_text.objectid=object_id('YourFunction')
So basically you have to find a proper way to analyse the usage.
edited May 23 '17 at 12:40
Community♦
1
answered Jul 21 '15 at 17:16
KASQLDBAKASQLDBA
5,32461943
edited May 23 '17 at 12:40
Community♦
1
edited May 23 '17 at 12:40
Community♦
1
edited May 23 '17 at 12:40
Community♦
1
1
answered Jul 21 '15 at 17:16
KASQLDBAKASQLDBA
5,32461943
answered Jul 21 '15 at 17:16
KASQLDBAKASQLDBA
5,32461943
answered Jul 21 '15 at 17:16
KASQLDBAKASQLDBA
5,32461943
5,32461943
Thanks KASQLDBA. I was hoping to find something with a small footprint like the following for stored procedure usage: SELECT OB.name, OB.object_id AS [ObjectID], MAX(last_execution_time) AS [LastRun] FROM sys.objects AS [OB] INNER JOIN sys.dm_exec_procedure_stats AS [DMEPS] ON OB.object_id = DMEPS.object_id WHERE OB.type = 'P' AND DMEPS.database_id = DB_ID() GROUP BY OB.name, OB.object_id; Thanks for the ideas!
– user71064
Jul 21 '15 at 19:50
Your usecounts query gets me part of the way there.
– user71064
Jul 21 '15 at 19:59
add a comment |
Thanks KASQLDBA. I was hoping to find something with a small footprint like the following for stored procedure usage: SELECT OB.name, OB.object_id AS [ObjectID], MAX(last_execution_time) AS [LastRun] FROM sys.objects AS [OB] INNER JOIN sys.dm_exec_procedure_stats AS [DMEPS] ON OB.object_id = DMEPS.object_id WHERE OB.type = 'P' AND DMEPS.database_id = DB_ID() GROUP BY OB.name, OB.object_id; Thanks for the ideas!
– user71064
Jul 21 '15 at 19:50
Your usecounts query gets me part of the way there.
– user71064
Jul 21 '15 at 19:59
Thanks KASQLDBA. I was hoping to find something with a small footprint like the following for stored procedure usage: SELECT OB.name, OB.object_id AS [ObjectID], MAX(last_execution_time) AS [LastRun] FROM sys.objects AS [OB] INNER JOIN sys.dm_exec_procedure_stats AS [DMEPS] ON OB.object_id = DMEPS.object_id WHERE OB.type = 'P' AND DMEPS.database_id = DB_ID() GROUP BY OB.name, OB.object_id; Thanks for the ideas!
– user71064
Jul 21 '15 at 19:50
Thanks KASQLDBA. I was hoping to find something with a small footprint like the following for stored procedure usage: SELECT OB.name, OB.object_id AS [ObjectID], MAX(last_execution_time) AS [LastRun] FROM sys.objects AS [OB] INNER JOIN sys.dm_exec_procedure_stats AS [DMEPS] ON OB.object_id = DMEPS.object_id WHERE OB.type = 'P' AND DMEPS.database_id = DB_ID() GROUP BY OB.name, OB.object_id; Thanks for the ideas!
– user71064
Jul 21 '15 at 19:50
Your usecounts query gets me part of the way there.
– user71064
Jul 21 '15 at 19:59
Your usecounts query gets me part of the way there.
– user71064
Jul 21 '15 at 19:59
add a comment |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f107650%2fi-need-to-figure-out-the-last-time-that-a-function-was-used-by-sql-server-how-c%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
