locked
Speed question RRS feed

  • Question

  • We use SQL Server 2005, and the SQL Server machine is a 64 bit machine with Win 2003, and 8 processors of 2394 Mhz each and 32 gig of RAM. CPU usage ranges from 50% to 80%.
    There are 2 active databases on the server, 1 is about 2 gig in size, the other (the database I am using) is about 200 meg.
    There are about 2 T of free disk space on the data drive, and 70 Gig on the log drive.
    There is no other program running in the server except SQL Server.
    For testing, I made a copy of the 200 meg database, and on the query options I selected "Discard results after excecution"
    I am the only one using this test database, there is no insertion or deletion or any other activity in the database besides me running a query for testing.
    On the server itself, when I run the exact same query (on SSMS) on this test database that nobody else is using, the speed is not consistent. It varies between 6 - 14 seconds. Again, the amount of data stays the same all the time since nobody is inserting or deleting data there.
    What can cause this ?
    It's not the network because I run the query on SSMS on the server itself.
    It's not that another process blocking the query, since nobody else is using the test database, and there is no other activity going on in the test database.
    When I check if there is any blocking going on with the other 2 live databases, sometimes there are.
    Could the activities in the other databases affect the speed on this database ?

    Thank you

    Thursday, March 29, 2012 2:43 PM

Answers

  • No guarantee that any one of these by themselves will absolutely give you more consistency, but they can eliminate a couple sources of your varying results:

    • Reset all your statistics before every run.
    • Put your test query in a PROC and run with OPTION RECOMPILE so the execution plan is recreated every time
    • Maybe even DROP and recreate the test tables from another copy of the tables each time so it's always a newly created table (and indexes if any).
    • Is whatever it is you are testing something that could be done by installing and using SQL Server Express on your local machine, or does it have to be on the production server?  (Still do the above steps to try and create a consistent scenario, though).
    • Are you running one single identical query every time with the same items in the where clause?  The execution plan may be changing on the fly if not.
    • Proposed as answer by Naomi N Thursday, April 12, 2012 3:08 PM
    • Marked as answer by Naomi N Thursday, April 12, 2012 3:08 PM
    Thursday, March 29, 2012 7:29 PM
  • Both databases are so small they both can reside in buffer pool entirely. 2GB + 200MB should easily fit in 32GB RAM, if properly set.
    The root cause is something else.

    1. Have you checked MIN/MAX memory settings for SQL Server?
    2. Is the SQL Server installed as 64-bit?
    3. Since the operating system is Windows 2003, have you checked you AWE setting (to get access to more RAM than 4 GB)?
    4. Have you checked the "keep pages in memory" group policy setting?
    5. Is the server running multiple instances?


    N 56°04'39.26"
    E 12°55'05.63"

    • Marked as answer by Naomi N Thursday, April 12, 2012 3:09 PM
    Sunday, April 1, 2012 8:13 AM
  • A frequently run bad query can cause 80% cpu load.

    System & query optimization:

    http://www.sqlusa.com/articles/query-optimization/

    Disk resources optimization:

    http://www.sqlusa.com/bestpractices/configureharddisk/

    You can find the offending queries via:


    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012


    • Proposed as answer by SAlok Friday, April 6, 2012 12:08 PM
    • Marked as answer by Naomi N Thursday, April 12, 2012 3:09 PM
    • Edited by Kalman Toth Tuesday, October 2, 2012 5:47 PM
    Friday, April 6, 2012 11:44 AM

All replies

  • Hi, all databases are on the same server right? Then they will compete for the same resources (e.g., processor, memory, disk). For example, if lots of queries are being executed then probably it will wait a bit for a slot on processor.

    By blocking you mean accessing the same rows/pages right (i.e., locks and concurrency)? Then the activity on the other databases won't affect the test database you have created (this way).

    David.

    Thursday, March 29, 2012 3:08 PM
  • Thank you.
    Yes, all databases are on the same server.
    Yes, blocking is when I run this query the other 2 databases would appear for a couple seconds, then dissappear:
    SELECT database_id,sql_handle,session_id ,status ,blocking_session_id,wait_type ,wait_time ,wait_resource,transaction_id
    FROM sys.dm_exec_requests WHERE status = N'suspended';

    So, the reason why the speed of the query on the test database varies between 6 - 14 seconds, is because when it took 6 seconds the other databases were not using the resources (processor, memory, etc) , and when it took 14 seconds, the other database were using the resources (processor, memory, etc) ?

     CPU usage ranges from 50% to 80%, so you think the machine is too busy to handle the 2 databases ? It is a machine with 8 processors of 2394 Mhz each, maybe need a machine with more CPUs ?

    Thursday, March 29, 2012 3:14 PM
  • If you're testing queries with SSMS, is there anything on your local machine that could effect your results? Open sessions?

    Is the execution plan identical on every execution?

    Have you run SQL Profiler?

    Are the tables indexed correctly?

    Sorry but there are many things that could be interfering with your testing.


    Try to look ahead so you're not forced to look behind.

    Thursday, March 29, 2012 3:22 PM
  • The issue you are facing can by related with only one of those resources (processor, memory, disk).

    Before saying that you need more cpu/memory/disk speed you should analyze it in a much more detail to understand exactly where's the bottleneck. For this you can check Wait Stats.

    David.

    Thursday, March 29, 2012 3:23 PM
  • I ran the query on SSMS on the server itself, not on my local machine. What do you mean by Open sessions?
    Is the execution plan identical on every execution? Yes

    I will check out the Wait Stats.

    Thank you

    Thursday, March 29, 2012 3:37 PM
  • No guarantee that any one of these by themselves will absolutely give you more consistency, but they can eliminate a couple sources of your varying results:

    • Reset all your statistics before every run.
    • Put your test query in a PROC and run with OPTION RECOMPILE so the execution plan is recreated every time
    • Maybe even DROP and recreate the test tables from another copy of the tables each time so it's always a newly created table (and indexes if any).
    • Is whatever it is you are testing something that could be done by installing and using SQL Server Express on your local machine, or does it have to be on the production server?  (Still do the above steps to try and create a consistent scenario, though).
    • Are you running one single identical query every time with the same items in the where clause?  The execution plan may be changing on the fly if not.
    • Proposed as answer by Naomi N Thursday, April 12, 2012 3:08 PM
    • Marked as answer by Naomi N Thursday, April 12, 2012 3:08 PM
    Thursday, March 29, 2012 7:29 PM
  • Hi,

    As per your question it seems that you have to upgrade your sql server.

    Sunday, April 1, 2012 7:59 AM
  • A frequently run bad query can cause 80% cpu load.

    System & query optimization:

    http://www.sqlusa.com/articles/query-optimization/

    Disk resources optimization:

    http://www.sqlusa.com/bestpractices/configureharddisk/

    You can find the offending queries via:


    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012


    • Proposed as answer by SAlok Friday, April 6, 2012 12:08 PM
    • Marked as answer by Naomi N Thursday, April 12, 2012 3:09 PM
    • Edited by Kalman Toth Tuesday, October 2, 2012 5:47 PM
    Friday, April 6, 2012 11:44 AM