SQL Server - Help needed

Hello,

We have 6 sql instances on a server, I am asked to study the existing usage of memory, cpu, disk space etc. I don't have rdp access to this server. What's the best way I can accomplish this task?

Here's some information about these instances.
1. We have a lot of user databases on these 6 instances( about 80 databases in 6 instances together with sizes ranging from a few MB to 60 GB ). There's a nightly job that runs for full backups of these user dbs everyday (all the dbs are in simple recovery mode).
2. In 2 instances we have transactional replication.
3. All these instances are used for development and testing purposes  by 6 to 8 app & testing teams.
4. All the instances are: Microsoft SQL Server 2008 (SP2) - 10.0.4000.0 (X64)  on Windows NT 6.0 <X64> (Build 6002: Service Pack 2)  

I need to come up with a strategy for managing backups to reduce the disk space usage. I am thinking of a weekly once fullbackup and a differential backup everyday. any other ideas?

How can I best study the current usage of memory, cpu and related details for these 6 instances with out having access to this server? I could have run performance monitor if I had access to this server, is there a way we can run performance monitor remotely on this server? Any other ideas?

If it's absolutely necessary, we are thinking to migrate 2 instances (non- replication instances) to a new server and that would be based on my inputs.

I am looking for help to accomplish this task. I hope I have provided some kind of useful information to help me on this, please let me know for any additional information. Thanks.

LVL 4
rocky_lotus_newbieAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

lcohanDatabase AnalystCommented:
Here's how you could check CPU LOAD on each instance

--CPU Load:
SELECT scheduler_id
,load_factor                  -- Internal value that indicates the perceived load on this scheduler. This value is used to determine whether a new task should be put on this scheduler or another scheduler.
                                    -- This value is useful for debugging purposes when it appears that schedulers are not evenly loaded.
                                    -- In SQL Server 2000, a task is routed to a particular scheduler.
                                    -- In SQL Server 2005 the routing decision is made based on the load on the scheduler.
                                    -- SQL Server also uses a load factor of nodes and schedulers to help determine the best location to acquire resources.
                                    -- When a task is enqueued, the load factor is increased. When a task is completed, the load factor is decreased. Using the load factors helps SQL Server OS balance the work load better.
, current_tasks_count      -- Number of current tasks that are associated with this scheduler. This count includes the following:
                                    -- Tasks that are waiting for a worker to execute them.
                                    -- Tasks that are currently waiting or running (in SUSPENDED or RUNNABLE state)
, runnable_tasks_count      -- Number of workers, with tasks assigned to them, that are waiting to be scheduled on the runnable queue.
, work_queue_count            -- Number of tasks in the pending queue. These tasks are waiting for a worker to pick them up.
FROM sys.dm_os_schedulers WHERE scheduler_id < 255

To check memory you could run:

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')


Also I suggest you could run Performance Dashboard reports from Microsoft

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

even though they are for 2005 there's a workaround for that:

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

or start collecting using the new one for 2008:
http://msdn.microsoft.com/en-us/library/bb677179(v=SQL.100).aspx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rocky_lotus_newbieAuthor Commented:
Thanks for your timely response Icohan
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.