SQL Server Performance

Running SQL Profiler

Discussion in 'General DBA Questions' started by vaddi, Feb 8, 2007.

  1. vaddi New Member

    Hello,

    I am running the SQL Profiler on the sql server 2000. It doesnot show the values of DBusername , DBname and NTuser name. What should I do for this.

    And while running , the prefomance monitor , I have set the counters like disk i/o , cpu time , but its not starting . What might be the reason or do I need still further to do something.

    Thanks in advance for the help.
  2. madhuottapalam New Member

    In the Trace Property WIndow, Data Column Tab u can add the Columns u want.

    Madhu
  3. Luis Martin Moderator

    Run:

    select * from sysperfinfo

    to be sure you have counters.



    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All in Love is Fair
    Stevie Wonder


    All postings are provided “AS IS” with no warranties for accuracy.



  4. vaddi New Member

    when I executed this command

    select * from sysperfinfo

    it gave me Invalid object name 'sysperfinfo'

    This is the first time I am using the perfomance monitor.so not really sure what to setup.


    Thanks

  5. Luis Martin Moderator

    You have to run in master database.


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All in Love is Fair
    Stevie Wonder


    All postings are provided “AS IS” with no warranties for accuracy.



  6. madhuottapalam New Member

    quote:Originally posted by vaddi

    when I executed this command

    select * from sysperfinfo

    it gave me Invalid object name 'sysperfinfo'

    This is the first time I am using the perfomance monitor.so not really sure what to setup.


    Thanks



    run this query in Master database
    or
    select * from master..sysperfinfo
  7. vaddi New Member

    Thanks Martin,

    I have executed this on the master database. And I have the following counter for a particular instance "Test" ,

    Data File(s)Size , Log file size , Log fiel Used size , Percent log , Active Transcations , log cache , Log cache hit ratio , backup/restore , dbcc logical scan, log truncations , log growths , log shrink.

    So do I need to add more counters or the counters I want to track and how should I add the counter.


    Thanks




  8. Luis Martin Moderator

  9. vaddi New Member

    Thank you martin , I will look into that link and see what I can do.

    Hello madhu

    "In the Trace Property WIndow, Data Column Tab u can add the Columns u want."

    Can you explain this me a little.


    Thanks


  10. Luis Martin Moderator

  11. vaddi New Member

    Thank you very much Luis
    Thanks
  12. vaddi New Member

    when I tried to use the LODCTR utility
    I used the commands:

    unlodctr.exe MSSQLServer.

    Then to load them again, using the command:

    lodctr.exe BINNsqlstr.ini.


    When I issued the second command it has thrown me an error:

    unable to find initialization file BINNsqlstr.ini.

    I am using windows 2003 with SP1.

    Do I need to apply the latest SP

    Thanks
  13. MohammedU New Member

    Looks like Vaddi problem is not sql counter missing issue...

    Are you getting any rows backup when you run "select * from master..sysperfinfo" query?
    If your answer is yes then your sql counters are not missing.

    You need to run LoadCtr.exe only when the sql counters are missing in perfmon...

    Another way you can check for sql perfmon counters missing..
    open the perfmon and connect to the server...click Add counter button(+) at the top...click Perfomance object drop down window... in the drop down window if you see "SQL Server:..." counters then your sql counters are not missing...

    Answer to your initial question...
    In sql 2000 when you run the profiler...some columns will not get the data even after selecting the columns...it depends on what events you selected...





    MohammedU.
    Moderator
    SQL-Server-Performance.com
  14. Haywood New Member

    quote:Originally posted by MohammedU


    Answer to your initial question...
    In sql 2000 when you run the profiler...some columns will not get the data even after selecting the columns...it depends on what events you selected...

    See Gert's trace matrix for event & column mappings...


    http://sqldev.net/misc/SQLTraceEventMatrix.htm
  15. vaddi New Member

    Hello MohammedU

    when I use the select * from master..sysperfinfo , some data or coutners are retrived.

    Also in the perfomance monitor , in the drop down window of the pefomance object : I see the name " Server ".

    So what might be the reason that the counters that I want to monitor or not running.

    Thanks for the help
  16. MohammedU New Member

    You can't run the perfmon at all or sql counter can't be added to perfmon?

    In your first post you said you can't see "values of DBusername , DBname and NTuser name"...

    So now the problem is perfmon OR profiler?


    MohammedU.
    Moderator
    SQL-Server-Performance.com
  17. Luis Martin Moderator

    "when I use the select * from master..sysperfinfo , some data or coutners are retrived."

    Only some?



    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All in Love is Fair
    Stevie Wonder


    All postings are provided “AS IS” with no warranties for accuracy.



  18. vaddi New Member

    Hello MohammedU

    The problem is with Perfmon. In the first post the values that I mention were for the profiler , but that has been cleared.

    Now in the perfmon , I am able to add the objects , counters ,schedule it but it does not start.

    It gives out the error :

    " The test log or alert has not started.Refresh the log or alert list to view current status , or see the application event log for any errors.some logs and alerts might require a few minutes to start , especially if they include many counters or they contain counters to be collected from a remote system that is no longer on the network."

    I am doing the perfmon on the local server only and the counters I have added were , pages/sec for the memory , cpu average wait time and disk i/o.

    where can I see the application log.

    Thanks
  19. vaddi New Member

    Hello MohammedU

    The problem is with Perfmon. In the first post the values that I mention were for the profiler , but that has been cleared.

    Now in the perfmon , I am able to add the objects , counters ,schedule it but it does not start.

    It gives out the error :

    " The test log or alert has not started.Refresh the log or alert list to view current status , or see the application event log for any errors.some logs and alerts might require a few minutes to start , especially if they include many counters or they contain counters to be collected from a remote system that is no longer on the network."

    I am doing the perfmon on the local server only and the counters I have added were , pages/sec for the memory , cpu average wait time and disk i/o.

    where can I see the application log.

    Thanks
  20. Luis Martin Moderator

    " The test log or alert has not started.Refresh the log or alert list to view current status , or see the application event log for any errors.some logs and alerts might require a few minutes to start , especially if they include many counters or they contain counters to be collected from a remote system that is no longer on the network."

    That sound like you want to trace Performance Log and Alerts. No system monitor. (both in performance monitor).

    I suppose the server is too busy to response.

    If you call Performance Monitor in server, by default (adding nothing), you must have the following counters:

    1) Pages/sec (memory).
    2) Avg. Disk Queue length... _Total.
    3) %Processor Time ...... _Total

    If you can't see that, we can go from there.





    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All in Love is Fair
    Stevie Wonder


    All postings are provided “AS IS” with no warranties for accuracy.



  21. vaddi New Member

    Hello Luis

    I have rebooted the server(which is a test one and is a windows 2003) and now the System Monitor is working with these counters:

    1) Pages/sec (memory).
    2) Avg. Disk Queue length... _Total.
    3) %Processor Time ...... _Total

    Can I store these values in table or something so that I can read .

    But the counter logs and the trace logs are not starting.

    Might be a basic question , but I didnot get what the counter and trace logs do.

    I have got another test server which is windows 2000 and on that , the counter logs and trace logs are working good.

    So I am wondering what might have been wrong the windows2003 server.

    Thanks
  22. Luis Martin Moderator

    Nothing is wrong with 2003, the only problem is the account. So, go to Services (Administrative tools), services, Performance log and Alerts and check the account. This one must have the same privileges than Administrator.



    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All in Love is Fair
    Stevie Wonder


    All postings are provided “AS IS” with no warranties for accuracy.



  23. vaddi New Member

    Thank you very much luismartin.<br /><br />Now I will figure out what I can do from here with the monitoring and counter logs as I have never done it [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />vaddi

Share This Page