Query Governor

Hello all,
Im setting up a SQL-Server and there is an option named Query
Governor to avoid queries to exceed a specific cost. How is this cost
measured ? Milliseconds ?
best regards,
Evandro
You can set this in enterprise manager under server properties using the
server settings tab.
Or
EXEC sp_configure 'show advanced option', '1'
exec sp_configure N'query governor cost limit', 100 (or the max elapsed time
in seconds)
http://www.schemamania.org/jkl/books..._server_51.htm
http://msdn.microsoft.com/library/de...onfig_73u6.asp
"Evandro Braga" <evandro_braga@.hotmail.com> wrote in message
news:eBM64z28EHA.1396@.tk2msftngp13.phx.gbl...
> Hello all,
> Im setting up a SQL-Server and there is an option named Query
> Governor to avoid queries to exceed a specific cost. How is this cost
> measured ? Milliseconds ?
>
> best regards,
> Evandro
>
|||Hi Evandro,
EXEC sp_configure N'query governor cost limit', 100 is a server-wide
setting. Unless you have a very specific reason, do not set this option.
At the client level, statementwise one can use
SET QUERY_GOVERNOR_COST_LIMIT
Thanks
Yogish

Query Governor

Hello all,
I´m setting up a SQL-Server and there is an option named Query
Governor to avoid queries to exceed a specific cost. How is this cost
measured ? Milliseconds ?
best regards,
EvandroYou can set this in enterprise manager under server properties using the
server settings tab.
Or
EXEC sp_configure 'show advanced option', '1'
exec sp_configure N'query governor cost limit', 100 (or the max elapsed time
in seconds)
http://www.schemamania.org/jkl/booksonline/SQLBOL70/html/1_server_51.htm
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_config_73u6.asp
"Evandro Braga" <evandro_braga@.hotmail.com> wrote in message
news:eBM64z28EHA.1396@.tk2msftngp13.phx.gbl...
> Hello all,
> I´m setting up a SQL-Server and there is an option named Query
> Governor to avoid queries to exceed a specific cost. How is this cost
> measured ? Milliseconds ?
>
> best regards,
> Evandro
>|||Hi Evandro,
EXEC sp_configure N'query governor cost limit', 100 is a server-wide
setting. Unless you have a very specific reason, do not set this option.
At the client level, statementwise one can use
SET QUERY_GOVERNOR_COST_LIMIT
--
Thanks
Yogishsql

Query Governor

Hello all,
Im setting up a SQL-Server and there is an option named Query
Governor to avoid queries to exceed a specific cost. How is this cost
measured ? Milliseconds ?
best regards,
EvandroYou can set this in enterprise manager under server properties using the
server settings tab.
Or
EXEC sp_configure 'show advanced option', '1'
exec sp_configure N'query governor cost limit', 100 (or the max elapsed time
in seconds)
http://www.schemamania.org/jkl/book...1_server_51.htm
http://msdn.microsoft.com/library/d... />
g_73u6.asp
"Evandro Braga" <evandro_braga@.hotmail.com> wrote in message
news:eBM64z28EHA.1396@.tk2msftngp13.phx.gbl...
> Hello all,
> Im setting up a SQL-Server and there is an option named Query
> Governor to avoid queries to exceed a specific cost. How is this cost
> measured ? Milliseconds ?
>
> best regards,
> Evandro
>|||Hi Evandro,
EXEC sp_configure N'query governor cost limit', 100 is a server-wide
setting. Unless you have a very specific reason, do not set this option.
At the client level, statementwise one can use
SET QUERY_GOVERNOR_COST_LIMIT
Thanks
Yogish

Query Governer

Hi,
Can I define limitation on consuming CPU/IO for specific database user ?
We have an application that it's database resource consumption should be
restricted.
I can't use query governor because it puts global restriction for all users
(am i correct?)
Thanks,
AllanNo, sorry.
"A.M" <IHateSpam@.sapm123.com> wrote in message
news:OczCnokIEHA.3664@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Can I define limitation on consuming CPU/IO for specific database user ?
> We have an application that it's database resource consumption should be
> restricted.
> I can't use query governor because it puts global restriction for all
users
> (am i correct?)
> Thanks,
> Allan
>

Query Governer

Hi,
Can I define limitation on consuming CPU/IO for specific database user ?
We have an application that it's database resource consumption should be
restricted.
I can't use query governor because it puts global restriction for all users
(am i correct?)
Thanks,
AllanNo, sorry.
"A.M" <IHateSpam@.sapm123.com> wrote in message
news:OczCnokIEHA.3664@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Can I define limitation on consuming CPU/IO for specific database user ?
> We have an application that it's database resource consumption should be
> restricted.
> I can't use query governor because it puts global restriction for all
users
> (am i correct?)
> Thanks,
> Allan
>

Query Governer

Hi,
Can I define limitation on consuming CPU/IO for specific database user ?
We have an application that it's database resource consumption should be
restricted.
I can't use query governor because it puts global restriction for all users
(am i correct?)
Thanks,
Allan
No, sorry.
"A.M" <IHateSpam@.sapm123.com> wrote in message
news:OczCnokIEHA.3664@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Can I define limitation on consuming CPU/IO for specific database user ?
> We have an application that it's database resource consumption should be
> restricted.
> I can't use query governor because it puts global restriction for all
users
> (am i correct?)
> Thanks,
> Allan
>
sql

Query Entire DB

I need to search all of the tables in a DB for a specific number (say, 123456). I am unsure of which tables may have this number in it. Is there a way to search an entire DB for a specific criteria?

This is what I want:

Select *

From [db]

where [column] like '%123456%'

I understand why this dosen't work (the DB doesn't have column names), but is there a way to display table names where this number exists? If I had that, then I could search the tables individually.

Yes, this can be done. Here is a link where there is a stored procedure that you can use to search for a string in all tables...

http://searchsqlserver.techtarget.com/tip/1,289483,sid87_gci1125053,00.html

|||

This is exactly what I was looking for. Thanks!

Have you used this toward a Microsoft Dynamics company db?

Query Entire DB

I need to search all of the tables in a DB for a specific number (say, 123456). I am unsure of which tables may have this number in it. Is there a way to search an entire DB for a specific criteria?

This is what I want:

Select *

From [db]

where [column] like '%123456%'

I understand why this dosen't work (the DB doesn't have column names), but is there a way to display table names where this number exists? If I had that, then I could search the tables individually.

Yes, this can be done. Here is a link where there is a stored procedure that you can use to search for a string in all tables...

http://searchsqlserver.techtarget.com/tip/1,289483,sid87_gci1125053,00.html

|||

This is exactly what I was looking for. Thanks!

Have you used this toward a Microsoft Dynamics company db?

Query by database or code

I have a database and would like to retrieve specific data via queries. This database is also connected to an ASP .Net 2.0 application to be the front end. Ive created the query in the database. Would you recommend i use parameter names to retrieve the data via code or should i have the query within my code to retrieve the data?

Thanks

I'm not exactly sure what you are asking. Are you asking whether or not to do your database access from the ASPX page or the code-behind? Or, are you asking if you should parameterize your queries?

|||

Im using ASP.Net to create a website and heres and example of how i retrieve the data:

Dim strCommandText as String= "SELECT * FROM Player INNER JOIN Manufacturer ON Player.PlayerManufacturerID =
Manufacturer.ManufacturerID ORDER BY Player.PlayerName"
Dim myCommand As SqlCommand = new SqlCommand(strCommandText, myConnection)

Now above i write the SQL syntax into my application to retrieve data. If i have a query created within the DATABASE called ParameterName then i can write the code as:

MyCommand.Paramters.Add(ParameterName)

So my question is really asking which method is approved and why as theyre both doing the same thing?

Thanks

|||

If I still misunderstood your question, then my apologies, but I think what you're asking is if your Select statement requires parameters, then should I inject them directly into my statement, or use a parameter, and add the parameters afterwards. If this is what you're truly asking, then I'd definetely suggest the use of parameters as they create a much less error-proned design. Here's an article on the subject:

http://www.4guysfromrolla.com/webtech/092601-1.shtml

|||

The "common" best practice approach would be to have a separate class library that will return the "Players" via a stored procedure. I would also recommend not using SELECT *, you should only select the fields you need.

|||

If I still misunderstood your question, then my apologies, but I think what you're asking is if your Select statement requires parameters, then should I inject them directly into my statement, or use a parameter, and add the parameters afterwards. If this is what you're truly asking, then I'd definetely suggest the use of parameters as they create a much less error-proned design. Here's an article on the subject:

http://www.4guysfromrolla.com/webtech/092601-1.shtml

Not quite. Im just trying to see if i should use a Select query in my code or should i call aparameter Stored Procedure from the database to retrieve the data. Sorry my fault i was using the wrong terminology, i should have said Stored Procedure but got my wires crossed as ASP .Net terms this as a parameter being passed in to the code your writing

jguadagno:

The "common" best practice approach would be to have a separate class library that will return the "Players" via a stored procedure. I would also recommend not using SELECT *, you should only select the fields you need.

I think this may have answered the question. Where can i get started with Class libraries? also thanks for advising not to use SELECT * - i only used that in this question as an example.

Thanks guys

|||

EssCee:

I think this may have answered the question. Where can i get started with Class libraries?

http://www.15seconds.com/issue/050721.htm

Query Assistance - Average Days Between Services

Hi,
I need some help writing a two queries to determine the average number of
days between services for 1. a specific machineid 2. for all specific
machineids.
The table contains many columns including a MachineID column (INT) and a
ServiceDate column (DATETIME) so sample data (excluding other columns) would
look like:
MachineID ServiceDate
123 2005-01-14 00:00:00
123 2005-02-10 00:00:00
123 2005-03-14 00:00:00
124 2005-02-18 00:00:00
123 2005-05-14 00:00:00
124 2005-03-14 00:00:00
124 2005-05-14 00:00:00
The is no IDENTITY column on the table.
So the resultsets would resemble:
1. For a specific machineid
MachineID Average Days Between Services
123 40
2. For all machineids
MachineID Average Days Between Services
123 40
124 42.5
It seems simple but I'm struggling with this one!
Please let me know if you need additional information.
Thanks
JerryJerry,
I think this will do what you want. If you want all MachineID values
listed, even if there is only one ServiceDate, it would help to have a
table of MachineID values, which you can LEFT JOIN so you get
them to appear with NULL average if they appear fewer than twice
in the service table.
If you want the dates to be interpreted correctly in all locales, add
the T between the date and time. The format you are using is not
independent of language and dateformat setting.
Steve Kass
Drew University
set nocount on
go
create table T (
MachineID int,
ServiceDate datetime
)
insert into T values (123,'2005-01-14T00:00:00')
insert into T values (123,'2005-02-10T00:00:00')
insert into T values (123,'2005-03-14T00:00:00')
insert into T values (124,'2005-02-18T00:00:00')
insert into T values (123,'2005-05-14T00:00:00')
insert into T values (124,'2005-03-14T00:00:00')
insert into T values (124,'2005-05-14T00:00:00')
go
select
MachineID, avg(Gap) as AvgGap
from (
select
T1.MachineID,
1.0*datediff(day,T1.ServiceDate,min(T2.ServiceDate)) as Gap
from T as T1
join T as T2
on T2.MachineID = T1.MachineID
where T2.MachineID = T1.MachineID
and T2.ServiceDate > T1.ServiceDate
group by T1.MachineID, T1.ServiceDate
) T
group by MachineID
go
drop table T
Jerry Spivey wrote:

>Hi,
>I need some help writing a two queries to determine the average number of
>days between services for 1. a specific machineid 2. for all specific
>machineids.
>The table contains many columns including a MachineID column (INT) and a
>ServiceDate column (DATETIME) so sample data (excluding other columns) woul
d
>look like:
>MachineID ServiceDate
>123 2005-01-14 00:00:00
>123 2005-02-10 00:00:00
>123 2005-03-14 00:00:00
>124 2005-02-18 00:00:00
>123 2005-05-14 00:00:00
>124 2005-03-14 00:00:00
>124 2005-05-14 00:00:00
>The is no IDENTITY column on the table.
>So the resultsets would resemble:
>1. For a specific machineid
>MachineID Average Days Between Services
>123 40
>2. For all machineids
>MachineID Average Days Between Services
>123 40
>124 42.5
>It seems simple but I'm struggling with this one!
>Please let me know if you need additional information.
>Thanks
>Jerry
>
>
>
>
>
>|||Try,
use northwind
go
create table t1 (
MachineID int not null,
ServiceDate datetime not null,
constraint pk_t1 primary key (MachineID, ServiceDate)
)
go
insert into t1 values(123, '2005-01-14 00:00:00')
insert into t1 values(123, '2005-02-10 00:00:00')
insert into t1 values(123, '2005-03-14 00:00:00')
insert into t1 values(124, '2005-02-18 00:00:00')
insert into t1 values(123, '2005-05-14 00:00:00')
insert into t1 values(124, '2005-03-14 00:00:00')
insert into t1 values(124, '2005-05-14 00:00:00')
go
create view v1
as
select
a.MachineID,
a.ServiceDate,
datediff(day, b.ServiceDate, a.ServiceDate) * 1.0 as days_since_last_serv
from
t1 as a
inner join
t1 as b
on a.MachineID = b.MachineID
and b.ServiceDate = (select max(c.ServiceDate) from t1 as c where
c.MachineID = a.MachineID and c.ServiceDate < a.ServiceDate)
where
datediff(day, b.ServiceDate, a.ServiceDate) is not null
go
select
*
from
v1
order by
MachineID,
ServiceDate
go
select
MachineID,
avg(days_since_last_serv) as [Average Days Between Services]
from
v1
group by
MachineID
order by
MachineID
go
select
MachineID,
avg(days_since_last_serv) as [Average Days Between Services]
from
v1
where
MachineID = 123
group by
MachineID
go
drop view v1
go
drop table t1
go
AMB
"Jerry Spivey" wrote:

> Hi,
> I need some help writing a two queries to determine the average number of
> days between services for 1. a specific machineid 2. for all specific
> machineids.
> The table contains many columns including a MachineID column (INT) and a
> ServiceDate column (DATETIME) so sample data (excluding other columns) wou
ld
> look like:
> MachineID ServiceDate
> 123 2005-01-14 00:00:00
> 123 2005-02-10 00:00:00
> 123 2005-03-14 00:00:00
> 124 2005-02-18 00:00:00
> 123 2005-05-14 00:00:00
> 124 2005-03-14 00:00:00
> 124 2005-05-14 00:00:00
> The is no IDENTITY column on the table.
> So the resultsets would resemble:
> 1. For a specific machineid
> MachineID Average Days Between Services
> 123 40
> 2. For all machineids
> MachineID Average Days Between Services
> 123 40
> 124 42.5
> It seems simple but I'm struggling with this one!
> Please let me know if you need additional information.
> Thanks
> Jerry
>
>
>
>
>
>|||SELECT
MachineID,
(datediff(d, min(ServiceDate), max(ServiceDate))/ (count(machineid)-1)) as
AverageDays FROM TABLE1
GROUP BY MachineId
ORDER BY Machineid
--
Programmer
"Jerry Spivey" wrote:

> Hi,
> I need some help writing a two queries to determine the average number of
> days between services for 1. a specific machineid 2. for all specific
> machineids.
> The table contains many columns including a MachineID column (INT) and a
> ServiceDate column (DATETIME) so sample data (excluding other columns) wou
ld
> look like:
> MachineID ServiceDate
> 123 2005-01-14 00:00:00
> 123 2005-02-10 00:00:00
> 123 2005-03-14 00:00:00
> 124 2005-02-18 00:00:00
> 123 2005-05-14 00:00:00
> 124 2005-03-14 00:00:00
> 124 2005-05-14 00:00:00
> The is no IDENTITY column on the table.
> So the resultsets would resemble:
> 1. For a specific machineid
> MachineID Average Days Between Services
> 123 40
> 2. For all machineids
> MachineID Average Days Between Services
> 123 40
> 124 42.5
> It seems simple but I'm struggling with this one!
> Please let me know if you need additional information.
> Thanks
> Jerry
>
>
>
>
>
>|||CREATE TABLE ServiceLog
(machine_id INTEGER NOT NULL,
service_date DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL
CHECK (service_date
= CAST(CEILING (CAST(service_date AS FLOAT)) AS DATETIME)), --drop
time
PRIMARY KEY (machine_id, service_date));
INSERT INTO ServiceLog VALUES (123, '2005-01-14 00:00:00');
INSERT INTO ServiceLog VALUES (123, '2005-02-10 00:00:00');
INSERT INTO ServiceLog VALUES (123, '2005-03-14 00:00:00');
INSERT INTO ServiceLog VALUES (123, '2005-05-14 00:00:00');
INSERT INTO ServiceLog VALUES (124, '2005-02-18 00:00:00');
INSERT INTO ServiceLog VALUES (124, '2005-03-14 00:00:00');
INSERT INTO ServiceLog VALUES (124, '2005-05-14 00:00:00');
SELECT machine_id,
DATEDIFF(DD, MIN(service_date), MAX(service_date))
/ (1.0 *COUNT(*)) AS avg_gap
FROM ServiceLog
GROUP BY machine_id;
This gives me:
macine_id avg_gap
===============
123 30.00
124 28.33
Which look more correct than your 40 days just by eyeballing it -- i.e.
you service things around the 15-th of each month. I did this problem
years ago and got caught in the "procedural mindset" trap like Steve
did. This where you compute each INDIVIDUAL gap between events and
then use an average function on them. Instead think of each machine as
a grouping (subset) that has properties as a whole -- duration range,
and count of events.|||Ok you three are absolutely brilliant!!!
Now just trying to figure out the logic that you used :-) May have a few
questions for you in a few.
Thanks again!!!!
Jerry
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:O5sExXgYFHA.3840@.tk2msftngp13.phx.gbl...
> Hi,
> I need some help writing a two queries to determine the average number of
> days between services for 1. a specific machineid 2. for all specific
> machineids.
> The table contains many columns including a MachineID column (INT) and a
> ServiceDate column (DATETIME) so sample data (excluding other columns)
> would look like:
> MachineID ServiceDate
> 123 2005-01-14 00:00:00
> 123 2005-02-10 00:00:00
> 123 2005-03-14 00:00:00
> 124 2005-02-18 00:00:00
> 123 2005-05-14 00:00:00
> 124 2005-03-14 00:00:00
> 124 2005-05-14 00:00:00
> The is no IDENTITY column on the table.
> So the resultsets would resemble:
> 1. For a specific machineid
> MachineID Average Days Between Services
> 123 40
> 2. For all machineids
> MachineID Average Days Between Services
> 123 40
> 124 42.5
> It seems simple but I'm struggling with this one!
> Please let me know if you need additional information.
> Thanks
> Jerry
>
>
>
>
>|||God, I get sloppy! I forgot to remove one of the days at the end of the
total duration.
SELECT machine_id,
DATEDIFF(DD, MIN(service_date), MAX(service_date))
/ (1.0 *COUNT(*) -1) AS avg_gap
FROM ServiceLog
GROUP BY machine_id;|||Sergey,
If I add only record for a machine I get a divide by zero error. How can I
fix that just in case the data contains only one record for a machineid?
Thanks again.
Jerry
"Sergey Zuyev" <SergeyZuyev@.discussions.microsoft.com> wrote in message
news:6CE8FE0C-FCF0-4E3C-AAF6-F395485F32C4@.microsoft.com...
> SELECT
> MachineID,
> (datediff(d, min(ServiceDate), max(ServiceDate))/ (count(machineid)-1))
> as
> AverageDays FROM TABLE1
> GROUP BY MachineId
> ORDER BY Machineid
> --
> Programmer
>
> "Jerry Spivey" wrote:
>|||I think I got it - added a HAVING COUNT(*) > 1 to the query.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23t5HC4gYFHA.2124@.TK2MSFTNGP14.phx.gbl...
> Sergey,
> If I add only record for a machine I get a divide by zero error. How can
> I fix that just in case the data contains only one record for a machineid?
> Thanks again.
> Jerry
> "Sergey Zuyev" <SergeyZuyev@.discussions.microsoft.com> wrote in message
> news:6CE8FE0C-FCF0-4E3C-AAF6-F395485F32C4@.microsoft.com...
>|||something like that, but im not sure that is the best approach
SELECT
MachineID,
(datediff(d, min(ServiceDate), max(ServiceDate))/ Case
(Count(MachineID)-1) WHEN 0 THEN 1 ELSE (Count(MachineID)-1) END) as
AverageDays FROM TABLE1
GROUP BY MachineId
ORDER BY Machineid
--
Programmer
"Jerry Spivey" wrote:

> Sergey,
> If I add only record for a machine I get a divide by zero error. How can
I
> fix that just in case the data contains only one record for a machineid?
> Thanks again.
> Jerry
> "Sergey Zuyev" <SergeyZuyev@.discussions.microsoft.com> wrote in message
> news:6CE8FE0C-FCF0-4E3C-AAF6-F395485F32C4@.microsoft.com...
>
>