locked
computing the CPU usage ,memory usage for an inserted record RRS feed

  • Question

  •  

     

     I have a client program that writes  10 to 30 records per second to sql server database. i want to compute the CPU usage and the memory usage for the  sql server database in use for the program. or CPU usage,memory usage  for the insert statement in the program .

     

    Can i compute this in the sql server express or do i have to do this in code.

    Can anybody help me with this?

     

    Friday, November 2, 2007 10:08 PM

Answers

  • Hi,

     

    You can use performance dashboard to analyse which query is occupying more CPU usage, memory etc.

    Refer SQL Server 2005 Performance Dashboard Reports

     

    DMVs are there in SQL 2005 which will help you to sort out these issues.

     

    The following query gives you a high-level view of which currently cached batches or procedures are using the most CPU

     

    Code Block

    select top 50

    sum(qs.total_worker_time) as total_cpu_time,

    sum(qs.execution_count) as total_execution_count,

    count(*) as number_of_statements,

    qs.plan_handle

    from

    sys.dm_exec_query_stats qs

    group by qs.plan_handle

    order by sum(qs.total_worker_time) desc

     

     

    Friday, November 2, 2007 11:57 PM

All replies

  •  

     

     I have a client program that writes to sql server database 10 records per second . i want to compute the CPU usage and the memory usage for the whole program  or CPU usage,memory usage  for the insert statement in the program .

     

    Can anybody help me with this?

     

     

     

    Friday, November 2, 2007 10:01 PM
  •  

    I can help you with the question. The answer will be up to you.

     

    I don't think the question is meaningful for many reasons. I'd first of all ask you to focus on cpu measurement. Are you going to look at just the program or are you going to look at the program plus what  SQL resource consumption? If you are going to look at SQL resource consumption what is it that you are going to study? When I bring up SQL it takes 80 to 100 mb of memory. Shall I charge that to the program? Or shall i charge that to SQL?

     

    The second question was about program memory consumption. There is the CLR with all of its libraries. Are you going to charge that to the program? Is it the only .Net program running on the system? Agan what are you going to measure?

     

    With a program and a database, I do not think you are asking the best questions that can be asked. Rather than to attempt to get hard number on blurred boundaries, why not look at system capacity, whether a system has the resources to run a given program?

    Friday, November 2, 2007 11:10 PM
  • Hi,

     

    You can use performance dashboard to analyse which query is occupying more CPU usage, memory etc.

    Refer SQL Server 2005 Performance Dashboard Reports

     

    DMVs are there in SQL 2005 which will help you to sort out these issues.

     

    The following query gives you a high-level view of which currently cached batches or procedures are using the most CPU

     

    Code Block

    select top 50

    sum(qs.total_worker_time) as total_cpu_time,

    sum(qs.execution_count) as total_execution_count,

    count(*) as number_of_statements,

    qs.plan_handle

    from

    sys.dm_exec_query_stats qs

    group by qs.plan_handle

    order by sum(qs.total_worker_time) desc

     

     

    Friday, November 2, 2007 11:57 PM
  • Merge the two threads...
    Monday, November 5, 2007 9:02 AM
  • What i want to do is .. to find the best way to insert the high speed data( that comes at every 10 ms) to the sql server express database table . 

     

    I have two options to store the data in the table . One is to insert all the data in one row , the other is to divide it into many rows in the same table . In approach A i will get like 10 records per second and in approach B i may get i may get 30 - 50 records per second  based on the amount of data that is comming . i.e i am creating a new row in the table for every extra column added in Approach A and duplication the other columns.

     

    I wanted to find the better way to insert the data based on the performace metrics like CPU usage and memory usage.

     

     

    Monday, November 5, 2007 5:31 PM