February 15, 2011 at 1:14 pm
Hi
I facing this paging issue, so that the server getting slow in performance how to identify and rectify the action. so that the server to get fast. As a DBA wat action to be taken and how to solve this problem pls help me.
And in another server user saying that for committing 1300 records its take more time to commit.pls tell me the solution.
February 16, 2011 at 2:44 pm
run some of standard reports such as memory consumption, performance total i/o
February 16, 2011 at 7:28 pm
As SQL Server takes chunks of memory from the OS then if you see a valid process(es) using a 'normal' amount of Memory but is paging due to SQL Server taking a high % of available memory then consider limiting SQL memory to stop the swapping.
You could also look at how much memory you have (is it adequate) and move your page files or add new ones.
February 17, 2011 at 5:18 am
1) ALWAYS have a max memory set for sql server. leave 2-4GB for OS and other things
2) what is server configuration please?
3) the commit slowness could be from MANY things - there are almost 200 wait types in sql 2005 IIRC. slow IO, CPU bound, BLOCKING, parallelism, bad code, etc.
4) based on the question I will surmise you aren't very experienced at performance tuning. So the quickest way to resolve this type of problem is to simply hire a performance tuning professional to figure it out and then help mentor you in how to do the same in the future.
5) if you ARE experienced, use sp_whoisactive, wait stats analysis and file IO stall analysis to see what is happening and fix it.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 2, 2012 at 4:35 am
To the topic "are you experienced with performance tuning":
I am the T-SQL developer for my company but NOT a SQL Server performance tuning expert. Since I "live" in SQL everyone looks to me when there is an issue with SQL Server.. and, just my luck, we now have a performance tuning issue that I can't find the root cause of.
I plan to hire an outside consultant. Do you have advice for what to look for as we interview candidates to help us resolve our performance tuning issues? Is there a top things to look for (good or bad)?
Thanks,
-Allen
February 2, 2012 at 7:18 am
Look at the task manager and identify the process that is causing paging. If it is SQL Server then there might two reason that is happening.
1) Check the memory on the system and the max memory settings for SQL Server. If you haven't set the max memory at least 1-2 GB less than the system memory then the OS will keep asking for memory from SQL Server. Based on the 'lock pages in memory settings' you could see that SQL Server is giving up the memory and sometimes page. Check if the SQL Server error logs has any messages related to CACHE FLUSHES.
2) Run perfmon counters and check 'Available Memory MBs' and 'Page life expextancy' (PLE). PLE Should be at least 300-500 and if that is not the case, then SQL Server is facing memory constraints and could be causing paging.
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