I'm primarily a vb .net developer, doubling (as many of us have to) as a sql
2000 dba.
I'm having difficulty running a vb.net exe as a sql agent job. I figured
out that sql 2000 can't really run a windows forms exe, as it thinks it
needs user interaction. However, since it can run an activex script, can it
also run a windows service produced in visual studio 2003?
Thanks for any help.
Bernie Yaeger
As I posted within .programming
I thought that windows service ran interactively (without the need to call
them via the command line)? It should be possible to run any exe that does
not have a gui and that does not require user interaction. There are
permissions issues that you will need to consider if you are not calling the
exe as dbo.
Keith
"Bernie Yaeger" <berniey@.cherwellinc.com> wrote in message
news:fETEc.60920$OT6.24612300@.news4.srv.hcvlny.cv. net...
> I'm primarily a vb .net developer, doubling (as many of us have to) as a
sql
> 2000 dba.
> I'm having difficulty running a vb.net exe as a sql agent job. I figured
> out that sql 2000 can't really run a windows forms exe, as it thinks it
> needs user interaction. However, since it can run an activex script, can
it
> also run a windows service produced in visual studio 2003?
> Thanks for any help.
> Bernie Yaeger
>
|||I'm curious, could you use xp_cmdshell and see if you could run it from
Query analyser, before you test it on sqlagent?
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.
Showing posts with label dba. Show all posts
Showing posts with label dba. Show all posts
Friday, March 30, 2012
running a vb .net exe as a sql agent job
I'm primarily a vb .net developer, doubling (as many of us have to) as a sql
2000 dba.
I'm having difficulty running a vb.net exe as a sql agent job. I figured
out that sql 2000 can't really run a windows forms exe, as it thinks it
needs user interaction. However, since it can run an activex script, can it
also run a windows service produced in visual studio 2003?
Thanks for any help.
Bernie YaegerAs I posted within .programming
I thought that windows service ran interactively (without the need to call
them via the command line)? It should be possible to run any exe that does
not have a gui and that does not require user interaction. There are
permissions issues that you will need to consider if you are not calling the
exe as dbo.
Keith
"Bernie Yaeger" <berniey@.cherwellinc.com> wrote in message
news:fETEc.60920$OT6.24612300@.news4.srv.hcvlny.cv.net...
> I'm primarily a vb .net developer, doubling (as many of us have to) as a
sql
> 2000 dba.
> I'm having difficulty running a vb.net exe as a sql agent job. I figured
> out that sql 2000 can't really run a windows forms exe, as it thinks it
> needs user interaction. However, since it can run an activex script, can
it
> also run a windows service produced in visual studio 2003?
> Thanks for any help.
> Bernie Yaeger
>|||I'm curious, could you use xp_cmdshell and see if you could run it from
Query analyser, before you test it on sqlagent?
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.
2000 dba.
I'm having difficulty running a vb.net exe as a sql agent job. I figured
out that sql 2000 can't really run a windows forms exe, as it thinks it
needs user interaction. However, since it can run an activex script, can it
also run a windows service produced in visual studio 2003?
Thanks for any help.
Bernie YaegerAs I posted within .programming
I thought that windows service ran interactively (without the need to call
them via the command line)? It should be possible to run any exe that does
not have a gui and that does not require user interaction. There are
permissions issues that you will need to consider if you are not calling the
exe as dbo.
Keith
"Bernie Yaeger" <berniey@.cherwellinc.com> wrote in message
news:fETEc.60920$OT6.24612300@.news4.srv.hcvlny.cv.net...
> I'm primarily a vb .net developer, doubling (as many of us have to) as a
sql
> 2000 dba.
> I'm having difficulty running a vb.net exe as a sql agent job. I figured
> out that sql 2000 can't really run a windows forms exe, as it thinks it
> needs user interaction. However, since it can run an activex script, can
it
> also run a windows service produced in visual studio 2003?
> Thanks for any help.
> Bernie Yaeger
>|||I'm curious, could you use xp_cmdshell and see if you could run it from
Query analyser, before you test it on sqlagent?
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.
running a vb .net exe as a sql agent job
I'm primarily a vb .net developer, doubling (as many of us have to) as a sql
2000 dba.
I'm having difficulty running a vb.net exe as a sql agent job. I figured
out that sql 2000 can't really run a windows forms exe, as it thinks it
needs user interaction. However, since it can run an activex script, can it
also run a windows service produced in visual studio 2003?
Thanks for any help.
Bernie YaegerAs I posted within .programming
I thought that windows service ran interactively (without the need to call
them via the command line)? It should be possible to run any exe that does
not have a gui and that does not require user interaction. There are
permissions issues that you will need to consider if you are not calling the
exe as dbo.
Keith
"Bernie Yaeger" <berniey@.cherwellinc.com> wrote in message
news:fETEc.60920$OT6.24612300@.news4.srv.hcvlny.cv.net...
> I'm primarily a vb .net developer, doubling (as many of us have to) as a
sql
> 2000 dba.
> I'm having difficulty running a vb.net exe as a sql agent job. I figured
> out that sql 2000 can't really run a windows forms exe, as it thinks it
> needs user interaction. However, since it can run an activex script, can
it
> also run a windows service produced in visual studio 2003?
> Thanks for any help.
> Bernie Yaeger
>|||I'm curious, could you use xp_cmdshell and see if you could run it from
Query analyser, before you test it on sqlagent?
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.
2000 dba.
I'm having difficulty running a vb.net exe as a sql agent job. I figured
out that sql 2000 can't really run a windows forms exe, as it thinks it
needs user interaction. However, since it can run an activex script, can it
also run a windows service produced in visual studio 2003?
Thanks for any help.
Bernie YaegerAs I posted within .programming
I thought that windows service ran interactively (without the need to call
them via the command line)? It should be possible to run any exe that does
not have a gui and that does not require user interaction. There are
permissions issues that you will need to consider if you are not calling the
exe as dbo.
Keith
"Bernie Yaeger" <berniey@.cherwellinc.com> wrote in message
news:fETEc.60920$OT6.24612300@.news4.srv.hcvlny.cv.net...
> I'm primarily a vb .net developer, doubling (as many of us have to) as a
sql
> 2000 dba.
> I'm having difficulty running a vb.net exe as a sql agent job. I figured
> out that sql 2000 can't really run a windows forms exe, as it thinks it
> needs user interaction. However, since it can run an activex script, can
it
> also run a windows service produced in visual studio 2003?
> Thanks for any help.
> Bernie Yaeger
>|||I'm curious, could you use xp_cmdshell and see if you could run it from
Query analyser, before you test it on sqlagent?
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.
Monday, March 26, 2012
Running a job via osql?
Hello All,
A VERY green SQL Server DBA here looking for some help. Our main production environment is Oracle, which utilizes Control-M as a scheduler. At the end of the Oracle batch process, we would like to automate a process to kick off a sql server job (perhaps via osql??) Is this possible?
Thanks in advance,
TonyYes, just create a bat file with the osql commands in it and execute the bat file..I would imagine you would have to have osql on the oracle box...so if it's unix, then I'm not sure...|||Thanks Brad!|||ummmm...That's Brett
Or "x002548"
Or "HEY YOU"
Or "You look like a man who needs a drink"
Or "Coach" (As in hey coach..ummm...can I play forward? 13 voices at once)
Don't forget to redirect the output to a log so you can track what happened...that would be from a second bat file calling the one with the osql commands...
I like this approach, because it kind reminds me of my old JCL home
Weep no more, my lady,
Oh weep no more today!
We will sing one song for the old JCL home,
For the old JCL home far away.|||Brett,
My sincerest apologies!! That's what I get for multi-tasking & not paying attention when I type.
Our scheduling software has a windows client version that would allow us to excute the .bat file on the SQL Server box.
In order to call the job, do I just refer to the Job Name in the /Q [query] portion of the osql "statement"?
Server- PROD01
User- Tony
Password- Test
Job Name- Get1|||See the attachments
The txt file should be the bat, but it didn't let me upload a bat file|||ok, it only allows 1 upload per post..
This is a sample sql that osql executes
Well maybe it would allow more than 1, it didn't like the *.sql extension which is what it was|||Very Cool!! Once again, many thanks. I know that yours was executing a .sql script. How do I get it to recognize a pre-exisiting SQL Server Job?|||Place something like this in the *.sql file
EXECUTE msdb..sp_start_job 'Load_Ledger_Init_sp'|||The job and subsequently the script that calls the job need to have an error trapping mechanism. With current syntax of both batch file and SQL script Control-M will report SUCCESS even if OSQL part fails. At a minimum add -b to OSQL to force it to fail if error occurs.|||The osql failure will show uo in the log.
I usually load the log to a table to interogate what happened.
Whats -b?
yeah, yeah, BOL|||The osql failure will show uo in the log.
I usually load the log to a table to interogate what happened.
Whats -b?
yeah, yeah, BOL
EDIT:
-b
Specifies that osql exits and returns a DOS ERRORLEVEL value when an error occurs. The value returned to the DOS ERRORLEVEL variable is 1 when the SQL Server error message has a severity of 10 or greater; otherwise, the value returned is 0. Microsoft MS-DOS batch files can test the value of DOS ERRORLEVEL and handle the error appropriately.|||The error WILL show up in the log, but Control-M will not know that the error occurred, unless you use -b.|||Good point.
But I still would interogate the log anyway...but thanks for the info...
Anything with a severity error of less than 10 though still returns a zero...
EXINFO 1 Informational, nonerror.
EXUSER 2 User error.
EXNONFATAL 3 Nonfatal error.
EXCONVERSION 4 Error in DB-Library data conversion.
5 The server has returned an error flag.
EXTIME 6 Time-out period exceeded while waiting for
a response from the server; the DBPROCESS is still alive.
EXPROGRAM 7 Coding error in user program.
EXRESOURCE 8 Running out of resources; the DBPROCESS may be dead.
EXCOMM 9 Failure in communication with server; the DBPROCESS is dead.
EXFATAL 10 Fatal error; the DBPROCESS is dead.
EXCONSISTENCY 11 Internal software error; notify your primary support provider|||Sample fool-proof batch for Control-M:
OSQL...
if errorlevel 1 goto ErrorHandler
goto end
:ErrorHandler
echo OSQL ended with code %ERRORLEVEL%!
exit /B 1
:end|||Fine, but what about the severity errors below 10?
You're not suggesting that they completed. And since the return code will be 0, you still have to check.
No?|||Sample fool-proof SQL script (pseudo):
<sql_statement>
if @.@.error != 0 begin
raiserror ('The last statement failed (<WHAT STATEMENT? BE SPECIFIC!>)', 15, 1)
return
end
...|||are you sure it's not brad?
A VERY green SQL Server DBA here looking for some help. Our main production environment is Oracle, which utilizes Control-M as a scheduler. At the end of the Oracle batch process, we would like to automate a process to kick off a sql server job (perhaps via osql??) Is this possible?
Thanks in advance,
TonyYes, just create a bat file with the osql commands in it and execute the bat file..I would imagine you would have to have osql on the oracle box...so if it's unix, then I'm not sure...|||Thanks Brad!|||ummmm...That's Brett
Or "x002548"
Or "HEY YOU"
Or "You look like a man who needs a drink"
Or "Coach" (As in hey coach..ummm...can I play forward? 13 voices at once)
Don't forget to redirect the output to a log so you can track what happened...that would be from a second bat file calling the one with the osql commands...
I like this approach, because it kind reminds me of my old JCL home
Weep no more, my lady,
Oh weep no more today!
We will sing one song for the old JCL home,
For the old JCL home far away.|||Brett,
My sincerest apologies!! That's what I get for multi-tasking & not paying attention when I type.
Our scheduling software has a windows client version that would allow us to excute the .bat file on the SQL Server box.
In order to call the job, do I just refer to the Job Name in the /Q [query] portion of the osql "statement"?
Server- PROD01
User- Tony
Password- Test
Job Name- Get1|||See the attachments
The txt file should be the bat, but it didn't let me upload a bat file|||ok, it only allows 1 upload per post..
This is a sample sql that osql executes
Well maybe it would allow more than 1, it didn't like the *.sql extension which is what it was|||Very Cool!! Once again, many thanks. I know that yours was executing a .sql script. How do I get it to recognize a pre-exisiting SQL Server Job?|||Place something like this in the *.sql file
EXECUTE msdb..sp_start_job 'Load_Ledger_Init_sp'|||The job and subsequently the script that calls the job need to have an error trapping mechanism. With current syntax of both batch file and SQL script Control-M will report SUCCESS even if OSQL part fails. At a minimum add -b to OSQL to force it to fail if error occurs.|||The osql failure will show uo in the log.
I usually load the log to a table to interogate what happened.
Whats -b?
yeah, yeah, BOL|||The osql failure will show uo in the log.
I usually load the log to a table to interogate what happened.
Whats -b?
yeah, yeah, BOL
EDIT:
-b
Specifies that osql exits and returns a DOS ERRORLEVEL value when an error occurs. The value returned to the DOS ERRORLEVEL variable is 1 when the SQL Server error message has a severity of 10 or greater; otherwise, the value returned is 0. Microsoft MS-DOS batch files can test the value of DOS ERRORLEVEL and handle the error appropriately.|||The error WILL show up in the log, but Control-M will not know that the error occurred, unless you use -b.|||Good point.
But I still would interogate the log anyway...but thanks for the info...
Anything with a severity error of less than 10 though still returns a zero...
EXINFO 1 Informational, nonerror.
EXUSER 2 User error.
EXNONFATAL 3 Nonfatal error.
EXCONVERSION 4 Error in DB-Library data conversion.
5 The server has returned an error flag.
EXTIME 6 Time-out period exceeded while waiting for
a response from the server; the DBPROCESS is still alive.
EXPROGRAM 7 Coding error in user program.
EXRESOURCE 8 Running out of resources; the DBPROCESS may be dead.
EXCOMM 9 Failure in communication with server; the DBPROCESS is dead.
EXFATAL 10 Fatal error; the DBPROCESS is dead.
EXCONSISTENCY 11 Internal software error; notify your primary support provider|||Sample fool-proof batch for Control-M:
OSQL...
if errorlevel 1 goto ErrorHandler
goto end
:ErrorHandler
echo OSQL ended with code %ERRORLEVEL%!
exit /B 1
:end|||Fine, but what about the severity errors below 10?
You're not suggesting that they completed. And since the return code will be 0, you still have to check.
No?|||Sample fool-proof SQL script (pseudo):
<sql_statement>
if @.@.error != 0 begin
raiserror ('The last statement failed (<WHAT STATEMENT? BE SPECIFIC!>)', 15, 1)
return
end
...|||are you sure it's not brad?
Saturday, February 25, 2012
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:
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...
>> 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
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...
>> 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
>>
>|||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...
> > 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
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:
> 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...
> > > 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
> > >
> >
> >
> >
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...
>> 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
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...
>> 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
>>
>|||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...
> > 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
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:
> 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...
> > > 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
> > >
> >
> >
> >
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:
>
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:
>
Subscribe to:
Posts (Atom)