speed up query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

speed up query

Author

Topic

maryxu
Starting Member

36 Posts

Posted - 2008-03-14 : 17:53:39

try to run this query faster,
select d.name, max(b.backup_finish_date) from master..sysdatabases d, msdb..backupset b where b.type = 'D' and d.name *= b.database_name
group by d.name
thank you

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-03-14 : 17:57:43

How long does it take for you? Are you pruning msdb..backupset?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2008-03-15 : 07:10:10

Looking at the execution plan - the clustered index "backupsetuuid" is the highest cost .

Jack Vamvas
--------------------
Search IT jobs from multiple sources- http://www.ITjobfeed.com

maryxu
Starting Member

36 Posts

Posted - 2008-03-17 : 11:59:02

it is on 2000, and database numbers varied on different servers, some
are over 90. i notice that i run the query with
another server with about 90 databases, the query return very fast, it is
very strange to me, would it be server related? i guess could also related to
how big is the backupset tables, the one return fast has 474811 rows, though
has 90 databases, the other one with 35 databases has 1865568 rows (took 50 secs). So what
else would you think would help to improve? thank you

maryxu
Starting Member

36 Posts

Posted - 2008-03-17 : 12:13:43

So what would be the good practice to prune the table(is it used to to reduce the number of the rows in the table)? like how often? use script? thanks

How long does it take for you? Are you pruning msdb..backupset?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/
[/quote]

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2008-03-17 : 12:32:15

I don't know about speed, but it may help if you write it in T-SQL rather than ANSI standard.


select 
	d.name, 
	max(b.backup_finish_date)
from master..sysdatabases d left join  msdb..backupset b
on d.name = b.database_name 
where b.type = 'D' 
group by d.name

maryxu
Starting Member

36 Posts

Posted - 2008-03-17 : 12:46:11

i did change the syntax, but not much help, i think the main problems is the backupset table size, i have given the statistics on previous reply, please check, do i need to prune the backupset table reguarly? Is there any problem with that ? thanks

I don't know about speed, but it may help if you write it in T-SQL rather than ANSI standard.


select 
	d.name, 
	max(b.backup_finish_date)
from master..sysdatabases d left join  msdb..backupset b
on d.name = b.database_name 
where b.type = 'D' 
group by d.name

[/quote]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources