SQL Server Performance

2.5 mins for 4million rows, is this performance normal?

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by nmavra, Feb 27, 2009.

  1. nmavra New Member

    Hello all,
    I'm running a simple select query that brings me 4,5 million rows. The query is
    select * from blablabla
    The question is if it is normal to have such a long execution time and also if the network traffic generated is normal to be 1gb of data (the whole db is around 3.5gb large)!!! I'm using Management studio on a LAN and I see transfer rates of around 6-7mb/sec. Obviously if I run this query from home over my DSL connection it would take forever, right..?
    On the other hand what confuses me is that even if i run the query on the local SQL server, it still takes the same amount of time to complete.
    Is this normal, and if yes, what can I do to improve the performance?
    The server is a Dell PowerEdge 2950 with 2x Xeon 5120 (1.86GHz) and 8GB ram with 3 disks in raid5, on a Win2003 x64 running SQL2005 x64..
    The server frequently hits 100% cpu usage and I can't find what the bottleneck is.
    All help greatly appreciated!
  2. Adriaan New Member

    Why would you ever need to retrieve the complete contents of the table?
    Your queries should be to-the-point, as in: only select those columns that you actually have to use (so try not to use the *), and only retrieve rows that you need (so use a WHERE clause).
    If your simple query also includes an ORDER BY clause or DISTINCT, then you will have to allow for additional processing time on the server.
  3. SqlMan4u New Member

    It should not take this much time to retrieve the data.
    But here the problem is that your CPU usage is 100% very frequently. that means there is a bottleneck for sure. Usually the data retrieval becomes slow when CPU utilization is 100%.
    Are there any other programs running on this server like Web servers? Try to use the performance monitor to check what is causing 100% cpu usage.
  4. nmavra New Member

    Hello Adriaan, I'm retrieving the complete contents of the table just to check our performance, and this is a big enough table to test! :)
    SqlMan4u, I see the 100% cpu usage frequently, but when i run this simple query it doesn't hit 100% . It may reach 100% when other things are running in the background, but if only this query is running then the utilization goes up to 60-65% max.
    When you say performance monitor, which one do you mean exactly? I'm checking cpu/mem/disk etc usage by using Anvir Task Manager.
  5. Adriaan New Member

    An individual query that retrieves huge amounts of data will perform really differently from a huge amount of queries that retrieve small samples of specific data.
    Performance tests should be on the "normal" level of traffic that your SQL Server will be facing, perhaps unless you're troubleshooting I/O issues.
  6. satya Moderator

    I see that this question has popped up here after a long time [:)].
    The HIGH CPU can occurr due to many reasons and not for a query execution, if you still think that accessing all the columns on that table is required and needs an optimization then you have to think about creating views to obtain set of data and further refer to this http://sqlserver-qa.net/blogs/perftune/archive/2007/09/20/2152.aspx to identify the root cause.
  7. moh_hassan20 New Member

    That query consume IO and network resources rather than CPU.
    can you run and post the result of :
    set statistics io on
    set statistics time on
  8. nmavra New Member

    Hello, I ran the
    set statistics io on
    set statistics time on
    and re-ran the initial query and the execution time was 5minutes, this time for 5,200,000 results (our table grows fast).
    In fact, this is the message I got:
    SQL Server parse and compile time:
    CPU time = 0 ms, elapsed time = 1 ms.

    (5256685 row(s) affected)
    Table 'tHistory'. Scan count 1, logical reads 143315, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    SQL Server Execution Times:
    CPU time = 13610 ms, elapsed time = 339763 ms.
    Any comments on this?
  9. ndinakar Member

    SELECT * FROM Table is a bad query to measure performance numbers from. You will get high numbers even with the best of networks and they wouldnt mean anything. Run a more realistic query or a stored proc and get the numbers.
  10. nmavra New Member

    Hello ndinakar, I have been involved in various different urgent issues and so didn't look into this matter.
    The reason why we run this query is because it is needed by the management when we run some reports. What I mean is this query needs to run from time to time, it was not just used for the test.
    You say that this is a bad query and that I need to run a more realistic, could you please elaborate a bit on this?
    I am attaching a screenshot that shows some interesting things that are related to my SQL2005 instance using TaskInfo.
    Do you see anything that looks out of the norm? You can also see very high cpu usage there.
  11. ndinakar Member

    Occassional CPU spikes is normal.. if your CPU is pegged @ 90% then it should be concerning.
    What I meant by my earlier statement is that when you do a SELECT *, SQL server has to retrieve all rows. Unless you have an index covering all the columns in your query it will have to do an RID lookup. Instead use a SELECT with a couple of column list that are part of an index.
  12. satya Moderator

    When you get complaints about performance is degraded on the SQL Server applications and you observe high CPU means first thing to check for poor query plans, blocking and any scheduled jobs that runs DBCC statements during a busy period on the database, like the ones I have referred above. Not only this there is still another catch in the problem that unusal chain reaction within your environment a SQL Server 2000 service that is running on a computer that has a system time that is approaching a multiple of 49.7 days from the time when the operating system is restarted. Around this period, you back up a database at least one time or you restore a database at least one time (obviously).
    KBA 930484 talks that In this scenario, CPU utilization may approach 100 percent after the backup operation is complete or after the restore operation is complete. After approximately 15 minutes or less, CPU utilization automatically decreases. If you back up the database or restore the database when CPU utilization approaches 100 percent, CPU utilization appears to decrease.

    Note This problem appears to occur more frequently on Itanium-based computers than on x86-based computers. This problem is very noticeable on the SQL Server servers that perform backup operations or restore operations around this 49.7 day interval.
    sys.dm_os_schedulers - useful to find on whether your SQL Server engine is weakening!
  13. nmavra New Member

    Very interesting post!
    Here are my results as of now:
    FROM sys.dm_os_schedulers
    WHERE scheduler_id < 255
    scheduler_id current_tasks_count runnable_tasks_count
    0 7 2
    1 8 0
    2 7 2
    3 8 0
    select pending_disk_io_count from sys.dm_os_schedulers
    select AVG (runnable_tasks_count) from sys.dm_os_schedulers where status = 'VISIBLE ONLINE'
    (No column name)
    We use SQLServer 2005 x64 but have not yet applied SP3, will do that on our next downtime!

Share This Page