Query Help

Just started working at a place which has a SP which runs a loop and gets
the id.
Then this id is used as follows:
SELECT name, 0 from tbl
WHERE name like '%'+id+'%' AND xtype = 'u'
Basically to get the names of the tables that have the "id" as part of the
name. How can I avoid going in a loop on this query, say if I have the list
of IDs in a temp table.
Thanks.Try,
select distinct
a.table_name
from
information_schema.tables as a
inner join
ids as b
on a.table_name like '%' + col_id + '%'
and a.table_type = 'base table'
order by
a.table_name
AMB
"XXX" wrote:

> Just started working at a place which has a SP which runs a loop and gets
> the id.
>
> Then this id is used as follows:
> SELECT name, 0 from tbl
> WHERE name like '%'+id+'%' AND xtype = 'u'
>
> Basically to get the names of the tables that have the "id" as part of the
> name. How can I avoid going in a loop on this query, say if I have the lis
t
> of IDs in a temp table.
>
> Thanks.
>
>

query hangs

Hi,
Have a query that runs okay in SQL 2000 but when I try to run the same query
in 2005 it hans the server.
any suggestions
thxWhat is the nature of the query?
What are the differences in hardware?
Are you using the same network connection?
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
Make SQL Server faster - www.quicksqlserver.com
___________________________________
"stoney" <stoney@.discussions.microsoft.com> wrote in message
news:FB83E362-FA12-471C-AA36-52F1C0DDD3FA@.microsoft.com...
> Hi,
> Have a query that runs okay in SQL 2000 but when I try to run the same
query
> in 2005 it hans the server.
> any suggestions
> thx|||stoney wrote:
> Hi,
> Have a query that runs okay in SQL 2000 but when I try to run the same que
ry
> in 2005 it hans the server.
> any suggestions
> thx
Begin by comparing the Estimated Execution Plan for the query in 2000
vs. the plan in 2005. Are they different? Assuming the database in
2005 was migrated from 2000, did you update statistics after the migration?
When the query hangs, check the master..sysprocesses table, does
anything show as being blocked?
Tracy McKibben
MCDBA
http://www.realsqlguy.comsql

query hangs

Hi,
Have a query that runs okay in SQL 2000 but when I try to run the same query
in 2005 it hans the server.
any suggestions
thxWhat is the nature of the query?
What are the differences in hardware?
Are you using the same network connection?
--
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
Make SQL Server faster - www.quicksqlserver.com
___________________________________
"stoney" <stoney@.discussions.microsoft.com> wrote in message
news:FB83E362-FA12-471C-AA36-52F1C0DDD3FA@.microsoft.com...
> Hi,
> Have a query that runs okay in SQL 2000 but when I try to run the same
query
> in 2005 it hans the server.
> any suggestions
> thx|||stoney wrote:
> Hi,
> Have a query that runs okay in SQL 2000 but when I try to run the same query
> in 2005 it hans the server.
> any suggestions
> thx
Begin by comparing the Estimated Execution Plan for the query in 2000
vs. the plan in 2005. Are they different? Assuming the database in
2005 was migrated from 2000, did you update statistics after the migration?
When the query hangs, check the master..sysprocesses table, does
anything show as being blocked?
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Query execution plan different between production/test - same data

I have a query that runs much much slower on my production server than my test server. By tweaking the query I can make it run fast in production. Why can't I make prod act like test? The difference in execution time is 15 minutes! (test - 5 seconds/prod
- 15 minutes). I need to make PROD more like TEST, or at least TEST should be slower than PROD (what is the point of load testing?)
My test server has the same SQL version/patch level and OS version/patch level as my production server. The data is a replica. The query execution plans are different. The production server has more ram, faster CPUs (dual), and faster disk. Production is
under light utilization (<20%). It is reindexed each night. I have updated statistics on all tables involved. I ran a server configuration and schema configuration comparison with RedGate software and all items are indenticle (save ram/cpu/names of jobs).
The prod server has 2 cpu so I tried restricting parallelism to 1. When the slow query runs on production it chews one of the CPU's @.50% for all 15 minutes. The problem has slowly gotten worse of the past month. The database is only 120mb and is the only
db so far on this server. The DB server is dedicated, there are no other uses for it. Its paired app server is under very low utilization also. As I said, by tweaking the query the query time goes from 15 minutes down to 4 seconds without changing the re
sult set. The main gotcha is that my test server runs the fast execution plan with or without tweaking the query. I need it to run fast without tweaking so my users can run ad hoc queries without me having to get involved. I know it is possible, look at m
y test box!!
I am stumped on this performance problem. Please help if you can with any thoughts or suggestions for getting MSSQL to run consistant with respect to execution plans between my two servers. Our Oracle DBA is laughing...
There are many explanations for difference in query behaviour and
execution. The same query can behave differently on different boxes
depending on configuration and general environfment specific to a
particular box.
You might want to refer to
243588 HOW TO: Troubleshoot the Performance of Ad-Hoc Queries
http://support.microsoft.com/?id=243588
Things you might want to pay attention to :-
* The tables or views the query touches - are they identical on both the
'good' (Test) and the 'bad' (Production) sql servers? Meaning, do they have
identical indexes (you can check doing a sp_help on the tables) OR do they
have the same kind of data and data distribution?
* How often are statistics updated on the good and bad sql servers? Try
doing an UPDATE STATISTICS myTable WITH FULLSCAN on each of the tables that
are accessed by this query. (You might want to do it off peak hours though).
* run the query through the Index Tuning Wizard - see if it recommends any
additional non clustered or covering indexes.
* General load on the server(s), are they the same?
Hope that helps.
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

Query execution plan different between production/test - same data

I have a query that runs much much slower on my production server than my te
st server. By tweaking the query I can make it run fast in production. Why c
an't I make prod act like test? The difference in execution time is 15 minut
es! (test - 5 seconds/prod
- 15 minutes). I need to make PROD more like TEST, or at least TEST should b
e slower than PROD (what is the point of load testing?)
My test server has the same SQL version/patch level and OS version/patch lev
el as my production server. The data is a replica. The query execution plans
are different. The production server has more ram, faster CPUs (dual), and
faster disk. Production is
under light utilization (<20%). It is reindexed each night. I have updated s
tatistics on all tables involved. I ran a server configuration and schema co
nfiguration comparison with RedGate software and all items are indenticle (s
ave ram/cpu/names of jobs).
The prod server has 2 cpu so I tried restricting parallelism to 1. When the
slow query runs on production it chews one of the CPU's @.50% for all 15 minu
tes. The problem has slowly gotten worse of the past month. The database is
only 120mb and is the only
db so far on this server. The DB server is dedicated, there are no other use
s for it. Its paired app server is under very low utilization also. As I sai
d, by tweaking the query the query time goes from 15 minutes down to 4 secon
ds without changing the re
sult set. The main gotcha is that my test server runs the fast execution pla
n with or without tweaking the query. I need it to run fast without tweaking
so my users can run ad hoc queries without me having to get involved. I kno
w it is possible, look at m
y test box!!
I am stumped on this performance problem. Please help if you can with any th
oughts or suggestions for getting MSSQL to run consistant with respect to ex
ecution plans between my two servers. Our Oracle DBA is laughing...There are many explanations for difference in query behaviour and
execution. The same query can behave differently on different boxes
depending on configuration and general environfment specific to a
particular box.
You might want to refer to
243588 HOW TO: Troubleshoot the Performance of Ad-Hoc Queries
http://support.microsoft.com/?id=243588
Things you might want to pay attention to :-
* The tables or views the query touches - are they identical on both the
'good' (Test) and the 'bad' (Production) sql servers? Meaning, do they have
identical indexes (you can check doing a sp_help on the tables) OR do they
have the same kind of data and data distribution?
* How often are statistics updated on the good and bad sql servers? Try
doing an UPDATE STATISTICS myTable WITH FULLSCAN on each of the tables that
are accessed by this query. (You might want to do it off peak hours though).
* run the query through the Index Tuning Wizard - see if it recommends any
additional non clustered or covering indexes.
* General load on the server(s), are they the same?
Hope that helps.
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

Query execution plan different between production/test - same data

I have a query that runs much much slower on my production server than my test server. By tweaking the query I can make it run fast in production. Why can't I make prod act like test? The difference in execution time is 15 minutes! (test - 5 seconds/prod
- 15 minutes). I need to make PROD more like TEST, or at least TEST should be slower than PROD (what is the point of load testing?)
My test server has the same SQL version/patch level and OS version/patch level as my production server. The data is a replica. The query execution plans are different. The production server has more ram, faster CPUs (dual), and faster disk. Production is
under light utilization (<20%). It is reindexed each night. I have updated statistics on all tables involved. I ran a server configuration and schema configuration comparison with RedGate software and all items are indenticle (save ram/cpu/names of jobs).
The prod server has 2 cpu so I tried restricting parallelism to 1. When the slow query runs on production it chews one of the CPU's @.50% for all 15 minutes. The problem has slowly gotten worse of the past month. The database is only 120mb and is the only
db so far on this server. The DB server is dedicated, there are no other uses for it. Its paired app server is under very low utilization also. As I said, by tweaking the query the query time goes from 15 minutes down to 4 seconds without changing the re
sult set. The main gotcha is that my test server runs the fast execution plan with or without tweaking the query. I need it to run fast without tweaking so my users can run ad hoc queries without me having to get involved. I know it is possible, look at m
y test box!!
I am stumped on this performance problem. Please help if you can with any thoughts or suggestions for getting MSSQL to run consistant with respect to execution plans between my two servers. Our Oracle DBA is laughing...
Can you show the query? You say you tweaked it? How did you do that? Is
the data the exact same on both machines? What does the query plan look like
for the slow one compared to the fast one?
Andrew J. Kelly SQL MVP
"Aaron" <Aaron@.discussions.microsoft.com> wrote in message
news:33F423DF-1D9C-4227-B1F4-2CAF5142BD9C@.microsoft.com...
> I have a query that runs much much slower on my production server than my
test server. By tweaking the query I can make it run fast in production. Why
can't I make prod act like test? The difference in execution time is 15
minutes! (test - 5 seconds/prod - 15 minutes). I need to make PROD more like
TEST, or at least TEST should be slower than PROD (what is the point of load
testing?)
> My test server has the same SQL version/patch level and OS version/patch
level as my production server. The data is a replica. The query execution
plans are different. The production server has more ram, faster CPUs (dual),
and faster disk. Production is under light utilization (<20%). It is
reindexed each night. I have updated statistics on all tables involved. I
ran a server configuration and schema configuration comparison with RedGate
software and all items are indenticle (save ram/cpu/names of jobs). The prod
server has 2 cpu so I tried restricting parallelism to 1. When the slow
query runs on production it chews one of the CPU's @.50% for all 15 minutes.
The problem has slowly gotten worse of the past month. The database is only
120mb and is the only db so far on this server. The DB server is dedicated,
there are no other uses for it. Its paired app server is under very low
utilization also. As I said, by tweaking the query the query time goes from
15 minutes down to 4 seconds without changing the result set. The main
gotcha is that my test server runs the fast execution plan with or without
tweaking the query. I need it to run fast without tweaking so my users can
run ad hoc queries without me having to get involved. I know it is possible,
look at my test box!!
> I am stumped on this performance problem. Please help if you can with any
thoughts or suggestions for getting MSSQL to run consistant with respect to
execution plans between my two servers. Our Oracle DBA is laughing...
>

Query Execution Failed for Dataset (Beginner)

I got this Error, Query Execution for Dataset 'Source'.

When i try to run a report that is actually a drillthrough from another report. It runs fine in Report Designer, and when i deploy to my Local server. But when i deploy it to my virtual Report Server, I get this error messege. Why is it doing this? and where can i see errors for this type of stuff, so i can figure this out. This uses the same Datasource as the report linked from it. That report works fine, why wont this one? any ideas?

Look for the report server log at this location C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\LogFiles if installed to the default location.|||A collegue of mine helped me figure this one out. My stored procedure was not set to public in the properties section. Thats why it worked on my local computer and not on the report server.

query cannot return any results

I have a very uncommon problem ... I have a application that runs like this:
1) it receives users inserts, including a Status = 'U' field;
2) based on a Status field (index) the application query and select the last
inserted registries all day long, each 30 seconds;
3) every time it read the registries it changes the Status field to 'R'.
The problem is that after about 24 hours, the query identifies no longer
registries with that index. It returns nothing. I use a execurereader
command, and it happens that myreader.hasrows = false, even if there are row
s
with Status field = 'U'
Can somebody help me to know what is happening?
--
Sergio R Piresquery optimiser should use best available index or column stats to decide
strategy, and this may be cached for long period.
Maybe stats get recomputed automatically if you make lotsa changes and have
updatestats dboption set, or explicitly by your DBA [recommendation used to
be to do explicitly due to excessive overhead but nowadays with autonomics
MSSQL does the right thing].
If you truncate/delete staging table [daily] just before query is compiled
into cache it may decide to use tablescan even if index available [since so
_few_ rows] and this may persist some time even if cardinality builds up a
lot.
Unfortunately the sysindexes.rowcnt cannot be relied on for accuracy [due to
transaction activity], so you may have to force count(*) to get real count
but this has locking issues [nolock would only give approx count like
sysindexes].
Dependencies can be omitted from sysdepends [to support forward compilation]
so optimiser may be similarly ignorant.
I suspect the optimiser is getting , so suggest that
1. check latest Service Pack applied
2. exec sp_dboption 'pubs','auto create statistics','on' -- substitute
dbname for pubs
3. exec sp_dboption 'pubs','auto update statistics','on' -- substitute
dbname for pubs
4. use QA to show query plan [Control-L]
5. try explicit sp_recompile
6. check dependencies
if all else fails you can mark your sproc "WITH RECOMPILE" to ignore cached
copy, thus keep abreast of actual cardinality
best wishes!
Dick
"Sergio R Pires" wrote:

> I have a very uncommon problem ... I have a application that runs like thi
s:
> 1) it receives users inserts, including a Status = 'U' field;
> 2) based on a Status field (index) the application query and select the la
st
> inserted registries all day long, each 30 seconds;
> 3) every time it read the registries it changes the Status field to 'R'.
> The problem is that after about 24 hours, the query identifies no longer
> registries with that index. It returns nothing. I use a execurereader
> command, and it happens that myreader.hasrows = false, even if there are r
ows
> with Status field = 'U'
> Can somebody help me to know what is happening?
> --
> Sergio R Pires