April 14, 2011 at 9:58 pm
Two days back one of my client reported me that database is very slow and all users are being affected. Then today she reported that database is normal for few users but few users are still facing slowness problem on database. What I can do in this scenario.
Any help will be appreciable.
April 15, 2011 at 1:19 am
kindly do reindexing of the databse
April 15, 2011 at 1:37 am
If database needs reindexing then how another user is accessing the database in normal ways.
April 15, 2011 at 3:58 am
not sure how you can just say do a full reindex without any information whatsoever.
first thing you need to do is a bit of information digging. find out what part(s) of the application(s) are slow and if the users who are working ok are using the same parts of the application(s).
It could be that those who are working fine are working on a completely seperate DB than those who "are running slow".
As a standard practice it would be wise to check sql error logs/windows application logs etc just to make sure your not missing something which is glaringly obvious.
John
April 16, 2011 at 6:15 am
Hi John,
I looked into log file and checked events log too. But could not get any information regarding slowness of db. What else I can do as I mentioned in my beginning post that few users are facing this issue whereas rest users are using database without any issue. Please suggest.
April 16, 2011 at 7:21 am
Animal Magic (4/15/2011)
first thing you need to do is a bit of information digging. find out what part(s) of the application(s) are slow and if the users who are working ok are using the same parts of the application(s).It could be that those who are working fine are working on a completely seperate DB than those who "are running slow".
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 17, 2011 at 6:37 am
First you check fragmentation level on your database with help of following query
use [your database name]
SELECT
OBJECT_NAME(i.object_id) AS TableName ,
phystat.index_type_desc,
i.name AS TableIndexName,
phystat.avg_fragmentation_in_percent
FROM
sys.dm_db_index_physical_stats(DB_ID(your database Id), NULL, NULL, NULL, 'LIMITED') phystat
inner JOIN sys.indexes i
ON i.object_id = phystat.object_id
AND i.index_id = phystat.index_id WHERE phystat.avg_fragmentation_in_percent > 30
order by phystat.avg_fragmentation_in_percent desc
if avg_fragmentation_in _percent in greater than 30 % then you need to do reindexing on clustered and nonclustered indexes. Dont do it on heap indexes.
April 17, 2011 at 10:40 am
If database is highly fragmented then how few users are using that database without any problem ?
April 17, 2011 at 11:14 am
use the DMV's to determine the slowest query currently in the cache. examine the queries to see if they can benefit to from being rewritten to be more SARG-able, or maybe an index addition or include to improve performance. check your statistics to see if they might be slowing down/affecting queries because they are stale.
Read Gail Shaw's site for performance hints.
Lowell
April 19, 2011 at 1:50 am
amit.sharma 20949 (4/15/2011)
kindly do reindexing of the databse
should not be tried as the first step in troubleshooting!
April 19, 2011 at 2:02 am
beejug1983 (4/16/2011)
Hi John,I looked into log file and checked events log too. But could not get any information regarding slowness of db. What else I can do as I mentioned in my beginning post that few users are facing this issue whereas rest users are using database without any issue. Please suggest.
Log file and event logs will not give much information about bad performance. SQL Server cannot differentiate between good and bad performance. It is us who set the standards with respect to our acceptable limits.
-As you mentioned that few users are doing fine but few are not. Are you sure about the nature of queries they hit? ASK THEM or Trace it
- Use DMVs. I will explain as per my experience. I use DMVs to find which stored procedures are executed most often. Now you have a list with you. Now from this list find out the ones whose average duration is high+ CPU usage+ Reads\Writes
- If you can identify some procedures from the above list, find out their execution plan stored in the cache. In case your RAM is small, the chances are that the execution plan will get wiped out.So execute them your self in your test environment and see their execution plan. You need to make sure there are no index\table scans or lookups.
- You can also run a trace and do include the option of capturing execution plans. Run this from your machine or some test server. Not directly on a prod server.
These are just some tips. I have often seen few dirty solution like adding hardware for a quick fix. Sometimes they work and sometimes they don't. Adding more memory can prevent you from physical I/Os but a scan from a big table or index even if it is in memory is costly. This was taught to me by GilaMonster :-P.
Additionally, have you configured your SQL Server architecture in the recommended ways? You should do that by separating data and log files, tempdb (if possible)
April 20, 2011 at 7:37 am
1) Your best bet BY FAR is to have a qualified professional remote into your machine and debug the perf issue. Probably take under an hour for said individual to find the issue(s) and get you on your way to aleviating the problem.
2) try sp_whoisactive (from sqlblog.com) to check for poor plans, blocking, etc.
3) could be parameter sniffing issue too.
4) LOTS of other potential causes of what you are describing. If you need to get this solved quickly, see 1) above!!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
April 20, 2011 at 10:08 pm
Firstly you need to determine , are all users connecting at same time, what is time period? Are all users in same domain, campus etc, Check if scheduled jobs are running, try to caputure queries and observe the waits, blockings on the server.
The information above will help you segerate the information in terms of queries and server information.
April 26, 2011 at 9:58 am
Were any Network issues detected?
How about running perfmon and getting data from all the major areas(e.g database, hardware , network and even the client machines, in case something has changed there?)
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply