[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL Server - Help needed

Posted on 2011-05-12
2
Medium Priority
?
308 Views
Last Modified: 2012-05-11
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.

0
Comment
Question by:rocky_lotus_newbie
2 Comments
 
LVL 40

Accepted Solution

by:
lcohan earned 2000 total points
ID: 35747508
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
 
LVL 4

Author Comment

by:rocky_lotus_newbie
ID: 35771502
Thanks for your timely response Icohan
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Suggested Courses

834 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