• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 186
  • Last Modified:

SQL Server Speed

Hi,

We have a SQL Server 2008 r2 Running on a VM Ware platform with 18gb Ram Assigned on Windows 2008 r2, 4 Cores assigned to VM.

Users are reporting that when they use software that connect to server it seems to run very slow, looking on processors they are never above 50 % and there is 2gb of Ram free.

Has anyone got any suggestions of where to check to make sure the SQL side is running at full steam, not a SQL expert so any help would be great,

Thanks
0
ComexIT
Asked:
ComexIT
  • 3
  • 2
  • 2
  • +2
1 Solution
 
Ryan McCauleyCommented:
The processors aren't at 100% and there's RAM free, but how are the disks? I'd check to make sure the latency is low and throughput is good - I've run into a number of cases (especially on VMs) where storage is slow and it's not always obvious. Do you notice latency when you connect to the server's desktop and attempt to perform normal operations (like browsing folders)? Are there multiple physical drives in the server (or connected to the server) and is SQL spread effectively across storage?

When you're dealing with a VM, all storage aside from dedicated storage inside the host mapped directly to the VM is remote shared storage, so ensure that it's able to access it without any issues.
0
 
Dave BaldwinFixer of ProblemsCommented:
"Speed" of the SQL Server also depends on the queries that they are running.  If they are using a lot of joins in a complicated query that can't use indexes, that can be slow all by itself.
0
 
ComexITAuthor Commented:
Hi,

Its connected to a Dell Equalogix box which is quick, when i do anything on desktop its quick via RDP, no delays at all.

J
0
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
ComexITAuthor Commented:
Just one thing i noticed, when i clicked on properties it shows memory as set to 8gb ..... should this be set to more ?
0
 
Anthony PerkinsCommented:
looking on processors they are never above 50 % and there is 2gb of Ram free.
How do you know that?  If the answer is "I looked at Task Manager" then that is not the whole story:   TM is only showing what VMWare is feeding it.

But as indicated previously your or your DBA or your vendor need to start analyzing what is the query that is making it feel slow.  Caution the root cause may not be the same as the one the user is complaining about.
0
 
Ryan McCauleyCommented:
If you're using a VM, keep in mind that what you see in your resource indicators inside the VM is only part of the story as well - you'll need to ensure that RAM, processor, and all the other resources at the physical host level are also in good shape. Even if the VM is only using 50% if its assigned processor, that doesn't meant that the physical host doesn't contain 50 VMs that are all pegging the host processor as a whole.
0
 
Anthony PerkinsCommented:
If you're using a VM, keep in mind that what you see in your resource indicators inside the VM is only part of the story as well
Exactly.
0
 
gmartinncCommented:
1. How is the host connected to the disks?
2. What is the allocation block size?
3. As for RAM: SQL Server will take all you can give it. You can safely use up to 96% of available RAM, as long as there are no competing apps on that VM. Please be sure your VM's RAM is fixed/reserved in the Host (not dynamically shared with other apps).
4. Also, run this code on the SQL Server and see what you come up with--
/*
Code Courtesy of Jonathan Kehayias
URL: http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/f11537a5-8dc9-40a8-b2e4-17874685b074
Modified
*/
;WITH WaitSource AS
(
select top 10 
wait_type
, waiting_tasks_count
, wait_time_ms
, max_wait_time_ms
, signal_wait_time_ms
--SELECT *
from sys.dm_os_wait_stats
where wait_type not in --remove common waits to identify worst bottlenecks
( 
'KSOURCE_WAKEUP', 'SLEEP_BPOOL_FLUSH', 'BROKER_TASK_STOP',
'XE_TIMER_EVENT', 'XE_DISPATCHER_WAIT', 'FT_IFTS_SCHEDULER_IDLE_WAIT',   
'SQLTRACE_BUFFER_FLUSH', 'CLR_AUTO_EVENT', 'BROKER_EVENTHANDLER',
'LAZYWRITER_SLEEP', 'BAD_PAGE_PROCESS', 'BROKER_TRANSMITTER', 
'CHECKPOINT_QUEUE', 'DBMIRROR_EVENTS_QUEUE', 'LAZYWRITER_SLEEP', 
'ONDEMAND_TASK_QUEUE', 'REQUEST_FOR_DEADLOCK_SEARCH', 'LOGMGR_QUEUE', 
'SLEEP_TASK', 'SQLTRACE_BUFFER_FLUSH', 'CLR_MANUAL_EVENT',
'BROKER_RECEIVE_WAITFOR', 'PREEMPTIVE_OS_GETPROCADDRESS', 
'PREEMPTIVE_OS_AUTHENTICATIONOPS', 'BROKER_TO_FLUSH'
--add from Pinal Dave
, 'FT_IFTSHC_MUTEX', 'FT_RESTART_CRAWL', ' FT_METADATA_MUTEX', ' FT_IFTSHC_MUTEX', ' FT_IFTSISM_MUTEX', ' FT_IFTS_RWLOCK', ' FT_COMPROWSET_RWLOCK', ' FT_MASTER_MERGE', ' FT_IFTS_SCHEDULER_IDLE_WAIT'	
--add from Graeme
, 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP'

) 
order by wait_time_ms desc
)
, SummaryWaits AS
(
	SELECT
		SUM( ws.wait_time_ms) AS TOTAL_wait_time_ms
		, SUM( ws.signal_wait_time_ms) AS TOTAL_signal_time_ms
		, SUM( ws.waiting_tasks_count) AS TOTAL_waiting_tasks_count
	FROM
		WaitSource ws
)
SELECT 
	ws.*
	, CAST( CAST( ws.wait_time_ms AS FLOAT)/CAST( sw.TOTAL_wait_time_ms AS FLOAT) *100 AS NUMERIC(9,2)) AS PCT_TOTAL_WaitTime
FROM
	WaitSource ws
	CROSS JOIN SummaryWaits sw

Open in new window

0
 
Anthony PerkinsCommented:
I think the author is MIA.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 3
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now