Running a program from a SQL job

Hi
I'm having a strange problem which puzzels me a little bit.
I have created a SQL job that can zip a file that has been generated from a
provious job. I've made a bat file that contains the string to run to zip
the file. When I run this bat file manually just by doubleclicking it, it
runs fine and do the job. When I set the SQL job to execute the same bat
file, it executes the bat file, but the bat file doesn't zip the file. It
reports that everything is ok, but the file isn't being created.
It seems like the zip program I've found that can be executed from a command
line (it's a program called 7-zip) will only do the job if it can "open" a
command prompt in the background which SQL apparently doesn't do.
My question is now if any of you know a way to let a SQL job "open" a
command prompt and then execute a program from there. It could also be as
simple as using a different zip program. 7-zip was just one I found that
could zip a file from a command line.
Regards
SteenHi,
Use XP_CMDSHELL to fire a OS command from command prompt.
Before commentng more, can you send out the script you are trying out.
Thanks
Hari
MCDBA
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:uKWljChUEHA.1764@.TK2MSFTNGP10.phx.gbl...
> Hi
> I'm having a strange problem which puzzels me a little bit.
> I have created a SQL job that can zip a file that has been generated from
a
> provious job. I've made a bat file that contains the string to run to zip
> the file. When I run this bat file manually just by doubleclicking it, it
> runs fine and do the job. When I set the SQL job to execute the same bat
> file, it executes the bat file, but the bat file doesn't zip the file. It
> reports that everything is ok, but the file isn't being created.
> It seems like the zip program I've found that can be executed from a
command
> line (it's a program called 7-zip) will only do the job if it can "open" a
> command prompt in the background which SQL apparently doesn't do.
> My question is now if any of you know a way to let a SQL job "open" a
> command prompt and then execute a program from there. It could also be as
> simple as using a different zip program. 7-zip was just one I found that
> could zip a file from a command line.
> Regards
> Steen
>|||Hi Hari
Thanks for the advise. I've just tried it, but it seems like it has the same
problem as when I run it un a job.
The commandline I'm running is : "c:\program files\7-zip\7z" a -tzip
filename.zip filename.mdc
This works fine when running it either as a bat file or just in a cmd
window, but from within SQL it seems to fail. The output I get from running
it either as a job or with the XP_CmdShell is :
NULL
7-Zip 2.30 Beta 28 Copyright (c) 1999-2003 Igor Pavlov 2003-02-16
Scanning
NULL
Updating archive filename.zip
NULL
NULL
Everything is Ok
NULL
It reports that everything is ok, but it just don't create the zip file. It
might be that this 7-zip program just can't run properly without having an
open cmd prompt - which of course is a bit weird.
Regards
Steen
"Hari" <hari_prasad_k@.hotmail.com> skrev i en meddelelse
news:eCrlkGhUEHA.484@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Use XP_CMDSHELL to fire a OS command from command prompt.
> Before commentng more, can you send out the script you are trying out.
> --
> Thanks
> Hari
> MCDBA
> "Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
> news:uKWljChUEHA.1764@.TK2MSFTNGP10.phx.gbl...
from[vbcol=seagreen]
> a
zip[vbcol=seagreen]
it[vbcol=seagreen]
It[vbcol=seagreen]
> command
a[vbcol=seagreen]
as[vbcol=seagreen]
>|||Hi,
Login to SQL server from command prompt:-
OSQL -Usa -Ppassword -S SQl_server
This will allow you to go to a SQL prompt:-
1>
Here you execute the xp_cmdshell
1>master..xp_cmdsehll 'c:\program files\7-zip\7z a -tzip'
2>go
Note:
Please use the syntax clearly
Thanks
Hari
MCDBA
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:uPWCBNhUEHA.3988@.tk2msftngp13.phx.gbl...
> Hi Hari
> Thanks for the advise. I've just tried it, but it seems like it has the
same
> problem as when I run it un a job.
> The commandline I'm running is : "c:\program files\7-zip\7z" a -tzip
> filename.zip filename.mdc
> This works fine when running it either as a bat file or just in a cmd
> window, but from within SQL it seems to fail. The output I get from
running
> it either as a job or with the XP_CmdShell is :
> NULL
> 7-Zip 2.30 Beta 28 Copyright (c) 1999-2003 Igor Pavlov 2003-02-16
> Scanning
> NULL
> Updating archive filename.zip
> NULL
> NULL
> Everything is Ok
> NULL
> It reports that everything is ok, but it just don't create the zip file.
It
> might be that this 7-zip program just can't run properly without having an
> open cmd prompt - which of course is a bit weird.
> Regards
> Steen
>
>
> "Hari" <hari_prasad_k@.hotmail.com> skrev i en meddelelse
> news:eCrlkGhUEHA.484@.TK2MSFTNGP10.phx.gbl...
> from
> zip
> it
bat[vbcol=seagreen]
> It
"open"[vbcol=seagreen]
> a
> as
that[vbcol=seagreen]
>|||Hi Hari
Thansk for your suggestions, but it simply won't do it. I get the same
result when running is with the Osql command. Apparently this zip program
only works when it's launched from it's "own" cmd prompt.
Steen
"Hari" <hari_prasad_k@.hotmail.com> skrev i en meddelelse
news:%23zI$HThUEHA.1020@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Login to SQL server from command prompt:-
> OSQL -Usa -Ppassword -S SQl_server
> This will allow you to go to a SQL prompt:-
> 1>
> Here you execute the xp_cmdshell
> 1>master..xp_cmdsehll 'c:\program files\7-zip\7z a -tzip'
> 2>go
> Note:
> Please use the syntax clearly
> --
> Thanks
> Hari
> MCDBA
> "Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
> news:uPWCBNhUEHA.3988@.tk2msftngp13.phx.gbl...
> same
> running
> It
an[vbcol=seagreen]
to[vbcol=seagreen]
it,[vbcol=seagreen]
> bat
file.[vbcol=seagreen]
> "open"
a[vbcol=seagreen]
be[vbcol=seagreen]
> that
>|||Did you qualify the path for both the input as well as output files?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message news:uPWCBNhUEHA.3988@.tk2msftngp13.phx
.gbl...
> Hi Hari
> Thanks for the advise. I've just tried it, but it seems like it has the sa
me
> problem as when I run it un a job.
> The commandline I'm running is : "c:\program files\7-zip\7z" a -tzip
> filename.zip filename.mdc
> This works fine when running it either as a bat file or just in a cmd
> window, but from within SQL it seems to fail. The output I get from runnin
g
> it either as a job or with the XP_CmdShell is :
> NULL
> 7-Zip 2.30 Beta 28 Copyright (c) 1999-2003 Igor Pavlov 2003-02-16
> Scanning
> NULL
> Updating archive filename.zip
> NULL
> NULL
> Everything is Ok
> NULL
> It reports that everything is ok, but it just don't create the zip file. I
t
> might be that this 7-zip program just can't run properly without having an
> open cmd prompt - which of course is a bit weird.
> Regards
> Steen
>
>
> "Hari" <hari_prasad_k@.hotmail.com> skrev i en meddelelse
> news:eCrlkGhUEHA.484@.TK2MSFTNGP10.phx.gbl...
> from
> zip
> it
> It
> a
> as
>|||Solved it.....
In my bat file I just changed to the directory where the files where
placed - that did the trick(..should have thought about that from the very
beginning...).
It might as well work if I specify the full path for both input and output
files as Tibor suggest, but I haven't tried it.
Thanks for all your suggestions.
Regards
Steen
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> skrev i
en meddelelse news:%23lkrJmhUEHA.2692@.TK2MSFTNGP09.phx.gbl...
> Did you qualify the path for both the input as well as output files?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:uPWCBNhUEHA.3988@.tk2msftngp13.phx.gbl...
same[vbcol=seagreen]
running[vbcol=seagreen]
It[vbcol=seagreen]
an[vbcol=seagreen]
to[vbcol=seagreen]
it,[vbcol=seagreen]
bat[vbcol=seagreen]
file.[vbcol=seagreen]
"open"[vbcol=seagreen]
a[vbcol=seagreen]
be[vbcol=seagreen]
that[vbcol=seagreen]
>|||Make sure the sqlagent account has enough permission to zip. Also you might
try winzip 8.0 command line addon. It's working for me.
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:uKWljChUEHA.1764@.TK2MSFTNGP10.phx.gbl...
> Hi
> I'm having a strange problem which puzzels me a little bit.
> I have created a SQL job that can zip a file that has been generated from
a
> provious job. I've made a bat file that contains the string to run to zip
> the file. When I run this bat file manually just by doubleclicking it, it
> runs fine and do the job. When I set the SQL job to execute the same bat
> file, it executes the bat file, but the bat file doesn't zip the file. It
> reports that everything is ok, but the file isn't being created.
> It seems like the zip program I've found that can be executed from a
command
> line (it's a program called 7-zip) will only do the job if it can "open" a
> command prompt in the background which SQL apparently doesn't do.
> My question is now if any of you know a way to let a SQL job "open" a
> command prompt and then execute a program from there. It could also be as
> simple as using a different zip program. 7-zip was just one I found that
> could zip a file from a command line.
> Regards
> Steen
>|||In article <uKWljChUEHA.1764@.TK2MSFTNGP10.phx.gbl>, "Steen Persson" <SPE@.REMOVEdatea.dk> wro
te:
>Hi
>I'm having a strange problem which puzzels me a little bit.
>I have created a SQL job that can zip a file that has been generated from a
>provious job. I've made a bat file that contains the string to run to zip
>the file. When I run this bat file manually just by doubleclicking it, it
>runs fine and do the job. When I set the SQL job to execute the same bat
>file, it executes the bat file, but the bat file doesn't zip the file. It
>reports that everything is ok, but the file isn't being created.
>It seems like the zip program I've found that can be executed from a comman
d
>line (it's a program called 7-zip) will only do the job if it can "open" a
>command prompt in the background which SQL apparently doesn't do.
>My question is now if any of you know a way to let a SQL job "open" a
>command prompt and then execute a program from there. It could also be as
>simple as using a different zip program. 7-zip was just one I found that
>could zip a file from a command line.
>Regards
>Steen
Use PKZip.
The original and best cmdline tool.

Run two stored procs in parallel

Hi
I have a simple stored proc which runs in less than a second.
This stored proc calculates different values based on parameters
passed.
For example sp_name "test 1 ' and
sp_name 'test 2 ' are totally different.
Now The issue is this stored proc is called more than 500 times in some
applications.
So these applications run for 500+ seconds . Although load on sql
server is almost negligible. Memory is also hugely available.
I need your suggestion in running these all at the same time.
In other words all 500 must be started at the same time ..there is no
need for one procedure to complete and the next to start. None of the
results are depenedent on any other results.
pls help me .
Kannan
Is this SQL 2000 or SQL 2005?
In 2005 is very easily accomplished using Service Broker. Instead of calling
the procedure 500 times, send 500 messages to a service that uses an
activated stored procedure. The message body would contain the arguments you
now pass in. Using the 'max_queue_readers' clause of CREATE QUEUE (see
http://msdn2.microsoft.com/en-us/library/ms190495(en-US,SQL.90).aspx) you
can control the maximum number of procedures to run in paralel.
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"Kannan" <Kannanmca@.gmail.com> wrote in message
news:1134587293.323886.256810@.z14g2000cwz.googlegr oups.com...
> Hi
> I have a simple stored proc which runs in less than a second.
> This stored proc calculates different values based on parameters
> passed.
> For example sp_name "test 1 ' and
> sp_name 'test 2 ' are totally different.
>
> Now The issue is this stored proc is called more than 500 times in some
> applications.
> So these applications run for 500+ seconds . Although load on sql
> server is almost negligible. Memory is also hugely available.
> I need your suggestion in running these all at the same time.
> In other words all 500 must be started at the same time ..there is no
> need for one procedure to complete and the next to start. None of the
> results are depenedent on any other results.
>
> pls help me .
> Kannan
>
|||> So these applications run for 500+ seconds . Although load on sql
> server is almost negligible. Memory is also hugely available.
Remus provided some SQL 2005 suggestions to improve throughput but I suggest
you do root cause performance analysis rather than throw parallel processing
at the problem.
Why do the procs run so long? Disk i/o? CPU? Network utilization? Do you
have SET NOCOUNT ON in your procs? If the load on SQL Server is low and you
have no resource problem, the procs should either run very fast or you
should observe high resource utilization.
Hope this helps.
Dan Guzman
SQL Server MVP
"Kannan" <Kannanmca@.gmail.com> wrote in message
news:1134587293.323886.256810@.z14g2000cwz.googlegr oups.com...
> Hi
> I have a simple stored proc which runs in less than a second.
> This stored proc calculates different values based on parameters
> passed.
> For example sp_name "test 1 ' and
> sp_name 'test 2 ' are totally different.
>
> Now The issue is this stored proc is called more than 500 times in some
> applications.
> So these applications run for 500+ seconds . Although load on sql
> server is almost negligible. Memory is also hugely available.
> I need your suggestion in running these all at the same time.
> In other words all 500 must be started at the same time ..there is no
> need for one procedure to complete and the next to start. None of the
> results are depenedent on any other results.
>
> pls help me .
> Kannan
>
|||Its SQL server 2000.
Its not a single proc that runs for 500 seconds its becuse the same
procedure is called 500 times with different parameters.
As I mentioned if you run the stored proc once it wont even run for a
second.
There are applications which take input from users and invoke my stored
procedure , The user may enter one value or 500 value.
If the user enters 500 values then the application calls my stored proc
500 times one after another.
That is what is causing a problem , So I created another proc which can
take any number of parameters and then invoke my orignal proc .
Like exec super_proc a,b,c will invoke
exec original_proc 'a'
exec original_proc 'b'
exec original_proc 'c'
Here again SQL server starts executing the next proc after it finishes
first.
I dont want sql server to execute serially but paralelly.
This explains why the load and memory are not utilized fully.
regards
Kannan
|||> Its not a single proc that runs for 500 seconds its becuse the same
> procedure is called 500 times with different parameters.
> As I mentioned if you run the stored proc once it wont even run for a
> second.
Yes, I gathered this from your original post. My concern is that one second
is a *very* long time. I have seen single-threaded applications repeatedly
execute an insert stored procedure several hundred times per second. If
each of your proc executions run for a second, I would expect that you would
see high resource utilization (CPU, disk or network) for the duration of the
application run. If you don't, then something is wrong. You can certainly
throw additional threads at the problem but I suspect the performance
improvement won't be as good as you would like. For example, if you are I/O
bound, parallel processing might actually hurt performance.

> If the user enters 500 values then the application calls my stored proc
> 500 times one after another.
> That is what is causing a problem , So I created another proc which can
> take any number of parameters and then invoke my orignal proc .
This might make a miniscule difference due to reduced latency but it's
obviously not the long pole in the tent. I don't think you are addressing
the root cause of your slowness. Can you provide more details on the
calculations the proc performs. Perhaps some code and DDL would help.
Hope this helps.
Dan Guzman
SQL Server MVP
"Kannan" <Kannanmca@.gmail.com> wrote in message
news:1134591179.063554.66800@.g14g2000cwa.googlegro ups.com...
> Its SQL server 2000.
> Its not a single proc that runs for 500 seconds its becuse the same
> procedure is called 500 times with different parameters.
> As I mentioned if you run the stored proc once it wont even run for a
> second.
> There are applications which take input from users and invoke my stored
> procedure , The user may enter one value or 500 value.
> If the user enters 500 values then the application calls my stored proc
> 500 times one after another.
> That is what is causing a problem , So I created another proc which can
> take any number of parameters and then invoke my orignal proc .
> Like exec super_proc a,b,c will invoke
> exec original_proc 'a'
> exec original_proc 'b'
> exec original_proc 'c'
> Here again SQL server starts executing the next proc after it finishes
> first.
> I dont want sql server to execute serially but paralelly.
> This explains why the load and memory are not utilized fully.
> regards
> Kannan
>
|||I used one second just to illustrate my point. It actually runs in
milliseconds as u have mentioned.
I am not sure if i can post the code / Let me check on this .
will opening multiple connections from end application help ? multiple
connections will be run without any wait time right.
In other words if I run the same proc from two places at the same time
then the second proc will not wait for first one to complete right ?
This is what I want to achive using one connection !.
Kannan
|||>I used one second just to illustrate my point. It actually runs in
> milliseconds as u have mentioned.
Is the 500 seconds total elapsed time accurate? Total elapsed time should
be roughly the sum the proc duration plus application processing time. So,
if you run a proc 500 times at an average of 10 ms. each, total duration
ought to be about 5 seconds.

> will opening multiple connections from end application help ? multiple
> connections will be run without any wait time right.
> In other words if I run the same proc from two places at the same time
> then the second proc will not wait for first one to complete right ?
> This is what I want to achive using one connection !.
There should be very little 'wait' time between proc executions on a single
connection. Perhaps a little latency but with only 500 executions, it
shouldn't add up to more than a few milliseconds total.
Multiple connections (multi-threaded processing) might help if you are not
resource constrained but you really need to know the cause of the problem is
before you develop a solution.

> I am not sure if i can post the code / Let me check on this .
The code might not be needed if the proc only runs in few milliseconds.
There's something else going on if the total elapsed time is high.
Hope this helps.
Dan Guzman
SQL Server MVP
"Kannan" <Kannanmca@.gmail.com> wrote in message
news:1134594222.338716.212980@.g43g2000cwa.googlegr oups.com...
>I used one second just to illustrate my point. It actually runs in
> milliseconds as u have mentioned.
> I am not sure if i can post the code / Let me check on this .
> will opening multiple connections from end application help ? multiple
> connections will be run without any wait time right.
> In other words if I run the same proc from two places at the same time
> then the second proc will not wait for first one to complete right ?
> This is what I want to achive using one connection !.
> Kannan
>

Run two stored procs in parallel

Hi
I have a simple stored proc which runs in less than a second.
This stored proc calculates different values based on parameters
passed.
For example sp_name "test 1 ' and
sp_name 'test 2 ' are totally different.
Now The issue is this stored proc is called more than 500 times in some
applications.
So these applications run for 500+ seconds . Although load on sql
server is almost negligible. Memory is also hugely available.
I need your suggestion in running these all at the same time.
In other words all 500 must be started at the same time ..there is no
need for one procedure to complete and the next to start. None of the
results are depenedent on any other results.
pls help me .
KannanIs this SQL 2000 or SQL 2005?
In 2005 is very easily accomplished using Service Broker. Instead of calling
the procedure 500 times, send 500 messages to a service that uses an
activated stored procedure. The message body would contain the arguments you
now pass in. Using the 'max_queue_readers' clause of CREATE QUEUE (see
http://msdn2.microsoft.com/en-us/library/ms190495(en-US,SQL.90).aspx) you
can control the maximum number of procedures to run in paralel.
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"Kannan" <Kannanmca@.gmail.com> wrote in message
news:1134587293.323886.256810@.z14g2000cwz.googlegroups.com...
> Hi
> I have a simple stored proc which runs in less than a second.
> This stored proc calculates different values based on parameters
> passed.
> For example sp_name "test 1 ' and
> sp_name 'test 2 ' are totally different.
>
> Now The issue is this stored proc is called more than 500 times in some
> applications.
> So these applications run for 500+ seconds . Although load on sql
> server is almost negligible. Memory is also hugely available.
> I need your suggestion in running these all at the same time.
> In other words all 500 must be started at the same time ..there is no
> need for one procedure to complete and the next to start. None of the
> results are depenedent on any other results.
>
> pls help me .
> Kannan
>|||> So these applications run for 500+ seconds . Although load on sql
> server is almost negligible. Memory is also hugely available.
Remus provided some SQL 2005 suggestions to improve throughput but I suggest
you do root cause performance analysis rather than throw parallel processing
at the problem.
Why do the procs run so long? Disk i/o? CPU? Network utilization? Do you
have SET NOCOUNT ON in your procs? If the load on SQL Server is low and you
have no resource problem, the procs should either run very fast or you
should observe high resource utilization.
Hope this helps.
Dan Guzman
SQL Server MVP
"Kannan" <Kannanmca@.gmail.com> wrote in message
news:1134587293.323886.256810@.z14g2000cwz.googlegroups.com...
> Hi
> I have a simple stored proc which runs in less than a second.
> This stored proc calculates different values based on parameters
> passed.
> For example sp_name "test 1 ' and
> sp_name 'test 2 ' are totally different.
>
> Now The issue is this stored proc is called more than 500 times in some
> applications.
> So these applications run for 500+ seconds . Although load on sql
> server is almost negligible. Memory is also hugely available.
> I need your suggestion in running these all at the same time.
> In other words all 500 must be started at the same time ..there is no
> need for one procedure to complete and the next to start. None of the
> results are depenedent on any other results.
>
> pls help me .
> Kannan
>|||Its SQL server 2000.
Its not a single proc that runs for 500 seconds its becuse the same
procedure is called 500 times with different parameters.
As I mentioned if you run the stored proc once it wont even run for a
second.
There are applications which take input from users and invoke my stored
procedure , The user may enter one value or 500 value.
If the user enters 500 values then the application calls my stored proc
500 times one after another.
That is what is causing a problem , So I created another proc which can
take any number of parameters and then invoke my orignal proc .
Like exec super_proc a,b,c will invoke
exec original_proc 'a'
exec original_proc 'b'
exec original_proc 'c'
Here again SQL server starts executing the next proc after it finishes
first.
I dont want sql server to execute serially but paralelly.
This explains why the load and memory are not utilized fully.
regards
Kannan|||> Its not a single proc that runs for 500 seconds its becuse the same
> procedure is called 500 times with different parameters.
> As I mentioned if you run the stored proc once it wont even run for a
> second.
Yes, I gathered this from your original post. My concern is that one second
is a *very* long time. I have seen single-threaded applications repeatedly
execute an insert stored procedure several hundred times per second. If
each of your proc executions run for a second, I would expect that you would
see high resource utilization (CPU, disk or network) for the duration of the
application run. If you don't, then something is wrong. You can certainly
throw additional threads at the problem but I suspect the performance
improvement won't be as good as you would like. For example, if you are I/O
bound, parallel processing might actually hurt performance.

> If the user enters 500 values then the application calls my stored proc
> 500 times one after another.
> That is what is causing a problem , So I created another proc which can
> take any number of parameters and then invoke my orignal proc .
This might make a miniscule difference due to reduced latency but it's
obviously not the long pole in the tent. I don't think you are addressing
the root cause of your slowness. Can you provide more details on the
calculations the proc performs. Perhaps some code and DDL would help.
Hope this helps.
Dan Guzman
SQL Server MVP
"Kannan" <Kannanmca@.gmail.com> wrote in message
news:1134591179.063554.66800@.g14g2000cwa.googlegroups.com...
> Its SQL server 2000.
> Its not a single proc that runs for 500 seconds its becuse the same
> procedure is called 500 times with different parameters.
> As I mentioned if you run the stored proc once it wont even run for a
> second.
> There are applications which take input from users and invoke my stored
> procedure , The user may enter one value or 500 value.
> If the user enters 500 values then the application calls my stored proc
> 500 times one after another.
> That is what is causing a problem , So I created another proc which can
> take any number of parameters and then invoke my orignal proc .
> Like exec super_proc a,b,c will invoke
> exec original_proc 'a'
> exec original_proc 'b'
> exec original_proc 'c'
> Here again SQL server starts executing the next proc after it finishes
> first.
> I dont want sql server to execute serially but paralelly.
> This explains why the load and memory are not utilized fully.
> regards
> Kannan
>|||I used one second just to illustrate my point. It actually runs in
milliseconds as u have mentioned.
I am not sure if i can post the code / Let me check on this .
will opening multiple connections from end application help ' multiple
connections will be run without any wait time right.
In other words if I run the same proc from two places at the same time
then the second proc will not wait for first one to complete right ?
This is what I want to achive using one connection !.
Kannan|||>I used one second just to illustrate my point. It actually runs in
> milliseconds as u have mentioned.
Is the 500 seconds total elapsed time accurate? Total elapsed time should
be roughly the sum the proc duration plus application processing time. So,
if you run a proc 500 times at an average of 10 ms. each, total duration
ought to be about 5 seconds.

> will opening multiple connections from end application help ' multiple
> connections will be run without any wait time right.
> In other words if I run the same proc from two places at the same time
> then the second proc will not wait for first one to complete right ?
> This is what I want to achive using one connection !.
There should be very little 'wait' time between proc executions on a single
connection. Perhaps a little latency but with only 500 executions, it
shouldn't add up to more than a few milliseconds total.
Multiple connections (multi-threaded processing) might help if you are not
resource constrained but you really need to know the cause of the problem is
before you develop a solution.

> I am not sure if i can post the code / Let me check on this .
The code might not be needed if the proc only runs in few milliseconds.
There's something else going on if the total elapsed time is high.
Hope this helps.
Dan Guzman
SQL Server MVP
"Kannan" <Kannanmca@.gmail.com> wrote in message
news:1134594222.338716.212980@.g43g2000cwa.googlegroups.com...
>I used one second just to illustrate my point. It actually runs in
> milliseconds as u have mentioned.
> I am not sure if i can post the code / Let me check on this .
> will opening multiple connections from end application help ' multiple
> connections will be run without any wait time right.
> In other words if I run the same proc from two places at the same time
> then the second proc will not wait for first one to complete right ?
> This is what I want to achive using one connection !.
> Kannan
>

Run Time Database Connection

Hi
I 've created dsn for sqlserver during run time but while connecting with crystalreport it gives ODBC Error . I need to connect using CRAXDRT.Report,CRAXDRT.Application ,I'm Using vb as front end
Thanks In AdvGenerall the errors are descriptive, so please provide details on which ODBC error it is.

Dave
(Me.HomeTown = DarrenLehmann.HomeTown)
Go Aussies.|||Hi Spring soft

I solved my problem thanx for ur post|||Hi Tony,
Perhaps you could let us know the solution, for enlightenment of others.
On reflection, I suspect the report had a subreport. That's the most common reason (apart from improperly structured formulae) that raises ODBC errors.

Dave|||Ofcourse dave

Private m_crxApp As New CRAXDRT.Application
Private m_crxRep As CRAXDRT.Report
Attribute m_crxRep.VB_VarHelpID = -1
Private m_crxTab As CRAXDRT.DatabaseTable
Implements clsSimpleReport
Public Function clsSimpleReport_RunReport(sReportFileName As String, sTitle As String) As Variant
Dim i As Integer, j As Integer
On Error Resume Next
Screen.MousePointer = 11
m_crxApp.LogOnServer "p2sodbc.dll", "DSN", "DBNAME, "UID", "PWD"
Set m_crxRep = m_crxApp.OpenReport(App.Path & "\Reports\" & sReportFileName)
crvReport.ReportSource = m_crxRep
crvReport.ViewReport
End sub

My mistake was instead of p2sodbc.dll in logon server function i put some other dll file i'm not remembering that nowsql

Run Sql Stored Procedure From Excel

Hi

I want to create a Stored Procedure in SQL with three variables that are declared in an Excel file.

Thus, when the user enters the three variables into

Cells A1, B1, C1

Then hits a button...

The Stored procedure in SQL 2000 will run - and then return the results to a refreshable pivot table in the Excel file.

Any ideas?

Quote:

Originally Posted by flickimp

Hi

I want to create a Stored Procedure in SQL with three variables that are declared in an Excel file.

Thus, when the user enters the three variables into

Cells A1, B1, C1

Then hits a button...

The Stored procedure in SQL 2000 will run - and then return the results to a refreshable pivot table in the Excel file.

Any ideas?

Write an Excel Macro for this.
Connect to SQL Server from Excel and call a procedure as required.|||New to macros.

Any tips?|||

Quote:

Originally Posted by flickimp

New to macros.

Any tips?

You can find lots of examples by little web searching.
Kindly POST what you tried so that we could help in case of any problem!!|||Hi

Heres the Stored Procedure

Say in Excel -- cell A1 = @.Type and cell B1 = @.LSS

Create Proc sp_Flickimp_test
@.TypeVarchar(3),
@.LSSVarchar(3)
as
SelectProvider_Code,
Commissioner_Code,
LSS_Flag,
Financial_Year,
Financial_Month,
Specialty_Code,
Description_Contract,
Activity_Type,
CaseWhen Activity_Type in ('Emergency','Non-elective') Then 'Non-Elective'
Else Activity_Type End as Activity_Type_Grouped,
HRG_Code,
Specialised_Services_Code,
Local_AdmitUnit_Code,
Total_Spells, Tariff_Initial_Amount, Tariff_ShortStay_Spells,
Tariff_ShortStay_Adj, Tariff_SpecServ_Adj, Tariff_Final_Amount,
Tariff_LS_Spells, Tariff_LS_Days, Tariff_LS_Rate, Tariff_LS_Payment,
Tariff_Total_Payment, Local_Total_Payment, Local_AdmitUnit_Adj

Fromtbl_BaseLine_UHL_Spells_Trend_0708 a

LeftOuter Join dbo.tbl_Refs_Specialty_UHL b
on a.Specialty_Code = b.Code

LeftOuter Join dbo.tbl_Refs_IP_SpecServ_0708 c
on a.Specialised_Services_Code = c.SpecServ

Where(Financial_year = '2006/07' and Financial_Month between '7' and '12')
or(Financial_year = '2007/08' and Financial_Month between '1' and '6')
andCommissioner_Code = @.Type
andLSS_Flag = @.LSS|||As a full member now, you should know that we expect your code to be posted in [CODE] tags (See How to Ask a Question).

This makes it easier for our Experts to read and understand it. Failing to do so creates extra work for the moderators, thus wasting resources, otherwise available to answer the members' questions.

Please use the tags in future.

MODERATOR|||

Quote:

Originally Posted by flickimp

Hi

Heres the Stored Procedure

Say in Excel -- cell A1 = @.Type and cell B1 = @.LSS

Create Proc sp_Flickimp_test
@.TypeVarchar(3),
@.LSSVarchar(3)
as
SelectProvider_Code,
Commissioner_Code,
LSS_Flag,
Financial_Year,
Financial_Month,
Specialty_Code,
Description_Contract,
Activity_Type,
CaseWhen Activity_Type in ('Emergency','Non-elective') Then 'Non-Elective'
Else Activity_Type End as Activity_Type_Grouped,
HRG_Code,
Specialised_Services_Code,
Local_AdmitUnit_Code,
Total_Spells, Tariff_Initial_Amount, Tariff_ShortStay_Spells,
Tariff_ShortStay_Adj, Tariff_SpecServ_Adj, Tariff_Final_Amount,
Tariff_LS_Spells, Tariff_LS_Days, Tariff_LS_Rate, Tariff_LS_Payment,
Tariff_Total_Payment, Local_Total_Payment, Local_AdmitUnit_Adj

Fromtbl_BaseLine_UHL_Spells_Trend_0708 a

LeftOuter Join dbo.tbl_Refs_Specialty_UHL b
on a.Specialty_Code = b.Code

LeftOuter Join dbo.tbl_Refs_IP_SpecServ_0708 c
on a.Specialised_Services_Code = c.SpecServ

Where(Financial_year = '2006/07' and Financial_Month between '7' and '12')
or(Financial_year = '2007/08' and Financial_Month between '1' and '6')
andCommissioner_Code = @.Type
andLSS_Flag = @.LSS

Alright!! Did you try calling this from a Macro.
You need to connect to SQL server from Excel.|||Hi

Sorry for not using the tags.

The Stored procedure was created in SQL Query Analyzer 2000.

I tried to use MS Query to pull the SP back with the variables, but no joy.

What steps must I take to allow the parameters from excel cells to be picked up by the stored procedure so that it returns the results back to excel?

run sql server 2000 as sql server 7

Hi
I want to downgrade a sql server from v2000 to v7 for compatibility
reasons. How would I go about doing this?
Many Thanks
Iain
In EM, right click on the database you want to change, choose properties,
then the options tab, at the bottom of the dialog, there is a compatability
option, set that to 7.0
"Iain A. Mcleod" <mcleodia@.dcs.gla.ac.uk> wrote in message
news:usdnofIqEHA.1308@.TK2MSFTNGP14.phx.gbl...
> Hi
> I want to downgrade a sql server from v2000 to v7 for compatibility
> reasons. How would I go about doing this?
> Many Thanks
> Iain
>
|||Ahh, I was looking under the options for a server not for a database!!!
Does that mean you can run one DB as v7 and one as v2000?
If so: kewl!
Thanks and have a nice weekend!
IM
"DWinter" <dwinter@.attbi.com> wrote in message
news:uXhBNcJqEHA.348@.TK2MSFTNGP15.phx.gbl...
> In EM, right click on the database you want to change, choose properties,
> then the options tab, at the bottom of the dialog, there is a
compatability
> option, set that to 7.0
> "Iain A. Mcleod" <mcleodia@.dcs.gla.ac.uk> wrote in message
> news:usdnofIqEHA.1308@.TK2MSFTNGP14.phx.gbl...
>
|||Yep
"Iain A. Mcleod" <mcleodia@.dcs.gla.ac.uk> wrote in message
news:u0rbkfJqEHA.3668@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> Ahh, I was looking under the options for a server not for a database!!!
> Does that mean you can run one DB as v7 and one as v2000?
> If so: kewl!
> Thanks and have a nice weekend!
> IM
> "DWinter" <dwinter@.attbi.com> wrote in message
> news:uXhBNcJqEHA.348@.TK2MSFTNGP15.phx.gbl...
properties,
> compatability
>

Run Policies /Surface Area Configuration - Bug

Hi!

I've created the "Off By Default Policy" as described in BOL (ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_4deptrbl/html/41c3022d-ab13-443e-ac64-ba1d64584f79.htm) for SQL Mail and Database Mail.

When I then activate SQL Mail and Database Mail through Surface Area Configuration (without closing it!) and then run the policy and configure it (so that SQL Mail and Database Mail) is then off, these changes are not reflected by Surface Area Configuration.

You must restart Surface Area Configuration, so that you can see the changes.

HTH

Klaus Aschenbrenner

http://www.csharp.at

http://www.csharp.at/blog

The SAC doesn't auto refresh but that seems quite normal to me.

Maybe a refresh button would be nice since restarting is not an intuitive way to refresh of course :-)

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||

The Surface Area Configuration tool will most likely be pulled in a later CTP; probably as soon as we can support all of the server types (DB Engine, Analysis Services, and Reporting Services) in Management Studio using policies. For now you'll need to close and re-open the Surface Area Configuration tool to see the changes.

Cheers,

Dan

Run a scheduled job from the command promt

Hi
I was wondering if it is possible to run a scheduele from the command
promt.
I have a job that runs every night that updates my sales in my
datawarehouse. That jobs executes several DTS-packages. But sometimes
some of my users wish to update the sales in the middle of the day.
So now I wondered if I could create a .bat file that executed the job.
I know that I can use the DTSRUN command to execute a package, but
thats not flexible enough for me
Regards and thanks in advance
FlemmingHi
Take a look at OSQL utility in the BOL.
"Flemming Nielsen" <flemming.delph@.gmail.com> wrote in message
news:fccae6c9.0506080010.5b48d526@.posting.google.com...
> Hi
> I was wondering if it is possible to run a scheduele from the command
> promt.
> I have a job that runs every night that updates my sales in my
> datawarehouse. That jobs executes several DTS-packages. But sometimes
> some of my users wish to update the sales in the middle of the day.
> So now I wondered if I could create a .bat file that executed the job.
> I know that I can use the DTSRUN command to execute a package, but
> thats not flexible enough for me
> Regards and thanks in advance
> Flemming

Ruling out OS - SQL Server Performance.

Hi:
I have a question hopefully the SQL Server Pros and MVPs would have an
answer for. Most of the time like any normal DBA to find performance issues
with SQL Server I make use of the performance counters through performance
monitor or system monitor and SQL Server Profiler and then rule out OS as
the bottleneck and then concentrate on the application part. My question
here is if there a simple way that without using any of these counters or
tools a DBA can rule out OS as the potential bottleneck. For example I would
assume that if my backups start taking a long time all of a sudden when they
usually take less time every day then I can probably point out OS as the
issue. I am not sure if thats the right way to tell.
I am looking for a comprehensive list of possible symptoms (like paging,
Virtual memory, Disk I/O) that can conclusively deduce that OS is/is not
the bottle neck without me making use of any of the above mentioned tools.
Any KB Article or a White paper or blogs with more information would be
really helpful. Any other information is greatly appreciated.
Thanks for your attention.
M
Meher
Well, my first question is what is your SQL Server's verion?
If you are using SQL Server 2005 you have lots of system catalogs,views that
contain such kind of info.Personally , I have not played with it
unfortunatly but as I heard it provides a comprehensive info about internal
behaviour of SQL Server as well as OS.
If you use SQL Server 2000 I have my doubt that without these tolls you can
roll out OS as the potential bottleneck at 'first glance'.
"Meher" <NOSPAM_mmsagar@.hotmail.com> wrote in message new, views
s:uIdeYK$JGHA.532@.TK2MSFTNGP15.phx.gbl...
> Hi:
> I have a question hopefully the SQL Server Pros and MVPs would have an
> answer for. Most of the time like any normal DBA to find performance
> issues with SQL Server I make use of the performance counters through
> performance monitor or system monitor and SQL Server Profiler and then
> rule out OS as the bottleneck and then concentrate on the application
> part. My question here is if there a simple way that without using any of
> these counters or tools a DBA can rule out OS as the potential bottleneck.
> For example I would assume that if my backups start taking a long time all
> of a sudden when they usually take less time every day then I can probably
> point out OS as the issue. I am not sure if thats the right way to tell.
> I am looking for a comprehensive list of possible symptoms (like paging,
> Virtual memory, Disk I/O) that can conclusively deduce that OS is/is not
> the bottle neck without me making use of any of the above mentioned tools.
> Any KB Article or a White paper or blogs with more information would be
> really helpful. Any other information is greatly appreciated.
> Thanks for your attention.
> M
>
|||Hi Uri:
My Version is SQL Server 2000. You are right that without using counters how
can a DBA Can say that OS Can be ruled out and that the Application is
possibly the bottleneck. That I assumed is my limited knowledge of
performance and if there are some kind of telltale signs that a DBA Can
point out without even using tools. If thats not possible then i guess the
tools are the only one i can rely on.
Thanks Uri for your response.
Meher
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uXhj3U$JGHA.3912@.TK2MSFTNGP10.phx.gbl...
> Meher
> Well, my first question is what is your SQL Server's verion?
> If you are using SQL Server 2005 you have lots of system catalogs,views
> that contain such kind of info.Personally , I have not played with it
> unfortunatly but as I heard it provides a comprehensive info about
> internal behaviour of SQL Server as well as OS.
>
> If you use SQL Server 2000 I have my doubt that without these tolls you
> can roll out OS as the potential bottleneck at 'first glance'.
>
>
>
> "Meher" <NOSPAM_mmsagar@.hotmail.com> wrote in message new, views
> s:uIdeYK$JGHA.532@.TK2MSFTNGP15.phx.gbl...
>
|||Meher
Yes , based on my experience 80% of the problems ( lack of indexes, bad
written queries) caused by an application and only 10% -20% caused by a
hardware. I used to capture some events by using SQL Server Profiler and it
is absolutely reliable tool that provides very valuable info as well as
Perf. Monitor
"Meher" <NOSPAM_mmsagar@.hotmail.com> wrote in message
news:ewjsHY$JGHA.2036@.TK2MSFTNGP14.phx.gbl...
> Hi Uri:
> My Version is SQL Server 2000. You are right that without using counters
> how can a DBA Can say that OS Can be ruled out and that the Application is
> possibly the bottleneck. That I assumed is my limited knowledge of
> performance and if there are some kind of telltale signs that a DBA Can
> point out without even using tools. If thats not possible then i guess the
> tools are the only one i can rely on.
> Thanks Uri for your response.
> Meher
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:uXhj3U$JGHA.3912@.TK2MSFTNGP10.phx.gbl...
>
|||I use performance counters to supplement my SQL performance monitoring, not
to eliminate it. Most of the time, performance problems are due excessive
resource consumption. The counters tell me which resource is being
consumed. I then look at SQL to see which processes are consuming those
resources, often by using SQL traces. Be aware that SQL will trade one
resource for another for speed. Primarily it trades higher memory and CPU
usage for less disk usage. Sometimes a problem in one area can appear to be
something else. For example, I had a system with very high disk queue
lengths on the data disk. That would seem to indicate an I/O issue.
However, it also had an extremely short page life expectency, indicating
lack of cache memory. I upped the RAM and the disk problem went away. The
I/O problem was caused by SQL having to constantly reload data that should
have stayed in cache. I still need to tune some processes that use large
intermediate result sets, but that isn't an emergency priority.
This example illustrates that it is not just when X counter exceeds Y value
that tells you there is a problem. You have to know what is normal for your
system and work from there. Counters only give you a partial picture.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Meher" <NOSPAM_mmsagar@.hotmail.com> wrote in message
news:uIdeYK$JGHA.532@.TK2MSFTNGP15.phx.gbl...
> Hi:
> I have a question hopefully the SQL Server Pros and MVPs would have an
> answer for. Most of the time like any normal DBA to find performance
> issues with SQL Server I make use of the performance counters through
> performance monitor or system monitor and SQL Server Profiler and then
> rule out OS as the bottleneck and then concentrate on the application
> part. My question here is if there a simple way that without using any of
> these counters or tools a DBA can rule out OS as the potential bottleneck.
> For example I would assume that if my backups start taking a long time all
> of a sudden when they usually take less time every day then I can probably
> point out OS as the issue. I am not sure if thats the right way to tell.
> I am looking for a comprehensive list of possible symptoms (like paging,
> Virtual memory, Disk I/O) that can conclusively deduce that OS is/is not
> the bottle neck without me making use of any of the above mentioned tools.
> Any KB Article or a White paper or blogs with more information would be
> really helpful. Any other information is greatly appreciated.
> Thanks for your attention.
> M
>
|||Thanks Geoff. Your post is really informative. The reason why I am asking
this question is i was asked iimagining i dont have any tools to troubleshoot
SQL Server Performance issues, before driving to the conclusion that
application related issues are the cause of performance, how do I eliminate
OS as the primary cause. I am trying to get a grasp on the approach of senior
SQL DBAs in such a scenario.
Thanks again.
"Geoff N. Hiten" wrote:

> I use performance counters to supplement my SQL performance monitoring, not
> to eliminate it. Most of the time, performance problems are due excessive
> resource consumption. The counters tell me which resource is being
> consumed. I then look at SQL to see which processes are consuming those
> resources, often by using SQL traces. Be aware that SQL will trade one
> resource for another for speed. Primarily it trades higher memory and CPU
> usage for less disk usage. Sometimes a problem in one area can appear to be
> something else. For example, I had a system with very high disk queue
> lengths on the data disk. That would seem to indicate an I/O issue.
> However, it also had an extremely short page life expectency, indicating
> lack of cache memory. I upped the RAM and the disk problem went away. The
> I/O problem was caused by SQL having to constantly reload data that should
> have stayed in cache. I still need to tune some processes that use large
> intermediate result sets, but that isn't an emergency priority.
> This example illustrates that it is not just when X counter exceeds Y value
> that tells you there is a problem. You have to know what is normal for your
> system and work from there. Counters only give you a partial picture.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "Meher" <NOSPAM_mmsagar@.hotmail.com> wrote in message
> news:uIdeYK$JGHA.532@.TK2MSFTNGP15.phx.gbl...
>
>
|||Meher
A bottle neck can be due to either of LAN/WAN, SAN/disk, CPU or memory.
You will need to use an approach to see if a similar task can be
completed at the OS level without using SQL specific connetivity.
An example would be if you have network performance issues you will
test a command prompt file copy (say 100MB) and capture packets with
Ethereal to see how that performs compared to data transfer using SQL
commands.
regards
Pradeep Arora
Meher wrote:[vbcol=seagreen]
> Thanks Geoff. Your post is really informative. The reason why I am asking
> this question is i was asked iimagining i dont have any tools to troubleshoot
> SQL Server Performance issues, before driving to the conclusion that
> application related issues are the cause of performance, how do I eliminate
> OS as the primary cause. I am trying to get a grasp on the approach of senior
> SQL DBAs in such a scenario.
> Thanks again.
> "Geoff N. Hiten" wrote:

Ruling out OS - SQL Server Performance.

Hi:
I have a question hopefully the SQL Server Pros and MVPs would have an
answer for. Most of the time like any normal DBA to find performance issues
with SQL Server I make use of the performance counters through performance
monitor or system monitor and SQL Server Profiler and then rule out OS as
the bottleneck and then concentrate on the application part. My question
here is if there a simple way that without using any of these counters or
tools a DBA can rule out OS as the potential bottleneck. For example I would
assume that if my backups start taking a long time all of a sudden when they
usually take less time every day then I can probably point out OS as the
issue. I am not sure if thats the right way to tell.
I am looking for a comprehensive list of possible symptoms (like paging,
Virtual memory, Disk I/O) that can conclusively deduce that OS is/is not
the bottle neck without me making use of any of the above mentioned tools.
Any KB Article or a White paper or blogs with more information would be
really helpful. Any other information is greatly appreciated.
Thanks for your attention.
MMeher
Well, my first question is what is your SQL Server's verion?
If you are using SQL Server 2005 you have lots of system catalogs,views that
contain such kind of info.Personally , I have not played with it
unfortunatly but as I heard it provides a comprehensive info about internal
behaviour of SQL Server as well as OS.
If you use SQL Server 2000 I have my doubt that without these tolls you can
roll out OS as the potential bottleneck at 'first glance'.
"Meher" <NOSPAM_mmsagar@.hotmail.com> wrote in message new, views
s:uIdeYK$JGHA.532@.TK2MSFTNGP15.phx.gbl...
> Hi:
> I have a question hopefully the SQL Server Pros and MVPs would have an
> answer for. Most of the time like any normal DBA to find performance
> issues with SQL Server I make use of the performance counters through
> performance monitor or system monitor and SQL Server Profiler and then
> rule out OS as the bottleneck and then concentrate on the application
> part. My question here is if there a simple way that without using any of
> these counters or tools a DBA can rule out OS as the potential bottleneck.
> For example I would assume that if my backups start taking a long time all
> of a sudden when they usually take less time every day then I can probably
> point out OS as the issue. I am not sure if thats the right way to tell.
> I am looking for a comprehensive list of possible symptoms (like paging,
> Virtual memory, Disk I/O) that can conclusively deduce that OS is/is not
> the bottle neck without me making use of any of the above mentioned tools.
> Any KB Article or a White paper or blogs with more information would be
> really helpful. Any other information is greatly appreciated.
> Thanks for your attention.
> M
>|||Hi Uri:
My Version is SQL Server 2000. You are right that without using counters how
can a DBA Can say that OS Can be ruled out and that the Application is
possibly the bottleneck. That I assumed is my limited knowledge of
performance and if there are some kind of telltale signs that a DBA Can
point out without even using tools. If thats not possible then i guess the
tools are the only one i can rely on.
Thanks Uri for your response.
Meher
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uXhj3U$JGHA.3912@.TK2MSFTNGP10.phx.gbl...
> Meher
> Well, my first question is what is your SQL Server's verion?
> If you are using SQL Server 2005 you have lots of system catalogs,views
> that contain such kind of info.Personally , I have not played with it
> unfortunatly but as I heard it provides a comprehensive info about
> internal behaviour of SQL Server as well as OS.
>
> If you use SQL Server 2000 I have my doubt that without these tolls you
> can roll out OS as the potential bottleneck at 'first glance'.
>
>
>
> "Meher" <NOSPAM_mmsagar@.hotmail.com> wrote in message new, views
> s:uIdeYK$JGHA.532@.TK2MSFTNGP15.phx.gbl...
>|||Meher
Yes , based on my experience 80% of the problems ( lack of indexes, bad
written queries) caused by an application and only 10% -20% caused by a
hardware. I used to capture some events by using SQL Server Profiler and it
is absolutely reliable tool that provides very valuable info as well as
Perf. Monitor
"Meher" <NOSPAM_mmsagar@.hotmail.com> wrote in message
news:ewjsHY$JGHA.2036@.TK2MSFTNGP14.phx.gbl...
> Hi Uri:
> My Version is SQL Server 2000. You are right that without using counters
> how can a DBA Can say that OS Can be ruled out and that the Application is
> possibly the bottleneck. That I assumed is my limited knowledge of
> performance and if there are some kind of telltale signs that a DBA Can
> point out without even using tools. If thats not possible then i guess the
> tools are the only one i can rely on.
> Thanks Uri for your response.
> Meher
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:uXhj3U$JGHA.3912@.TK2MSFTNGP10.phx.gbl...
>|||I use performance counters to supplement my SQL performance monitoring, not
to eliminate it. Most of the time, performance problems are due excessive
resource consumption. The counters tell me which resource is being
consumed. I then look at SQL to see which processes are consuming those
resources, often by using SQL traces. Be aware that SQL will trade one
resource for another for speed. Primarily it trades higher memory and CPU
usage for less disk usage. Sometimes a problem in one area can appear to be
something else. For example, I had a system with very high disk queue
lengths on the data disk. That would seem to indicate an I/O issue.
However, it also had an extremely short page life expectency, indicating
lack of cache memory. I upped the RAM and the disk problem went away. The
I/O problem was caused by SQL having to constantly reload data that should
have stayed in cache. I still need to tune some processes that use large
intermediate result sets, but that isn't an emergency priority.
This example illustrates that it is not just when X counter exceeds Y value
that tells you there is a problem. You have to know what is normal for your
system and work from there. Counters only give you a partial picture.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Meher" <NOSPAM_mmsagar@.hotmail.com> wrote in message
news:uIdeYK$JGHA.532@.TK2MSFTNGP15.phx.gbl...
> Hi:
> I have a question hopefully the SQL Server Pros and MVPs would have an
> answer for. Most of the time like any normal DBA to find performance
> issues with SQL Server I make use of the performance counters through
> performance monitor or system monitor and SQL Server Profiler and then
> rule out OS as the bottleneck and then concentrate on the application
> part. My question here is if there a simple way that without using any of
> these counters or tools a DBA can rule out OS as the potential bottleneck.
> For example I would assume that if my backups start taking a long time all
> of a sudden when they usually take less time every day then I can probably
> point out OS as the issue. I am not sure if thats the right way to tell.
> I am looking for a comprehensive list of possible symptoms (like paging,
> Virtual memory, Disk I/O) that can conclusively deduce that OS is/is not
> the bottle neck without me making use of any of the above mentioned tools.
> Any KB Article or a White paper or blogs with more information would be
> really helpful. Any other information is greatly appreciated.
> Thanks for your attention.
> M
>|||Thanks Geoff. Your post is really informative. The reason why I am asking
this question is i was asked iimagining i dont have any tools to troubleshoo
t
SQL Server Performance issues, before driving to the conclusion that
application related issues are the cause of performance, how do I eliminate
OS as the primary cause. I am trying to get a grasp on the approach of senio
r
SQL DBAs in such a scenario.
Thanks again.
"Geoff N. Hiten" wrote:

> I use performance counters to supplement my SQL performance monitoring, no
t
> to eliminate it. Most of the time, performance problems are due excessive
> resource consumption. The counters tell me which resource is being
> consumed. I then look at SQL to see which processes are consuming those
> resources, often by using SQL traces. Be aware that SQL will trade one
> resource for another for speed. Primarily it trades higher memory and CPU
> usage for less disk usage. Sometimes a problem in one area can appear to
be
> something else. For example, I had a system with very high disk queue
> lengths on the data disk. That would seem to indicate an I/O issue.
> However, it also had an extremely short page life expectency, indicating
> lack of cache memory. I upped the RAM and the disk problem went away. Th
e
> I/O problem was caused by SQL having to constantly reload data that should
> have stayed in cache. I still need to tune some processes that use large
> intermediate result sets, but that isn't an emergency priority.
> This example illustrates that it is not just when X counter exceeds Y valu
e
> that tells you there is a problem. You have to know what is normal for yo
ur
> system and work from there. Counters only give you a partial picture.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "Meher" <NOSPAM_mmsagar@.hotmail.com> wrote in message
> news:uIdeYK$JGHA.532@.TK2MSFTNGP15.phx.gbl...
>
>|||Meher
A bottle neck can be due to either of LAN/WAN, SAN/disk, CPU or memory.
You will need to use an approach to see if a similar task can be
completed at the OS level without using SQL specific connetivity.
An example would be if you have network performance issues you will
test a command prompt file copy (say 100MB) and capture packets with
Ethereal to see how that performs compared to data transfer using SQL
commands.
regards
Pradeep Arora
Meher wrote:[vbcol=seagreen]
> Thanks Geoff. Your post is really informative. The reason why I am asking
> this question is i was asked iimagining i dont have any tools to troublesh
oot
> SQL Server Performance issues, before driving to the conclusion that
> application related issues are the cause of performance, how do I eliminat
e
> OS as the primary cause. I am trying to get a grasp on the approach of sen
ior
> SQL DBAs in such a scenario.
> Thanks again.
> "Geoff N. Hiten" wrote:
>