Announcement

Collapse
No announcement yet.

SQL 2000 Performance Issues

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • SQL 2000 Performance Issues

    Hello all

    we are currently still using Server 2000 aswell as SQL 2000, with plans to move to server 2008/ SQL 2008 later this year

    however, im having serious performance issues with SQL at the moment

    the server is the PDC for the domain, DNS aswell as the SQL for the company, we have about 90 users.

    the spec of the server is 8gb Ram (but only 3.5 will be seen due to 2000 limitations), Dual 3.2 Xeon Procs

    when i have ran performance monitors i used

    Target server memory (KB) average is @ 82.45 an maximum is @ 472.015!!!!

    Batch Server requests - these readings are - average @ 40.07 and max is @ 300.07

    the memory is configured as dynamic

    do those readings sound ok to you?
    Last edited by mudcow007; 15th June 2010, 14:09.

  • #2
    Re: sql 2000 (i dunno where to put this thread) performance issues

    would PAE help you with the extra memory??
    http://support.microsoft.com/kb/268363/

    http://support.microsoft.com/kb/283037
    Please do show your appreciation to those who assist you by leaving Rep Point https://www.petri.com/forums/core/im.../icon_beer.gif

    Comment


    • #3
      Re: sql 2000 (i dunno where to put this thread) performance issues

      sorry should have mentioned its server 2000 (standard)

      Comment


      • #4
        Re: sql 2000 (i dunno where to put this thread) performance issues

        Crap.

        Unfortunately, performance monitoring, and interpretation thereof, is not one of my strong areas.

        I'd recommend upgrading it ASAP, to let it use as much memory as it can.
        I do know that SQL will take as much memory as is available to it..

        How's your paging looking.. ?
        Please do show your appreciation to those who assist you by leaving Rep Point https://www.petri.com/forums/core/im.../icon_beer.gif

        Comment


        • #5
          Re: sql 2000 (i dunno where to put this thread) performance issues

          paging looks ok to be honest...

          it comes down to costs here, they say they dont need to upgrade so the cost is a waste!!

          i think with the end of life for 2000 coming up soon it hink that will be a kick in the arse!

          Comment


          • #6
            Re: sql 2000 (i dunno where to put this thread) performance issues

            the stubborn mule in me says:

            write up a business case and present it.
            When they say it's not worth the cost, simply say ok.
            Next time someone complains, say you've previously analysed the situation, and established that there's nothing further you can do about the problem at present, and that in the current scenario, you are actually underusing the supplied hardware, but cannot change it,
            Please do show your appreciation to those who assist you by leaving Rep Point https://www.petri.com/forums/core/im.../icon_beer.gif

            Comment


            • #7
              Re: sql 2000 (i dunno where to put this thread) performance issues

              Sorry I'm late to the party guys... i got busy for a couple days...
              Ok, so your perfmon data needs to include disk Qs as well as cpu data...
              don't just assume that it's a memory issue just because you've got lower memory than you think you need. I'm not saying it's not a memory issue, i'm just saying that I haven't heard enough to land on that yet.

              You may very well find also that tuning some queries and indexes may get your further than the sql upgrade... that's where i'd spend the bulk of my time to be honest. Tune what you have then work on upgrades. But if the DB doesn't perform well to begin with then you'll just fill up the next box and the next one.
              Sean McCown, SQL Server MVP

              See my FREE SQL Server training videos at:
              http://www.MidnightDBA.com

              Blog Author of:
              Database Underground -- http://infoworld.com/blogs/sean-mccown
              DBA Rant http://dbarant.blogspot.com

              Comment


              • #8
                Re: sql 2000 (i dunno where to put this thread) performance issues

                thanks for the reply midnight...its never to late to join the party!

                right ok, im going to run a log for :

                Memory: pages/sec
                Memory: available Bytes
                physical Disk % disk time
                physical disk avg disk queue length
                Processor % time
                system % processor length
                SQL server buffer cache hit ratio

                an hopefully that should give more hints to whats going on.

                Comment


                • #9
                  Re: sql 2000 (i dunno where to put this thread) performance issues

                  Update

                  Hello all im still getting loads fo complaints regarding our SQL server, i have run the following on performance monitor an results are below

                  Memory: Page/ Sec - Average = 0.263 Max = 18.00
                  Memory: Available Bytes - Average = 1013.280 Max = 1014.000
                  Physical Disk: % Disk Time - Average = 12.351 Max = 27.533
                  Pysical Disk: Avg. Disk Queue Length - Average = 0.242 Max = 1.043
                  Processor: % Processor Time - Average = 1.851 Max = 54.297
                  System: Processor Queue Length - Average = 0.030 Max = 2.000
                  SQL Server Buffer: Buffer Cache Hit Ratio - Average = 99.341 Max = 99.343

                  do these figures seem normal?

                  thanks

                  Comment


                  • #10
                    Re: sql 2000 (i dunno where to put this thread) performance issues

                    OK, so a lot of these counters look ok... you're not disk-bound, and as far as i can tell you're not cpu or memory-bound. Not on the surface anyway...

                    so you may be blocking instead...

                    run this during one of the times they're complaining and look at the results... if there's something in the 'blocked' column other than 0, then that's the spid causing the issue. Notice I didn't ask you to post the results here. It would prob be too long. but you can paste snippets here if you like... or you can put it in a file and attach it to your post... if you do though just filter out the sensitive stuff like machine names and user accts...

                    run this:
                    SELECT * FROM SYSPROCESSES

                    and again, if you have some info in there that you don't want us to see like app names, server names, etc, then just leave those out of the resultset.
                    Sean McCown, SQL Server MVP

                    See my FREE SQL Server training videos at:
                    http://www.MidnightDBA.com

                    Blog Author of:
                    Database Underground -- http://infoworld.com/blogs/sean-mccown
                    DBA Rant http://dbarant.blogspot.com

                    Comment


                    • #11
                      Re: sql 2000 (i dunno where to put this thread) performance issues

                      I personally wouldn't worry as much about the SQL performance issue but rather on the fact that the SQL server runs on a DC. That's a No No IMO.
                      Caesar's cipher - 3

                      ZKHQ BRX HYHQWXDOOB GHFLSKHU WKLV BRX ZLOO UHDOLVH LW ZDV D ZDVWH RI WLPH!

                      SFX JNRS FC U6 MNGR

                      Comment


                      • #12
                        Re: sql 2000 (i dunno where to put this thread) performance issues

                        While I agree with you about sql being on a DC, that wasn't part of the question. We all know it's bad and we're not going to change that here... so the best we can do is work on the perf problem as best we can.
                        Sean McCown, SQL Server MVP

                        See my FREE SQL Server training videos at:
                        http://www.MidnightDBA.com

                        Blog Author of:
                        Database Underground -- http://infoworld.com/blogs/sean-mccown
                        DBA Rant http://dbarant.blogspot.com

                        Comment


                        • #13
                          Re: sql 2000 (i dunno where to put this thread) performance issues

                          thanks for the replies guys

                          i know its not ideal it being the PDC but its the domain i inherited when i got the job, its also the DNS server too!

                          i will try an run those SQL commands later today..

                          im just looking at SQL Profiler at the moment, i have found threads to say create a template with the following items to see if you can spot any issues.

                          Database:

                          database file auto grow
                          log file auto grow

                          Errors/ Warnings:

                          errorlog
                          eventlog
                          Exception

                          Lock:

                          Lock:deadlock
                          Lock:deadlock chain


                          Stored Procedures:

                          RPC:completed
                          RPC:starting
                          SP:cachehit
                          SP:cacheinsert
                          SP:cachemiss
                          SP:cacheremove
                          SP:completed
                          SP:execcontenthit
                          SP:recompile
                          SP:starting
                          SP:stmtcompleted

                          T-SQL:

                          SQL:batchcompleted
                          SQL:batchstarting
                          SQL:stmtcompleted


                          after i have ran the trace, what do i do with it?!

                          appologies im lame when it comes to SQL!

                          thanks
                          Last edited by mudcow007; 23rd June 2010, 09:45.

                          Comment

                          Working...
                          X