January 17, 2012 at 2:40 pm
Hi,
My sql server database is down, so my application performance is also down due to the database server down.
How to improve the performance of DataBase Server.
Please give me suggetions.
Thanks,
Suresh
January 17, 2012 at 3:18 pm
Down or slow? Down usually means unavailable, in which case performance tuning isn't going to help. If you mean slow, read these
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
January 18, 2012 at 3:52 pm
Thanks for your reply,
Sql Server DataBase is Slow it's means, when user Searches or Insert or updates in my web application it's taking too much time to do the operation.
This is not there before, recently we are suffering from this situation DataBase Server is slow.
So, There is any method to do improve the performance of Database Server.
or
Any other tools to improve the DB Performance.
Thanks and Regards
Suresh
January 18, 2012 at 4:16 pm
GilaMonster (1/17/2012)
If you mean slow, read these
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
January 19, 2012 at 7:01 am
Performance tuning is hard. It's difficult and it's time consuming and no, there are no reliable and accurate shortcuts.
You can fix some simple stuff by running software like SQL Cop which validates standard settings. That is extremely unlikely to fix your issues, but will help. Other than that, you have to do the hard work of tuning. Gails articles are the single best starting point. When you're ready for more, I recommend my book (link down below).
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 26, 2012 at 9:00 pm
If the performance had been good in the past then I would start with updating the statistics with full scan. And later rebuilding the indexes. You need to schedule these during off hours since the index rebuilds will take the indexes offline.
Why don't you first check the last time the stats were updated using the following query.
select a.id as 'ObjectID', isnull(a.name,'Heap') as 'IndexName', b.name as 'TableName',
stats_date (id,indid) as stats_last_updated_time
from sys.sysindexes as a
inner join sys.objects as b
on a.id = b.object_id
where b.type = 'U' order by stats_last_updated_time desc
If you see that most of the stats were not recently updated then update the stats.
Then check index fragmentation using the following query.
SELECT ps.page_count, ps.database_id, ps.OBJECT_ID, ps.index_id, si.name, ps.avg_fragmentation_in_percent,
(SELECT distinct so.name FROM sys.objects so INNER JOIN sys.indexes ON so.object_id = si.object_id) ParentTable
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS ps
INNER JOIN sys.indexes si ON ps.OBJECT_ID = si.OBJECT_ID
AND ps.index_id = si.index_id
WHERE ps.database_id = DB_ID() AND si.name is not null AND
ps.avg_fragmentation_in_percent > 10 and ps.page_count > 200
ORDER BY ps.avg_fragmentation_in_percent desc
GO
If avg fragmentation is more than 30% then rebuild those indexes. If it is between 10 to 30% then reorg them.
Check stats and index fragmentation for the databases.
Blog
http://saveadba.blogspot.com/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply