Query From 3 Database Servers

I have a t-sql query that I need to execute weekly on three different
database servers. The same t-sql query will executed on all three database
servers.
I have created DTS package with the three different database servers.
Should I create database link to the different database servers with a user
account that has db_datareader privileges to the database that is linked?
Please help me with this process.
Thanks,
Hi
I preseume that in the DTS package you have a connection to the given
database server? What do you specify as the login? Whatever login is used,
the associated database user will need enough privileges to run your
query/procedure. This should be kept to a minimum so if you are only wanting
to return data from a give table select privilege on that table should be
enough; if you use a stored procedure then you would (hopefully) be able to
use ownership chains and therefore only execute permission on the given
procedure will be necessary.
HTH
John
"Joe K." wrote:

> I have a t-sql query that I need to execute weekly on three different
> database servers. The same t-sql query will executed on all three database
> servers.
> I have created DTS package with the three different database servers.
> Should I create database link to the different database servers with a user
> account that has db_datareader privileges to the database that is linked?
> Please help me with this process.
> Thanks,

Query From 3 Database Servers

I have a t-sql query that I need to execute weekly on three different
database servers. The same t-sql query will executed on all three database
servers.
I have created DTS package with the three different database servers.
Should I create database link to the different database servers with a user
account that has db_datareader privileges to the database that is linked?
Please help me with this process.
Thanks,Hi
I preseume that in the DTS package you have a connection to the given
database server? What do you specify as the login? Whatever login is used,
the associated database user will need enough privileges to run your
query/procedure. This should be kept to a minimum so if you are only wanting
to return data from a give table select privilege on that table should be
enough; if you use a stored procedure then you would (hopefully) be able to
use ownership chains and therefore only execute permission on the given
procedure will be necessary.
HTH
John
"Joe K." wrote:

> I have a t-sql query that I need to execute weekly on three different
> database servers. The same t-sql query will executed on all three databas
e
> servers.
> I have created DTS package with the three different database servers.
> Should I create database link to the different database servers with a use
r
> account that has db_datareader privileges to the database that is linked?
> Please help me with this process.
> Thanks,

Query for Tables in a Database...

Good Morning

Shopping for help writing a query for my VB Program to execute against
SQL Server. Here is what I have so far:

SELECT name
FROM sysobjects
WHERE (xtype = 'U')

Is there a way to add to that query to get the list of tables that have the properties COST and PARTNUMBER?

Thanks,
EdUSE Northwind
GO

SELECT *
FROM INFORMATION_SCHEMA.Tables t
WHERE EXISTS (SELECT *
FROM INFORMATION_SCHEMA.Columns c
WHERE t.TABLE_NAME = c.TABLE_NAME
AND c.COLUMN_NAME IN ('Parts','Cost'))
GO|||thanks...

It took me a bit to translate your guidance into what SQL Server wanted.
I ended up with:

select name from sysobjects t where exists( select * from syscolumns c where t.id = c.id
and c.name in ('PartNumber', 'Cost'))
order by name

Thanks,
Ed|||Did you just try and cut and paste the code?

Look up INFORMATION_SCHEMA in Books online...

You want to use those over the system tables.

These are views, which use the system tables...|||IC what you mean now...

Here is what we have now.

SELECT distinct A.TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES A, INFORMATION_SCHEMA.COLUMNS B
WHERE A.TABLE_NAME = B.TABLE_NAME
and b.column_name in ('Partnumber', 'Cost')
ORDER BY A.TABLE_NAME

Thanks,
Ed|||Obviously you found out that the join will produce dupTablenames (because of the columns)...why didn't you just use what I gave you?|||the first time I tried it, no results were given. Thought I had to do some
heeby geeby to get it ready for use for my database. But then I noticed
the Parts column reference instead of Partnumber. Changed that and now it runs and returne the proper results.

Thanks,
ED

Query execution time

Hi,
Is there any command that i can use, that tells me with
precision the execution time of a query?
I'm using:
select getdate()
execute Query
select getdate()
Thanks,
Best regards
Have you tried: SET STATISTICS TIME ON ?
This will return a message with CPU time and elapsed time.
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:28b0701c46519$98104f50$a601280a@.phx.gbl...
> Hi,
> Is there any command that i can use, that tells me with
> precision the execution time of a query?
> I'm using:
> select getdate()
> execute Query
> select getdate()
> Thanks,
> Best regards
|||Look at the SET STATISTICS TIME statement. You might want to look ate SET
STATISTICS IO and SET STATISTICS PROFILE as additional performance tuning
tools.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:28b0701c46519$98104f50$a601280a@.phx.gbl...
> Hi,
> Is there any command that i can use, that tells me with
> precision the execution time of a query?
> I'm using:
> select getdate()
> execute Query
> select getdate()
> Thanks,
> Best regards

Query execution time

Hi,
Is there any command that i can use, that tells me with
precision the execution time of a query?
I'm using:
select getdate()
execute Query
select getdate()
Thanks,
Best regardsHave you tried: SET STATISTICS TIME ON ?
This will return a message with CPU time and elapsed time.
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:28b0701c46519$98104f50$a601280a@.phx
.gbl...
> Hi,
> Is there any command that i can use, that tells me with
> precision the execution time of a query?
> I'm using:
> select getdate()
> execute Query
> select getdate()
> Thanks,
> Best regards|||Look at the SET STATISTICS TIME statement. You might want to look ate SET
STATISTICS IO and SET STATISTICS PROFILE as additional performance tuning
tools.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:28b0701c46519$98104f50$a601280a@.phx
.gbl...
> Hi,
> Is there any command that i can use, that tells me with
> precision the execution time of a query?
> I'm using:
> select getdate()
> execute Query
> select getdate()
> Thanks,
> Best regards

Query execution time

Hi,
Is there any command that i can use, that tells me with
precision the execution time of a query?
I'm using:
select getdate()
execute Query
select getdate()
Thanks,
Best regardsHave you tried: SET STATISTICS TIME ON ?
This will return a message with CPU time and elapsed time.
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:28b0701c46519$98104f50$a601280a@.phx.gbl...
> Hi,
> Is there any command that i can use, that tells me with
> precision the execution time of a query?
> I'm using:
> select getdate()
> execute Query
> select getdate()
> Thanks,
> Best regards|||Look at the SET STATISTICS TIME statement. You might want to look ate SET
STATISTICS IO and SET STATISTICS PROFILE as additional performance tuning
tools.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:28b0701c46519$98104f50$a601280a@.phx.gbl...
> Hi,
> Is there any command that i can use, that tells me with
> precision the execution time of a query?
> I'm using:
> select getdate()
> execute Query
> select getdate()
> Thanks,
> Best regards

Query execution priority

When you execute several long duration queries simultaneous on the SQL
server, is it then possible to give the execution of one query a higher or
lower priority than another query?
Hi,
As far as I know no. The only priority that could be set deals with how the
query behave in case of deadlocking. see DEADLOCK_PRIORITY in BOL.
Lionel
"pehuan" wrote:

> When you execute several long duration queries simultaneous on the SQL
> server, is it then possible to give the execution of one query a higher or
> lower priority than another query?
|||You can set the maximum degree of parallelism (the number of processors to
use) for a query on a multi processor machine, using OPTION (MAXDOP n). This
doesn't really set priority directly, but it gives you influence over the
amount of resources each query can use.
Jacco Schalkwijk
SQL Server MVP
"pehuan" <pehuan@.discussions.microsoft.com> wrote in message
news:29D00740-2821-4995-9312-11E3E5AA8207@.microsoft.com...
> When you execute several long duration queries simultaneous on the SQL
> server, is it then possible to give the execution of one query a higher or
> lower priority than another query?
|||There is no need for priority on SQL Server worker threads because SQL
Server does not use preemptive multi-tasking; it uses the cooperative
multi-tasking of the Win 3.0 days. UMS contexts yield as required, unless,
of corse, there is some sort of error.
Sincerely,
Anthony Thomas

"pehuan" <pehuan@.discussions.microsoft.com> wrote in message
news:29D00740-2821-4995-9312-11E3E5AA8207@.microsoft.com...
When you execute several long duration queries simultaneous on the SQL
server, is it then possible to give the execution of one query a higher or
lower priority than another query?

Query execution priority

When you execute several long duration queries simultaneous on the SQL
server, is it then possible to give the execution of one query a higher or
lower priority than another query?Hi,
As far as I know no. The only priority that could be set deals with how the
query behave in case of deadlocking. see DEADLOCK_PRIORITY in BOL.
Lionel
"pehuan" wrote:
> When you execute several long duration queries simultaneous on the SQL
> server, is it then possible to give the execution of one query a higher or
> lower priority than another query?|||You can set the maximum degree of parallelism (the number of processors to
use) for a query on a multi processor machine, using OPTION (MAXDOP n). This
doesn't really set priority directly, but it gives you influence over the
amount of resources each query can use.
--
Jacco Schalkwijk
SQL Server MVP
"pehuan" <pehuan@.discussions.microsoft.com> wrote in message
news:29D00740-2821-4995-9312-11E3E5AA8207@.microsoft.com...
> When you execute several long duration queries simultaneous on the SQL
> server, is it then possible to give the execution of one query a higher or
> lower priority than another query?|||There is no need for priority on SQL Server worker threads because SQL
Server does not use preemptive multi-tasking; it uses the cooperative
multi-tasking of the Win 3.0 days. UMS contexts yield as required, unless,
of corse, there is some sort of error.
Sincerely,
Anthony Thomas
"pehuan" <pehuan@.discussions.microsoft.com> wrote in message
news:29D00740-2821-4995-9312-11E3E5AA8207@.microsoft.com...
When you execute several long duration queries simultaneous on the SQL
server, is it then possible to give the execution of one query a higher or
lower priority than another query?

Query execution priority

When you execute several long duration queries simultaneous on the SQL
server, is it then possible to give the execution of one query a higher or
lower priority than another query?Hi,
As far as I know no. The only priority that could be set deals with how the
query behave in case of deadlocking. see DEADLOCK_PRIORITY in BOL.
Lionel
"pehuan" wrote:

> When you execute several long duration queries simultaneous on the SQL
> server, is it then possible to give the execution of one query a higher or
> lower priority than another query?|||You can set the maximum degree of parallelism (the number of processors to
use) for a query on a multi processor machine, using OPTION (MAXDOP n). This
doesn't really set priority directly, but it gives you influence over the
amount of resources each query can use.
Jacco Schalkwijk
SQL Server MVP
"pehuan" <pehuan@.discussions.microsoft.com> wrote in message
news:29D00740-2821-4995-9312-11E3E5AA8207@.microsoft.com...
> When you execute several long duration queries simultaneous on the SQL
> server, is it then possible to give the execution of one query a higher or
> lower priority than another query?|||There is no need for priority on SQL Server worker threads because SQL
Server does not use preemptive multi-tasking; it uses the cooperative
multi-tasking of the Win 3.0 days. UMS contexts yield as required, unless,
of corse, there is some sort of error.
Sincerely,
Anthony Thomas
"pehuan" <pehuan@.discussions.microsoft.com> wrote in message
news:29D00740-2821-4995-9312-11E3E5AA8207@.microsoft.com...
When you execute several long duration queries simultaneous on the SQL
server, is it then possible to give the execution of one query a higher or
lower priority than another query?sql

Query estimate cost and real run time different

I have a query, before I do any change, the estimate execute plan show me th
e cost is 1400, the real run time is about 1 minute 33 seconds.
After I eliminated some concatinated column, the estimate execute plan shows
me cost reduce to 66, I expect the query will run quite faster than the ori
ginal one, but real run time still keep in 1 minute 27 seconds.
I know estimate execute cost is not accurate, but should not be such a big d
ifference.
Any body can tell me why, what is the good way to tunning my query?
Thanks in advance,
HGHG,
The best way to tune your query is to actually run it and see what it does.
Examine the execution plan and focus on the parts of the plan that are most
expensive. If one step takes 75% of the effort and five other steps each
take 5%, then examine the 75% and see what you can do about it. (Better
join criteria, another index, recasting the code to use a different
approach, etc.)
Estimated plan is sometimes mildly helpful, but (as you note) cannot be
relied upon. Also, even the actual execution plan has blind spots. For
example, it will not measure how much time is spent in a UDF, the IS_MEMBER
function, and so forth, viewing them as nearly free.
Therefore, in addition to the execution plan, test with timing statements
that show how much clock time the steps take. If you run several tests you
will get a good measure of the execution time.
Russell Fields
"HG" <anonymous@.discussions.microsoft.com> wrote in message
news:C01707AF-3B18-45AC-B188-52D313EBE860@.microsoft.com...
> I have a query, before I do any change, the estimate execute plan show me
the cost is 1400, the real run time is about 1 minute 33 seconds.
> After I eliminated some concatinated column, the estimate execute plan
shows me cost reduce to 66, I expect the query will run quite faster than
the original one, but real run time still keep in 1 minute 27 seconds.
> I know estimate execute cost is not accurate, but should not be such a big
difference.
> Any body can tell me why, what is the good way to tunning my query?
> Thanks in advance,
> HG|||I am new to sql. Can you provide an example of a timing statement that will
show me how long a querry took?|||Larry,
DECLARE @.StartTime DATETIME
SET @.StartTime = GETDATE()
Execute your code here
SELECT DATEDIFF(ms,@.StartTime, GETDATE()) AS ElapsedMilliseconds
Because there are variable, run this a few times to get a best time.
Compare best times of two different strategies to determine how they
compare.
Russell Fields
"Larrry Pensil" <anonymous@.discussions.microsoft.com> wrote in message
news:56CC0769-A7B1-4E97-ADE6-FEF9AF056A77@.microsoft.com...
> I am new to sql. Can you provide an example of a timing statement that
will show me how long a querry took?

query duration using parameters vrs no parameters

Hi,
I have an app in C# that executes a query using SQLCommand and parameters and is taking too much time to execute.

I open a SQLProfiler and this is what I have :

exec sp_executesql N' SELECT TranDateTime ... WHERE CustomerId = @.CustomerId',
N'@.CustomerId nvarchar(4000)', @.CustomerId = N'11111

I ran the same query directly from Query Analyzer and take the same amount of time to execute (about 8 seconds)

I decided to take the parameters out and concatenate the value and it takes less than 2 second to execute.

Here it comes the first question...
Why does using parameters takes way too much time more than not using parameters?

Then, I decided to move the query to a Stored Procedure and it executes in a snap too.
The only problem I have using a SP is that the query can receive more than 1 parameter and up to 5 parameters, which is easy to build in the application but not in the SP

I usually do it something like
(@.CustomerId is null or CustomerId = @.CustomerId) but it generate a table scan and with a table with a few mills of records is not a good idea to have such scan.

Is there a way to handle "dynamic parameters" in a efficient way?

Did you observe any difference in query plans?

Thanks