sql 2008r2 performance issues

Question

Post reply

sql 2008r2 performance issues

guy3145

SSC Enthusiast

Points: 108
More actions
April 5, 2015 at 9:22 am

#398284

Hi All
im using windows server 2008r2 with sql server 2008r2
on this server im running also the TFS server.
lately tfs clients get disconnected, only after a full restart they can work 4 hours straight.
im adding some stats from performance monitor and some sql queries.
can anyone tell what is wrong with this server?
Thanks
Guy

Viewing 11 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 1

Did not understand your question. Can you be more explicit in your qustion??

Thanks.

guy3145 SSC Enthusiast Points: 108 More actions · Answer 2

Hi

thank for your answer!

does all provided data implies high I/O utilization in sql?

we encounter a lot of sql clients disconnected at some point of the day, it is all fixed by restarting the sql server service. im trying to troubleshoot the problem but im a little lost with all the data

Eirikur Eiriksson SSC Guru Points: 182948 More actions · Answer 3

guy3145 (4/5/2015)
Hi
thank for your answer!
does all provided data implies high I/O utilization in sql?
we encounter a lot of sql clients disconnected at some point of the day, it is all fixed by restarting the sql server service. im trying to troubleshoot the problem but im a little lost with all the data

Quick questions, what are the min/max memory settings for the sql server? what is the available memory when things start to break?

😎

guy3145 SSC Enthusiast Points: 108 More actions · Answer 4

the total memory on the server is 30GB.

the minimum memory is set to 0

the maximum memory is set to 10GB. we tried to increase the maximum to 16gb that didnt help.

about memory when things go bad. do you mean task manager view of the memory or some sql query?

Eirikur Eiriksson SSC Guru Points: 182948 More actions · Answer 5

guy3145 (4/6/2015)
the total memory on the server is 30GB.
the minimum memory is set to 0
the maximum memory is set to 10GB. we tried to increase the maximum to 16gb that didnt help.
about memory when things go bad. do you mean task manager view of the memory or some sql query?

Normally I would start by 2/3rd to SQL Server and 1/3rd to the OS, depending on the activities on the server.

😎

DO NOT use the Task Manager, it will lie to you any given Sunday, use something like this query instead:

SELECT

OSM.total_physical_memory_kb / POWER(2,10) AS TOTAL_MEM_MB

,OSM.available_physical_memory_kb / POWER(2,10) AS AVAIL_MEM_MB

,CONVERT(NUMERIC(5,2),(OSM.available_physical_memory_kb / (OSM.total_physical_memory_kb * 0.01)),0) AS AVAIL_MEM_PRC

,OSM.system_memory_state_desc

FROM sys.dm_os_sys_memory OSM;

guy3145 SSC Enthusiast Points: 108 More actions · Answer 6

when the server is "stuck" the output from your query is:

total_mem_mb= 30719

avail_mem_mb=9262

avail_mem_prc=30.15

seems ok, right?

do you have some queries to check I/O latency in sql?

thanks!

guy

Eirikur Eiriksson SSC Guru Points: 182948 More actions · Answer 7

guy3145 (4/6/2015)
when the server is "stuck" the output from your query is:
total_mem_mb= 30719
avail_mem_mb=9262
avail_mem_prc=30.15
seems ok, right?
do you have some queries to check I/O latency in sql?
thanks!
guy

Suggest you increase the max mem, based on this results, around 6-8 Gb and then check again.

😎

What are the server's specs? What type of I/O subsystem, IOPS etc.? Why do you think the I/O latency is truly reflecting the cause of the problem?

Check out this excellent blog post by Paul S. Randal, Wait statistics, or please tell me where it hurts[/url]

guy3145 SSC Enthusiast Points: 108 More actions · Answer 8

thanks.

about the memory we tried to increase it to almost 20GB that didnt solve the issue.

i ran the query from the article you provided and here is the result, seems like a problem with tempDB?

Eirikur Eiriksson SSC Guru Points: 182948 More actions · Answer 9

Have a look at this article: Optimizing tempdb Performance and

Compilation of SQL Server TempDB IO Best Practices

😎

guy3145 SSC Enthusiast Points: 108 More actions · Answer 10

guy3145

SSC Enthusiast

Points: 108

April 8, 2015 at 11:09 am

#1789627

Thanks!

i will try that