[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

How do I run statistics on MS Sql Server database?

Posted on 2009-05-04
7
Medium Priority
?
999 Views
Last Modified: 2012-05-06
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.
0
Comment
Question by:burtonrhodes
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 60

Accepted Solution

by:
chapmandew earned 800 total points
ID: 24296818
You can get most of this data using profiler, but you can use performance monitor for these stats as well.  

perf mon
http://www.sql-server-performance.com/tips/sql_server_performance_monitor_coutners_p1.aspx

profiler
http://www.sql-server-performance.com/tips/sql_server_profiler_tips_p1.aspx
0
 
LVL 31

Assisted Solution

by:RiteshShah
RiteshShah earned 500 total points
ID: 24296833
well as long as hardware concern, it should be depends on your database size, maximum users at any given time, expected DB growth in near future and your own needs and budgets. However, you can use profiler to see your current performance.  Have a look at following URLs for profiler

http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1171978,00.html

http://www.mssqltips.com/tip.asp?tip=1264

http://www.youtube.com/watch?v=QFyJ2NtebzM
 
0
 
LVL 51

Assisted Solution

by:Mark Wills
Mark Wills earned 700 total points
ID: 24297412
Getting a good idea of the type of activity on the database is a good idea to help benchmark the nature of your database. You will also want to look at growth, and probably reads and writes (to help determine typical nature of disk activity - adding data or retrieving data). But getting throughput type results (such as queries per second) is heavily dependant on the current performance of your machine. If using just as a benchmark, then fine, but if using to indicate the volume of activity, then it could be tainted by other factors.

There is a lot of information you can get out of SQL Server itself, but running perfmon over a defined period of activity will probably help formulate a slightly more "qualified" set of measures (and something to fall back on). So, make sure you run the monitor over a fair representation of the nature and type of work you are doing.

One of the difficulties you have is differentiating the type of activity and stats you can gather with the health and performance of your queries. So, you will probably also need to gather some memory, and processor stats as well.

Things like %Processor time, PageLifeExpectancy, BufferCacheHitRatio, ProcessorQueueLength, DiskQueueLength, Disk reads and writes, and can be worthwhile looking for dropped packets on the network, because may as well check network bandwidth at the same time.

A lot of the scripts needed can be found at : http://www.microsoft.com/technet/scriptcenter/scripts/sql/sql2005/default.mspx?mfr=true

There is also some good doco (word documents) to download from MS : http://download.microsoft.com/download/4/7/a/47a548b9-249e-484c-abd7-29f31282b04d/Performance_Tuning_Waits_Queues.doc   and   http://download.microsoft.com/download/1/3/4/134644fd-05ad-4ee8-8b5a-0aed1c18a31e/TShootPerfProbs.doc    these are worthwhile reading because of the nature and types of metrics they discuss along with some queries used to extract data.


0
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 
LVL 51

Expert Comment

by:Mark Wills
ID: 24297586
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

0
 
LVL 3

Author Comment

by:burtonrhodes
ID: 24297646
Thanks to all for your suggestions.  Let me go through the advice offered over the next several hours and assign point accordingly.
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 24297723
One more for you then, and that is a list of statistics available : http://technet.microsoft.com/en-us/library/ms190382(SQL.90).aspx
0
 
LVL 3

Author Closing Comment

by:burtonrhodes
ID: 31577624
Many thanks for all your responses.
0

Featured Post

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question