Lats execution time of stored procedure

Hello,
Is it possible to know the last time that a stored procedure was executed /
invoced?
Thanks and best regards,
Jorge
Hi,
No, By default sql server will not store those information.
The only way to get this is by reading the tranasction log using tools like
Log explorer (www.lumigent.com)
or by running SQL Profiler.
Thanks
Hari
SQL Server MVP
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:912EA071-3EF7-490D-87BF-6EC9AFBA4A50@.microsoft.com...
> Hello,
> Is it possible to know the last time that a stored procedure was executed
> /
> invoced?
> Thanks and best regards,
> Jorge
>
|||Thank and best regards.
Jorge
"Hari Prasad" wrote:

> Hi,
> No, By default sql server will not store those information.
> The only way to get this is by reading the tranasction log using tools like
> Log explorer (www.lumigent.com)
> or by running SQL Profiler.
> Thanks
> Hari
> SQL Server MVP
>
> "CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
> news:912EA071-3EF7-490D-87BF-6EC9AFBA4A50@.microsoft.com...
>
>

Lats execution time of stored procedure

Hello,
Is it possible to know the last time that a stored procedure was executed /
invoced?
Thanks and best regards,
JorgeHi,
No, By default sql server will not store those information.
The only way to get this is by reading the tranasction log using tools like
Log explorer (www.lumigent.com)
or by running SQL Profiler.
Thanks
Hari
SQL Server MVP
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:912EA071-3EF7-490D-87BF-6EC9AFBA4A50@.microsoft.com...
> Hello,
> Is it possible to know the last time that a stored procedure was executed
> /
> invoced?
> Thanks and best regards,
> Jorge
>|||Thank and best regards.
Jorge
"Hari Prasad" wrote:
> Hi,
> No, By default sql server will not store those information.
> The only way to get this is by reading the tranasction log using tools like
> Log explorer (www.lumigent.com)
> or by running SQL Profiler.
> Thanks
> Hari
> SQL Server MVP
>
> "CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
> news:912EA071-3EF7-490D-87BF-6EC9AFBA4A50@.microsoft.com...
> > Hello,
> >
> > Is it possible to know the last time that a stored procedure was executed
> > /
> > invoced?
> >
> > Thanks and best regards,
> > Jorge
> >
> >
>
>

Lats execution time of stored procedure

Hello,
Is it possible to know the last time that a stored procedure was executed /
invoced?
Thanks and best regards,
JorgeHi,
No, By default sql server will not store those information.
The only way to get this is by reading the tranasction log using tools like
Log explorer (www.lumigent.com)
or by running SQL Profiler.
Thanks
Hari
SQL Server MVP
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:912EA071-3EF7-490D-87BF-6EC9AFBA4A50@.microsoft.com...
> Hello,
> Is it possible to know the last time that a stored procedure was executed
> /
> invoced?
> Thanks and best regards,
> Jorge
>|||Thank and best regards.
Jorge
"Hari Prasad" wrote:

> Hi,
> No, By default sql server will not store those information.
> The only way to get this is by reading the tranasction log using tools lik
e
> Log explorer (www.lumigent.com)
> or by running SQL Profiler.
> Thanks
> Hari
> SQL Server MVP
>
> "CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
> news:912EA071-3EF7-490D-87BF-6EC9AFBA4A50@.microsoft.com...
>
>

Latest updated date of a Stored Procedure

How to get the last updated date of a Stored procedure... From the sysobjects, we are able to get the created date... but how to get the latest updated date...?
Tx
Gkhttp://www.dbforums.com/archives/t317417.html

LAST_ALTERED Stored Procedure

Hi,
Is there anyway to find out the last altered date of a specific stored
procedure.
I tried Information_Schema.Routines but the value in that column doesn't
change after I modify the stored procedure.
When I look at BOL, it said "The last time the function was modified"
How to find out the date of all last_altered sp then'
Thanks
EdmundYou can't. SQL doesn't store this information.
Drop and recreate the procedure.
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:F9223ED0-2791-4CCC-9A6D-6A3BAC640D10@.microsoft.com...
> Hi,
> Is there anyway to find out the last altered date of a specific stored
> procedure.
> I tried Information_Schema.Routines but the value in that column doesn't
> change after I modify the stored procedure.
> When I look at BOL, it said "The last time the function was modified"
> How to find out the date of all last_altered sp then'
> Thanks
> Edmund
>|||what is the LAST_ALTERED column for under Information_Schema.Routines since
I
don't see the value of this column can be changed!!!!
Ed
"Raymond D'Anjou" wrote:

> You can't. SQL doesn't store this information.
> Drop and recreate the procedure.
> "Ed" <Ed@.discussions.microsoft.com> wrote in message
> news:F9223ED0-2791-4CCC-9A6D-6A3BAC640D10@.microsoft.com...
>
>|||These views are defined by ANSI SQL, to they "have to" be present in SQL Ser
ver whether the
information is actually available or not. Read the source for the view and y
ou see that it actually
displays creation date. Books Online is not correct, though, as it states it
is last time altered...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:28240995-CC07-4103-A1F7-6992939B7035@.microsoft.com...
> what is the LAST_ALTERED column for under Information_Schema.Routines sinc
e I
> don't see the value of this column can be changed!!!!
> Ed
> "Raymond D'Anjou" wrote:
>|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23kFf4e%23WFHA.2124@.TK2MSFTNGP14.phx.gbl...
> These views are defined by ANSI SQL, to they "have to" be present in SQL
> Server whether the information is actually available or not. Read the
> source for the view and you see that it actually displays creation date.
> Books Online is not correct, though, as it states it is last time
> altered...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
Funny.
It's as if I told my client:
I'm obligated to give you a report of your accounts receivables
The amounts are wrong, but you have your report.|||It is probably just a TBD that just fell through the cracks. I once tried to
add a trigger to the sysobjects table that would update the column with
getdate() when a record where xtype='P' is inserted or updated, but this is
not allowed.
"Raymond D'Anjou" <rdanjou@.savantsoftNOSPAM.net> wrote in message
news:uAQmiv%23WFHA.2692@.TK2MSFTNGP15.phx.gbl...
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> message news:%23kFf4e%23WFHA.2124@.TK2MSFTNGP14.phx.gbl...
> Funny.
> It's as if I told my client:
> I'm obligated to give you a report of your accounts receivables
> The amounts are wrong, but you have your report.
>|||LOL... I agree. It would be better to display NULL and document it properly.
.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Raymond D'Anjou" <rdanjou@.savantsoftNOSPAM.net> wrote in message
news:uAQmiv%23WFHA.2692@.TK2MSFTNGP15.phx.gbl...
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:%23kFf4e%23WFHA.2124@.TK2MSFTNGP14.phx.gbl...
> Funny.
> It's as if I told my client:
> I'm obligated to give you a report of your accounts receivables
> The amounts are wrong, but you have your report.
>|||You DO know that anything, including SELECTs, on System tables are
unsupported.
That's because these tables could change in any upgrade or service pack.
"JT" <someone@.microsoft.com> wrote in message
news:%23jNUtK$WFHA.3140@.TK2MSFTNGP14.phx.gbl...
> It is probably just a TBD that just fell through the cracks. I once tried
> to
> add a trigger to the sysobjects table that would update the column with
> getdate() when a record where xtype='P' is inserted or updated, but this
> is
> not allowed.
> "Raymond D'Anjou" <rdanjou@.savantsoftNOSPAM.net> wrote in message
> news:uAQmiv%23WFHA.2692@.TK2MSFTNGP15.phx.gbl...
> in
>|||Technically, the SELECT against the system tables is supported, as long as y
ou don't derive
information from columns that aren't documented. But the intent if correct,
the structure of the
system tables will change in next version. The will be "replaced" by catalog
views, but still exist
for backwards compatibility and most code will run without changes (assuming
reserved and
non-documented columns has been used).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Raymond D'Anjou" <rdanjou@.savantsoftNOSPAM.net> wrote in message
news:uXlGE8GXFHA.3584@.TK2MSFTNGP14.phx.gbl...
> You DO know that anything, including SELECTs, on System tables are unsuppo
rted.
> That's because these tables could change in any upgrade or service pack.
> "JT" <someone@.microsoft.com> wrote in message news:%23jNUtK$WFHA.3140@.TK2M
SFTNGP14.phx.gbl...
>|||Thanks Tibor.
I've gone over to the dark side a few times and directly queried system
tables but I've never included these in production code.
Off the top of your head, do you know of any system table information that
cannot be obtained by using "Information schema views" or "System stored
procedures".
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uGWA6rHXFHA.2540@.tk2msftngp13.phx.gbl...
> Technically, the SELECT against the system tables is supported, as long as
> you don't derive information from columns that aren't documented. But the
> intent if correct, the structure of the system tables will change in next
> version. The will be "replaced" by catalog views, but still exist for
> backwards compatibility and most code will run without changes (assuming
> reserved and non-documented columns has been used).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Raymond D'Anjou" <rdanjou@.savantsoftNOSPAM.net> wrote in message
> news:uXlGE8GXFHA.3584@.TK2MSFTNGP14.phx.gbl...
>

last update date for a procedure

see SqlServer Enterprise Manager: the list of stored procedures has just
name, owner, type and create date as columns.
I'd like to have also the date of the last update for a stored procedure.
This can help me a lot.
Is this possible? Is this date recorder in the system tables?
Thanks.Hi,
SQL Server will not store the modified date and time of the procedure.So if
you use ALter Procedure you cant see the modified date and time.
Thanks
Hari
SQL Server MVP
"Francesco" <Francesco@.discussions.microsoft.com> wrote in message
news:BE716295-7938-4A53-BA43-7A74DF780488@.microsoft.com...
> see SqlServer Enterprise Manager: the list of stored procedures has just
> name, owner, type and create date as columns.
> I'd like to have also the date of the last update for a stored procedure.
> This can help me a lot.
> Is this possible? Is this date recorder in the system tables?
> Thanks.
>|||Hi
No, SQL Server does not track such info.
Are you only a person in the company to make changes in stored procedures
and other objects?
"Francesco" <Francesco@.discussions.microsoft.com> wrote in message
news:BE716295-7938-4A53-BA43-7A74DF780488@.microsoft.com...
> see SqlServer Enterprise Manager: the list of stored procedures has just
> name, owner, type and create date as columns.
> I'd like to have also the date of the last update for a stored procedure.
> This can help me a lot.
> Is this possible? Is this date recorder in the system tables?
> Thanks.
>|||> see SqlServer Enterprise Manager: the list of stored procedures has just
> name, owner, type and create date as columns.
> I'd like to have also the date of the last update for a stored procedure.
> This can help me a lot.
> Is this possible? Is this date recorder in the system tables?
No. This is what source control is for.|||As Aaron mentions – use a source control, that’s what they are there for
.
This is simply said rather than done however and has been the subject of my
concern for many years now. There is a document
(http://www.innovartis.co.uk/pdf/ In...Mgt
.pdf)
which outlines the approach I’ve taken with unparalleled success. I’d lo
ve an
open debate on this subject as I still see no solution other than the one I
offer which gives an automated approach to database change management that
will save your company a lot of money, which I believe (IMHO) - is our
primary function as IT personnel.
When you have your source code in your source control for any thing - be it
applications, dynamic link libraries, user controls or databases there are
some necessary processes that need to be followed.
1. The source code needs to added to your source control.
2. Developers check out the source code to make changes. With application
code integrated development environments are readily available that will
check out the relevant files however these environments for SQL Server are
not so abundant and suffer many quirks which is why – and this is just my
opinion – I still find Query Analyzer to be the best development environme
nt
meaning I still have to check out the files from the source control via the
source controls interface.
3. Developers add new source code to the source control when new
functionality is required. IE: when dealing with applications this may be ne
w
forms, DLL’s, classes etc and when dealing with databases this may be new
tables or stored procedures. Different objects may be open to different
groups to develop as many organizations require that only a certain
individuals have the necessary abilities to make changes to tables for
example. These rules can be part of the source control allowing for a single
coherent view of functionality across the enterprise.
4. The code needs to be versioned at regular intervals to create snapshots
that facilitate deployment and code audits. All source controls have this
functionality IE: Visual Source Safe uses labels for this functionality.
5. You need a process that can deliver all the changes made to the code base
to the relevant areas. When you are dealing with applications this means
compiling the source code and deploying the compiled code to the required
machines. When talking databases this means compiling the source code and
deploying the changes to the required environments. I think this is where
people start to question this approach. Compile a database? Building a
database from the source code, compiles the source code. This step ensures
any changes made that break existing code are identified. Too often I see
broken code due to deployed code changes that ignore this fact. With
application code, compilers throw errors when dependencies are invalid. Now
that the source code has been validated a comparison can be made to determin
e
what needs to be changed on the target environment and then these changes ar
e
made. For this step I always implement an automated process on a machine
where a copy of the end environment can be held to test the deployment. The
process records a SQL delta file which can then be used (if the process has
been successful) on those environments requiring the changes. In the event a
n
error occurs, the error can be identified, the person responsible can be
identified, the time the error was made can be identified creating an ideal
environment for the code to be fixed quickly and the process repeated to
verify the change/fix.
This is what I like to call database change management and DB Ghost
(http://www.dbghost.com) is the result of the many years of thinking to
facilitate this process.
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Database change management for SQL Server
"Francesco" wrote:

> see SqlServer Enterprise Manager: the list of stored procedures has just
> name, owner, type and create date as columns.
> I'd like to have also the date of the last update for a stored procedure.
> This can help me a lot.
> Is this possible? Is this date recorder in the system tables?
> Thanks.
>|||As Aaron mentions – use a source control, that’s what they are there for
.
This is simply said rather than done however and has been the subject of my
concern for many years now. There is a document
(http://www.innovartis.co.uk/pdf/ In...Mgt
.pdf)
which outlines the approach I’ve taken with unparalleled success. I’d lo
ve an
open debate on this subject as I still see no solution other than the one I
offer which gives an automated approach to database change management that
will save your company a lot of money, which I believe (IMHO) - is our
primary function as IT personnel.
When you have your source code in your source control for any thing - be it
applications, dynamic link libraries, user controls or databases there are
some necessary processes that need to be followed.
1. The source code needs to added to your source control.
2. Developers check out the source code to make changes. With application
code integrated development environments are readily available that will
check out the relevant files however these environments for SQL Server are
not so abundant and suffer many quirks which is why – and this is just my
opinion – I still find Query Analyzer to be the best development environme
nt
meaning I still have to check out the files from the source control via the
source controls interface.
3. Developers add new source code to the source control when new
functionality is required. IE: when dealing with applications this may be ne
w
forms, DLL’s, classes etc and when dealing with databases this may be new
tables or stored procedures. Different objects may be open to different
groups to develop as many organizations require that only a certain
individuals have the necessary abilities to make changes to tables for
example. These rules can be part of the source control allowing for a single
coherent view of functionality across the enterprise.
4. The code needs to be versioned at regular intervals to create snapshots
that facilitate deployment and code audits. All source controls have this
functionality IE: Visual Source Safe uses labels for this functionality.
5. You need a process that can deliver all the changes made to the code base
to the relevant areas. When you are dealing with applications this means
compiling the source code and deploying the compiled code to the required
machines. When talking databases this means compiling the source code and
deploying the changes to the required environments. I think this is where
people start to question this approach. Compile a database? Building a
database from the source code, compiles the source code. This step ensures
any changes made that break existing code are identified. Too often I see
broken code due to deployed code changes that ignore this fact. With
application code, compilers throw errors when dependencies are invalid. Now
that the source code has been validated a comparison can be made to determin
e
what needs to be changed on the target environment and then these changes ar
e
made. For this step I always implement an automated process on a machine
where a copy of the end environment can be held to test the deployment. The
process records a SQL delta file which can then be used (if the process has
been successful) on those environments requiring the changes. In the event a
n
error occurs, the error can be identified, the person responsible can be
identified, the time the error was made can be identified creating an ideal
environment for the code to be fixed quickly and the process repeated to
verify the change/fix.
This is what I like to call database change management and DB Ghost
(http://www.dbghost.com) is the result of the many years of thinking to
facilitate this process.
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Database change management for SQL Server
"Francesco" wrote:

> see SqlServer Enterprise Manager: the list of stored procedures has just
> name, owner, type and create date as columns.
> I'd like to have also the date of the last update for a stored procedure.
> This can help me a lot.
> Is this possible? Is this date recorder in the system tables?
> Thanks.
>|||You can see this in information_schema.routines (column name is last_altered
)
"Francesco" wrote:

> see SqlServer Enterprise Manager: the list of stored procedures has just
> name, owner, type and create date as columns.
> I'd like to have also the date of the last update for a stored procedure.
> This can help me a lot.
> Is this possible? Is this date recorder in the system tables?
> Thanks.
>|||try testing this column, you should see that it doesn't work...
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Database change management for SQL Server
"harvinder" wrote:
[vbcol=seagreen]
> You can see this in information_schema.routines (column name is last_alter
ed)
> "Francesco" wrote:
>|||> You can see this in information_schema.routines (column name is
> last_altered)
NO, the data is not accurate unless you've never modified the stored
procedure. The datetime value stored here is always the same as created
date, and is never updated on ALTER... this will be more reliable using the
catalog views in SQL Server 2005, but for now the data is not stored.

last update date for a procedure

see SqlServer Enterprise Manager: the list of stored procedures has just
name, owner, type and create date as columns.
I'd like to have also the date of the last update for a stored procedure.
This can help me a lot.
Is this possible? Is this date recorder in the system tables?
Thanks.Hi,
SQL Server will not store the modified date and time of the procedure.So if
you use ALter Procedure you cant see the modified date and time.
Thanks
Hari
SQL Server MVP
"Francesco" <Francesco@.discussions.microsoft.com> wrote in message
news:BE716295-7938-4A53-BA43-7A74DF780488@.microsoft.com...
> see SqlServer Enterprise Manager: the list of stored procedures has just
> name, owner, type and create date as columns.
> I'd like to have also the date of the last update for a stored procedure.
> This can help me a lot.
> Is this possible? Is this date recorder in the system tables?
> Thanks.
>|||Hi
No, SQL Server does not track such info.
Are you only a person in the company to make changes in stored procedures
and other objects?
"Francesco" <Francesco@.discussions.microsoft.com> wrote in message
news:BE716295-7938-4A53-BA43-7A74DF780488@.microsoft.com...
> see SqlServer Enterprise Manager: the list of stored procedures has just
> name, owner, type and create date as columns.
> I'd like to have also the date of the last update for a stored procedure.
> This can help me a lot.
> Is this possible? Is this date recorder in the system tables?
> Thanks.
>|||> see SqlServer Enterprise Manager: the list of stored procedures has just
> name, owner, type and create date as columns.
> I'd like to have also the date of the last update for a stored procedure.
> This can help me a lot.
> Is this possible? Is this date recorder in the system tables?
No. This is what source control is for.|||As Aaron mentions â' use a source control, thatâ's what they are there for.
This is simply said rather than done however and has been the subject of my
concern for many years now. There is a document
(http://www.innovartis.co.uk/pdf/Innovartis_An_Automated_Approach_To_Do_Change_Mgt.pdf)
which outlines the approach Iâ've taken with unparalleled success. Iâ'd love an
open debate on this subject as I still see no solution other than the one I
offer which gives an automated approach to database change management that
will save your company a lot of money, which I believe (IMHO) - is our
primary function as IT personnel.
When you have your source code in your source control for any thing - be it
applications, dynamic link libraries, user controls or databases there are
some necessary processes that need to be followed.
1. The source code needs to added to your source control.
2. Developers check out the source code to make changes. With application
code integrated development environments are readily available that will
check out the relevant files however these environments for SQL Server are
not so abundant and suffer many quirks which is why â' and this is just my
opinion â' I still find Query Analyzer to be the best development environment
meaning I still have to check out the files from the source control via the
source controls interface.
3. Developers add new source code to the source control when new
functionality is required. IE: when dealing with applications this may be new
forms, DLLâ's, classes etc and when dealing with databases this may be new
tables or stored procedures. Different objects may be open to different
groups to develop as many organizations require that only a certain
individuals have the necessary abilities to make changes to tables for
example. These rules can be part of the source control allowing for a single
coherent view of functionality across the enterprise.
4. The code needs to be versioned at regular intervals to create snapshots
that facilitate deployment and code audits. All source controls have this
functionality IE: Visual Source Safe uses labels for this functionality.
5. You need a process that can deliver all the changes made to the code base
to the relevant areas. When you are dealing with applications this means
compiling the source code and deploying the compiled code to the required
machines. When talking databases this means compiling the source code and
deploying the changes to the required environments. I think this is where
people start to question this approach. Compile a database? Building a
database from the source code, compiles the source code. This step ensures
any changes made that break existing code are identified. Too often I see
broken code due to deployed code changes that ignore this fact. With
application code, compilers throw errors when dependencies are invalid. Now
that the source code has been validated a comparison can be made to determine
what needs to be changed on the target environment and then these changes are
made. For this step I always implement an automated process on a machine
where a copy of the end environment can be held to test the deployment. The
process records a SQL delta file which can then be used (if the process has
been successful) on those environments requiring the changes. In the event an
error occurs, the error can be identified, the person responsible can be
identified, the time the error was made can be identified creating an ideal
environment for the code to be fixed quickly and the process repeated to
verify the change/fix.
This is what I like to call database change management and DB Ghost
(http://www.dbghost.com) is the result of the many years of thinking to
facilitate this process.
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Database change management for SQL Server
"Francesco" wrote:
> see SqlServer Enterprise Manager: the list of stored procedures has just
> name, owner, type and create date as columns.
> I'd like to have also the date of the last update for a stored procedure.
> This can help me a lot.
> Is this possible? Is this date recorder in the system tables?
> Thanks.
>|||As Aaron mentions â' use a source control, thatâ's what they are there for.
This is simply said rather than done however and has been the subject of my
concern for many years now. There is a document
(http://www.innovartis.co.uk/pdf/Innovartis_An_Automated_Approach_To_Do_Change_Mgt.pdf)
which outlines the approach Iâ've taken with unparalleled success. Iâ'd love an
open debate on this subject as I still see no solution other than the one I
offer which gives an automated approach to database change management that
will save your company a lot of money, which I believe (IMHO) - is our
primary function as IT personnel.
When you have your source code in your source control for any thing - be it
applications, dynamic link libraries, user controls or databases there are
some necessary processes that need to be followed.
1. The source code needs to added to your source control.
2. Developers check out the source code to make changes. With application
code integrated development environments are readily available that will
check out the relevant files however these environments for SQL Server are
not so abundant and suffer many quirks which is why â' and this is just my
opinion â' I still find Query Analyzer to be the best development environment
meaning I still have to check out the files from the source control via the
source controls interface.
3. Developers add new source code to the source control when new
functionality is required. IE: when dealing with applications this may be new
forms, DLLâ's, classes etc and when dealing with databases this may be new
tables or stored procedures. Different objects may be open to different
groups to develop as many organizations require that only a certain
individuals have the necessary abilities to make changes to tables for
example. These rules can be part of the source control allowing for a single
coherent view of functionality across the enterprise.
4. The code needs to be versioned at regular intervals to create snapshots
that facilitate deployment and code audits. All source controls have this
functionality IE: Visual Source Safe uses labels for this functionality.
5. You need a process that can deliver all the changes made to the code base
to the relevant areas. When you are dealing with applications this means
compiling the source code and deploying the compiled code to the required
machines. When talking databases this means compiling the source code and
deploying the changes to the required environments. I think this is where
people start to question this approach. Compile a database? Building a
database from the source code, compiles the source code. This step ensures
any changes made that break existing code are identified. Too often I see
broken code due to deployed code changes that ignore this fact. With
application code, compilers throw errors when dependencies are invalid. Now
that the source code has been validated a comparison can be made to determine
what needs to be changed on the target environment and then these changes are
made. For this step I always implement an automated process on a machine
where a copy of the end environment can be held to test the deployment. The
process records a SQL delta file which can then be used (if the process has
been successful) on those environments requiring the changes. In the event an
error occurs, the error can be identified, the person responsible can be
identified, the time the error was made can be identified creating an ideal
environment for the code to be fixed quickly and the process repeated to
verify the change/fix.
This is what I like to call database change management and DB Ghost
(http://www.dbghost.com) is the result of the many years of thinking to
facilitate this process.
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Database change management for SQL Server
"Francesco" wrote:
> see SqlServer Enterprise Manager: the list of stored procedures has just
> name, owner, type and create date as columns.
> I'd like to have also the date of the last update for a stored procedure.
> This can help me a lot.
> Is this possible? Is this date recorder in the system tables?
> Thanks.
>|||You can see this in information_schema.routines (column name is last_altered)
"Francesco" wrote:
> see SqlServer Enterprise Manager: the list of stored procedures has just
> name, owner, type and create date as columns.
> I'd like to have also the date of the last update for a stored procedure.
> This can help me a lot.
> Is this possible? Is this date recorder in the system tables?
> Thanks.
>|||try testing this column, you should see that it doesn't work...
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Database change management for SQL Server
"harvinder" wrote:
> You can see this in information_schema.routines (column name is last_altered)
> "Francesco" wrote:
> > see SqlServer Enterprise Manager: the list of stored procedures has just
> > name, owner, type and create date as columns.
> > I'd like to have also the date of the last update for a stored procedure.
> > This can help me a lot.
> >
> > Is this possible? Is this date recorder in the system tables?
> > Thanks.
> >|||> You can see this in information_schema.routines (column name is
> last_altered)
NO, the data is not accurate unless you've never modified the stored
procedure. The datetime value stored here is always the same as created
date, and is never updated on ALTER... this will be more reliable using the
catalog views in SQL Server 2005, but for now the data is not stored.sql

last update date for a procedure

see SqlServer Enterprise Manager: the list of stored procedures has just
name, owner, type and create date as columns.
I'd like to have also the date of the last update for a stored procedure.
This can help me a lot.
Is this possible? Is this date recorder in the system tables?
Thanks.
Hi,
SQL Server will not store the modified date and time of the procedure.So if
you use ALter Procedure you cant see the modified date and time.
Thanks
Hari
SQL Server MVP
"Francesco" <Francesco@.discussions.microsoft.com> wrote in message
news:BE716295-7938-4A53-BA43-7A74DF780488@.microsoft.com...
> see SqlServer Enterprise Manager: the list of stored procedures has just
> name, owner, type and create date as columns.
> I'd like to have also the date of the last update for a stored procedure.
> This can help me a lot.
> Is this possible? Is this date recorder in the system tables?
> Thanks.
>
|||Hi
No, SQL Server does not track such info.
Are you only a person in the company to make changes in stored procedures
and other objects?
"Francesco" <Francesco@.discussions.microsoft.com> wrote in message
news:BE716295-7938-4A53-BA43-7A74DF780488@.microsoft.com...
> see SqlServer Enterprise Manager: the list of stored procedures has just
> name, owner, type and create date as columns.
> I'd like to have also the date of the last update for a stored procedure.
> This can help me a lot.
> Is this possible? Is this date recorder in the system tables?
> Thanks.
>
|||> see SqlServer Enterprise Manager: the list of stored procedures has just
> name, owner, type and create date as columns.
> I'd like to have also the date of the last update for a stored procedure.
> This can help me a lot.
> Is this possible? Is this date recorder in the system tables?
No. This is what source control is for.
|||As Aaron mentions – use a source control, that’s what they are there for.
This is simply said rather than done however and has been the subject of my
concern for many years now. There is a document
(http://www.innovartis.co.uk/pdf/Inno...ange_Mgt. pdf)
which outlines the approach I’ve taken with unparalleled success. I’d love an
open debate on this subject as I still see no solution other than the one I
offer which gives an automated approach to database change management that
will save your company a lot of money, which I believe (IMHO) - is our
primary function as IT personnel.
When you have your source code in your source control for any thing - be it
applications, dynamic link libraries, user controls or databases there are
some necessary processes that need to be followed.
1.The source code needs to added to your source control.
2.Developers check out the source code to make changes. With application
code integrated development environments are readily available that will
check out the relevant files however these environments for SQL Server are
not so abundant and suffer many quirks which is why – and this is just my
opinion – I still find Query Analyzer to be the best development environment
meaning I still have to check out the files from the source control via the
source controls interface.
3.Developers add new source code to the source control when new
functionality is required. IE: when dealing with applications this may be new
forms, DLL’s, classes etc and when dealing with databases this may be new
tables or stored procedures. Different objects may be open to different
groups to develop as many organizations require that only a certain
individuals have the necessary abilities to make changes to tables for
example. These rules can be part of the source control allowing for a single
coherent view of functionality across the enterprise.
4.The code needs to be versioned at regular intervals to create snapshots
that facilitate deployment and code audits. All source controls have this
functionality IE: Visual Source Safe uses labels for this functionality.
5.You need a process that can deliver all the changes made to the code base
to the relevant areas. When you are dealing with applications this means
compiling the source code and deploying the compiled code to the required
machines. When talking databases this means compiling the source code and
deploying the changes to the required environments. I think this is where
people start to question this approach. Compile a database? Building a
database from the source code, compiles the source code. This step ensures
any changes made that break existing code are identified. Too often I see
broken code due to deployed code changes that ignore this fact. With
application code, compilers throw errors when dependencies are invalid. Now
that the source code has been validated a comparison can be made to determine
what needs to be changed on the target environment and then these changes are
made. For this step I always implement an automated process on a machine
where a copy of the end environment can be held to test the deployment. The
process records a SQL delta file which can then be used (if the process has
been successful) on those environments requiring the changes. In the event an
error occurs, the error can be identified, the person responsible can be
identified, the time the error was made can be identified creating an ideal
environment for the code to be fixed quickly and the process repeated to
verify the change/fix.
This is what I like to call database change management and DB Ghost
(http://www.dbghost.com) is the result of the many years of thinking to
facilitate this process.
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Database change management for SQL Server
"Francesco" wrote:

> see SqlServer Enterprise Manager: the list of stored procedures has just
> name, owner, type and create date as columns.
> I'd like to have also the date of the last update for a stored procedure.
> This can help me a lot.
> Is this possible? Is this date recorder in the system tables?
> Thanks.
>
|||As Aaron mentions – use a source control, that’s what they are there for.
This is simply said rather than done however and has been the subject of my
concern for many years now. There is a document
(http://www.innovartis.co.uk/pdf/Inno...ange_Mgt. pdf)
which outlines the approach I’ve taken with unparalleled success. I’d love an
open debate on this subject as I still see no solution other than the one I
offer which gives an automated approach to database change management that
will save your company a lot of money, which I believe (IMHO) - is our
primary function as IT personnel.
When you have your source code in your source control for any thing - be it
applications, dynamic link libraries, user controls or databases there are
some necessary processes that need to be followed.
1.The source code needs to added to your source control.
2.Developers check out the source code to make changes. With application
code integrated development environments are readily available that will
check out the relevant files however these environments for SQL Server are
not so abundant and suffer many quirks which is why – and this is just my
opinion – I still find Query Analyzer to be the best development environment
meaning I still have to check out the files from the source control via the
source controls interface.
3.Developers add new source code to the source control when new
functionality is required. IE: when dealing with applications this may be new
forms, DLL’s, classes etc and when dealing with databases this may be new
tables or stored procedures. Different objects may be open to different
groups to develop as many organizations require that only a certain
individuals have the necessary abilities to make changes to tables for
example. These rules can be part of the source control allowing for a single
coherent view of functionality across the enterprise.
4.The code needs to be versioned at regular intervals to create snapshots
that facilitate deployment and code audits. All source controls have this
functionality IE: Visual Source Safe uses labels for this functionality.
5.You need a process that can deliver all the changes made to the code base
to the relevant areas. When you are dealing with applications this means
compiling the source code and deploying the compiled code to the required
machines. When talking databases this means compiling the source code and
deploying the changes to the required environments. I think this is where
people start to question this approach. Compile a database? Building a
database from the source code, compiles the source code. This step ensures
any changes made that break existing code are identified. Too often I see
broken code due to deployed code changes that ignore this fact. With
application code, compilers throw errors when dependencies are invalid. Now
that the source code has been validated a comparison can be made to determine
what needs to be changed on the target environment and then these changes are
made. For this step I always implement an automated process on a machine
where a copy of the end environment can be held to test the deployment. The
process records a SQL delta file which can then be used (if the process has
been successful) on those environments requiring the changes. In the event an
error occurs, the error can be identified, the person responsible can be
identified, the time the error was made can be identified creating an ideal
environment for the code to be fixed quickly and the process repeated to
verify the change/fix.
This is what I like to call database change management and DB Ghost
(http://www.dbghost.com) is the result of the many years of thinking to
facilitate this process.
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Database change management for SQL Server
"Francesco" wrote:

> see SqlServer Enterprise Manager: the list of stored procedures has just
> name, owner, type and create date as columns.
> I'd like to have also the date of the last update for a stored procedure.
> This can help me a lot.
> Is this possible? Is this date recorder in the system tables?
> Thanks.
>
|||You can see this in information_schema.routines (column name is last_altered)
"Francesco" wrote:

> see SqlServer Enterprise Manager: the list of stored procedures has just
> name, owner, type and create date as columns.
> I'd like to have also the date of the last update for a stored procedure.
> This can help me a lot.
> Is this possible? Is this date recorder in the system tables?
> Thanks.
>
|||try testing this column, you should see that it doesn't work...
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Database change management for SQL Server
"harvinder" wrote:
[vbcol=seagreen]
> You can see this in information_schema.routines (column name is last_altered)
> "Francesco" wrote:
|||> You can see this in information_schema.routines (column name is
> last_altered)
NO, the data is not accurate unless you've never modified the stored
procedure. The datetime value stored here is always the same as created
date, and is never updated on ALTER... this will be more reliable using the
catalog views in SQL Server 2005, but for now the data is not stored.

Last recompilation date of the procedure / view

Hi,

Kindly let me know How will we find the last recompilation date of the procedure / view in sql server.

Regards,

S.Balavenkatesh

perhaps what you seek is in:

select refdate, crdate from sysobjects

or

select create_date, modify_date from sys.system_objects -- 2005

?

Dave

Last Modified to Store Procedure

Hello everyone,

I have store procedure that was created on 9/15/2005. It went through some modificiations since then. How can I find out when was the last updated or last two update on this object?

Thanks

select modify_date from sys.objects

where name = '<object_name>'

Thanks

|||

Thanks Zoya.

It workout perfectly

Tom

Last Modified Date of the Database Object ?

Dear All,
In order to get a version control.
I would like to get the last modified date of the Store Procedure, Table De
finition
I find the the [sysobjects] Table ONLY contains a Create Date.
How can i find it.
Do SQL Server remember the [ALTER] and [RECOMPILE] time of these objects
Thank you very much
EricLunCurrent versions of SQL Server do not track modifications to objects, only
creation. You will have to track these yourself, or use a third party
product to do so. Your version control strategy, by the way, should not
rely on a feature of the product. In other words, if you were using version
control tools, you should have alter scripts stored in your source control
tool, without having to look at the database (other than to verify that what
is deployed exists in source control, or vice versa).
http://www.aspfaq.com/2495
Yukon will offer DDL triggers, but that's several months away, at least.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"EricLun >" <eric_klyuen@.yahoo.com.hk <no spam pls> wrote in message
news:DCE9AA09-6AF7-4BFD-A576-6D730BD4D79B@.microsoft.com...

quote:

---

> Dear All,
> In order to get a version control.
> I would like to get the last modified date of the Store Procedure, Table

---

Definition

quote:

---

> I find the the [sysobjects] Table ONLY contains a Create Date.
> How can i find it.
> Do SQL Server remember the [ALTER] and [RECOMPILE] time of these objects
> Thank you very much
> EricLun

---

|||Thank you very much form your help.

Last Modified Date of the Database Object ?

Dear All,
In order to get a version control.
I would like to get the last modified date of the Store Procedure, Table Definition
I find the the [sysobjects] Table ONLY contains a Create Date.
How can i find it.
Do SQL Server remember the [ALTER] and [RECOMPILE] time of these objects
Thank you very much
EricLunCurrent versions of SQL Server do not track modifications to objects, only
creation. You will have to track these yourself, or use a third party
product to do so. Your version control strategy, by the way, should not
rely on a feature of the product. In other words, if you were using version
control tools, you should have alter scripts stored in your source control
tool, without having to look at the database (other than to verify that what
is deployed exists in source control, or vice versa).
http://www.aspfaq.com/2495
Yukon will offer DDL triggers, but that's several months away, at least.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"EricLun >" <eric_klyuen@.yahoo.com.hk <no spam pls> wrote in message
news:DCE9AA09-6AF7-4BFD-A576-6D730BD4D79B@.microsoft.com...
> Dear All,
> In order to get a version control.
> I would like to get the last modified date of the Store Procedure, Table
Definition
> I find the the [sysobjects] Table ONLY contains a Create Date.
> How can i find it.
> Do SQL Server remember the [ALTER] and [RECOMPILE] time of these objects
> Thank you very much
> EricLun|||Thank you very much form your help.

Last modified date of a stored procedure

Is it possible to retrieve the last modified date for Stored procedures without programming?
I only can find the the creation date in the sysobjects table.To my knowledge SQL Server does not maintain changes to stored procedures, only the last version and compilation date.

Last Execution Time of Stored Procedure

Hi all,
since seeing the post from Satya with regards to Alter time of proc, there's
been a question bugging me for quite a long time now.
Is there a way within SQLServer (2000) or 2005 where i can tell the last
time a proc was executed?
ImmyImmy
> Is there a way within SQLServer (2000) or 2005 where i can tell the last
> time a proc was executed?
No. SQL Server Profiler is your friend
"Immy" <therealasianbabe@.hotmail.com> wrote in message
news:uj5H6udlGHA.4540@.TK2MSFTNGP04.phx.gbl...
> Hi all,
> since seeing the post from Satya with regards to Alter time of proc,
> there's been a question bugging me for quite a long time now.
> Is there a way within SQLServer (2000) or 2005 where i can tell the last
> time a proc was executed?
> Immy
>|||I would rather have a log table and have an insert (getdate()) to the log
table as the first line of the proc.
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||Hi
Could be , however it's overhead, especially in OLTP applications
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:32205CF4-F393-4535-B88D-45B836FA2230@.microsoft.com...
>I would rather have a log table and have an insert (getdate()) to the log
> table as the first line of the proc.
> --
> -Omnibuzz (The SQL GC)
> http://omnibuzz-sql.blogspot.com/
>|||Thats right. you would not be wanting to know the last execution time of a
Stored proc that gets executed frequently. My suggestion would be more
appropriate for a batch process.
Actually, i was thinking of this scenario where you have hundreds of procs
and you want to monitor the execution of a few of them. In that case you can
let the profiler to be running and keeping track of all the procs. Am I righ
t?
--
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||Yes, you are
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:DF96BA8C-73A7-42D7-80DE-E37B797B5B16@.microsoft.com...
> Thats right. you would not be wanting to know the last execution time of a
> Stored proc that gets executed frequently. My suggestion would be more
> appropriate for a batch process.
> Actually, i was thinking of this scenario where you have hundreds of procs
> and you want to monitor the execution of a few of them. In that case you
> can
> let the profiler to be running and keeping track of all the procs. Am I
> right?
> --
> -Omnibuzz (The SQL GC)
> http://omnibuzz-sql.blogspot.com/
>|||Immy (therealasianbabe@.hotmail.com) writes:
> since seeing the post from Satya with regards to Alter time of proc,
> there's been a question bugging me for quite a long time now.
> Is there a way within SQLServer (2000) or 2005 where i can tell the last
> time a proc was executed?
In SQL 2005 there is, sort of. This is query lists the last execution
time for all SQL modules in a database:
SELECT object_name(m.object_id), MAX(qs.last_execution_time)
FROM sys.sql_modules m
LEFT JOIN (sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text (qs.sql_handle) st)
ON m.object_id = st.objectid
AND st.dbid = db_id()
GROUP BY object_name(m.object_id)
But there are tons of caveats. The starting point of this query is
the dynamic management view dm_exec_query_stats, and the contents is
per *query plan*. If a stored procedure contains several queries,
there are more than one entry for the procedure in dm_exec_query_stats.
More importantly, if the procedure has no query plans at all, it will
not appear in dm_exec_query_stats. This could happen if you have a
procedures that just assign variables, or only calls a couple of other
stored procedure.
Furthermore, dm_exec_query_stats reflects what's in the *cache*. That is,
if the plans for a stored procedure falls out of the cache, so does the
information in sys.dm_exec_query_plans. How long a plan stays in the
cache depends on how lively the activity is on the server, and how
often the procedure is executed. Note that certain activities will
flush all plans for a table, for instance adding an index. Or restarting
SQL Server.
So whlle the query above can give you some interesting revelations,
you cannot reliably use it to determine that there are procedures
are not in use and that can be dropped.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Last Executed SP (Log file)

Hi
I want to know what is the last stored procedure executed in the server and
by which user. Is it possible to find. (I think there is some log for this)
anybody can help me.
Advanced Thanks
B. SundarNo, there isn't any log for it. You probably need to run Profiler to get
that kind of information.
"Sundar Bramanayagam" wrote:

> Hi
> I want to know what is the last stored procedure executed in the server an
d
> by which user. Is it possible to find. (I think there is some log for this
)
> anybody can help me.
> Advanced Thanks
> B. Sundar

Last Executed SP (Log file)

Hi
I want to know what is the last stored procedure executed in the server and
by which user. Is it possible to find. (I think there is some log for this)
anybody can help me.
Advanced Thanks
B. SundarNo, there isn't any log for it. You probably need to run Profiler to get
that kind of information.
"Sundar Bramanayagam" wrote:
> Hi
> I want to know what is the last stored procedure executed in the server and
> by which user. Is it possible to find. (I think there is some log for this)
> anybody can help me.
> Advanced Thanks
> B. Sundar

Last Executed SP (Log file)

Hi
I want to know what is the last stored procedure executed in the server and
by which user. Is it possible to find. (I think there is some log for this)
anybody can help me.
Advanced Thanks
B. Sundar
No, there isn't any log for it. You probably need to run Profiler to get
that kind of information.
"Sundar Bramanayagam" wrote:

> Hi
> I want to know what is the last stored procedure executed in the server and
> by which user. Is it possible to find. (I think there is some log for this)
> anybody can help me.
> Advanced Thanks
> B. Sundar

Large Text

I need to run a usp (user defined stored procedure) with a very large input text variable

probably 10,000-20,000 characters.

what variable can I use?

thanks in advanceUse varchar(max)|||

Hi,

If you are using sql server 2000 then use 'text' datatype, if you are using sql server 2005 then you can use 'varchar(max)' as Arnie Rowland suggested.

Ravi

Large query, slow DB responses

Hello,
I=E2=80=99m not a DBA and only use SQL to write the odd stored procedure et=
c,
so please be patient with me.
We have a, very badly structured, DB which misbehaves every now and
then. We are in the process of re-writing the .NET code and some sps
but until that=E2=80=99s done we have to deal with the following problem.
Our site is busiest over the weekend. It=E2=80=99s not a big site (usual
number of DB connections over the weekend is ~200). The worse thing is
that the backoffice uses the same DB as the users at home. When a
backoffice query is run during those busy times, it will strain the DB
so much that no one else will be able to access the site.
So, my question is: is there anything within SQL that I can see which
query (or queries) is causing this? I would ultimately like to write an
app the support guys could use to find these things out, but it=E2=80=99d be
nice to know where to start =EF=81=8A
Thank you for your time
KS
KS
Run SQL Server Profiler to identify long running queries . Now that you
identified them see an execution plan of each query.
Does the optimizer use indexes defined on the table?
"KS" <kiki@.dyky.co.uk> wrote in message
news:1143361090.378310.151760@.e56g2000cwe.googlegr oups.com...
Hello,
Im not a DBA and only use SQL to write the odd stored procedure etc,
so please be patient with me.
We have a, very badly structured, DB which misbehaves every now and
then. We are in the process of re-writing the .NET code and some sps
but until thats done we have to deal with the following problem.
Our site is busiest over the weekend. Its not a big site (usual
number of DB connections over the weekend is ~200). The worse thing is
that the backoffice uses the same DB as the users at home. When a
backoffice query is run during those busy times, it will strain the DB
so much that no one else will be able to access the site.
So, my question is: is there anything within SQL that I can see which
query (or queries) is causing this? I would ultimately like to write an
app the support guys could use to find these things out, but itd be
nice to know where to start ?
Thank you for your time
KS
|||Thank you for your response Uri. I did think of using a sql trace to
see the duration of each execution, but will this put on any more
strain? Is it ok to have the trace running the whole day?
Thanks again
|||If you have multiple/hyperthreaded processors, consider changing the 'max
degree of parallelism configuration option to '1' until you are able to
correct the underlying problem. This will prevent SQL Server from
generating a parallel plan for your BO users that can monopolize the
processors.
Hope this helps.
Dan Guzman
SQL Server MVP
"KS" <kiki@.dyky.co.uk> wrote in message
news:1143361090.378310.151760@.e56g2000cwe.googlegr oups.com...
Hello,
I'm not a DBA and only use SQL to write the odd stored procedure etc,
so please be patient with me.
We have a, very badly structured, DB which misbehaves every now and
then. We are in the process of re-writing the .NET code and some sps
but until that's done we have to deal with the following problem.
Our site is busiest over the weekend. It's not a big site (usual
number of DB connections over the weekend is ~200). The worse thing is
that the backoffice uses the same DB as the users at home. When a
backoffice query is run during those busy times, it will strain the DB
so much that no one else will be able to access the site.
So, my question is: is there anything within SQL that I can see which
query (or queries) is causing this? I would ultimately like to write an
app the support guys could use to find these things out, but it'd be
nice to know where to start ?
Thank you for your time
KS
|||KS
> strain? Is it ok to have the trace running the whole day?
Yes, just make sure that you created a trace on the client and not a server
side
"KS" <kiki@.dyky.co.uk> wrote in message
news:1143376377.890485.156100@.z34g2000cwc.googlegr oups.com...
> Thank you for your response Uri. I did think of using a sql trace to
> see the duration of each execution, but will this put on any more
> strain? Is it ok to have the trace running the whole day?
> Thanks again
>
|||Thank you both
|||Hi
SQL Profiler will allow you to determine which sps are taking the longest
durations, but will need to procedure to complete before giving you the
timings. If you are actually seeing blocking then sp_who2 will display
blocked processes or check out http://support.microsoft.com/kb/224453 and the
sp_blocker_pss80 script. If you are looking for a third party monitoring tool
then check out the Quest offerings http://www.quest.com/sql_server/index.asp
John
"KS" wrote:

> Hello,
> I’m not a DBA and only use SQL to write the odd stored procedure etc,
> so please be patient with me.
> We have a, very badly structured, DB which misbehaves every now and
> then. We are in the process of re-writing the .NET code and some sps
> but until that’s done we have to deal with the following problem.
> Our site is busiest over the weekend. It’s not a big site (usual
> number of DB connections over the weekend is ~200). The worse thing is
> that the backoffice uses the same DB as the users at home. When a
> backoffice query is run during those busy times, it will strain the DB
> so much that no one else will be able to access the site.
> So, my question is: is there anything within SQL that I can see which
> query (or queries) is causing this? I would ultimately like to write an
> app the support guys could use to find these things out, but it’d be
> nice to know where to start ?
> Thank you for your time
> KS
>

Large query, slow DB responses

Hello,
I=E2=80=99m not a DBA and only use SQL to write the odd stored procedure et= c,
so please be patient with me.
We have a, very badly structured, DB which misbehaves every now and
then. We are in the process of re-writing the .NET code and some sps
but until that=E2=80=99s done we have to deal with the following problem.
Our site is busiest over the weekend. It=E2=80=99s not a big site (usual
number of DB connections over the weekend is ~200). The worse thing is
that the backoffice uses the same DB as the users at home. When a
backoffice query is run during those busy times, it will strain the DB
so much that no one else will be able to access the site.
So, my question is: is there anything within SQL that I can see which
query (or queries) is causing this? I would ultimately like to write an
app the support guys could use to find these things out, but it=E2=80=99d be
nice to know where to start =EF=81=8A
Thank you for your time
KSKS
Run SQL Server Profiler to identify long running queries . Now that you
identified them see an execution plan of each query.
Does the optimizer use indexes defined on the table?
"KS" <kiki@.dyky.co.uk> wrote in message
news:1143361090.378310.151760@.e56g2000cwe.googlegroups.com...
Hello,
I?m not a DBA and only use SQL to write the odd stored procedure etc,
so please be patient with me.
We have a, very badly structured, DB which misbehaves every now and
then. We are in the process of re-writing the .NET code and some sps
but until that?s done we have to deal with the following problem.
Our site is busiest over the weekend. It?s not a big site (usual
number of DB connections over the weekend is ~200). The worse thing is
that the backoffice uses the same DB as the users at home. When a
backoffice query is run during those busy times, it will strain the DB
so much that no one else will be able to access the site.
So, my question is: is there anything within SQL that I can see which
query (or queries) is causing this? I would ultimately like to write an
app the support guys could use to find these things out, but it?d be
nice to know where to start ?
Thank you for your time
KS|||Thank you for your response Uri. I did think of using a sql trace to
see the duration of each execution, but will this put on any more
strain? Is it ok to have the trace running the whole day?
Thanks again|||If you have multiple/hyperthreaded processors, consider changing the 'max
degree of parallelism configuration option to '1' until you are able to
correct the underlying problem. This will prevent SQL Server from
generating a parallel plan for your BO users that can monopolize the
processors.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"KS" <kiki@.dyky.co.uk> wrote in message
news:1143361090.378310.151760@.e56g2000cwe.googlegroups.com...
Hello,
I'm not a DBA and only use SQL to write the odd stored procedure etc,
so please be patient with me.
We have a, very badly structured, DB which misbehaves every now and
then. We are in the process of re-writing the .NET code and some sps
but until that's done we have to deal with the following problem.
Our site is busiest over the weekend. It's not a big site (usual
number of DB connections over the weekend is ~200). The worse thing is
that the backoffice uses the same DB as the users at home. When a
backoffice query is run during those busy times, it will strain the DB
so much that no one else will be able to access the site.
So, my question is: is there anything within SQL that I can see which
query (or queries) is causing this? I would ultimately like to write an
app the support guys could use to find these things out, but it'd be
nice to know where to start ?
Thank you for your time
KS|||KS
> strain? Is it ok to have the trace running the whole day?
Yes, just make sure that you created a trace on the client and not a server
side
"KS" <kiki@.dyky.co.uk> wrote in message
news:1143376377.890485.156100@.z34g2000cwc.googlegroups.com...
> Thank you for your response Uri. I did think of using a sql trace to
> see the duration of each execution, but will this put on any more
> strain? Is it ok to have the trace running the whole day?
> Thanks again
>|||Thank you both :)|||Hi
SQL Profiler will allow you to determine which sps are taking the longest
durations, but will need to procedure to complete before giving you the
timings. If you are actually seeing blocking then sp_who2 will display
blocked processes or check out http://support.microsoft.com/kb/224453 and the
sp_blocker_pss80 script. If you are looking for a third party monitoring tool
then check out the Quest offerings http://www.quest.com/sql_server/index.asp
John
"KS" wrote:
> Hello,
> Iâ'm not a DBA and only use SQL to write the odd stored procedure etc,
> so please be patient with me.
> We have a, very badly structured, DB which misbehaves every now and
> then. We are in the process of re-writing the .NET code and some sps
> but until thatâ's done we have to deal with the following problem.
> Our site is busiest over the weekend. Itâ's not a big site (usual
> number of DB connections over the weekend is ~200). The worse thing is
> that the backoffice uses the same DB as the users at home. When a
> backoffice query is run during those busy times, it will strain the DB
> so much that no one else will be able to access the site.
> So, my question is: is there anything within SQL that I can see which
> query (or queries) is causing this? I would ultimately like to write an
> app the support guys could use to find these things out, but itâ'd be
> nice to know where to start ï'
> Thank you for your time
> KS
>