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
ComexITAsked:
Who is Participating?
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.

Ryan McCauleyData and Analytics ManagerCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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 McCauleyData and Analytics ManagerCommented:
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
gmartinncDatabase ArchitectCommented:
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

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
Anthony PerkinsCommented:
I think the author is MIA.
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 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.