When I took this new job in January, I inherited a Windows 2003 Standard server running Sql Server 2005 with only 4 gigs of ram. How in the world does this server even run Sql Server with only 4 gigs of ram. As I always do, I set the min and max memory setting for Sql Server with min being 1 gig of ram and the max getting 2 gigs of ram. If you are not aware, Windows 2003 Standard maximum memory is 4 gigs.
As I began to monitor this server, the first thing I did was ask for more ram since I was seeing Page Life Expectancy issues everyday. If I could get a page to last 3 seconds, I was lucky. I knew to get better performance from the Sql Server, I had to solve this issue. Rebuilding the server was not an option since development was building a newer version of the product. To make it even worse, the server has the web and app layer also on this server. I know you are not suppose to do this, but remember I inherited it. Lucky me!
Due to a great resource of BrentOzar.com, I ran sp_blitzindex on the server to see if what type of indexing issues might be occurring. I saw several indexes not being used and missing indexes. I also saw tables with no clustered indexes. Last night with a change control ticket, I fixed all the missing indexes issues and on one query which was timing out some times, I added the missing clustered index. I also dropped indexes not in use. This morning when I came in to review the monitoring of the server, I noticed my Page Life Expectancy had changed dramatically. The server when from 3 seconds to 2 hours 17 minutes and 51 seconds. Wow, don’t tell my system administrators but fixing the index issues also solved memory pressure issues. You know a DBA always wants more memory. Yes, I knew it was going to help but not to the degree of change I am seeing now.
Check out Brent Ozar and look for the sp_blitzindex and sp_blitz scripts. Brent and team, you make me look very smart. Thanks for all you do for the community.