History of all SQL Executed

Is there a way to know in SQLSERVER - the history of all the SQL that has
been executed since the last re-start of the Server.
If Profiler is the only way of doing it - what are the performance impact of
running Profiler ( when Profiler writes to a file ) in a production System
Thanks> If Profiler is the only way of doing it - what are the performance impact
> of
> running Profiler ( when Profiler writes to a file ) in a production System
Profiler can have a negative performance impact if you have a lot of
qualifying events. I suggest that you instead create server-side SQL Trace
to log to a file. You can use Profiler to generate the SQL Trace script.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Sumit Pal" <Sumit Pal@.discussions.microsoft.com> wrote in message
news:4C7A8C06-671D-4DE4-AA2F-23B368191A69@.microsoft.com...
> Is there a way to know in SQLSERVER - the history of all the SQL that has
> been executed since the last re-start of the Server.
> If Profiler is the only way of doing it - what are the performance impact
> of
> running Profiler ( when Profiler writes to a file ) in a production System
> Thanks

History of all SQL Executed

Is there a way to know in SQLSERVER - the history of all the SQL that has
been executed since the last re-start of the Server.
If Profiler is the only way of doing it - what are the performance impact of
running Profiler ( when Profiler writes to a file ) in a production System
Thanks> If Profiler is the only way of doing it - what are the performance impact
> of
> running Profiler ( when Profiler writes to a file ) in a production System
Profiler can have a negative performance impact if you have a lot of
qualifying events. I suggest that you instead create server-side SQL Trace
to log to a file. You can use Profiler to generate the SQL Trace script.
Hope this helps.
Dan Guzman
SQL Server MVP
"Sumit Pal" <Sumit Pal@.discussions.microsoft.com> wrote in message
news:4C7A8C06-671D-4DE4-AA2F-23B368191A69@.microsoft.com...
> Is there a way to know in SQLSERVER - the history of all the SQL that has
> been executed since the last re-start of the Server.
> If Profiler is the only way of doing it - what are the performance impact
> of
> running Profiler ( when Profiler writes to a file ) in a production System
> Thanks

History of all SQL Executed

Is there a way to know in SQLSERVER - the history of all the SQL that has
been executed since the last re-start of the Server.
If Profiler is the only way of doing it - what are the performance impact of
running Profiler ( when Profiler writes to a file ) in a production System
Thanks
> If Profiler is the only way of doing it - what are the performance impact
> of
> running Profiler ( when Profiler writes to a file ) in a production System
Profiler can have a negative performance impact if you have a lot of
qualifying events. I suggest that you instead create server-side SQL Trace
to log to a file. You can use Profiler to generate the SQL Trace script.
Hope this helps.
Dan Guzman
SQL Server MVP
"Sumit Pal" <Sumit Pal@.discussions.microsoft.com> wrote in message
news:4C7A8C06-671D-4DE4-AA2F-23B368191A69@.microsoft.com...
> Is there a way to know in SQLSERVER - the history of all the SQL that has
> been executed since the last re-start of the Server.
> If Profiler is the only way of doing it - what are the performance impact
> of
> running Profiler ( when Profiler writes to a file ) in a production System
> Thanks

High reads

Some of my sps are showing high reads if executed from a >net application.
Like if sp is executed from .net app it shows some wayy around 5000000
reads and does not complete even in 10 min. Same sp , same time on th e same
server executes in 10 sec using sql query analyzer, and shows 30000 reads.
Can anyonme explains whats the problem?Bad query plans. Try adding the option WITH RECOMPILE to your stored
procedures.
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF
"VSS" <aa@.aa> wrote in message news:e04bYa6rGHA.2452@.TK2MSFTNGP03.phx.gbl...
> Some of my sps are showing high reads if executed from a >net application.
> Like if sp is executed from .net app it shows some wayy around 5000000
> reads and does not complete even in 10 min. Same sp , same time on th e
> same
> server executes in 10 sec using sql query analyzer, and shows 30000 reads.
> Can anyonme explains whats the problem?
>|||Check whether SET NCOUNT ON is set in SP .
M A Srinivas
VSS wrote:
> Some of my sps are showing high reads if executed from a >net application.
> Like if sp is executed from .net app it shows some wayy around 5000000
> reads and does not complete even in 10 min. Same sp , same time on th e sa
me
> server executes in 10 sec using sql query analyzer, and shows 30000 reads.
> Can anyonme explains whats the problem?

High reads

Some of my sps are showing high reads if executed from a >net application.
Like if sp is executed from .net app it shows some wayy around 5000000
reads and does not complete even in 10 min. Same sp , same time on th e same
server executes in 10 sec using sql query analyzer, and shows 30000 reads.
Can anyonme explains whats the problem?Bad query plans. Try adding the option WITH RECOMPILE to your stored
procedures.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF
"VSS" <aa@.aa> wrote in message news:e04bYa6rGHA.2452@.TK2MSFTNGP03.phx.gbl...
> Some of my sps are showing high reads if executed from a >net application.
> Like if sp is executed from .net app it shows some wayy around 5000000
> reads and does not complete even in 10 min. Same sp , same time on th e
> same
> server executes in 10 sec using sql query analyzer, and shows 30000 reads.
> Can anyonme explains whats the problem?
>|||Check whether SET NCOUNT ON is set in SP .
M A Srinivas
VSS wrote:
> Some of my sps are showing high reads if executed from a >net application.
> Like if sp is executed from .net app it shows some wayy around 5000000
> reads and does not complete even in 10 min. Same sp , same time on th e same
> server executes in 10 sec using sql query analyzer, and shows 30000 reads.
> Can anyonme explains whats the problem?

High Duration SQL 2005 Profiler

Hello everybody,

I have a very stranger problem that I need to understand...

In last days I executed a plan SQL 2005 Profiler to review TSQL Duration. When reviewing the results encounter that a SP displays a value of 4037312 in field DURATION which are not normal.

Could to help me to identify why passes this?

SQL Server 2005 has the ability to show duration in microseconds.

This can be changed in the options of Profiler.

Maybe this option is turned on?

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||The Duration Field show in microseconds is turned off. The generate value in the profiler for duration field in miliseconds is very high and not real...|||

This comes out to approximately 67 minutes. This is not necessarily wrong. A couple of things I've experienced in regards to long durations:

The job being executed is being blocked by something else and is continuously waiting on something else to complete before it can finish execution. The calling application is leaving the recordset open after calling. For example, if you use MS Access to open a table and don't bother to close it then it will show an extremely long duration (until the table is closed). Other applications could do the same thing if the author didn't properly include the code to close the recordset after retrieving the data. The client that's retrieving the data is on a particularly slow connection. Again - Duration is not the time it takes SQL Server to fulfill the request but the time it takes for the client to retrieve the data from the request and close the resultset. We once learned about a misconfigured network connection because durations were high while CPU and READs were low.|||The profiler duration field is in microseconds by default?|||No, its optional for SQL Server 2005.

Jens K. Suessmeyer.

http://www.sqlserver2005.de

Hiding of default objects in MS SQL Server

When created database in Microsoft SQL Server, many no. of default objects will be created.
when executed the command "sp_tables", so many no.of system tables will appear in addition to our own created tables. How to Hide them?

If you mean viewing in EM

From:

http://vyaskn.tripod.com/sql_server_tools_faq.htm#q2

How to hide/show system tables and other system objects in SQL Server Enterprise Manager?<top>

Open Enterprise Manager. Right click on your server name and choose 'Edit SQL Server Registration properties...' from the popup menu. This presents the 'Registered SQL Server Properties' dialog box. In the bottom half of this dialog box, there is a check box against 'Show system databases and system objects'. Check it to show system databases/tables or uncheck it to hide system databases/tables.
But:
I think (I can be wrong too) that sp_tables itself shows the tables that user can access. So I suppose there could be some ways with permissions to restrict the access (roles etc)