asked on

How do I run statistics on MS Sql Server database?

I have a few statistics that I need to figure out and I have never used the MS SQL 'Profiler'. Can someone help me understand how to gather the following stats. I am also under the assumption that I need to use the Profiler to gather this information - please correct me if I'm wrong! :)

avg/max queries per second
avg/max number of rows returned
ave/max number of connections (active)

Also, the purpose of this execise is to help me understand what sort of hardware I should purchase for the amount of bandwidth required on my database server. Are these the stats I really need to know? Please help - newbie here.

ASKER CERTIFIED SOLUTION

chapmandew

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

RiteshShah

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Mark Wills

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Mark Wills

Oh, and here is a handy script showing that a lot of info is in SQL Server.

SELECT master_files.database_id
,databases.name database_name
,master_files.name name
,databases.recovery_model recovery_model
,databases.user_access_desc user_access_desc
,databases.state_desc state_desc
 
,master_files.type_desc type_desc
,master_files.physical_name physical_name
,master_files.state_desc state_desc
,master_files.size size_8KBPages
,master_files.max_size max_size_8KBPages
,master_files.growth growth
,master_files.is_percent_growth is_percent_growth
 
,dm_io_virtual_file_stats.sample_ms MillisecondsSinceComputerWasStarted
,dm_io_virtual_file_stats.num_of_reads num_of_reads
,dm_io_virtual_file_stats.num_of_bytes_read num_of_bytes_read
,dm_io_virtual_file_stats.io_stall_read_ms io_stall_read_ms
,dm_io_virtual_file_stats.num_of_writes num_of_writes
,dm_io_virtual_file_stats.num_of_bytes_written num_of_bytes_written
,dm_io_virtual_file_stats.io_stall_write_ms io_stall_write_ms
,dm_io_virtual_file_stats.io_stall io_stall
,dm_io_virtual_file_stats.size_on_disk_bytes size_on_disk_bytes
 
FROM sys.dm_io_virtual_file_stats(NULL, NULL) dm_io_virtual_file_stats
LEFT JOIN sys.master_files master_files ON dm_io_virtual_file_stats.database_id = master_files.database_id AND dm_io_virtual_file_stats.file_id = master_files.file_id
LEFT JOIN sys.databases databases ON master_files.database_id = databases.database_id
 
ORDER BY databases.name,master_files.type DESC;

Open in new window

burtonrhodes

ASKER

Thanks to all for your suggestions. Let me go through the advice offered over the next several hours and assign point accordingly.

Mark Wills

One more for you then, and that is a list of statistics available : http://technet.microsoft.com/en-us/library/ms190382(SQL.90).aspx

burtonrhodes

ASKER

Many thanks for all your responses.