October 4, 2016 at 6:26 am
Morning All,
I want to analyse the datetime on a particular table as I want to identify an date range of data that can be archived. As it stand their is no archival and we're racking up 10 years of documents (blobs) in a 12 million row table.
The only information I need is the datetime field as a select happens.
It's dawned on me I've NO IDEA how to achieve this from the SQL Side of things. We do not have access to or the ability to recompile the application that drives this data.
It uses parameterised queries, so I don't get to see the value in a profiler/trace.
Does anyone have a suggestion to this? No changes are happening to this data it is SELECTS only.
Cheers Guys,
Alex
October 4, 2016 at 7:16 am
Try a network monitor to capture packets and shred contents to look for text that is of interest.
I'm not quite sure what you mean about not being able to see what you need via profiler. Can you post a bit of what you do see and explain what it is missing?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
October 4, 2016 at 7:47 am
Since you're in 2014, use extended events. You should absolutely be able to capture the parameter values on individual calls. If rpc_complete is not giving you the information you're looking for, instead try module_end.
"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
October 4, 2016 at 8:03 am
If I understand you correctly, you have a table containing a significant amount of history, much of which you believe can be archived elsewhere. You now want to know the full scope of date ranges that are queried, so you can know what rows to archive. The queries are embedded in stored procedures.
You can do this using a SQL Profiler trace and including one of the events that contain the input parameters.
I'm not sure, but the DBCC SHOW_STATISTICS command may also provide clues.
https://msdn.microsoft.com/en-us/library/ms174384.aspx
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
October 4, 2016 at 8:56 am
Hi Eric,
Thanks for reading. No, the parameters are not embedded in stored procedures sadly. They application uses parameterised queries (prepared statements) so the only thing I can see in a Profile Window is a @StartDate=@p1, @enddate=@p2
You are right about everything else though, I want to see roughly what range of dates are actively queried.
October 4, 2016 at 8:57 am
I am on 2014, I shall have a look at XE.
Though when I looked before I couldn't find anything but that was for 2008, things might have changed in my absence 🙂
October 4, 2016 at 10:23 am
If for whatever reason the extended event trace doesn't give you what you need about input parameter values, then can also implement procedure call auditing using a table and adding a couple lines of T-SQL code to the stored procedure(s).
create table SPExecuteAudit
(
constraint PK_SPExecuteAudit
primary key ( AuditDT, AuditProcedureName, SPID ),
AuditDT datetime not null default getdate(),
AuditProcedureName varchar(80) not null,
SPID smallint not null,
ParametersXML varchar(8000) null
);
For example, here we assume you have a procedure with parameters named @P1 and @P2.
insert into SPExecuteAudit ( AuditProcedureName, SPID, ParametersXML )
select isnull(object_name(@@procid),'???'), @@SPID,
(select @P1 as P1, @P2 as P2 for xml raw);
The value of ParametersXML would look something like this: <row P1="2015-01-01" P2="2016-10-04"/>
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
October 4, 2016 at 11:05 am
Eric M Russell (10/4/2016)
If for whatever reason the extended event trace doesn't give you what you need about input parameter values, then can also implement procedure call auditing using a table and adding a couple lines of T-SQL code to the stored procedure(s).
create table SPExecuteAudit
(
constraint PK_SPExecuteAudit
primary key ( AuditDT, AuditProcedureName, SPID ),
AuditDT datetime not null default getdate(),
AuditProcedureName varchar(80) not null,
SPID smallint not null,
ParametersXML varchar(8000) null
);
For example, here we assume you have a procedure with parameters named @P1 and @P2.
insert into SPExecuteAudit ( AuditProcedureName, SPID, ParametersXML )
select isnull(object_name(@@procid),'???'), @@SPID,
(select @P1 as P1, @P2 as P2 for xml raw);
The value of ParametersXML would look something like this: <row P1="2015-01-01" P2="2016-10-04"/>
Given that the application does not use stored procs, how would your suggestion be implemented?
--Edit: fixed typo
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 4, 2016 at 11:34 am
OK, I thought he said stored procedure earlier, so scratch that.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
October 5, 2016 at 1:36 am
Yeah sadly no stored procs, otherwise it would have been much easier.
October 5, 2016 at 6:58 am
alex.sqldba (10/5/2016)
Yeah sadly no stored procs, otherwise it would have been much easier.
Let this archived thread be an enduring lesson to anyone currently in the process of building a database application, especially independent software vendors. Without access to the underlying SQL and parameters, it's hard for the DBA to do their job.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
October 5, 2016 at 7:03 am
This place could be a case study in how NOT to do anything well with a database.
It seems it could also be a case study in corruption and fiddling money out of the company. But I shan't get in to that!
Edit: Obviously, I meant my place of work not this website 🙂
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply