bad query executionplan

Hallo!
I'm having a problem with bad query executionplans. The optimizer won't
get the right plan to execute the query fast.
The optimizer perform cluster index scan instead of using a index s
an also using a hash join instead of a loop join.
Now, when I set a join hint to use the loop join, the optimizer uses
the right index to.
Another methode i have found is to set forceplan to on. Then the
optimzer will also uses loop join and the correct indexes.
Anyone have an idea?
Server:
Language German
SQL 2000 SP3a and SP4
Windows 2003 SP1 and without
I already have reindexed all participating indexes (drop & create) an
drop an recreate all statistics.
Regards
LutzPlease post your DDL (including constraints and indexes) and problem query
or stored procedure so that we can help.
Hope this helps.
Dan Guzman
SQL Server MVP
<lutz.jahnke@.nord-com.net> wrote in message
news:1125053676.823090.8480@.g49g2000cwa.googlegroups.com...
> Hallo!
> I'm having a problem with bad query executionplans. The optimizer won't
> get the right plan to execute the query fast.
> The optimizer perform cluster index scan instead of using a index s
> an also using a hash join instead of a loop join.
> Now, when I set a join hint to use the loop join, the optimizer uses
> the right index to.
> Another methode i have found is to set forceplan to on. Then the
> optimzer will also uses loop join and the correct indexes.
> Anyone have an idea?
> Server:
> Language German
> SQL 2000 SP3a and SP4
> Windows 2003 SP1 and without
> I already have reindexed all participating indexes (drop & create) an
> drop an recreate all statistics.
> Regards
> Lutz
>|||In addition to Dan's reply: please specify if you are using a stored
procedure with parameters (which can benefit from parameter sniffing) or
running an ad-hoc query or a stored procedure with variables. Do you get
a 'good' query plan if you use literals instead of variables/parameters?
Gert-Jan
lutz.jahnke@.nord-com.net wrote:
> Hallo!
> I'm having a problem with bad query executionplans. The optimizer won't
> get the right plan to execute the query fast.
> The optimizer perform cluster index scan instead of using a index s
> an also using a hash join instead of a loop join.
> Now, when I set a join hint to use the loop join, the optimizer uses
> the right index to.
> Another methode i have found is to set forceplan to on. Then the
> optimzer will also uses loop join and the correct indexes.
> Anyone have an idea?
> Server:
> Language German
> SQL 2000 SP3a and SP4
> Windows 2003 SP1 and without
> I already have reindexed all participating indexes (drop & create) an
> drop an recreate all statistics.
> Regards
> Lutz|||(lutz.jahnke@.nord-com.net) writes:
> I'm having a problem with bad query executionplans. The optimizer won't
> get the right plan to execute the query fast.
> The optimizer perform cluster index scan instead of using a index s
> an also using a hash join instead of a loop join.
> Now, when I set a join hint to use the loop join, the optimizer uses
> the right index to.
> Another methode i have found is to set forceplan to on. Then the
> optimzer will also uses loop join and the correct indexes.
> Anyone have an idea?
In additions to Dan's and Gert-Jan's suggestions, try running UPDATE
STATISTICS on the involved tables. Statistics may be out of date. SQL
Server updates statistics automatically, but there is a lag and if
your search condition includes a column with monotonically growing
values, the optimizer will make incorrect estimates.
For more heavy-duty, you can add WITH FULLSCAN to the UPDATE STATISTICS
statement.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Backup-only ID

I have a stored procedure that I use to execute database backups. It delete
s
old backup files and then creates the full backups of all the databases.
I want to create an ID that can only execute this procedure. I tried
creating a SQL ID with execute permission on this SP and then put the ID in
the db_backupoperator role in each database. But I keep running into
permission issues.
Any suggestions?What issues are you running into? Give specific examples and error messages
.
MeanOldDBA
derrickleggett@.hotmail.com
http://weblogs.sqlteam.com/derrickl
When life gives you a lemon, fire the DBA.
"Stephanie" wrote:

> I have a stored procedure that I use to execute database backups. It dele
tes
> old backup files and then creates the full backups of all the databases.
> I want to create an ID that can only execute this procedure. I tried
> creating a SQL ID with execute permission on this SP and then put the ID i
n
> the db_backupoperator role in each database. But I keep running into
> permission issues.
> Any suggestions?
>

backup to network drive

I'm trying to create a job to backup to a network drive. The following SQL does not execute successfully:
USE master
EXEC sp_addumpdevice 'disk', 'Thurs6', '\\servername\DATA\SQL\Backup\Thursday 6 PM.txt'
BACKUP LOG Mock8 TO Thurs6 WITH INIT
However, if I change the destination file to one located on the SQL server, it executes fine. I've also tried this using a mapped drive letter, and that doesn't work either. Any thoughts?
Backups are done under the account that sql server runs under and not the
account you are logged on as. So if SQL Server is not running under a domain
account with access to that share it can not see it.
Andrew J. Kelly SQL MVP
"mike" <mike@.discussions.microsoft.com> wrote in message
news:85586673-2350-4BC0-A64D-4B2664703C92@.microsoft.com...
> I'm trying to create a job to backup to a network drive. The following SQL
does not execute successfully:
> USE master
> EXEC sp_addumpdevice 'disk', 'Thurs6',
'\\servername\DATA\SQL\Backup\Thursday 6 PM.txt'
> BACKUP LOG Mock8 TO Thurs6 WITH INIT
> However, if I change the destination file to one located on the SQL
server, it executes fine. I've also tried this using a mapped drive letter,
and that doesn't work either. Any thoughts?
|||It could be that the account used by SQL Server doesn't have permissions to
that share. Can you post the exact error messages?
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"mike" <mike@.discussions.microsoft.com> wrote in message
news:85586673-2350-4BC0-A64D-4B2664703C92@.microsoft.com...
> I'm trying to create a job to backup to a network drive. The following SQL
does not execute successfully:
> USE master
> EXEC sp_addumpdevice 'disk', 'Thurs6',
'\\servername\DATA\SQL\Backup\Thursday 6 PM.txt'
> BACKUP LOG Mock8 TO Thurs6 WITH INIT
> However, if I change the destination file to one located on the SQL
server, it executes fine. I've also tried this using a mapped drive letter,
and that doesn't work either. Any thoughts?
|||Hi,
To solve the issue you have start the SQL Server service using an Domain OS
user which got write access to the remote share.
So go to Control Panel -- Admin Tools -- Services -- MSSQL Server sercice--
Double click and select the "Log on" option.
There you give a Valid Domain OS user and password to start the service. Now
stop and start the MSSQL Serevr service.
Note: That domain user should have previlages in the remote share to write
the file as well as prev. to start the SQL server.
After this you try to execute the Backup database command in Query
Analyzer:-
USE master
go
EXEC sp_addumpdevice 'disk', 'Thurs6',
'\\servername\DATA\SQL\Backup\Thursday 6 PM.txt'
go
BACKUP LOG Mock8 TO Thurs6 WITH INIT
Thanks
Hari
MCDBA
"mike" <mike@.discussions.microsoft.com> wrote in message
news:85586673-2350-4BC0-A64D-4B2664703C92@.microsoft.com...
> I'm trying to create a job to backup to a network drive. The following SQL
does not execute successfully:
> USE master
> EXEC sp_addumpdevice 'disk', 'Thurs6',
'\\servername\DATA\SQL\Backup\Thursday 6 PM.txt'
> BACKUP LOG Mock8 TO Thurs6 WITH INIT
> However, if I change the destination file to one located on the SQL
server, it executes fine. I've also tried this using a mapped drive letter,
and that doesn't work either. Any thoughts?