Server Performance 1

[manmaria]

I like to test the server performance when I do the volume load. How could I do this? What I mean is, when all the users access the Database server at the same time and at that time I want to see the server performance.
We are using SQL Server 2005.

Thanks,

 

[onpnt]

You can use the tools that came with SQL Server 2005. Profiler and tuning advisor to name a few

_{____________ signature below ______________
The worst mistake you'll ever make is to do something simply the way you know how while ignoring the way it should be done}

 

[ptheriault]

Log on to the server and start Perfmon. You can find it in the Admin tools folder.

Add the following counters.
CPU
processor: %processor Time
processor: Interrupts / sec
Record CPU counters by instance & _Total

Memory:
SQL Server Buffer Manager: Page Life Expectancy
SQL Server Buffer Manager: Buffer cache hit ratio
Memory: pages /sec, page faults /sec (in and out)
Process: page faults /sec
process: Private bytes

Disk
Avg read write queue length
%disk time read/write

SQL Server counters
SQL: Access methods (page spilts/forwarded records)
SQL Server: Batch requests per sec
SQL Server: Databases Log flushes /sec
SQL Server Locks: Avg wait time


I could add a lot more but these counters will give you a general idea on how well your database is performing under a load.

Here are some querries to run that will also help you view performance.

--This query will give you an idea of 
--how many tasks are waiting in the system. 
--You can use this information to understand 
--blocking characteristics of your load
 
select count(*)
from sys.dm_os_waiting_tasks
where wait_type <> 'THREADPOOL'

select wait_type, count (*)
from sys.dm_os_waiting_tasks
group by wait_type
order by count (*) desc

--CPU BottleNeck

select  
    scheduler_id, 
    current_tasks_count, 
    runnable_tasks_count 
from  sys.dm_os_schedulers 
where scheduler_id < 255

--The following query gives you a high-level view of which 
--currently cached batches or procedures are using the most CPU
--The query aggregates the CPU consumed by all statements with the same
--plan__handle (meaning that they are part of the same batch or procedure).
--If a given plan_handle has more than one statement, you may have to drill in
--further to find
--the specific query that is the largest contributor to the overall CPU usage 

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

--To find more details about plan run this query
-- takes either spid or plan_handle
SELECT *
FROM sys.dm_exec_query_plan(0x06000700185B93054043ED7F010000000000000000000000)

--   4. Q. Does my load have an active resource bottleneck?
--You can answer this question by looking at the resource address 
--that tasks are blocked on.  
--Keep in mind that not all wait types have resource associated with them. 

select resource_address, count (*)
from sys.dm_os_waiting_tasks
WHERE resource_address <> 0
group by resource_address
order by count (*) desc

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[manmaria]

Thank you for the quick response. Is this supposed to be realtime? If it is realtime, can I record the performance?or can I see the server performance of a week ago or a particular day also?

Thanks,

 

[ptheriault]

This is real time, but you can set up these counters to run over the course of a week. It will create log files that you can then review so you'll have the history.

To customize your own perfmon with these counters. Follow these steps.

1.Open Perfmon.
2.Right Click on Counter Logs -> New Logs
3.Select Add Counters.
4.Add the counters I've suggested.
5.Click on the log Files tab
6.Click on the Configure Button
7.Select the path for you log files click OK
8.Click on the schedule tab & add the your schedule.
9.Click ok to create Perfmon job.

You should now be back at the main Perfmon window.
Click on the Counter Logs in the left hand side. On the right side you should see your Counter job. Right click on it to start it.





- Paul

- If at first you don't succeed, find out if the loser gets anything.