Showing posts with label dear. Show all posts
Showing posts with label dear. Show all posts

Friday, March 23, 2012

Optimize sql statements / find usefull indices

Dear all,

I try to find an easy method (like 'explain' in MySQL) to optimize my SQL statements and create usefull indices. I have created a trace table with the PROFILER and filtered the SQL statements by long DURATION time.

In the next step I used:

set SHOWPLAN_ALL ON;
my_sql_statment

to find the correct indices. Is there any tutorial available describes how to analyse such an output to find correct indices?

Best regards
febel

If you have SQL Profiler trace you could use Database Engine Tuning Advisor. Its wizard, that analyze trace and propose indexes, statictics etc

As tutorial you could use this book http://www.microsoft.com/MSPress/books/8565.aspx

|||Dear Konstantin,

thanks for your answer, but I have only the trace table and no possibility to use Database Engine Tuning Advisor. In addition I want use this in a programm which should set the indices in a automatic way depending on the results of the analysis of the trace table.

Best regards
febel
sql

Saturday, February 25, 2012

Operating system error = 32(error not found).

Dear all,
i try to backup abc.bak(21G) TO remote Server every night. But the system
gave me the following msg:
BackupDiskFile::CreateMedia: Backup
device '\\bkupsvr\dump$\abc.bak' failed to
create. Operating system error = 32(error not found).
SVR OS & SQL VERSION: win2003 STD SVR R2 with SQL SVR 2005 STDHi
Your message indicates that the file is being used by another process. At a
guess this might be either be a problem with disc caching or Anti-virus/ITD
type of system.
John
"Jet Lai" wrote:
> Dear all,
> i try to backup abc.bak(21G) TO remote Server every night. But the system
> gave me the following msg:
> BackupDiskFile::CreateMedia: Backup
> device '\\bkupsvr\dump$\abc.bak' failed to
> create. Operating system error = 32(error not found).
> SVR OS & SQL VERSION: win2003 STD SVR R2 with SQL SVR 2005 STD
>|||thank john for your answer.
Actually i can backup small bak file(said around 1G), but bak file with 21G
will be failed!
I've excluded this bak file from Anti-Virus System, but how do i clear "
disc caching "? , please tell me!
thanks again!
"John Bell" wrote:
> Hi
> Your message indicates that the file is being used by another process. At a
> guess this might be either be a problem with disc caching or Anti-virus/ITD
> type of system.
> John
> "Jet Lai" wrote:
> > Dear all,
> > i try to backup abc.bak(21G) TO remote Server every night. But the system
> > gave me the following msg:
> > BackupDiskFile::CreateMedia: Backup
> > device '\\bkupsvr\dump$\abc.bak' failed to
> > create. Operating system error = 32(error not found).
> >
> > SVR OS & SQL VERSION: win2003 STD SVR R2 with SQL SVR 2005 STD
> >
> >|||Hi
You would need to know how the disc cache has been set up on the remote
server and (possibly) allow write through caching using the software supplied
by the manufacturer. Have you checked that the problem still occurs after
turning off the AV?
You may also want to check things like BIOS versions for the disc cache to
see if it is up-to-date.
Another thought, is that there seems quite a few posts relating to remote
backup issues which are due to a faulty network card/network. Make sure that
you don't have problems with the any cards and that you have sufficient
bandwidth between the two machines to cope with the load.
John
"Jet Lai" wrote:
> thank john for your answer.
> Actually i can backup small bak file(said around 1G), but bak file with 21G
> will be failed!
> I've excluded this bak file from Anti-Virus System, but how do i clear "
> disc caching "? , please tell me!
> thanks again!
> "John Bell" wrote:
> > Hi
> >
> > Your message indicates that the file is being used by another process. At a
> > guess this might be either be a problem with disc caching or Anti-virus/ITD
> > type of system.
> >
> > John
> >
> > "Jet Lai" wrote:
> >
> > > Dear all,
> > > i try to backup abc.bak(21G) TO remote Server every night. But the system
> > > gave me the following msg:
> > > BackupDiskFile::CreateMedia: Backup
> > > device '\\bkupsvr\dump$\abc.bak' failed to
> > > create. Operating system error = 32(error not found).
> > >
> > > SVR OS & SQL VERSION: win2003 STD SVR R2 with SQL SVR 2005 STD
> > >
> > >|||32
The process cannot access the file because it is being used by another
process.
ERROR_SHARING_VIOLATION
"Jet Lai" <JetLai@.discussions.microsoft.com> wrote in message
news:8F4CD19E-72C2-4A89-87BB-B6A3C40D45EB@.microsoft.com...
> Dear all,
> i try to backup abc.bak(21G) TO remote Server every night. But the system
> gave me the following msg:
> BackupDiskFile::CreateMedia: Backup
> device '\\bkupsvr\dump$\abc.bak' failed to
> create. Operating system error = 32(error not found).
> SVR OS & SQL VERSION: win2003 STD SVR R2 with SQL SVR 2005 STD
>

Operating system error = 32(error not found).

Dear all,
i try to backup abc.bak(21G) TO remote Server every night. But the system
gave me the following msg:
BackupDiskFile::CreateMedia: Backup
device '\\bkupsvr\dump$\abc.bak' failed to
create. Operating system error = 32(error not found).
SVR OS & SQL VERSION: win2003 STD SVR R2 with SQL SVR 2005 STDHi
Your message indicates that the file is being used by another process. At a
guess this might be either be a problem with disc caching or Anti-virus/ITD
type of system.
John
"Jet Lai" wrote:

> Dear all,
> i try to backup abc.bak(21G) TO remote Server every night. But the system
> gave me the following msg:
> BackupDiskFile::CreateMedia: Backup
> device '\\bkupsvr\dump$\abc.bak' failed to
> create. Operating system error = 32(error not found).
> SVR OS & SQL VERSION: win2003 STD SVR R2 with SQL SVR 2005 STD
>|||thank john for your answer.
Actually i can backup small bak file(said around 1G), but bak file with 21G
will be failed!
I've excluded this bak file from Anti-Virus System, but how do i clear "
disc caching "? , please tell me!
thanks again!
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> Your message indicates that the file is being used by another process. At
a
> guess this might be either be a problem with disc caching or Anti-virus/IT
D
> type of system.
> John
> "Jet Lai" wrote:
>|||Hi
You would need to know how the disc cache has been set up on the remote
server and (possibly) allow write through caching using the software supplie
d
by the manufacturer. Have you checked that the problem still occurs after
turning off the AV?
You may also want to check things like BIOS versions for the disc cache to
see if it is up-to-date.
Another thought, is that there seems quite a few posts relating to remote
backup issues which are due to a faulty network card/network. Make sure that
you don't have problems with the any cards and that you have sufficient
bandwidth between the two machines to cope with the load.
John
"Jet Lai" wrote:
[vbcol=seagreen]
> thank john for your answer.
> Actually i can backup small bak file(said around 1G), but bak file with 2
1G
> will be failed!
> I've excluded this bak file from Anti-Virus System, but how do i clear "
> disc caching "? , please tell me!
> thanks again!
> "John Bell" wrote:
>|||32
The process cannot access the file because it is being used by another
process.
ERROR_SHARING_VIOLATION
"Jet Lai" <JetLai@.discussions.microsoft.com> wrote in message
news:8F4CD19E-72C2-4A89-87BB-B6A3C40D45EB@.microsoft.com...
> Dear all,
> i try to backup abc.bak(21G) TO remote Server every night. But the system
> gave me the following msg:
> BackupDiskFile::CreateMedia: Backup
> device '\\bkupsvr\dump$\abc.bak' failed to
> create. Operating system error = 32(error not found).
> SVR OS & SQL VERSION: win2003 STD SVR R2 with SQL SVR 2005 STD
>

Operating system and SQL server application on same disk set?

Dear all,
Can anyone please tell me what the advantages are when placing the operating
system and SQL server application on different disk sets? Or are the
differences minimal when doing this?
Thanks in advance,
--
Best Regards,
DennisI don't think there are any advantages in doing this. If the box is only
running SQL Server there should not be much I/O going taking place on the OS
volume.
There are advantages in separating tempdb, the data files, and the log files
onto different volumes.
Keith Kratochvil
"Dennis" <Dennis@.discussions.microsoft.com> wrote in message
news:BE15F7F7-A507-4C86-9352-B7B6FE7BE956@.microsoft.com...
> Dear all,
> Can anyone please tell me what the advantages are when placing the
> operating
> system and SQL server application on different disk sets? Or are the
> differences minimal when doing this?
> Thanks in advance,
> --
> Best Regards,
> Dennis|||Dennis wrote:
> Dear all,
> Can anyone please tell me what the advantages are when placing the operati
ng
> system and SQL server application on different disk sets? Or are the
> differences minimal when doing this?
> Thanks in advance,
There really is no benefit to placing the executables on different
volumes. Database files and transaction log files are a different
matter entirely.
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Operating system and SQL server application on same disk set?

Dear all,
Can anyone please tell me what the advantages are when placing the operating
system and SQL server application on different disk sets? Or are the
differences minimal when doing this?
Thanks in advance,
--
Best Regards,
DennisI don't think there are any advantages in doing this. If the box is only
running SQL Server there should not be much I/O going taking place on the OS
volume.
There are advantages in separating tempdb, the data files, and the log files
onto different volumes.
--
Keith Kratochvil
"Dennis" <Dennis@.discussions.microsoft.com> wrote in message
news:BE15F7F7-A507-4C86-9352-B7B6FE7BE956@.microsoft.com...
> Dear all,
> Can anyone please tell me what the advantages are when placing the
> operating
> system and SQL server application on different disk sets? Or are the
> differences minimal when doing this?
> Thanks in advance,
> --
> Best Regards,
> Dennis|||Dennis wrote:
> Dear all,
> Can anyone please tell me what the advantages are when placing the operating
> system and SQL server application on different disk sets? Or are the
> differences minimal when doing this?
> Thanks in advance,
There really is no benefit to placing the executables on different
volumes. Database files and transaction log files are a different
matter entirely.
Tracy McKibben
MCDBA
http://www.realsqlguy.com