Performance Advise

  • Please review my servers configuration and let me know what steps you suggest. I always get complaints for slow running queries.

    Physical Memory20 GB

    Virtual Memory39.6 GB

    CPU's8

    CPU Speed3000 MHz

    CPU TypePIII Xeon

    OS Win 2003 Enterprise(SP2)

    SQL Server 2005-Enterprise(SP2)

    Avg DBSize 1.5 TB

    No PK's on any tables but we do have clustered/non clustered indexs on all the tables

    I am trying to do some analysis so that i can decide on if i need to add any resource to my existing servers but not sure where to start, could some one point me to right direction.

    If i run some complicate queries and records time,CPU/memory utilization wud that help?

  • 64 or 32? Guessing 64..

    Hardware looks pretty good. How are your drives laid out? Data on one volume? Logs on another? How many drives? Local or SAN or ???? RAID configuration of those drives. How often do you re-index? Have you traced any long running queries to see what they are doing EXACTLY and what objects they are utilizing? Have you reviewed the query plans of the slow running queries to see where they are spending their time?

    You could have one or two processes that are HEAVILY used but REALLY inefficient, you need to find out. You also like have some other processes that are rarely used, ignore them for now, we are looking at bang-for-the-buck. An inefficient heavily used process is more expensive that a rarely used inefficient process.

    CEWII

  • Why no primary keys?

    With performance problems, the first place to look is the queries and indexes, not the hardware. These two articles may help you get started.

    http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/

    http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-2/

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • There are no PK;s since begining but they do have clustered index I dont think if that serves as PK , Actualy we do not have a unique key on our data may be we can do a composite key but we may not change the structure at this point.

    All our drives are on SAN and ofcourse data and log files are on different drives and more over all over data is in monthly partition.

    I did check query plans many times for long runing queries but did not find any weired stuff there and there were no index scans at all.

    Most of our production data is used just to read and we do not update any data directly on production. I wonder if 15 concurrent users trying to read only 1 database does they lock themselves or wait for 1 process to complete for another processs to start.

    Here is one of the sample queries

    drop table #emp

    select distinct EMPID

    into #emp

    from vwEmp_act

    where (place IN ('21','25','51') or (empcd = '1' and jobcode = 'Y'))

    AND empflag = 'F' and (servcdate between '1/1/2005' and '1/1/2009')

    go

    select distinct a.empid

    into #step1_ct

    from dbo.vwEmp as a inner join RevEmp.dbo.EmploerRaw as b

    on a.empspec = b.speccode, #DateCriteria

    where servcdate between serv_start and serv_end

    and

    (a.regid=b.regid)

    and

    (b.RegEmpSpec in (7,12))

  • Tara (7/17/2009)


    There are no PK;s since begining but they do have clustered index I dont think if that serves as PK , Actualy we do not have a unique key on our data may be we can do a composite key but we may not change the structure at this point.

    No pk and no unique index? Why not? How do you prevent duplicate rows?

    I did check query plans many times for long runing queries but did not find any weired stuff there and there were no index scans at all.

    So what are people complaining is slow? If you don't have long running queries, what are the complaining about?

    Most of our production data is used just to read and we do not update any data directly on production. I wonder if 15 concurrent users trying to read only 1 database does they lock themselves or wait for 1 process to complete for another processs to start.

    Read locks don't block other readers. If you have on updates, you shouldn't have any blocking.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Tara (7/17/2009)


    Here is one of the sample queries

    drop table #emp

    select distinct EMPID

    into #emp

    from vwEmp_act

    where (place IN ('21','25','51') or (empcd = '1' and jobcode = 'Y'))

    AND empflag = 'F' and (servcdate between '1/1/2005' and '1/1/2009')

    go

    select distinct a.empid

    into #step1_ct

    from dbo.vwEmp as a inner join RevEmp.dbo.EmploerRaw as b

    on a.empspec = b.speccode, #DateCriteria

    where servcdate between serv_start and serv_end

    and

    (a.regid=b.regid)

    and

    (b.RegEmpSpec in (7,12))

    Do you want advice on improving the performance of those queries? If so, can you post the entire procedures, table defs, index defs and execution plan please.

    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • other thing to mention is setting memory on the sql server box. our servers are dedicated to sql server and we set to use 90% of available memory for sql servers and also checkthe option of boos sql priority.

    If i set the memory to use 50% only does the sql server stick to 50% or it can also use more then 50% when required?

    If i need to test memory setting on sql server do i need to restart the service everytime of change settings?

  • Read locks don't block other readers. If you have on updates, you shouldn't have any blocking

    as i said most of the time we only do SELECTS on the databases and i see SHAREDLOCK in the activity monitor, does it mean it is putting in wait for the second process to perform SELECT query.

    does using READ UNCOMMITTED / NOCLOCK improves performance than Having shared lock.

  • Tara (7/17/2009)


    Read locks don't block other readers. If you have on updates, you shouldn't have any blocking

    as i said most of the time we only do SELECTS on the databases and i see SHAREDLOCK in the activity monitor, does it mean it is putting in wait for the second process to perform SELECT query.

    does using READ UNCOMMITTED / NOCLOCK improves performance than Having shared lock.

    Dont read through locks. There are exceptions , but as a rule of thumb dont.

    Heres a good example why http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/11/10/1280.aspx

    Post the query plans Etc as suggested by Gail above....



    Clear Sky SQL
    My Blog[/url]

  • Tara (7/17/2009)


    Read locks don't block other readers. If you have on updates, you shouldn't have any blocking

    as i said most of the time we only do SELECTS on the databases and i see SHAREDLOCK in the activity monitor, does it mean it is putting in wait for the second process to perform SELECT query.

    As I said, read locks (ie locks taken by selects) do not block readers (ie other people doing selects)

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • I agree with Gail, hardware is not the place to start, especially since it appears that you've got a pretty strong system. Have you systematically collected performance data on the queries? Meaning, set up a server side trace, gather data over a period of time (I'd recommend a couple of days, make sure you have disk space available) and then aggregate that data to see which queries are taking the most time, etc.

    Another approach is to gather wait states on the server. Find out what things are waiting for, disk, cpu, memory, and then determine what is waiting and what is causing the wait and go for the tuning from there.

    Just because you're not getting index scans doesn't mean your queries are running optimally.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Dave Ballantyne

    I understand READ UNCOMITTED is exceptional as it gives inconsitent result but i think we dont worry about it as we do not update our productions so i think that should be fine as long as we get all the data very quick.

    could some one please answer my question for memory settings on sql server

  • Tara (7/17/2009)


    Dave Ballantyne

    I understand READ UNCOMITTED is exceptional as it gives inconsitent result but i think we dont worry about it as we do not update our productions so i think that should be fine as long as we get all the data very quick.

    Read uncommitted is only useful when selects are getting blocked by inserts/updates/deletes. If you have no inserts/updates/deletes, then you will have no blocking. Hence using read uncommitted will not get the data any faster.

    could some one please answer my question for memory settings on sql server

    What are your memory settings?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Tara (7/17/2009)


    other thing to mention is setting memory on the sql server box. our servers are dedicated to sql server and we set to use 90% of available memory for sql servers

    With 20GB of physical memory you should allow at least 3-4GB for the OS. Otherwise you risk high paging and instability from the OS

    and also checkthe option of boos sql priority.

    You should not need to use that and it's not always a good idea.

    http://support.microsoft.com/kb/319942

    If i set the memory to use 50% only does the sql server stick to 50% or it can also use more then 50% when required?

    Whatever you set the max memory to is the max that SQL will use

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • The min memory setting is always 0 by default do i need to change that to work better.

    If it is 0 does ti mean there no min value? what if i change that to use 5gb out of 20gb?

Viewing 15 posts - 1 through 14 (of 14 total)

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