Sql server processing really really slow

  • HI Guys,

    i am very new in SQL server 2005, i don't know much tools and features about sql 2005, Acutally i need some ideas. we are running a millions of records in a hours time and sql sever process is getting soo slow, it's take ages to finish my single query. and some time it's happend that our network speed get slow the processing get really really slow. @ the moment i m runing 2 database mirroring and replication on the server, i dont' know this might casue the slow down sql server processing,

    althought we got the best system to run the processing..

    if any body have any good idea please let me know. i mean like if is there any way to spliting up database into different databases on 2 ,3 server to process the things fastly .

    Thanks and looking forward.

  • Sounds like a bottleneck somewhere although its difficult to say in the absence of any metrics. Are you experiencing memory pressure? I/O pressure? What are your batch requests per second? Take a look at the sys.dm_os_performance_counters DMV – i.e. buffer cache hit ratio, batch requests per second, etc. Check out SQL-Server-Performance.com for more information.

    select *

    from sys.dm_os_performance_counters

    where counter_name = 'Batch Requests/sec'

    select (a.cntr_value * 1.0 / b.cntr_value) * 100.0 as [BufferCacheHitRatio]

    from (select *, 1 x from sys.dm_os_performance_counters

    where counter_name = 'Buffer cache hit ratio'

    and object_name = 'SQLServer:Buffer Manager') as a

    join

    (select *, 1 x from sys.dm_os_performance_counters

    where counter_name = 'Buffer cache hit ratio base'

    and object_name = 'SQLServer:Buffer Manager') as b

    on a.x = b.x

    select *

    from sys.dm_os_performance_counters

    where counter_name = 'Page life expectancy'

    You might also want to consider using the performance dashboard or SQLH2 so that you can trend these over time.

    Hope this helps, good luck 🙂

  • some time i m hving I/O problems, but processing is too slow. now the server is working bit fine .. and i hv get the result..

    SQLServer:SQL Statistics Batch Requests/sec 146302272696576

    99.8725505814880

    SQLServer:Buffer ManagerPage life expectancy 1461765792

    SQLServer:Buffer Node Page life expectancy 0001461765792

    i know this might not true @ the moment, bcoz this was taken when not that much problem is for process the data....

    but this might can help u..

  • There are a number of possible causes.

    You could be hitting hardware bottlenecks. You could have poor table or index design, you could have poorly written queries. You could have all three.

    Is the server always slow? Are there times when everything slows down? Are certain queries always slow?

    As a first attempt at diagnosis, run perfon, and check the following counters:

    Processor: User time

    Memory: Pages/sec

    Physical disk: sec/read, sec/write, transfers/sec, %idle time.

    SQL Memory manager: Buffer cache hit ratio

    There are others that I'm sure people will suggest.

    You can use profiler to idenify badly performing queries. Run them in management studio with the exec plan switched on and look for table scans or clustered index scane. Both may indicate that your indexing is poor.

    Look for cursors or single row selects in while loops. Both are really bad ways of doing things and if they can be converted to set-based code will run faster.

    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

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

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