Performance Stats on SQL 2005 & SQL2008

compdigit44
compdigit44 used Ask the Experts™
on
I have two seperation SQL servers: One is running Windows 2003 SQL 2005 & the other is Windows 2008 R2 SQL 2008. I would like to find out the following information from both servers:

1) Number of totally DB connections to each server.
2) Memory IO stats - what is consider high or low I'm not a DB person myself
3) Disk IO stats - what is consider high or low
4) PAge file stats
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Database Analyst
Commented:
Following should give you a pretty clear picture of the memory and connections in both SQL 2005 and 2008 but for IO stats you need Perfmonitor or

Performance Dashboard for 2005
http://www.microsoft.com/downloads/en/details.aspx?FamilyID=1d3a4a0d-7e0c-4730-8204-e419218c1efc

and your own "data collection" for 2008 http://msdn.microsoft.com/en-us/library/bb677179(v=SQL.100).aspx


set nocount on
select [object_name],counter_name,cntr_value as current_value, 'Around 3000 and represents the percentage of pages found in the buffer cache without having to read from disk. The ratio is the total number of cache hits divided by the total number of cache lookups over the last few thousand page accesses. After a long period of time, the ratio moves very little. Because reading from the cache is much less expensive than reading from disk, you want this ratio to be high.'      normal_value from sys.dm_os_performance_counters with (nolock) where counter_name = 'Buffer Cache hit ratio' and object_name in('SQLServer:Buffer Manager','SQLServer:General Statistics','SQLServer:Memory Manager')
union all
select [object_name],counter_name,cntr_value as current_value, 'Idealy there should be no waits. Cumulative number that represents the number of requests per second that had to wait for a free page.'      normal_value from sys.dm_os_performance_counters with (nolock) where counter_name = 'Free list stalls/sec' and object_name in('SQLServer:Buffer Manager','SQLServer:General Statistics','SQLServer:Memory Manager')
union all
select [object_name],counter_name,cntr_value as current_value, 'Total number of pages on all free lists.'      normal_value from sys.dm_os_performance_counters with (nolock) where counter_name = 'Free Pages' and object_name in('SQLServer:Buffer Manager','SQLServer:General Statistics','SQLServer:Memory Manager')
union all
select [object_name],counter_name,cntr_value as current_value, 'Ideal number of pages in the buffer pool.'      normal_value from sys.dm_os_performance_counters with (nolock) where counter_name = 'Target Pages' and object_name in('SQLServer:Buffer Manager','SQLServer:General Statistics','SQLServer:Memory Manager')
union all
select [object_name],counter_name,cntr_value as current_value, 'Compare the number of targeted pages against the stolen pages. If the number of stolen pages does not stabilize over time, the server may eventually get into internal physical memory pressure. '      normal_value from sys.dm_os_performance_counters with (nolock) where counter_name = 'Stolen pages' and object_name in('SQLServer:Buffer Manager','SQLServer:General Statistics','SQLServer:Memory Manager')
union all
select [object_name],counter_name,cntr_value as current_value, 'Around 300 and if it drops below 300 seconds indicates memory pressure.'      normal_value from sys.dm_os_performance_counters with (nolock) where counter_name = 'Page life expectancy' and object_name in('SQLServer:Buffer Manager','SQLServer:General Statistics','SQLServer:Memory Manager')
union all
select [object_name],counter_name,cntr_value as current_value, '600 to 800 normal day / 800 to 1000 bussy day / above 1000 may indicate SQL blocking'      normal_value from sys.dm_os_performance_counters with (nolock) where counter_name = 'Logical Connections' and object_name in('SQLServer:Buffer Manager','SQLServer:General Statistics','SQLServer:Memory Manager')
union all
select [object_name],counter_name,cntr_value as current_value, 'Total SQL server memory needed.'      normal_value from sys.dm_os_performance_counters with (nolock) where counter_name = 'Target Server Memory (KB)' and object_name in('SQLServer:Buffer Manager','SQLServer:General Statistics','SQLServer:Memory Manager')
union all
select [object_name],counter_name,cntr_value as current_value, 'Total SQL server memory available.'      normal_value from sys.dm_os_performance_counters with (nolock) where counter_name = 'Total Server Memory (KB)' and object_name in('SQLServer:Buffer Manager','SQLServer:General Statistics','SQLServer:Memory Manager')
lcohanDatabase Analyst

Commented:
If you prefer to use Microsoft own performance dashboard for sql 2005 AND 2008 here's a link that helps you run it for both and this gives you all and more that you asked for...

http://sql-articles.com/blogs/utilizing-sql-2005-performance-dashboard-in-sql-server-2008/

Author

Commented:
Before I look at any of these links, I'm not a DB person. Will have to install anyting additional in or to run the dashboard?
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

lcohanDatabase Analyst

Commented:
You or someone would have to run some SQL against SQL 2005/2008 servers but is Microsoft own code same as SQL itself (we are running it against PROD db's for a 24*7 e-commerce web site) and the reports themsefl will be stored localy on a client computer.
other than that the query I sent you can run without any issues as they just SELECT data from sql tables/catalogs.

Author

Commented:
My SQL 2005 server has profiler installed?? CAn I use this at all?>

Author

Commented:
Sorry I'm a little confused is there an easy way for me to find all the number of open connections to the DB servers? THe query you posted before is a bit confusing
lcohanDatabase Analyst

Commented:
You should/could use SQL its own sp_who or sp_who2 to get all connections and what they are running in that sql instance or just the select below to get the number of connections only:

select [object_name],counter_name,cntr_value as current_value from sys.dm_os_performance_counters with (nolock) where counter_name = 'Logical Connections' and object_name in('SQLServer:Buffer Manager','SQLServer:General Statistics','SQLServer:Memory Manager')

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial