SQL Server Performance

SQL Server Performance Botleneck

Discussion in 'Performance Tuning for DBAs' started by simflex, Feb 8, 2007.

  1. simflex New Member

    Can someone, please explain to me how to troubleshoot out sql server2000 db?

    We upgraded our server memory to 2g because we thought we had server issues.

    Then performed maintenance checks on the server.

    It turned out that our sql server is the problem.

    We have two apps running on sql server. Both are so slow you could sleep and wake up but the apps are still trying to load.

    I went to the db, it took forever to open the db.

    When I try clicking on Meta Data Services, the system hangs.

    Any help would be greatly appreciated.
  2. Luis Martin Moderator

    What OS, SQL versions and services pack do you have installed?.

    The apps are running from WS or server?

    What hardware do you have?


    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.



  3. simflex New Member

    Thanks for the prompt response.

    Windows Server2003;
    service pack1

    An ASP web app running on this server,
    Dell
  4. Luis Martin Moderator

    I suggest to run Perfomance Monitor to find if the bottleneck is because Memory, CPU or Disk.

    Also run Profiler to find what process are running and what is the duration, reads, etc.



    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.



  5. simflex New Member

    does this mean anything?

    Avg. disk queue length = 100.000 physicalDisk?

    Everything else says 1.000

    If this is an issue, what do I need to do?

    Also, under Linked Servers, 3 remote servers were linked to the db.
    There is another that says: Remote Servers. This has the same name as the name of the server that the db is running on.

    Thanks again for assisting.
  6. Luis Martin Moderator

    That means a lot!!.

    Normal values are <= 2 for avg. disk queue length.

    So I suppose your appl is not using evacuated indexes.

    Find the query responsible for that queue length and try to optimize using DTA (sql 2005 tool).

    Or take a look of execution plan to have a picture what indexes (if any) is used.



    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.



  7. simflex New Member

    Find the query responsible for that queue length and try to optimize using DTA (sql 2005 tool).

    What is the best way to find this?

    Sorry, I continue to expose my ignorance.

    Thanks again for all the help
  8. Luis Martin Moderator

    Run profiler.

    Check this article:

    http://www.sql-server-performance.com/sql_server_performance_audit10.asp

    Don't worry to ask any time you need.

    Read the article and came back if you need more assistance.



    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.



  9. simflex New Member

    Ok, Thanks very much, I will be back
  10. simflex New Member

    hi Luis,

    The entire sql db is so slow that it even takes about 1 minute to open sql analyzer.

    So, I ran an execution plan on one of the tables that I think is causing the most trouble.

    Here is what I got and I don't know if it makes a lot of sense to you.

    Query cost relative to batch: 100.00%

    does that mean anything?

    I did a select * from...
  11. simflex New Member

    Sorry for too many posts.

    I am running the profiler now. I suppose the 2 areas of focus for me are the CPU usde and Duration.

    I am getting some crazy numbers. I just need a little help with interpreting them.

    Many thanks.
  12. Luis Martin Moderator

    Ok.

    After you run the profiler, find the query with highest duration.

    Copy that query to Query Analyzer (or Management Studio) and run ITW(for QA) or DTA for SM.

    You will have if any indexes are neccesary.


    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.



  13. simflex New Member

    what is ITW and DTA?
  14. joechang New Member

    also, after copying the problem query Luis mentioned above into Query Analyzer (SQL Management Studio if SQL 2005)<br /><br />assuming you are actually on SQL 2005<br />go to the top Query -&gt; Display Estimated Execution Plan<br /><br />which will generate a graphical plan<br />click on the gui plan<br />save that plan (File -&gt; Save Execution Plan as)<br /><br />the file extension is .sqlplan<br />open the file with notepad, and paste in here <br />example below<br /><br />&lt;?xml version="1.0" encoding="utf-16"?&gt;<br />&lt;ShowPlanXML xmlns<img src='/community/emoticons/emotion-12.gif' alt=':x' />si="http://www.w3.org/2001/XMLSchema-instance" xmlns<img src='/community/emoticons/emotion-12.gif' alt=':x' />sd="http://www.w3.org/2001/XMLSchema" Version="1.0" Build="9.00.2153.00" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan"&gt;<br /> &lt;BatchSequence&gt;<br /> &lt;Batch&gt;<br /> &lt;Statements&gt;<br /> &lt;StmtSimple StatementCompId="1" StatementEstRows="1" StatementId="1" StatementOptmLevel="TRIVIAL" StatementSubTreeCost="0.0032832" StatementText="SELECT TOP 1 L_ORDERKEY FROM LineItem#xD;#xA;" StatementType="SELECT"&gt;<br /> &lt;StatementSetOptions ANSI_NULLS="false" ANSI_PADDING="false" ANSI_WARNINGS="false" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="false" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="false" /&gt;<br /> &lt;QueryPlan CachedPlanSize="9"&gt;<br /> &lt;RelOp AvgRowSize="15" EstimateCPU="1E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Top" NodeId="0" Parallel="false" PhysicalOp="Top" EstimatedTotalSubtreeCost="0.0032832"&gt;<br /> &lt;OutputList&gt;<br /> &lt;ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_ORDERKEY" /&gt;<br /> &lt;/OutputList&gt;<br /> &lt;Top RowCount="false" IsPercent="false" WithTies="false"&gt;<br /> &lt;TopExpression&gt;<br /> &lt;ScalarOperator ScalarString="(1)"&gt;<br /> &lt;Const ConstValue="(1)" /&gt;<br /> &lt;/ScalarOperator&gt;<br /> &lt;/TopExpression&gt;<br /> &lt;RelOp AvgRowSize="15" EstimateCPU="65.9848" EstimateIO="155.911" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Index Scan" NodeId="1" Parallel="false" PhysicalOp="Index Scan" EstimatedTotalSubtreeCost="0.0032831"&gt;<br /> &lt;OutputList&gt;<br /> &lt;ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_ORDERKEY" /&gt;<br /> &lt;/OutputList&gt;<br /> &lt;IndexScan Ordered="false" ForcedIndex="false" NoExpandHint="false"&gt;<br /> &lt;DefinedValues&gt;<br /> &lt;DefinedValue&gt;<br /> &lt;ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_ORDERKEY" /&gt;<br /> &lt;/DefinedValue&gt;<br /> &lt;/DefinedValues&gt;<br /> &lt;Object Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Index="[L_ORDERKEY_IDX]" /&gt;<br /> &lt;/IndexScan&gt;<br /> &lt;/RelOp&gt;<br /> &lt;/Top&gt;<br /> &lt;/RelOp&gt;<br /> &lt;/QueryPlan&gt;<br /> &lt;/StmtSimple&gt;<br /> &lt;/Statements&gt;<br /> &lt;/Batch&gt;<br /> &lt;/BatchSequence&gt;<br />&lt;/ShowPlanXML&gt;<br /><br /><br />
  15. simflex New Member

    ok,

    I use sql2000.

    Will I still follow your instruction?

    Also, one of the things I found is that msdb database is full.

    Allocated size is 14MB

    Available is .56MB

    When I ran that profiler, most of the errors are from alert engines, audit logout and job manager.

    So, when I went to Management console, I found that tempdb and msdb logs are full.

    I need to free some spaces here because access to anything else, has been excruciatingly slow and painful.

    What do you guys think?
  16. Luis Martin Moderator

    Now we have something.

    1) You have sql 2000.
    2) You have no space for msdb and temp databases.


    So, 2) is a big problem.

    Please post all hardware including disk free space in any disk you have.

    We start 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.



  17. simflex New Member

    Well, the message said msdb and tempdb are full.

    However, when I view the spaces on both, I found that:

    Yes indeed, msdb is full because allocated space is 14MB
    free space is .53 MB. So this one is as full as it gets.

    tempdb has 9MB of space allocation but 7.6MB (86%) of free space.

    That looks like it is not full unless I am going crazy (hardly a surprise anyway).

    On the hardware side, I am much better there at diagnosing it than I am at db.

    So, hard drive space is 16.4GB - ok
    Memory is 2.0GB - ok
    I increased swap files to 4096 - now ok.

    So, hard drive is fully optimized but the problem is the sql db.

    We support law enforcement officials. Can you imagine the yelling so far since this issue started days ago?

    Thanks for all the assistance. I lose more than 10 strands of hair per minute since this ordeal.
  18. Luis Martin Moderator

    All the people want to kill the massager.

    Back to business.

    Tell me how is configurations of MSBD and TEMPDB databases.

    Enterprise Manager, Databases, MSDB, right click, properties, Data Files Tab, Automatically grow files? (check the same in log tab) yes, no, what?

    Same thing for TEMPDB.





    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.



  19. simflex New Member

    Please bear with me Luis, it is sooooo sloooow that it is hanging.

    It just came up now.

    Ok, msdb has following info:

    Allocated space = 13MB
    Available = .29MB
    Automatically grow files,
    Increase in megabytes = 1
    Unrestricted file growth

    Then tempdb
    Allocated space 9MB
    Available space = 7.71
    Automatically grow files
    Increase By = 10%
    Unrestricted file growth

    Also, this might be of importance to you.

    I registered this database to be viewed on my the sql server installed in my local hard drive.

    I don't know if I am using the right term but the way I set it up now, what you see remote server (where this problem is coming from) is what you see on my local hard drive.

    Is this a good idea? If not, how do I disconnect this problematic db from my local pc?

  20. Luis Martin Moderator

    May be I miss something.

    Are you (with your PC) in the network or via web with remote server?


    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. joechang New Member

    i don't think we are making progress in a systematic fashion

    so far we know:
    1. response time is slow
    2. disk queue is very high

    this points to an expensive query plan that probably has something like a scan to a table bigger than memory

    for this, you need a profiler trace,
    other things will not lead any where
  22. simflex New Member

    Joe is probably right.

    I am so stressed now that I am throwing out anything that I *think* will help me resolve this.

    Just for clarity ( and I will leave that alone and wait for further instructions from you experts), I have a sql server running on my local pc.

    If you right-click on SQL Server Group, the first option there is "New SQL Server Registration".

    I used this option to register this SQL Server db that is giving us problem. I registered it so I can work on from the SQL Server db on my local pc. I was just wondering if that could have anything to do with the issue we are having now.
    Sorry if that has confused this, and perhaps delayed the help I am asking for from you guys.

    Ok, I have gone back to look at perfMon and and as it was the case yesterday, I found the following:

    Scale=100.000. I suppose this is physicalDisk.

    When I go to profiler, what is the best option to select to get you what info that will relevant to helping me resolve this?

    I want to thank you again for the patience and assistance.
  23. joechang New Member

    the disk queue is displayed with a scale factor of 100,
    ie, if its at the top of the chart, thats 1, not 100

    i think you need to find some one familar with Perfmon and Profiler to provide the actual important information
  24. joechang New Member

    Stop
    rather than blindly trying to use these tools hoping you will accidentally solve the problem
    use help and online resources to learn how to use Perfmon (part of the operating system) and SQL Profiler (part of SQL Server)

    i have some pictures in my site, but it is not meant to be a first time learning tool, just get it from production, i think it is a waste of time trying to figure out how to run on your local pc

    follow my directions below in collecting a perfmon and profiler trace
    http://www.sql-server-performance.com/qdpma/
    http://www.sql-server-performance.com/qdpma/instructions.asp

  25. simflex New Member

    Very useful info you have on your website, indeed.

    I am just told my time is up. So, I have to get out but I have collected info with MSInfo32 and SQLDiag. I will collect the rest as soon as I get to my other office.

    Please hang with me a little longer.

    By the way, the info I provided about physicalDisk is not bogus. I have seen a bunch that are displayed at 1.000 but this particular one stands out. It is really displayed at 100.000 and in the end when I send you this report, you will agree with me that is the source of this problem.

    Please don't abandon me yet till I send this info later today.

    I want to once again extend my sincere gratitude to you all for your assistance so far.
  26. joechang New Member

    my question is:
    are you looking at the Scale at the bottom of Permon
    or the Last, Average, Min, Max for Avg Disk Que

    i cannot believe the last 4 are exactly 100.000
    maybe 99.999 or 100.001, but eactly 100.000?
  27. simflex New Member

    Joe, You are right. You know your space.

    There are 2 areas that exist evidently but I was looking at the bottom one.

    The area you are referring to says:

    Last_0.000; Avg. = 8.212; Min= 0.000;Max=784.003(the avg and max numbers keep fluctuating); Duration=1.40

    The figures I gave originally are correct. They Read:

    Scale Counter Object (these are the ones with info. THere are more)
    1.000 page/sec memory
    100.000 Avg. disk Queue length physicalDisk
    1.000 %processore time Processor

    These are the items that have data. The rest are blank, Oh there is color differentiator.

    Sorry, I am still trying to collect rest of data to send.
  28. simflex New Member

    Sorry, additional question.

    In using the performance log to add counters, yes you are right there are several object to choose from.

    Which ones do you recommend, ones that are relevant to what I am trying to fix?
  29. Luis Martin Moderator

    I insist to run profiler to find the long queries and optimize those queries.

    The counters are very high (memory and disk), to me is lack of indexes or poor design.


    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.



  30. joechang New Member

    the scale is for graphing only, do not report them, they have no useful information to us, only how you see the graph
    the counter i list are the ones that are useful in diagnosing a very large number of common problems

    i am assuming that at some point in time, your application acceptably or reasonably?
    and then it was bad?
    if that is the case, run profiler, saving the trace to a file (DO NOT save profiler to a table on your production server)
    set filters for CPU > 10 if you can

  31. simflex New Member

    I appreciate once again you guys patience and assistance.

    Please continue to forgive my ignorant questions.

    When I am in profiler, what should I select?

    For instance, in the template name, there are so many options to select from.

    What should I select that would hit the nail on the head?
  32. simflex New Member

    Ok, I am running one now. I will post the results as soon as they are done.

    Then, perhaps you can tell me if I ran the wrong stuff.
  33. Luis Martin Moderator

    Events:<br /><br />Store Procedures:<br /><br />RPC:Completed<br />SP<img src='/community/emoticons/emotion-7.gif' alt=':S' />tmtCompleted<br /><br />T-SQL:<br /><br />SQL:Bach Completed<br />SQL<img src='/community/emoticons/emotion-7.gif' alt=':S' />tmtCompleted<br /><br />Columns:<br /><br />AppicationName, TextData, Duration, LoginName, SPID, DatabaseID, CPU, Reads, Writes<br /><br />Filters:<br /><br />CPU &gt; 10<br /><br />Luis Martin<br />Moderator<br />SQL-Server-Performance.com<br /><br /><font size="1">All in Love is Fair <br />Stevie Wonder<br /></font id="size1"><br /><br /><font size="1">All postings are provided “AS IS” with no warranties for accuracy.</font id="size1"><br /><br /><br /><br />
  34. simflex New Member

    I have just stopped the one I was running. I did however, save it just in case.

    But how do I open it since it was saved with extension .trc?
  35. Luis Martin Moderator

    Profiler-->Files--->Open Trace File---> Done.

    That if you save the Trace file.
    If you did not then:

    Profiler---Files----->Save Trace in File--->AnyName-->Done

    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.



  36. simflex New Member

    I don't know how to send this profiler report. It seems to overwhelm the thread but it hangs when I attempt to post it here.

    Let me see if I can chop off some portion. I hope not to leave out anything important.
  37. Luis Martin Moderator

    1) Did you use the events, columns and filters I suggest?


    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.



  38. simflex New Member

    Yes,

    That's why I cancelled the one I was running so I could use exactly what you suggested.

    Do you want me to email it to you?

    Here is my email address: simflex at hotmail dot com

    I will then email it to you.

    Joe, if you want, I can sent it to you as well as a txt file. It is just way too big to be copied to this forum.
  39. Luis Martin Moderator

    Since we don't have your database, that will not help us.

    What you have to do is:

    Look the trace file. You have two windows, one up, one down. Find the query with high duration. Once you find it, you can see in the 2nd window (down) what is the query.

    Copy that query to Query Analyzer.
    Insert before the query:

    SET STATISTICS IO ON
    SET SHOWPLAN_TEXT ON

    Insert after the query:

    SET STATISTICS IO OFF
    SET SHOWPLAN_TEXT OFF


    After running you can post the results.



    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.



  40. simflex New Member

    Ok, I have shut down for a break before my head explodes.

    I will log back in later tonight, run the profiler again, do as you instructed and post the results.

    Many thanks for all the assistance
  41. simflex New Member

    The following tsql are 2 of the few tsql with high duration.

    When I attempted to run them like you instructed, I get the following errors:

    Server: Msg 1067, Level 15, State 1, Line 2
    The SET SHOWPLAN statements must be the only statements in the batch.
    Server: Msg 1067, Level 15, State 1, Line 5
    The SET SHOWPLAN statements must be the only statements in the batch.

    Even after commenting out SET STATISTICSIO ON AND OFF, I still get the errors.

    frustration continues. Anyway, below are 2 sets of querries. They took awhile to run by the way.
    SET STATISTICS IO ON
    SET SHOWPLAN_TEXT ON

    -- sp_sqlagent_get_perf_counters
    SELECT 'object_name' = RTRIM(SUBSTRING(spi1.object_name, 1, 50)),
    'counter_name' = RTRIM(SUBSTRING(spi1.counter_name, 1, 50)),
    'instance_name' = CASE spi1.instance_name
    WHEN N'' THEN NULL
    ELSE RTRIM(spi1.instance_name)
    END,
    'value' = CASE spi1.cntr_type
    WHEN 537003008 -- A ratio
    THEN CONVERT(FLOAT, spi1.cntr_value) / (SELECT CASE spi2.cntr_value WHEN 0 THEN 1 ELSE spi2.cntr_value END
    FROM master.dbo.sysperfinfo spi2
    WHERE (spi1.counter_name + ' ' = SUBSTRING(spi2.counter_name, 1, PATINDEX('% Base%', spi2.counter_name)))
    AND (spi1.instance_name = spi2.instance_name)
    AND (spi2.cntr_type = 1073939459))
    ELSE spi1.cntr_value
    END
    FROM master.dbo.sysperfinfo spi1,
    #temp tmp
    WHERE (spi1.cntr_type <> 1073939459) -- Divisors
    AND ((@all_counters = 1) OR
    (tmp.performance_condition = RTRIM(spi1.object_name) + '|' + RTRIM(spi1.counter_name)))

    SET STATISTICS IO OFF
    SET SHOWPLAN_TEXT OFF

    SET STATISTICS IO ON
    SET SHOWPLAN_TEXT ON
    select name, DATABASEPROPERTY(name, N'IsDetached'), (case when DATABASEPROPERTY(name, N'IsShutdown') is null then -1 else DATABASEPROPERTY(name, N'IsShutdown') end), DATABASEPROPERTY(name, N'IsSuspect'), DATABASEPROPERTY(name, N'IsOffline'), DATABASEPROPERTY(name, N'IsInLoad'), (case when DATABASEPROPERTY(name, N'IsInRecovery') is null then -1 else DATABASEPROPERTY(name, N'IsInRecovery') end), (case when DATABASEPROPERTY(name, N'IsNotRecovered') is null then -1 else DATABASEPROPERTY(name, N'IsNotRecovered') end), DATABASEPROPERTY(name, N'IsEmergencyMode'), DATABASEPROPERTY(name, N'IsInStandBy'), has_dbaccess(name), status, category, status2 from master.dbo.sysdatabases
    SET STATISTICS IO OFF
    SET SHOWPLAN_TEXT OFF
  42. Luis Martin Moderator

    Ok.

    2 Problems.

    1) Only one SET by run. Begin with STATISTICS. After run with SHOWPLAN. Post both results.

    2) This Query is not a appl query. All databases in the query are system databases, no one is user databases.
    So, try to find long query and check AplicationName column to match with the application name.


    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.



  43. joechang New Member

    you never answered my question regarding whether your app did run acceptably up to some point in time, then changed
    it is an important question
    for instance, if some one installed a third party monitoring product
    that could actually be the source of your problems

    on Luis: i prefer: SET SHOWPLAN_ALL ON
    over: SET SHOWPLAN_TEXT ON
    without the row count and cost info, it is hard to tell if a scan is actually bad
    try:

    SET STATISTICS IO ON
    GO
    SET SHOWPLAN_ALL
    GO
    Your SQL query
    GO
    SET SHOWPLAN_ALL
    GO
    SET STATISTICS IO OFF
    GO

    try running profiler with just the following:
    if you did not set the CPU filter, profiler gets overloaded,

    RPC:Completed
    T-SQL:
    SQL:Batch Completed

    Columns:
    AppicationName, TextData, Duration, LoginName, SPID, DatabaseID, CPU, Reads, Writes

    Filters:
    CPU > 10



  44. Luis Martin Moderator

    Yes, Joe. My mistake.


    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.



  45. simflex New Member

    Sorry, Joe and Luis; I spent 2 days at work doing maintenance work on our systems.

    Believe it or not, I just got back from work this morning.


    Rather than sleeping, I am jumping on my pc to try and see what I can do about this issue.

    Joe, sorry about neglecting to answer that question.

    As far as I know, no one installed a 3rd party tool on the server. No update patches, just nothing that I am aware of.

    I will run the profiler again with the additional info you provided and post back in a bit.
  46. simflex New Member


    I am getting the following error:

    Server: Msg 170, Level 15, State 1, Line 1
    Line 1: Incorrect syntax near 'SHOWPLAN_ALL'.
    Changed language setting to us_english.
    Server: Msg 170, Level 15, State 1, Line 1
    Line 1: Incorrect syntax near 'SHOWPLAN_ALL'.

    Below is the code I copied from profiler to sql analyzer

    -- network protocol: TCP/IP
    set quoted_identifier on
    set implicit_transactions off
    set cursor_close_on_commit off
    set ansi_warnings on
    set ansi_padding on
    set ansi_nulls on
    set concat_null_yields_null on
    set language us_english
    set dateformat mdy
    set datefirst 7
    GO
    SET SHOWPLAN_ALL
    GO
    SET STATISTICS IO OFF
    GO
  47. simflex New Member

    I think I found something that may potentially solve this problem.

    I checked the version of sql server on the server, and the service pack.

    Here is what I got. I just can't believe this!!


    Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Personal Edition on Windows NT 5.2 (Build 3790: Service Pack 1)


    Should this have an impact on the problem?
  48. Luis Martin Moderator

    Of course. Personal is for one user only.
    You can connect more, but there is an algorithm to decrease performance with more than one user.

    BTW: Is SET SHOWPLAN_ALL ON (you forget ON and OFF)

    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.



  49. simflex New Member

    Here is the result of the showplan:

    -- network protocol: TCP/IP set quoted_identifier on set implicit_transactions off110NULLNULL1NULLNULLNULLNULLNULLNULLNULLNULLSETON0NULL
    set cursor_close_on_commit off220NULLNULL2NULLNULLNULLNULLNULLNULLNULLNULLSETON0NULL
    set ansi_warnings on330NULLNULL3NULLNULLNULLNULLNULLNULLNULLNULLSETON0NULL
    set ansi_padding on440NULLNULL4NULLNULLNULLNULLNULLNULLNULLNULLSETON0NULL
    set ansi_nulls on550NULLNULL5NULLNULLNULLNULLNULLNULLNULLNULLSETON0NULL
    set concat_null_yields_null on660NULLNULL6NULLNULLNULLNULLNULLNULLNULLNULLSETON0NULL
    set language us_english770NULLNULL7NULLNULLNULLNULLNULLNULLNULLNULLSET0NULL
    set dateformat mdy880NULLNULL8NULLNULLNULLNULLNULLNULLNULLNULLSET0NULL
    set datefirst 7990NULLNULL9NULLNULLNULLNULLNULLNULLNULLNULLSET0NULL

    On the sql server, I was more concerned about the service pack which is currently 1.

  50. Luis Martin Moderator

    8.00.760 is sp3 or sp3a. Not sp1.

    Sp1 is 8.00.384

    If I were you I'll worry about Personal instead Standard o Enterprise Edition.

    Please run:

    select @@version





    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.



  51. simflex New Member

    I ran that already and posted result earlier. Here it is again:

    Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Personal Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
  52. Luis Martin Moderator

    Simflex:

    I don't know why do you have that. For sure 8.00.760 is sp3 or sp3a.

    Anyway, if sql version is Personal, I have no recommendation at all.

    About the results you show with SHOWPLAN_ALL, I suppose you forget to put the long query.

    So:

    Review the Joe's post about the order between SET --- QUERY --- SET, and try again.


    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.



  53. Luis Martin Moderator

    I've moved to relevant forum.



    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.



  54. MohammedU New Member

    Please post the answers to the following questions along with all hardware including disk free space in any disk you have asked by Luis...

    1. For disk queue check "Current Disk Queue lenght" counter and let us know the value...
    2. Check the application response setting... Right click my computer/properties/Advanced/performance options/applicaton response.

    3. If you are running the applications on SQL SERVER, it is advisable to set the max server memory setting. What is the max server memory setting at this time?

    4. Make sure you have enogh free space in tempdb atleast 200-1000 MB...

    5. Are there any startup parameters set? Open enterprise manager/right click server/properties/startup parameters

    6. Check the sql server memory usage through perfmon "Total Server memory" and "Target Server Memory" values...

    7. What is the sql build? Run " exec xp_msver" and post the results..

    8. How is the peformance when you access the sql server from the console (server) or locally?

    9. If this multi processor server what is the setting of parallalism? "exec sp_configure 'max degree of parallelism'"

    10. Do you see any sql related error in SQL errorlog/application log/system log? Do you see any .mdp or .mdmp files in sql log folder?



    MohammedU.
    Moderator
    SQL-Server-Performance.com
  55. simflex New Member

    Luis,

    That is the problem, I didn't have that. I know this post has now spanned 4 pages (thanks again) but as I indicated initially, I inherited what someone built.

    If I had built this, I probably would have blown the sql away since I know all the setups and configs.

    What about this query results?

    I used the select @@version but what service pack is this:

    Microsoft SQL Server 2000 - 8.00.194 (Intel X86) Aug 6 2000 00:57:48 Copyright (c) 1988-2000 Microsoft Corporation Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)

    In any case, Is below the long query you are talking about?

    It won't run because of 2 variables called #temp tmp and @all_counters = 1


    sp_sqlagent_get_perf_counters
    SELECT 'object_name' = RTRIM(SUBSTRING(spi1.object_name, 1, 50)),
    'counter_name' = RTRIM(SUBSTRING(spi1.counter_name, 1, 50)),
    'instance_name' = CASE spi1.instance_name
    WHEN N'' THEN NULL
    ELSE RTRIM(spi1.instance_name)
    END,
    'value' = CASE spi1.cntr_type
    WHEN 537003008 -- A ratio
    THEN CONVERT(FLOAT, spi1.cntr_value) / (SELECT CASE spi2.cntr_value WHEN 0 THEN 1 ELSE spi2.cntr_value END
    FROM master.dbo.sysperfinfo spi2
    WHERE (spi1.counter_name + ' ' = SUBSTRING(spi2.counter_name, 1, PATINDEX('% Base%', spi2.counter_name)))
    AND (spi1.instance_name = spi2.instance_name)
    AND (spi2.cntr_type = 1073939459))
    ELSE spi1.cntr_value
    END
    FROM master.dbo.sysperfinfo spi1,
    #temp tmp
    WHERE (spi1.cntr_type <> 1073939459) -- Divisors
    AND ((@all_counters = 1) OR
    (tmp.performance_condition = RTRIM(spi1.object_name) + '|' + RTRIM(spi1.counter_name)))
  56. Luis Martin Moderator

    Simflex:

    1) Why do you have different information? In one post we have Personal Edition with sp3 and now we have Standard Edition with no service pack at all (8.00.194 means NO Service Pack).

    2) That is what Joe wrote:

    SET STATISTICS IO ON
    GO
    SET SHOWPLAN_ALL
    GO
    Your SQL query
    GO
    SET SHOWPLAN_ALL
    GO
    SET STATISTICS IO OFF
    GO

    3) Again, I don't think that query is from application. That query is from other program, may be 3rd party.

    sysperinfo is a system table. So I believe is from Profiler itself or Performance Monitor, NO from your application.

    4) You can't not include in the query (to find SHOWPLAN and/or STATISTICS) any temporary table like #temp.

    So,

    a) What is the SQL version and what SP do you have in the server (no in you pc, or other workstation).
    b) Find the appl query who have more duration.
    c) Answer the Mohammed questions.



    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.



  57. simflex New Member

    Hi Luis,

    Sorry for posting that other sql version. I should have known that it would cause confusion.

    The sql version is the original version I posted = personal edition sp3.

    I will look harder for application query. So far, I have not been finding other those non-appl queries.

    I don't know how I missed Mohammed's questions.

    Let me get on it now, will be back in a bit.
  58. simflex New Member

    questions, followed by answers to the best of my knowledge:

    Please post the answers to the following questions along with all hardware including disk free space in any disk you have asked by Luis...

    1. For disk queue check "Current Disk Queue lenght" counter and let us know the value...
    I am getting 100.0000
    2. Check the application response setting... Right click my computer/properties/Advanced/performance options/applicaton response.

    I don't see this. This is windows server2003

    3. If you are running the applications on SQL SERVER, it is advisable to set the max server memory setting. What is the max server memory setting at this time?
    2096

    4. Make sure you have enogh free space in tempdb atleast 200-1000 MB...

    Space available =7.81; size= 8.75; space allocated=1MB. To me, this is the source of the problem.

    5. Are there any startup parameters set? Open enterprise manager/right click server/properties/startup parameters
    Yes, 3:
    master.mdf; errorlog; and mastlog.ldf


    6. Check the sql server memory usage through perfmon "Total Server memory" and "Target Server Memory" values...

    7. What is the sql build? Run " exec xp_msver" and post the results..

    1ProductNameNULLMicrosoft SQL Server
    2ProductVersion5242888.00.760
    3Language1033English (United States)
    4PlatformNULLNT INTEL X86
    5CommentsNULLNT INTEL X86
    6CompanyNameNULLMicrosoft Corporation
    7FileDescriptionNULLSQL Server Windows NT
    8FileVersionNULL2000.080.0760.00
    9InternalNameNULLSQLSERVR
    10LegalCopyrightNULL© 1988-2003 Microsoft Corp. All rights reserved.
    11LegalTrademarksNULLMicrosoft® is a registered trademark of Microsoft Corporation. Windows(TM) is a trademark of Microsoft Corporation
    12OriginalFilenameNULLSQLSERVR.EXE
    13PrivateBuildNULLNULL
    14SpecialBuild49807360NULL
    15WindowsVersion2483819575.2 (3790)
    16ProcessorCount22
    17ProcessorActiveMask300000003
    18ProcessorType586PROCESSOR_INTEL_PENTIUM
    19PhysicalMemory20462046 (2145431552)
    20Product IDNULLNULL


    8. How is the peformance when you access the sql server from the console (server) or locally?
    very, very slow. If I click on a databasename or tablename or stored proc; it takes forever to expand

    9. If this multi processor server what is the setting of parallalism? "exec sp_configure 'max degree of parallelism'"

    Name: max degree of parallelism; Min=0; max=32;config value=0; run value=0


    10. Do you see any sql related error in SQL errorlog/application log/system log? Do you see any .mdp or .mdmp files in sql log folder?

    No
  59. MohammedU New Member

    1. For disk queue check "Current Disk Queue lenght" counter and let us know the value...
    I am getting 100.0000


    This is really bad...Are there any disk errors? Check for any bad drives physically...

    2. Check the application response setting... Right click my computer/properties/Advanced/performance options/applicaton response.
    I don't see this. This is windows server2003

    I am sure even in Windows 2003 , you should see this option.

    4. Make sure you have enogh free space in tempdb atleast 200-1000 MB...
    Space available =7.81; size= 8.75; space allocated=1MB. To me, this is the source of the problem.


    I don't think it is the problem... but there is no harm increasing the size...If you don't have free space on the drive where tempdb located, move tempdb to other drive...
    http://support.microsoft.com/kb/224071

    9. If this multi processor server what is the setting of parallalism? "exec sp_configure 'max degree of parallelism'"
    Name: max degree of parallelism; Min=0; max=32;config value=0; run value=0


    Disable parallalism...set to 1

    exec sp_configure 'max degree of parallelism', 1
    go
    RECONFIGURE with override




    MohammedU.
    Moderator
    SQL-Server-Performance.com
  60. simflex New Member

    hi Mohammed, thanks for the assistance.

    Are there any disk errors? Check for any bad drives physically...
    No, we checked thoroughly but could find nothing.

    DBCC execution completed. If DBCC printed error messages, contact your system administrator.
    Configuration option 'max degree of parallelism' changed from 0 to 1. Run the RECONFIGURE statement to install.

    2. Check the application response setting... Right click my computer/properties/Advanced/performance options/applicaton response.
    I don't see this. This is windows server2003
    I am sure even in Windows 2003 , you should see this option

    I still don't see it. I see Performance Options but I don't see application response.

    If you don't have free space on the drive where tempdb located, move tempdb to other drive...

    There is enough space on the drive where tempdb is located.

    Disable parallalism...set to 1
    exec sp_configure 'max degree of parallelism', 1
    go
    RECONFIGURE with override


    I did but got the following message;
    DBCC execution completed. If DBCC printed error messages, contact your system administrator.
    Configuration option 'max degree of parallelism' changed from 0 to 1. Run the RECONFIGURE statement to install.

    Just an additional info:

    When we attempt to run an application from the internet browser, we get this:

    Active Server Pages error 'ASP 0113'
    Script timed out

    DEVPT/main.asp

    The maximum amount of time for a script to execute was exceeded. You can change this limit by specifying a new value for the property Server.ScriptTimeout or by changing the value in the IIS administration tools.

    This application sits on the server and accesses the sql server database we are trying to fix now.

    This has been going on for two weeks and I can't tell you guys how high my blood has risen because of this. I just can't seem to make any strides with it.
  61. simflex New Member

    I finally succeeded in finding an application to run and below is the result of the execution plan:<br /><br /> select top 1 fac, floor, zone, cell, bkgno from servername.databasename.dbo.tablenam where fieldname= 'J' and id = 'P00543'810NULLNULL1NULL1.0NULLNULLNULL1.0333333E-2NULLNULLSELECT0NULL<br /> |--Remote Query(SOURCE<img src='/community/emoticons/emotion-6.gif' alt=':(' />servername), QUERY<img src='/community/emoticons/emotion-6.gif' alt=':(' />SELECT TOP 1 Col1008,Col1006,Col1005,Col1004,Col1003 FROM (SELECT Tbl1001."fac" Col1003,Tbl1001."floor" Col1004,Tbl1001."Zone" Col1005,Tbl1001."Cell" Col1006,Tbl1001."Bkgno" Col1008 FROM "databasename"."dbo"."jail" T821Remote QueryRemote QuerySOURCE<img src='/community/emoticons/emotion-6.gif' alt=':(' />servername), QUERY<img src='/community/emoticons/emotion-6.gif' alt=':(' />SELECT TOP 1 Col1008,Col1006,Col1005,Col1004,Col1003 FROM (SELECT Tbl1001."fac" Col1003,Tbl1001."floor" Col1004,Tbl1001."Zone" Col1005,Tbl1001."Cell" Col1006,Tbl1001."Bkgno" Col1008 FROM "databasename"."dbo"."jail" Tbl1001 WHERE Tbl10NULL1.00.01.0333333E-2231.0333333E-2[servername].[databasename].[dbo].[jail].[Bkgno], [servername].[databasename].[dbo].[jail].[Cell], [servername].[databasename].[dbo].[jail].[Zone], [servername].[databasename].[dbo].[jail].[floor], [databasename].[databasename].[dbo].[jail].[fac]NULLPLAN_ROW01.0<br /><br /><br />I changed mostly the servername and databasename to preserve the source. Sorry, this is for the judicial system and highly confidential.<br /><br />I am curious to find out what those readings mean.
  62. MohammedU New Member

    Why are you using four part name?
    Are you executing the query from some other sql server as linked/remote server query?

    If not remove server name from the query...
    databasename.dbo.tablename should be fine...

    If you have the enough space why don't you increase tempdb manually...


    MohammedU.
    Moderator
    SQL-Server-Performance.com
  63. simflex New Member

    Yes, I am using the four part because the db is linked.

    Did you find anything wrong from what I posted?

    I am so frustrated because if what I posted is not an issue, I don't what else to do.
  64. Luis Martin Moderator

    Could you run the same query using Query Analyzer:

    SET STATISTICS IO ON

    your query.

    SET STATISTICS IO OFF

    and post the results?


    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.



  65. simflex New Member

    Here is what I got now from running several queries where cpu > 10.

    On this query:

    SET STATISTICS IO ON
    GO
    select disclaimer from security_db where uid = 31
    GO
    SET STATISTICS IO OFF
    GO

    The result is this:

    Table 'security_db'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0.


    On this query:
    SET STATISTICS IO ON
    GO
    select firstname, lastname from security_db where uid = '24'
    GO
    SET STATISTICS IO OFF
    GO

    The result is this:

    Table 'security_db'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0.

    On this query (same as yesterday):

    SET STATISTICS IO ON
    GO
    select top 1 fac, floor, zone, cell, bkgno from servername.database.dbo.tablename
    where cust = 'P' and id = 'P00830546'
    GO
    SET STATISTICS IO OFF
    GO

    The result is:


    (0 row(s) affected)


    Luis, I must mention here that the activity that takes up the most cpu, with the longest duration occur when I click on a database name or tablename or right-click on a stored proc to view the code.

    So, whatever it is that causes this thing to slow down, I don't know but what I do know is that it has to load with clicking on any database object.
  66. FrankKalis Moderator

    I've read this thread now right from the start 2 or 3 times, but honestly, I don't know more than I did before and I think this thread has probably come to a dead-end road.

    Would it be an option to spend some money for a consultant who can directly check your system at your place?

    --
    Frank Kalis
    Moderator
    Microsoft SQL Server MVP
    Webmaster:http://www.insidesql.de
  67. simflex New Member

    Would it be an option to spend some money for a consultant who can directly check your system at your place?

    Do you mean the database system or the server?

    I honestly believe that the problem is the database. We have top level server gurus who have vowed that the issue is not the server.

    What I am struggling with is a solid practical, time-tested instructions of what to look for on the db side because I truly believe db is the issue. So far, guys are trying, and I am doing the best I can to provide them with what they are asking for but like you said, it has helped very little.

  68. joechang New Member

    i think one reason this thread went the way it did is the lost of focus with too many drivers, too much confusion

    perhaps we should establish a convention in appropriate cases where one person volunteers to be the driver
    if he/she cannot continue, sign off, and some one else volunteers
  69. Luis Martin Moderator

    quote:Originally posted by joechang

    i think one reason this thread went the way it did is the lost of focus with too many drivers, too much confusion

    perhaps we should establish a convention in appropriate cases where one person volunteers to be the driver
    if he/she cannot continue, sign off, and some one else volunteers

    Agree. In this case I quit.


    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.



  70. simflex New Member

    Good people,

    sometimes we consider quality based on results.

    As far as I am concerned, whether this is solved or not, I am truly appreciative of all the many *unpaid* amount of time you guys spent *voluntarily* helping me.

    Thank you and please give yourselves credit for the well-intentioned efforts on your part to help me out this painful situation I am found myself in.
  71. Luis Martin Moderator

    quote:Originally posted by simflex

    Good people,

    sometimes we consider quality based on results.

    As far as I am concerned, whether this is solved or not, I am truly appreciative of all the many *unpaid* amount of time you guys spent *voluntarily* helping me.

    Thank you and please give yourselves credit for the well-intentioned efforts on your part to help me out this painful situation I am found myself in.

    That is unfair.

    I prefer Joe help you just because hi has more level than I.


    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.



  72. simflex New Member

    Can someone, anyone, please just answer one question for me.

    Please pretend as if this is the first question about sql server performance issues.

    Since the server (windows server2003) runs so well, and since if you click on any object on sql server, be it the database, be any of the tables, be it stored procs, be it management folder, be it security, it doesn't matter what you click on, the response time is soooo slow that it takes about 2 minutes or more to open.

    My question is this, if I back up the database, blow sql server away and reinstall it, does anyone know if this will solve the problem?

    Please let me know. Users are really, really very angry.
  73. joechang New Member

    i really think you should follow franks advice to get professional help

    you are blindly stabbing the in dark
    almost all performance problems can be solved very quickly if you know how to collect information

    for example, in reading perf mon, the "Scale" column is not the counter value, the counter value is displayed in the Last box
    the default scale for disk queue is 100.000, notice that is does not change
    yet you kept reporting the disk queue was 100.000

    SQL Server Enterprise Manager is a known pig that collects a lot of info everytime you click anything,
    thats why experienced DBAs get what they need with SQL scripts from QA
  74. simflex New Member

    Well,

    If I could afford to get professional help, trust me I would have done it as it would have saved me tons of headache.

    As for the performance monitoring request, you are absolutely right, I am stabbing in the dark but with proper guidance from experts like you, I could have moved a step closer to solving this. I have provided as much info about that performance read as possible. So, can you just pick the correct info from what I provided?

    For instance, here is the performance log again:

    The top part first:

    Last=0.000; Average=0.657; Minimum=0.000; Maximum=38.992; Duration=1.40

    Then at the bottom you have the following:

    Color | Scale | Counter | Instance | Parent |Object | Computer
    ---- |1.000 | pages/sec | -- | --- |memory| Computer Name
    ----|100.000|Avg. Disk Queue Length |_TotalPhysical Disk|---|Computer Name
    ---- |% Processor Time|_Total|-- | Processor |Computer Name

    This is all there is that I see. So, experts like you can get the relevant data, and in the process, point that out to me. Because even if they get a professional, I want to put myself in a position to do something about it when and if it happens again.

    I appreciate your help and everyone else's help but if the issue is because I am not providing the right data, please guide me as I wan tot get this taken care of.

    Thank you very much again.
  75. simflex New Member

    sorry, that last row should look like this:

    ---- |1.000 |% Processor Time|_Total|-- | Processor |Computer Name

    I missed the scale data of 1.000
  76. joechang New Member

    if there is one thing you need to learn from this
    when reporting perfmon info,

    do not bother reporting the Scale column

    the actual values are in the row:
    Last=0.000; Average=0.657; Minimum=0.000; Maximum=38.992; Duration=1.40


  77. simflex New Member

    Thanks,Joe.

    That's what I wanted to know.

    Did you see anything outstanding from that read, though?

    See, I have posted a bunch of info similar to that and no feedback as to whether they matter or not.
  78. Adriaan New Member

    At the risk of repeating something that may have been suggested earlier on - I haven't read the thread - but ...

    Have you checked the available disk space on all drives that are being used by this instance of SQL Server?
    Have you verified that the Windows Indexing Service is switched OFF at least for the SQL-related file types?
    Have you checked the file size(s) of the data and log file(s)? And what are the growth settings for these files?

    Is this a virtualized server?
  79. joechang New Member

    a single perf count reading generally has no value,
    you need to see the entire log, ie, many samples

    if you had followed the directions i gave in www.qdpma.com, it explained how to collect perfmon in log mode,

    regardless, SQL Profiler is probably more important for you

    follow the directions i give in the above URL (look for instructions)

    collect a profiler trace with the: Filters -> CPU -> Greater than or equal -> 10
    let it run for an hour or so, make sure that users are doing something

    if you can do that, then i will tell you what is slow
    why is for later
  80. simflex New Member

    Besides this:<br />EXECUTE msdb.dbo.sp_sqlagent_get_perf_counters<br />which has both the longest duration and highest cpu use,<br /><br />this is the one with the longest duration and greater cpu usage:<br /><br />exec sp_logout_user '2/15/2007 2:03:16 PM', '7'510NULLNULL1NULLNULLNULLNULLNULLNULLNULLNULLEXECUTE0NULL<br /> CREATE PROCEDURE sp_logout_user @datetime datetime, @uid int AS update security_db set logoff = @datetime, crypto_key = '' where uid = @uid621NULLNULL4NULL1.0NULLNULLNULL1.3578047E-2NULLNULLUPDATE0NULL<br /> |--Clustered Index Update(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[dbName].[dbo].[security_db].[PK_security_db]), SET<img src='/community/emoticons/emotion-6.gif' alt=':(' />[security_db].[logoff]=[@datetime], [security_db].[crypto_key]=''), WHERE<img src='/community/emoticons/emotion-6.gif' alt=':(' />[security_db].[uid]=[@uid]))632Clustered Index UpdateUpdateOBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[dbName].[dbo].[security_db].[PK_security_db]), SET<img src='/community/emoticons/emotion-6.gif' alt=':(' />[security_db].[logoff]=[@datetime], [security_db].[crypto_key]=''), WHERE<img src='/community/emoticons/emotion-6.gif' alt=':(' />[security_db].[uid]=[@uid])NULL1.01.0293772E-20.00000141.3578047E-2NULLNULLPLAN_ROW01.0<br />
  81. joechang New Member

    per
    http://www.sql-server-performance.com/forum/topic.asp?ARCHIVE=true&TOPIC_ID=1288

    apparently EXECUTE msdb.dbo.sp_sqlagent_get_perf_counters
    is one of the demo alerts installed by SQL

    in Enterprise Manager
    goto Management -> SQL Server Agent -> Alerts
    find it and disable or just disable all of them

    on the other one
    i find it hard to believe that a single row update has high cpu, it should be mostly zero

    zip the profiler trace file at email it to me at
    xxx
  82. simflex New Member

    Thanks so much for your continued assistance.

    I did send the zipped trace file.
  83. simflex New Member

    Thanks so much for your continued assistance.

    I did send the zipped trace file.
  84. simflex New Member

    This is now solved and you will be surprised what caused the problem in the first place.

    ODBC Tracing was turned on.

    Can you believe it?

    The great Joe Chang took it upon himself to help me out of my misery by giving me his personal email address so he could help more on a more personal level.

    We tried several diagnostic approaches including running the profiler again but no joy.

    Eventually, he asked me to zip sql error logs and other logs.

    Joe discovered something weird. There were the following messages;
    >In the Logs, the databases startup are skipped
    >2007-02-16 03:05:40.48 spid3 Skipping startup of clean database id 5
    >2007-02-16 03:05:40.48 spid3 Skipping startup of clean database id 6
    >2007-02-16 03:05:40.48 spid3 Skipping startup of clean database id 7
    >2007-02-16 03:05:40.48 spid3 Skipping startup of clean database id 8
    >2007-02-16 03:05:40.48 spid3 Skipping startup of clean database id 9

    and an assortment of the following:

    >Then you have repeated database starting messages
    >2007-02-16 07:56:07.03 spid55 Starting up database ''.
    >2007-02-16 07:56:08.20 spid55 Starting up database ''.
    >2007-02-16 07:56:09.28 spid55 Starting up database ''.
    >2007-02-16 07:56:09.63 spid55 Starting up database ''.
    >2007-02-16 08:13:44.49 spid58 Starting up database ''.
    >2007-02-16 08:13:44.77 spid58 Starting up database ''.

    Joe indicated these may not have anything to do with SQL Server after all and asked that I research further.

    Everything I read about Skipping startup of clean database and Starting up database pointed to MDAC and ODBC.

    So, I decided to look at the version of ODBC installed on that server. While
    looking, I clicked on the Tracing tab. Discovered that it was started.

    I then looked at three other servers we have that run similar apps, running
    on SQL Server and the Tracing on them were turned off.

    I turned off the tracing and vavoom, application and database activities started running with blazing speed again.

    I want to take a moment to thank Joe so very, very much for staying there
    with me.

    He didn't waiver; he didn't get irritated; he didn't loose his patience
    and most of all, his discovery led to the solution.

    I also want to thank those who contributed to this frustrating issue very much and hope that somehow, this thread will help someone in similar situation one.

    Thank you so much, indeed!
  85. MohammedU New Member

    Thanks for sharing your solution...<br />Contratulation Joe...You proved once again you are the one of the SQL Guru...<img src='/community/emoticons/emotion-1.gif' alt=':)' /><br /><br />When tracing is on don't you think SQL.log file grows very fast and big?<br /><br /><br /><br />MohammedU.<br />Moderator<br />SQL-Server-Performance.com
  86. joechang New Member

    this one has me bothered
    i have always relied on the assumption that anything causing performance problems will show in perfmon or profiler

    i will have to look sometime to see what counters will show the odbc trace is turned on

Share This Page