Query Wait Option

How can I determine what resources my query is waiting for?
If the query is waiting on memory how can I determine this?
We have several queries in a batch job we are trying to
determine why the query is waiting so long to complete.
Thanks
Mark
FROM BOL:
A transaction containing the waiting query may hold locks
while the query waits for memory. In rare situations, it
is possible for an undetectable deadlock to occur.
Decreasing the query wait time lowers the probability of
such deadlocks. Eventually, a waiting query will be
terminated and the transaction locks released. However,
increasing the maximum wait time may increase the amount
of time for the query to be terminated. Changes to this
option are not recommended.
There are a numbe rof columns in master..sysprocesses that help you look at
WAITTYPE info for a process. You should also take a look at dbcc
sqlperf(waitstats).
Tom Davidson, from MS, has written a nice article on this at SQLMag.com
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Mark" <anonymous@.discussions.microsoft.com> wrote in message
news:2b3fe01c46820$14c51900$a401280a@.phx.gbl...
> How can I determine what resources my query is waiting for?
> If the query is waiting on memory how can I determine this?
> We have several queries in a batch job we are trying to
> determine why the query is waiting so long to complete.
> Thanks
> Mark
>
> FROM BOL:
> A transaction containing the waiting query may hold locks
> while the query waits for memory. In rare situations, it
> is possible for an undetectable deadlock to occur.
> Decreasing the query wait time lowers the probability of
> such deadlocks. Eventually, a waiting query will be
> terminated and the transaction locks released. However,
> increasing the maximum wait time may increase the amount
> of time for the query to be terminated. Changes to this
> option are not recommended.
>

Query Wait Option

How can I determine what resources my query is waiting for?
If the query is waiting on memory how can I determine this?
We have several queries in a batch job we are trying to
determine why the query is waiting so long to complete.
Thanks
Mark
FROM BOL:
A transaction containing the waiting query may hold locks
while the query waits for memory. In rare situations, it
is possible for an undetectable deadlock to occur.
Decreasing the query wait time lowers the probability of
such deadlocks. Eventually, a waiting query will be
terminated and the transaction locks released. However,
increasing the maximum wait time may increase the amount
of time for the query to be terminated. Changes to this
option are not recommended.There are a numbe rof columns in master..sysprocesses that help you look at
WAITTYPE info for a process. You should also take a look at dbcc
sqlperf(waitstats).
Tom Davidson, from MS, has written a nice article on this at SQLMag.com
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Mark" <anonymous@.discussions.microsoft.com> wrote in message
news:2b3fe01c46820$14c51900$a401280a@.phx.gbl...
> How can I determine what resources my query is waiting for?
> If the query is waiting on memory how can I determine this?
> We have several queries in a batch job we are trying to
> determine why the query is waiting so long to complete.
> Thanks
> Mark
>
> FROM BOL:
> A transaction containing the waiting query may hold locks
> while the query waits for memory. In rare situations, it
> is possible for an undetectable deadlock to occur.
> Decreasing the query wait time lowers the probability of
> such deadlocks. Eventually, a waiting query will be
> terminated and the transaction locks released. However,
> increasing the maximum wait time may increase the amount
> of time for the query to be terminated. Changes to this
> option are not recommended.
>sql

Query Wait Option

How can I determine what resources my query is waiting for?
If the query is waiting on memory how can I determine this?
We have several queries in a batch job we are trying to
determine why the query is waiting so long to complete.
Thanks
Mark
FROM BOL:
A transaction containing the waiting query may hold locks
while the query waits for memory. In rare situations, it
is possible for an undetectable deadlock to occur.
Decreasing the query wait time lowers the probability of
such deadlocks. Eventually, a waiting query will be
terminated and the transaction locks released. However,
increasing the maximum wait time may increase the amount
of time for the query to be terminated. Changes to this
option are not recommended.There are a numbe rof columns in master..sysprocesses that help you look at
WAITTYPE info for a process. You should also take a look at dbcc
sqlperf(waitstats).
Tom Davidson, from MS, has written a nice article on this at SQLMag.com
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Mark" <anonymous@.discussions.microsoft.com> wrote in message
news:2b3fe01c46820$14c51900$a401280a@.phx
.gbl...
> How can I determine what resources my query is waiting for?
> If the query is waiting on memory how can I determine this?
> We have several queries in a batch job we are trying to
> determine why the query is waiting so long to complete.
> Thanks
> Mark
>
> FROM BOL:
> A transaction containing the waiting query may hold locks
> while the query waits for memory. In rare situations, it
> is possible for an undetectable deadlock to occur.
> Decreasing the query wait time lowers the probability of
> such deadlocks. Eventually, a waiting query will be
> terminated and the transaction locks released. However,
> increasing the maximum wait time may increase the amount
> of time for the query to be terminated. Changes to this
> option are not recommended.
>

Query using schema to determine if Trigger exists

Hi,

I would like to know if there's a query that can tell me whether or not a trigger exists!

Thanks!

Quote:

Originally Posted by Barno77

Hi,

I would like to know if there's a query that can tell me whether or not a trigger exists!

Thanks!

Returns list of trigger names from a specific database

select * from dbo.sysobjects where OBJECTPROPERTY(id, N'IsTrigger') = 1

Regards

Jim :)|||Thank you! This helped me!

Query to determine public access

Is there any T-SQL Script or SP that would determine what tables public users have access to? I only ask because I would like to make sure that any users with public rights do not have access to SYSXLOGINS, SYSDATABASES, SYSOBJECTS, SYSCOLUMNS and mswebtasks tables. Thanks
-Kyle

All users are members of the public role by default and out of the box public won't have any explicit permissions assigned to it. So unless you've tampered with these settings, you should be ok.

Remember if you assign explict deny permissions on public this could have unexpected consequences as ALL logins won't have access to those tables.

Id suggest having a user defined database role which you add all users that has the desired permissions.

HTH!

|||Being a DBA in a somewhat large company means that you inherit a lot of the main aspects of the job. Along with that, you can't satisfy auditers with the explanation that out of the box the public user role won't have rights to the tables. Thus, if anybody has a query that would tell the tables that the public user role has that would be fantastic.

Query to determine if something exists versus just trying to create it...

I have run into two situations in the recent past that both have the
same thing in common. I have to preface this with the fact that I am
running the following queries in a C#/.Net environment using SQL Server
Express...
The question is, and it may be silly, but, should one query for the
existance of a row in the DB before attempting to create it?
For example, I could write a query that says "select * from table where
id = 1". Then if the resulting dataset has one or 0 rows, I could
determine if I need to write a Update or Insert query to put in the
row. However, I am assuming that the rows will 99% of the time already
exist, but I need to update all of the fields in the table with
potentially new values. So the question is, in this case, it would be
silly to attempt to determine if the row is there, then to determine if
it should be updated or not. Basically, I am executing the update
statement first, and if it returns 0 (meaning that the row didn't
exist), then I translate the statement from an update to an insert...
I am also doing something similar with create/alter statements, where I
could test if the table/field exists in the DB before attempting to
create or alter the table, but why bother if I can execute the one
statement and then know if the table or field already existed based off
of if the statement executed correctly or not... ?
So, is there a faster way to say "insert OR update this data", in one
statement, or should I just continue executing one, then the other if
the first one fails?
AB> row. However, I am assuming that the rows will 99% of the time already
> exist, but I need to update all of the fields in the table with
> potentially new values.
If the usual case is that the row will exist, you might consider trying the
update first and then proceeding with the insert only if no rows were
updated. Something like:
CREATE PROC usp_SaveMyTable
@.MyTableId int,
@.SomeColumn int
AS
SET NOCOUNT, XACT_ABORT ON
DECLARE @.Error int, @.RowCount int
BEGIN TRAN
UPDATE dbo.MyTable
SET SomeColumn = @.SomeColumn
WHERE MyTableId = @.MyTableId
SELECT @.Error = @.@.ERROR, @.RowCount = @.@.ROWCOUNT
IF @.RowCount > 0 OR @.Error <> 0
BEGIN
GOTO Done
END
INSERT INTO dbo.MyTable(MyTableId, SomeColumn)
SELECT
@.MyTableId, @.SomeColumn
WHERE NOT EXISTS
(
SELECT *
FROM dbo.MyTable WITH (HOLDLOCK)
WHERE MyTableId = @.MyTableId
)
SELECT @.Error = @.@.ERROR
Done:
IF @.Error = 0
BEGIN
COMMIT
END
ELSE
BEGIN
ROLLBACK
END
GO
> I am also doing something similar with create/alter statements, where I
> could test if the table/field exists in the DB before attempting to
> create or alter the table, but why bother if I can execute the one
> statement and then know if the table or field already existed based off
> of if the statement executed correctly or not... ?
Personally, I prefer to avoid raising errors for expected conditions. If
you get error messages even when the script runs successfully, it's hard to
tell a real problem with all the noise.
IF OBJECT_ID('dbo.MyTable') IS NULL
BEGIN
CREATE TABLE dbo.MyTable
(
MyTableId int NOT NULL
CONSTRAINT PK_MyTable PRIMARY KEY,
SomeColumn int NOT NULL
)
END
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Beavis" <multiformity@.gmail.com> wrote in message
news:1156385524.486272.138630@.p79g2000cwp.googlegroups.com...
>I have run into two situations in the recent past that both have the
> same thing in common. I have to preface this with the fact that I am
> running the following queries in a C#/.Net environment using SQL Server
> Express...
> The question is, and it may be silly, but, should one query for the
> existance of a row in the DB before attempting to create it?
> For example, I could write a query that says "select * from table where
> id = 1". Then if the resulting dataset has one or 0 rows, I could
> determine if I need to write a Update or Insert query to put in the
> row. However, I am assuming that the rows will 99% of the time already
> exist, but I need to update all of the fields in the table with
> potentially new values. So the question is, in this case, it would be
> silly to attempt to determine if the row is there, then to determine if
> it should be updated or not. Basically, I am executing the update
> statement first, and if it returns 0 (meaning that the row didn't
> exist), then I translate the statement from an update to an insert...
> I am also doing something similar with create/alter statements, where I
> could test if the table/field exists in the DB before attempting to
> create or alter the table, but why bother if I can execute the one
> statement and then know if the table or field already existed based off
> of if the statement executed correctly or not... ?
> So, is there a faster way to say "insert OR update this data", in one
> statement, or should I just continue executing one, then the other if
> the first one fails?
> AB
>

Query to determine if something exists versus just trying to create it...

I have run into two situations in the recent past that both have the
same thing in common. I have to preface this with the fact that I am
running the following queries in a C#/.Net environment using SQL Server
Express...
The question is, and it may be silly, but, should one query for the
existance of a row in the DB before attempting to create it?
For example, I could write a query that says "select * from table where
id = 1". Then if the resulting dataset has one or 0 rows, I could
determine if I need to write a Update or Insert query to put in the
row. However, I am assuming that the rows will 99% of the time already
exist, but I need to update all of the fields in the table with
potentially new values. So the question is, in this case, it would be
silly to attempt to determine if the row is there, then to determine if
it should be updated or not. Basically, I am executing the update
statement first, and if it returns 0 (meaning that the row didn't
exist), then I translate the statement from an update to an insert...
I am also doing something similar with create/alter statements, where I
could test if the table/field exists in the DB before attempting to
create or alter the table, but why bother if I can execute the one
statement and then know if the table or field already existed based off
of if the statement executed correctly or not... ?
So, is there a faster way to say "insert OR update this data", in one
statement, or should I just continue executing one, then the other if
the first one fails?
AB> row. However, I am assuming that the rows will 99% of the time already
> exist, but I need to update all of the fields in the table with
> potentially new values.
If the usual case is that the row will exist, you might consider trying the
update first and then proceeding with the insert only if no rows were
updated. Something like:
CREATE PROC usp_SaveMyTable
@.MyTableId int,
@.SomeColumn int
AS
SET NOCOUNT, XACT_ABORT ON
DECLARE @.Error int, @.RowCount int
BEGIN TRAN
UPDATE dbo.MyTable
SET SomeColumn = @.SomeColumn
WHERE MyTableId = @.MyTableId
SELECT @.Error = @.@.ERROR, @.RowCount = @.@.ROWCOUNT
IF @.RowCount > 0 OR @.Error <> 0
BEGIN
GOTO Done
END
INSERT INTO dbo.MyTable(MyTableId, SomeColumn)
SELECT
@.MyTableId, @.SomeColumn
WHERE NOT EXISTS
(
SELECT *
FROM dbo.MyTable WITH (HOLDLOCK)
WHERE MyTableId = @.MyTableId
)
SELECT @.Error = @.@.ERROR
Done:
IF @.Error = 0
BEGIN
COMMIT
END
ELSE
BEGIN
ROLLBACK
END
GO

> I am also doing something similar with create/alter statements, where I
> could test if the table/field exists in the DB before attempting to
> create or alter the table, but why bother if I can execute the one
> statement and then know if the table or field already existed based off
> of if the statement executed correctly or not... ?
Personally, I prefer to avoid raising errors for expected conditions. If
you get error messages even when the script runs successfully, it's hard to
tell a real problem with all the noise.
IF OBJECT_ID('dbo.MyTable') IS NULL
BEGIN
CREATE TABLE dbo.MyTable
(
MyTableId int NOT NULL
CONSTRAINT PK_MyTable PRIMARY KEY,
SomeColumn int NOT NULL
)
END
Hope this helps.
Dan Guzman
SQL Server MVP
"Beavis" <multiformity@.gmail.com> wrote in message
news:1156385524.486272.138630@.p79g2000cwp.googlegroups.com...
>I have run into two situations in the recent past that both have the
> same thing in common. I have to preface this with the fact that I am
> running the following queries in a C#/.Net environment using SQL Server
> Express...
> The question is, and it may be silly, but, should one query for the
> existance of a row in the DB before attempting to create it?
> For example, I could write a query that says "select * from table where
> id = 1". Then if the resulting dataset has one or 0 rows, I could
> determine if I need to write a Update or Insert query to put in the
> row. However, I am assuming that the rows will 99% of the time already
> exist, but I need to update all of the fields in the table with
> potentially new values. So the question is, in this case, it would be
> silly to attempt to determine if the row is there, then to determine if
> it should be updated or not. Basically, I am executing the update
> statement first, and if it returns 0 (meaning that the row didn't
> exist), then I translate the statement from an update to an insert...
> I am also doing something similar with create/alter statements, where I
> could test if the table/field exists in the DB before attempting to
> create or alter the table, but why bother if I can execute the one
> statement and then know if the table or field already existed based off
> of if the statement executed correctly or not... ?
> So, is there a faster way to say "insert OR update this data", in one
> statement, or should I just continue executing one, then the other if
> the first one fails?
> AB
>

query the amount of transactions in a period of time with sql server 2000

Is there a native tool (profile,trace,performance) feature I can use
to determine the amount of transactions that occur throughout the day?
Or is there a system table that keeps track of this(would be
preferable .. less strain on the system)?
I assume figuring out the transaction in a certain period will enable
me to calculate the busiest periods...I need to know the busiest
period of the day...how do I do this without putting an additional
strain on the server (can I use a different machine other than the
server to save a trace) ...I need to determine strain on
(processor,memory, and disk).
I also need to get a count on the largest number of users (running
transactions) on the server simultaneously.
Any help/advice would be deeply appreciatedFor this to be really meaingful, you should first define what you mean by
transactions. Your definition of transactions can impact your count of
transactions per second.
But if you just want to get a rough idea and the number of SQL requests from
non-apps (e.g. your Enterprise Manager, your monitoring tools, your cluster
service, etc)is relatively small compared to the SQL requests from your apps,
the perfmon counter batch Requests/sec under SQLServer:SQL Statistics can
give you pretty good idea as to how busy your SQL instance is and when. And
collecting the values of this counter is inexpensive.
Linchi
"tom booster" wrote:
> Is there a native tool (profile,trace,performance) feature I can use
> to determine the amount of transactions that occur throughout the day?
> Or is there a system table that keeps track of this(would be
> preferable .. less strain on the system)?
> I assume figuring out the transaction in a certain period will enable
> me to calculate the busiest periods...I need to know the busiest
> period of the day...how do I do this without putting an additional
> strain on the server (can I use a different machine other than the
> server to save a trace) ...I need to determine strain on
> (processor,memory, and disk).
> I also need to get a count on the largest number of users (running
> transactions) on the server simultaneously.
>
> Any help/advice would be deeply appreciated
>