Post reply

SQL Server slowness

  • January 15, 2013 at 4:45 am

    #266986

    I have a SQL Server instance installed on a dedicated server with 32GB RAM.

    My colleague who spends the majority of his time working on the databases on this server has been complaining of slowness in recent weeks/days!

    I made some tweaks initially, like changing the MAX SQL Server memory to 28GB.

    I also collected some perfmon data over a few days and put them in a spreadhseet (as advised by Brent Ozar's excellent article http://www.brentozar.com/archive/2006/12/dba-101-using-perfmon-for-sql-performance-tuning. This is attached below. I don't see anything out of the ordinary but if you guys can then let me know.

    a simple count on a primary key on a table of 10 million rows took 7 seconds. Is this normal?! I performed a similar query on a different server and it took less time.

    SQL Server was last re-started on the 14th December 2012.

    SELECT *

    FROM sys.dm_os_wait_stats

    order by wait_time_ms desc

    gives back this:

    Also, tempdb is currently standing at 160GB but mostly unused space. But that wouldn't hurt performance would it?

    Any advice would be much appreciated.

    ---------------------------------------------------------

    It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
    David Edwards - Media lens[/url]

    Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
    Howard Zinn

  • January 15, 2013 at 5:42 am

    #1577280

    To start off make sure the indexes and stats are up to date.

    There seems to be parrallelism , do u have proper indexes defined?

    There also seems to be Page IO latch for read is operations , try to identify the long running queries and review the execution plan it should give you a place to start.

    Jayanth Kurup[/url]

  • January 15, 2013 at 5:55 am

    #1577285

    Jayanth_Kurup (1/15/2013)

    To start off make sure the indexes and stats are up to date.

    There seems to be parrallelism , do u have proper indexes defined?

    There also seems to be Page IO latch for read is operations , try to identify the long running queries and review the execution plan it should give you a place to start.

    If you only knew the uphill task that I have!

    The SQL instance is the home for a badly designed database (containing more than 3000 tables) mostly full of rubbish. I'm still waiting on the DB owners to confirm which tables need to go/archived etc...

    Indexes and stats have never been updated since they were created!

    max degree of parallelism default has been changed to 4 - would this cause problems?!

    Many many many heaps (but these could be the tables than will need deleting archiving so i don't want to make a big deal out of these ones).

    The worry is, it's a production server so we can't have much down time. Arrrghhhh!

    oh and I forgot to mention that the main database used by the front end web app has a VLF count of more than 350

    ---------------------------------------------------------

    It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
    David Edwards - Media lens[/url]

    Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
    Howard Zinn

  • January 15, 2013 at 6:29 am

    #1577307

    you need help , id start with the indexes since thats a quick win , maxdop for highly transactional db is bad for dw is good, vlf count is too high , check log reuse wait desc in sys.databases.

    Jayanth Kurup[/url]

  • January 15, 2013 at 10:54 am

    #1577432

    Is most of the stuff being run SP's or adhoc?

    I'd start with a DBCC Freeproccache, that should be the easiest

    then do a sp_updatestats

    rebuilding indexes might take a while...

    examine the exec plans...

    look up Kimberly Tripp on youtube there's a video of her giving a talk on procedural optimization...

    also there's ton's of great information here: (it's the MCM readiness video series put together by KT and the people at SQLSkills.com)

    http://technet.microsoft.com/en-us/sqlserver/ff977043.aspx

    ---------------------------------------------------------------
    Mike Hahn - MCSomething someday:-)
    Right way to ask for help!!
    http://www.sqlservercentral.com/articles/Best+Practices/61537/
    I post so I can see my avatar :hehe:
    I want a personal webpage 😎
    I want to win the lotto 😀
    I want a gf like Tiffa :w00t: Oh wait I'm married!:-D

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply