question

freshdba avatar image
freshdba asked

sql server performance

hi team, i work for a firm , my database size is 250 GB, and 100 users are working over that server, same application , but these days 30 users complaint that server is slow. i had conducted a survey with all users ,70% say it is fine, what migth be the issue, i had checked for blocking, no blockings are the. i am blank please guide me
sql-server-2005performance
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Just curious, why is this question marked as "community wiki"?
0 Likes 0 ·
I think that was a mistake. I unmarked it as a wiki entry. If I'm wrong, please let me know.
0 Likes 0 ·
Cyborg avatar image
Cyborg answered
One possible thing is that the activities of these users on the database might be different. e.g Some of the users may be only doing read operations and the rest may be doing Read/Write operations, note that the write operation is always resource intensive, (if the tables are having multiple indexes, or multiple updates operations - Exclusive lock). As you clearly mention that there is any blocking issue, you should run a trace to get the most expensive queries based on CPU/IO, Analyse those queries find Index Usage statistics, Check for Index fragmentation , Updated statistics etc. I advice you to perform a performance health check by using System monitor/ SQL Server trace Check for bottle-necks on the resources, Check the server configuration (memory), database settings (Auto shrink ,auto growth, auto update stats etc), Check for open transactions etc I hope this will be useful
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

sp_lock avatar image
sp_lock answered
A good start would be posting the specification of the server: cpu , mem, disk setup/config. You need to identify which part of you application is slow... Then look at the query(s) thare a running and see if you can optimize that. Other things are: - Do you maintain indexes? - Do you update stats? - Are there any jobs running during the reported "slow" times? - What are your highest wait types ([ http://www.mssqltips.com/tip.asp?tip=1949][1]) [1]: http://
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image
Grant Fritchey answered
I think the other answers cover the technical aspects of this fairly well. I'll just add this. You need to know how your system behaves. Before users complain, set up systems to gather metrics on the server and the queries. Then, when the users complain, as they almost always will at some point, you can determine if there are real issues. If there are issues, you can also determine if they're with your server, your database, your TSQL code, or elsewhere. Without the metrics in place to understand what's happening on your system, you're in the dark when that call comes in from the users. The systems you set up for gathering metrics can be home-grown using Performance Monitor, server-side traces, Policy Management, Data collection, or any of the other tools & utilities supplied by Microsoft. You can also go and purchase third party apps like Confio Ignite or Idera Diagnostic Manager. It doesn't matter. You need to gather the data ahead of time.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.