Server Performance

Question

Post reply

Server Performance

timotech

SSCrazy

Points: 2698
More actions
May 26, 2011 at 7:07 am

#389594

Please can someone direct me to a link or just tell me how to solve this problem: I have a system which i installed sql server 2005, and everyone over the network access the database on the server, i normally use stored procedures on my applications, so that the data is handled from the server area. But the problem is that, once someone is sending a request to the database, the system(i.e the system where the database recides) becomes so slow.
Please what can i do?
Thanks
Timotech

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

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

john jin Valued Member Points: 69 More actions · Answer 1

What can you do?

CPU / Memory / DB size / Table size / Query plan / Trace ....

Please post these key information

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 2

http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/

http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-2/

timotech SSCrazy Points: 2698 More actions · Answer 3

cpu is 2ghz, memory is 2gb, harddisk space 500gb, virtual memory starting at 2038 and ending at 4000mb. So any suggestions please.

Thanks

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

timotech (5/26/2011)
cpu is 2ghz, memory is 2gb, harddisk space 500gb, virtual memory starting at 2038 and ending at 4000mb. So any suggestions please.
Thanks

Has this application been upgraded from 2000 to 2005 on that server? If so did you reindex and update the stats?

Also 2 GB on a prod server is quite low (windows usually need close to that just to run or at least 50%). So that leaves very little for sql server to use! That "might" be a contention point on the server. But I'd start with the articles I just posted.

john jin Valued Member Points: 69 More actions · Answer 5

Would you mind to show the stored procedure and

rows of those tables involved in this stored proc

timotech SSCrazy Points: 2698 More actions · Answer 6

john jin (5/26/2011)
Would you mind to show the stored procedure and
rows of those tables involved in this stored proc

I have thousands of stored procedures running, so i would not be able to show the stored procedures

timotech SSCrazy Points: 2698 More actions · Answer 7

wrote:

I'll start with your posts, but how do i reindex or update the stats, i use sql server 2005

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 8

You have to do the hard slog. Gather performance metrics, capture wait stats, get the longest running and most frequently called queries, then start addressing the issues. There are extremely few shortcuts to this process.

Start with Gail's articles. They're great.

After that, you might want to pick up a copy of my book on Performance Tuning. It's TSQL focused, but the majority of issues that slow down a SQL Server system are.

----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 9

And here's the free version link :

http://downloads.sqlservercentral.com/Books/dissecting_complete.pdf?utm_source=ssc&utm_medium=weblink&utm_content=Grant&utm_campaign=sqltoolbelt

Syed Jahanzaib Bin hassan SSChampion Points: 11410 More actions · Answer 10

For Disk Delays

===============

select DB_NAME(database_id) DB_NAME, di.file_id,df.name,io_stall_read_ms ,num_of_reads

,cast(io_stall_read_ms/(1.0+num_of_reads) as numeric(10,1)) as 'avg_read_stall_ms'

,io_stall_write_ms,num_of_writes

,cast(io_stall_write_ms/(1.0+num_of_writes) as numeric(10,1)) as 'avg_write_stall_ms'

,io_stall_read_ms + io_stall_write_ms as io_stalls

,num_of_reads + num_of_writes as total_io

,cast((io_stall_read_ms+io_stall_write_ms)/(1.0+num_of_reads + num_of_writes) as numeric(10,1)) as 'avg_io_stall_ms'

from sys.dm_io_virtual_file_stats(null,null) di inner join sys.database_files df on df.file_id = di.file_id

where DB_NAME(database_id) = 'your database name'

order by avg_io_stall_ms desc

IO pendings

===========

select

database_id,

file_id,

io_stall,

io_pending_ms_ticks,

scheduler_address

from sys.dm_io_virtual_file_stats(NULL, NULL)t1,

sys.dm_io_pending_io_requests as t2

where t1.file_handle = t2.io_handle

For Memory Presure

==================

select * from sys.dm_os_performance_counters

where counter_name like 'page life%'

For Queries Utilizations

========================

SELECT ST.TEXT,SP.SPID,WAITTIME,LASTWAITTYPE,CPU,PHYSICAL_IO,STATUS,HOSTNAME,PROGRAM_NAME,CMD,LOGINAME FROM SYS.SYSPROCESSES SP

CROSS APPLY SYS.DM_EXEC_SQL_TEXT(SP.SQL_HANDLE) ST

WHERE STATUS !='SLEEPING'

ORDER BY CPU DESC

For CPU Usage

=============

select

scheduler_id,

current_tasks_count,

runnable_tasks_count

from

sys.dm_os_schedulers

where

scheduler_id < 255

For System Info

=============

Select * from sys.dm_os_sys_info

Provide these queries result then we can help you

My Blog
www.aureus-salah.com

timotech SSCrazy Points: 2698 More actions · Answer 11

Thanks Guys for your helps, i'll check on what u posted and get back to u. Thanks